Should you store an account balance or calculate it from transactions?

Store both, with one rule deciding which copy wins. Record every movement as a row in an append-only ledger table, which is the source of truth, and keep a cached balance column on the account that is updated in the same database transaction as the ledger insert, using a single atomic UPDATE. Reads stay cheap, every number can be explained line by line, and a reconciliation query catches the cache if it ever drifts.

Computing the balance on every read is fine when an account's history stays small, such as a till drawer for one shift or a student's fee account. A bare balance column with no ledger behind it is only safe when nobody will ever ask why the number is what it is. In the drawDB gallery, 18 schemas hold a balance of money, credits or stock: 8 store both, 6 compute from the ledger, and 4 keep a counter alone.

How do real schemas store balances?

We went through every schema in the gallery that tracks a quantity which goes up and down: money in a wallet, cash in a drawer, units on a shelf, hours of leave. Each one falls into one of three patterns.

The split follows the stakes. Every schema where a balance can be spent, withdrawn or audited by a regulator keeps a ledger. The cached column appears wherever a balance is checked on a hot path: the ATM authorising a withdrawal, the order screen showing buying power, a game client spending gems. The counters are a Twitch bit balance, a fleet's spare-parts shelf, product stock, and a billing customer's running balance, where losing the history costs a support ticket rather than an audit finding.

The schema notes say who wins in plain words. banking-ledger describes accounts.balance_minor as "Cached balance… The ledger, not this column, is the source of truth." warehouse-inventory calls stock_levels "a materialised view of the movement log… any disagreement is resolved in favour of the movements." That sentence is the design. Write it down before writing the column.

When is computing the balance from transactions enough?

When the set of rows you sum is bounded. The retail-pos cash_movements note says it directly: the expected drawer balance "is one SUM over the shift". A shift has dozens of rows. A student's fee_ledger grows by a handful of rows a term. An index on (account_id, created_at) makes these sums trivial, and there is no cache to keep honest.

SELECT COALESCE(SUM(amount_minor), 0) AS balance_minor
FROM wallet_entries
WHERE wallet_id = 1;

It stops being enough when an account accumulates years of entries and the balance is read on every request, or when two concurrent spends both need to see the same balance and agree on whether there is enough. Summing does not lock anything. Two transactions can each sum 100, each decide 70 is affordable, and each insert a -70 entry. A ledger without a row to lock has no natural place to serialise spends, which is the strongest argument for the cached column.

If you want compute-only at scale, checkpoint it. banking-ledger freezes a statements row per period with opening_balance_minor and closing_balance_minor, so a balance is the last closing balance plus the entries since, never a scan of ten years.

What does a ledger with a cached balance look like?

Here are the two core tables of the venmo-p2p schema, as drawDB's PostgreSQL exporter writes them (column comments removed):

CREATE TABLE IF NOT EXISTS "wallets" (
	"id" BIGINT GENERATED BY DEFAULT AS IDENTITY,
	"user_id" BIGINT NOT NULL,
	"currency_code" CHAR(3) NOT NULL,
	"balance_minor" BIGINT NOT NULL DEFAULT 0,
	"pending_out_minor" BIGINT NOT NULL DEFAULT 0,
	"status" VARCHAR(16) NOT NULL DEFAULT 'active',
	"created_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
	PRIMARY KEY("id")
);

CREATE TABLE IF NOT EXISTS "wallet_entries" (
	"id" BIGINT GENERATED BY DEFAULT AS IDENTITY,
	"wallet_id" BIGINT NOT NULL,
	"entry_type" VARCHAR(20) NOT NULL,
	"transfer_id" BIGINT,
	"funding_transfer_id" BIGINT,
	"amount_minor" BIGINT NOT NULL,
	"balance_after_minor" BIGINT NOT NULL,
	"created_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
	PRIMARY KEY("id")
);

