Double-entry ledger

The concurrency model behind every step here — snapshot reads, the optimistic commit guard, what FOR UPDATE records — is on Transactions & concurrency. This page shows the ledger; that page explains why it holds.

1. Schema


CREATE TABLE accounts (
  id        VARCHAR       PRIMARY KEY,
  owner     VARCHAR(120)  NOT NULL,
  currency  CHAR(3)       NOT NULL,
  balance   NUMERIC(18,2) NOT NULL DEFAULT 0,
  CHECK (balance >= 0)
);
 
CREATE TABLE journal (
  entry_id    VARCHAR       PRIMARY KEY,           -- caller-supplied, idempotency key
  posted_at   TIMESTAMP(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP,
  period      DATE          NOT NULL,              -- accounting period (first day of month)
  account_id  VARCHAR       NOT NULL REFERENCES accounts (id),
  amount      NUMERIC(18,2) NOT NULL,              -- +credit / -debit
  memo        VARCHAR(255),
  CHECK (amount <> 0)
);
 
CREATE INDEX ON journal (account_id);
CREATE INDEX ON journal (period);

What the schema buys you:

Declaration	Effect
`NUMERIC(18,2)`	Exact to the cent on every path — literal, bound parameter, `SUM`, division. `2.01 / 2` rounds to `1.01` like Postgres. since 0.1.43
`CHECK (balance >= 0)`	Enforced23514 on every `UPDATE`, including one staged inside a transaction.
`VARCHAR(120)` / `CHAR(3)`	Enforced22001 — an overlong value is refused, never truncated. since 0.1.43
`entry_id … PRIMARY KEY`	A second `INSERT` of the same entry is `23505`.
`REFERENCES accounts (id)`	Enforced23503 on `INSERT`: a journal line for an account that does not exist is refused, in the same transaction. Deleting an account that still has lines is not blocked — orphaning is the application’s decision, and `ON DELETE CASCADE` is refused at DDL with `0A000`.

2. Seed accounts


INSERT INTO accounts (id, owner, currency, balance) VALUES
  ('acc-alice', 'Alice', 'GBP', 500.00),
  ('acc-bob',   'Bob',   'GBP',  20.00);

3. The transfer

The shape is always the same: read both balances with FOR UPDATE, assert, write both legs, journal both legs, commit. If another transaction committed a write to either account between your read and your COMMIT, the commit fails with 40001 and nothing is written. You re-run the whole block.

psql


BEGIN;
 
SELECT id, balance
FROM accounts
WHERE id IN ('acc-alice', 'acc-bob')
FOR UPDATE;
-- inspect: alice must have >= 75.00
 
UPDATE accounts SET balance = balance - 75.00 WHERE id = 'acc-alice';
UPDATE accounts SET balance = balance + 75.00 WHERE id = 'acc-bob';
 
INSERT INTO journal (entry_id, period, account_id, amount, memo) VALUES
  ('tx-0001-d', DATE '2026-09-01', 'acc-alice', -75.00, 'rent share'),
  ('tx-0001-c', DATE '2026-09-01', 'acc-bob',    75.00, 'rent share');
 
COMMIT;   -- 40001 here → run the block again

If Alice does not have enough, CHECK (balance >= 0) fails the first UPDATE with 23514. Issue ROLLBACK and report the overdraft.

Python (psycopg)


import time
from decimal import Decimal
import psycopg
from psycopg import errors
 
DSN = "postgresql://dk_XXXX:%s@pg.uk-lon-1.dodil.io:5432/kb-prod?sslmode=require"
 
def transfer(conn, tx_id, src, dst, amount: Decimal, period, memo, attempts=5):
    for attempt in range(attempts):
        try:
            with conn.transaction():
                cur = conn.execute(
                    "SELECT id, balance FROM accounts WHERE id IN (%s, %s) FOR UPDATE",
                    (src, dst),
                )
                balances = dict(cur.fetchall())
                if balances[src] < amount:
                    raise ValueError(f"{src} has {balances[src]}, needs {amount}")
 
                conn.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (amount, src))
                conn.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, dst))
                conn.execute(
                    "INSERT INTO journal (entry_id, period, account_id, amount, memo) "
                    "VALUES (%s, %s, %s, %s, %s), (%s, %s, %s, %s, %s)",
                    (f"{tx_id}-d", period, src, -amount, memo,
                     f"{tx_id}-c", period, dst,  amount, memo),
                )
            return  # committed
        except errors.SerializationFailure:      # SQLSTATE 40001
            time.sleep(0.05 * (2 ** attempt))
        except errors.UniqueViolation:            # 23505: this tx_id was already posted
            return                                # idempotent re-run — nothing to do
        except errors.CheckViolation as e:        # 23514: overdraft
            raise ValueError("insufficient funds") from e
    raise RuntimeError("transfer did not commit after retries")
 
