#!/usr/bin/env python3 """ The Reconciliation Engine — three ledgers (orders, deliveries, invoices) and a bank feed, tied out every morning. Part of "The Engine Room" on simranjaiswal.in. python3 engine.py # reference run, seed 42, writes results.json python3 engine.py --seed 7 --uninvoiced 0.12 --typo 0.1 --shortpay 0.15 --tol 0.5 Everything here is deterministic. The synthetic book is generated from the printed rules below with a tiny 32-bit PRNG (mulberry32) that the in-browser engine on the case page implements bit-for-bit, so the page's reference run reproduces this file's results.json exactly. No real customer data is involved. Stdlib only. """ import argparse, json, math, os # ----------------------------------------------------------------------------- deterministic randomness class RNG: """mulberry32 — identical to the JavaScript port on the case page.""" M = 0xFFFFFFFF def __init__(self, seed): self.a = seed & self.M def random(self): self.a = (self.a + 0x6D2B79F5) & self.M t = self.a t = ((t ^ (t >> 15)) * (1 | t)) & self.M t = ((t + (((t ^ (t >> 7)) * (61 | t)) & self.M)) & self.M) ^ t return ((t ^ (t >> 14)) & self.M) / 4294967296 def normal(self, mu=0.0, sd=1.0): u = self.random() or 1e-12; v = self.random() return mu + sd * math.sqrt(-2 * math.log(u)) * math.cos(6.283185307179586 * v) HORIZON = 200 # the morning the engine runs, in days from the start of the book N_ORDERS = 1200 N_CUST = 60 # ----------------------------------------------------------------------------- the synthetic book def build_book(seed, p_uninv, p_typo, p_short): """Orders → deliveries → invoices → payments, with the mess dialled in by the rates.""" rng = RNG(seed) customers = [] for c in range(N_CUST): terms = [15, 30, 45][int(rng.random() * 3)] lag = max(-3.0, rng.normal(8, 10)) # habitual days after due prone = rng.random() < 0.12 # 12% of customers short-pay three times as often customers.append({"id": "C%03d" % (c + 1), "terms": terms, "lag": lag, "prone": prone}) orders, deliveries, invoices, payments = [], [], [], [] inv_n = 0; pay_n = 0 for i in range(N_ORDERS): cust = int(rng.random() * N_CUST) day = int(rng.random() * 180) amount = int(round(math.exp(rng.normal(10.3, 0.8)))) amount = min(400000, max(5000, amount)) oid = "PO-%04d" % (i + 1) orders.append({"id": oid, "cust": cust, "day": day, "amount": amount}) if rng.random() >= 0.98: continue # never delivered (cancelled) — no invoice possible ddate = day + 1 + int(rng.random() * 10) deliveries.append({"order": oid, "cust": cust, "day": ddate, "amount": amount}) if rng.random() < p_uninv: continue # THE LEAK: delivered, never invoiced inv_date = ddate + int(rng.random() * 5) ref = oid if rng.random() < p_typo: # a digit mistyped in the PO reference d = int(oid[-1]); ref = oid[:-1] + str((d + 1) % 10) inv_amount = amount if rng.random() < 0.03: # rounding noise between systems inv_amount = amount + (-1 if rng.random() < 0.5 else 1) inv_n += 1 inv = {"id": "INV-%04d" % inv_n, "ref": ref, "cust": cust, "day": inv_date, "amount": inv_amount, "order": oid} invoices.append(inv) if rng.random() < 0.04: # the same delivery invoiced twice inv_n += 1 invoices.append({"id": "INV-%04d" % inv_n, "ref": ref, "cust": cust, "day": inv_date + 1 + int(rng.random() * 3), "amount": inv_amount, "order": oid, "dup": True}) C = customers[cust] due = inv_date + C["terms"] paid_day = due + int(round(C["lag"] + rng.normal(0, 4))) r = rng.random() short_rate = p_short * (3 if C["prone"] else 1) blank = rng.random() < 0.10 # one payment in ten arrives with no reference if r < 0.10: pass # unpaid at the horizon elif r < 0.10 + short_rate: cut = 0.02 + rng.random() * 0.06 # 2–8% deducted without a credit note pay_n += 1 payments.append({"id": "PAY-%04d" % pay_n, "ref": "" if blank else inv["id"], "cust": cust, "day": paid_day, "amount": int(round(inv_amount * (1 - cut)))}) elif r < 0.10 + short_rate + 0.05: a1 = int(round(inv_amount * 0.6)) # paid in two parts pay_n += 1; payments.append({"id": "PAY-%04d" % pay_n, "ref": "" if blank else inv["id"], "cust": cust, "day": paid_day, "amount": a1}) pay_n += 1; payments.append({"id": "PAY-%04d" % pay_n, "ref": "" if blank else inv["id"], "cust": cust, "day": paid_day + 7, "amount": inv_amount - a1}) else: pay_n += 1 payments.append({"id": "PAY-%04d" % pay_n, "ref": "" if blank else inv["id"], "cust": cust, "day": paid_day, "amount": inv_amount}) payments = [p for p in payments if p["day"] <= HORIZON] # the bank feed only knows what has arrived return customers, orders, deliveries, invoices, payments # ----------------------------------------------------------------------------- the engine def reconcile(customers, orders, deliveries, invoices, payments, tol_pct): tol = tol_pct / 100.0 by_id = {o["id"]: o for o in orders} delivered = {d["order"]: d for d in deliveries} claimed = {} # order id -> invoice id that owns it tiers = {"T1": 0, "T2": 0, "none": 0} exceptions = [] # {cause, id, cust, value, age} log = [] # 1. invoices → deliveries for inv in invoices: o = by_id.get(inv["ref"]) if o and o["id"] in delivered and abs(inv["amount"] - o["amount"]) <= 1: if o["id"] in claimed: exceptions.append({"cause": "DUPLICATE_INVOICE", "id": inv["id"], "cust": inv["cust"], "value": inv["amount"], "age": HORIZON - inv["day"], "note": "same delivery as " + claimed[o["id"]]}) inv["match"] = "DUP" else: claimed[o["id"]] = inv["id"]; inv["match"] = "T1"; tiers["T1"] += 1 continue inv["match"] = None for inv in invoices: # tier 2: keyed fuzzy — same customer, amount within tolerance, delivery within 30 days if inv["match"] is not None: continue best = None 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 diff = abs(inv["amount"] - d["amount"]) if diff <= max(1, d["amount"] * tol) and (best is None or diff < best[0]): best = (diff, d) if best: claimed[best[1]["order"]] = inv["id"]; inv["match"] = "T2"; tiers["T2"] += 1 log.append("%s matched to %s by amount+window (ref was %s)" % (inv["id"], best[1]["order"], inv["ref"])) else: inv["match"] = "NONE"; tiers["none"] += 1 exceptions.append({"cause": "INVOICE_WITHOUT_DELIVERY", "id": inv["id"], "cust": inv["cust"], "value": inv["amount"], "age": HORIZON - inv["day"], "note": "ref " + inv["ref"] + " not found"}) # 2. deliveries with no invoice after 7 days for d in deliveries: if d["order"] not in claimed and HORIZON - d["day"] > 7: exceptions.append({"cause": "UNINVOICED_DELIVERY", "id": d["order"], "cust": d["cust"], "value": d["amount"], "age": HORIZON - d["day"], "note": "delivered day %d, never billed" % d["day"]}) # 3. payments → invoices inv_by_id = {i["id"]: i for i in invoices} for inv in invoices: inv["remaining"] = inv["amount"]; inv["paid_day"] = None; inv["pays"] = 0 pay = {"P1": 0, "P2": 0, "partial": 0, "none": 0} for p in sorted(payments, key=lambda x: (x["day"], x["id"])): target = inv_by_id.get(p["ref"]) if p["ref"] else None if target is not None: pay["P1"] += 1 else: cands = [i for i in invoices if i["cust"] == p["cust"] and i["remaining"] > 0 and i["match"] != "DUP"] exact = [i for i in cands if abs(i["remaining"] - p["amount"]) <= max(1, i["amount"] * tol)] if exact: target = min(exact, key=lambda i: (abs(i["remaining"] - p["amount"]), i["day"])); pay["P2"] += 1 else: bigger = [i for i in cands if i["remaining"] > p["amount"]] if bigger: target = min(bigger, key=lambda i: (i["remaining"] - p["amount"], i["day"])); pay["partial"] += 1 log.append("%s applied as part-payment to %s" % (p["id"], target["id"])) if target is None: pay["none"] += 1 exceptions.append({"cause": "UNMATCHED_PAYMENT", "id": p["id"], "cust": p["cust"], "value": p["amount"], "age": HORIZON - p["day"], "note": "no reference, no invoice of this size"}) continue target["remaining"] -= p["amount"]; target["paid_day"] = p["day"]; target["pays"] += 1 # 4. what is left on each invoice for inv in invoices: if inv["match"] == "DUP": continue due = inv["day"] + customers[inv["cust"]]["terms"] rem = inv["remaining"] if inv["pays"] > 0 and rem > max(1, inv["amount"] * tol) and HORIZON - inv["paid_day"] > 14: exceptions.append({"cause": "SHORT_PAY", "id": inv["id"], "cust": inv["cust"], "value": rem, "age": HORIZON - inv["paid_day"], "note": "paid %s of %s, no credit note" % (fmt(inv["amount"] - rem), fmt(inv["amount"]))}) elif inv["pays"] == 0 and due < HORIZON: exceptions.append({"cause": "OVERDUE_OPEN", "id": inv["id"], "cust": inv["cust"], "value": rem, "age": HORIZON - due, "note": "%d days past due" % (HORIZON - due)}) return tiers, pay, exceptions, log def fmt(v): return "₹{:,}".format(int(round(v))) CAUSES = ["UNINVOICED_DELIVERY", "DUPLICATE_INVOICE", "SHORT_PAY", "INVOICE_WITHOUT_DELIVERY", "UNMATCHED_PAYMENT", "OVERDUE_OPEN"] FOUND = {"UNINVOICED_DELIVERY", "DUPLICATE_INVOICE", "SHORT_PAY"} # money the ledger was silently losing AGE_BANDS = [("0–30", 0, 30), ("31–60", 31, 60), ("61–90", 61, 90), ("90+", 91, 10**6)] def summarise(customers, orders, deliveries, invoices, payments, tiers, pay, exceptions, params): by_cause = [] for c in CAUSES: rows = [e for e in exceptions if e["cause"] == c] by_cause.append({"cause": c, "count": len(rows), "value": int(sum(r["value"] for r in rows))}) ageing = [] for name, lo, hi in AGE_BANDS: rows = [e for e in exceptions if lo <= e["age"] <= hi and e["cause"] in FOUND] ageing.append({"band": name, "count": len(rows), "value": int(sum(r["value"] for r in rows))}) per_cust = {} for e in exceptions: if e["cause"] == "OVERDUE_OPEN": continue k = customers[e["cust"]]["id"]; per_cust.setdefault(k, {"cust": k, "count": 0, "value": 0, "causes": {}}) per_cust[k]["count"] += 1; per_cust[k]["value"] += e["value"]; per_cust[k]["causes"][e["cause"]] = per_cust[k]["causes"].get(e["cause"], 0) + 1 top = sorted(per_cust.values(), key=lambda r: (-r["value"], r["cust"]))[:8] for t in top: t["value"] = int(t["value"]) money_found = int(sum(e["value"] for e in exceptions if e["cause"] in FOUND)) billed = int(sum(i["amount"] for i in invoices if not i.get("dup"))) return { "engine": "reconciliation-engine", "seed": params["seed"], "horizon_day": HORIZON, "params": params, "book": {"customers": len(customers), "orders": len(orders), "deliveries": len(deliveries), "invoices": len(invoices), "payments": len(payments), "billed": billed}, "invoice_match": {"T1_exact": tiers["T1"], "T2_amount_window": tiers["T2"], "unmatched": tiers["none"]}, "payment_match": {"P1_reference": pay["P1"], "P2_amount": pay["P2"], "partial": pay["partial"], "unmatched": pay["none"]}, "exceptions_total": len(exceptions), "by_cause": by_cause, "ageing_found": ageing, "top_customers": top, "money_found": money_found, "money_found_pct_of_billed": round(100.0 * money_found / billed, 2), "exposure_overdue": int(sum(e["value"] for e in exceptions if e["cause"] == "OVERDUE_OPEN")), "sample_exceptions": sorted(exceptions, key=lambda e: (-e["value"], e["id"]))[:12], } def run(seed=42, uninvoiced=0.05, typo=0.06, shortpay=0.08, tol=0.5): params = {"seed": seed, "uninvoiced_rate": uninvoiced, "typo_rate": typo, "shortpay_rate": shortpay, "tolerance_pct": tol} customers, orders, deliveries, invoices, payments = build_book(seed, uninvoiced, typo, shortpay) tiers, pay, exceptions, log = reconcile(customers, orders, deliveries, invoices, payments, tol) res = summarise(customers, orders, deliveries, invoices, payments, tiers, pay, exceptions, params) res["log_sample"] = log[:10] return res if __name__ == "__main__": ap = argparse.ArgumentParser() ap.add_argument("--seed", type=int, default=42); ap.add_argument("--uninvoiced", type=float, default=0.05) ap.add_argument("--typo", type=float, default=0.06); ap.add_argument("--shortpay", type=float, default=0.08) ap.add_argument("--tol", type=float, default=0.5); ap.add_argument("--out", default=os.path.join(os.path.dirname(os.path.abspath(__file__)), "results.json")) a = ap.parse_args() res = run(a.seed, a.uninvoiced, a.typo, a.shortpay, a.tol) with open(a.out, "w") as f: json.dump(res, f, indent=1) b = res["book"] print("book: %d orders, %d deliveries, %d invoices, %d payments, billed %s" % (b["orders"], b["deliveries"], b["invoices"], b["payments"], fmt(b["billed"]))) print("invoices matched: T1 %d, T2 %d, unmatched %d" % (res["invoice_match"]["T1_exact"], res["invoice_match"]["T2_amount_window"], res["invoice_match"]["unmatched"])) print("payments matched: ref %d, amount %d, partial %d, unmatched %d" % tuple(res["payment_match"].values())) for c in res["by_cause"]: print(" %-26s %4d %s" % (c["cause"], c["count"], fmt(c["value"]))) print("money found: %s (%.2f%% of billed) · overdue exposure %s" % (fmt(res["money_found"]), res["money_found_pct_of_billed"], fmt(res["exposure_overdue"]))) print("wrote", a.out)