[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_199":200,"_201":202},"routes/_app.x.$handle.$name",{"_100":99,"_32":45,"_107":90,"_27":53,"_13":14,"_108":-5,"_11":109,"_110":111,"_112":113,"_114":115,"_116":117,"_20":-5,"_118":-5,"_119":-5,"_120":25,"_121":122,"_123":59,"_124":-5,"_125":-5,"_126":127},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_43":44,"_32":45,"_38":46,"_47":-5,"_48":25,"_49":50,"_51":50,"_52":53,"_54":-5,"_18":55,"_56":57,"_58":59,"_60":-5,"_61":-5,"_62":63,"_64":65,"_66":67,"_68":69,"_70":71,"_72":-5,"_73":74,"_75":74,"_76":77,"_78":77,"_79":77,"_80":74,"_81":74,"_82":83,"_84":85,"_86":87,"_88":25},"version",316,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":23,"_24":25,"_26":25,"_27":28,"_29":28},"file",{"_30":31,"_32":28,"_33":34,"_35":37,"_38":39,"_40":14,"_41":42,"_24":25},"previous",{"_30":31,"_32":28,"_33":34,"_35":36},"deleted",false,"deletedHere","path","analytics_dashboard.ts","latestPath","fileId","5a9b2d3c-5d37-11f1-9376-1607ee4eb77e","name","fileType","http","content","import sqlite from \"https://esm.town/v/std/sqlite/main.ts\";\n\nconst tables = [\"events\", \"crashes\", \"logs\"] as const;\n\n// Cold starts would otherwise re-run every migration statement (each a DB\n// round trip) before the first query can be served; one probe skips the\n// whole block once the schema is fully in place.\nconst schemaProbe = await sqlite.batch([\n {\n sql: `SELECT (SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name IN ('events','crashes','logs','sessions')) AS tables_found, (SELECT COUNT(*) FROM pragma_table_info('events')) AS event_columns`,\n args: [],\n },\n]);\nconst probe = schemaProbe[0].rows[0] as\n | { tables_found?: number | string; event_columns?: number | string }\n | undefined;\nif (\n !probe ||\n Number(probe.tables_found) !== 4 ||\n Number(probe.event_columns) !== 12\n) {\n for (const table of tables) {\n const migrations = [\n `CREATE TABLE IF NOT EXISTS ${table} (id INTEGER PRIMARY KEY AUTOINCREMENT, device_id TEXT, session_id TEXT, platform TEXT, app TEXT, version TEXT, build TEXT, build_id TEXT, portal_name TEXT, page_url TEXT, data TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP)`,\n `ALTER TABLE ${table} ADD COLUMN device_id TEXT`,\n `ALTER TABLE ${table} ADD COLUMN session_id TEXT`,\n `ALTER TABLE ${table} ADD COLUMN platform TEXT`,\n `ALTER TABLE ${table} ADD COLUMN app TEXT`,\n `ALTER TABLE ${table} ADD COLUMN version TEXT`,\n `ALTER TABLE ${table} ADD COLUMN build TEXT`,\n `ALTER TABLE ${table} ADD COLUMN build_id TEXT`,\n `ALTER TABLE ${table} ADD COLUMN portal_name TEXT`,\n `ALTER TABLE ${table} ADD COLUMN page_url TEXT`,\n `ALTER TABLE ${table} ADD COLUMN data TEXT`,\n `ALTER TABLE ${table} ADD COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP`,\n ];\n for (const sql of migrations) {\n try {\n await sqlite.batch([{ sql, args: [] }]);\n } catch {}\n }\n }\n\n await sqlite.batch([\n {\n sql: `CREATE TABLE IF NOT EXISTS sessions (session_id TEXT PRIMARY KEY, device_id TEXT, platform TEXT, version TEXT, portal_name TEXT, surface TEXT, first_at DATETIME, last_at DATETIME)`,\n args: [],\n },\n ]);\n}\n\nconst HTML = `\n\n\n\n\nCrownspell Analytics\n\n\n\n\n\n

Crownspell Analytics

\n \n \n \n \n \n \n \n \n

\n\n

\n\n\n\n

\n\n

Platforms & Portals

\n\n

D1 Over Time

Daily device cohorts; D1 = active again the next day. Only cohorts whose return window has elapsed

\n\n

Retention Cohort

\n\n

Engagement

\n\n

Session Funnel

\n\n

Day-0 Progression Tiers (devices)

\n\n

Web Loading

\n\n

Crashes

\n\n

Card Battle Outcomes

\n\n

