Payment Batch Reconciliation: Closing the Loop Between Approved Reimbursements and Bank Settlement

Payment batch reconciliation is the control that proves every approved reimbursement your system promised to pay actually left the bank at the exact amount, to the exact account, exactly once — no silent short-pays, no double disbursements, no orphaned line the ledger still shows as outstanding. Within the broader Reimbursement Routing & Approval Sync framework, this topic area picks up where approvals end: it collects fully approved payout instructions, packs them into an ACH/NACHA or SEPA payment run, hands that file to the bank, and then — hours or days later — parses the settlement and return files the bank sends back to prove the run against reality. It owns batch assembly, the expected-versus-settled three-way match, and return handling; it delegates journal posting to ERP Export Synchronization and consumes the clean, policy-passed records produced upstream by Automated Policy Validation & Anomaly Flagging. This guide covers the batching engine, the reconciliation matcher, its configuration surface, and the runbook that keeps the money and the ledger in lockstep.

Problem Framing & Root Causes

The core failure of naive payout systems is treating “the file was sent” as “the money was paid.” A NACHA or SEPA file is a request, not a settlement; the bank can reject an entry, an ACH debit can bounce days later with a return code, and a beneficiary bank can post a slightly different amount after an FX conversion. Four failure modes dominate production incidents. Fire-and-forget disbursement posts the payment to the ledger the moment the file is generated, so a returned payment leaves the employee unpaid while the books show them settled. Amount drift occurs when the settled amount differs from the instructed amount — cross-border fees, FX rounding, or a bank truncating minor units — and a system that only checks item counts never notices the cents that leak. Trace-id ambiguity breaks matching when the internal record, the file line, and the bank statement each key the transaction differently, forcing fragile matching on amount + date that collides whenever two people are reimbursed the same figure on the same day. Duplicate runs happen when a batch job retries after a partial failure and re-emits entries already sent, double-paying employees unless every line carries a stable idempotency key the bank and the matcher both honor. Reconciliation exists to make each of these loud, deterministic, and auditable rather than silent.

Design Constraints & Prerequisites

This component is transactional and stateful: it owns the batch table, the run manifest, and the reconciliation ledger, and it must be safe to re-run after any crash without paying anyone twice. It assumes upstream approval has already produced immutable, policy-passed payout instructions with a resolved bank account and a currency; if an instruction is missing a routing number, IBAN, or currency it must divert to an exception queue rather than enter a run. Money is always minor-unit integers (cents) or Decimal, never float, because a single sub-cent rounding error multiplied across a 40,000-line run is a material reconciliation break.

The run manifest and reconciliation ledger are the durable state; treat batch windows, cutoff times, and tolerance bands as configuration-as-code so treasury can adjust them without a redeploy.

Production Python Implementation

The engine is three composable stages: deterministic batch assembly that stamps idempotency keys and freezes a run, a NACHA/SEPA file emitter, and a three-way reconciliation matcher that consumes settlement and return files. Each stage is independently testable and emits structured audit metadata.

Batch assembly and idempotent run freezing

Assembly gathers approved instructions, assigns each a deterministic idempotency key derived from its stable identity, and freezes the set into a content-addressed run so re-running the job is a no-op rather than a second payment.

from __future__ import annotations

import hashlib
import logging
from dataclasses import dataclass, field
from datetime import datetime, timezone
from decimal import Decimal
from typing import Sequence

logger = logging.getLogger("reimb.batch.assembly")


@dataclass(frozen=True)
class PayoutInstruction:
    """An immutable, fully approved reimbursement ready to be paid."""
    payout_id: str
    payee_account: str          # tokenized account reference, never raw PAN/IBAN
    amount_minor: int           # minor units (cents); never float money
    currency: str               # ISO 4217
    approved_at: datetime

    def idempotency_key(self, run_date: str) -> str:
        """Stable key: identical instruction in the same run window hashes equal."""
        basis = f"{self.payout_id}|{self.amount_minor}|{self.currency}|{run_date}"
        return hashlib.sha256(basis.encode("utf-8")).hexdigest()


@dataclass
class PaymentRun:
    """A frozen, content-addressed batch of payout lines."""
    run_date: str
    lines: list[dict] = field(default_factory=list)
    run_hash: str = ""

    def freeze(self) -> str:
        """Content-address the run so a retry produces the same run_hash."""
        joined = "\n".join(sorted(line["idempotency_key"] for line in self.lines))
        self.run_hash = hashlib.sha256(joined.encode("utf-8")).hexdigest()
        return self.run_hash


