Record every money movement in an append-only ledger

How to do it

Each row holds the time, type, signed amount in minor units, currency, order ID and provider object ID. A unique constraint on the provider ID stops double entries. Corrections are new rows that reverse old ones. Balances are sums of rows. Give the app's database role only INSERT and SELECT on the ledger.

Example

CREATE TABLE ledger_entries (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  occurred_at  timestamptz NOT NULL,
  entry_type   text NOT NULL CHECK (entry_type IN ('charge', 'refund', 'fee', 'dispute', 'dispute_reversal', 'payout', 'adjustment')),
  amount_minor bigint NOT NULL,
  currency     char(3) NOT NULL,
  order_id     text,
  provider_id  text NOT NULL UNIQUE
);
GRANT SELECT, INSERT ON ledger_entries TO app_role;

How to check

As the app role, run an UPDATE and a DELETE on the ledger. Both fail with permission denied. For every order, the sum of its charge and refund rows equals the paid amount minus refunds on the order.