[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_235":236,"_237":238},"routes/_app.x.$handle.$name",{"_98":97,"_31":43,"_107":88,"_26":108,"_13":14,"_109":-5,"_11":110,"_111":112,"_113":114,"_115":116,"_117":118,"_20":-5,"_119":-5,"_120":-5,"_121":24,"_122":123,"_124":57,"_125":-5,"_126":-5,"_127":128},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_41":42,"_31":43,"_36":44,"_45":-5,"_46":24,"_47":48,"_49":48,"_50":51,"_52":-5,"_18":53,"_54":55,"_56":57,"_58":-5,"_59":-5,"_60":61,"_62":63,"_64":65,"_66":67,"_68":69,"_70":-5,"_71":72,"_73":72,"_74":75,"_76":75,"_77":75,"_78":72,"_79":72,"_80":81,"_82":83,"_84":85,"_86":24},"version",30,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":-7,"_23":24,"_25":24,"_26":27,"_28":27},"file",{"_29":30,"_31":27,"_32":33,"_34":35,"_36":37,"_38":14,"_39":40,"_23":24},"previous","deleted",false,"deletedHere","path","backfill.ts","latestPath","fileId","fb249158-9f13-11f1-b3f3-1607ee4eb77e","name","fileType","script","content","// 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","createdAt",["D",1787504359169],"branchVersion","branchId","8dada7df-82cd-483c-b8c3-97f8f7061b3f","id","5882322f-5326-4d08-a671-0dfe32c6ecb1","spendings-tracker",["D",1783925871877],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","Personal spending tracker (SG) — email-alert capture + mobile UI","imageUrl","23bb1bdf-86c6-4322-9537-f748ea9d33ae","pinnedFileId","8a53d0a2-7e88-11f1-9f2e-1607ee4eb77e","pinPreview",true,"pinMigrated","screenshotVersion","likeCount",0,"orgId","019f5a3b-3cf5-77c9-ae50-24a4edd5903f","tursoName","5882322f53264d08a6710dfe32c6ecb1","tursoHostname","5882322f53264d08a6710dfe32c6ecb1-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789524079207],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_98":97,"_106":102},"org",{"_98":97,"_99":100,"_101":102,"_18":53,"_103":104,"_105":24},"branch",{"_13":87,"_31":88,"_41":40,"_89":42,"_90":-5,"_91":-5,"_92":-5,"_93":-5,"_94":-5,"_95":43,"_96":97},"isOwned",50,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","sherwynchan","handle","displayName","Sherwyn Chan","avatar","https://lh3.googleusercontent.com/a/ACg8ocLBaSEPUgLpRxKWEU-dXRA-hGCPXcSQNK_NaBdHdD-_sGPJQw=s96-c","tier","free","canRestrictVals","profileImageUrl","branchName","","adoptOrgId",{"_41":42,"_31":43,"_36":230,"_45":-5,"_46":24,"_47":48,"_49":48,"_50":51,"_52":-5,"_18":53,"_54":55,"_56":57,"_58":-5,"_59":-5,"_60":61,"_62":63,"_64":65,"_66":67,"_68":231,"_70":-5,"_71":72,"_73":72,"_74":75,"_76":75,"_77":75,"_78":72,"_79":72,"_80":232,"_82":233,"_84":234,"_86":24},"tree",[201,202,203,204,205,206,207],"files",[201,202,203,204,205,206,207],"counts",{"_191":61,"_192":185,"_193":61,"_194":195,"_196":197},"contents",[129,130,131,132,133,134,135],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_41":188,"_31":27,"_29":30,"_139":-5,"_39":40,"_38":14,"_141":57,"_36":189,"_13":61,"_32":33,"_34":35,"_23":24,"_145":-5,"_26":190},{"_41":180,"_31":181,"_29":182,"_139":-5,"_39":40,"_38":183,"_141":24,"_36":184,"_13":185,"_32":33,"_34":186,"_23":24,"_145":-5,"_26":187},{"_41":171,"_31":172,"_29":173,"_139":-5,"_39":40,"_38":174,"_141":24,"_36":175,"_13":176,"_32":177,"_34":178,"_23":24,"_145":-5,"_26":179},{"_41":162,"_31":163,"_29":55,"_139":-5,"_39":40,"_38":164,"_141":24,"_36":165,"_13":166,"_32":167,"_34":168,"_23":24,"_145":169,"_26":170},{"_41":154,"_31":155,"_29":156,"_139":-5,"_39":40,"_38":157,"_141":24,"_36":158,"_13":159,"_32":33,"_34":160,"_23":24,"_145":-5,"_26":161},{"_41":147,"_31":148,"_29":149,"_139":-5,"_39":40,"_38":150,"_141":57,"_36":151,"_13":61,"_32":33,"_34":152,"_23":24,"_145":-5,"_26":153},{"_41":136,"_31":137,"_29":138,"_139":-5,"_39":40,"_38":140,"_141":57,"_36":142,"_13":143,"_32":33,"_34":144,"_23":24,"_145":-5,"_26":146},"d3769e85-7977-424c-9386-1cede683597d","test.ts","9d3410ce-7e88-11f1-be9d-1607ee4eb77e","parentId",28,"latest",["D",1787504287071],4,"// Regression harness: replays REAL stored emails through the parser.\n// Run after any parser/ingester change. No writes — read-only check.\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 subject, raw_body FROM email_alerts WHERE raw_body IS NOT NULL AND raw_body != ''`,\n)).rows as any[];\n\nconst out: any[] = [];\nfor (const r of rows) {\n const subject = String(r.subject ?? \"\");\n const notice = NON_TRANSACTIONAL.some((rx) => rx.test(subject));\n const relevant = RELEVANT.some((k) => subject.toLowerCase().includes(k));\n if (notice || !relevant) {\n out.push({ subject, verdict: \"notice → skipped (correct)\" });\n continue;\n }\n try {\n const p = parseAlert(subject, String(r.raw_body));\n out.push({ subject, verdict: \"PARSED\", ...p });\n } catch (e) {\n out.push({ subject, verdict: \"FAILED\", error: String(e) });\n }\n}\nconsole.log(JSON.stringify(out, null, 1));\n","label",[137],"b6eb3e4b-55e3-4d7b-aa41-bb5ecd833678","stmt-parser.ts","92fa7310-7ed7-11f1-b345-1607ee4eb77e",14,["D",1783959977403],"// OCBC deposit-account statement PDF parser (unpdf/pdf.js text layout).\n// pdf.js emits columns in a scrambled-but-deterministic order:\n// \"01 JUN 6.50 2,203.2902 JUN DEBIT PURCHASE\" = txnDate amount balance+valueDate type\n// Direction (withdrawal vs deposit) is inferred from the running-balance delta,\n// and the whole parse is verified against the statement's own totals line.\n\nexport interface StmtTxn {\n txnDate: string; // ISO\n amountCents: number;\n direction: \"withdrawal\" | \"deposit\";\n txnType: string;\n merchant: string;\n description: string;\n}\n\nexport interface StmtParse {\n accountNo: string | null;\n periodStart: string | null;\n periodEnd: string | null;\n balanceBf: number | null; // cents\n balanceCf: number | null;\n statedWithdrawals: number | null;\n statedDeposits: number | null;\n txns: StmtTxn[];\n problems: string[]; // empty = totals verified\n}\n\nconst MONTHS: Record = {\n JAN: \"01\", FEB: \"02\", MAR: \"03\", APR: \"04\", MAY: \"05\", JUN: \"06\",\n JUL: \"07\", AUG: \"08\", SEP: \"09\", OCT: \"10\", NOV: \"11\", DEC: \"12\",\n};\n\nconst TXN_RE = /^(\\d{2}) ([A-Z]{3}) ([\\d,]+\\.\\d{2}) ([\\d,]+\\.\\d{2})(\\d{2} [A-Z]{3}) (.+)$/;\n\nconst NOISE = [\n /^OCBC Bank$/, /^65 Chulia/, /^Singapore \\d{6}$/, /^Oversea-Chinese/,\n /^Co\\. Reg/, /^Singapore dollar deposits/, /^for up to S\\$/, /^and separately insured/,\n /^Foreign currency/, /^Deposit Insurance Scheme/, /^\\d( \\d)+$/, /^STATEMENT OF ACCOUNT$/,\n /^Page \\d+ of \\d+$/, /^For enquiries/, /^our Customer Service/, /^OCBC .*Branch$/,\n /^Information$/, /^As part of our efforts/, /^statements will now be printed/,\n /^RNB/, /^Transaction$/, /^Date Date Description/, /Withdrawal Deposit Balance/,\n /^Value$/, /^OCBC FRANK ACCOUNT$/, /^Account No\\./, /\\d{4} TO \\d{1,2} [A-Z]{3} \\d{4}$/,\n /^SHERWYN/, /^\\d+[A-Z]? .*Road$/, /^#\\d{2}-\\d{2}$/, /^SINGAPORE \\d{6}$/,\n /^CHECK YOUR STATEMENT$/, /^Please check this statement/, /^we will take this statement/,\n /^UPDATING YOUR/, /^You may update/, /^the Change of address/, /^OCBC PROMOTION/,\n];\n\nconst cents = (s: string) => Math.round(parseFloat(s.replace(/,/g, \"\")) * 100);\n\nfunction merchantOf(txnType: string, lines: string[]): string {\n const t = txnType.toUpperCase();\n if (t.includes(\"PURCHASE\")) {\n for (let i = 0; i < lines.length; i++) {\n const m = lines[i].match(/^xx-(\\d{4}) (.+?)( \\d{4,})?$/);\n if (m) {\n let name = m[2].trim();\n let j = i + 1;\n while (j < lines.length && lines[j] !== \"S\" && !lines[j].startsWith(\"xx-\") && !/^[\\d/]+$/.test(lines[j])) {\n name += \" \" + lines[j];\n j++;\n }\n return name.replace(/ \\d{4,}$/, \"\").trim();\n }\n }\n }\n if (t.includes(\"NETS QR\")) {\n const i = lines.findIndex((l) => l.includes(\"NETS QR PURCHASE\"));\n if (i >= 0 && lines[i + 1]) return lines[i + 1].trim();\n }\n for (const l of lines) {\n const m = l.match(/^to (.+)$/);\n if (m) return m[1].trim();\n }\n if (t.includes(\"INTEREST\")) return \"OCBC Interest\";\n if (t.includes(\"REBATE\")) return \"OCBC Rebate\";\n return (lines[0] ?? txnType).trim();\n}\n\nexport function parseStatementText(pages: string[]): StmtParse {\n const text = pages.join(\"\\n\");\n const out: StmtParse = {\n accountNo: text.match(/Account No\\. (\\d+)/)?.[1] ?? null,\n periodStart: null, periodEnd: null,\n balanceBf: null, balanceCf: null,\n statedWithdrawals: null, statedDeposits: null,\n txns: [], problems: [],\n };\n\n const period = text.match(/(\\d{1,2}) ([A-Z]{3}) (\\d{4}) TO (\\d{1,2}) ([A-Z]{3}) (\\d{4})/);\n if (period) {\n out.periodStart = `${period[3]}-${MONTHS[period[2]]}-${period[1].padStart(2, \"0\")}`;\n out.periodEnd = `${period[6]}-${MONTHS[period[5]]}-${period[4].padStart(2, \"0\")}`;\n }\n const bf = text.match(/([\\d,]+\\.\\d{2})BALANCE B\\/F/);\n const cf = text.match(/([\\d,]+\\.\\d{2})BALANCE C\\/F/);\n out.balanceBf = bf ? cents(bf[1]) : null;\n out.balanceCf = cf ? cents(cf[1]) : null;\n\n // Totals appear as two concatenated numbers after the C/F block, e.g. \"1,111.151,119.26\"\n const totIdx = text.indexOf(\"Total Withdrawals/Deposits\");\n if (totIdx >= 0) {\n const tm = text.slice(totIdx, totIdx + 300).match(/^([\\d,]+?\\.\\d{2})([\\d,]+\\.\\d{2})$/m);\n if (tm) {\n // order is ambiguous in the extracted layout — assign via the balance equation\n const a = cents(tm[1]), b = cents(tm[2]);\n if (out.balanceBf !== null && out.balanceCf !== null) {\n if (out.balanceBf - a + b === out.balanceCf) {\n out.statedWithdrawals = a; out.statedDeposits = b;\n } else {\n out.statedWithdrawals = b; out.statedDeposits = a;\n }\n }\n }\n }\n\n if (out.balanceBf === null) {\n out.problems.push(\"Opening balance not found — is this an OCBC deposit-account statement?\");\n return out;\n }\n\n const year = out.periodEnd ? out.periodEnd.slice(0, 4) : String(new Date().getFullYear());\n let prev = out.balanceBf;\n let current: (StmtTxn & { _lines: string[] }) | null = null;\n let done = false;\n\n for (let raw of text.split(\"\\n\")) {\n const line = raw.trim();\n if (!line || done) continue;\n if (line.includes(\"BALANCE C/F\") || line.startsWith(\"Total Withdrawals/Deposits\")) {\n done = true;\n continue;\n }\n const m = line.match(TXN_RE);\n if (m) {\n const amount = cents(m[3]), balance = cents(m[4]);\n let direction: \"withdrawal\" | \"deposit\";\n if (prev - amount === balance) direction = \"withdrawal\";\n else if (prev + amount === balance) direction = \"deposit\";\n else {\n out.problems.push(`Cannot classify row (balance jump): ${line}`);\n continue;\n }\n let txnDate = `${year}-${MONTHS[m[2]]}-${m[1]}`;\n if (out.periodStart && txnDate < out.periodStart) {\n txnDate = `${out.periodStart.slice(0, 4)}-${MONTHS[m[2]]}-${m[1]}`;\n }\n current = {\n txnDate, amountCents: amount, direction,\n txnType: m[6].trim(), merchant: \"\", description: \"\", _lines: [],\n };\n out.txns.push(current);\n prev = balance;\n continue;\n }\n if (current && !NOISE.some((rx) => rx.test(line)) && !line.includes(\"BALANCE B/F\")) {\n current._lines.push(line);\n }\n }\n\n for (const t of out.txns as (StmtTxn & { _lines: string[] })[]) {\n t.merchant = merchantOf(t.txnType, t._lines);\n t.description = [t.txnType, ...t._lines].join(\" | \").slice(0, 200);\n delete (t as any)._lines;\n }\n\n // verify against the statement's own totals\n const w = out.txns.filter((t) => t.direction === \"withdrawal\").reduce((s, t) => s + t.amountCents, 0);\n const d = out.txns.filter((t) => t.direction === \"deposit\").reduce((s, t) => s + t.amountCents, 0);\n if (out.statedWithdrawals !== null && w !== out.statedWithdrawals) {\n out.problems.push(`Parsed withdrawals ${w / 100} != statement total ${out.statedWithdrawals / 100}`);\n }\n if (out.statedDeposits !== null && d !== out.statedDeposits) {\n out.problems.push(`Parsed deposits ${d / 100} != statement total ${out.statedDeposits / 100}`);\n }\n if (out.balanceCf !== null && out.balanceBf - w + d !== out.balanceCf) {\n out.problems.push(\"Balance movement does not reconcile\");\n }\n return out;\n}\n\nexport async function parsePdf(bytes: Uint8Array): Promise {\n const { extractText, getDocumentProxy } = await import(\"npm:unpdf\");\n const pdf = await getDocumentProxy(bytes);\n const { text } = await extractText(pdf, { mergePages: false });\n return parseStatementText(text as string[]);\n}\n",[148],"20c12134-abf4-4923-802b-035345838cac","parser.ts","4856473e-7e88-11f1-af71-1607ee4eb77e",29,["D",1787504330267],6,"// Parser for OCBC transaction alert emails (PayNow, NETS QR, card).\n// Port of the Python parser validated against real alerts (July 2026).\n\nexport interface ParsedAlert {\n amountCents: number;\n txnDate: string; // ISO\n merchant: string;\n description: string;\n reference: string;\n cardLast4?: string; // present for card transaction alerts — used for account routing\n}\n\nconst MONTHS: Record = {\n jan: \"01\", feb: \"02\", mar: \"03\", apr: \"04\", may: \"05\", jun: \"06\",\n jul: \"07\", aug: \"08\", sep: \"09\", oct: \"10\", nov: \"11\", dec: \"12\",\n};\n\nexport function htmlToText(html: string): string {\n let t = html.replace(/<\\/td>\\s*]*>/gi, \" \");\n t = t.replace(/<\\/tr>||<\\/p>|<\\/table>/gi, \"\\n\");\n t = t.replace(/<[^>]+>/g, \"\");\n return t\n .replace(/ /g, \" \").replace(/&/g, \"&\").replace(/>/g, \">\")\n .replace(/</g, \"<\").replace(/'/g, \"'\").replace(/"/g, '\"');\n}\n\nexport function parseAlert(subject: string, body: string): ParsedAlert {\n if (body.includes(\"<\") && body.includes(\">\")) body = htmlToText(body);\n body = body.replace(/\\r\\n/g, \"\\n\");\n\n // Card Transaction Alert format (noreply@notify.ocbc.com):\n // \"SGD5.62 was charged at 14:26 on 13-Jul-26 to your card (-8824) at NTUC FairPrice App Paymen.\"\n const card = body.match(\n /SGD\\s?([\\d,]+\\.?\\d{0,2})\\s+was charged at\\s+(\\d{1,2}:\\d{2})\\s+on\\s+(\\d{1,2})-([A-Za-z]{3})-(\\d{2,4})\\s+to\\s+your card\\s+\$(-?\\d{3,4})\$\\s+at\\s+([\\s\\S]+?)\\./,\n );\n if (card) {\n const [, amt, time, dd, mon, yy, last4, merchRaw] = card;\n const year = yy.length === 2 ? `20${yy}` : yy;\n return {\n amountCents: Math.round(parseFloat(amt.replace(/,/g, \"\")) * 100),\n txnDate: `${year}-${MONTHS[mon.toLowerCase()]}-${dd.padStart(2, \"0\")}`,\n merchant: merchRaw.replace(/\\s+/g, \" \").trim(),\n description: `Card ${last4.replace(\"-\", \"•\")} · ${time}`,\n reference: \"\",\n cardLast4: last4.replace(\"-\", \"\"),\n };\n }\n\n // Google Pay transfer format:\n // \"SGD5.70 was sent to KOX LIX OX using his/her mobile number (+XXXXXX0807)\n // from the account you linked to Google Pay.\" Date of transfer: 06-Aug-2026\n const gpay = body.match(\n /SGD\\s?([\\d,]+\\.?\\d{0,2})\\s+was sent to\\s+([\\s\\S]+?)\\s+using[\\s\\S]*?Date of transfer:\\s*(\\d{1,2})-([A-Za-z]{3})-(\\d{2,4})/i,\n );\n if (gpay) {\n const [, amt, payee, dd, mon, yy] = gpay;\n const time = body.match(/Time of transfer:\\s*([\\d.:]+\\s?[AP]M)/i)?.[1] ?? \"\";\n return {\n amountCents: Math.round(parseFloat(amt.replace(/,/g, \"\")) * 100),\n txnDate: `${yy.length === 2 ? `20${yy}` : yy}-${MONTHS[mon.toLowerCase()]}-${dd.padStart(2, \"0\")}`,\n merchant: payee.replace(/\\s+/g, \" \").trim(),\n description: [\"Google Pay\", time].filter(Boolean).join(\" · \"),\n reference: \"\",\n };\n }\n\n const fields: Record = {};\n for (const m of body.matchAll(\n /^\\s*(Date(?: of Transfer)?|Time(?: of Transfer)?|Amount|To(?: account)?|Description|Reference number|From your account)\\s*:\\s*(.+?)\\s*$/gm,\n )) fields[m[1]] = m[2].trim();\n // Funds-transfer alerts label the same fields differently\n fields[\"Date\"] ??= fields[\"Date of Transfer\"];\n fields[\"Time\"] ??= fields[\"Time of Transfer\"];\n\n const amtSrc = fields[\"Amount\"] ?? body;\n const am = amtSrc.match(/(?:SGD|S\\$)\\s*([\\d,]+\\.?\\d{0,2})/);\n if (!am) throw new Error(\"no amount found\");\n const amountCents = Math.round(parseFloat(am[1].replace(/,/g, \"\")) * 100);\n\n const dm = (fields[\"Date\"] ?? \"\").match(/^(\\d{1,2}) ([A-Za-z]{3}) (\\d{4})$/);\n if (!dm) throw new Error(\"no date found\");\n const txnDate = `${dm[3]}-${MONTHS[dm[2].toLowerCase()]}-${dm[1].padStart(2, \"0\")}`;\n\n const sub = subject.toLowerCase();\n let merchant = \"\", kind = \"\";\n if (sub.includes(\"paynow\")) {\n const pm = body.match(/has been made to\\s+([\\s\\S]+?)\\s+using\\s+(?:his\\/her|their)/);\n merchant = pm ? pm[1].replace(/\\s+/g, \" \").trim() : \"\";\n kind = \"PayNow\";\n } else if (sub.includes(\"nets\")) {\n merchant = fields[\"To\"] ?? \"\";\n kind = \"NETS QR\";\n } else if (sub.includes(\"transfer\")) {\n // \"We have processed your funds transfer request\" — internet-banking transfer\n merchant = fields[\"To account\"] ?? fields[\"To\"] ?? \"\";\n kind = \"Funds transfer\";\n } else {\n const cm = body.match(/transaction (?:was made )?at\\s+(.+?)[.\\n]/);\n merchant = cm ? cm[1].trim() : (fields[\"To\"] ?? \"\");\n kind = subject.trim() || \"Card\";\n }\n if (!merchant) throw new Error(\"no payee/merchant found\");\n\n const reference = fields[\"Reference number\"] ?? \"\";\n const description = [kind, fields[\"Time\"] ?? \"\", reference ? `ref ${reference}` : \"\"]\n .filter(Boolean).join(\" · \");\n return { amountCents, txnDate, merchant, description, reference };\n}\n",[155],"14e552da-e342-46ec-8fe2-61b6b3b7fd7b","main.ts",17,["D",1783960152819],5,"http","// Spending Tracker — mobile web UI. Login-gated to the owner's Val Town account.\nimport { Hono } from \"npm:hono\";\nimport { getOAuthUserData, oauthMiddleware } from \"https://esm.town/v/std/oauth/middleware.ts\";\nimport { parseVal } from \"https://esm.town/v/std/utils/index.ts\";\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { initDb, OWNER_ID, suggestCategory } from \"./db.ts\";\n\nconst app = new Hono();\napp.onError((err) => Promise.reject(err));\n\nconst money = (c: number) => (c / 100).toLocaleString(\"en-SG\", { minimumFractionDigits: 2 });\nconst esc = (s: unknown) =>\n String(s ?? \"\").replace(/&/g, \"&\").replace(//g, \">\").replace(/\"/g, \""\");\nconst today = () => new Date(Date.now() + 8 * 3600e3).toISOString().slice(0, 10); // SGT\n\nconst CSS = `\n:root{--bg:#f4f6f8;--card:#fff;--ink:#17242b;--muted:#6b7a85;--accent:#1f4e5f;--accent2:#2e7d6b;--red:#c0392b;--green:#1e8e5a}\n*{box-sizing:border-box}body{margin:0;font-family:-apple-system,\"Segoe UI\",Roboto,sans-serif;background:var(--bg);color:var(--ink);font-size:16px}\nmain{max-width:560px;margin:0 auto;padding:16px 16px 96px}h1{font-size:1.3rem;margin:8px 0 14px}\nh2{font-size:.9rem;color:var(--muted);margin:0 0 10px;text-transform:uppercase;letter-spacing:.04em}\na{color:var(--accent);text-decoration:none}small{color:var(--muted)}\n.hero{background:linear-gradient(135deg,var(--accent),var(--accent2));color:#fff;border-radius:14px;padding:20px;text-align:center;margin-bottom:14px}\n.hero .big{font-size:2.1rem;font-weight:700;margin:4px 0}.hero .label{opacity:.85;font-size:.85rem}\n.delta{font-size:.85rem}.card{background:var(--card);border-radius:14px;padding:14px 16px;margin-bottom:14px;box-shadow:0 1px 3px rgba(0,0,0,.06)}\n.empty{color:var(--muted);padding:10px 0}\n.row{display:flex;justify-content:space-between;align-items:center;padding:6px 0}\n.bar{height:6px;background:#e8edf0;border-radius:3px;margin-top:4px}.bar div{height:100%;background:var(--accent2);border-radius:3px}\n.txn{display:flex;gap:10px;align-items:center;padding:9px 0;color:inherit;border-bottom:1px solid #eef2f4}.txn:last-child{border-bottom:0}\n.icon{font-size:1.3rem;width:32px;text-align:center;flex:none}.txnmain{flex:1;min-width:0}\n.txnmain div{white-space:nowrap;overflow:hidden;text-overflow:ellipsis}.txn b{flex:none}.credit{color:var(--green)}\n.bottomnav{position:fixed;bottom:0;left:0;right:0;display:flex;justify-content:space-around;align-items:center;background:var(--card);border-top:1px solid #e3e9ec;padding:6px 0 max(6px,env(safe-area-inset-bottom))}\n.bottomnav a{display:flex;flex-direction:column;align-items:center;font-size:1.15rem;color:var(--muted);min-width:60px;position:relative}\n.bottomnav a span{font-size:.65rem}.bottomnav a.active{color:var(--accent)}\n.addbtn{background:var(--accent);color:#fff!important;border-radius:50%;width:52px;height:52px;justify-content:center;font-size:1.8rem!important;margin-top:-22px;box-shadow:0 3px 8px rgba(0,0,0,.25)}\n.badge{position:absolute;top:-4px;right:6px;background:var(--red);color:#fff;font-style:normal;font-size:.65rem;border-radius:999px;padding:1px 6px;font-weight:700}\nlabel{display:block;margin-bottom:12px;font-size:.9rem;color:var(--muted)}\ninput,select,button{font:inherit}\ninput[type=text],input[type=number],input[type=date],input[type=search],select{width:100%;padding:12px;border:1px solid #d6dee3;border-radius:10px;margin-top:4px;background:#fff;color:var(--ink)}\ninput[name=amount]{font-size:1.6rem;font-weight:700}\nbutton.primary{width:100%;padding:14px;background:var(--accent);color:#fff;border:0;border-radius:12px;font-weight:600;font-size:1.05rem}\nbutton.danger{width:100%;padding:12px;margin-top:10px;background:#fff;color:var(--red);border:1px solid var(--red);border-radius:12px}\n.chip{border:1px solid var(--accent);color:var(--accent);background:#fff;border-radius:999px;padding:7px 14px;font-size:.85rem}\n.quick{display:flex;gap:8px;flex-wrap:wrap;margin-bottom:14px}\n.filterrow{display:flex;gap:8px;margin-bottom:8px}\n.importrow{display:flex;gap:8px;align-items:center;padding:8px 0;border-bottom:1px solid #eef2f4}\n.importrow select{width:150px;flex:none;padding:8px}\n.skipbox{flex:none;font-size:.7rem;color:var(--muted);display:flex;flex-direction:column;align-items:center;margin:0}\n.alert{background:#fdecea;color:var(--red);border-radius:10px;padding:10px 12px;margin:8px 0;font-size:.9rem}\npre.raw{white-space:pre-wrap;font-size:.7rem;background:#f6f8f9;padding:8px;border-radius:8px}\n.checkline{display:flex;gap:8px;align-items:center;color:var(--ink)}.checkline input{width:auto}\n.monthnav{display:flex;align-items:center;justify-content:space-between}.monthnav a,.monthnav s{font-size:1.6rem;padding:0 14px;text-decoration:none}.monthnav s{opacity:.2}\nfooter{text-align:center;padding:10px;font-size:.7rem}`;\n\nasync function pendingCount(): Promise {\n const r = await sqlite.execute(\n \"SELECT (SELECT COUNT(*) FROM transactions WHERE status='pending_review') + (SELECT COUNT(*) FROM email_alerts WHERE parse_status='failed') c\");\n return Number((r.rows[0] as any).c);\n}\n\nfunction layout(title: string, path: string, badge: number, content: string): string {\n const nav = (href: string, icon: string, label: string, b = 0) =>\n `${icon}${label}${b ? `${b}` : \"\"}`;\n return `\n\n${esc(title)}\n\n

