Building a Budget Tracker: Recording Transactions

This is Part 4 of the budget tracker series. In Part 2 and Part 3, we built and queried the chart of accounts: 27 accounts across five types, organized into a hierarchy. Now we record transactions.

Every account is a bucket. Transactions are flows between buckets. A salary deposit fills the Checking bucket. A rent payment drains it into the Rent Expense bucket. The rule is that every flow must touch at least two buckets, and the amounts must cancel out. We enforce that in SQL.

Setup

Create the chart of accounts from Part 2 (click to expand, then run)

If you're starting fresh or your session has expired, run this to restore the schema from Part 2.

DROP TABLE IF EXISTS accounts;
DROP TABLE IF EXISTS account_types;

CREATE TABLE account_types (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  normal_balance TEXT NOT NULL CHECK (normal_balance IN ('debit', 'credit')),
  description TEXT
);

INSERT INTO account_types (id, name, normal_balance, description) VALUES
  (1, 'Asset',     'debit',  'Resources owned — bank accounts, cash, receivables'),
  (2, 'Liability', 'credit', 'Obligations owed — credit cards, loans, mortgages'),
  (3, 'Equity',    'credit', 'Net worth — the difference between assets and liabilities'),
  (4, 'Income',    'credit', 'Revenue streams — salary, freelance, interest'),
  (5, 'Expense',   'debit',  'Spending categories — rent, groceries, utilities');

CREATE TABLE accounts (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  account_type_id INTEGER NOT NULL REFERENCES account_types(id),
  parent_account_id INTEGER REFERENCES accounts(id),
  account_code TEXT UNIQUE,
  description TEXT,
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(name, account_type_id)
);

CREATE INDEX idx_accounts_type ON accounts(account_type_id);
CREATE INDEX idx_accounts_parent ON accounts(parent_account_id);

INSERT INTO accounts (name, account_type_id, account_code, description) VALUES
  ('Checking Account',    1, '1001', 'Primary bank account'),
  ('Savings Account',     1, '1002', 'Emergency fund and savings'),
  ('Cash',                1, '1003', 'Physical cash on hand'),
  ('Investments',         1, '1004', 'Brokerage and retirement accounts');

INSERT INTO accounts (name, account_type_id, account_code, description) VALUES
  ('Credit Card',         2, '2001', 'Visa credit card'),
  ('Student Loan',        2, '2002', 'Federal student loan'),
  ('Car Loan',            2, '2003', 'Auto financing');

INSERT INTO accounts (name, account_type_id, account_code, description) VALUES
  ('Opening Balance',     3, '3001', 'Initial balances when starting the system');

INSERT INTO accounts (name, account_type_id, account_code, description) VALUES
  ('Salary',              4, '4001', 'Primary employment income'),
  ('Freelance Income',    4, '4002', 'Side project and consulting income'),
  ('Interest Income',     4, '4003', 'Bank and investment interest');

INSERT INTO accounts (name, account_type_id, account_code, description) VALUES
  ('Housing',             5, '5100', 'Housing-related expenses');
INSERT INTO accounts (name, account_type_id, parent_account_id, account_code, description) VALUES
  ('Rent',                5, (SELECT id FROM accounts WHERE account_code = '5100'), '5101', 'Monthly rent payment'),
  ('Utilities',           5, (SELECT id FROM accounts WHERE account_code = '5100'), '5102', 'Electric, water, gas, internet'),
  ('Renters Insurance',   5, (SELECT id FROM accounts WHERE account_code = '5100'), '5103', 'Monthly insurance premium');

INSERT INTO accounts (name, account_type_id, account_code, description) VALUES
  ('Food & Dining',       5, '5200', 'Food-related expenses');
INSERT INTO accounts (name, account_type_id, parent_account_id, account_code, description) VALUES
  ('Groceries',           5, (SELECT id FROM accounts WHERE account_code = '5200'), '5201', 'Grocery store purchases'),
  ('Restaurants',         5, (SELECT id FROM accounts WHERE account_code = '5200'), '5202', 'Dining out and takeout'),
  ('Coffee',              5, (SELECT id FROM accounts WHERE account_code = '5200'), '5203', 'Coffee shops');

INSERT INTO accounts (name, account_type_id, account_code, description) VALUES
  ('Transportation',      5, '5300', 'Getting around');
