[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8},"actionData","errors","root",{"_105":106,"_107":108},"routes/embed.x.$handle.$name.$",{"_9":10,"_11":12,"_13":14,"_15":16,"_17":18,"_19":20,"_21":-5},"handle","colel","name","newsletter-signup","path","db.ts","project",{"_22":45,"_11":12,"_33":46,"_47":-5,"_48":42,"_49":50,"_51":50,"_52":53,"_54":-5,"_55":56,"_57":-5,"_58":42,"_59":-5,"_60":-5,"_61":62,"_63":64,"_65":66,"_67":68,"_69":70,"_71":-5,"_72":73,"_74":73,"_75":76,"_77":76,"_78":76,"_79":73,"_80":73,"_81":82,"_83":84,"_85":86,"_87":42},"file",{"_22":23,"_11":14,"_24":25,"_26":-5,"_27":28,"_29":30,"_31":32,"_33":34,"_35":36,"_37":38,"_39":40,"_41":42,"_43":-5,"_13":44},"permalink","https://www.val.town/x/colel/newsletter-signup/code/db.ts","extension","id","9e1cd65d-a4f9-491e-8c50-f359f40ee992","fileId","bddbfe22-9af8-11f1-9164-1607ee4eb77e","parentId","branchId","d72d2544-fed2-11f0-a1dd-42dde27851f2","branchVersion",42,"latest",true,"createdAt",["D",1787056142588],"version",1,"fileType","script","content","/**\n * Database schema + queries.\n *\n * Tables:\n * - newsletter_subscribers: signups (email + flexible JSON details + notes)\n * - ntfy_receivers: configurable ntfy.sh destinations, each with an optional filter\n * - ntfy_deliveries: audit log of notification attempts (sent / failed / skipped)\n */\n\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport type { MatchMode } from \"./filter.ts\";\n\nexport type NtfyEvent = \"signup\" | \"details\";\nexport type DeliveryOutcome = \"sent\" | \"failed\" | \"skipped\";\n\nexport interface Subscriber {\n id: number;\n email: string;\n details: Record | null;\n developer_notes: string | null;\n created_at: string;\n updated_at: string;\n}\n\nexport interface Receiver {\n id: number;\n name: string;\n server: string;\n topic: string;\n priority: number;\n tags: string | null;\n /** ntfy access token (tk_…) or \"user:password\" for basic auth. */\n auth: string | null;\n /** Comma-separated list of NtfyEvent values. */\n events: string;\n /** Filter rules, one per line — see filter.ts. Null/empty means \"notify always\". */\n filter: string | null;\n filter_mode: MatchMode;\n enabled: number;\n created_at: string;\n updated_at: string;\n}\n\nexport interface Delivery {\n id: number;\n receiver_id: number | null;\n receiver_name: string;\n subscriber_id: number | null;\n event: string;\n outcome: DeliveryOutcome;\n ok: number;\n status: number | null;\n /** Failure message, or the filter reason for skipped deliveries. */\n error: string | null;\n created_at: string;\n}\n\nawait sqlite.execute(`\n CREATE TABLE IF NOT EXISTS newsletter_subscribers (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n email TEXT NOT NULL,\n details TEXT,\n developer_notes TEXT,\n created_at TEXT DEFAULT (datetime('now')),\n updated_at TEXT DEFAULT (datetime('now'))\n )\n`);\n\nawait sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ntfy_receivers (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n server TEXT NOT NULL DEFAULT 'https://ntfy.sh',\n topic TEXT NOT NULL,\n priority INTEGER NOT NULL DEFAULT 3,\n tags TEXT,\n auth TEXT,\n events TEXT NOT NULL DEFAULT 'signup',\n filter TEXT,\n filter_mode TEXT NOT NULL DEFAULT 'all',\n enabled INTEGER NOT NULL DEFAULT 1,\n created_at TEXT DEFAULT (datetime('now')),\n updated_at TEXT DEFAULT (datetime('now'))\n )\n`);\n\nawait sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ntfy_deliveries (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n receiver_id INTEGER,\n receiver_name TEXT NOT NULL,\n subscriber_id INTEGER,\n event TEXT NOT NULL,\n outcome TEXT NOT NULL DEFAULT 'sent',\n ok INTEGER NOT NULL,\n status INTEGER,\n error TEXT,\n created_at TEXT DEFAULT (datetime('now'))\n )\n`);\n\n/** Adds columns to already-existing tables (SQLite has no IF NOT EXISTS for columns). */\nasync function ensureColumns(table: string, columns: Record) {\n const info = await sqlite.execute(`PRAGMA table_info(${table})`);\n const existing = new Set(\n info.rows.map((row: any) => String(row.name ?? row[1] ?? \"\")),\n );\n for (const [name, definition] of Object.entries(columns)) {\n if (existing.has(name)) continue;\n await sqlite.execute(`ALTER TABLE ${table} ADD COLUMN ${name} ${definition}`);\n }\n}\n\nawait ensureColumns(\"ntfy_receivers\", {\n filter: \"TEXT\",\n filter_mode: \"TEXT NOT NULL DEFAULT 'all'\",\n});\nawait ensureColumns(\"ntfy_deliveries\", {\n outcome: \"TEXT NOT NULL DEFAULT 'sent'\",\n});\n\nfunction parseDetails(raw: unknown): Record | null {\n if (!raw || typeof raw !== \"string\") return null;\n try {\n return JSON.parse(raw);\n } catch {\n return { _unparsed: raw };\n }\n}\n\nfunction toSubscriber(row: any): Subscriber {\n return {\n id: Number(row.id),\n email: String(row.email),\n details: parseDetails(row.details),\n developer_notes: row.developer_notes ?? null,\n created_at: String(row.created_at),\n updated_at: String(row.updated_at),\n };\n}\n\n/* ---------------------------------- subscribers --------------------------------- */\n\nexport async function insertSubscriber(email: string, details: unknown): Promise {\n const res = await sqlite.execute({\n sql: \"INSERT INTO newsletter_subscribers (email, details) VALUES (?, ?)\",\n args: [email, details ? JSON.stringify(details) : null],\n });\n return Number(res.lastInsertRowid);\n}\n\nexport async function getSubscriber(id: number | string): Promise {\n const res = await sqlite.execute({\n sql: \"SELECT * FROM newsletter_subscribers WHERE id = ?\",\n args: [id],\n });\n return res.rows.length ? toSubscriber(res.rows[0]) : null;\n}\n\nexport async function listSubscribers(limit = 500): Promise {\n const res = await sqlite.execute({\n sql: \"SELECT * FROM newsletter_subscribers ORDER BY id DESC LIMIT ?\",\n args: [limit],\n });\n return res.rows.map(toSubscriber);\n}\n\nexport async function countSubscribers(): Promise {\n const res = await sqlite.execute(\"SELECT COUNT(*) AS n FROM newsletter_subscribers\");\n return Number((res.rows[0] as any).n);\n}\n\n/** Shallow-merges `details` into the subscriber's existing details. */\nexport async function mergeSubscriberDetails(\n id: number | string,\n details: Record,\n): Promise | null> {\n const existing = await getSubscriber(id);\n if (!existing) return null;\n const merged = { ...(existing.details ?? {}), ...details };\n await sqlite.execute({\n sql: \"UPDATE newsletter_subscribers SET details = ?, updated_at = datetime('now') WHERE id = ?\",\n args: [JSON.stringify(merged), id],\n });\n return merged;\n}\n\nexport async function updateSubscriber(\n id: number | string,\n fields: { details?: unknown; developer_notes?: string },\n): Promise {\n const updates: string[] = [];\n const args: any[] = [];\n if (fields.details !== undefined) {\n updates.push(\"details = ?\");\n args.push(fields.details ? JSON.stringify(fields.details) : null);\n }\n if (fields.developer_notes !== undefined) {\n updates.push(\"developer_notes = ?\");\n args.push(fields.developer_notes);\n }\n if (!updates.length) return false;\n updates.push(\"updated_at = datetime('now')\");\n args.push(id);\n await sqlite.execute({\n sql: `UPDATE newsletter_subscribers SET ${updates.join(\", \")} WHERE id = ?`,\n args,\n });\n return true;\n}\n\nexport async function deleteSubscriber(id: number | string): Promise {\n await sqlite.execute({\n sql: \"DELETE FROM newsletter_subscribers WHERE id = ?\",\n args: [id],\n });\n}\n\n/* ----------------------------------- receivers ---------------------------------- */\n\nfunction toReceiver(row: any): Receiver {\n return {\n id: Number(row.id),\n name: String(row.name),\n server: String(row.server),\n topic: String(row.topic),\n priority: Number(row.priority),\n tags: row.tags ?? null,\n auth: row.auth ?? null,\n events: String(row.events),\n filter: row.filter ?? null,\n filter_mode: row.filter_mode === \"any\" ? \"any\" : \"all\",\n enabled: Number(row.enabled),\n created_at: String(row.created_at),\n updated_at: String(row.updated_at),\n };\n}\n\nexport async function listReceivers(): Promise {\n const res = await sqlite.execute(\"SELECT * FROM ntfy_receivers ORDER BY id ASC\");\n return res.rows.map(toReceiver);\n}\n\nexport async function getReceiver(id: number | string): Promise {\n const res = await sqlite.execute({\n sql: \"SELECT * FROM ntfy_receivers WHERE id = ?\",\n args: [id],\n });\n return res.rows.length ? toReceiver(res.rows[0]) : null;\n}\n\n/** Enabled receivers subscribed to a given event (filters are applied later, per signup). */\nexport async function receiversForEvent(event: NtfyEvent): Promise {\n const all = await listReceivers();\n return all.filter(r => r.enabled && r.events.split(\",\").map(e => e.trim()).includes(event));\n}\n\nexport type ReceiverInput = {\n name: string;\n server: string;\n topic: string;\n priority: number;\n tags: string | null;\n auth: string | null;\n events: string;\n filter: string | null;\n filter_mode: MatchMode;\n enabled: number;\n};\n\nexport async function createReceiver(input: ReceiverInput): Promise {\n const res = await sqlite.execute({\n sql: `INSERT INTO ntfy_receivers\n (name, server, topic, priority, tags, auth, events, filter, filter_mode, enabled)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n args: [\n input.name,\n input.server,\n input.topic,\n input.priority,\n input.tags,\n input.auth,\n input.events,\n input.filter,\n input.filter_mode,\n input.enabled,\n ],\n });\n return Number(res.lastInsertRowid);\n}\n\nexport async function updateReceiver(\n id: number | string,\n input: Partial,\n): Promise {\n const updates: string[] = [];\n const args: any[] = [];\n for (const [key, value] of Object.entries(input)) {\n if (value === undefined) continue;\n updates.push(`${key} = ?`);\n args.push(value as any);\n }\n if (!updates.length) return;\n updates.push(\"updated_at = datetime('now')\");\n args.push(id);\n await sqlite.execute({\n sql: `UPDATE ntfy_receivers SET ${updates.join(\", \")} WHERE id = ?`,\n args,\n });\n}\n\nexport async function deleteReceiver(id: number | string): Promise {\n await sqlite.execute({ sql: \"DELETE FROM ntfy_receivers WHERE id = ?\", args: [id] });\n}\n\n/* ---------------------------------- deliveries ---------------------------------- */\n\nexport async function logDelivery(entry: {\n receiver_id: number | null;\n receiver_name: string;\n subscriber_id: number | null;\n event: string;\n outcome: DeliveryOutcome;\n status: number | null;\n error: string | null;\n}): Promise {\n await sqlite.execute({\n sql: `INSERT INTO ntfy_deliveries\n (receiver_id, receiver_name, subscriber_id, event, outcome, ok, status, error)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,\n args: [\n entry.receiver_id,\n entry.receiver_name,\n entry.subscriber_id,\n entry.event,\n entry.outcome,\n entry.outcome === \"sent\" ? 1 : 0,\n entry.status,\n entry.error,\n ],\n });\n}\n\nexport async function recentDeliveries(limit = 25): Promise {\n const res = await sqlite.execute({\n sql: \"SELECT * FROM ntfy_deliveries ORDER BY id DESC LIMIT ?\",\n args: [limit],\n });\n return res.rows.map((row: any) => ({\n id: Number(row.id),\n receiver_id: row.receiver_id === null ? null : Number(row.receiver_id),\n receiver_name: String(row.receiver_name),\n subscriber_id: row.subscriber_id === null ? null : Number(row.subscriber_id),\n event: String(row.event),\n outcome: (row.outcome ?? (Number(row.ok) ? \"sent\" : \"failed\")) as DeliveryOutcome,\n ok: Number(row.ok),\n status: row.status === null ? null : Number(row.status),\n error: row.error ?? null,\n created_at: String(row.created_at),\n }));\n}\n","deleted",false,"label",[14],"d72cb6b8-fed2-11f0-a1dd-42dde27851f2",["D",1769884195507],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","Signup API with ntfy push alerts and an admin dashboard","imageUrl","userId","c821e8f7-4cc4-4c23-96f1-319b8f33d4b7","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","6b193d60-e165-45a9-869c-c3f886455f3e","tursoName","d72cb6b8fed211f0a1dd42dde27851f2","tursoHostname","d72cb6b8fed211f0a1dd42dde27851f2-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789447273596],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_9":10,"_104":100},"org",{"_9":10,"_98":-5,"_99":100,"_55":56,"_101":102,"_103":42},"branch",{"_35":88,"_11":89,"_22":28,"_90":45,"_91":-5,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":12,"_97":10},"isOwned",51,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","avatar","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvdXBsb2FkZWQvaW1nXzJ6RVZCOExVNXBIT3FrVDh5a0VZQTNRRG5ycCJ9","tier","pro","canRestrictVals","profileImageUrl","ctx",{"_81":-5,"_138":139,"_140":141,"_142":143,"_144":42,"_145":42,"_146":42,"_147":32,"_148":-5,"_149":-7,"_150":-7,"_151":62,"_152":42,"_153":42,"_154":-5,"_155":42,"_156":42,"_157":42,"_158":-5},"ENV",{"_109":110,"_111":112,"_113":89,"_114":115,"_116":117,"_118":119,"_120":121,"_122":123,"_124":125,"_126":127,"_128":129,"_130":131,"_132":133,"_134":135,"_136":137},"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"]