Payments and Reconciliation

RECORDS FROM BOTH SIDES
  |
  1. EXACT REFERENCE      unique id present and identical on both sides
  |                       -> should clear 60-80% instantly. If it does not,
  |                          fix your reference propagation before anything else.
  |
  2. EXACT AMOUNT + DATE  same amount, same value date, one candidate only
  |
  3. TOLERANCE MATCH      amount within fee/rounding tolerance,
  |                       date within settlement window
  |                       -> tolerances are POLICY, versioned and approved
  |
  4. DERIVED-FEE MATCH    expected_gross - known_fee_schedule = observed_net
  |
  5. ONE-TO-MANY          one settlement credit vs N transactions
  |                       -> subset-sum within tolerance
  |
  6. MANY-TO-MANY         N payments against M invoices
  |                       -> the genuinely hard case
  |
  7. PROBABILISTIC        no reference: score on amount, timing, payer
  |                       history, narration similarity
  |                       -> SUGGESTS a match, a human confirms
  |
  8. EXCEPTION            categorise, age, assign an owner
  |
NEVER let stages 5-7 auto-post without review until you have measured
their precision on your own data for at least one full close cycle.

from dataclasses import dataclass
from datetime import date, timedelta
from decimal import Decimal

@dataclass(frozen=True)
class Tolerance:
    """Tolerances are POLICY. Versioned, approved, logged with every match.
    Widening a tolerance to clear a backlog is a control change, not a
    configuration tweak."""
    version: str
    amount_abs: Decimal        # absolute paise/cents tolerance
    amount_pct: Decimal        # proportional tolerance for fee variance
    date_days: int             # settlement window

def amount_within(a: Decimal, b: Decimal, tol: Tolerance) -> bool:
    diff = abs(a - b)
    return diff <= tol.amount_abs or (b and diff / abs(b) <= tol.amount_pct)

def match_pass(ledger, bank, tol: Tolerance):
    """Returns (matches, unmatched_ledger, unmatched_bank).
    Each match records WHICH RULE fired - that is what makes the
    reconciliation auditable rather than merely complete."""
    matches, used_bank = [], set()

    # Stage 1: exact reference. Highest certainty, do it first.
    bank_by_ref = {}
    for b in bank:
        if b.get("reference"):
            bank_by_ref.setdefault(b["reference"].strip().upper(), []).append(b)

    for l in ledger:
        ref = (l.get("reference") or "").strip().upper()
        cands = [b for b in bank_by_ref.get(ref, []) if id(b) not in used_bank]
        if ref and len(cands) == 1:
            b = cands[0]
            used_bank.add(id(b))
            matches.append({"ledger": l, "bank": b, "rule": "exact_reference",
                            "confidence": 1.0, "tolerance_version": tol.version})

    # Stage 2-3: amount + date within tolerance, ONE candidate only.
    # Ambiguity is an exception, never a guess.
    rem_l = [l for l in ledger if not any(m["ledger"] is l for m in matches)]
    for l in rem_l:
        cands = [b for b in bank
                 if id(b) not in used_bank
                 and amount_within(Decimal(str(l["amount"])),
                                   Decimal(str(b["amount"])), tol)
                 and abs((l["value_date"] - b["value_date"]).days) <= tol.date_days]
        if len(cands) == 1:
            b = cands[0]
            used_bank.add(id(b))
            exact = Decimal(str(l["amount"])) == Decimal(str(b["amount"]))
            matches.append({"ledger": l, "bank": b,
                            "rule": "amount_date_exact" if exact else "amount_date_tolerance",
                            "difference": Decimal(str(b["amount"])) - Decimal(str(l["amount"])),
                            "confidence": 0.95 if exact else 0.85,
                            "tolerance_version": tol.version})

    matched_l = {id(m["ledger"]) for m in matches}
    return (matches,
            [l for l in ledger if id(l) not in matched_l],
            [b for b in bank if id(b) not in used_bank])

