René's URL Explorer Experiment


Title: perf(billing): sum reporting-window usage from a shared prefix plus a short tail by waleedlatif1 · Pull Request #8974 · simstudioai/sim · GitHub

Open Graph Title: perf(billing): sum reporting-window usage from a shared prefix plus a short tail by waleedlatif1 · Pull Request #8974 · simstudioai/sim

X Title: perf(billing): sum reporting-window usage from a shared prefix plus a short tail by waleedlatif1 · Pull Request #8974 · simstudioai/sim

Description: Summary The reporting-window usage sum (sum(cost) from usage_log where billing_entity_* = … and created_at in [start, end)) is already an index-only scan. It is expensive only because a year-long window covers so many index entries. The soft-gate cache in reporting-usage-cache.ts re-ran it on every 30 s refill, and processes that missed at the same time each ran their own copy. This PR changes only the soft-gate read path (readSoftGateUsageCost, enterprise reporting windows, default pool). Prefix plus tail. Redis holds the window's sum before a cutoff (now − 5 min) for each payer and window, tagged with a global prefix epoch and a per-payer prefix epoch. Each refill adds that prefix to a sum of [cutoff, end), a small index range. One MGET reads the prefix and both epochs together. A prefix whose stored epochs differ from the current ones counts as absent. Single flight across processes. One process re-sums the prefix about every 3 min, under a SET NX PX lease held for one statement timeout (60 s). The lease holder sums its tail in parallel with the prefix, so its read takes about as long as today's one sum. While a refresh runs, other processes serve the stored prefix. It is kept for at most one refresh interval plus one lease. With no prefix stored and the lease held elsewhere, a reader sums the whole window at once, as today. No read waits. Within one process, coalescing is unchanged: LRU fetchMethod. Top-ups to old rows. recordCumulativeUsage replaces the payer's epoch (usage:reporting:v1:prefix-epoch::) after a top-up's transaction commits, when the topped-up row's created_at is older than the cutoff lag (with a 1 min skew margin). Fire-and-forget, Redis-optional, and logged on failure. Any stored cutoff is ≤ its read time − lag, so this bumps for every row that can sit inside a prefix. A row younger than that is still in every tail and needs no bump. A new-row insert never bumps. The next read finds no prefix and sums the whole window under the existing lease, then stores a fresh prefix. So the soft gate sees top-ups with the same ~30 s freshness as before. No race: a refresh that read the epoch before the bump stores under a stale epoch, which nothing reads; one that read it after snapshots after the commit. Nothing increments a stored prefix, so nothing double counts. The epoch keys and invalidation live in reporting-usage-epoch.ts, so usage-log.ts can bump without an import cycle. Deletions. Account deletion (deleteUserAccount, the only path that deletes user rows, and so the only one that cascades usage_log rows away) replaces the global prefix epoch after its transaction commits. That discards every stored prefix, so a deleted member's usage is never counted from a prefix. A refresh that read the old epoch stores under it, where nothing reads it. One that reads the new epoch sums after the commit. Unchanged. Without a ready Redis (absent, not ready, throwing, or slow), the read is today's single whole-window sum. The same goes for a window that does not span the cutoff. The exact billing callers (getBillingPeriodUsageCost and its other callers) are untouched. The prefix and tail are calls to that same function with narrower bounds. Load (largest payer) Before After Whole-window sums One per ~35 s refill, in every process that missed One per ~3 min refresh, leased to one process Full scans per day ~4.5k/day over the last 7 days at p50 937 ms, with same-second duplicates from processes missing together ≤ ~480/day per payer when not constantly topped up; never more than today Each 30 s refill The whole-window sum An index range of ≤ ~8 min of rows A payer whose old rows are topped up continuously falls back to today's whole-window cadence, still single-flight for the refresh, so it is never worse than today. The refresh interval, cutoff lag, lease, and retention are named constants (PREFIX_REFRESH_AFTER_MS, PREFIX_CUTOFF_LAG_MS, PREFIX_LEASE_MS, PREFIX_RETAIN_MS). Correctness contract Never above the ledger. The prefix and tail split the window at the same bound. The ledger only grows (inserts with cost ≥ 0, plus the monotonic cumulative top-up), and deletion invalidates the prefix. Staleness. Reads lag by up to the 30 s cache, as today, including top-ups to old rows (the payer epoch discards the prefix). The one write that goes unseen until the next refresh (≤ ~3 min, ≤ ~4 min if a refresh fails) is a row whose transaction outlived the 5 min lag; created_at defaults to now(), the DB transaction start. It makes the read low. Lease. The lease value cannot tell a stranded lease from a live one, and doesn't need to. While a lease is held, every other reader serves the stored prefix or runs today's whole-window sum. A stranded lease costs at most 60 s without single flight, never a slower or wrong read. Tests Integration tests in reporting-usage-cache.integration.ts run against real Postgres and Redis: Single flight. 5 cold processes hit a due refresh together. Exactly one re-sums the window; the rest serve the stored prefix. No wait. With no prefix and the lease held elsewhere, a read is one immediate whole-window sum (< 1 s). Lower bound. Over random recent inserts, late inserts with old created_at, and old-row top-ups, the served value stays ≤ the exact sum, and equals it after the refresh interval. Deletion. Ledger rows are deleted, the global epoch is replaced, and the next read equals the exact sum. Old-row top-up. A real recordCumulativeUsage top-up to a row inside the stored prefix bumps the payer epoch, and the next read equals the exact sum. Young-row top-up. A top-up to a row still in the tail does not bump, and the next read is still exact via the tail. Top-up racing a refresh. A top-up that commits while the lease holder sums its prefix never makes the read exceed the ledger, and the next read is exact. Stale serving. A stale prefix is served without a window scan while another process holds the lease. No Redis. With Redis absent, the read is one whole-window sum, unchanged from today. Red on the old code. These tests fail on the old implementation and on the previous revision of this PR. Ignoring the epoch makes the deletion test fail. Residual risks Failed payer-epoch bump. If the bump after a top-up fails (Redis not ready or erroring; logged), that top-up can stay unseen by soft gates for ≤ ~4 min. Invoicing and cycle close are unaffected. Failed epoch bump. If replacing the epoch fails after a deletion (Redis not ready or erroring; logged), the deleted usage can stay counted for ≤ ~4 min. Today's 30 s full-sum cache already counts it for ≤ 30 s. Evicted epoch key. If Redis evicts the epoch key within its 1 h retention, the same ≤ ~4 min window applies.

