Two Timestamps Beat a 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’s reversible. Pulling a record isn’t destroying its “published” state; it’s stamping pulled_at. Change your mind and re-publish, and that’s a new published_at. The transitions are additive — you’re appending to a tiny event log spread across a row, never clobbering it — so a record can go out, come back, and go out again without losing a single step of where it’s been. This is just event sourcing worn lightly: keep what happened, derive the current picture from it.

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 four timestamps is more to write and read than a single column, and it asks every consumer to understand the derivation. And if the lifecycle is genuinely complex — a dozen states, cycles, transitions that carry their own metadata — a widening row of *_at columns gets unwieldy, and you should reach for a real 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 status column is a bet that no one will ever ask about the past. It’s a cheap, comfortable bet, and you lose it the first quarter-end that someone does. Two timestamps cost a little more up front and answer the thing a status column can’t answer at any price: not just what state a record is in, but when — and, quietly, what it was before you changed your mind.