Skip to content
Tutorials Center
My learning

Topics

Lesson 114 of 125 ยท Projects

Project: Banking Database

Build and audit a banking database in SQL. Guard a ledger with constraints and triggers, move money atomically, reconcile balances, print statements and keep an audit trail.

Banking is the classic test for a database design, because the rules are strict and the cost of a mistake is real. Money must never appear or vanish. A transfer either happens completely or not at all. And when someone asks "why is my balance this number?", you need an answer that you can prove from the data.

In this project you get the bank sample database, a small retail bank with 12 customers, 20 accounts, 56 transfers and a ledger of 693 entries covering January to June 2025. You will check that the constraints hold, reconcile the stored balances against the ledger (and find two accounts that have drifted and one transfer that lost half of itself), repair the damage, add triggers so it cannot happen again, move money inside a transaction, print a statement with a running balance, and record an audit trail.

You'll use constraints, foreign keys, transactions, triggers and window functions. The project is a companion to Project: Inventory and Orders Database, which builds a similar ledger from scratch; here the data already exists, and the work is auditing and hardening it.

The design in one picture

The most important decision is already made: the ledger is the source of truth. Every change to a balance is a row in ledger, and nothing is ever updated or deleted there. The balance_cents column on accounts is only a stored copy of the ledger total, kept so that "what is my balance?" does not have to add up thousands of rows.

flowchart LR
  C["customers"] --> A["accounts<br/>balance_cents (a cache)"]
  A --> L["ledger<br/>one row per money movement"]
  T["transfers<br/>one row per transfer"] --> L

A transfer is one row in transfers and two rows in ledger, one negative and one positive, so the pair always adds up to zero.

Amounts are stored as integer cents, never as decimals, because integers are exact. Here is the ledger table, which holds most of the rules:

SQL
CREATE TABLE ledger (
    id INTEGER PRIMARY KEY,
    account_id INTEGER NOT NULL REFERENCES accounts(id),
    kind TEXT NOT NULL CHECK (kind IN ('deposit', 'withdrawal',
         'transfer_in', 'transfer_out', 'fee', 'interest')),
    amount_cents INTEGER NOT NULL CHECK (amount_cents <> 0),
    created_at TEXT NOT NULL,
    transfer_id INTEGER REFERENCES transfers(id),
    note TEXT,
    CHECK ((kind IN ('transfer_in', 'transfer_out')) = (transfer_id IS NOT NULL)),
    CHECK ((kind IN ('deposit', 'transfer_in', 'interest') AND amount_cents > 0)
        OR (kind IN ('withdrawal', 'transfer_out', 'fee') AND amount_cents < 0))
);

CREATE UNIQUE INDEX one_leg_per_direction
  ON ledger (transfer_id, kind) WHERE transfer_id IS NOT NULL;

Read the two table-level CHECKs carefully. The first says a row has a transfer_id exactly when it is a transfer leg. The second ties the sign of the amount to the kind: deposits add, withdrawals subtract, and a "withdrawal" of +50.00 is impossible. The unique index allows at most one transfer_in and one transfer_out per transfer.

Stage 1: look around

Before changing anything, get a feel for the data. How many rows are there, and where does the money sit?

SQL Row counts and balances by account type ยท database: bank

Press Run (or Ctrl + Enter) to see the result here.

The bank holds 107,119.27 in 12 active checking accounts and 55,280.29 in six active savings accounts. One savings account is frozen (13,072.68, no movements allowed) and one is closed with a zero balance. Those two statuses matter later, because a trigger will refuse to move money in or out of an account that is not active.

Stage 2: try to break the rules

A constraint you have never seen fail is a constraint you only hope works. Both examples below fail on purpose: the run stops with the database's error, which is exactly what a bank wants to happen.

First, a withdrawal recorded with a positive amount, the kind of sign mistake that quietly creates money:

SQL Wrong sign (fails on purpose) ยท database: bank

Press Run (or Ctrl + Enter) to see the result here.

The CHECK that ties the sign to the kind rejects it. Now try to credit the same transfer twice, which would pay the receiver double:

SQL Second credit leg for one transfer (fails on purpose) ยท database: bank

Press Run (or Ctrl + Enter) to see the result here.

The unique index on (transfer_id, kind) stops it with UNIQUE constraint failed: ledger.transfer_id, ledger.kind. Notice that neither rule needed any application code: even a script run by hand in a database console cannot break them.

Constraints see one row at a time. They cannot say "this withdrawal would overdraw the account", because that depends on other rows. You'll enforce that rule with a trigger in Stage 4.

Stage 3: reconcile

Reconciliation means recomputing a number from first principles and comparing it with the stored one. It is the most important habit in financial data work. We'll run two checks.

The first compares each account's stored balance_cents with the sum of its ledger rows. The second looks for transfers that do not have exactly two legs that add up to zero. A transfer where money left one account but never arrived in the other is the worst kind of bug.

SQL Two reconciliation checks ยท database: bank

Press Run (or Ctrl + Enter) to see the result here.

