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 (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 = '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.

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.