Replace the Heuristic With a Boundary: Rebuilding a Leave Table as an Append-Only Ledger

We deleted the reconciliation step from a data migration and replaced it with a single timestamp. Nothing overlaps, so nothing needs matching. That's the ending. Here's how we got there - and the short version of part 1, for anyone arriving without it: We were moving short-term leave data from a legacy processor to a new service. To avoid double-counting during the overlap, a step called heal matched a new row against a legacy-seeded one on value shape - (employee_id, date, leave_type, unit, amount) , the only columns the two feeds shared - and appended a compensating negative entry. Two days of bug fixing later we measured it: 9 of the 13 compensating rows it had ever written in production were wrong, and 238 of 244 overlapping employee-days reconciled exactly with no compensation at all. Separately, the table's primary key (request_id, date) turned out not to be unique - one correction request can carry four entries across two dates, and our upsert silently published 16 hours where the truth was 8. Two problems: rows that shouldn't be matched, and rows that shouldn't collide. | Before | After | | |---|---|---| | Grain | one row per (request, date) | one row per entry | | Key | (request_id, date) | (request_id, date, amount, seq) | | Conflict | DO UPDATE … WHERE IS DISTINCT FROM | DO NOTHING | | Amount type | REAL | NUMERIC(10,4) | | Overlap handling | match on value shape, compensate | a cutover boundary; no matching at all | Start from the read path The thing we should have looked at first is what the consumer actually asks for: SELECT employee_id, date, leave_type, unit, SUM(amount) AS amount FROM leave_ledger WHERE (employee_id, date) IN (SELECT * FROM unnest($1::int[], $2::date[])) GROUP BY employee_id, date, leave_type, unit That is a ledger read. It sums signed rows and never cares how many there are. It had looked like that from day one. Every problem in part 1 came from the write path underneath it being a mutable table pretending otherwise - and from heal, which existed only to keep that mutable table's arithmetic straight. Make the write path agree with the read path and both problems dissolve. One row per entry, keyed on the amount, never updated INSERT INTO leave_ledger (request_id, date, employee_id, leave_type, unit, amount, seq, source) VALUES ($1, $2, $3, $4, $5, $6, $7, 'new-feed') ON CONFLICT (request_id, date, amount, seq) DO NOTHING RETURNING * Three deliberate choices in five lines. DO NOTHING , not DO UPDATE . The rolling lookback deliberately re-sends the same request across consecutive syncs, and the transformer derives the same key set from the same payload every time, so a re-send is an exact no-op. Rows are never mutated. This isn't purism - part 1's measurement found that 0 of 1,299 rows had ever been updated across syncs anyway. The update branch existed only to produce the 16h/0h bug. amount is in the key. Here is the correction from part 1, stored under the new key: | entry | date | amount | seq | | |---|---|---|---|---| | 1 | 13/08 | −8 | 1 | | | 2 | 13/08 | +8 | 1 | distinct key - differs on amount | | 3 | 14/08 | +8 | 1 | | | 4 | 14/08 | −8 | 1 | distinct key - differs on amount | | balance: 8 and 8 ✅ | Four entries, four rows, nothing overwritten, and the day totals come out right without anyone netting anything by hand. RETURNING * is the change signal. It yields rows only for genuinely new entries - precisely the "what changed, what should we republish downstream" question the old IS DISTINCT FROM predicate was computing. The change-detection logic didn't need porting to the new model; it fell out of the insert. That was the moment the design felt right rather than merely correct. seq , and why it counts per group amount in the key separates −8 from +8 . It does not separate a shift split as 13/08 - 4 Hours; 13/08 - 4 Hours , where the two entries are genuinely identical: // Number entries within each (date, amount) group, not across the whole request. // Because the entries it separates are identical, any ordering of them is equivalent - // so re-parsing the same payload in any order yields the same key set. const seqByGroup = new Map (); return entries.map(({ date, amount }) => { const group = ${date}:${amount}; const seq = (seqByGroup.get(group) ?? 0) + 1; seqByGroup.set(group, seq); return { requestId, date, amount, seq, employeeId, leaveType, unit }; }); Counting per (date, amount) group rather than per entry matters more than it looks. The correction payload from part 1 proves the source doesn't order entries consistently - −8, +8 on one date and +8, −8 on the other, in the same request. A seq assigned by entry position would change between syncs, produce new keys, and re-insert the same absence forever. Assigned per group, it's a pure function of the payload's contents. Is it necessary? We can't prove it. A seq > 1 row has never been observed in production. And we can't go and check the history either, because the old upsert overwrote the evidence - the exact rows that would tell us are the ones it destroyed. So seq ships as a column that is either load-bearing or free, and there is no experiment available that distinguishes those. Given the alternative is finding out after the balances are wrong, that's a fine trade. NUMERIC , not REAL , once it's in the key The legacy column was REAL and nobody had minded. Putting the amount in the primary key changes that, because the data is not binary-exact: fractional-day holidays exist, and out of a REAL column they read back as 0.83000004 and 0.66999996 , with a booking-plus-correction pair netting to −4.47e-8 instead of 0 . Keying on a type that cannot represent its own values exactly makes luck load-bearing. NUMERIC(10,4) . The boundary that replaced heal The report we poll is filtered on a completed_on_or_after parameter. Set that boundary to the instant the seed snapshot was taken, and no request in the seed can ever arrive from the new feed. Nothing overlaps, so nothing needs matching. The unanswerable question is never asked. That's the entire replacement for heal: a parameter we were already passing, given one specific value. The boundary is completion time, not absence date - an easy thing to get wrong. Corrections to historical absences legitimately arrive after cutover; they're just new ledger entries whose signed amount adjusts the balance. Cutting on absence date would drop them on the floor. What that deleted The whole change was 16 files, +1,230 / −1,158. The interesting part is where the deletions landed: | File | Lines | |---|---| | The queries file (heal's five CTEs, the upsert, the change predicate) | +27 / −184 | | Its repository tests | +96 / −506 | Everything under src/ | +417 / −847 | The test file is the number I'd point at. Five hundred lines of tests didn't get deleted so much as become meaningless: they tested ranking, netting, snapshot-bounding and 1:1 capping, none of which are concepts in the new model. All five root causes from part 1 were retired structurally - the code path they lived in no longer exists, so they can't regress rather than merely being fixed. 277 tests pass on the other side. The assumption we couldn't eliminate Honest ledgers have honest caveats. Ours: because amount is in the key, a restated amount under an existing request ID would append a second entry rather than conflict with the first, inflating the balance. DO NOTHING cannot catch it - a changed value simply isn't a conflict. We can't prevent it, so we detect it. The report always sends a request's full entry list, so for any request ID it mentions, the entries we derive should exactly equal what we've stored. A stored entry the report no longer claims means the request was restated rather than corrected: // Warn-only, deliberately. A false positive must never block a sync. const stored = await this.repository.findStoredEntryKeys(requestIds); const identity = (e) => ${e.date}:${e.amount}:${e.seq}; // For each request the report mentions, compare its derived identity set against // the stored one; log a warning for any stored entry the report no longer claims. It has never fired. If it ever does, we find out from a log line rather than from payroll. The invariants we run Three one-line guards, run alongside the parity query. Each maps to a way the model could quietly stop being true: SELECT 'mutated ledger rows' AS invariant, COUNT() AS violations FROM leave_ledger WHERE updated_at <> created_at UNION ALL SELECT 'heal-sourced rows', COUNT() FROM leave_ledger WHERE source = 'heal' UNION ALL SELECT 'legacy rows losing precision at NUMERIC(10,4)', COUNT(*) FROM leave_legacy WHERE amount IS NOT NULL AND amount::numeric(10,4) <> amount::numeric; The first says nothing has learned to update a ledger row. The second says nobody has reintroduced compensation - the mechanism is gone, so any row claiming that source is a regression by definition. The third is the one I like: before you migrate REAL into a narrower NUMERIC , make the database tell you whether every existing value survives the round trip. The cutover is where the risk actually went Deleting heal didn't remove risk from the migration, it concentrated it into one step: deriving the boundary. Here are six consecutive parity runs, with no code changes between them: | Run | State | Mismatched employee-days | Net hours diff | |---|---|---|---| | 1 | Seeded, narrow catchup window | 235 | −394.75 | | 2 | Catchup window widened | 25 | −41 | | 3 | Widened again | 19 | +7 | | 4 | Both feeds caught up | 0 | 0 | | 5 | One new booking mid-run | 1 | +8 | | 6 | Both schedulers live | 0 | 0 | Run 1 is the lesson. We derived the boundary from MAX(created_at) on the seeded rows while the legacy processor was still writing. But legacy's write clock lags the platform's completion clock by up to about 7 hours 15 minutes - a 13-hour approval lookback, a 30-minute cadence, and no runs overnight. Any request that completed before the boundary but was written after it landed in neith