with psycopg.connect(DSN % SECRET) as conn:
    transfer(conn, "tx-0001", "acc-alice", "acc-bob", Decimal("75.00"),
             "2026-09-01", "rent share")

Two things the retry loop relies on: psycopg.errors.SerializationFailure is the driver’s class for 40001, and conn.transaction() rolls back on any exception, so a failed attempt leaves nothing staged.

What each line is doing for correctness:

Line	Why it is there
`FOR UPDATE`	Records the two account keys. A concurrent committed write to either account makes this transaction’s `COMMIT` fail `40001` instead of committing a stale arithmetic result. No lock is held, so other transfers on other accounts are never blocked.
Two `UPDATE`s	Both are guarded on the keys they write, so even without the `FOR UPDATE` a lost update cannot commit. The read guard is what protects the decision made from the balances.
Journal `INSERT`s	Both legs commit or neither does. Cross-table transactions are atomic. The `REFERENCES` probe runs here: an unknown `account_id` fails the block with `23503`, and a concurrent write to the referenced account row fails it with `40001`.
`entry_id`	On a retried call that already committed, the `INSERT` hits `23505`. Treat that as success.

4. Trial balance

SUM over NUMERIC(18,2) is exact, so a ledger that is in balance sums to exactly 0.00, not to -1e-13.


-- Whole ledger: must be 0.00
SELECT SUM(amount) AS imbalance FROM journal;
 
-- Per account: journal must agree with the running balance
SELECT a.id,
       a.balance,
       COALESCE(SUM(j.amount), 0) AS from_journal,
       a.balance - COALESCE(SUM(j.amount), 0) AS drift
FROM accounts a
LEFT JOIN journal j ON j.account_id = a.id
GROUP BY a.id, a.balance
HAVING a.balance - COALESCE(SUM(j.amount), 0) <> 0;

The second query returns no rows on a healthy ledger. Seeding balances directly, as step 2 did, is the one legitimate source of drift; post an opening-balance entry instead if you want the check to hold from day one.

Averages and allocations stay exact too. SUM(amount) / COUNT(*) on a NUMERIC column is decimal division with at least 16 fractional digits, and a CAST(… AS NUMERIC(18,2)) rounds half away from zero. since 0.1.43

5. Close a period

Once September is reconciled, seal it. ADD IMMUTABLE <name> CHECK (<predicate>) marks every row matching the predicate as sealed. Any UPDATE or DELETE that touches a sealed row fails with 23P90, and an INSERT into the sealed range is refused the same way.


ALTER TABLE journal
  ADD IMMUTABLE closed_2026_09 CHECK (period = DATE '2026-09-01');


-- Later: someone tries to "fix" a September entry
UPDATE journal SET amount = -70.00 WHERE entry_id = 'tx-0001-d';
-- ERROR:  … sealed by immutable rule closed_2026_09   (SQLSTATE 23P90)

Corrections to a closed period are new entries in the open period, which is how a ledger is meant to work. 23P90 is a distinct code from 23514 so your application can tell “closed period” from “bad value”.

6. Idempotent posting from an API handler

An HTTP handler that posts a transfer will be retried by its caller. The entry_id primary key makes that safe:

The first call commits both legs.
A retry of the same tx_id runs the same block; its journal INSERT fails 23505 before COMMIT, the transaction rolls back, and the handler returns the original success.
A retry that lost a 40001 race simply runs again.

Never catch 23505 around the UPDATEs alone: the guard is on the journal row. If you want the account updates skipped without a round trip, check SELECT 1 FROM journal WHERE entry_id = %s first, inside the same transaction, and return early. Both patterns are on Idempotent writes & retries.

What you cannot lean on

Postgres habit	Here
`ON DELETE CASCADE` / `SET NULL` / `DEFERRABLE`	Refused at DDL with `0A000`. The `INSERT` side of a foreign key is enforced; the delete side is yours: check for journal lines before deleting an account, in the same transaction.
Waiting on a row lock	Nothing waits. Two transfers touching the same account both run; the second to commit fails `40001`. Retry.
`SERIAL` journal ids	Accepted, but a generated id cannot be an idempotency key. Supply `entry_id` from the caller.
`BEFORE INSERT` trigger to stamp the period	Trigger DDL is refused with `0A000` and a pointer to alternatives. Compute `period` in the application, or default it with `CURRENT_TIMESTAMP` and derive on read.