Royal Palace Analytics

[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_198":199,"_200":201},"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",164,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":23,"_24":25,"_26":25,"_27":28,"_29":28},"file",{"_30":31,"_32":28,"_34":35,"_36":37,"_38":39,"_40":14,"_41":42,"_24":25},"previous",{"_30":31,"_32":33,"_34":35,"_36":37},"deleted",false,"deletedHere","path","analytics_server_mikeyp.ts","latestPath","fileId","87fa7d22-5ae0-11f1-b550-ee650bb23af1","name","analytics_server.ts","fileType","http","content","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 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 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 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(`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, data) VALUES (?, ?, ?, ?, ?, ?)`,\n args: [\n body.device_id || \"\",\n body.session_id || \"\",\n body.platform || \"\",\n body.app,\n body.version || \"\",\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","createdAt",["D",1784654689476],"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",338,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","mikeyp","handle","displayName","avatar","tier","free","canRestrictVals","profileImageUrl","branchName","adoptOrgId",{"_43":44,"_32":45,"_38":193,"_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":194,"_72":-5,"_73":74,"_75":74,"_76":77,"_78":77,"_79":77,"_80":74,"_81":74,"_82":195,"_84":196,"_86":197,"_88":25},"tree",[176,177,178,179],"files",[176,177,178,179],"counts",{"_164":63,"_165":166,"_167":63,"_168":169,"_170":171},"contents",[128,129,130,131],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_43":155,"_32":156,"_30":157,"_135":-5,"_41":42,"_40":158,"_137":25,"_38":159,"_13":160,"_34":35,"_36":161,"_24":25,"_142":162,"_27":163},{"_43":149,"_32":150,"_30":57,"_135":-5,"_41":42,"_40":151,"_137":25,"_38":152,"_13":63,"_34":35,"_36":37,"_24":25,"_142":153,"_27":154},{"_43":144,"_32":28,"_30":31,"_135":-5,"_41":42,"_40":14,"_137":25,"_38":145,"_13":146,"_34":35,"_36":37,"_24":25,"_142":147,"_27":148},{"_43":132,"_32":133,"_30":134,"_135":-5,"_41":42,"_40":136,"_137":59,"_38":138,"_13":139,"_34":140,"_36":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],"ad37e92f-0c53-40ae-8f8e-f0ddbd8d45b7",["D",1784654689476],14,"mikeyp-analytics",[28],"8192168f-a669-49ed-bafd-f38fa6f8b25c","analytics_server-copy.ts",163,["D",1784654602029],"rp-analytics",[150],"bddb7406-707e-4f1d-9f74-5ffd5026b59a","analytics_dashboard.ts","5a9b2d3c-5d37-11f1-9376-1607ee4eb77e",160,["D",1782854379589],141,"import sqlite from \"https://esm.town/v/std/sqlite/main.ts\";\n\nconst DEV_DEVICE_IDS: string[] = [\n // Add developer device IDs here to always exclude from analytics\n \"b68cc496-fb13-48fd-b101-cfb63f57dbca\",\n \"9b0e2544-93e8-4a95-b637-58bd2a3258c8\",\n \"e1183240-1881-4466-bd8b-e5e32e65517e\",\n];\n\nconst tables = [\"events\", \"crashes\", \"logs\"] as const;\nfor (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, 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 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\nconst HTML = `\n\n\n\n\nRoyal Palace Analytics\n\n\n\n\n\n\n\n

\n\n

Platform Breakdown

Version Breakdown

\n\n

Events Over Time

\n\n

Player Funnel

Engagement Metrics

\n\n

Session Duration

\n\n

Retention Cohort

\n\n

Card Battle Outcomes

\n\n

Card Battle Analytics

\n\n

Spell Usage

Spells vs Battles

\n\n

Edict Usage

Top Event Types

\n\n

Level Progression

\n\n

Run Map — Node Selections by Row

Run Map — Depth Reached

\n\n

Crown Seal Sources

Armory Uptake

\n\n

Recent Events

\n\n

Crashes

