YNAB Transaction Review Skill

Purpose

Batch-review unapproved YNAB transactions: auto-approve matched, detect cross-account duplicates, classify by memo patterns, and present ambiguous items for user decision. Designed for Vietnamese bank statements (TCB, SCB, MoMo) imported via bank-statement-wrangler pipeline.

Architecture

ynab-dump CLI → staging/transactions.json → DuckDB queries (READ)
                                                    ↓
                                          classify + present
                                                    ↓
                                          YNAB MCP (WRITE only)

DuckDB for reads — query staging/transactions.json via fs:query_duckdb tool. Full SQL: JOINs, GROUP BY, window functions, CTEs. No 80KB truncation, no narrow date windows.

YNAB MCP for writes — utils:ynab__updateTransactions via call_tool_destructive for approvals, categorization, memo updates, transfer conversion.

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:

sql
1SELECT
2    COUNT(*) AS total,
3    MIN(date) AS earliest,
4    MAX(date) AS latest,
5    SUM(CASE WHEN approved = false THEN 1 ELSE 0 END) AS unapproved,
6    SUM(CASE WHEN approved = false AND matched_transaction_id IS NOT NULL THEN 1 ELSE 0 END) AS matched_unapproved
7FROM read_json_auto('scripts/bank-statement-wrangler/staging/transactions.json')
8WHERE 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.

sql
1SELECT id, account_name, date, memo, amount/1000 AS amount_vnd
2FROM read_json_auto('TXN_JSON')
3WHERE deleted = false
4  AND approved = false
5  AND matched_transaction_id IS NOT NULL
6ORDER 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:

sql
1WITH txns AS (
2    SELECT *, amount/1000 AS amount_vnd
3    FROM read_json_auto('TXN_JSON')
4    WHERE deleted = false
5)
6SELECT
7    a.id AS id_a, a.account_name AS acct_a,
8    b.id AS id_b, b.account_name AS acct_b,
9    a.date AS date_a, b.date AS date_b,
10    a.amount_vnd AS amt_a, b.amount_vnd AS amt_b,
11    a.memo AS memo_a, b.memo AS memo_b,
12    a.approved AS appr_a, b.approved AS appr_b
13FROM txns a
14JOIN txns b
15    ON ABS(a.amount) = ABS(b.amount)
16    AND a.account_id != b.account_id
17    AND a.amount != 0
18    AND ABS(DATE_DIFF('day', a.date::DATE, b.date::DATE)) <= 3
19    AND a.id < b.id
20WHERE NOT (a.approved AND b.approved)
21ORDER 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:

sql
1SELECT id, date, amount/1000 AS amount_vnd, memo,
2       category_name, payee_name, account_name
3FROM read_json_auto('TXN_JSON')
4WHERE deleted = false
5  AND approved = false
6  AND matched_transaction_id IS NULL
7  AND account_name LIKE '%tcb%'  -- or '%scb%' for SCB subagent
8ORDER 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):

javascript
1call_tool('utils', 'ynab__updateTransactions', {
2  plan_id: 'last-used',
3  transactions: [
4    {id: 'uuid', category_id: 'cat-uuid', approved: true},
5    {id: 'uuid', category_id: 'cat-uuid', memo: 'Human-readable memo', approved: true},
6    {id: 'uuid', payee_id: 'transfer-payee-uuid', approved: true}
7  ]
8});

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:

sql
1SELECT account_name, COUNT(*) AS remaining
2FROM read_json_auto('TXN_JSON')
3WHERE deleted = false AND approved = false
4GROUP 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 |