reconciliation

How it is built

Architecture overview

A reconciliation system compares two or more sets of records that should describe the same money: the company's ledger, a payment processor's report, the bank statement, sometimes a partner's file. It matches what agrees, explains what does not, and turns every difference into a tracked exception that someone resolves. It runs daily in most companies, intraday in the largest, and its output feeds the books, the close and the audit.

The idea that makes it work is a cascade of passes from strict to loose, with every item ending in exactly one state. Cheap exact joins on a reference clear the bulk of the volume. Windowed and grouped passes handle what the references cannot (payouts that bundle many sales, amounts that settle a day later). Whatever remains is not guessed at but queued, with an age and a reason. The correctness property is a balance check: matched plus unmatched on each side must add up to the statement's opening and closing balances, which catches missing lines that item-by-item matching never sees.

Reconciliation does not fix anything by editing a source. A resolved exception becomes a new ledger adjustment, so both sources stay as they were received and the audit trail is complete.

Parts:

• Intake — receives statements, processor reports and internal changes, once each — Change data capture, Incremental sync cursors, Consumer offsets, Message sequence numbers, Webhook delivery, iso-20022-messages (no page yet)
• Normalisation — one record shape: integer amounts, currency, day, clean reference — Money representation, CRC-32C checksums, SHA-2 and BLAKE3
• Matching engine — runs the passes — Reconciliation, Hash join, Sort-merge join, External merge sort, pdqsort, k-way-merge (no page yet), subset-sum-dp (no page yet), fuzzy-string-matching (no page yet), Union-find, d-ary heap, SIMD programming
• Ledger and exceptions — books resolutions and tracks open items — Double-entry ledger, Event sourcing, Optimistic concurrency control, largest-remainder-rounding (no page yet)
• Orchestration — daily and intraday runs that can crash and restart — batch-checkpoint-restart (no page yet), cron-scheduling (no page yet), Durable job queue, Idempotency keys, Dead-letter queue, Circuit breaker
• Platform — durability, recovery and testing — Write-ahead log, Segmented log, Point-in-time recovery, Primary-backup replication, Leases, Fencing tokens, Deterministic simulation testing, Coverage-guided fuzzing, Mutual TLS, Process supervision, Graceful draining, hdr-histogram (no page yet)

 internal ledger ──CDC──┐
 processor reports ─────┼──► intake (idempotent, by source+id) ─► raw store ─► normalise ─┐
 bank statements ───────┘                                                                 ▼
                                                              pass 1 reference join ─► matched
                                                              pass 2 amount + window ─► matched
                                                              pass 3 grouped sums    ─► matched
                                                                         │ rest
                                                                         ▼
                  ledger adjustments ◄── analyst / rules ◄── exception queue (age, reason, owner)
                         │
                         └─► balance check vs statement opening/closing ─► close report

How the parts fit together

Intake

Every source is ingested idempotently: each external line is keyed by (source, statement id, line number) or the provider's own id, so re-importing a file changes nothing (Idempotency keys). Statement formats differ: ISO 20022 camt.053 (end of day), camt.052 (intraday) and camt.054 (notifications), SWIFT MT940 and, in the US, BAI2 (see the Reconciliation block for the format list and iso-20022-messages, no page yet, for message structure). Raw files are kept unmodified, with a content hash (SHA-2 and BLAKE3), so the parser can be fixed and re-run later.

The internal side comes from the ledger. Rather than ask the ledger database for "what changed since yesterday" with ad-hoc queries, Change data capture decodes its commit log into ordered row-change events with a resume position, and Consumer offsets record how far reconciliation has read. Where a source offers an API, Incremental sync cursors give the same property: pull only what is new, advance the cursor after the data is stored, and use Message sequence numbers to detect gaps. Processors that push events use Webhook delivery semantics: duplicates are normal, so the intake deduplicates by event id.

Normalisation

Matching is only as good as the normalisation. Amounts are parsed straight into integers in minor units, with sign convention fixed (Money representation); currencies are ISO codes; dates become day numbers in one time zone, with the value date kept apart from the booking date; references are trimmed, upper-cased and stripped of known noise. A CRC-32C over the file segment is a cheap integrity check at rest, distinct from the cryptographic hash used as identity.

Matching engine

The passes follow the cascade in the Reconciliation block; this page is about how they scale and combine.

• Pass 1, reference join. Hash one side by reference and probe with the other: a hash join, linear in the inputs. Where both inputs already arrive sorted by reference (statement files often do) or do not fit in memory, a sort-merge join is cheaper: sort with pdqsort in memory, or External merge sort with a k-way-merge (no page yet) when the data is bigger than RAM. The choice is the classic hash-versus-sort trade-off, made per batch from sizes and sort order.
• Pass 2, amount and window. Bucket the unmatched by (currency, amount); inside a bucket match each record to the closest unused counterpart within a tolerance of a few days. Ties break deterministically (oldest first), so a rerun gives the same answer. A d-ary heap serves the "closest first" order when buckets are large.
• Pass 3, grouping. Processor payouts are net of fees and refunds and bundle many sales. Group the internal side by payout or batch id and match the group sum to one bank line. Where no group key exists, finding a subset of items that sums to a bank amount is subset-sum, pseudo-polynomial in the amount with a DP (subset-sum-dp, no page yet) and only worth it for small candidate sets. Union-find tracks the groups as links are discovered (one bank line matched to several items that share a payout).
• Fuzzy matching. For references that differ in spelling, fuzzy-string-matching (no page yet; edit distance, Jaro-Winkler, trigram indexes) proposes candidates. Fuzzy matches are one-to-one only, scored, and above a threshold go to review, not to auto-accept.

