Multi-Branch Bank Transactions Ledger

Project roadmap

1. 01

   Design the schema

   ~2h

   Write schema.sql with branches, customers, accounts (branch, customer, currency, opened date, current balance as numeric(14,2)), transfers (id, created at, description) and ledger_entries (transfer, account, amount: negative for money out, positive for money in). Use foreign keys, and a check that an amount is never zero. Explain in a comment why balances use numeric and never float. Done when: the schema loads without errors.

2. 02

   Write a safe transfer function

   ~2.5h

   Write a PL/pgSQL function transfer(from, to, amount, description) that locks both accounts (always in the same order, by id, to avoid deadlocks), refuses the transfer if the source balance would go negative, inserts the transfer and its two ledger entries, and updates both balances, all in one transaction. Add a CHECK constraint so a balance can never be negative. Done when: valid transfers work, and overdrawing raises an error without changing anything.

3. 03

   Load sample data

   ~1.5h

   Create 3 branches, 50 customers and 80 accounts, then use generate_series to call transfer thousands of times over a few months, with deposits and withdrawals modeled as transfers to and from a special bank cash account. Include a few accounts with bursts of many quick transfers for the next steps. Done when: the data loads, and the sum of all ledger entries is exactly 0.

4. 04

   Rebuild running balances

   ~2h

   Write a query showing an account's history with a running balance after every entry, using SUM(amount) OVER (PARTITION BY account ORDER BY created_at, id). Write a check query comparing each account's stored balance with the sum of its entries, which must return no rows. Done when: the running balance ends at the stored balance for every account.

5. 05

   Detect rapid transfers

   ~2h

   Flag accounts that send 5 or more transfers within any 10-minute window, using a window function with RANGE BETWEEN INTERVAL '10 minutes' PRECEDING AND CURRENT ROW, or a self-join. Show the account, the window start, the number of transfers and the total amount. Done when: the query finds exactly the accounts with bursts added in the sample data.

6. 06

   Report branch daily summaries

   ~1.5h

   Write a query with total money in, money out and net change per branch per day, including days with no activity (using generate_series), and a view branch_daily_summary for it. Done when: the daily totals per branch add up to the change in that branch's total balance.

7. 07

   Test concurrency

   ~1.5h

   Open two psql sessions and run opposite transfers between the same two accounts at the same time, showing that the lock order prevents a deadlock and the balances stay correct. Write tests.sql checking: overdrawing fails, ledger entries always sum to zero, and stored balances match the entries. Done when: tests.sql passes, and the concurrency test is described in README.md.

Resources

• DocsPostgreSQL: explicit locking
• DocsPostgreSQL: window functions tutorial
• DocsPostgreSQL: PL/pgSQL
• ArticleDouble-entry bookkeeping (Wikipedia)
• DocspgAdmin documentation