金融科技初创公司的复式记账账本：最小但仍然正确的版本

CREATE TABLE ledger_accounts (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  owner    TEXT NOT NULL,   -- 'user:42', 'provider:acme', 'platform'
  kind     TEXT NOT NULL,   -- 'user_wallet', 'provider_clearing', 'fee_revenue'
  currency TEXT NOT NULL,   -- 'USD', 'EUR', 'BTC': one currency per account
  UNIQUE (owner, kind, currency)
);

CREATE TABLE ledger_transactions (
  id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  idempotency_key TEXT        NOT NULL UNIQUE,
  kind            TEXT        NOT NULL,   -- 'deposit', 'withdrawal_request', 'fee'
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  metadata        JSONB       NOT NULL DEFAULT '{}'
);

CREATE TABLE ledger_entries (
  id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  transaction_id BIGINT NOT NULL REFERENCES ledger_transactions (id),
  account_id     BIGINT NOT NULL REFERENCES ledger_accounts (id),
  amount         BIGINT NOT NULL CHECK (amount <> 0)  -- minor units: + credit, - debit
);
CREATE INDEX ledger_entries_account_idx     ON ledger_entries (account_id);
CREATE INDEX ledger_entries_transaction_idx ON ledger_entries (transaction_id);

-- Zero-sum rule, checked per currency. Deferred, so it runs at COMMIT,
-- after every entry of the transaction has been inserted.
CREATE FUNCTION ledger_assert_balanced() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF EXISTS (
    SELECT 1
    FROM ledger_entries e
    JOIN ledger_accounts a ON a.id = e.account_id
    WHERE e.transaction_id = NEW.transaction_id
    GROUP BY a.currency
    HAVING sum(e.amount) <> 0
  ) THEN
    RAISE EXCEPTION 'ledger transaction % does not sum to zero', NEW.transaction_id;
  END IF;
  RETURN NULL;
END;
$$;

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

-- The application role can append and read. It cannot change history.
GRANT SELECT, INSERT ON ledger_transactions, ledger_entries TO app_role;
GRANT SELECT, INSERT, UPDATE ON ledger_accounts TO app_role;  -- UPDATE allows row locks
REVOKE UPDATE, DELETE, TRUNCATE ON ledger_transactions, ledger_entries FROM app_role;

CREATE FUNCTION ledger_request_withdrawal(
  p_key TEXT, p_wallet BIGINT, p_pending BIGINT, p_amount BIGINT
) RETURNS BIGINT
LANGUAGE plpgsql AS $$
DECLARE
  v_tx      BIGINT;
  v_balance NUMERIC;
BEGIN
  IF p_amount <= 0 THEN
    RAISE EXCEPTION 'withdrawal amount must be positive';
  END IF;

  -- A second withdrawal on this wallet waits here until the first commits.
  PERFORM 1 FROM ledger_accounts WHERE id = p_wallet FOR UPDATE;

  INSERT INTO ledger_transactions (idempotency_key, kind)
  VALUES (p_key, 'withdrawal_request')
  ON CONFLICT (idempotency_key) DO NOTHING
  RETURNING id INTO v_tx;

  IF v_tx IS NULL THEN   -- a retry: this request was already posted
    RETURN (SELECT id FROM ledger_transactions WHERE idempotency_key = p_key);
  END IF;

  SELECT COALESCE(sum(amount), 0) INTO v_balance
  FROM ledger_entries WHERE account_id = p_wallet;

  IF v_balance < p_amount THEN
    RAISE EXCEPTION 'insufficient funds';   -- rolls back the row inserted above
  END IF;

  INSERT INTO ledger_entries (transaction_id, account_id, amount) VALUES
    (v_tx, p_wallet,  -p_amount),
    (v_tx, p_pending,  p_amount);

  RETURN v_tx;
END;
$$;