from itertools import combinations
from decimal import Decimal

def subset_match(settlement_amount, candidates, tol, max_items=6, max_combos=50000):
    """A single settlement credit against N underlying transactions.
    Brute force is 2^N - bound it hard and fall through to exception.

    Returns the UNIQUE subset if exactly one fits. Multiple fits means
    ambiguity, and ambiguity goes to a human. Picking one arbitrarily
    is how reconciliations become confidently wrong."""
    target = Decimal(str(settlement_amount))
    hits, tried = [], 0

    # Prune first: nothing larger than the target can be in the subset
    pool = sorted((c for c in candidates
                   if Decimal(str(c["amount"])) <= target + tol.amount_abs),
                  key=lambda c: -Decimal(str(c["amount"])))

    for size in range(1, min(max_items, len(pool)) + 1):
        for combo in combinations(pool, size):
            tried += 1
            if tried > max_combos:
                return {"status": "search_exhausted", "tried": tried}
            total = sum(Decimal(str(c["amount"])) for c in combo)
            if abs(total - target) <= tol.amount_abs:
                hits.append(combo)
                if len(hits) > 1:
                    return {"status": "ambiguous", "candidate_count": len(hits)}

    if len(hits) == 1:
        return {"status": "matched", "items": hits[0], "rule": "one_to_many_subset"}
    return {"status": "no_match"}

You are a payments operations lead who has built reconciliation
for high-volume fintechs.

My situation:
- Rails I use: [e.g. UPI via Razorpay, cards via a PG, NACH mandates]
- Daily transaction volume: [number]
- Settlement pattern: [e.g. T+1 net settlement, gross, multiple]
- Systems holding records: [ledger, PG dashboard, bank statement, ERP]
- Current process: [describe, or "spreadsheets"]
- Team size on reconciliation: [number]

Give me:

1. A matching cascade for MY rails - the specific stages in order,
   and what share of volume each should clear.
2. The tolerance bands I should start with for amount and date,
   with the reasoning for each rail.
3. My exception categories, and the action and owner for each.
4. Which exceptions are timing artefacts that will self-resolve, and
   at what point each becomes a genuine break I must investigate.
5. The India-specific timing rules I must respect - TAT windows,
   settlement cycles, dispute sequencing.
6. What to log so that a year from now I can prove where a specific
   customer's money went.
7. The three ways this reconciliation will silently go wrong.

Be concrete about numbers and say where they must be calibrated on
my own settlement data.

-- Money is never a column you UPDATE. It is a sequence of entries
-- you APPEND. Balances are derived, never stored as mutable truth.

CREATE TABLE ledger_entry (
  entry_id        BIGSERIAL PRIMARY KEY,
  transaction_id  UUID        NOT NULL,   -- groups the legs together
  account_id      TEXT        NOT NULL,
  direction       CHAR(2)     NOT NULL CHECK (direction IN ('DR','CR')),
  amount_minor    BIGINT      NOT NULL CHECK (amount_minor > 0),
  currency        CHAR(3)     NOT NULL,
  effective_at    TIMESTAMPTZ NOT NULL,   -- when it counts (value date)
  recorded_at     TIMESTAMPTZ NOT NULL DEFAULT now(),  -- when we learned
  idempotency_key TEXT        NOT NULL,
  external_ref    TEXT,                   -- the reference that must survive
  metadata        JSONB       NOT NULL DEFAULT '{}',
  UNIQUE (idempotency_key)
);

-- Every transaction must balance. Enforce it, do not assume it.
CREATE OR REPLACE FUNCTION assert_balanced(txn UUID) RETURNS VOID AS $$
DECLARE d BIGINT; c BIGINT;
BEGIN
  SELECT COALESCE(SUM(amount_minor) FILTER (WHERE direction='DR'),0),
         COALESCE(SUM(amount_minor) FILTER (WHERE direction='CR'),0)
    INTO d, c FROM ledger_entry WHERE transaction_id = txn;
  IF d <> c THEN
    RAISE EXCEPTION 'Unbalanced transaction %: DR=% CR=%', txn, d, c;
  END IF;
