Royal Palace Analytics

[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_174":175,"_176":177},"routes/_app.x.$handle.$name",{"_101":100,"_33":46,"_108":91,"_27":54,"_13":14,"_109":-5,"_11":110,"_111":112,"_113":114,"_115":116,"_117":118,"_20":-5,"_119":-5,"_120":-5,"_121":25,"_122":123,"_124":60,"_125":-5,"_126":-5,"_127":128},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_44":45,"_33":46,"_39":47,"_48":-5,"_49":25,"_50":51,"_52":51,"_53":54,"_55":-5,"_18":56,"_57":58,"_59":60,"_61":-5,"_62":-5,"_63":64,"_65":66,"_67":68,"_69":70,"_71":72,"_73":-5,"_74":75,"_76":75,"_77":78,"_79":78,"_80":78,"_81":75,"_82":75,"_83":84,"_85":86,"_87":88,"_89":25},"version",50,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":23,"_24":25,"_26":25,"_27":28,"_29":30},"file",{"_31":32,"_33":28,"_34":35,"_36":38,"_39":40,"_41":14,"_42":43,"_24":25},"previous",{"_31":32,"_33":28,"_34":35,"_36":37},"deleted",false,"deletedHere","path","analytics_server.ts","latestPath","analytics_server_mikeyp.ts","fileId","87fa7d22-5ae0-11f1-b550-ee650bb23af1","name","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}\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 for (const row of rows) {\n await sqlite.execute(\n `INSERT INTO ${table} (device_id, session_id, platform, app, version, data) VALUES (?, ?, ?, ?, ?, ?)`,\n [\n body.device_id || \"\",\n body.session_id || \"\",\n body.platform || \"\",\n body.app,\n body.version || \"\",\n JSON.stringify(row),\n ],\n );\n }\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","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}\n\nexport default async function (req: Request): Promise {\n const url = new URL(req.url);\n\n // GET /pixel — image pixel fallback for web builds behind restrictive CSP\n if (req.method === \"GET\" && url.pathname === \"/pixel\") {\n const encoded = url.searchParams.get(\"d\");\n if (!encoded) return new Response(\"missing d\", { status: 400 });\n\n try {\n const payload = JSON.parse(atob(encoded));\n if (payload.token !== TOKEN) {\n return new Response(\"unauthorized\", { status: 401 });\n }\n\n const table = payload.type === \"crash\" ? \"crashes\"\n : payload.type === \"debug_log\" ? \"logs\" : \"events\";\n\n await sqlite.execute(\n `INSERT INTO ${table} (device_id, session_id, platform, app, version, data) VALUES (?, ?, ?, ?, ?, ?)`,\n [payload.device_id || \"\", payload.session_id || \"\", payload.platform || \"\", payload.app || \"\", payload.version || \"\", JSON.stringify(payload)],\n );\n } catch (e) {\n console.error(\"Pixel insert failed:\", e);\n }\n\n // 1x1 transparent GIF\n const gif = new Uint8Array([0x47, 0x49, 0x46, 0x38, 0x39, 0x61, 0x01, 0x00, 0x01, 0x00, 0x00, 0x00, 0x00, 0x3B]);\n return new Response(gif, { headers: { \"Content-Type\": \"image/gif\", \"Cache-Control\": \"no-store\" } });\n }\n\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 for (const row of rows) {\n await sqlite.execute(\n `INSERT INTO ${table} (device_id, session_id, platform, app, version, data) VALUES (?, ?, ?, ?, ?, ?)`,\n [\n body.device_id || \"\",\n body.session_id || \"\",\n body.platform || \"\",\n body.app,\n body.version || \"\",\n JSON.stringify(row),\n ],\n );\n }\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",1780546970616],"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",{"_101":100,"_107":54},"org",{"_101":100,"_102":54,"_103":54,"_18":56,"_104":105,"_106":25},"branch",{"_13":90,"_33":91,"_44":43,"_92":45,"_93":-5,"_94":-5,"_95":-5,"_96":-5,"_97":-5,"_98":46,"_99":100},"isOwned",338,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","mikeyp","handle","displayName","avatar","tier","free","canRestrictVals","profileImageUrl","branchName","adoptOrgId",{"_44":45,"_33":46,"_39":169,"_48":-5,"_49":25,"_50":51,"_52":51,"_53":54,"_55":-5,"_18":56,"_57":58,"_59":60,"_61":-5,"_62":-5,"_63":64,"_65":66,"_67":68,"_69":70,"_71":170,"_73":-5,"_74":75,"_76":75,"_77":78,"_79":78,"_80":78,"_81":75,"_82":75,"_83":171,"_85":172,"_87":173,"_89":25},"tree",[160,161],"files",[160,161],"counts",{"_148":64,"_149":150,"_151":64,"_152":153,"_154":155},"contents",[129,130],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_44":139,"_33":140,"_31":141,"_132":-5,"_42":43,"_41":142,"_133":25,"_39":143,"_13":144,"_34":35,"_36":145,"_24":25,"_136":146,"_27":147},{"_44":131,"_33":28,"_31":32,"_132":-5,"_42":43,"_41":14,"_133":25,"_39":134,"_13":135,"_34":35,"_36":38,"_24":25,"_136":137,"_27":138},"02e2cf49-d656-472a-966b-b653b5e1c023","parentId","latest",["D",1780546970616],10,"label","mikeyp-analytics",[28],"9df74018-feb3-45e9-8e81-37a88a388301","analytics_dashboard.ts","5a9b2d3c-5d37-11f1-9376-1607ee4eb77e",49,["D",1780538433188],35,"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

Battle Outcomes

\n\n

Session Duration

\n\n

Edict Usage

\n\n

Top Event Types

\n\n

Level Progression

\n\n

Engagement Metrics

\n\n

Retention Cohort

\n\n

Recent Events

Crashes

\n\n\n\n`;\n\nexport default async function (req: Request): Promise {\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, exclude_devices, versions } = body;\n\n const trimmedUpper = typeof sql === \"string\"\n ? sql.trim().toUpperCase()\n : \"\";\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 (exclude_devices !== undefined) {\n if (\n !Array.isArray(exclude_devices) ||\n !exclude_devices.every((d: unknown) => typeof d === \"string\")\n ) {\n return Response.json(\n {\n error: \"exclude_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 mergedExclude = [\n ...new Set([\n ...(DEV_DEVICE_IDS || []),\n ...(Array.isArray(exclude_devices) ? exclude_devices : []),\n ]),\n ];\n\n const filterClauses: string[] = [];\n const filterArgs: (string | number)[] = [];\n\n if (mergedExclude.length > 0) {\n const placeholders = mergedExclude.map(() => \"?\").join(\",\");\n filterClauses.push(`device_id NOT IN (${placeholders})`);\n filterArgs.push(...mergedExclude);\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 if (filterClauses.length > 0) {\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]) =>\n `${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}","mikeyp-analyticsdashboard",[140],"pulls","branches",1,"forks","envVars",{"_156":158,"_159":150},"connections",{"_156":64,"_157":64},"count","unfilledCount",4,"emptyCount",{"_44":139,"_33":140,"_31":141,"_132":-5,"_42":43,"_41":142,"_133":25,"_39":166,"_13":144,"_34":35,"_36":145,"_24":25,"_136":146,"_163":167,"_27":168},{"_44":131,"_33":28,"_31":32,"_132":-5,"_42":43,"_41":14,"_133":25,"_39":162,"_13":135,"_34":35,"_36":38,"_24":25,"_136":137,"_163":164,"_27":165},["D",1780546970616],"children",[],[28],["D",1780538433188],[],[140],["D",1780005582550],["D",1789509043237],{"_101":100,"_107":54},{"_101":100,"_102":54,"_103":54,"_18":56,"_104":105,"_106":25},{"_13":90,"_33":91,"_44":43,"_92":45,"_93":-5,"_94":-5,"_95":-5,"_96":-5,"_97":-5,"_98":46,"_99":100},"ctx",{"_83":-5,"_126":207,"_208":209,"_210":211,"_212":25,"_213":25,"_214":25,"_215":60,"_216":-5,"_217":-7,"_218":-7,"_219":64,"_220":25,"_221":25,"_222":-5,"_223":25,"_224":25,"_225":25,"_226":-5},"ENV",{"_178":179,"_180":181,"_182":91,"_183":184,"_185":186,"_187":188,"_189":190,"_191":192,"_193":194,"_195":196,"_197":198,"_199":200,"_201":202,"_203":204,"_205":206},"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"]

Diff · v50