Designing a Double-Entry Reconciliation Ledger in Postgres

A single-table “audit_results” schema silently loses money: updates overwrite the history that proves a recovery, credits and disputes cannot be tied back to the original invoice, and the reconciled balance never ties out. This page rebuilds that store as an append-only, double-entry ledger in Postgres where the books balance by construction.

The failure you are hitting

Most freight-audit programs start with one table. A row per audited invoice carries the billed amount, the expected amount, a variance, and a status that walks from open to disputed to recovered. It works in the demo. In production it fails in three ways that all look like data-entry problems until a controller asks you to prove the recovered-dollars number:

• History is overwritten. When a dispute resolves, code runs UPDATE audit_results SET status = 'recovered', recovered_amount = 412.00 WHERE id = .... The prior state is gone. You can no longer show when the claim opened, what it opened at, or that the recovered figure was ever anything else. An auditor asking “prove this $412” has nothing to read.
• Credits float free. A carrier issues a credit memo for $412 referencing its own claim number. There is no place to attach it to the original invoice row except another status flip, so partial credits, over-credits, and credits that arrive after a write-off have nowhere to live. They get keyed as adjustments in a second table that never reconciles against the first.
• The balance never ties out. The “total recovered” number is SELECT SUM(recovered_amount), and the “still open” number is SELECT SUM(variance) WHERE status = 'disputed'. These two queries read different columns updated by different code paths, so they drift. Nobody can say the drift is zero because there is no invariant that forces it to be.

This store sits at the settlement end of Reconciliation Ledger Schema. When it cannot prove its own numbers, every dollar the audit claims to have recovered is contestable.

Root cause analysis

The three symptoms share one root: the schema encodes state as mutable columns instead of encoding events as immutable facts. Three specific design gaps follow from that choice.

1. Mutable rows. Because a row is updated in place, the only thing the table can tell you is its current state. Accounting has known for centuries that a financial record of truth must be a journal of what happened, not a snapshot of where things stand — the snapshot is derived from the journal, never stored alongside it.
2. No double-entry invariant. With a single amount column there is nothing that forces the money to be conserved. A dollar can be marked recovered without ever being removed from the open pool, so the two numbers drift with no error raised. Double-entry closes this by requiring every event to move money between two accounts in equal and opposite amounts, so the total is conserved by construction.
3. No immutability guard. Even a well-meaning append-only convention fails the first time someone runs a “quick fix” UPDATE in a console. Without a database-level guard revoking UPDATE and DELETE, the append-only property is a hope, not a guarantee — and the one time it is violated is the time the audit is questioned.

Reproducible diagnostic

Before migrating, quantify the damage in the existing table. Two queries expose it: one finds rows whose recovered amount cannot be traced to a source event, the other shows that the two “truth” numbers disagree.

-- 1. Recovered rows with no traceable credit source (history was overwritten).
SELECT status, count(*) AS rows, sum(recovered_amount) AS untraceable_dollars
FROM audit_results
WHERE status = 'recovered'
  AND recovered_amount IS NOT NULL
  AND source_credit_ref IS NULL   -- nothing ties the dollar to a real credit
GROUP BY status;

-- 2. Does the ledger tie out? These two SUMs should net against a control total
--    but read different mutable columns, so they drift.
SELECT
  (SELECT COALESCE(sum(variance), 0)         FROM audit_results WHERE status = 'disputed') AS open_pool,
  (SELECT COALESCE(sum(recovered_amount), 0) FROM audit_results WHERE status = 'recovered') AS recovered,
  (SELECT COALESCE(sum(variance), 0)         FROM audit_results)                            AS total_flagged;

Read the output as a decision table:

If the first query returns any rows at all, the store cannot prove its recovered figure and the migration below is not optional.

Resolution path

The fix is a three-table schema — accounts, entries, legs — plus two triggers that make the invariants unbreakable. Pin the client library so CI and production agree:

# requirements.txt
psycopg[binary]==3.2.3
pydantic==2.10.6

Step 1 — Create the append-only, double-entry schema

The entry header carries business context; the legs carry money. amount is always positive and direction carries the sign, which keeps the balance query simple and makes a negative amount a constraint violation rather than a silent sign error.

-- migrations/001_ledger.sql
CREATE TABLE ledger_account (
    account_code   text PRIMARY KEY,
    normal_balance text NOT NULL CHECK (normal_balance IN ('DEBIT', 'CREDIT'))
);

INSERT INTO ledger_account VALUES
    ('AP_CONTROL', 'CREDIT'), ('DISPUTES_RECV', 'DEBIT'),
    ('RECOVERY_INCOME', 'CREDIT'), ('WRITE_OFF_EXP', 'DEBIT');

