Designing a Ledger Database for Payments and Balances

The short answer

A reliable payment ledger rests on five rules:

1. Double entry: every operation moves money between at least two accounts, and its entries sum to zero.
2. Immutability: records are never updated or deleted; mistakes are fixed with a new reversing transaction.
3. Integers only: amounts are bigint in the smallest unit — tiyin or cents — never float.
4. Idempotency: a repeated callback from Payme, Click or Uzum cannot create a second payment.
5. Locking: two parallel purchases cannot both spend the same balance.

A simple users.balance column that you increase and decrease meets none of these rules: you cannot explain where a number came from or prove it is correct.

The schema

CREATE TABLE accounts (
  id          bigserial PRIMARY KEY,
  owner_id    bigint,
  kind        text   NOT NULL,              -- user_wallet, provider_clearing, revenue
  currency    char(3) NOT NULL DEFAULT 'UZS',
  balance     bigint NOT NULL DEFAULT 0,    -- cached, in tiyin
  allow_negative boolean NOT NULL DEFAULT false
);

CREATE TABLE ledger_transactions (
  id              bigserial PRIMARY KEY,
  idempotency_key text NOT NULL UNIQUE,
  kind            text NOT NULL,            -- topup, purchase, refund, reversal
  created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE ledger_entries (
  id             bigserial PRIMARY KEY,
  transaction_id bigint NOT NULL REFERENCES ledger_transactions(id),
  account_id     bigint NOT NULL REFERENCES accounts(id),
  amount         bigint NOT NULL CHECK (amount <> 0)  -- + increases, - decreases
);

Here a simplified sign convention is used: a positive amount increases the account balance, a negative one decreases it. In every transaction SUM(amount) = 0 — check this in the same database transaction that writes the entries, or with a deferred constraint trigger.

A top-up of 50,000 sum through a provider becomes two entries: −5,000,000 on the provider_clearing account and +5,000,000 on the user wallet. Money did not appear from nowhere: the clearing account shows how much the provider owes you until settlement. That is why system accounts like this one have allow_negative = true, while user wallets do not.

Money as integers

• float cannot represent many decimal fractions exactly, and errors accumulate in sums.
• numeric is exact but slower and tempts people to store fractions of tiyin.
• bigint in tiyin is exact, fast and simple. Format to sums only when displaying.

Providers may send amounts in different units: some in tiyin, some in sums with a fractional part. Check each provider's documentation and convert once, at the integration boundary.

Immutable records

• Revoke UPDATE and DELETE on ledger tables from the application role, or block them with a trigger.
• A refund is a new transaction with mirrored entries that references the original.
• Statuses (pending, completed, cancelled) belong to the payment object, not to ledger entries. Write to the ledger only what has actually happened with money.

Idempotent callbacks

Payment providers repeat requests on timeouts and network errors, and the same operation may arrive several times. Build the key from the provider and its transaction id:

INSERT INTO ledger_transactions (idempotency_key, kind)
VALUES ('payme:6720f1c2a9', 'topup')
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING id;

If nothing is returned, the operation was already processed: find it and send the provider the same response as the first time. Each provider protocol has its own steps (check, create, perform, cancel), and each step must be safe to repeat.

Locking against double spending

BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;
-- if balance < 1500000: ROLLBACK and return "insufficient funds"
INSERT INTO ledger_transactions (idempotency_key, kind)
VALUES ('order:981', 'purchase') RETURNING id;          -- :tx
INSERT INTO ledger_entries (transaction_id, account_id, amount)
VALUES (:tx, 42, -1500000), (:tx, 7, 1500000);
UPDATE accounts SET balance = balance - 1500000 WHERE id = 42;
UPDATE accounts SET balance = balance + 1500000 WHERE id = 7;
COMMIT;

• FOR UPDATE makes a second parallel purchase wait until the first one commits and then see the new balance.
• Lock several accounts in a fixed order (for example, by id) to avoid deadlocks.
• A system account such as revenue becomes a hot row. Options: do not cache its balance and compute it from entries, or split it into several sub-accounts.
• An alternative is the SERIALIZABLE isolation level with retries on serialization errors.

Balances and reconciliation

The cached balance is a convenience; the source of truth is the sum of entries. Regularly check that they match:

SELECT a.id, a.balance, COALESCE(SUM(e.amount), 0) AS from_entries
FROM accounts a LEFT JOIN ledger_entries e ON e.account_id = a.id
GROUP BY a.id HAVING a.balance <> COALESCE(SUM(e.amount), 0);

For large volumes, store periodic balance snapshots and sum only newer entries.

Reconciliation compares your ledger with provider reports: every provider transaction must have exactly one ledger transaction with the same amount. Sort the differences into “missing in ledger”, “missing at provider” and “amount mismatch”, and resolve them only with new correcting transactions.