For large volume the inner loops use SIMD programming for key comparison. Matching is a pure function of two normalised sets, which is why it can be tested and replayed.

Ledger and exceptions

Every unmatched or mismatched item enters an exception queue with source, amount, age and a reason code (missing in bank, missing in books, amount differs, duplicate, timing). Analysts or rules resolve them, and the resolution is written to the double-entry ledger as an adjustment (a pair of postings), never as an edit of a source record. The exception log is an event-sourced stream: opened, assigned, commented, resolved. Two analysts working the same item are handled with Optimistic concurrency control: the second save sees the changed version and must review. Fee or interest allocations across entities that must sum exactly use largest-remainder-rounding (no page yet).

Orchestration

A daily run is a batch with many steps: ingest, normalise, match, balance-check, report. batch-checkpoint-restart (no page yet) is the pattern: persist progress after each step and make every step idempotent, so a crash resumes instead of redoing or duplicating. Schedules (end of day per time zone, bank cut-offs, month-end) come from cron-scheduling (no page yet). Ingest and parse tasks run through a Durable job queue with leases, and a file that keeps failing parks in a Dead-letter queue rather than blocking the run. A Circuit breaker protects the pull from a bank or processor API that is down.

Platform

The store is a database with a write-ahead log; the raw files and event streams use a segmented log layout. Failover follows primary-backup replication with leases and fencing tokens so two matching workers cannot both own a run. With point-in-time recovery a bad import can be undone without losing the day. Connections to banks and processors use Mutual TLS. Services run under Process supervision and drain on deploy (Graceful draining).

Because the result is money, it is tested the hard way: Deterministic simulation testing replays runs with duplicated, reordered and late inputs, and parsers are fuzzed (Coverage-guided fuzzing) since they read untrusted partner files.

hdr-histogram (no page yet) records how long a run takes from last input to published exceptions, and how long a single pass takes, as percentiles; a pass that is fast on average but slow in its tail shows up before the cut-off for the next settlement day is at risk.

Data and control flow

Flow 1: daily bank reconciliation.

1. A schedule fires after the bank's statement time. The run is created with an idempotency key (account, date).
2. Intake fetches the camt.053, stores the raw file and hash, parses it and inserts lines keyed by statement id and line number. A re-run inserts nothing new.
3. The internal ledger events for the same value dates are read from the CDC stream.
4. Matching runs pass 1, 2 and 3. Each pass removes matched items from the pools of the next.
5. Remaining items become exceptions. The balance check compares opening balance plus matched and unmatched movements to the closing balance; a mismatch fails the run loudly.
6. The close report is written and the run is marked complete. Time goes to waiting for the bank and to the exception review by people, not to the join.

Flow 2: resolving an exception.

1. An analyst opens "bank line with no ledger entry". The system proposes candidates (fuzzy match, same amount).
2. They link it to an existing entry (a timing difference) or book a new adjustment (a fee the books missed).
3. The adjustment is posted to the ledger with a reference to the exception; the exception event stream records who and why.
4. The next run sees the new entry and matches normally.

Flow 3: processor payout.

1. The processor's report lists the payout with its line items; the bank shows one credit.
2. The report is matched to the internal sales and refunds by id (strict).
3. The payout sum, net of fees, is matched to the bank credit by amount and date window.
4. A difference (a fee not in the report) is an exception with the delta.

Scaling

1. People, not computers, are the first limit: the exception queue grows with volume faster than staff. Lever: raise the auto-match rate with better references and rules, and rank exceptions by amount and age.
2. Matching cost is dominated by the larger side. Lever: hash join on references for the bulk, sorted inputs for the rest, partition by (account, currency, date) and run partitions in parallel.
3. Memory is bound by the hash side. Lever: sort-merge or external sort when inputs exceed memory.
4. Source latency: banks deliver on their own schedule. Lever: intraday reports for large accounts, and tolerance windows that keep timing differences out of the queue.
5. History: years of lines. Lever: partition by date, keep recent hot, archive older in cheap storage.

Failure modes

Real implementations

Dedicated reconciliation products (BlackLine, Trintech, Duco, Modern Treasury) are closed. Open code exists in ledgers that include reconciliation features and in the CDC tooling that feeds them.

Sources

• H. W. Kuhn, The Hungarian method for the assignment problem, Naval Research Logistics Quarterly 1955. The classic algorithm for optimal one-to-one assignment when matching candidates carry scores, the alternative to greedy tie-breaking. DOI
• Stripe documentation on reconciling payouts with the underlying transactions. docs
• Debezium documentation on change data capture. docs
• ISO 20022 message families camt and pain (iso20022.org).
• Repositories listed above.