DEV Community

Cover image for How to Build a Double-Entry Ledger for P2P Payments in Python and Postgres
Audrey Xu
Audrey Xu

Posted on

How to Build a Double-Entry Ledger for P2P Payments in Python and Postgres

Most wallet prototypes start with a balance column and UPDATE wallets SET balance = balance - 30. It works in the demo, and then three things happen in production: two requests spend the same money at the same time, a retry sends a transfer twice, and finance asks why the bank statement disagrees with the database. Nobody can answer, because the old balances were overwritten.

A double entry ledger fixes all three by changing what you store. You stop storing balances and start storing movements: every transfer becomes a journal entry with two or more postings that sum to zero. Balances become a query. History becomes append-only. Bugs become visible as a non-zero sum.

Here is the problem in one line of real output. Ten threads each try to send $30 from a wallet that holds $100, using a “check the balance, then write” pattern that looks correct (with a 50 ms pause between the check and the commit, the length of a quick fraud-check call):

lock=False  succeeded=10  declined= 0  alice=-200.00 USD
Enter fullscreen mode Exit fullscreen mode

By the end of this article the same ten requests produce 3 successes, 7 declines and a $10.00 balance, and a property-based test hammers the ledger with random operation sequences to prove the invariants hold.

What we build, step by step:

  • Money as integers in minor units, with the ISO 4217 exponent.
  • A Postgres schema where the database itself rejects unbalanced entries.
  • Append-only storage: corrections are reversals, never UPDATE.
  • Balances (posted, pending, available) derived from postings.
  • One write path that handles overdraft checks and concurrent transfers.
  • Idempotent retries, holds (pending to posted), and reconciliation against a bank statement.
  • Unit tests plus a Hypothesis state machine that checks the invariants after every step.

Stack: Python 3.13.18, PostgreSQL 16.15, psycopg 3.3.6, pytest 9.2.0, Hypothesis 6.170.14. Every output block below is copied from a real run of the code; the full project, tests included, is about 800 lines.

The model: accounts, journal entries, postings

Double-entry bookkeeping records “both sides of each event”: each debit has a matching credit, so total debits always equal total credits. In a ledger database that rule maps to three tables:

Table What a row means Rule
accounts A bucket of value in one currency: a user wallet, your bank account, fee revenue Has a normal side (debit or credit)
transactions One journal entry: “Alice paid Bob $25.00 plus a $0.30 fee” Has 2+ postings; never deleted
postings One line of the entry: account, currency, signed amount Sum per transaction and currency is 0; never updated

The part that confuses developers new to double-entry bookkeeping is the sign. Here is the convention used throughout: a debit is a positive amount, a credit is a negative one, and each account says which side is “normal” for it.

For a P2P app, the money users keep in their wallets is not your money. It is a liability: you owe it back. So wallets are credit-normal, while the cash sitting in your bank account is a debit-normal asset. When Alice tops up $100, you debit the bank (assets go up) and credit Alice’s wallet (what you owe her goes up). When she pays Bob, you debit her wallet and credit his. Fees go to a credit-normal revenue account.

Diagram of a P2P transfer as a journal entry: Alice’s wallet debited 25.30, Bob’s wallet credited 25.00, fee revenue credited 0.30, postings summing to zero, with each account’s normal side

The payoff of this convention: the accounting equation (assets = liabilities + revenue) holds automatically, and the property test at the end checks it after every random step.

Step 1. Money as integer minor units

Floats cannot represent most decimal fractions exactly; the Python tutorial’s own example is that 0.1 + 0.1 + 0.1 == 0.3 is False. A ledger stores integers in the currency’s minor unit instead: cents for USD, but whole yen for JPY (exponent 0) and fils for KWD (exponent 3). Those exponents come from the ISO 4217 list, so they live in a table, not in a hardcoded * 100.

"""Money as integer minor units. The exponent comes from ISO 4217."""
from decimal import Decimal

# Minor-unit exponents from the ISO 4217 list (a subset for the demo).
EXPONENT = {"USD": 2, "EUR": 2, "JPY": 0, "KWD": 3}

def to_minor(amount: str, currency: str) -> int:
    """'25.30' USD -> 2530. Refuses floats and extra precision."""
    if not isinstance(amount, str):
        raise TypeError("pass amounts as strings, never floats")

    exp = EXPONENT[currency]
    value = Decimal(amount) * (10 ** exp)

    if value != value.to_integral_value():
        raise ValueError(f"{amount} has more precision than {currency} allows ({exp} decimals)")

    return int(value)

