Matching Settlement Files to Tap Ledgers

This page builds the row-level match that reconciles an acquirer or vendor settlement file against the internal tap ledger, line by line, before either side is ever aggregated. The task is to construct a match key from each settlement row — media reference or transaction reference, amount, and a time window — probe the ledger for it, and classify every row into one of four buckets: matched, in-ledger-not-settled, settled-not-in-ledger, or amount-mismatch. It is the completeness layer under Daily Variance Reconciliation, inside Revenue Reconciliation & Settlement, and its output is what lets the daily report trust its totals.

A summed variance tells you the books do not foot; it cannot tell you which transactions caused it. That is this match’s job. An aggregate under-collection of forty dollars might be one missing batch, four hundred taps each short a dime, or a single fat-fingered refund — and only a per-row classification distinguishes them. The match runs first so the variance report downstream aggregates over rows that have already been confirmed real, complete, and correctly amounted.

Match classification flow

Each settlement row is probed against the ledger by its match key. A hit on the key with an equal amount is a clean match; a hit with a different amount is an amount mismatch; a miss is a settled row with no ledger origin. After every settlement row is consumed, any ledger entry never touched is a tap that was priced but never settled. The flow below traces one row through those outcomes:

Step 1 — Model both sides and the match key

The two record types come from different systems and rarely share a field name, so the first job is a common key. The key is deliberately not the amount alone: it is the transaction or media reference plus a quantized time bucket, which lets a fuzzy time match probe adjacent buckets without a full scan. Amount is compared after a key hit, not folded into the key, so that a genuine amount discrepancy classifies as AMOUNT_MISMATCH rather than vanishing into a miss.

from __future__ import annotations

import logging
from dataclasses import dataclass
from datetime import datetime, timedelta
from decimal import Decimal
from enum import Enum

logger = logging.getLogger("transit.settlement.match")


class MatchClass(Enum):
    MATCHED = "matched"
    IN_LEDGER_NOT_SETTLED = "in_ledger_not_settled"
    SETTLED_NOT_IN_LEDGER = "settled_not_in_ledger"
    AMOUNT_MISMATCH = "amount_mismatch"


@dataclass(frozen=True)
class LedgerEntry:
    txn_ref: str            # internal transaction reference
    media_ref: str          # canonical media hash
    amount: Decimal         # priced fare, minor units
    tapped_at: datetime     # UTC


@dataclass(frozen=True)
class SettlementRow:
    txn_ref: str            # acquirer's echo of the transaction reference
    media_ref: str          # media token as reported by the acquirer
    amount: Decimal         # settled amount, minor units
    settled_at: datetime    # UTC


@dataclass(frozen=True)
class MatchResult:
    classification: MatchClass
    settlement: SettlementRow | None
    ledger: LedgerEntry | None
    amount_delta: Decimal   # settled - ledger, 0 when one side absent