CREATE UNIQUE INDEX "uq_wallets_user_currency"
ON "wallets" ("user_id", "currency_code");
CREATE INDEX "idx_wallet_entries_wallet_created"
ON "wallet_entries" ("wallet_id", "created_at");
ALTER TABLE "wallet_entries"
ADD FOREIGN KEY("wallet_id") REFERENCES "wallets"("id")
ON UPDATE NO ACTION ON DELETE RESTRICT;

Four decisions are worth copying:

• Signed integer amounts in minor units. amount_minor is negative when money leaves. A peer payment is two rows that sum to zero, so the whole network nets to what was actually funded.
• One currency per wallet. The unique index on (user_id, currency_code) means a second currency is a second wallet, and no balance ever adds dollars to euros.
• pending_out_minor beside the balance. Money committed to an in-flight cash-out is still in the balance but not spendable, the same split as available_minor in banking-ledger and locked_base in crypto-exchange.
• ON DELETE RESTRICT. You cannot delete a wallet that has entries. Financial history outlives the account.

Fintech

Venmo-style peer-to-peer payments database schema

How a social payments app models its data: wallets and balances, linked bank accounts and cards, transfers between friends, payment requests, bill splits, per-payment privacy, and the feed with comments and likes.

How do you update a balance without losing writes?

Never read the balance into your application, subtract, and write it back. Under PostgreSQL's default READ COMMITTED isolation, two transactions can read the same value and the second write silently overwrites the first. We measured it on PostgreSQL 16: four concurrent workers each made 2,000 one-cent spends from a single wallet using SELECT then UPDATE … SET balance_minor = <value read>. Across three runs, between 2,048 and 2,109 of the 8,000 spends reached the balance. Roughly three in four simply vanished, and nothing raised an error.

The same test with the arithmetic inside the UPDATE recorded all 8,000. PostgreSQL re-checks the row after waiting for a concurrent writer, so balance_minor = balance_minor - 1 always applies to the latest committed value (PostgreSQL docs, Read Committed).

A transfer then looks like this: lock both wallets in a fixed order, debit with the funds check in the WHERE clause, and write each ledger entry from the RETURNING value of its own update through a data-modifying CTE.

BEGIN;

SELECT id FROM wallets WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

WITH debit AS (
  UPDATE wallets
  SET balance_minor = balance_minor - 3000
  WHERE id = 1
    AND balance_minor - pending_out_minor >= 3000
  RETURNING id, balance_minor
)
INSERT INTO wallet_entries (wallet_id, entry_type, amount_minor, balance_after_minor)
SELECT id, 'transfer_out', -3000, balance_minor FROM debit;

WITH credit AS (
  UPDATE wallets
  SET balance_minor = balance_minor + 3000
  WHERE id = 2
  RETURNING id, balance_minor
)
INSERT INTO wallet_entries (wallet_id, entry_type, amount_minor, balance_after_minor)
SELECT id, 'transfer_in', 3000, balance_minor FROM credit;

COMMIT;

If the sender cannot afford it, the debit updates zero rows and the insert reports INSERT 0 0. Your code checks that count and rolls back before the credit runs. The ORDER BY id FOR UPDATE line is there for deadlocks: without it, a transfer from wallet 1 to 2 and one from 2 to 1 at the same moment can each lock their sender and wait forever for the other.

in-game-economy takes the other route and puts a version column on wallets for optimistic locking. That works too, and suits an ORM that already supports version columns, but it turns contention into retries. For a balance with real concurrent spenders, the atomic update is simpler.

Should the database stop a balance going negative?

Yes, as a backstop. The WHERE clause above is the business rule; a CHECK constraint is the guarantee that a bug in some other code path cannot break it.

ALTER TABLE wallets
  ADD CONSTRAINT chk_wallets_balance_non_negative CHECK (balance_minor >= 0);

crypto-exchange documents the same rule for available_base ("may never go negative") and pharmacy for quantity_on_hand. Skip it only where negative is a legitimate state. In usage-based-billing, a negative balance_minor means the customer is in credit.

Make the ledger itself append-only too. Corrections are new reversing rows, never edits, and a trigger turns that convention into an error:

