Timestamps Beat a Mutable Status Column

Someone asked which records had been live on the last day of the quarter. Reasonable question — finance wanted to reconcile against what the reports had actually shown that day. And I couldn’t answer it.

The table had a status column: draft, approved, published, pulled. It held exactly one value per record — today’s. Every time a record changed state, the pipeline ran an UPDATE and wrote the new value over the old one. By the time the question arrived, the column knew what everything was now and had thrown away, permanently and on purpose, what anything had been then.

A status column is a cached conclusion

Here’s the thing I hadn’t looked at squarely: published was never a fact. It was a conclusion. A record is “published” because it was approved, then published, and hasn’t been pulled since. That’s three events and their order, compressed into one word. Storing only the word throws away every fact that produced it — and because it lives in a single cell, each transition overwrites the last. The status column isn’t a record of state. It’s a derived value, cached in the most lossy way available: by destroying its own inputs on every write.

Which means it can answer precisely one question — what is this now? — and actively prevents you from answering the two that turn out to matter more: what was it then, and how did it get here.

Store the events, derive the state

The fix is to stop storing the state and start storing the events that cause it. Instead of one mutable status, a few nullable, write-once timestamps:

approved_at
published_at
pulled_at

You never overwrite these; you stamp them. And the current state stops being a stored value and becomes a computed one — a CASE over which stamps are set and in what order:

CASE
  WHEN pulled_at    IS NOT NULL THEN 'pulled'
  WHEN published_at IS NOT NULL THEN 'published'
  WHEN approved_at  IS NOT NULL THEN 'approved'
  ELSE 'draft'
END AS status

You compute published at read time instead of asserting it at write time. That one move — derive, don’t store — is the whole post, and it pays out three ways.

It preserves a one-way lifecycle. Pulling a record does not destroy when it was first published; it stamps pulled_at. For states that advance once in order, the timestamps form a compact history from which current state can be derived.

It answers “as of when.” The quarter-end question that had no answer becomes a WHERE clause:

WHERE published_at <= @asof
  AND (pulled_at IS NULL OR pulled_at > @asof)

Was this live on that date? Look at whether it had been published by then and not yet pulled. Point-in-time reconstruction stops being a special project and becomes an ordinary filter.

It carries its own audit. Put approved_by next to approved_at and the who-and-when is in the row itself — no separate history table to populate and keep in sync, no trigger that someone will forget to maintain.

The same mistake, again

This is the sibling of a bug I’ve written about before: a column that should be derived at read time gets frozen at write time, and the freezing erases history. There it was a date dimension rewriting last quarter’s numbers; here it’s a status column forgetting last quarter’s states. Same shape — a value that ought to be computed from more durable facts gets cached as if it were a fact itself, and the cache is lossy in exactly the direction you’ll later need.

When the trade isn’t worth it

It isn’t free. A CASE over three timestamps is more to write and read than a single column, and it asks every consumer to understand the derivation. If the lifecycle can repeat—publish, pull, and republish—or transitions carry their own metadata, a widening row of *_at columns is not enough. Updating published_at would erase the first publication, so use an event table with one row per transition instead.

The heuristic I settled on: as long as the states are a small, mostly-ordered set, and anyone ever asks “what was true when?”, the timestamps win. The one thing that should make the decision for you is that point-in-time question. The first time it’s asked and the status column can’t answer it — at any price, because the data is simply gone — you’ve already paid more than the CASE expression ever would have cost.

A mutable status column cannot reconstruct the past. For a short, one-way lifecycle, write-once timestamps can. For anything cyclical, use an event table rather than pretending a handful of timestamp columns is a complete history.