Ledger Schemas, Accounts, Balances and Holds

CREATE TABLE accounts (id bigserial PRIMARY KEY, type text NOT NULL, currency char(3) NOT NULL,
  owner_id bigint, status text NOT NULL DEFAULT 'active');
CREATE TABLE journal_entries (id bigserial PRIMARY KEY, idempotency_key text UNIQUE NOT NULL, kind text NOT NULL,
  effective_at timestamptz NOT NULL DEFAULT now(), external_ref text, metadata jsonb);
CREATE TABLE postings (id bigserial PRIMARY KEY, entry_id bigint NOT NULL REFERENCES journal_entries,
  account_id bigint NOT NULL REFERENCES accounts, amount_minor bigint NOT NULL CHECK (amount_minor <> 0), currency char(3) NOT NULL);
CREATE TABLE balances (account_id bigint PRIMARY KEY REFERENCES accounts, posted bigint NOT NULL DEFAULT 0,
  held bigint NOT NULL DEFAULT 0, version bigint NOT NULL DEFAULT 0);

-- the journal is append-only: no UPDATE or DELETE for the application role
REVOKE UPDATE, DELETE ON journal_entries, postings FROM app_rw;

-- post an entry atomically: lock the affected balances in a fixed order (avoids deadlocks), check funds, write
BEGIN;
SELECT account_id, posted, held FROM balances WHERE account_id = ANY($accounts) ORDER BY account_id FOR UPDATE;
-- application checks: posted - held + delta >= 0 for debited customer accounts
INSERT INTO journal_entries (idempotency_key, kind, external_ref) VALUES ($key, 'p2p_transfer', $ref) RETURNING id;
INSERT INTO postings (entry_id, account_id, amount_minor, currency) VALUES ($e, $ada, -502500, 'NGN'), ($e, $bayo, 500000, 'NGN'), ($e, $fees, 2500, 'NGN');
UPDATE balances SET posted = posted + d.delta, version = version + 1 FROM (VALUES ($ada, -502500), ($bayo, 500000), ($fees, 2500)) d(id, delta) WHERE account_id = d.id;
COMMIT;