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

Battle 1 Abandonment Depth

\n\n

Battle 1 Duration

\n\n

Card Battle Outcomes

\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","mikeyp-analyticsdashboard",[155],"pulls",1,"forks","envVars",{"_169":171,"_172":163},"connections",{"_169":54,"_170":54},"count","unfilledCount",4,"emptyCount",{"_20":154,"_13":155,"_120":156,"_122":-5,"_123":21,"_124":30,"_126":50,"_22":187,"_29":158,"_129":142,"_131":159,"_133":38,"_134":160,"_178":188,"_92":189},{"_20":146,"_13":147,"_120":48,"_122":-5,"_123":21,"_124":148,"_126":50,"_22":184,"_29":150,"_129":142,"_131":151,"_133":38,"_134":152,"_178":185,"_92":186},{"_20":136,"_13":137,"_120":138,"_122":-5,"_123":21,"_124":139,"_126":50,"_22":181,"_29":141,"_129":142,"_131":143,"_133":38,"_134":144,"_178":182,"_92":183},{"_20":118,"_13":119,"_120":121,"_122":-5,"_123":21,"_124":125,"_126":50,"_22":177,"_29":128,"_129":130,"_131":132,"_133":38,"_134":-5,"_178":179,"_92":180},["D",1782859657365],"children",[],[119],["D",1786737808713],[],[137],["D",1789409942159],[],[147],["D",1789442487926],[],[155],["D",1780005582550],["D",1789509043237],{"_11":12,"_90":43},{"_11":12,"_85":43,"_86":43,"_45":46,"_87":88,"_89":38},{"_29":30,"_13":26,"_20":21,"_27":28,"_31":-5,"_32":-5,"_80":-5,"_81":-5,"_82":-5,"_83":14,"_84":12},"ctx",{"_73":-5,"_111":228,"_229":230,"_231":232,"_233":38,"_234":38,"_235":38,"_236":50,"_237":-5,"_238":-7,"_239":-7,"_240":54,"_241":38,"_242":38,"_243":-5,"_244":38,"_245":38,"_246":38,"_247":-5},"ENV",{"_199":200,"_201":202,"_203":26,"_204":205,"_206":207,"_208":209,"_210":211,"_212":213,"_214":215,"_216":217,"_218":219,"_220":221,"_222":223,"_224":225,"_226":227},"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

Battle 1 Abandonment Depth

Battle 1 Duration

Card Battle Outcomes

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

Branches1

Branches
1