Reconciliation Ledger Schema

The reconciliation ledger is where the freight-audit program’s dollars become defensible. Every stage upstream produces a claim — an overcharge flagged, a dispute filed, a credit expected — but a claim is not money until it is booked, balanced, and reconciled against what the carrier actually paid or credited. This is the layer that turns a spreadsheet of “we think we’re owed $412K” into a per-invoice, per-account trail that survives a controller’s review and a SOX walkthrough. It sits at the settlement end of Discrepancy Resolution & Dispute Routing: variance is classified, disputes are routed to carriers, and the financial consequences of both land here as journal entries.

The design constraint that shapes everything on this page is that AP must be able to prove the recovered-dollars number to the cent, months later, without trusting a single mutable field. That rules out the naive “audit_results” table where a status column flips from open to recovered and the original amount is overwritten. Instead the ledger is double-entry — every financial event books equal debits and credits so the books always balance — and append-only — nothing is ever updated or deleted, only reversed by a compensating entry. Five event types drive the ledger: an invoice booked as a liability, a dispute filed against it, a credit received from the carrier, a short-pay applied at remittance time, and a write-off when a claim is abandoned. Each becomes a balanced transaction whose legs post to accounts whose running balances are the audit’s ground truth.

Prerequisites

This stage consumes the output of the classification and dispute tiers and writes to the same Postgres instance that settlement and reporting read from. Before wiring the ledger, confirm the following are in place:

On the Python side the posting API depends on psycopg 3.x for server-side parameterization and pydantic 2.x for validating an entry before it is written. Currency is handled with decimal.Decimal end to end — the ledger never sees a binary float. One config key governs correctness: ledger.balance_tolerance, the maximum residual (default 0.00) tolerated between summed debits and credits. Unlike a validation tolerance, this one is zero by design: a ledger that does not balance exactly is not a ledger.

Ledger data model and field contract

The schema is three tables. ledger_account is the chart of accounts — the named buckets money moves between. journal_entry is the header for one financial event, carrying the business context (event type, source ids, timestamp). journal_leg is the append-only body: two or more rows per entry, each a debit or a credit against one account, constrained so the entry balances. The field contract below is what every downstream consumer — settlement, reporting, an auditor’s ad-hoc query — is allowed to depend on.

The invariant that makes the whole thing trustworthy is that, for every entry_id, the sum of amount where direction = 'DEBIT' equals the sum where direction = 'CREDIT'. Running account balances are never stored as a mutable number; they are derived by summing legs, so there is no counter to drift out of sync with history.

Ledger architecture

The diagram traces one dollar of recovery from event to proof. Freight-audit events on the left each become a balanced journal entry; the balance invariant gates every post; balanced entries update derived account balances; unbalanced ones are rejected and rolled back before anything is written. The account balances are what settlement draws down and what reporting sums.

Step-by-step implementation

Step 1 — Define the chart of accounts and append-only journal

Start with the schema. The account table is small and seeded once; the journal tables are where volume lands. The constraints do the heavy lifting: direction and event_type are checked, amount must be positive, and there is no UPDATE-friendly status column anywhere — state is expressed by posting new entries.

-- schema/ledger.sql
CREATE TABLE ledger_account (
    account_code   text PRIMARY KEY,
    normal_balance text NOT NULL CHECK (normal_balance IN ('DEBIT', 'CREDIT')),
    description    text NOT NULL
);

INSERT INTO ledger_account (account_code, normal_balance, description) VALUES
    ('AP_CONTROL',      'CREDIT', 'Liability owed to carriers for booked invoices'),
    ('DISPUTES_RECV',   'DEBIT',  'Open freight-audit claims against carriers'),
    ('RECOVERY_INCOME', 'CREDIT', 'Recovered dollars realized to date'),
    ('WRITE_OFF_EXP',   'DEBIT',  'Abandoned claims expensed');

CREATE TABLE journal_entry (
    entry_id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_type        text NOT NULL CHECK (event_type IN
                          ('INVOICE_BOOKED','DISPUTE_FILED','CREDIT_RECEIVED',
                           'SHORT_PAY','WRITE_OFF')),
    invoice_id        text NOT NULL,
    dispute_id        text,
    posted_at         timestamptz NOT NULL DEFAULT now(),
    reverses_entry_id bigint REFERENCES journal_entry (entry_id)
);

