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-profiling | 0 |
| react-import-map | react,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-controller | pull_requests |
| route-action | files |
| fetch-nonce | v2:20cfc587-6918-0450-0b3c-0fc2b27c558c |
| current-catalog-service-hash | ae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b |
| request-id | DE58:2FBB44:2334C8:315541:6AFB5130 |
| html-safe-nonce | 0172f283036c2cd649c716aad8be63c54aec41e1290ddd6d4ba0297d208abe0b |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJERTU4OjJGQkI0NDoyMzM0Qzg6MzE1NTQxOjZBRkI1MTMwIiwidmlzaXRvcl9pZCI6IjExMDE3MDQxODM1Nzk0ODcyMCIsInJlZ2lvbl9lZGdlIjoiaWFkIiwicmVnaW9uX3JlbmRlciI6ImlhZCJ9 |
| visitor-hmac | fa87117c01b3797d7f972623b8578910f8b3486c81df7635ec08297d551b9163 |
| hovercard-subject-tag | pull_request:4847652947 |
| github-keyboard-shortcuts | repository,pull-request-list,pull-request-conversation,pull-request-files-changed,copilot |
| google-site-verification | Apib7-x98H0j5cPqHWwSMm6dNU4GmODRoqxLiDzdx9I |
| octolytics-url | https://collector.github.com/github/collect |
| analytics-location | ///pull_requests/show/files |
| fb:app_id | 1401488693436528 |
| apple-itunes-app | app-id=1477376905, app-argument=https://github.com/simstudioai/sim/pull/8974/files |
| twitter:image | https://avatars.githubusercontent.com/u/40672544?s=400&v=4 |
| twitter:card | summary_large_image |
| og:image | https://avatars.githubusercontent.com/u/40672544?s=400&v=4 |
| og:image:alt | 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... |
| og:site_name | GitHub |
| og:type | object |
| hostname | github.com |
| expected-hostname | github.com |
| None | 7122bb414b542dba14d71a3f0b9f6fd2abce715b9f5912d082800563e6c806d1 |
| turbo-cache-control | no-preview |
| diff-view | unified |
| go-import | github.com/simstudioai/sim git https://github.com/simstudioai/sim.git |
| octolytics-dimension-user_id | 199344406 |
| octolytics-dimension-user_login | simstudioai |
| octolytics-dimension-repository_id | 912559512 |
| octolytics-dimension-repository_nwo | simstudioai/sim |
| octolytics-dimension-repository_public | true |
| octolytics-dimension-repository_is_fork | false |
| octolytics-dimension-repository_network_root_id | 912559512 |
| octolytics-dimension-repository_network_root_nwo | simstudioai/sim |
| turbo-body-classes | logged-out env-production page-responsive full-width |
| disable-turbo | true |
| browser-stats-url | https://api.github.com/_private/browser/stats |
| browser-errors-url | https://api.github.com/_private/browser/errors |
| release | cbf66c024bb6d10e6955baa7413c54cf13a201bb |
| ui-target | full |
| theme-color | #1e2327 |
| color-scheme | light dark |
Links:
| Skip to content | https://github.com/simstudioai/sim/pull/8974/files#start-of-content |
| https://github.com/ |
| Sign in | https://github.com/login?return_to=https%3A%2F%2Fgithub.com%2Fsimstudioai%2Fsim%2Fpull%2F8974%2Ffiles |
| GitHubRepository hosting, code review, and collaboration | https://github.com/platform |
| CopilotAI coding agent for tasks, projects, and automated work | https://github.com/features/copilot |
| ActionsCI/CD and automation | https://github.com/features/actions |
| Code QualityQuality analysis, fixes, and enforcement | https://github.com/features/code-quality |
| CodespacesCloud development environments | https://github.com/features/codespaces |
| Code SecurityVulnerability detection and remediation | https://github.com/security/advanced-security/code-security |
| Secret ProtectionSecret scanning and leak prevention | https://github.com/security/advanced-security/secret-protection |
| All Features | https://github.com/features |
| Documentation | https://docs.github.com/en |
| Training | https://learn.github.com/skills |
| Certifications | https://learn.github.com/credentials |
| Build with Copilot | https://github.com/features/ai |
| Customize Copilot | https://docs.github.com/en/copilot/how-tos/copilot-on-github/customize-copilot/customize-copilot-overview |
| Integrations | https://github.com/marketplace |
| Trending repos | https://github.com/trending |
| GitHub Sponsors | https://github.com/open-source/sponsors |
| Developer articles | https://github.com/resources/articles |
| ChangelogYour source for the latest features, improvements, and fixes across GitHub products, all in one place. | https://github.blog/changelog/ |
| Enterprises | https://github.com/enterprise |
| Team | https://github.com/team |
| Startups | https://github.com/enterprise/startups |
| Nonprofits | https://github.com/solutions/industry/nonprofits |
| Healthcare | https://github.com/solutions/industry/healthcare |
| Financial services | https://github.com/solutions/industry/financial-services |
| Manufacturing | https://github.com/solutions/industry/manufacturing |
| Government | https://github.com/solutions/industry/government |
| See all industries | https://github.com/solutions/industry |
| Why GitHub | https://github.com/why-github |
| Customer Stories | https://github.com/customer-stories |
| Business insights | https://github.com/solutions/executive-insights |
| Trust Center | https://github.com/trust-center |
| GitHub Advanced SecurityEnterprise-grade code security | https://github.com/security/advanced-security |
| Copilot for businessEnterprise-grade AI features | https://github.com/features/copilot/copilot-business |
| Premium SupportEnterprise-grade 24/7 support | https://github.com/enterprise/premium-support |
| Documentation | https://docs.github.com/en |
| Resource center | https://github.com/resources |
| Blog | https://github.blog/ |
| Events & webinars | https://github.com/resources/events |
| Community Forum | https://github.com/orgs/community/discussions |
| Customer Stories | https://github.com/customer-stories |
| Integrations | https://github.com/marketplace |
| Customer support | https://help.github.com/ |
| Training | https://learn.github.com/skills |
| GitHub Status | https://www.githubstatus.com/ |
| About | https://github.com/about |
| Partners | https://github.com/partners |
| Changelog | https://github.blog/changelog/ |
| GitHub UniverseThe event for developers, by developers — talks, workshops, and what’s next for GitHub. | https://githubuniverse.com/ |
| Pricing | https://github.com/pricing |
| Sign in | https://github.com/login?return_to=https%3A%2F%2Fgithub.com%2Fsimstudioai%2Fsim%2Fpull%2F8974%2Ffiles |
| Sign up | https://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 |
| Reload | https://github.com/simstudioai/sim/pull/8974/files |
| Reload | https://github.com/simstudioai/sim/pull/8974/files |
| Reload | https://github.com/simstudioai/sim/pull/8974/files |
| simstudioai | https://github.com/simstudioai |
| sim | https://github.com/simstudioai/sim |
| Code | https://github.com/simstudioai/sim |
| Issues129 (129) | https://github.com/simstudioai/sim/issues |
| Pull requests197 (197) | https://github.com/simstudioai/sim/pulls |
| Actions | https://github.com/simstudioai/sim/actions |
| Projects | https://github.com/simstudioai/sim/projects |
| Security and quality | https://github.com/simstudioai/sim/security |
| Insights | https://github.com/simstudioai/sim/pulse |
| Sign up for GitHub
| https://github.com/signup?return_to=%2Fsimstudioai%2Fsim%2Fissues%2Fnew%2Fchoose |
| terms of service | https://docs.github.com/terms |
| privacy statement | https://docs.github.com/privacy |
| Sign in | https://github.com/login?return_to=%2Fsimstudioai%2Fsim%2Fissues%2Fnew%2Fchoose |
| waleedlatif1 | https://github.com/waleedlatif1 |
| staging | https://github.com/simstudioai/sim/tree/staging |
| perf/billing-usage-cache | https://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 page | https://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 page | https://github.com/simstudioai/sim/pull/8974/files |
| Please reload this page | https://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.ts | https://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 page | https://github.com/simstudioai/sim/pull/8974/files |
| Please reload this page | https://github.com/simstudioai/sim/pull/8974/files |
|
| https://github.com |
| Terms | https://docs.github.com/site-policy/github-terms/github-terms-of-service |
| Privacy | https://docs.github.com/site-policy/privacy-policies/github-privacy-statement |
| Security | https://github.com/security |
| Status | https://www.githubstatus.com/ |
| Community | https://github.community/ |
| Docs | https://docs.github.com/ |
| Contact | https://support.github.com?tags=dotcom-footer |
Viewport: width=device-width
URLs of crawlers that visited me.