Handling Balances and Transactions: Patterns and Anti-Patterns

Almost every business system has to answer one question correctly: how much is in this account right now? The account might be a ledger account, a customer receivable, a wallet or a loyalty-point balance. The obvious design is a balance column that every transaction updates. It works in a demo. Under real load, audit requirements and integrations that retry, it tends to produce lost updates, unexplained differences and slow reports.
This article describes a different approach, taken from how Odoo 19 handles accounting on PostgreSQL. Odoo does not treat a mutable "current balance" as the authoritative record. It stores balanced journal lines and the matching events between them. It keeps a few line-level values that are read constantly, such as the open residual. It calculates every broader balance with set-based queries.

Core rule: write immutable ledger facts and read balances as indexed projections. Keep a stored balance only as a cache you can rebuild.[1]

1. Foundation: Ledger Model

Why: A single mutable balance row becomes a contention hotspot. It also can't answer historical questions such as "balance on 30 June" or "balance by partner".[1]
Benefit: You get a full audit trail, and any filter (account, date, partner, currency) can produce a balance.
Impact: Writes become cheap inserts, and reads become aggregates that indexes keep fast.

CREATE TABLE ledger_entry (
  id bigserial PRIMARY KEY,
  company_id bigint NOT NULL,
  external_system text,
  external_event_id text,
  state text NOT NULL DEFAULT 'draft'
);

CREATE TABLE ledger_line (
  id bigserial PRIMARY KEY,
  entry_id bigint NOT NULL REFERENCES ledger_entry(id),
  account_id bigint NOT NULL,
  partner_id bigint,
  posted_at date NOT NULL,
  amount numeric(18,2) NOT NULL,          -- signed: debit +, credit -
  amount_residual numeric(18,2) NOT NULL DEFAULT 0,
  reconciled boolean NOT NULL DEFAULT false,
  state text NOT NULL DEFAULT 'draft'
);

Example: A 500.00 sale inserts two rows, receivable +500 and revenue -500. No balance column is updated.

Choose one canonical amount. If debit, credit and balance are maintained independently, they can drift. Store the signed amount and derive the others.[1]

SELECT GREATEST(amount,0) AS debit, GREATEST(-amount,0) AS credit FROM ledger_line;

2. Write Patterns

Atomic, balanced posting

Why: An entry spans several lines, and a partial write leaves the books wrong.
Benefit: Other sessions never see half-finished work, and a failure rolls everything back.
Impact: The ledger stays consistent.[1]

BEGIN;
INSERT INTO ledger_line(entry_id, account_id, posted_at, amount)
VALUES (1001, 10, CURRENT_DATE, 500.00), (1001, 20, CURRENT_DATE, -500.00);

SELECT entry_id FROM ledger_line
WHERE entry_id = 1001
GROUP BY entry_id
HAVING ROUND(SUM(amount),2) <> 0;   -- row returned means ROLLBACK
COMMIT;

When validating many entries, use WHERE entry_id = ANY(...) in one grouped query. Don't load lines into the application.[1]

Database constraints for local rules

Why: Application checks can be bypassed by scripts or other services.
Benefit: Invalid rows can't exist.
Impact: Fewer integrity incidents. Rules spanning rows, such as entry balance, still need the grouped check above.[1]

ALTER TABLE ledger_line ADD CONSTRAINT chk_acc CHECK (account_id IS NOT NULL);

Idempotent posting

Why: Integrations retry, and "search then insert" races between two workers.
Benefit: The database decides the winner.
Impact: No duplicates after timeouts or message replays.[1]

ALTER TABLE ledger_entry
  ADD CONSTRAINT uq_event UNIQUE (company_id, external_system, external_event_id);

INSERT INTO ledger_entry(company_id, external_system, external_event_id)
VALUES (1,'ERP','INV-778')
ON CONFLICT (company_id, external_system, external_event_id) DO NOTHING
RETURNING id;   -- no row means already posted

Reverse and repost

Why: Editing posted history breaks audit trails and period closing.
Benefit: Every correction is visible.
Impact: The ledger stays append-only.[1]

INSERT INTO ledger_line(entry_id, account_id, posted_at, amount, state)
SELECT %(new_entry)s, account_id, CURRENT_DATE, -amount, 'posted'
FROM ledger_line WHERE entry_id = %(old_entry)s;

Batch writes

Why: Per-record loops repeat ORM, access-control, recompute and query overhead.
Benefit: Overhead is paid once per batch.
Impact: Much faster imports and integrations.[1]

Line.create(vals_list)                       # good
lines.write({'custom_state': 'processed'})   # good
for v in vals_list: Line.create(v)           # avoid

Atomic counters, only when unavoidable

Why: Wallet and quota use cases sometimes need a mutable balance.
Benefit: The check and the update happen in one statement.
Impact: There is no lost update, but you lose history unless you also append a ledger row.[1]

UPDATE wallet_account SET balance = balance - %(amt)s
WHERE id = %(id)s AND balance >= %(amt)s
RETURNING balance;   -- no row means insufficient funds or no account

3. Read Patterns

Grouped balance

Why: One query per account (N+1) grows with the number of accounts.
Benefit: The query count stays roughly constant.
Impact: Dashboards and reports stay fast as data grows.[1]

SELECT account_id,
       SUM(amount) FILTER (WHERE amount>0)  AS debit,
       -SUM(amount) FILTER (WHERE amount<0) AS credit,
       SUM(amount) AS balance