def fmt(minor: int, currency: str) -> str:
    """2530 USD -> '25.30 USD'."""
    exp = EXPONENT[currency]
    sign = "-" if minor < 0 else ""
    whole, frac = divmod(abs(minor), 10 ** exp)
    return f"{sign}{whole}.{frac:0{exp}d} {currency}" if exp else f"{sign}{whole} {currency}"
Enter fullscreen mode Exit fullscreen mode

Amounts enter as strings and go through Decimal, so "9.999" USD is rejected instead of silently rounded. In Postgres the column is bigint, which is enough for any realistic wallet.

Step 2. A schema that refuses unbalanced entries

CREATE TABLE currencies (
    code     char(3)  PRIMARY KEY,           -- ISO 4217 alphabetic code
    exponent smallint NOT NULL CHECK (exponent BETWEEN 0 AND 4)
);

INSERT INTO currencies VALUES ('USD', 2), ('EUR', 2), ('JPY', 0), ('KWD', 3);

CREATE TABLE accounts (
    id             bigserial PRIMARY KEY,
    name           text     NOT NULL UNIQUE,         -- e.g. 'liabilities:wallet:alice'
    currency       char(3)  NOT NULL REFERENCES currencies,
    normal_side    smallint NOT NULL CHECK (normal_side IN (1, -1)),  -- 1 = debit-normal, -1 = credit-normal
    allow_negative boolean  NOT NULL DEFAULT false,
    UNIQUE (id, currency)                            -- target for the postings FK below
);

