${title} — Spend Agent

[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_262":263,"_264":265},"routes/_app.x.$handle.$name",{"_100":99,"_32":45,"_108":90,"_27":109,"_13":14,"_110":-5,"_11":111,"_112":113,"_114":115,"_116":117,"_118":119,"_20":-5,"_120":-5,"_121":-5,"_122":25,"_123":124,"_125":59,"_126":-5,"_127":-5,"_128":129},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_43":44,"_32":45,"_38":46,"_47":-5,"_48":25,"_49":50,"_51":50,"_52":53,"_54":-5,"_18":55,"_56":57,"_58":59,"_60":-5,"_61":-5,"_62":63,"_64":65,"_66":67,"_68":69,"_70":71,"_72":-5,"_73":74,"_75":74,"_76":77,"_78":77,"_79":77,"_80":74,"_81":74,"_82":83,"_84":85,"_86":87,"_88":25},"version",198,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":23,"_24":25,"_26":25,"_27":28,"_29":28},"file",{"_30":31,"_32":28,"_33":34,"_35":37,"_38":39,"_40":14,"_41":42,"_24":25},"previous",{"_30":31,"_32":28,"_33":34,"_35":36},"deleted",false,"deletedHere","path","db.ts","latestPath","fileId","89fb741c-5aab-11f1-8905-ee650bb23af1","name","fileType","script","content","// Spend Agent — Database Schema & Helpers\n//\n// Uses the val-scoped SQLite database (`std/sqlite/main.ts`) — each val gets its\n// own isolated database, so plain unprefixed table names are safe (no collision\n// with other vals). The legacy account-global `std/sqlite` is shared across all\n// vals and is intentionally NOT used here.\nimport sqlite from 'https://esm.town/v/std/sqlite/main.ts'\nimport { CAT_COLORS } from './lib.ts'\n\nexport { sqlite }\n\n// Canonical table names — referenced by name from other modules so a future\n// rename only touches this file.\nexport const T = {\n tx: 'transactions',\n summaries: 'monthly_summaries',\n config: 'config',\n tellerTokens: 'teller_tokens',\n} as const\n\n// ── Schema ──────────────────────────────────────────────────────\nexport async function ensureSchema() {\n await sqlite.batch([\n `CREATE TABLE IF NOT EXISTS transactions (\n id TEXT PRIMARY KEY,\n source TEXT NOT NULL,\n source_id TEXT,\n date TEXT NOT NULL,\n description TEXT NOT NULL,\n amount REAL NOT NULL,\n abs_amount REAL NOT NULL,\n account_name TEXT,\n institution TEXT,\n category TEXT,\n category_source TEXT,\n llm_confidence REAL,\n llm_reasoning TEXT,\n is_transfer INTEGER DEFAULT 0,\n is_income INTEGER DEFAULT 0,\n is_duplicate INTEGER DEFAULT 0,\n duplicate_of TEXT,\n status TEXT DEFAULT 'posted',\n created_at TEXT DEFAULT (datetime('now'))\n )`,\n `CREATE TABLE IF NOT EXISTS monthly_summaries (\n month TEXT PRIMARY KEY,\n total_income REAL,\n total_spend REAL,\n total_transfers REAL,\n net_cash_flow REAL,\n category_breakdown TEXT,\n tx_count INTEGER\n )`,\n `CREATE TABLE IF NOT EXISTS config (\n key TEXT PRIMARY KEY,\n value TEXT\n )`,\n `CREATE TABLE IF NOT EXISTS teller_tokens (\n id TEXT PRIMARY KEY,\n access_token TEXT NOT NULL,\n enrollment_id TEXT,\n institution TEXT NOT NULL,\n linked_at TEXT DEFAULT (datetime('now'))\n )`,\n ])\n}\n\n// ── Config helpers ──────────────────────────────────────────────\nexport async function getConfig(key: string, fallback = ''): Promise {\n const res = await sqlite.execute({\n sql: 'SELECT value FROM config WHERE key = ?',\n args: [key],\n })\n return res.rows.length > 0 ? String(res.rows[0][0] ?? fallback) : fallback\n}\n\nexport async function setConfig(key: string, value: string) {\n await sqlite.execute({\n sql: 'INSERT OR REPLACE INTO config (key, value) VALUES (?, ?)',\n args: [key, value],\n })\n}\n\n// ── Monthly summary recompute ───────────────────────────────────\nexport async function recomputeMonth(month: string) {\n const stats = await sqlite.execute({\n sql: `SELECT\n SUM(CASE WHEN is_income = 1 AND is_transfer = 0 THEN abs_amount ELSE 0 END),\n SUM(CASE WHEN is_income = 0 AND is_transfer = 0 AND is_duplicate = 0 THEN abs_amount ELSE 0 END),\n SUM(CASE WHEN is_transfer = 1 THEN abs_amount ELSE 0 END),\n COUNT(*)\n FROM transactions WHERE substr(date, 1, 7) = ?`,\n args: [month],\n })\n const r = stats.rows[0] as any[]\n const income = Number(r[0]) || 0\n const spend = Number(r[1]) || 0\n const transfers = Number(r[2]) || 0\n const txCount = Number(r[3]) || 0\n\n const catRes = await sqlite.execute({\n sql: `SELECT category, SUM(abs_amount) FROM transactions\n WHERE substr(date, 1, 7) = ? AND is_income = 0 AND is_transfer = 0 AND is_duplicate = 0\n GROUP BY category ORDER BY SUM(abs_amount) DESC`,\n args: [month],\n })\n const cats: Record = {}\n for (const cr of catRes.rows)\n cats[String(cr[0] || 'Other')] = Math.round(Number(cr[1]) * 100) / 100\n\n await sqlite.execute({\n sql: `INSERT OR REPLACE INTO monthly_summaries (month, total_income, total_spend, total_transfers, net_cash_flow, category_breakdown, tx_count)\n VALUES (?, ?, ?, ?, ?, ?, ?)`,\n args: [\n month,\n Math.round(income * 100) / 100,\n Math.round(spend * 100) / 100,\n Math.round(transfers * 100) / 100,\n Math.round((income - spend) * 100) / 100,\n JSON.stringify(cats),\n txCount,\n ],\n })\n}\n\n// ── Default system prompt ───────────────────────────────────────\nexport const DEFAULT_SYSTEM_PROMPT = `You are a personal finance categorizer. Classify each transaction into exactly one category.\n\nCategories are enforced by schema — focus on rules for ambiguous cases:\n\nRules:\n- Venmo/Zelle payments TO someone are the actual spend category, not \"Transfers\"\n- Venmo/Zelle payments FROM someone are income or reimbursements\n- Credit card payments and bank transfers between own accounts are \"Transfers\"\n- Recurring charges (Netflix, Spotify, gym) are \"Subscriptions\"\n- Restaurant and food delivery (DoorDash, UberEats, Grubhub) are \"Food\"\n- Uber/Lyft rides are \"Transportation\"\n- Amazon purchases are \"Shopping\" unless clearly groceries\n\nIncome/credit classification:\n- PAYROLL, direct deposits from employers → \"Salary\"\n- Venmo/Zelle payments FROM someone for rent → \"Housing\"\n- Reimbursements: use the category of what was reimbursed (e.g., trip reimbursement → \"Travel\", utility reimbursement → \"Bills\")\n- Tax refunds (IRS, state) → \"Income\"\n- Credit card statement credits, hotel credits, rewards → use underlying category (travel credit → \"Travel\", entertainment credit → \"Entertainment\")\n- PayPal/Cash App transfers to yourself → \"Transfers\"\n- Family gifts via Zelle/Venmo → \"Gifts\"\n- Other misc income → \"Income\"`\n\n// ── Categories ──────────────────────────────────────────────────\nexport const DEFAULT_CATEGORIES = [\n 'Housing',\n 'Food',\n 'Transportation',\n 'Shopping',\n 'Entertainment',\n 'Health',\n 'Subscriptions',\n 'Bills',\n 'Travel',\n 'Investments',\n 'Transfers',\n 'Salary',\n 'Income',\n 'Gifts',\n 'Other',\n] as const\n\n// Keep CATEGORIES as alias for backward compat\nexport const CATEGORIES = DEFAULT_CATEGORIES\n\nexport type Category = (typeof DEFAULT_CATEGORIES)[number]\n\n// Get user-customized categories (falls back to defaults)\nexport async function getCategories(): Promise {\n const stored = await getConfig('categories', '')\n if (stored) {\n try {\n return JSON.parse(stored) as string[]\n } catch {}\n }\n return [...DEFAULT_CATEGORIES]\n}\n\nexport async function setCategories(cats: string[]) {\n // Always ensure \"Other\" exists as fallback\n if (!cats.includes('Other')) cats.push('Other')\n await setConfig('categories', JSON.stringify(cats))\n}\n\n// Read user-set category colors (overlays CAT_COLORS defaults).\nexport async function getCategoryColors(): Promise> {\n const raw = await getConfig('category_colors', '')\n if (!raw) return {}\n try {\n return JSON.parse(raw) as Record\n } catch {\n return {}\n }\n}\n\nexport async function setCategoryColors(colors: Record) {\n await setConfig('category_colors', JSON.stringify(colors))\n}\n\n// Merge user-saved colors into the in-process CAT_COLORS map. Called once per\n// request so the dashboard and Settings UI both see the current palette.\nexport async function loadCategoryColors(): Promise {\n const userColors = await getCategoryColors()\n for (const [name, hex] of Object.entries(userColors)) {\n if (hex) CAT_COLORS[name] = hex\n }\n}\n\nexport type CategoryRow = { original: string; current: string; color?: string }\n\nexport type SaveCategoriesResult = {\n added: string[]\n renamed: { from: string; to: string; count: number }[]\n removed: string[]\n unclassifiedCount: number\n saved: string[]\n colors: Record\n}\n\nexport async function saveCategoriesAndCleanup(rows: CategoryRow[]): Promise {\n // Normalize each row, dedupe by current name (case-sensitive), drop empty\n const seen = new Set()\n const clean: CategoryRow[] = []\n for (const r of rows || []) {\n const current = (r?.current || '').trim()\n if (!current || seen.has(current)) continue\n seen.add(current)\n clean.push({\n original: (r?.original || '').trim(),\n current,\n color: r?.color,\n })\n }\n\n const oldCats = await getCategories()\n const oldColors = await getCategoryColors()\n const newNames = new Set(clean.map((r) => r.current))\n\n // Anything in old that doesn't appear as anyone's \"original\" is REMOVED.\n // (Rename rows have original=oldName so the oldName isn't considered removed.)\n const claimedOriginals = new Set(clean.map((r) => r.original).filter(Boolean))\n const removed = oldCats.filter(\n (c) => c !== 'Other' && !newNames.has(c) && !claimedOriginals.has(c)\n )\n\n const added: string[] = []\n const renamed: { from: string; to: string; count: number }[] = []\n const newColors: Record = {}\n\n for (const row of clean) {\n if (row.color) newColors[row.current] = row.color\n if (!row.original) {\n // Brand-new category\n if (!oldCats.includes(row.current)) added.push(row.current)\n continue\n }\n if (row.original === row.current) continue // kept as-is\n // Rename: update transactions to the new name\n const countRes = await sqlite.execute({\n sql: 'SELECT COUNT(*) FROM transactions WHERE category = ?',\n args: [row.original],\n })\n const count = Number((countRes.rows[0] as any[])[0]) || 0\n if (count > 0) {\n await sqlite.execute({\n sql: 'UPDATE transactions SET category = ? WHERE category = ?',\n args: [row.current, row.original],\n })\n }\n renamed.push({ from: row.original, to: row.current, count })\n }\n\n // Unclassify transactions tied to removed categories\n let unclassifiedCount = 0\n for (const cat of removed) {\n const countRes = await sqlite.execute({\n sql: 'SELECT COUNT(*) FROM transactions WHERE category = ?',\n args: [cat],\n })\n const n = Number((countRes.rows[0] as any[])[0]) || 0\n if (n === 0) continue\n await sqlite.execute({\n sql: `UPDATE transactions\n SET category = NULL, category_source = NULL,\n llm_confidence = NULL, llm_reasoning = NULL\n WHERE category = ?`,\n args: [cat],\n })\n unclassifiedCount += n\n }\n\n // Persist new categories list (current names)\n await setCategories(clean.map((r) => r.current))\n\n // Persist new colors (drop entries for removed categories, propagate renames)\n const finalColors: Record = {}\n for (const r of clean) {\n const c = newColors[r.current] || oldColors[r.original] || oldColors[r.current]\n if (c) finalColors[r.current] = c\n }\n await setCategoryColors(finalColors)\n // Reflect into the in-process map so subsequent renders see it\n for (const [name, hex] of Object.entries(finalColors)) CAT_COLORS[name] = hex\n for (const cat of removed) delete CAT_COLORS[cat]\n\n const saved = await getCategories()\n return {\n added,\n renamed,\n removed,\n unclassifiedCount,\n saved,\n colors: finalColors,\n }\n}\n\n// ── Demo seed data (template only) ──────────────────────────────\n//\n// Inserts ~36 realistic transactions across the last 3 calendar months so a\n// remixer sees a populated dashboard on first load. No-op if the table already\n// has rows. Dates are computed from `now` so the demo always looks fresh.\n//\n// Most rows are pre-classified (`category_source = 'seed'`); a few are left\n// unclassified so the \"Review & Classify\" banner appears and the remixer can\n// experience the LLM classification flow on first visit.\n\ntype SeedRow = {\n monthOffset: number // 0 = current month, 1 = last, 2 = two ago\n day: number\n description: string\n amount: number // negative = spend, positive = credit\n account: string\n institution: string\n category: string | null // null leaves it for the classifier\n isIncome?: 0 | 1\n}\n\nconst SEED_ROWS: SeedRow[] = [\n // Current month\n {\n monthOffset: 0,\n day: 1,\n description: 'PAYROLL DIRECT DEPOSIT — ACME CORP',\n amount: 5800.0,\n account: 'Chase Checking',\n institution: 'Chase',\n category: 'Salary',\n isIncome: 1,\n },\n {\n monthOffset: 0,\n day: 1,\n description: 'RENT — 123 MAIN ST APARTMENTS',\n amount: -2400.0,\n account: 'Chase Checking',\n institution: 'Chase',\n category: 'Housing',\n },\n {\n monthOffset: 0,\n day: 2,\n description: 'NETFLIX.COM',\n amount: -15.49,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Subscriptions',\n },\n {\n monthOffset: 0,\n day: 3,\n description: 'WHOLE FOODS MARKET',\n amount: -87.43,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Food',\n },\n {\n monthOffset: 0,\n day: 4,\n description: 'UBER TRIP',\n amount: -18.2,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Transportation',\n },\n {\n monthOffset: 0,\n day: 5,\n description: 'DOORDASH',\n amount: -34.5,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Food',\n },\n {\n monthOffset: 0,\n day: 6,\n description: 'SPOTIFY USA',\n amount: -10.99,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Subscriptions',\n },\n {\n monthOffset: 0,\n day: 7,\n description: 'AMAZON.COM',\n amount: -42.18,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Shopping',\n },\n {\n monthOffset: 0,\n day: 8,\n description: 'SHELL GAS STATION',\n amount: -52.3,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Transportation',\n },\n {\n monthOffset: 0,\n day: 9,\n description: \"TRADER JOE'S\",\n amount: -64.12,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Food',\n },\n {\n monthOffset: 0,\n day: 10,\n description: 'AMC THEATER',\n amount: -28.5,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: null,\n },\n {\n monthOffset: 0,\n day: 11,\n description: 'VENMO PAYMENT — REIMBURSEMENT FROM SAM',\n amount: 45.0,\n account: 'Venmo',\n institution: 'Venmo',\n category: null,\n isIncome: 1,\n },\n\n // Last month\n {\n monthOffset: 1,\n day: 1,\n description: 'PAYROLL DIRECT DEPOSIT — ACME CORP',\n amount: 5800.0,\n account: 'Chase Checking',\n institution: 'Chase',\n category: 'Salary',\n isIncome: 1,\n },\n {\n monthOffset: 1,\n day: 1,\n description: 'RENT — 123 MAIN ST APARTMENTS',\n amount: -2400.0,\n account: 'Chase Checking',\n institution: 'Chase',\n category: 'Housing',\n },\n {\n monthOffset: 1,\n day: 2,\n description: 'NETFLIX.COM',\n amount: -15.49,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Subscriptions',\n },\n {\n monthOffset: 1,\n day: 3,\n description: 'WHOLE FOODS MARKET',\n amount: -112.88,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Food',\n },\n {\n monthOffset: 1,\n day: 5,\n description: 'UBER EATS',\n amount: -28.45,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Food',\n },\n {\n monthOffset: 1,\n day: 7,\n description: 'AMAZON.COM',\n amount: -156.22,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Shopping',\n },\n {\n monthOffset: 1,\n day: 8,\n description: 'SPOTIFY USA',\n amount: -10.99,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Subscriptions',\n },\n {\n monthOffset: 1,\n day: 10,\n description: 'CVS PHARMACY',\n amount: -18.34,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Health',\n },\n {\n monthOffset: 1,\n day: 12,\n description: 'DELTA AIRLINES',\n amount: -342.5,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Travel',\n },\n {\n monthOffset: 1,\n day: 14,\n description: 'HILTON HOTEL',\n amount: -218.0,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Travel',\n },\n {\n monthOffset: 1,\n day: 16,\n description: \"TRADER JOE'S\",\n amount: -71.9,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Food',\n },\n {\n monthOffset: 1,\n day: 18,\n description: 'VENMO TO MOM — GIFT',\n amount: -100.0,\n account: 'Venmo',\n institution: 'Venmo',\n category: 'Gifts',\n },\n {\n monthOffset: 1,\n day: 20,\n description: 'SHELL GAS STATION',\n amount: -48.75,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Transportation',\n },\n {\n monthOffset: 1,\n day: 22,\n description: 'COMCAST INTERNET',\n amount: -89.99,\n account: 'Chase Checking',\n institution: 'Chase',\n category: 'Bills',\n },\n {\n monthOffset: 1,\n day: 25,\n description: 'VANGUARD INVEST',\n amount: -500.0,\n account: 'Chase Checking',\n institution: 'Chase',\n category: 'Investments',\n },\n {\n monthOffset: 1,\n day: 28,\n description: 'CHIPOTLE',\n amount: -14.2,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Food',\n },\n\n // Two months ago\n {\n monthOffset: 2,\n day: 1,\n description: 'PAYROLL DIRECT DEPOSIT — ACME CORP',\n amount: 5800.0,\n account: 'Chase Checking',\n institution: 'Chase',\n category: 'Salary',\n isIncome: 1,\n },\n {\n monthOffset: 2,\n day: 1,\n description: 'RENT — 123 MAIN ST APARTMENTS',\n amount: -2400.0,\n account: 'Chase Checking',\n institution: 'Chase',\n category: 'Housing',\n },\n {\n monthOffset: 2,\n day: 2,\n description: 'NETFLIX.COM',\n amount: -15.49,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Subscriptions',\n },\n {\n monthOffset: 2,\n day: 4,\n description: 'WHOLE FOODS MARKET',\n amount: -98.22,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Food',\n },\n {\n monthOffset: 2,\n day: 6,\n description: 'LYFT',\n amount: -22.4,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Transportation',\n },\n {\n monthOffset: 2,\n day: 8,\n description: 'SPOTIFY USA',\n amount: -10.99,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Subscriptions',\n },\n {\n monthOffset: 2,\n day: 10,\n description: 'AMAZON.COM',\n amount: -78.15,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Shopping',\n },\n {\n monthOffset: 2,\n day: 12,\n description: \"TRADER JOE'S\",\n amount: -55.3,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Food',\n },\n {\n monthOffset: 2,\n day: 15,\n description: 'VENMO FROM ALEX — DINNER REIMBURSEMENT',\n amount: 32.5,\n account: 'Venmo',\n institution: 'Venmo',\n category: 'Food',\n isIncome: 1,\n },\n {\n monthOffset: 2,\n day: 18,\n description: 'EQUINOX MEMBERSHIP',\n amount: -185.0,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: null,\n },\n {\n monthOffset: 2,\n day: 20,\n description: 'PG&E ELECTRIC',\n amount: -94.2,\n account: 'Chase Checking',\n institution: 'Chase',\n category: 'Bills',\n },\n {\n monthOffset: 2,\n day: 22,\n description: 'STARBUCKS',\n amount: -6.75,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Food',\n },\n {\n monthOffset: 2,\n day: 25,\n description: 'VANGUARD INVEST',\n amount: -500.0,\n account: 'Chase Checking',\n institution: 'Chase',\n category: 'Investments',\n },\n {\n monthOffset: 2,\n day: 27,\n description: 'TARGET',\n amount: -67.88,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: null,\n },\n]\n\nfunction monthString(offset: number): string {\n const now = new Date()\n const d = new Date(now.getFullYear(), now.getMonth() - offset, 1)\n return `${d.getFullYear()}-${String(d.getMonth() + 1).padStart(2, '0')}`\n}\n\n// Realistic merchant pool for the \"Simulate 10 transactions\" demo button.\n// Category is left NULL so simulated rows flow through the normal AI\n// classification loop (the button triggers /classify right after).\nconst SIM_MERCHANTS: { desc: string; min: number; max: number; income?: boolean }[] = [\n { desc: 'WHOLEFDS MKT #10234', min: 28, max: 142 },\n { desc: 'TRADER JOES #455', min: 22, max: 88 },\n { desc: 'STARBUCKS STORE 07731', min: 4, max: 17 },\n { desc: 'CHIPOTLE 1845', min: 11, max: 29 },\n { desc: 'UBER *TRIP', min: 8, max: 41 },\n { desc: 'LYFT *RIDE WED 8PM', min: 7, max: 38 },\n { desc: 'SHELL OIL 57442109', min: 32, max: 74 },\n { desc: 'AMAZON.COM*RT4U91PB3', min: 14, max: 156 },\n { desc: 'NETFLIX.COM', min: 15, max: 23 },\n { desc: 'SPOTIFY USA', min: 11, max: 11 },\n { desc: 'DELTA AIR 0062371882', min: 184, max: 612 },\n { desc: 'BLUE BOTTLE COFFEE', min: 5, max: 19 },\n { desc: 'CVS/PHARMACY #8821', min: 9, max: 67 },\n { desc: 'EQUINOX FITNESS', min: 215, max: 215 },\n { desc: 'PG&E WEB ONLINE', min: 48, max: 132 },\n { desc: 'DOORDASH*SWEETGREEN', min: 18, max: 44 },\n { desc: 'APPLE.COM/BILL', min: 3, max: 29 },\n { desc: 'PAYROLL DIRECT DEP', min: 3200, max: 3200, income: true },\n]\n\n// Insert 10 realistic, unclassified transactions spread over the last 28 days.\n// Reuses an existing linked account name if present. Returns count inserted.\nexport async function insertSimulatedTransactions(): Promise {\n await ensureSchema()\n const acctRes = await sqlite.execute(\n `SELECT account_name, institution FROM ${T.tx} WHERE account_name IS NOT NULL AND account_name != '' ORDER BY date DESC LIMIT 1`\n )\n const acctName = String((acctRes.rows[0]?.[0] as any) || 'Everyday Checking')\n const institution = String((acctRes.rows[0]?.[1] as any) || 'Simulated Bank')\n\n const pool = [...SIM_MERCHANTS].sort(() => Math.random() - 0.5).slice(0, 10)\n const affected = new Set()\n let inserted = 0\n for (let i = 0; i < pool.length; i++) {\n const m = pool[i]\n const amt = Math.round((m.min + Math.random() * (m.max - m.min)) * 100) / 100\n const d = new Date()\n d.setDate(d.getDate() - Math.floor(Math.random() * 28))\n const date = d.toISOString().slice(0, 10)\n const isIncome = m.income ? 1 : 0\n const amount = isIncome ? amt : -amt\n await sqlite.execute({\n sql: `INSERT INTO ${T.tx} (id, source, source_id, date, description, amount, abs_amount, account_name, institution, is_income, status)\n VALUES (?, 'simulated', ?, ?, ?, ?, ?, ?, ?, ?, 'posted')`,\n args: [\n crypto.randomUUID(),\n `sim-${Date.now()}-${i}`,\n date,\n m.desc,\n amount,\n amt,\n acctName,\n institution,\n isIncome,\n ],\n })\n inserted++\n affected.add(date.slice(0, 7))\n }\n for (const month of affected) await recomputeMonth(month)\n return inserted\n}\n\nexport async function seedDemoData() {\n // One-time cleanup: drop legacy demo teller tokens that earlier template\n // versions auto-seeded. Real Teller-Connect tokens are untouched.\n await sqlite.execute({\n sql: `DELETE FROM ${T.tellerTokens} WHERE id LIKE 'demo_seed_%'`,\n args: [],\n })\n\n const existing = await sqlite.execute({\n sql: `SELECT COUNT(*) FROM ${T.tx}`,\n args: [],\n })\n const count = Number((existing.rows[0] as any[])[0]) || 0\n if (count > 0) return\n\n let i = 0\n for (const row of SEED_ROWS) {\n const month = monthString(row.monthOffset)\n const date = `${month}-${String(row.day).padStart(2, '0')}`\n const id = `seed_${++i}_${date}`\n await sqlite.execute({\n sql: `INSERT INTO ${T.tx} (id, source, date, description, amount, abs_amount, account_name, institution, category, category_source, is_income, is_transfer, status) VALUES (?, 'seed', ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, 'posted')`,\n args: [\n id,\n date,\n row.description,\n row.amount,\n Math.abs(row.amount),\n row.account,\n row.institution,\n row.category,\n row.category ? 'seed' : null,\n row.isIncome ?? 0,\n ],\n })\n }\n\n for (let m = 0; m < 3; m++) await recomputeMonth(monthString(m))\n}\n","// Spend Agent — Database Schema & Helpers\n//\n// Uses the val-scoped SQLite database (`std/sqlite/main.ts`) — each val gets its\n// own isolated database, so plain unprefixed table names are safe (no collision\n// with other vals). The legacy account-global `std/sqlite` is shared across all\n// vals and is intentionally NOT used here.\nimport sqlite from 'https://esm.town/v/std/sqlite/main.ts'\n// Legacy account-global SQLite — ONLY used by the one-time migration below to\n// copy old prefixed tables into this val's scoped DB. Not used anywhere else.\nimport { sqlite as globalDb } from 'https://esm.town/v/std/sqlite'\nimport { CAT_COLORS } from './lib.ts'\n\nexport { sqlite }\n\n// One-time migration: copy the old prefixed tables from the legacy account-global\n// database into this val's scoped database (unprefixed names). Idempotent —\n// INSERT OR IGNORE keyed on each table's PRIMARY KEY, so re-running is safe.\nexport async function migrateFromGlobalToScoped() {\n await ensureSchema() // create unprefixed tables in the scoped DB\n const copied: Record = {}\n const copy = async (src: string, dest: string) => {\n let res: any\n try {\n res = await globalDb.execute(`SELECT * FROM ${src}`)\n } catch {\n copied[`${src}->${dest}`] = 'source missing'\n return\n }\n const cols: string[] = res.columns || []\n if (!cols.length) {\n copied[`${src}->${dest}`] = 0\n return\n }\n const placeholders = cols.map(() => '?').join(',')\n const colList = cols.join(',')\n let n = 0\n for (const row of res.rows) {\n await sqlite.execute({\n sql: `INSERT OR IGNORE INTO ${dest} (${colList}) VALUES (${placeholders})`,\n args: cols.map((_, i) => (row as any)[i]),\n })\n n++\n }\n copied[`${src}->${dest}`] = n\n }\n await copy('sat_transactions', 'transactions')\n await copy('sat_config', 'config')\n await copy('sat_monthly_summaries', 'monthly_summaries')\n await copy('sat_teller_tokens', 'teller_tokens')\n const scopedCounts: Record = {}\n for (const t of ['transactions', 'config', 'monthly_summaries', 'teller_tokens']) {\n const r = await sqlite.execute(`SELECT COUNT(*) FROM ${t}`)\n scopedCounts[t] = Number((r.rows[0] as any)[0]) || 0\n }\n return { copied, scopedCounts }\n}\n\n// Canonical table names — referenced by name from other modules so a future\n// rename only touches this file.\nexport const T = {\n tx: 'transactions',\n summaries: 'monthly_summaries',\n config: 'config',\n tellerTokens: 'teller_tokens',\n} as const\n\n// ── Schema ──────────────────────────────────────────────────────\nexport async function ensureSchema() {\n await sqlite.batch([\n `CREATE TABLE IF NOT EXISTS transactions (\n id TEXT PRIMARY KEY,\n source TEXT NOT NULL,\n source_id TEXT,\n date TEXT NOT NULL,\n description TEXT NOT NULL,\n amount REAL NOT NULL,\n abs_amount REAL NOT NULL,\n account_name TEXT,\n institution TEXT,\n category TEXT,\n category_source TEXT,\n llm_confidence REAL,\n llm_reasoning TEXT,\n is_transfer INTEGER DEFAULT 0,\n is_income INTEGER DEFAULT 0,\n is_duplicate INTEGER DEFAULT 0,\n duplicate_of TEXT,\n status TEXT DEFAULT 'posted',\n created_at TEXT DEFAULT (datetime('now'))\n )`,\n `CREATE TABLE IF NOT EXISTS monthly_summaries (\n month TEXT PRIMARY KEY,\n total_income REAL,\n total_spend REAL,\n total_transfers REAL,\n net_cash_flow REAL,\n category_breakdown TEXT,\n tx_count INTEGER\n )`,\n `CREATE TABLE IF NOT EXISTS config (\n key TEXT PRIMARY KEY,\n value TEXT\n )`,\n `CREATE TABLE IF NOT EXISTS teller_tokens (\n id TEXT PRIMARY KEY,\n access_token TEXT NOT NULL,\n enrollment_id TEXT,\n institution TEXT NOT NULL,\n linked_at TEXT DEFAULT (datetime('now'))\n )`,\n ])\n}\n\n// ── Config helpers ──────────────────────────────────────────────\nexport async function getConfig(key: string, fallback = ''): Promise {\n const res = await sqlite.execute({\n sql: 'SELECT value FROM config WHERE key = ?',\n args: [key],\n })\n return res.rows.length > 0 ? String(res.rows[0][0] ?? fallback) : fallback\n}\n\nexport async function setConfig(key: string, value: string) {\n await sqlite.execute({\n sql: 'INSERT OR REPLACE INTO config (key, value) VALUES (?, ?)',\n args: [key, value],\n })\n}\n\n// ── Monthly summary recompute ───────────────────────────────────\nexport async function recomputeMonth(month: string) {\n const stats = await sqlite.execute({\n sql: `SELECT\n SUM(CASE WHEN is_income = 1 AND is_transfer = 0 THEN abs_amount ELSE 0 END),\n SUM(CASE WHEN is_income = 0 AND is_transfer = 0 AND is_duplicate = 0 THEN abs_amount ELSE 0 END),\n SUM(CASE WHEN is_transfer = 1 THEN abs_amount ELSE 0 END),\n COUNT(*)\n FROM transactions WHERE substr(date, 1, 7) = ?`,\n args: [month],\n })\n const r = stats.rows[0] as any[]\n const income = Number(r[0]) || 0\n const spend = Number(r[1]) || 0\n const transfers = Number(r[2]) || 0\n const txCount = Number(r[3]) || 0\n\n const catRes = await sqlite.execute({\n sql: `SELECT category, SUM(abs_amount) FROM transactions\n WHERE substr(date, 1, 7) = ? AND is_income = 0 AND is_transfer = 0 AND is_duplicate = 0\n GROUP BY category ORDER BY SUM(abs_amount) DESC`,\n args: [month],\n })\n const cats: Record = {}\n for (const cr of catRes.rows)\n cats[String(cr[0] || 'Other')] = Math.round(Number(cr[1]) * 100) / 100\n\n await sqlite.execute({\n sql: `INSERT OR REPLACE INTO monthly_summaries (month, total_income, total_spend, total_transfers, net_cash_flow, category_breakdown, tx_count)\n VALUES (?, ?, ?, ?, ?, ?, ?)`,\n args: [\n month,\n Math.round(income * 100) / 100,\n Math.round(spend * 100) / 100,\n Math.round(transfers * 100) / 100,\n Math.round((income - spend) * 100) / 100,\n JSON.stringify(cats),\n txCount,\n ],\n })\n}\n\n// ── Default system prompt ───────────────────────────────────────\nexport const DEFAULT_SYSTEM_PROMPT = `You are a personal finance categorizer. Classify each transaction into exactly one category.\n\nCategories are enforced by schema — focus on rules for ambiguous cases:\n\nRules:\n- Venmo/Zelle payments TO someone are the actual spend category, not \"Transfers\"\n- Venmo/Zelle payments FROM someone are income or reimbursements\n- Credit card payments and bank transfers between own accounts are \"Transfers\"\n- Recurring charges (Netflix, Spotify, gym) are \"Subscriptions\"\n- Restaurant and food delivery (DoorDash, UberEats, Grubhub) are \"Food\"\n- Uber/Lyft rides are \"Transportation\"\n- Amazon purchases are \"Shopping\" unless clearly groceries\n\nIncome/credit classification:\n- PAYROLL, direct deposits from employers → \"Salary\"\n- Venmo/Zelle payments FROM someone for rent → \"Housing\"\n- Reimbursements: use the category of what was reimbursed (e.g., trip reimbursement → \"Travel\", utility reimbursement → \"Bills\")\n- Tax refunds (IRS, state) → \"Income\"\n- Credit card statement credits, hotel credits, rewards → use underlying category (travel credit → \"Travel\", entertainment credit → \"Entertainment\")\n- PayPal/Cash App transfers to yourself → \"Transfers\"\n- Family gifts via Zelle/Venmo → \"Gifts\"\n- Other misc income → \"Income\"`\n\n// ── Categories ──────────────────────────────────────────────────\nexport const DEFAULT_CATEGORIES = [\n 'Housing',\n 'Food',\n 'Transportation',\n 'Shopping',\n 'Entertainment',\n 'Health',\n 'Subscriptions',\n 'Bills',\n 'Travel',\n 'Investments',\n 'Transfers',\n 'Salary',\n 'Income',\n 'Gifts',\n 'Other',\n] as const\n\n// Keep CATEGORIES as alias for backward compat\nexport const CATEGORIES = DEFAULT_CATEGORIES\n\nexport type Category = (typeof DEFAULT_CATEGORIES)[number]\n\n// Get user-customized categories (falls back to defaults)\nexport async function getCategories(): Promise {\n const stored = await getConfig('categories', '')\n if (stored) {\n try {\n return JSON.parse(stored) as string[]\n } catch {}\n }\n return [...DEFAULT_CATEGORIES]\n}\n\nexport async function setCategories(cats: string[]) {\n // Always ensure \"Other\" exists as fallback\n if (!cats.includes('Other')) cats.push('Other')\n await setConfig('categories', JSON.stringify(cats))\n}\n\n// Read user-set category colors (overlays CAT_COLORS defaults).\nexport async function getCategoryColors(): Promise> {\n const raw = await getConfig('category_colors', '')\n if (!raw) return {}\n try {\n return JSON.parse(raw) as Record\n } catch {\n return {}\n }\n}\n\nexport async function setCategoryColors(colors: Record) {\n await setConfig('category_colors', JSON.stringify(colors))\n}\n\n// Merge user-saved colors into the in-process CAT_COLORS map. Called once per\n// request so the dashboard and Settings UI both see the current palette.\nexport async function loadCategoryColors(): Promise {\n const userColors = await getCategoryColors()\n for (const [name, hex] of Object.entries(userColors)) {\n if (hex) CAT_COLORS[name] = hex\n }\n}\n\nexport type CategoryRow = { original: string; current: string; color?: string }\n\nexport type SaveCategoriesResult = {\n added: string[]\n renamed: { from: string; to: string; count: number }[]\n removed: string[]\n unclassifiedCount: number\n saved: string[]\n colors: Record\n}\n\nexport async function saveCategoriesAndCleanup(rows: CategoryRow[]): Promise {\n // Normalize each row, dedupe by current name (case-sensitive), drop empty\n const seen = new Set()\n const clean: CategoryRow[] = []\n for (const r of rows || []) {\n const current = (r?.current || '').trim()\n if (!current || seen.has(current)) continue\n seen.add(current)\n clean.push({\n original: (r?.original || '').trim(),\n current,\n color: r?.color,\n })\n }\n\n const oldCats = await getCategories()\n const oldColors = await getCategoryColors()\n const newNames = new Set(clean.map((r) => r.current))\n\n // Anything in old that doesn't appear as anyone's \"original\" is REMOVED.\n // (Rename rows have original=oldName so the oldName isn't considered removed.)\n const claimedOriginals = new Set(clean.map((r) => r.original).filter(Boolean))\n const removed = oldCats.filter(\n (c) => c !== 'Other' && !newNames.has(c) && !claimedOriginals.has(c)\n )\n\n const added: string[] = []\n const renamed: { from: string; to: string; count: number }[] = []\n const newColors: Record = {}\n\n for (const row of clean) {\n if (row.color) newColors[row.current] = row.color\n if (!row.original) {\n // Brand-new category\n if (!oldCats.includes(row.current)) added.push(row.current)\n continue\n }\n if (row.original === row.current) continue // kept as-is\n // Rename: update transactions to the new name\n const countRes = await sqlite.execute({\n sql: 'SELECT COUNT(*) FROM transactions WHERE category = ?',\n args: [row.original],\n })\n const count = Number((countRes.rows[0] as any[])[0]) || 0\n if (count > 0) {\n await sqlite.execute({\n sql: 'UPDATE transactions SET category = ? WHERE category = ?',\n args: [row.current, row.original],\n })\n }\n renamed.push({ from: row.original, to: row.current, count })\n }\n\n // Unclassify transactions tied to removed categories\n let unclassifiedCount = 0\n for (const cat of removed) {\n const countRes = await sqlite.execute({\n sql: 'SELECT COUNT(*) FROM transactions WHERE category = ?',\n args: [cat],\n })\n const n = Number((countRes.rows[0] as any[])[0]) || 0\n if (n === 0) continue\n await sqlite.execute({\n sql: `UPDATE transactions\n SET category = NULL, category_source = NULL,\n llm_confidence = NULL, llm_reasoning = NULL\n WHERE category = ?`,\n args: [cat],\n })\n unclassifiedCount += n\n }\n\n // Persist new categories list (current names)\n await setCategories(clean.map((r) => r.current))\n\n // Persist new colors (drop entries for removed categories, propagate renames)\n const finalColors: Record = {}\n for (const r of clean) {\n const c = newColors[r.current] || oldColors[r.original] || oldColors[r.current]\n if (c) finalColors[r.current] = c\n }\n await setCategoryColors(finalColors)\n // Reflect into the in-process map so subsequent renders see it\n for (const [name, hex] of Object.entries(finalColors)) CAT_COLORS[name] = hex\n for (const cat of removed) delete CAT_COLORS[cat]\n\n const saved = await getCategories()\n return {\n added,\n renamed,\n removed,\n unclassifiedCount,\n saved,\n colors: finalColors,\n }\n}\n\n// ── Demo seed data (template only) ──────────────────────────────\n//\n// Inserts ~36 realistic transactions across the last 3 calendar months so a\n// remixer sees a populated dashboard on first load. No-op if the table already\n// has rows. Dates are computed from `now` so the demo always looks fresh.\n//\n// Most rows are pre-classified (`category_source = 'seed'`); a few are left\n// unclassified so the \"Review & Classify\" banner appears and the remixer can\n// experience the LLM classification flow on first visit.\n\ntype SeedRow = {\n monthOffset: number // 0 = current month, 1 = last, 2 = two ago\n day: number\n description: string\n amount: number // negative = spend, positive = credit\n account: string\n institution: string\n category: string | null // null leaves it for the classifier\n isIncome?: 0 | 1\n}\n\nconst SEED_ROWS: SeedRow[] = [\n // Current month\n {\n monthOffset: 0,\n day: 1,\n description: 'PAYROLL DIRECT DEPOSIT — ACME CORP',\n amount: 5800.0,\n account: 'Chase Checking',\n institution: 'Chase',\n category: 'Salary',\n isIncome: 1,\n },\n {\n monthOffset: 0,\n day: 1,\n description: 'RENT — 123 MAIN ST APARTMENTS',\n amount: -2400.0,\n account: 'Chase Checking',\n institution: 'Chase',\n category: 'Housing',\n },\n {\n monthOffset: 0,\n day: 2,\n description: 'NETFLIX.COM',\n amount: -15.49,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Subscriptions',\n },\n {\n monthOffset: 0,\n day: 3,\n description: 'WHOLE FOODS MARKET',\n amount: -87.43,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Food',\n },\n {\n monthOffset: 0,\n day: 4,\n description: 'UBER TRIP',\n amount: -18.2,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Transportation',\n },\n {\n monthOffset: 0,\n day: 5,\n description: 'DOORDASH',\n amount: -34.5,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Food',\n },\n {\n monthOffset: 0,\n day: 6,\n description: 'SPOTIFY USA',\n amount: -10.99,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Subscriptions',\n },\n {\n monthOffset: 0,\n day: 7,\n description: 'AMAZON.COM',\n amount: -42.18,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Shopping',\n },\n {\n monthOffset: 0,\n day: 8,\n description: 'SHELL GAS STATION',\n amount: -52.3,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Transportation',\n },\n {\n monthOffset: 0,\n day: 9,\n description: \"TRADER JOE'S\",\n amount: -64.12,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Food',\n },\n {\n monthOffset: 0,\n day: 10,\n description: 'AMC THEATER',\n amount: -28.5,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: null,\n },\n {\n monthOffset: 0,\n day: 11,\n description: 'VENMO PAYMENT — REIMBURSEMENT FROM SAM',\n amount: 45.0,\n account: 'Venmo',\n institution: 'Venmo',\n category: null,\n isIncome: 1,\n },\n\n // Last month\n {\n monthOffset: 1,\n day: 1,\n description: 'PAYROLL DIRECT DEPOSIT — ACME CORP',\n amount: 5800.0,\n account: 'Chase Checking',\n institution: 'Chase',\n category: 'Salary',\n isIncome: 1,\n },\n {\n monthOffset: 1,\n day: 1,\n description: 'RENT — 123 MAIN ST APARTMENTS',\n amount: -2400.0,\n account: 'Chase Checking',\n institution: 'Chase',\n category: 'Housing',\n },\n {\n monthOffset: 1,\n day: 2,\n description: 'NETFLIX.COM',\n amount: -15.49,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Subscriptions',\n },\n {\n monthOffset: 1,\n day: 3,\n description: 'WHOLE FOODS MARKET',\n amount: -112.88,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Food',\n },\n {\n monthOffset: 1,\n day: 5,\n description: 'UBER EATS',\n amount: -28.45,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Food',\n },\n {\n monthOffset: 1,\n day: 7,\n description: 'AMAZON.COM',\n amount: -156.22,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Shopping',\n },\n {\n monthOffset: 1,\n day: 8,\n description: 'SPOTIFY USA',\n amount: -10.99,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Subscriptions',\n },\n {\n monthOffset: 1,\n day: 10,\n description: 'CVS PHARMACY',\n amount: -18.34,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Health',\n },\n {\n monthOffset: 1,\n day: 12,\n description: 'DELTA AIRLINES',\n amount: -342.5,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Travel',\n },\n {\n monthOffset: 1,\n day: 14,\n description: 'HILTON HOTEL',\n amount: -218.0,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Travel',\n },\n {\n monthOffset: 1,\n day: 16,\n description: \"TRADER JOE'S\",\n amount: -71.9,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Food',\n },\n {\n monthOffset: 1,\n day: 18,\n description: 'VENMO TO MOM — GIFT',\n amount: -100.0,\n account: 'Venmo',\n institution: 'Venmo',\n category: 'Gifts',\n },\n {\n monthOffset: 1,\n day: 20,\n description: 'SHELL GAS STATION',\n amount: -48.75,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Transportation',\n },\n {\n monthOffset: 1,\n day: 22,\n description: 'COMCAST INTERNET',\n amount: -89.99,\n account: 'Chase Checking',\n institution: 'Chase',\n category: 'Bills',\n },\n {\n monthOffset: 1,\n day: 25,\n description: 'VANGUARD INVEST',\n amount: -500.0,\n account: 'Chase Checking',\n institution: 'Chase',\n category: 'Investments',\n },\n {\n monthOffset: 1,\n day: 28,\n description: 'CHIPOTLE',\n amount: -14.2,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Food',\n },\n\n // Two months ago\n {\n monthOffset: 2,\n day: 1,\n description: 'PAYROLL DIRECT DEPOSIT — ACME CORP',\n amount: 5800.0,\n account: 'Chase Checking',\n institution: 'Chase',\n category: 'Salary',\n isIncome: 1,\n },\n {\n monthOffset: 2,\n day: 1,\n description: 'RENT — 123 MAIN ST APARTMENTS',\n amount: -2400.0,\n account: 'Chase Checking',\n institution: 'Chase',\n category: 'Housing',\n },\n {\n monthOffset: 2,\n day: 2,\n description: 'NETFLIX.COM',\n amount: -15.49,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Subscriptions',\n },\n {\n monthOffset: 2,\n day: 4,\n description: 'WHOLE FOODS MARKET',\n amount: -98.22,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Food',\n },\n {\n monthOffset: 2,\n day: 6,\n description: 'LYFT',\n amount: -22.4,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Transportation',\n },\n {\n monthOffset: 2,\n day: 8,\n description: 'SPOTIFY USA',\n amount: -10.99,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Subscriptions',\n },\n {\n monthOffset: 2,\n day: 10,\n description: 'AMAZON.COM',\n amount: -78.15,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Shopping',\n },\n {\n monthOffset: 2,\n day: 12,\n description: \"TRADER JOE'S\",\n amount: -55.3,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Food',\n },\n {\n monthOffset: 2,\n day: 15,\n description: 'VENMO FROM ALEX — DINNER REIMBURSEMENT',\n amount: 32.5,\n account: 'Venmo',\n institution: 'Venmo',\n category: 'Food',\n isIncome: 1,\n },\n {\n monthOffset: 2,\n day: 18,\n description: 'EQUINOX MEMBERSHIP',\n amount: -185.0,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: null,\n },\n {\n monthOffset: 2,\n day: 20,\n description: 'PG&E ELECTRIC',\n amount: -94.2,\n account: 'Chase Checking',\n institution: 'Chase',\n category: 'Bills',\n },\n {\n monthOffset: 2,\n day: 22,\n description: 'STARBUCKS',\n amount: -6.75,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: 'Food',\n },\n {\n monthOffset: 2,\n day: 25,\n description: 'VANGUARD INVEST',\n amount: -500.0,\n account: 'Chase Checking',\n institution: 'Chase',\n category: 'Investments',\n },\n {\n monthOffset: 2,\n day: 27,\n description: 'TARGET',\n amount: -67.88,\n account: 'Chase Sapphire',\n institution: 'Chase',\n category: null,\n },\n]\n\nfunction monthString(offset: number): string {\n const now = new Date()\n const d = new Date(now.getFullYear(), now.getMonth() - offset, 1)\n return `${d.getFullYear()}-${String(d.getMonth() + 1).padStart(2, '0')}`\n}\n\n// Realistic merchant pool for the \"Simulate 10 transactions\" demo button.\n// Category is left NULL so simulated rows flow through the normal AI\n// classification loop (the button triggers /classify right after).\nconst SIM_MERCHANTS: { desc: string; min: number; max: number; income?: boolean }[] = [\n { desc: 'WHOLEFDS MKT #10234', min: 28, max: 142 },\n { desc: 'TRADER JOES #455', min: 22, max: 88 },\n { desc: 'STARBUCKS STORE 07731', min: 4, max: 17 },\n { desc: 'CHIPOTLE 1845', min: 11, max: 29 },\n { desc: 'UBER *TRIP', min: 8, max: 41 },\n { desc: 'LYFT *RIDE WED 8PM', min: 7, max: 38 },\n { desc: 'SHELL OIL 57442109', min: 32, max: 74 },\n { desc: 'AMAZON.COM*RT4U91PB3', min: 14, max: 156 },\n { desc: 'NETFLIX.COM', min: 15, max: 23 },\n { desc: 'SPOTIFY USA', min: 11, max: 11 },\n { desc: 'DELTA AIR 0062371882', min: 184, max: 612 },\n { desc: 'BLUE BOTTLE COFFEE', min: 5, max: 19 },\n { desc: 'CVS/PHARMACY #8821', min: 9, max: 67 },\n { desc: 'EQUINOX FITNESS', min: 215, max: 215 },\n { desc: 'PG&E WEB ONLINE', min: 48, max: 132 },\n { desc: 'DOORDASH*SWEETGREEN', min: 18, max: 44 },\n { desc: 'APPLE.COM/BILL', min: 3, max: 29 },\n { desc: 'PAYROLL DIRECT DEP', min: 3200, max: 3200, income: true },\n]\n\n// Insert 10 realistic, unclassified transactions spread over the last 28 days.\n// Reuses an existing linked account name if present. Returns count inserted.\nexport async function insertSimulatedTransactions(): Promise {\n await ensureSchema()\n const acctRes = await sqlite.execute(\n `SELECT account_name, institution FROM ${T.tx} WHERE account_name IS NOT NULL AND account_name != '' ORDER BY date DESC LIMIT 1`\n )\n const acctName = String((acctRes.rows[0]?.[0] as any) || 'Everyday Checking')\n const institution = String((acctRes.rows[0]?.[1] as any) || 'Simulated Bank')\n\n const pool = [...SIM_MERCHANTS].sort(() => Math.random() - 0.5).slice(0, 10)\n const affected = new Set()\n let inserted = 0\n for (let i = 0; i < pool.length; i++) {\n const m = pool[i]\n const amt = Math.round((m.min + Math.random() * (m.max - m.min)) * 100) / 100\n const d = new Date()\n d.setDate(d.getDate() - Math.floor(Math.random() * 28))\n const date = d.toISOString().slice(0, 10)\n const isIncome = m.income ? 1 : 0\n const amount = isIncome ? amt : -amt\n await sqlite.execute({\n sql: `INSERT INTO ${T.tx} (id, source, source_id, date, description, amount, abs_amount, account_name, institution, is_income, status)\n VALUES (?, 'simulated', ?, ?, ?, ?, ?, ?, ?, ?, 'posted')`,\n args: [\n crypto.randomUUID(),\n `sim-${Date.now()}-${i}`,\n date,\n m.desc,\n amount,\n amt,\n acctName,\n institution,\n isIncome,\n ],\n })\n inserted++\n affected.add(date.slice(0, 7))\n }\n for (const month of affected) await recomputeMonth(month)\n return inserted\n}\n\nexport async function seedDemoData() {\n // One-time cleanup: drop legacy demo teller tokens that earlier template\n // versions auto-seeded. Real Teller-Connect tokens are untouched.\n await sqlite.execute({\n sql: `DELETE FROM ${T.tellerTokens} WHERE id LIKE 'demo_seed_%'`,\n args: [],\n })\n\n const existing = await sqlite.execute({\n sql: `SELECT COUNT(*) FROM ${T.tx}`,\n args: [],\n })\n const count = Number((existing.rows[0] as any[])[0]) || 0\n if (count > 0) return\n\n let i = 0\n for (const row of SEED_ROWS) {\n const month = monthString(row.monthOffset)\n const date = `${month}-${String(row.day).padStart(2, '0')}`\n const id = `seed_${++i}_${date}`\n await sqlite.execute({\n sql: `INSERT INTO ${T.tx} (id, source, date, description, amount, abs_amount, account_name, institution, category, category_source, is_income, is_transfer, status) VALUES (?, 'seed', ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, 'posted')`,\n args: [\n id,\n date,\n row.description,\n row.amount,\n Math.abs(row.amount),\n row.account,\n row.institution,\n row.category,\n row.category ? 'seed' : null,\n row.isIncome ?? 0,\n ],\n })\n }\n\n for (let m = 0; m < 3; m++) await recomputeMonth(monthString(m))\n}\n","createdAt",["D",1779997839910],"branchVersion","branchId","0e663cfa-5877-11f1-a8f8-ee650bb23af1","id","0e656f14-5877-11f1-a8f8-ee650bb23af1","spend-agent",["D",1779740379107],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","Your personal, AI-native finance tool. ","imageUrl","5751f21a-8735-41fd-9675-c4c5ee261e48","pinnedFileId","8ac5067e-5aab-11f1-bb3c-ee650bb23af1","pinPreview",true,"pinMigrated","screenshotVersion","likeCount",4,"orgId","019d935d-aed7-71ff-b9f9-a9cc46cb7275","tursoName","0e656f14587711f1a8f8ee650bb23af1","tursoHostname","0e656f14587711f1a8f8ee650bb23af1-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789504908499],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_100":99,"_107":103},"org",{"_100":99,"_101":-5,"_102":103,"_18":55,"_104":105,"_106":25},"branch",{"_13":89,"_32":90,"_43":42,"_91":44,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":-5,"_97":45,"_98":99},"isOwned",204,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","kevinsuh","handle","displayName","avatar","https://avatars.githubusercontent.com/u/8541122?v=4","tier","pro","canRestrictVals","profileImageUrl","branchName","","adoptOrgId",{"_43":44,"_32":45,"_38":257,"_47":-5,"_48":25,"_49":50,"_51":50,"_52":53,"_54":-5,"_18":55,"_56":57,"_58":59,"_60":-5,"_61":-5,"_62":63,"_64":65,"_66":67,"_68":69,"_70":258,"_72":-5,"_73":74,"_75":74,"_76":77,"_78":77,"_79":77,"_80":74,"_81":74,"_82":259,"_84":260,"_86":261,"_88":25},"tree",[220,221,222,223,224,225,226,227,228],"files",[220,221,222,223,224,225,226,227,228],"counts",{"_208":209,"_210":211,"_212":146,"_213":214,"_215":216},"contents",[130,131,132,133,134,135,136,137,138],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_43":201,"_32":202,"_30":203,"_142":-5,"_41":42,"_40":204,"_144":59,"_38":205,"_13":63,"_33":34,"_35":206,"_24":25,"_149":-5,"_27":207},{"_43":193,"_32":194,"_30":57,"_142":-5,"_41":42,"_40":195,"_144":25,"_38":196,"_13":197,"_33":198,"_35":199,"_24":25,"_149":-5,"_27":200},{"_43":185,"_32":186,"_30":187,"_142":-5,"_41":42,"_40":188,"_144":25,"_38":189,"_13":190,"_33":34,"_35":191,"_24":25,"_149":-5,"_27":192},{"_43":177,"_32":178,"_30":179,"_142":-5,"_41":42,"_40":180,"_144":59,"_38":181,"_13":182,"_33":34,"_35":183,"_24":25,"_149":-5,"_27":184},{"_43":169,"_32":170,"_30":171,"_142":-5,"_41":42,"_40":172,"_144":59,"_38":173,"_13":174,"_33":147,"_35":175,"_24":25,"_149":-5,"_27":176},{"_43":165,"_32":28,"_30":31,"_142":-5,"_41":42,"_40":14,"_144":25,"_38":166,"_13":167,"_33":34,"_35":37,"_24":25,"_149":-5,"_27":168},{"_43":158,"_32":159,"_30":160,"_142":-5,"_41":42,"_40":161,"_144":59,"_38":162,"_13":63,"_33":34,"_35":163,"_24":25,"_149":-5,"_27":164},{"_43":151,"_32":152,"_30":153,"_142":-5,"_41":42,"_40":154,"_144":59,"_38":155,"_13":63,"_33":34,"_35":156,"_24":25,"_149":-5,"_27":157},{"_43":139,"_32":140,"_30":141,"_142":-5,"_41":42,"_40":143,"_144":59,"_38":145,"_13":146,"_33":147,"_35":148,"_24":25,"_149":-5,"_27":150},"bb43f7d4-587a-11f1-8735-ee650bb23af1","sync.ts","4ca11b20-5877-11f1-85ed-ee650bb23af1","parentId",25,"latest",["D",1779741957609],2,"interval","// Spend Agent — Teller Sync Cron (15-minute interval)\nimport { runSync } from \"./sync-engine.ts\";\n\nexport default async function () {\n const result = await runSync();\n if (result.errors.length > 0) console.log(\"Sync errors:\", result.errors.join(\"; \"));\n}","label",[140],"5da9d632-5ac5-11f1-8fce-ee650bb23af1","sync-engine.ts","bb0671f2-587a-11f1-800c-ee650bb23af1",183,["D",1779993915129],"// Spend Agent — Sync Engine (shared logic)\n// Exported runSync used by app.ts (on-demand) and sync.ts (15min cron).\nimport { sqlite, ensureSchema, T } from './db.ts'\nimport { fixPem } from './lib.ts'\nimport { blob } from 'https://esm.town/v/std/blob'\n\nexport async function runSync(): Promise<{\n accounts: number\n inserted: number\n skipped: number\n errors: string[]\n}> {\n await ensureSchema()\n\n const tokensRes = await sqlite.execute('SELECT id, access_token, institution FROM teller_tokens')\n if (tokensRes.rows.length === 0) {\n return { accounts: 0, inserted: 0, skipped: 0, errors: ['No Teller tokens linked'] }\n }\n\n // Sandbox mode doesn't use mTLS — only development/production need certs.\n const tellerEnv = Deno.env.get('TELLER_ENV') || 'sandbox'\n let client: Deno.HttpClient | undefined\n if (tellerEnv !== 'sandbox') {\n try {\n const certRaw = Deno.env.get('TELLER_CERT')\n const keyRaw = Deno.env.get('TELLER_KEY')\n if (!certRaw || !keyRaw)\n throw new Error(`Missing TELLER_CERT or TELLER_KEY (required when TELLER_ENV=${tellerEnv})`)\n client = Deno.createHttpClient({ cert: fixPem(certRaw), key: fixPem(keyRaw) })\n } catch (e: any) {\n return { accounts: 0, inserted: 0, skipped: 0, errors: [e.message] }\n }\n }\n\n const allAccounts: any[] = []\n const balances: Record = {}\n const errors: string[] = []\n const seenAccountIds = new Set()\n let txInserted = 0\n let txSkipped = 0\n\n const existingRes = await sqlite.execute(`SELECT id FROM ${T.tx} WHERE source = 'teller'`)\n const existingIds = new Set(existingRes.rows.map((r: any) => String(r[0])))\n\n for (const row of tokensRes.rows) {\n const accessToken = String(row[1])\n const institution = String(row[2])\n\n try {\n const accounts = await tellerFetch('/accounts', accessToken, client)\n\n for (const acct of accounts) {\n if (seenAccountIds.has(acct.id)) continue\n seenAccountIds.add(acct.id)\n\n allAccounts.push({\n id: acct.id,\n name: acct.name,\n type: acct.type,\n subtype: acct.subtype,\n institution,\n status: acct.status,\n })\n\n try {\n const bal = await tellerFetch(`/accounts/${acct.id}/balances`, accessToken, client)\n balances[acct.id] = {\n available: parseFloat(bal.available || '0'),\n ledger: parseFloat(bal.ledger || '0'),\n }\n } catch (e: any) {\n errors.push(`${institution}/${acct.name}/bal: ${e.message}`)\n }\n\n try {\n const txns = await tellerFetch(\n `/accounts/${acct.id}/transactions`,\n accessToken,\n client\n )\n for (const tx of txns) {\n if (tx.status !== 'posted') {\n txSkipped++\n continue\n }\n if (existingIds.has(tx.id)) {\n txSkipped++\n continue\n }\n\n const amount = parseFloat(tx.amount || '0')\n const absAmount = Math.round(Math.abs(amount) * 100) / 100\n const isIncome = acct.type === 'credit' ? (amount < 0 ? 1 : 0) : amount > 0 ? 1 : 0\n\n try {\n await sqlite.execute({\n sql: `INSERT INTO ${T.tx} (id, source, source_id, date, description, amount, abs_amount, account_name, institution, is_income, status)\n VALUES (?, 'teller', ?, ?, ?, ?, ?, ?, ?, ?, 'posted')`,\n args: [\n tx.id,\n tx.id,\n tx.date,\n tx.description,\n amount,\n absAmount,\n acct.name,\n institution,\n isIncome,\n ],\n })\n txInserted++\n } catch {\n txSkipped++\n }\n }\n } catch (e: any) {\n errors.push(`${institution}/${acct.name}/txns: ${e.message}`)\n }\n }\n } catch (e: any) {\n errors.push(`${institution}: ${e.message}`)\n }\n }\n\n // Save snapshot blob\n let totalChecking = 0,\n totalSavings = 0,\n totalCredit = 0\n for (const a of allAccounts) {\n const b = balances[a.id]\n if (!b) continue\n if (a.type === 'depository' && a.subtype === 'checking') totalChecking += b.available\n else if (a.type === 'depository' && a.subtype === 'savings') totalSavings += b.available\n else if (a.type === 'credit') totalCredit += Math.abs(b.ledger)\n }\n\n await blob.setJSON('spend-agent-snapshot', {\n lastUpdated: new Date().toISOString(),\n accounts: allAccounts,\n balances,\n errors,\n summary: {\n totalChecking: Math.round(totalChecking * 100) / 100,\n totalSavings: Math.round(totalSavings * 100) / 100,\n totalCreditOwed: Math.round(totalCredit * 100) / 100,\n },\n })\n\n console.log(\n `Sync: ${allAccounts.length} accounts, ${txInserted} new txns, ${txSkipped} skipped, ${errors.length} errors`\n )\n return { accounts: allAccounts.length, inserted: txInserted, skipped: txSkipped, errors }\n}\n\nasync function tellerFetch(\n endpoint: string,\n accessToken: string,\n client?: Deno.HttpClient\n): Promise {\n // Sandbox mode hits api.teller.io without an mTLS client — only dev/prod\n // attach the cert-backed HttpClient.\n const init: any = {\n headers: {\n Authorization: `Basic ${btoa(accessToken + ':')}`,\n 'Content-Type': 'application/json',\n },\n }\n if (client) init.client = client\n const res = await fetch(`https://api.teller.io${endpoint}`, init)\n if (!res.ok) throw new Error(`${endpoint}: ${res.status} ${await res.text()}`)\n return res.json() as Promise\n}\n",[152],"698d495e-5ac8-11f1-a899-ee650bb23af1","lib.ts","8a496758-5aab-11f1-a068-ee650bb23af1",192,["D",1779995223579],"// Spend Agent — Shared Utilities\nexport const TELLER_APP_ID = Deno.env.get('TELLER_APP_ID') || ''\n\n// ── Formatting ──────────────────────────────────────────────────\nexport function fmt(n: number): string {\n return (\n '$' +\n Math.abs(n).toLocaleString('en-US', { minimumFractionDigits: 2, maximumFractionDigits: 2 })\n )\n}\n\nexport function fmtK(n: number): string {\n const abs = Math.abs(n)\n return (n < 0 ? '-' : '') + (abs >= 1000 ? '$' + (abs / 1000).toFixed(1) + 'K' : fmt(n))\n}\n\nexport function pct(n: number, d: number): string {\n return d === 0 ? '0%' : ((n / d) * 100).toFixed(0) + '%'\n}\n\nexport function ago(iso: string): string {\n const ms = Date.now() - new Date(iso).getTime()\n const mins = Math.floor(ms / 60000)\n if (mins < 60) return `${mins}m ago`\n const hrs = Math.floor(mins / 60)\n if (hrs < 24) return `${hrs}h ago`\n return `${Math.floor(hrs / 24)}d ago`\n}\n\nexport function escHtml(s: string): string {\n return s\n .replace(/&/g, '&')\n .replace(//g, '>')\n .replace(/\"/g, '"')\n}\n\n\n// Settings-page JS for the Categories CRUD card + shadcn-style confirm modal.\nexport const CATEGORIES_JS = `\nfunction showModal(title, bodyHtml){return new Promise(function(resolve){var m=document.getElementById('cat-modal');if(!m)return resolve(window.confirm(title));document.getElementById('cat-modal-title').textContent=title;document.getElementById('cat-modal-body').innerHTML=bodyHtml;m.classList.add('open');var ok=document.getElementById('cat-modal-ok'),cancel=document.getElementById('cat-modal-cancel');function done(v){m.classList.remove('open');ok.onclick=null;cancel.onclick=null;resolve(v);}ok.onclick=function(){done(true);};cancel.onclick=function(){done(false);};});}\nfunction addCategoryRow(){var l=document.getElementById('categories-list'),d=document.createElement('div');d.className='cat-row';d.setAttribute('data-original','');d.innerHTML='';l.appendChild(d);d.querySelector('.cat-input').focus();}\nfunction removeCategoryRow(b){b.closest('.cat-row').remove();}\nasync function saveCategoriesList(){var st=document.getElementById('categories-status'),rows=document.querySelectorAll('#categories-list .cat-row'),data=[],seen={};for(var i=0;i'+willRemove.join(', ')+' will send all transactions in those categories back to unclassified for re-review. Continue?');if(!ok)return;}\n st.textContent='Saving...';\n try{var res=await fetch('/save-categories',{method:'POST',headers:{'Content-Type':'application/json'},body:JSON.stringify({rows:data})}),resp=await res.json();if(!resp.ok){st.textContent='Error: '+(resp.error||'Failed');return;}var msgs=[];if(resp.added&&resp.added.length)msgs.push('Added '+resp.added.length);if(resp.renamed&&resp.renamed.length)msgs.push('Renamed '+resp.renamed.length);if(resp.removed&&resp.removed.length)msgs.push('Removed '+resp.removed.length+' ('+resp.unclassifiedCount+' tx unclassified)');st.textContent=msgs.length?'Saved. '+msgs.join(', ')+'.':'Saved.';setTimeout(function(){window.location.reload();},1500);}catch(e){st.textContent='Error: '+e.message;}\n}\n`\n\n// Modal HTML rendered once on the Settings page. JS toggles .open.\nexport const CATEGORY_MODAL_HTML = ``\n\n// Production setup accordion HTML — sibling to the sandbox creds accordion.\nexport const PRODUCTION_INSTRUCTIONS_HTML = `

