[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_462":463,"_464":465},"routes/_app.x.$handle.$name",{"_11":12,"_13":14,"_15":16,"_17":18,"_38":-7,"_128":-5,"_19":129,"_130":131,"_132":133,"_134":135,"_136":137,"_21":138,"_139":-5,"_140":-5,"_141":24,"_142":143,"_144":145,"_146":-5,"_147":-5,"_148":149},"routes/_app.x.$handle.$name.branch.$branch.history_.$",{"_11":12,"_13":14,"_15":16,"_17":18,"_19":20,"_21":22,"_23":24,"_25":26,"_27":28,"_29":24,"_30":-5},"handle","kevinvu184","name","up","branchName","main","path","docs/adr/0007-webhook-ingestion.md","project",{"_32":77,"_13":14,"_36":78,"_66":-5,"_79":24,"_80":81,"_82":81,"_83":84,"_85":-5,"_30":73,"_86":87,"_88":24,"_89":-5,"_90":-5,"_91":26,"_40":41,"_92":93,"_94":95,"_96":97,"_98":-5,"_99":100,"_101":100,"_102":103,"_104":103,"_105":103,"_106":100,"_107":100,"_53":108,"_109":110,"_111":112,"_113":24},"file",{"_68":69,"_13":70,"_71":21,"_72":24,"_76":-5},"isDirectory",false,"page",0,"history",[31],"hasNext","userId",{"_32":33,"_34":35,"_36":37,"_38":39,"_40":41,"_42":-5,"_43":44,"_45":-5,"_46":-5,"_47":-5,"_48":-5,"_49":50,"_51":52,"_53":54,"_21":55,"_56":-5,"_57":58,"_59":-5,"_60":-5,"_61":62},"id","4a55e496-b264-4a10-b753-ab3b8661b348","branchId","925807de-bb5e-437a-98a0-3189777f4eba","createdAt",["D",1787576457509],"version",1,"orgId","d858092b-54df-4060-a8ab-7ffc4c08f3f7","parentId","incomingCommitId","f5e9f131-5f29-45b7-bacc-161e64178448","aiChatMessageId","aiChatThreadId","aiChatThreadUserId","aiChatThreadTitle","source","mcp","clientLabel","Claude","user",{"_32":73,"_40":41,"_11":12,"_74":75},{"_68":69,"_13":70,"_71":21,"_72":24},"pull","merge",{"_34":64,"_13":16,"_38":65,"_66":67},"revert","multiple","commits",[63],{"_32":33,"_34":35,"_36":37,"_38":39,"_40":41,"_42":-5,"_43":44,"_45":-5,"_46":-5,"_47":-5,"_48":-5,"_49":50,"_51":52,"_53":54,"_21":55,"_56":-5,"_57":58,"_59":-5},"5eb027d0-57aa-48d6-b067-bd183a34c1c6",220,"deletedAt",["D",1787576587283],"fileId","01a033dc-1a01-70c1-86c3-a6c17308c5df","0007-webhook-ingestion.md","fileType","deleted","b8f42ed3-3d1f-4d7c-97d6-f4511ceba06b","avatar","https://imagedelivery.net/iHX6Ovru0O7AjmyT5yZRoA/4969e9a6-d660-4432-bcf5-66fbbc5a0e01/square512?exp=1789588799&sig=8f9ae13a5b2b3568c92cbc06855822d57748cb2952e89985c2aecbaa534ed58e","deletedAtVersion","aa7140bf-d3a5-4d82-8736-686396ccc7fa",["D",1787576457509],"legacyEnvVars","privacy","public","httpPrivacy","description","Up Bank mirror: bronze/silver/gold transaction pipeline","imageUrl","pinnedFileId","01a033dc-1a01-70c1-86c3-c9a7743829d8","pinPreview","pinMigrated","screenshotVersion","likeCount","tursoName","aa7140bfd3a54d828736686396ccc7fa","tursoHostname","aa7140bfd3a54d828736686396ccc7fa-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789571433983],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_11":12,"_127":75},"org",{"_11":12,"_123":-5,"_74":75,"_30":73,"_124":125,"_126":24},"branch",{"_38":114,"_13":16,"_32":35,"_115":77,"_116":-5,"_117":-5,"_118":-5,"_119":-5,"_120":-5,"_121":14,"_122":12},"isOwned",49,"projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","tier","free","canRestrictVals","profileImageUrl","adoptOrgId",{"_32":77,"_13":14,"_36":457,"_66":-5,"_79":24,"_80":81,"_82":81,"_83":84,"_85":-5,"_30":73,"_86":87,"_88":24,"_89":-5,"_90":-5,"_91":26,"_40":41,"_92":93,"_94":95,"_96":458,"_98":-5,"_99":100,"_101":100,"_102":103,"_104":103,"_105":103,"_106":100,"_107":100,"_53":459,"_109":460,"_111":461,"_113":24},"tree",[172,178,194,199,202,206],"files",[172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206],"counts",{"_161":26,"_162":39,"_163":26,"_164":165,"_166":167},"contents",[],{"_32":150,"_13":70,"_68":69,"_42":151,"_34":35,"_152":39,"_153":145,"_36":154,"_38":26,"_71":21,"_155":156,"_72":24,"_157":-5,"_17":158},"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles",true,"grants","orgs","customDomains",{},"5fd8b065-86ee-4d2b-a05f-f33a52251bea","01a033dc-1a01-70c1-86c3-be39892a8804","branchVersion","latest",["D",1787576457509],"content","# ADR 0007. Webhook ingestion into bronze\n\nStatus: accepted. Implemented 2026-08-24 in kevinvu184/up. Complements ADR 0001 (the poll becomes the safety net) and closes a gap in ADR 0003's silver contract.\n\n## Context\n\nPolling gave bronze a daily freshness bound and, more seriously, a blind spot: polling re-observes rows that exist but cannot observe absence, so a HELD transaction deleted upstream (a returned deposit, or Up's rare settle-as-delete-plus-create sequence) would linger in silver as a ghost. Up's webhook API delivers TRANSACTION_CREATED, TRANSACTION_SETTLED and TRANSACTION_DELETED events with retry on non-200, a shared-secret HMAC signature, and a 30 second response deadline. The facts were verified against Up's OpenAPI spec and developer.up.com.au/llms.txt before building.\n\n## Decisions\n\n### An HTTP composition root with no lock and no cursor\n\n`bronze.webhook.ts` is the fourth entry point and follows the established shape minus the machinery it does not need. It has no lock, because concurrent deliveries only perform atomic single-row writes keyed by transaction id, and no progress key, because a webhook has no position. It records `bronze_webhook_last_event` per delivery and `bronze_webhook_last_error` on failure, the same key shapes as the cron jobs. It runs the migration runner first, because after a deploy the webhook may be the first entry point to fire.\n\n### The signature is the authentication\n\nThe endpoint is public by necessity, so every request is verified before anything is parsed: SHA-256 HMAC of the raw body against the `X-Up-Authenticity-Signature` header (hex, confirmed by Up's own examples), compared constant-time, with a 401 and no side effects on mismatch. The secret lives in `UP_WEBHOOK_SECRET`, captured once at webhook creation.\n\n### Fetch current state, never trust the payload\n\nEvents carry only a transaction id, so the handler fetches the transaction from the API through the existing client (which gained `fetchTransaction`, reusing the same backoff and deadline discipline) and writes what it fetched via the same bronze upsert the poller uses. This also dissolves ordering: retries can deliver CREATED after SETTLED, but every delivery writes the state fetched at that moment, so duplicates and reordering converge on the same row.\n\n### 200 means durably accepted\n\nThe handler responds 200 only after the write commits and 500 on any failure, so Up's exponential backoff is the retry queue and the idempotent upsert makes redelivery free. Up advises deferring work to a queue before responding; this handler deliberately works synchronously instead, because one fetch plus one row write completes in seconds against a 30 second deadline, and 200-after-commit is exactly what makes the retry a real safety net. Unknown event types are recorded and answered 200, so a future Up event type can never trap the endpoint in a retry loop.\n\n### Deletions carry through both layers\n\nMigration 8 adds `deleted_at` to bronze. A DELETED event marks the existing row (keeping its raw, because \"Up deleted this\" is a fact bronze preserves) and bumps `last_seen_at` so the silver read picks it up; an unknown id lands as a tombstone whose raw is the event body. The silver cron partitions each batch into transforms and deletes, and the silver write applies upserts, deletes and the watermark in one transaction, keeping silver's contract of current live transactions true. Gold needed no change at all: deletions apply only to HELD transactions and gold counts settled rows only. The rare settle-as-delete-plus-create sequence needs no special handling, because the held id leaves through the delete path and the new id arrives through the ordinary create path.\n\n## Consequences\n\nA transaction reaches bronze within seconds of occurring and flows to silver and gold on the existing quarter-hour cadence, with the daily poll now serving as the sweep for anything delivered while the endpoint was down beyond Up's retry horizon; formally demoting that poll to a reconciler is the next increment. The one new operational duty is secret hygiene: `secretKey` is shown once at webhook creation, and losing it means creating a replacement webhook. Up's delivery logs help debugging but may be purged, so bronze remains the only audit trail.\n","label",[159,160,70],"docs","adr","pulls","branches","forks","envVars",{"_168":170,"_171":26},"connections",{"_168":26,"_169":26},"count","unfilledCount",2,"emptyCount",{"_32":453,"_13":420,"_68":415,"_42":-5,"_34":35,"_152":39,"_153":145,"_36":454,"_38":26,"_71":245,"_155":-5,"_72":24,"_157":-5,"_213":455,"_17":456},{"_32":446,"_13":447,"_68":448,"_42":415,"_34":35,"_152":39,"_153":145,"_36":449,"_38":26,"_71":223,"_155":450,"_72":24,"_157":-5,"_213":451,"_17":452},{"_32":438,"_13":439,"_68":440,"_42":415,"_34":35,"_152":441,"_153":145,"_36":442,"_38":39,"_71":223,"_155":443,"_72":24,"_157":-5,"_213":444,"_17":445},{"_32":429,"_13":430,"_68":431,"_42":415,"_34":35,"_152":432,"_153":145,"_36":433,"_38":434,"_71":223,"_155":435,"_72":24,"_157":-5,"_213":436,"_17":437},{"_32":421,"_13":422,"_68":423,"_42":415,"_34":35,"_152":424,"_153":145,"_36":425,"_38":274,"_71":223,"_155":426,"_72":24,"_157":-5,"_213":427,"_17":428},{"_32":412,"_13":413,"_68":414,"_42":415,"_34":35,"_152":39,"_153":145,"_36":416,"_38":26,"_71":223,"_155":417,"_72":24,"_157":-5,"_213":418,"_17":419},{"_32":408,"_13":159,"_68":404,"_42":-5,"_34":35,"_152":39,"_153":145,"_36":409,"_38":26,"_71":245,"_155":-5,"_72":24,"_157":-5,"_213":410,"_17":411},{"_32":403,"_13":160,"_68":151,"_42":404,"_34":35,"_152":39,"_153":145,"_36":405,"_38":26,"_71":245,"_155":-5,"_72":24,"_157":-5,"_213":406,"_17":407},{"_32":396,"_13":397,"_68":398,"_42":151,"_34":35,"_152":39,"_153":145,"_36":399,"_38":26,"_71":21,"_155":400,"_72":24,"_157":-5,"_213":401,"_17":402},{"_32":389,"_13":390,"_68":391,"_42":151,"_34":35,"_152":39,"_153":145,"_36":392,"_38":26,"_71":21,"_155":393,"_72":24,"_157":-5,"_213":394,"_17":395},{"_32":382,"_13":383,"_68":384,"_42":151,"_34":35,"_152":39,"_153":145,"_36":385,"_38":26,"_71":21,"_155":386,"_72":24,"_157":-5,"_213":387,"_17":388},{"_32":375,"_13":376,"_68":377,"_42":151,"_34":35,"_152":39,"_153":145,"_36":378,"_38":26,"_71":21,"_155":379,"_72":24,"_157":-5,"_213":380,"_17":381},{"_32":368,"_13":369,"_68":370,"_42":151,"_34":35,"_152":39,"_153":145,"_36":371,"_38":26,"_71":21,"_155":372,"_72":24,"_157":-5,"_213":373,"_17":374},{"_32":361,"_13":362,"_68":363,"_42":151,"_34":35,"_152":39,"_153":145,"_36":364,"_38":26,"_71":21,"_155":365,"_72":24,"_157":-5,"_213":366,"_17":367},{"_32":150,"_13":70,"_68":69,"_42":151,"_34":35,"_152":39,"_153":145,"_36":358,"_38":26,"_71":21,"_155":156,"_72":24,"_157":-5,"_213":359,"_17":360},{"_32":351,"_13":352,"_68":353,"_42":151,"_34":35,"_152":39,"_153":145,"_36":354,"_38":26,"_71":21,"_155":355,"_72":24,"_157":-5,"_213":356,"_17":357},{"_32":344,"_13":345,"_68":346,"_42":151,"_34":35,"_152":39,"_153":145,"_36":347,"_38":26,"_71":21,"_155":348,"_72":24,"_157":-5,"_213":349,"_17":350},{"_32":337,"_13":338,"_68":339,"_42":151,"_34":35,"_152":39,"_153":145,"_36":340,"_38":26,"_71":21,"_155":341,"_72":24,"_157":-5,"_213":342,"_17":343},{"_32":330,"_13":331,"_68":332,"_42":151,"_34":35,"_152":39,"_153":145,"_36":333,"_38":26,"_71":21,"_155":334,"_72":24,"_157":-5,"_213":335,"_17":336},{"_32":322,"_13":323,"_68":324,"_42":151,"_34":35,"_152":325,"_153":145,"_36":326,"_38":26,"_71":21,"_155":327,"_72":24,"_157":-5,"_213":328,"_17":329},{"_32":314,"_13":315,"_68":316,"_42":151,"_34":35,"_152":317,"_153":145,"_36":318,"_38":26,"_71":21,"_155":319,"_72":24,"_157":-5,"_213":320,"_17":321},{"_32":307,"_13":208,"_68":308,"_42":151,"_34":35,"_152":309,"_153":145,"_36":310,"_38":170,"_71":21,"_155":311,"_72":24,"_157":-5,"_213":312,"_17":313},{"_32":303,"_13":279,"_68":271,"_42":-5,"_34":35,"_152":39,"_153":145,"_36":304,"_38":26,"_71":245,"_155":-5,"_72":24,"_157":-5,"_213":305,"_17":306},{"_32":295,"_13":296,"_68":297,"_42":271,"_34":35,"_152":298,"_153":145,"_36":299,"_38":232,"_71":275,"_155":300,"_72":24,"_157":-5,"_213":301,"_17":302},{"_32":287,"_13":288,"_68":87,"_42":271,"_34":35,"_152":289,"_153":145,"_36":290,"_38":274,"_71":291,"_155":292,"_72":24,"_157":-5,"_213":293,"_17":294},{"_32":280,"_13":281,"_68":282,"_42":271,"_34":35,"_152":39,"_153":145,"_36":283,"_38":26,"_71":275,"_155":284,"_72":24,"_157":-5,"_213":285,"_17":286},{"_32":268,"_13":269,"_68":270,"_42":271,"_34":35,"_152":272,"_153":145,"_36":273,"_38":274,"_71":275,"_155":276,"_72":24,"_157":-5,"_213":277,"_17":278},{"_32":264,"_13":256,"_68":251,"_42":-5,"_34":35,"_152":39,"_153":145,"_36":265,"_38":26,"_71":245,"_155":-5,"_72":24,"_157":-5,"_213":266,"_17":267},{"_32":257,"_13":258,"_68":259,"_42":251,"_34":35,"_152":39,"_153":145,"_36":260,"_38":26,"_71":223,"_155":261,"_72":24,"_157":-5,"_213":262,"_17":263},{"_32":248,"_13":249,"_68":250,"_42":251,"_34":35,"_152":39,"_153":145,"_36":252,"_38":26,"_71":223,"_155":253,"_72":24,"_157":-5,"_213":254,"_17":255},{"_32":243,"_13":227,"_68":219,"_42":-5,"_34":35,"_152":39,"_153":145,"_36":244,"_38":26,"_71":245,"_155":-5,"_72":24,"_157":-5,"_213":246,"_17":247},{"_32":236,"_13":237,"_68":238,"_42":219,"_34":35,"_152":39,"_153":145,"_36":239,"_38":26,"_71":223,"_155":240,"_72":24,"_157":-5,"_213":241,"_17":242},{"_32":228,"_13":229,"_68":230,"_42":219,"_34":35,"_152":114,"_153":145,"_36":231,"_38":232,"_71":223,"_155":233,"_72":24,"_157":-5,"_213":234,"_17":235},{"_32":216,"_13":217,"_68":218,"_42":219,"_34":35,"_152":220,"_153":145,"_36":221,"_38":222,"_71":223,"_155":224,"_72":24,"_157":-5,"_213":225,"_17":226},{"_32":207,"_13":208,"_68":209,"_42":-5,"_34":35,"_152":210,"_153":145,"_36":211,"_38":39,"_71":21,"_155":212,"_72":24,"_157":-5,"_213":214,"_17":215},"6986af51-1f21-4d7a-8b3f-6e1c1bf5a41d","README.md","01a033dc-1a02-7558-bb95-af667e023d51",38,["D",1787630588873],"# up\n\nA personal mirror of an Up Bank account: transactions arrive by webhook within seconds (daily sweep as backup), land raw in bronze, become typed rows in silver, and roll up into gold marts for spend and cashflow.\n\n![Architecture: Up API feeds a webhook and a daily sweep into bronze, silver and gold, which serve analysis](https://imagedelivery.net/iHX6Ovru0O7AjmyT5yZRoA/5e63c89c-87db-4b9e-3131-368d5a7b3b00/public)\n\n## Remix quickstart\n\n1. Remix this val. Migrations bootstrap the schema on first run.\n2. Set `UP_API_TOKEN` in env vars (token from https://api.up.com.au).\n3. Trigger `jobs/bronze.cron.ts` a few times until the logs report backfill done. Each run resumes from its cursor.\n4. Silver and gold fill on the next quarter-hour, or trigger their crons in order.\n5. In `tools/webhookSetup.ts` set `MODE = \"register\"`, run, paste the printed secret into env vars as `UP_WEBHOOK_SECRET`, then `MODE = \"ping\"` and expect DELIVERED / HTTP 200. Leave `MODE` on `status`.\n6. Verify in the SQLite panel: bronze count matches your history, silver matches bronze minus deleted minus rejects, gold tables have rows.\n7. After changing `models/silverTransform.ts`, run `tools/silverReplay.ts` to re-transform anything in `silver_rejects`.\n\n## Tables\n\n| Table | Holds |\n| --- | --- |\n| `bronze_transactions` | Latest state per id: raw JSON as last received, upserted by id, `deleted_at` tombstones |\n| `bronze_transactions_log` | Every observation, append-only: one row per cron page row, webhook fetch or delete event, with source and run id |\n| `silver_transactions` | Typed columns, integer cents, UTC timestamps, live rows only |\n| `silver_rejects` | Bronze ids `toSilver` refused, with reason and attempt count; clears itself when the id transforms cleanly |\n| `gold_category_spend` | Settled spend per day and category, rebuilt whole; durations roll up at query time |\n| `gold_cashflow` | Settled money in and out per day and account, rebuilt whole |\n| `gold_balance` | Reconstructed balance per day and account, dense day spine |\n| `gold_income` | Income per day and type (salary, interest, tax refunds), rebuilt whole |\n| `meta` | Cursors, watermarks, locks, per-job run state |\n\nWhy it is built this way: `docs/adr/`.\n","children",[],[208],"dcdccd33-3f49-4ecf-bee5-d3fe1bb81c24","webhookSetup.ts","01a033dc-1a02-7558-bb95-a7ba71dd4863","01a033dc-1a02-7558-bb95-ab2133f69a22",11,["D",1787576893566],10,"script","/**\n * Webhook setup helper. Run this by hand from the editor; it is not\n * scheduled and nothing imports it.\n *\n * Set MODE below, hit run, read the logs:\n *\n * \"status\" (default, read-only) — lists webhooks registered on the Up\n * account, flags the one pointing at this val, shows recent\n * delivery attempts, and reports whether UP_WEBHOOK_SECRET\n * is set locally.\n * \"register\" — creates a webhook for ENDPOINT_URL and prints its\n * secretKey ONCE. Refuses if one already points here (use\n * \"rotate\" instead).\n * \"rotate\" — deletes the existing webhook for ENDPOINT_URL and\n * creates a fresh one, printing the new secretKey. Use when\n * the secret is lost, since Up never returns it twice.\n * \"ping\" — sends a PING event to the registered webhook, then shows\n * the delivery result.\n * \"deregister\" — deletes the webhook pointing at THIS val (safe: it\n * matches on ENDPOINT_URL, so it cannot touch another val's\n * webhook). Clear UP_WEBHOOK_SECRET afterwards; the endpoint\n * then answers 405/500 to anything that still arrives.\n * \"deregister-id\" — deletes the webhook whose id is in TARGET_ID below.\n * For cleaning up stale registrations pointing at other,\n * retired vals. Run \"status\" first and copy the id from\n * there; the script prints the URL it is about to delete so\n * you can check it is the right one before it goes.\n *\n * Deleting is permanent and immediate: Up stops sending events to that URL\n * at once, and the secretKey cannot be recovered, so a webhook you delete\n * must be re-registered from scratch rather than restored.\n *\n * Why the secret is printed rather than stored: it belongs in the val's\n * env vars, which this script cannot reach — set it by hand in the Val\n * Town UI (or via the Val Town MCP's add_env_var tool). It is deliberately\n * NOT written to sqlite or blob storage — secrets belong in env vars, and\n * `meta` is readable in the panel. The logs hold it until they are purged;\n * rotate if that bothers you.\n */\nconst MODE:\n | \"status\"\n | \"register\"\n | \"rotate\"\n | \"ping\"\n | \"deregister\"\n | \"deregister-id\" = \"status\";\n\n/** Only used by MODE \"deregister-id\". Copy an id from a \"status\" run. */\nconst TARGET_ID = \"\";\n\nconst ENDPOINT_URL =\n \"https://kevinvu184--01a033dc1a0170c186c3c9a7743829d8.web.val.run\";\nconst UP_BASE_URL = \"https://api.up.com.au/api/v1\";\n\ninterface Webhook {\n id: string;\n attributes: {\n url: string;\n description: string | null;\n secretKey?: string;\n createdAt: string;\n };\n}\n\nconst token = Deno.env.get(\"UP_API_TOKEN\");\nif (!token) throw new Error(\"UP_API_TOKEN is not set\");\n\nasync function up(path: string, init: RequestInit = {}): Promise {\n const res = await fetch(`${UP_BASE_URL}${path}`, {\n ...init,\n headers: {\n Authorization: `Bearer ${token}`,\n \"Content-Type\": \"application/json\",\n ...(init.headers ?? {}),\n },\n });\n if (res.status === 204) return null;\n const body = await res.json().catch(() => null);\n if (!res.ok) {\n throw new Error(\n `${init.method ?? \"GET\"} ${path} -> ${res.status}: ${\n JSON.stringify(body)\n }`,\n );\n }\n return body;\n}\n\nasync function listWebhooks(): Promise {\n const body = (await up(\"/webhooks\")) as { data: Webhook[] };\n return body.data;\n}\n\nfunction findOurs(hooks: Webhook[]): Webhook | undefined {\n return hooks.find((h) => h.attributes.url === ENDPOINT_URL);\n}\n\nasync function showDeliveryLogs(id: string): Promise {\n const body = (await up(`/webhooks/${id}/logs?page%5Bsize%5D=5`)) as {\n data: {\n attributes: {\n deliveryStatus: string;\n createdAt: string;\n response: { statusCode: number; body: string } | null;\n };\n }[];\n };\n if (body.data.length === 0) {\n console.log(\"delivery logs: none yet\");\n return;\n }\n console.log(\"recent deliveries (newest first):\");\n for (const log of body.data) {\n const a = log.attributes;\n const code = a.response === null\n ? \"no response\"\n : `HTTP ${a.response.statusCode}`;\n console.log(` ${a.createdAt} ${a.deliveryStatus} ${code}`);\n }\n}\n\n/** Printed once. Up never returns secretKey again. */\nfunction printSecret(secret: string | undefined): void {\n if (!secret) {\n console.error(\n \"no secretKey in the response — cannot recover it; rotate to get a new one\",\n );\n return;\n }\n console.log(\"\");\n console.log(\"=== secretKey, shown once ===\");\n console.log(secret);\n console.log(\"=============================\");\n console.log(\n \"Paste this into the val's env vars as UP_WEBHOOK_SECRET, then run MODE=ping.\",\n );\n}\n\nconst hooks = await listWebhooks();\nconst ours = findOurs(hooks);\nconst secretSet = Boolean(Deno.env.get(\"UP_WEBHOOK_SECRET\"));\n\nif (MODE === \"status\") {\n console.log(`webhooks on the account: ${hooks.length} of 10 allowed`);\n for (const h of hooks) {\n const mark = h.attributes.url === ENDPOINT_URL ? \"*\" : \" \";\n console.log(`${mark} ${h.id} ${h.attributes.url}`);\n }\n console.log(`UP_WEBHOOK_SECRET set locally: ${secretSet}`);\n if (ours === undefined) {\n console.log(\"no webhook points at this val yet — set MODE to register\");\n } else {\n console.log(`registered since ${ours.attributes.createdAt}`);\n await showDeliveryLogs(ours.id);\n if (!secretSet) {\n console.log(\n \"registered but no local secret: deliveries will 401 — set MODE to rotate\",\n );\n }\n }\n} else if (MODE === \"register\") {\n if (ours !== undefined) {\n console.error(\n `a webhook already points here (${ours.id}); use MODE=rotate instead`,\n );\n } else {\n const body = (await up(\"/webhooks\", {\n method: \"POST\",\n body: JSON.stringify({\n data: {\n attributes: { url: ENDPOINT_URL, description: \"bronze webhook\" },\n },\n }),\n })) as { data: Webhook };\n console.log(`created webhook ${body.data.id}`);\n printSecret(body.data.attributes.secretKey);\n }\n} else if (MODE === \"rotate\") {\n if (ours !== undefined) {\n await up(`/webhooks/${ours.id}`, { method: \"DELETE\" });\n console.log(`deleted old webhook ${ours.id}`);\n }\n const body = (await up(\"/webhooks\", {\n method: \"POST\",\n body: JSON.stringify({\n data: {\n attributes: { url: ENDPOINT_URL, description: \"bronze webhook\" },\n },\n }),\n })) as { data: Webhook };\n console.log(`created webhook ${body.data.id}`);\n printSecret(body.data.attributes.secretKey);\n} else if (MODE === \"deregister\") {\n if (ours === undefined) {\n console.log(\"no webhook points at this val — nothing to deregister\");\n } else {\n await up(`/webhooks/${ours.id}`, { method: \"DELETE\" });\n console.log(`deregistered ${ours.id} (${ours.attributes.url})`);\n console.log(\n \"Up will send no further events here. Clear UP_WEBHOOK_SECRET from the env vars;\",\n );\n console.log(\"the daily bronze cron keeps ingesting on its own.\");\n }\n} else if (MODE === \"deregister-id\") {\n const target = hooks.find((h) => h.id === TARGET_ID);\n if (TARGET_ID === \"\") {\n console.error(\"set TARGET_ID to an id from a status run first\");\n } else if (target === undefined) {\n console.error(\n `no webhook with id ${TARGET_ID} on this account; run MODE=status for the list`,\n );\n } else {\n // Echo the URL before deleting so a mistyped id is obvious in the log\n // even though the delete has already happened.\n console.log(`deleting ${target.id} -> ${target.attributes.url}`);\n await up(`/webhooks/${target.id}`, { method: \"DELETE\" });\n console.log(\n \"deleted; its secretKey is gone and it cannot be restored, only re-registered\",\n );\n }\n} else if (MODE === \"ping\") {\n if (ours === undefined) {\n console.error(\"no webhook points at this val — set MODE to register first\");\n } else {\n await up(`/webhooks/${ours.id}/ping`, { method: \"POST\" });\n console.log(\n \"ping sent; delivery is asynchronous, so wait a moment and re-run MODE=status\",\n );\n // Give Up a moment, then read the log so one run usually suffices.\n await new Promise((r) => setTimeout(r, 3_000));\n await showDeliveryLogs(ours.id);\n console.log(\n secretSet\n ? \"expect DELIVERED with HTTP 200; a 401 means the local secret does not match\"\n : \"UP_WEBHOOK_SECRET is not set, so expect HTTP 500 misconfigured\",\n );\n }\n}",[],[227,217],"tools","f8ddbb21-642d-44a9-a276-f67b4ffbcbd7","silverReplay.ts","ba4edebe-a039-11f1-83ef-1607ee4eb77e",["D",1787631065658],4,"/**\n * Silver reject replay (ADR 0013). Run by hand from the editor after a\n * fix to models/silverTransform.ts; it is not scheduled and nothing\n * imports it.\n *\n * Re-runs toSilver over every id in silver_rejects using the CURRENT\n * bronze raw, writes the ones that now pass through the normal silver\n * store (which also clears their reject rows), re-records the ones that\n * still fail (attempts bumps), and applies deletes for ids bronze has\n * since tombstoned. The silver watermark is read and written back\n * unchanged: this tool never moves it, so it cannot skip or replay\n * anything the cron owns.\n *\n * Why a tool rather than waiting: the cron only re-reads bronze rows at\n * or past the watermark, so a fixed transform would not revisit an old\n * rejected row until the daily sweep happened to bump its last_seen_at.\n *\n * Talks only to sqlite via the store; no Up API calls.\n */\nimport {\n metaStore,\n readSilverRejectCount,\n readSilverRejectsWithBronze,\n silverStore,\n} from \"../core/sqliteStore.ts\";\nimport type { SilverReject } from \"../core/state.ts\";\nimport { KEYS } from \"../core/state.ts\";\nimport { toSilver } from \"../models/silverTransform.ts\";\nimport type { SilverRecord } from \"../models/silverTransform.ts\";\n\nconst nowIso = new Date().toISOString();\n\nconst watermark = await metaStore.get(KEYS.silverWatermark);\nif (watermark === null) {\n throw new Error(\"silver_watermark is not set; silver has never run, nothing to replay\");\n}\n\nconst rejects = await readSilverRejectsWithBronze();\nconsole.log(`rejects flagged: ${rejects.length}`);\nif (rejects.length === 0) {\n console.log(\"nothing to replay\");\n} else {\n const records: SilverRecord[] = [];\n const deleteIds: string[] = [];\n const still: SilverReject[] = [];\n const lines: string[] = [];\n\n for (const r of rejects) {\n if (r.raw === null) {\n // Reject row with no bronze row: cannot happen through the cron\n // (rejects come from bronze reads), so it is hand-edited state.\n // Leave it flagged and say so rather than guess.\n lines.push(`${r.id} | ${r.reason} | NO BRONZE ROW — left flagged`);\n continue;\n }\n if (r.deletedAt !== null) {\n deleteIds.push(r.id);\n lines.push(`${r.id} | ${r.reason} | deleted in bronze — reject cleared`);\n continue;\n }\n const res = toSilver(r.id, r.raw);\n if (res.ok) {\n records.push(res.record);\n lines.push(`${r.id} | ${r.reason} | now ok (after ${r.attempts} attempt(s))`);\n } else {\n still.push({ id: r.id, reason: res.reason, lastSeenAt: r.lastSeenAt ?? nowIso });\n lines.push(`${r.id} | ${r.reason} | still rejected: ${res.reason}`);\n }\n }\n\n await silverStore.writeBatch(records, deleteIds, still, watermark, nowIso);\n\n console.log(\"id | before | now\");\n for (const l of lines) console.log(l);\n console.log(\n `replay done: ok=${records.length} deleted=${deleteIds.length} still=${still.length}; watermark unchanged (${watermark})`,\n );\n}\n\n// Refresh only the backlog count on the existing run record, whether or\n// not anything was replayed: the cron owns `at` and `rows`, and an idle\n// cron run exits before recording, so without this the panel would show\n// a stale count until bronze next changed.\nconst lastRunRaw = await metaStore.get(KEYS.silverLastRun);\nif (lastRunRaw !== null) {\n const lastRun = JSON.parse(lastRunRaw) as Record;\n lastRun.rejectsOutstanding = await readSilverRejectCount();\n await metaStore.set(KEYS.silverLastRun, JSON.stringify(lastRun));\n console.log(`silver_last_run.rejectsOutstanding -> ${lastRun.rejectsOutstanding}`);\n}\n",[],[227,229],"046c6a61-bd36-433d-b278-a7a110cb9449","balanceCheck.ts","01a033dc-1a02-7558-bb95-a34800bf3fdf",["D",1787576457509],"/**\n * Balance reconciliation check. Run by hand from the editor; it is not\n * scheduled and nothing imports it.\n *\n * Compares actual balances from GET /accounts against the reconstructed\n * balances in gold_balance (latest day per account). The mart is\n * the single calculation site for reconstruction (BALANCE_EXPR in\n * models/goldModels.ts, ADR 0008); this tool only reads its output.\n *\n * The mart is only as current as the last gold rebuild, so the gold\n * freshness from meta is printed alongside — a delta measured against a\n * stale mart is a freshness problem, not a formula problem. Reconstruction\n * is approximate by design; small stable deltas are expected. HELD\n * transactions (and their round-ups) debit the real balance before the\n * SETTLED-only mart sees them, so a delta read while purchases are pending\n * wobbles by the pending amount and snaps back on settlement. A delta that\n * moves between runs with a fresh mart and nothing pending means something\n * new has stopped being represented as a transaction.\n *\n * Talks to the Up API directly (webhookSetup.ts is the only other file\n * that does): one read-only request, one sqlite read, writes nothing.\n */\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts?v=31\";\n\nconst UP_BASE_URL = \"https://api.up.com.au/api/v1\";\n\nconst token = Deno.env.get(\"UP_API_TOKEN\");\nif (!token) throw new Error(\"UP_API_TOKEN is not set\");\n\nconst dollars = (cents: number) => (cents / 100).toFixed(2);\n\ninterface UpAccount {\n id: string;\n attributes: {\n displayName: string;\n accountType: string;\n balance: { valueInBaseUnits: number };\n };\n}\n\n// Accounts are paginated like everything else; follow links.next even though\n// a personal account list almost always fits one page.\nasync function fetchAllAccounts(): Promise {\n const accounts: UpAccount[] = [];\n let url: string | null = `${UP_BASE_URL}/accounts?page[size]=100`;\n while (url !== null) {\n const res = await fetch(url, { headers: { Authorization: `Bearer ${token}` } });\n if (!res.ok) throw new Error(`GET /accounts -> ${res.status}: ${(await res.text()).slice(0, 200)}`);\n const body = (await res.json()) as { data: UpAccount[]; links: { next: string | null } };\n accounts.push(...body.data);\n url = body.links?.next ?? null;\n }\n return accounts;\n}\n\nconst martRes = await sqlite.execute({\n sql: `SELECT account_id, day, balance_cents\n FROM gold_balance\n WHERE day = (SELECT MAX(day) FROM gold_balance)`,\n args: [],\n});\nif (martRes.rows.length === 0) {\n throw new Error(\n \"gold_balance is empty — trigger jobs/gold.cron.ts (delete the gold_last_run meta key first if it skips)\",\n );\n}\nconst mart = new Map(\n martRes.rows.map((r) => [String(r.account_id), Number(r.balance_cents)]),\n);\nconst martDay = String(martRes.rows[0].day);\n\nconst freshnessRes = await sqlite.execute({\n sql: \"SELECT value FROM meta WHERE key = 'gold_last_run'\",\n args: [],\n});\nconst goldBuiltAt = freshnessRes.rows.length === 0\n ? \"never\"\n : (() => {\n try {\n return String(JSON.parse(String(freshnessRes.rows[0].value)).at);\n } catch {\n return \"unreadable\";\n }\n })();\nconsole.log(`mart day: ${martDay}; gold last rebuilt: ${goldBuiltAt}`);\n\nconst accounts = await fetchAllAccounts();\nconsole.log(`accounts on Up: ${accounts.length}; accounts in mart: ${mart.size}`);\nfor (const a of accounts) {\n const actual = a.attributes.balance.valueInBaseUnits;\n const reconstructed = mart.get(a.id);\n if (reconstructed === undefined) {\n console.log(`${a.attributes.displayName} (${a.attributes.accountType}) not in mart; actual: $${dollars(actual)}`);\n continue;\n }\n const delta = actual - reconstructed;\n console.log(\n `${a.attributes.displayName} (${a.attributes.accountType})\\n` +\n ` reconstructed: $${dollars(reconstructed)} actual: $${dollars(actual)} delta: $${dollars(delta)}`,\n );\n mart.delete(a.id);\n}\nfor (const [id, cents] of mart) {\n console.log(`mart has account ${id} ($${dollars(cents)}) not returned by /accounts`);\n}\n",[],[227,237],"5fd31fa4-7762-4a74-a8e4-5baab5695765",["D",1787576457509],"directory",[203,204,205],[227],"ebbc60e0-8fe2-4f12-94f8-5a7a138a875b","silverTransform.ts","01a033dc-1a01-70c1-86c3-de48d4f5a7a6","01a033dc-1a02-7558-bb95-9eb5cb7ef7c3",["D",1787576457509],"/**\n * The one place raw becomes typed. Pure: the only inputs are the arguments,\n * the only output is the return value; no sqlite, no fetch, no clock.\n * Interpretation changes (a new derived column, a renamed Up field) edit\n * this file and the schema migration, nothing else.\n *\n * Strict on required fields, tolerant of absent optional ones. A required\n * field that is missing or wrongly typed, or an optional one that is\n * present but malformed, is a rejection naming the field, never a default\n * or a silent skip. Bronze keeps the raw, so a rejection costs a rerun\n * after a code fix, not data.\n *\n * Cleaning rules:\n * - Money comes from valueInBaseUnits only, as signed integers. The decimal\n * `value` string is never parsed; floats never touch money.\n * - Timestamps are normalised to UTC ISO strings. Up sends mixed +10:00 and\n * +11:00 offsets across daylight saving, which break lexicographic\n * ordering in SQLite; normalising at write time fixes ordering once.\n */\n\nexport interface SilverRecord {\n id: string;\n accountId: string;\n status: string;\n transactionType: string | null;\n description: string;\n rawText: string | null;\n message: string | null;\n amountCents: number;\n currency: string;\n foreignAmountCents: number | null;\n foreignCurrency: string | null;\n roundUpCents: number | null;\n cashbackCents: number | null;\n categoryId: string | null;\n parentCategoryId: string | null;\n transferAccountId: string | null;\n createdAtUtc: string;\n settledAtUtc: string | null;\n}\n\nexport type TransformResult =\n | { ok: true; record: SilverRecord }\n | { ok: false; id: string; reason: string };\n\ntype Json = Record;\n\nexport function toSilver(id: string, raw: string): TransformResult {\n const fail = (reason: string): TransformResult => ({ ok: false, id, reason });\n\n let root: unknown;\n try {\n root = JSON.parse(raw);\n } catch {\n return fail(\"raw is not valid JSON\");\n }\n if (!isObj(root)) return fail(\"raw is not an object\");\n const attrs = isObj(root.attributes) ? root.attributes : null;\n if (attrs === null) return fail(\"attributes missing\");\n const rels: Json = isObj(root.relationships) ? root.relationships : {};\n\n const status = attrs.status;\n if (typeof status !== \"string\" || status.length === 0) {\n return fail(\"attributes.status missing or not a string\");\n }\n const description = attrs.description;\n if (typeof description !== \"string\") {\n return fail(\"attributes.description missing or not a string\");\n }\n\n const transactionType = optString(attrs.transactionType);\n if (transactionType.invalid) {\n return fail(\"attributes.transactionType present but not a string\");\n }\n\n const rawText = optString(attrs.rawText);\n if (rawText.invalid) return fail(\"attributes.rawText present but not a string\");\n const message = optString(attrs.message);\n if (message.invalid) return fail(\"attributes.message present but not a string\");\n\n const amountCents = moneyCents(attrs.amount);\n if (amountCents === null) {\n return fail(\"attributes.amount.valueInBaseUnits missing or not an integer\");\n }\n const currency = isObj(attrs.amount) && typeof attrs.amount.currencyCode === \"string\"\n ? attrs.amount.currencyCode\n : null;\n if (currency === null) return fail(\"attributes.amount.currencyCode missing\");\n\n const foreign = optMoney(attrs.foreignAmount);\n if (foreign.invalid) return fail(\"attributes.foreignAmount present but malformed\");\n const foreignCurrency = foreign.cents !== null\n && isObj(attrs.foreignAmount)\n && typeof attrs.foreignAmount.currencyCode === \"string\"\n ? attrs.foreignAmount.currencyCode\n : null;\n if (foreign.cents !== null && foreignCurrency === null) {\n return fail(\"attributes.foreignAmount.currencyCode missing\");\n }\n\n const roundUp = optMoney(isObj(attrs.roundUp) ? attrs.roundUp.amount : attrs.roundUp);\n if (roundUp.invalid) return fail(\"attributes.roundUp present but malformed\");\n const cashback = optMoney(isObj(attrs.cashback) ? attrs.cashback.amount : attrs.cashback);\n if (cashback.invalid) return fail(\"attributes.cashback present but malformed\");\n\n const createdAtUtc = utcIso(attrs.createdAt);\n if (createdAtUtc === null) return fail(\"attributes.createdAt missing or unparseable\");\n let settledAtUtc: string | null = null;\n if (attrs.settledAt !== null && attrs.settledAt !== undefined) {\n settledAtUtc = utcIso(attrs.settledAt);\n if (settledAtUtc === null) return fail(\"attributes.settledAt present but unparseable\");\n }\n\n const accountId = relId(rels, \"account\");\n if (accountId === null) return fail(\"relationships.account.data.id missing\");\n\n return {\n ok: true,\n record: {\n id,\n accountId,\n status,\n transactionType: transactionType.value,\n description,\n rawText: rawText.value,\n message: message.value,\n amountCents,\n currency,\n foreignAmountCents: foreign.cents,\n foreignCurrency,\n roundUpCents: roundUp.cents,\n cashbackCents: cashback.cents,\n categoryId: relId(rels, \"category\"),\n parentCategoryId: relId(rels, \"parentCategory\"),\n transferAccountId: relId(rels, \"transferAccount\"),\n createdAtUtc,\n settledAtUtc,\n },\n };\n}\n\nfunction isObj(v: unknown): v is Json {\n return typeof v === \"object\" && v !== null && !Array.isArray(v);\n}\n\n/** null/undefined -> absent; string -> value; anything else -> invalid. */\nfunction optString(v: unknown): { value: string | null; invalid: boolean } {\n if (v === null || v === undefined) return { value: null, invalid: false };\n return typeof v === \"string\" ? { value: v, invalid: false } : { value: null, invalid: true };\n}\n\n/** A MoneyObject -> integer base units; not a valid money object -> null. */\nfunction moneyCents(v: unknown): number | null {\n if (!isObj(v)) return null;\n const n = v.valueInBaseUnits;\n return typeof n === \"number\" && Number.isInteger(n) ? n : null;\n}\n\n/** null/undefined -> absent; valid money -> cents; anything else -> invalid. */\nfunction optMoney(v: unknown): { cents: number | null; invalid: boolean } {\n if (v === null || v === undefined) return { cents: null, invalid: false };\n const cents = moneyCents(v);\n return cents === null ? { cents: null, invalid: true } : { cents, invalid: false };\n}\n\n/** Absent or null relationship -> null; relationship with data.id -> the id. */\nfunction relId(rels: Json, name: string): string | null {\n const rel = rels[name];\n if (!isObj(rel)) return null;\n const data = rel.data;\n if (!isObj(data)) return null;\n return typeof data.id === \"string\" ? data.id : null;\n}\n\n/** RFC 3339 string with any offset -> UTC ISO string; anything else -> null. */\nfunction utcIso(v: unknown): string | null {\n if (typeof v !== \"string\") return null;\n const ms = Date.parse(v);\n return Number.isFinite(ms) ? new Date(ms).toISOString() : null;\n}\n",[],[256,249],"models","94bc03aa-417e-4c48-9235-18bf1ecf5640","goldModels.ts","01a033dc-1a01-70c1-86c3-dbf3065845a7",["D",1787576457509],"/**\n * Gold models: dbt as a discipline, not a dependency. Each gold table is\n * one declarative SELECT over silver, kept here as data; the store wraps\n * every model in the same DELETE plus INSERT, so rebuilds are idempotent\n * and atomic per model, and a change to what a metric means edits one\n * SELECT in this file.\n *\n * THE ONE COUPLING: each SELECT's column order matches its table's column\n * order in migrations.ts, because the runner inserts positionally.\n *\n * Gold is the product's only query surface (ADR 0009). At daily grain the\n * spend and cashflow marts barely compress silver; they are kept for the\n * contract, not the compression. Consumers read gold and never silver, so\n * metric definitions live here alone and silver stays free to change.\n *\n * Interpretation decisions baked in:\n * - Grain is one row per UTC calendar day from created_at_utc (ADR 0009).\n * Durations are a query-time concern: weeks, months, quarters and custom\n * ranges are GROUP BYs over day, never stored. UTC day boundaries sit\n * mid-morning Melbourne time; accepted limit, fixable here.\n * - Category spend counts settled, negative-amount, non-transfer rows,\n * stored as positive cents. Non-transfer means BOTH filters: no\n * transferAccount relationship AND transaction_type not Transfer,\n * Scheduled Transfer or Round Up — Up leaves many internal transfers\n * relationship-untagged (ADR 0010's discovery, applied outbound). Also\n * excluded: rows matching the private curation_external_destinations\n * table (ADR 0011) — own money leaving Up is not consumption.\n * - Cashflow counts settled rows per account, transfers included, because\n * per-account flow is the point and transfers net out across accounts.\n * - Income (ADR 0010) counts settled, positive, non-transfer rows whose\n * transactionType is Salary or Interest, plus ATO tax refunds (Direct\n * Credit rows with raw_text 'ATO', surfaced as income_type 'Tax Refund').\n * Deliberately NOT income: person-to-person Osko (reimbursements),\n * incoming international (family gifts), and other Direct Credits\n * (movements of own money from external accounts). The savings-rate\n * denominator is SUM(income_cents) over the period.\n * - Balance is the cumulative BALANCE_EXPR per account with a dense day\n * spine: every account gets a row for every day from its first\n * transaction through today, carrying the balance forward through quiet\n * days, so \"balance on day D\" never misses an inactive account.\n */\n\nexport interface GoldModel {\n name: string;\n table: string;\n select: string;\n}\n\n/**\n * THE ONE CALCULATION SITE for balance reconstruction. Up records\n * round-ups asymmetrically: a metadata-only debit on the source account\n * (no transaction) and a real \"Round Up\" credit transaction on the\n * receiving Saver, so summing amounts alone overstates any\n * round-up-enabled account. Every consumer of reconstructed balances\n * (the balance mart, tools/balanceCheck.ts via that mart) goes through\n * this expression; no other file may restate the arithmetic.\n * Reconstruction is approximate, not ledger-exact: see ADR 0008.\n */\nexport const BALANCE_EXPR = \"amount_cents + COALESCE(round_up_cents, 0)\";\n\n/**\n * External destinations — outflows to the owner's own accounts outside Up\n * (savings banks, brokers, super) — are movements of own money, not\n * consumption, so category spend excludes them. WHICH institutions those\n * are is personal data, so the list lives as rows in the private\n * curation_external_destinations table (migration 12), never in this\n * public file; the model anti-joins against it. Managing the list is a\n * row edit in the SQLite panel (ADR 0011). An empty table excludes\n * nothing. A NULL transaction_type never matches, so untyped rows stay\n * counted as spend.\n */\n\nexport const GOLD_MODELS: GoldModel[] = [\n {\n name: \"category spend\",\n table: \"gold_category_spend\",\n select: `SELECT substr(created_at_utc, 1, 10) AS day,\n category_id,\n parent_category_id,\n COUNT(*) AS txn_count,\n SUM(-amount_cents) AS spend_cents\n FROM silver_transactions\n WHERE status = 'SETTLED'\n AND amount_cents < 0\n AND transfer_account_id IS NULL\n AND (transaction_type IS NULL\n OR transaction_type NOT IN ('Transfer', 'Scheduled Transfer', 'Round Up'))\n AND NOT EXISTS (\n SELECT 1 FROM curation_external_destinations c\n WHERE c.transaction_type = silver_transactions.transaction_type\n AND c.description = silver_transactions.description\n )\n GROUP BY day, category_id, parent_category_id`,\n },\n {\n name: \"cashflow\",\n table: \"gold_cashflow\",\n select: `SELECT substr(created_at_utc, 1, 10) AS day,\n account_id,\n SUM(CASE WHEN amount_cents > 0 THEN amount_cents ELSE 0 END) AS in_cents,\n SUM(CASE WHEN amount_cents < 0 THEN -amount_cents ELSE 0 END) AS out_cents,\n SUM(amount_cents) AS net_cents,\n COUNT(*) AS txn_count\n FROM silver_transactions\n WHERE status = 'SETTLED'\n GROUP BY day, account_id`,\n },\n {\n name: \"income\",\n table: \"gold_income\",\n select: `SELECT substr(created_at_utc, 1, 10) AS day,\n CASE WHEN transaction_type = 'Direct Credit'\n THEN 'Tax Refund'\n ELSE transaction_type END AS income_type,\n COUNT(*) AS txn_count,\n SUM(amount_cents) AS income_cents\n FROM silver_transactions\n WHERE status = 'SETTLED'\n AND amount_cents > 0\n AND transfer_account_id IS NULL\n AND (\n transaction_type IN ('Salary', 'Interest')\n OR (transaction_type = 'Direct Credit' AND raw_text = 'ATO')\n )\n GROUP BY day, income_type`,\n },\n {\n name: \"balance\",\n table: \"gold_balance\",\n select: `WITH RECURSIVE\n nets AS (\n SELECT account_id,\n substr(created_at_utc, 1, 10) AS day,\n SUM(${BALANCE_EXPR}) AS net_cents\n FROM silver_transactions\n WHERE status = 'SETTLED'\n GROUP BY account_id, day\n ),\n bounds AS (\n SELECT account_id, MIN(day) AS first_day FROM nets GROUP BY account_id\n ),\n spine (account_id, day) AS (\n SELECT account_id, first_day FROM bounds\n UNION ALL\n SELECT account_id, date(day, '+1 day')\n FROM spine\n WHERE day < date('now')\n )\n SELECT s.day,\n s.account_id,\n SUM(COALESCE(n.net_cents, 0)) OVER (\n PARTITION BY s.account_id ORDER BY s.day\n ) AS balance_cents\n FROM spine s\n LEFT JOIN nets n ON n.account_id = s.account_id AND n.day = s.day`,\n },\n];\n",[],[256,258],"e813d9af-0035-422a-b743-43f9703f897c",["D",1787576457509],[200,201],[256],"25b78a6a-11b2-4993-b6bb-5bacaede0404","silver.cron.ts","01a033dc-1a01-70c1-86c3-d2d2ddc5cccd","01a033dc-1a01-70c1-86c3-d62802e2b4dd",30,["D",1787630504826],3,"interval","/**\n * Silver cron: derives silver_transactions from bronze, independent of the\n * bronze sync (ADR 0004). Composition root for the silver job; holds the\n * run shape and no logic of its own.\n *\n * The crons never coordinate: this job reads only committed bronze rows,\n * silver upserts are idempotent, and each batch lands its rows and\n * watermark in one transaction, so anything the bronze cron commits\n * mid-run is simply picked up on the next pass. Mutual exclusion is\n * against this job only, via its own seeded lock row.\n *\n * Run shape:\n * 0. migrate (any cron may fire first after a deploy; the runner is\n * versioned, so later arrivals apply nothing)\n * 1. acquire the silver lock; if held, exit\n * 2. transform bronze rows at or past the watermark, batch by batch,\n * until caught up or out of budget\n * 3. write run state and release the lock, including on error\n */\nimport { acquireLock, releaseLock } from \"../core/lock.ts\";\nimport { migrate } from \"../core/migrations.ts\";\nimport {\n db,\n metaStore,\n readBronzeMaxSeen,\n readBronzeSince,\n readSilverRejectCount,\n silverLockStore,\n silverStore,\n} from \"../core/sqliteStore.ts\";\nimport { makeSilverState } from \"../core/state.ts\";\nimport type { SilverReject } from \"../core/state.ts\";\nimport { toSilver } from \"../models/silverTransform.ts\";\nimport type { SilverRecord } from \"../models/silverTransform.ts\";\n\n// Same wall-clock discipline as the bronze cron: the free tier kills a run\n// at 60 seconds, so budget 45 and land batches as we go.\nconst RUN_BUDGET_MS = 45_000;\n// TTL only needs to outlive the kill wall; expiry recovers a killed run's\n// lock well before the next trigger.\nconst LOCK_TTL_MS = 5 * 60_000;\n// Comfortably above any same-timestamp cluster (a bronze page shares one\n// seenAt and holds at most 100 rows), so the watermark can always advance.\n// This assumption is load-bearing: if it ever breaks, the batch loop\n// throws rather than silently stalling (see the cluster check below).\nconst SILVER_BATCH = 500;\n\nexport default async function run(_interval?: unknown) {\n const now = () => Date.now();\n const randomId = () => crypto.randomUUID();\n const deadline = now() + RUN_BUDGET_MS;\n\n const applied = await migrate(db);\n if (applied.from !== applied.to) {\n console.log(`schema migrated v${applied.from} -> v${applied.to}`);\n }\n\n const lock = await acquireLock(silverLockStore, now, randomId, LOCK_TTL_MS);\n if (!lock.acquired) {\n console.log(\"silver lock held by another run; exiting\");\n return;\n }\n\n const state = makeSilverState(metaStore, silverStore);\n\n try {\n // Cheap pre-check for the 15-minute cadence: exit before the batch\n // loop when bronze has nothing new, so an idle run is one read rather\n // than a re-worked boundary page. String comparison is sound because\n // both sides are UTC ISO timestamps.\n let watermark = await state.getWatermark();\n const maxSeen = await readBronzeMaxSeen();\n if (maxSeen === null || (watermark !== null && maxSeen <= watermark)) {\n console.log(\"bronze unchanged; skipping\");\n return;\n }\n\n // Transform bronze rows at or past the watermark, batch by batch. The\n // >= read re-transforms boundary rows; idempotent upserts make that\n // harmless and bounded at one page.\n let silverRows = 0;\n let silverRejects = 0;\n while (now() < deadline) {\n const batch = await readBronzeSince(watermark, SILVER_BATCH);\n if (batch.length === 0) break;\n const records: SilverRecord[] = [];\n const deleteIds: string[] = [];\n const rejects: SilverReject[] = [];\n for (const row of batch) {\n // A row marked deleted in bronze leaves silver: silver's contract\n // is current live transactions. No transform is attempted; the raw\n // may be a tombstone event body, not a transaction resource.\n if (row.deletedAt !== null) {\n deleteIds.push(row.id);\n continue;\n }\n const res = toSilver(row.id, row.raw);\n // A rejection is quarantined, not fatal (ADR 0013): the id lands\n // in silver_rejects with its reason, the rest of the batch and the\n // watermark still advance, and tools/silverReplay.ts clears the\n // backlog after a transform fix. Bronze keeps the raw either way.\n if (!res.ok) {\n console.warn(`silver transform rejected ${res.id}: ${res.reason}`);\n rejects.push({ id: res.id, reason: res.reason, lastSeenAt: row.lastSeenAt });\n continue;\n }\n records.push(res.record);\n }\n const nextWatermark = batch[batch.length - 1].lastSeenAt;\n await state.writeBatch(records, deleteIds, rejects, nextWatermark, new Date(now()).toISOString());\n silverRows += records.length;\n silverRejects += rejects.length;\n if (deleteIds.length > 0) {\n console.log(`silver deletes applied: ${deleteIds.length}`);\n }\n if (rejects.length > 0) {\n console.warn(`silver rejects recorded: ${rejects.length}`);\n }\n console.log(`silver batch landed: ${records.length} rows`);\n if (batch.length < SILVER_BATCH) break;\n if (nextWatermark === watermark) {\n // A full batch that cannot advance the watermark is a same-timestamp\n // cluster >= SILVER_BATCH. The next run would repeat the identical\n // read, and once the cluster is bronze's max last_seen_at the\n // pre-check would report \"unchanged\" — a silent, permanent stall.\n // Fail loud instead; the fix is a code change (raise SILVER_BATCH or\n // page within the cluster), and bronze keeps the raw, so this costs\n // a rerun, never data.\n throw new Error(\n `silver watermark stalled: same-timestamp cluster >= ${SILVER_BATCH} rows at ${nextWatermark}`,\n );\n }\n watermark = nextWatermark;\n }\n\n const outstanding = await readSilverRejectCount();\n await state.recordSuccess(new Date(now()).toISOString(), silverRows, silverRejects, outstanding);\n if (outstanding > 0) {\n // Warn, not error: the run succeeded. Warnings still surface in the\n // logs panel, so a lingering backlog is visible without failing runs.\n console.warn(`silver rejects outstanding: ${outstanding} (see silver_rejects; run tools/silverReplay.ts after a fix)`);\n }\n console.log(`silver ok: rows=${silverRows} rejects=${silverRejects}`);\n } catch (err) {\n const message = String((err as Error)?.message ?? err);\n console.error(`silver failed: ${message}`);\n // Best effort by design: if the failure is sqlite itself, this write\n // fails too, the TTL recovers the lock, and the logs are the record.\n try {\n await state.recordFailure(new Date(now()).toISOString(), message);\n } catch (writeErr) {\n console.error(`could not write silver_last_error: ${String(writeErr)}`);\n }\n throw err;\n } finally {\n await releaseLock(silverLockStore, lock);\n }\n}\n",[],[279,269],"jobs","a39a5a29-ecb7-45c4-a538-0f71c17c687b","gold.cron.ts","01a033dc-1a01-70c1-86c3-cfefc538f248",["D",1787576457509],"/**\n * Gold cron: rebuilds the gold aggregate tables from silver. Third\n * composition root; holds the run shape and no business rules — every\n * metric definition lives in goldModels.ts.\n *\n * No coordination with the other crons: it reads committed silver, each\n * model rebuilds in one transaction, and the watermark skip makes quiet\n * hours a no-op. Mutual exclusion is against this job only, via its own\n * seeded lock row.\n *\n * Run shape:\n * 0. migrate (any cron may fire first after a deploy)\n * 1. acquire the gold lock; if held, exit\n * 2. skip if silver has not moved since the last successful build\n * 3. rebuild each model in order, one transaction per model, checking\n * the deadline between models\n * 4. write run state and release the lock, including on error\n */\nimport { acquireLock, releaseLock } from \"../core/lock.ts\";\nimport { migrate } from \"../core/migrations.ts\";\nimport { GOLD_MODELS } from \"../models/goldModels.ts\";\nimport { db, goldLockStore, metaStore, rebuildGoldModel } from \"../core/sqliteStore.ts\";\nimport { makeGoldState } from \"../core/state.ts\";\n\nconst RUN_BUDGET_MS = 45_000;\nconst LOCK_TTL_MS = 5 * 60_000;\n\nexport default async function run(_interval?: unknown) {\n const now = () => Date.now();\n const randomId = () => crypto.randomUUID();\n const deadline = now() + RUN_BUDGET_MS;\n\n const applied = await migrate(db);\n if (applied.from !== applied.to) {\n console.log(`schema migrated v${applied.from} -> v${applied.to}`);\n }\n\n const lock = await acquireLock(goldLockStore, now, randomId, LOCK_TTL_MS);\n if (!lock.acquired) {\n console.log(\"gold lock held by another run; exiting\");\n return;\n }\n\n const state = makeGoldState(metaStore);\n\n try {\n const watermark = await state.getSilverWatermark();\n if (watermark === null) {\n console.log(\"no silver watermark yet; nothing to build\");\n return;\n }\n const lastRun = await state.getLastRun();\n if (lastRun !== null && lastRun.inputWatermark === watermark) {\n console.log(\"silver unchanged since last build; skipping\");\n return;\n }\n\n const rows: Record = {};\n for (const model of GOLD_MODELS) {\n if (now() >= deadline) {\n // Out of budget mid-list: fail rather than record a success that\n // covers models this run never rebuilt. Each table already rebuilt\n // is individually consistent; the next run retries the whole list.\n throw new Error(`out of budget before rebuilding ${model.table}`);\n }\n rows[model.table] = await rebuildGoldModel(model);\n console.log(`gold rebuilt: ${model.table} = ${rows[model.table]} rows`);\n }\n\n await state.recordSuccess(new Date(now()).toISOString(), rows, watermark);\n console.log(`gold ok: ${JSON.stringify(rows)}`);\n } catch (err) {\n const message = String((err as Error)?.message ?? err);\n console.error(`gold failed: ${message}`);\n // Best effort by design: if the failure is sqlite itself, this write\n // fails too, the TTL recovers the lock, and the logs are the record.\n try {\n await state.recordFailure(new Date(now()).toISOString(), message);\n } catch (writeErr) {\n console.error(`could not write gold_last_error: ${String(writeErr)}`);\n }\n throw err;\n } finally {\n await releaseLock(goldLockStore, lock);\n }\n}\n",[],[279,281],"e5f63a53-3bbf-402a-abd4-d472d561c3c0","bronze.webhook.ts",27,["D",1787630482912],"http","/**\n * Bronze webhook: near-real-time ingestion from Up's webhook events\n * (ADR 0007). HTTP composition root; holds the delivery shape and no\n * business rules.\n *\n * The endpoint is public by necessity; the X-Up-Authenticity-Signature\n * (SHA-256 HMAC of the raw body, hex) is the authentication, verified\n * constant-time before anything is parsed. Delivery is at-least-once with\n * exponential backoff on non-200, so: 200 only after the write commits,\n * 500 to request redelivery, and idempotent writes make redelivery free.\n *\n * Events carry only a transaction id, so the handler fetches current\n * state from the API and writes what it fetched; retries delivering\n * CREATED after SETTLED therefore converge instead of regressing. Up\n * times deliveries out at 30 seconds; the work here is one fetch plus one\n * single-row write, done synchronously because 200-after-commit is what\n * makes Up's retry the safety net.\n *\n * No lock and no cursor: concurrent deliveries only do atomic single-row\n * writes keyed by id.\n */\nimport { migrate } from \"../core/migrations.ts\";\nimport { bronzeStore, db, markBronzeDeleted, metaStore } from \"../core/sqliteStore.ts\";\nimport { makeWebhookState } from \"../core/state.ts\";\nimport { makeUpClient } from \"../core/upClient.ts\";\n\n// Up times the delivery out at 30 seconds; budget under it so a backoff\n// wait ends the attempt cleanly and Up's retry takes over.\nconst RUN_BUDGET_MS = 25_000;\n\n/** Constant-time check of the hex HMAC signature against the raw body. */\nexport async function verifySignature(\n rawBody: string,\n header: string | null,\n secret: string,\n): Promise {\n if (header === null) return false;\n const encoder = new TextEncoder();\n const key = await crypto.subtle.importKey(\n \"raw\",\n encoder.encode(secret),\n { name: \"HMAC\", hash: \"SHA-256\" },\n false,\n [\"sign\"],\n );\n const mac = new Uint8Array(await crypto.subtle.sign(\"HMAC\", key, encoder.encode(rawBody)));\n const expected = Array.from(mac)\n .map((b) => b.toString(16).padStart(2, \"0\"))\n .join(\"\");\n const received = header.toLowerCase();\n if (received.length !== expected.length) return false;\n let diff = 0;\n for (let i = 0; i < expected.length; i++) {\n diff |= expected.charCodeAt(i) ^ received.charCodeAt(i);\n }\n return diff === 0;\n}\n\ninterface WebhookEventBody {\n data?: {\n attributes?: { eventType?: unknown; createdAt?: unknown };\n relationships?: { transaction?: { data?: { id?: unknown } } };\n };\n}\n\nexport default async function handle(req: Request): Promise {\n const now = () => Date.now();\n const sleep = (ms: number) => new Promise((resolve) => setTimeout(resolve, ms));\n const deadline = now() + RUN_BUDGET_MS;\n\n // Method check first. This URL is public and gets probed constantly by\n // internet scanners; answering their GETs with 405 keeps unrelated\n // traffic out of the error path (and out of alert emails). Only a POST\n // can possibly be an Up delivery.\n if (req.method !== \"POST\") {\n return new Response(\"method not allowed\", {\n status: 405,\n headers: { Allow: \"POST\" },\n });\n }\n\n const secret = Deno.env.get(\"UP_WEBHOOK_SECRET\");\n const token = Deno.env.get(\"UP_API_TOKEN\");\n if (!secret || !token) {\n console.error(\"webhook misconfigured: UP_WEBHOOK_SECRET or UP_API_TOKEN not set\");\n return new Response(\"misconfigured\", { status: 500 });\n }\n\n // The signature is the authentication: verify against the raw body\n // before parsing anything, and do nothing on mismatch. An unsigned or\n // wrongly signed POST is hostile or misconfigured, not a server fault,\n // so it is logged as a warning rather than an error.\n const rawBody = await req.text();\n const valid = await verifySignature(\n rawBody,\n req.headers.get(\"X-Up-Authenticity-Signature\"),\n secret,\n );\n if (!valid) {\n console.warn(\"webhook rejected: invalid signature\");\n return new Response(\"invalid signature\", { status: 401 });\n }\n\n const state = makeWebhookState(metaStore);\n try {\n // The webhook may be the first entry point to run after a deploy that\n // adds a migration; the runner is versioned, so this normally applies\n // nothing and costs one read.\n await migrate(db);\n\n const event = JSON.parse(rawBody) as WebhookEventBody;\n const attrs = event?.data?.attributes;\n const eventType = typeof attrs?.eventType === \"string\" ? attrs.eventType : \"UNKNOWN\";\n const txData = event?.data?.relationships?.transaction?.data;\n const txId = typeof txData?.id === \"string\" ? txData.id : null;\n const nowIso = new Date(now()).toISOString();\n // One run id per delivery for bronze_transactions_log (ADR 0012); the\n // webhook has no lock token to reuse.\n const meta = { source: \"webhook\" as const, runId: crypto.randomUUID(), eventType };\n\n if (eventType === \"TRANSACTION_CREATED\" || eventType === \"TRANSACTION_SETTLED\") {\n if (txId === null) throw new Error(`${eventType} event without transaction id`);\n const up = makeUpClient({ fetchFn: fetch, now, sleep }, token, deadline);\n const res = await up.fetchTransaction(txId);\n if (res.kind === \"error\" && res.status === 404) {\n // Gone between the event and the fetch (e.g. a cancelled hold): the\n // DELETED event carries the truth, so this delivery ends as a\n // recorded no-op rather than a 500 that would put Up into a retry\n // loop over a row that can never be fetched.\n console.log(`webhook fetch ${txId}: 404, gone before fetch; no-op`);\n } else if (res.kind !== \"record\") {\n throw new Error(\n `fetch ${txId}: ${res.kind === \"error\" ? res.detail : \"out of budget\"}`,\n );\n } else {\n // seenAt is stamped AFTER the fetch, immediately before the write.\n // Stamping earlier let a slow fetch (retries can take ~25s) commit a\n // row whose last_seen_at was already behind the silver watermark,\n // hiding it from silver until the daily sweep re-bumped it.\n await bronzeStore.writePage(\n [{ ...res.record, seenAt: new Date(now()).toISOString() }],\n null,\n meta,\n );\n }\n } else if (eventType === \"TRANSACTION_DELETED\") {\n // Deleted transactions no longer exist in the API, so no fetch; the\n // event body itself is the raw for a tombstone.\n if (txId === null) throw new Error(\"TRANSACTION_DELETED event without transaction id\");\n const createdAt = typeof attrs?.createdAt === \"string\" ? attrs.createdAt : nowIso;\n await markBronzeDeleted(txId, createdAt, rawBody, nowIso, meta);\n }\n // PING and unknown event types fall through to a recorded 200, so a\n // future Up event type never puts this endpoint into a retry loop.\n\n await state.recordEvent(nowIso, eventType);\n console.log(`webhook ok: ${eventType}${txId === null ? \"\" : ` ${txId}`}`);\n return new Response(\"ok\", { status: 200 });\n } catch (err) {\n const message = String((err as Error)?.message ?? err);\n console.error(`webhook failed: ${message}`);\n // Best effort by design: if the failure is sqlite itself, this write\n // fails too and the logs are the record. The 500 asks Up to redeliver.\n try {\n await state.recordFailure(new Date(now()).toISOString(), message);\n } catch (writeErr) {\n console.error(`could not write bronze_webhook_last_error: ${String(writeErr)}`);\n }\n return new Response(\"error\", { status: 500 });\n }\n}\n",[],[279,288],"2c9956d7-223c-4735-a863-a6e4555cb095","bronze.cron.ts","01a033dc-1a01-70c1-86c3-c7508215fe09",24,["D",1787630472216],"/**\n * Bronze cron: syncs the Up account into bronze_transactions. Composition\n * root: the only place real sqlite, real fetch, and Date.now are wired in.\n * Computes the run deadline once and passes it down. Holds the run shape\n * and contains no logic of its own.\n *\n * Run shape:\n * 0. migrate (see ordering note below)\n * 1. acquire the bronze lock; if held, exit\n * 2. backfill: resume from the cursor, page towards the start of history\n * 3. incremental 30-day window with whatever budget remains\n * 4. write run state and release the lock, including on the error path\n *\n * The lock token doubles as the run id in bronze_transactions_log\n * (ADR 0012): it is unique per run and already in hand.\n *\n * Silver derivation runs on silver.cron.ts; gold rebuilds on gold.cron.ts.\n */\nimport { acquireLock, releaseLock } from \"../core/lock.ts\";\nimport { migrate } from \"../core/migrations.ts\";\nimport { bronzeLockStore, bronzeStore, db, metaStore } from \"../core/sqliteStore.ts\";\nimport { BRONZE_DONE, makeBronzeState } from \"../core/state.ts\";\nimport { backfillStartUrl, incrementalStartUrl, makeUpClient } from \"../core/upClient.ts\";\n\n// The free tier kills a run at 60 seconds. Budget 45 and persist the cursor\n// after every page, so a killed run resumes with at most one page refetched.\nconst RUN_BUDGET_MS = 45_000;\n// The TTL only needs to outlive the 60s kill wall (a killed run never runs\n// its release path). 5 minutes clears the wall comfortably and recovers the\n// lock long before the next daily trigger.\nconst LOCK_TTL_MS = 5 * 60_000;\nconst PAGE_SIZE = 100;\nconst INCREMENTAL_WINDOW_DAYS = 30;\n\nexport default async function run(_interval?: unknown) {\n const now = () => Date.now();\n const sleep = (ms: number) => new Promise((resolve) => setTimeout(resolve, ms));\n const randomId = () => crypto.randomUUID();\n const deadline = now() + RUN_BUDGET_MS;\n\n const token = Deno.env.get(\"UP_API_TOKEN\");\n if (!token) {\n throw new Error(\"UP_API_TOKEN is not set — add it in the Val Town UI (read-only env var)\");\n }\n\n // Ordering note: migration runs BEFORE the lock, deviating from the run\n // shape as listed, because the migrations seed the very rows the locks'\n // conditional UPDATEs target.\n const applied = await migrate(db);\n if (applied.from !== applied.to) {\n console.log(`schema migrated v${applied.from} -> v${applied.to}`);\n }\n\n const lock = await acquireLock(bronzeLockStore, now, randomId, LOCK_TTL_MS);\n if (!lock.acquired) {\n console.log(\"bronze lock held by another run; exiting without touching the cursor\");\n return;\n }\n\n const state = makeBronzeState(metaStore, bronzeStore);\n const up = makeUpClient({ fetchFn: fetch, now, sleep }, token, deadline);\n\n try {\n // --- Backfill: finite, so it runs first; the sentinel skips it forever\n // once the start of history has been reached.\n let backfillRows = 0;\n const cursor = await state.getCursor();\n if (cursor === BRONZE_DONE) {\n console.log(\"backfill sentinel set; skipping straight to incremental window\");\n } else {\n let url = cursor ?? backfillStartUrl(PAGE_SIZE);\n while (true) {\n const page = await up.fetchPage(url);\n if (page.kind === \"out_of_budget\") {\n console.log(`backfill paused at budget after ${backfillRows} rows; cursor persisted`);\n break;\n }\n if (page.kind === \"error\") throw new Error(`backfill: ${page.detail}`);\n const seenAt = new Date(now()).toISOString();\n const rows = page.records.map((r) => ({ ...r, seenAt }));\n if (page.nextUrl === null) {\n // The final page of history and the done sentinel land in one\n // transaction.\n await state.writeBackfillPage(rows, BRONZE_DONE, lock.token);\n backfillRows += rows.length;\n console.log(\"backfill reached start of history; final page and sentinel written together\");\n break;\n }\n await state.writeBackfillPage(rows, page.nextUrl, lock.token);\n backfillRows += rows.length;\n console.log(`backfill page landed: ${rows.length} rows`);\n url = page.nextUrl;\n }\n }\n\n // --- Incremental window: last 30 days, newest first, with the remaining\n // budget. Runs second so a fat window can never starve the finite\n // backfill. Pages land in bronze through the same writer (no cursor\n // involved). The upsert count is the liveness signal in the panel —\n // proof the token works and the account is reachable.\n let incrementalRows = 0;\n const sinceIso = new Date(\n now() - INCREMENTAL_WINDOW_DAYS * 24 * 60 * 60 * 1_000,\n ).toISOString();\n let incUrl: string | null = incrementalStartUrl(PAGE_SIZE, sinceIso);\n while (incUrl !== null) {\n const page = await up.fetchPage(incUrl);\n if (page.kind === \"out_of_budget\") {\n console.log(`incremental window paused at budget after ${incrementalRows} rows`);\n break;\n }\n if (page.kind === \"error\") throw new Error(`incremental: ${page.detail}`);\n const seenAt = new Date(now()).toISOString();\n await state.writeIncrementalPage(page.records.map((r) => ({ ...r, seenAt })), lock.token);\n incrementalRows += page.count;\n incUrl = page.nextUrl;\n }\n\n await state.recordSuccess(new Date(now()).toISOString(), {\n backfill: backfillRows,\n incremental: incrementalRows,\n });\n console.log(`bronze ok: backfill=${backfillRows} incremental=${incrementalRows}`);\n } catch (err) {\n const message = String((err as Error)?.message ?? err);\n console.error(`bronze failed: ${message}`);\n // Best effort by design: if the failure is sqlite itself, this write\n // fails too, the TTL recovers the lock, and the logs are the record.\n try {\n await state.recordFailure(new Date(now()).toISOString(), message);\n } catch (writeErr) {\n console.error(`could not write bronze_last_error: ${String(writeErr)}`);\n }\n throw err;\n } finally {\n await releaseLock(bronzeLockStore, lock);\n }\n}\n",[],[279,296],"0196458e-1173-487f-a166-41b411d4ed14",["D",1787576457509],[195,196,197,198],[279],"b68a9f4e-885b-41e8-9121-102f98e86fe5","01a033dc-1a01-70c1-86c3-bb20613d5636",37,["D",1787630579922],"# Architecture decision records\n\nOne file per decision, numbered in order: `NNNN-short-title.md`. New records get\nthe next number; superseded records stay in place with a note pointing at their\nreplacement.\n\n| ADR | Title | Status |\n| ---- | ---------------------------------- | -------- |\n| 0001 | Up transaction poller | Accepted |\n| 0002 | Bronze table for raw transactions | Accepted, extended by 0012 |\n| 0003 | Silver layer of typed transactions | Accepted, amended by 0004 and 0013 |\n| 0004 | Promotion runs on its own cron | Accepted |\n| 0005 | Gold layer of aggregate tables | Accepted, marts re-grained by 0009 |\n| 0006 | Layer naming scheme | Accepted |\n| 0007 | Webhook ingestion into bronze | Accepted |\n| 0008 | Balance reconstruction from transactions | Accepted, monthly mart superseded by 0009 |\n| 0009 | Gold at daily grain, durations at query time | Accepted |\n| 0010 | Income mart for the savings-rate denominator | Accepted |\n| 0011 | External destinations are not spend | Accepted |\n| 0012 | Bronze keeps an append-only observation log | Accepted |\n| 0013 | Silver quarantines rejects instead of halting | Accepted |",[],[159,160,208],"446180f7-a787-4643-b1cc-17195baf942d","0013-silver-rejects.md","d8aab7b6-a039-11f1-b464-1607ee4eb77e",35,["D",1787630573363],"# ADR 0013. Silver quarantines rejects instead of halting\n\nStatus: accepted. Implemented 2026-08-25 in kevinvu184/up. Amends ADR 0003 (silver layer): the transform's strictness is unchanged, what happens on a rejection is not.\n\n## Context\n\n`toSilver` rejects any bronze row with a missing or malformed required field, naming the field. The silver cron treated the first rejection as fatal: it threw, the batch did not land, the watermark did not advance, and every row behind the bad one waited until a code fix shipped. That was defended as \"costs a rerun, never data\" — true, but one unexpected Up payload shape (a new transactionType with an odd field, a resource with a null `description`) would silently freeze silver and gold for as long as nobody noticed. The medallion contract for silver is to flag or reject invalid records systematically, not to let one record block the layer.\n\n## Decisions\n\n### Reject rows are recorded, the run continues\n\nMigration 14 adds `silver_rejects` (id, reason, last_seen_at, first_rejected_at, last_rejected_at, attempts). The cron pushes each rejection into the batch's reject list, logs a warning with the same message text as before, and lands the batch: good rows, deletes, reject rows and the watermark advance in one transaction. `toSilver` itself is untouched — it still refuses rather than defaults.\n\n### One row per id, self-clearing\n\nA reject upsert bumps `attempts` and keeps `first_rejected_at`. Any batch that transforms the same id cleanly, or deletes it, removes its reject row in the same transaction. So a fixed transform or a corrected upstream payload clears the flag on the next pass with no manual step.\n\n### A regressed row keeps its stale silver row\n\nIf an id that already has a silver row starts rejecting, silver keeps the old row and the reject row is the flag. Deleting from silver on rejection would make gold marts flicker on a transient upstream quirk; a stale row plus a visible flag was judged the lesser harm. This is a deliberate, documented staleness.\n\n### The run reports the backlog\n\n`silver_last_run` gains `rejects` (this run) and `rejectsOutstanding` (table total). A non-zero outstanding count logs a warning at the end of every successful run so the backlog stays visible without failing runs.\n\n### A replay tool, because the watermark will not revisit\n\nThe cron only reads bronze rows at or past the watermark, so once a rejected row's `last_seen_at` is behind the watermark, a transform fix would not reach it until the daily sweep happened to re-bump it. `tools/silverReplay.ts` re-runs `toSilver` over every flagged id against current bronze, writes what now passes, re-records what still fails, and leaves the watermark exactly where it was.\n\n### The watermark-stall throw stays fatal\n\nA full batch that cannot advance the watermark is an invariant failure, not a data-quality event, and still throws.\n\n## Consequences\n\nSilver degrades per row instead of per layer: a bad payload costs one flagged id, not a frozen pipeline. The price is a new place to look — `silver_rejects` in the panel, `rejectsOutstanding` in run state, the warning in logs — and the accepted staleness of a regressed row until it is fixed or deleted.\n",[],[159,160,315],"99136358-2c86-4b62-928a-90d1b25d3666","0012-bronze-append-only-log.md","cdc917e8-a039-11f1-8b0f-1607ee4eb77e",34,["D",1787630555098],"# ADR 0012. Bronze keeps an append-only observation log\n\nStatus: accepted. Implemented 2026-08-25 in kevinvu184/up. Extends ADR 0002 (bronze table) and ADR 0007 (webhook tombstones).\n\n## Context\n\n`bronze_transactions` was built as a latest-state mirror: the page writer upserts by id and replaces `raw`, so a transaction moving HELD → SETTLED overwrites the earlier payload, and a webhook fetch overwrites whatever the daily sweep last saw. That fails two things bronze is supposed to guarantee under the medallion contract: history is not preserved (there is no way to answer \"what did this row look like on day X\"), and there is no provenance (nothing records whether a row arrived by cron backfill, cron incremental sweep, or webhook, nor which run wrote it). `first_seen_at` and `last_seen_at` record *when*, not *what* or *by whom*. Two alternatives were rejected: making the mirror itself append-only (every reader — silver's watermark scan, the tombstone path — is keyed by id and would need rewriting), and storing diffs (a diff format is a second interpretation layer bronze is not supposed to have).\n\n## Decisions\n\n### A second table, insert-only, alongside the mirror\n\nMigration 13 adds `bronze_transactions_log` (seq, id, seen_at, source, run_id, event_type, raw). Every observation appends one row: each row of a cron page, each webhook fetch, and each DELETED event (whose raw is the event body, labelled by event_type). The mirror is unchanged and remains what silver reads. The log's `seen_at` equals the `last_seen_at` written to the mirror in the same statement.\n\n### Log and mirror land in one transaction\n\nThe log insert is pushed into the same `sqlite.batch` as the mirror upsert (and, for backfill, the cursor advance), so the log can never lack an observation the mirror has, and a killed run leaves both or neither. `bronzeLogInsert` in `core/sqliteStore.ts` is the only producer of statements against the log; there is no UPDATE or DELETE against it anywhere in the codebase.\n\n### Provenance is the entry point plus its run\n\n`source` is one of `cron_backfill`, `cron_incremental`, `webhook`. `run_id` is the bronze lock token for cron rows (unique per run, already in hand) and a fresh UUID per delivery for the webhook. `event_type` carries the webhook eventType and is NULL for cron rows.\n\n### Every observation, no dedupe\n\nThe daily 30-day sweep re-observes rows whose raw has not changed; those append too. `seen_at` is the fact recorded, and deduping would require the writer to read before writing. Volume at personal-account scale is tens to low hundreds of rows a day.\n\n### Seeded from the mirror\n\nThe migration copies every existing mirror row into the log with `source = 'migration_13_seed'`, so the log starts no emptier than the mirror. Pre-migration history is still lost — this record starts the archive, it cannot recover what the upserts already discarded.\n\n## Consequences\n\nBronze now meets the append-only and lineage halves of its contract: the full observation history of any id is `SELECT * FROM bronze_transactions_log WHERE id = ? ORDER BY seq`, and every row says how it got there. A full silver rebuild still reads the mirror, unchanged. Costs: the log grows without bound (a retention rule is a future record if it matters), and the mirror is still overwritten — the immutable property lives in the log, not the primary table. Change data capture as *diffs* is not provided; consumers compare consecutive log rows themselves.\n",[],[159,160,323],"4ef82995-b138-474d-8b6d-b992266f0f1c","0011-external-destinations.md","01a033dc-1a01-70c1-86c3-b5b7729098ea",["D",1787576457509],"# ADR 0011. External destinations are not spend\n\nStatus: accepted. Implemented 2026-08-24 in kevinvu184/up. Refines ADR 0005's category-spend model; same curation philosophy as ADR 0010. Amended same day: the counterparty list moved from a code constant into private data, and named institutions and figures were removed from this record — the code is public, the list is personal.\n\n## Context\n\nAfter the transfer-exclusion fix, category spend still counted large outflows that are not consumption: movements of the owner's own money to accounts outside Up — savings banks, a broker, a super fund, including a recurring auto-invest that quietly inflated every routine month. These made a handful of months look like enormous spending when the real consumption was ordinary. Two alternatives were rejected: timing heuristics that match inflows to nearby outflows (ambiguous matches misclassify silently — the failure class this project is built against), and Up app tags (no webhook fires on tag edits and the poller re-reads only 30 days, so tags on historical rows never reach bronze without a history re-walk tool).\n\n## Decisions\n\n### Curation is private data, not public code\n\nThe list of external destinations reveals which institutions the owner banks and invests with, so it cannot live in a public file. Migration 12 creates `curation_external_destinations` (transaction_type, description) — an empty shape in public code — and the spend model anti-joins against it with NOT EXISTS. The rows are inserted through the SQLite panel and exist only in the private database. Adding a new external account is a row insert, no deploy; an empty table excludes nothing, so a remix starts neutral; a NULL transaction_type never matches, so untyped rows stay counted as spend. This supersedes the first implementation, a hardcoded predicate constant, which was equivalent in behaviour (verified: identical mart totals) but leaked the institution names into public code.\n\n### Deliberately kept as spend\n\nRent paid through agents, conveyancing, and two-way person-to-person shared-expense flows all stay counted. The person-to-person flows have money moving both directions; netting them properly is a separate problem for a future record if it matters.\n\n### Exclusion now, savings-flow mart later\n\nThe curation table defines precisely the outbound half of a savings-and-investment flow metric. When savings rate needs it, a `gold_savings_flow` model anti-joins the same table inverted — one shared definition, so the two surfaces can never disagree about what \"external destination\" means.\n\n## Consequences\n\nSpend now measures consumption: mean and median monthly figures converge instead of the mean running several times the median, and the former outlier months resolve to ordinary values — most of what they held was money moving to savings. The cost is the usual curation trade: an outflow to a new, unlisted external destination counts as spend until a suspicious month prompts a row insert — the same accepted, visible failure mode as the income definition, and exactly how the original list was discovered. Personal specifics (institutions, counterparties, amounts) now live only in the private database, never in code or docs.\n",[],[159,160,331],"b1a2f8e5-2e3b-4c48-91cb-847fdcdfe4a9","0010-income-mart.md","01a033dc-1a01-70c1-86c3-b1c25d50539c",["D",1787576457509],"# ADR 0010. Income mart for the savings-rate denominator\n\nStatus: accepted. Implemented 2026-08-24 in kevinvu184/up. Builds on ADR 0003 (silver), 0005 and 0009 (gold); first production use of ADR 0003's watermark-reset rebuild. Amended same day: dollar figures and counterparty specifics originally cited in this record were removed — the codebase is public and account specifics are personal data (ADR 0011 records where curation now lives).\n\n## Context\n\nSavings rate is net saved over income. The numerator already exists (`gold_cashflow.net_cents`); the denominator did not, and the obvious definition — positive, settled, `transfer_account_id IS NULL` — is wrong on real data: a large share of inflows carry `transactionType: 'Transfer'` but no transferAccount relationship (Up leaves many internal moves relationship-untagged), and much of the rest is the owner's own money arriving from external banks. Up's `transactionType` attribute, never promoted into silver, is the discriminator: the recurring salary is exactly `transactionType: 'Salary'`, covering every month of the mirrored period, and a stray non-salary credit sharing the employer's description — which a description match would have miscounted — is excluded by type.\n\n## Decisions\n\n### Silver carries the field, gold owns the meaning\n\nMigration 11 adds `transaction_type` to silver and the transform promotes `attributes.transactionType` verbatim (optional string, same strictness rules as the other optionals). What counts as income is interpretation, so it lives only in the `gold_income` SELECT in `goldModels.ts`, consistent with the two-homes rule from ADR 0005.\n\n### A curated income definition\n\n`gold_income` (day x income_type grain, per ADR 0009) counts settled, positive, non-transfer rows where `transaction_type` is `Salary` or `Interest`, plus ATO tax refunds — Direct Credits whose `raw_text` is `'ATO'` (the wire-level payer identifier, present on every observed refund), surfaced as income_type `'Tax Refund'`. Deliberately not income: person-to-person receipts (a reimbursement pattern), incoming international payments (gifts), and remaining Direct Credits (movements of own money). Widening the definition is one edit to the WHERE clause.\n\n### Rebuild via the escape hatch\n\nExisting silver rows gained the column by deleting `silver_watermark` and letting the cron rebuild from bronze — the mechanism ADR 0003 built for exactly this, used for the first time in production, completing within one budgeted run. Gold then needed its own forced rebuild (`gold_last_run` deleted), because the restored watermark matched the value gold had already built from — the skip check working correctly, if unexpectedly.\n\n## Consequences\n\nSavings rate is now a query: `SUM(gold_cashflow.net_cents) / SUM(gold_income.income_cents)` over the same day range, rolled up with the ADR 0009 bucketing patterns; the mart reconciled against silver to the cent on rebuild day. One known caveat moves to the numerator: `net_cents` counts own-money inflows from external accounts as positive net, so periods with large external transfers in overstate the rate; tightening the numerator is a future record if it matters. The income definition is curated to the account's observed data, which is the point — a remixer edits one WHERE clause, not a taxonomy.\n",[],[159,160,338],"a92fc87b-b647-4b5f-bb80-be9e6cacfda9","0009-gold-daily-grain.md","01a033dc-1a01-70c1-86c3-ac4418822c43",["D",1787576457509],"# ADR 0009. Gold at daily grain, durations at query time\n\nStatus: accepted. Implemented 2026-08-24 in kevinvu184/up. Re-grains ADR 0005's marts and supersedes ADR 0008's monthly balance mart (the reconstruction decision there stands; only its grain changes).\n\n## Context\n\nThe monthly marts answered exactly one duration. The product on top of gold needs daily, weekly, monthly, quarterly and arbitrary custom ranges, and a mart per duration would multiply tables while still failing the custom case. The dataset is about 6,000 transactions, so a daily grain holds low thousands of rows and any rollup over it runs in milliseconds.\n\n## Decisions\n\n### Store the finest grain, derive every duration\n\nGold stores one row per UTC calendar day; a week, month, quarter or custom range is a GROUP BY over days computed by the consumer. No table stores a duration, no table name mentions one, and no schema column encodes one. Migration 10 drops the three monthly tables (gold rebuilds wholesale every run, so nothing is lost) and creates `gold_category_spend`, `gold_cashflow` and `gold_balance`, keyed on `day`.\n\n### Gold is the product's only query surface\n\nAt daily grain the spend and cashflow marts barely compress silver. They are kept anyway, because their value is the contract, not the compression: metric definitions (what counts as spend, what counts as flow, how balance is reconstructed via `BALANCE_EXPR`) live in the gold SELECTs alone, consumers including the future MCP server read gold and never silver, and silver stays free to change underneath.\n\n### Dense days for balance\n\n`gold_balance` gives every account a row for every day from its first transaction through today, carrying the balance forward through quiet days, so \"balance on day D\" never misses an inactive account. The other two marts stay sparse; absence of a row means zero activity, which is the correct reading for flows.\n\n## Consequences\n\nConsumers own bucketing. The canonical rollup patterns, so every consumer copies these rather than inventing its own:\n\n- Monthly: `GROUP BY substr(day, 1, 7)`\n- Quarterly: group by year and `(CAST(substr(day, 6, 2) AS INTEGER) + 2) / 3`\n- Weekly: `GROUP BY strftime('%Y-W%W', day)`, noting %W weeks start Monday and week 0 exists; use with care around year boundaries\n- Custom range: `WHERE day BETWEEN ? AND ?`\n- Balance at a boundary: the `gold_balance` row at `MAX(day) <= boundary` per account; net worth at a boundary is the sum of those rows\n\nDay boundaries are UTC, so a Melbourne evening purchase lands on the next calendar day; this was an accepted blur at monthly grain and is more visible at daily grain. It remains accepted and fixable in one place, the bucketing expressions in `models/goldModels.ts`. The balance spine grows by one row per account per day, roughly a thousand rows per account per three years, which the gold cron budget absorbs without change.\n",[],[159,160,345],"bca42120-76ec-4af0-bcfd-1bbb330ee69d","0008-balance-reconstruction.md","01a033dc-1a01-70c1-86c3-ab3aa6629a21",["D",1787576457509],"# ADR 0008. Balance reconstruction from transactions\n\nStatus: accepted. Implemented 2026-08-24 in kevinvu184/up. Builds on ADR 0005 (gold layer) and consumes ADR 0003's silver contract. The monthly mart described here is superseded by ADR 0009's daily grain; the reconstruction decision itself stands.\n\n## Context\n\nThe Up API has no balance history endpoint: `GET /accounts` returns each account's current balance only, so any balance-over-time capability must be manufactured. Reconciling live balances against silver sums on 2026-08-24 established the ground truth for how Up represents balance changes. Every change is a transaction except round-ups, which Up records asymmetrically: the debit on the source account is metadata on the purchase (`roundUp` field, no transaction of its own) while the credit on the receiving Saver is a real \"Round Up\" transaction. Summing `amount_cents` alone therefore materially overstated the round-up-enabled account, while accounts without round-ups reconciled to the cent. Adding the round-up metadata back closed nearly the whole gap; a small residue remained, attributed to a closed Saver, because closed accounts vanish from `/accounts` and their transactions vanish from the transaction feed.\n\n## Decisions\n\n### Reconstruct, do not sync\n\nBalances derive from data already in silver rather than from a new accounts sync: the running sum of `amount_cents + COALESCE(round_up_cents, 0)` over SETTLED rows, per account. The silver schema already carried `round_up_cents`, so no new ingestion, no new entity, and no new cron were needed. The approximation this buys is accepted; the reconstruction is an estimate, not a ledger.\n\n### One calculation site\n\nThe expression lives once, as `BALANCE_EXPR` in `models/goldModels.ts`. The monthly balance mart builds its SELECT from it and `tools/balanceCheck.ts` reads the mart's output rather than recomputing, so no second copy of the arithmetic can drift.\n\n### A dense monthly mart\n\n`gold_monthly_balance` (migration 9) holds one row per month per account, from each account's first transaction month through the current month, with the balance carried forward through months that had no transactions. A sparse mart would make \"net worth in month M\" silently miss any account inactive that month. It rebuilds on the existing gold cron through the same DELETE plus INSERT pattern as the other marts; months bucket on `created_at_utc` as UTC `YYYY-MM`, consistent with them.\n\n## Consequences\n\nNet worth and per-account balance history become one-row reads over gold, at the cost of approximation: accounts with round-up history carry a small fixed residue, and net worth before a closed account's closure understates by whatever that account held at the time, which the API can never return. `tools/balanceCheck.ts` doubles as a drift detector, comparing the mart's latest month against live balances; a delta that moves between runs means a new kind of non-transactional balance change has appeared. Month boundaries are UTC, so figures near the 1st of a month shift relative to Melbourne local months, the same accepted limit as the other gold marts. Should exact balances ever be needed, the upgrade path is an accounts snapshot sync that anchors the curve forward without replacing the historical reconstruction.\n",[],[159,160,352],["D",1787576457509],[],[159,160,70],"7cccbbcf-9580-4d52-b51a-38039ebd7fe0","0006-layer-naming-scheme.md","01a033dc-1a01-70c1-86c3-a2a692572d52",["D",1787576457509],"# ADR 0006. Layer naming scheme\n\nStatus: accepted. Implemented 2026-08-24 in kevinvu184/up. Renames artifacts from ADR 0001 through 0004; those records keep their original names as history.\n\n## Context\n\nThe codebase had grown asymmetric names. The first cron and its state were unprefixed legacy (`poller.cron.ts`, `lock`, `last_sync_at`, `last_run_rows`, `makeRunState`), later jobs got verb prefixes (`promote_*`, and `champion_*` planned), while the tables and row types were layer-named all along (`bronze_transactions`, `SilverRecord`). Two candidate schemes were on the table: name everything by job verb (`poll_*`, `promote_*`, `champion_*`) or by medallion layer (`bronze_*`, `silver_*`, `gold_*`).\n\n## Decisions\n\n### The layer scheme\n\nTables and row types cannot sensibly become verbs, so the verb scheme would leave the data side permanently inconsistent. The layer scheme makes every artifact line up behind the layer it belongs to, and each cron maps one-to-one onto the layer it produces: `bronze.cron.ts` fills `bronze_transactions`, `silver.cron.ts` fills `silver_transactions` via `silverTransform.ts`, `gold.cron.ts` fills the `gold_*` tables via `goldModels.ts`.\n\n### What carries a layer prefix and what does not\n\nLayer-owned artifacts take the prefix: the three crons, the two interpretation files, the state factories (`makeBronzeState`, `makeSilverState`, `makeGoldState`), the stores (`bronzeStore`, `silverStore`, `rebuildGoldModel`, and the three lock stores). Shared infrastructure keeps concern names, because it belongs to no layer: `upClient.ts`, `lock.ts`, `state.ts`, `migrations.ts`, `sqliteStore.ts`.\n\n### Symmetric meta keys\n\nEvery layer now records the same key shapes: `_lock` (seeded by migrations), `_last_run` (overwritten on success), `_last_error` (written on failure, cleared on the next success), plus one progress key where the layer needs one (`bronze_cursor`, `silver_watermark`; gold's skip memory lives inside `gold_last_run`). The old poller pair `last_sync_at` plus `last_run_rows` merged into a single `bronze_last_run` record so all three layers read alike in the panel.\n\n### The rename shipped as migration 5, not as edits to old migrations\n\nThe decisive fact, discovered by reading the live database before touching anything: the pipeline had already run. Schema stood at version 4, backfill had reached its sentinel, and the seeded lock rows existed under their old names. Rewriting the seed statements inside migrations 1 and 4 would have left the live rows orphaned and silently broken locking, because an applied migration never runs again. So migrations 1 through 4 are frozen exactly as applied, and migration 5 renames the live keys: UPDATE for each rename, one INSERT SELECT to merge the two poller keys into `bronze_last_run`, then a DELETE of the merged pair. A fresh database reaches the same end state by seeding the old names and renaming them, which is what a remixed val does on its first run.\n\n## Consequences\n\nNaming is now mechanical: given any artifact, its prefix says which layer owns it, and given a layer, the full set of its artifacts is predictable. The general principle this change hardened is worth stating on its own: applied migrations are frozen, and history only grows by appending. The ADRs before this one keep the old names in their text; they were true when written, and this record is the bridge. The one residual asymmetry is deliberate: shared infrastructure stays unprefixed, because forcing a layer name onto a module every layer uses would be a lie.\n",[],[159,160,362],"f5fa7c4e-4b7f-4a74-a572-dd453d705df6","0005-gold-layer.md","01a033dc-1a01-70c1-86c3-9d8c34fc9b72",["D",1787576457509],"# ADR 0005. Gold layer of aggregate tables\n\nStatus: accepted. Implemented 2026-08-24 in kevinvu184/up. Builds on ADR 0003 and 0004; uses the naming scheme of ADR 0006. Marts re-grained to daily by ADR 0009. The schedule details in Consequences are historical: silver and gold now run quarter-hourly (silver :03/:18/:33/:48, gold :08/:23/:38/:53), so a transaction reaches gold within about 20 minutes of landing in bronze. Amended 2026-08-24 (second): the category-spend transfer exclusion gained a transaction_type filter (Transfer, Scheduled Transfer, Round Up) — the transferAccount relationship alone let a large volume of relationship-untagged internal transfers count as spend; see ADR 0010's context and models/goldModels.ts.\n\n## Context\n\nSilver answers \"what happened\", one typed row per transaction. The questions worth asking daily are aggregates: spend by category per month, cashflow per account per month. This change adds a gold layer for them, rebuilt from silver by a third cron, `gold.cron.ts`, hourly at :45. dbt was explored as the tool for this layer and the exploration needed an honest verdict.\n\n## Decisions\n\n### dbt as a discipline, not a dependency\n\ndbt-core does not fit this runtime: it is a Python CLI that needs an adapter with direct database access, vals run Deno, and the val-scoped SQLite is reachable only through the std/sqlite HTTP module, which no dbt adapter targets. Val Town also does not expose the val database to external tools, so running dbt outside the platform is not an option either. What survives the exploration is dbt's ideas: SQL-first models, one uniform materialisation wrapper the models never see, idempotent rebuilds. If the warehouse ever moves to a user-owned Turso or Postgres, the model SELECTs port to real dbt nearly unchanged, and that portability costs nothing to keep.\n\n### Every metric lives in one SQL file\n\n`goldModels.ts` exports a list of `{ name, table, select }`, pure data. Each gold table is one declarative SELECT over silver; the store wraps every model in the same DELETE plus INSERT INTO SELECT. A change to what a metric means edits one SELECT; a new gold table is one list entry plus one migration. The single coupling, stated at the top of the file: each SELECT's column order matches its table's column order, because the runner inserts positionally.\n\n### Two starter models, opinionated\n\nMonthly category spend counts settled, negative-amount, non-transfer rows, stored as positive cents. Monthly per-account cashflow counts settled rows with transfers included, because per-account flow is the point and transfers net out across accounts. Months bucket on `created_at_utc` as UTC YYYY-MM: exact Melbourne months would need daylight-saving rules in SQL, and a fixed +10 offset misclassifies hours around month boundaries. Accepted limit, fixable as a model edit.\n\n### Full rebuild, atomically per model\n\nAggregates over a personal account are small, so each run rebuilds whole tables: DELETE plus INSERT in one transaction per model, meaning readers see the old table or the new one, never a half-built one. This removes all incremental bookkeeping from the layer, and makes gold disposable the way silver is: nothing exists only in gold.\n\n### Skip when silver has not moved\n\nThe gold run records the `silver_watermark` it built from inside `gold_last_run`. The next trigger compares and exits before touching gold when the watermark is unchanged, so the hourly schedule is free on quiet hours. Deleting `gold_last_run` forces a rebuild. A run that exhausts its budget mid-list fails loudly rather than recording a success that covers models it never rebuilt; each table already rebuilt is individually consistent and the next hour retries the whole list.\n\n### Migration 6 through the existing runner\n\nBoth tables and the `gold_lock` row arrive as the sixth numbered migration. The runner did not change.\n\n## Consequences\n\nA transaction reaches gold within about an hour of landing in bronze (sync daily 18:00 UTC, silver :30, gold :45). Interpretation now has exactly two homes: row meaning in `silverTransform.ts`, metric meaning in `goldModels.ts`; the crons and store stay rule-free. The gold cron follows the established pattern (own lock, own state keys, no cross-job coordination), so the layer added one composition root and one pure module and touched nothing else.\n",[],[159,160,369],"76178624-38c9-48ff-8af6-a35968484a2b","0004-promote-cron-split.md","01a033dc-1a01-70c1-86c3-9adf5160b3ed",["D",1787576457509],"# ADR 0004. Promotion runs on its own cron\n\nStatus: accepted. Implemented 2026-08-24 in kevinvu184/up. Amends ADR 0003.\n\n## Context\n\nAs first built, silver promotion ran as step 4 of the daily poller. The module-level separation held (pure transform, I/O at the edges, one composition root), but the run-level coupling had two concrete defects. First, entangled failure semantics: a transform rejection threw, so the run wrote `last_error` and never wrote `last_sync_at`, reporting a failed sync even when backfill and the incremental window had succeeded and committed their pages. Second, budget contention: promotion competed with backfill for the same 45 seconds, and during backfill that is the wrong trade, because syncing from the rate-limited external API is the scarce work while promotion reads local data and can run any time.\n\n## Decisions\n\n### A separate promote cron\n\n`promote.cron.ts` derives silver from bronze on its own hourly schedule; the poller goes back to syncing only. Each cron is its own composition root with the same shape as before: migrate, lock, work in budget-checked batches, record state, release. The poller's success record returns to backfill and incremental counts.\n\n### No coordination between the two crons\n\nThe promoter needs no mutual exclusion against the poller, only against itself. Three properties already in place make this true: the promoter reads only committed bronze rows, silver upserts are idempotent, and each silver batch lands its rows and watermark in one transaction. Anything the poller commits mid-promotion is picked up on the promoter's next pass. Nothing new was built to make the split safe; the split is cheap because ADR 0002 and 0003 paid for it.\n\n### Per-job lock rows\n\nThe promoter gets its own seeded lock row, `promote_lock`, via migration 4. The lock store is parameterised by key (`makeLockStore(key)`) instead of hardcoding one row; the lock module itself did not change, since LockDeps never named the row. Overlapping manual triggers of the promoter exclude each other exactly as the poller's do, with the same TTL recovery.\n\n### Per-job state keys\n\nThe promoter records `promote_last_run` (timestamp and rows promoted) and `promote_last_error`, with the same reset semantics as the poller's keys. Without this, the two jobs would clobber a shared `last_error`, and the panel could not say which job failed. In code, the run-state factory split the same way: `makeRunState` serves the poller, `makePromoteState` serves the promoter, and neither carries the other's methods.\n\n### Both crons migrate first\n\nEach cron runs the migration runner at the top, because either may fire first after a deploy that adds a migration. The runner is versioned, so the second arrival applies nothing. Two crons applying the same pending migration at the same instant would fail loudly on the second CREATE or INSERT, which is the project's preferred failure mode and self-heals on the next trigger.\n\n## Consequences\n\nSync failures and promotion failures now report independently: a transform rejection halts promotion and writes `promote_last_error` while the poller keeps syncing and `last_sync_at` stays truthful. Silver lag drops from a day to at most an hour. The costs are two schedules and two log streams to keep in mind, and one more seeded row in `meta`. The module layout is unchanged; only the composition roots multiplied, which is what composition roots are for.\n",[],[159,160,376],"164888d0-a51f-44f5-a160-7729f28dc0f6","0003-silver-layer.md","01a033dc-1a01-70c1-86c3-977defb783ea",["D",1787576457509],"# ADR 0003. Silver layer of typed transactions\n\nStatus: accepted. Implemented 2026-08-24 in kevinvu184/up. Builds on ADR 0002; amended by ADR 0004, which moves promotion to its own cron. Amended 2026-08-24: the same-timestamp stall guard described under \"Coarse watermark\" now fails the run loudly instead of exiting cleanly — a cluster at or above the batch size would repeat the identical read forever and the pre-check would then hide it, so it throws; see jobs/silver.cron.ts.\n\n## Context\n\nBronze holds raw JSON, which answers \"what did Up say\" but not \"what can I query\". This change derives a `silver_transactions` table of cleaned, typed records. The constraints are unchanged: one production database, no staging, the 45 second budget, and the project's rule that quiet corruption is the failure class to design against.\n\n## Decisions\n\n### Silver is derived and disposable\n\nBronze is the source of record; silver holds nothing that exists only in silver. A single `meta` key, `silver_watermark`, tracks the highest bronze `last_seen_at` already transformed, and deleting that key rebuilds silver from all of bronze on the next run. This is the escape hatch for every future interpretation change: no data migration, just reset and rerun. It is safe precisely because the transform is pure and the upserts are idempotent.\n\n### One pure transform file owns all interpretation\n\n`transform.ts` exports `toSilver(id, raw)`, a pure function whose only inputs are its arguments and whose only output is its return value: no sqlite, no fetch, no clock. It returns the typed record or a rejection naming the field that failed and why. The cron holds no transform logic, the store holds no interpretation, so a change to what silver means edits this file and the schema migration and nothing else. Purity also made it testable before any database existed.\n\n### Typed means typed, with two cleaning rules baked in\n\nMoney comes from `valueInBaseUnits` only, stored as signed integers; the decimal `value` string is never parsed, and floats never touch money. Timestamps are normalised to UTC ISO strings at transform time, because Up sends mixed +10:00 and +11:00 offsets across daylight saving and mixed offsets break lexicographic ordering in SQLite. Required fields (account, status, description, amount, currency, createdAt) reject when missing or wrongly typed; optional fields tolerate absence but reject malformation. A rejection is never a default.\n\n### Fail loud, lose nothing\n\nA row the transform rejects fails the run through the existing error path rather than landing in a quarantine table. Bronze keeps the raw, so the cost of a rejection is a rerun after a code fix, never data. A quarantine table was considered and dropped: it is more machinery for a failure mode a single-developer pipeline handles by reading `last_error`, which carries the exact id and field.\n\n### Coarse watermark with atomic batches\n\nEach pass selects bronze rows with `last_seen_at >= watermark`, transforms them, and lands each batch as one transaction: the upserts plus the watermark advance together, the same rule as bronze pages. The `>=` comparison re-transforms rows at the boundary timestamp on the next pass; a bronze page shares one timestamp and holds at most 100 rows, upserts are idempotent, so the rework is bounded and free, and keyset pagination on a compound cursor would buy nothing but complexity. The batch size (500) sits above any same-timestamp cluster so the watermark can always advance, and a stall guard exits cleanly if it ever cannot.\n\n### Migration 3 through the existing runner\n\nThe table arrives as the third numbered migration, applied atomically with its version write. The runner did not change.\n\n## Consequences\n\nSilver is queryable with correct ordering and integer money, and every interpretation question has one home. A transform rejection halts promotion until the code is fixed, which is deliberate: the alternative is a silver table that silently disagrees with bronze. As first built, promotion ran as a fourth step inside the poller; ADR 0004 records why that placement was wrong and where it moved.\n",[],[159,160,383],"ea44797c-bceb-43d4-9b18-fb9d5b29ddd0","0002-bronze-table.md","01a033dc-1a01-70c1-86c3-9211aeabd6fd",["D",1787576457509],"# ADR 0002. Bronze table for raw transactions\n\nStatus: accepted. Implemented 2026-08-24 in kevinvu184/up. Builds on ADR 0001.\n\n## Context\n\nThe poller from ADR 0001 walked the account and discarded the records: backfill advanced a cursor, the incremental window counted. This change keeps the records in a `bronze_transactions` table so later steps can read them. Both walks revisit records by design (backfill refetches at most one page after a kill, and the 30 day window re-reads the same records every day), and Up mutates records in place when a HELD transaction settles. The run budget, lock, cursor, and single production database from ADR 0001 all still apply.\n\n## Decisions\n\n### Bronze means raw, with minimal promoted columns\n\nEach row stores the transaction resource as JSON in a `raw` column, plus only what the write path needs: the Up transaction id as primary key, `created_at` for ordering, and `first_seen_at` / `last_seen_at`. Nothing else is promoted. Status, amounts, and categories stay inside `raw` for a later silver step to read, because promoting them now bakes interpretation into bronze and forces a migration every time the interpretation changes. `raw` is the record re-serialised from the parsed page, not the wire bytes; the API is JSON and key order carries no meaning.\n\n### Upsert on id rather than append-only\n\nA revisited record updates its existing row: `raw`, `created_at` and `last_seen_at` replace, `first_seen_at` keeps its original value. Append-only bronze would grow a copy of every record on every daily window pass and would need dedup logic downstream. Upsert with latest raw winning fits how Up behaves, since a HELD transaction settles by mutation of the same id, and a history of HELD states is out of scope. If that history ever matters it is a different schema, not a change to this one.\n\n### Atomic page writes\n\nThe page is the unit of durability. Each consumed page lands as one batch transaction: the upserts plus, during backfill, the cursor advance; the final page of history lands together with the `done` sentinel the same way. The previous design persisted the cursor as a separate write after counting, which allowed a kill between the two writes to leave a cursor pointing past rows that were never stored. That failure mode is now impossible by construction rather than merely unlikely.\n\n### A malformed resource fails the page\n\nA resource missing its `id` or `attributes.createdAt` turns the whole page into an error result instead of being skipped. A silent skip would corrupt bronze quietly, and quiet corruption is the exact failure class this project is designed against. The error path from ADR 0001 (write `last_error`, release the lock, TTL as backstop) handles it unchanged.\n\n### Migration 2 through the existing runner\n\nThe table arrives as the second numbered migration, applied atomically with its version write. This is the case the runner was built for; the runner itself did not change.\n\n## Consequences\n\nWrites spend the same 45 second budget as fetches and backoff, bounded at one storage round trip per page, so a large account's backfill spans several runs with the cursor carrying progress between them. The `last_run_rows` key now counts rows upserted per run and stays the liveness signal. One accepted limit: a mutation on a record older than the 30 day window is never picked up, so bronze keeps the last raw seen inside the window; HELD states resolve within days, so widening the window is deferred until it matters. Silver reads `raw` and owns all interpretation; nothing in the write path changes when the interpretation does.\n",[],[159,160,390],"487d7f6f-8c10-4089-8230-c483a35ea66a","0001-up-transaction-poller.md","01a033dc-1a01-70c1-86c3-8d902b461092",["D",1787576457509],"# ADR 0001. Up transaction poller\n\nStatus: accepted. Implemented 2026-08-24 in kevinvu184/up.\n\n## Context\n\nA daily cron walks a real Up bank account over the Up API and writes the only copy of its own state. The runtime is the Val Town free tier, which kills a run at 60 seconds. There is one production SQLite database, no staging, and branches of the val share that same database. A wrong cursor or a stuck lock fails silently, so the design ranks correctness above speed and maintainability above cleverness.\n\n## Decisions\n\n### Pure core, one I/O edge\n\nModules that make decisions never touch sqlite, fetch, or the system clock. Each declares a narrow interface for what it needs (a lock store, a key-value store, now()) and the cron file poller.cron.ts is the composition root, the only place real sqlite, real fetch, and Date.now are wired in. It computes the run deadline once and passes it down. sqliteStore.ts is the single file that imports sqlite, so a storage or schema change touches one file. The correctness-bearing logic (lock expiry, cursor advance, budget cutoff, backoff against deadline) stays small and checkable in isolation.\n\n### One key-value table, versioned migrations\n\nAll state lives in a `meta` table of key-value pairs, created by a versioned migration runner. Each numbered migration and its version write land in one batch transaction, so a run killed mid-migration leaves either the old schema or the new one, never a half-applied step that a rerun trips over. The runner exists for the second schema change, not the first: with one production database, every later change must be a numbered step that runs exactly once, and the `schema_version` key makes the schema state readable in the panel and exposes a branch and database mismatch.\n\nThe version read treats \"no such table: meta\" as version 0, because migration 1 creates the table the version lives in. That is the only swallowed error in the codebase. Any other fault on any other statement surfaces, so a real database problem never reads as \"already applied\".\n\n### TTL lock via one conditional update\n\nThe lock is a JSON value of expiry and holder token on a row that migration 1 seeds. Acquire and release are each a single conditional UPDATE judged by rowsAffected, so atomicity lives in SQL and two concurrent runs cannot both see the lock as free. A run that loses the race exits without touching the cursor; the poller never waits. The TTL is 5 minutes because it is the real safety mechanism: a run killed at the 60 second wall never executes its release path, and 5 minutes clears the wall while recovering the lock long before the next daily trigger.\n\n### 45 second budget, cursor persisted per page\n\nThe run budgets 45 of the 60 available seconds. The budget is wall-clock and shared with backoff sleeps: the Up client takes the deadline, and if a computed wait (including an honoured Retry-After) would cross it, the client returns an out-of-budget result instead of sleeping, so a 429 with a 30 second Retry-After ends the run cleanly rather than hitting the kill wall. The backfill cursor is persisted after every consumed page, so a killed run resumes with at most the one in-flight page refetched.\n\n### Backfill on Up's cursor links, sticky done sentinel\n\nUp returns transactions newest first and paginates through opaque prev and next links, so the backfill follows links.next toward the start of history and stores the next-page URL as the cursor. When next is null, the cursor becomes the sentinel `done`. Every other state key has reset semantics that clear it on the next relevant run, but the sentinel never resets: a cleared cursor would be indistinguishable from \"never started\" and would restart the backfill from scratch. The exception is commented in the code because it breaks the stated pattern on purpose.\n\n### Incremental window runs second\n\nAfter backfill, the run pages through the last 30 days with whatever budget remains and counts transactions seen. The count is the liveness signal in the panel, proof the token works and the account is reachable, and the seam where a future consumer of stored transactions plugs in. It runs second because backfill is finite and the window repeats daily; a fat window must never starve the backfill.\n\n### Pinned imports\n\nThe storage import is pinned to an explicit version (std/sqlite at v31, the main branch version at build time). The web editor has no lockfile, so the pin is the only version protection. Bumps are deliberate edits, never implicit.\n\n### Migration before lock\n\nThe run migrates before acquiring the lock, deviating from the drafted run shape, because migration 1 creates and seeds the very row the lock's conditional UPDATE targets. The deviation is commented at the call site.\n\n## Consequences\n\nOn failure the run writes a `last_error` key with a timestamp and releases the lock, both best effort: if the fault is sqlite itself, both writes fail, the TTL recovers the lock, and the run logs are the record. Retry waits consume the same budget as page fetches, so a rate-limited run makes less backfill progress rather than risking the kill wall. Branch experiments still run against the live database; the schema version key makes that detectable but not safe. Any future table arrives as migration 2 through the same runner, and any future consumer of transactions plugs into the incremental window without changing the run shape.\n",[],[159,160,397],"d15b5f0d-cb97-4286-bb5f-191af2fd96b5","01a033dc-1a01-70c1-86c3-c178eb11a79c",["D",1787576457509],[180,181,182,183,184,185,186,187,188,189,190,191,192,193],[159,160],"68162b01-c8d4-4f64-b78a-0cfe9435c505",["D",1787576457509],[179],[159],"30ae9390-7364-4e42-8233-0fb6ce7eb1a1","upClient.ts","01a033dc-1a01-70c1-86c3-8660b3a1aefa","01a033dc-1a01-70c1-86c3-89433252479b",["D",1787576457509],"/**\n * Up API client: typed responses, cursor pagination, single-resource\n * fetches, and a request wrapper with exponential backoff on 408, 425,\n * 429 and 5xx, honouring Retry-After. Knows nothing about the database.\n *\n * Takes the run deadline: the budget is wall-clock and shared with backoff\n * sleeps, so if a computed wait would cross the deadline the wrapper\n * returns a clean \"out of budget\" result instead of sleeping. A 429 with a\n * 30-second Retry-After must end the run cleanly, not blow through the\n * caller's kill wall.\n *\n * Up API notes (developer.up.com.au/llms.txt):\n * - GET /transactions returns newest first; following links.next pages\n * towards the start of history. Pagination uses opaque cursor links —\n * always follow them rather than rebuilding query params.\n * - filter[since]/filter[until] narrow the window but must not be used for\n * pagination; the links preserve the filters.\n * - GET /transactions/{id} retrieves one transaction; webhook events carry\n * only the id, so this is how event payloads become full resources.\n */\n\nexport interface UpClientDeps {\n fetchFn: typeof fetch;\n now: () => number;\n sleep: (ms: number) => Promise;\n}\n\ninterface UpTransactionsPage {\n data: unknown[];\n links: { prev: string | null; next: string | null };\n}\n\ninterface UpSingleResource {\n data: unknown;\n}\n\n/**\n * One raw resource extracted from a response: the Up id, createdAt for\n * ordering, and the resource re-serialised as JSON (not the wire bytes;\n * the API is JSON and key order carries no meaning).\n */\nexport interface UpRecord {\n id: string;\n createdAt: string;\n raw: string;\n}\n\nexport type PageResult =\n | { kind: \"page\"; count: number; nextUrl: string | null; records: UpRecord[] }\n | { kind: \"out_of_budget\" }\n | { kind: \"error\"; detail: string; status?: number };\n\nexport type RecordResult =\n | { kind: \"record\"; record: UpRecord }\n | { kind: \"out_of_budget\" }\n | { kind: \"error\"; detail: string; status?: number };\n\ntype JsonResult =\n | { kind: \"json\"; body: unknown }\n | { kind: \"out_of_budget\" }\n | { kind: \"error\"; detail: string; status?: number };\n\nexport const UP_BASE_URL = \"https://api.up.com.au/api/v1\";\n\nconst RETRYABLE_STATUSES = new Set([408, 425, 429, 500, 502, 503, 504]);\nconst MAX_ATTEMPTS = 5;\nconst BASE_DELAY_MS = 1_000;\nconst MAX_DELAY_MS = 16_000;\n\n/** First page of the full-history walk (newest first; next = older). */\nexport function backfillStartUrl(pageSize: number): string {\n const u = new URL(`${UP_BASE_URL}/transactions`);\n u.searchParams.set(\"page[size]\", String(pageSize));\n return u.toString();\n}\n\n/** First page of the incremental window. */\nexport function incrementalStartUrl(pageSize: number, sinceIso: string): string {\n const u = new URL(`${UP_BASE_URL}/transactions`);\n u.searchParams.set(\"page[size]\", String(pageSize));\n u.searchParams.set(\"filter[since]\", sinceIso);\n return u.toString();\n}\n\nexport function makeUpClient(deps: UpClientDeps, token: string, deadlineMs: number) {\n const { fetchFn, now, sleep } = deps;\n\n /** No sleep may run past the deadline. */\n async function waitWithinBudget(waitMs: number): Promise {\n if (now() + waitMs >= deadlineMs) return false;\n await sleep(waitMs);\n return true;\n }\n\n /** The one retrying request path; everything else parses its result. */\n async function requestJson(url: string): Promise {\n for (let attempt = 0; attempt < MAX_ATTEMPTS; attempt++) {\n if (now() >= deadlineMs) return { kind: \"out_of_budget\" };\n\n let res: Response;\n try {\n res = await fetchFn(url, {\n headers: { Authorization: `Bearer ${token}` },\n });\n } catch (_networkErr) {\n // Network fault: retry like a 5xx.\n if (!(await waitWithinBudget(backoffMs(attempt)))) {\n return { kind: \"out_of_budget\" };\n }\n continue;\n }\n\n if (res.ok) {\n try {\n return { kind: \"json\", body: await res.json() };\n } catch (_bodyErr) {\n // An OK status with an unparseable body is a truncated or garbled\n // response: retry it like a network fault rather than throwing\n // past the Result contract.\n if (!(await waitWithinBudget(backoffMs(attempt)))) {\n return { kind: \"out_of_budget\" };\n }\n continue;\n }\n }\n\n if (!RETRYABLE_STATUSES.has(res.status)) {\n return {\n kind: \"error\",\n detail: `HTTP ${res.status}: ${await safeBodySnippet(res)}`,\n status: res.status,\n };\n }\n\n // Retryable: honour Retry-After when present, else exponential backoff.\n const wait = retryAfterMs(res, now) ?? backoffMs(attempt);\n if (!(await waitWithinBudget(wait))) return { kind: \"out_of_budget\" };\n }\n return {\n kind: \"error\",\n detail: `retries exhausted after ${MAX_ATTEMPTS} attempts: ${url}`,\n };\n }\n\n async function fetchPage(url: string): Promise {\n const res = await requestJson(url);\n if (res.kind !== \"json\") return res;\n const body = res.body as UpTransactionsPage | null;\n if (body === null || !Array.isArray(body.data)) {\n // A body without a data array is malformed at the page level: an\n // error result, not a throw — same contract as the per-resource check.\n return { kind: \"error\", detail: `response body has no data array: ${url}` };\n }\n const records: UpRecord[] = [];\n for (const resource of body.data) {\n const extracted = extractRecord(resource);\n if (extracted === null) {\n // A malformed resource is an error result, not a skipped record:\n // a silent skip would corrupt bronze quietly.\n return {\n kind: \"error\",\n detail: `malformed resource (missing id or attributes.createdAt): ${url}`,\n };\n }\n records.push(extracted);\n }\n return {\n kind: \"page\",\n count: records.length,\n nextUrl: body.links?.next ?? null,\n records,\n };\n }\n\n /** One transaction by id; the webhook's read path. */\n async function fetchTransaction(id: string): Promise {\n const url = `${UP_BASE_URL}/transactions/${encodeURIComponent(id)}`;\n const res = await requestJson(url);\n if (res.kind !== \"json\") return res;\n const body = res.body as UpSingleResource | null;\n const extracted = extractRecord(body?.data);\n if (extracted === null) {\n return {\n kind: \"error\",\n detail: `malformed resource (missing id or attributes.createdAt): ${url}`,\n };\n }\n return { kind: \"record\", record: extracted };\n }\n\n return { fetchPage, fetchTransaction };\n}\n\nfunction extractRecord(resource: unknown): UpRecord | null {\n const r = resource as { id?: unknown; attributes?: { createdAt?: unknown } };\n const id = typeof r?.id === \"string\" ? r.id : null;\n const createdAt = typeof r?.attributes?.createdAt === \"string\"\n ? r.attributes.createdAt\n : null;\n if (id === null || createdAt === null) return null;\n return { id, createdAt, raw: JSON.stringify(resource) };\n}\n\nfunction backoffMs(attempt: number): number {\n return Math.min(BASE_DELAY_MS * 2 ** attempt, MAX_DELAY_MS);\n}\n\n/** Retry-After may be delta-seconds or an HTTP date. */\nfunction retryAfterMs(res: Response, now: () => number): number | null {\n const header = res.headers.get(\"Retry-After\");\n if (header === null) return null;\n const secs = Number(header);\n if (Number.isFinite(secs)) return Math.max(0, secs * 1_000);\n const dateMs = Date.parse(header);\n return Number.isFinite(dateMs) ? Math.max(0, dateMs - now()) : null;\n}\n\nasync function safeBodySnippet(res: Response): Promise {\n try {\n return (await res.text()).slice(0, 200);\n } catch {\n return \"(unreadable body)\";\n }\n}\n",[],[420,413],"core","ad7baef0-3082-4d0b-9732-a68f9b5cc349","state.ts","01a033dc-1a01-70c1-86c3-81a08424f20e",15,["D",1787630417007],"/**\n * Run state: owns the named `meta` keys, their reset semantics, and the\n * batch-write semantics for the bronze and silver tables. Decision logic\n * only: talks to the injected stores, never to sqlite.\n *\n * One factory per layer, symmetric by construction: makeBronzeState,\n * makeSilverState, makeGoldState. Each job records to its own keys\n * (_last_run, _last_error) so their panels and error paths\n * never clobber each other.\n */\n\nimport type { SilverRecord } from \"../models/silverTransform.ts\";\n\nexport interface MetaStore {\n get(key: string): Promise;\n set(key: string, value: string): Promise;\n delete(key: string): Promise;\n}\n\n/** One raw transaction ready to land in bronze. */\nexport interface BronzeRow {\n id: string;\n createdAt: string;\n raw: string;\n seenAt: string;\n}\n\n/**\n * Provenance for a bronze write (ADR 0012): which entry point observed\n * the rows and which run did it. Lands in bronze_transactions_log, never\n * in the mirror.\n */\nexport interface BronzeWriteMeta {\n source: \"cron_backfill\" | \"cron_incremental\" | \"webhook\";\n runId: string;\n /** Webhook eventType; null for cron rows. */\n eventType: string | null;\n}\n\nexport interface BronzeStore {\n /**\n * Upsert the page's rows into the mirror, append one log row per row,\n * and, when cursorAfter is given, advance the bronze cursor — all in\n * ONE transaction. Rows, log and cursor land together or not at all:\n * the cursor must never point past a page whose rows are absent, and\n * the log must never lack an observation the mirror has. Upsert\n * semantics on the mirror: `raw`, `created_at` and `last_seen_at`\n * replace on conflict; `first_seen_at` keeps its original value. The\n * log is insert-only.\n */\n writePage(rows: BronzeRow[], cursorAfter: string | null, meta: BronzeWriteMeta): Promise;\n}\n\n/** One bronze row toSilver refused, with the reason it gave (ADR 0013). */\nexport interface SilverReject {\n id: string;\n reason: string;\n lastSeenAt: string;\n}\n\nexport interface SilverStore {\n /**\n * Upsert the batch's typed records, apply the batch's deletes, record\n * the batch's rejects, and advance the silver watermark in ONE\n * transaction. On conflict the whole row replaces; silver keeps no\n * observation history of its own, bronze owns that. Delete ids not\n * present in silver are no-ops. Every id in `records` or `deleteIds`\n * is cleared from silver_rejects; every id in `rejects` is upserted\n * there with its attempt count bumped. A rejected id that already has\n * a silver row keeps it (stale but present) — the reject row is the\n * flag.\n */\n writeBatch(\n records: SilverRecord[],\n deleteIds: string[],\n rejects: SilverReject[],\n watermarkAfter: string,\n nowIso: string,\n ): Promise;\n}\n\n/**\n * Named keys in `meta`, symmetric per layer: a lock (seeded by\n * migrations, owned by the lock store), a last_run record overwritten on\n * every success, a last_error written on failure and cleared on the next\n * success, and one progress key where the layer needs one.\n *\n * Progress keys:\n * - bronze_cursor: advances per page, atomically with that page's rows.\n * EXCEPTION (deliberate): the `done` sentinel never resets — see\n * writeBackfillPage.\n * - silver_watermark: highest bronze last_seen_at already transformed;\n * advances atomically with each silver batch. Deleting it rebuilds\n * silver from all of bronze on the next run.\n * - gold has no progress key: the input watermark inside gold_last_run is\n * its skip memory, and deleting gold_last_run forces a rebuild.\n *\n * The webhook records bronze_webhook_last_event on every delivery and\n * bronze_webhook_last_error on failure, cleared by the next success. It\n * has no lock and no progress key: no exclusive work, no position to\n * track.\n *\n * (`schema_version` is owned by migrations.ts; the three `_lock`\n * keys by the parameterised lock store.)\n */\nexport const KEYS = {\n bronzeCursor: \"bronze_cursor\",\n bronzeLastRun: \"bronze_last_run\",\n bronzeLastError: \"bronze_last_error\",\n bronzeWebhookLastEvent: \"bronze_webhook_last_event\",\n bronzeWebhookLastError: \"bronze_webhook_last_error\",\n silverWatermark: \"silver_watermark\",\n silverLastRun: \"silver_last_run\",\n silverLastError: \"silver_last_error\",\n goldLastRun: \"gold_last_run\",\n goldLastError: \"gold_last_error\",\n} as const;\n\nexport const BRONZE_DONE = \"done\";\n\n/** Parsed gold last-run record: the skip check's memory. */\nexport interface GoldLastRun {\n at: string;\n rows: Record;\n inputWatermark: string;\n}\n\n/** State for the bronze cron: pages, cursor, and sync run records. */\nexport function makeBronzeState(store: MetaStore, pages: BronzeStore) {\n return {\n async getCursor(): Promise {\n return await store.get(KEYS.bronzeCursor);\n },\n\n /**\n * Land a backfill page: the rows plus the next cursor in one\n * transaction, so a killed run resumes with at most the one in-flight\n * page refetched and never a cursor pointing past absent rows. Passing\n * BRONZE_DONE as cursorAfter writes the final page of history and the\n * sentinel together.\n *\n * The `done` sentinel is sticky BY DESIGN and never resets: a cleared\n * cursor would be indistinguishable from \"never started\" and would\n * restart the backfill from scratch. This deliberately breaks the\n * \"every key resets\" pattern documented on KEYS above.\n */\n async writeBackfillPage(rows: BronzeRow[], cursorAfter: string, runId: string): Promise {\n await pages.writePage(rows, cursorAfter, { source: \"cron_backfill\", runId, eventType: null });\n },\n\n /** Land an incremental page: rows only, the cursor is not involved. */\n async writeIncrementalPage(rows: BronzeRow[], runId: string): Promise {\n await pages.writePage(rows, null, { source: \"cron_incremental\", runId, eventType: null });\n },\n\n /** Success path: write fresh run state and clear the error key. */\n async recordSuccess(\n nowIso: string,\n rows: { backfill: number; incremental: number },\n ): Promise {\n await store.set(KEYS.bronzeLastRun, JSON.stringify({ at: nowIso, rows }));\n await store.delete(KEYS.bronzeLastError);\n },\n\n /**\n * Failure path: record what happened, with a timestamp. Best effort by\n * design — if the fault is sqlite itself this write fails too, the lock\n * TTL recovers, and the run logs are the record.\n */\n async recordFailure(nowIso: string, message: string): Promise {\n await store.set(KEYS.bronzeLastError, JSON.stringify({ at: nowIso, message }));\n },\n };\n}\n\n/** State for the silver cron: watermark, batches, and run records. */\nexport function makeSilverState(store: MetaStore, silver: SilverStore) {\n return {\n async getWatermark(): Promise {\n return await store.get(KEYS.silverWatermark);\n },\n\n /**\n * Land a silver batch: the upserts, the deletes, plus the watermark\n * advance in one transaction, the same rule as bronze pages. The\n * watermark must never point past rows that were not written.\n */\n async writeBatch(\n records: SilverRecord[],\n deleteIds: string[],\n rejects: SilverReject[],\n watermarkAfter: string,\n nowIso: string,\n ): Promise {\n await silver.writeBatch(records, deleteIds, rejects, watermarkAfter, nowIso);\n },\n\n /**\n * Success path: write fresh run state and clear the error key.\n * `rejects` is this run's count; `rejectsOutstanding` is the table\n * total after the run, so the panel shows a backlog even on idle runs.\n */\n async recordSuccess(\n nowIso: string,\n rows: number,\n rejects: number,\n rejectsOutstanding: number,\n ): Promise {\n await store.set(\n KEYS.silverLastRun,\n JSON.stringify({ at: nowIso, rows, rejects, rejectsOutstanding }),\n );\n await store.delete(KEYS.silverLastError);\n },\n\n /** Failure path: same best-effort rule as bronze's. */\n async recordFailure(nowIso: string, message: string): Promise {\n await store.set(KEYS.silverLastError, JSON.stringify({ at: nowIso, message }));\n },\n };\n}\n\n/** State for the gold cron: skip memory and rebuild run records. */\nexport function makeGoldState(store: MetaStore) {\n return {\n /** The silver watermark, read-only here: the skip check's input. */\n async getSilverWatermark(): Promise {\n return await store.get(KEYS.silverWatermark);\n },\n\n /** Last successful build, or null. Deleting the key forces a rebuild. */\n async getLastRun(): Promise {\n const rawValue = await store.get(KEYS.goldLastRun);\n if (rawValue === null) return null;\n try {\n return JSON.parse(rawValue) as GoldLastRun;\n } catch {\n // An unreadable record must not wedge the skip check; treat it as\n // \"never built\", which only costs one rebuild.\n return null;\n }\n },\n\n /** Success path: record counts and the watermark built from; clear error. */\n async recordSuccess(\n nowIso: string,\n rows: Record,\n inputWatermark: string,\n ): Promise {\n await store.set(\n KEYS.goldLastRun,\n JSON.stringify({ at: nowIso, rows, inputWatermark }),\n );\n await store.delete(KEYS.goldLastError);\n },\n\n /** Failure path: same best-effort rule as the other layers. */\n async recordFailure(nowIso: string, message: string): Promise {\n await store.set(KEYS.goldLastError, JSON.stringify({ at: nowIso, message }));\n },\n };\n}\n\n/** State for the bronze webhook: delivery records only, no lock, no cursor. */\nexport function makeWebhookState(store: MetaStore) {\n return {\n /** Success path: record the delivery and clear the error key. */\n async recordEvent(nowIso: string, eventType: string): Promise {\n await store.set(\n KEYS.bronzeWebhookLastEvent,\n JSON.stringify({ at: nowIso, eventType }),\n );\n await store.delete(KEYS.bronzeWebhookLastError);\n },\n\n /** Failure path: same best-effort rule as the cron jobs. */\n async recordFailure(nowIso: string, message: string): Promise {\n await store.set(\n KEYS.bronzeWebhookLastError,\n JSON.stringify({ at: nowIso, message }),\n );\n },\n };\n}\n",[],[420,422],"10bcb002-ce00-40de-a662-f144259718b7","sqliteStore.ts","01a033dc-1a01-70c1-86c3-7e687c504a83",32,["D",1787630531502],6,"/**\n * The I/O edge: real sqlite implementations of the narrow interfaces the\n * decision modules declare. A storage or schema change touches this file\n * (plus migrations.ts) and nothing else.\n *\n * The import is pinned to an explicit version: there is no lockfile in the\n * editor, so the ?v= pin is the only version protection. std/sqlite main\n * was at v31 when this was written; bump deliberately, never implicitly.\n */\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts?v=31\";\nimport type { Db } from \"./migrations.ts\";\nimport type { LockDeps } from \"./lock.ts\";\nimport type {\n BronzeRow,\n BronzeStore,\n BronzeWriteMeta,\n MetaStore,\n SilverReject,\n SilverStore,\n} from \"./state.ts\";\nimport type { SilverRecord } from \"../models/silverTransform.ts\";\nimport type { GoldModel } from \"../models/goldModels.ts\";\n\nexport const db: Db = {\n execute: (stmt) => sqlite.execute(stmt),\n batch: (stmts) => sqlite.batch(stmts),\n};\n\nexport const metaStore: MetaStore = {\n async get(key) {\n const res = await sqlite.execute({\n sql: \"SELECT value FROM meta WHERE key = ?\",\n args: [key],\n });\n return res.rows.length === 0 ? null : String(res.rows[0].value);\n },\n async set(key, value) {\n await sqlite.execute({\n sql: `INSERT INTO meta (key, value) VALUES (?, ?)\n ON CONFLICT (key) DO UPDATE SET value = excluded.value`,\n args: [key, value],\n });\n },\n async delete(key) {\n await sqlite.execute({ sql: \"DELETE FROM meta WHERE key = ?\", args: [key] });\n },\n};\n\n/**\n * The bronze log is INSERT-only (ADR 0012). This helper is the only\n * producer of statements against bronze_transactions_log, and there is no\n * UPDATE or DELETE against that table anywhere in the codebase — the\n * mirror (bronze_transactions) may be overwritten, the log may not. Every\n * observation appends, even when raw is byte-identical to the last one:\n * seen_at is the fact being recorded.\n */\nfunction bronzeLogInsert(\n id: string,\n seenAt: string,\n raw: string,\n meta: BronzeWriteMeta,\n): { sql: string; args: (string | null)[] } {\n return {\n sql: `INSERT INTO bronze_transactions_log (id, seen_at, source, run_id, event_type, raw)\n VALUES (?, ?, ?, ?, ?, ?)`,\n args: [id, seenAt, meta.source, meta.runId, meta.eventType, raw],\n };\n}\n\n/**\n * Bronze page atomicity lives here: the page's log appends, mirror\n * upserts and (for backfill) the cursor advance go through one\n * sqlite.batch, so they land together or not at all. On conflict in the\n * mirror, first_seen_at keeps its original value; raw, created_at and\n * last_seen_at replace.\n */\nexport const bronzeStore: BronzeStore = {\n async writePage(rows: BronzeRow[], cursorAfter: string | null, meta: BronzeWriteMeta) {\n const stmts: { sql: string; args: (string | null)[] }[] = [];\n for (const r of rows) {\n stmts.push(bronzeLogInsert(r.id, r.seenAt, r.raw, meta));\n stmts.push({\n sql: `INSERT INTO bronze_transactions (id, created_at, raw, first_seen_at, last_seen_at)\n VALUES (?, ?, ?, ?, ?)\n ON CONFLICT (id) DO UPDATE SET\n raw = excluded.raw,\n created_at = excluded.created_at,\n last_seen_at = excluded.last_seen_at`,\n args: [r.id, r.createdAt, r.raw, r.seenAt, r.seenAt],\n });\n }\n if (cursorAfter !== null) {\n stmts.push({\n sql: `INSERT INTO meta (key, value) VALUES ('bronze_cursor', ?)\n ON CONFLICT (key) DO UPDATE SET value = excluded.value`,\n args: [cursorAfter],\n });\n }\n if (stmts.length > 0) await sqlite.batch(stmts);\n },\n};\n\n/**\n * Highest last_seen_at in bronze, or null when bronze is empty: the silver\n * cron's pre-check read, so an idle run exits on one query instead of\n * re-working the boundary page.\n */\nexport async function readBronzeMaxSeen(): Promise {\n const res = await sqlite.execute({\n sql: \"SELECT MAX(last_seen_at) AS m FROM bronze_transactions\",\n args: [],\n });\n const m = res.rows[0]?.m;\n return m === null || m === undefined ? null : String(m);\n}\n\n/** Bronze rows at or past the watermark, oldest first: the silver cron's read. */\nexport async function readBronzeSince(\n watermark: string | null,\n limit: number,\n): Promise<{ id: string; raw: string; lastSeenAt: string; deletedAt: string | null }[]> {\n const res = await sqlite.execute({\n sql: `SELECT id, raw, last_seen_at, deleted_at FROM bronze_transactions\n WHERE last_seen_at >= ?\n ORDER BY last_seen_at, id\n LIMIT ?`,\n args: [watermark ?? \"\", limit],\n });\n return res.rows.map((r) => ({\n id: String(r.id),\n raw: String(r.raw),\n lastSeenAt: String(r.last_seen_at),\n deletedAt: r.deleted_at === null || r.deleted_at === undefined ? null : String(r.deleted_at),\n }));\n}\n\n/**\n * Mark a bronze row deleted, atomically: one log append plus one mirror\n * upsert in a batch. An existing mirror row keeps its raw and created_at\n * and gains deleted_at plus a fresh last_seen_at (so the silver read\n * picks it up); an id bronze has never seen lands as a tombstone whose\n * raw is the webhook event body, so the fact is not dropped. The log row\n * always carries the event body, labelled by event_type.\n */\nexport async function markBronzeDeleted(\n id: string,\n createdAt: string,\n eventRaw: string,\n seenAt: string,\n meta: BronzeWriteMeta,\n): Promise {\n await sqlite.batch([\n bronzeLogInsert(id, seenAt, eventRaw, meta),\n {\n sql: `INSERT INTO bronze_transactions (id, created_at, raw, first_seen_at, last_seen_at, deleted_at)\n VALUES (?, ?, ?, ?, ?, ?)\n ON CONFLICT (id) DO UPDATE SET\n deleted_at = excluded.deleted_at,\n last_seen_at = excluded.last_seen_at`,\n args: [id, createdAt, eventRaw, seenAt, seenAt, seenAt],\n },\n ]);\n}\n\n/** Rows currently flagged in silver_rejects: the silver cron's run-state read. */\nexport async function readSilverRejectCount(): Promise {\n const res = await sqlite.execute({ sql: \"SELECT COUNT(*) AS n FROM silver_rejects\", args: [] });\n return Number(res.rows[0]?.n ?? 0);\n}\n\n/** All flagged rejects joined to their current bronze row: tools/silverReplay.ts's read. */\nexport async function readSilverRejectsWithBronze(): Promise<\n {\n id: string;\n reason: string;\n attempts: number;\n raw: string | null;\n lastSeenAt: string | null;\n deletedAt: string | null;\n }[]\n> {\n const res = await sqlite.execute({\n sql: `SELECT r.id, r.reason, r.attempts, b.raw, b.last_seen_at, b.deleted_at\n FROM silver_rejects r\n LEFT JOIN bronze_transactions b ON b.id = r.id\n ORDER BY r.first_rejected_at, r.id`,\n args: [],\n });\n const str = (v: unknown) => (v === null || v === undefined ? null : String(v));\n return res.rows.map((r) => ({\n id: String(r.id),\n reason: String(r.reason),\n attempts: Number(r.attempts),\n raw: str(r.raw),\n lastSeenAt: str(r.last_seen_at),\n deletedAt: str(r.deleted_at),\n }));\n}\n\n/**\n * Silver batch atomicity lives here: the upserts, the deletes, the reject\n * bookkeeping and the watermark advance go through one sqlite.batch. On\n * conflict the whole silver row replaces. Reject rows: upsert bumps\n * attempts and keeps first_rejected_at; a clean transform or a delete of\n * the same id removes its reject row, so a fixed row clears itself.\n */\nexport const silverStore: SilverStore = {\n async writeBatch(\n records: SilverRecord[],\n deleteIds: string[],\n rejects: SilverReject[],\n watermarkAfter: string,\n nowIso: string,\n ) {\n const stmts: { sql: string; args: (string | number | null)[] }[] = records.map((r) => ({\n sql: `INSERT INTO silver_transactions (\n id, account_id, status, transaction_type, description, raw_text, message,\n amount_cents, currency, foreign_amount_cents, foreign_currency,\n round_up_cents, cashback_cents, category_id, parent_category_id,\n transfer_account_id, created_at_utc, settled_at_utc\n ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n ON CONFLICT (id) DO UPDATE SET\n account_id = excluded.account_id,\n status = excluded.status,\n transaction_type = excluded.transaction_type,\n description = excluded.description,\n raw_text = excluded.raw_text,\n message = excluded.message,\n amount_cents = excluded.amount_cents,\n currency = excluded.currency,\n foreign_amount_cents = excluded.foreign_amount_cents,\n foreign_currency = excluded.foreign_currency,\n round_up_cents = excluded.round_up_cents,\n cashback_cents = excluded.cashback_cents,\n category_id = excluded.category_id,\n parent_category_id = excluded.parent_category_id,\n transfer_account_id = excluded.transfer_account_id,\n created_at_utc = excluded.created_at_utc,\n settled_at_utc = excluded.settled_at_utc`,\n args: [\n r.id,\n r.accountId,\n r.status,\n r.transactionType,\n r.description,\n r.rawText,\n r.message,\n r.amountCents,\n r.currency,\n r.foreignAmountCents,\n r.foreignCurrency,\n r.roundUpCents,\n r.cashbackCents,\n r.categoryId,\n r.parentCategoryId,\n r.transferAccountId,\n r.createdAtUtc,\n r.settledAtUtc,\n ],\n }));\n for (const id of deleteIds) {\n stmts.push({ sql: \"DELETE FROM silver_transactions WHERE id = ?\", args: [id] });\n }\n for (const r of records) {\n stmts.push({ sql: \"DELETE FROM silver_rejects WHERE id = ?\", args: [r.id] });\n }\n for (const id of deleteIds) {\n stmts.push({ sql: \"DELETE FROM silver_rejects WHERE id = ?\", args: [id] });\n }\n for (const rj of rejects) {\n stmts.push({\n sql: `INSERT INTO silver_rejects (\n id, reason, last_seen_at, first_rejected_at, last_rejected_at, attempts\n ) VALUES (?, ?, ?, ?, ?, 1)\n ON CONFLICT (id) DO UPDATE SET\n reason = excluded.reason,\n last_seen_at = excluded.last_seen_at,\n last_rejected_at = excluded.last_rejected_at,\n attempts = silver_rejects.attempts + 1`,\n args: [rj.id, rj.reason, rj.lastSeenAt, nowIso, nowIso],\n });\n }\n stmts.push({\n sql: `INSERT INTO meta (key, value) VALUES ('silver_watermark', ?)\n ON CONFLICT (key) DO UPDATE SET value = excluded.value`,\n args: [watermarkAfter],\n });\n await sqlite.batch(stmts);\n },\n};\n\n/**\n * Gold rebuild atomicity lives here: DELETE plus INSERT INTO ... SELECT in\n * one batch per model, so readers see the old table or the new one, never\n * a half-built one. Table names and SELECTs come from goldModels.ts\n * constants, never from input, so the interpolation is safe.\n */\nexport async function rebuildGoldModel(model: GoldModel): Promise {\n await sqlite.batch([\n { sql: `DELETE FROM ${model.table}` },\n { sql: `INSERT INTO ${model.table} ${model.select}` },\n ]);\n const res = await sqlite.execute({\n sql: `SELECT COUNT(*) AS n FROM ${model.table}`,\n args: [],\n });\n return Number(res.rows[0]?.n ?? 0);\n}\n\n/**\n * Lock atomicity lives here: each operation is a single conditional UPDATE\n * on a seeded row, so two concurrent runs cannot both observe the lock as\n * free and both claim it. Parameterised by key: each layer's cron has its\n * own row (bronze_lock, silver_lock, gold_lock, all seeded by migrations)\n * and excludes only itself.\n */\nexport function makeLockStore(key: string): LockDeps {\n return {\n async tryAcquire(nowMs, expiresAtMs, token) {\n const res = await sqlite.execute({\n sql: `UPDATE meta\n SET value = json_object('expiresAt', ?, 'token', ?)\n WHERE key = ?\n AND CAST(json_extract(value, '$.expiresAt') AS INTEGER) <= ?`,\n args: [expiresAtMs, token, key, nowMs],\n });\n return res.rowsAffected === 1;\n },\n async release(token) {\n const res = await sqlite.execute({\n sql: `UPDATE meta\n SET value = json_object('expiresAt', 0, 'token', '')\n WHERE key = ?\n AND json_extract(value, '$.token') = ?`,\n args: [key, token],\n });\n return res.rowsAffected === 1;\n },\n };\n}\n\nexport const bronzeLockStore: LockDeps = makeLockStore(\"bronze_lock\");\nexport const silverLockStore: LockDeps = makeLockStore(\"silver_lock\");\nexport const goldLockStore: LockDeps = makeLockStore(\"gold_lock\");\n",[],[420,430],"005ae8ad-c0c0-4653-8f27-d70dfab99dc5","migrations.ts","01a033dc-1a01-70c1-86c3-7a8ee0c79c91",12,["D",1787630391604],"/**\n * Versioned migration runner. Every schema change — including the first —\n * arrives as a numbered step that runs exactly once, applied atomically via\n * batch. The `schema_version` key makes the schema state readable in the\n * SQLite panel, and is the guard that makes a branch/database mismatch\n * detectable (branches share the one production database).\n *\n * No hand-rolled ALTERs that swallow errors: a real database fault must not\n * read identically to \"already applied\". The one documented exception is\n * the version read itself — see readSchemaVersion.\n */\n\nexport interface Db {\n execute(stmt: { sql: string; args?: unknown[] }): Promise<{\n rows: Record[];\n rowsAffected: number;\n }>;\n /** All statements succeed or all roll back. */\n batch(stmts: { sql: string; args?: unknown[] }[]): Promise;\n}\n\nexport const SCHEMA_VERSION_KEY = \"schema_version\";\n\ninterface Migration {\n version: number;\n statements: { sql: string; args?: unknown[] }[];\n}\n\nconst MIGRATIONS: Migration[] = [\n {\n // Migration 1: create `meta` and seed the lock row, so the lock's\n // conditional UPDATE always has a row to hit.\n version: 1,\n statements: [\n { sql: \"CREATE TABLE meta (key TEXT PRIMARY KEY, value TEXT NOT NULL)\" },\n {\n sql: \"INSERT INTO meta (key, value) VALUES ('lock', json_object('expiresAt', 0, 'token', ''))\",\n },\n ],\n },\n {\n // Migration 2: bronze table for raw transaction records. Only the key,\n // one ordering column, and the seen timestamps are promoted; everything\n // else lives inside `raw` for a later silver step to read.\n version: 2,\n statements: [\n {\n sql: `CREATE TABLE bronze_transactions (\n id TEXT PRIMARY KEY,\n created_at TEXT NOT NULL,\n raw TEXT NOT NULL,\n first_seen_at TEXT NOT NULL,\n last_seen_at TEXT NOT NULL\n )`,\n },\n ],\n },\n {\n // Migration 3: silver table of cleaned, typed records derived from\n // bronze. Money as signed integer base units, timestamps as UTC ISO\n // strings; the transform in transform.ts owns all interpretation.\n version: 3,\n statements: [\n {\n sql: `CREATE TABLE silver_transactions (\n id TEXT PRIMARY KEY,\n account_id TEXT NOT NULL,\n status TEXT NOT NULL,\n description TEXT NOT NULL,\n raw_text TEXT,\n message TEXT,\n amount_cents INTEGER NOT NULL,\n currency TEXT NOT NULL,\n foreign_amount_cents INTEGER,\n foreign_currency TEXT,\n round_up_cents INTEGER,\n cashback_cents INTEGER,\n category_id TEXT,\n parent_category_id TEXT,\n transfer_account_id TEXT,\n created_at_utc TEXT NOT NULL,\n settled_at_utc TEXT\n )`,\n },\n ],\n },\n {\n // Migration 4: seed the promote job's lock row. Promotion runs on its\n // own cron (ADR 0004) with its own mutual exclusion.\n version: 4,\n statements: [\n {\n sql: \"INSERT INTO meta (key, value) VALUES ('promote_lock', json_object('expiresAt', 0, 'token', ''))\",\n },\n ],\n },\n {\n // Migration 5: rename meta keys to the symmetric layer scheme\n // (bronze_*, silver_*, gold_*). Renames only, applied to a live\n // database; earlier migrations keep seeding the old names and this\n // step renames them, so a fresh database reaches the same end state.\n // The two old poller keys merge into one bronze_last_run record; the\n // INSERT SELECT writes nothing when either old key is absent.\n version: 5,\n statements: [\n { sql: \"UPDATE meta SET key = 'bronze_lock' WHERE key = 'lock'\" },\n { sql: \"UPDATE meta SET key = 'bronze_cursor' WHERE key = 'backfill_cursor'\" },\n {\n sql: `INSERT INTO meta (key, value)\n SELECT 'bronze_last_run', json_object('at', a.value, 'rows', json(b.value))\n FROM meta a, meta b\n WHERE a.key = 'last_sync_at' AND b.key = 'last_run_rows'`,\n },\n { sql: \"DELETE FROM meta WHERE key IN ('last_sync_at', 'last_run_rows')\" },\n { sql: \"UPDATE meta SET key = 'bronze_last_error' WHERE key = 'last_error'\" },\n { sql: \"UPDATE meta SET key = 'silver_lock' WHERE key = 'promote_lock'\" },\n { sql: \"UPDATE meta SET key = 'silver_last_run' WHERE key = 'promote_last_run'\" },\n { sql: \"UPDATE meta SET key = 'silver_last_error' WHERE key = 'promote_last_error'\" },\n ],\n },\n {\n // Migration 6: gold aggregate tables plus the gold job's lock row.\n // Column order is load-bearing: goldModels.ts inserts positionally.\n version: 6,\n statements: [\n {\n sql: `CREATE TABLE gold_monthly_category_spend (\n month TEXT NOT NULL,\n category_id TEXT,\n parent_category_id TEXT,\n txn_count INTEGER NOT NULL,\n spend_cents INTEGER NOT NULL,\n PRIMARY KEY (month, category_id)\n )`,\n },\n {\n sql: `CREATE TABLE gold_monthly_cashflow (\n month TEXT NOT NULL,\n account_id TEXT NOT NULL,\n in_cents INTEGER NOT NULL,\n out_cents INTEGER NOT NULL,\n net_cents INTEGER NOT NULL,\n txn_count INTEGER NOT NULL,\n PRIMARY KEY (month, account_id)\n )`,\n },\n {\n sql: \"INSERT INTO meta (key, value) VALUES ('gold_lock', json_object('expiresAt', 0, 'token', ''))\",\n },\n ],\n },\n {\n // Migration 7: index for the silver cron's pre-check and batch read at\n // the 15-minute cadence; both key on last_seen_at.\n version: 7,\n statements: [\n {\n sql: \"CREATE INDEX idx_bronze_last_seen ON bronze_transactions (last_seen_at)\",\n },\n ],\n },\n {\n // Migration 8: soft-delete marker for webhook TRANSACTION_DELETED\n // events (ADR 0007). Polling cannot observe deletions; the webhook\n // can, and bronze keeps the fact rather than dropping the row.\n version: 8,\n statements: [\n { sql: \"ALTER TABLE bronze_transactions ADD COLUMN deleted_at TEXT\" },\n ],\n },\n {\n // Migration 9: gold monthly balance mart (ADR 0008) — no new lock, it\n // rebuilds on the existing gold cron. Column order is load-bearing:\n // goldModels.ts inserts positionally (see migration 6).\n version: 9,\n statements: [\n {\n sql: `CREATE TABLE gold_monthly_balance (\n month TEXT NOT NULL,\n account_id TEXT NOT NULL,\n balance_cents INTEGER NOT NULL,\n PRIMARY KEY (month, account_id)\n )`,\n },\n ],\n },\n {\n // Migration 10: re-grain gold to daily (ADR 0009). Gold tables rebuild\n // wholesale on every run, so dropping the monthly marts loses nothing.\n // Names carry no duration; day is a column. Column order is\n // load-bearing: goldModels.ts inserts positionally (see migration 6).\n version: 10,\n statements: [\n { sql: \"DROP TABLE gold_monthly_category_spend\" },\n { sql: \"DROP TABLE gold_monthly_cashflow\" },\n { sql: \"DROP TABLE gold_monthly_balance\" },\n {\n sql: `CREATE TABLE gold_category_spend (\n day TEXT NOT NULL,\n category_id TEXT,\n parent_category_id TEXT,\n txn_count INTEGER NOT NULL,\n spend_cents INTEGER NOT NULL,\n PRIMARY KEY (day, category_id)\n )`,\n },\n {\n sql: `CREATE TABLE gold_cashflow (\n day TEXT NOT NULL,\n account_id TEXT NOT NULL,\n in_cents INTEGER NOT NULL,\n out_cents INTEGER NOT NULL,\n net_cents INTEGER NOT NULL,\n txn_count INTEGER NOT NULL,\n PRIMARY KEY (day, account_id)\n )`,\n },\n {\n sql: `CREATE TABLE gold_balance (\n day TEXT NOT NULL,\n account_id TEXT NOT NULL,\n balance_cents INTEGER NOT NULL,\n PRIMARY KEY (day, account_id)\n )`,\n },\n ],\n },\n {\n // Migration 11: promote transactionType into silver and add the income\n // mart (ADR 0010). Existing silver rows gain the column on the full\n // rebuild triggered by the watermark reset. Column order is\n // load-bearing: goldModels.ts inserts positionally (see migration 6).\n version: 11,\n statements: [\n { sql: \"ALTER TABLE silver_transactions ADD COLUMN transaction_type TEXT\" },\n {\n sql: `CREATE TABLE gold_income (\n day TEXT NOT NULL,\n income_type TEXT NOT NULL,\n txn_count INTEGER NOT NULL,\n income_cents INTEGER NOT NULL,\n PRIMARY KEY (day, income_type)\n )`,\n },\n ],\n },\n {\n // Migration 12: curation table for external destinations (ADR 0011).\n // The rows are personal data (which institutions the owner banks with),\n // so this public migration creates only the empty shape; rows are\n // inserted privately via the SQLite panel and never appear in code.\n version: 12,\n statements: [\n {\n sql: `CREATE TABLE curation_external_destinations (\n transaction_type TEXT NOT NULL,\n description TEXT NOT NULL,\n PRIMARY KEY (transaction_type, description)\n )`,\n },\n ],\n },\n {\n // Migration 13: append-only bronze log (ADR 0012). bronze_transactions\n // stays the latest-state mirror; every observation of a transaction\n // (cron page row, webhook fetch, DELETED event) appends one row here\n // and nothing ever updates or deletes it. The seed copies the mirror\n // so the log is never emptier than the mirror; on a fresh database it\n // copies nothing. Tombstones seed with their event type so the raw\n // (an event body, not a resource) is labelled.\n version: 13,\n statements: [\n {\n sql: `CREATE TABLE bronze_transactions_log (\n seq INTEGER PRIMARY KEY AUTOINCREMENT,\n id TEXT NOT NULL,\n seen_at TEXT NOT NULL,\n source TEXT NOT NULL,\n run_id TEXT NOT NULL,\n event_type TEXT,\n raw TEXT NOT NULL\n )`,\n },\n {\n sql: \"CREATE INDEX idx_bronze_log_id_seq ON bronze_transactions_log (id, seq)\",\n },\n {\n sql: `INSERT INTO bronze_transactions_log (id, seen_at, source, run_id, event_type, raw)\n SELECT id, last_seen_at, 'migration_13_seed', 'migration_13',\n CASE WHEN deleted_at IS NULL THEN NULL ELSE 'TRANSACTION_DELETED' END,\n raw\n FROM bronze_transactions\n ORDER BY last_seen_at, id`,\n },\n ],\n },\n {\n // Migration 14: silver rejects (ADR 0013). A bronze row that toSilver\n // rejects is recorded here and the run continues, instead of the first\n // rejection halting silver for every other row. One row per id: the\n // latest reason and attempt count, cleared when the id transforms\n // cleanly or is deleted.\n version: 14,\n statements: [\n {\n sql: `CREATE TABLE silver_rejects (\n id TEXT PRIMARY KEY,\n reason TEXT NOT NULL,\n last_seen_at TEXT NOT NULL,\n first_rejected_at TEXT NOT NULL,\n last_rejected_at TEXT NOT NULL,\n attempts INTEGER NOT NULL\n )`,\n },\n ],\n },\n];\n\n/**\n * EXCEPTION, by design: migration 1 creates the table the version lives in,\n * so \"no such table: meta\" on this one read means version 0. Catch that one\n * error, on this one read, and nothing else — any other fault must surface.\n */\nasync function readSchemaVersion(db: Db): Promise {\n try {\n const res = await db.execute({\n sql: \"SELECT value FROM meta WHERE key = ?\",\n args: [SCHEMA_VERSION_KEY],\n });\n return res.rows.length === 0 ? 0 : Number(res.rows[0].value);\n } catch (err) {\n if (String((err as Error)?.message ?? err).includes(\"no such table: meta\")) {\n return 0;\n }\n throw err;\n }\n}\n\nexport async function migrate(db: Db): Promise<{ from: number; to: number }> {\n const from = await readSchemaVersion(db);\n let current = from;\n for (const m of MIGRATIONS) {\n if (m.version <= current) continue;\n // Statements plus the version write land in one transaction, so a run\n // killed mid-migration leaves either the old schema or the new one —\n // never a half-applied step that a rerun would trip over.\n await db.batch([\n ...m.statements,\n {\n sql: `INSERT INTO meta (key, value) VALUES (?, ?)\n ON CONFLICT (key) DO UPDATE SET value = excluded.value`,\n args: [SCHEMA_VERSION_KEY, String(m.version)],\n },\n ]);\n current = m.version;\n }\n return { from, to: current };\n}\n",[],[420,439],"c51a851e-72f5-4c18-af5d-92dcea115308","lock.ts","01a033dc-1a01-70c1-86c3-77352da5b5a3",["D",1787576457509],"/**\n * Run lock: TTL plus an atomic conditional UPDATE on the seeded `meta` key.\n * Lose the race, exit — the poller never waits.\n *\n * The TTL is the real safety mechanism: a run killed at the platform's\n * 60-second wall never executes its release path, so expiry must recover\n * the lock on its own. The TTL therefore only needs to outlive the kill\n * wall, not a \"normal\" run.\n *\n * Decision logic only. The atomicity itself (a single conditional UPDATE)\n * lives behind LockDeps, in the store implementation.\n */\n\nexport interface LockDeps {\n /**\n * Atomically claim the lock iff the stored expiry <= nowMs.\n * Must be one conditional UPDATE in the store, so two concurrent runs\n * cannot both see the lock as free. Returns true on the winning claim.\n */\n tryAcquire(nowMs: number, expiresAtMs: number, token: string): Promise;\n /** Atomically release iff `token` still holds the lock. True if released. */\n release(token: string): Promise;\n}\n\nexport interface Lock {\n acquired: boolean;\n token: string;\n}\n\nexport async function acquireLock(\n deps: LockDeps,\n now: () => number,\n randomId: () => string,\n ttlMs: number,\n): Promise {\n const token = randomId();\n const nowMs = now();\n const acquired = await deps.tryAcquire(nowMs, nowMs + ttlMs, token);\n return { acquired, token };\n}\n\n/**\n * Best effort: returns false if the lock has already been taken over after\n * TTL expiry (fine — the newer holder owns it) or if the store call itself\n * fails (the TTL recovers; the logs are the record).\n */\nexport async function releaseLock(deps: LockDeps, lock: Lock): Promise {\n try {\n return await deps.release(lock.token);\n } catch {\n return false;\n }\n}\n",[],[420,447],"63743ff6-8d59-48a1-b156-b61b842b72b8",["D",1787576457509],[173,174,175,176,177],[420],["D",1787576457509],["D",1789571433983],{"_11":12,"_127":75},{"_11":12,"_123":-5,"_74":75,"_30":73,"_124":125,"_126":24},{"_38":114,"_13":16,"_32":35,"_115":77,"_116":-5,"_117":-5,"_118":-5,"_119":-5,"_120":-5,"_121":14,"_122":12},"ctx",{"_53":-5,"_147":495,"_496":497,"_498":499,"_500":24,"_501":24,"_502":24,"_503":145,"_504":-5,"_505":-7,"_506":-7,"_507":26,"_508":24,"_509":24,"_510":-5,"_511":24,"_512":24,"_513":24,"_514":-5},"ENV",{"_466":467,"_468":469,"_470":16,"_471":472,"_473":474,"_475":476,"_477":478,"_479":480,"_481":482,"_483":484,"_485":486,"_487":488,"_489":490,"_491":492,"_493":494},"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 · 0007-webhook-ingestion.md