improvement(billing): migrate hot path writes away from user_stats by icecrasher321 · Pull Request #4768 · simstudioai/sim

icecrasher321 · 2026-05-28T09:35:01Z

Summary

Migrate billing away from hot path writes to user_stats table. Source of truth becomes usage logs.

Type of Change

Other: Performance

Testing

Tested manually

Checklist

Code follows project style guidelines
Self-reviewed my changes
Tests added/updated and passing
No new warnings introduced
I confirm that I have read and agree to the terms outlined in the Contributor License Agreement (CLA)

vercel · 2026-05-28T09:35:03Z

The latest updates on your projects. Learn more about Vercel for GitHub.

Project	Deployment	Actions	Updated (UTC)
docs	Ready	Preview, Comment	May 28, 2026 7:28pm

cursor · 2026-05-28T09:35:11Z

PR Summary

High Risk
Changes how usage, limits, overage, and period resets are computed across billing webhooks and enforcement—incorrect ledger aggregation or reset math could mis-bill or block customers.

Overview
This PR moves hot-path billing writes off user_stats and treats usage_log as the append-only ledger for new spend. recordUsage now inserts scoped rows (billing entity, period, hashed eventKey, optional sourceReference) with idempotent onConflictDoNothing, and no longer runs per-event SQL counter bumps on user_stats.

Read paths combine the legacy user_stats period baseline with getBillingPeriodUsageCost (and per-user ledger maps on invoice reset) for limits, overage, org pooling, daily-refresh math, and threshold billing. Call sites (copilot update-cost, wand, voice tokens, workflows, enrichments, knowledge embeddings, etc.) drop additionalStats / MCP call counters / workflow lastActive touches in favor of ledger references.

Period rollover (resetUsageForSubscription, invoice finalized/succeeded, subscription lifecycle) now uses the invoice subscription line period and folds ledger totals into lastPeriodCost before delta-clearing current-period fields.

^{Reviewed by Cursor Bugbot for commit 784fe70. Configure here.}

greptile-apps · 2026-05-28T09:43:39Z

Greptile Summary

This PR migrates the billing hot path away from synchronous user_stats row writes on every usage event. Usage is now written as append-only ledger rows to usage_log with new columns (event_key, billing_entity_type/id, billing_period_start/end); cost aggregations for the current period are computed from the ledger, while the existing userStats.currentPeriodCost column becomes a "pre-cutover baseline" that is added to the ledger sum during the transition period.

Schema: Adds billingEntityTypeEnum, five new nullable columns on usage_log, a partial unique index on event_key for idempotency, and a compound billing-entity-period index with an all-or-none check constraint.
recordUsage: Drops all userStats increments; now inserts attributed ledger rows with SHA-256 event keys and ON CONFLICT DO NOTHING deduplication. Billing context (entity + period) is resolved lazily via resolveBillingContext if not pre-supplied by the caller.
Overage / refresh / threshold paths: Every consumer (calculateSubscriptionOverage, computeOrgOverageAmount, getSimplifiedBillingSummary, threshold billing, usage monitor) now sums baseline + getBillingPeriodUsageCost for the effective usage figure, and passes billingEntity into computeDailyRefreshConsumed to scope the daily-refresh SQL query.

Confidence Score: 4/5

Safe to merge after addressing one billing correctness issue with the daily-refresh deduction during the transition period.

The migration architecture is sound and the idempotency layer, schema constraints, and ledger aggregation functions are well-constructed. One concrete billing defect exists: computeDailyRefreshConsumed now filters to billing-entity-attributed rows only, which excludes pre-cutover NULL-attributed rows, but every caller simultaneously adds the pre-cutover userStats.currentPeriodCost baseline to rawCost. This asymmetry causes the daily refresh to be under-deducted for all days that had usage before deployment, inflating effective usage for the rest of the current billing period and potentially triggering threshold charges or overage amounts sooner than intended.

apps/sim/lib/billing/credits/daily-refresh.ts and all callers that pass billingEntity to computeDailyRefreshConsumed

Important Files Changed

Filename	Overview
apps/sim/lib/billing/core/usage-log.ts	Core of the migration: replaces userStats hot-path writes with append-only ledger inserts, adds billing entity/period attribution, and idempotency via SHA-256 event keys. New getBillingPeriodUsageCost aggregations use exact timestamp equality on period bounds.
apps/sim/lib/billing/credits/daily-refresh.ts	Adds optional billingEntityFilter to the day-bucket SQL query, but the equality predicate excludes all pre-cutover NULL-attributed rows while all callers simultaneously add the pre-cutover userStats baseline to rawCost. This overstates effective usage during the transition period.
apps/sim/lib/billing/core/billing.ts	calculateSubscriptionOverage and getSimplifiedBillingSummary now add getBillingPeriodUsageCost to the userStats baseline. computeOrgOverageAmount is the single place for org overage math; ledgerUsage is added by callers, not internally, avoiding double-counting.
apps/sim/lib/billing/webhooks/invoices.ts	getSubscriptionLinePeriod uses the newer Stripe parent.subscription_item_details API to extract per-period bounds. resetUsageForSubscription now includes ledger usage in lastPeriodCost via getBillingPeriodUsageCostByUser.
packages/db/schema.ts	Adds billingEntityTypeEnum, eventKey/billingEntity/billingPeriod columns to usageLog, partial unique index on eventKey, compound billing-entity-period index, and all-or-none check constraint. Deprecated userStats columns are documented inline.
packages/db/migrations/0218_chubby_zzzax.sql	Correctly adds nullable columns with NOT VALID check constraint and partial btree indexes. The check constraint uses NOT VALID which defers existing-row validation and avoids a full table scan during migration.

