Case study · Subscription revamp
What should a subscription table store?
Ours stored the answer: ACTIVE, 10 staff, ends day 30. That answer goes stale at midnight and loses its history on every edit. In the overhaul we stored the facts instead, and worked the answer out when someone asks.
- id
- 5512
- startDate
- day 1
- endDate
- day 30
- status
- ACTIVE
- staffLimit
- 10
- updatedAt
- day 1
Stored status and dates agree.
The dates say ACTIVE
Before
One row did three jobs
The old table kept one row per subscription and edited it in place. The same row was the record of the purchase, the current state, and the source for staff access.
Job 1
It stored an answer that depends on the date
Status lived in a column. A refresh job had to rewrite it whenever a date passed. When we compared each row's stored status with its own dates, a large share of the table disagreed with itself.
Job 2
Every change erased the one before
A renewal overwrote the dates. An extension moved the end date and kept no record of its price. The row showed the current limit and nothing about how it got there.
Job 3
Access trusted it
The access check read the status and skipped the dates. It also let through rows that were deactivated or had not started yet.
Decision 1
Store what happened
The ledger is the new source of truth. Each row is one fact about coverage, and nobody updates or deletes a row. A correction is a new row with the opposite sign.
A fact is filed once
Every row records where it came from, down to the order and the line item. We call that its provenance, and it is unique across the ledger. A payment webhook delivered twice finds its provenance already filed and appends nothing. The customer gets no second grant and no second SMS.
Decision 2
Compute the state
State comes from the ledger and today's date. For any day, add the signed counts of the rows whose window covers it. We call that sum the fold. Trial rows and paid rows fold separately and paid coverage wins while it is in force, so a purchase in the middle of a trial needs no row that cancels the trial. Some paid plans keep access for a few days of grace after the window ends.
One fictional account, 75 days
Choose which facts happen. Then move today.
Ledger
| Filed | Provenance | Days | Count |
|---|
Projection row
This page runs a port of the fold on whole-number days. The projection row is the stored result of the fold, covered in the next section. The account, the days and the counts are fictional.
Decision 3
Store the answer once
Every request checks entitlement. Folding the ledger on every request is wasted work, so the writer stores the fold's result in a projection table with one row per account and product.
Login guards, banners and cron audiences read that row and nothing else.
Writers refresh it in their own transaction
A sale locks the account, appends its rows, folds the ledger again and writes the projection before it commits. The access check sees the sale in the same commit that recorded it.
Dates need a refresh with no writer
An expiry is not a write. A once-per-day check, attached to a per-account refresh the product already ran, folds again if nothing wrote the row today.
Side effects run on change only
Each staff member carries an access flag copied from the subscription. Flipping it means loading every staff member of the account. The refresh compares the new row with the old one and does that work only when the state changed.
When to clear the cache
The projection row is cached per account. The writer clears the cache only after it commits. Clearing it inside the transaction looks safer and is wrong.
- WriterAppends rows and writes the new projection. Not committed yet.
- WriterClears the cache.
- ReaderMisses the cache, reads the table, gets the old committed row and caches it.
- WriterCommits.
The cache holds the old row until it expires.
- WriterAppends rows and writes the new projection.
- WriterCommits.
- WriterClears the cache.
- ReaderMisses the cache, reads the table, gets the new row and caches it.
The next read caches the committed row.
The ledger stores facts. The fold computes state. One row caches the answer.
Since September 2026 every access check reads that row.