CREATE TABLE journal_leg (
    leg_id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    entry_id     bigint NOT NULL REFERENCES journal_entry (entry_id),
    account_code text NOT NULL REFERENCES ledger_account (account_code),
    direction    text NOT NULL CHECK (direction IN ('DEBIT', 'CREDIT')),
    amount       numeric(14,4) NOT NULL CHECK (amount > 0)
);

CREATE INDEX idx_leg_entry   ON journal_leg (entry_id);
CREATE INDEX idx_leg_account ON journal_leg (account_code);
CREATE INDEX idx_entry_inv   ON journal_entry (invoice_id);

Common mistake: adding a balance column to ledger_account and updating it on every post. That single mutable counter is the first thing to drift, and once it disagrees with the legs you can no longer tell which is right. Derive the balance; never store it.

Step 2 — Post a balanced transaction atomically

The posting API is the only writer. It accepts an event and a set of legs, validates that debits equal credits before opening a transaction, then writes the entry and all its legs in one atomic unit. If the legs do not balance, nothing is written — the gate in the diagram.

# ledger/posting.py
from dataclasses import dataclass
from decimal import Decimal
from typing import Literal, Sequence
import psycopg

Direction = Literal["DEBIT", "CREDIT"]


@dataclass(frozen=True)
class Leg:
    account_code: str
    direction: Direction
    amount: Decimal  # always positive; direction carries the sign


class UnbalancedEntryError(ValueError):
    """Raised when a transaction's debits and credits do not net to zero."""


def _assert_balanced(legs: Sequence[Leg], tolerance: Decimal = Decimal("0.00")) -> None:
    debits = sum((l.amount for l in legs if l.direction == "DEBIT"), Decimal("0"))
    credits = sum((l.amount for l in legs if l.direction == "CREDIT"), Decimal("0"))
    residual = abs(debits - credits)
    if residual > tolerance:
        raise UnbalancedEntryError(
            f"debits {debits} != credits {credits} (residual {residual})"
        )


def post_entry(
    conn: psycopg.Connection,
    event_type: str,
    invoice_id: str,
    legs: Sequence[Leg],
    dispute_id: str | None = None,
    reverses_entry_id: int | None = None,
) -> int:
    """Write one balanced entry and its legs atomically; return the entry_id."""
    if len(legs) < 2:
        raise UnbalancedEntryError("an entry needs at least one debit and one credit")
    _assert_balanced(legs)  # gate before any write

    with conn.transaction():  # all-or-nothing; a failure rolls back every leg
        entry_id = conn.execute(
            """
            INSERT INTO journal_entry (event_type, invoice_id, dispute_id, reverses_entry_id)
            VALUES (%s, %s, %s, %s) RETURNING entry_id
            """,
            (event_type, invoice_id, dispute_id, reverses_entry_id),
        ).fetchone()[0]
        conn.cursor().executemany(
            """
            INSERT INTO journal_leg (entry_id, account_code, direction, amount)
            VALUES (%s, %s, %s, %s)
            """,
            [(entry_id, l.account_code, l.direction, l.amount) for l in legs],
        )
    return entry_id

Common mistake: validating balance inside the transaction after the inserts and relying on a rollback to undo a bad write. It works, but it churns entry-id sequence values and hides the error behind a database round-trip. Check in Python first; let the transaction guarantee atomicity, not correctness.

Step 3 — Express each event as the right pair of legs

The five event types map to fixed leg templates. Booking an invoice credits the AP liability and debits nothing yet (the offset is the goods/services already received, booked upstream). Filing a dispute moves the disputed amount into a receivable. A credit received or a short-pay applied realizes recovery income and draws the receivable down. A write-off expenses the abandoned claim. The helper below keeps the accounting rules in one place so callers cannot invent an unbalanced pairing.

# ledger/events.py
from decimal import Decimal
from .posting import Leg, post_entry