CREATE TABLE journal_entry (
    entry_id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_type        text NOT NULL,
    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)
);

Step 2 — Enforce the invariant and immutability at the database

Application code is not a trustworthy gatekeeper for financial invariants — the one direct psql session bypasses it. Two triggers move the guarantees into Postgres. The first fires when an entry’s legs are complete and raises if debits do not equal credits. The second blocks any UPDATE or DELETE on the journal tables outright.

-- migrations/002_guards.sql

-- (a) Balance invariant: sum(debits) must equal sum(credits) per entry.
CREATE OR REPLACE FUNCTION assert_entry_balanced() RETURNS trigger AS $$
DECLARE
    dr numeric(14,4);
    cr numeric(14,4);
BEGIN
    SELECT
        COALESCE(sum(amount) FILTER (WHERE direction = 'DEBIT'),  0),
        COALESCE(sum(amount) FILTER (WHERE direction = 'CREDIT'), 0)
    INTO dr, cr
    FROM journal_leg
    WHERE entry_id = NEW.entry_id;

    IF dr <> cr THEN
        RAISE EXCEPTION 'entry % unbalanced: debits % <> credits %', NEW.entry_id, dr, cr;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- DEFERRABLE so all legs of one transaction insert before the check runs.
CREATE CONSTRAINT TRIGGER trg_entry_balanced
    AFTER INSERT ON journal_leg
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW EXECUTE FUNCTION assert_entry_balanced();

-- (b) Append-only: block mutation of committed history.
CREATE OR REPLACE FUNCTION block_mutation() RETURNS trigger AS $$
BEGIN
    RAISE EXCEPTION 'journal is append-only; correct via a reversal entry, not %', TG_OP;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_no_mutate_entry
    BEFORE UPDATE OR DELETE ON journal_entry
    FOR EACH ROW EXECUTE FUNCTION block_mutation();

CREATE TRIGGER trg_no_mutate_leg
    BEFORE UPDATE OR DELETE ON journal_leg
    FOR EACH ROW EXECUTE FUNCTION block_mutation();

The DEFERRABLE INITIALLY DEFERRED constraint trigger is the key detail: it lets both legs of a transaction insert before the balance is checked at commit, so a well-formed two-leg entry passes while a lopsided one aborts the whole transaction.

Step 3 — Post entries through a single Python API

With the database enforcing correctness, the Python layer only has to compose the right legs and hand them over atomically. A Pydantic model validates the shape; the balance check here is a fast-fail courtesy, but the trigger is the real guarantee.

# ledger/repository.py
from decimal import Decimal
from typing import Literal, Sequence
from pydantic import BaseModel, field_validator
import psycopg

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


class Leg(BaseModel):
    account_code: str
    direction: Direction
    amount: Decimal

    @field_validator("amount")
    @classmethod
    def positive(cls, v: Decimal) -> Decimal:
        if v <= 0:
            raise ValueError("leg amount must be positive; direction carries the sign")
        return v.quantize(Decimal("0.0001"))  # match numeric(14,4), kill float drift


def post(conn: psycopg.Connection, event_type: str, invoice_id: str,
         legs: Sequence[Leg], dispute_id: str | None = None,
         reverses_entry_id: int | None = None) -> int:
    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"))
    if debits != credits:  # fail before the round-trip; trigger is the backstop
        raise ValueError(f"unbalanced: debits {debits} != credits {credits}")

    with conn.transaction():
        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


def correct(conn: psycopg.Connection, wrong_entry_id: int,
            invoice_id: str, legs: Sequence[Leg]) -> int:
    """Reverse a mis-posted entry by mirroring its legs, then post the right one."""
    mirrored = [Leg(account_code=l.account_code,
                    direction="CREDIT" if l.direction == "DEBIT" else "DEBIT",
                    amount=l.amount) for l in legs]
    return post(conn, "REVERSAL", invoice_id, mirrored,
                reverses_entry_id=wrong_entry_id)

The same posting API is what the short-pay reconciler calls when it closes a deduction against a remittance — see Reconciling Short-Pays Against Carrier Remittance — so no matter how a dollar enters the ledger it obeys the same invariant.

Verification

Prove the invariants hold under both the good and the bad path. These assertions belong in the migration’s integration suite and run against a real Postgres, because the guarantees live in the database, not the ORM.

# tests/test_postgres_ledger.py
from decimal import Decimal
import psycopg
import pytest
from ledger.repository import Leg, post


def test_balanced_entry_commits(conn):
    entry_id = post(conn, "DISPUTE_FILED", "INV-7001",
                    legs=[Leg(account_code="DISPUTES_RECV", direction="DEBIT",  amount=Decimal("412.0000")),
                          Leg(account_code="AP_CONTROL",    direction="CREDIT", amount=Decimal("412.0000"))],
                    dispute_id="DSP-1")
    assert entry_id > 0