Battle-1 Abandonment Depth

\n\n

Battle-1 Duration

\n\n

Card Battle Analytics

\n\n

Run Map — Node Selections by Row

Run Map — Depth Reached

\n\n

Spell Usage

Spells vs Battles

\n\n

Edict Usage

Armory Uptake

\n\n

Crown Seal Sources

Resolve Economy

\n\n

Ability Usage

\n\n

Level Progression

\n\n

Raw Log Reports

\n\n

Live Pulse

\n\n

Version Breakdown

\n\n

Events Over Time

\n\n

Top Event Types

\n\n

Recent Events

Crownspell Analytics

\n \n \n \n \n \n \n \n \n

\n\n

\n\n\n\n

\n\n

Platforms & Portals

\n\n

D1 Over Time

Daily device cohorts; D1 = active again the next day. Only cohorts whose return window has elapsed

\n\n

Retention Cohort

\n\n

Engagement

\n\n

Session Funnel

\n\n

Day-0 Progression Tiers (devices)

\n\n

Web Loading

\n\n

Crashes

\n\n

Card Battle Outcomes

\n\n

Battle-1 Abandonment Depth

\n\n

Battle-1 Duration

\n\n

Card Battle Analytics

\n\n

Run Map — Node Selections by Row

Run Map — Depth Reached

\n\n

Spell Usage

Spells vs Battles

\n\n

Edict Usage

Armory Uptake

\n\n

Crown Seal Sources

Resolve Economy

\n\n

Ability Usage

\n\n

Level Progression

\n\n

Raw Log Reports

\n\n

Live Pulse

\n\n

Version Breakdown

\n\n

Events Over Time

\n\n

Top Event Types

\n\n

Recent Events