def file_dispute(conn, invoice_id: str, dispute_id: str, amount: Decimal) -> int:
    """Move a disputed amount into receivable: DR DISPUTES_RECV / CR AP_CONTROL."""
    return post_entry(
        conn,
        event_type="DISPUTE_FILED",
        invoice_id=invoice_id,
        dispute_id=dispute_id,
        legs=[
            Leg("DISPUTES_RECV", "DEBIT", amount),
            Leg("AP_CONTROL", "CREDIT", amount),
        ],
    )


def apply_recovery(conn, invoice_id: str, dispute_id: str, amount: Decimal,
                   event_type: str = "CREDIT_RECEIVED") -> int:
    """Realize recovery and draw down the receivable.

    Used for both CREDIT_RECEIVED (carrier issues a credit memo) and
    SHORT_PAY (the deduction is applied at remittance). Same legs, distinct
    event_type so reporting can tell how the dollar came back.
    """
    return post_entry(
        conn,
        event_type=event_type,
        invoice_id=invoice_id,
        dispute_id=dispute_id,
        legs=[
            Leg("RECOVERY_INCOME", "CREDIT", amount),
            Leg("DISPUTES_RECV", "DEBIT", amount),  # DR reduces the receivable balance
        ],
    )


def write_off(conn, invoice_id: str, dispute_id: str, amount: Decimal) -> int:
    """Abandon a claim: DR WRITE_OFF_EXP / CR DISPUTES_RECV."""
    return post_entry(
        conn,
        event_type="WRITE_OFF",
        invoice_id=invoice_id,
        dispute_id=dispute_id,
        legs=[
            Leg("WRITE_OFF_EXP", "DEBIT", amount),
            Leg("DISPUTES_RECV", "CREDIT", amount),
        ],
    )

Common mistake: correcting a mis-posted amount with an UPDATE journal_leg. The append-only rule is absolute — to fix an entry you post a reversal (reverses_entry_id set, legs mirrored) and then the correct entry, so history shows exactly what happened and when.

Step 4 — Derive running balances and the reconciled position

Balances are a query, not a table. Summing the legs with the account’s normal-balance sign gives the current position; grouping by invoice_id gives the reconciled state of any single claim. Because nothing is ever updated, this query is deterministic and reproducible at any point in time by filtering on posted_at.

-- reporting/balances.sql
-- Current balance per account, signed by its normal balance.
CREATE VIEW account_balance AS
SELECT a.account_code,
       a.normal_balance,
       COALESCE(SUM(
           CASE
               WHEN l.direction = a.normal_balance THEN l.amount
               ELSE -l.amount
           END
       ), 0)::numeric(14,4) AS balance
FROM ledger_account a
LEFT JOIN journal_leg l ON l.account_code = a.account_code
GROUP BY a.account_code, a.normal_balance;

-- Reconciled position of one claim: what is still open on the receivable.
CREATE VIEW claim_position AS
SELECT e.invoice_id,
       e.dispute_id,
       SUM(CASE WHEN l.account_code = 'DISPUTES_RECV' AND l.direction = 'DEBIT'
                THEN l.amount ELSE 0 END) AS opened,
       SUM(CASE WHEN l.account_code = 'DISPUTES_RECV' AND l.direction = 'CREDIT'
                THEN l.amount ELSE 0 END) AS closed
FROM journal_entry e
JOIN journal_leg l ON l.entry_id = e.entry_id
GROUP BY e.invoice_id, e.dispute_id;

The deeper mechanics of the schema itself — the immutability trigger and the constraint that enforces the balance invariant at the database layer — are worked end to end in Designing a Double-Entry Reconciliation Ledger in Postgres.

Validation and testing

The ledger has exactly one invariant that must never break, so the test suite asserts it directly: after any sequence of posts, every entry balances and no account balance can be reconstructed except by summing legs. Property-style tests that post random balanced and unbalanced entries catch regressions the happy path misses.

# tests/test_ledger.py
from decimal import Decimal
import pytest
from ledger.posting import Leg, post_entry, UnbalancedEntryError
from ledger.events import file_dispute, apply_recovery


