How to build a budget and expense tracker with Claude

Set up a budget for me in databasedmcp, in one go.

1. Create a database called budget, described as "Budget: categories, monthly budgets and transactions in cents, filed by Claude from what I tell it or paste in".
2. Create three tables:
   - categories: id, name (required, unique).
   - budgets: id, month (YYYY-MM, required), category_id (references categories), amount_cents (required, whole cents). One budget per category per month: make month and category_id unique together.
   - transactions: id, spent_on (date, defaults to today), amount_cents (required, whole cents: negative for spending, positive for income), payee (required), category_id (references categories, NULL for income), note.
3. Write the database a guide that says what each table is for, to reuse an existing category before adding another, never to store card or account numbers since payee and amount are enough, to insert a pasted statement in one statement and tell me which lines you weren't sure how to categorise, and the query for "how much is left in each category this month" (budget, spent and remaining per category).

From then on: when I tell you about a purchase, refund or payment in, add a transaction in the right category. When I paste lines from a statement, file them all at once. When I set a month's budget, add a row per category. Show me the tables when you're finished.

CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE budgets (
  id INTEGER PRIMARY KEY,
  month TEXT NOT NULL,
  category_id INTEGER NOT NULL REFERENCES categories(id),
  amount_cents INTEGER NOT NULL,
  UNIQUE (month, category_id)
);

CREATE TABLE transactions (
  id INTEGER PRIMARY KEY,
  spent_on TEXT NOT NULL DEFAULT (date('now')),
  amount_cents INTEGER NOT NULL,
  payee TEXT NOT NULL,
  category_id INTEGER REFERENCES categories(id),
  note TEXT
);

# budget: categories, monthly budgets, transactions

- **categories**: what money goes to. Reuse an existing one before adding another.
- **budgets**: one row per category per month (`month` is YYYY-MM), in cents.
- **transactions**: one row per purchase, refund or payment in. `amount_cents` is negative for spending, positive for income. `category_id` is NULL for income. Never store card or account numbers: payee and amount are enough.

Left this month: `SELECT c.name, b.amount_cents / 100.0 AS budget, -coalesce(sum(t.amount_cents), 0) / 100.0 AS spent, (b.amount_cents + coalesce(sum(t.amount_cents), 0)) / 100.0 AS remaining FROM budgets b JOIN categories c ON c.id = b.category_id LEFT JOIN transactions t ON t.category_id = b.category_id AND strftime('%Y-%m', t.spent_on) = b.month WHERE b.month = strftime('%Y-%m', 'now') GROUP BY b.id ORDER BY remaining`

When I paste a statement, insert all lines in one statement and tell me which ones you weren't sure how to categorise.

INSERT INTO categories (name) VALUES (?), (?), (?), (?)
-- params: ["Rent","Groceries","Eating out","Transport"]

INSERT INTO budgets (month, category_id, amount_cents) VALUES (strftime('%Y-%m', 'now'), 1, ?), (strftime('%Y-%m', 'now'), 2, ?), (strftime('%Y-%m', 'now'), 3, ?), (strftime('%Y-%m', 'now'), 4, ?)
-- params: [120000,45000,15000,8000]

INSERT INTO transactions (amount_cents, payee) VALUES (?, ?)
-- params: [315000,"Salary, Halden Works"]

INSERT INTO transactions (spent_on, amount_cents, payee, category_id) VALUES (date('now', '-4 days'), ?, ?, 2), (date('now', '-3 days'), ?, ?, 4), (date('now', '-2 days'), ?, ?, 3), (date('now', '-1 day'), ?, ?, 2)
-- params: [-6420,"Corner Market",-4900,"Tram pass",-3150,"Luna Noodle Bar",-1875,"Corner Market"]

INSERT INTO transactions (amount_cents, payee, category_id) VALUES (?, ?, ?)
-- params: [-120000,"Rent",1]

SELECT c.name, b.amount_cents / 100.0 AS budget, -coalesce(sum(t.amount_cents), 0) / 100.0 AS spent, (b.amount_cents + coalesce(sum(t.amount_cents), 0)) / 100.0 AS remaining FROM budgets b JOIN categories c ON c.id = b.category_id LEFT JOIN transactions t ON t.category_id = b.category_id AND strftime('%Y-%m', t.spent_on) = b.month WHERE b.month = strftime('%Y-%m', 'now') GROUP BY b.id ORDER BY remaining

SELECT payee, count(*) AS times, -sum(amount_cents) / 100.0 AS spent FROM transactions WHERE amount_cents < 0 AND spent_on >= date('now', '-3 months') GROUP BY payee ORDER BY spent DESC LIMIT 10

SELECT strftime('%Y-%m', spent_on) AS month, sum(CASE WHEN amount_cents > 0 THEN amount_cents ELSE 0 END) / 100.0 AS income, -sum(CASE WHEN amount_cents < 0 THEN amount_cents ELSE 0 END) / 100.0 AS spending, sum(amount_cents) / 100.0 AS saved FROM transactions GROUP BY month ORDER BY month DESC