Reconciliation is not a script

Reconciliation is the part of a payment system nobody specifies and everybody eventually needs.

The pattern is consistent enough to predict. A business integrates a provider, money flows, everything appears to work. Some months later somebody in finance asks whether the money actually received matches what the system says was collected, and the honest answer is that nobody knows, because nothing has ever checked.

What usually gets built at that point is a script. Sum the provider’s settlement file, sum the internal transaction table, compare the two. It runs, it produces a difference, and the difference is never zero.

That script has told you that you disagree. It has not told you where, or why, or which side is wrong, and those are the only questions anybody actually has.

Why the totals never match, legitimately

Before treating a difference as a defect, it is worth knowing how many entirely correct reasons there are for one.

Provider fees are deducted before settlement, so the amount arriving is smaller than the amount captured, by a figure that varies per transaction and per card type. Settlement runs on the provider’s schedule rather than yours, so a payment captured at 23:50 lands in tomorrow’s file. Refunds and chargebacks arrive asynchronously, sometimes weeks later. Multi-currency transactions are converted at a rate fixed at a moment you did not choose.

A totals comparison folds all of that into a single number, where timing differences and genuine losses look identical. The work is in telling them apart, and that requires matching at the level of individual transactions rather than sums.

The ledger has to be append-only

Everything downstream depends on this, so it is worth getting right before anything else.

Balances are derived from immutable entries rather than stored as a mutable figure. Corrections are appended as reversing entries, never as updates to the original.

CREATE TABLE ledger_entries (
    id             BIGINT       IDENTITY PRIMARY KEY,
    account_id     BIGINT       NOT NULL,
    -- minor units, never a decimal type and never a float.
    amount_minor   BIGINT       NOT NULL,
    currency       CHAR(3)      NOT NULL,
    entry_type     VARCHAR(24)  NOT NULL,   -- capture, refund, fee, fx_adjustment
    -- what caused this entry to exist, so any figure can be traced back
    source_type    VARCHAR(24)  NOT NULL,   -- payment, settlement_line, manual
    source_id      VARCHAR(64)  NOT NULL,
    -- set only on a reversal, pointing at the entry being undone
    reverses_id    BIGINT       NULL REFERENCES ledger_entries(id),
    occurred_at    DATETIME2    NOT NULL,
    recorded_at    DATETIME2    NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE INDEX ix_ledger_source ON ledger_entries (source_type, source_id);

The two timestamps are not redundant. occurred_at is when the thing happened in the world, recorded_at is when you learned about it. A chargeback that occurred on the 3rd and arrived on the 19th needs both, or your restated March figures will never agree with what March looked like at the time.

The reason to accept the extra write cost is that it makes one question answerable with a query rather than an investigation: why is this balance what it is? Mutable balances cannot answer it, because the evidence was overwritten by the thing you are now trying to explain.

Amounts are integer minor units with an explicit currency, for reasons worth reading separately in idempotency keys in payment APIs. The short version is that binary floating point error is invisible per transaction and accumulates across a settlement run.

Ingest the file, do not just read it

Settlement files are pulled on a schedule, parsed into one canonical shape regardless of provider format, and stored as received.

Keeping the raw file matters more than it sounds. When a discrepancy surfaces five weeks later, you need what the provider actually sent, not your parser’s interpretation of it. Parsers have bugs, and the only way to prove whether a historic difference came from the provider or from your own ingestion is to still have the original bytes.

Ingestion also has to be safely repeatable. Files get redelivered, jobs get rerun by hand after a failure, and somebody will eventually import the same file twice:

-- the provider's own line identifier, scoped to the file, is the natural key
CREATE UNIQUE INDEX ux_settlement_line
    ON settlement_lines (provider_code, provider_reference);

A unique constraint turns a double import into a no-op at the database rather than a duplicate set of ledger entries you then have to find and reverse.

Matching runs in tiers

Tier one matches on the provider reference you stored when the payment was captured. This is exact, cheap, and handles the overwhelming majority of volume.

Tier two handles what tier one could not, usually because a reference was lost, mangled by an intermediary, or never returned. Match on amount and date inside a tolerance window:

SELECT s.id AS settlement_line_id, p.id AS payment_id
  FROM settlement_lines s
  JOIN payments p
    ON  p.amount_minor = s.amount_minor
    AND p.currency     = s.currency
    AND p.captured_at >= DATEADD(day, -@toleranceDays, s.settled_at)
    AND p.captured_at <  DATEADD(day,  @toleranceDays, s.settled_at)
 WHERE s.matched_payment_id IS NULL
   AND p.matched_line_id    IS NULL
 GROUP BY s.id, p.id
HAVING COUNT(*) OVER (PARTITION BY s.id) = 1;   -- only accept unambiguous matches

That last condition is the one people leave out. If two payments fit the window equally well, the system must not guess. An automatic match that is wrong is considerably worse than an exception, because it looks settled and nobody will ever look at it again.

@toleranceDays is configuration, not a constant in code. It varies by provider, it gets tuned once the real distribution of settlement lag is visible, and changing it should not require a deployment.

Exceptions are a workflow, not a report

What survives both tiers is a genuine exception, and this is where most reconciliation projects quietly fail. They produce a list. Nobody owns the list. Within a fortnight nobody opens it.

An exception needs the same treatment as any other business process: explicit states, legal transitions, an owner, and a record of what was decided. It is the state machine problem in a different costume.

public enum ExceptionState
{
    Open,            // detected, nobody looking at it yet
    Investigating,   // assigned, someone is on it
    Resolved,        // explained, with a reason and any correcting entry
    WrittenOff,      // accepted as a loss, which is a decision, not a silence
}

WrittenOff is the state that matters most and gets left out most often. Small unexplained differences are a normal part of payments. Accepting one should be a deliberate act by somebody with the authority to make it, recorded with a reason, not something that happens by a row ageing out of a report.

Resolution writes a reversing or correcting entry into the ledger. It never edits history.

Currency conversion needs three recorded moments

Multi-currency is where reconciliation gets genuinely hard, and the cause is almost always the same: a rate was applied but not stored.

Authorisation, capture and settlement can each happen at a different moment with a different rate. If the system records only the converted figure, the difference between what you expected and what arrived cannot be explained afterwards, because the inputs are gone.

Store the original amount, the original currency, the rate applied, the rate source, and the timestamp the rate was taken at, for every conversion. It is five columns, and without them every currency discrepancy becomes unresolvable rather than merely annoying.

What you end up with

A daily answer to whether the books match reality. A trail behind every figure that survives an auditor asking where it came from. Differences surfaced while they are still small enough to explain, and written off deliberately when that is the right call rather than by neglect.

None of that comes from comparing two totals. It comes from matching at the transaction level, keeping the evidence, and giving the leftovers an owner.