Ynab Review

Prerequisites

• Staging data: user runs ynab-dump --since <window> before invoking this skill
• YNAB MCP tools on utils server, prefix ynab__, use call_tool_destructive
• DuckDB query tool on fs server: fs:query_duckdb via call_tool_read
• Discover tools with: retrieve_tools("ynab budget transactions") and retrieve_tools("duckdb query")

Staging JSON Path

scripts/bank-statement-wrangler/staging/transactions.json

All DuckDB queries use this path. Shorthand in examples below: TXN_JSON.

Execution Protocol

Phase 0: Verify Staging Data

Before anything, confirm staging data exists and is fresh:

SELECT
    COUNT(*) AS total,
    MIN(date) AS earliest,
    MAX(date) AS latest,
    SUM(CASE WHEN approved = false THEN 1 ELSE 0 END) AS unapproved,
    SUM(CASE WHEN approved = false AND matched_transaction_id IS NOT NULL THEN 1 ELSE 0 END) AS matched_unapproved
FROM read_json_auto('scripts/bank-statement-wrangler/staging/transactions.json')
WHERE deleted = false

If no data or stale dates → ask user to run ynab-dump --since <window>.

Phase 1: Auto-Resolve (no user input needed)

Step 1.1: Bulk approve matched transactions

Matched = matched_transaction_id IS NOT NULL and approved = false. These are bank-imported transactions that YNAB already paired with manual entries. Approve immediately, zero review needed.

SELECT id, account_name, date, memo, amount/1000 AS amount_vnd
FROM read_json_auto('TXN_JSON')
WHERE deleted = false
  AND approved = false
  AND matched_transaction_id IS NOT NULL
ORDER BY account_name, date

Collect all IDs → batch updateTransactions with {id, approved: true} (max 50 per call).

Step 1.2: Detect cross-account duplicates/transfers

Find transaction pairs across different accounts with same |amount| within ±3 days, where at least one side is unapproved:

WITH txns AS (
    SELECT *, amount/1000 AS amount_vnd
    FROM read_json_auto('TXN_JSON')
    WHERE deleted = false
)
SELECT
    a.id AS id_a, a.account_name AS acct_a,
    b.id AS id_b, b.account_name AS acct_b,
    a.date AS date_a, b.date AS date_b,
    a.amount_vnd AS amt_a, b.amount_vnd AS amt_b,
    a.memo AS memo_a, b.memo AS memo_b,
    a.approved AS appr_a, b.approved AS appr_b
FROM txns a
JOIN txns b
    ON ABS(a.amount) = ABS(b.amount)
    AND a.account_id != b.account_id
    AND a.amount != 0
    AND ABS(DATE_DIFF('day', a.date::DATE, b.date::DATE)) <= 3
    AND a.id < b.id
WHERE NOT (a.approved AND b.approved)
ORDER BY a.date DESC, ABS(a.amount) DESC

Present to user as table. Possible actions per pair:

• Confirmed transfer → convert both sides: set payee_id to destination account's transfer payee
• Confirmed duplicate → delete the imported copy
• Not related → proceed to classify individually (coincidental amount match)

Phase 2: Classify Unmatched (subagent-parallelizable)

⚠️ BLOCKING: Phase 1.2 (duplicates) must COMPLETE before spawning Phase 2 subagents. Running them in parallel caused SCB "CIA CASH IN" records to be classified as MoMo transfers when they were actually duplicates of approved TCB records. The duplicates agent identifies which transactions to EXCLUDE from classification.

After Phase 1, query remaining unmatched unapproved per account:

SELECT id, date, amount/1000 AS amount_vnd, memo,
       category_name, payee_name, account_name
FROM read_json_auto('TXN_JSON')
WHERE deleted = false
  AND approved = false
  AND matched_transaction_id IS NULL
  AND account_name LIKE '%tcb%'  -- or '%scb%' for SCB subagent
ORDER BY date

Subagent strategy: Spawn parallel subagents per account, each with:

• This skill's pattern rules (copy the Auto-Categorization Rules section)
• The DuckDB query tool access
• Account filter in their query
• Category/account ID tables
• Instructions to return a JSON array of {id, category_id, approved: true, memo?} objects

Classification tiers:

1. Auto-categorize — high-confidence pattern match → include in batch
2. Ambiguous — return to main agent for user presentation

Phase 3: Present & Execute

Step 3.1: Present ambiguous items

DO NOT use AskUserQuestion for multi-item reviews. Present as inline markdown table:

| # | Account | Date | Memo | Amount | Proposed |
|---|---------|------|------|--------|----------|

Let user respond naturally in chat with decisions.

Step 3.2: Execute updates

Batch all confirmed categorizations into updateTransactions calls (max 50 per call):

call_tool('utils', 'ynab__updateTransactions', {
  plan_id: 'last-used',
  transactions: [
    {id: 'uuid', category_id: 'cat-uuid', approved: true},
    {id: 'uuid', category_id: 'cat-uuid', memo: 'Human-readable memo', approved: true},
    {id: 'uuid', payee_id: 'transfer-payee-uuid', approved: true}
  ]
});

