Payments and Banking: Double-Entry Ledgers

CREATE TABLE account (
  id bigint PRIMARY KEY, owner_id bigint NOT NULL, currency char(3) NOT NULL,
  kind text NOT NULL CHECK (kind IN ('customer','merchant','fees','settlement')),
  balance_minor bigint NOT NULL DEFAULT 0,
  allow_negative boolean NOT NULL DEFAULT false,
  CHECK (allow_negative OR balance_minor >= 0)
);
CREATE TABLE journal_txn (
  id uuid PRIMARY KEY, kind text NOT NULL, ref text, created_at timestamptz NOT NULL DEFAULT now(),
  idempotency_key text UNIQUE
);
CREATE TABLE ledger_entry (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  txn_id uuid NOT NULL REFERENCES journal_txn(id),
  account_id bigint NOT NULL REFERENCES account(id),
  amount_minor bigint NOT NULL CHECK (amount_minor <> 0),
  currency char(3) NOT NULL
);
CREATE INDEX ON ledger_entry (account_id, id);
REVOKE UPDATE, DELETE ON ledger_entry, journal_txn FROM app_rw;   -- append-only

-- transfer 500.00 INR from account 1 to 2, deadlock-safe and balanced
BEGIN;
SELECT id FROM account WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
INSERT INTO journal_txn (id, kind, idempotency_key) VALUES ('0192f0c1-8a2e-7c11-b3a4-5d6e7f809a1b', 'transfer', 'tr-7781');
INSERT INTO ledger_entry (txn_id, account_id, amount_minor, currency) VALUES
  ('0192f0c1-8a2e-7c11-b3a4-5d6e7f809a1b', 1, -50000, 'INR'),
  ('0192f0c1-8a2e-7c11-b3a4-5d6e7f809a1b', 2,  50000, 'INR');
UPDATE account SET balance_minor = balance_minor - 50000 WHERE id = 1;  -- CHECK rejects overdraft
UPDATE account SET balance_minor = balance_minor + 50000 WHERE id = 2;
COMMIT;

-- invariant check (should return no rows)
SELECT txn_id FROM ledger_entry GROUP BY txn_id HAVING sum(amount_minor) <> 0;