CREATE TABLE transactions (
    id              bigserial PRIMARY KEY,
    kind            text NOT NULL,                   -- deposit, transfer, hold, reversal, adjustment
    status          text NOT NULL DEFAULT 'posted' CHECK (status IN ('pending', 'posted', 'voided')),
    idempotency_key text UNIQUE,
    request_hash    text,
    external_ref    text,                            -- bank reference, used by reconciliation
    reverses_id     bigint UNIQUE REFERENCES transactions,  -- a transaction can be reversed once
    created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE postings (
    id             bigserial PRIMARY KEY,
    transaction_id bigint  NOT NULL REFERENCES transactions,
    account_id     bigint  NOT NULL,
    currency       char(3) NOT NULL,
    amount         bigint  NOT NULL CHECK (amount <> 0),  -- minor units; debit > 0, credit < 0
    FOREIGN KEY (account_id, currency) REFERENCES accounts (id, currency)  -- no USD posting on a EUR account
);

CREATE INDEX ON postings (account_id);
CREATE INDEX ON postings (transaction_id);
Enter fullscreen mode Exit fullscreen mode

Two design choices to notice. The composite foreign key (account_id, currency) makes it impossible to post USD to a EUR account. And reverses_id is UNIQUE, so the database, not application code, guarantees a transaction is reversed at most once.

Now the core invariant. Postings are inserted one row at a time, so the zero-sum check cannot run after each row: the first row of every transfer would fail. PostgreSQL has a tool for exactly this: a constraint trigger declared DEFERRABLE INITIALLY DEFERRED runs at the end of the transaction instead of the end of the statement.

-- Invariant 1: every transaction has >= 2 postings and sums to zero per currency.
-- Checked at COMMIT (deferred), so postings can be inserted one by one.
CREATE OR REPLACE FUNCTION assert_balanced() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
    tx_id bigint;
    bad   record;
BEGIN
    IF TG_TABLE_NAME = 'postings' THEN tx_id := NEW.transaction_id; ELSE tx_id := NEW.id; END IF;

    IF (SELECT count(*) FROM postings WHERE transaction_id = tx_id) < 2 THEN
        RAISE EXCEPTION 'transaction % has fewer than 2 postings', tx_id;
    END IF;

    SELECT currency, sum(amount) AS total INTO bad
      FROM postings WHERE transaction_id = tx_id
     GROUP BY currency HAVING sum(amount) <> 0 LIMIT 1;

    IF FOUND THEN
        RAISE EXCEPTION 'transaction % is unbalanced: % % off', tx_id, bad.total, bad.currency;
    END IF;

    RETURN NULL;
END $$;

CREATE CONSTRAINT TRIGGER postings_balanced AFTER INSERT ON postings
    DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION assert_balanced();

CREATE CONSTRAINT TRIGGER transactions_balanced AFTER INSERT ON transactions
    DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION assert_balanced();
Enter fullscreen mode Exit fullscreen mode

The second trigger, on transactions, catches an entry that was inserted with no postings at all. Putting the rule in the database matters because a ledger outlives its application code: a migration script, an admin console or a hurried hotfix all go through the same check.

Step 3. Append-only: corrections are reversals

-- Invariant 2: the ledger is append-only. Postings never change.
CREATE OR REPLACE FUNCTION forbid_change() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    RAISE EXCEPTION '% on % is not allowed: post a reversal instead', TG_OP, TG_TABLE_NAME;
END $$;

CREATE TRIGGER postings_append_only BEFORE UPDATE OR DELETE ON postings
    FOR EACH ROW EXECUTE FUNCTION forbid_change();

CREATE TRIGGER transactions_no_delete BEFORE DELETE ON transactions
    FOR EACH ROW EXECUTE FUNCTION forbid_change();

-- The only allowed UPDATE on transactions: pending -> posted | voided.
CREATE OR REPLACE FUNCTION guard_status() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    IF OLD.status = 'pending' AND NEW.status IN ('posted', 'voided')
       AND (to_jsonb(NEW) - 'status') = (to_jsonb(OLD) - 'status') THEN
        RETURN NEW;
    END IF;
    RAISE EXCEPTION 'illegal transaction update: % -> %', OLD.status, NEW.status;
END $$;

CREATE TRIGGER transactions_status_only BEFORE UPDATE ON transactions
    FOR EACH ROW EXECUTE FUNCTION guard_status();
Enter fullscreen mode Exit fullscreen mode

Postings can never change. The only update allowed anywhere is a pending hold moving to posted or voided, and the guard compares the whole row as JSON to make sure nothing else changed with it. If a transfer was wrong, you post its mirror image as a new transaction (a reversal, or “storno” in accounting terms). The audit trail then shows both the mistake and the fix, with timestamps.

Step 4. Balance is a derived value

-- Balances are derived from postings, never stored as the source of truth.
-- Signed by the account's normal side, so a wallet (credit-normal liability) shows a positive balance.
CREATE VIEW balances AS
SELECT a.id, a.name, a.currency, a.allow_negative,
       COALESCE(sum(a.normal_side * p.amount) FILTER (WHERE t.status = 'posted'), 0)::bigint  AS posted,
       COALESCE(sum(a.normal_side * p.amount) FILTER (WHERE t.status = 'pending'), 0)::bigint AS pending,
       -- available = posted minus pending outflows; pending inflows are not spendable yet
       ( COALESCE(sum(a.normal_side * p.amount) FILTER (WHERE t.status = 'posted'), 0)
       + COALESCE(sum(LEAST(a.normal_side * p.amount, 0)) FILTER (WHERE t.status = 'pending'), 0) )::bigint AS available
  FROM accounts a
  LEFT JOIN postings p ON p.account_id = a.id
  LEFT JOIN transactions t ON t.id = p.transaction_id
 GROUP BY a.id;
Enter fullscreen mode Exit fullscreen mode

Three numbers per account:

  • posted: settled money.
  • pending: the net effect of open holds.
  • available: posted minus pending outflows. Pending inflows are not spendable until they settle. That asymmetry is the conservative choice (money on hold leaves at once, money on its way arrives only when it lands), and it is the rule the property test will catch being broken later.

Summing every posting on every read is O(n) per account. That is fine for a demo and for most early-stage apps with an index on account_id. A common optimization is a periodic balance snapshot, so a read sums only the postings after the last snapshot; the source of truth stays the same.

Step 5. One write path for every money movement

Every operation (deposit, transfer, hold, reversal, adjustment) goes through a single post() method:

def post(self, postings, *, kind, idempotency_key=None, status="posted",
         external_ref=None, reverses_id=None, lock=True, think_time=0.0) -> int:

    request_hash = hashlib.sha256(
        json.dumps([kind, sorted(postings), status, external_ref, reverses_id]).encode()
    ).hexdigest()

    with self.conn.transaction():
        accounts = {
            name: (acc_id, cur, side, allow_neg)
            for acc_id, name, cur, side, allow_neg in self.conn.execute(
                "SELECT id, name, currency, normal_side, allow_negative FROM accounts WHERE name = ANY(%s)",
                ([n for n, _ in postings],),
            )
        }

        # Accounts this transaction draws down and that must not go negative.
        guarded = sorted({accounts[n][0] for n, amt in postings
                          if accounts[n][2] * amt < 0 and not accounts[n][3]})

        if lock and guarded:
            # Lock in a fixed order (by id) so two opposite transfers cannot deadlock.
            self.conn.execute(
                "SELECT id FROM accounts WHERE id = ANY(%s) ORDER BY id FOR UPDATE", (guarded,)
            )

        tx_id = self.conn.execute(
            "INSERT INTO transactions (kind, status, idempotency_key, request_hash, external_ref, reverses_id) "
            "VALUES (%s, %s, %s, %s, %s, %s) ON CONFLICT (idempotency_key) DO NOTHING RETURNING id",
            (kind, status, idempotency_key, request_hash, external_ref, reverses_id),
        ).fetchone()

        if tx_id is None:  # same key seen before: replay, do not post twice
            prev_id, prev_hash = self.conn.execute(
                "SELECT id, request_hash FROM transactions WHERE idempotency_key = %s", (idempotency_key,)
            ).fetchone()
            if prev_hash != request_hash:
                raise IdempotencyConflict(f"key {idempotency_key!r} was used for a different request")
            return prev_id

        tx_id = tx_id[0]

        with self.conn.cursor() as cur:
            cur.executemany(
                "INSERT INTO postings (transaction_id, account_id, currency, amount) VALUES (%s, %s, %s, %s)",
                [(tx_id, accounts[n][0], accounts[n][1], amt) for n, amt in postings],
            )

        if guarded:
            time.sleep(think_time)  # stands in for a fraud check or an API call
            short = self.conn.execute(
                "SELECT name, available FROM balances WHERE id = ANY(%s) AND available < 0", (guarded,)
            ).fetchone()
            if short:
                raise InsufficientFunds(f"{short[0]} would have available balance {short[1]}")

        return tx_id
Enter fullscreen mode Exit fullscreen mode

The thin wrappers on top of it:

def deposit(self, wallet, bank, amount, *, bank_ref, key):
    return self.post([debit(bank, amount), credit(wallet, amount)],
                     kind="deposit", idempotency_key=key, external_ref=bank_ref)

def transfer(self, sender, recipient, amount, *, fee=0, fee_account="revenue:fees", key=None, **kw):
    legs = [debit(sender, amount + fee), credit(recipient, amount)]
    if fee:
        legs.append(credit(fee_account, fee))
    return self.post(legs, kind="transfer", idempotency_key=key, **kw)

def authorize(self, sender, recipient, amount, *, key=None):
    """Place a hold: pending postings reduce the sender's available balance only."""
    return self.post([debit(sender, amount), credit(recipient, amount)],
                     kind="hold", status="pending", idempotency_key=key)
Enter fullscreen mode Exit fullscreen mode

Here is a top-up, a $25.00 transfer with a $0.30 fee, and four things the ledger must refuse:

usd = lambda s: to_minor(s, "USD")

L.deposit("liabilities:wallet:alice", "assets:bank:usd", usd("100.00"), bank_ref="BNK-001", key="dep-1")
t = L.transfer("liabilities:wallet:alice", "liabilities:wallet:bob", usd("25.00"),
               fee=usd("0.30"), key="p2p-42")

for name, amount in conn.execute(
    "SELECT a.name, p.amount FROM postings p JOIN accounts a ON a.id = p.account_id "
    "WHERE p.transaction_id = %s ORDER BY p.id", (t,)
):
    side = "DR" if amount > 0 else "CR"
    print(f"tx {t}  {side}  {name:<26} {fmt(abs(amount), 'USD'):>10}")

total = conn.execute("SELECT sum(amount)::bigint FROM postings WHERE transaction_id = %s", (t,)).fetchone()[0]
print(f"sum of postings: {total}")
conn.commit()
Enter fullscreen mode Exit fullscreen mode
tx 2  DR  liabilities:wallet:alice    25.30 USD
tx 2  CR  liabilities:wallet:bob      25.00 USD
tx 2  CR  revenue:fees                 0.30 USD
sum of postings: 0

unbalanced: RaiseException: transaction 3 is unbalanced: 1 USD off
currency mix: RaiseException: transaction 4 is unbalanced: -100 JPY off
UPDATE posting: RaiseException: UPDATE on postings is not allowed: post a reversal instead
overdraft: InsufficientFunds: liabilities:wallet:bob would have available balance -500
Enter fullscreen mode Exit fullscreen mode

The overdraft message reports minor units: Bob had 2,500 cents and tried to send 3,000, so he would end at -500. Note the “currency mix” case: it does not violate the foreign key (each posting uses its own account’s currency), but it leaves 100 USD and -100 JPY, so the per-currency sum check rejects it. A real FX transfer needs four postings, two per currency, through an FX account.

Reversing the transfer posts a mirror entry, and a second reversal hits the UNIQUE constraint:

reversal tx 6 reverses tx 2
reverse twice: UniqueViolation: duplicate key value violates unique constraint "transactions_reverses_id_key"
liabilities:wallet:alice   posted  100.00 USD
liabilities:wallet:bob     posted    0.00 USD
revenue:fees               posted    0.00 USD
Enter fullscreen mode Exit fullscreen mode

Step 6. Concurrent transfers and the negative balance

Back to the race from the intro. race_demo.py gives Alice $100 and starts ten threads behind a threading.Barrier, each sending $30 through post(). With lock=False the code checks the available balance correctly, inside a transaction, after inserting its own postings. It still fails:

lock=False  succeeded=10  declined= 0  alice=-200.00 USD
lock=False  succeeded= 5  declined= 5  alice=-50.00 USD
lock=False  succeeded=10  declined= 0  alice=-200.00 USD
lock=False  succeeded= 9  declined= 1  alice=-170.00 USD
lock=False  succeeded=10  declined= 0  alice=-200.00 USD
Enter fullscreen mode Exit fullscreen mode

That is five runs with no artificial delay. PostgreSQL’s default isolation level is Read Committed, where each statement sees only data committed before it began. A thread that runs its check before the others commit sees only its own postings plus Alice’s $100, concludes she can afford $30, and commits. How many threads land in that window varies by run, which is why the second run declined five. The default run of the script adds a 50 ms pause between the check and the commit (standing in for a fraud-scoring call), which makes the overdraft reproducible:

lock=False  succeeded=10  declined= 0  alice=-200.00 USD
lock=True   succeeded= 3  declined= 7  alice=10.00 USD
Enter fullscreen mode Exit fullscreen mode

The fix is the SELECT ... FOR UPDATE in post(). A row lock blocks other SELECT FOR UPDATE statements on the same row until the holder’s transaction ends. The second transfer waits, and when it continues, its next statement sees the first transfer’s committed postings. With the lock and no delay, three runs:

lock=True   succeeded= 3  declined= 7  alice=10.00 USD
lock=True   succeeded= 3  declined= 7  alice=10.00 USD
lock=True   succeeded= 3  declined= 7  alice=10.00 USD
Enter fullscreen mode Exit fullscreen mode

Three details in that post() method are deliberate:

  • Lock order. Accounts are locked sorted by id. Alice-to-Bob and Bob-to-Alice at the same moment would otherwise deadlock; the PostgreSQL docs name acquiring locks in a consistent order as the best defense.
  • Lock only what is drawn down. The recipient and revenue:fees are not locked. Every paid transfer credits the fee account, so locking it would serialize your entire app on one row.
  • Locks on accounts, sums on postings. The account row works as a mutex even though no column on it changes.

Two alternatives are worth knowing. Running transfers at the SERIALIZABLE isolation level also prevents the anomaly, but the application must retry every transaction that fails with SQLSTATE 40001. Keeping a cached balance column and writing UPDATE ... SET balance = balance - 30 WHERE balance >= 30 is also safe, because Read Committed re-checks the WHERE clause against the updated row. It brings back a mutable balance, so treat it as a cache you verify against the postings, not as the truth.

Timeline of two concurrent 30 dollar transfers: without a row lock both read 100 and commit, leaving the wallet negative; with SELECT FOR UPDATE the second waits, re-reads the balance after the first commit and continues

Step 7. Idempotent transfers, briefly

Mobile clients retry. The transactions.idempotency_key column is UNIQUE, and post() inserts with ON CONFLICT DO NOTHING. A retry with the same key returns the original transaction id; the same key with a different payload is an error, which is also how Stripe’s API treats reused keys. Because the conflict is resolved by a unique index inside the same database transaction as the postings, there is no window where the key is saved but the money is not, or the other way round.

first call -> tx 8, retry -> tx 8
same key, other amount: IdempotencyConflict: key 'p2p-43' was used for a different request
Enter fullscreen mode Exit fullscreen mode

Key expiry, response caching and HTTP semantics deserve a separate article; for the ledger, a unique constraint next to the postings is the part that matters.

Step 8. Holds: pending to posted

Card-style flows and “request money” features need to reserve funds before moving them. TigerBeetle, which describes itself as a financial transactions database, models this as two-phase transfers: a pending transfer reserves funds, and it is later posted or voided. Our schema does the same with the status column: the postings of a hold are written once, and only the status moves.

h = L.authorize("liabilities:wallet:alice", "liabilities:wallet:bob", usd("50.00"), key="hold-7")

for acct in ("liabilities:wallet:alice", "liabilities:wallet:bob"):
    b_ = L.balance(acct)
    print(f"after hold    {acct:<26} posted {fmt(b_.posted, 'USD'):>10}  available {fmt(b_.available, 'USD'):>10}")

attempt("spend held money", lambda: L.transfer("liabilities:wallet:alice", "liabilities:wallet:bob", usd("45.00")))

L.capture(h)

for acct in ("liabilities:wallet:alice", "liabilities:wallet:bob"):
    b_ = L.balance(acct)
    print(f"after capture {acct:<26} posted {fmt(b_.posted, 'USD'):>10}  available {fmt(b_.available, 'USD'):>10}")

attempt("capture twice", lambda: L.capture(h))
Enter fullscreen mode Exit fullscreen mode
after hold    liabilities:wallet:alice   posted  90.00 USD  available  40.00 USD
after hold    liabilities:wallet:bob     posted  10.00 USD  available  10.00 USD
spend held money: InsufficientFunds: liabilities:wallet:alice would have available balance -500
after capture liabilities:wallet:alice   posted  40.00 USD  available  40.00 USD
after capture liabilities:wallet:bob     posted  60.00 USD  available  60.00 USD
capture twice: LedgerError: transaction 11 is not pending
Enter fullscreen mode Exit fullscreen mode

After the hold, Alice still has $90.00 posted but only $40.00 available, so a $45.00 transfer is declined. Bob’s available balance does not include the incoming $50.00 until capture. A second capture fails because the UPDATE ... WHERE status = 'pending' matches no row.

State diagram of a hold: pending can become posted on capture or voided on void or expiry, with Alice’s posted and available balances shown at each state from the demo run

What this demo leaves out: expiry. Stripe documents that an authorization for an online card payment is usually valid for 7 days, after which the funds are released. A production ledger needs a job that voids pending holds past their deadline, otherwise available balances shrink forever.

Step 9. Reconciliation against the bank statement

The ledger only proves internal consistency. Reconciliation proves it matches the outside world. Every transaction that touches assets:bank:usd carries the bank’s reference in external_ref, so matching is a dictionary comparison:

def reconcile(ours: dict[str, int], theirs: dict[str, int]) -> dict[str, list]:
    report = {"matched": [], "amount_mismatch": [], "missing_in_ledger": [], "missing_in_bank": []}
    for ref in sorted(ours.keys() | theirs.keys(), key=str):
        if ref not in theirs:
            report["missing_in_bank"].append((ref, ours[ref], None))
        elif ref not in ours:
            report["missing_in_ledger"].append((ref, None, theirs[ref]))
        elif ours[ref] != theirs[ref]:
            report["amount_mismatch"].append((ref, ours[ref], theirs[ref]))
        else:
            report["matched"].append((ref, ours[ref], theirs[ref]))
    return report
Enter fullscreen mode Exit fullscreen mode

The statement for our omnibus account:

date,bank_ref,amount,description
2026-10-27,BNK-101,100.00,ACH credit alice
2026-10-27,BNK-102,49.75,ACH credit bob
2026-10-28,BNK-104,250.00,Wire credit carol
2026-10-28,FEE-OCT,-15.00,Monthly account fee
Enter fullscreen mode Exit fullscreen mode
matched            BNK-101  ledger   100.00 USD   bank   100.00 USD
matched            BNK-104  ledger   250.00 USD   bank   250.00 USD
amount_mismatch    BNK-102  ledger    50.00 USD   bank    49.75 USD
missing_in_ledger  FEE-OCT  ledger            -   bank   -15.00 USD
missing_in_bank    BNK-103  ledger    75.00 USD   bank            -
net movement: ledger 475.00 USD, statement 384.75 USD

after booking FEE-OCT:
amount_mismatch    BNK-102  ledger    50.00 USD   bank    49.75 USD
missing_in_bank    BNK-103  ledger    75.00 USD   bank            -
gap 75.25 USD, explained by open items 75.25 USD
Enter fullscreen mode Exit fullscreen mode

Each bucket has its own playbook:

  • missing_in_ledger (FEE-OCT): the bank charged a fee you never booked. Post an adjusting entry (debit expenses:bank_fees, credit the bank), as the script does. Again a new entry, not an edit.
  • missing_in_bank (BNK-103): usually a timing difference, money in transit. Keep it open and expect it on tomorrow’s statement; escalate if it ages.
  • amount_mismatch (BNK-102): Bob’s $50.00 arrived as $49.75. Do not quietly change Bob’s wallet. Investigate first (a network fee? a partial return?), then post whatever correction the investigation supports.

The last line is the check finance actually cares about: after booking the fee, the $75.25 gap between ledger and bank is fully explained by open items. A gap that open items do not explain means money is missing or duplicated somewhere.

Reconciliation buckets with the action for each: matched, amount mismatch, missing in ledger, missing in bank, plus the waterfall from the 475.00 ledger movement to the 384.75 statement movement

Real statements complicate matching: one bank line can settle a batch of 500 payouts, and references get truncated. The bucket structure stays the same; the matching key becomes a batch id or a fuzzy rule.

Step 10. Tests, including property-based tests of the invariants

The unit tests (tests/test_ledger.py) pin down one behavior each: unbalanced and single-entry transactions are rejected, UPDATE and DELETE fail, overdrafts are declined, idempotent retries post once, reversals run once, holds behave, and ten concurrent transfers never overdraw. One test documents the bug itself: without the lock the balance goes negative.

Example-based tests only check the cases you thought of. Hypothesis can check rules instead. The first property: the ledger accepts a random list of postings if and only if it has at least two postings and they sum to zero.

@settings(max_examples=100, deadline=None, suppress_health_check=[HealthCheck.too_slow])
@given(st.lists(st.tuples(st.sampled_from(ACCOUNTS), st.integers(-10_000, 10_000).filter(bool)),
                min_size=1, max_size=5))
def test_ledger_accepts_exactly_the_balanced_transactions(legs):
    L = fresh_ledger(CONN)
    for name in ACCOUNTS:  # let any account go negative: this test is only about the zero-sum rule
        CONN.execute("UPDATE accounts SET allow_negative = true WHERE name = %s", (name,))
    CONN.commit()

    balanced = len(legs) >= 2 and sum(a for _, a in legs) == 0
    try:
        L.post(legs, kind="random")
        accepted = True
    except psycopg.errors.RaiseException:
        CONN.rollback()
        accepted = False

    assert accepted == balanced
Enter fullscreen mode Exit fullscreen mode

The second is a stateful test. Hypothesis generates sequences of deposits, transfers, holds, captures, voids and reversals, mirrors each in a plain-Python model, and after every step checks three invariants against the real database:

@invariant()
def every_transaction_sums_to_zero(self):
    bad = CONN.execute("SELECT transaction_id FROM postings GROUP BY transaction_id, currency "
                       "HAVING sum(amount) <> 0").fetchall()
    CONN.commit()
    assert bad == []

@invariant()
def accounting_equation_holds(self):
    # assets = liabilities + revenue, computed from the database
    b = {a: self.L.balance(a).posted for a in ACCOUNTS}
    assert b[BANK] == sum(b[w] for w in WALLETS) + b[FEES]

@invariant()
def database_matches_model_and_nobody_is_negative(self):
    for a in ACCOUNTS:
        bal = self.L.balance(a)
        assert bal.posted == self.posted[a]
        if a in WALLETS:
            assert bal.available == self.available(a) >= 0
Enter fullscreen mode Exit fullscreen mode

Full run, with Hypothesis statistics trimmed to the events:

tests/test_properties.py::test_ledger_accepts_exactly_the_balanced_transactions:
    - 100 passing, 0 failing, and 17 invalid test cases
tests/test_properties.py::test_ledger_state_machine::runTest:
    - 6 passing, 0 failing, and 0 invalid test cases
    - 54 passing, 0 failing, and 7 invalid test cases
      * 70.49%, transfer declined
      * 31.15%, hold placed
      * 27.87%, transfer posted
      * 21.31%, reversal posted
14 passed in 53.99s
Enter fullscreen mode Exit fullscreen mode

Does the property test catch real bugs? mutation_check.sh copies the schema, changes one expression so pending inflows count as available (drops the LEAST(..., 0)), and runs the state machine against it:

E               AssertionError: assert 1 == 0
E                +  where 1 = Balance(posted=0, pending=1, available=1).available
E                +  and   0 = available('liabilities:wallet:alice')
E                +    where available = LedgerMachine({'transfers': [], 'holds': [VarReference(name='holds_0')]}).available
E               Failing test case: <state machine>
E               state = LedgerMachine()
E               state.database_matches_model_and_nobody_is_negative()
E               state.deposit(amount=1, w='liabilities:wallet:bob')
E               state.database_matches_model_and_nobody_is_negative()
E               holds_0 = state.authorize(amount=1, dst='liabilities:wallet:alice', src='liabilities:wallet:bob')
E               state.database_matches_model_and_nobody_is_negative()
E               state.teardown()
1 failed, 1 deselected in 32.39s
Enter fullscreen mode Exit fullscreen mode

Hypothesis shrank the failure to two steps: deposit 1 cent to Bob, put a 1-cent hold from Bob to Alice. Alice now has 1 cent “available” that has not arrived. That is exactly the kind of bug that slips past hand-written examples, because nobody writes a test for a 1-cent hold.


Gotchas I hit while building this

  • sum(bigint) returns numeric. PostgreSQL widens the type to avoid overflow, so psycopg hands you a Decimal, and my f-string formatter crashed on it. The view casts back to bigint explicitly.
  • PL/pgSQL checks record fields inside the whole expression. My first trigger used CASE TG_TABLE_NAME WHEN 'postings' THEN NEW.transaction_id ELSE NEW.id END. On transactions it failed with record "new" has no field "transaction_id", even though that branch could never be taken there. Splitting it into an IF statement fixed it.
  • DDL per Hypothesis example is slow. With 200 examples, recreating the schema for each one took 82 seconds (49 with fsync=off). Creating it once and running TRUNCATE ... RESTART IDENTITY between examples took 17 seconds. The test cluster also runs with fsync=off, which is fine for throwaway test data and never for production.
  • Reversals can overdraw. If Bob already spent the $25.00, reversing Alice’s transfer would push his wallet negative. post() treats a reversal like any other entry, so it is declined; the test test_reversal_cannot_overdraw_recipient covers it. Your support flow needs a different answer (a receivable from Bob, a write-off), not a bypass.
  • Self-transfers. Alice-to-Alice with a fee is a balanced entry that only moves the fee. Reject it at the API layer; the property test skips it on purpose.

Production checklist

  • [ ] Integer minor units everywhere, currency exponent from data, amounts parsed from strings.
  • [ ] Zero-sum per currency enforced by the database at commit time.
  • [ ] No UPDATE or DELETE on postings; revoke the privileges too, not only triggers.
  • [ ] Balances derived from postings; snapshots for speed, verified against a full replay.
  • [ ] Row locks in a fixed order on the accounts being drawn down, or SERIALIZABLE with retries.
  • [ ] Idempotency key stored in the same transaction as the postings.
  • [ ] Holds with an expiry job and partial capture if your product needs it.
  • [ ] Daily reconciliation per external account, with aging on open items.
  • [ ] A property-based test of the invariants in CI, plus a planted-bug check that proves it can fail.

Wrapping up

A double entry ledger is not accounting ceremony. It is a data model where the most expensive payment bugs (double spending, lost retries, silent edits, numbers that disagree with the bank) become either impossible or loud. The database enforces the zero-sum rule, the code has one write path, and the tests check rules rather than examples. Double entry accounting software for businesses solves a different problem, but the core idea is the same and it scales down to a few hundred lines.

How does your team handle hot accounts like a fee or settlement account that every transfer touches: locks, sharding the account into sub-accounts, or batching postings?

Top comments (1)

Collapse
 
sunnatulla_dev profile image
Sunnatulla •

The append-only triggers are row-level, so TRUNCATE transactions CASCADE still wipes both tables with no error and no trace. I ran your two triggers in Postgres and it went straight through. A statement-level one closes it:

CREATE TRIGGER postings_no_truncate BEFORE TRUNCATE ON postings
  FOR EACH STATEMENT EXECUTE FUNCTION forbid_change();
Enter fullscreen mode Exit fullscreen mode

Same for transactions. Your forbid_change already works as a statement trigger (it prints "TRUNCATE on postings is not allowed"), so it's a two-line fix. I'd still revoke TRUNCATE from the app role too.