\n\n\n\n`;\n\nconst DASHBOARD_USER = Deno.env.get(\"DASHBOARD_USER\");\nconst DASHBOARD_PASS = Deno.env.get(\"DASHBOARD_PASS\");\n\nfunction checkAuth(req: Request): Response | null {\n if (!DASHBOARD_USER || !DASHBOARD_PASS) return null;\n const auth = req.headers.get(\"Authorization\");\n if (!auth || !auth.startsWith(\"Basic \")) {\n return new Response(\"Unauthorized\", {\n status: 401,\n headers: { \"WWW-Authenticate\": 'Basic realm=\"Analytics\"' },\n });\n }\n const decoded = atob(auth.slice(6));\n const [user, pass] = decoded.split(\":\");\n if (user !== DASHBOARD_USER || pass !== DASHBOARD_PASS) {\n return new Response(\"Unauthorized\", {\n status: 401,\n headers: { \"WWW-Authenticate\": 'Basic realm=\"Analytics\"' },\n });\n }\n return null;\n}\n\n// Data is append-only and the dashboard auto-refreshes every 60s, so a\n// short-lived result cache keyed by final SQL+args makes warm reloads and\n// filter toggling near-instant without showing meaningfully stale data.\nconst CACHE_TTL_MS = 30_000;\nconst queryCache = new Map<\n string,\n { at: number; rows: unknown[]; truncated: boolean }\n>();\n\nfunction cacheGet(key: string) {\n const entry = queryCache.get(key);\n if (!entry) return null;\n if (Date.now() - entry.at > CACHE_TTL_MS) {\n queryCache.delete(key);\n return null;\n }\n return entry;\n}\n\nfunction cacheSet(key: string, rows: unknown[], truncated: boolean) {\n if (queryCache.size >= 500) queryCache.clear();\n queryCache.set(key, { at: Date.now(), rows, truncated });\n}\n\nexport default async function (req: Request): Promise {\n const authResponse = checkAuth(req);\n if (authResponse) return authResponse;\n\n const url = new URL(req.url);\n\n if (url.pathname === \"/\" && req.method === \"GET\") {\n return new Response(HTML, {\n headers: { \"Content-Type\": \"text/html; charset=utf-8\" },\n });\n }\n\n if (url.pathname === \"/api/query\" && req.method === \"POST\") {\n let body;\n try {\n body = await req.json();\n } catch {\n return Response.json({ error: \"invalid JSON\" }, { status: 400 });\n }\n\n const { include_devices, versions, portals, platforms, surfaces } = body;\n\n if (include_devices !== undefined) {\n if (\n !Array.isArray(include_devices) ||\n !include_devices.every((d: unknown) => typeof d === \"string\")\n ) {\n return Response.json(\n {\n error: \"include_devices must be an array of strings\",\n },\n { status: 400 },\n );\n }\n }\n\n if (versions !== undefined) {\n if (\n !Array.isArray(versions) ||\n !versions.every((v: unknown) => typeof v === \"string\")\n ) {\n return Response.json(\n { error: \"versions must be an array of strings\" },\n { status: 400 }\n );\n }\n }\n\n if (portals !== undefined) {\n if (\n !Array.isArray(portals) ||\n !portals.every((p: unknown) => typeof p === \"string\")\n ) {\n return Response.json(\n { error: \"portals must be an array of strings\" },\n { status: 400 }\n );\n }\n }\n\n if (platforms !== undefined) {\n if (\n !Array.isArray(platforms) ||\n !platforms.every((p: unknown) => typeof p === \"string\")\n ) {\n return Response.json(\n { error: \"platforms must be an array of strings\" },\n { status: 400 }\n );\n }\n }\n\n if (surfaces !== undefined) {\n if (\n !Array.isArray(surfaces) ||\n !surfaces.every((s: unknown) => typeof s === \"string\")\n ) {\n return Response.json(\n { error: \"surfaces must be an array of strings\" },\n { status: 400 }\n );\n }\n }\n\n // Session-scoped filters resolve against the small derived `sessions`\n // table (maintained by the ingestion endpoint) instead of re-deriving\n // matching sessions from a full scan of `events` on every query. With\n // no filters active, queries run unwrapped against the raw tables.\n const filterColumns: [unknown, string][] = [\n [include_devices, \"device_id\"],\n [versions, \"version\"],\n [portals, \"portal_name\"],\n [platforms, \"platform\"],\n ];\n const sessionPredicates: string[] = [];\n const sessionArgs: (string | number)[] = [];\n for (const [list, column] of filterColumns) {\n if (Array.isArray(list) && list.length > 0) {\n sessionPredicates.push(\n `${column} IN (${list.map(() => \"?\").join(\",\")})`,\n );\n sessionArgs.push(...(list as (string | number)[]));\n }\n }\n // Surface is a session-derived attribute: a session's surface is its\n // first non-null application_type (heartbeat/sdk_lifecycle beacons;\n // native sessions never carry one and resolve to \"unattributed\").\n if (Array.isArray(surfaces) && surfaces.length > 0) {\n sessionPredicates.push(\n `COALESCE(surface, 'unattributed') IN (${surfaces.map(() => \"?\").join(\",\")})`,\n );\n sessionArgs.push(...surfaces);\n }\n const scopedSessions = sessionPredicates.length\n ? `SELECT session_id FROM sessions WHERE ${sessionPredicates.join(\" AND \")}`\n : \"\";\n\n function applyScope(sqlIn: string, argsIn: (string | number)[]) {\n let finalSql = sqlIn.trim();\n const finalArgs: (string | number)[] = [...argsIn];\n if (!scopedSessions) return { finalSql, finalArgs };\n const tableAliases: [string, string][] = [];\n for (const table of tables) {\n if (new RegExp(`\\\\bFROM ${table}\\\\b`, \"i\").test(finalSql)) {\n tableAliases.push([table, \"_f\" + table[0]]);\n }\n }\n if (tableAliases.length === 0) return { finalSql, finalArgs };\n const ctes = tableAliases.map(\n ([table, alias]) =>\n `${alias} AS (SELECT * FROM ${table} WHERE session_id IN (${scopedSessions}))`,\n );\n for (const [table, alias] of tableAliases) {\n finalSql = finalSql.replace(\n new RegExp(`\\\\bFROM ${table}\\\\b`, \"gi\"),\n `FROM ${alias}`,\n );\n }\n if (/^WITH\\b/i.test(finalSql)) {\n finalSql = \"WITH \" + ctes.join(\", \") + \", \" + finalSql.substring(4).trim();\n } else {\n finalSql = \"WITH \" + ctes.join(\", \") + \" \" + finalSql;\n }\n finalArgs.unshift(...tableAliases.flatMap(() => sessionArgs));\n return { finalSql, finalArgs };\n }\n\n // A batch request ({ queries: [...] }) runs every statement in one\n // sqlite.batch round trip; a single request ({ sql, args }) is a batch\n // of one and keeps the { rows, truncated? } response shape.\n const isBatch = Array.isArray(body.queries);\n const items: { sql: unknown; args: unknown }[] = isBatch\n ? body.queries\n : [{ sql: body.sql, args: body.args }];\n\n for (const item of items) {\n const trimmedUpper =\n typeof item.sql === \"string\" ? item.sql.trim().toUpperCase() : \"\";\n // Allow read-only queries: plain SELECT or a WITH (CTE) that selects.\n if (\n typeof item.sql !== \"string\" ||\n !(trimmedUpper.startsWith(\"SELECT\") || trimmedUpper.startsWith(\"WITH\"))\n ) {\n return Response.json(\n { error: \"only SELECT queries allowed\" },\n { status: 400 },\n );\n }\n if (item.args !== undefined && !Array.isArray(item.args)) {\n return Response.json(\n { error: \"args must be an array\" },\n { status: 400 },\n );\n }\n }\n\n const fresh = !!body.fresh;\n const MAX_ROWS = 10000;\n const results: ({ rows: unknown[]; truncated?: boolean } | { error: string })[] =\n [];\n const pending: { index: number; key: string; stmt: { sql: string; args: (string | number)[] } }[] =\n [];\n\n for (let i = 0; i < items.length; i++) {\n const scoped = applyScope(\n items[i].sql as string,\n (Array.isArray(items[i].args) ? items[i].args : []) as (string | number)[],\n );\n const key = JSON.stringify([scoped.finalSql, scoped.finalArgs]);\n if (!fresh) {\n const hit = cacheGet(key);\n if (hit) {\n results[i] = hit.truncated\n ? { rows: hit.rows, truncated: true }\n : { rows: hit.rows };\n continue;\n }\n }\n pending.push({\n index: i,\n key,\n stmt: { sql: scoped.finalSql, args: scoped.finalArgs },\n });\n }\n\n if (pending.length) {\n let outcomes: ({ rows: unknown[] } | null)[];\n try {\n const batched = await sqlite.batch(pending.map((p) => p.stmt));\n outcomes = batched as unknown as { rows: unknown[] }[];\n } catch {\n // sqlite.batch is transactional: one bad statement fails all.\n // Re-run individually so each section keeps its own error state.\n outcomes = [];\n for (const p of pending) {\n try {\n const single = await sqlite.batch([p.stmt]);\n outcomes.push(single[0] as unknown as { rows: unknown[] });\n } catch (e) {\n console.error(\"Query failed:\", e, \"SQL:\", p.stmt.sql);\n outcomes.push(null);\n }\n }\n }\n pending.forEach((p, n) => {\n const outcome = outcomes[n];\n if (!outcome) {\n results[p.index] = { error: \"query execution failed\" };\n return;\n }\n const rows = outcome.rows.slice(0, MAX_ROWS);\n const truncated = outcome.rows.length > MAX_ROWS;\n cacheSet(p.key, rows, truncated);\n results[p.index] = truncated\n ? { rows, truncated: true }\n : { rows };\n });\n }\n\n if (!isBatch) {\n const first = results[0];\n if (first && \"error\" in first) {\n return Response.json({ error: first.error }, { status: 500 });\n }\n return Response.json(first);\n }\n return Response.json({ results });\n }\n\n return Response.json({ error: \"not found\" }, { status: 404 });\n}\n","createdAt",["D",1789435398560],"branchVersion","branchId","87f81046-5ae0-11f1-b550-ee650bb23af1","id","87f76182-5ae0-11f1-b550-ee650bb23af1","analytics",["D",1780005582550],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","","imageUrl","8450179d-7474-4781-b5e7-b56e7752a224","pinnedFileId","df84d868-8528-11f1-8187-1607ee4eb77e","pinPreview",true,"pinMigrated","screenshotVersion","likeCount",0,"orgId","019e6fde-874a-721f-a61f-8402222bdc3b","tursoName","87f761825ae011f1b550ee650bb23af1","tursoHostname","87f761825ae011f1b550ee650bb23af1-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789509043237],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_100":99,"_106":53},"org",{"_100":99,"_101":53,"_102":53,"_18":55,"_103":104,"_105":25},"branch",{"_13":89,"_32":90,"_43":42,"_91":44,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":-5,"_97":45,"_98":99},"isOwned",337,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","mikeyp","handle","displayName","avatar","tier","free","canRestrictVals","profileImageUrl","branchName","adoptOrgId",{"_43":44,"_32":45,"_38":194,"_47":-5,"_48":25,"_49":50,"_51":50,"_52":53,"_54":-5,"_18":55,"_56":57,"_58":59,"_60":-5,"_61":-5,"_62":63,"_64":65,"_66":67,"_68":69,"_70":195,"_72":-5,"_73":74,"_75":74,"_76":77,"_78":77,"_79":77,"_80":74,"_81":74,"_82":196,"_84":197,"_86":198,"_88":25},"tree",[177,178,179,180],"files",[177,178,179,180],"counts",{"_165":63,"_166":167,"_168":63,"_169":170,"_171":172},"contents",[128,129,130,131],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_43":161,"_32":28,"_30":31,"_135":-5,"_41":42,"_40":14,"_137":25,"_38":162,"_13":155,"_33":34,"_35":37,"_24":25,"_142":163,"_27":164},{"_43":153,"_32":154,"_30":57,"_135":-5,"_41":42,"_40":155,"_137":59,"_38":156,"_13":157,"_33":34,"_35":158,"_24":25,"_142":159,"_27":160},{"_43":144,"_32":145,"_30":146,"_135":-5,"_41":42,"_40":147,"_137":59,"_38":148,"_13":149,"_33":34,"_35":150,"_24":25,"_142":151,"_27":152},{"_43":132,"_32":133,"_30":134,"_135":-5,"_41":42,"_40":136,"_137":59,"_38":138,"_13":139,"_33":140,"_35":141,"_24":25,"_142":-5,"_27":143},"dccf0ccb-f5f8-42ea-9a34-c16eec14f9eb","purge_old_data.ts","6148c264-74c7-11f1-965b-1607ee4eb77e","parentId",162,"latest",["D",1782859657365],2,"interval","import sqlite from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { blob } from \"https://esm.town/v/std/blob/main.ts\";\n\nconst RETENTION_DAYS = 90;\nconst CHUNK_SIZE = 200;\nconst MAX_ROWS_PER_RUN = 5000;\n\nexport default async function () {\n const cutoff = new Date(Date.now() - RETENTION_DAYS * 86400000).toISOString();\n const runId = new Date().toISOString().slice(0, 16).replace(/[:T]/g, \"-\");\n const tables = [\"events\", \"crashes\", \"logs\"];\n let totalArchived = 0;\n\n for (const table of tables) {\n const { rows } = await sqlite.execute({\n sql: `SELECT id, device_id, session_id, platform, app, version, data, created_at FROM ${table} WHERE created_at < ? ORDER BY id LIMIT ?`,\n args: [cutoff, MAX_ROWS_PER_RUN],\n });\n\n if (!rows.length) continue;\n\n // Write chunks to blob storage before deleting\n for (let i = 0; i < rows.length; i += CHUNK_SIZE) {\n const chunk = rows.slice(i, i + CHUNK_SIZE);\n const chunkIdx = Math.floor(i / CHUNK_SIZE);\n await blob.setJSON(`archive/${table}_${runId}_${chunkIdx}`, chunk);\n }\n\n // Delete archived rows in sub-batches (SQLite parameter limit)\n const ids = rows.map((r: any) => r.id);\n for (let i = 0; i < ids.length; i += 500) {\n const batch = ids.slice(i, i + 500);\n const placeholders = batch.map(() => \"?\").join(\",\");\n await sqlite.execute({\n sql: `DELETE FROM ${table} WHERE id IN (${placeholders})`,\n args: batch,\n });\n }\n\n totalArchived += rows.length;\n const chunkCount = Math.ceil(rows.length / CHUNK_SIZE);\n console.log(`${table}: archived ${rows.length} rows to ${chunkCount} blobs, then deleted`);\n }\n\n console.log(`Archive complete: ${totalArchived} rows archived and purged (cutoff: ${cutoff.slice(0, 10)})`);\n}\n","label",[133],"9b46cd2a-9904-4410-a2fd-186ec09d84e4","analytics_server_mikeyp.ts","87fa7d22-5ae0-11f1-b550-ee650bb23af1",176,["D",1786737808713],17,"import sqlite from \"https://esm.town/v/std/sqlite/main.ts\";\n\nconst TOKEN = Deno.env.get(\"TOKEN\");\n\nconst tables = [\"events\", \"crashes\", \"logs\"] as const;\nconst schemas: Record = {\n events: `(\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n device_id TEXT, session_id TEXT, platform TEXT, app TEXT, version TEXT,\n build TEXT, build_id TEXT, portal_name TEXT, page_url TEXT,\n data TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP\n )`,\n crashes: `(\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n device_id TEXT, session_id TEXT, platform TEXT, app TEXT, version TEXT,\n build TEXT, build_id TEXT, portal_name TEXT, page_url TEXT,\n data TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP\n )`,\n logs: `(\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n device_id TEXT, session_id TEXT, platform TEXT, app TEXT, version TEXT,\n build TEXT, build_id TEXT, portal_name TEXT, page_url TEXT,\n data TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP\n )`,\n};\n\nfor (const table of tables) {\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS ${table} ${schemas[table]}`);\n try { await sqlite.execute(`ALTER TABLE ${table} ADD COLUMN session_id TEXT`); } catch {}\n try { await sqlite.execute(`ALTER TABLE ${table} ADD COLUMN platform TEXT`); } catch {}\n try { await sqlite.execute(`ALTER TABLE ${table} ADD COLUMN build TEXT`); } catch {}\n try { await sqlite.execute(`ALTER TABLE ${table} ADD COLUMN build_id TEXT`); } catch {}\n try { await sqlite.execute(`ALTER TABLE ${table} ADD COLUMN portal_name TEXT`); } catch {}\n try { await sqlite.execute(`ALTER TABLE ${table} ADD COLUMN page_url TEXT`); } catch {}\n try { await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_${table}_created ON ${table}(created_at)`); } catch {}\n try { await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_${table}_device ON ${table}(device_id)`); } catch {}\n try { await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_${table}_session ON ${table}(session_id)`); } catch {}\n}\n\nexport default async function (req: Request): Promise {\n if (req.method !== \"POST\") {\n return Response.json({ error: \"POST only\" }, { status: 405 });\n }\n\n if (!TOKEN) {\n return Response.json({ error: \"server misconfigured\" }, { status: 500 });\n }\n\n let body;\n try {\n body = await req.json();\n } catch {\n return Response.json({ error: \"invalid JSON\" }, { status: 400 });\n }\n\n if (!body.app || !body.type || !body.data) {\n return Response.json({ error: \"missing fields\" }, { status: 400 });\n }\n if (body.token !== TOKEN) {\n return Response.json({ error: \"unauthorized\" }, { status: 401 });\n }\n\n const validTypes = { events: \"events\", crash: \"crashes\", debug_log: \"logs\" };\n const table = validTypes[body.type as keyof typeof validTypes];\n if (!table) {\n return Response.json({ error: \"unknown type\" }, { status: 400 });\n }\n\n const rows = Array.isArray(body.data) ? body.data : [body.data];\n if (rows.length > 500) {\n return Response.json({ error: \"too many rows\" }, { status: 413 });\n }\n\n try {\n const statements = rows.map(row => ({\n sql: `INSERT INTO ${table} (device_id, session_id, platform, app, version, build, build_id, portal_name, page_url, data) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n args: [\n body.device_id || \"\",\n body.session_id || \"\",\n body.platform || \"\",\n body.app,\n body.version || \"\",\n body.build || null,\n body.build_id || null,\n body.portal_name || null,\n body.page_url || null,\n JSON.stringify(row),\n ],\n }));\n await sqlite.batch(statements);\n } catch (e) {\n console.error(\"Insert failed:\", e);\n return Response.json({ error: \"insert failed\" }, { status: 500 });\n }\n\n return Response.json({ ok: true });\n}\n","mikeyp-analytics",[145],"299c0995-93df-4295-bfb5-9084efe4095d","analytics_server.ts",277,["D",1789409942159],13,"import sqlite from \"https://esm.town/v/std/sqlite/main.ts\";\n\nconst TOKEN = Deno.env.get(\"TOKEN\");\nconst TOKENS = Deno.env.get(\"TOKENS\")\n ?.split(\",\")\n .map(t => t.trim())\n .filter(Boolean) ?? [];\nconst VALID_TOKENS = new Set(TOKEN ? [TOKEN, ...TOKENS] : TOKENS);\n\nconst tables = [\"events\", \"crashes\", \"logs\"] as const;\n\n// Beacon responses are read cross-origin from game pages/portals; without\n// ACAO the browser blocks the (already-delivered) response and logs a CORS\n// error per beacon. sendBeacon posts text/plain (no preflight), so a plain\n// allow-origin header silences the console noise.\nconst CORS_HEADERS = { \"Access-Control-Allow-Origin\": \"*\" };\n\nfunction json(payload: unknown, status = 200): Response {\n return Response.json(payload, { status, headers: CORS_HEADERS });\n}\n\nconst schemas: Record = {\n events: `(\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n device_id TEXT, session_id TEXT, platform TEXT, app TEXT, version TEXT,\n build TEXT, build_id TEXT, portal_name TEXT, page_url TEXT,\n data TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP\n )`,\n crashes: `(\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n device_id TEXT, session_id TEXT, platform TEXT, app TEXT, version TEXT,\n build TEXT, build_id TEXT, portal_name TEXT, page_url TEXT,\n data TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP\n )`,\n logs: `(\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n device_id TEXT, session_id TEXT, platform TEXT, app TEXT, version TEXT,\n build TEXT, build_id TEXT, portal_name TEXT, page_url TEXT,\n data TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP\n )`,\n};\n\n// Cold starts would otherwise re-run every migration statement (each a DB\n// round trip) before the first beacon is accepted; one probe skips the\n// whole block once the schema is fully in place.\nconst schemaProbe = await sqlite.batch([\n {\n sql: `SELECT (SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name IN ('events','crashes','logs','sessions')) AS tables_found, (SELECT COUNT(*) FROM pragma_table_info('events')) AS event_columns, (SELECT COUNT(*) FROM sqlite_master WHERE type='index' AND name IN ('idx_events_created','idx_events_device','idx_events_session','idx_events_event')) AS indexes_found`,\n args: [],\n },\n]);\nconst probe = schemaProbe[0].rows[0] as\n | { tables_found?: number | string; event_columns?: number | string; indexes_found?: number | string }\n | undefined;\nif (\n !probe ||\n Number(probe.tables_found) !== 4 ||\n Number(probe.event_columns) !== 12 ||\n Number(probe.indexes_found) !== 4\n) {\n for (const table of tables) {\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS ${table} ${schemas[table]}`);\n try { await sqlite.execute(`ALTER TABLE ${table} ADD COLUMN session_id TEXT`); } catch {}\n try { await sqlite.execute(`ALTER TABLE ${table} ADD COLUMN platform TEXT`); } catch {}\n try { await sqlite.execute(`ALTER TABLE ${table} ADD COLUMN build TEXT`); } catch {}\n try { await sqlite.execute(`ALTER TABLE ${table} ADD COLUMN build_id TEXT`); } catch {}\n try { await sqlite.execute(`ALTER TABLE ${table} ADD COLUMN portal_name TEXT`); } catch {}\n try { await sqlite.execute(`ALTER TABLE ${table} ADD COLUMN page_url TEXT`); } catch {}\n try { await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_${table}_created ON ${table}(created_at)`); } catch {}\n try { await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_${table}_device ON ${table}(device_id)`); } catch {}\n try { await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_${table}_session ON ${table}(session_id)`); } catch {}\n }\n try { await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_events_event ON events(json_extract(data, '$.event'))`); } catch {}\n\n // Derived per-session attributes so the dashboard can session-scope queries\n // against a ~2k-row table instead of re-deriving sessions from full scans of\n // `events` on every query. `surface` is the session's first non-null\n // application_type (beacons carry it on heartbeat/sdk_lifecycle).\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS sessions (\n session_id TEXT PRIMARY KEY,\n device_id TEXT, platform TEXT, version TEXT, portal_name TEXT,\n surface TEXT, first_at DATETIME, last_at DATETIME\n )`);\n}\n\nexport default async function (req: Request): Promise {\n if (req.method !== \"POST\") {\n return json({ error: \"POST only\" }, 405);\n }\n\n if (VALID_TOKENS.size === 0) {\n return json({ error: \"server misconfigured\" }, 500);\n }\n\n let body;\n try {\n body = await req.json();\n } catch {\n return json({ error: \"invalid JSON\" }, 400);\n }\n\n if (!body.app || !body.type || !body.data) {\n return json({ error: \"missing fields\" }, 400);\n }\n if (!VALID_TOKENS.has(body.token)) {\n return json({ error: \"unauthorized\" }, 401);\n }\n\n const validTypes = { events: \"events\", crash: \"crashes\", debug_log: \"logs\" };\n const table = validTypes[body.type as keyof typeof validTypes];\n if (!table) {\n return json({ error: \"unknown type\" }, 400);\n }\n\n const rows = Array.isArray(body.data) ? body.data : [body.data];\n if (rows.length > 500) {\n return json({ error: \"too many rows\" }, 413);\n }\n\n try {\n const statements = rows.map((row: unknown) => ({\n sql: `INSERT INTO ${table} (device_id, session_id, platform, app, version, build, build_id, portal_name, page_url, data) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n args: [\n body.device_id || \"\",\n body.session_id || \"\",\n body.platform || \"\",\n body.app,\n body.version || \"\",\n body.build || null,\n body.build_id || null,\n body.portal_name || null,\n body.page_url || null,\n JSON.stringify(row),\n ],\n }));\n if (body.session_id) {\n for (const row of rows as { data?: { application_type?: string } }[]) {\n statements.push({\n sql: `INSERT INTO sessions (session_id, device_id, platform, version, portal_name, surface, first_at, last_at)\n VALUES (?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)\n ON CONFLICT(session_id) DO UPDATE SET\n last_at = excluded.last_at,\n surface = COALESCE(sessions.surface, excluded.surface),\n device_id = COALESCE(NULLIF(sessions.device_id, ''), excluded.device_id),\n platform = COALESCE(NULLIF(sessions.platform, ''), excluded.platform),\n version = COALESCE(sessions.version, excluded.version),\n portal_name = COALESCE(sessions.portal_name, excluded.portal_name)`,\n args: [\n body.session_id,\n body.device_id || \"\",\n body.platform || \"\",\n body.version || \"\",\n body.portal_name || null,\n row?.data?.application_type ?? null,\n ],\n });\n }\n }\n await sqlite.batch(statements);\n } catch (e) {\n console.error(\"Insert failed:\", e);\n return json({ error: \"insert failed\" }, 500);\n }\n\n return json({ ok: true });\n}\n","rp-analytics",[154],"3cb9479e-ce06-4622-b15a-f83eff0fc202",["D",1789435398560],"mikeyp-analyticsdashboard",[28],"pulls","branches",1,"forks","envVars",{"_173":175,"_176":167},"connections",{"_173":63,"_174":63},"count","unfilledCount",4,"emptyCount",{"_43":161,"_32":28,"_30":31,"_135":-5,"_41":42,"_40":14,"_137":25,"_38":191,"_13":155,"_33":34,"_35":37,"_24":25,"_142":163,"_182":192,"_27":193},{"_43":153,"_32":154,"_30":57,"_135":-5,"_41":42,"_40":155,"_137":59,"_38":188,"_13":157,"_33":34,"_35":158,"_24":25,"_142":159,"_182":189,"_27":190},{"_43":144,"_32":145,"_30":146,"_135":-5,"_41":42,"_40":147,"_137":59,"_38":185,"_13":149,"_33":34,"_35":150,"_24":25,"_142":151,"_182":186,"_27":187},{"_43":132,"_32":133,"_30":134,"_135":-5,"_41":42,"_40":136,"_137":59,"_38":181,"_13":139,"_33":140,"_35":141,"_24":25,"_142":-5,"_182":183,"_27":184},["D",1782859657365],"children",[],[133],["D",1786737808713],[],[145],["D",1789409942159],[],[154],["D",1789435398560],[],[28],["D",1780005582550],["D",1789509043237],{"_100":99,"_106":53},{"_100":99,"_101":53,"_102":53,"_18":55,"_103":104,"_105":25},{"_13":89,"_32":90,"_43":42,"_91":44,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":-5,"_97":45,"_98":99},"ctx",{"_82":-5,"_125":232,"_233":234,"_235":236,"_237":25,"_238":25,"_239":25,"_240":59,"_241":-5,"_242":-7,"_243":-7,"_244":63,"_245":25,"_246":25,"_247":-5,"_248":25,"_249":25,"_250":25,"_251":-5},"ENV",{"_203":204,"_205":206,"_207":90,"_208":209,"_210":211,"_212":213,"_214":215,"_216":217,"_218":219,"_220":221,"_222":223,"_224":225,"_226":227,"_228":229,"_230":231},"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",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]

Crownspell Analytics

Platforms & Portals

D1 Over Time

Retention Cohort

Engagement

Session Funnel

Day-0 Progression Tiers (devices)

Web Loading

Crashes

Card Battle Outcomes

Battle-1 Abandonment Depth

Battle-1 Duration

Card Battle Analytics

Run Map — Node Selections by Row

Run Map — Depth Reached

Spell Usage

Spells vs Battles

Edict Usage

Armory Uptake

Crown Seal Sources

Resolve Economy

Ability Usage

Level Progression

Raw Log Reports

Live Pulse Tail (10s)

Version Breakdown

Events Over Time

Top Event Types

Recent Events

Crownspell Analytics

Platforms & Portals

D1 Over Time

Retention Cohort

Engagement

Session Funnel

Day-0 Progression Tiers (devices)

Web Loading

Crashes

Card Battle Outcomes

Battle-1 Abandonment Depth

Battle-1 Duration

Card Battle Analytics

Run Map — Node Selections by Row

Run Map — Depth Reached

Spell Usage

Spells vs Battles

Edict Usage

Armory Uptake

Crown Seal Sources

Resolve Economy

Ability Usage

Level Progression

Raw Log Reports

Live Pulse Tail (10s)

Version Breakdown

Events Over Time

Top Event Types

Recent Events

Live Pulse

Live Pulse

Diff · v316