${sc.icon} ${esc(sc.label)}switch account ⇄

\n${content}

\n\n\n`;\n}\n\n// --- auth gate: every route requires the owner's session ---\napp.use(\"*\", async (c, next) => {\n const session = await getOAuthUserData(c.req.raw);\n if (!session?.user) return c.redirect(\"/auth/login\");\n if (session.user.id !== OWNER_ID) return c.text(\"This is a personal app. Access denied.\", 403);\n await initDb();\n await next();\n});\n\n// ---------- Account picker (landing page, no bottom nav) ----------\napp.get(\"/\", async (c) => {\n const month = today().slice(0, 7);\n const [y, m] = [parseInt(month.slice(0, 4)), parseInt(month.slice(5, 7))];\n const start = `${month}-01`;\n const end = `${m === 12 ? y + 1 : y}-${String((m % 12) + 1).padStart(2, \"0\")}-01`;\n\n const spend = await sqlite.execute({\n sql: `SELECT a.bank, COALESCE(SUM(t.amount_cents),0) s FROM transactions t\n JOIN accounts a ON a.id=t.account_id\n WHERE t.direction='withdrawal' AND t.status='confirmed' AND t.txn_date>=? AND t.txn_date = {};\n for (const r of spend.rows as any[]) byBank[String(r.bank)] = Number(r.s);\n const total = Object.values(byBank).reduce((a, b) => a + b, 0);\n\n const pend = await sqlite.execute(\n `SELECT a.bank, COUNT(*) n FROM transactions t JOIN accounts a ON a.id=t.account_id\n WHERE t.status='pending_review' GROUP BY a.bank`);\n const pendByBank: Record = {};\n for (const r of pend.rows as any[]) pendByBank[String(r.bank)] = Number(r.n);\n const pendTotal = Object.values(pendByBank).reduce((a, b) => a + b, 0);\n\n const tile = (key: string, amount: number, pending: number, sub: string) => {\n const s = SCOPES[key];\n return `\n ${s.icon}\n

${esc(s.label)}

${pending ? `${pending} to review` : sub}

S$ ${money(amount)}

`;\n };\n\n const html = `\n\nSpending Tracker\n\n

Spending Tracker

${month} · choose an account

\n${tile(\"all\", total, pendTotal, \"everything combined\")}\n${tile(\"ocbc\", byBank[\"OCBC\"] ?? 0, pendByBank[\"OCBC\"] ?? 0, \"FRANK account, card, PayNow, NETS\")}\n${tile(\"dbs\", byBank[\"DBS\"] ?? 0, pendByBank[\"DBS\"] ?? 0, \"DBS card + PayLah!\")}\n${tile(\"cash\", byBank[\"CASH\"] ?? 0, pendByBank[\"CASH\"] ?? 0, \"cash & manual entries\")}\n

Amounts are this month's confirmed spending.

`;\n return c.html(html);\n});\n\n// must be registered before \"/:scope\" or it would be treated as a scope name\napp.get(\"/source\", (c) => c.redirect(parseVal().links.self.val));\n\n// ---------- scope guard ----------\napp.use(\"/:scope/*\", async (c, next) => {\n if (!isScope(c.req.param(\"scope\"))) return c.redirect(\"/\");\n await next();\n});\n\n// ---------- Dashboard ----------\napp.get(\"/:scope\", async (c) => {\n const scope = c.req.param(\"scope\");\n if (!isScope(scope)) return c.redirect(\"/\");\n const sc = scopeSql(scope);\n const month = c.req.query(\"month\") ?? today().slice(0, 7);\n const [y, m] = [parseInt(month.slice(0, 4)), parseInt(month.slice(5, 7))];\n const start = `${month}-01`;\n const end = `${m === 12 ? y + 1 : y}-${String((m % 12) + 1).padStart(2, \"0\")}-01`;\n const pm = `${m === 1 ? y - 1 : y}-${String(m === 1 ? 12 : m - 1).padStart(2, \"0\")}`;\n const W = `direction='withdrawal' AND status='confirmed' AND txn_date>=? AND txn_date=? AND t.txn_date Number(r.s)));\n const delta = total - prev;\n const nextM = month < today().slice(0, 7)\n ? `${m === 12 ? y + 1 : y}-${String((m % 12) + 1).padStart(2, \"0\")}` : null;\n const lastDay = new Date(new Date(`${end}T00:00:00Z`).getTime() - 864e5).toISOString().slice(0, 10);\n const base = `/${scope}`;\n\n const content = `\n

‹

${month}

${nextM ? `›` : \"›\"}

Spent this month

S$ ${money(total)}

\n${prev ? `

${delta > 0 ? \"▲\" : \"▼\"} S$ ${money(Math.abs(delta))} vs last month

` : \"\"}

\n${byCat.rows.length\n ? `

By category — tap to see transactions

${byCat.rows.map((r: any) => `\n ${r.icon}

${esc(r.name)}S$ ${money(Number(r.s))} ›

`).join(\"\")}

By payment method

${byMethod.rows.map((r: any) => `

${esc(r.name)}S$ ${money(Number(r.s))}

`).join(\"\")}

`\n : `

No confirmed spending for ${month} yet.

`}\n

Recent

${recent.rows.length ? recent.rows.map((t: any) => `\n ${t.ci ?? \"❓\"}\n

${esc(t.merchant || t.description || \"—\")}${t.status === \"pending_review\" ? \" ⏳\" : \"\"}

${t.txn_date} · ${esc(t.an ?? \"\")}

\n ${t.direction === \"deposit\" ? \"+\" : \"\"}S$ ${money(t.amount_cents)}`).join(\"\") : `

Nothing yet.

`;\n return c.html(layout(\"Spending Tracker\", scope, \"\", await pendingCount(scope), content));\n});\n\n// ---------- Add / edit ----------\nasync function form(scope: string, t: any | null): Promise {\n const bank = SCOPES[scope].bank;\n const cats = await sqlite.execute(\"SELECT * FROM categories WHERE is_archived=0 ORDER BY id\");\n const accs = await sqlite.execute(\n bank\n ? { sql: \"SELECT * FROM accounts WHERE is_active=1 AND bank=? ORDER BY id\", args: [bank] }\n : \"SELECT * FROM accounts WHERE is_active=1 ORDER BY id\");\n const base = `/${scope}`;\n const quick = t ? { rows: [] } : await sqlite.execute(\n `SELECT merchant, category_id, account_id, COUNT(*) n FROM transactions WHERE source='manual' AND merchant!='' GROUP BY merchant, category_id, account_id ORDER BY n DESC LIMIT 6`);\n return `

${t ? \"Edit\" : \"Add expense\"}

\n${quick.rows.length ? `

${quick.rows.map((q: any) => ``).join(\"\")}

` : \"\"}\n\n${t ? `` : \"\"}`;\n}\n\napp.get(\"/:scope/add\", async (c) => {\n const scope = c.req.param(\"scope\");\n return c.html(layout(\"Add\", scope, \"/add\", await pendingCount(scope), await form(scope, null)));\n});\n\napp.post(\"/:scope/add\", async (c) => {\n const scope = c.req.param(\"scope\");\n const f = await c.req.parseBody();\n await sqlite.execute({\n sql: `INSERT INTO transactions(amount_cents, txn_date, merchant, description, category_id, account_id, source, status)\n VALUES(?,?,?,?,?,?, 'manual', 'confirmed')`,\n args: [Math.round(parseFloat(String(f.amount)) * 100), String(f.txn_date), String(f.merchant ?? \"\").trim(),\n String(f.note ?? \"\").trim(), Number(f.category_id), Number(f.account_id)],\n });\n return c.redirect(`/${scope}`);\n});\n\napp.get(\"/:scope/transactions/:id/edit\", async (c) => {\n const scope = c.req.param(\"scope\");\n const r = await sqlite.execute({ sql: \"SELECT * FROM transactions WHERE id=?\", args: [c.req.param(\"id\")] });\n if (!r.rows.length) return c.text(\"Not found\", 404);\n return c.html(layout(\"Edit\", scope, \"\", await pendingCount(scope), await form(scope, r.rows[0])));\n});\n\napp.post(\"/:scope/transactions/:id/edit\", async (c) => {\n const scope = c.req.param(\"scope\");\n const f = await c.req.parseBody();\n const merchant = String(f.merchant ?? \"\").trim();\n await sqlite.execute({\n sql: `UPDATE transactions SET amount_cents=?, txn_date=?, merchant=?, description=?, category_id=?, account_id=?, updated_at=datetime('now') WHERE id=?`,\n args: [Math.round(parseFloat(String(f.amount)) * 100), String(f.txn_date), merchant,\n String(f.note ?? \"\").trim(), Number(f.category_id), Number(f.account_id), c.req.param(\"id\")],\n });\n if (f.remember && merchant) {\n await sqlite.batch([\n { sql: \"DELETE FROM merchant_rules WHERE lower(pattern)=lower(?)\", args: [merchant] },\n { sql: \"INSERT INTO merchant_rules(pattern, category_id) VALUES(?,?)\", args: [merchant, Number(f.category_id)] },\n ]);\n }\n return c.redirect(`/${scope}/transactions`);\n});\n\napp.post(\"/:scope/transactions/:id/delete\", async (c) => {\n await sqlite.execute({ sql: \"DELETE FROM transactions WHERE id=?\", args: [c.req.param(\"id\")] });\n return c.redirect(`/${c.req.param(\"scope\")}/transactions`);\n});\n\n// ---------- Review queue ----------\napp.get(\"/:scope/review\", async (c) => {\n const scope = c.req.param(\"scope\");\n const sc = scopeSql(scope);\n const base = `/${scope}`;\n const cats = await sqlite.execute(\"SELECT * FROM categories WHERE is_archived=0 ORDER BY id\");\n const rows = await sqlite.execute({\n sql: `SELECT * FROM transactions WHERE status='pending_review'${sc.sql} ORDER BY txn_date DESC, id DESC`,\n args: sc.args,\n });\n const failed = await sqlite.execute(\n \"SELECT * FROM email_alerts WHERE parse_status='failed' ORDER BY id DESC LIMIT 20\");\n const content = `

Review captured transactions

\n${failed.rows.length ? `

⚠ ${failed.rows.length} alert(s) failed to parse

${failed.rows.map((a: any) => `\n

${esc(a.subject)}

${esc(a.received_at)} · ${esc(a.failure_reason)}\n

raw

${esc(a.raw_body)}

`).join(\"\")}

` : \"\"}\n${rows.rows.length ? ``\n : `

Nothing waiting for review. 🎉

`}`;\n return c.html(layout(\"Review\", scope, \"/review\", await pendingCount(scope), content));\n});\n\napp.post(\"/:scope/review/confirm\", async (c) => {\n const f = await c.req.parseBody();\n const stmts = [];\n for (const key of Object.keys(f)) {\n if (!key.startsWith(\"cat_\")) continue;\n const id = Number(key.slice(4));\n if (f[`dismiss_${id}`]) {\n stmts.push({ sql: \"DELETE FROM transactions WHERE id=? AND status='pending_review'\", args: [id] });\n } else {\n stmts.push({\n sql: \"UPDATE transactions SET status='confirmed', category_id=?, updated_at=datetime('now') WHERE id=? AND status='pending_review'\",\n args: [Number(f[key]), id],\n });\n }\n }\n if (stmts.length) await sqlite.batch(stmts as any);\n return c.redirect(`/${c.req.param(\"scope\")}/review`);\n});\n\napp.post(\"/:scope/review/alerts/:id/dismiss\", async (c) => {\n await sqlite.execute({ sql: \"UPDATE email_alerts SET parse_status='skipped' WHERE id=?\", args: [c.req.param(\"id\")] });\n return c.redirect(`/${c.req.param(\"scope\")}/review`);\n});\n\n// ---------- History ----------\napp.get(\"/:scope/transactions\", async (c) => {\n const scope = c.req.param(\"scope\");\n const sc = scopeSql(scope);\n const base = `/${scope}`;\n const q = c.req.query(\"q\") ?? \"\", cat = c.req.query(\"category\") ?? \"\", start = c.req.query(\"start\") ?? \"\", end = c.req.query(\"end\") ?? \"\";\n const where = [\"1=1\"]; const args: any[] = [];\n if (q) { where.push(\"(t.merchant LIKE ? OR t.description LIKE ?)\"); args.push(`%${q}%`, `%${q}%`); }\n if (cat) { where.push(\"t.category_id=?\"); args.push(Number(cat)); }\n if (start) { where.push(\"t.txn_date>=?\"); args.push(start); }\n if (end) { where.push(\"t.txn_date<=?\"); args.push(end); }\n const rows = await sqlite.execute({\n sql: `SELECT t.*, c.name cn, c.icon ci, a.name an FROM transactions t\n LEFT JOIN categories c ON c.id=t.category_id LEFT JOIN accounts a ON a.id=t.account_id\n WHERE ${where.join(\" AND \")}${sc.sql.replace(\"account_id\", \"t.account_id\")}\n ORDER BY t.txn_date DESC, t.id DESC LIMIT 500`,\n args: [...args, ...sc.args],\n });\n const cats = await sqlite.execute(\"SELECT * FROM categories WHERE is_archived=0 ORDER BY id\");\n const total = rows.rows.reduce((s: number, r: any) => r.direction === \"withdrawal\" && r.status === \"confirmed\" ? s + r.amount_cents : s, 0);\n const content = `

History

\n\n

${rows.rows.length} transactions · confirmed spend S$ ${money(total)}

${rows.rows.length ? rows.rows.map((t: any) => `\n ${t.ci ?? \"❓\"}\n

${esc(t.merchant || t.description || \"—\")}${t.status === \"pending_review\" ? \" ⏳\" : \"\"}

\n ${t.txn_date} · ${esc(t.cn ?? \"\")} · ${esc(t.an ?? \"\")}

\n ${t.direction === \"deposit\" ? \"+\" : \"\"}S$ ${money(t.amount_cents)}`).join(\"\") : `

No transactions match.

`;\n return c.html(layout(\"History\", scope, \"/transactions\", await pendingCount(scope), content));\n});\n\n// ---------- Statement import (OCBC only) ----------\nconst norm = (s: string) => s.toLowerCase().replace(/[^a-z0-9]/g, \"\");\nfunction sameMerchant(a: string, b: string): boolean {\n const x = norm(a), y = norm(b);\n if (!x || !y) return false;\n return x === y || x.includes(y.slice(0, 10)) || y.includes(x.slice(0, 10));\n}\nconst dayDiff = (a: string, b: string) =>\n Math.abs(new Date(a).getTime() - new Date(b).getTime()) / 864e5;\n\napp.get(\"/:scope/import\", async (c) => {\n const scope = c.req.param(\"scope\");\n if (!SCOPES[scope].import) return c.redirect(`/${scope}`);\n const base = `/${scope}`;\n const batches = await sqlite.execute(\n \"SELECT id, file_name, period_start, period_end, row_count, status, created_at FROM import_batches ORDER BY id DESC LIMIT 8\");\n const content = `

Import statement

Upload an OCBC account statement PDF. You'll see a preview first — rows already captured by email alerts are detected and skipped, so nothing double-counts. Re-uploading the same file is safe.

\n${batches.rows.length ? `

Previous imports

${batches.rows.map((b: any) => `\n

${esc(b.file_name)}
${b.period_start ?? \"?\"} → ${b.period_end ?? \"?\"} · ${b.status}${b.row_count} rows

`).join(\"\")}

` : \"\"}`;\n return c.html(layout(\"Import\", scope, \"/import\", await pendingCount(scope), content));\n});\n\napp.post(\"/:scope/import/upload\", async (c) => {\n const scope = c.req.param(\"scope\");\n const base = `/${scope}`;\n const f = await c.req.parseBody();\n const file = f.file as File;\n if (!file?.arrayBuffer) return c.text(\"No file received\", 400);\n const bytes = new Uint8Array(await file.arrayBuffer());\n const hashBuf = await crypto.subtle.digest(\"SHA-256\", bytes);\n const fileHash = [...new Uint8Array(hashBuf)].map((b) => b.toString(16).padStart(2, \"0\")).join(\"\");\n\n const dup = await sqlite.execute({\n sql: \"SELECT created_at, row_count FROM import_batches WHERE file_hash=? AND status='committed'\",\n args: [fileHash],\n });\n if (dup.rows.length) {\n const d: any = dup.rows[0];\n return c.html(layout(\"Import\", scope, \"/import\", await pendingCount(scope),\n `

Import statement

This exact file was already imported on ${d.created_at} (${d.row_count} rows). Nothing to do.

Back

`));\n }\n\n let st;\n try {\n st = await parsePdf(bytes);\n } catch (err) {\n return c.html(layout(\"Import\", scope, \"/import\", await pendingCount(scope),\n `

Import statement

Could not parse this PDF: ${esc(String(err))}

Back

`));\n }\n if (!st.txns.length) {\n return c.html(layout(\"Import\", scope, \"/import\", await pendingCount(scope),\n `

Import statement

${esc(st.problems.join(\"; \") || \"No transactions found in this PDF.\")}

Back

`));\n }\n\n const lo = st.periodStart ?? st.txns[0].txnDate, hi = st.periodEnd ?? st.txns[st.txns.length - 1].txnDate;\n const existing = (await sqlite.execute({\n sql: \"SELECT id, amount_cents, txn_date, merchant, source, status FROM transactions WHERE txn_date>=date(?, '-4 days') AND txn_date<=date(?, '+4 days')\",\n args: [lo, hi],\n })).rows as any[];\n const claimed = new Set();\n const rows: any[] = [];\n for (const t of st.txns) {\n let dupOf: any = null;\n for (const e of existing) {\n if (claimed.has(e.id) || e.amount_cents !== t.amountCents) continue;\n if (e.source === \"statement_pdf\" ? e.txn_date !== t.txnDate : dayDiff(e.txn_date, t.txnDate) > 3) continue;\n if (e.source === \"statement_pdf\" || sameMerchant(e.merchant, t.merchant)) { dupOf = e; break; }\n }\n if (dupOf) claimed.add(dupOf.id);\n const cat = t.direction === \"deposit\" ? 11 : ((await suggestCategory(t.merchant, t.description)) ?? 12);\n rows.push({ ...t, catId: cat, dup: !!dupOf, dupSource: dupOf?.source ?? null });\n }\n const cur = await sqlite.execute({\n sql: `INSERT INTO import_batches(file_name, file_hash, period_start, period_end, row_count, rows_json)\n VALUES(?,?,?,?,?,?)\n ON CONFLICT(file_hash) DO UPDATE SET rows_json=excluded.rows_json, status='previewed' RETURNING id`,\n args: [file.name, fileHash, st.periodStart, st.periodEnd, rows.length, JSON.stringify(rows)],\n });\n const batchId = (cur.rows[0] as any).id;\n const cats = await sqlite.execute(\"SELECT * FROM categories WHERE is_archived=0 ORDER BY id\");\n const dups = rows.filter((r) => r.dup).length;\n const newSpend = rows.filter((r) => !r.dup && r.direction === \"withdrawal\").reduce((s, r) => s + r.amountCents, 0);\n\n const content = `

Preview: ${st.periodStart} → ${st.periodEnd}

${rows.length} rows · ${dups} already captured (pre-skipped) · S$ ${money(newSpend)} new spend

\n${st.problems.length ? `

⚠ ${esc(st.problems.join(\"; \"))}

` : `

✓ Parsed totals match the statement's own totals.

\n`;\n return c.html(layout(\"Preview\", scope, \"/import\", await pendingCount(scope), content));\n});\n\napp.post(\"/:scope/import/:id/commit\", async (c) => {\n const batchId = c.req.param(\"id\");\n const b = await sqlite.execute({ sql: \"SELECT rows_json FROM import_batches WHERE id=?\", args: [batchId] });\n if (!b.rows.length) return c.text(\"Batch not found\", 404);\n const rows = JSON.parse((b.rows[0] as any).rows_json);\n const f = await c.req.parseBody();\n let inserted = 0;\n const stmts: any[] = [];\n for (let i = 0; i < rows.length; i++) {\n if (f[`skip_${i}`]) continue;\n const r = rows[i];\n const dh = await dedupHash(2, r.txnDate, r.amountCents, r.description);\n stmts.push({\n sql: `INSERT OR IGNORE INTO transactions(amount_cents, direction, txn_date, merchant, description,\n category_id, account_id, source, status, dedup_hash)\n VALUES(?,?,?,?,?,?, 2, 'statement_pdf', 'confirmed', ?)`,\n args: [r.amountCents, r.direction, r.txnDate, r.merchant, r.description,\n Number(f[`cat_${i}`] ?? r.catId), dh],\n });\n inserted++;\n }\n if (stmts.length) await sqlite.batch(stmts);\n await sqlite.execute({\n sql: \"UPDATE import_batches SET status='committed', row_count=? WHERE id=?\",\n args: [inserted, batchId],\n });\n return c.redirect(`/${c.req.param(\"scope\")}/transactions`);\n});\n\nexport default oauthMiddleware(app.fetch);\n","sherwyn-spendings",[],[229],"5ac7ef2b-1bd3-40e6-9f77-c3306d667299","ingest-email.ts","55052e50-7e88-11f1-bc08-1607ee4eb77e",38,["D",1787504734298],12,"email","// Email entrypoint: Gmail forwards OCBC alerts here; each becomes a\n// pending-review transaction. Idempotent by Message-ID.\n// Non-OCBC mail (e.g. Gmail's forwarding-verification email) is stored in\n// email_alerts with parse_status='skipped' so its content can be inspected.\nimport { dedupHash, initDb, suggestCategory } from \"./db.ts\";\nimport { parseAlert } from \"./parser.ts\";\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\n\nconst RELEVANT = [\n \"paynow\", \"nets qr\", \"card\", \"purchase\", \"transaction\", \"payment\", \"transfer\",\n \"transaction alerts\", \"paylah\", // DBS\n];\n\n// OCBC service notices that contain no transaction — card activation, PIN,\n// wallet provisioning, statements ready, etc. Marked 'skipped', not 'failed',\n// so they never clutter the review queue.\nconst NON_TRANSACTIONAL = [\n /added to wallet/i, /digital wallet/i, /\\bPIN\\b.*(setup|reset|authenticate)/i,\n /here is a pin/i, /card (activation|activated|is on its way|delivery|replacement)/i,\n /e-?statement/i, /statement.*ready/i, /security (alert|advisory)/i,\n /password|access code|one-?time password|\\botp\\b/i,\n /profile (update|change)/i, /contact details/i, /welcome/i,\n /payee/i, // \"funds transfer payee added\" — setup notice, no money moved\n // --- DBS / POSB service notices (no money moved) ---\n /limit (increase|increased|change)/i, /activation status/i,\n /payment controls/i, /mobile wallets/i, /edocument/i,\n /instructions received/i, /auto-debit/i, /linkage/i,\n /contact details/i, /digibot/i, /digibank mobile/i,\n // incoming money — not spending, and usually the other half of a transfer\n // already captured on the OCBC side\n /received a transfer/i,\n];\n\nexport default async function (e: Email) {\n await initDb();\n const messageId = e.headers[\"message-id\"] ?? e.headers[\"Message-Id\"] ??\n `${e.from}|${e.subject}|${(e.text ?? e.html ?? \"\").slice(0, 80)}`;\n const seen = await sqlite.execute({\n sql: \"SELECT 1 FROM email_alerts WHERE message_id=?\", args: [messageId],\n });\n if (seen.rows.length) return console.log(\"skip: already seen\", messageId);\n\n const subject = e.subject ?? \"\";\n const body = e.text || e.html || \"\";\n // Covers direct delivery, Gmail filter-forwards (original sender kept),\n // and manual \"Fwd:\" (sender becomes the user, so fall back to body markers).\n const from = e.from.toLowerCase();\n const isBank = /ocbc\\.com|dbs\\.com/.test(from) || /ocbc|dbs bank|posb|paylah/i.test(body);\n\n const isNotice = NON_TRANSACTIONAL.some((rx) => rx.test(subject));\n if (!isBank || isNotice || !RELEVANT.some((k) => subject.toLowerCase().includes(k))) {\n await sqlite.execute({\n sql: `INSERT INTO email_alerts(message_id, received_at, subject, from_addr, raw_body, parse_status)\n VALUES(?,?,?,?,?, 'skipped')`,\n args: [messageId, new Date().toISOString(), subject, e.from, body.slice(0, 4000)],\n });\n return console.log(\"skipped non-alert email:\", e.from, subject);\n }\n\n try {\n const p = parseAlert(subject, body, e.headers[\"date\"] ?? new Date().toISOString());\n // Route to the right account by card/wallet last-4 when present; default FRANK (id 2).\n let accId = 2;\n if (p.cardLast4) {\n const acc = await sqlite.execute({\n sql: \"SELECT id FROM accounts WHERE last4=? AND is_active=1\", args: [p.cardLast4],\n });\n if (acc.rows.length) accId = (acc.rows[0] as any).id;\n }\n const cat = (await suggestCategory(p.merchant, p.description)) ?? 12; // Uncategorised\n const dh = await dedupHash(accId, p.txnDate, p.amountCents, p.reference || messageId);\n await sqlite.batch([\n {\n sql: `INSERT INTO email_alerts(message_id, received_at, subject, from_addr, raw_body, parse_status)\n VALUES(?,?,?,?,?, 'parsed')`,\n args: [messageId, new Date().toISOString(), subject, e.from, body.slice(0, 4000)],\n },\n {\n sql: `INSERT OR IGNORE INTO transactions(amount_cents, direction, txn_date, merchant,\n description, category_id, account_id, source, status, dedup_hash)\n VALUES(?, 'withdrawal', ?,?,?,?,?, 'email_alert', 'pending_review', ?)`,\n args: [p.amountCents, p.txnDate, p.merchant, p.description, cat, accId, dh],\n },\n ]);\n console.log(\"captured:\", p.txnDate, p.merchant, p.amountCents / 100);\n } catch (err) {\n await sqlite.execute({\n sql: `INSERT INTO email_alerts(message_id, received_at, subject, from_addr, raw_body,\n parse_status, failure_reason) VALUES(?,?,?,?,?, 'failed', ?)`,\n args: [messageId, new Date().toISOString(), subject, e.from, body.slice(0, 4000), String(err)],\n });\n console.error(\"parse failed:\", String(err));\n }\n}\n",[],[238],"7f7738d6-235d-4654-8f59-f554b47d2ea3","db.ts","3b1f9f8e-7e88-11f1-a0c4-1607ee4eb77e",44,["D",1787505510848],6,"// Schema, seed data and helpers. Amounts stored as integer cents (SGD).\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\n\n// Only this Val Town account may use the app (checked in main.ts).\nexport const OWNER_ID = \"23bb1bdf-86c6-4322-9537-f748ea9d33ae\"; // sherwynchan\n\nconst SCHEMA = [\n `CREATE TABLE IF NOT EXISTS categories (\n id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE,\n icon TEXT DEFAULT '', is_archived INTEGER DEFAULT 0)`,\n `CREATE TABLE IF NOT EXISTS accounts (\n id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE,\n type TEXT NOT NULL, last4 TEXT, bank TEXT, is_active INTEGER DEFAULT 1)`,\n `CREATE TABLE IF NOT EXISTS merchant_rules (\n id INTEGER PRIMARY KEY, pattern TEXT NOT NULL,\n category_id INTEGER, normalized_name TEXT)`,\n `CREATE TABLE IF NOT EXISTS transactions (\n id INTEGER PRIMARY KEY,\n amount_cents INTEGER NOT NULL,\n direction TEXT NOT NULL DEFAULT 'withdrawal',\n txn_date TEXT NOT NULL,\n merchant TEXT DEFAULT '',\n description TEXT DEFAULT '',\n category_id INTEGER,\n account_id INTEGER,\n source TEXT NOT NULL DEFAULT 'manual',\n status TEXT NOT NULL DEFAULT 'confirmed',\n dedup_hash TEXT UNIQUE,\n created_at TEXT DEFAULT (datetime('now')),\n updated_at TEXT DEFAULT (datetime('now')))`,\n `CREATE INDEX IF NOT EXISTS idx_txn_date ON transactions(txn_date)`,\n `CREATE TABLE IF NOT EXISTS import_batches (\n id INTEGER PRIMARY KEY,\n file_name TEXT, file_hash TEXT UNIQUE,\n period_start TEXT, period_end TEXT,\n row_count INTEGER DEFAULT 0,\n status TEXT DEFAULT 'previewed',\n rows_json TEXT,\n created_at TEXT DEFAULT (datetime('now')))`,\n `CREATE TABLE IF NOT EXISTS email_alerts (\n id INTEGER PRIMARY KEY,\n message_id TEXT UNIQUE NOT NULL,\n received_at TEXT, subject TEXT, from_addr TEXT, raw_body TEXT,\n parse_status TEXT DEFAULT 'parsed',\n failure_reason TEXT,\n created_at TEXT DEFAULT (datetime('now')))`,\n];\n\nconst CATEGORIES: [string, string][] = [\n [\"Food & Hawker\", \"🍜\"], [\"Groceries\", \"🛒\"], [\"Transport\", \"🚇\"],\n [\"Shopping\", \"🛍️\"], [\"Bills & Utilities\", \"🧾\"], [\"Subscriptions\", \"🔁\"],\n [\"Health\", \"💊\"], [\"Entertainment\", \"🎮\"], [\"Travel\", \"✈️\"],\n [\"Transfers & Others\", \"↔️\"], [\"Income & Rebates\", \"💰\"], [\"Uncategorised\", \"❓\"],\n];\n\n// name, type, last4, bank (\"OCBC\" | \"DBS\" | \"CASH\" — drives the account picker)\nconst ACCOUNTS: [string, string, string | null, string][] = [\n [\"Cash\", \"cash\", null, \"CASH\"],\n [\"OCBC FRANK Account\", \"debit_card\", \"2001\", \"OCBC\"],\n [\"Other\", \"other\", null, \"CASH\"],\n [\"DBS/POSB Card\", \"debit_card\", \"5946\", \"DBS\"],\n [\"DBS PayLah!\", \"other\", \"9706\", \"DBS\"],\n];\n\nconst RULES: [string, string][] = [\n [\"BUS/MRT\", \"Transport\"], [\"anywheel\", \"Transport\"], [\"Grab\", \"Transport\"],\n [\"McDonalds\", \"Food & Hawker\"], [\"CHICKEN RICE\", \"Food & Hawker\"],\n [\"BAN MIAN\", \"Food & Hawker\"], [\"MALA\", \"Food & Hawker\"],\n [\"WESTERN\", \"Food & Hawker\"], [\"MEE\", \"Food & Hawker\"],\n [\"THAI FO\", \"Food & Hawker\"], [\"FONG SENG\", \"Food & Hawker\"],\n [\"COLD STORAGE\", \"Groceries\"], [\"NTUC\", \"Groceries\"], [\"FairPrice\", \"Groceries\"],\n [\"Lazada\", \"Shopping\"], [\"Shopee\", \"Shopping\"], [\"Amazon\", \"Shopping\"],\n [\"Netflix\", \"Subscriptions\"], [\"Spotify\", \"Subscriptions\"],\n [\"MOOMOO\", \"Transfers & Others\"], [\"INTERACTIVE BR\", \"Transfers & Others\"],\n];\n\nexport async function initDb(): Promise {\n for (const s of SCHEMA) await sqlite.execute(s);\n // migration: accounts.bank (added 2026-08 for the account picker)\n try {\n await sqlite.execute(\"ALTER TABLE accounts ADD COLUMN bank TEXT\");\n } catch { /* column already exists */ }\n await sqlite.batch([\n { sql: \"UPDATE accounts SET bank='OCBC' WHERE bank IS NULL AND name LIKE 'OCBC%'\" },\n { sql: \"UPDATE accounts SET bank='DBS' WHERE bank IS NULL AND (name LIKE 'DBS%' OR name LIKE '%PayLah%')\" },\n { sql: \"UPDATE accounts SET bank='CASH' WHERE bank IS NULL\" },\n ]);\n const n = await sqlite.execute(\"SELECT COUNT(*) c FROM categories\");\n if ((n.rows[0] as any).c === 0) {\n const stmts = CATEGORIES.map((c) => ({\n sql: \"INSERT INTO categories(name, icon) VALUES(?,?)\", args: c as any,\n }));\n stmts.push(...ACCOUNTS.map((a) => ({\n sql: \"INSERT INTO accounts(name, type, last4, bank) VALUES(?,?,?,?)\", args: a as any,\n })));\n await sqlite.batch(stmts);\n const cats = await sqlite.execute(\"SELECT id, name FROM categories\");\n const byName = Object.fromEntries(cats.rows.map((r: any) => [r.name, r.id]));\n await sqlite.batch(RULES.map(([p, c]) => ({\n sql: \"INSERT INTO merchant_rules(pattern, category_id) VALUES(?,?)\",\n args: [p, byName[c]],\n })));\n }\n}\n\nexport async function suggestCategory(merchant: string, description = \"\"): Promise {\n const hay = `${merchant} ${description}`.toLowerCase();\n const rules = await sqlite.execute(\n \"SELECT pattern, category_id FROM merchant_rules ORDER BY LENGTH(pattern) DESC\");\n for (const r of rules.rows as any[]) {\n if (hay.includes(String(r.pattern).toLowerCase())) return r.category_id as number;\n }\n return null;\n}\n\nexport async function dedupHash(accountId: number, txnDate: string, amountCents: number, key: string): Promise {\n const raw = `${accountId}|${txnDate}|${amountCents}|${key}`;\n const buf = await crypto.subtle.digest(\"SHA-256\", new TextEncoder().encode(raw));\n return [...new Uint8Array(buf)].map((b) => b.toString(16).padStart(2, \"0\")).join(\"\");\n}\n",[],[248],"c124ca80-eb25-47a9-afb6-de52db3aba30","backfill.ts","fb249158-9f13-11f1-b3f3-1607ee4eb77e",30,["D",1787504359169],"// One-off utility: re-run the parser over stored alert emails that were\n// previously skipped/failed, and insert any real transactions found.\n// Safe to re-run — idempotent via dedup_hash.\nimport { dedupHash, suggestCategory } from \"./db.ts\";\nimport { parseAlert } from \"./parser.ts\";\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\n\nconst NON_TRANSACTIONAL = [\n /added to wallet/i, /digital wallet/i, /\\bPIN\\b.*(setup|reset|authenticate)/i,\n /here is a pin/i, /card (activation|activated|is on its way|delivery|replacement)/i,\n /e-?statement/i, /statement.*ready/i, /security (alert|advisory)/i,\n /password|access code|one-?time password|\\botp\\b/i,\n /profile (update|change)/i, /contact details/i, /welcome/i, /payee/i,\n];\nconst RELEVANT = [\"paynow\", \"nets qr\", \"card\", \"purchase\", \"transaction\", \"payment\", \"transfer\"];\n\nconst rows = (await sqlite.execute(\n `SELECT id, message_id, subject, raw_body FROM email_alerts\n WHERE parse_status IN ('skipped','failed') AND raw_body IS NOT NULL AND raw_body != ''`,\n)).rows as any[];\n\nconst report: any[] = [];\nfor (const r of rows) {\n const subject = String(r.subject ?? \"\");\n if (NON_TRANSACTIONAL.some((rx) => rx.test(subject))) continue;\n if (!RELEVANT.some((k) => subject.toLowerCase().includes(k))) continue;\n let p;\n try {\n p = parseAlert(subject, String(r.raw_body));\n } catch {\n continue;\n }\n const cat = (await suggestCategory(p.merchant, p.description)) ?? 12;\n const dh = await dedupHash(2, p.txnDate, p.amountCents, p.reference || String(r.message_id));\n const res = await sqlite.execute({\n sql: `INSERT OR IGNORE INTO transactions(amount_cents, direction, txn_date, merchant,\n description, category_id, account_id, source, status, dedup_hash)\n VALUES(?, 'withdrawal', ?,?,?,?, 2, 'email_alert', 'pending_review', ?)`,\n args: [p.amountCents, p.txnDate, p.merchant, p.description, cat, dh],\n });\n await sqlite.execute({\n sql: \"UPDATE email_alerts SET parse_status='parsed', failure_reason=NULL WHERE id=?\",\n args: [r.id],\n });\n report.push({ date: p.txnDate, amount: p.amountCents / 100, merchant: p.merchant, inserted: res.rowsAffected });\n}\nconsole.log(JSON.stringify(report, null, 1));\n",[],[257],["D",1787504862135],[],[18],["D",1783925871877],["D",1789524079207],{"_11":12,"_125":70},{"_11":12,"_120":121,"_69":70,"_30":68,"_122":123,"_124":24},{"_38":111,"_13":16,"_32":35,"_112":72,"_113":-5,"_114":-5,"_115":-5,"_116":-5,"_117":-5,"_118":14,"_119":12},"ctx",{"_53":-5,"_144":305,"_306":307,"_308":309,"_310":24,"_311":24,"_312":24,"_313":85,"_314":-5,"_315":-7,"_316":-7,"_317":26,"_318":24,"_319":24,"_320":-5,"_321":24,"_322":24,"_323":24,"_324":-5},"ENV",{"_276":277,"_278":279,"_280":16,"_281":282,"_283":284,"_285":286,"_287":288,"_289":290,"_291":292,"_293":294,"_295":296,"_297":298,"_299":300,"_301":302,"_303":304},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","fdb67791413f4386b4dbf18f354029a617f6160f","RENDER_GIT_BRANCH","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]

History · backfill-dbs.ts