PostgreSQL patterns for payment ledgers.

A payment ledger is a database that must be right in a way most application databases do not. A wrong number in a profile page is an annoyance; a wrong number in a ledger is a discrepancy someone has to explain to an auditor. PostgreSQL gives you the tools to make many kinds of wrongness impossible, but only if you design with them in mind.

This post collects the patterns we use: how to store amounts, how to model movements, how to keep transactions balanced, how to choose isolation and locking, and how to index for the queries that matter. The examples target PostgreSQL, though the ideas are portable.

Money is an integer

Floating-point numbers cannot represent most decimal fractions exactly, so using them for money guarantees eventual rounding errors. The Money pattern ^[7] captures the idea: an amount is a quantity plus a currency, and arithmetic respects both. In practice, store integer minor units and keep the currency or token beside them.

Tokens are not all six decimals

A stablecoin can use different decimals on different networks, and the token standard treats the value as informational ^[9]. Store integer token units and the decimals in effect, and never format or round in the database.

CREATE DOMAIN token_units AS numeric(78,0) CHECK (VALUE >= 0);

CREATE TABLE assets (
  id       text PRIMARY KEY,            -- e.g. 'usdc:example-network'
  symbol   text NOT NULL,
  decimals smallint NOT NULL CHECK (decimals BETWEEN 0 AND 36)
);

CREATE TABLE payments (
  id           uuid PRIMARY KEY,
  asset_id     text NOT NULL REFERENCES assets(id),
  amount_units token_units NOT NULL CHECK (amount_units > 0)
);

The ledger: append-only, balanced entries

A balance is a derived fact. The primary fact is a movement. Model every change of value as one or more immutable entries, and compute balances by summing them ^[8]. This is double-entry thinking: each transaction contains entries that sum to zero, so the ledger cannot silently gain or lose value.

CREATE TABLE accounts (
  id       uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  code     text NOT NULL UNIQUE,         -- e.g. 'assets:received', 'liabilities:orders', 'suspense'
  asset_id text NOT NULL REFERENCES assets(id)
);

CREATE TABLE ledger_transactions (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ref         text NOT NULL UNIQUE,      -- idempotency key, e.g. 'payment:confirmed:<payment_id>'
  description text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE ledger_entries (
  id             bigserial PRIMARY KEY,
  transaction_id uuid   NOT NULL REFERENCES ledger_transactions(id),
  account_id     uuid   NOT NULL REFERENCES accounts(id),
  amount_units   numeric(78,0) NOT NULL,   -- signed: debits positive, credits negative
  created_at     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ledger_entries_account ON ledger_entries (account_id, id);

• Entries are never updated or deleted. A mistake is corrected by a new, reversing transaction, so the history of the error is preserved.
• The transaction `ref` is an idempotency key. Re-processing the same business event inserts nothing new.
• Sign carries direction. A single signed column keeps the arithmetic trivial.

Enforcing that every transaction balances

Balance is an invariant across several rows, which a simple CHECK cannot express. A deferred constraint trigger, evaluated at commit, can verify that each transaction's entries sum to zero ^[2].

CREATE FUNCTION assert_balanced() RETURNS trigger AS $$
DECLARE total numeric;
BEGIN
  SELECT COALESCE(SUM(amount_units), 0) INTO total
  FROM ledger_entries WHERE transaction_id = NEW.transaction_id;
  IF total <> 0 THEN
    RAISE EXCEPTION 'ledger transaction % is unbalanced by %', NEW.transaction_id, total;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

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

Why deferred

Entries for one transaction are inserted one at a time, so the sum is only correct once all are in. A deferred constraint trigger runs at commit, when the whole transaction is visible, and aborts the commit if the books do not balance.

BEGIN;
  INSERT INTO ledger_transactions (ref, description)
  VALUES ('payment:confirmed:7f3a', 'Payment confirmed for order 1042')
  RETURNING id \gset tx_

  INSERT INTO ledger_entries (transaction_id, account_id, amount_units) VALUES
    (:'tx_id', (SELECT id FROM accounts WHERE code='assets:received'),    +25000000),  -- debit
    (:'tx_id', (SELECT id FROM accounts WHERE code='liabilities:orders'), -25000000);  -- credit
COMMIT;

Balances: derive, cache, and verify

Summing all entries on every read does not scale, and a stored balance can drift. The safe pattern is to treat the sum as the truth and a cached balance as an optimisation that is continually verified against it.

SELECT a.code, b.balance_units AS cached, s.sum_units AS actual
FROM   account_balances b
JOIN   accounts a ON a.id = b.account_id
JOIN  (SELECT account_id, SUM(amount_units) AS sum_units
       FROM ledger_entries GROUP BY account_id) s ON s.account_id = b.account_id
WHERE  b.balance_units <> s.sum_units;      -- any row returned is a bug or corruption

Isolation levels and the anomalies they prevent

PostgreSQL's default isolation level is read committed, under which each statement sees data committed before it began. For many operations that is fine; for balance checks it is not, because two concurrent transactions can each read the same balance and both decide to spend it ^[3].

async function serializable<T>(fn: (tx: Tx) => Promise<T>, attempts = 5): Promise<T> {
  for (let i = 0; i < attempts; i++) {
    try {
      return await db.transaction(fn, { isolationLevel: "serializable" });
    } catch (e: any) {
      if (e.code === "40001" && i < attempts - 1) continue;   // serialization_failure: retry
      throw e;
    }
  }
  throw new Error("unreachable");
}

Locking when isolation is not enough

Serialisable isolation is powerful but retries can be costly on hot rows. For the common case of "check a balance, then spend it", an explicit row lock is often simpler and predictable ^[4].

BEGIN;
  SELECT balance_units FROM account_balances WHERE account_id = $1 FOR UPDATE;
  -- application checks balance_units >= amount, then inserts entries
  -- concurrent spenders wait here until this transaction commits
COMMIT;

Lock in a consistent order

When a transaction locks several rows, always lock them in the same order everywhere. Inconsistent ordering is the classic cause of deadlocks.

Idempotency at the ledger layer

Business events get processed more than once: webhooks are redelivered, workers retry, an operator clicks twice. The ledger should be the place that makes repeated processing harmless. The unique ref on ledger_transactions does this.

INSERT INTO ledger_transactions (ref, description)
VALUES ('payment:confirmed:7f3a', 'Payment confirmed for order 1042')
ON CONFLICT (ref) DO NOTHING
RETURNING id;
-- If no row is returned, this event was already posted: skip inserting entries.

Indexes for the queries that matter

Reconciliation and audit have predictable questions. Index for them rather than guessing ^[5].

CREATE INDEX payments_open_by_age
  ON payments (created_at)
  WHERE state IN ('created','seen','confirming','underpaid');   -- terminal states excluded

Growing up: partitioning and archiving

Append-only ledgers grow forever. Table partitioning ^[6] by time keeps recent data fast and makes archiving old data a metadata operation rather than a massive delete.

• Partition entries by month. Queries for recent activity touch only recent partitions.
• Never delete; detach and archive. Move old partitions to cheaper storage while keeping totals verifiable.
• Keep constraints in mind. Unique constraints on partitioned tables must include the partition key; design idempotency keys accordingly.
• Test the verification job on partitions. Balance checks must still cover the whole history.

A checklist for a ledger you can defend

None of this is novel; it is the accumulated practice of people who have had to explain a discrepancy. The value is in applying it early, in the schema, where it makes an entire class of bug impossible to write.