def test_trigger_rejects_unbalanced_even_if_python_bypassed(conn):
    # Insert legs directly to prove the DB trigger — not just Python — enforces balance.
    with pytest.raises(psycopg.errors.RaiseException):
        with conn.transaction():
            eid = conn.execute(
                "INSERT INTO journal_entry (event_type, invoice_id) "
                "VALUES ('DISPUTE_FILED','INV-7002') RETURNING entry_id").fetchone()[0]
            conn.execute("INSERT INTO journal_leg (entry_id, account_code, direction, amount) "
                         "VALUES (%s,'DISPUTES_RECV','DEBIT',100.0000)", (eid,))
            conn.execute("INSERT INTO journal_leg (entry_id, account_code, direction, amount) "
                         "VALUES (%s,'AP_CONTROL','CREDIT',99.9900)", (eid,))  # off by a cent


def test_update_is_blocked(conn):
    post(conn, "DISPUTE_FILED", "INV-7003",
         legs=[Leg(account_code="DISPUTES_RECV", direction="DEBIT",  amount=Decimal("50.0000")),
               Leg(account_code="AP_CONTROL",    direction="CREDIT", amount=Decimal("50.0000"))])
    with pytest.raises(psycopg.errors.RaiseException):
        conn.execute("UPDATE journal_leg SET amount = 999 WHERE amount = 50.0000")

In production the proof is a nightly control query: SELECT sum(CASE WHEN direction='DEBIT' THEN amount ELSE -amount END) FROM journal_leg must return exactly 0. If it ever returns anything else, an entry was written unbalanced — which the trigger makes impossible, so a non-zero result means the trigger was dropped, and that is the alert.

Preventive configuration

Encode the guarantees so a future migration cannot quietly remove them:

# ledger_guards.yml — asserted by a CI schema test
required_triggers:
  - trg_entry_balanced      # DEFERRABLE constraint trigger on journal_leg
  - trg_no_mutate_entry     # BEFORE UPDATE OR DELETE on journal_entry
  - trg_no_mutate_leg       # BEFORE UPDATE OR DELETE on journal_leg
required_constraints:
  journal_leg.amount: "amount > 0"
  journal_leg.direction: "direction IN ('DEBIT','CREDIT')"
money_dtype: "numeric(14,4)"     # never float / double precision for amounts
global_control_query: "SELECT sum(CASE WHEN direction='DEBIT' THEN amount ELSE -amount END) FROM journal_leg = 0"

• Schema test in CI. Query pg_trigger and fail the build if any guard trigger is missing after a migration.
• Global control query on a schedule. Run the signed-sum query every night; page if it is ever non-zero.
• Revoke direct grants. Only the ledger service role may INSERT; no role has UPDATE/DELETE on the journal tables, so the guard cannot be tested by accident.

FAQ

Why not just add created_at/updated_at and keep the single table?

Timestamps record when a row changed but not what it was before, so an overwritten recovered amount is still unrecoverable. Append-only means the prior fact stays in the table as its own row; the current state is derived by summing, never by reading a column that was mutated in place.

How do I correct a mis-posted entry if updates are blocked?

Post a reversal. Mirror the original legs — every debit becomes a credit and vice versa — with reverses_entry_id pointing at the wrong entry, then post the correct entry. History then shows the mistake, the reversal, and the fix, which is exactly what an auditor needs to see.

Should the balance trigger fire per row or per statement?

Use a DEFERRABLE constraint trigger that evaluates at commit. A plain per-row AFTER INSERT trigger fires after the first leg, when the entry is intentionally unbalanced, and would reject every valid two-leg entry. Deferring to commit lets all legs land first, then checks the completed entry.

Do I need numeric, or is double precision fine for money?

Use numeric. Binary floating point cannot represent most decimal cents exactly, so a double precision amount introduces sub-cent drift that a zero-tolerance balance check will reject on otherwise-correct entries. Store amounts as numeric(14,4) and quantize at the Python boundary.

Related

• Reconciliation Ledger Schema — the parent design this Postgres schema implements, with the full event model and reporting views.
• Reconciling Short-Pays Against Carrier Remittance — the many-to-many matcher that posts close-out entries into this ledger.
• Building an Accessorial Charge Lookup Table in Postgres — a sibling Postgres schema-design walkthrough for the rate-mapping tier.
• Short-Pay & Deduction Management — the deduction engine whose applied short-pays post recovery legs here.

Up one level: Reconciliation Ledger Schema · Section: Discrepancy Resolution & Dispute Routing