The checks find three problems. Account 4's stored balance is 25.00 too high, and account 11's is 10.00 too low. Both are drift: something updated the stored copy without writing the matching ledger row, or the other way around. And transfer 31 (24.39 from account 5 to savings account 16) has only one leg: the money was taken from account 5 but the credit to account 16 was never written.

The LEFT JOIN and COALESCE in both checks are deliberate. With an inner join, an account with no ledger rows at all would not appear in the result, and a reconciliation that can silently skip rows is not much of a check.

Stage 4: repair, then prevent

Which number is right, the stored balance or the ledger? The ledger, by design. So the repair has two parts: add the missing credit leg of transfer 31 (an append, marked as a repair, never an edit of history), then recompute every stored balance from the ledger. Do it in one transaction, then rerun the checks as a count:

SQL Repair in a transaction and verify ยท database: bank

Press Run (or Ctrl + Enter) to see the result here.

Both counts are now 0, and savings account 16 holds 1,346,726 cents (13,467.26), which is its old stored balance plus the 243.94 that was never credited.

Repairing is only half the job. The drift happened because two writes, "add a ledger row" and "update the balance", were separate steps that something could skip. The fix is to make the database do the second step itself, every time, and to refuse movements that should never happen:

  • A BEFORE INSERT trigger guards the ledger: no movement on an account that is not active, and no movement that would take the balance below zero.
  • An AFTER INSERT trigger applies the amount to accounts.balance_cents.

With both in place, the only way to change a balance is to insert a ledger row, and the two can no longer disagree. A transfer is then three inserts inside one BEGIN ... COMMIT: one into transfers, one out leg and one in leg.

SQL Triggers and an atomic transfer ยท database: bank

Press Run (or Ctrl + Enter) to see the result here.

Account 1 goes from 1,026,427 to 1,001,427 cents and account 13 from 665,451 to 690,451: exactly 250.00 moved, and the bank's total did not change. In an application you would send those statements from one database transaction and issue ROLLBACK if any of them raised an error, so a failure on the second leg undoes the first.

The guard is easy to test. This script tries to move more money than account 12 holds, and it fails on purpose on the first leg:

SQL Overdraft (fails on purpose) ยท database: bank

Press Run (or Ctrl + Enter) to see the result here.

The error reads insufficient funds. A trigger can show a far friendlier message than the CHECK (balance_cents >= 0) on accounts, which would also have caught this, but only with a generic "constraint failed". Keeping both is a good habit: the trigger explains the problem, the constraint is the last line of defense.

Stage 5: statements

A customer's statement lists every movement in a period with a running balance: the opening balance plus everything so far. A window function does the running total, and the opening balance is the ledger sum before the period starts.

SQL March statement for account 1 ยท database: bank

Press Run (or Ctrl + Enter) to see the result here.

The statement starts from an opening balance of 3,365.43, adds the 4,161.21 salary, subtracts seven outgoing movements (among them a 5.00 service fee and two transfers), and ends at 5,526.62. The summary query agrees: 3,365.43 + 4,161.21 - 2,000.02 = 5,526.62. The ORDER BY created_at, id inside the window gives two entries with the same timestamp a stable order.

Stage 6: an audit trail

Who froze this account, and when? A bank has to be able to answer. A small audit table filled by a trigger records every status change, whoever makes it and however they do it:

SQL Audit trigger on account status ยท database: bank

Press Run (or Ctrl + Enter) to see the result here.

Exactly two rows appear, because the WHEN clause skips updates that do not change the status. In production you would also store who made the change (the application user's id), since SQLite has no database users and the trigger cannot know. The pattern is the same as in Triggers: the log is written by the database, not by code that could forget.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is the ledger treated as the source of truth, with balance_cents only a stored copy?

    A ledger of immutable rows can be re-added at any time to check the stored balance and shows the history behind it. A single balance column has no history.
  2. 2. A transfer is stored as one transfers row plus two ledger rows. Which check finds a transfer that lost a leg?

    A healthy transfer has exactly two legs, +x and -x, so the net is zero. HAVING COUNT(l.id) <> 2 OR SUM(l.amount_cents) <> 0 finds the others.
  3. 3. Why can a CHECK constraint not enforce 'no overdrafts'?

    The new balance depends on all earlier ledger rows, so the rule needs a trigger or a transaction that reads them. The balance_cents >= 0 check on accounts is only a backstop.
  4. 4. What does wrapping the three transfer inserts in BEGIN ... COMMIT guarantee?

    A transaction is atomic: money cannot leave one account without arriving in the other. If an error occurs between the inserts, the application rolls back the whole transfer.

Summary

  • Make the ledger the source of truth: append-only rows, with balance_cents as a stored cache that can be rebuilt.
  • Store money as integer cents, and encode rules in CHECK constraints (sign per kind) and unique indexes (one leg per direction).
  • Reconcile on a schedule: compare stored balances with ledger sums, and check that every transfer has two legs that net to zero.
  • Repair by appending corrections and recomputing the cache inside a transaction, never by editing history.
  • Use triggers for rules that span rows (overdrafts, inactive accounts) and to keep the cached balance in step with the ledger.
  • Move money in one transaction, print statements with a window-function running balance, and log sensitive changes with an audit trigger.

Next: Project: Bookstore Customer Analytics, where you turn orders into customer segments.