Reconcile a gateway settlement file into ledger entries, not a report

On Monday morning a gateway's settlement file lands in the SFTP folder: 4,812 lines, a payout of EUR 183,420.17 due on Wednesday, and a ledger that says you captured EUR 184,105.90 through that gateway in the same window. The gap is EUR 685.73, and finance will ask what it is. By the end of this playbook that gap is explained by posted ledger entries (fees, refunds, chargebacks, a currency difference) and by named exceptions with an owner, and the batch cannot close until the file's payout total equals what was posted. The rule the whole process rests on: reconciliation is a posting process, not a comparison, so its output is journal entries and exceptions, never a spreadsheet of differences for a person to work through.

Record what you expect to be settled at the moment it happens

A settlement line can only be matched to something your system already knows about. If the first time the ledger hears of a EUR 120.00 capture is when the gateway settles it, you are reconciling the gateway against itself. So every event that will later appear in a file (a capture, a refund, a chargeback) writes a settlement expectation at the moment the gateway accepts it, keyed by the gateway's own reference.

The expectation row needs these fields and nothing clever:

Write it in the same transaction as the payment state change, so a capture that commits always has an expectation and one that rolls back never does. You know it worked when a query for gateway events in the period without an open expectation returns zero rows before the file arrives.

Store every file line unchanged before you interpret one of them

The parser will reject lines: a type you have not seen, a column the gateway added last week, a currency code in lower case. If parsing and matching happen in one pass, a rejected line vanishes and the batch can never balance, because part of the file was never counted. The same file will also arrive twice at some point (a re-send after an SFTP timeout, an operator dropping it by hand), and the second copy must not post again.

Store a file row with the gateway, file name, SHA-256 of the bytes, line count and the payout total declared in the trailer, unique on the hash. Store one line row per physical line with file_id, line_no, raw_text, the parsed fields as JSON or null, parse_error and a state of parsed or rejected, unique on (file_id, line_no). Insert every line in one transaction, then parse in a second pass that can be re-run after a fix without touching the raw rows. You know it worked when importing the same file twice leaves the line count unchanged, and parsed plus rejected always equals the file's line count.

Match on reference and line type, and check the amount afterwards

Amounts collide. Two GBP 25.00 sales in the same afternoon are routine, and a matcher that pairs lines to expectations by amount and date pairs them the wrong way round often enough to matter, silently, because both sides still balance. I have worked on payment orchestration across 15+ gateways and 200+ currencies, and the rule I apply is that an amount is a check on a match, never a way of finding one. The match key is the gateway's reference plus the line type; amount and currency are compared only once the pair exists.

// simplified: the match step for one parsed settlement line
type Outcome =
  | { kind: 'post'; expectationId: string }
  | { kind: 'exception'; reason: Reason; expectationId?: string };

type Reason =
  | 'unknown_reference' | 'duplicate_line'
  | 'currency_mismatch' | 'amount_mismatch';

async function matchLine(line: ParsedLine): Promise<Outcome> {
  const exp = await expectations.findOne({
    gateway: line.gateway,
    gatewayRef: line.gatewayRef,
    lineType: line.lineType, // sale, refund or chargeback
  });
  if (!exp) return { kind: 'exception', reason: 'unknown_reference' };
  if (exp.status === 'settled') {
    return { kind: 'exception', reason: 'duplicate_line', expectationId: exp.id };
  }
  if (exp.currency !== line.currency) {
    return { kind: 'exception', reason: 'currency_mismatch', expectationId: exp.id };
  }
  if (exp.grossMinor !== line.grossMinor) {
    return { kind: 'exception', reason: 'amount_mismatch', expectationId: exp.id };
  }
  return { kind: 'post', expectationId: exp.id };
}

Compare in minor units and apply no tolerance here: a one-cent difference is a rounding question for the posting step, not a licence to call two numbers equal. You know it worked when a test file with every amount shifted by one minor unit produces one amount_mismatch per line and zero postings.

flowchart TD
  L[Settlement line] --> P{Parses}
  P -- no --> R[Rejected line exception]
  P -- yes --> F{Expectation by ref and type}
  F -- none --> U[Unknown reference exception]
  F -- settled --> D[Duplicate line exception]
  F -- open --> A{Gross and currency equal}
  A -- no --> M[Amount mismatch exception]
  A -- yes --> E[Post journal entries]:::accent
  E --> C[Mark expectation settled]

Post each matched line as journal entries, fees and FX included

A fee that sits in a report is not in the books. The gateway receivable (what the gateway owes you since capture) stays overstated by every fee until something posts it, and that overstatement is most of the EUR 685.73 above. Each matched line moves money out of the receivable and into payout clearing, the account you will later match against the bank statement, with the fee and any currency difference split out on the same journal.

The refund row looks backwards until you remember the receivable was already reduced when the refund was accepted; the settlement line only confirms the deduction from the payout. A fee-only line has no expectation and posts against the file itself, so its absence from the exception queue is deliberate. When a USD 100.00 capture settles into a EUR payout at the gateway's rate, keep the receivable in the capture currency and post the difference between your capture-day rate and the settlement rate to a settlement FX difference account, so the receivable clears to zero. You know it worked when, for one file, debits to payout clearing minus credits equal the declared payout total to the minor unit.

Name every exception and give it an owner

An exception without a type is a row in a spreadsheet. With a type it has an owner and a rule for what closes it, and the count per type over a month shows which part of the pipeline is wrong.

The one that catches people is the refund across the cut-off. A customer pays EUR 120.00 on 28 September under PAY-8F3K2. On 30 September at 23:40 the merchant refunds EUR 40.00, but the gateway closed that day's file at 23:00, so Monday's file carries the sale and Tuesday's carries the refund. On Monday the sale line finds its expectation, 117.96 posts to payout clearing and 2.04 to fees, and the refund expectation stays open, correctly, because its event_at is still inside the settlement window. On Tuesday the refund line arrives as PAY-8F3K2, type refund, gross 4000, finds the open refund expectation and posts; the customer sees EUR 40.00 return to their card and never knows any of this happened.

Now remove line_type from the match key. Tuesday's refund line finds the sale expectation instead, already settled, and raises duplicate_line. The refund expectation then ages past the window and raises missing_from_file. That is two exceptions for one correct event: an engineer hunting a re-sent file that was never re-sent, and an operator querying the gateway about money that was never missing. Two things contain it: the line type in the key, and a missing_from_file check that fires only after event_at plus the settlement window plus one further day, so a refund made minutes before the cut-off is never reported early.

Close the batch only when the control total balances

A batch that closes with an unexplained difference is a report again, with a tick next to it. The control is one equation: the payout total declared in the file equals the sum posted to payout clearing plus the amounts sitting in open exceptions for that file. When it holds, the exceptions are the whole story and the batch closes as balanced with exceptions; payout clearing can then be matched to Wednesday's bank statement line. When it does not hold, something was dropped (a rejected line left out of the exception sum, a skipped fee-only line) and the batch stays open.

Give the batch its own states, ingesting, parsed, matched, then balanced or unbalanced, and let only balanced release the expected amount to whoever matches bank statements. You know it worked when no closed batch has posted plus exception amounts differing from its file total, and Wednesday's bank line matches payout clearing without anyone opening the file.

Build the expectation row first: it is one insert in the capture, refund and chargeback paths, and nothing else here works without it. Then the immutable file import, then the matcher. Postings and exception types can follow one line type at a time, starting with sales and fees because they carry nearly all of the money.