Ask a subscriptions table what a customer paid in March and it tells you what they pay today. That’s how a $400 signup that upgraded on the 18th ends up reported as $900 of new business.
The query behind that is the most common MRR query there is: take the subscriptions table as it stands, sum the active amounts, and label anything that started this month as new. The total is fine. The label is wrong, because the customer signed on March 1 at $400, and by the time the query ran the row said $900 and nothing else.
The customer is invented, and so is the vendor. Lanternfish Analytics sells seats, and Bellmoor and Co is the fixture I’m using for this build. It’s small on purpose: four events, enough to show where a state-based report goes wrong.
What the subscriptions table forgot
A subscription row is the answer to “what is this customer on right now”. An upgrade overwrites the plan. A seat change overwrites the quantity. Rerun March’s report in July and Bellmoor shows $750, a number that was never true in March.
You can patch this with month-end snapshots. They fix the totals and still can’t say whether a $500 difference between two snapshots was one upgrade or an upgrade and a downgrade that happened to net out. Finance asks about the movements, so I’d rather store the movements.
One row shape for every billing event
Billing systems emit lifecycle events with different payloads: created, plan changed, quantity changed, paused, resumed, cancelled. The normalizer’s only job is to flatten them into one row shape that carries the state after the change.
create table subscription_event (
event_id text primary key,
subscription_id text not null,
customer_id text not null,
event_type text not null check (event_type in
('created', 'plan_changed', 'quantity_changed',
'paused', 'resumed', 'cancelled')),
effective_at timestamptz not null,
unit_amount numeric(12,2) not null, -- per unit, per billing interval
quantity integer not null,
billing_interval text not null check (billing_interval in ('month', 'year'))
);
Two things about this table are easy to get wrong. effective_at is when the change takes effect for billing, never when the webhook arrived. And a cancellation is stamped with the first day the customer is no longer paying, so a cancel effective August 31 lands as September 1.
A ledger table where nothing updates
create table mrr_movement (
event_id text not null references subscription_event (event_id),
entry text not null default 'event'
check (entry in ('event', 'pause_expiry')),
subscription_id text not null,
customer_id text not null,
effective_on date not null,
movement_type text not null check (movement_type in
('new', 'expansion', 'contraction', 'churn', 'reactivation')),
mrr_delta numeric(12,2) not null check (mrr_delta <> 0),
mrr_after numeric(12,2) not null check (mrr_after >= 0),
primary key (event_id, entry, movement_type)
);
Rows go in and never change. I use the same habit for usage billing, for the same reason: a correction is a new row you can see, where an update is a number that quietly became different.
MRR on any date is the sum of mrr_delta up to that date. There’s no separate total to drift away from the movements.
LAG does the subtraction
with priced as (
select e.*,
case
when event_type in ('cancelled', 'paused') then 0
when billing_interval = 'year'
then round(unit_amount * quantity / 12, 2)
else unit_amount * quantity
end as mrr_after
from subscription_event e
),
stepped as (
select p.*,
lag(mrr_after) over w as mrr_before,
lag(event_type) over w as prev_type,
lag(effective_at) over w as prev_at,
max(mrr_after) over (w rows between unbounded preceding
and 1 preceding) as peak_before
from priced p
window w as (partition by subscription_id
order by effective_at, event_id)
)
insert into mrr_movement
(event_id, subscription_id, customer_id, effective_on,
movement_type, mrr_delta, mrr_after)
select event_id, subscription_id, customer_id,
(effective_at at time zone 'UTC')::date,
case
when coalesce(peak_before, 0) = 0 then 'new'
when mrr_before = 0 and prev_type = 'paused'
and effective_at < prev_at + interval '60 days'
then 'expansion'
when mrr_before = 0 then 'reactivation'
when event_type = 'cancelled' then 'churn'
when mrr_after > mrr_before then 'expansion'
else 'contraction'
end,
mrr_after - coalesce(mrr_before, 0),
mrr_after
from stepped
where mrr_after <> coalesce(mrr_before, 0)
on conflict do nothing;
peak_before is there for trials. A subscription that starts at $0 and converts has a previous row with zero MRR, and without that column the conversion would be classified as a reactivation. With it, anything that has never paid is new.
The where clause drops events that don’t move MRR, like a plan rename. The on conflict makes the job safe to rerun over the whole event table, which is how I’d run it until volume says otherwise.
Five rules finance can read without SQL
The SQL encodes decisions that finance should be able to read without reading SQL. These live next to the code:
- An annual plan counts as its price divided by 12, from the day it starts. Cash timing belongs to a different report.
- Proration charges and credits are not MRR. They’re one-time invoice lines that exist because a change happened mid-period. The change itself is already in the ledger.
- A pause is contraction to zero. If the customer resumes inside 60 days, the resume is expansion. If the pause reaches day 60, it becomes churn.
- Coming back after churn is reactivation, never new.
- A discount with an end date is a plan change at both ends. One-time credits are ignored.
The 60-day rule is the awkward one in an append-only table, because the pause was already booked as contraction. A daily job reclassifies it with two rows that net to zero:
insert into mrr_movement
(event_id, entry, subscription_id, customer_id, effective_on,
movement_type, mrr_delta, mrr_after)
select m.event_id, 'pause_expiry', m.subscription_id, m.customer_id,
m.effective_on + 60, v.movement_type, v.sign * m.mrr_delta, 0
from mrr_movement m
join subscription_event e
on e.event_id = m.event_id and e.event_type = 'paused'
cross join (values ('contraction', -1), ('churn', 1))
as v (movement_type, sign)
where m.entry = 'event'
and m.movement_type = 'contraction'
and m.effective_on + 60 <= current_date
and not exists (
select 1 from subscription_event n
where n.subscription_id = m.subscription_id
and n.event_type = 'resumed'
and n.effective_at > e.effective_at
and n.effective_at < e.effective_at + interval '60 days')
on conflict do nothing;
One row reverses the contraction, the other books the same amount as churn. Total MRR doesn’t move on day 60. Only the label does.
Bellmoor and Co through the job
Four events in, four ledger rows out:
| Effective | Event | MRR after | Delta | Movement |
|---|---|---|---|---|
| Mar 1 | created, flat $400 plan | 400 | +400 | new |
| Mar 18 | plan changed, 30 seats at $30 | 900 | +500 | expansion |
| Jun 1 | quantity changed, 25 seats | 750 | -150 | contraction |
| Sep 1 | cancelled, paid through Aug 31 | 0 | -750 | churn |
March now reads $400 new and $500 expansion. The upgrade invoice also carried a proration charge of $225.81, which is the $500 difference for 14 of March’s 31 days. It appears nowhere above, and that’s correct: add it and March MRR becomes a number no later month will ever match.
The deltas sum to zero, which is what a customer who came and went should sum to.
Rollup, then an argument with the invoices
select month, new_mrr, expansion, contraction, churn, reactivation,
sum(net) over (order by month) as ending_mrr
from (
select date_trunc('month', effective_on)::date as month,
coalesce(sum(mrr_delta) filter (where movement_type = 'new'), 0) as new_mrr,
coalesce(sum(mrr_delta) filter (where movement_type = 'expansion'), 0) as expansion,
coalesce(sum(mrr_delta) filter (where movement_type = 'contraction'), 0) as contraction,
coalesce(sum(mrr_delta) filter (where movement_type = 'churn'), 0) as churn,
coalesce(sum(mrr_delta) filter (where movement_type = 'reactivation'), 0) as reactivation,
sum(mrr_delta) as net
from mrr_movement
group by 1
) m
order by month;
Ending MRR is a running sum of the same rows the five columns came from, so the columns can’t disagree with the total. They can still both be wrong, if an event never arrived.
That’s what the reconciliation check is for. For each subscription, take the recurring lines on its latest invoice, leave out proration and one-time items, divide annual ones by 12, and compare with the ledger’s mrr_after on the invoice date. Mismatches go to a list before the dashboard refreshes. A missing webhook shows up there as an invoice for $900 against a ledger that still says $400.
What I’d still change
The grain is the subscription. A customer who cancels one subscription and opens another the same day shows as churn plus new, when finance would call it a plan change. Partitioning the window by customer_id fixes the label and makes every other rule harder to reason about, so I’d start per subscription and add a customer-level pass only when that case turns up in the mismatch list.
Late events are the other gap. If an event shows up backdated behind rows that are already booked, on conflict do nothing keeps the old deltas, and they’re now wrong. The append-only answer is a reversal entry plus a rebook, and I haven’t written it here. Until it exists, the reconciliation list is what catches this.
Events with identical timestamps are ordered by event_id here, which is arbitrary. If the billing system exposes a sequence number, order by that.