View production setup (real banks)

Sign up at teller.io and create an application — copy the Application ID.
Apply for production access in the Teller dashboard (free up to 100 accounts, requires ID verification).
Download certificate.pem + private_key.pem — these mTLS certs authenticate every API call.
In Val Town env vars: set TELLER_APP_ID, TELLER_ENV=production, TELLER_CERT, TELLER_KEY (paste full PEMs).
Reload and click Connect Bank.

PEMs stay server-side as env vars. Teller production docs →

`\n\n// ── PEM Reconstruction ──────────────────────────────────────────\n// Val Town env vars strip newlines. This rebuilds valid PEM from flattened input.\nexport function fixPem(raw: string): string {\n if (raw.includes('\\n')) return raw\n const beginMatch = raw.match(/-----BEGIN ([A-Z ]+)-----/)\n const endMatch = raw.match(/-----END ([A-Z ]+)-----/)\n if (!beginMatch || !endMatch) return raw\n const type = beginMatch[1]\n let b64 = raw\n .replace(`-----BEGIN ${type}-----`, '')\n .replace(`-----END ${type}-----`, '')\n .replace(/\\s+/g, '')\n const lines: string[] = []\n for (let i = 0; i < b64.length; i += 64) {\n lines.push(b64.slice(i, i + 64))\n }\n return `-----BEGIN ${type}-----\\n${lines.join('\\n')}\\n-----END ${type}-----\\n`\n}\n\n// ── Category Colors ─────────────────────────────────────────────\nexport const CAT_COLORS: Record = {\n Housing: '#8b5cf6',\n Food: '#f97316',\n Transportation: '#3b82f6',\n Shopping: '#a855f7',\n Entertainment: '#ec4899',\n Health: '#ef4444',\n Subscriptions: '#6366f1',\n Bills: '#64748b',\n Travel: '#14b8a6',\n Investments: '#22d3ee',\n Transfers: '#475569',\n Salary: '#059669',\n Income: '#10b981',\n Gifts: '#f59e0b',\n Other: '#6b7280',\n}\n\n// ── HTML Shell ──────────────────────────────────────────────────\nexport function pageShell(title: string, activePath: string, body: string, scripts = ''): string {\n const nav = [\n { path: '/', label: 'Overview' },\n { path: '/ask', label: 'Ask' },\n { path: '/settings', label: 'Settings' },\n ]\n .map((n) => {\n const active = activePath === n.path\n return `${n.label}`\n })\n .join('')\n\n return `\n\n\n \n ${title} — Spend Agent\n \n \n\n\n

