Query Cookbook

Canonical queries for the most common questions about Treeline data.

Written for AI agents, useful for humans. If you’re an agent: read the conventions first — they’re what make naive queries return wrong numbers — then adapt the queries below. If you’re a human: paste this page into your AI tool of choice as context, or save it as a skill in whatever harness you use.

Conventions (read this first)

Amounts carry their sign. Negative = money out, positive = money in. “Spending” means amount < 0, and you’ll usually want -amount so totals read as positive numbers.

Query the transactions view, and don’t outsmart it. The view already excludes deleted rows, and split transactions are handled for you: splitting soft-deletes the parent, so only the child rows are visible. Do not add parent_transaction_id IS NULL as a “defensive” filter — that excludes every split child and undercounts.

Don’t assume what counts as “spending” or “income” — that’s the user’s definition, not yours. amount < 0 is an outflow, nothing more. The same row — a credit card payment, an investment contribution, a 529 deposit, a Venmo to a friend — is consumption to one user, savings to another, noise to a third. Setups differ just as much: some users track transactions on every account (so a card payment appears twice, an outflow and a matching inflow); others track only some accounts (so a brokerage contribution appears once, as a bare outflow from checking). And tags are entirely user-defined — there is no fixed vocabulary.

So before answering cash-flow questions, discover this user’s definitions instead of supplying your own:

-- Which accounts even carry transactions?
SELECT a.name, a.account_type, a.classification, COUNT(t.transaction_id) AS txns
FROM accounts a
LEFT JOIN transactions t USING (account_id)
GROUP BY a.name, a.account_type, a.classification
ORDER BY txns DESC;

-- What vocabulary does this user tag with?
SELECT tag, COUNT(*) AS n
FROM (SELECT UNNEST(tags) AS tag FROM transactions)
GROUP BY tag ORDER BY n DESC;

-- What are the biggest flows, and how are they tagged?
SELECT posted_date, description, amount, account_name, tags
FROM transactions
ORDER BY ABS(amount) DESC
LIMIT 20;

Build your filters from what you learn, and when a large flow is ambiguous, ask the user rather than classify it yourself. Patterns you notice — income closely tracking spending, big untagged outflows — are questions to raise with the user, not conclusions to bake into the numbers.

The queries below exclude rows with list_has_any(tags, ['transfer', 'payment']). That list is a syntactic placeholder showing where the user’s exclusions plug in — not a recommendation of what to exclude. Whatever the list ends up being, apply it at the row level, before UNNEST — a row tagged ['transfer', 'savings'] leaks into per-tag totals through its second tag if you filter after unnesting.

Net worth comes from balance_snapshots, not accounts.balance. Take the latest snapshot per account and sum the raw balances. Balances carry their own sign — liabilities are negative — so do not flip signs by classification; that adds debt back and inflates net worth. Snapshots can be sparse (manual accounts, paused syncs), so time-series queries must carry each account’s last known balance forward rather than bucketing snapshots by period.

Tags are a VARCHAR[] column. Use list_contains(tags, 'foo') for membership (wrap in COALESCE(..., false) — untagged rows are NULL), and UNNEST(tags) in a subquery to group by tag.

Use now()::TIMESTAMP::DATE for “today”. CURRENT_DATE needs the icu extension, which Treeline deliberately doesn’t load (tl query, the desktop app, and MCP all reject it) — the double cast works everywhere.

Net worth right now

WITH latest AS (
  SELECT account_id, balance
  FROM balance_snapshots
  QUALIFY ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY snapshot_time DESC) = 1
)
SELECT ROUND(SUM(balance), 2) AS net_worth FROM latest;

Latest snapshot per account, raw sum. No sign flipping (see conventions).

Net worth over time

WITH months AS (
  SELECT DISTINCT DATE_TRUNC('month', snapshot_time) AS month
  FROM balance_snapshots
),
carried AS (
  SELECT m.month, s.account_id, s.balance,
    ROW_NUMBER() OVER (
      PARTITION BY m.month, s.account_id
      ORDER BY s.snapshot_time DESC
    ) AS rn
  FROM months m
  JOIN balance_snapshots s ON s.snapshot_time < m.month + INTERVAL 1 MONTH
)
SELECT month, ROUND(SUM(balance), 2) AS net_worth
FROM carried
WHERE rn = 1
GROUP BY month
ORDER BY month;

