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.
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 per scope (the per-row values), and v2_3_balances is the GROUP BY customer × feature × scope sum over it. The only — but decisive — difference is that the ETL aggregates over all entitlement rows in your database, while the API first filters to the active, current set.
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.
| Column (both tables unless noted) | Definition |
|---|
included_grant (breakdowns) | allowance + adjustment, scaled by product quantity / entity count |
prepaid_grant (breakdowns) | prepaid quantity × billing units (0 unless a prepaid price is linked) |
granted (balances) | Σ(included_grant + prepaid_grant) + rollover-granted |
usage | included_grant + prepaid_grant - balance per row (signed balance); summed, then + rollover_usage - unused |
remaining | max(0, balance) per row — floored at 0 — summed, then + rollover_balance + unused |
overage | not stored — see below |
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.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 three independent reasons, all 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.
- Rollover and unused terms are layered into
granted/remaining/usage separately (rollover-granted, rollover-balance, rollover-usage, unused), and don’t cancel.
- 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.
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. This is cusEntToInvoiceOverage.
billable_overage = Σ max(0, usage - included_grant - prepaid_grant) -- floor each row, then sum
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.
displayed_overage = max(0, Σusage - Σgranted) -- sum first, then floor once
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.
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.
- Active product only. Keep breakdown rows whose
internal_product_id matches an active subscription (v2_3_subscriptions.status = 'active'). If your org enables include_past_due, also keep past_due. One-off entitlements (reset_interval = 'one_off') are always live.
- 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_product_id), min(reset_resets_at) where reset_resets_at > now. one_off rows have no cycle and are always kept.
- Pooled vs per-entity. Keep
coalesce(entity_id, '') = '' for customer-level (pooled) totals. Customers with config.disable_pooled_balance set track per-entity instead — for those, sum the per-entity rows rather than the pooled row. See Querying → Pooled vs per-entity.
- Not expired. Drop rows whose
expires_at is in the past.
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.
On the Iceberg balances/breakdowns/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. See Querying → Pooled vs per-entity.