${content}

\n\n\n`;\n}\n\n// --- auth gate: every route below 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// ---------- Dashboard ----------\napp.get(\"/\", async (c) => {\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 pStart = `${pm}-01`, pEnd = start;\n const W = \"direction='withdrawal' AND status='confirmed' AND txn_date>=? AND 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\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\", \"/\", await pendingCount(), content));\n});\n\n// ---------- Add / edit ----------\nasync function form(t: any | null): Promise {\n const cats = await sqlite.execute(\"SELECT * FROM categories WHERE is_archived=0 ORDER BY id\");\n const accs = await sqlite.execute(\"SELECT * FROM accounts WHERE is_active=1 ORDER BY id\");\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(\"/add\", async (c) => c.html(layout(\"Add\", \"/add\", await pendingCount(), await form(null))));\n\napp.post(\"/add\", async (c) => {\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(\"/\");\n});\n\napp.get(\"/transactions/:id/edit\", async (c) => {\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\", \"\", await pendingCount(), await form(r.rows[0])));\n});\n\napp.post(\"/transactions/:id/edit\", async (c) => {\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(\"/transactions\");\n});\n\napp.post(\"/transactions/:id/delete\", async (c) => {\n await sqlite.execute({ sql: \"DELETE FROM transactions WHERE id=?\", args: [c.req.param(\"id\")] });\n return c.redirect(\"/transactions\");\n});\n\n// ---------- Review queue ----------\napp.get(\"/review\", async (c) => {\n const cats = await sqlite.execute(\"SELECT * FROM categories WHERE is_archived=0 ORDER BY id\");\n const rows = await sqlite.execute(\n \"SELECT * FROM transactions WHERE status='pending_review' ORDER BY txn_date DESC, id DESC\");\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\", \"/review\", await pendingCount(), content));\n});\n\napp.post(\"/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(\"/review\");\n});\n\napp.post(\"/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(\"/review\");\n});\n\n// ---------- History ----------\napp.get(\"/transactions\", async (c) => {\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(\"(merchant LIKE ? OR description LIKE ?)\"); args.push(`%${q}%`, `%${q}%`); }\n if (cat) { where.push(\"category_id=?\"); args.push(Number(cat)); }\n if (start) { where.push(\"txn_date>=?\"); args.push(start); }\n if (end) { where.push(\"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 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 \")} ORDER BY txn_date DESC, t.id DESC LIMIT 500`, 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\", \"/transactions\", await pendingCount(), content));\n});\n\n// ---------- Statement import (upload → preview → commit) ----------\nimport { parsePdf } from \"./stmt-parser.ts\";\nimport { dedupHash } from \"./db.ts\";\n\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(\"/import\", async (c) => {\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\", \"/import\", await pendingCount(), content));\n});\n\napp.post(\"/import/upload\", async (c) => {\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\", \"/import\", await pendingCount(),\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\", \"/import\", await pendingCount(),\n `