For each month, every account contributes its last balance as of that month’s end — not just snapshots taken during the month. Real snapshot data is sparse (manual accounts, paused syncs); bucketing by snapshot month silently drops accounts and produces wild swings.

Current balance per account

WITH latest AS (
  SELECT account_id, balance, snapshot_time
  FROM balance_snapshots
  QUALIFY ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY snapshot_time DESC) = 1
)
SELECT a.name, a.account_type, a.classification, l.balance,
       CAST(l.snapshot_time AS DATE) AS as_of
FROM latest l
JOIN accounts a USING (account_id)
ORDER BY l.balance DESC;

The as_of date matters: a stale snapshot means a stale answer, and saying so builds trust.

Spending by month

SELECT DATE_TRUNC('month', posted_date) AS month, ROUND(SUM(-amount), 2) AS spending
FROM transactions
WHERE amount < 0
  AND NOT COALESCE(list_has_any(tags, ['transfer', 'payment']), false)
GROUP BY month
ORDER BY month;

The exclusion list is the user’s definition of what doesn’t count (see conventions) — without one, every outflow counts, whatever it is.

Income by month

SELECT DATE_TRUNC('month', posted_date) AS month, ROUND(SUM(amount), 2) AS income
FROM transactions
WHERE amount > 0
  AND NOT COALESCE(list_has_any(tags, ['transfer', 'payment']), false)
GROUP BY month
ORDER BY month;

Apply the user’s exclusions on the income side too — depending on their setup, inflows can include the receiving side of internal moves (a payment arriving at a tracked credit card is a positive amount there).

Savings rate

WITH monthly AS (
  SELECT DATE_TRUNC('month', posted_date) AS month,
    SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) AS income,
    SUM(CASE WHEN amount < 0 THEN -amount ELSE 0 END) AS spending
  FROM transactions
  WHERE NOT COALESCE(list_has_any(tags, ['transfer', 'payment']), false)
  GROUP BY month
)
SELECT month, income, spending,
       ROUND(100 * (income - spending) / NULLIF(income, 0), 1) AS savings_rate_pct
FROM monthly
ORDER BY month;

This is one common definition of savings rate — confirm it matches the user’s before presenting it as their savings rate. NULLIF guards months with no income. Partial months (the current one, the first synced one) will look off — consider excluding them when summarizing.

Spending by tag

SELECT tag, ROUND(SUM(-amount), 2) AS total, COUNT(*) AS txns
FROM (
  SELECT UNNEST(tags) AS tag, amount
  FROM transactions
  WHERE amount < 0
    AND posted_date >= now()::TIMESTAMP::DATE - INTERVAL 90 DAY
    AND NOT COALESCE(list_has_any(tags, ['transfer', 'payment']), false)
)
GROUP BY tag
ORDER BY total DESC;

Transfer exclusion happens inside the subquery, before UNNEST — see conventions. Note a transaction with two tags counts toward both, so tag totals can legitimately exceed total spending.

Spending trend per tag

SELECT tag, DATE_TRUNC('month', posted_date) AS month, ROUND(SUM(-amount), 2) AS total
FROM (
  SELECT UNNEST(tags) AS tag, posted_date, amount
  FROM transactions
  WHERE amount < 0
    AND NOT COALESCE(list_has_any(tags, ['transfer', 'payment']), false)
)
GROUP BY tag, month
ORDER BY tag, month;

Top merchants

SELECT description, COUNT(*) AS txns, ROUND(SUM(-amount), 2) AS total
FROM transactions
WHERE amount < 0
  AND posted_date >= now()::TIMESTAMP::DATE - INTERVAL 90 DAY
  AND NOT COALESCE(list_has_any(tags, ['transfer', 'payment']), false)
GROUP BY description
ORDER BY total DESC
LIMIT 10;

description is the raw bank string, so the same merchant can appear under several variants — good enough for a top-10, but say so when it matters.

Recurring charges & subscriptions

