The situation
A cross-border payments company processing around eighteen million pounds a month had a finance team that spent four days at every month end reconciling, and still routinely wrote off between two and nine thousand pounds a month as unexplained.
The amounts were small relative to volume. The problem was that nobody could explain them, and the company was preparing for a funding round where "we write off a few thousand a month and do not know why" is not a sentence you want in a data room.
The root cause
The original system stored a balance per account and updated it on each transaction. A payment debited one balance and credited another inside a transaction, which sounds correct and is the design almost everyone reaches for first.
It has one fatal property: the balance is the truth, and the history is a log written alongside it. When those two disagree, there is no way to determine which is right. You can see that an account is 40 pounds short. You cannot see why, because the record of what happened is separate from the thing that changed.
Four specific failure modes were producing the discrepancies:
Non-idempotent retries. A timeout on a downstream processor triggered a retry. Sometimes the original had succeeded. The transaction log showed both attempts, the balance reflected one or two depending on timing, and there was no key to tell them apart.
Rounding on currency conversion. Conversions rounded at the point of display in some paths and at the point of storage in others. Each instance was a fraction of a penny. Across six hundred thousand monthly transactions the fractions accumulated into a real number with no home.
Fee application order. Fees were applied by a separate service that read the balance, computed the fee and wrote back. Under concurrency, two fees computed against the same read produced a lost update. Rare, but present.
Manual corrections. When finance found a discrepancy, someone adjusted a balance directly with a note in a spreadsheet. That is entirely understandable and it destroyed the audit trail, which meant the next month's investigation started from a corrupted baseline.
What we built
An immutable double-entry ledger. Balances are no longer stored. They are derived. Every financial event writes a set of entries that must sum to zero, and an account's balance is the sum of its entries. If a balance is wrong, the entries that produced it are right there, in order, with their originating event.
The core constraint is enforced by the database rather than by application code:
create table ledger_entries (
id bigserial primary key,
transaction_id uuid not null,
account_id uuid not null references accounts(id),
currency char(3) not null,
amount_minor bigint not null, -- signed, smallest currency unit
created_at timestamptz not null default now()
);
-- No updates, no deletes. The ledger only grows.
create rule ledger_no_update as on update to ledger_entries do instead nothing;
create rule ledger_no_delete as on delete to ledger_entries do instead nothing;
A deferred constraint trigger checks that every transaction_id group sums to zero per currency before the enclosing transaction commits. An unbalanced write cannot be committed, regardless of what the application intended.
Idempotency as a first class concept. Every write requires a client supplied idempotency key stored under a unique constraint. A retry with the same key returns the original result rather than creating a second transaction. This removed the first failure mode entirely.
Integer minor units throughout. No floating point anywhere in the money path. Amounts are signed integers in the smallest unit of the currency. Conversion is a single function with an explicit rounding rule and a residual entry that records the rounding remainder against a designated account, so the fractions have somewhere to live and the ledger still balances.
Corrections as reversals. There is no adjustment. To correct an error you post a reversing transaction that references the original. The history is preserved and the correction is itself auditable. Finance initially disliked this because it is more work. They now cite it as the feature they would least give up, because every number can be traced to a decision and a person.
Continuous reconciliation. A job runs every fifteen minutes comparing ledger totals against processor settlement reports and flagging breaks immediately. Month end stopped being a discovery exercise and became a confirmation exercise.
The migration, which was the hard part
Replacing the accounting core of a live payments system with no downtime and no risk to correctness was the majority of the engineering effort. We ran it in five stages across four months.
Stage one, shadow writes. Every financial event wrote to both the old system and the new ledger. The old system remained authoritative. Nothing in the product changed. This ran for six weeks.
Stage two, continuous comparison. A job compared derived ledger balances against stored balances every fifteen minutes and reported divergence. In the first fortnight we found eleven discrepancy classes, four of which were bugs in our new code and seven of which were pre-existing bugs in the old system that had never been visible. Finding those seven was worth the project on its own.
Stage three, ledger authoritative for reads. Reads moved to the ledger while writes continued to both systems. Any read discrepancy would have been immediately visible in the product. We ran this for three weeks and saw none.
Stage four, cutover. Writes to the old system stopped. The old tables were kept read-only.
Stage five, decommission. After a full quarter close plus an external audit against the new system, the old code path was removed.
Total customer-facing downtime across the whole migration: zero. That was a hard requirement, and shadow writing is how you meet it. It roughly doubles the write path cost during the transition period, which is a price worth paying to never be in a position where rolling back means reconstructing financial history.
Results
Month-end reconciliation went from four days of finance team effort to about six hours, most of which is review rather than investigation.
In the twelve months since cutover there have been no unexplained discrepancies. There have been discrepancies, seven of them, and every one was traced to a specific transaction and cause within an hour. That is the actual deliverable. Errors do not stop happening. They become explainable.
The seven pre-existing bugs found during shadow comparison accounted for approximately sixty-one thousand pounds of historical drift, which was quantified and provisioned properly rather than continuing to leak.
The funding round closed. Technical due diligence on the ledger, which the client had expected to be the difficult conversation, took under two hours.
What we would tell another team
Do not store balances. Derive them. Every system that stores a balance eventually has a balance that disagrees with its own history, and at that point you have no way to determine the truth.
Shadow write for longer than feels necessary. Our six weeks felt excessive at week three and was clearly correct by week five, when the comparison job surfaced a concurrency bug that appeared roughly once in four hundred thousand transactions. That bug would have been a genuine incident had we cut over on the original schedule.
Make corrections harder rather than easier. Every system that offers a convenient way to adjust a number will have that path used, and each use erases the trail that the next investigation depends on.