why your migration reconciliation script agreed with the bug

A backfill copied a few thousand message threads from a legacy table into a new one. It had a reconciliation step that compared the two sides and reported parity. It reported 100% while individual rows were wrong, and it kept reporting 100% for as long as the bug existed, because the check and the bug shared the same blind spot. Three defects, each small, each with a rule worth keeping.

the denormalized column never converged

Each thread carries a denormalized “last message at” column for sorting. The backfill computed it by reading MAX(created_at) from the database into JavaScript and writing it back.

Postgres timestamptz keeps microseconds. A JavaScript Date keeps milliseconds. So the value written back was always a fraction behind the true maximum, the comparison on the next run saw a difference, and the advance re-fired every run without ever converging. A no-op re-run was indistinguishable from a run with real work.

Two smaller problems hid inside the same code. The advance was monotonic: it only moved the column forward. When a later pass moved a thread’s opening message backwards in time, the column pointed at an instant no message occupied. The read-then-write pair also had a window in which a concurrently committed message was missed.

The replacement is one SQL statement:

UPDATE thread t
SET last_message_at = sub.max_at
FROM (
  SELECT thread_id, MAX(created_at) AS max_at
  FROM message GROUP BY thread_id
) sub
WHERE sub.thread_id = t.id
  AND t.last_message_at IS DISTINCT FROM sub.max_at;

Exact, because it never leaves the database’s precision. Self-healing in both directions, because it sets rather than advances. Safe under concurrency, because under READ COMMITTED a blocked UPDATE re-evaluates its predicate and subquery against the new row version when the lock clears, so a message committed in the window is picked up rather than overwritten.

One consequence, accepted deliberately: raw SQL bypasses the ORM’s client-side updatedAt handling, so a denorm repair no longer bumps the thread’s modified timestamp. Nothing in the repo read that column, and a three-thousand-row repair should not look like three thousand edits to anything watching it.

the parity check compared what neither side held

The reconciliation compared row counts and MAX(created_at) between the legacy and new tables. Counts cannot see a per-row divergence. The max comparison went through toISOString(), which truncates to milliseconds exactly as the backfill’s write did.

So the first pass wrote a legacy 10:00:00.329054 back as .329, and every JavaScript-side check compared .329 against .329 and passed. Both sides agreed on a value neither of them held.

The fix is a per-row join in SQL, comparing every field at full precision:

SELECT COUNT(*) AS mismatches
FROM message m
JOIN legacy_message l ON l.id = m.legacy_id
WHERE l.created_at IS DISTINCT FROM m.created_at
   OR l.body       IS DISTINCT FROM m.body
   OR l.author_id  IS DISTINCT FROM m.author_id
   OR l.thread_id  IS DISTINCT FROM m.thread_id;

That count gates the parity result. The join is on a unique column, so a full check is one indexed join per migrated row. It ran in under a second on 8,470 rows, which is why sampling was rejected as unnecessary.

The rule generalizes: for any copy migration, the gate belongs in the database, comparing rows. Comparing in application code after reading both sides was considered and rejected for the reason above. The truncation is symmetric, so the comparison passes regardless.

the negation that dropped rows from both sides

One pass needed “every message that is not the thread opener”. The opener is a system message with a specific event type, and the event-type column is nullable, set only on system messages. The natural expression:

WHERE NOT (kind = 'SYSTEM' AND event_type = 'STARTED')

For a system row whose event type is NULL, the conjunction evaluates to NULL, and NOT NULL is NULL, which a WHERE treats as false. Those rows match neither the predicate nor its negation. They vanish from the comparison entirely.

The staging snapshot carried 243 such rows across 69 threads. On one tenant the NOT form reported zero misplaced openers where the corrected form reported 28, and the repair pass declined to fix any of them because, as far as it could see, there was nothing to fix.

Two correct forms. In raw SQL, IS NOT TRUE instead of NOT, since NULL IS NOT TRUE is true. Through the ORM, enumerate the complement explicitly: an OR over the concrete enum values plus an IS NULL arm. The rule applies to any predicate that negates a conjunction touching a nullable column, which in a typical schema includes every deleted_at.

The better long-term fix is a check constraint that forbids a system message with a null event type, so the class of row cannot exist. It could not be added while 243 violating rows existed, and those rows needed their own investigation first. They were cloned at exact one-hour offsets with identical milliseconds, which is the signature of a performance seed that omitted the column.

what the three have in common

Each defect was invisible to the check that should have caught it, for the same reason the defect existed. A reconciliation step is only worth its runtime if it uses a different mechanism from the thing it is checking, and the most different mechanism available to a migration is the database itself.