The NULL timestamp that broke a TTL sweep and a funnel metric at the same time

A row in our databases registry has a last_queried_at column. Two unrelated systems read it: a daily sweep that evicts anonymous DBs whose last_queried_at is older than 90 days, and a funnel metric that counts "DBs that have ever returned an answer." Both quietly broke for the same reason, and the bug is worth sharing because it's a whole class of mistake, not a one-off.

We added the column in a migration that backfilled existing rows (UPDATE … SET last_queried_at = updated_at WHERE last_queried_at IS NULL) — textbook. What we forgot: the INSERT on the create path never set the column. So every row created after the migration was NULL.

Now watch both readers fail, differently:

• The sweep silently keeps everything. WHERE last_queried_at < :cutoff looks like it evicts old rows. But in SQL, NULL < anything is NULL, which is not TRUE, so a NULL row never matches a < predicate. The age-based eviction became a no-op for every new row. No error, no log — the table just grows.
• The metric silently reads zero. "DBs that returned an answer" was COUNT(*) WHERE last_queried_at IS NOT NULL. Every new row is NULL, so the metric is pinned at 0 regardless of what users actually did. We nearly shipped a "fix" for a conversion problem that didn't exist — the instrument was broken, not the funnel.

Three takeaways

1. A backfill is not a default. If a column needs a value, set it at write time (a DEFAULT, or in every INSERT). A one-time backfill fixes the past and nothing else.
2. NULL is not "old" or "zero" — it's "unknown," and it poisons comparisons. Any < / > / != against a nullable column has a third outcome you have to design for. COALESCE at the read, or forbid the NULL.
3. Before "fixing" a metric that reads 0, prove the instrument can ever read non-zero. Ours structurally couldn't.

(Context: this was in nlqdb, a service that turns plain-English HTML components into SQL — the anonymous-DB sweep is how we keep the free tier's storage bounded. The fix was two lines: seed the column at create, re-run the backfill once.)