Import statement

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

Back

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

Import statement

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

Back

`));\n }\n\n // Reconcile: match against ALL existing transactions (email-captured, manual, prior imports)\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\", \"/import\", await pendingCount(), content));\n});\n\napp.post(\"/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(\"/transactions\");\n});\n\napp.get(\"/source\", (c) => c.redirect(parseVal().links.self.val));\n\nexport default oauthMiddleware(app.fetch);\n","sherwyn-spendings",[163],"dd3d283f-6f83-4687-9bcc-2fff5b65a7c4","ingest-email.ts","55052e50-7e88-11f1-bc08-1607ee4eb77e",27,["D",1787504271533],7,"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 = [\"paynow\", \"nets qr\", \"card\", \"purchase\", \"transaction\", \"payment\", \"transfer\"];\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];\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 isOcbc = e.from.toLowerCase().includes(\"ocbc.com\") ||\n body.includes(\"OCBC FRANK Account\") || /ocbc/i.test(body);\n\n const isNotice = NON_TRANSACTIONAL.some((rx) => rx.test(subject));\n if (!isOcbc || 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);\n // Route to the right account by card 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, parse_status)\n VALUES(?,?,?,?, 'parsed')`,\n args: [messageId, new Date().toISOString(), subject, e.from],\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",[172],"74799253-2255-4b0a-8c7a-3feb4d72abc6","db.ts","3b1f9f8e-7e88-11f1-a0c4-1607ee4eb77e",15,["D",1783960110002],1,"// 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, 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\nconst ACCOUNTS: [string, string, string | null][] = [\n [\"Cash\", \"cash\", null],\n [\"OCBC FRANK Account\", \"debit_card\", \"2001\"],\n [\"Other\", \"other\", null],\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 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) 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",[181],"c124ca80-eb25-47a9-afb6-de52db3aba30",["D",1787504359169],[27],"pulls","branches","forks","envVars",{"_198":61,"_200":61},"connections",{"_198":61,"_199":61},"count","unfilledCount","emptyCount",{"_41":188,"_31":27,"_29":30,"_139":-5,"_39":40,"_38":14,"_141":57,"_36":227,"_13":61,"_32":33,"_34":35,"_23":24,"_145":-5,"_209":228,"_26":229},{"_41":180,"_31":181,"_29":182,"_139":-5,"_39":40,"_38":183,"_141":24,"_36":224,"_13":185,"_32":33,"_34":186,"_23":24,"_145":-5,"_209":225,"_26":226},{"_41":171,"_31":172,"_29":173,"_139":-5,"_39":40,"_38":174,"_141":24,"_36":221,"_13":176,"_32":177,"_34":178,"_23":24,"_145":-5,"_209":222,"_26":223},{"_41":162,"_31":163,"_29":55,"_139":-5,"_39":40,"_38":164,"_141":24,"_36":218,"_13":166,"_32":167,"_34":168,"_23":24,"_145":169,"_209":219,"_26":220},{"_41":154,"_31":155,"_29":156,"_139":-5,"_39":40,"_38":157,"_141":24,"_36":215,"_13":159,"_32":33,"_34":160,"_23":24,"_145":-5,"_209":216,"_26":217},{"_41":147,"_31":148,"_29":149,"_139":-5,"_39":40,"_38":150,"_141":57,"_36":212,"_13":61,"_32":33,"_34":152,"_23":24,"_145":-5,"_209":213,"_26":214},{"_41":136,"_31":137,"_29":138,"_139":-5,"_39":40,"_38":140,"_141":57,"_36":208,"_13":143,"_32":33,"_34":144,"_23":24,"_145":-5,"_209":210,"_26":211},["D",1787504287071],"children",[],[137],["D",1783959977403],[],[148],["D",1787504330267],[],[155],["D",1783960152819],[],[163],["D",1787504271533],[],[172],["D",1783960110002],[],[181],["D",1787504359169],[],[27],["D",1783925871877],["D",1789524079207],{"_98":97,"_106":102},{"_98":97,"_99":100,"_101":102,"_18":53,"_103":104,"_105":24},{"_13":87,"_31":88,"_41":40,"_89":42,"_90":-5,"_91":-5,"_92":-5,"_93":-5,"_94":-5,"_95":43,"_96":97},"ctx",{"_80":-5,"_126":268,"_269":270,"_271":272,"_273":24,"_274":24,"_275":24,"_276":57,"_277":-5,"_278":-7,"_279":-7,"_280":61,"_281":24,"_282":24,"_283":-5,"_284":24,"_285":24,"_286":24,"_287":-5},"ENV",{"_239":240,"_241":242,"_243":88,"_244":245,"_246":247,"_248":249,"_250":251,"_252":253,"_254":255,"_256":257,"_258":259,"_260":261,"_262":263,"_264":265,"_266":267},"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"]

Diff · v30