Skip to main content
Available on request — The Autumn Lakehouse is provisioned per customer. Contact us at hey@useautumn.com to get access.
v2_3_balances and v2_3_breakdowns look like a tidy relational schema, but they aren’t one. They are denormalized snapshots of Autumn’s read-time balance computation — the same computation behind GET /customers/:id and the dashboard. Read them as columns named after their source values rather than as a schema you can do naive arithmetic on, and the surprises below disappear. If you only take one thing from this page: the warehouse keeps every entitlement row, the API does not. To reproduce an API or dashboard figure you must re-apply the filter the API applies — active products, non-expired entitlements, current cycle. And add the rollovers back in, because they live in their own table now.

​
The model in one paragraph

For each feature, the API gathers a customer’s active customer_entitlement rows, computes a small set of values per row (included_grant, prepaid_grant, remaining, usage, overage), and sums them into one balance. The ETL materializes exactly this: v2_3_breakdowns is one row per entitlement (the per-row values), and v2_3_balances is the GROUP BY customer × feature × scope sum over it. Two differences matter: the ETL aggregates over all entitlement rows in your database while the API first filters to the active, current set, and the ETL leaves rollover balances out of both tables entirely.

​
Why your numbers look inflated

v2_3_breakdowns is built from a plain join over customer_entitlements with no status, expiry, or cycle filter. Every entitlement row your account has ever held is in there:
  • superseded / cancelled product versions,
  • past reset cycles that already rolled over,
  • pre-seeded future cycles.
The API, by contrast, keeps only entitlements whose product is active (status active, or past_due if your org enables include_past_due) and whose expires_at is in the future, then dedups and sums. So a single v2_3_balances row can sum many lifetimes of usage for one customer × feature. This is not because usage is a lifetime accumulator — each entitlement’s usage is its own current value. It’s because the row aggregates over entitlements the API would have thrown away. A pooled balance that reads granted = 53,000, usage = 3,408,161, remaining = +10,312 is not a corrupted row — it is dozens of historical entitlements summed together. Filter to the active, current-cycle set and it reconciles.

​
Per-column meaning

These are the values the ETL writes, verbatim from the read-time math. Two consequences fall straight out of these definitions:
  1. remaining is floored. Each entitlement contributes max(0, balance), never a negative. You cannot recover how far a balance went negative from remaining — that information lives only in usage.
  2. usage carries the sign. Because usage = granted - balance (per row), usage - granted = -balance. When a balance goes negative (overage), usage exceeds granted by exactly that amount. This is the hook used to reconstruct overage.

​
granted, remaining, usage are not a closed triple

remaining ≠ granted - usage. They diverge for two independent reasons, both by design:
  • Manual “Set Balance” writes balance directly, decoupling it from granted. Set a balance below zero and usage (= granted - balance) inflates past granted with no real consumption behind it — the “spurious negative balance / huge usage” artifact.
  • The flooring of remaining (above) breaks the identity whenever any entitlement is in overage.
Treat each column as the named sum it is, not as a term in an equation. Rollovers used to be a third reason — they were layered into all three columns as separate rollover_* terms that didn’t cancel. They no longer appear here at all, which makes the columns cleaner but means you have to add them yourself.

​
Rollovers live in their own table

Rollover config (rollover_max, rollover_max_percentage, and the two expiry fields) has always been visible, on v2_3_plan_items. Rollover balances were not in the lakehouse at all — so balances.remaining and balances.usage understated the real figures for every customer holding a rollover, silently. v2_3_rollovers fixes that. It is a correctness fix, not a convenience table: if you built a report on rollover-enabled features before it existed, that report was wrong and should be re-run. One row per rollover grant. Join internal_breakdown_id → v2_3_breakdowns.internal_id to attach a rollover to the entitlement it carried over from; internal_plan_item_id gets you the config that produced it. It is a separate table rather than extra columns on v2_3_breakdowns because rollovers are sparse — only about 6.8% of customer entitlements carry one — so folding them in would hang mostly-null columns off every breakdown row to serve a small minority of them. One entitlement can also hold several grants with different expiry dates, which no fixed set of columns represents.
Rollovers expire, and the table keeps the expired ones. expires_at null means never expires; a past expires_at means the grant is dead but still present. Sum balance without a filter and you overstate what the customer holds — always guard with (expires_at IS NULL OR expires_at > now), spelled (coalesce(expires_at, 0) = 0 OR expires_at > toUnixTimestamp(now()) * 1000) in ClickHouse.
Aggregate rollovers in a subquery before joining them to breakdowns — one entitlement can hold several grants, and a direct join fans the breakdown row out so its remaining gets counted once per rollover. There is a ready-to-run query in Querying → Balance including rollovers. Rollover balance also feeds back into overage: unspent rollover is available allowance, so a customer who looks over their grant on v2_3_breakdowns alone may not be in overage once rollovers are counted. The leaderboard query below omits them — fold them in if your features roll over.

