[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8},"actionData","errors","root",{"_111":112,"_113":114},"routes/embed.x.$handle.$name.$",{"_9":10,"_11":12,"_13":14,"_15":16,"_17":18,"_19":20,"_21":-5},"handle","pikachu","name","Quickstart-Full-Stack","path","backend/db.ts","project",{"_22":48,"_11":12,"_35":49,"_50":-5,"_51":44,"_52":53,"_54":53,"_55":56,"_57":58,"_59":60,"_61":62,"_63":34,"_64":-5,"_65":-5,"_66":67,"_68":69,"_70":71,"_72":73,"_74":75,"_76":-5,"_77":78,"_79":78,"_80":81,"_82":81,"_83":81,"_84":78,"_85":78,"_86":87,"_88":89,"_90":91,"_92":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/pikachu/Quickstart-Full-Stack/code/backend/db.ts","extension","id","13e917a6-48a2-4036-8e6e-cc9fdd7d8442","db.ts","fileId","aad2cfa2-48c5-11f1-bfb8-42b51c65c3df","parentId","8630361c-4951-11f1-a193-42b51c65c3df","branchId","5a0a7ac8-4895-11f1-80ec-42b51c65c3df","branchVersion",341,"latest",true,"createdAt",["D",1788140102565],"version",17,"fileType","script","content","// db.ts — SQLite helpers for this App's isolated database.\n// Each val (App) gets its own SQLite — no shared namespace with other vals.\n// Docs: https://docs.val.town/reference/std/sqlite/\n\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { DEMO_DATA_RETENTION_HOURS } from \"./shared.ts\";\n\n// ── Schema Migration ──────────────────────────────────────────────────────────\n// Lazily run on first DB call, not at import time. This prevents crashes\n// when running locally (off Val Town) where the SQLite API isn't available.\n\nlet migration: Promise | null = null;\n\n/** Share one migration across concurrent calls and reset it after failure. */\nasync function migrate(): Promise {\n migration ??= runMigration();\n await migration;\n}\n\nasync function runMigration(): Promise {\n return await sqlite.batch([\n `CREATE TABLE IF NOT EXISTS visits (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n path TEXT NOT NULL,\n ts TEXT NOT NULL DEFAULT (datetime('now'))\n )`,\n `CREATE TABLE IF NOT EXISTS cron_runs (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n task TEXT NOT NULL,\n note TEXT,\n ts TEXT NOT NULL DEFAULT (datetime('now'))\n )`,\n `CREATE TABLE IF NOT EXISTS users (\n username TEXT PRIMARY KEY,\n email TEXT,\n first_login TEXT NOT NULL DEFAULT (datetime('now')),\n last_login TEXT NOT NULL DEFAULT (datetime('now')),\n is_admin INTEGER NOT NULL DEFAULT 0\n )`,\n `CREATE TABLE IF NOT EXISTS reminders (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n title TEXT NOT NULL,\n note TEXT,\n due_date TEXT,\n done INTEGER NOT NULL DEFAULT 0,\n created_by TEXT NOT NULL,\n created_at TEXT NOT NULL DEFAULT (datetime('now'))\n )`,\n `CREATE TABLE IF NOT EXISTS inbound_emails (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n from_addr TEXT NOT NULL,\n subject TEXT,\n is_spam INTEGER NOT NULL DEFAULT 0,\n summary TEXT,\n received_at TEXT NOT NULL DEFAULT (datetime('now'))\n )`,\n ]).then(() => undefined).catch((error) => {\n migration = null;\n throw error;\n });\n}\n\n// ── Visits ────────────────────────────────────────────────────────────────────\n\n/** Record a page visit. */\nexport async function recordVisit(path: string): Promise {\n await migrate();\n await sqlite.execute({\n sql: `INSERT INTO visits (path) VALUES (?)`,\n args: [path],\n });\n}\n\n/** Get total visit count. */\nexport async function getVisitCount(): Promise {\n await migrate();\n const { rows } = await sqlite.execute(\n `SELECT COUNT(*) as count FROM visits`,\n );\n // Row supports [key: number] indexing — no cast required.\n return Number(rows[0][0]);\n}\n\n/** Get the N most recent visits. */\nexport async function getRecentVisits(\n limit = 20,\n): Promise> {\n await migrate();\n const { rows } = await sqlite.execute({\n sql: `SELECT id, path, ts FROM visits ORDER BY ts DESC LIMIT ?`,\n args: [limit],\n });\n // Row is ArrayLike with [key: number]: Value — index directly, no cast.\n return rows.map((r) => ({\n id: Number(r[0]),\n path: String(r[1]),\n ts: String(r[2]),\n }));\n}\n\n/** Delete visits older than `days` days. Idempotent — safe to call repeatedly. */\nexport async function pruneOldVisits(days = 30): Promise {\n await migrate();\n const interval = `-${Math.floor(Math.abs(days))} days`;\n await sqlite.execute({\n sql: `DELETE FROM visits WHERE ts < datetime('now', ?)`,\n args: [interval],\n });\n}\n\n// ── Cron runs ─────────────────────────────────────────────────────────────────\n\n/** Append a cron run log entry. Called by cron.ts tasks. */\nexport async function logCronRun(task: string, note?: string): Promise {\n await migrate();\n await sqlite.execute({\n sql: `INSERT INTO cron_runs (task, note) VALUES (?, ?)`,\n args: [task, note ?? null],\n });\n}\n\n/** Get the N most recent cron run entries across all tasks. */\nexport async function getCronRuns(\n limit = 30,\n): Promise<\n Array<{ id: number; task: string; note: string | null; ts: string }>\n> {\n await migrate();\n const { rows } = await sqlite.execute({\n sql: `SELECT id, task, note, ts FROM cron_runs ORDER BY ts DESC LIMIT ?`,\n args: [limit],\n });\n // Row is ArrayLike with [key: number]: Value — index directly, no cast.\n return rows.map((r) => ({\n id: Number(r[0]),\n task: String(r[1]),\n note: r[2] != null ? String(r[2]) : null,\n ts: String(r[3]),\n }));\n}\n\n// ── Users ─────────────────────────────────────────────────────────────────────\n\nexport interface UserRow {\n username: string;\n email: string | null;\n first_login: string;\n last_login: string;\n is_admin: boolean;\n}\n\n/** Upsert user on login — sets first_login on create, updates last_login always. */\nexport async function upsertUser(\n username: string,\n email?: string | null,\n): Promise {\n await migrate();\n await sqlite.execute({\n sql: `INSERT INTO users (username, email)\n VALUES (?, ?)\n ON CONFLICT(username) DO UPDATE SET\n last_login = datetime('now'),\n email = COALESCE(excluded.email, users.email)`,\n args: [username, email ?? null],\n });\n}\n\n/** Get a single user by username. */\nexport async function getUser(username: string): Promise {\n await migrate();\n const { rows } = await sqlite.execute({\n sql: `SELECT username, email, first_login, last_login, is_admin FROM users WHERE username = ?`,\n args: [username],\n });\n if (!rows.length) return null;\n return rowToUser(rows[0]);\n}\n\n/** List all registered users (most recent login first). */\nexport async function listUsers(): Promise {\n await migrate();\n const { rows } = await sqlite.execute(\n `SELECT username, email, first_login, last_login, is_admin FROM users ORDER BY last_login DESC`,\n );\n return rows.map(rowToUser);\n}\n\n/** Set is_admin flag for a user. Only super-admins should call this. */\nexport async function setUserAdmin(\n username: string,\n isAdmin: boolean,\n): Promise {\n await migrate();\n const { rowsAffected } = await sqlite.execute({\n sql: `UPDATE users SET is_admin = ? WHERE username = ?`,\n args: [isAdmin ? 1 : 0, username],\n });\n return rowsAffected > 0;\n}\n\nfunction rowToUser(r: ArrayLike): UserRow {\n return {\n username: String(r[0]),\n email: r[1] != null ? String(r[1]) : null,\n first_login: String(r[2]),\n last_login: String(r[3]),\n is_admin: Number(r[4]) === 1,\n };\n}\n\n// ── Reminders — the flagship shared \"tiny app\" demo ───────────────────────────\n// Public read, sign-in-required write (see backend/auth.ts's getAuthedUc).\n// Demo data — filtered at read time AND physically pruned by cron.ts's pulse\n// so the \"wiped every 24 hours\" promise holds even between cron ticks.\n\nexport interface Reminder {\n id: number;\n title: string;\n note: string | null;\n dueDate: string | null;\n done: boolean;\n createdBy: string;\n createdAt: string;\n}\n\nexport async function createReminder(\n title: string,\n note: string | null,\n dueDate: string | null,\n createdBy: string,\n): Promise {\n await migrate();\n await sqlite.execute({\n sql: `INSERT INTO reminders (title, note, due_date, created_by) VALUES (?, ?, ?, ?)`,\n args: [title, note, dueDate, createdBy],\n });\n}\n\n/** List reminders created within the retention window — oldest expired rows never surface. */\nexport async function listReminders(\n hours = DEMO_DATA_RETENTION_HOURS,\n): Promise {\n await migrate();\n const { rows } = await sqlite.execute({\n sql: `SELECT id, title, note, due_date, done, created_by, created_at\n FROM reminders\n WHERE created_at > datetime('now', ?)\n ORDER BY done ASC, (due_date IS NULL), due_date ASC, created_at DESC`,\n args: [`-${hours} hours`],\n });\n return rows.map(rowToReminder);\n}\n\nexport async function setReminderDone(id: number, done: boolean): Promise {\n await migrate();\n const { rowsAffected } = await sqlite.execute({\n sql: `UPDATE reminders SET done = ? WHERE id = ?`,\n args: [done ? 1 : 0, id],\n });\n return rowsAffected > 0;\n}\n\nexport async function deleteReminder(id: number): Promise {\n await migrate();\n const { rowsAffected } = await sqlite.execute({\n sql: `DELETE FROM reminders WHERE id = ?`,\n args: [id],\n });\n return rowsAffected > 0;\n}\n\n/** Physically delete reminders past the retention window. Idempotent. */\nexport async function pruneOldReminders(hours = DEMO_DATA_RETENTION_HOURS): Promise {\n await migrate();\n await sqlite.execute({\n sql: `DELETE FROM reminders WHERE created_at < datetime('now', ?)`,\n args: [`-${hours} hours`],\n });\n}\n\nfunction rowToReminder(r: ArrayLike): Reminder {\n return {\n id: Number(r[0]),\n title: String(r[1]),\n note: r[2] != null ? String(r[2]) : null,\n dueDate: r[3] != null ? String(r[3]) : null,\n done: Number(r[4]) === 1,\n createdBy: String(r[5]),\n createdAt: String(r[6]),\n };\n}\n\n// ── Inbound emails — the AI spam/summary triage demo ──────────────────────────\n// Public read-only. Written by email.ts's inbound handler. Same 24h demo-data\n// retention as Reminders.\n\nexport interface InboundEmail {\n id: number;\n from: string;\n subject: string;\n isSpam: boolean;\n summary: string | null;\n receivedAt: string;\n}\n\nexport async function recordInboundEmail(\n from: string,\n subject: string,\n isSpam: boolean,\n summary: string | null,\n): Promise {\n await migrate();\n await sqlite.execute({\n sql: `INSERT INTO inbound_emails (from_addr, subject, is_spam, summary) VALUES (?, ?, ?, ?)`,\n args: [from, subject, isSpam ? 1 : 0, summary],\n });\n}\n\nexport async function listRecentEmails(\n hours = DEMO_DATA_RETENTION_HOURS,\n limit = 20,\n): Promise {\n await migrate();\n const { rows } = await sqlite.execute({\n sql: `SELECT id, from_addr, subject, is_spam, summary, received_at\n FROM inbound_emails\n WHERE received_at > datetime('now', ?)\n ORDER BY received_at DESC\n LIMIT ?`,\n args: [`-${hours} hours`, limit],\n });\n return rows.map(rowToInboundEmail);\n}\n\n/** Physically delete inbound emails past the retention window. Idempotent. */\nexport async function pruneOldEmails(hours = DEMO_DATA_RETENTION_HOURS): Promise {\n await migrate();\n await sqlite.execute({\n sql: `DELETE FROM inbound_emails WHERE received_at < datetime('now', ?)`,\n args: [`-${hours} hours`],\n });\n}\n\nfunction rowToInboundEmail(r: ArrayLike): InboundEmail {\n return {\n id: Number(r[0]),\n from: String(r[1]),\n subject: String(r[2]),\n isSpam: Number(r[3]) === 1,\n summary: r[4] != null ? String(r[4]) : null,\n receivedAt: String(r[5]),\n };\n}\n","deleted",false,"label",[47,24],"backend","5a09ae54-4895-11f1-80ec-42b51c65c3df",["D",1777994172310],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","An elegant template for an elegant single-person full stack app.","imageUrl","https://imagedelivery.net/iHX6Ovru0O7AjmyT5yZRoA/3ed9401e-3850-4a69-dec2-1bdd924c0900/square512?exp=1789531199&sig=25e0839584bbf63595c6ac6dbd78175c0b770a9cb42a70bb0a297f376a4b3c61","userId","173e7424-8658-428f-a3bc-ae4faeaabd1c","pinnedFileId","e1c705aa-4b18-11f1-b4ae-ee650bb23af1","pinPreview","pinMigrated","screenshotVersion","likeCount",1,"orgId","019df771-9af8-7238-99a4-541e39355038","tursoName","5a09ae54489511f180ec42b51c65c3df","tursoHostname","5a09ae54489511f180ec42b51c65c3df-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789511441255],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_9":10,"_110":106},"org",{"_9":10,"_103":104,"_105":106,"_59":60,"_107":108,"_109":44},"branch",{"_37":93,"_11":94,"_22":30,"_95":48,"_96":-5,"_97":-5,"_98":-5,"_99":-5,"_100":-5,"_101":12,"_102":10},"isOwned",384,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","Minion","avatar","https://imagedelivery.net/iHX6Ovru0O7AjmyT5yZRoA/22c8ba37-42c8-4af4-8daf-8cdd2f5f1800/square512?exp=1789531199&sig=05babba3fe8cea299fe2e8213e88092eb4dfe7ad37c094bcfea9f9ef7e96f614","tier","free","canRestrictVals","profileImageUrl","ctx",{"_86":-5,"_144":145,"_146":147,"_148":149,"_150":44,"_151":44,"_152":44,"_153":34,"_154":-5,"_155":-7,"_156":-7,"_157":158,"_159":44,"_160":44,"_161":-5,"_162":44,"_163":44,"_164":44,"_165":-5},"ENV",{"_115":116,"_117":118,"_119":94,"_120":121,"_122":123,"_124":125,"_126":127,"_128":129,"_130":131,"_132":133,"_134":135,"_136":137,"_138":139,"_140":141,"_142":143},"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",0,"flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]