Nine of Thirteen Corrections Were Wrong: The Fix That Came With a Proof It Couldn't Work

Nine of the thirteen corrections our migration code had ever written in production were wrong. We found that out by stopping the bug fixes and counting.

Some context. We were migrating short-term leave data — sick days, compassionate leave, the odd half-day — from a legacy processor to a new integration service. Same source system upstream, same consumers downstream, new table in the middle. The kind of migration you scope at a week.

Two days of it went into fixing bugs in a mechanism that could never have worked. This is how we finally noticed. Part 2 is what we replaced it with.

All identifiers are anonymised and the systems described generically. The numbers are real.

The setup

Two feeds write leave data:

• The legacy processor — running for years, polls the HR platform every 30 minutes during the day, writes one row per employee-day.
• The new service — polls every 15 minutes, 24/7, writes to a new table.

To cut over, we froze a snapshot of the legacy table, copied it into the new table tagged source = 'legacy', and let the new feed take over. The new service would gradually re-report the same absences as its rolling lookback window swept over them.

Which creates the obvious problem: the new feed writes an absence that is already in the table as a legacy row, and the balance double-counts.

Our answer was a step called heal. When the new feed wrote a row that looked like an existing legacy row, heal appended a compensating negative entry cancelling the legacy one out:

The balance stays correct, and over time every legacy row gets superseded by a properly-sourced one. The consumer never sees any of this — it reads SUM(amount) per employee-day. Remember that detail; it matters more than anything else in this post.

The catch — and we knew this at design time, we just didn't weigh it properly — is that the two feeds share no identifier. Legacy request IDs are day-level. The new report's are request-level, and one request can span five days. There is no join key.

So heal matched on the only columns both feeds had:

-- "Is this new-feed row a replacement for that legacy row?"
(employee_id, date, leave_type, unit, amount)

Match on value shape. It worked in testing. It worked in the first sync after the snapshot.

The parity check

Everything that follows came out of one query. Sum both tables per employee-day, full outer join, show me anything that differs:

WITH legacy AS (
  SELECT employee_id, date, SUM(amount::numeric(10,4)) AS total
  FROM leave_legacy
  WHERE date BETWEEN :from AND :to
  GROUP BY employee_id, date
),
ledger AS (
  SELECT employee_id, date, SUM(amount) AS total
  FROM leave_ledger
  WHERE date BETWEEN :from AND :to
  GROUP BY employee_id, date
)
SELECT COALESCE(l.employee_id, r.employee_id) AS employee_id,
       COALESCE(l.date, r.date)               AS date,
       COALESCE(l.total, 0)                   AS legacy_total,
       COALESCE(r.total, 0)                   AS ledger_total
FROM legacy l
FULL OUTER JOIN ledger r
  ON l.employee_id = r.employee_id AND l.date = r.date
WHERE ABS(COALESCE(r.total, 0) - COALESCE(l.total, 0)) > 0.0001
ORDER BY 1, 2;

Two things to note, because both come back later. The comparison key is (employee_id, date) — never the request ID, since the two feeds' IDs are at different grains and never match. And that > 0.0001 tolerance is not defensive padding: the legacy column is REAL, ours is NUMERIC, and Postgres returns sum(real) -> real, so an exact comparison reports float noise as drift. 0.83 reads back as 0.83000004.

That query returns rows. So you go and fix things.

Two days of whack-a-mole

Bug 1 — the superseded field. The HR platform had quietly started sending a new field for the leave type name, superseding the old one. Both were present during the transition. We were reading the old one. Type labels diverged between the feeds, so heal never matched, so nothing healed, so everything double-counted. Around 1,900 records' worth.

Bug 2 — timezone off-by-one. toIsoDate() read a Postgres DATE back and called .toISOString() on it. The driver builds that Date from local-time components, not UTC, so any process running ahead of UTC shifts the calendar day by one. Heal was comparing the 12th against the 13th and finding nothing.