​
Overage is derived, not stored — and there are two of them

There is no overage column on v2_3_balances or v2_3_breakdowns. Overage is computed at read time, and there are two distinct figures that are easy to conflate. They use the same per-row quantity (usage - granted = -balance) but floor at different points, so they give different answers. Billable overage — what Autumn invoices. Per entitlement, max(0, -balance), floored per row, then summed. Equivalently Σ max(0, usage - included_grant - prepaid_grant). An undrawn grant on one entitlement never reduces the bill on another.
Displayed overage — what the balance header and dashboard show. The feature-level net, summed first and floored once: max(0, Σusage - Σgranted). Because balances.usage and balances.granted already net per-entitlement surpluses against deficits, an undrawn grant on one entitlement does offset an overage on another.
These two diverge by exactly the undrawn grants (Σ max(0, granted - usage)), and the difference can be large. For one real customer, billable overage was ≈ 824k while displayed (dashboard) overage was ≈ 715k — a ≈ 110k gap of unused allowance and lifetime grants that offset the deficit in the net but not in the per-row floor. The absolute figures drift each cycle; the gap structure does not. The dashboard balance header shows displayed — decide which one you mean before you report it, and label it.
Either way, overage is only meaningful where a usage-based / overage-allowed price exists — a capped feature never goes negative. The leaderboard query in Querying → Current-period overage computes the displayed net (to match the dashboard) and shows the billable variant alongside.

​
The active + current-cycle filter

This is the filter the API applies and the warehouse does not. Re-apply it before any balance, usage, or overage query.
  1. Active plan only. Keep breakdown rows whose internal_customer_product_id matches an active subscription — it is v2_3_subscriptions.internal_id, so this is one direct equality against the customer’s own subscription row, and its status is right there. (Don’t match on internal_product_id instead — it identifies the shared plan version rather than the customer’s copy of it, so you’d have to match on customer and plan and hope the customer had held that plan only once.) If your org enables include_past_due, also keep past_due. One-off entitlements (reset_interval = 'one_off') are always live. A row whose internal_customer_product_id matches a v2_3_purchases row instead is a one-off purchase, not a subscription.
  2. Current cycle only. An entitlement carries one row per reset cycle it has lived through. The current cycle is the one whose reset is the earliest still in the future: per (internal_customer_id, internal_customer_product_id), min(reset_resets_at) where reset_resets_at > now. one_off rows have no cycle and are always kept.
  3. Scope. Keep coalesce(entity_id, '') = '' for customer-scoped totals — but note this now excludes entity-scoped entitlements rather than deduping, because each entitlement produces exactly one row. Customers with config.disable_pooled_balance set track per-entity instead; for those, sum the per-entity rows. See Querying → Pooled vs per-entity.
  4. Not expired. Drop rows whose expires_at is in the past.
  5. Add rollovers. Sum the unexpired v2_3_rollovers.balance for the surviving breakdown rows — the API includes them and these tables don’t.
A ready-to-run query that applies all of this and reproduces the dashboard’s overage leaderboard is in Querying → Current-period overage.

​
Deduction order (why monthly drains before lifetime)

When usage is recorded, Autumn deducts from entitlements shortest-reset-interval-first (a daily grant drains before a monthly, which drains before a lifetime/one_off), after a few higher-priority rules — entity-scoped before pooled when tracking an entity, unlimited first, prepaid before pay-per-use. This is why, when a customer has both a monthly grant and a lifetime grant for the same feature, the monthly empties first and overage lands on whichever pool is drained last. It matters for analytics because it determines which breakdown row shows the overage, not just the total.

​
Footgun: IS NULL throws on these tables

In ClickHouse, on the Iceberg balances/breakdowns/rollovers/flags tables, WHERE entity_id IS NULL raises NOT_FOUND_COLUMN_IN_BLOCK (it reaches for an unmaterialized entity_id.null subcolumn). Use coalesce(entity_id, '') = '' instead, everywhere you’d reach for IS NULL — including v2_3_rollovers.expires_at, where null carries real meaning (“never expires”) and the numeric form is coalesce(expires_at, 0) = 0. BigQuery is unaffected; plain IS NULL works there. See Querying → Pooled vs per-entity.