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
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.
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}"
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);
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();
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();
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;
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
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)
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()
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
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
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
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
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
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:feesare 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.
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
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))
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
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.
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
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
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
Each bucket has its own playbook:
-
missing_in_ledger(FEE-OCT): the bank charged a fee you never booked. Post an adjusting entry (debitexpenses: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.
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
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
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
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
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 aDecimal, and my f-string formatter crashed on it. The view casts back tobigintexplicitly. -
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. Ontransactionsit failed withrecord "new" has no field "transaction_id", even though that branch could never be taken there. Splitting it into anIFstatement 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 runningTRUNCATE ... RESTART IDENTITYbetween examples took 17 seconds. The test cluster also runs withfsync=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 testtest_reversal_cannot_overdraw_recipientcovers 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
UPDATEorDELETEon 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
SERIALIZABLEwith 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)
The append-only triggers are row-level, so
TRUNCATE transactions CASCADEstill 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: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.