Building a reconciliation system

A provider call times out at 2am. The matcher gets an empty list, finds nothing unmatched, and stores a clean run. That green tick is the most dangerous output a reconciliation system can produce, because it says everything was checked on the one night nothing was.

Reconciliation checks your ledger against the outside world: every record the bank or a provider holds matched to a posting in your ledger, and every posting matched back. Whatever doesn't match, and isn't an expected timing difference, is a break. This is how I build the system that finds them.

Three design rules

Silence must never look like a match. A failed read from a provider is recorded as a break. Not a crash that kills the run, and never skipped. If a provider call times out and your matcher reads the empty response as "nothing to match", you've built a system that reports success at the exact moment it went blind. That's worse than having no reconciliation, because no reconciliation doesn't hand anyone a green tick.

A break is a work item, not a log line. It has a category, an owner, an age, a value and a closing reason, and it sits in a queue until a person closes it. Order the queue by value and age, because a £40,000 break from this morning and a £3 break from six weeks ago both need attention for different reasons. Writing one off needs a second approver, because a write-off moves money.

The matcher can never write a posting. Read access to the ledger, write access only to its own match and break tables, enforced by database grants rather than code review. Corrections go through the same code paths as every other ledger write, with a second approver. The thing whose job is to detect errors must not be able to cause them.

Match continuously, check safeguarding daily, and cover the gap

I run transaction matching every few minutes. The two safeguarding checks run once a day: the internal one tests your own records against themselves, and the external one tests them against balances the institutions confirm. A break found the next morning has had a night to spread. A break found five minutes after it happened usually has one payment behind it.

One rule removes a whole category of scheduling bugs: each run covers everything since the last successful run, not just today.

Run every day, weekends included. A quiet Saturday run finds nothing and costs nothing. A Tuesday lost to an outage gets picked up on Wednesday, which covers both days and records them separately. The alternative is a calendar of business days in your scheduler, which is one more table to maintain and to get wrong the year a bank holiday moves.

Pick a fixed cut-off time and keep it. Not "sometime overnight, depending on queue depth". The cut-off is something you agree once with finance, not a property of your infrastructure's mood.

What it looks like in the database

One mirror table per provider. Store the raw payload as jsonb plus the typed columns you match on, extracted at ingest. When a provider changes a field, you want the old payloads intact and a cheap backfill.

The match is a full outer join. One pass, all three outcomes:

select
  coalesce(p.match_key, l.match_key) as match_key,
  p.amount_minor as provider_amount,
  l.amount_minor as ledger_amount
from provider_mirror p
full outer join ledger_postings l
  on l.match_key = p.match_key
where p.match_key is null      -- unmatched at provider
   or l.match_key is null      -- unmatched in ledger
   or p.amount_minor is distinct from l.amount_minor

Here's the trap: this join cannot see a duplicate that shares a key. Two ledger rows with the same key and amount both match the provider row and drop out of the result. Enforce uniqueness on the match key before this runs, with a constraint or an explicit count.

The other kind of duplicate is more common. The same bank transaction arrives by webhook and again in a statement file, with the reference formatted differently each time, and gets posted twice. That second copy has a key the provider doesn't know, so it comes out of this join as unmatched at the provider. A balance comparison can miss it entirely, if an unposted receipt of the same size happens to cancel it out. The matcher can't.

Even that isn't the whole job: two genuine payments with different provider IDs against one instruction both match cleanly, so also check each instruction against the movements it actually produced.

Don't load both sides into application memory and diff them with a hash map. It reads fine in review and falls over the first time a provider hands you a big period, which is exactly the catch-up run after an outage.

Runs are keyed by date, provider and kind, and idempotent. Each attempt keeps its own inputs and results, and the run points at the current attempt. A rerun neither duplicates breaks nor erases the failed attempt you're required to keep.

Checkpoint on ingestion time, not transaction date. Otherwise a record arriving on Wednesday dated Monday falls behind the boundary and is never looked at again.

Match against a replica, not the primary. It's a long read over large ranges and it must not compete with payment approvals for connections. Wait until the replica has caught up past the run's cut-off, or replication lag invents unmatched records for you.

Schedule it outside the application. A timer inside a container means a scaled-down or restarted worker silently skips a day. Use the platform's scheduler, and record the run even when it fails to start, because the day with no record at all is the day someone asks about.

Buying a reconciliation platform is worth pricing at volume. Just know what it doesn't remove: someone still maps every provider identifier, names every expected difference and owns every break category. That mapping is the work.

Keep the failures

Store every run, pass or fail, with the confirmation the institution gave you, in storage with object lock so nobody can quietly improve history. Retain for years, not months.

The mistake is keeping only the clean runs. Auditors read the failures, because a failure with an owner, an age and a closing reason is proof the control works. A system that only keeps its passing runs can't show that the control ever caught anything.

Break it on purpose

None of this is trustworthy until you've broken it deliberately. Inject a provider record with no posting, a posting with no provider record, an amount difference and a duplicate, and assert exactly one break of the right category for each.

Then the test that carries the whole design, because it's the case a balance comparison can't see: inject two errors that cancel, and assert the run produces two breaks and a balance difference of zero.

The rest of the suite: remove a posting and the total you owe should stop matching the accounts. Leave a wallet negative and the total shouldn't shrink. Skip a day and the next run should cover both. Fail a provider call and you should get a break, not a crash and not a clean sheet.

Alarm on the match rate

The monitor I'd add first, and the one that's easiest to forget: alarm when the auto-match rate falls, not only when breaks appear.

At 90% auto-match on 10,000 items a month, 1,000 land on someone's desk. At 98%, it's 200. Same system, same team, five times the manual work, and the difference is usually one field a provider changed in a payload. I aim for 98% per provider, matched with nobody touching them.

The rate is what tells you something systemic changed. A provider adds a prefix to a reference, or issues IDs in a new format for one product, and the resulting breaks look like ordinary noise one at a time. The rate sliding from 99% to 95% is the one number that says it isn't noise, days before the queue becomes visibly unmanageable. Breaks tell you what went wrong. The match rate tells you the world moved.