INSERT INTO accounts (name, account_type_id, parent_account_id, account_code, description) VALUES
  ('Gas',                 5, (SELECT id FROM accounts WHERE account_code = '5300'), '5301', 'Fuel for car'),
  ('Car Insurance',       5, (SELECT id FROM accounts WHERE account_code = '5300'), '5302', 'Auto insurance premium'),
  ('Parking & Tolls',     5, (SELECT id FROM accounts WHERE account_code = '5300'), '5303', 'Parking fees and toll charges');

INSERT INTO accounts (name, account_type_id, account_code, description) VALUES
  ('Personal',            5, '5400', 'Personal spending');
INSERT INTO accounts (name, account_type_id, parent_account_id, account_code, description) VALUES
  ('Entertainment',       5, (SELECT id FROM accounts WHERE account_code = '5400'), '5401', 'Movies, games, streaming'),
  ('Health & Fitness',    5, (SELECT id FROM accounts WHERE account_code = '5400'), '5402', 'Gym, sports, medical co-pays'),
  ('Clothing',            5, (SELECT id FROM accounts WHERE account_code = '5400'), '5403', 'Clothes and accessories');

The Schema

The transaction layer follows a header-lines pattern: one journal_entries row represents the transaction as a whole, and one or more journal_lines rows hold the individual debit and credit entries.

DROP TABLE IF EXISTS journal_lines;
DROP TABLE IF EXISTS journal_entries;

