Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 33 ยท Projects

Project: Personal Budget Tracker

Design a budget tracker in SQLite: accounts, categories and transactions in integer cents, CHECK constraints, monthly summaries and running balances.

A budget tracker is a good test of database design because money is unforgiving. Totals must be exact, impossible entries must be rejected, and the reports people want (what did I spend this month, what is my balance after each purchase) need grouping and window functions. In this project you build the database from scratch and answer those questions in plain SQL.

You will combine constraints and STRICT tables, triggers, dates, aggregation and window functions. Each example is self-contained, so you can edit and re-run any of them.

The data model

accounts Checking, savings, cash.
categories Income or expense.
transactions One signed amount per row.

Positive is money in, negative is money out.

1 / 2

Design rules for money

Three decisions shape the schema:

  1. Store money as integer cents. SQLite's REAL is a binary floating-point number, so sums such as 0.1 + 0.2 are not exactly 0.3. Integers add exactly. Convert to dollars only when displaying (amount_cents / 100.0).
  2. One signed amount per transaction. Income is positive, spending is negative. Then a balance is simply SUM(amount_cents), with no CASE needed.
  3. Let the database refuse nonsense. CHECK constraints and a trigger stop bad rows at the door, whichever program wrote them.

Dates are stored as ISO-8601 text (2026-03-15). The constraint date(posted_on) IS posted_on rejects anything that is not a real calendar date written in exactly that form, such as 2026-02-30 or 15/03/2026. Note IS and not =: date() returns NULL for text it cannot read, NULL = 'x' is NULL, and a CHECK that evaluates to NULL counts as passed. IS turns that case into a plain false.

Step 1: schema, data and guard rails

The trigger below enforces something a single-table CHECK cannot: the sign of an amount must match the category (income categories take positive amounts, expense categories take negative ones).

SQL Create the schema and load three months of data

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

The balance of an account is its opening balance plus every signed amount. Checking starts at 1,000.00 and ends the quarter at 5,714.00 after three salaries and all the spending; savings goes from 5,000.00 to 5,037.50 through interest alone. Notice the LEFT JOIN and coalesce: an account with no transactions yet would otherwise disappear or show NULL.

Step 2: watch the constraints work

Before building reports, check that the guard rails hold. Using INSERT OR IGNORE turns a CHECK violation into a skipped row, so we can count what was refused. After the skipped rows, the final statement is a deliberate mismatch that the trigger rejects with an error: a negative amount in an income category. That last statement is meant to fail, and the message you see is the trigger's.

SQL Bad rows are refused

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

The first three attempts each report 0 rows inserted (impossible date, wrong format, zero amount), and the valid row reports 1 with one row in the table. The last statement ends the run with amount sign does not match the category kind. In an application you would not use OR IGNORE for this; you would let the statement fail, catch the error and tell the user what was wrong.

A trigger like this checks only inserts. If rows can be updated, add a matching BEFORE UPDATE trigger, or make edits go through delete-and-insert in one transaction.

Step 3: monthly summaries

The monthly report groups by the first seven characters of the date (2026-03), which strftime('%Y-%m', posted_on) produces. Because income is positive and spending is negative, conditional sums give the three numbers people want. A window function then adds the change from the previous month.

SQL Income, spending, net and month-over-month change

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

The first month has no previous row, so lag() returns NULL and the CASE leaves change_vs_prev empty (without it, printf would print +0.00, which would be misleading). The view makes the report reusable: any later query can select from monthly as if it were a table. %+.2f in printf forces an explicit sign.

Step 4: where does the money go, and what broke the budget?

Spending by category uses a join and a filter on negative amounts. For budgets, add a table of monthly limits per category, then compare each month's spending with its limit and keep only the months that went over.

SQL Spending by category and over-budget months

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

Rent dominates the spending list, which is typical. Only one month broke a budget: Fun in March, where the weekend trip alone cost 150.00 against a limit of 100.00. The HAVING clause filters after grouping, which is why it can use the sum, unlike WHERE.

Step 5: running balance with a window function

A bank statement shows the balance after each transaction. That is a running total: SUM(amount_cents) OVER (...) ordered by date, per account, plus the opening balance. Add the row id as a tie-breaker so two transactions on one day have a stable order, and use an explicit ROWS frame so rows with the same date are not lumped together.

SQL Balance after every transaction, and each month-end balance

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

The statement starts at 1,000.00 plus the 3,000.00 salary, then falls with each payment. The second query numbers rows within each month from the newest backwards and keeps number 1, which is a standard way to get "the last row per group". In a real app you would also add an index on (account_id, posted_on), as the schema in step 1 does, so the window's sort is cheap.

Where to take it next

  • Transfers. Record a move between two accounts as two rows (one negative, one positive) inside one transaction, with a shared transfer_id, so the net worth does not change.
  • Recurring items. A recurring table plus a job that inserts due rows with INSERT ... SELECT.
  • Multiple currencies. Add a currency column and a rates table; never add amounts of different currencies together.
  • Import and export. Load a bank's CSV into a staging table and clean it before inserting; see Project: Analyzing a CSV Dataset.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why store amounts as integer cents instead of REAL dollars?

    REAL is binary floating point, so values like 0.1 are approximations and sums drift. Integer cents are exact.
  2. 2. What does CHECK (date(posted_on) IS posted_on) reject?

    date() returns NULL for unreadable input and a normalized YYYY-MM-DD string otherwise, so any value that does not round-trip fails the check. IS is used because a plain = with NULL would let the row through.
  3. 3. Why does the running balance use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW with an id tie-breaker?

    The default frame treats rows with equal ORDER BY values as peers and gives them the same total. A ROWS frame and a unique ordering give one step per row.
  4. 4. Why is the over-budget filter written in HAVING and not in WHERE?

    WHERE filters individual rows before they are grouped; a condition on an aggregate must wait until the groups exist.
  5. 5. A LEFT JOIN from accounts to transactions with coalesce(sum(...), 0) is used so that:

    An inner join would drop accounts that have no transactions, and sum() over nothing is NULL, which coalesce turns into 0.

Summary

  • Store money in integer cents and use one signed amount per transaction.
  • CHECK constraints validate single rows, and a trigger can enforce rules across tables, such as sign versus category kind.
  • Monthly reports group on strftime('%Y-%m', date); HAVING filters on aggregates; a view makes the report reusable.
  • Running balances use SUM() OVER (PARTITION BY ... ORDER BY ... ROWS ...); row_number() picks the last row per group.
  • Index the columns you filter and sort by, here (account_id, posted_on).

Next: Project: Local-First Event Log and Job Queue, using SQLite as an append-only log and a work queue.