def assemble_run(instructions: Sequence[PayoutInstruction],
                 run_date: str | None = None) -> PaymentRun:
    """Pack approved instructions into a single frozen payment run.

    Each line gets a stable idempotency key and a trace id; duplicate
    instructions collapse to one line so a retry cannot double-pay.
    """
    run_date = run_date or datetime.now(timezone.utc).strftime("%Y-%m-%d")
    run = PaymentRun(run_date=run_date)
    seen: set[str] = set()
    for inst in instructions:
        key = inst.idempotency_key(run_date)
        if key in seen:
            logger.warning("duplicate_instruction_skipped", extra={"payout_id": inst.payout_id})
            continue
        seen.add(key)
        run.lines.append({
            "idempotency_key": key,
            "trace_id": key[:15],          # deterministic ACH/SEPA trace reference
            "payout_id": inst.payout_id,
            "payee_account": inst.payee_account,
            "amount_minor": inst.amount_minor,
            "currency": inst.currency,
            "expected_minor": inst.amount_minor,
            "status": "PENDING_SETTLEMENT",
        })
    run.freeze()
    logger.info("run_frozen", extra={"run_hash": run.run_hash, "line_count": len(run.lines)})
    return run

Because run_hash is a pure function of the line set, a crash-and-retry re-emits the identical run; the downstream file writer and the bank both dedupe on the per-line trace_id, so no employee is paid twice.

Emitting the payment file

The emitter renders each line into a fixed-width NACHA entry (or a SEPA pain.001 record) and records the trace id it wrote, which is the anchor the matcher later joins against. The example keeps the NACHA entry-detail shape without a real routing number.

from __future__ import annotations

import logging
from typing import Iterable

logger = logging.getLogger("reimb.batch.filewriter")


def render_nacha_entry(line: dict, dfi_routing: str) -> str:
    """Render one NACHA PPD entry-detail record (type 6) for a payout line.

    Fixed-width, 94 chars. amount is minor units, right-justified. The trace
    number embeds the line's deterministic trace id for later reconciliation.
    """
    amount = str(line["amount_minor"]).rjust(10, "0")          # cents, no decimal point
    account = line["payee_account"].ljust(17)[:17]
    name = line["payout_id"].ljust(22)[:22]
    trace = line["trace_id"].rjust(15, "0")[:15]
    record = (
        "6"          # record type: entry detail
        "22"         # transaction code: checking credit
        f"{dfi_routing[:8]}"
        f"{dfi_routing[8:9] if len(dfi_routing) > 8 else '0'}"
        f"{account}"
        f"{amount}"
        f"{name}"
        "  "         # discretionary data
        "0"          # addenda indicator
        f"{trace}"
    )
    return record.ljust(94)[:94]


def write_run_file(lines: Iterable[dict], dfi_routing: str = "076401251") -> str:
    """Serialize a frozen run to a NACHA-style body; header/control omitted."""
    body = "\n".join(render_nacha_entry(line, dfi_routing) for line in lines)
    logger.info("run_file_rendered", extra={"dfi_routing": dfi_routing})
    return body

The emitter never mutates the run; it is a pure projection of frozen state, so re-emitting after a transmission failure is safe. Details of guaranteeing that safety across retries and partial transmissions live in Idempotent ERP sync with retry-safe exports, whose retry model applies equally to bank submission.

Three-way reconciliation matcher

Reconciliation joins three views of each payment — the internal record, the instruction line the bank received, and the settlement statement line — on the shared trace id, then classifies each item by comparing expected against settled minor units within a configured tolerance. Returns arrive as a separate feed keyed by the same trace id.

from __future__ import annotations

import logging
from dataclasses import dataclass
from typing import Iterator

logger = logging.getLogger("reimb.batch.reconcile")


@dataclass
class SettlementLine:
    trace_id: str
    settled_minor: int
    currency: str


@dataclass
class ReturnLine:
    trace_id: str
    return_code: str            # e.g. NACHA R01, R02, R03
    reason: str


def reconcile(run_lines: list[dict],
              settlements: list[SettlementLine],
              returns: list[ReturnLine],
              tolerance_minor: int = 0) -> Iterator[dict]:
    """Yield one classified audit record per run line.

    SETTLED   -> matched settlement within tolerance
    RETURNED  -> present in the returns feed (re-queue candidate)
    UNMATCHED -> neither settled nor returned, or amount out of tolerance
    """
    settled_by_trace = {s.trace_id: s for s in settlements}
    returned_by_trace = {r.trace_id: r for r in returns}

    for line in run_lines:
        trace = line["trace_id"]
        expected = line["expected_minor"]
        ret = returned_by_trace.get(trace)
        stl = settled_by_trace.get(trace)

        if ret is not None:
            status, detail = "RETURNED", {"return_code": ret.return_code, "reason": ret.reason}
        elif stl is not None and abs(stl.settled_minor - expected) <= tolerance_minor:
            status, detail = "SETTLED", {"settled_minor": stl.settled_minor}
        elif stl is not None:
            status = "UNMATCHED"
            detail = {"settled_minor": stl.settled_minor,
                      "variance_minor": stl.settled_minor - expected}
        else:
            status, detail = "UNMATCHED", {"reason": "NO_SETTLEMENT_LINE"}

        record = {
            "trace_id": trace,
            "payout_id": line["payout_id"],
            "expected_minor": expected,
            "status": status,
            "currency": line["currency"],
            **detail,
        }
        logger.info("reconcile_item", extra={"trace_id": trace, "status": status})
        yield record

