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.

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:

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<string, number>();

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:

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 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 neither side. 235 employee-days, ~395 hours, invisible until we widened the catchup window far enough to sweep them back up.

The fix is one step at the top of the seed script: disable and drain the legacy processor before deriving the boundary. That makes the gap empty by construction rather than by luck — the difference between a boundary that's sound and one that happens to work.

Run 5 is the other lesson. A single new booking arrived between runs and showed as a mismatch until the other feed's next cycle. A non-zero parity result is not automatically a regression — and "stable across two runs" doesn't rule out lag either. We had a mismatch set hold steady across two consecutive runs and resolve on the third.

Runs 4 and 6 are both zero with 15+ new rows landing on each side in between, which is what makes it steady-state agreement rather than two feeds happening to line up after a bad snapshot. Both sides: 212,887 rows, 17,808 employees, hours totals identical.

One caveat we wrote down rather than chased: the days column reads −0.0022 forever, because the legacy column is REAL, ours is NUMERIC, and Postgres returns sum(real) -> real. It's documented as expected, with the > 0.0001 tolerance in the comparison queries. Don't let a known float artifact train your team to ignore a red result.

Where this actually stands

Because a war story that ends "and then it was perfect" is a war story that's lying to you:

• The real cutover hasn't happened. Everything above is a rehearsal. Parity was reached by convergence — widening the catchup window until both feeds covered the gap — not by a sound boundary. The production run still has to do the drain-first sequence that run 1 taught us.
• Nothing reads the ledger yet. The API and the warehouse view still read the legacy table. The parity we're protecting is parity for a table with no consumers.
• That's also why the rebuild was affordable. Changing a primary key on ~3M rows was a drop-and-recreate rather than a delicate in-place migration, precisely because no reader would notice. Had this been found a phase later, the same fix would have been an order of magnitude more expensive. The bug was worth finding early far more than it was worth fixing well.

What the design taught us

Prefer a boundary to a heuristic. If you need to decide whether two records describe the same real-world event and they share no identifier, don't build a matcher — find a boundary that makes the overlap empty. Ours already existed as a filter parameter on the source report. We'd just never thought of it as a cutover instant.

Deleting the mechanism moves the risk, it doesn't remove it. Heal was over a thousand lines of continuously-wrong compensation; the boundary is one timestamp that has to be right once. Much better trade — but only if you treat that instant with the seriousness those thousand lines used to absorb. Run 1 is what happens when you don't.

Put the thing you key on in a type that can represent it. REAL was fine as a payload column and unfit as a key column, and nothing about the migration would have told us that if we hadn't gone looking for 0.83000004.

Immutability isn't purism when the data is financial. Leave balances feed payroll. Corrections staying individually queryable, rather than being netted away by an update, is worth the extra rows — and the source was already emitting signed deltas.

Let the write path match the read path. This is the part that stings. The consumer query was SUM(amount) GROUP BY employee, date, type all along. It had been a ledger the whole time. We'd just built a mutable table underneath it and spent two days reconciling the difference.