Orders in one system, deliveries in another, invoices in a third, money in the bank feed — and nobody whose job it is to make the four agree. This engine does that job every morning: three matching tiers with printed tolerances, an exception queue with a cause on every line, and a tie-out that refuses to publish if the columns don't add up. It is running below, in your browser, on a 1,200-order book you can make as messy as you like.
Every company that ships before it bills has four records of the same event and no single one of them is true. The order says what was promised; the delivery note says what left; the invoice says what was asked for; the bank says what came. Where they disagree, money sits: a delivery nobody billed is revenue that will never be chased, a duplicate invoice is a customer who will pay once and dispute the rest, a short payment with no credit note is a deduction nobody approved. In case 01 that gap was worth more than any late payer. The Reconciliation Engine is what closes it — TRACE and SEAL of the Leak Ledger, written down as rules and run on a schedule.
The engine is deliberately boring: no learning, no fuzzy-string cleverness, no threshold tuned by feel. Each tier is a sentence a finance controller can read and disagree with, and the tolerance is a number printed on the page. That is what makes an exception queue trustworthy: when the engine says a delivery was never billed, the controller can see exactly which rule said so and why the next rule didn't rescue it.
| STEP | RULE | WHAT IT CATCHES |
|---|---|---|
| T1 · exact | Invoice PO reference = order id, delivered, amounts within ₹1 | The 90% that are simply right. A second invoice claiming an already-claimed delivery becomes DUPLICATE_INVOICE. |
| T2 · amount + window | Same customer, an unclaimed delivery within the tolerance (default 0.5%), invoiced 0–30 days after delivery; nearest amount wins | Mistyped references and rounding between systems — matched, and logged so the reference gets fixed upstream. |
| T3 · nothing | No delivery fits | INVOICE_WITHOUT_DELIVERY — billed something that never left. |
| Deliveries | Delivered more than 7 days ago and claimed by no invoice | UNINVOICED_DELIVERY — the leak itself. |
| P1 · reference | Payment carries the invoice id | Applied directly. |
| P2 · amount | No reference; an open invoice of the same customer within the tolerance | Applied; the closest amount, oldest first on ties. |
| P3 · part-payment | No reference; smaller than every open invoice — applied to the smallest open invoice it fits inside | Instalments. Anything that fits nowhere is UNMATCHED_PAYMENT. |
| Aftermath | Paid but a balance above tolerance remains for 14+ days → SHORT_PAY; unpaid and past terms → OVERDUE_OPEN | Deductions nobody approved; money to chase (the input to the chase list). |
# tier 2 — the sentence a controller can disagree with (engine.py) for d in deliveries: if d["cust"] != inv["cust"] or d["order"] in claimed: continue gap = inv["day"] - d["day"] if gap < 0 or gap > 30: continue # invoiced 0–30 days after delivery diff = abs(inv["amount"] - d["amount"]) if diff <= max(1, d["amount"] * tol) and (best is None or diff < best[0]): best = (diff, d) # nearest amount within tolerance wins
Run the morning to see what the engine finds.
Two things to try. Drag "never invoiced" to zero and the money found collapses to short-pays and duplicates — most of what a ledger loses is simply work that was never billed. Then widen the tolerance to 2%: T2 rescues more mistyped references, but watch the short-pay count fall too — a wide tolerance quietly forgives small deductions, which is exactly how a 2% "rounding" leak becomes policy. The tolerance is a business decision, so it is a slider, not a constant.
| WHEN | WHAT HAPPENS | WHO SEES IT |
|---|---|---|
| 06:00 daily | Pull the four ledgers as of midnight; refuse to run if any extract is older than 26 hours or its row count moved more than 20% day-on-day. | A failed run pages the owner; a stale source never becomes a wrong queue. |
| 06:04 | Match, then tie out: deliveries = claimed + uninvoiced + too-recent; invoices = T1 + T2 + duplicates + unmatched. Any gap → no publish. | The controller. A tie-out failure is the engine finding a bug in itself. |
| 06:05 | Publish the queue: one row per exception, cause, value, age, owner; new since yesterday on top. | Finance works the queue; sales sees uninvoiced deliveries in their own list. |
| Weekly | Two numbers on one chart: money found this week, money still open by age. Tolerance and rules reviewed only when that chart moves. | Leadership — the WATCH stage of the Leak Ledger. |
Reconciliation is not an audit; it is a morning. The value is not in finding the ₹61 lakh once — it is in the queue being empty by Friday and staying that way, because the same rules ran again on Monday. Make the rules readable, make the tolerance a decision, make the tie-out refuse to lie, and the ledger stops being the leak.
Four systems that don't agree? A reconciliation engine is a two-to-four-week SEAL: your ledgers, your tolerances, your queue — running every morning with a tie-out that refuses to publish a wrong number.
Start with a TRACE → engine.py README results.json The case it came from →