The row that never reached the comparison

You’re migrating order_status to a new status field that lives somewhere else: a new service’s database, a read model, a search index. During the cutover, every change is written twice, once to the old representation and once to the new one. Before switching readers over, you run the validation query: join the two sides on order ID, compare the values, count mismatches.

Zero mismatches. Ship it.

Except order O-17 was created while the writer crashed between its two writes. It exists in the old store and not in the new one. The validation query never compared it, because a join only returns rows that exist on both sides. The check didn’t say O-17 was fine. It never mentioned O-17 at all.

This piece is about that gap: why it happens, a small synthetic demo that reproduces it with a real crash, the check that catches it, and what a repair is allowed to do with the evidence it has.

Where this came from

This started as comments on other people’s writing, not as an incident of mine. Everything below is a fictional example.

In Part 3 of suprabhat kumar’s series on safely evolving a schema in production, the worked example migrates order_status to status through a dual-write phase followed by validation. I asked whether that validation could include a deliberately interrupted update:

A comparison over records where both fields are populated can miss a row that never reached that condition. The test would make the recovery rule explicit: either the representations commit together, or an incomplete update remains detectable and repairable before consumers treat it as complete.

That’s a proposed test, not a claim that his system has this bug. I haven’t seen his system.

Drake Pham’s article on schema evolution in real-time payment systems describes a rollout where a reconciliation path silently skipped events in a new format. The incident and its details are his report. My comment suggested checking completeness separately from parse success: give each emitted event an expected outcome and account for it as matched, rejected or still pending. That’s the same idea from the event side.

A third thread, on Zeinab Dashti’s piece about event schemas for long-lived contracts, is about something different: whether archived events can still be decoded once their producer is gone. It comes back near the end, as a separate gate.

None of these authors reviewed this piece.

The gap only exists between two commits

First, the trap I wanted to avoid. If both representations live in one database and you write them in one transaction, a crash before COMMIT rolls back both. There’s no half-write to find. PostgreSQL’s transaction tutorial describes exactly this all-or-nothing behavior, and SQLite gives the same guarantee.

So the demo includes that case as a control. Both tables in one SQLite file, one transaction, and the writer process exits with code 137 after both writes but before COMMIT. Afterward, O-17 was in neither table, and a restarted writer then wrote it to both. One caveat, which the independent review caught: the uncommitted pages never left SQLite’s in-memory cache, so the database file itself was never half-written and SQLite’s rollback journal had nothing to undo. The control shows that no half-write is visible after a crash. It doesn’t exercise the recovery machinery. If it had shown a half-write, the harness would have been wrong and nothing else in it could be trusted.

The real gap needs two commits: two databases, a database and a search index, a database and a message broker. Commit one succeeds, the process dies, commit two never happens. That’s the shape of most migrations that move data to a new home, and it’s what the rest of the demo uses.

Eight orders, one crash

The demo is deliberately small. Two SQLite files stand in for the old and new stores. Six orders, O-11 to O-16, start in both, already backfilled. Then four operations run through the dual writer: update O-12, insert O-17, insert O-18, update O-14.

The writer runs as a separate process. At a named point it calls os._exit(137), which ends the process without cleanup. That’s a process kill, not a power loss: the operating system’s file cache survives it. The harness then starts a fresh writer process, as a restart would, and inspects both stores from a third.

The writer’s resume rule is the ordinary one: an operation is done if the legacy store says it’s done. That marker is written in the same transaction as the legacy row, so it’s accurate. It’s just not the whole truth.

The code, fixtures, expected outcomes and results are published next to this article, and every file in that folder runs with the Python standard library.

Before any code existed, the fixtures, the expected outcome for each of seven scenarios and six written hypotheses were saved and hashed. All seven scenarios matched their frozen expectations on the first run. A harness that can only pass proves nothing, so seven unit tests check the harness itself. One feeds the comparator a deliberately wrong expectation and checks it’s rejected. One swaps the audit for a join-based version and checks that the frozen expectation then fails. Others check that every crash really was a child exit with code 137.

The pair check saw 7 rows and called them clean

Crash	Pair check	ID audit
None	8 of 8 equal	8 of 8 matched
Insert O-17	7 of 7 equal	O-17 missing
Update O-14	O-14 differs	O-14 differs

Source: results.json, fictional fixture of 8 orders. The pair check’s denominator is the rows it compared; the audit’s is all 8 expected IDs.

The middle row is the whole article. The pair check did its job perfectly on the rows it was given. It just wasn’t given O-17, and a count of 7 where 8 were expected doesn’t look alarming unless you’re comparing it with something.

The last row is the contrast. The same crash during an update left O-14 with its old value in the new store. The row existed on both sides, so the join found it and the values disagreed. A pair check catches a stale row. It can’t catch an absent one.

Count from the expected IDs, not from the join

The fix is boring, which is good. Start from the set of IDs that should exist, here every order_id in the legacy store, and classify each one:

• matched: present in the new store with an equal value
• mismatched: present with a different value
• missing: not present at all

In SQL, that’s a left join from the expected set instead of an inner join, with the missing rows counted instead of filtered out. In the demo, the audit reported 7 matched and O-17 missing.