Open Graph Description: Summary The reporting-window usage sum (sum(cost) from usage_log where billing_entity_* = … and created_at in [start, end)) is already an index-only scan. It is expensive only because a year-long w...

X Description: Summary The reporting-window usage sum (sum(cost) from usage_log where billing_entity_* = … and created_at in [start, end)) is already an index-only scan. It is expensive only because a year-long w...

Opengraph URL: https://github.com/simstudioai/sim/pull/8974

X: @github

direct link

Domain: github.com

react-profiling0
react-import-mapreact,react-dom,react-dom/client,react-dom/profiling,react-is,react-reconciler,react/compiler-runtime,react/jsx-dev-runtime,react/jsx-runtime,scheduler@d5750093facf
route-pattern/:user_id/:repository/pull/:id/files(.:format)
route-controllerpull_requests
route-actionfiles
fetch-noncev2:20cfc587-6918-0450-0b3c-0fc2b27c558c
current-catalog-service-hashae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b
request-idDE58:2FBB44:2334C8:315541:6AFB5130
html-safe-nonce0172f283036c2cd649c716aad8be63c54aec41e1290ddd6d4ba0297d208abe0b
visitor-payloadeyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJERTU4OjJGQkI0NDoyMzM0Qzg6MzE1NTQxOjZBRkI1MTMwIiwidmlzaXRvcl9pZCI6IjExMDE3MDQxODM1Nzk0ODcyMCIsInJlZ2lvbl9lZGdlIjoiaWFkIiwicmVnaW9uX3JlbmRlciI6ImlhZCJ9
visitor-hmacfa87117c01b3797d7f972623b8578910f8b3486c81df7635ec08297d551b9163
hovercard-subject-tagpull_request:4847652947
github-keyboard-shortcutsrepository,pull-request-list,pull-request-conversation,pull-request-files-changed,copilot
google-site-verificationApib7-x98H0j5cPqHWwSMm6dNU4GmODRoqxLiDzdx9I
octolytics-urlhttps://collector.github.com/github/collect
analytics-location///pull_requests/show/files
fb:app_id1401488693436528
apple-itunes-appapp-id=1477376905, app-argument=https://github.com/simstudioai/sim/pull/8974/files
twitter:imagehttps://avatars.githubusercontent.com/u/40672544?s=400&v=4
twitter:cardsummary_large_image
og:imagehttps://avatars.githubusercontent.com/u/40672544?s=400&v=4
og:image:altSummary The reporting-window usage sum (sum(cost) from usage_log where billing_entity_* = … and created_at in [start, end)) is already an index-only scan. It is expensive only because a year-long w...
og:site_nameGitHub
og:typeobject
hostnamegithub.com
expected-hostnamegithub.com
None7122bb414b542dba14d71a3f0b9f6fd2abce715b9f5912d082800563e6c806d1
turbo-cache-controlno-preview
diff-viewunified
go-importgithub.com/simstudioai/sim git https://github.com/simstudioai/sim.git
octolytics-dimension-user_id199344406
octolytics-dimension-user_loginsimstudioai
octolytics-dimension-repository_id912559512
octolytics-dimension-repository_nwosimstudioai/sim
octolytics-dimension-repository_publictrue
octolytics-dimension-repository_is_forkfalse
octolytics-dimension-repository_network_root_id912559512
octolytics-dimension-repository_network_root_nwosimstudioai/sim
turbo-body-classeslogged-out env-production page-responsive full-width
disable-turbotrue
browser-stats-urlhttps://api.github.com/_private/browser/stats
browser-errors-urlhttps://api.github.com/_private/browser/errors
releasecbf66c024bb6d10e6955baa7413c54cf13a201bb
ui-targetfull
theme-color#1e2327
color-schemelight dark

