ynab-db

Querying ynab.db

A local SQLite export of a YNAB account, produced by ynab_to_sqlite.py. Refresh it anytime with python3 ynab_to_sqlite.py (incremental, cheap); check staleness first: SELECT MAX(updated_at) FROM sync_state;

Schema in one look

budgets(id, name, last_modified_on, first_month, last_month, currency_iso, currency_symbol)
accounts(id, budget_id, name, type, on_budget, closed, balance, cleared_balance, uncleared_balance, last_reconciled_at, deleted)
category_groups(id, budget_id, name, hidden, deleted)
categories(id, budget_id, category_group_id, name, hidden, budgeted, activity, balance, goal_type, deleted)
payees(id, budget_id, name, transfer_account_id, deleted)
transactions(id, budget_id, account_id, date, amount, memo, cleared, approved, flag_color, payee_id, category_id, transfer_account_id, import_id, deleted)
subtransactions(id, transaction_id, amount, memo, payee_id, category_id, deleted)
sync_state(budget_id, resource, server_knowledge, updated_at)   -- internal, ignore for analysis

Rules that prevent wrong answers

1. Always filter deleted = 0. Deletions are soft — deleted rows stay in every table with deleted = 1. Skipping this inflates all totals.
2. Always scope by budget_id (or JOIN budgets and GROUP BY). One account often has several budgets; mixing them produces nonsense. List them first: SELECT id, name, currency_iso FROM budgets;
3. Exclude transfers from income/spending math. A transfer between the user's own accounts has transfer_account_id IS NOT NULL and is not real income or spending — counting it double-counts money.
4. Amounts are decimal currency units (already converted from YNAB milliunits): negative = outflow, positive = inflow.
5. Income = inflows to the category named Inflow: Ready to Assign (a real row in categories). Don't treat every positive amount as income — refunds and transfers are also positive.
6. Splits: a split transaction's own category_id may be NULL; its line items live in subtransactions. For per-category accuracy, take categorized rows from transactions where the id is NOT in subtransactions' parents, plus all subtransactions rows.
7. category_id IS NULL + transfer_account_id IS NULL = genuinely uncategorized spending — report it as such rather than dropping it.
8. Dates are TEXT YYYY-MM-DD — safe for lexicographic comparison and SQLite date functions (date('now','-3 months'), strftime).