How an Append-Only Ledger Design Eliminated a Complex Data Migration Reconciliation Step

A development team migrating short-term leave data from a legacy processor to a new service initially struggled with mismatched rows and key collisions during reconciliation. They discovered that the existing read path already treated leave data as a signed-row ledger, summing amounts via SQL GROUP BY queries rather than relying on row-level overwrites. By aligning the write path to match this pattern, they redesigned the table as an append-only ledger with a composite key of request ID, date, amount, and sequence number. Using INSERT ... ON CONFLICT DO NOTHING instead of DO UPDATE eliminated the need for compensating rows and value-matching logic entirely. Switching the amount column from REAL to NUMERIC also removed floating-point precision errors that would have caused key mismatches in a primary-key-based design.