Payments Reconciliation Engine

Every digital payment leaves two paper trails: one in the switch (the system that processed the attempt) and one in the bank file (that settles the actual money a day later). They should match. Sometimes they don't. This engine finds every mismatch — across a million records — in under five seconds.

In plain terms: think of it as the accountant who checks your wallet against your bank statement every single day, except your wallet has a million entries and the bank statement refuses to arrive on time. Payments go missing, get recorded twice, or settle for slightly different amounts. This engine compares both sides, flags every disagreement, and — this is the important part — explains exactly why it flagged each one, with evidence.

Problem

The switch ledger is real-time and authoritative for payment attempts. The bank settlement file arrives at T+1 and is authoritative for money actually moving. In a perfect world they'd tell the same story. In the real world: payments get dropped, posted twice, settle late, settle with fee deductions, or arrive with reference numbers that look like a cat walked over the keyboard.

The job is not to make totals look plausible — anyone can make totals look plausible. The job is to classify every single disagreement with evidence an operations team can act on. No guessing allowed.

Architecture

A generator creates both ledgers plus a secret answer sheet (the defect manifest — every injected mistake, written down). The engine never gets to peek at that answer sheet. It runs five numbered SQL passes: clean the data, set aside duplicates, match the easy exact ones, attempt a carefully limited fuzzy rescue for the mangled ones, and classify whatever's left. Only after the engine is done do we check its work against the answer sheet.

Key decisions

1. ADR-0001: deterministic multi-pass matching. Rules run in order, so every result is explainable. If the fuzzy matcher finds more than one possible match, it refuses to guess and raises AMBIGUOUS. A matcher that guesses is a matcher that quietly hides real problems.
2. ADR-0002: DuckDB SQL as the core. The matching logic is just SQL files you can read top to bottom. No framework, no magic. The same SQL runs on my laptop, in CI, and in your browser via DuckDB-WASM. That portability is why the demo below exists.
3. ADR-0003: ground-truth oracle. The generator writes the defect manifest separately from the inputs the engine reads. Verification compares what was injected with what was found. Anything less would be grading your own exam.

Correctness contract

The contract is strict and a little brutal: every injected defect must be found, and no innocent transaction may be accused. Recall of 100%, false positives of zero. Anything less and the build fails.

Verified on a laptop: 1M pairs reconciled in 4.7s, with 100% recall across all 35,000 injected defects in the seven defect classes and zero false positives. Scaled it up too: 5M pairs in 217s on the same laptop (memory-bound because a streaming pipeline VM was hogging the machine — rude — still 100% recall, zero false positives).

Failure modes

Try it

Play with the sliders below — inject defects into 3,000 transaction pairs and watch the engine catch them. Your browser runs the engine's actual, unmodified SQL files; nothing is faked for the demo. Go on, try to fool it. It doesn't get tired, and it doesn't guess.