SELECT description,
       COUNT(DISTINCT DATE_TRUNC('month', posted_date)) AS months_seen,
       ROUND(AVG(-amount), 2) AS avg_amount,
       ROUND(MIN(-amount), 2) AS min_amount,
       ROUND(MAX(-amount), 2) AS max_amount
FROM transactions
WHERE amount < 0
GROUP BY description
HAVING months_seen >= 3
   AND (MAX(-amount) - MIN(-amount)) / NULLIF(AVG(-amount), 0) < 0.25
ORDER BY avg_amount DESC;

Heuristic: same description in 3+ distinct months with amounts within ±25% of each other. Catches subscriptions and steady bills without relying on tags; surface min ≠ max rows as possible price changes.

Largest expenses this month

SELECT posted_date, description, ROUND(-amount, 2) AS spent, tags
FROM transactions
WHERE amount < 0
  AND posted_date >= DATE_TRUNC('month', now()::TIMESTAMP::DATE)
  AND NOT COALESCE(list_has_any(tags, ['transfer', 'payment']), false)
ORDER BY amount ASC
LIMIT 15;

Untagged transactions

SELECT posted_date, description, amount, account_name
FROM transactions
WHERE tags IS NULL OR LEN(tags) = 0
ORDER BY posted_date DESC
LIMIT 25;

The review queue. If you’re tagging on the user’s behalf, propose tags and let them confirm — check their existing tag vocabulary first (SELECT DISTINCT UNNEST(tags) FROM transactions).

Budget vs actual (Budget plugin)

The Budget plugin stores a complete category set per month in plugin_budget.categories — there is no template that carries forward, so a month with no rows simply hasn’t been set up. If the tables are missing or contain no rows at all, the user likely doesn’t have the Budget plugin installed — say so rather than reporting a zero budget.

The plugin’s own matching rules, which your query must reproduce to agree with what the user sees on screen:

A category matches transactions by tag: any of tags when require_all is false, all of them when true.
amount_sign optionally restricts to inflows ('positive') or outflows ('negative') — used when one tag (a rental property, say) carries both income and expenses.
Actuals bucket by transaction_date, not the posted_date used elsewhere on this page.
Expense actuals are sign-flipped so spending reads positive; income actuals are kept as-is.
Rollovers (plugin_budget.rollovers) carry leftover budget between months, keyed by category name; a category’s balance is its incoming rollovers plus its variance.

WITH cats AS (
  SELECT * FROM plugin_budget.categories
  WHERE month = strftime(now()::TIMESTAMP, '%Y-%m')
),
roll_in AS (
  SELECT to_category, SUM(amount) AS rolled_in
  FROM plugin_budget.rollovers
  WHERE to_month = strftime(now()::TIMESTAMP, '%Y-%m')
  GROUP BY to_category
)
SELECT c.type, c.name, c.expected,
       ROUND(COALESCE(SUM(CASE WHEN c.type = 'expense' THEN -t.amount ELSE t.amount END), 0), 2) AS actual,
       ROUND(COALESCE(r.rolled_in, 0), 2) AS rolled_in,
       ROUND(COALESCE(r.rolled_in, 0)
             + CASE WHEN c.type = 'income'
                    THEN COALESCE(SUM(t.amount), 0) - c.expected
                    ELSE c.expected - COALESCE(SUM(-t.amount), 0) END, 2) AS balance
FROM cats c
LEFT JOIN transactions t
  ON strftime(t.transaction_date, '%Y-%m') = c.month
 AND len(c.tags) > 0
 AND (CASE WHEN c.require_all THEN list_has_all(COALESCE(t.tags, []), c.tags)
           ELSE list_has_any(COALESCE(t.tags, []), c.tags) END)
 AND (c.amount_sign IS NULL OR c.amount_sign = 'any'
      OR (c.amount_sign = 'positive' AND t.amount > 0)
      OR (c.amount_sign = 'negative' AND t.amount < 0))
LEFT JOIN roll_in r ON r.to_category = c.name
GROUP BY c.type, c.name, c.expected, r.rolled_in, c.sort_order
ORDER BY c.type DESC, c.sort_order;