def _bucket(ts: datetime, window: timedelta) -> int:
    """Quantize a timestamp to an integer window bucket for coarse keying."""
    return int(ts.timestamp() // window.total_seconds())

The _bucket helper is what makes the time-window match cheap. Rather than compare every settlement row against every ledger entry, both sides are indexed by a coarse time bucket derived from the tolerance width, so a probe only ever inspects a row’s own bucket and its two neighbors.

Step 2 — Index the ledger with a fuzzy time window

Settlement clocks and validator clocks disagree by seconds to minutes, so an exact timestamp equality never matches. The index keys each ledger entry under (txn_ref, bucket) and, because a tap near a bucket edge can settle into the adjacent bucket, a probe checks the entry’s own bucket plus the ones on either side. Within those candidates it accepts the closest entry whose reference matches and whose time falls inside the tolerance.

from collections import defaultdict
from typing import Iterable


class LedgerIndex:
    """Time-bucketed index over the tap ledger for fuzzy-window probing."""

    def __init__(self, entries: Iterable[LedgerEntry], window: timedelta) -> None:
        self._window = window
        self._by_key: dict[tuple[str, int], list[LedgerEntry]] = defaultdict(list)
        self._consumed: set[int] = set()
        self._entries: list[LedgerEntry] = list(entries)
        for entry in self._entries:
            self._by_key[(entry.txn_ref, _bucket(entry.tapped_at, window))].append(entry)

    def probe(self, row: SettlementRow) -> LedgerEntry | None:
        """Find the closest unconsumed ledger entry for this row's reference."""
        base = _bucket(row.settled_at, self._window)
        best: LedgerEntry | None = None
        best_gap = self._window
        for b in (base - 1, base, base + 1):
            for entry in self._by_key.get((row.txn_ref, b), ()):
                if id(entry) in self._consumed:
                    continue
                gap = abs(entry.tapped_at - row.settled_at)
                if gap <= best_gap:
                    best, best_gap = entry, gap
        return best

    def consume(self, entry: LedgerEntry) -> None:
        self._consumed.add(id(entry))

    def unconsumed(self) -> list[LedgerEntry]:
        return [e for e in self._entries if id(e) not in self._consumed]

Consumption is tracked by object identity so a single ledger entry can never satisfy two settlement rows — a subtle but important guard, because a duplicated settlement line would otherwise happily match the same tap twice and hide a double-capture.

Step 3 — Classify every row and sweep the remainder

The matcher drives the index: probe each settlement row, compare amounts on a hit, and record the classification. After the settlement file is exhausted, whatever ledger entries were never consumed are the taps that priced revenue but never settled — swept into IN_LEDGER_NOT_SETTLED in a final pass.

def match_settlement(
    settlement_rows: Iterable[SettlementRow],
    ledger_entries: Iterable[LedgerEntry],
    window: timedelta = timedelta(minutes=5),
) -> list[MatchResult]:
    """Classify each settlement row against the tap ledger, then sweep unmatched taps."""
    index = LedgerIndex(ledger_entries, window)
    results: list[MatchResult] = []

    for row in settlement_rows:
        entry = index.probe(row)
        if entry is None:
            results.append(MatchResult(
                MatchClass.SETTLED_NOT_IN_LEDGER, row, None, Decimal("0")))
            logger.warning("settled row with no ledger origin: txn=%s", row.txn_ref)
            continue

        delta = row.amount - entry.amount
        if delta == 0:
            index.consume(entry)
            results.append(MatchResult(MatchClass.MATCHED, row, entry, Decimal("0")))
        else:
            index.consume(entry)  # consumed so it is not double-reported as unsettled
            results.append(MatchResult(MatchClass.AMOUNT_MISMATCH, row, entry, delta))
            logger.warning(
                "amount mismatch txn=%s: settled=%s ledger=%s delta=%s",
                row.txn_ref, row.amount, entry.amount, delta)

    for orphan in index.unconsumed():
        results.append(MatchResult(
            MatchClass.IN_LEDGER_NOT_SETTLED, None, orphan, Decimal("0")))
    return results

An AMOUNT_MISMATCH still consumes its ledger entry. If it did not, the mismatched tap would be reported twice — once as a wrong-amount settlement and again as an unsettled tap — inflating both exception buckets and doubling the apparent problem.

Validation & Test Cases

The tests cover a clean one-to-one match, a settlement clock offset that must still match inside the window, and each of the three exception classes.

from datetime import datetime, timedelta, timezone
from decimal import Decimal

UTC = timezone.utc

ledger = [
    LedgerEntry("TXN1", "MEDIA_A", Decimal("290"),
                datetime(2026, 7, 16, 8, 0, 0, tzinfo=UTC)),
    LedgerEntry("TXN2", "MEDIA_B", Decimal("450"),
                datetime(2026, 7, 16, 8, 2, 0, tzinfo=UTC)),
    LedgerEntry("TXN3", "MEDIA_C", Decimal("290"),   # priced but never settled
                datetime(2026, 7, 16, 8, 5, 0, tzinfo=UTC)),
]
settlement = [
    # Clean: same amount, settled 90s later than the tap -> inside 5-min window.
    SettlementRow("TXN1", "MEDIA_A", Decimal("290"),
                  datetime(2026, 7, 16, 8, 1, 30, tzinfo=UTC)),
    # Amount mismatch: settled 20c high (a fee smeared onto the fare).
    SettlementRow("TXN2", "MEDIA_B", Decimal("470"),
                  datetime(2026, 7, 16, 8, 3, 0, tzinfo=UTC)),
    # Settled with no ledger origin: a stray or misrouted transaction.
    SettlementRow("TXN9", "MEDIA_Z", Decimal("290"),
                  datetime(2026, 7, 16, 8, 4, 0, tzinfo=UTC)),
]

results = match_settlement(settlement, ledger, window=timedelta(minutes=5))
by_class = {}
for r in results:
    by_class.setdefault(r.classification, []).append(r)

# One clean match despite the 90-second settlement offset.
assert len(by_class[MatchClass.MATCHED]) == 1
assert by_class[MatchClass.MATCHED][0].ledger.txn_ref == "TXN1"

# One amount mismatch with a signed +20c delta.
mm = by_class[MatchClass.AMOUNT_MISMATCH][0]
assert mm.amount_delta == Decimal("20") and mm.ledger.txn_ref == "TXN2"

# One settled row with no ledger origin.
assert len(by_class[MatchClass.SETTLED_NOT_IN_LEDGER]) == 1
assert by_class[MatchClass.SETTLED_NOT_IN_LEDGER][0].settlement.txn_ref == "TXN9"

# TXN3 was priced but never settled -> swept into the unsettled bucket.
orphans = by_class[MatchClass.IN_LEDGER_NOT_SETTLED]
assert len(orphans) == 1 and orphans[0].ledger.txn_ref == "TXN3"

# Edge case: an exact-reference hit just outside the window is NOT matched.
late = [SettlementRow("TXN1", "MEDIA_A", Decimal("290"),
                      datetime(2026, 7, 16, 8, 30, 0, tzinfo=UTC))]  # 30 min later
late_results = match_settlement(late, ledger[:1], window=timedelta(minutes=5))
assert late_results[0].classification is MatchClass.SETTLED_NOT_IN_LEDGER

The clean match is the important assertion: TXN1 settles 90 seconds after the tap, which an exact-timestamp join would miss entirely, yet the five-minute window matches it while still classifying the 30-minute-late probe as unmatched. The +20c delta on TXN2 is the signature of a fee or surcharge landing on the fare line instead of its own bucket — exactly the kind of row the aggregate variance can see but never locate.

Edge Cases

• Duplicate settlement lines. Acquirers redeliver, and a file can carry the same transaction twice. Identity-based consumption means the first line matches its tap and the second finds the entry already consumed, correctly surfacing as SETTLED_NOT_IN_LEDGER — a signal to dedupe the file rather than a real orphan.
• Window too wide. Widening the time tolerance to force more matches eventually matches the wrong tap when a rider re-taps the same reference minutes apart. Keep the window near the real settlement latency and let genuine misses surface as exceptions rather than masking them.
• Reference echoed inconsistently. Some vendors truncate or re-case the transaction reference. Canonicalize both sides to the same form when building the key, or a perfectly valid match reads as a paired orphan-and-unsettled — the same false signal a business-date misalignment produces one layer up.

Integration Note

This match is the row-level foundation the parent Daily Variance Reconciliation batch stands on: it confirms each settlement line is real and correctly amounted before the totals are summed, so the aggregate variance reflects genuine collection gaps rather than data-plumbing noise. Its downstream sibling is Automating Daily Fare Variance Reports, which aggregates the matched rows into the graded per-product report — the exception classes produced here (amount mismatch, settled-not-in-ledger, in-ledger-not-settled) are the drill-down detail behind every flagged line that report surfaces.

FAQ

Related

• Daily Variance Reconciliation — the parent batch that aggregates the rows this match validates.
• Automating Daily Fare Variance Reports — the report that surfaces these exception classes as flagged lines.
• Ledger & Audit Trail Design — the append-only tap ledger this match probes against.
• Inter-Agency Settlement Clearing — where confirmed matched revenue is later prorated across operators.
• Smart Card Schema Mapping — the normalization that produces the canonical media reference both sides key on.

Part of Daily Variance Reconciliation, within Revenue Reconciliation & Settlement.