END; $$ LANGUAGE plpgsql;

-- THREE THINGS THAT PREVENT MOST RECONCILIATION PAIN:
--
-- 1. amount_minor as BIGINT in the smallest unit (paise, cents).
--    Never floating point. Never. 0.1 + 0.2 != 0.3 and you will
--    discover this during a month-end close.
--
-- 2. effective_at separate from recorded_at. The two clocks from the
--    Beginner lane, made explicit in the schema. This is what lets you
--    answer "what did we believe on Tuesday" versus "what was true".
--
-- 3. idempotency_key UNIQUE. A retried webhook, a double-clicked
--    button, a replayed settlement file - all become no-ops instead
--    of duplicate money.

1. AUTO-MATCH RATE, by rail and by rule
   matched automatically / total items
   Mature platforms report 90-99%. Track it MONTHLY by rail - a drop
   in one rail is a signal, a blended figure hides it.
   Break it down by RULE: a high rate achieved mostly through wide
   tolerances is not the same as one achieved by exact reference.

2. EXCEPTION AGING
   The single most important number. No unmatched item older than
   30 days without a documented owner and a plan.
   Report the distribution, not the average: 0-3d / 4-7d / 8-30d / 30d+

3. VALUE AT RISK IN EXCEPTIONS
   Sum of unmatched amounts, by category and age.
   500 exceptions worth Rs 200 each is an annoyance.
   3 exceptions worth Rs 40 lakh each is an incident.

4. REVIEWER HOURS: PREPARING vs APPROVING
   Before automation the ratio is heavily weighted to preparing.
   It should invert within two close cycles. If it has not, the
   automation moved work rather than removing it.

5. DAYS TO RECONCILE
   Target a 50% reduction within two close cycles.

WHAT NOT TO REPORT AS SUCCESS
  - auto-match rate alone, with no rule breakdown
  - exceptions CLOSED, with no distinction between resolved
    and written off
  - a blended rate across rails with very different profiles

{
  "run_id": "rec_2026-05-23_upi_01",
  "rail": "upi",
  "period": {"from": "2026-05-22T00:00:00Z", "to": "2026-05-22T23:59:59Z"},
  "executed_at": "2026-05-23T04:15:00Z",

  "sources": [
    {"name": "internal_ledger", "records": 41288, "control_total_minor": 918442100},
    {"name": "psp_settlement_file", "file": "stl_20260522.csv",
     "records": 41290, "declared_records": 41290,
     "control_total_minor": 916118400, "checksum": "sha256:9a1c..."},
    {"name": "bank_statement", "records": 37, "control_total_minor": 916118400}
  ],
  "control_total_check": "passed",

  "configuration": {
    "cascade_version": "recon-cascade-v9",
    "tolerance_version": "tol-v4",
    "tolerances": {"amount_abs_minor": 100, "amount_pct": 0.002, "date_days": 2},
    "tolerance_approved_by": "finance_controller_2026-04-02"
  },

  "results": {
    "matched": 41205,
    "by_rule": {"exact_reference": 39880, "amount_date_exact": 1102,
                "amount_date_tolerance": 198, "one_to_many_subset": 25},
    "auto_match_rate": 0.998,
    "exceptions": 85,
    "exceptions_by_category": {"timing": 61, "fee_variance": 14,
                               "missing_in_ledger": 7, "ambiguous": 3},
    "exception_value_minor": 2323700
  },

  "aging": {"0_3d": 61, "4_7d": 17, "8_30d": 6, "over_30d": 1,
            "over_30d_owner": "ops_lead_04", "over_30d_plan_ref": "JIRA-8812"},

  "signoff": {"prepared_by": "system", "reviewed_by": "analyst_12",
              "approved_by": "finance_controller", "approved_at": "..."}
}