Two cautions. The expected set has to come from somewhere authoritative. If you derive it from the new store, you’ve rebuilt the join. And at real scale you won’t diff every row on every run. You’ll compare counts and checksums per range, then drill into ranges that disagree. The principle is the same: the denominator comes from what should exist, not from what happened to arrive.

Drake’s article is about events, not rows, but the idea carries: an event that reaches a consumer and never gets any reconciliation outcome is the event-stream version of O-17.

Repair needs evidence written with the first commit

Finding O-17 is half the job. The other half is fixing it without guessing.

The demo’s treatment writer adds one thing: in the same transaction as the legacy row, it writes an intent, a record of the operation ID, order, status and version, marked not-yet-projected. Because it commits atomically with the legacy row, the intent exists if and only if commit one happened. After the projection commits, a third small transaction marks the intent projected. This is the shape of a transactional outbox. Debezium’s outbox event router is one production version of the idea for the database-plus-event case, though the relay differs: it reads outbox rows from the database’s change log rather than flipping a flag like this demo does.

The reconciler reads unprojected intents and decides what each one allows. The rules are short, and each one ran as a scenario:

• Repair. O-17’s intent was unprojected and the projection had no row. The reconciler applied it: 1 write. A second run made 0 writes, because run 1 had marked the intent projected. The projection also records applied operation IDs as a second guard, but this run didn’t exercise that path.
• Conflict. Before reconciling, a newer value for O-17 (cancelled, version 2) was written straight into the projection, standing in for a writer that had already cut over. The reconciler flagged a conflict and left the projection at cancelled, version 2. It didn’t overwrite a newer value with an older intent. The audit then showed O-17 as mismatched, which is correct: someone has to decide. The rule is deliberately blunt. It would also flag an older intent that a later operation had already superseded, and it re-reports the same conflict on every run because conflicts aren’t recorded anywhere.
• No evidence, no write. With the legacy store unavailable, the reconciler couldn’t read the intent. It reported the source unavailable and wrote nothing. O-17 stayed missing, and the audit still said so.

Schema history is a separate gate

The demo repairs from a row in a live database. Some repairs replay archived events instead, and that brings in a different dependency.

Zeinab’s article describes a stable envelope whose payload carries a schema identifier, resolved when the event is read. My reply asked what happens when you replay an old event after its producer has been retired: the bytes and the ID are still there, but can a clean consumer still find the schema the ID points to? Confluent’s Schema Registry docs cover backup and restore, which is relevant here even though they’re written for disaster recovery in general.

That gate has nothing to do with why O-17 went missing. It decides whether a replay-based repair can run at all. I didn’t build or test it here. It’s in the diagram as a dashed box so it doesn’t get confused with the things that did run.

What I didn’t check

• The single-transaction control didn’t exercise rollback. The uncommitted write stayed in memory, so the file was never half-written. A tiny page cache plus padding rows didn’t force SQLite to spill pages to disk either, and the demo stops there rather than tune SQLite internals.
• SQLite files, not real systems. Two SQLite files on one disk stand in for two databases. No network, no broker, no concurrent writers, no replication lag.
• One crash point per scenario, chosen in advance. Real crashes don’t pick a convenient line. Crashes between commit two and commit three (marking the intent projected) or mid-reconciler weren’t tested. The projection’s applied-ops table is meant to make those safe, but the demo never exercised that path.
• The expected set is the legacy store. That works here because the legacy store is the source of truth during the migration. If your source of truth is itself the thing being migrated, you need a different denominator.
• The audit only looks one way, and only at status. Starting from legacy IDs, it can’t see orphan rows that exist only in the projection, and it compares status but not version. A real audit should check both directions.
• The reconciler treats every read error as “source unavailable”. A locked database or a missing table would be reported the same way as a missing file.
• Exact-row audit at 8 rows. At production size you’d compare counts and range checksums first. None of that was measured.
• The schema-history gate didn’t run.
• Sources. suprabhat kumar’s article is now member-limited from an anonymous view. The reading of it here comes from a full read recorded before my comment, not a fresh one. Drake’s incident is his report, and I’ve used only its shape, not its details.

Seven checks before you trust a migration validation

1. Write down where each commit boundary is. If the two representations commit together, test that a crash leaves neither; if not, assume a half-write can happen.
2. Crash the writer on purpose between the commits, then restart it, and look at both stores from a separate process.
3. Count from the expected ID set. Report matched, mismatched and missing, with the denominator.
4. Fail the validation if compared rows don’t equal expected rows.
5. Record intent in the same transaction as the first write, so the evidence for a repair exists whenever the gap does.
6. Make repair idempotent, never overwrite a newer version, and write nothing when the record is unreadable.
7. If repair replays archived events, test that a clean environment can still resolve their schemas.

If you’re planning a migration like this and want a second pair of eyes on the validation, here’s how I work.

---

AI-assisted, human-reviewed.

How this was made: fixtures, expected outcomes and hypotheses were written and hashed before any code. The demo is Python standard library and SQLite, with real child-process crashes. Diagrams are drawn as SVG from the scenario results. The demo files are published next to this article.