change timestamps

a timestamp column that downstream systems read as "when this changed" is a contract, and the two obvious implementations differ in a way that only shows up under load:

• updated_at = now() on every write — means when we last wrote
• updated_at = now() only when the content hash differs — means when the content last changed

writing on every write is the default in most ORMs, upsert helpers, and hand-rolled indexers, because it is one fewer comparison. it is also wrong for every consumer that treats the column as a change marker.

the general thesis: one value carries one meaning. when a second consumer needs a second meaning, it gets a second value — never a reinterpretation of the first. the two meanings coincide often enough to pass review and diverge under exactly the load conditions that matter. the same failure recurs at the cursor layer in watermark-semantics.

who depends on the distinction

anything that pins the column and assumes the set below the pin is stable:

• incremental sync and CDC — "give me everything since T" returns rows whose content is identical to what the consumer already has
• snapshot gates — a build that copies rows below a watermark and then verifies the copied count against the source count
• cache invalidation and ETags — a re-write with identical bytes invalidates every downstream copy
• conflict resolution — last-write-wins picks a winner on write recency rather than change recency

the sync and cache cases waste work. the gate case fails outright.

how the gate fails

a snapshot builder's count gate compares rows copied against rows the source reports below the pinned watermark, with a small tolerance for deletions during the build. the gate is correct only if the set of rows below the watermark does not change while the build runs.

with indexed_at = now() on every write, any re-write of existing content moves a row from below the watermark to above it. platforms that re-publish full archives make this routine — one account re-writing 4,500 identical documents in minutes drifted the two counts by roughly 450 rows per build, about eight times the tolerance. the gate could not pass while writes continued.

the fix is the column's meaning, not the tolerance. widening the tolerance makes the gate stop measuring correctness and start measuring write traffic — and a gate that cannot fail is not a gate. changing the indexer to preserve the old timestamp when the content hash is unchanged makes the gate's assumption true, and the original tolerance correct again.

rules

• pin a column that changes only when the data changes. if a consumer pins it, the write path owes it that property.
• when a gate is noisy, suspect the pinned column before the threshold. loosening a threshold to accommodate noise converts a correctness check into a formality.
• carry both when both are needed. updated_at and content_changed_at are different facts and both have consumers — operational debugging wants write recency, sync and gates want change recency. deriving one from the other after the fact is not possible.
• content hashing is the cheap enforcement. the indexer already has the bytes in hand; a hash comparison before write is far less than the write it avoids.

related

• serving-from-snapshots — the gate this breaks
• watermark-semantics — one value, one meaning at the cursor layer

sources

• pub-search — docs/builder-offbox-plan.md; the indexed_at redefinition that made the snapshot count gate passable (july 2026)