Data Models
Event Tables vs Status Columns: Modeling Record History
Learn when to replace an overwritten status column with an event table, so past states stay answerable and summaries never drift from the facts.
The Row That Answers Two Questions
The most common modeling mistake is a single row that tries to answer two questions: what is this thing, and what has happened to it.
Take a loans table with a status column. On Monday it says out. On Wednesday someone renews the loan and the column says renewed. On Thursday the drill comes back and it says returned. Every update is correct, and every update destroyed the answer before it.
Now a member disputes a late fee: "I renewed it on Wednesday." The row cannot say whether they did.
Three Rules for Record Shape
1. A record has an identity. Events against it are separate records.
The identity keeps its identifier, its meaning and its links. Each event (checked out, renewed, returned) is its own row, with its own time and its own author. The identity's summary, such as its current status, is a rollup over its events. It is never a field that the latest event overwrites.
2. Two lifecycles with two owners are two state machines.
A tool has a lending state (available, out) that volunteers change at the desk. It also has a maintenance state (in service, awaiting repair, retired) that the workshop lead changes. Put both in one status column and each owner's change can silently erase the other's. Model them as two fields, each with its own allowed transitions.
3. Store a fact once. Compute everything else.
If loans.status and the latest loan_events.kind both exist, one day they will disagree, and nothing will tell you which is right. Delete one copy and compute it when it is read. Two stored copies of the same fact are a staleness bug that has not happened yet.
When to Use Events, and When Not To
Events are not free. Use them where history is actually asked for.
| Use identity + events when | Keep a plain column when |
|---|---|
| Someone will ask what it used to be (disputes, audits, who changed this) | Nobody will ever ask (fixing a typo in a display name) |
| More than one person or process changes it | One owner changes it for one reason |
| It drives money, access or compliance | It is cosmetic |
Changing a Live Shape Safely: Expand, Switch, Contract
Sometimes you discover the wrong shape after the table holds real data. Fix it in three separate steps:
- Expand. Add the new structure beside the old one. Create
loan_eventsand backfill it from what you have. - Switch. Move every reader and every writer to the new structure. Verify that they have all moved.
- Contract. Remove the old column as a separate, later change, once nothing uses it.
Never do all three in one migration. If the switch turns out to be wrong, the contract step has already deleted your way back.
Key Takeaways
- A row that answers "what is it" and "what happened to it" has to overwrite one of the answers.
- Keep the identity stable. Record each event as its own row. Compute summaries from the events.
- Two lifecycles with two owners are two fields, not one.
- Store each fact once. A second stored copy is a future disagreement.
- Change a live shape by expanding, switching and then contracting, as separate steps.
Example
-- Status is COMPUTED from events, never stored on the loan.
-- One row per loan: its most recent event.
create view public.loan_status as
select distinct on (e.loan_id)
e.loan_id,
e.kind as status,
e.occurred_at as since
from public.loan_events e
order by e.loan_id, e.occurred_at desc;
-- "What was true on Wednesday?" is now a query, not an argument.
select kind, occurred_at
from public.loan_events
where loan_id = (select id from public.loans where loan_number = 'L-104')
and occurred_at < timestamptz '2026-09-17 00:00+00'
order by occurred_at desc
limit 1;-- Status is COMPUTED from events, never stored on the loan.
-- One row per loan: its most recent event.
create view public.loan_status as
select distinct on (e.loan_id)
e.loan_id,
e.kind as status,
e.occurred_at as since
from public.loan_events e
order by e.loan_id, e.occurred_at desc;
-- "What was true on Wednesday?" is now a query, not an argument.
select kind, occurred_at
from public.loan_events
where loan_id = (select id from public.loans where loan_number = 'L-104')
and occurred_at < timestamptz '2026-09-17 00:00+00'
order by occurred_at desc
limit 1;Where You'll See This in the Real World
Accounting has worked this way for centuries: a ledger never erases an entry, and it posts a correcting entry instead. Payment processors, order systems and issue trackers all keep an event history behind the status they display, because "who changed this, and when?" is the first question in every dispute. Two status columns that drift apart are a sign that one record is being asked to answer two questions.