Reconciliation is a design input, not an ops task

Reconciliation usually enters a codebase as a spreadsheet. Someone notices the provider's dashboard says one number and the ledger says another, two CSVs get exported, and a VLOOKUP settles the argument. That holds for a few months. Then it becomes a cron job. Then it becomes somebody's full-time job.

By the time it's a cron job the schema is already fixed, and the answer is often not derivable from what you stored. Not because the data is wrong. Because the four or five fields that would let you line the two sides up were never written down, and there is no migration that invents them retroactively.

Reconciliation is not a report you generate at the end. It's a property your data model either has or doesn't, and you decide which one the day you write the first migration.

Matching totals is not reconciliation

Here's the check that gets built first:

select sum(amount) from payments where created_at::date = '2026-08-12';
-- compare against the number on the provider's dashboard

Green means almost nothing. A payment of £50 you recorded and they didn't, sitting alongside a payment of £50 they recorded and you didn't, produces a perfectly matching total and two broken records. Offsetting errors are not a rare pathology, either. They're the normal result of one retry landing twice and one request being lost, which is to say the normal result of a bad afternoon.

Reconciliation is set equality over individual movements. Every movement on your side maps to exactly one on theirs, with the same amount, the same direction and the same period, and every movement on theirs maps back. Anything that doesn't map is a break, and a break needs a category, an owner and a resolution.

So you have to join two sets of rows. The rest of this post is about the fact that the join key is not free, and cannot be bolted on afterwards.

The join key has to be one you generated

The obvious candidate is the provider's ID. It's the wrong one, for two reasons.

You might never receive it. The provider commits, then the connection dies before the response reaches you. Now you have a payment you believe you made, with a null provider ID, which is simultaneously the row most in need of reconciling and the row with nothing to reconcile on.

And even when you do receive it, you received it from the API. Reconciliation runs against a settlement file. At most providers those are two different systems with two different release cycles, and the second one has never heard of half the fields in the first.

So you generate your own reference before you make the call, store it against the payment, and send it in whichever field the provider echoes back: reference, metadata, end_to_end_id, remittance_information, depending on the rail.

Then do the thing that separates people who have run this from people who have read about it: verify your reference survives into the settlement file, not just into the API response. Push one real transaction through, wait for the file to land the next morning, and grep it. The metadata object that comes back on the API response is very often absent from the CSV, and finding that out during integration costs an afternoon. Finding it out in month four costs you every transaction in between.

While you're there, check the field's width. ISO 20022 EndToEndId caps at 35 characters. A canonical UUID is 36 with its hyphens, so the obvious choice silently truncates or gets rejected, depending on how forgiving the rail is feeling. Pick a reference format that fits the narrowest field of any rail you might touch, and pick it once, before you have two formats in production.

Store both clocks, and the batch they arrived in

Your created_at is not their value date, and neither of them is the date on the statement.

A concrete version: you record a payment at 23:47 UTC on the 4th. The provider's business day closes at 17:00 in New York. Your reconciliation for the 4th expects that payment. Their file for the 4th doesn't contain it. It shows up on the 5th, exactly as designed, and you have generated a break out of a system that is working correctly.

Do that at volume and you get hundreds of breaks a day that resolve themselves overnight, which is a specific and preventable failure discussed below.

The design input is three timestamps, none of which overwrites another:

• when you created the intent
• when the provider says it happened, stored verbatim with its own offset rather than normalised into your timezone on the way in
• when it appeared in a statement you ingested, and which statement

That last one implies the statement is an entity in your schema. Not a filename in an S3 bucket that a script parses and forgets. A row, with the raw bytes stored next to it, so that "which file did we compare against on the 4th" has an answer a year from now.

A break needs a category before it needs an owner

There are four ways two sets of rows fail to line up, and they are not the same problem:

The fourth row is the one that quietly destroys the practice. If your reconciliation can't distinguish "hasn't settled yet" from "is never going to settle", every run produces a wall of breaks that clear themselves by morning. People learn within about three weeks that the report is noise. And then a real break appears in the middle of it and nobody looks.

A reconciliation that cries wolf is worse than no reconciliation, because it converts an unknown into a false assurance. Nobody writes "we don't check this" on a status page, but that's the honest description of a break report that nobody reads.

Separating the two requires an expectation, which means storing one. For this payment, on this rail, by when should it have appeared? That's the rail's settlement SLA applied to a business calendar, computed at creation time and written to the row:

alter table payments add column expected_settlement_by timestamptz;

With that column, unmatched-and-not-yet-due is a state. Without it, it's indistinguishable from a break, and you will be triaging the difference by hand forever.

One row on your side is not one row on theirs

The provider takes a fee. Then it nets your payouts against your collections. Then it settles the remainder as a single bank credit, two days later.

So four hundred payouts leave your system as four hundred ledger entries, and arrive at your bank as one line reading FASTER PAYMENTS CR 1,847,233.19 REF ACME-SETTLE-0912. There is no one-to-one join available here, and if you insist on one you'll end up writing a function that sums by date and hopes.

Reconcile at two levels instead, because there are genuinely two different claims to verify:

1. Each transaction against the provider's transaction-level statement. Joined on the reference you generated.
2. The provider's declared net settlement against the bank credit. Joined on the settlement batch identifier and the bank's remittance reference.