\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\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 { sql, args, include_devices, versions } = body;\n\n const trimmedUpper =\n typeof sql === \"string\" ? sql.trim().toUpperCase() : \"\";\n // Allow read-only queries: plain SELECT or a WITH (CTE) that selects.\n if (\n typeof sql !== \"string\" ||\n !(trimmedUpper.startsWith(\"SELECT\") || trimmedUpper.startsWith(\"WITH\"))\n ) {\n return Response.json(\n { error: \"only SELECT queries allowed\" },\n {\n status: 400,\n }\n );\n }\n\n if (args !== undefined && !Array.isArray(args)) {\n return Response.json({ error: \"args must be an array\" }, { status: 400 });\n }\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 let finalSql = sql.trim();\n const finalArgs: (string | number)[] = Array.isArray(args) ? [...args] : [];\n\n const filterClauses: string[] = [\"session_id IS NOT NULL\"];\n const filterArgs: (string | number)[] = [];\n\n const devExclude = DEV_DEVICE_IDS?.length\n ? DEV_DEVICE_IDS\n : [];\n\n if (Array.isArray(include_devices) && include_devices.length > 0) {\n const placeholders = include_devices.map(() => \"?\").join(\",\");\n filterClauses.push(`device_id IN (${placeholders})`);\n filterArgs.push(...include_devices);\n }\n if (devExclude.length > 0) {\n const placeholders = devExclude.map(() => \"?\").join(\",\");\n filterClauses.push(`device_id NOT IN (${placeholders})`);\n filterArgs.push(...devExclude);\n }\n\n if (Array.isArray(versions) && versions.length > 0) {\n const placeholders = versions.map(() => \"?\").join(\",\");\n filterClauses.push(`version IN (${placeholders})`);\n filterArgs.push(...versions);\n }\n\n {\n const whereClause = \" WHERE \" + filterClauses.join(\" AND \");\n\n const tableAliases: [string, string][] = [];\n for (const table of [\"events\", \"crashes\", \"logs\"]) {\n if (new RegExp(`\\\\bFROM ${table}\\\\b`, \"i\").test(finalSql)) {\n tableAliases.push([table, \"_f\" + table[0]]);\n }\n }\n\n if (tableAliases.length > 0) {\n const ctes = tableAliases.map(\n ([table, alias]) => `${alias} AS (SELECT * FROM ${table}${whereClause})`\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 const cteStr = ctes.join(\", \");\n if (/^WITH\\b/i.test(finalSql)) {\n finalSql = \"WITH \" + cteStr + \", \" + finalSql.substring(4).trim();\n } else {\n finalSql = \"WITH \" + cteStr + \" \" + finalSql;\n }\n finalArgs.unshift(...filterArgs);\n }\n }\n\n try {\n const MAX_ROWS = 10000;\n const results = await sqlite.batch([{ sql: finalSql, args: finalArgs }]);\n const result = results[0];\n const rows = result.rows.slice(0, MAX_ROWS);\n const truncated = result.rows.length > MAX_ROWS;\n return Response.json({ rows, truncated });\n } catch (e) {\n console.error(\"Query failed:\", e, \"SQL:\", finalSql);\n return Response.json(\n { error: \"query execution failed\", sql: finalSql },\n { status: 500 }\n );\n }\n }\n\n return Response.json({ error: \"not found\" }, { status: 404 });\n}\n","mikeyp-analyticsdashboard",[156],"pulls","branches",1,"forks","envVars",{"_172":174,"_175":166},"connections",{"_172":63,"_173":63},"count","unfilledCount",4,"emptyCount",{"_43":155,"_32":156,"_30":157,"_135":-5,"_41":42,"_40":158,"_137":25,"_38":190,"_13":160,"_34":35,"_36":161,"_24":25,"_142":162,"_181":191,"_27":192},{"_43":149,"_32":150,"_30":57,"_135":-5,"_41":42,"_40":151,"_137":25,"_38":187,"_13":63,"_34":35,"_36":37,"_24":25,"_142":153,"_181":188,"_27":189},{"_43":144,"_32":28,"_30":31,"_135":-5,"_41":42,"_40":14,"_137":25,"_38":184,"_13":146,"_34":35,"_36":37,"_24":25,"_142":147,"_181":185,"_27":186},{"_43":132,"_32":133,"_30":134,"_135":-5,"_41":42,"_40":136,"_137":59,"_38":180,"_13":139,"_34":140,"_36":141,"_24":25,"_142":-5,"_181":182,"_27":183},["D",1782859657365],"children",[],[133],["D",1784654689476],[],[28],["D",1784654602029],[],[150],["D",1782854379589],[],[156],["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":231,"_232":233,"_234":235,"_236":25,"_237":25,"_238":25,"_239":59,"_240":-5,"_241":-7,"_242":-7,"_243":63,"_244":25,"_245":25,"_246":-5,"_247":25,"_248":25,"_249":25,"_250":-5},"ENV",{"_202":203,"_204":205,"_206":90,"_207":208,"_209":210,"_211":212,"_213":214,"_215":216,"_217":218,"_219":220,"_221":222,"_223":224,"_225":226,"_227":228,"_229":230},"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"]

Royal Palace Analytics

Platform Breakdown

Version Breakdown

Events Over Time

Player Funnel SessionsDevices

Engagement Metrics

Session Duration

Retention Cohort

Card Battle Outcomes

Card Battle Analytics

Spell Usage

Spells vs Battles

Edict Usage

Top Event Types

Level Progression

Run Map — Node Selections by Row

Run Map — Depth Reached

Crown Seal Sources

Armory Uptake

Recent Events

Crashes

Player Funnel

Diff · v164