FROM ledger_line
WHERE account_id = ANY(%(ids)s) AND state='posted'
  AND posted_at BETWEEN %(from)s AND %(to)s
GROUP BY account_id;

Accounts with no rows are absent from the result, so default them to zero in the caller.

Running balance with a window

Why: A running balance depends on the filter and order, so it can't be a global stored value.
Benefit: It is correct for any filter and costs one query.
Impact: Statements are accurate, provided the window runs before pagination.[1]

WITH opening AS (
  SELECT COALESCE(SUM(amount),0) AS amt FROM ledger_line
  WHERE account_id=%(acc)s AND state='posted' AND posted_at < %(from)s
), period AS (
  SELECT id, posted_at, amount,
         SUM(amount) OVER (ORDER BY posted_at, id
              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run
  FROM ledger_line
  WHERE account_id=%(acc)s AND state='posted'
    AND posted_at BETWEEN %(from)s AND %(to)s
)
SELECT p.*, o.amt + p.run AS running_balance
FROM period p CROSS JOIN opening o ORDER BY posted_at, id;

The explicit ROWS frame avoids default peer-row behavior, and id makes the order deterministic.

Open items and partner balance

Why: Payments can be partial, multiple or in foreign currency, and overwriting the invoice destroys history.
Benefit: The original fact stays intact, matching is reversible, and aging is cheap.
Impact: Unreconciling means deleting a match row and recomputing the residual.[1]

CREATE TABLE ledger_match (
  id bigserial PRIMARY KEY,
  debit_line_id bigint REFERENCES ledger_line(id),
  credit_line_id bigint REFERENCES ledger_line(id),
  amount numeric(18,2) NOT NULL
);

SELECT partner_id, SUM(amount_residual) AS due
FROM ledger_line
WHERE reconciled IS NOT TRUE AND state='posted'
GROUP BY partner_id;

The residual is the line amount, minus what is matched as debit, plus what is matched as credit. Recompute it in batches. Example: an invoice of 1000 with payments of 300 and 200 leaves a residual of 500 and reconciled = false.

Indexes that match the queries

Why: Balance queries filter by account and date, and by open status.
Benefit: Scans touch only the needed rows, and a partial index stays small.
Impact: Reads get faster, but every index slows writes, so add only the dominant ones.[1]

CREATE INDEX idx_acct_date ON ledger_line(account_id, posted_at);
CREATE INDEX idx_open ON ledger_line(account_id, partner_id) WHERE reconciled IS NOT TRUE;

Use the same predicate in the query (reconciled IS NOT TRUE), or the planner may skip the partial index.

Rebuildable snapshots

Why: Some fixed aggregates, such as daily balances, are too costly to recompute repeatedly.
Benefit: Hot reports read quickly.
Impact: Backdated entries, reversals and migrations must update or rebuild the affected dates. Add a snapshot only after profiling.[1]

CREATE TABLE account_daily_balance (
  account_id bigint, balance_date date, balance numeric NOT NULL DEFAULT 0,
  PRIMARY KEY (account_id, balance_date));

INSERT INTO account_daily_balance VALUES (%s,%s,%s)
ON CONFLICT (account_id, balance_date)
DO UPDATE SET balance = account_daily_balance.balance + EXCLUDED.balance;

For analytics, use a materialized view refreshed asynchronously, not on every post.[1]

4. Anti-Patterns

Anti-pattern	Why it hurts	Fix
Read, add, write (bal = bal + x in app code)	Concurrent workers lose updates, which causes silent money errors	Atomic SQL update, or append a ledger row
Stored balance as the only truth	Hot row, no history, no dimensional queries	Ledger plus aggregates
Query per account or partner	The query count grows with the data	One grouped query
Summing rows in Python	Network and memory cost	SUM ... GROUP BY in the database
Running total over the current page	The total restarts on page 2	Window over the full set, then paginate
Editing posted lines	Breaks audit and closed periods	Reverse and repost
Float money (0.1+0.2 != 0.3)	Rounding errors	numeric, currency rounding, precision-aware zero test
Search, then insert	Race creates duplicates	Unique constraint
Stored partner total over all lines	Broad recomputation on every post	Sum open residuals
Partial-index predicate mismatch	Index not used	Keep predicates identical
Materialized-view refresh per post	A cheap insert becomes a global refresh	Scheduled refresh
Raw SQL without flush or invalidate	Stale reads and ORM cache, bypassed rules	Flush before reads, invalidate after writes
Commits inside loops	Breaks atomicity and rollback	One transaction per business operation
Indexing every dimension	Slower writes, more storage	Index only dominant predicates

All rows reflect the anti-pattern list in the source document.[1]

Concurrency example. Two workers each read a balance of 100 and add 50. The stored result is 150, not 200. The atomic statement SET balance = balance + 50 avoids this because PostgreSQL serializes the row update.

5. Production Checklist

• Each entry's signed total rounds to zero.
• Posting is batched, atomic and idempotent.
• Posted and locked-period rows are protected.
• Multi-currency and partial-reconciliation rounding are tested.
• EXPLAIN (ANALYZE, BUFFERS) runs on production-shaped data.
• Account/date and open-item queries use the intended indexes.
• Concurrent posting and reconciliation are load-tested.
• Snapshots can be rebuilt from the ledger.

The SQL is simplified and untested against your schema. The table definitions, reversal insert and idempotent insert are my own illustrative examples.