Bitemporal Data: When Your Database Needs to Remember What It Used to Believe
We maintain a pipeline that ingests healthcare code sets — diagnosis codes, procedure codes, billing groupers — from their official government and standards-body sources, on their official update cadences: some annual, some quarterly, some monthly.
The naive model is a lookup table: code, description, valid-from, valid-to. Refresh on schedule. Done.
That model is wrong in a subtle way that eventually corrupts your answers, and the reason it's wrong is the most interesting data-modeling lesson we know: the publishers revise the past.
Two kinds of time
A code set update doesn't just add next year's codes. Sometimes it corrects history — a code's effective date is amended, a retroactive fix changes what was billable last quarter. The world's timeline itself gets edited.
So a correct system has to track two independent timelines:
- Valid time — when a fact was true in the world. Code X meant Y from January to June.
- Transaction time — when your system believed it. We learned this in March; before March we believed something else.
Track both and you can answer the question that audits and reprocessing actually require: "what did we believe on date D about date V?" A claim processed in February was processed against February's knowledge. If April's publication retroactively changed January's codes, the February decision was still correct at the time — and your database can prove it, because the old belief isn't overwritten. It's closed out with a transaction-time end and preserved.
One timeline gives you a lookup table. Two timelines give you an audit trail of your own knowledge.
Where it bites
The theory is elegant. Production adds teeth:
Retroactive slices collide. When a source publishes a historical backfill, you're inserting valid-time ranges underneath existing current beliefs. Our worst bug in this system was a comparator that couldn't tell "the current belief about this period" from "a historical slice of it" — revising history would occasionally collapse the wrong version and violate the no-overlap constraint. The fix was conceptually one line — current beliefs are the ones whose validity extends to infinity; compare within, never across — but finding it required actually holding the two-timeline model in your head.
Sources drift. Government file layouts are not an API contract. Fiscal-year publications arrive with renamed files, date-stamped suffixes, restructured directories. An ingest pipeline that matched last year's layout will silently find nothing — which is why every ingest ends with sanity gates: expected system counts, freshness canaries per source cadence, and hard failures when a "monthly" source hasn't produced anything new in ninety days. Silence is an error state; we've written about that pattern before.
Corrections must be new facts. The iron rule of the whole design: never UPDATE a belief in place. Close the old row in transaction time, insert the new one. The moment someone patches history directly, your audit trail has a hole exactly where an auditor will look.
Do you need this?
More often than people think. Bitemporality earns its complexity whenever three things are true: facts can be revised retroactively, decisions were made against old versions of the facts, and someone may later demand to know why a decision was made. Healthcare billing, yes — but also pricing, tax rules, compliance policies, insurance rates, sports odds, even feature flags in a regulated product.
If your domain has all three, a single-timeline table is a slow-motion data-loss incident: every refresh destroys the evidence of what you used to believe. You won't notice until the day someone asks "why did the system do that in February?" — and by then the answer is gone.
Model both timelines from day one. It's one extra pair of columns and a discipline. The alternative is explaining to an auditor that your database has amnesia.