def test_unbalanced_entry_is_rejected(conn):
    with pytest.raises(UnbalancedEntryError):
        post_entry(conn, "DISPUTE_FILED", "INV-9001",
                   legs=[Leg("DISPUTES_RECV", "DEBIT", Decimal("100.00")),
                         Leg("AP_CONTROL", "CREDIT", Decimal("99.99"))])


def test_every_entry_balances(conn):
    file_dispute(conn, "INV-9002", "DSP-1", Decimal("412.00"))
    apply_recovery(conn, "INV-9002", "DSP-1", Decimal("412.00"))
    rows = conn.execute("""
        SELECT entry_id,
               SUM(CASE WHEN direction='DEBIT'  THEN amount ELSE 0 END) AS dr,
               SUM(CASE WHEN direction='CREDIT' THEN amount ELSE 0 END) AS cr
        FROM journal_leg GROUP BY entry_id
    """).fetchall()
    for _entry_id, dr, cr in rows:
        assert dr == cr  # invariant: debits equal credits for every entry


def test_claim_nets_to_zero_when_fully_recovered(conn):
    file_dispute(conn, "INV-9003", "DSP-2", Decimal("250.00"))
    apply_recovery(conn, "INV-9003", "DSP-2", Decimal("250.00"))
    opened, closed = conn.execute("""
        SELECT opened, closed FROM claim_position WHERE dispute_id = 'DSP-2'
    """).fetchone()
    assert opened == closed  # receivable fully drawn down

The fixture matrix worth keeping: a partially recovered claim (recovery < disputed), an over-credit (carrier returns more than claimed), a reversal of a mis-posted entry, and a write-off followed by an unexpected late credit. Each has a known reconciled position, so a schema change that breaks the arithmetic surfaces as a failing assertion, not a controller’s question at quarter-end.

Performance and tuning

The journal grows without bound because it is append-only, so the tuning story is about keeping the balance queries fast as history accumulates. Range-partitioning journal_leg by posted_at keeps a monthly report scanning one partition instead of the whole table, and materializing balances on a cadence avoids re-summing years of legs for a dashboard.

For a program processing tens of thousands of claims a month, a single Postgres node summing an indexed, monthly-partitioned journal_leg returns account balances in single-digit milliseconds cold, and a materialized view makes the reporting dashboard effectively free. The append-only design pays off here: because rows are never updated, the partitions are write-once and highly compressible, and autovacuum has almost nothing to do.

Failure modes

The currency-rounding failure is the subtle one: because the balance invariant tolerance is 0.00, a single float that introduces 0.00000001 of drift will reject an otherwise-correct entry. Quantize every amount to numeric(14,4) at the API boundary and the class of bug disappears.

Integration points

The ledger’s output is two derived views with a stable contract. Settlement draws down account_balance when a carrier actually pays, and reporting sums RECOVERY_INCOME and claim_position to produce the recovered-dollars figure AP reports upward. Neither consumer is allowed to write to the ledger — they read the derived state only.

The reconciliation that closes short-pays against a carrier’s remittance — where the identifiers and amounts rarely line up cleanly — is its own problem, worked in Reconciling Short-Pays Against Carrier Remittance. It writes back into this ledger through the same posting API, so the balance invariant holds regardless of how messy the match was.

In this section

• Designing a Double-Entry Reconciliation Ledger in Postgres — the DDL, the append-only immutability trigger, and the database-level constraint that enforces sum(debits) = sum(credits), for teams migrating off a single mutable “audit_results” table that silently loses money.
• Reconciling Short-Pays Against Carrier Remittance — the many-to-many matcher that ties short-pay deductions to the carrier’s later credit memos when references and amounts do not agree, closing out phantom open balances.

Related

• Discrepancy Resolution & Dispute Routing — the parent architecture whose classified variance and filed disputes this ledger books.
• Charge Variance Classification — supplies the disputed amount and reason code a dispute entry records.
• Carrier Dispute API Integration — produces the dispute_id that anchors ledger entries to carrier claims.
• Short-Pay & Deduction Management — the deduction engine whose applied short-pays draw the receivable down.

---

Up: Discrepancy Resolution & Dispute Routing