CREATE TABLE journal_entries (
  id          SERIAL PRIMARY KEY,
  entry_date  DATE NOT NULL DEFAULT CURRENT_DATE,
  description TEXT NOT NULL,
  is_posted   BOOLEAN NOT NULL DEFAULT false,
  posted_at   TIMESTAMPTZ,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_journal_entries_date   ON journal_entries(entry_date);
CREATE INDEX idx_journal_entries_posted ON journal_entries(is_posted);

is_posted is the key field. A transaction starts as a draft (false) and becomes permanent once posted (true). posted_at is NULL until posting happens, then set automatically by a trigger.

Now the lines:

CREATE TABLE journal_lines (
  id               SERIAL PRIMARY KEY,
  journal_entry_id INTEGER        NOT NULL REFERENCES journal_entries(id),
  account_id       INTEGER        NOT NULL REFERENCES accounts(id),
  debit            NUMERIC(15, 2) NOT NULL DEFAULT 0,
  credit           NUMERIC(15, 2) NOT NULL DEFAULT 0,
  description      TEXT,

  CHECK (debit  >= 0),
  CHECK (credit >= 0),
  CHECK (debit > 0 OR credit > 0),
  CHECK (NOT (debit > 0 AND credit > 0))
);

CREATE INDEX idx_journal_lines_entry   ON journal_lines(journal_entry_id);
CREATE INDEX idx_journal_lines_account ON journal_lines(account_id);

Four CHECK constraints enforce the rules at the row level:

Constraint	What it prevents
`debit >= 0`	Negative debit amounts
`credit >= 0`	Negative credit amounts
`debit > 0 OR credit > 0`	Empty lines with no amount
`NOT (debit > 0 AND credit > 0)`	A line that is both a debit and a credit

NUMERIC(15, 2) is the right type for money: exact decimal arithmetic (no floating-point rounding), two decimal places, up to 13 digits before the decimal point.

Enforcing Balance

Here's the problem with double-entry in SQL: the balance rule (total debits must equal total credits) spans multiple rows. A CHECK constraint only sees a single row. You cannot write CHECK (SUM(debit) = SUM(credit)).

The solution is a trigger that fires when a transaction is posted. While a transaction is being built, the lines can be in any state. At the moment is_posted flips to true, the trigger verifies the totals. If they don't match, the update is rejected.

CREATE OR REPLACE FUNCTION check_posting_balance()
RETURNS TRIGGER AS $$
DECLARE
  total_debit  NUMERIC;
  total_credit NUMERIC;
  line_count   INTEGER;
BEGIN
  IF NEW.is_posted = true AND OLD.is_posted = false THEN
    SELECT
      COALESCE(SUM(debit),  0),
      COALESCE(SUM(credit), 0),
      COUNT(*)
    INTO total_debit, total_credit, line_count
    FROM journal_lines
    WHERE journal_entry_id = NEW.id;

    IF line_count < 2 THEN
      RAISE EXCEPTION
        'Journal entry % must have at least 2 lines (has %)',
        NEW.id, line_count;
    END IF;

    IF total_debit <> total_credit THEN
      RAISE EXCEPTION
        'Journal entry % is unbalanced: debits=% credits=%',
        NEW.id, total_debit, total_credit;
    END IF;

    NEW.posted_at := now();
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_posting_balance
  BEFORE UPDATE ON journal_entries
  FOR EACH ROW
  EXECUTE FUNCTION check_posting_balance();

The trigger only acts when is_posted transitions from false to true. Two checks run: at least two lines exist, and debits equal credits. If either fails, RAISE EXCEPTION rolls back the entire update.

NEW.posted_at := now() sets the timestamp in the same operation. BEFORE triggers can modify NEW before the row is written, so this works without a separate UPDATE.

Making Posted Transactions Immutable

Once posted, a transaction is permanent. This is a core accounting principle: you don't edit history. Mistakes are corrected with a new reversing entry, not by modifying the original. Two triggers enforce this.

Block changes to posted journal entries:

CREATE OR REPLACE FUNCTION prevent_posted_entry_changes()
RETURNS TRIGGER AS $$
BEGIN
  RAISE EXCEPTION 'Cannot modify posted journal entry %', OLD.id;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_posted_entry_changes
  BEFORE UPDATE OR DELETE ON journal_entries
  FOR EACH ROW
  WHEN (OLD.is_posted = true)
  EXECUTE FUNCTION prevent_posted_entry_changes();

The WHEN (OLD.is_posted = true) clause runs the function only for posted entries. Unposted entries can still be edited or deleted freely.

Block changes to lines belonging to posted entries:

CREATE OR REPLACE FUNCTION prevent_posted_line_changes()
RETURNS TRIGGER AS $$
DECLARE
  entry_posted BOOLEAN;
BEGIN
  SELECT is_posted INTO entry_posted
  FROM journal_entries
  WHERE id = OLD.journal_entry_id;

  IF entry_posted THEN
    RAISE EXCEPTION
      'Cannot modify lines of posted journal entry %',
      OLD.journal_entry_id;
  END IF;

  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_posted_line_changes
  BEFORE UPDATE OR DELETE ON journal_lines
  FOR EACH ROW
  EXECUTE FUNCTION prevent_posted_line_changes();

RETURN COALESCE(NEW, OLD) handles both UPDATE and DELETE in one function. On UPDATE, NEW exists and is returned. On DELETE, NEW is NULL, so OLD is returned, allowing the deletion to proceed for unposted entries.

Recording Transactions

Each transaction follows the same pattern: insert the header, insert the lines, post it. We use INSERT ... RETURNING inside a CTE to carry the new entry ID into the lines insert without a second round-trip.

Transaction 1: Opening Balance

Before recording real transactions, we need starting balances. The convention is to debit the asset accounts being initialized and credit an Equity account called "Opening Balance".

WITH entry AS (
  INSERT INTO journal_entries (entry_date, description)
  VALUES ('2024-01-01', 'Opening balances')
  RETURNING id
)
INSERT INTO journal_lines (journal_entry_id, account_id, debit, credit, description)
SELECT
  entry.id,
  a.id,
  CASE a.account_code WHEN '1001' THEN 2500.00 ELSE 0    END,
  CASE a.account_code WHEN '3001' THEN 2500.00 ELSE 0    END,
  'Opening balance'
FROM entry CROSS JOIN accounts a
WHERE a.account_code IN ('1001', '3001');

UPDATE journal_entries SET is_posted = true
WHERE description = 'Opening balances';

Debit Checking Account (Asset increases), credit Opening Balance (Equity increases). Both sides of the accounting equation grow equally.

INSERT ... RETURNING works like a SELECT on the rows just inserted. Wrapping it in a CTE lets you use that result in the next statement. This avoids relying on sequence state or making two separate queries.

Transaction 2: Salary Deposit

WITH entry AS (
  INSERT INTO journal_entries (entry_date, description)
  VALUES ('2024-01-15', 'January salary')
  RETURNING id
)
INSERT INTO journal_lines (journal_entry_id, account_id, debit, credit)
SELECT
  entry.id,
  a.id,
  CASE a.account_code WHEN '1001' THEN 3000.00 ELSE 0 END,
  CASE a.account_code WHEN '4001' THEN 3000.00 ELSE 0 END
FROM entry CROSS JOIN accounts a
WHERE a.account_code IN ('1001', '4001');

UPDATE journal_entries SET is_posted = true
WHERE description = 'January salary';

Debit Checking (Asset up 3,000), credit Salary (Income up 3,000).

Transaction 3: Rent Payment

WITH entry AS (
  INSERT INTO journal_entries (entry_date, description)
  VALUES ('2024-02-01', 'February rent')
  RETURNING id
)
INSERT INTO journal_lines (journal_entry_id, account_id, debit, credit)
SELECT
  entry.id,
  a.id,
  CASE a.account_code WHEN '5101' THEN 1500.00 ELSE 0 END,
  CASE a.account_code WHEN '1001' THEN 1500.00 ELSE 0 END
FROM entry CROSS JOIN accounts a
WHERE a.account_code IN ('5101', '1001');

UPDATE journal_entries SET is_posted = true
WHERE description = 'February rent';

Debit Rent (Expense up 1,500), credit Checking (Asset down 1,500).

Transaction 4: Groceries on Credit Card

WITH entry AS (
  INSERT INTO journal_entries (entry_date, description)
  VALUES ('2024-02-03', 'Weekly groceries')
  RETURNING id
)
INSERT INTO journal_lines (journal_entry_id, account_id, debit, credit)
SELECT
  entry.id,
  a.id,
  CASE a.account_code WHEN '5201' THEN 147.32 ELSE 0 END,
  CASE a.account_code WHEN '2001' THEN 147.32 ELSE 0 END
FROM entry CROSS JOIN accounts a
WHERE a.account_code IN ('5201', '2001');

UPDATE journal_entries SET is_posted = true
WHERE description = 'Weekly groceries';

Debit Groceries (Expense up 147.32), credit Credit Card (Liability up 147.32). No cash changed hands. The credit card balance grew.

Transaction 5: Credit Card Payment

WITH entry AS (
  INSERT INTO journal_entries (entry_date, description)
  VALUES ('2024-02-15', 'Credit card payment')
  RETURNING id
)
INSERT INTO journal_lines (journal_entry_id, account_id, debit, credit)
SELECT
  entry.id,
  a.id,
  CASE a.account_code WHEN '2001' THEN 500.00 ELSE 0 END,
  CASE a.account_code WHEN '1001' THEN 500.00 ELSE 0 END
FROM entry CROSS JOIN accounts a
WHERE a.account_code IN ('2001', '1001');

UPDATE journal_entries SET is_posted = true
WHERE description = 'Credit card payment';

Debit Credit Card (Liability down 500), credit Checking (Asset down 500). Notice that paying a liability is a debit to that liability account, because Liability is a credit-normal account type. Debiting it reduces it.

Querying the Ledger

Query 1: Transaction Summary

SELECT
  je.id,
  je.entry_date,
  je.description,
  je.is_posted,
  SUM(jl.debit)  AS total_debit,
  SUM(jl.credit) AS total_credit,
  COUNT(jl.id)   AS line_count
FROM journal_entries je
JOIN journal_lines jl ON jl.journal_entry_id = je.id
GROUP BY je.id, je.entry_date, je.description, je.is_posted
ORDER BY je.entry_date;

 id | entry_date |      description       | is_posted | total_debit | total_credit | line_count
----+------------+------------------------+-----------+-------------+--------------+------------
  1 | 2024-01-01 | Opening balances       | t         |     2500.00 |      2500.00 |          2
  2 | 2024-01-15 | January salary         | t         |     3000.00 |      3000.00 |          2
  3 | 2024-02-01 | February rent          | t         |     1500.00 |      1500.00 |          2
  4 | 2024-02-03 | Weekly groceries       | t         |      147.32 |       147.32 |          2
  5 | 2024-02-15 | Credit card payment    | t         |      500.00 |       500.00 |          2

Every posted transaction shows equal debits and credits. The trigger enforced it; this query confirms it.

Query 2: Full Ledger View

SELECT
  je.entry_date,
  je.description          AS entry,
  a.account_code,
  a.name                  AS account,
  jl.debit,
  jl.credit
FROM journal_entries je
JOIN journal_lines jl ON jl.journal_entry_id = je.id
JOIN accounts      a  ON a.id = jl.account_id
WHERE je.is_posted = true
ORDER BY je.entry_date, je.id, a.account_code;

 entry_date |      entry          | code | account          | debit   | credit
------------+---------------------+------+------------------+---------+---------
 2024-01-01 | Opening balances    | 1001 | Checking Account | 2500.00 |    0.00
 2024-01-01 | Opening balances    | 3001 | Opening Balance  |    0.00 | 2500.00
 2024-01-15 | January salary      | 1001 | Checking Account | 3000.00 |    0.00
 2024-01-15 | January salary      | 4001 | Salary           |    0.00 | 3000.00
 2024-02-01 | February rent       | 1001 | Checking Account |    0.00 | 1500.00
 2024-02-01 | February rent       | 5101 | Rent             | 1500.00 |    0.00
 2024-02-03 | Weekly groceries    | 2001 | Credit Card      |    0.00 |  147.32
 2024-02-03 | Weekly groceries    | 5201 | Groceries        |  147.32 |    0.00
 2024-02-15 | Credit card payment | 1001 | Checking Account |    0.00 |  500.00
 2024-02-15 | Credit card payment | 2001 | Credit Card      |  500.00 |    0.00

Query 3: Account Balances

To calculate the balance of each account, sum its debits and credits across all posted transactions. For debit-normal accounts (Assets, Expenses), a positive balance means debits exceed credits. For credit-normal accounts (Liabilities, Equity, Income), it's the reverse.

SELECT
  a.account_code,
  a.name,
  at.name          AS account_type,
  at.normal_balance,
  SUM(jl.debit)    AS total_debits,
  SUM(jl.credit)   AS total_credits,
  CASE at.normal_balance
    WHEN 'debit'  THEN SUM(jl.debit)  - SUM(jl.credit)
    WHEN 'credit' THEN SUM(jl.credit) - SUM(jl.debit)
  END              AS balance
FROM accounts      a
JOIN account_types at ON at.id = a.account_type_id
JOIN journal_lines jl ON jl.account_id = a.id
JOIN journal_entries je ON je.id = jl.journal_entry_id
WHERE je.is_posted = true
GROUP BY a.id, a.account_code, a.name, at.name, at.normal_balance
ORDER BY a.account_code;

 code | account          | account_type | normal_balance | total_debits | total_credits | balance
------+------------------+--------------+----------------+--------------+---------------+---------
 1001 | Checking Account | Asset        | debit          |      5500.00 |       2000.00 | 3500.00
 2001 | Credit Card      | Liability    | credit         |       500.00 |        147.32 |  -352.68 (*)
 3001 | Opening Balance  | Equity       | credit         |         0.00 |       2500.00 | 2500.00
 4001 | Salary           | Income       | credit         |         0.00 |       3000.00 | 3000.00
 5101 | Rent             | Expense      | debit          |      1500.00 |          0.00 | 1500.00
 5201 | Groceries        | Expense      | debit          |       147.32 |          0.00 |  147.32

The Checking Account balance is correct: we started with $2,500, received $3,000 in salary, paid $1,500 rent and $500 toward the credit card, leaving $3,500.

(*) The Credit Card balance is $352.68 owed. A negative balance for a liability means you owe less than zero, which would be unusual. In this case the balance column is calculated as credits - debits for credit-normal accounts: 147.32 - 500.00 = -352.68. A negative balance on a Liability account means you've overpaid it, which is a receivable, not a liability. This is actually correct: we paid $500 toward a $147.32 charge, so the credit card owes us $352.68. In practice you'd add more transactions before running this query.

Challenges

Challenge 1: Try to post an unbalanced transaction. Insert a journal entry with one line (debit only, no matching credit) and try to set is_posted = true. What error do you get? Which of the two checks in the trigger fires first?

Hint: the line_count < 2 check runs before the balance check. A one-line transaction will fail at the first check.

Challenge 2: Write a query to find all accounts that have no transactions. These are candidates for review.

Hint: LEFT JOIN journal_lines onto accounts. Filter where journal_entry_id IS NULL.

Challenge 3: Write a reversal for the February rent payment. A reversal is a new journal entry that mirrors the original exactly, but with debits and credits swapped. This is the correct accounting way to undo a posted transaction.

Hint: SELECT from journal_lines WHERE the journal entry is the rent payment. INSERT those lines into a new entry with debit and credit columns swapped.

What's Next

We can now record transactions that are enforced to balance and locked once posted. But we have no record of what changed, when, or who did it. If a transaction is deleted before posting, it's gone.

In Part 5, we build audit_log: a generic change-tracking table with triggers attached to journal_entries, journal_lines, and accounts. Every insert, update, and delete gets logged with a before and after snapshot in JSONB.

Let's keep building.