Links:

Skip to contenthttps://github.com/simstudioai/sim/pull/8974/files#start-of-content
https://github.com/
Sign inhttps://github.com/login?return_to=https%3A%2F%2Fgithub.com%2Fsimstudioai%2Fsim%2Fpull%2F8974%2Ffiles
GitHubRepository hosting, code review, and collaborationhttps://github.com/platform
CopilotAI coding agent for tasks, projects, and automated workhttps://github.com/features/copilot
ActionsCI/CD and automationhttps://github.com/features/actions
Code QualityQuality analysis, fixes, and enforcementhttps://github.com/features/code-quality
CodespacesCloud development environmentshttps://github.com/features/codespaces
Code SecurityVulnerability detection and remediationhttps://github.com/security/advanced-security/code-security
Secret ProtectionSecret scanning and leak preventionhttps://github.com/security/advanced-security/secret-protection
All Featureshttps://github.com/features
Documentationhttps://docs.github.com/en
Traininghttps://learn.github.com/skills
Certificationshttps://learn.github.com/credentials
Build with Copilothttps://github.com/features/ai
Customize Copilothttps://docs.github.com/en/copilot/how-tos/copilot-on-github/customize-copilot/customize-copilot-overview
Integrationshttps://github.com/marketplace
Trending reposhttps://github.com/trending
GitHub Sponsorshttps://github.com/open-source/sponsors
Developer articleshttps://github.com/resources/articles
ChangelogYour source for the latest features, improvements, and fixes across GitHub products, all in one place.https://github.blog/changelog/
Enterpriseshttps://github.com/enterprise
Teamhttps://github.com/team
Startupshttps://github.com/enterprise/startups
Nonprofitshttps://github.com/solutions/industry/nonprofits
Healthcarehttps://github.com/solutions/industry/healthcare
Financial serviceshttps://github.com/solutions/industry/financial-services
Manufacturinghttps://github.com/solutions/industry/manufacturing
Governmenthttps://github.com/solutions/industry/government
See all industrieshttps://github.com/solutions/industry
Why GitHubhttps://github.com/why-github
Customer Storieshttps://github.com/customer-stories
Business insightshttps://github.com/solutions/executive-insights
Trust Centerhttps://github.com/trust-center
GitHub Advanced SecurityEnterprise-grade code securityhttps://github.com/security/advanced-security
Copilot for businessEnterprise-grade AI featureshttps://github.com/features/copilot/copilot-business
Premium SupportEnterprise-grade 24/7 supporthttps://github.com/enterprise/premium-support
Documentationhttps://docs.github.com/en
Resource centerhttps://github.com/resources
Bloghttps://github.blog/
Events & webinarshttps://github.com/resources/events
Community Forumhttps://github.com/orgs/community/discussions
Customer Storieshttps://github.com/customer-stories
Integrationshttps://github.com/marketplace
Customer supporthttps://help.github.com/
Traininghttps://learn.github.com/skills
GitHub Statushttps://www.githubstatus.com/
Abouthttps://github.com/about
Partnershttps://github.com/partners
Changeloghttps://github.blog/changelog/
GitHub UniverseThe event for developers, by developers — talks, workshops, and what’s next for GitHub.https://githubuniverse.com/
Pricinghttps://github.com/pricing
Sign inhttps://github.com/login?return_to=https%3A%2F%2Fgithub.com%2Fsimstudioai%2Fsim%2Fpull%2F8974%2Ffiles
Sign uphttps://github.com/signup?ref_cta=Sign+up&ref_loc=header+logged+out&ref_page=%2F%3Cuser-name%3E%2F%3Crepo-name%3E%2Fpull_requests%2Fshow%2Ffiles&source=header-repo&source_repo=simstudioai%2Fsim
Reloadhttps://github.com/simstudioai/sim/pull/8974/files
Reloadhttps://github.com/simstudioai/sim/pull/8974/files
Reloadhttps://github.com/simstudioai/sim/pull/8974/files
simstudioaihttps://github.com/simstudioai
simhttps://github.com/simstudioai/sim
Codehttps://github.com/simstudioai/sim
Issues129 (129)https://github.com/simstudioai/sim/issues
Pull requests197 (197)https://github.com/simstudioai/sim/pulls
Actionshttps://github.com/simstudioai/sim/actions
Projectshttps://github.com/simstudioai/sim/projects
Security and qualityhttps://github.com/simstudioai/sim/security
Insightshttps://github.com/simstudioai/sim/pulse
Sign up for GitHub https://github.com/signup?return_to=%2Fsimstudioai%2Fsim%2Fissues%2Fnew%2Fchoose
terms of servicehttps://docs.github.com/terms
privacy statementhttps://docs.github.com/privacy
Sign inhttps://github.com/login?return_to=%2Fsimstudioai%2Fsim%2Fissues%2Fnew%2Fchoose
waleedlatif1https://github.com/waleedlatif1
staginghttps://github.com/simstudioai/sim/tree/staging
perf/billing-usage-cachehttps://github.com/simstudioai/sim/tree/perf/billing-usage-cache
Conversation 13 https://github.com/simstudioai/sim/pull/8974
Commits 3 https://github.com/simstudioai/sim/pull/8974/commits
Checks 51 https://github.com/simstudioai/sim/pull/8974/checks
Files changed https://github.com/simstudioai/sim/pull/8974/files
Please reload this pagehttps://github.com/simstudioai/sim/pull/8974/files
perf(billing): sum reporting-window usage from a shared prefix plus a short tail https://github.com/simstudioai/sim/pull/8974/files#top
Show all changes 3 commits https://github.com/simstudioai/sim/pull/8974/files
edfc4ef perf(billing): sum reporting-window usage from a shared prefix plus a… waleedlatif1 Oct 13, 2026 https://github.com/simstudioai/sim/pull/8974/commits/edfc4efdeb836161df013e425da2db01d4799d86
1f572ca perf(billing): refresh the usage prefix every 3 minutes, discard it o… waleedlatif1 Oct 13, 2026 https://github.com/simstudioai/sim/pull/8974/commits/1f572ca8a4646fdc1edd6619bdd6a2c3c539e506
96385cc fix(billing): discard a payer's usage prefix when a top-up raises a r… waleedlatif1 Oct 13, 2026 https://github.com/simstudioai/sim/pull/8974/commits/96385cce35f18def3cfa0ba84a453b5cc3114e34
Clear filters https://github.com/simstudioai/sim/pull/8974/files
Please reload this pagehttps://github.com/simstudioai/sim/pull/8974/files
Please reload this pagehttps://github.com/simstudioai/sim/pull/8974/files
reporting-usage-cache.integration.ts https://github.com/simstudioai/sim/pull/8974/files#diff-82e94e157ca2946ebf8db682fc4721e56025668b5ee5eb403d21e74881cacbca
reporting-usage-cache.ts https://github.com/simstudioai/sim/pull/8974/files#diff-977dfbb8bb7a097f1980636fd3a5e4b78fdcafae0905d0fbd941eba44a35ab7f
reporting-usage-epoch.ts https://github.com/simstudioai/sim/pull/8974/files#diff-d701e999f19d01652769c666c736b8a867d285847a36b63c288e8c22351e99d9
usage-log.ts https://github.com/simstudioai/sim/pull/8974/files#diff-61e17bbb06140b8753417feb8b60df7a9170f3216b76d314f14d5210cb9e8431
account-deletion.ts https://github.com/simstudioai/sim/pull/8974/files#diff-0ad7b582145ede5bc28a9b4d0fdc670303e35dfff9f0be64a9750358c65b509b
apps/sim/lib/billing/core/reporting-usage-cache.integration.tshttps://github.com/simstudioai/sim/pull/8974/files#diff-82e94e157ca2946ebf8db682fc4721e56025668b5ee5eb403d21e74881cacbca
View file https://github.com/simstudioai/sim/blob/96385cce35f18def3cfa0ba84a453b5cc3114e34/apps/sim/lib/billing/core/reporting-usage-cache.integration.ts
Open in desktop https://desktop.github.com
https://github.co/hiddenchars
https://github.com/simstudioai/sim/pull/8974/{{ revealButtonHref }}
https://github.com/simstudioai/sim/pull/8974/files#diff-82e94e157ca2946ebf8db682fc4721e56025668b5ee5eb403d21e74881cacbca
https://github.com/simstudioai/sim/pull/8974/files#diff-82e94e157ca2946ebf8db682fc4721e56025668b5ee5eb403d21e74881cacbca
https://github.com/simstudioai/sim/pull/8974/files#diff-82e94e157ca2946ebf8db682fc4721e56025668b5ee5eb403d21e74881cacbca
https://github.com/simstudioai/sim/pull/8974/files#diff-82e94e157ca2946ebf8db682fc4721e56025668b5ee5eb403d21e74881cacbca
https://github.com/simstudioai/sim/pull/8974/files#diff-82e94e157ca2946ebf8db682fc4721e56025668b5ee5eb403d21e74881cacbca
https://github.com/simstudioai/sim/pull/8974/files#diff-82e94e157ca2946ebf8db682fc4721e56025668b5ee5eb403d21e74881cacbca
Please reload this pagehttps://github.com/simstudioai/sim/pull/8974/files
Please reload this pagehttps://github.com/simstudioai/sim/pull/8974/files
https://github.com
Termshttps://docs.github.com/site-policy/github-terms/github-terms-of-service
Privacyhttps://docs.github.com/site-policy/privacy-policies/github-privacy-statement
Securityhttps://github.com/security
Statushttps://www.githubstatus.com/
Communityhttps://github.community/
Docshttps://docs.github.com/
Contacthttps://support.github.com?tags=dotcom-footer

Viewport: width=device-width


URLs of crawlers that visited me.