Event-Sourced Account Ledger

Project roadmap

1. 01

   Design the ledger

   ~2h

   Write DESIGN.md: accounts, ledger entries (id, account, amount in cents positive or negative, type, a transaction id grouping the entries of one operation, the entry it reverses if any, and a created time and sequence number), snapshots (account, balance, and the last entry included), the rules (entries are never updated or deleted, each transaction's entries sum to zero, and balances can't go below an account's limit), and how reconciliation works. Done when: DESIGN.md defines the tables and every rule with an example.

2. 02

   Create append-only tables

   ~2h

   Write schema.sql with the tables, and make the ledger append-only: revoke UPDATE and DELETE from the application role, and add a trigger that raises an error on any update or delete. Add a deferred constraint trigger that checks each transaction's entries sum to zero when the transaction commits. Done when: updating or deleting an entry fails, and an unbalanced transaction can't commit.

3. 03

   Post transactions safely

   ~2.5h

   Write PL/pgSQL functions deposit, withdraw, transfer and reverse(transaction_id), each inserting balanced entries in one transaction. Lock the accounts involved (in a fixed order, by id, to avoid deadlocks) and check the balance limit against the current balance (latest snapshot plus later entries). Reversal creates opposite entries linked to the originals and refuses to reverse the same transaction twice. Done when: valid operations post balanced entries, and overdrafts and double reversals are refused.

4. 04

   Compute balances and history

   ~2h

   Write balance_of(account) using the latest snapshot plus later entries, and a statement query showing an account's entries with a running balance using SUM() OVER (ORDER BY sequence). Write balance_at(account, time) for a past moment. Compare the fast balance with a full sum of all entries. Done when: fast and full balances agree for every account.

5. 05

   Take snapshots and reconcile

   ~2h

   Write take_snapshots() that stores each account's balance up to the latest entry, safe to run while transactions are posting. Write reconcile() that recomputes every account's balance from all entries and compares it with the latest snapshot plus later entries, writing any differences to a reconciliation_issues table. Done when: reconciliation reports nothing on healthy data, and finds a snapshot you corrupt on purpose.

6. 06

   Load test and check concurrency

   ~1.5h

   Generate a large realistic history (thousands of accounts, a million entries) and measure balance lookups with and without snapshots using EXPLAIN ANALYZE. Run many concurrent transfers with pgbench (on your own machine) and confirm no balance limit is broken and the ledger still sums correctly. Done when: README.md shows the timings, and the concurrency test finds no errors.

7. 07

   Test with psql

   ~1.5h

   Write test.sql that, inside a transaction, posts each kind of operation, checks balances, statements and balance_at, confirms updates and deletes fail, confirms double reversals and overdrafts fail, runs snapshots and reconciliation, then rolls back. Done when: psql -f test.sql passes.

Resources

• DocsPostgreSQL: CREATE TRIGGER (including constraint triggers)
• DocsPostgreSQL: explicit locking
• DocsPostgreSQL: window functions
• Docspgbench documentation
• ArticleMartin Fowler: event sourcing