What reconciliation actually is

Your ledger says the safeguarding account holds £4,320,000. The bank confirms £4,320,000. Difference: zero. Green tick, run stored, everyone goes home.

Underneath, two things are wrong. A £12,400 settlement reached you twice, once by webhook and once in the bank's statement file, with the reference formatted differently each time. Your ingest saw two IDs and posted it twice, so your balance is £12,400 too high. A separate £12,400 receipt arrived at the bank and was never posted, so your balance is £12,400 too low. Net effect: nothing.

The amounts don't need to match to the penny for this to hurt. Errors of £12,400 and £12,380 net to £20, and £20 on a £4.3m account gets filed as timing and never looked at again.

Most payments products start with exactly this check. Ask the bank for a balance, compare it with yours, store the difference. Two numbers and a subtraction, and it feels like a control. It isn't one, and this essay is about what it misses.

Accountants named this problem centuries ago

Reconciliation is how you find out your ledger is wrong. Not whether it adds up - you can check that inside your own database, and a ledger that adds up perfectly can still describe money that isn't there. Reconciliation is the check against the outside world: the bank, the card processor, the FX provider, anyone holding or moving money your records make claims about.

It's easy to think of it as an accounting chore done in a spreadsheet. In practice it's closer to a monitoring system, and it's the only part of a payments product whose whole job is to tell you the rest of it is wrong.

Bookkeepers ran into the £4.32m problem a long time ago. The trial balance has been double-entry's self-check for centuries, and accounting textbooks have always listed where it goes blind, under a heading worth remembering: errors not revealed by the trial balance. One of them is compensating errors - two mistakes of equal size in opposite directions.

A balance comparison is the same shape of check, one total held against another, so it inherits the same blind spot. That's not a new insight, it's a known limitation of the method.

The fix is to match every transaction, in both directions. Every provider record matched to a posting, and every posting matched to a provider record. Be precise about which postings: a bank statement line matches postings to that bank account, not both sides of every entry. Internal transfers and fee accruals have no bank record and never should.

Run that on the £4.32m example and the cancelling pair shows up as discrepancies instead of one clean zero: a duplicate posting whose reference the bank has never heard of, and a bank record with nothing in front of it.

Keep the balance comparison. It's one call and one subtraction, and it catches plenty of simple mistakes cheaply. Just demote it to a second check that the matched set adds up.

Three runs, each proving one thing

Reconciliation isn't one job. I split it into three, each with its own stored result.

• Transaction matching proves every movement is recorded exactly once. The other two stand on it.
• The internal check proves your own arithmetic: per-customer balances against the account totals they roll up into, using only your records.
• The external check proves your totals against balances the institutions confirm, and stores that confirmation next to the result.

Merge them and you lose the ability to say what failed. "Reconciliation failed" is not an incident description. "The external run couldn't read the card processor's balance, and the internal run passed" is one somebody can act on.

Match on an ID the provider issued, never on amount and date

Every match is keyed on a stable reference both records carry, usually one the provider issued, sometimes one of yours it echoes back.

Fuzzy matching is wrong some of the time, and you can't tell which times. Two customers pay £250 on the same afternoon, your matcher pairs each with the other's posting, both sides balance, and nothing in the output says two customers are now wrong in your records.

It also can't be evidenced. "We think these are the same payment because they're the same amount on the same day" is a guess, and a guess isn't a control you can show an auditor.

Where a provider issues no identifier at all, don't bury a heuristic in a join. Make that record type a named manual process with an owner.

This is also where ledger design pays off. A ledger that stores a unique effect key per posting, so a replayed event can't post twice, already has a stable identity for every movement. Store the provider's reference beside that key, normalised to one format, and matching becomes a join rather than a guess.

The total you owe won't net

Two numbers you must never confuse: what you owe customers in total, and what's actually sitting in the accounts. The second has to be at least the first, and every part of the gap has to be explainable.

Computing the first is where the mistakes live, and the first mistake is netting. A wallet left negative by a forced debit doesn't reduce what you owe everyone else. One customer owing you £4,600 doesn't make another customer's £10,000 any less owed. If your total is select sum(balance) from wallets, it's short by the size of every debt. A negative wallet is a receivable, a debt the customer owes you, and it belongs in its own account outside the total.

The outbound side is the other half. A payment stops counting towards what you owe only once the funds reach the payee or the payee's bank. A card payment that has cleared against the wallet but hasn't settled out of your bank still counts, even though the customer's balance has already dropped. So your schema has to tell submitted from delivered, and cleared from settled. If there's one boolean called sent, you can't compute the total correctly, and no logic on top of it will fix that.

When the accounts come up short, page a person, top up the same day, and record the shortfall even if it was closed within the hour. A shortfall that got fixed is still a shortfall that happened.

Name the timing differences, then watch what's left

Two systems that touch money are rarely equal at any given instant, and chasing exact equality is how teams end up distrusting their own tool. A timing difference you can explain, and that clears on a known schedule, isn't a problem. So name each one, with its value:

One thing isn't on the list: a payment the customer approved that hasn't been sent yet. That's a hold, a reservation with no posting behind it, so it changes neither your ledger balance nor the bank's, so there's no difference to explain.

Equality was never the goal. Explain every difference between the two numbers, and whatever's left over is your real signal. An explained error is still an error, though - the table is for timing, not for excuses.

What's left goes in a register. Ops teams call the entries breaks, accountants tend to say discrepancies. Same row either way.

Two questions for your own system

Would your reconciliation notice two errors that cancel? And what does it do when a provider call times out?

If the answers are "no" and "carries on", what you have is a green tick, not a control.