Memo updates: When original memo is cryptic (e.g., QR S624260M, MCCVMAYBIHYPKNP 464728034) and user identifies it, include a memo field with human-readable description.

Step 3.3: Verify

User re-runs ynab-dump → check counts drop to zero. Or query staging directly if user hasn't re-dumped:

SELECT account_name, COUNT(*) AS remaining
FROM read_json_auto('TXN_JSON')
WHERE deleted = false AND approved = false
GROUP BY 1 ORDER BY 2 DESC

Note: staging data is a snapshot — approvals via MCP won't reflect until next dump. Use this query only as pre-dump sanity check against the user's in-session notes.

Auto-Categorization Rules

TCB Memo Patterns

TCB Food Keywords (in memo → 🛒 food & drinks)

cafe, coffee, pho, mi, bun, com, banh, hu tiu, caztus, doner, kebab, bap rang, bap, bot chien, matcha, cookie, com tam, tra sua, sinh to, tau hu, oc, lau, nem, nui, taco, pizza, breakfast, snack, snacks, groceries, bluish, hadilao, bon chon, spicy box

TCB Merchant Keywords (in POS/Internet memo → 🛒 food & drinks)

HIGHLANDS, 7-ELEVEN, MINISTOP, CARL JR, METRO HCM, PAYOO, FAMIMA, CIRCLE K, GS25, THUC COFFEE, QUAY NFK

SCB Memo Patterns

MoMo Memo Patterns

Account IDs

Category IDs

Personal Context (CRITICAL — DO NOT LOSE)

Vietnamese Memo Decoder

Workflow Efficiency Rules

1. DuckDB for reads, MCP for writes. Never use YNAB MCP for reading transactions.
2. Matched = auto-approve. Zero user input. Execute first.
3. ⚠️ Duplicates BLOCK categorization. Phase 1.2 must COMPLETE before spawning Phase 2 subagents. Running them in parallel causes SCB CIA CASH IN records to be misclassified as transfers when they're actually duplicates of TCB records.
4. Subagents per account with >0 unapproved. Skip accounts with 0 unapproved for classification (e.g., MoMo). But ALL accounts (including 0-unapproved ones) must be in Phase 1.2 duplicate scan — approved MoMo/wallet txns are counterparts to unapproved SCB/TCB records.
5. Batch updates ≤ 50 per updateTransactions call.
6. No AskUserQuestion for reviews. Use inline markdown tables with account column.
7. Execute in rounds: matched → duplicates → auto-patterns → present ambiguous → user confirms → execute.
8. SCB fee pairing: Foreign charge + identical memo at -10,000₫ → categorize both the same.
9. MoMo bill pairs: Outflow (payment) + inflow (top-up) come together. Categorize outflow, convert inflow to transfer.
10. Memo updates: When user identifies a cryptic memo, include human-readable memo field in the update.
11. Verify via re-dump. User runs ynab-dump again after each round to confirm counts.
12. Salary/income inflows. Approve without setting category_id — YNAB routes uncategorized inflows to "Ready to Assign" automatically.
13. SCB CIA CASH IN dedup. Before classifying as MoMo transfer, extract txn ID from memo and check if TCB has a matching approved Nạp tiền...{txn ID}. If match → SCB is a duplicate, delete it. This is the #1 source of double-counting.

Subagent Prompt Template

When spawning per-account classification subagents, inject:

## Task: Classify {ACCOUNT} unapproved transactions

Query staging data via DuckDB, classify each transaction, return results.

### Tool Access
- `fs:query_duckdb` via `mcp__proxy__call_tool_read` — SQL on staging JSON
- Staging path: scripts/bank-statement-wrangler/staging/transactions.json

### Your Query
SELECT id, date, amount/1000 AS amount_vnd, memo, category_name, payee_name
FROM read_json_auto('scripts/bank-statement-wrangler/staging/transactions.json')
WHERE deleted = false AND approved = false AND matched_transaction_id IS NULL
  AND account_name LIKE '%{ACCOUNT_FILTER}%'
ORDER BY date

### Classification Rules
{PASTE AUTO-CATEGORIZATION RULES FOR THIS ACCOUNT}
{PASTE FOOD KEYWORDS}
{PASTE PERSONAL CONTEXT}
{PASTE VIETNAMESE MEMO DECODER}

### Category IDs
{PASTE CATEGORY ID TABLE}

### Output Format
Return TWO lists:
1. **auto_categorize** — JSON array, one object per line. Main agent will paste directly into `updateTransactions`:
   ```json
   {"id": "full-uuid", "category_id": "full-uuid", "approved": true}
   {"id": "full-uuid", "payee_id": "full-uuid", "approved": true}
   {"id": "full-uuid", "category_id": "full-uuid", "memo": "human-readable", "approved": true}

CRITICAL: Use full UUIDs from the query results — no placeholders, no abbreviations.

2. ambiguous — markdown table with full IDs so main agent can execute without re-querying: | # | ID | Date | Memo | Amount | Proposed | Why ambiguous |