Comments Outside Diff (1)

apps/sim/lib/billing/credits/daily-refresh.ts, line 76-113 (link)

Refresh under-deducted during the transition period

computeDailyRefreshConsumed now only sums usageLog rows where billingEntityType = X. Pre-cutover rows (inserted before this PR) have billing_entity_type IS NULL and are silently excluded by the equality filter. Every caller that passes billingEntity also adds the pre-cutover userStats.currentPeriodCost baseline to rawCost, so the baseline usage is included in the "raw" side but gets zero refresh credit on any day it was incurred. The result: effective usage is overstated by SUM(MIN(pre-cutover-day-usage, daily_refresh_dollars)) for every day that had usage before deployment. This inflates reported effective usage for the remainder of the current billing period, triggering threshold bills and computing overage amounts sooner than they should fire.

To fix without abandoning the entity-scope approach: also include rows where billingEntityType IS NULL (pre-cutover rows belonging to these users) in the date-range clauses, or hold off adding billingEntityFilter until userStats.currentPeriodCost has been zeroed out by the first period reset under the new system.

_{Reviews (2): Last reviewed commit: "regen migrations" | Re-trigger Greptile}

icecrasher321 · 2026-05-28T17:20:25Z

@greptile

icecrasher321 · 2026-05-28T17:20:30Z

bugbot run

Co-authored-by: Cursor <cursoragent@cursor.com>

…g-ledge

icecrasher321 · 2026-05-28T19:25:22Z

@greptile

icecrasher321 · 2026-05-28T19:25:26Z

bugbot run

cursor

Cursor Bugbot has reviewed your changes and found 1 potential issue.

^{❌ Bugbot Autofix is OFF. To automatically fix reported issues with cloud agents, enable autofix in the Cursor dashboard.}

^{Reviewed by Cursor Bugbot for commit 784fe70. Configure here.}

icecrasher321 added 2 commits May 28, 2026 02:15

improvement(billing): migrate hot path writes away from user_stats

5996a5e

fix period start and end sot

05d3bab

vercel Bot temporarily deployed to Preview May 28, 2026 09:35 Inactive

cursor Bot reviewed May 28, 2026

View reviewed changes

Comment thread apps/sim/lib/billing/credits/daily-refresh.ts

Comment thread apps/sim/lib/billing/calculations/usage-monitor.ts

Comment thread apps/sim/app/api/mcp/copilot/route.ts Outdated

greptile-apps Bot reviewed May 28, 2026

View reviewed changes

Comment thread apps/sim/lib/billing/webhooks/invoices.ts

address comments

c8fa9b7

vercel Bot temporarily deployed to Preview May 28, 2026 17:20 Inactive

cursor Bot reviewed May 28, 2026

View reviewed changes

Comment thread apps/sim/lib/billing/calculations/usage-monitor.ts Outdated

icecrasher321 and others added 3 commits May 28, 2026 12:19

Remove stale billing migration

6656796

Co-authored-by: Cursor <cursoragent@cursor.com>

Merge remote-tracking branch 'origin/staging' into improvement/billin…

fdd1da3

…g-ledge

regen migrations

784fe70

vercel Bot deployed to Preview May 28, 2026 19:28 View deployment

cursor Bot reviewed May 28, 2026

View reviewed changes

Comment thread apps/sim/lib/billing/webhooks/invoices.ts

icecrasher321 merged commit 53eaa60 into staging May 28, 2026
14 checks passed

icecrasher321 deleted the improvement/billing-ledge branch May 28, 2026 20:54

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

improvement(billing): migrate hot path writes away from user_stats#4768

improvement(billing): migrate hot path writes away from user_stats#4768
icecrasher321 merged 6 commits into
stagingfrom
improvement/billing-ledge

icecrasher321 commented May 28, 2026 •

edited

Loading

Uh oh!

vercel Bot commented May 28, 2026 •

edited

Loading

Uh oh!

cursor Bot commented May 28, 2026 •

edited

Loading

Uh oh!

Uh oh!

Uh oh!

Uh oh!

greptile-apps Bot commented May 28, 2026 •

edited

Loading

Comments Outside Diff (1)

Uh oh!

Uh oh!

icecrasher321 commented May 28, 2026

Uh oh!

icecrasher321 commented May 28, 2026

Uh oh!

Uh oh!

icecrasher321 commented May 28, 2026

Uh oh!

icecrasher321 commented May 28, 2026

Uh oh!

cursor Bot left a comment

Uh oh!

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

1 participant

Conversation

icecrasher321 commented May 28, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Summary

Type of Change

Testing

Checklist

Uh oh!

vercel Bot commented May 28, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

cursor Bot commented May 28, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

PR Summary

Uh oh!

Uh oh!

Uh oh!

Uh oh!

greptile-apps Bot commented May 28, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Greptile Summary

Confidence Score: 4/5

Important Files Changed

Comments Outside Diff (1)

Uh oh!

Uh oh!

icecrasher321 commented May 28, 2026

Uh oh!

icecrasher321 commented May 28, 2026

Uh oh!

Uh oh!

icecrasher321 commented May 28, 2026

Uh oh!

icecrasher321 commented May 28, 2026

Uh oh!

cursor Bot left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

1 participant

icecrasher321 commented May 28, 2026 •

edited

Loading

vercel Bot commented May 28, 2026 •

edited

Loading

cursor Bot commented May 28, 2026 •

edited

Loading

greptile-apps Bot commented May 28, 2026 •

edited

Loading