CREATE FUNCTION forbid_ledger_rewrite() RETURNS trigger AS $$
BEGIN
  RAISE EXCEPTION 'wallet_entries is append-only; post a reversing entry instead';
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER wallet_entries_append_only
BEFORE UPDATE OR DELETE ON wallet_entries
FOR EACH ROW EXECUTE FUNCTION forbid_ledger_rewrite();

Revoking UPDATE and DELETE from the application's role does the same job with less code, if your app connects with its own role.

How do you catch a cached balance that has drifted?

Run the reconciliation the design promises. Every wallet whose cached balance differs from the sum of its entries comes back as a row:

SELECT w.id, w.balance_minor, COALESCE(SUM(e.amount_minor), 0) AS ledger_sum
FROM wallets w
LEFT JOIN wallet_entries e ON e.wallet_id = w.id
GROUP BY w.id, w.balance_minor
HAVING w.balance_minor <> COALESCE(SUM(e.amount_minor), 0);

Schedule it, alert on any row, and fix drift by correcting the cache to the ledger, never the other way round. in-game-economy adds lifetime_credited_minor and lifetime_debited_minor on each wallet, so a cheaper check, balance = credited − debited, can run on every write.

Should each ledger entry store the running balance?

Yes, if users see statements. Five of the eight cached-balance schemas store a balance-after value on each entry: running_balance_minor in banking-ledger, balance_after_minor in venmo-p2p, balance_after_base in crypto-exchange, balance_after in in-game-economy, and running_balance in pharmacy's controlled-drugs register. In the transfer above it costs nothing, because RETURNING hands you the value.

Without it, a statement page needs a window function over the account's entire history:

SELECT id, created_at, amount_minor,
       SUM(amount_minor) OVER (
         PARTITION BY wallet_id ORDER BY created_at, id
       ) AS running_balance_minor
FROM wallet_entries
WHERE wallet_id = 1
ORDER BY created_at, id;

That query is correct, but page 40 of a statement still sums pages 1 to 39. A stored running balance also makes tampering visible, which is why the pharmacy register keeps one: a gap between one row's balance and the next row's movement is a discrepancy anyone can spot.

How do you do this in MySQL?

MySQL has no UPDATE … RETURNING, so lock first, then read back inside the same transaction. The atomic update and its funds check are unchanged; ROW_COUNT() tells you whether the debit happened.

START TRANSACTION;

SELECT id FROM wallets WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

UPDATE wallets
SET balance_minor = balance_minor - 3000
WHERE id = 1 AND balance_minor - pending_out_minor >= 3000;

INSERT INTO wallet_entries (wallet_id, entry_type, amount_minor, balance_after_minor)
SELECT id, 'transfer_out', -3000, balance_minor FROM wallets WHERE id = 1;

UPDATE wallets SET balance_minor = balance_minor + 3000 WHERE id = 2;

INSERT INTO wallet_entries (wallet_id, entry_type, amount_minor, balance_after_minor)
SELECT id, 'transfer_in', 3000, balance_minor FROM wallets WHERE id = 2;

COMMIT;

Check ROW_COUNT() straight after the debit and roll back if it is 0. The FOR UPDATE locking read holds both rows until commit, so the balance you read back is the one you just wrote. CHECK constraints are enforced from MySQL 8.0.16; on older versions they parse and are ignored. The MySQL export of venmo-p2p gives you the matching tables.

When is a plain balance column fine?

When the history is somewhere else or does not matter. fleet-management keeps a single quantity_on_hand on parts and says why: a fleet's store is one shelf, and bin-level stock with a movement log is a warehouse problem. ecommerce-store keeps on_hand and reserved as two counters, which is enough to stop overselling at checkout. Even then, write them with the atomic update and the CHECK constraint. Those two cost nothing and stop the lost updates, which hit a counter just as hard as a wallet.

The moment someone can dispute the number, add the ledger. Adding one later means reconstructing history you never recorded.

To start from a working design, open venmo-p2p in the editor for the wallet pattern, or banking-ledger for full double-entry with internal accounts, holds and statements, and export the DDL for your database.

Frequently asked questions