Bug 3 — case sensitivity. The two feeds spelled some leave types with different casing. The match was case-sensitive.

Fixing 2 and 3 together took a test sync from 25 of 45 rows healing correctly to 472 of 473. That felt like winning. We added a regression query to the comparison script and moved on.

Bug 4 — the unbounded heal. The replacement test was an EXISTS: does a new-feed row with this value shape exist for this employee-day? If two legacy rows happened to share an identical shape on the same day and only one replacement existed, EXISTS was true for both, so heal cancelled both — moving the balance by (legacy_count − replacement_count) × amount.

The fix was to cap healing 1:1 per group: count the available replacements, count the ones already used by previous heals, rank the unhealed legacy rows, and only heal up to the difference.

WITH keys AS (…),
     legacy_rows AS (…),          -- + whether each is already healed
     group_used AS (…),           -- replacements consumed by previous runs
     replacement_counts AS (…),   -- replacements available in this group
     ranked_unhealed AS (…)       -- ROW_NUMBER() over the group
INSERT INTO leave_ledger (…)
SELECT … FROM ranked_unhealed
WHERE rn <= available_replacements - used_replacements

It worked, and it was idempotent, and it turned a twenty-line statement into five CTEs. At the time that read as rigour. It was actually the mechanism telling us something: a matching rule that needs rank-and-cap arithmetic to stay correct is a matching rule that doesn't have enough information to work with.

Bug 5 — the one that broke it. A brand-new leave request can have the exact same value shape as an old legacy booking that has already been cancelled by a correction. Heal saw the shape match, decided the new request was a duplicate, and cancelled the legacy row a second time. Silently deflating a real person's balance.

We fixed that one properly — or thought we did. The insight was that the signal separating the two cases is temporal, and the source system already sends it: the legacy rows are a frozen copy taken at one instant, so they cannot contain an event that hadn't been initiated by then. A new row initiated after its candidate legacy row was snapshotted is provably a different event. We added an initiated timestamp to the schema, threaded it through the transformer, and gated heal on causality rather than coincidence. Three regression tests, one replaying a real three-sync sequence against production IDs.

It was, honestly, a nice fix. It was also the moment the whole approach fell over.

The sentence that ended it

While writing the commit message for bug 5, we wrote this to explain why no simpler rule would do:

No rule over those five columns can fix this — the identical shape also occurs when the heal
is correct, so any ordering preference gets one of the two cases wrong.

Read that again with fresh eyes. It isn't a description of a bug. It's a statement that the mechanism cannot be made correct. (employee_id, date, leave_type, unit, amount) takes the same value when two rows are the same underlying event and when they are unrelated events that coincide. Two 8-hour sick days for the same person on the same date are indistinguishable in those columns whether one is a duplicate of the other or not.

Not undecidable in the computer-science sense — this is more mundane and more annoying than a halting problem. The information needed to answer the question is simply not present in the data, and no amount of cleverness over five columns conjures it. Every fix we'd shipped was a better guess at a question that has no answer.

Five root causes in two days, all living inside the same matching rule, and the fifth one's fix shipped with a written argument for why the rule can't be made sound. That's not a code quality problem. That's the design talking.

So we stopped fixing and started measuring.

Measuring instead of fixing

Three queries against production data:

Three things fall straight out of that.

Heal was net-negative in production. Thirteen rows, out of roughly 1,300 the new feed had written. Nine wrong, four right, and all four right ones came from one sync immediately after the snapshot. That asymmetry is structural, not bad luck: genuine overlap is a spent cutover artifact that decays to nothing, while coincidental value matches accrue forever, with every new request. The mechanism was guaranteed to get worse over time.

Corrections are deltas, not restatements. 238 of 244 overlapping employee-days reconcile exactly if you just let both rows stand and add them up. The HR platform doesn't restate a day's total; it emits signed adjustments that sum correctly. We had built compensation for a problem the source data already solved.

