My Derived Field Went Stale Three Days After I Backfilled It

That morning I ran a scan over my research index. One command, JSON output with the status of every topic. One of the fields is next_review, the date that says when a research archive is worth reopening. Three days earlier I had backfilled that field by hand for 13 old topics. The data looked tidy. I liked it.

Then I noticed the topic I had researched most recently had an empty next_review. The backfill was already partially stale, because one new file had entered the index.

Backfill Is Reporting, Not Fixing

Two commits earlier I was convinced a one-time backfill was the fix. Commit 3b9d8fb set next_review = research date + 90 days for 13 legacy topics. It worked, technically. Then a single new research file landed and part of those 27 lines went stale again.

That was the moment I stopped blaming the data and started blaming the decision to store it. next_review is a derived field: you can compute it from last_updated plus 90 days. Storing the result of a calculation that changes just adds a new class of housework. Every time the source changes, the stored copy needs another sync. Derive it at read time and that whole class of work disappears.

The actual fix is commit 93b12a2. The scan() function in my index script now derives next_review itself whenever the field is empty. Manual values survive, because the logic only fills in blanks. The selftest covers both paths: a manual field is never overwritten, an empty field gets filled.

Databases Made This Call Before I Did

I didn't invent this pattern. SQLite has generated columns, columns whose value is a function of other columns in the same row [1]. You can read them but you cannot write them directly. They come in two flavors: VIRTUAL, computed when read, and STORED, computed when the row is written. STORED takes up space in the database file, VIRTUAL spends a few CPU cycles on each read.

PostgreSQL describes a generated column as what a column is to a table, relative to what a view is [2]. The virtual kind behaves like a plain view, the stored kind like a materialized view. And a plain PostgreSQL view never stores its result: the query runs every time the view is referenced [3]. Vendors settled this argument long ago: derive on read, store only with a reason.

One neighbor concept needs a distinction: the column default. A default is evaluated once, at insert time, and it cannot reference other columns [2]. So a default is not a derived field. It is an initial value, not a mirror of another column that has to stay in sync.

When Storing Still Makes Sense

Two situations justify storage, and both are honest about their trade-offs.

First, manual overrides. In my research index, a few topics are deliberately flagged for review sooner or later than the 90-day formula. That is why the only-when-empty condition matters: the formula fills the gaps, the human decision overrides the formula. That is the model I shipped.

Second, read-heavy performance. A VIRTUAL generated column gets recomputed on every read [1]. For an expensive expression under heavy read traffic, that burns CPU for nothing. SQLite still lets generated columns participate in indexes: one using a STORED column is just an ordinary index, one built on VIRTUAL columns becomes an expression index. So if reads are frequent and the expression is expensive, storing the result in a STORED column and indexing it is a legitimate engineering call, not a violation of the pattern.

What I avoid is the gray zone: storing a derived field with no override and no performance reason, just because it looks easier that afternoon. That is where the sync debt waits.

So next time you spot a field that is always computable from other columns, hold off before adding a storage column for it. Write one derive function where the data gets read. Keep storage for values a human deliberately set, or for read loads that genuinely hurt.

Sources

1. SQLite, "Generated Columns" — sqlite.org, accessed 2026-09-17
2. PostgreSQL, "Documentation: 5.4. Generated Columns" — postgresql.org, accessed 2026-09-17
3. PostgreSQL, "Documentation: CREATE VIEW" — postgresql.org, accessed 2026-09-17