Because the classification is a deterministic function of three inputs, the same settlement and return feeds always produce the same ledger — the property that makes a reconciliation break reproducible and therefore defensible. Settled items flow to journal posting; returned and unmatched items are the subject of the two focused guides below.

Configuration Reference

Expose every tunable through environment variables or a treasury policy store so cutoff times and tolerances change without a code deploy. Pin the file-format version — NACHA and ISO 20022 record layouts change with scheme releases.

Validation & Testing

Test the invariants that protect money: idempotent assembly, exact expected-versus-settled matching, and correct classification of returns. Use fixtures drawn from real breaks — an amount that drifts by one cent, a trace present in the returns feed, and a settlement that never arrives.

from datetime import datetime, timezone


def _inst(pid: str, cents: int) -> PayoutInstruction:
    return PayoutInstruction(payout_id=pid, payee_account="acct-tok-1",
                             amount_minor=cents, currency="USD",
                             approved_at=datetime.now(timezone.utc))


def test_assembly_is_idempotent_on_duplicates():
    run = assemble_run([_inst("P1", 5000), _inst("P1", 5000)], run_date="2026-07-16")
    assert len(run.lines) == 1  # duplicate collapsed, cannot double-pay


def test_reconcile_classifies_settled_returned_unmatched():
    run = assemble_run([_inst("P1", 5000), _inst("P2", 7500), _inst("P3", 9000)],
                       run_date="2026-07-16")
    t1, t2, t3 = (l["trace_id"] for l in run.lines)
    settlements = [SettlementLine(t1, 5000, "USD")]        # P1 settled exactly
    returns = [ReturnLine(t2, "R01", "Insufficient funds")]  # P2 returned
    out = {r["payout_id"]: r["status"]
           for r in reconcile(run.lines, settlements, returns)}
    assert out["P1"] == "SETTLED"
    assert out["P2"] == "RETURNED"
    assert out["P3"] == "UNMATCHED"   # never settled, never returned


def test_amount_drift_is_flagged_unmatched():
    run = assemble_run([_inst("P1", 5000)], run_date="2026-07-16")
    t1 = run.lines[0]["trace_id"]
    out = list(reconcile(run.lines, [SettlementLine(t1, 4999, "USD")], [],
                         tolerance_minor=0))
    assert out[0]["status"] == "UNMATCHED"
    assert out[0]["variance_minor"] == -1

Gate deployment on these assertions in CI. Add fixtures that exercise the tolerance band exactly at its boundary and confirm that a missing payee account raises before assembly rather than producing a run with an unpayable line.

Operational Runbook

1. Provision the ledger and manifest store. Mount durable storage for BATCH_RECON_LEDGER_PATH and the run manifest table before enabling live disbursement, so every run and its reconciliation outcome are recoverable.
2. Freeze runs at cutoff, never mid-approval. Assemble at BATCH_RUN_CUTOFF_UTC so a run is a stable snapshot; late approvals roll to the next window rather than mutating an in-flight file.
3. Hold the return window open. Keep each run in PENDING_SETTLEMENT for BATCH_RETURN_WINDOW_DAYS banking days; do not finalize the ledger until the ACH return window closes, because a payment can bounce after it appears settled.
4. Reconcile on every inbound feed. Run the matcher each time a settlement or returns file arrives; route SETTLED to ERP Export Synchronization for posting and RETURNED/UNMATCHED to the exception handlers.
5. Set alert thresholds. Page when the unmatched rate exceeds 0.5% of a run, when total settled minor units diverge from total expected by more than the tolerance budget, or when a returns feed carries an unrecognized return code.
6. Roll back safely. Because assembly is idempotent and the run is content-addressed, re-running a failed batch job is a no-op; to pause disbursement, stop freezing new runs while continuing to reconcile in-flight ones so the ledger stays current.

Payment batch reconciliation is a deterministic financial control, not a bookkeeping afterthought. Idempotent assembly, a three-way match on a shared trace id, and an immutable reconciliation ledger give treasury and AP a component that pays every approved reimbursement exactly once and can prove it settled — down to the cent — to any auditor.

Related

• Reimbursement Routing & Approval Sync — the parent guide this component completes
• Reconciling reimbursement batches against bank settlement files — parsing settlement and return files and matching by trace id
• Handling partial and failed reimbursement payments — return codes, compensating entries, and re-queueing
• ERP Export Synchronization — posts settled payments to the general ledger
• Automated Policy Validation & Anomaly Flagging — produces the policy-passed records that enter a run