Versioned source data — read one version, never sum them

Our commission data arrives in a daily feed from the agency’s legacy system, and we keep it in a replica that is append-only. A record is identified by its identifier plus a version, and a new delivery can append a new version of a record that already exists. Old versions stay, on purpose: “what did this say in July” is a legitimate question, and overwriting the past destroys the ability to answer it.

That design creates one rule that every consumer has to follow: “what this record says now” is the highest version — never every row.

The failure mode that hides for months

For a long time the source delivered exactly one row per record. Every query that summed or counted across the replica was therefore correct, not because it filtered correctly, but because filtering would have been a no-op. Version semantics existed in two places — a status query and the comments on the reconciliation SQL — and in no mechanism that reached the business code.

Then one delivery appended second versions of a few hundred records, and every query that aggregated across the table counted each of those records twice.

The damage was selective, which is what made it interesting. Agents whose records had not been re-versioned saw unchanged numbers. A handful of agents saw doubled totals. Nothing failed, nothing logged an error, and every automated check stayed green:

• The per-dataset content hashes verified the bytes that were written, not the semantics that were read.
• The replay check proved the import was idempotent — it added no duplicate rows. Idempotent writes say nothing about duplicate reads.
• The control reconciliation passed, because the reconciliation query itself already read only the current version. The verifier was stricter than the code it was verifying.

That last point is the durable lesson. “Both readings used to give the same answer” is the most dangerous state a data rule can be in: the bug does not depend on any code change, only on whether the next delivery happens to re-version something.

The fix, and the guard that matters more

The predicate is one line: a row is current when no row with the same business identifier exists at a higher version. We put it in a single shared module so there is one definition, and wired it into every consumer on the money path.

The more valuable part is what we added afterwards. A source-level test now asserts that the number of queries reading the commission table equals the number of queries carrying the version predicate. Add a new query and forget the predicate, and the build fails.

Documentation did not prevent this bug — the rule was already written down in two places. A test that fails loudly is the only mechanism that reaches a developer who has not read those two places.

The same bug outside the money path

Version mistakes are not only a money problem. Counting policies without the predicate makes a single policy look like two candidates for the same role. Our mapping logic then concludes the policy has no single answer and quietly declines to auto-map it — fewer automatic mappings, no error, no doubled total to notice.

Every dataset in the replica that can hold multiple versions needs the same discipline. Today the guard covers the money path; the honest statement is that it covers the money path and the mapping path, and that the remaining datasets need the same treatment the day a consumer touches them.

What we would tell another team

• If your source is versioned, decide one way to read it and put it somewhere shared, not in each query.
• Treat the day a delivery changes shape — one version per record to many — as a scheduled upgrade with an audit, not as a surprise.
• Do not trust an idempotency check to prove read correctness. Replay proves writes do not duplicate; it cannot prove reads do not.
• After every delivery, read a fixed sample of headline numbers back and compare them against a second, independent path. Automation verifies the transfer; only read-back verifies the meaning.