Building a Budget Tracker: The Chart of Accounts

This is Part 2 of the budget tracker series. In Part 1, we covered what double-entry bookkeeping is and why it's the right foundation for a budget tracker. Now we build.

Every financial system starts with the same thing: a chart of accounts. It's a structured list of every account, every bucket where money can sit or flow through. Your checking account, your credit card, your rent expense, your salary income. Each one is an account.

In double-entry accounting, accounts fall into five types:

Type	What it represents	Normal Balance	Example
Asset	Things you own	Debit	Checking, Savings, Cash
Liability	Money you owe	Credit	Credit Card, Student Loan
Equity	Net worth	Credit	Opening Balance
Income	Money flowing in	Credit	Salary, Freelance
Expense	Money flowing out	Debit	Rent, Groceries, Utilities

The "normal balance" column will matter when we record transactions later in the series. For now, just know that it's how accounting determines whether a debit increases or decreases an account.

Let's build the schema.

The Schema

Two tables. One for the five account types, one for individual accounts.

-- Drop tables in correct order (children before parents)
DROP TABLE IF EXISTS accounts;
DROP TABLE IF EXISTS account_types;

-- The five fundamental 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');

The CHECK constraint on normal_balance ensures only valid values get in. No typos, no nulls, no "Debit" vs "debit" inconsistencies.

Now the accounts table:

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(),

  -- Prevent duplicate account names within the same type
  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);

Three things worth calling out:

parent_account_id is a self-referencing foreign key. It lets us nest accounts: "Groceries" can be a child of "Food & Dining", which is a child of the broader Expense type. This hierarchy is how you answer questions like "how much did I spend on food total?" without summing individual categories manually.

account_code follows accounting convention. Numeric codes where the range tells you the type (1000s = assets, 2000s = liabilities, etc.). It gives you natural sort order and makes accounts easy to reference.

UNIQUE(name, account_type_id) allows the same name across types (you could have "Insurance" as both an Expense and an Asset for prepaid insurance) but prevents duplicates within a type.

Seed Data

A realistic personal budget chart of accounts:

-- Assets (codes 1000-1999)
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');

-- Liabilities (codes 2000-2999)
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');

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

-- Income (codes 4000-4999)
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');

-- Expenses (codes 5000-5999) - parent categories first, then children
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');

27 accounts total. The expense accounts are organized hierarchically: four parent categories (Housing, Food & Dining, Transportation, Personal) with three children each. The rest (assets, liabilities, equity, income) are flat because they don't need sub-grouping at this scale.

Design Decisions

Why a separate account_types table instead of a CHECK constraint?
A CHECK (account_type IN ('Asset', 'Liability', ...)) would work, but a separate table gives us the normal_balance field and descriptions. It also makes JOIN-based reporting cleaner because you can group and filter by type without hardcoding strings.

Why account_code as TEXT, not INTEGER?
Some accounting systems use codes like "5100.01" or "A-1001". TEXT gives flexibility without losing sort order, as long as codes are consistently formatted.

Why is_active instead of deleting accounts?
Once transactions reference an account, you can't delete it without breaking referential integrity. Setting is_active = false preserves historical data while hiding the account from new transactions.

Why hierarchical accounts?
Flat lists break down past 30-40 expense categories. The self-referencing FK is simple, well-understood, and enables recursive CTE rollups for reports later in this series.

What's Next

We have the schema, now let's query it. In Part 3, we'll explore the chart of accounts with JOINs, self-joins, aggregations, and data validation queries. We'll answer questions like "which accounts are leaf accounts?", "does the hierarchy have integrity issues?", and "how are accounts distributed across types?"