Building a Budget Tracker: Why Double-Entry Bookkeeping?

Why Budgeting Matters

Here's a number that should make you uncomfortable: 78% of American workers live paycheck to paycheck. Not just minimum wage workers. Households earning over $100,000 a year are in that group too. Inflation has pushed grocery bills up 25% since 2020. Rent in major cities has climbed 30-40%. Interest rates have made borrowing more expensive than it's been in two decades.

The natural response is to track your spending. And most people start the same way: a spreadsheet. One column for the date, one for the amount, one for the category. Maybe a few formulas at the bottom.

It works for a month. Maybe two. Then things get messy:

• You pay rent with your credit card to get points, then pay off the card from checking. That's one expense but three transactions. Where does it go?
• You get reimbursed for a work expense. Is that income? A negative expense? Your categories break down.
• Your partner Venmos you for half of dinner. Now you're tracking transfers between people and accounts, and your spreadsheet has no concept of that.
• At the end of the month, your bank balance doesn't match your spreadsheet total, and you have no idea where the discrepancy is.

These aren't edge cases. They're the normal reality of personal finance. And the accounting world solved every one of these problems 500 years ago.

A Brief History of Double-Entry Bookkeeping

In 1494, a Franciscan friar named Luca Pacioli published Summa de Arithmetica in Venice. It was a mathematics textbook, but buried inside was a 27-page section called "Particularis de Computis et Scripturis" (details of calculation and recording). It described the bookkeeping system that Venetian merchants had been using for over a century.

The system was dead simple: every financial event must be recorded in two places.

Buy a shipment of spices for 100 ducats? Your Merchandise account goes up by 100, and your Cash account goes down by 100. The total across all your accounts? Still zero. Always zero. If it's not zero, someone made an error, and you can find it.

This wasn't just clever record-keeping. It was a self-auditing system. Before double-entry, merchants could only tell if they were profitable by counting their money and comparing it to what they started with. With double-entry, they could see where the money went, when it moved, and whether every transaction was accounted for.

The Core Principles

Double-entry bookkeeping rests on three ideas:

1. Every Transaction Has Two Sides

When you spend $50 on groceries with your debit card:

• Your Checking Account (an asset) decreases by $50
• Your Groceries (an expense) increases by $50

When you receive a $3,000 paycheck:

• Your Checking Account (an asset) increases by $3,000
• Your Salary (income) increases by $3,000

Nothing appears from nowhere. Nothing vanishes. Money always moves from somewhere to somewhere.

2. Debits Always Equal Credits

Every transaction is recorded as a set of debits and credits. The total debits must always equal the total credits. No exceptions.

This is confusing at first because "debit" and "credit" don't mean what you think they mean. Forget your debit card and credit card. Those are marketing terms. In accounting:

So when you buy $50 of groceries with your debit card:

• Debit Groceries $50 (expense goes up)
• Credit Checking Account $50 (asset goes down)

Total debits: $50. Total credits: $50. Balanced.

3. The Accounting Equation Must Always Hold

Assets = Liabilities + Equity + (Income - Expenses)

This equation is the ultimate check. At any point in time, if you sum up all your assets, they must equal the sum of your liabilities, equity, and net income. If they don't, there's an error in your books.

This isn't just theory. We're going to enforce this in SQL with constraints and triggers.

Why SQL?

You could implement double-entry in any language. But SQL is the natural fit because the guarantees of double-entry map directly to database features:

The database enforces correctness. Not your application code, not your API layer, the database itself. Even if you bypass the UI entirely and run raw SQL, the constraints won't let you create an unbalanced transaction.

That's a level of data integrity that spreadsheets and most apps can't match.

What We're Building

Over the next few articles, we'll build a complete budget tracking system from the ground up:

1. The Chart of Accounts (next article) - the schema: account types, hierarchical categories, and the constraints that keep financial data clean
2. Querying the Chart of Accounts - exploring the schema with JOINs, self-joins, aggregations, and data validation queries
3. Double-Entry Transactions - recording money movement with journal entries and line items, with SQL constraints that enforce balance
4. Triggers and Audit Trails - automatic integrity checks and a complete history of every change
5. Reports: Balance Sheets and Income Statements - turning raw data into the financial reports that actually answer "where did my money go?"

Each article builds on the previous one. The schema is cumulative. By the end, you'll have a working system with real accounting rigor behind it.

A Preview of the Schema

Here's a high-level view of where we're headed:

account_types          accounts              journal_entries
├── id                 ├── id                ├── id
├── name               ├── name              ├── date
├── normal_balance     ├── account_type_id ──┤ ├── description
└── description        ├── parent_account_id │ ├── is_posted
                       ├── account_code      │ └── created_at
                       └── is_active         │
                                             │
                       journal_lines ────────┘  audit_log
                       ├── id                   ├── id
                       ├── journal_entry_id     ├── table_name
                       ├── account_id           ├── action
                       ├── debit               ├── old_data (JSONB)
                       ├── credit              ├── new_data (JSONB)
                       └── description          └── changed_at

Four core tables, one audit table. The audit_log is a generic change-tracking table. The table_name column identifies which entity changed, and old_data/new_data store the before/after state as JSONB. We'll attach triggers selectively to the tables that matter most: journal_lines (prevent tampering with posted transactions), journal_entries (track status changes), and accounts (track deactivations and renames). Not every table needs auditing, just the ones where unauthorized or accidental changes would cause real problems.

Series

• Part 2: The Chart of Accounts
• Part 3: Querying the Chart of Accounts

Let's start building.