The rows were already immutable. Zero of 1,299 rows had ever been updated across syncs. The 8.8 ms maximum gap between create and update means the only DO UPDATE that ever fired did so within a single batch — never across syncs. Our upsert's update branch existed purely to produce bugs.

The bill for all this was nine hours of leave, deflated across two people's balances, deleted by hand once we understood them. Small, and it stayed small for an unsatisfying reason: the new table had no downstream readers yet, because the later phases of the migration hadn't run. The sync does publish each changed balance as an event, so the wrong numbers did leave the service — but the tables the wider business reads were still the legacy ones.

Nothing alerted, either. Every one of those 13 rows was written by a sync that reported success. The only reason we know the number at all is that we'd built the parity harness.

Which brings us to those two unexplained employee-days.

The twist

One request from the HR platform, business process "Absence Correction", carried this:

entries:            "13/08 - -8 Hours; 13/08 - 8 Hours;
                     14/08 -  8 Hours; 14/08 - -8 Hours"
total_units_hours:  "0"

Four entries, two dates, netting zero on both — a cancel-and-rebook, which is exactly what a correction looks like.

Our table's primary key was (request_id, date). Our transformer emitted one row per entry. So all four rows collided on two keys, and the sequential upsert kept the last write per date:

The balance we published was 16 hours on the 13th and 0 on the 14th. The truth was 8 and 8.

No error. No warning. No failed row. And look at the last line of the payload: the source system sent us total_units_hours: "0", a checksum for the entire request, flatly contradicting what we had stored. We had never checked it.

The design document for the table said, in as many words, that corrections "are modelled as separate requests, not as mutations of the original row", and therefore (request_id, date) identifies exactly one value. That assumption was wrong. A single request can carry multiple entries for the same date, and any correction that cancels and re-books on the same day hits it.

Worse, the bug destroys its own evidence. An overwritten row leaves nothing behind saying it was overwritten, so you cannot query the table to find out how often this happened. We still don't know.

Why the tests were green

They were green because they were honest tests of the wrong thing.

The transformer had unit tests. The repository had integration tests against real Postgres in a container, not a mock. Both passed throughout. Their fixtures were built from payloads we had actually seen — one entry per date, clean value shapes, the happy path — because that is what fixtures are: a record of what you already know.

The four-entry correction wasn't in the fixture set because we didn't know that shape existed. Every bug in this post lived in the correction path, which is a few percent of volume, produces no errors when it's wrong, and is the part that matters most.

So: the reconciliation step could not be made correct, and the primary key it reconciled against was not unique. Two findings, one conclusion — this wasn't a patch job. Part 2 covers what we built instead.

What we'd tell ourselves two days earlier

When a fix requires proving no simpler rule works, the design is the bug. We wrote a paragraph explaining why value-shape matching can't be made correct, and then shipped a sixth fix to it. That paragraph was the finding.

Watch the shape of your fixes, not just their correctness. Bug 4's fix was right, tested and idempotent, and it took the query from twenty lines to five CTEs. Rising complexity in the same spot is the cheapest signal you get that the model underneath is wrong, and it arrives before the proof does.

Count the mechanism's production output before you fix it again. "13 rows ever written, 9 of them wrong" took one query and ended a two-day argument. Any mechanism you're repeatedly patching can be measured, and the measurement is usually cheaper than the next fix.

Ask what happens if you delete it. "238 of 244 overlapping employee-days reconcile with no compensation at all" was the single most valuable number we produced. The counterfactual query — what would this data look like if the code didn't exist — is badly underused.

Trust the checksums your source hands you. The payload contained a total that contradicted what we stored. Free validation, ignored for months.

Silent data loss doesn't leave evidence, so build the harness. Nothing here threw an exception. Every wrong row was written by a green sync. The parity query is the only reason any of it is a story rather than a slowly-drifting table.

Part 2: the append-only ledger that replaced it, the one-line change that made the whole reconciliation problem disappear, and the cutover run that lost 235 employee-days.