Spend Agent

${nav}

\n ${body}\n

\n ${scripts}\n\n`\n}\n",[159],"8107c374-5ace-11f1-b996-ee650bb23af1",["D",1779997839910],5,[28],"25b57a7a-59f2-11f1-9279-ee650bb23af1","classify.ts","62089d3a-5877-11f1-ab13-ee650bb23af1",86,["D",1779903197447],6,"// Spend Agent — Daily Classification Cron\n// Thin wrapper around classify-engine.ts for Val Town's interval runner.\nimport { runClassification } from './classify-engine.ts'\n\nexport default async function () {\n const result = await runClassification()\n if (result.errors.length > 0) console.log('Classification errors:', result.errors.join('; '))\n}\n",[170],"eb0117e8-59ee-11f1-bfca-ee650bb23af1","classify-ui.ts","d1610e4a-59eb-11f1-a639-ee650bb23af1",83,["D",1779901810486],3,"// Spend Agent — Classification Drawer UI\n// Extracted to stay under Val Town's 80K char file limit.\nimport { fmt, escHtml, CAT_COLORS } from './lib.ts'\n\ntype UncTx = {\n id: string\n date: string\n description: string\n abs_amount: number\n account_name: string\n institution: string\n}\n\nexport function classifyDrawerHtml(\n uncCount: number,\n uncTxs: UncTx[],\n currentPrompt: string\n): { banner: string; drawer: string; scripts: string } {\n const banner =\n uncCount > 0\n ? `