Level two only exists if the settlement batch is a real entity in your schema, with individual payouts pointing at it. Derive it from dates afterwards and you're reconstructing a grouping the provider already told you, badly.

And the fee is where the schema decision bites hardest. If you store the payout as "we sent 4,955.00 net", you have destroyed the ability to check the fee against the contracted schedule. Store gross, fee and net as three separate facts, with the fee as its own ledger movement:

payout    gross   5,000.00   GBP
fee        -45.00            GBP
payout    net     4,955.00   GBP

Providers change fee schedules. Sometimes they change them by accident, or apply the wrong tier to a subset of your volume for a month. That is a genuinely common and genuinely expensive error, it is entirely invisible if you only stored the net, and it is trivially detectable if you stored all three. The same argument applies to FX: store the rate, both currencies and both amounts, not just the converted figure.

You can't reconcile against a table that changes underneath you

If a payment row is mutable, if status is updated in place and amounts are corrected in place, then last Tuesday's reconciliation is not reproducible. When somebody asks in eleven months why the 4th was signed off, you need the data as it stood on the 4th, the file you compared it against, and the decisions that were made. A payments table with an updated_at column gives you none of the three.

Three things follow, and they're all schema decisions:

• Ledger entries are append-only. A correction is a new entry, not an edit. This is the same argument as reversals not being deletions, arriving from a different direction.
• The ingested statement is stored raw, before parsing, so a parser bug is recoverable rather than terminal.
• The reconciliation itself is recorded, run by run, match by match.

That last table is the one people skip, and it looks roughly like this:

create table recon_runs (
  id            uuid        primary key,
  provider      text        not null,
  period_start  timestamptz not null,
  period_end    timestamptz not null,
  statement_id  uuid        not null references statements (id),
  started_at    timestamptz not null default now(),
  completed_at  timestamptz,
  matched_count int,
  break_count   int
);

create table recon_matches (
  run_id            uuid not null references recon_runs (id),
  entry_id          uuid references ledger_entries (id),
  statement_line_id uuid references statement_lines (id),
  status            text not null
                    check (status in ('matched', 'ours_only', 'theirs_only',
                                      'amount_mismatch', 'pending')),
  matched_on        text not null
                    check (matched_on in ('reference', 'provider_id',
                                          'heuristic', 'manual')),
  resolved_by       text,
  note              text
);

matched_on earns its place. It records how each match was made, which means you can count them by kind. A reference match is exact. A heuristic match on amount and date is a guess that happened to look right. A manual match is a human's judgement call.

Track the ratio over time and you have the best leading indicator in the whole system: a rising manual match rate means something upstream broke weeks ago and people have been absorbing it by hand. That signal is free if you stored the column and unrecoverable if you didn't.

Run it on day one, with twelve transactions

This is the part that makes reconciliation a design input rather than a phase.

Run it in week one, against a sandbox, on a dozen transactions. It will be useless as a control and invaluable as a discovery mechanism, because that's when you find out:

• your reference doesn't appear in the settlement file at all
• the file reports amounts to two decimal places while the API reports minor units
• their "date" is a value date, in a timezone nobody mentioned
• fees are netted into the transaction line rather than itemised
• refunds arrive as negative amounts on the same line type, or in a completely separate file, or both depending on the age of the original charge

Every one of those is a ten-minute fix in week one. The same fix at four hundred thousand transactions with two years of history is a backfill against information you never captured, and the honest version of the outcome is a reconciliation that only goes back to March.

You don't need the ops process on day one. You need the columns.

What this doesn't buy you

• Not correctness. Both sides agreeing proves agreement, not intent. A payment that should never have been authorised reconciles perfectly.
• Not idempotency. Reconciliation finds the double charge tomorrow morning. It doesn't prevent it tonight. These are complementary controls and neither substitutes for the other.
• Not fraud detection. An insider who moves money through the same rails you reconcile produces matched rows.
• Not accounting. That the movements agree says nothing about whether they're classified into the right accounts, which is a separate reconciliation against a different counterparty: your own general ledger.

The short version

• Compare individual movements, not totals. Offsetting errors match beautifully.
• Generate your own reference before the call. The provider's ID arrives too late, and sometimes never.
• Confirm the reference reaches the settlement file, not just the API response. Test it with one real transaction, early.
• Store your timestamp, their timestamp and the statement it arrived in, without collapsing them into one column.
• Give every transaction an expected settlement time, so "not yet" stops looking like "never".
• Model the settlement batch. Netting means the bank line is a sum, and sums need a grouping you were told rather than one you guessed.
• Store gross, fee and net separately. It's the only way to notice a fee schedule change.
• Keep entries append-only and statements raw, or last month's reconciliation can't be re-run.
• Record each match and how it was made. Manual match rate is your early warning.
• Run the whole thing in week one, on twelve transactions, purely to find out what the file actually contains.

A concrete place to start: open your payments table and answer one question. For a payment created forty days ago that the provider never confirmed, what column would you join on? If the honest answer is amount and date, you have a heuristic rather than a reconciliation, and you have it in the exact case where you most need certainty. Then go and read your provider's settlement file specification, the real one, and check that the reference field you're populating is in it.