Swap the month filter for any 'YYYY-MM' string to look at past months. A positive balance on an expense category is room left to spend; negative is overspend.

Budget coverage: double counted and unbudgeted (Budget plugin)

Tag matching has two blind spots the budget table can’t show you. A transaction carrying several tags can match several categories and be counted once in each. A transaction can match no category at all — a tag nobody budgeted, or a typo. Budget plugin 0.3.0 and later expose both through plugin_budget.coverage('YYYY-MM'), a table macro that returns one row per transaction in that month with the categories it matched. It applies the same rules as the query above, so its answers agree with what the user sees on screen. Older installs don’t have it — fall back to the join above, and say so.

SELECT * FROM plugin_budget.coverage('2026-08')
WHERE match_count <> 1
ORDER BY match_count DESC, ABS(amount) DESC;

match_count >= 2 is double counted, 0 is unbudgeted. Neither is automatically wrong. Overlap is how people build umbrella categories — an “Everything discretionary” line sitting over “Food” and “Shopping” — so report it and ask, rather than assuming a category needs narrowing.

Unbudgeted tags are easier to read grouped. plugin_budget.ignored_tags is the user’s list of tags that deliberately have no category, so exclude it:

SELECT tag, COUNT(*) AS txns, ROUND(SUM(amount), 2) AS total
FROM (
  SELECT UNNEST(tags) AS tag, amount
  FROM plugin_budget.coverage('2026-08')
  WHERE match_count = 0 AND LEN(tags) > 0
)
WHERE tag NOT IN (SELECT tag FROM plugin_budget.ignored_tags)
GROUP BY tag
ORDER BY ABS(total) DESC;

What’s left is a mix, and the mix is the point. transfer or payment will sit there until the user either budgets it or adds it to ignored_tags — that’s their call, not yours. A tag one character away from one the categories already watch (spendig next to spending) is worth raising as a possible typo, but a brand-new tag is just as likely to be a category the user hasn’t created yet. Ask which it is.

Untagged transactions are a separate problem with its own queue — see Untagged transactions above.

For a summary across the current month and the two before it, without writing any of this yourself:

SELECT * FROM plugin_budget.doctor;

Two rows, double_counted and unbudgeted_tags, each with a status, a one-line message, and sample rows in details. tl doctor reports the same two as budget.double_counted and budget.unbudgeted_tags alongside its built-in checks. Months with no categories are skipped rather than counted as entirely unbudgeted. To work across months yourself, query the plugin_budget.coverage_all view the macro is built on — same columns plus month.

Setting up a new budget month

Because each month is self-contained, someone — the user or their agent — has to create it. First see what exists:

SELECT month, COUNT(*) AS categories,
       ROUND(SUM(expected) FILTER (WHERE type = 'income'), 2) AS planned_income,
       ROUND(SUM(expected) FILTER (WHERE type = 'expense'), 2) AS planned_spending
FROM plugin_budget.categories
GROUP BY month
ORDER BY month;

If the latest month is behind strftime(now()::TIMESTAMP, '%Y-%m'), copy it forward — same categories, fresh UUIDs:

INSERT INTO plugin_budget.categories
  (category_id, month, type, name, expected, tags, require_all, amount_sign, sort_order)
SELECT uuid()::VARCHAR, strftime(now()::TIMESTAMP, '%Y-%m'),
       type, name, expected, tags, require_all, amount_sign, sort_order
FROM plugin_budget.categories
WHERE month = (SELECT max(month) FROM plugin_budget.categories
               WHERE month < strftime(now()::TIMESTAMP, '%Y-%m'))
  AND NOT EXISTS (SELECT 1 FROM plugin_budget.categories
                  WHERE month = strftime(now()::TIMESTAMP, '%Y-%m'));

This is a write, so it needs tl query --allow-writes or the MCP query_write tool; the NOT EXISTS guard makes it safe to run repeatedly. The copied expected amounts are last month’s plan, not this month’s — review them with the user afterward rather than assuming they still apply.

Exploring further

SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'main';

DESCRIBE transactions (or any table) shows columns. The full schema reference is at Database Schema. Prefer the views (transactions, accounts, balance_snapshots) — the sys_* tables carry raw provider data and include soft-deleted rows.