\n ${uncCount} unclassified transaction${uncCount > 1 ? 's' : ''}\n Review & Classify ▶\n

`\n : ''\n\n const drawer = `\n \n `\n\n const escapedTxs = JSON.stringify(uncTxs)\n const escapedPrompt = JSON.stringify(currentPrompt)\n const escapedColors = JSON.stringify(CAT_COLORS)\n\n const scripts = `\n var uncTxData = ${escapedTxs};\n var previewResults = null;\n var drawerPrompt = ${escapedPrompt};\n var originalPrompt = ${escapedPrompt};\n var catColors = ${escapedColors};\n\n function getCatColor(cat) {\n return catColors[cat] || '#6b7280';\n }\n\n function openClassifyDrawer() {\n document.getElementById('classify-overlay').style.display = 'block';\n document.getElementById('classify-drawer').style.display = 'block';\n document.getElementById('drawer-step-list').style.display = 'block';\n document.getElementById('drawer-step-preview').style.display = 'none';\n document.getElementById('drawer-prompt').value = drawerPrompt;\n renderTxList();\n }\n function closeClassifyDrawer() {\n document.getElementById('classify-overlay').style.display = 'none';\n document.getElementById('classify-drawer').style.display = 'none';\n }\n function onPromptEdit() {\n var btn = document.getElementById('drawer-save-prompt-btn');\n var changed = document.getElementById('drawer-prompt').value !== originalPrompt;\n btn.style.display = changed ? 'inline' : 'none';\n }\n function onPreviewPromptEdit() {\n var btn = document.getElementById('preview-save-prompt-btn');\n var changed = document.getElementById('preview-prompt').value !== originalPrompt;\n btn.style.display = changed ? 'inline' : 'none';\n }\n async function savePrompt() {\n var promptEl = document.getElementById('drawer-prompt') || document.getElementById('preview-prompt');\n var val = promptEl.value;\n var btns = [document.getElementById('drawer-save-prompt-btn'), document.getElementById('preview-save-prompt-btn')];\n btns.forEach(function(b) { if (b) b.textContent = 'Saving...'; });\n try {\n await fetch('/save-prompt', { method: 'POST', headers: {'Content-Type':'application/json'}, body: JSON.stringify({prompt: val}) });\n originalPrompt = val;\n btns.forEach(function(b) { if (b) { b.style.display = 'none'; b.textContent = 'Save'; } });\n // Sync both textareas\n var dp = document.getElementById('drawer-prompt');\n var pp = document.getElementById('preview-prompt');\n if (dp) dp.value = val;\n if (pp) pp.value = val;\n } catch(e) { btns.forEach(function(b) { if (b) b.textContent = 'Error'; }); }\n }\n function renderTxList() {\n var el = document.getElementById('drawer-tx-list');\n if (uncTxData.length === 0) { el.innerHTML = '

No unclassified transactions

'; return; }\n el.innerHTML = uncTxData.map(function(tx) {\n var d = new Date(tx.date + 'T12:00:00').toLocaleDateString('en-US', {month:'short',day:'numeric'});\n return '

' +\n '

' + tx.description.slice(0,45) + '

' +\n '

' + d + ' \\\\u00b7 ' + (tx.account_name || '') + '

' +\n '$' + tx.abs_amount.toFixed(2) + '

';\n }).join('');\n }\n async function runPreview() {\n var btn = document.getElementById('drawer-preview-btn');\n btn.textContent = 'Classifying...'; btn.disabled = true;\n try {\n var promptVal = document.getElementById('drawer-prompt').value;\n var body = {};\n if (promptVal !== originalPrompt) {\n body.prompt = promptVal;\n }\n var res = await fetch('/preview-classify', { method: 'POST', headers: {'Content-Type':'application/json'}, body: JSON.stringify(body) });\n var data = await res.json();\n if (data.error) { btn.textContent = 'Error: ' + data.error; btn.disabled = false; return; }\n previewResults = data.results;\n drawerPrompt = promptVal;\n renderPreview(data);\n document.getElementById('drawer-step-list').style.display = 'none';\n document.getElementById('drawer-step-preview').style.display = 'block';\n document.getElementById('preview-prompt').value = promptVal;\n } catch(e) { alert('Error: ' + e.message); }\n btn.textContent = 'Preview Classification'; btn.disabled = false;\n }\n async function retryFromPreview() {\n var btn = document.getElementById('drawer-retry-btn');\n btn.textContent = 'Classifying...'; btn.disabled = true;\n try {\n var promptVal = document.getElementById('preview-prompt').value;\n var body = {};\n if (promptVal !== originalPrompt) {\n body.prompt = promptVal;\n }\n var res = await fetch('/preview-classify', { method: 'POST', headers: {'Content-Type':'application/json'}, body: JSON.stringify(body) });\n var data = await res.json();\n if (data.error) { btn.textContent = 'Error: ' + data.error; btn.disabled = false; return; }\n previewResults = data.results;\n drawerPrompt = promptVal;\n // Sync prompt back to list step\n document.getElementById('drawer-prompt').value = promptVal;\n renderPreview(data);\n } catch(e) { alert('Error: ' + e.message); }\n btn.textContent = '\\\\u21bb Retry'; btn.disabled = false;\n }\n function renderPreview(data) {\n document.getElementById('preview-count').textContent = data.results.length + ' classified' + (data.errors.length ? ', ' + data.errors.length + ' errors' : '');\n var el = document.getElementById('drawer-preview-list');\n el.innerHTML = data.results.map(function(r) {\n var d = new Date(r.date + 'T12:00:00').toLocaleDateString('en-US', {month:'short',day:'numeric'});\n var conf = r.confidence >= 0.7 ? '#22c55e' : '#f59e0b';\n var catColor = getCatColor(r.category);\n return '

' +\n '

' +\n '' + r.description.slice(0,40) + '' +\n '$' + r.abs_amount.toFixed(2) + '

' +\n '

' +\n '' + r.category + '' +\n '' + Math.round(r.confidence * 100) + '%' +\n '' + (r.reasoning || '').slice(0,60) + '

';\n }).join('');\n }\n function backToList() {\n document.getElementById('drawer-step-list').style.display = 'block';\n document.getElementById('drawer-step-preview').style.display = 'none';\n }\n async function approveResults() {\n if (!previewResults || previewResults.length === 0) return;\n var btn = document.getElementById('drawer-approve-btn');\n btn.textContent = 'Saving...'; btn.disabled = true;\n try {\n var promptEl = document.getElementById('preview-prompt');\n var body = { results: previewResults };\n if (promptEl.value !== originalPrompt) body.prompt = promptEl.value;\n var res = await fetch('/approve-classify', { method: 'POST', headers: {'Content-Type':'application/json'}, body: JSON.stringify(body) });\n var data = await res.json();\n btn.textContent = data.applied + ' applied!';\n setTimeout(function() { location.reload(); }, 1000);\n } catch(e) { btn.textContent = 'Error'; btn.disabled = false; }\n }`\n\n return { banner, drawer, scripts }\n}\n",[178],"5d7bbbbc-5ac5-11f1-8ff8-ee650bb23af1","classify-engine.ts","28683b24-5879-11f1-ab12-ee650bb23af1",182,["D",1779993914851],12,"// Spend Agent — Classification Engine (shared logic)\n// Exported functions used by app.ts (on-demand) and classify.ts (daily cron).\nimport {\n sqlite,\n ensureSchema,\n getConfig,\n DEFAULT_SYSTEM_PROMPT,\n CATEGORIES,\n getCategories,\n recomputeMonth,\n T,\n} from './db.ts'\nimport Anthropic from 'npm:@anthropic-ai/sdk'\n\n// ── Shared tool-use classification helper ───────────────────────\ntype RawTx = {\n id: string\n date: string\n description: string\n amount: number\n abs_amount: number\n account_name: string\n institution: string\n is_income: number\n}\n\ntype ClassifyResult = {\n id: string\n category: string\n confidence: number\n reasoning: string\n}\n\nasync function classifyBatch(\n client: Anthropic,\n batch: RawTx[],\n cats: string[],\n systemPrompt: string\n): Promise<{ results: ClassifyResult[]; error?: string }> {\n const txLines = batch\n .map(\n (tx, idx) =>\n `${idx + 1}. [${tx.id}] ${tx.date} | ${tx.description} | $${tx.abs_amount} | ${tx.account_name} (${tx.institution}) | ${tx.is_income ? 'CREDIT/INCOME' : 'DEBIT/SPEND'}`\n )\n .join('\\n')\n\n const tool: Anthropic.Tool = {\n name: 'classify_transactions',\n description: 'Classify a batch of financial transactions into categories',\n input_schema: {\n type: 'object' as const,\n properties: {\n classifications: {\n type: 'array',\n items: {\n type: 'object',\n properties: {\n id: { type: 'string', description: 'Transaction ID' },\n category: { type: 'string', enum: cats, description: 'Exactly one valid category' },\n confidence: { type: 'number', description: 'Confidence 0-1' },\n reasoning: { type: 'string', description: 'Brief reasoning' },\n },\n required: ['id', 'category', 'confidence', 'reasoning'],\n },\n },\n },\n required: ['classifications'],\n },\n }\n\n const response = await client.messages.create({\n model: 'claude-haiku-4-5-20251001',\n max_tokens: 8192,\n system: systemPrompt,\n tools: [tool],\n tool_choice: { type: 'tool', name: 'classify_transactions' },\n messages: [\n {\n role: 'user',\n content: `Classify these ${batch.length} transactions.\n\nIMPORTANT: For CREDIT/INCOME transactions, classify by underlying purpose — do NOT default to \"Income\":\n- Payroll / direct deposits from employers → \"Salary\"\n- Rent payments from roommate (Venmo/Zelle with \"rent\" in description) → \"Housing\"\n- Travel credits, hotel refunds → \"Travel\"\n- Entertainment credits → \"Entertainment\"\n- Uber/Lyft refunds → \"Transportation\"\n- Utility/wifi reimbursements → \"Bills\"\n- Self-transfers (Cash App to yourself, PayPal transfer) → \"Transfers\"\n- Zelle/Venmo from friends or family (not for rent/bills) → \"Gifts\"\n- Only use \"Income\" for truly miscellaneous income (tax refunds, unknown deposits)\n\nTransactions:\n${txLines}`,\n },\n ],\n })\n\n const toolBlock = response.content.find((b) => b.type === 'tool_use')\n if (!toolBlock || toolBlock.type !== 'tool_use') {\n return { results: [], error: 'No tool_use block in response' }\n }\n\n const input = toolBlock.input as { classifications: ClassifyResult[] }\n return { results: input.classifications }\n}\n\nexport async function runClassification(\n reclassifyAll = false\n): Promise<{ classified: number; skipped: number; deduped: number; errors: string[] }> {\n await ensureSchema()\n const apiKey = Deno.env.get('ANTHROPIC_API_KEY')\n if (!apiKey)\n return { classified: 0, skipped: 0, deduped: 0, errors: ['Missing ANTHROPIC_API_KEY'] }\n\n const errors: string[] = []\n const deduped = await runDedup()\n\n const sql = reclassifyAll\n ? `SELECT id, date, description, amount, abs_amount, account_name, institution, is_income FROM ${T.tx} WHERE category_source != 'user' AND is_duplicate = 0 ORDER BY date DESC`\n : `SELECT id, date, description, amount, abs_amount, account_name, institution, is_income FROM ${T.tx} WHERE category IS NULL AND is_duplicate = 0 ORDER BY date DESC`\n\n const txRes = await sqlite.execute(sql)\n const uncategorized = txRes.rows.map((r: any) => ({\n id: String(r[0]),\n date: String(r[1]),\n description: String(r[2]),\n amount: Number(r[3]),\n abs_amount: Number(r[4]),\n account_name: String(r[5] || ''),\n institution: String(r[6] || ''),\n is_income: Number(r[7]),\n }))\n\n if (uncategorized.length === 0) {\n console.log(`Classification: 0 to classify, ${deduped} deduped`)\n return { classified: 0, skipped: 0, deduped, errors }\n }\n\n const systemPrompt = await getConfig('system_prompt', DEFAULT_SYSTEM_PROMPT)\n const cats = await getCategories()\n const client = new Anthropic({ apiKey })\n let classified = 0\n let skipped = 0\n const batchSize = 50\n const affectedMonths = new Set()\n\n for (let i = 0; i < uncategorized.length; i += batchSize) {\n const batch = uncategorized.slice(i, i + batchSize)\n\n try {\n const { results, error: batchError } = await classifyBatch(client, batch, cats, systemPrompt)\n if (batchError) {\n errors.push(`Batch ${i}: ${batchError}`)\n skipped += batch.length\n continue\n }\n\n for (const r of results) {\n const cat = r.category\n const isTransfer = cat === 'Transfers' || cat === 'Investments' ? 1 : 0\n const tx = batch.find((t) => t.id === r.id)\n const isIncome = cat === 'Income' ? 1 : (tx?.is_income ?? 0)\n try {\n await sqlite.execute({\n sql: `UPDATE ${T.tx} SET category = ?, category_source = 'llm', llm_confidence = ?, llm_reasoning = ?, is_transfer = ?, is_income = ? WHERE id = ? AND (category_source != 'user' OR category_source IS NULL)`,\n args: [cat, r.confidence, r.reasoning, isTransfer, isIncome, r.id],\n })\n classified++\n if (tx) affectedMonths.add(tx.date.slice(0, 7))\n } catch {\n skipped++\n }\n }\n } catch (e: any) {\n errors.push(`Batch ${i}: ${e.message}`)\n skipped += batch.length\n }\n }\n\n for (const month of affectedMonths) await recomputeMonth(month)\n console.log(\n `Classification: ${classified} classified, ${skipped} skipped, ${deduped} deduped, ${errors.length} errors`\n )\n return { classified, skipped, deduped, errors }\n}\n\nexport type PreviewResult = {\n id: string\n date: string\n description: string\n abs_amount: number\n account_name: string\n institution: string\n is_income: number\n category: string\n confidence: number\n reasoning: string\n}\n\nexport async function previewClassification(\n customPrompt?: string\n): Promise<{ results: PreviewResult[]; errors: string[] }> {\n await ensureSchema()\n const apiKey = Deno.env.get('ANTHROPIC_API_KEY')\n if (!apiKey) return { results: [], errors: ['Missing ANTHROPIC_API_KEY'] }\n\n const txRes = await sqlite.execute(\n `SELECT id, date, description, amount, abs_amount, account_name, institution, is_income FROM ${T.tx} WHERE category IS NULL AND is_duplicate = 0 ORDER BY date DESC`\n )\n const uncategorized = txRes.rows.map((r: any) => ({\n id: String(r[0]),\n date: String(r[1]),\n description: String(r[2]),\n amount: Number(r[3]),\n abs_amount: Number(r[4]),\n account_name: String(r[5] || ''),\n institution: String(r[6] || ''),\n is_income: Number(r[7]),\n }))\n\n if (uncategorized.length === 0) return { results: [], errors: [] }\n\n const systemPrompt = customPrompt || (await getConfig('system_prompt', DEFAULT_SYSTEM_PROMPT))\n const cats = await getCategories()\n const client = new Anthropic({ apiKey })\n const allResults: PreviewResult[] = []\n const errors: string[] = []\n const batchSize = 50\n\n for (let i = 0; i < uncategorized.length; i += batchSize) {\n const batch = uncategorized.slice(i, i + batchSize)\n\n try {\n const { results, error: batchError } = await classifyBatch(client, batch, cats, systemPrompt)\n if (batchError) {\n errors.push(`Batch ${i}: ${batchError}`)\n continue\n }\n\n for (const r of results) {\n const tx = batch.find((t) => t.id === r.id)\n if (!tx) continue\n allResults.push({\n ...tx,\n category: r.category,\n confidence: r.confidence,\n reasoning: r.reasoning,\n })\n }\n } catch (e: any) {\n errors.push(`Batch ${i}: ${e.message}`)\n }\n }\n\n return { results: allResults, errors }\n}\n\nexport async function applyClassification(\n results: PreviewResult[]\n): Promise<{ applied: number; errors: string[] }> {\n await ensureSchema()\n const cats = await getCategories()\n let applied = 0\n const errors: string[] = []\n const affectedMonths = new Set()\n\n for (const r of results) {\n const cat = cats.includes(r.category) ? r.category : 'Other'\n const isTransfer = cat === 'Transfers' || cat === 'Investments' ? 1 : 0\n const isIncome = cat === 'Income' ? 1 : r.is_income\n try {\n await sqlite.execute({\n sql: `UPDATE ${T.tx} SET category = ?, category_source = 'llm', llm_confidence = ?, llm_reasoning = ?, is_transfer = ?, is_income = ? WHERE id = ? AND (category_source != 'user' OR category_source IS NULL)`,\n args: [cat, r.confidence, r.reasoning, isTransfer, isIncome, r.id],\n })\n applied++\n affectedMonths.add(r.date.slice(0, 7))\n } catch (e: any) {\n errors.push(`${r.id}: ${e.message}`)\n }\n }\n\n for (const month of affectedMonths) await recomputeMonth(month)\n return { applied, errors }\n}\n\nasync function runDedup(): Promise {\n const res = await sqlite.execute(`\n SELECT a.id, b.id, a.description, b.description, a.institution, b.institution\n FROM ${T.tx} a\n JOIN ${T.tx} b ON a.abs_amount = b.abs_amount\n AND ABS(julianday(a.date) - julianday(b.date)) <= 2\n AND a.id < b.id AND a.institution != b.institution\n WHERE a.is_duplicate = 0 AND b.is_duplicate = 0 AND a.abs_amount > 50\n `)\n\n let deduped = 0\n for (const row of res.rows) {\n const idA = String(row[0]),\n idB = String(row[1])\n const descA = String(row[2]).toUpperCase(),\n descB = String(row[3]).toUpperCase()\n const genericPatterns = ['VENMO PAYMENT', 'ZELLE PAYMENT', 'PAYPAL']\n const aIsGeneric = genericPatterns.some((p) => descA.includes(p))\n const bIsGeneric = genericPatterns.some((p) => descB.includes(p))\n\n if (aIsGeneric && !bIsGeneric) {\n await sqlite.execute({\n sql: `UPDATE ${T.tx} SET is_duplicate = 1, duplicate_of = ?, is_transfer = 1, category = 'Transfers', category_source = 'dedup' WHERE id = ?`,\n args: [idB, idA],\n })\n deduped++\n } else if (bIsGeneric && !aIsGeneric) {\n await sqlite.execute({\n sql: `UPDATE ${T.tx} SET is_duplicate = 1, duplicate_of = ?, is_transfer = 1, category = 'Transfers', category_source = 'dedup' WHERE id = ?`,\n args: [idA, idB],\n })\n deduped++\n }\n }\n return deduped\n}\n",[186],"539af19e-5aca-11f1-a319-ee650bb23af1","app.ts",196,["D",1779996045762],23,"http","// Spend Agent — Lean burn tracker\n// Home: stacked bar chart + burn number + category breakdown (expandable)\n// Settings: connect bank + classification prompt + CSV upload\nimport {\n sqlite,\n ensureSchema,\n getConfig,\n setConfig,\n DEFAULT_SYSTEM_PROMPT,\n getCategories,\n saveCategoriesAndCleanup,\n loadCategoryColors,\n recomputeMonth,\n seedDemoData,\n insertSimulatedTransactions,\n T,\n} from './db.ts'\nimport { runClassification, previewClassification, applyClassification } from './classify-engine.ts'\nimport { classifyDrawerHtml } from './classify-ui.ts'\nimport { runSync } from './sync-engine.ts'\nimport { blob } from 'https://esm.town/v/std/blob'\nimport {\n fmt,\n escHtml,\n CAT_COLORS,\n pageShell,\n TELLER_APP_ID,\n CATEGORIES_JS,\n CATEGORY_MODAL_HTML,\n} from './lib.ts'\n\nexport default async function (req: Request): Promise {\n await ensureSchema()\n await seedDemoData()\n await loadCategoryColors()\n const url = new URL(req.url)\n const path = url.pathname\n\n if (path === '/sync' && req.method === 'POST') return handleSync()\n if (path === '/classify' && req.method === 'POST') return handleClassify(false)\n if (path === '/reclassify' && req.method === 'POST') return handleClassify(true)\n if (path === '/save-token' && req.method === 'POST') return handleSaveToken(req)\n if (path === '/update-category' && req.method === 'POST') return handleUpdateCategory(req)\n if (path === '/save-prompt' && req.method === 'POST') return handleSavePrompt(req)\n if (path === '/save-categories' && req.method === 'POST') return handleSaveCategories(req)\n if (path === '/upload-csv' && req.method === 'POST') return handleCsvUpload(req)\n if (path === '/category-count') return handleCategoryCount(url)\n if (path === '/simulate-transactions' && req.method === 'POST')\n return handleSimulateTransactions()\n\n if (path === '/preview-classify' && req.method === 'POST') return handlePreviewClassify(req)\n if (path === '/approve-classify' && req.method === 'POST') return handleApproveClassify(req)\n if (path === '/add-rule' && req.method === 'POST') return handleAddRule(req)\n if (path === '/ask' && req.method === 'POST') return handleAsk(req)\n if (path === '/diagram.png') {\n const png = await blob.get('spend-agent-diagram')\n return new Response(png.body, {\n headers: { 'Content-Type': 'image/png', 'Cache-Control': 'public, max-age=86400' },\n })\n }\n if (path === '/ask') return html(await askPage())\n if (path === '/settings') return html(await settingsPage())\n return html(await burnPage(url))\n}\n\nfunction html(body: string): Response {\n return new Response(body, { headers: { 'Content-Type': 'text/html' } })\n}\n\nasync function handleSimulateTransactions(): Promise {\n try {\n const inserted = await insertSimulatedTransactions()\n return Response.json({ inserted })\n } catch (e: any) {\n return Response.json({ error: e.message }, { status: 500 })\n }\n}\n\nasync function handleCategoryCount(url: URL): Promise {\n const name = url.searchParams.get('name') || ''\n if (!name) return Response.json({ count: 0 })\n const res = await sqlite.execute({\n sql: `SELECT COUNT(*) as cnt FROM ${T.tx} WHERE category = ?`,\n args: [name],\n })\n const count = Number((res.rows[0] as any)[0]) || 0\n return Response.json({ count })\n}\n\n// ── Burn Page (Home) ────────────────────────────────────────────\nasync function burnPage(url: URL): Promise {\n const now = new Date()\n const monthParam = url.searchParams.get('month') || ''\n const curMonth =\n monthParam || `${now.getFullYear()}-${String(now.getMonth() + 1).padStart(2, '0')}`\n\n // Check setup state for empty-state\n const hasCert = !!Deno.env.get('TELLER_CERT')\n const tokensRes = await sqlite.execute('SELECT COUNT(*) FROM sat_teller_tokens')\n const hasAccounts = Number(tokensRes.rows[0]?.[0]) > 0\n const txCountRes = await sqlite.execute('SELECT COUNT(*) FROM sat_transactions')\n const txTotal = Number(txCountRes.rows[0]?.[0]) || 0\n\n if (txTotal === 0) {\n const steps = []\n if (!TELLER_APP_ID) {\n steps.push(`

Get started

Upload a CSV below, or connect a bank by setting TELLER_APP_ID in your env vars. Sandbox works without certificates — add TELLER_CERT + TELLER_KEY for real banks.

`)\n } else if (!hasAccounts) {\n steps.push(`

Connect your bank

Link a checking or credit card account to start tracking.

\n Go to Settings\n

`)\n } else {\n steps.push(`

Syncing transactions...

Your bank is connected. Transactions will appear after the next sync.

\n \n

`)\n }\n steps.push(`

Or upload a CSV export from your credit card or Venmo

\n Upload CSV\n

`)\n const scripts = ``\n return pageShell('Burn', '/', steps.join(''), scripts)\n }\n\n // ── Stacked bar chart data: per-month per-category ──\n const stackRes = await sqlite.execute(\n `SELECT substr(date,1,7) AS m, COALESCE(category, 'Unclassified') AS cat, SUM(abs_amount) AS spend\n FROM sat_transactions\n WHERE is_income = 0 AND is_transfer = 0 AND is_duplicate = 0\n GROUP BY m, cat\n ORDER BY m DESC`\n )\n\n // Build per-month structure\n const monthMap: Record<\n string,\n { month: string; total: number; cats: { cat: string; spend: number }[] }\n > = {}\n for (const r of stackRes.rows) {\n const m = String(r[0])\n const cat = String(r[1])\n const spend = Number(r[2])\n if (!monthMap[m]) monthMap[m] = { month: m, total: 0, cats: [] }\n monthMap[m].total += spend\n monthMap[m].cats.push({ cat, spend })\n }\n\n const allMonths = Object.values(monthMap)\n .sort((a, b) => a.month.localeCompare(b.month))\n .slice(-12)\n const maxSpend = Math.max(...allMonths.map((m) => m.total), 1)\n\n // Per-month income for net chart\n const incomeStackRes = await sqlite.execute(\n `SELECT substr(date,1,7) AS m, SUM(abs_amount) AS inc\n FROM sat_transactions\n WHERE is_income = 1 AND is_transfer = 0 AND is_duplicate = 0\n GROUP BY m ORDER BY m DESC`\n )\n const monthIncomeMap: Record = {}\n for (const r of incomeStackRes.rows) {\n monthIncomeMap[String(r[0])] = Number(r[1])\n }\n\n // Current month spend\n const curData = allMonths.find((m) => m.month === curMonth)\n const monthSpend = curData?.total || 0\n\n // Unclassified count\n const uncRes = await sqlite.execute({\n sql: 'SELECT COUNT(*) FROM sat_transactions WHERE category IS NULL AND is_duplicate = 0 AND substr(date,1,7) = ?',\n args: [curMonth],\n })\n const uncCount = Number(uncRes.rows[0]?.[0]) || 0\n\n // Category breakdown for current month\n const cats = await getCategories()\n const catRes = await sqlite.execute({\n sql: `SELECT category, SUM(abs_amount) as total, COUNT(*) as cnt\n FROM sat_transactions WHERE substr(date,1,7) = ? AND is_income = 0 AND is_transfer = 0 AND is_duplicate = 0\n GROUP BY category ORDER BY SUM(abs_amount) DESC`,\n args: [curMonth],\n })\n\n // Income total for current month\n const incRes = await sqlite.execute({\n sql: `SELECT SUM(abs_amount) FROM sat_transactions\n WHERE substr(date,1,7) = ? AND is_income = 1 AND is_transfer = 0 AND is_duplicate = 0`,\n args: [curMonth],\n })\n const monthIncome = Number(incRes.rows[0]?.[0]) || 0\n const netCashFlow = monthIncome - monthSpend\n\n // All transactions for this month\n const txRes = await sqlite.execute({\n sql: `SELECT id, date, description, abs_amount, category, category_source, llm_confidence, llm_reasoning, is_income, is_transfer, account_name, institution\n FROM sat_transactions WHERE substr(date,1,7) = ? AND is_duplicate = 0\n ORDER BY date DESC, abs_amount DESC`,\n args: [curMonth],\n })\n\n // Group by category + collect income transactions\n const txByCategory: Record = {}\n const incomeTxs: any[] = []\n for (const r of txRes.rows) {\n const cat = r[4] ? String(r[4]) : 'Unclassified'\n const isIncome = Number(r[8])\n const isTransfer = Number(r[9])\n const txObj = {\n id: String(r[0]),\n date: String(r[1]),\n desc: String(r[2]),\n amount: Number(r[3]),\n category: r[4] ? String(r[4]) : null,\n source: r[5] ? String(r[5]) : null,\n confidence: r[6] != null ? Number(r[6]) : null,\n reasoning: r[7] ? String(r[7]) : null,\n account: String(r[10] || ''),\n institution: String(r[11] || ''),\n }\n if (isTransfer) continue\n if (isIncome) {\n incomeTxs.push(txObj)\n } else {\n if (!txByCategory[cat]) txByCategory[cat] = []\n txByCategory[cat].push(txObj)\n }\n }\n\n // ── Stacked bar chart HTML ──\n // Collect global category totals for legend (top 7)\n const globalCatTotals: Record = {}\n for (const m of allMonths) {\n for (const c of m.cats) {\n globalCatTotals[c.cat] = (globalCatTotals[c.cat] || 0) + c.spend\n }\n }\n const legendCats = Object.entries(globalCatTotals)\n .sort((a, b) => b[1] - a[1])\n .slice(0, 7)\n .map(([cat]) => cat)\n\n const barChart = allMonths\n .map((m) => {\n const totalH = Math.max(Math.round((m.total / maxSpend) * 120), 2)\n const active = m.month === curMonth\n const label = new Date(m.month + '-15').toLocaleDateString('en-US', {\n month: 'short',\n })\n const shortYear = m.month.slice(2, 4)\n\n // Consolidate small categories (<4% of month) into \"Other\" for cleaner bars\n const threshold = m.total * 0.04\n const bigCats: { cat: string; spend: number }[] = []\n let smallSum = 0\n for (const c of m.cats) {\n if (c.spend >= threshold) bigCats.push(c)\n else smallSum += c.spend\n }\n if (smallSum > 0) bigCats.push({ cat: 'Other', spend: smallSum })\n bigCats.sort((a, b) => a.spend - b.spend)\n\n const segments = bigCats\n .map((c) => {\n const segH = Math.max(Math.round((c.spend / m.total) * totalH), 2)\n const color = CAT_COLORS[c.cat] || '#6b7280'\n return `

`\n })\n .join('')\n\n return `

${m.total >= 1000 ? '$' + (m.total / 1000).toFixed(1) + 'k' : '$' + Math.round(m.total)}

${segments}

${label}
'${shortYear}

`\n })\n .join('')\n\n const legend = legendCats\n .map((cat) => {\n const color = CAT_COLORS[cat] || '#6b7280'\n return `${cat}`\n })\n .join('')\n\n // ── Net income bar chart ──\n const netMonths = allMonths.map((m) => {\n const inc = monthIncomeMap[m.month] || 0\n const net = inc - m.total\n return { month: m.month, spend: m.total, income: inc, net }\n })\n const maxNetAbs = Math.max(...netMonths.map((m) => Math.abs(m.net)), 1)\n\n const netBarChart = netMonths\n .map((m) => {\n const active = m.month === curMonth\n const label = new Date(m.month + '-15').toLocaleDateString('en-US', { month: 'short' })\n const shortYear = m.month.slice(2, 4)\n const barH = Math.max(Math.round((Math.abs(m.net) / maxNetAbs) * 100), 2)\n const color = m.net >= 0 ? '#22c55e' : '#ef4444'\n const sign = m.net >= 0 ? '+' : '-'\n const valStr =\n Math.abs(m.net) >= 1000\n ? `${sign}$${(Math.abs(m.net) / 1000).toFixed(1)}k`\n : `${sign}$${Math.round(Math.abs(m.net))}`\n\n return `

${valStr}

${label}
'${shortYear}

`\n })\n .join('')\n\n const netLegend = `Positive\n Negative`\n\n // Category accordion\n const catRows = catRes.rows\n .map((r: any) => {\n const cat = String(r[0] || 'Unclassified')\n const total = Number(r[1])\n const count = Number(r[2])\n const color = CAT_COLORS[cat] || '#6b7280'\n const pctVal = monthSpend > 0 ? Math.round((total / monthSpend) * 100) : 0\n const barW = monthSpend > 0 ? Math.round((total / monthSpend) * 100) : 0\n const slug = cat.toLowerCase().replace(/[^a-z]/g, '')\n\n const txs = txByCategory[cat] || []\n const txRowsHtml = txs\n .map((tx) => {\n const dateLabel = new Date(tx.date + 'T12:00:00').toLocaleDateString('en-US', {\n month: 'short',\n day: 'numeric',\n })\n const confBadge =\n tx.confidence != null && tx.confidence < 0.7\n ? 'LOW'\n : ''\n const catOptions = cats\n .map(\n (c: string) =>\n ``\n )\n .join('')\n\n return `

\n ${dateLabel}\n ${escHtml(tx.desc.slice(0, 40))}\n -${fmt(tx.amount)}\n

${escHtml(tx.desc)}

\n ${escHtml(tx.account)}${tx.institution ? ' · ' + escHtml(tx.institution) : ''}\n ${tx.reasoning ? `${escHtml(tx.reasoning)}` : ''}\n

\n ${confBadge}\n

`\n })\n .join('')\n\n return `

\n \n ${escHtml(cat)}\n ${count}\n

\n ${fmt(total)}\n ${pctVal}%\n ▶\n

${txRowsHtml}

`\n })\n .join('')\n\n // Collect unclassified transactions for drawer\n const uncTxs = (txByCategory['Unclassified'] || []).map((tx: any) => ({\n id: tx.id,\n date: tx.date,\n description: tx.desc,\n abs_amount: tx.amount,\n account_name: tx.account,\n institution: tx.institution,\n }))\n const currentPrompt = await getConfig('system_prompt', DEFAULT_SYSTEM_PROMPT)\n const drawerUi = classifyDrawerHtml(uncCount, uncTxs, currentPrompt)\n const uncBanner = drawerUi.banner\n\n const monthLabel = new Date(curMonth + '-15').toLocaleDateString('en-US', {\n month: 'long',\n year: 'numeric',\n })\n\n // Group income by category\n const incomeByCategory: Record = {}\n for (const tx of incomeTxs) {\n const cat = tx.category || 'Income'\n if (!incomeByCategory[cat]) incomeByCategory[cat] = { txs: [], total: 0 }\n incomeByCategory[cat].txs.push(tx)\n incomeByCategory[cat].total += tx.amount\n }\n // ── Budget view: merged spend + income per category ──\n const spendByCategory: Record = {}\n for (const r of catRes.rows) {\n spendByCategory[String(r[0] || 'Unclassified')] = Number(r[1])\n }\n const allBudgetCats = new Set([...Object.keys(spendByCategory), ...Object.keys(incomeByCategory)])\n const budgetRows = [...allBudgetCats]\n .map((cat) => {\n const spend = spendByCategory[cat] || 0\n const income = incomeByCategory[cat]?.total || 0\n const net = income - spend\n const spendTxs = txByCategory[cat] || []\n const incTxs = incomeByCategory[cat]?.txs || []\n return { cat, spend, income, net, spendTxs, incTxs, totalActivity: spend + income }\n })\n .sort((a, b) => b.totalActivity - a.totalActivity)\n\n const budgetCatRows = budgetRows\n .map((row) => {\n const color = CAT_COLORS[row.cat] || '#6b7280'\n const slug = 'bgt-' + row.cat.toLowerCase().replace(/[^a-z]/g, '')\n const netColor = row.net >= 0 ? '#22c55e' : '#ef4444'\n const netSign = row.net >= 0 ? '+' : '-'\n const txCount = row.spendTxs.length + row.incTxs.length\n // Sub-labels showing spend and income breakdown\n const parts: string[] = []\n if (row.spend > 0) parts.push(`-${fmt(row.spend)}`)\n if (row.income > 0) parts.push(`+${fmt(row.income)}`)\n const breakdown =\n parts.length === 2\n ? `(${parts.join(' ')})`\n : ''\n\n // Mix spend + income transactions sorted by date\n const allTxs = [\n ...row.spendTxs.map((tx: any) => ({ ...tx, _type: 'spend' })),\n ...row.incTxs.map((tx: any) => ({ ...tx, _type: 'income' })),\n ].sort((a: any, b: any) => b.date.localeCompare(a.date))\n\n const txRowsHtml = allTxs\n .map((tx: any) => {\n const dateLabel = new Date(tx.date + 'T12:00:00').toLocaleDateString('en-US', {\n month: 'short',\n day: 'numeric',\n })\n const amtColor = tx._type === 'income' ? '#22c55e' : '#ef4444'\n const amtSign = tx._type === 'income' ? '+' : '-'\n const catOptions = cats\n .map(\n (c: string) =>\n ``\n )\n .join('')\n return `

\n ${dateLabel}\n ${escHtml(tx.desc.slice(0, 40))}\n ${amtSign}${fmt(tx.amount)}\n

${escHtml(tx.desc)}

\n ${escHtml(tx.account)}${tx.institution ? ' · ' + escHtml(tx.institution) : ''}\n ${tx.reasoning ? `${escHtml(tx.reasoning)}` : ''}\n

\n \n

`\n })\n .join('')\n\n return `

\n \n ${escHtml(row.cat)}\n ${txCount}\n

\n ${netSign}${fmt(Math.abs(row.net))}\n ${breakdown}\n ▶\n

${txRowsHtml}

`\n })\n .join('')\n\n const body = `\n ${uncBanner}\n

${barChart}

${legend}

${netBarChart}

${netLegend}

\n ${monthLabel}\n

\n \n \n

Total Spend

\n ${fmt(monthSpend)}\n

Income

\n ${monthIncome > 0 ? '+' : ''}${fmt(monthIncome)}\n

−

Spend

\n ${fmt(monthSpend)}\n

Net

\n = 0 ? '#22c55e' : '#ef4444'}\">${netCashFlow >= 0 ? '+' : '-'}${fmt(Math.abs(netCashFlow))}\n

${catRows || '

No spending this month

\n \n ${drawerUi.drawer}`\n\n const scripts = ``\n\n return pageShell('Burn', '/', body, scripts)\n}\n\n// ── Ask Page (Agent Q&A) ────────────────────────────────────────\nasync function askPage(): Promise {\n const suggestions = [\n \"Where's my money going this month?\",\n 'How does this month compare to last?',\n 'Any new subscriptions I should know about?',\n \"What's my biggest spending spike?\",\n 'Am I spending more on food than usual?',\n 'What can I cut to save $500/month?',\n ]\n\n const chips = suggestions\n .map(\n (s) => ``\n )\n .join('')\n\n const body = `\n

Ask about your spending

AI-powered insights from your real transaction data

${chips}

\n \n

\n \n \n \n \n

`\n\n const scripts = ``\n\n return pageShell('Ask', '/ask', body, scripts)\n}\n\nasync function gatherFinancialContext(): Promise {\n const now = new Date()\n const curMonth = `${now.getFullYear()}-${String(now.getMonth() + 1).padStart(2, '0')}`\n\n // Last 6 months summaries\n const summaries = await sqlite.execute(\n `SELECT month, total_spend, total_income, category_breakdown, tx_count\n FROM sat_monthly_summaries ORDER BY month DESC LIMIT 6`\n )\n\n let ctx = `## Monthly Summaries (last 6 months)\\n`\n for (const r of summaries.rows) {\n const month = String(r[0])\n const spend = Number(r[1])\n const income = Number(r[2])\n const cats = r[3] ? String(r[3]) : '{}'\n ctx += `${month}: $${spend.toFixed(0)} spent, $${income.toFixed(0)} income | ${cats}\\n`\n }\n\n // Current month transactions (last 50)\n const txRes = await sqlite.execute({\n sql: `SELECT date, description, abs_amount, category, account_name, institution, is_income, is_transfer\n FROM sat_transactions\n WHERE substr(date,1,7) = ? AND is_duplicate = 0\n ORDER BY date DESC LIMIT 50`,\n args: [curMonth],\n })\n\n ctx += `\\n## Current month transactions (${curMonth}, most recent 50)\\n`\n ctx += `date | description | amount | category | account\\n`\n for (const r of txRes.rows) {\n const isIncome = Number(r[6])\n const isTransfer = Number(r[7])\n const tag = isIncome ? ' [INCOME]' : isTransfer ? ' [TRANSFER]' : ''\n ctx += `${r[0]} | ${String(r[1]).slice(0, 50)} | $${Number(r[2]).toFixed(2)}${tag} | ${r[3] || 'Unclassified'} | ${r[4] || ''}\\n`\n }\n\n // Recurring charges detection\n const recurRes = await sqlite.execute(\n `SELECT description, COUNT(*) as cnt, ROUND(AVG(abs_amount),2) as avg_amt\n FROM sat_transactions\n WHERE is_income = 0 AND is_transfer = 0 AND is_duplicate = 0\n GROUP BY LOWER(TRIM(description))\n HAVING cnt >= 3\n ORDER BY cnt DESC LIMIT 20`\n )\n\n ctx += `\\n## Likely recurring charges (appeared 3+ times)\\n`\n for (const r of recurRes.rows) {\n ctx += `${r[0]} — ${r[1]}x, avg $${r[2]}\\n`\n }\n\n // Recent user corrections (for context)\n const overrides = await sqlite.execute(\n `SELECT description, category FROM sat_transactions\n WHERE category_source = 'user' ORDER BY created_at DESC LIMIT 20`\n )\n if (overrides.rows.length > 0) {\n ctx += `\\n## Recent user corrections (these override LLM classifications)\\n`\n for (const r of overrides.rows) {\n ctx += `${String(r[0]).slice(0, 50)} → ${r[1]}\\n`\n }\n }\n\n return ctx\n}\n\nasync function handleAsk(req: Request): Promise {\n try {\n const { question, images } = await req.json()\n if (!question && (!images || images.length === 0))\n return Response.json({ error: 'No question' }, { status: 400 })\n\n const apiKey = Deno.env.get('ANTHROPIC_API_KEY')\n if (!apiKey)\n return Response.json({\n answer: 'ANTHROPIC_API_KEY not set. Add it in Val Town environment variables.',\n })\n\n const context = await gatherFinancialContext()\n const hasImages = images && images.length > 0\n\n // Build multimodal content blocks\n const content: any[] = []\n if (hasImages) {\n for (const img of images) {\n const match = img.match(/^data:(image\\/[a-zA-Z+]+);base64,(.+)$/)\n if (match) {\n content.push({\n type: 'image',\n source: { type: 'base64', media_type: match[1], data: match[2] },\n })\n }\n }\n }\n content.push({\n type: 'text',\n text:\n question || 'What do you see in this screenshot? Suggest any classification corrections.',\n })\n\n const systemPrompt = `You are a helpful personal finance assistant analyzing the user's real transaction data.${hasImages ? ' The user has shared a screenshot — analyze it alongside their transaction data to identify any misclassified transactions.' : ''} Be concise, specific, and use actual dollar amounts from the data. Format responses with markdown: use **bold** for key numbers and totals, bullet lists for category breakdowns, and ### headings to organize sections. Keep paragraphs short. If you spot concerning patterns, flag them directly. Don't be preachy — be useful.${hasImages ? '\\n\\nWhen suggesting category corrections, list each one clearly: the transaction description, its current category, and what it should be changed to.' : ''}\\n\\n${context}`\n\n const res = await fetch('https://api.anthropic.com/v1/messages', {\n method: 'POST',\n headers: {\n 'x-api-key': apiKey,\n 'anthropic-version': '2023-06-01',\n 'content-type': 'application/json',\n },\n body: JSON.stringify({\n model: 'claude-haiku-4-5-20251001',\n max_tokens: 2048,\n system: systemPrompt,\n messages: [{ role: 'user', content }],\n }),\n })\n\n const data = await res.json()\n const answer = data.content?.[0]?.text || 'No response from AI.'\n return Response.json({ answer })\n } catch (e: any) {\n return Response.json({ answer: 'Error: ' + e.message })\n }\n}\n\n// ── Settings Page ───────────────────────────────────────────────\nasync function settingsPage(): Promise {\n const prompt = await getConfig('system_prompt', DEFAULT_SYSTEM_PROMPT)\n const tellerEnv = Deno.env.get('TELLER_ENV') || 'sandbox'\n const isSandbox = tellerEnv === 'sandbox'\n const categories = await getCategories()\n const categoriesListHtml = categories\n .map((c) => {\n const o = c === 'Other'\n const s = escHtml(c)\n const col = CAT_COLORS[c] || '#6b7280'\n return `

${o ? 'required' : ''}

`\n })\n .join('')\n\n // Teller tokens\n const tokensRes = await sqlite.execute(\n 'SELECT id, institution, linked_at FROM sat_teller_tokens ORDER BY linked_at DESC'\n )\n const tellerTokens = tokensRes.rows.map((r: any) => ({\n id: String(r[0]),\n inst: String(r[1]),\n linked: String(r[2]),\n }))\n\n // Sandbox credentials disclosed via a small

accordion below the\n // \"Get one free\" / \"Connect Bank\" affordance. Discoverable but unobtrusive.\n // Source: https://teller.io/docs/guides/sandbox\n const sandboxCredsHtml = isSandbox\n ? `\n

View sandbox test credentials

\n \n \n \n \n \n

Username	`username` (or `otp` / `challenge` for MFA flows)
Password	`password`
OTP code	`0000` (if username = `otp`)
Challenge	`blue` (if username = `challenge`)

All sandbox institutions behave identically. Teller sandbox docs →

`\n : ''\n\n function renderAccountList(\n tokens: { id: string; inst: string; linked: string }[],\n source: string\n ) {\n if (tokens.length === 0)\n return `No ${source} accounts linked`\n return tokens\n .map(\n (\n t\n ) => `

\n \n ${escHtml(t.inst)}\n Linked ${new Date(t.linked).toLocaleDateString('en-US', { month: 'short', day: 'numeric' })}\n \n

`\n )\n .join('')\n }\n\n const body = `\n

Teller Accounts

\n Banks & credit cards\n

\n ${renderAccountList(tellerTokens, 'Teller')}\n ${!TELLER_APP_ID ? `

The accounts above are demo placeholders. Set TELLER_APP_ID in env vars to connect a real (sandbox or production) bank. Get one free at teller.io

` : ''}\n ${TELLER_APP_ID ? `

` : ''}\n

${sandboxCredsHtml}\n \n

CSV Import

\n \n \n

\n \n

Select a format above first

\n \n

\n \n \n \n

Classification Prompt

Tell the AI how to categorize. Use natural language.

\n \n

\n \n \n

Diff · v198