[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8},"actionData","errors","root",{"_108":109,"_110":111},"routes/embed.x.$handle.$name.$",{"_9":10,"_11":12,"_13":14,"_15":16,"_17":18,"_19":20,"_21":-5},"handle","plusjade","name","app-clarkview","path","lib/reminderStore.ts","project",{"_22":48,"_11":12,"_35":49,"_50":-5,"_51":44,"_52":53,"_54":53,"_55":56,"_57":-5,"_58":59,"_60":-5,"_61":44,"_62":-5,"_63":-5,"_64":65,"_66":67,"_68":69,"_70":71,"_72":73,"_74":-5,"_75":76,"_77":76,"_78":79,"_80":79,"_81":79,"_82":76,"_83":76,"_84":85,"_86":87,"_88":89,"_90":44},"file",{"_22":23,"_11":24,"_25":26,"_27":28,"_29":30,"_31":32,"_33":34,"_35":36,"_37":38,"_39":40,"_41":42,"_43":44,"_45":-5,"_13":46},"permalink","https://www.val.town/x/plusjade/app-clarkview/code/lib/reminderStore.ts","extension","id","13992d06-2aaa-49df-8247-dae24b21a6f3","reminderStore.ts","fileId","7e2745ce-ac92-11f1-aeb4-1607ee4eb77e","parentId","01a01c4d-2bf2-7199-84aa-883b3a9a9d0b","branchId","9635267c-e84d-4621-be8a-af9e5052d6d5","branchVersion",335,"latest",true,"createdAt",["D",1789078089989],"version",4,"fileType","script","content","// Durable ledger for event reminders: one row per intended notification.\n//\n// The row is the unit of both scheduling and idempotency. UNIQUE(device_id,item_id,rule)\n// makes re-enqueueing a no-op, and the claim below is a single compare-and-swap, so\n// overlapping runs and repeated triggers can never send twice. Delivery is\n// at-most-once by design: an abandoned claim is failed, never retried.\n//\n// Stored times follow the parent convention in `time.ts`: ISO-8601 UTC text, compared\n// as text because the format is fixed-width. Read that module before touching a\n// timestamp here — the `T` separator is load-bearing.\n\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport {\n ISO_FORMAT,\n isoFromEpochColumn,\n isoFromUnix,\n NOW_UTC,\n nowIsoUtc,\n} from \"./time.ts\";\n\nexport { isoFromUnix, unixFromIso } from \"./time.ts\";\n\nexport type ReminderState =\n | \"pending\"\n | \"claimed\"\n | \"sent\"\n | \"failed\"\n | \"void\"\n | \"disabled\";\n\nexport type ReminderRow = {\n id: number;\n deviceId: number;\n installId: string;\n itemId: string;\n rule: string;\n eventAt: string;\n // The item's expiry bound. An estimate the source published, stored so send-time\n // policy can ask \"is this event over?\" without another read. Never shown to anyone.\n expiresAt: string;\n sendAfter: string;\n title: string;\n body: string;\n state: ReminderState;\n};\n\nexport const DEFAULT_LEAD_SECONDS = 3600;\n\n// How overdue a reminder may be and still be worth sending. This must exceed the\n// drain interval plus the scheduler's slack, or rows that come due just after a tick\n// are voided instead of sent — a silently dropped reminder. Val Town cron fires up to\n// ~5 minutes past its configured minute (observed 2026-09-10: a :35 tick ran at :40),\n// so a 15-minute cadence needs a cap well above 20 minutes, not equal to the cadence.\n// Being 30 minutes late still leaves a default 60-minute lead half its usefulness;\n// the guard that actually protects the user is the started-event check, not this one.\nexport const MAX_LATENESS_SECONDS = 30 * 60;\n\n// A claim older than this belongs to a run that died mid-send. Its outcome is\n// unknowable, and at-most-once resolves that ambiguity against sending.\nexport const ABANDONED_CLAIM_SECONDS = 10 * 60;\n\nasync function columnTypes(table: string): Promise> {\n const result = await sqlite.execute(`PRAGMA table_info(${table})`);\n return new Map(\n (result.rows as { name: string; type: string }[]).map((row) => [row.name, row.type]),\n );\n}\n\nconst QUEUE_COLUMNS = `\n id INTEGER PRIMARY KEY,\n device_id INTEGER NOT NULL REFERENCES devices(id) ON DELETE CASCADE,\n item_id TEXT NOT NULL,\n rule TEXT NOT NULL,\n event_at TEXT NOT NULL,\n expires_at TEXT NOT NULL,\n send_after TEXT NOT NULL,\n title TEXT NOT NULL,\n body TEXT NOT NULL,\n state TEXT NOT NULL DEFAULT 'pending'\n CHECK(state IN ('pending','claimed','sent','failed','void','disabled')),\n claimed_at TEXT,\n sent_at TEXT,\n attempts INTEGER NOT NULL DEFAULT 0,\n apns_status TEXT,\n created_at TEXT NOT NULL,\n UNIQUE(device_id, item_id, rule)`;\n\n// The first cut of this table stored epoch integers. Rebuilding is the only way to\n// change a column's declared type in SQLite, and the conversion is lossless.\nasync function migrateEpochQueue() {\n const existing = await columnTypes(\"notification_queue\");\n if (existing.get(\"event_at\") !== \"INTEGER\") return;\n\n const asIso = isoFromEpochColumn;\n await sqlite.execute(\"ALTER TABLE notification_queue RENAME TO notification_queue_epoch\");\n await sqlite.execute(`CREATE TABLE notification_queue (${QUEUE_COLUMNS})`);\n await sqlite.execute(`INSERT INTO notification_queue\n (id,device_id,item_id,rule,event_at,expires_at,send_after,title,body,state,\n claimed_at,sent_at,attempts,apns_status,created_at)\n SELECT id,device_id,item_id,rule,${asIso(\"event_at\")},\n strftime('${ISO_FORMAT}',event_at+1,'unixepoch'),${asIso(\"send_after\")},\n title,body,state,\n CASE WHEN claimed_at IS NULL THEN NULL ELSE ${asIso(\"claimed_at\")} END,\n CASE WHEN sent_at IS NULL THEN NULL ELSE ${asIso(\"sent_at\")} END,\n attempts,apns_status,${asIso(\"created_at\")}\n FROM notification_queue_epoch`);\n await sqlite.execute(\"DROP TABLE notification_queue_epoch\");\n}\n\n// Rows queued before the item window carry no expiry. SQLite cannot add a NOT NULL\n// column without a constant default, and there is no honest constant — the parent\n// has no domain model to infer a duration from — so the table is rebuilt and each\n// existing row is backfilled as instantaneous (one second). The next build refreshes\n// every pending row with the source's real window, so the sentinel is short-lived.\nasync function migrateWindowQueue() {\n const existing = await columnTypes(\"notification_queue\");\n if (existing.size === 0 || existing.has(\"expires_at\")) return;\n\n await sqlite.execute(\"ALTER TABLE notification_queue RENAME TO notification_queue_instant\");\n await sqlite.execute(`CREATE TABLE notification_queue (${QUEUE_COLUMNS})`);\n await sqlite.execute(`INSERT INTO notification_queue\n (id,device_id,item_id,rule,event_at,expires_at,send_after,title,body,state,\n claimed_at,sent_at,attempts,apns_status,created_at)\n SELECT id,device_id,item_id,rule,event_at,\n strftime('${ISO_FORMAT}',event_at,'+1 second'),send_after,\n title,body,state,claimed_at,sent_at,attempts,apns_status,created_at\n FROM notification_queue_instant`);\n await sqlite.execute(\"DROP TABLE notification_queue_instant\");\n}\n\nexport async function ensureReminderSchema() {\n await migrateEpochQueue();\n await migrateWindowQueue();\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS notification_queue (${QUEUE_COLUMNS})`);\n await sqlite.execute(\n `CREATE INDEX IF NOT EXISTS notification_queue_due\n ON notification_queue(state, send_after)`,\n );\n\n // Reminder policy and the widget's last observed offset live on the device row;\n // both are inferred or defaulted, never required configuration.\n const columns = await columnTypes(\"devices\");\n const additions: [string, string][] = [\n [\"reminder_lead_seconds\", `INTEGER NOT NULL DEFAULT ${DEFAULT_LEAD_SECONDS}`],\n [\"last_tz_offset_seconds\", \"INTEGER\"],\n [\"last_tz_seen_at\", \"TEXT\"],\n [\"reminders_built_at\", \"TEXT\"],\n ];\n for (const [name, definition] of additions) {\n if (columns.has(name)) continue;\n await sqlite.execute(`ALTER TABLE devices ADD COLUMN ${name} ${definition}`);\n }\n}\n\nexport type BuildCandidate = {\n id: number;\n installId: string;\n leadSeconds: number;\n tzOffsetSeconds: number | null;\n};\n\n// Ordering by staleness makes the sweep self-healing: a run killed by the wall-clock\n// limit leaves the devices it finished marked, and the next run resumes at the rest.\n// Newly paired devices sort first because their column is null.\nexport async function stalestDevices(limit: number): Promise {\n const result = await sqlite.execute({\n sql: `SELECT id, install_id, reminder_lead_seconds, last_tz_offset_seconds\n FROM devices\n ORDER BY reminders_built_at IS NOT NULL, reminders_built_at, id\n LIMIT ?`,\n args: [limit],\n });\n return (result.rows as {\n id: number;\n install_id: string;\n reminder_lead_seconds: number | null;\n last_tz_offset_seconds: number | null;\n }[]).map((row) => ({\n id: Number(row.id),\n installId: row.install_id,\n leadSeconds: Number(row.reminder_lead_seconds ?? DEFAULT_LEAD_SECONDS),\n tzOffsetSeconds: row.last_tz_offset_seconds == null\n ? null\n : Number(row.last_tz_offset_seconds),\n }));\n}\n\nexport async function markDeviceBuilt(deviceId: number) {\n await sqlite.execute({\n sql: \"UPDATE devices SET reminders_built_at = ? WHERE id = ?\",\n args: [nowIsoUtc(), deviceId],\n });\n}\n\nexport type PendingReminder = {\n itemId: string;\n rule: string;\n eventAt: string;\n expiresAt: string;\n sendAfter: string;\n title: string;\n body: string;\n};\n\n// One batch per device keeps round trips down while still committing device by\n// device, so a run cut short by the wall-clock limit loses at most one device's work.\nexport async function syncDeviceReminders(\n deviceId: number,\n reminders: PendingReminder[],\n): Promise {\n const now = nowIsoUtc();\n const statements = reminders.map((reminder) => ({\n sql: `INSERT INTO notification_queue\n (device_id,item_id,rule,event_at,expires_at,send_after,title,body,created_at)\n VALUES (?,?,?,?,?,?,?,?,?)\n ON CONFLICT(device_id,item_id,rule) DO UPDATE SET\n event_at=excluded.event_at,\n expires_at=excluded.expires_at,\n send_after=excluded.send_after,\n title=excluded.title,\n body=excluded.body\n WHERE notification_queue.state='pending'`,\n args: [\n deviceId,\n reminder.itemId,\n reminder.rule,\n reminder.eventAt,\n reminder.expiresAt,\n reminder.sendAfter,\n reminder.title,\n reminder.body,\n now,\n ],\n }));\n\n // An item that left the feed is cancelled in place. Voiding is enough because the\n // drainer treats a non-pending row as nothing to do, so no external schedule\n // needs to be recalled.\n const keep = reminders.map((reminder) => reminder.itemId);\n const placeholders = keep.map(() => \"?\").join(\",\");\n statements.push({\n sql: `UPDATE notification_queue SET state='void'\n WHERE device_id=? AND state='pending'\n ${keep.length ? `AND item_id NOT IN (${placeholders})` : \"\"}`,\n args: [deviceId, ...keep],\n });\n\n await sqlite.batch(statements);\n}\n\nexport async function dueReminders(limit: number): Promise {\n const result = await sqlite.execute({\n sql: `SELECT q.id, q.device_id, d.install_id, q.item_id, q.rule,\n q.event_at, q.expires_at, q.send_after, q.title, q.body, q.state\n FROM notification_queue q\n JOIN devices d ON d.id = q.device_id\n WHERE q.state='pending' AND q.send_after <= ${NOW_UTC}\n ORDER BY q.send_after, q.id\n LIMIT ?`,\n args: [limit],\n });\n return (result.rows as {\n id: number;\n device_id: number;\n install_id: string;\n item_id: string;\n rule: string;\n event_at: string;\n expires_at: string;\n send_after: string;\n title: string;\n body: string;\n state: ReminderState;\n }[]).map((row) => ({\n id: Number(row.id),\n deviceId: Number(row.device_id),\n installId: row.install_id,\n itemId: row.item_id,\n rule: row.rule,\n eventAt: row.event_at,\n expiresAt: row.expires_at,\n sendAfter: row.send_after,\n title: row.title,\n body: row.body,\n state: row.state,\n }));\n}\n\n// The only lock in the system. Rows affected is the answer to \"is this send mine?\",\n// which is what makes a duplicate trigger — a retried webhook, an overlapping run —\n// harmless rather than a second notification.\nexport async function claimReminder(id: number): Promise {\n const result = await sqlite.execute({\n sql: `UPDATE notification_queue\n SET state='claimed', claimed_at=${NOW_UTC}, attempts=attempts+1\n WHERE id=? AND state='pending'`,\n args: [id],\n });\n return result.rowsAffected === 1;\n}\n\nexport async function settleReminder(\n id: number,\n state: Extract,\n apnsStatus: string | null,\n) {\n await sqlite.execute({\n sql: `UPDATE notification_queue\n SET state=?, sent_at=${NOW_UTC}, apns_status=?\n WHERE id=?`,\n args: [state, apnsStatus, id],\n });\n}\n\nexport async function failAbandonedClaims(): Promise {\n const result = await sqlite.execute({\n sql: `UPDATE notification_queue SET state='failed', apns_status='AbandonedClaim'\n WHERE state='claimed'\n AND claimed_at < strftime('${ISO_FORMAT}','now',?)`,\n args: [`-${ABANDONED_CLAIM_SECONDS} seconds`],\n });\n return result.rowsAffected;\n}\n\nexport type ReminderCounts = Record;\n\nexport async function reminderCounts(): Promise {\n const result = await sqlite.execute(\n \"SELECT state, COUNT(*) AS total FROM notification_queue GROUP BY state\",\n );\n return Object.fromEntries(\n (result.rows as { state: string; total: number }[])\n .map((row) => [row.state, Number(row.total)]),\n );\n}\n","deleted",false,"label",[47,24],"lib","27c7d349-8dc0-4ddf-8288-3f5cab6fe8a0",["D",1787009561780],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","The main web app and server API for Clark View","imageUrl","userId","b0a8da26-58e7-4d19-8b5c-eedf487df9a9","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","5a10edca-919b-4a78-babb-71908f69af93","tursoName","27c7d3498dc04ddf82883f5cab6fe8a0","tursoHostname","27c7d3498dc04ddf82883f5cab6fe8a0-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789532458193],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_9":10,"_107":103},"org",{"_9":10,"_101":-5,"_102":103,"_58":59,"_104":105,"_106":44},"branch",{"_37":91,"_11":92,"_22":30,"_93":48,"_94":-5,"_95":-5,"_96":-5,"_97":-5,"_98":-5,"_99":12,"_100":10},"isOwned",346,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","avatar","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yVHpab25kTlBTM3M3WmpCa3NMSTd5akVwWksuanBlZyJ9","tier","free","canRestrictVals","profileImageUrl","ctx",{"_84":-5,"_141":142,"_143":144,"_145":146,"_147":44,"_148":44,"_149":44,"_150":34,"_151":-5,"_152":-7,"_153":-7,"_154":65,"_155":44,"_156":44,"_157":-5,"_158":44,"_159":44,"_160":44,"_161":-5},"ENV",{"_112":113,"_114":115,"_116":92,"_117":118,"_119":120,"_121":122,"_123":124,"_125":126,"_127":128,"_129":130,"_131":132,"_133":134,"_135":136,"_137":138,"_139":140},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","e87eb04e707cf64e7e532c8e8ec395f209e17f15","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","orgs",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]