[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_191":192,"_193":194},"routes/_app.x.$handle.$name",{"_108":107,"_33":54,"_117":98,"_28":118,"_13":14,"_119":-5,"_11":120,"_121":122,"_123":124,"_125":126,"_127":128,"_21":-5,"_129":-5,"_130":-5,"_131":26,"_132":133,"_134":67,"_135":-5,"_136":-5,"_137":138},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_52":53,"_33":54,"_39":55,"_56":-5,"_57":26,"_58":59,"_60":59,"_61":62,"_63":-5,"_18":64,"_65":47,"_66":67,"_68":-5,"_69":-5,"_70":71,"_72":73,"_74":75,"_76":77,"_78":79,"_80":-5,"_81":82,"_83":82,"_84":85,"_86":85,"_87":85,"_88":82,"_89":82,"_90":91,"_92":93,"_94":95,"_96":26},"version",90,"changes",[19,20],"aiChatInfo","userId",{"_21":44,"_23":45,"_25":26,"_27":26,"_28":46,"_30":46},{"_21":22,"_23":24,"_25":26,"_27":26,"_28":29,"_30":29},"file",{"_31":32,"_33":29,"_34":35,"_36":38,"_39":40,"_41":14,"_42":43,"_25":26},"previous",{"_31":32,"_33":29,"_34":35,"_36":37},"deleted",false,"deletedHere","path","db.ts","latestPath","fileId","2c6db772-ae27-11f1-a5ee-1607ee4eb77e","name","fileType","script","content","import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { classifyTrafficSource, parseUA } from \"./util.ts\";\n\nexport interface Pageview {\n id: number;\n site: string;\n path: string;\n referrer: string;\n ua: string;\n country: string | null;\n country_code: string | null;\n region: string | null;\n city: string | null;\n lat: number | null;\n lon: number | null;\n utm_source: string | null;\n utm_medium: string | null;\n utm_campaign: string | null;\n utm_term: string | null;\n utm_content: string | null;\n device_type: string | null;\n browser: string | null;\n os: string | null;\n viewport: string | null;\n visitor_id: string | null;\n traffic_source: string | null;\n event: string;\n event_label: string | null;\n created_at: string;\n}\n\nexport interface Geo {\n country: string | null;\n countryCode: string | null;\n region: string | null;\n city: string | null;\n lat: number | null;\n lon: number | null;\n}\n\nexport interface EventFields {\n site: string;\n path: string;\n referrer: string;\n ua: string;\n geo: Geo | null;\n utmSource?: string | null;\n utmMedium?: string | null;\n utmCampaign?: string | null;\n utmTerm?: string | null;\n utmContent?: string | null;\n deviceType?: string | null;\n browser?: string | null;\n os?: string | null;\n viewport?: string | null;\n visitorId?: string | null;\n trafficSource?: string | null;\n event?: string;\n eventLabel?: string | null;\n}\n\nawait sqlite.execute(`CREATE TABLE IF NOT EXISTS pageviews (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n site TEXT NOT NULL,\n path TEXT NOT NULL,\n referrer TEXT NOT NULL DEFAULT '',\n ua TEXT NOT NULL DEFAULT '',\n created_at TEXT NOT NULL DEFAULT (datetime('now'))\n)`);\n\nawait sqlite.execute(\n `CREATE INDEX IF NOT EXISTS idx_pageviews_site_time ON pageviews (site, created_at)`,\n);\n\n// Added after the table already existed in production — ALTER TABLE has no\n// \"IF NOT EXISTS\" for columns in SQLite, so add each one and swallow the\n// \"duplicate column\" error on every run after the first.\nasync function ensureColumn(name: string, type: string) {\n try {\n await sqlite.execute(`ALTER TABLE pageviews ADD COLUMN ${name} ${type}`);\n } catch (err) {\n if (!String(err).includes(\"duplicate column\")) throw err;\n }\n}\nawait ensureColumn(\"country\", \"TEXT\");\nawait ensureColumn(\"country_code\", \"TEXT\");\nawait ensureColumn(\"region\", \"TEXT\");\nawait ensureColumn(\"city\", \"TEXT\");\nawait ensureColumn(\"lat\", \"REAL\");\nawait ensureColumn(\"lon\", \"REAL\");\nawait ensureColumn(\"utm_source\", \"TEXT\");\nawait ensureColumn(\"utm_medium\", \"TEXT\");\nawait ensureColumn(\"utm_campaign\", \"TEXT\");\nawait ensureColumn(\"utm_term\", \"TEXT\");\nawait ensureColumn(\"utm_content\", \"TEXT\");\nawait ensureColumn(\"device_type\", \"TEXT\");\nawait ensureColumn(\"browser\", \"TEXT\");\nawait ensureColumn(\"os\", \"TEXT\");\nawait ensureColumn(\"viewport\", \"TEXT\");\nawait ensureColumn(\"visitor_id\", \"TEXT\");\nawait ensureColumn(\"traffic_source\", \"TEXT\");\nawait ensureColumn(\"event\", \"TEXT\");\nawait ensureColumn(\"event_label\", \"TEXT\");\n\nawait sqlite.execute(\n `CREATE INDEX IF NOT EXISTS idx_pageviews_visitor ON pageviews (visitor_id)`,\n);\n\nexport async function recordEvent(f: EventFields) {\n await sqlite.execute({\n sql: `INSERT INTO pageviews (\n site, path, referrer, ua,\n country, country_code, region, city, lat, lon,\n utm_source, utm_medium, utm_campaign, utm_term, utm_content,\n device_type, browser, os, viewport, visitor_id, traffic_source, event, event_label\n ) VALUES (?,?,?,?, ?,?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?,?,?,?)`,\n args: [\n f.site,\n f.path,\n f.referrer,\n f.ua,\n f.geo?.country ?? null,\n f.geo?.countryCode ?? null,\n f.geo?.region ?? null,\n f.geo?.city ?? null,\n f.geo?.lat ?? null,\n f.geo?.lon ?? null,\n f.utmSource ?? null,\n f.utmMedium ?? null,\n f.utmCampaign ?? null,\n f.utmTerm ?? null,\n f.utmContent ?? null,\n f.deviceType ?? null,\n f.browser ?? null,\n f.os ?? null,\n f.viewport ?? null,\n f.visitorId ?? null,\n f.trafficSource ?? null,\n f.event ?? \"pageview\",\n f.eventLabel ?? null,\n ],\n });\n}\n\n// One-time (idempotent) backfill for rows recorded before this schema\n// existed. `ua` and `referrer` were captured from the very first version of\n// this tracker, so device/browser/OS and traffic-source CAN be recovered for\n// old rows by re-parsing them now. What can NOT be recovered: geolocation\n// (no IP was ever stored — deliberately, for privacy — so there's nothing to\n// re-look-up) and UTM/visitor-id (the old beacon never sent them; the data\n// simply doesn't exist). Only touches rows that haven't been backfilled yet.\n// `all=true` reclassifies every row instead of only ones never touched —\n// for when the parsing logic itself changed (e.g. the bot-detection fix)\n// and existing rows may have been classified wrong the first time, not just\n// left unclassified.\nexport async function backfillHistoricalRows(all = false): Promise {\n const rows = await sqlite.execute(\n all\n ? `SELECT id, ua, referrer, utm_source, utm_medium FROM pageviews LIMIT 2000`\n : `SELECT id, ua, referrer, utm_source, utm_medium FROM pageviews WHERE device_type IS NULL LIMIT 2000`,\n );\n const list = rows.rows as unknown as {\n id: number;\n ua: string;\n referrer: string;\n utm_source: string | null;\n utm_medium: string | null;\n }[];\n for (const r of list) {\n const { deviceType, browser, os } = parseUA(r.ua || \"\");\n // Re-derive from the row's own utm columns (not null,null) — a forced\n // `all` pass must not clobber a newer row's already-correct UTM-based\n // classification (Paid/Campaign/etc.) by falling back to referrer-only.\n const trafficSource = classifyTrafficSource(r.referrer || \"\", r.utm_medium, r.utm_source);\n await sqlite.execute({\n sql:\n `UPDATE pageviews SET device_type = ?, browser = ?, os = ?, traffic_source = ?, event = COALESCE(event, 'pageview') WHERE id = ?`,\n args: [deviceType, browser, os, trafficSource, r.id],\n });\n }\n return list.length;\n}\n\nexport async function siteTotals(sinceSql: string) {\n const result = await sqlite.execute({\n sql:\n `SELECT site, COUNT(*) as views FROM pageviews WHERE created_at >= datetime('now', ?) GROUP BY site ORDER BY views DESC`,\n args: [sinceSql],\n });\n return result.rows as unknown as { site: string; views: number }[];\n}\n\n// A GA4-style filter set: every Reports-tab query takes one of these instead\n// of a growing list of positional params, so adding a new filter dimension\n// (traffic source, device) doesn't mean touching every function's signature\n// again. `trafficSource` of \"Referral\" or \"Campaign\" matches as a prefix,\n// since the real values carry the host/source too (e.g. \"Referral (x.com)\")\n// and there's no fixed small set to offer exact-match options for.\nexport interface ReportFilters {\n sinceSql: string;\n // Optional upper bound (created_at < datetime('now', untilSql)) — unset\n // means \"through now\". Only needed to carve out a bounded window, e.g.\n // the *previous* period for a GA4-style comparison (sinceSql=-60 day,\n // untilSql=-30 day gives the 30 days immediately before the current one).\n untilSql?: string;\n site?: string;\n trafficSource?: string;\n deviceType?: string;\n}\n\nfunction buildWhere(f: ReportFilters): { where: string; args: unknown[] } {\n const clauses = [`created_at >= datetime('now', ?)`];\n const args: unknown[] = [f.sinceSql];\n if (f.untilSql) {\n clauses.push(`created_at < datetime('now', ?)`);\n args.push(f.untilSql);\n }\n if (f.site) {\n clauses.push(`site = ?`);\n args.push(f.site);\n }\n if (f.trafficSource) {\n if (f.trafficSource === \"Referral\" || f.trafficSource === \"Campaign\") {\n clauses.push(`traffic_source LIKE ?`);\n args.push(f.trafficSource + \"%\");\n } else {\n clauses.push(`traffic_source = ?`);\n args.push(f.trafficSource);\n }\n }\n if (f.deviceType) {\n clauses.push(`device_type = ?`);\n args.push(f.deviceType);\n }\n return { where: clauses.join(\" AND \"), args };\n}\n\nexport async function topPaths(f: ReportFilters, limit = 10) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql: `SELECT path, COUNT(*) as views FROM pageviews WHERE ${where} GROUP BY path ORDER BY views DESC LIMIT ?`,\n args: [...args, limit],\n });\n return result.rows as unknown as { path: string; views: number }[];\n}\n\nexport async function recentViews(limit = 50, site?: string) {\n const result = await sqlite.execute({\n sql: site\n ? `SELECT * FROM pageviews WHERE site = ? ORDER BY id DESC LIMIT ?`\n : `SELECT * FROM pageviews ORDER BY id DESC LIMIT ?`,\n args: site ? [site, limit] : [limit],\n });\n return result.rows as unknown as Pageview[];\n}\n\n// \"Live\" view — visits within the last N minutes, most recent first. This\n// page auto-refreshes every 15s (plain , no websocket), so this\n// query is what actually drives the near-real-time feel.\nexport async function activeVisits(minutes = 10, limit = 100, site?: string) {\n const result = await sqlite.execute({\n sql: site\n ? `SELECT * FROM pageviews WHERE site = ? AND created_at >= datetime('now', ?) ORDER BY id DESC LIMIT ?`\n : `SELECT * FROM pageviews WHERE created_at >= datetime('now', ?) ORDER BY id DESC LIMIT ?`,\n args: site ? [site, `-${minutes} minute`, limit] : [`-${minutes} minute`, limit],\n });\n return result.rows as unknown as Pageview[];\n}\n\nexport async function topLocations(f: ReportFilters, limit = 12) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql:\n `SELECT COALESCE(city, region, country, 'Unknown') as place, country_code, COUNT(*) as views\n FROM pageviews WHERE ${where} GROUP BY place, country_code ORDER BY views DESC LIMIT ?`,\n args: [...args, limit],\n });\n return result.rows as unknown as { place: string; country_code: string | null; views: number }[];\n}\n\nexport async function trafficSources(f: ReportFilters, limit = 10) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql:\n `SELECT COALESCE(traffic_source, 'Unknown') as source, COUNT(*) as views FROM pageviews WHERE ${where} GROUP BY source ORDER BY views DESC LIMIT ?`,\n args: [...args, limit],\n });\n return result.rows as unknown as { source: string; views: number }[];\n}\n\nexport async function deviceBreakdown(f: ReportFilters, limit = 20) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql:\n `SELECT COALESCE(device_type,'unknown') as device_type, COALESCE(browser,'unknown') as browser, COALESCE(os,'unknown') as os, COUNT(*) as views\n FROM pageviews WHERE ${where} GROUP BY device_type, browser, os ORDER BY views DESC LIMIT ?`,\n args: [...args, limit],\n });\n return result.rows as unknown as { device_type: string; browser: string; os: string; views: number }[];\n}\n\nexport async function topCampaigns(f: ReportFilters, limit = 10) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql:\n `SELECT utm_source, utm_medium, utm_campaign, COUNT(*) as views FROM pageviews\n WHERE ${where} AND utm_campaign IS NOT NULL\n GROUP BY utm_source, utm_medium, utm_campaign ORDER BY views DESC LIMIT ?`,\n args: [...args, limit],\n });\n return result.rows as unknown as {\n utm_source: string | null;\n utm_medium: string | null;\n utm_campaign: string | null;\n views: number;\n }[];\n}\n\nexport async function eventBreakdown(f: ReportFilters, limit = 15) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql:\n `SELECT event, COALESCE(event_label, '') as event_label, COUNT(*) as n FROM pageviews\n WHERE ${where} AND event IS NOT NULL AND event != 'pageview'\n GROUP BY event, event_label ORDER BY n DESC LIMIT ?`,\n args: [...args, limit],\n });\n return result.rows as unknown as { event: string; event_label: string; n: number }[];\n}\n\n// Of visitors active in the window, how many are new (their very first-ever\n// hit falls inside the window) vs returning (they'd already been seen\n// before it started). Only counts rows carrying a visitor_id, so it's\n// meaningless for data recorded before that field existed. The filters\n// (site/source/device) scope which visits count as \"active\"; whether that\n// visitor is new-or-returning is still judged against their entire history.\nexport async function newVsReturning(f: ReportFilters) {\n const { where, args } = buildWhere(f);\n const sql = `\n WITH active AS (\n SELECT DISTINCT visitor_id FROM pageviews\n WHERE visitor_id IS NOT NULL AND ${where}\n ),\n first_seen AS (\n SELECT visitor_id, MIN(created_at) as fs FROM pageviews\n WHERE visitor_id IN (SELECT visitor_id FROM active)\n GROUP BY visitor_id\n )\n SELECT\n SUM(CASE WHEN fs >= datetime('now', ?) THEN 1 ELSE 0 END) as new_visitors,\n SUM(CASE WHEN fs < datetime('now', ?) THEN 1 ELSE 0 END) as returning_visitors\n FROM first_seen`;\n const result = await sqlite.execute({ sql, args: [...args, f.sinceSql, f.sinceSql] });\n const row = result.rows[0] as unknown as\n | { new_visitors: number | null; returning_visitors: number | null }\n | undefined;\n return { newVisitors: row?.new_visitors ?? 0, returningVisitors: row?.returning_visitors ?? 0 };\n}\n\n// Raw timestamps for the Reports tab's trend chart, one row per pageview in\n// the window. Bucketing into calendar days happens in main.tsx using\n// Eastern-time day keys — SQLite's date() only knows UTC, and the\n// business's own timezone is what \"by day\" should mean.\nexport async function dailyPageviewTimes(f: ReportFilters) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql: `SELECT created_at FROM pageviews WHERE ${where}`,\n args,\n });\n return (result.rows as unknown as { created_at: string }[]).map((r) => r.created_at);\n}\n\nexport async function dailySeriesRaw(f: ReportFilters) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql: `SELECT site, created_at FROM pageviews WHERE ${where}`,\n args,\n });\n return result.rows as unknown as { site: string; created_at: string }[];\n}\n\n// Raw rows for custom (non-pageview) events, for the Events report's trend\n// chart and per-event-name breakdown — same shape idea as\n// dailyPageviewTimes/dailySeriesRaw, just scoped to real events.\nexport async function eventTimes(f: ReportFilters) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql:\n `SELECT created_at, event, COALESCE(event_label, '') as event_label, site FROM pageviews\n WHERE ${where} AND event IS NOT NULL AND event != 'pageview'`,\n args,\n });\n return result.rows as unknown as {\n created_at: string;\n event: string;\n event_label: string;\n site: string;\n }[];\n}\n","import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { classifyTrafficSource, parseUA } from \"./util.ts\";\n\nexport interface Pageview {\n id: number;\n site: string;\n path: string;\n referrer: string;\n ua: string;\n country: string | null;\n country_code: string | null;\n region: string | null;\n utm_source: string | null;\n utm_medium: string | null;\n utm_campaign: string | null;\n utm_term: string | null;\n utm_content: string | null;\n device_type: string | null;\n browser: string | null;\n os: string | null;\n viewport: string | null;\n visitor_id: string | null;\n traffic_source: string | null;\n event: string;\n event_label: string | null;\n created_at: string;\n}\n\nexport interface Geo {\n country: string | null;\n countryCode: string | null;\n region: string | null;\n}\n\nexport interface EventFields {\n site: string;\n path: string;\n referrer: string;\n ua: string;\n geo: Geo | null;\n utmSource?: string | null;\n utmMedium?: string | null;\n utmCampaign?: string | null;\n utmTerm?: string | null;\n utmContent?: string | null;\n deviceType?: string | null;\n browser?: string | null;\n os?: string | null;\n viewport?: string | null;\n visitorId?: string | null;\n trafficSource?: string | null;\n event?: string;\n eventLabel?: string | null;\n}\n\nawait sqlite.execute(`CREATE TABLE IF NOT EXISTS pageviews (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n site TEXT NOT NULL,\n path TEXT NOT NULL,\n referrer TEXT NOT NULL DEFAULT '',\n ua TEXT NOT NULL DEFAULT '',\n created_at TEXT NOT NULL DEFAULT (datetime('now'))\n)`);\n\nawait sqlite.execute(\n `CREATE INDEX IF NOT EXISTS idx_pageviews_site_time ON pageviews (site, created_at)`,\n);\n\n// Added after the table already existed in production — ALTER TABLE has no\n// \"IF NOT EXISTS\" for columns in SQLite, so add each one and swallow the\n// \"duplicate column\" error on every run after the first.\nasync function ensureColumn(name: string, type: string) {\n try {\n await sqlite.execute(`ALTER TABLE pageviews ADD COLUMN ${name} ${type}`);\n } catch (err) {\n if (!String(err).includes(\"duplicate column\")) throw err;\n }\n}\nawait ensureColumn(\"country\", \"TEXT\");\nawait ensureColumn(\"country_code\", \"TEXT\");\nawait ensureColumn(\"region\", \"TEXT\");\nawait ensureColumn(\"utm_source\", \"TEXT\");\nawait ensureColumn(\"utm_medium\", \"TEXT\");\nawait ensureColumn(\"utm_campaign\", \"TEXT\");\nawait ensureColumn(\"utm_term\", \"TEXT\");\nawait ensureColumn(\"utm_content\", \"TEXT\");\nawait ensureColumn(\"device_type\", \"TEXT\");\nawait ensureColumn(\"browser\", \"TEXT\");\nawait ensureColumn(\"os\", \"TEXT\");\nawait ensureColumn(\"viewport\", \"TEXT\");\nawait ensureColumn(\"visitor_id\", \"TEXT\");\nawait ensureColumn(\"traffic_source\", \"TEXT\");\nawait ensureColumn(\"event\", \"TEXT\");\nawait ensureColumn(\"event_label\", \"TEXT\");\n\nawait sqlite.execute(\n `CREATE INDEX IF NOT EXISTS idx_pageviews_visitor ON pageviews (visitor_id)`,\n);\n\n// IP-prefix cache for the region lookup (see lookupGeo in main.tsx). Keyed\n// on a /24 (v4) or /64 (v6) prefix rather than the full IP, so a shared\n// network's visitors — the common case — cost one external call, not one\n// per visitor. `fetched_at` backs the TTL check; there is no cron sweep of\n// stale rows because the table stays small (one row per prefix ever seen,\n// not per hit) and a stale-but-unexpired row is simply re-fetched on its\n// next miss, not deleted.\nawait sqlite.execute(`CREATE TABLE IF NOT EXISTS geo_cache (\n ip_prefix TEXT PRIMARY KEY,\n region TEXT,\n fetched_at TEXT NOT NULL DEFAULT (datetime('now'))\n)`);\n\n// Region cache reads/writes for lookupGeo (main.tsx). Kept here, not in\n// main.tsx, so this file stays the only one that touches sqlite directly.\nexport async function getCachedRegion(\n prefix: string,\n): Promise<{ region: string | null; fetchedAt: string } | null> {\n const result = await sqlite.execute({\n sql: `SELECT region, fetched_at FROM geo_cache WHERE ip_prefix = ?`,\n args: [prefix],\n });\n const row = result.rows[0] as unknown as { region: string | null; fetched_at: string } | undefined;\n return row ? { region: row.region, fetchedAt: row.fetched_at } : null;\n}\n\nexport async function setCachedRegion(prefix: string, region: string | null): Promise {\n await sqlite.execute({\n sql:\n `INSERT INTO geo_cache (ip_prefix, region, fetched_at) VALUES (?, ?, datetime('now'))\n ON CONFLICT(ip_prefix) DO UPDATE SET region = excluded.region, fetched_at = excluded.fetched_at`,\n args: [prefix, region],\n });\n}\n\nexport async function recordEvent(f: EventFields) {\n await sqlite.execute({\n sql: `INSERT INTO pageviews (\n site, path, referrer, ua,\n country, country_code, region,\n utm_source, utm_medium, utm_campaign, utm_term, utm_content,\n device_type, browser, os, viewport, visitor_id, traffic_source, event, event_label\n ) VALUES (?,?,?,?, ?,?,?, ?,?,?,?,?, ?,?,?,?,?,?,?,?)`,\n args: [\n f.site,\n f.path,\n f.referrer,\n f.ua,\n f.geo?.country ?? null,\n f.geo?.countryCode ?? null,\n f.geo?.region ?? null,\n f.utmSource ?? null,\n f.utmMedium ?? null,\n f.utmCampaign ?? null,\n f.utmTerm ?? null,\n f.utmContent ?? null,\n f.deviceType ?? null,\n f.browser ?? null,\n f.os ?? null,\n f.viewport ?? null,\n f.visitorId ?? null,\n f.trafficSource ?? null,\n f.event ?? \"pageview\",\n f.eventLabel ?? null,\n ],\n });\n}\n\n// One-time (idempotent) backfill for rows recorded before this schema\n// existed. `ua` and `referrer` were captured from the very first version of\n// this tracker, so device/browser/OS and traffic-source CAN be recovered for\n// old rows by re-parsing them now. What can NOT be recovered: geolocation\n// (no IP was ever stored — deliberately, for privacy — so there's nothing to\n// re-look-up) and UTM/visitor-id (the old beacon never sent them; the data\n// simply doesn't exist). Only touches rows that haven't been backfilled yet.\n// `all=true` reclassifies every row instead of only ones never touched —\n// for when the parsing logic itself changed (e.g. the bot-detection fix)\n// and existing rows may have been classified wrong the first time, not just\n// left unclassified.\nexport async function backfillHistoricalRows(all = false): Promise {\n const rows = await sqlite.execute(\n all\n ? `SELECT id, ua, referrer, utm_source, utm_medium FROM pageviews LIMIT 2000`\n : `SELECT id, ua, referrer, utm_source, utm_medium FROM pageviews WHERE device_type IS NULL LIMIT 2000`,\n );\n const list = rows.rows as unknown as {\n id: number;\n ua: string;\n referrer: string;\n utm_source: string | null;\n utm_medium: string | null;\n }[];\n for (const r of list) {\n const { deviceType, browser, os } = parseUA(r.ua || \"\");\n // Re-derive from the row's own utm columns (not null,null) — a forced\n // `all` pass must not clobber a newer row's already-correct UTM-based\n // classification (Paid/Campaign/etc.) by falling back to referrer-only.\n const trafficSource = classifyTrafficSource(r.referrer || \"\", r.utm_medium, r.utm_source);\n await sqlite.execute({\n sql:\n `UPDATE pageviews SET device_type = ?, browser = ?, os = ?, traffic_source = ?, event = COALESCE(event, 'pageview') WHERE id = ?`,\n args: [deviceType, browser, os, trafficSource, r.id],\n });\n }\n return list.length;\n}\n\nexport async function siteTotals(sinceSql: string) {\n const result = await sqlite.execute({\n sql:\n `SELECT site, COUNT(*) as views FROM pageviews WHERE created_at >= datetime('now', ?) GROUP BY site ORDER BY views DESC`,\n args: [sinceSql],\n });\n return result.rows as unknown as { site: string; views: number }[];\n}\n\n// A GA4-style filter set: every Reports-tab query takes one of these instead\n// of a growing list of positional params, so adding a new filter dimension\n// (traffic source, device) doesn't mean touching every function's signature\n// again. `trafficSource` of \"Referral\" or \"Campaign\" matches as a prefix,\n// since the real values carry the host/source too (e.g. \"Referral (x.com)\")\n// and there's no fixed small set to offer exact-match options for.\nexport interface ReportFilters {\n sinceSql: string;\n // Optional upper bound (created_at < datetime('now', untilSql)) — unset\n // means \"through now\". Only needed to carve out a bounded window, e.g.\n // the *previous* period for a GA4-style comparison (sinceSql=-60 day,\n // untilSql=-30 day gives the 30 days immediately before the current one).\n untilSql?: string;\n site?: string;\n trafficSource?: string;\n deviceType?: string;\n}\n\nfunction buildWhere(f: ReportFilters): { where: string; args: unknown[] } {\n const clauses = [`created_at >= datetime('now', ?)`];\n const args: unknown[] = [f.sinceSql];\n if (f.untilSql) {\n clauses.push(`created_at < datetime('now', ?)`);\n args.push(f.untilSql);\n }\n if (f.site) {\n clauses.push(`site = ?`);\n args.push(f.site);\n }\n if (f.trafficSource) {\n if (f.trafficSource === \"Referral\" || f.trafficSource === \"Campaign\") {\n clauses.push(`traffic_source LIKE ?`);\n args.push(f.trafficSource + \"%\");\n } else {\n clauses.push(`traffic_source = ?`);\n args.push(f.trafficSource);\n }\n }\n if (f.deviceType) {\n clauses.push(`device_type = ?`);\n args.push(f.deviceType);\n }\n return { where: clauses.join(\" AND \"), args };\n}\n\nexport async function topPaths(f: ReportFilters, limit = 10) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql: `SELECT path, COUNT(*) as views FROM pageviews WHERE ${where} GROUP BY path ORDER BY views DESC LIMIT ?`,\n args: [...args, limit],\n });\n return result.rows as unknown as { path: string; views: number }[];\n}\n\nexport async function recentViews(limit = 50, site?: string) {\n const result = await sqlite.execute({\n sql: site\n ? `SELECT * FROM pageviews WHERE site = ? ORDER BY id DESC LIMIT ?`\n : `SELECT * FROM pageviews ORDER BY id DESC LIMIT ?`,\n args: site ? [site, limit] : [limit],\n });\n return result.rows as unknown as Pageview[];\n}\n\n// \"Live\" view — visits within the last N minutes, most recent first. This\n// page auto-refreshes every 15s (plain , no websocket), so this\n// query is what actually drives the near-real-time feel.\nexport async function activeVisits(minutes = 10, limit = 100, site?: string) {\n const result = await sqlite.execute({\n sql: site\n ? `SELECT * FROM pageviews WHERE site = ? AND created_at >= datetime('now', ?) ORDER BY id DESC LIMIT ?`\n : `SELECT * FROM pageviews WHERE created_at >= datetime('now', ?) ORDER BY id DESC LIMIT ?`,\n args: site ? [site, `-${minutes} minute`, limit] : [`-${minutes} minute`, limit],\n });\n return result.rows as unknown as Pageview[];\n}\n\nexport async function topLocations(f: ReportFilters, limit = 12) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql:\n `SELECT COALESCE(region, country, 'Unknown') as place, country_code, COUNT(*) as views\n FROM pageviews WHERE ${where} GROUP BY place, country_code ORDER BY views DESC LIMIT ?`,\n args: [...args, limit],\n });\n return result.rows as unknown as { place: string; country_code: string | null; views: number }[];\n}\n\n// Exhaustive (no LIMIT) country_code/region counts for a site, used to\n// compute the \"Geo match\" card — what fraction of a site's traffic actually\n// comes from the state it targets. Needs every bucket, not just the top N,\n// since the match/other-US/non-US/unknown split has to add up to 100%.\nexport async function regionBreakdown(f: ReportFilters) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql:\n `SELECT COALESCE(country_code, '') as country_code, COALESCE(region, '') as region, COUNT(*) as n\n FROM pageviews WHERE ${where} GROUP BY country_code, region`,\n args,\n });\n return result.rows as unknown as { country_code: string; region: string; n: number }[];\n}\n\nexport async function trafficSources(f: ReportFilters, limit = 10) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql:\n `SELECT COALESCE(traffic_source, 'Unknown') as source, COUNT(*) as views FROM pageviews WHERE ${where} GROUP BY source ORDER BY views DESC LIMIT ?`,\n args: [...args, limit],\n });\n return result.rows as unknown as { source: string; views: number }[];\n}\n\nexport async function deviceBreakdown(f: ReportFilters, limit = 20) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql:\n `SELECT COALESCE(device_type,'unknown') as device_type, COALESCE(browser,'unknown') as browser, COALESCE(os,'unknown') as os, COUNT(*) as views\n FROM pageviews WHERE ${where} GROUP BY device_type, browser, os ORDER BY views DESC LIMIT ?`,\n args: [...args, limit],\n });\n return result.rows as unknown as { device_type: string; browser: string; os: string; views: number }[];\n}\n\nexport async function topCampaigns(f: ReportFilters, limit = 10) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql:\n `SELECT utm_source, utm_medium, utm_campaign, COUNT(*) as views FROM pageviews\n WHERE ${where} AND utm_campaign IS NOT NULL\n GROUP BY utm_source, utm_medium, utm_campaign ORDER BY views DESC LIMIT ?`,\n args: [...args, limit],\n });\n return result.rows as unknown as {\n utm_source: string | null;\n utm_medium: string | null;\n utm_campaign: string | null;\n views: number;\n }[];\n}\n\nexport async function eventBreakdown(f: ReportFilters, limit = 15) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql:\n `SELECT event, COALESCE(event_label, '') as event_label, COUNT(*) as n FROM pageviews\n WHERE ${where} AND event IS NOT NULL AND event != 'pageview'\n GROUP BY event, event_label ORDER BY n DESC LIMIT ?`,\n args: [...args, limit],\n });\n return result.rows as unknown as { event: string; event_label: string; n: number }[];\n}\n\n// Of visitors active in the window, how many are new (their very first-ever\n// hit falls inside the window) vs returning (they'd already been seen\n// before it started). Only counts rows carrying a visitor_id, so it's\n// meaningless for data recorded before that field existed. The filters\n// (site/source/device) scope which visits count as \"active\"; whether that\n// visitor is new-or-returning is still judged against their entire history.\nexport async function newVsReturning(f: ReportFilters) {\n const { where, args } = buildWhere(f);\n const sql = `\n WITH active AS (\n SELECT DISTINCT visitor_id FROM pageviews\n WHERE visitor_id IS NOT NULL AND ${where}\n ),\n first_seen AS (\n SELECT visitor_id, MIN(created_at) as fs FROM pageviews\n WHERE visitor_id IN (SELECT visitor_id FROM active)\n GROUP BY visitor_id\n )\n SELECT\n SUM(CASE WHEN fs >= datetime('now', ?) THEN 1 ELSE 0 END) as new_visitors,\n SUM(CASE WHEN fs < datetime('now', ?) THEN 1 ELSE 0 END) as returning_visitors\n FROM first_seen`;\n const result = await sqlite.execute({ sql, args: [...args, f.sinceSql, f.sinceSql] });\n const row = result.rows[0] as unknown as\n | { new_visitors: number | null; returning_visitors: number | null }\n | undefined;\n return { newVisitors: row?.new_visitors ?? 0, returningVisitors: row?.returning_visitors ?? 0 };\n}\n\n// Raw timestamps for the Reports tab's trend chart, one row per pageview in\n// the window. Bucketing into calendar days happens in main.tsx using\n// Eastern-time day keys — SQLite's date() only knows UTC, and the\n// business's own timezone is what \"by day\" should mean.\nexport async function dailyPageviewTimes(f: ReportFilters) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql: `SELECT created_at FROM pageviews WHERE ${where}`,\n args,\n });\n return (result.rows as unknown as { created_at: string }[]).map((r) => r.created_at);\n}\n\nexport async function dailySeriesRaw(f: ReportFilters) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql: `SELECT site, created_at FROM pageviews WHERE ${where}`,\n args,\n });\n return result.rows as unknown as { site: string; created_at: string }[];\n}\n\n// Raw rows for custom (non-pageview) events, for the Events report's trend\n// chart and per-event-name breakdown — same shape idea as\n// dailyPageviewTimes/dailySeriesRaw, just scoped to real events.\nexport async function eventTimes(f: ReportFilters) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql:\n `SELECT created_at, event, COALESCE(event_label, '') as event_label, site FROM pageviews\n WHERE ${where} AND event IS NOT NULL AND event != 'pageview'`,\n args,\n });\n return result.rows as unknown as {\n created_at: string;\n event: string;\n event_label: string;\n site: string;\n }[];\n}\n","createdAt",["D",1789226792184],"branchVersion","branchId","0e49238c-239c-4e36-95d6-7f0c7c028ce1",{"_31":47,"_33":46,"_34":48,"_36":50,"_39":51,"_41":14,"_42":43,"_25":26},{"_31":47,"_33":46,"_34":48,"_36":49},"main.tsx","25c3e054-ae27-11f1-936e-1607ee4eb77e","http","import { Hono } from \"npm:hono@4\";\nimport {\n activeVisits,\n backfillHistoricalRows,\n dailyPageviewTimes,\n dailySeriesRaw,\n deviceBreakdown,\n eventBreakdown,\n eventTimes,\n type Geo,\n newVsReturning,\n type Pageview,\n recentViews,\n recordEvent,\n type ReportFilters,\n siteTotals,\n topCampaigns,\n topLocations,\n topPaths,\n trafficSources,\n} from \"./db.ts\";\nimport {\n classifyTrafficSource,\n easternDayKey,\n formatDayLabel,\n formatEastern,\n parseSqliteUtc,\n parseUA,\n} from \"./util.ts\";\nimport { feature } from \"npm:topojson-client@3\";\n\nconst app = new Hono();\n\n// Map geometry (coastlines, country borders, US state borders), fetched once\n// per val instance and cached in memory — \"50m\"/\"10m\" resolution TopoJSON,\n// a few hundred KB each, plenty for a dashboard map without being enormous.\n// Gives the dot-map actual recognizable geography instead of a bare grid.\ntype Rings = [number, number][][];\nconst topoCache = new Map();\nasync function fetchTopoRings(url: string, objectKey: string): Promise {\n const cacheKey = `${url}#${objectKey}`;\n const cached = topoCache.get(cacheKey);\n if (cached) return cached;\n try {\n const res = await fetch(url, { signal: AbortSignal.timeout(6000) });\n const topology = await res.json();\n const geo = feature(topology, topology.objects[objectKey]) as any;\n const rings: Rings = [];\n const geoms = geo.type === \"FeatureCollection\"\n ? geo.features.map((f: any) => f.geometry)\n : [geo.geometry];\n for (const g of geoms) {\n if (!g) continue;\n const polys = g.type === \"MultiPolygon\" ? g.coordinates : g.type === \"Polygon\" ? [g.coordinates] : [];\n for (const poly of polys) for (const ring of poly) rings.push(ring);\n }\n topoCache.set(cacheKey, rings);\n return rings;\n } catch (err) {\n console.error(`topo fetch failed for ${objectKey}:`, err);\n topoCache.set(cacheKey, []);\n return [];\n }\n}\nfunction getWorldLandRings() {\n return fetchTopoRings(\"https://cdn.jsdelivr.net/npm/world-atlas@2.0.2/land-50m.json\", \"land\");\n}\nfunction getCountryBorderRings() {\n return fetchTopoRings(\"https://cdn.jsdelivr.net/npm/world-atlas@2.0.2/countries-50m.json\", \"countries\");\n}\nfunction getUSStateBorderRings() {\n return fetchTopoRings(\"https://cdn.jsdelivr.net/npm/us-atlas@3/states-10m.json\", \"states\");\n}\n\n// 1x1 transparent GIF, served as the tracking beacon.\nconst PIXEL = Uint8Array.from(\n atob(\"R0lGODlhAQABAIAAAAAAAP///ywAAAAAAQABAAACAUwAOw==\"),\n (c) => c.charCodeAt(0),\n);\n\nconst SITE_LABELS: Record = {\n \"mississippivehicleregistrationrenewal.com\": \"MS Vehicle Registration\",\n \"fileunemploymentflorida.com\": \"FL Unemployment Benefits\",\n \"notarypublicsouthdakota.com\": \"SD Notary Public\",\n \"delawareevtaxcredit.com\": \"DE EV & Solar Incentives\",\n \"homesteadexemptioninflorida.com\": \"FL Homestead Exemption\",\n \"filefloridasalestax.com\": \"FL Sales Tax\",\n \"filenewyorksalestax.com\": \"NY Sales Tax\",\n \"jerseywaterdeliverypros.com\": \"Jersey Water Delivery Pros\",\n \"manhattanwaterdeliverypros.com\": \"Manhattan Water Delivery Pros\",\n \"miamiwaterdeliverypros.com\": \"Miami Water Delivery Pros\",\n};\n\nfunction siteLabel(site: string) {\n return SITE_LABELS[site] || site;\n}\n\n// Cloudflare Pages serves every project at its own .pages.dev\n// subdomain in addition to the custom domain — real hits land there from\n// crawlers/previews, but they're noise the dashboard doesn't need to surface.\nfunction isDevHost(site: string) {\n return site.endsWith(\".pages.dev\");\n}\n\nfunction esc(s: string) {\n return s.replace(/[&<>\"']/g, (c) =>\n ({ \"&\": \"&\", \"<\": \"<\", \">\": \">\", '\"': \""\", \"'\": \"'\" }[c] as string)\n );\n}\n\n// Country code -> flag emoji, via regional indicator symbols. Cosmetic only.\nfunction flag(cc: string | null) {\n if (!cc || cc.length !== 2) return \"\";\n const codePoints = [...cc.toUpperCase()].map((c) => 0x1f1e6 + (c.charCodeAt(0) - 65));\n return String.fromCodePoint(...codePoints);\n}\n\nfunction timeAgo(iso: string) {\n const then = new Date(iso.replace(\" \", \"T\") + \"Z\").getTime();\n const secs = Math.max(0, Math.round((Date.now() - then) / 1000));\n if (secs < 60) return `${secs}s ago`;\n if (secs < 3600) return `${Math.floor(secs / 60)}m ago`;\n return `${Math.floor(secs / 3600)}h ago`;\n}\n\nfunction isPrivateIp(ip: string) {\n return /^(10\\.|127\\.|192\\.168\\.|172\\.(1[6-9]|2\\d|3[01])\\.|::1$|fc|fd|0\\.0\\.0\\.0)/.test(ip);\n}\n\n// Best-effort IP -> approximate location. Free, keyless, city-level accuracy\n// at best — this is \"rough mapping\", not precise tracking. Never blocks the\n// pixel response on failure; a 2.5s timeout keeps a slow lookup from making\n// the beacon itself slow.\nasync function lookupGeo(ip: string): Promise {\n if (!ip || isPrivateIp(ip)) return null;\n try {\n const res = await fetch(`https://ipapi.co/${ip}/json/`, {\n signal: AbortSignal.timeout(2500),\n headers: { \"User-Agent\": \"tribeca-site-analytics/1.0\" },\n });\n if (!res.ok) return null;\n const data = await res.json();\n if (!data || data.error) return null;\n return {\n country: data.country_name ?? null,\n countryCode: data.country_code ?? null,\n region: data.region ?? null,\n city: data.city ?? null,\n lat: typeof data.latitude === \"number\" ? data.latitude : null,\n lon: typeof data.longitude === \"number\" ? data.longitude : null,\n };\n } catch {\n return null;\n }\n}\n\n// The tracking beacon. A 1x1 GIF via , not fetch() — sidesteps CORS entirely,\n// works even on a strict _headers file, and degrades silently if blocked.\napp.get(\"/px.gif\", async (c) => {\n const q = (key: string, max = 300) => (c.req.query(key) || \"\").slice(0, max);\n const site = q(\"s\", 200).toLowerCase().replace(/^www\\./, \"\");\n const path = q(\"p\") || \"/\";\n const ref = q(\"r\");\n const ua = (c.req.header(\"user-agent\") || \"\").slice(0, 300);\n const ip = c.req.header(\"cf-connecting-ip\") ||\n (c.req.header(\"x-forwarded-for\") || \"\").split(\",\")[0].trim();\n\n // GA4-style flexible signals. All optional — the beacon still works (as a\n // plain pageview) if a site's snippet is the older, simpler version.\n const utmSource = q(\"utm_source\") || null;\n const utmMedium = q(\"utm_medium\") || null;\n const utmCampaign = q(\"utm_campaign\") || null;\n const utmTerm = q(\"utm_term\") || null;\n const utmContent = q(\"utm_content\") || null;\n const viewport = q(\"vp\", 20) || null;\n const visitorId = q(\"vid\", 64) || null;\n // \"ev\" lets a site call window.tribecaTrack('cta_click', 'quote button')\n // for its own custom events, reusing this same pixel — no new endpoint,\n // no schema change needed for whatever a site wants to track next.\n const eventName = q(\"ev\", 60) || \"pageview\";\n const eventLabel = q(\"el\", 200) || null;\n\n if (site) {\n try {\n const geo = await lookupGeo(ip);\n const { deviceType, browser, os } = parseUA(ua);\n const trafficSource = classifyTrafficSource(ref, utmMedium, utmSource);\n await recordEvent({\n site,\n path,\n referrer: ref,\n ua,\n geo,\n utmSource,\n utmMedium,\n utmCampaign,\n utmTerm,\n utmContent,\n deviceType,\n browser,\n os,\n viewport,\n visitorId,\n trafficSource,\n event: eventName,\n eventLabel,\n });\n } catch (err) {\n console.error(\"pixel record failed:\", err);\n }\n }\n\n c.header(\"Content-Type\", \"image/gif\");\n c.header(\"Cache-Control\", \"no-store, no-cache, must-revalidate, private\");\n c.header(\"Access-Control-Allow-Origin\", \"*\");\n return c.body(PIXEL);\n});\n\n// One-time (repeatable, idempotent) backfill trigger for rows recorded\n// before device/browser/traffic-source existed. Same key as the dashboard.\napp.get(\"/admin/backfill\", async (c) => {\n const wantKey = Deno.env.get(\"ANALYTICS_DASH_KEY\");\n if (wantKey && c.req.query(\"key\") !== wantKey) return c.text(\"Forbidden\", 403);\n const all = c.req.query(\"all\") === \"1\";\n const updated = await backfillHistoricalRows(all);\n return c.json({ updated, note: updated === 2000 ? \"hit the per-call cap — call again\" : \"done\" });\n});\n\napp.get(\"/\", (c) => c.redirect(\"/dashboard\"));\n\n// A rough equirectangular dot map, auto-zoomed to the bounding box of the\n// points actually plotted (so a mostly-US audience isn't three pixels in the\n// corner of a full world map). lon -180..180 -> x, lat 90..-90 -> y.\nasync function buildMapSvg(points: Pageview[]) {\n const withGeo = points.filter((p) => p.lat != null && p.lon != null);\n\n // Default view: continental US, since that's where this network's traffic\n // actually lives. The map frame (grid + bounds) always renders even with\n // zero points — an empty grid reads as \"no one's here right now\", which is\n // the truth; a blank space where the map should be reads as \"broken\".\n let minLat = 24, maxLat = 50, minLon = -125, maxLon = -66;\n\n if (withGeo.length > 0) {\n const lats = withGeo.map((p) => p.lat as number);\n const lons = withGeo.map((p) => p.lon as number);\n const padDeg = 4;\n minLat = Math.min(...lats) - padDeg;\n maxLat = Math.max(...lats) + padDeg;\n minLon = Math.min(...lons) - padDeg;\n maxLon = Math.max(...lons) + padDeg;\n // Keep a sane minimum span so a single visitor isn't a meaningless close-up.\n if (maxLat - minLat < 10) {\n const mid = (maxLat + minLat) / 2;\n minLat = mid - 5;\n maxLat = mid + 5;\n }\n if (maxLon - minLon < 10) {\n const mid = (maxLon + minLon) / 2;\n minLon = mid - 5;\n maxLon = mid + 5;\n }\n minLat = Math.max(minLat, -85);\n maxLat = Math.min(maxLat, 85);\n minLon = Math.max(minLon, -180);\n maxLon = Math.min(maxLon, 180);\n }\n\n const W = 720, H = 420;\n const x = (lon: number) => ((lon - minLon) / (maxLon - minLon)) * W;\n const y = (lat: number) => H - ((lat - minLat) / (maxLat - minLat)) * H;\n\n const [landRings, countryRings, stateRings] = await Promise.all([\n getWorldLandRings(),\n getCountryBorderRings(),\n getUSStateBorderRings(),\n ]);\n const ringsToPath = (rings: Rings) =>\n rings.map((ring) =>\n \"M\" + ring.map(([lon, lat]) => `${x(lon).toFixed(1)},${y(lat).toFixed(1)}`).join(\"L\") + \"Z\"\n ).join(\" \");\n const landPath = ringsToPath(landRings);\n const countryPath = ringsToPath(countryRings);\n const statePath = ringsToPath(stateRings);\n\n // Gridlines every 10 degrees within the visible bounds.\n const gridLines: string[] = [];\n for (let lon = Math.ceil(minLon / 10) * 10; lon <= maxLon; lon += 10) {\n gridLines.push(``);\n }\n for (let lat = Math.ceil(minLat / 10) * 10; lat <= maxLat; lat += 10) {\n gridLines.push(``);\n }\n\n // Most recent first in the input; draw oldest-first so the freshest dot\n // renders on top, then brighten by recency rank.\n const ordered = [...withGeo].reverse();\n const n = ordered.length;\n const dots = ordered.map((p, i) => {\n const cx = x(p.lon as number).toFixed(1);\n const cy = y(p.lat as number).toFixed(1);\n const recency = (i + 1) / n; // 0..1, newest = 1\n const r = (3.5 + recency * 3).toFixed(1);\n const opacity = (0.35 + recency * 0.65).toFixed(2);\n const place = [p.city, p.region, p.country].filter(Boolean).join(\", \") || \"Unknown location\";\n // parseUA files anything crawler-, script-, or headless-browser-shaped\n // under browser \"Bot\" (checked ahead of the normal browser checks, so a\n // crawler UA that also happens to mention \"Chrome\" isn't missed) — the\n // one signal this tracker has for \"was this actually a person.\"\n const isBot = p.browser === \"Bot\";\n const label = [\n `${siteLabel(p.site)} — ${place}`,\n formatEastern(p.created_at),\n `${p.traffic_source || \"Unknown source\"} · ${p.device_type || \"device unknown\"} · ${\n isBot ? \"🤖 likely bot\" : \"🧑 person\"\n }`,\n ].join(\"\\n\");\n const tip = esc(label);\n return `` +\n ``;\n }).join(\"\");\n\n const svg = ``;\n\n if (withGeo.length === 0) {\n return svg + `

No located visits yet in this window.

`;\n }\n return svg;\n}\n\nfunction locationRows(rows: { place: string; country_code: string | null; views: number }[]) {\n return rows.map((r) =>\n `${flag(r.country_code)} ${esc(r.place)}${r.views}`\n ).join(\"\");\n}\n\nfunction trafficRows(rows: { source: string; views: number }[]) {\n return rows.map((r) => `${esc(r.source)}${r.views}`).join(\n \"\",\n );\n}\n\nfunction deviceRows(rows: { device_type: string; browser: string; os: string; views: number }[]) {\n return rows.map((r) =>\n `${esc(r.device_type)}${esc(r.browser)}${esc(r.os)}${r.views}`\n ).join(\"\");\n}\n\nfunction campaignRows(\n rows: { utm_source: string | null; utm_medium: string | null; utm_campaign: string | null; views: number }[],\n) {\n return rows.map((r) =>\n `${esc(r.utm_campaign || \"—\")}${esc(r.utm_source || \"—\")}${\n esc(r.utm_medium || \"—\")\n }${r.views}`\n ).join(\"\");\n}\n\nfunction eventRows(rows: { event: string; event_label: string; n: number }[]) {\n return rows.map((r) =>\n `${esc(r.event)}${esc(r.event_label || \"—\")}${r.n}`\n ).join(\"\");\n}\n\nfunction liveRows(rows: Pageview[], showSite = true) {\n return rows.map((r) => {\n const place = [r.city, r.region, r.country_code ? r.country_code : r.country].filter(Boolean)\n .join(\", \") || \"Unknown\";\n return `\n ${showSite ? `${esc(siteLabel(r.site))}` : \"\"}\n ${esc(r.path)}\n ${flag(r.country_code)} ${esc(place)}\n ${esc(r.referrer || \"—\")}\n ${timeAgo(r.created_at)}\n `;\n }).join(\"\");\n}\n\nfunction navTabs(active: \"overview\" | \"reports\" | \"events\", site: string, keyQs: string) {\n const siteQs = site ? `&site=${encodeURIComponent(site)}` : \"\";\n const tab = (href: string, label: string, isActive: boolean) =>\n `${label}`;\n return `

\n ${tab(`/dashboard?key=${keyQs}${siteQs}`, \"Overview\", active === \"overview\")}\n ${tab(`/reports?key=${keyQs}${siteQs}`, \"Reports\", active === \"reports\")}\n ${tab(`/events?key=${keyQs}${siteQs}`, \"Events\", active === \"events\")}\n

`;\n}\n\nconst CHART_COLORS = [\n \"#5b8def\",\n \"#34d399\",\n \"#f59e0b\",\n \"#f472b6\",\n \"#a78bfa\",\n \"#22d3ee\",\n \"#fb7185\",\n \"#84cc16\",\n \"#facc15\",\n \"#38bdf8\",\n];\n\n// A dependency-free multi-line chart: one per series on a shared,\n// gap-filled day axis (missing days are zero rather than silently compressing\n// the x-axis). Built for 1..N series so the same renderer covers both the\n// single-site line and the all-sites, one-line-per-site breakdown.\nfunction buildLineChart(\n series: { label: string; color: string; points: { day: string; views: number }[] }[],\n days: number,\n) {\n const dayKeys: string[] = [];\n for (let i = days - 1; i >= 0; i--) {\n dayKeys.push(easternDayKey(new Date(Date.now() - i * 86400000)));\n }\n\n const filled = series.map((s) => {\n const byDay = new Map(s.points.map((p) => [p.day, p.views]));\n return { ...s, values: dayKeys.map((k) => byDay.get(k) ?? 0) };\n });\n\n const W = 720, H = 220, padL = 34, padR = 10, padT = 10, padB = 22;\n const plotW = W - padL - padR, plotH = H - padT - padB;\n const max = Math.max(1, ...filled.flatMap((s) => s.values));\n const slot = dayKeys.length > 1 ? plotW / (dayKeys.length - 1) : 0;\n const xAt = (i: number) => padL + i * slot;\n const yAt = (v: number) => padT + plotH - (v / max) * plotH;\n const multi = filled.length > 1;\n\n const lines = filled.map((s) => {\n const pts = s.values.map((v, i) => `${xAt(i).toFixed(1)},${yAt(v).toFixed(1)}`).join(\" \");\n return ``;\n }).join(\"\");\n\n // A visible dot plus a larger invisible hover target at every data point,\n // each carrying its own tooltip text (date + series + count) via\n // data-tip — the page-level script in page() positions a floating\n // tooltip on hover, so every point is individually inspectable rather\n // than the whole line sharing one title.\n const points = filled.map((s) => {\n return dayKeys.map((k, i) => {\n const v = s.values[i];\n const cx = xAt(i).toFixed(1), cy = yAt(v).toFixed(1);\n const tip = esc(`${multi ? s.label + \" — \" : \"\"}${formatDayLabel(k)}: ${v} view${v === 1 ? \"\" : \"s\"}`);\n return `` +\n ``;\n }).join(\"\");\n }).join(\"\");\n\n const ticks = [0, 0.5, 1].map((f) => {\n const val = Math.round(max * f);\n const ty = padT + plotH - f * plotH;\n return `\n ${val}`;\n }).join(\"\");\n\n const xStep = Math.max(1, Math.ceil(dayKeys.length / 8));\n const lastRegular = Math.floor((dayKeys.length - 1) / xStep) * xStep;\n const xLabels = dayKeys.map((day, i) => {\n const isRegular = i % xStep === 0;\n const isFinal = i === dayKeys.length - 1 && i - lastRegular >= Math.ceil(xStep / 2);\n if (!isRegular && !isFinal) return \"\";\n return `${day.slice(5)}`;\n }).join(\"\");\n\n const svg = ``;\n\n const legend = series.length > 1\n ? `

${\n series.map((s) =>\n `${esc(s.label)}`\n ).join(\"\")\n }

`\n : \"\";\n\n return svg + legend;\n}\n\n// GA4-style \"vs previous period\": a small colored delta under a stat card.\n// previous === 0 has no ratio to show — \"new\" if there's now activity where\n// there was none, an em dash if both periods are genuinely zero.\nfunction deltaBadge(current: number, previous: number): string {\n if (previous === 0) {\n return current === 0\n ? `—`\n : `new`;\n }\n const pct = ((current - previous) / previous) * 100;\n const cls = pct > 0.5 ? \"up\" : pct < -0.5 ? \"down\" : \"flat\";\n const sign = pct > 0 ? \"+\" : \"\";\n return `${sign}${pct.toFixed(0)}% vs ${previous}`;\n}\n\n// Two-line \"this period vs previous period\" chart, aligned by day-offset\n// within each period rather than by calendar date (their calendar dates\n// don't overlap) — the same idea as GA4's comparison chart. Replaces the\n// per-site breakdown chart while comparison mode is on, since overlaying\n// N sites × 2 periods stops being readable.\nfunction buildComparisonChart(\n currentPoints: { day: string; views: number }[],\n previousPoints: { day: string; views: number }[],\n days: number,\n) {\n const curDayKeys: string[] = [];\n const prevDayKeys: string[] = [];\n for (let i = days - 1; i >= 0; i--) {\n curDayKeys.push(easternDayKey(new Date(Date.now() - i * 86400000)));\n prevDayKeys.push(easternDayKey(new Date(Date.now() - (i + days) * 86400000)));\n }\n const curByDay = new Map(currentPoints.map((p) => [p.day, p.views]));\n const prevByDay = new Map(previousPoints.map((p) => [p.day, p.views]));\n const curValues = curDayKeys.map((k) => curByDay.get(k) ?? 0);\n const prevValues = prevDayKeys.map((k) => prevByDay.get(k) ?? 0);\n\n const W = 720, H = 220, padL = 34, padR = 10, padT = 10, padB = 22;\n const plotW = W - padL - padR, plotH = H - padT - padB;\n const max = Math.max(1, ...curValues, ...prevValues);\n const slot = days > 1 ? plotW / (days - 1) : 0;\n const xAt = (i: number) => padL + i * slot;\n const yAt = (v: number) => padT + plotH - (v / max) * plotH;\n const CUR_COLOR = \"#5b8def\", PREV_COLOR = \"#6b7280\";\n\n const lineFor = (values: number[], color: string, dashed: boolean) => {\n const pts = values.map((v, i) => `${xAt(i).toFixed(1)},${yAt(v).toFixed(1)}`).join(\" \");\n return ``;\n };\n const lines = lineFor(prevValues, PREV_COLOR, true) + lineFor(curValues, CUR_COLOR, false);\n\n const points = curDayKeys.map((k, i) => {\n const cv = curValues[i], pv = prevValues[i];\n const cx = xAt(i).toFixed(1);\n const curTip = esc(`This period — ${formatDayLabel(k)}: ${cv} view${cv === 1 ? \"\" : \"s\"}`);\n const prevTip = esc(\n `Previous period — ${formatDayLabel(prevDayKeys[i])}: ${pv} view${pv === 1 ? \"\" : \"s\"}`,\n );\n return `` +\n `` +\n `` +\n ``;\n }).join(\"\");\n\n const ticks = [0, 0.5, 1].map((f) => {\n const val = Math.round(max * f);\n const ty = padT + plotH - f * plotH;\n return `\n ${val}`;\n }).join(\"\");\n\n const xStep = Math.max(1, Math.ceil(curDayKeys.length / 8));\n const lastRegular = Math.floor((curDayKeys.length - 1) / xStep) * xStep;\n const xLabels = curDayKeys.map((day, i) => {\n const isRegular = i % xStep === 0;\n const isFinal = i === curDayKeys.length - 1 && i - lastRegular >= Math.ceil(xStep / 2);\n if (!isRegular && !isFinal) return \"\";\n return `${day.slice(5)}`;\n }).join(\"\");\n\n const svg = ``;\n\n const legend = `

\n This period\n Previous period\n

`;\n\n return svg + legend;\n}\n\nconst REPORT_RANGES = [7, 14, 30, 90];\nconst SOURCE_OPTIONS = [\n [\"\", \"All sources\"],\n [\"Direct\", \"Direct\"],\n [\"Organic Search\", \"Organic Search\"],\n [\"Paid\", \"Paid\"],\n [\"Social\", \"Social\"],\n [\"Social (campaign)\", \"Social (campaign)\"],\n [\"Email\", \"Email\"],\n [\"Referral\", \"Referral (any)\"],\n [\"Campaign\", \"Campaign (any)\"],\n];\nconst DEVICE_OPTIONS = [\n [\"\", \"All devices\"],\n [\"desktop\", \"Desktop\"],\n [\"mobile\", \"Mobile\"],\n [\"tablet\", \"Tablet\"],\n];\n\n// A GET form (no client JS framework needed) so every filter combination is\n// a plain, bookmarkable/shareable URL — ?days=&source=&device= alongside the\n// existing ?site=&key=. Each \n ${site ? `` : \"\"}\n Range\n Traffic source\n Device\n ${\n eventOptions\n ? `Event`\n : \"\"\n }\n Compare to previous period\n ${changed ? `Clear filters` : \"\"}\n `;\n}\n\napp.get(\"/reports\", async (c) => {\n const key = c.req.query(\"key\");\n const wantKey = Deno.env.get(\"ANALYTICS_DASH_KEY\");\n if (wantKey && key !== wantKey) {\n return c.text(\"Forbidden — add ?key=... to the URL.\", 403);\n }\n\n const site = c.req.query(\"site\") || \"\";\n const keyQs = esc(key || \"\");\n const daysParam = parseInt(c.req.query(\"days\") || \"30\", 10);\n const days = REPORT_RANGES.includes(daysParam) ? daysParam : 30;\n const source = c.req.query(\"source\") || \"\";\n const device = c.req.query(\"device\") || \"\";\n const compare = c.req.query(\"compare\") === \"1\";\n const sinceSql = `-${days} day`;\n\n // `base` omits source/device so the two breakdown tables that populate\n // those filter dropdowns (traffic sources, devices) always show the full,\n // unfiltered picture — otherwise picking \"Mobile\" would collapse the\n // device table to a single row with nothing else to switch to.\n const base: ReportFilters = { sinceSql, site: site || undefined };\n const applied: ReportFilters = {\n sinceSql,\n site: site || undefined,\n trafficSource: source || undefined,\n deviceType: device || undefined,\n };\n // The N days immediately before the current period, same filters — GA4's\n // default \"preceding period\" comparison.\n const previous: ReportFilters = {\n ...applied,\n sinceSql: `-${days * 2} day`,\n untilSql: `-${days} day`,\n };\n\n const [\n dailyTimes,\n dailyBySiteRaw,\n sources,\n locations,\n devices,\n campaigns,\n events,\n nvr,\n top30,\n prevDailyTimes,\n prevNvr,\n ] = await Promise.all([\n dailyPageviewTimes(applied),\n site ? Promise.resolve([]) : dailySeriesRaw(applied),\n trafficSources(base),\n topLocations(applied),\n deviceBreakdown(base),\n topCampaigns(applied),\n eventBreakdown(applied),\n newVsReturning(applied),\n site ? topPaths(applied, 15) : Promise.resolve([]),\n compare ? dailyPageviewTimes(previous) : Promise.resolve([]),\n compare ? newVsReturning(previous) : Promise.resolve({ newVisitors: 0, returningVisitors: 0 }),\n ]);\n\n // Bucket into Eastern-time calendar days here in JS (Intl handles the\n // EST/EDT switch) rather than in SQL, which only ever knows UTC.\n const bucketByDay = (times: string[]) => {\n const counts = new Map();\n for (const t of times) {\n const k = easternDayKey(parseSqliteUtc(t));\n counts.set(k, (counts.get(k) ?? 0) + 1);\n }\n return Array.from(counts, ([day, views]) => ({ day, views }));\n };\n\n const daily = bucketByDay(dailyTimes);\n const totalViews = dailyTimes.length;\n const avgPerDay = Math.round(totalViews / days);\n const prevTotalViews = prevDailyTimes.length;\n const prevAvgPerDay = Math.round(prevTotalViews / days);\n const topPagesRows = top30.map((p) =>\n `${esc(p.path)}${p.views}`\n ).join(\"\");\n\n // Comparison mode swaps the by-site breakdown for a single this-vs-previous\n // overlay — a per-site x per-period grid stops being readable, so pick one.\n let chartHtml: string;\n if (compare) {\n chartHtml = buildComparisonChart(daily, bucketByDay(prevDailyTimes), days);\n } else if (site) {\n chartHtml = buildLineChart([{ label: siteLabel(site), color: CHART_COLORS[0], points: daily }], days);\n } else {\n const bySite = new Map();\n for (const r of dailyBySiteRaw) {\n if (isDevHost(r.site)) continue;\n if (!bySite.has(r.site)) bySite.set(r.site, []);\n bySite.get(r.site)!.push(r.created_at);\n }\n const ranked = Array.from(bySite.entries())\n .map(([s, times]) => [s, times.length] as const)\n .sort((a, b) => b[1] - a[1]);\n const chartSeries = ranked.map(([s], i) => ({\n label: siteLabel(s),\n color: CHART_COLORS[i % CHART_COLORS.length],\n points: bucketByDay(bySite.get(s)!),\n }));\n chartHtml = buildLineChart(chartSeries, days);\n }\n\n const filterNote = [\n source && `source: ${SOURCE_OPTIONS.find(([v]) => v === source)?.[1] ?? source}`,\n device && `device: ${DEVICE_OPTIONS.find(([v]) => v === device)?.[1] ?? device}`,\n compare && \"comparing to previous period\",\n ].filter(Boolean).join(\" · \");\n\n return c.html(page(`\n ${navTabs(\"reports\", site, keyQs)}\n ${site ? `

← All sites

` : \"\"}\n

Reports${site ? ` — ${esc(siteLabel(site))}` : \" — all sites\"}

${site ? esc(site) : \"Historical trends across the lead-gen network\"} · last ${days} days${\n filterNote ? ` · ${esc(filterNote)}` : \"\"\n }

\n\n ${filterBar(keyQs, site, days, source, device, compare)}\n\n

${totalViews}

pageviews, ${days}d

${\n compare ? deltaBadge(totalViews, prevTotalViews) : \"\"\n }

${avgPerDay}

avg / day

${\n compare ? deltaBadge(avgPerDay, prevAvgPerDay) : \"\"\n }

${nvr.newVisitors}

new visitors

${\n compare ? deltaBadge(nvr.newVisitors, prevNvr.newVisitors) : \"\"\n }

${nvr.returningVisitors}

returning visitors

${\n compare ? deltaBadge(nvr.returningVisitors, prevNvr.returningVisitors) : \"\"\n }

\n\n

Pageviews by day${compare ? \" — vs previous period\" : site ? \"\" : \", by site\"}

${chartHtml}

\n\n

Traffic sources (${days} days)

${\n trafficRows(sources) || ''\n }

Source	Views
No data yet

\n\n

Top locations (${days} days)

${\n locationRows(locations) || ''\n }

Place	Views
No data yet

\n\n

Devices & browsers (${days} days)

${\n deviceRows(devices) || ''\n }

Device	Browser	OS	Views
No data yet

\n\n

Campaigns (${days} days)

${\n campaignRows(campaigns) || ''\n }

Campaign	Source	Medium	Views
No UTM-tagged traffic yet

\n\n ${\n site\n ? `

Top pages (${days} days)

\n ${\n topPagesRows || ''\n }

Path	Views
No data yet

`\n : \"\"\n }\n\n

Custom events (${days} days)

${\n eventRows(events) ||\n ''\n }

Event	Label	Count
No custom events tracked yet — call window.tribecaTrack(name, label) from a page

\n `, false));\n});\n\nfunction eventsBySiteRows(rows: { site: string; n: number }[]) {\n return rows.map((r) =>\n `${esc(siteLabel(r.site))}${r.n}`\n ).join(\"\");\n}\n\napp.get(\"/events\", async (c) => {\n const key = c.req.query(\"key\");\n const wantKey = Deno.env.get(\"ANALYTICS_DASH_KEY\");\n if (wantKey && key !== wantKey) {\n return c.text(\"Forbidden — add ?key=... to the URL.\", 403);\n }\n\n const site = c.req.query(\"site\") || \"\";\n const keyQs = esc(key || \"\");\n const daysParam = parseInt(c.req.query(\"days\") || \"30\", 10);\n const days = REPORT_RANGES.includes(daysParam) ? daysParam : 30;\n const source = c.req.query(\"source\") || \"\";\n const device = c.req.query(\"device\") || \"\";\n const eventName = c.req.query(\"event\") || \"\";\n const compare = c.req.query(\"compare\") === \"1\";\n const sinceSql = `-${days} day`;\n\n // `base` (no source/device/event) keeps the filter dropdowns — traffic\n // source, device, and the event-name list itself — showing every option\n // that exists in the window, the same reasoning as the Reports tab.\n const base: ReportFilters = { sinceSql, site: site || undefined };\n const applied: ReportFilters = {\n sinceSql,\n site: site || undefined,\n trafficSource: source || undefined,\n deviceType: device || undefined,\n };\n const previous: ReportFilters = { ...applied, sinceSql: `-${days * 2} day`, untilSql: `-${days} day` };\n\n const [rowsRaw, allEvents, prevRowsRaw] = await Promise.all([\n eventTimes(applied),\n eventBreakdown(base, 50),\n compare ? eventTimes(previous) : Promise.resolve([]),\n ]);\n const rows = rowsRaw.filter((r) => !isDevHost(r.site) && (!eventName || r.event === eventName));\n const prevRows = prevRowsRaw.filter((r) => !isDevHost(r.site) && (!eventName || r.event === eventName));\n\n const eventOptions = Array.from(new Set(allEvents.map((r) => r.event))).sort();\n\n const bucketByDay = (times: { created_at: string }[]) => {\n const counts = new Map();\n for (const t of times) {\n const k = easternDayKey(parseSqliteUtc(t.created_at));\n counts.set(k, (counts.get(k) ?? 0) + 1);\n }\n return Array.from(counts, ([day, views]) => ({ day, views }));\n };\n\n const totalEvents = rows.length;\n const avgPerDay = Math.round(totalEvents / days);\n const distinctTypes = new Set(rows.map((r) => r.event)).size;\n const prevTotalEvents = prevRows.length;\n const prevAvgPerDay = Math.round(prevTotalEvents / days);\n const prevDistinctTypes = new Set(prevRows.map((r) => r.event)).size;\n\n let chartHtml: string;\n if (compare) {\n chartHtml = buildComparisonChart(bucketByDay(rows), bucketByDay(prevRows), days);\n } else {\n const byEvent = new Map();\n for (const r of rows) {\n if (!byEvent.has(r.event)) byEvent.set(r.event, []);\n byEvent.get(r.event)!.push(r);\n }\n const ranked = Array.from(byEvent.entries())\n .map(([e, times]) => [e, times.length] as const)\n .sort((a, b) => b[1] - a[1]);\n const chartSeries = ranked.map(([e], i) => ({\n label: e,\n color: CHART_COLORS[i % CHART_COLORS.length],\n points: bucketByDay(byEvent.get(e)!),\n }));\n chartHtml = buildLineChart(chartSeries, days);\n }\n\n const bySite = new Map();\n for (const r of rows) bySite.set(r.site, (bySite.get(r.site) ?? 0) + 1);\n const bySiteRows = Array.from(bySite, ([s, n]) => ({ site: s, n })).sort((a, b) => b.n - a.n);\n\n const breakdownRows = allEvents.filter((r) => !eventName || r.event === eventName);\n\n // Outbound clicks get their own table — the generic event breakdown groups\n // by (event, label) already, but \"outbound_click\" rows are the ones worth\n // seeing ranked on their own, since every other event type is a small,\n // fixed set while outbound destinations are open-ended.\n const outboundCounts = new Map();\n for (const r of rows) {\n if (r.event !== \"outbound_click\") continue;\n outboundCounts.set(r.event_label, (outboundCounts.get(r.event_label) ?? 0) + 1);\n }\n const outboundRows = Array.from(outboundCounts, ([label, n]) => ({ label, n }))\n .sort((a, b) => b.n - a.n);\n\n const filterNote = [\n source && `source: ${SOURCE_OPTIONS.find(([v]) => v === source)?.[1] ?? source}`,\n device && `device: ${DEVICE_OPTIONS.find(([v]) => v === device)?.[1] ?? device}`,\n eventName && `event: ${eventName}`,\n compare && \"comparing to previous period\",\n ].filter(Boolean).join(\" · \");\n\n return c.html(page(`\n ${navTabs(\"events\", site, keyQs)}\n ${site ? `

← All sites

` : \"\"}\n

Events${site ? ` — ${esc(siteLabel(site))}` : \" — all sites\"}

${\n site ? esc(site) : \"Custom events across the lead-gen network\"\n } · last ${days} days${filterNote ? ` · ${esc(filterNote)}` : \"\"}

\n\n ${\n filterBar(keyQs, site, days, source, device, compare, {\n basePath: \"/events\",\n eventName,\n eventOptions,\n })\n }\n\n

${totalEvents}

events, ${days}d

${\n compare ? deltaBadge(totalEvents, prevTotalEvents) : \"\"\n }

${avgPerDay}

avg / day

${\n compare ? deltaBadge(avgPerDay, prevAvgPerDay) : \"\"\n }

${distinctTypes}

event types

${\n compare ? deltaBadge(distinctTypes, prevDistinctTypes) : \"\"\n }

\n\n

Events by day${compare ? \" — vs previous period\" : eventName ? \"\" : \", by event\"}

${chartHtml}

\n\n

Event breakdown (${days} days)

${\n eventRows(breakdownRows) ||\n ''\n }

Event	Label	Count
No custom events tracked yet — call window.tribecaTrack(name, label) from a page

\n\n

Outbound link clicks (${days} days)

${\n outboundRows.map((r) =>\n ``\n ).join(\"\") || ''\n }

Destination	Clicks
${esc(r.label)}	${r.n}
No outbound clicks yet

\n\n ${\n site\n ? \"\"\n : `

Events by site (${days} days)

${\n eventsBySiteRows(bySiteRows) || ''\n }

Site	Count
No data yet

`\n }\n `, false));\n});\n\napp.get(\"/dashboard\", async (c) => {\n const key = c.req.query(\"key\");\n const wantKey = Deno.env.get(\"ANALYTICS_DASH_KEY\");\n if (wantKey && key !== wantKey) {\n return c.text(\"Forbidden — add ?key=... to the URL.\", 403);\n }\n\n const site = c.req.query(\"site\") || \"\";\n const keyQs = esc(key || \"\");\n\n if (site) {\n const f30: ReportFilters = { sinceSql: \"-30 day\", site };\n const [d7, d30, top30, recent, live, locations, sources, devices, campaigns, events, nvr] =\n await Promise.all([\n siteTotals(\"-7 day\").then((r) => r.find((x) => x.site === site)?.views ?? 0),\n siteTotals(\"-30 day\").then((r) => r.find((x) => x.site === site)?.views ?? 0),\n topPaths(f30, 15),\n recentViews(40, site),\n activeVisits(30, 100, site),\n topLocations(f30),\n trafficSources(f30),\n deviceBreakdown(f30),\n topCampaigns(f30),\n eventBreakdown(f30),\n newVsReturning(f30),\n ]);\n\n const topRows = top30.map((p) =>\n `${esc(p.path)}${p.views}`\n ).join(\"\");\n\n const recentRows = recent.map((r) => {\n const place = [r.city, r.region, r.country].filter(Boolean).join(\", \") || \"—\";\n return `${esc(r.path)}${flag(r.country_code)} ${esc(place)}${\n esc(r.referrer || \"—\")\n }${formatEastern(r.created_at)}`;\n }).join(\"\");\n\n return c.html(page(`\n ${navTabs(\"overview\", site, keyQs)}\n

← All sites

${esc(siteLabel(site))}

${esc(site)}

${d7}

pageviews, 7 days

${d30}

pageviews, 30 days

${live.length}

active, last 30 min

${nvr.newVisitors}

new visitors, 30d

${nvr.returningVisitors}

returning, 30d

\n\n

Live — last 30 minutes

${await buildMapSvg(live)}

${\n liveRows(live, false) || ''\n }

Path	Location	Referrer	When
No active visitors right now

\n\n

Top locations (30 days)

${\n locationRows(locations) || ''\n }

Place	Views
No data yet

\n\n

Traffic sources (30 days)

${\n trafficRows(sources) || ''\n }

Source	Views
No data yet

\n\n

Devices & browsers (30 days)

${\n deviceRows(devices) || ''\n }

Device	Browser	OS	Views
No data yet

\n\n

Campaigns (30 days)

${\n campaignRows(campaigns) ||\n ''\n }

Campaign	Source	Medium	Views
No UTM-tagged traffic yet

\n\n

Custom events (30 days)

${\n eventRows(events) ||\n ''\n }

Event	Label	Count
No custom events tracked yet — call window.tribecaTrack(name, label) from the page

\n\n

Top pages (30 days)

\n ${\n topRows || ''\n }

Path	Views
No data yet

Recent pageviews

\n ${\n recentRows || ''\n }

Path	Location	Referrer	Time (ET)
No data yet

\n `, true));\n }\n\n const f30: ReportFilters = { sinceSql: \"-30 day\" };\n const [today, last7, last30, recentRaw, liveRaw, locations, sources, devices, campaigns, events, nvr] =\n await Promise.all([\n siteTotals(\"-1 day\"),\n siteTotals(\"-7 day\"),\n siteTotals(\"-30 day\"),\n recentViews(40),\n activeVisits(30, 200),\n topLocations(f30),\n trafficSources(f30),\n deviceBreakdown(f30),\n topCampaigns(f30),\n eventBreakdown(f30),\n newVsReturning(f30),\n ]);\n const recent = recentRaw.filter((r) => !isDevHost(r.site));\n const live = liveRaw.filter((r) => !isDevHost(r.site));\n\n const bySite = new Map();\n for (const r of last30) if (!isDevHost(r.site)) bySite.set(r.site, { today: 0, d7: 0, d30: r.views });\n for (const r of last7) {\n if (isDevHost(r.site)) continue;\n const e = bySite.get(r.site) || { today: 0, d7: 0, d30: 0 };\n e.d7 = r.views;\n bySite.set(r.site, e);\n }\n for (const r of today) {\n if (isDevHost(r.site)) continue;\n const e = bySite.get(r.site) || { today: 0, d7: 0, d30: 0 };\n e.today = r.views;\n bySite.set(r.site, e);\n }\n const rows = Array.from(bySite.entries()).sort((a, b) => b[1].d30 - a[1].d30);\n\n const siteCards = rows.map(([s, v]) =>\n `\n

\n `\n ).join(\"\");\n\n const recentRows = recent.map((r) => {\n const place = [r.city, r.region, r.country].filter(Boolean).join(\", \") || \"—\";\n return `${esc(siteLabel(r.site))}${esc(r.path)}${\n flag(r.country_code)\n } ${esc(place)}${esc(r.referrer || \"—\")}${formatEastern(r.created_at)}`;\n }).join(\"\");\n\n return c.html(page(`\n ${navTabs(\"overview\", site, keyQs)}\n

Lead-gen network — visitor tracking

Self-hosted pageview tracker (1x1 pixel beacon) with approximate, IP-derived location. Click a site for its own page-level breakdown.

${\n siteCards ||\n '

No pageviews recorded yet — add the tracking snippet to a site and visit a page.

'\n }

\n\n

Live — active in the last 30 minutes (${live.length})

This page refreshes every 15 seconds. Location is city-level at best, derived from the visitor's IP address — not GPS, not exact.

${await buildMapSvg(live)}

${\n liveRows(live) || ''\n }

Site	Path	Location	Referrer	When
No active visitors right now

\n\n

Top locations (30 days, all sites)

${\n locationRows(locations) || ''\n }

Place	Views
No data yet

\n\n

New vs. returning visitors (30 days, all sites)

${nvr.newVisitors}

new

${nvr.returningVisitors}

returning

Only counts visits carrying a visitor ID — rows recorded before this feature existed aren't included.

\n\n

Traffic sources (30 days, all sites)

${\n trafficRows(sources) || ''\n }

Source	Views
No data yet

\n\n

Devices & browsers (30 days, all sites)

${\n deviceRows(devices) || ''\n }

Device	Browser	OS	Views
No data yet

\n\n

Campaigns (30 days, all sites)

${\n campaignRows(campaigns) || ''\n }

Campaign	Source	Medium	Views
No UTM-tagged traffic yet

\n\n

Custom events (30 days, all sites)

${\n eventRows(events) ||\n ''\n }

Event	Label	Count
No custom events tracked yet — call window.tribecaTrack(name, label) from any page

\n\n

Recent pageviews, all sites

\n ${\n recentRows || ''\n }

Site	Path	Location	Referrer	Time (ET)
No data yet

\n `, true));\n});\n\nfunction page(body: string, liveRefresh = false) {\n return `\n\n${liveRefresh ? '' : \"\"}\nSite Analytics\n\n${body}\n

\n\n`;\n}\n\nexport default app.fetch;\n","import { Hono } from \"npm:hono@4\";\nimport type { Context } from \"npm:hono@4\";\nimport {\n activeVisits,\n backfillHistoricalRows,\n dailyPageviewTimes,\n dailySeriesRaw,\n deviceBreakdown,\n eventBreakdown,\n eventTimes,\n getCachedRegion,\n type Geo,\n newVsReturning,\n type Pageview,\n recentViews,\n recordEvent,\n regionBreakdown,\n type ReportFilters,\n setCachedRegion,\n siteTotals,\n topCampaigns,\n topLocations,\n topPaths,\n trafficSources,\n} from \"./db.ts\";\nimport {\n classifyTrafficSource,\n easternDayKey,\n formatDayLabel,\n formatEastern,\n parseSqliteUtc,\n parseUA,\n} from \"./util.ts\";\nconst app = new Hono();\n\n// 1x1 transparent GIF, served as the tracking beacon.\nconst PIXEL = Uint8Array.from(\n atob(\"R0lGODlhAQABAIAAAAAAAP///ywAAAAAAQABAAACAUwAOw==\"),\n (c) => c.charCodeAt(0),\n);\n\nconst SITE_LABELS: Record = {\n \"mississippivehicleregistrationrenewal.com\": \"MS Vehicle Registration\",\n \"fileunemploymentflorida.com\": \"FL Unemployment Benefits\",\n \"notarypublicsouthdakota.com\": \"SD Notary Public\",\n \"delawareevtaxcredit.com\": \"DE EV & Solar Incentives\",\n \"homesteadexemptioninflorida.com\": \"FL Homestead Exemption\",\n \"filefloridasalestax.com\": \"FL Sales Tax\",\n \"filenewyorksalestax.com\": \"NY Sales Tax\",\n \"jerseywaterdeliverypros.com\": \"Jersey Water Delivery Pros\",\n \"manhattanwaterdeliverypros.com\": \"Manhattan Water Delivery Pros\",\n \"miamiwaterdeliverypros.com\": \"Miami Water Delivery Pros\",\n};\n\nfunction siteLabel(site: string) {\n return SITE_LABELS[site] || site;\n}\n\n// The US state each site is actually targeting (ipapi.co's `region` field\n// format — full name, e.g. \"Florida\" — since that's what we compare it\n// against). Drives the per-site \"Geo match\" card: is this site's traffic\n// coming from the state it's about, or is it noise (bots, unrelated\n// nationwide SEO trawling, curious out-of-state visitors)? A site missing\n// from this map just doesn't get a Geo match card — every current site has\n// an entry.\nconst SITE_EXPECTED_STATE: Record = {\n \"mississippivehicleregistrationrenewal.com\": \"Mississippi\",\n \"fileunemploymentflorida.com\": \"Florida\",\n \"notarypublicsouthdakota.com\": \"South Dakota\",\n \"delawareevtaxcredit.com\": \"Delaware\",\n \"homesteadexemptioninflorida.com\": \"Florida\",\n \"filefloridasalestax.com\": \"Florida\",\n \"filenewyorksalestax.com\": \"New York\",\n \"jerseywaterdeliverypros.com\": \"New Jersey\",\n \"manhattanwaterdeliverypros.com\": \"New York\",\n \"miamiwaterdeliverypros.com\": \"Florida\",\n};\n\n// Cloudflare Pages serves every project at its own .pages.dev\n// subdomain in addition to the custom domain — real hits land there from\n// crawlers/previews, but they're noise the dashboard doesn't need to surface.\nfunction isDevHost(site: string) {\n return site.endsWith(\".pages.dev\");\n}\n\n// Fail CLOSED: every protected route (dashboard/reports/events/backfill)\n// renders visitor-level rows (IP-derived city/lat/lon, visitor id, UA), so a\n// missing/deleted ANALYTICS_DASH_KEY must never mean \"public by accident\".\n// Previously `if (wantKey && key !== wantKey)` let an unset key skip the\n// check entirely and serve to anyone.\nfunction requireKey(c: Context): Response | null {\n const wantKey = Deno.env.get(\"ANALYTICS_DASH_KEY\");\n if (!wantKey) {\n return c.text(\"Service unavailable — no dashboard key is configured.\", 503);\n }\n if (c.req.query(\"key\") !== wantKey) {\n return c.text(\"Forbidden — add ?key=... to the URL.\", 403);\n }\n return null;\n}\n\n// Site allowlist derived from the sites we actually know about, plus each\n// one's Cloudflare Pages preview twin (every Pages project also serves at\n// .pages.dev). The `s` param is otherwise fully attacker-controlled\n// — a browser tracker's key is public by definition, there's no way around\n// that — so this plus the Referer cross-check below is what actually stops\n// someone forging hits for an arbitrary or nonexistent site.\nconst PROD_SITES = new Set(Object.keys(SITE_LABELS));\nconst ALLOWED_SITES = new Set();\nfor (const s of PROD_SITES) {\n ALLOWED_SITES.add(s);\n ALLOWED_SITES.add(s.replace(/\\.com$/, \".pages.dev\"));\n}\n\n// A production domain and its .pages.dev preview are the same logical site.\nfunction siteBase(site: string): string {\n return site.replace(/\\.pages\\.dev$/, \"\").replace(/\\.com$/, \"\");\n}\n\n// Reject an `s` outside the allowlist, always. When a Referer is present,\n// also reject if its host doesn't correspond to the reported site — that\n// cross-check is the part that actually does the work; the allowlist alone\n// only proves \"a real site\", not \"this hit came from it\". No Referer\n// (stripped by a privacy browser/extension, or a strict referrer-policy) is\n// NOT itself grounds for rejection: dropping those would silently lose real\n// visitors, which is worse than recording an unverified-but-allowlisted hit.\n//\n// IMPORTANT: this is not a forgery control, just a noise/laziness filter.\n// Omitting the Referer header entirely (trivial for anything other than a\n// real browser — a bare `curl` has no Referer) passes straight through, by\n// design, for the reason above. The actual control against sustained or\n// deliberate forgery is per-IP rate limiting plus ingest-rate visibility —\n// not built yet (see item 17 in the site's own security review), and it\n// matters more than this check does for anyone who actually wants to\n// forge data at scale.\nfunction isAllowedHit(site: string, referrer: string): boolean {\n if (!ALLOWED_SITES.has(site)) return false;\n if (!referrer) return true;\n try {\n const refHost = new URL(referrer).hostname.replace(/^www\\./, \"\");\n return siteBase(refHost) === siteBase(site);\n } catch {\n return true;\n }\n}\n\nfunction esc(s: string) {\n return s.replace(/[&<>\"']/g, (c) =>\n ({ \"&\": \"&\", \"<\": \"<\", \">\": \">\", '\"': \""\", \"'\": \"'\" }[c] as string)\n );\n}\n\n// Country code -> flag emoji, via regional indicator symbols. Cosmetic only.\nfunction flag(cc: string | null) {\n if (!cc || cc.length !== 2) return \"\";\n const codePoints = [...cc.toUpperCase()].map((c) => 0x1f1e6 + (c.charCodeAt(0) - 65));\n return String.fromCodePoint(...codePoints);\n}\n\nfunction timeAgo(iso: string) {\n const then = new Date(iso.replace(\" \", \"T\") + \"Z\").getTime();\n const secs = Math.max(0, Math.round((Date.now() - then) / 1000));\n if (secs < 60) return `${secs}s ago`;\n if (secs < 3600) return `${Math.floor(secs / 60)}m ago`;\n return `${Math.floor(secs / 3600)}h ago`;\n}\n\nfunction isPrivateIp(ip: string) {\n return /^(10\\.|127\\.|192\\.168\\.|172\\.(1[6-9]|2\\d|3[01])\\.|::1$|fc|fd|0\\.0\\.0\\.0)/.test(ip);\n}\n\n// Country name from a 2-letter code, via the JS runtime's own locale data —\n// no lookup table to maintain, no external call.\nconst REGION_DISPLAY_NAMES = new Intl.DisplayNames([\"en\"], { type: \"region\" });\nfunction countryName(code: string | null): string | null {\n if (!code) return null;\n try {\n return REGION_DISPLAY_NAMES.of(code) ?? code;\n } catch {\n return code;\n }\n}\n\n// Cache key for the region lookup below: a /24 for IPv4 (drop the last\n// octet), a /64 for IPv6 (first four hextets) — coarse enough that a shared\n// network's visitors share one cache entry, which is what actually keeps\n// the call volume down. Falls back to the address itself if it doesn't\n// parse as either shape (safe — just less cache-effective, not incorrect).\nfunction ipPrefix(ip: string): string {\n const v4 = ip.match(/^(\\d{1,3}\\.\\d{1,3}\\.\\d{1,3})\\.\\d{1,3}$/);\n if (v4) return `v4:${v4[1]}`;\n const hextets = ip.split(\":\");\n if (hextets.length >= 4) return `v6:${hextets.slice(0, 4).join(\":\")}`;\n return `raw:${ip}`;\n}\n\nconst GEO_CACHE_TTL_MS = 30 * 24 * 60 * 60 * 1000; // 30 days\n\n// Geo, in two tiers. Country comes from Cloudflare's own `cf-ipcountry`\n// header — free, no call, no visitor IP ever leaves this val for it. State\n// (region) is the one dimension this network actually needs (it's a\n// state-specific site network), and ipapi.co is the only source for it —\n// but it's only called for US traffic (the state question doesn't apply\n// elsewhere) and only on a cache miss, so a shared /24 or /64 pays for the\n// lookup once, not once per visitor. No cf-ipcountry at all (a request that\n// somehow didn't come through the expected edge) means no external call\n// either way — there's nothing to look up region *for* without knowing the\n// country first.\nasync function lookupGeo(ip: string, countryCode: string | null): Promise {\n if (!countryCode) return null;\n const country = countryName(countryCode);\n if (countryCode !== \"US\" || !ip || isPrivateIp(ip)) {\n return { country, countryCode, region: null };\n }\n\n const prefix = ipPrefix(ip);\n try {\n const cached = await getCachedRegion(prefix);\n if (cached && Date.now() - parseSqliteUtc(cached.fetchedAt).getTime() < GEO_CACHE_TTL_MS) {\n return { country, countryCode, region: cached.region };\n }\n } catch (err) {\n console.error(\"geo cache read failed:\", err);\n }\n\n let region: string | null = null;\n try {\n const res = await fetch(`https://ipapi.co/${ip}/json/`, {\n signal: AbortSignal.timeout(2500),\n headers: { \"User-Agent\": \"tribeca-site-analytics/1.0\" },\n });\n if (res.ok) {\n const data = await res.json();\n if (data && !data.error) region = data.region ?? null;\n }\n } catch {\n // best-effort; region stays null, still cached below so a flaky\n // lookup doesn't get retried on every hit from the same /24.\n }\n try {\n await setCachedRegion(prefix, region);\n } catch (err) {\n console.error(\"geo cache write failed:\", err);\n }\n\n return { country, countryCode, region };\n}\n\n// The tracking beacon. A 1x1 GIF via , not fetch() — sidesteps CORS entirely,\n// works even on a strict _headers file, and degrades silently if blocked.\napp.get(\"/px.gif\", async (c) => {\n const q = (key: string, max = 300) => (c.req.query(key) || \"\").slice(0, max);\n const site = q(\"s\", 200).toLowerCase().replace(/^www\\./, \"\");\n const path = q(\"p\") || \"/\";\n const ref = q(\"r\");\n const ua = (c.req.header(\"user-agent\") || \"\").slice(0, 300);\n const ip = c.req.header(\"cf-connecting-ip\") ||\n (c.req.header(\"x-forwarded-for\") || \"\").split(\",\")[0].trim();\n const countryCode = c.req.header(\"cf-ipcountry\") || null;\n\n // GA4-style flexible signals. All optional — the beacon still works (as a\n // plain pageview) if a site's snippet is the older, simpler version.\n const utmSource = q(\"utm_source\") || null;\n const utmMedium = q(\"utm_medium\") || null;\n const utmCampaign = q(\"utm_campaign\") || null;\n const utmTerm = q(\"utm_term\") || null;\n const utmContent = q(\"utm_content\") || null;\n const viewport = q(\"vp\", 20) || null;\n const visitorId = q(\"vid\", 64) || null;\n // \"ev\" lets a site call window.tribecaTrack('cta_click', 'quote button')\n // for its own custom events, reusing this same pixel — no new endpoint,\n // no schema change needed for whatever a site wants to track next.\n const eventName = q(\"ev\", 60) || \"pageview\";\n const eventLabel = q(\"el\", 200) || null;\n\n if (site && isAllowedHit(site, ref)) {\n try {\n const geo = await lookupGeo(ip, countryCode);\n const { deviceType, browser, os } = parseUA(ua);\n const trafficSource = classifyTrafficSource(ref, utmMedium, utmSource);\n await recordEvent({\n site,\n path,\n referrer: ref,\n ua,\n geo,\n utmSource,\n utmMedium,\n utmCampaign,\n utmTerm,\n utmContent,\n deviceType,\n browser,\n os,\n viewport,\n visitorId,\n trafficSource,\n event: eventName,\n eventLabel,\n });\n } catch (err) {\n console.error(\"pixel record failed:\", err);\n }\n }\n\n c.header(\"Content-Type\", \"image/gif\");\n c.header(\"Cache-Control\", \"no-store, no-cache, must-revalidate, private\");\n c.header(\"Access-Control-Allow-Origin\", \"*\");\n return c.body(PIXEL);\n});\n\n// One-time (repeatable, idempotent) backfill trigger for rows recorded\n// before device/browser/traffic-source existed. Same key as the dashboard.\napp.get(\"/admin/backfill\", async (c) => {\n const denied = requireKey(c);\n if (denied) return denied;\n const all = c.req.query(\"all\") === \"1\";\n const updated = await backfillHistoricalRows(all);\n return c.json({ updated, note: updated === 2000 ? \"hit the per-call cap — call again\" : \"done\" });\n});\n\napp.get(\"/\", (c) => c.redirect(\"/dashboard\"));\n\n// Percent-and-count breakdown of a site's traffic against the state it's\n// actually targeting (see SITE_EXPECTED_STATE) — matched / other US /\n// non-US / unknown (no geo signal at all). Replaces the old dot-map: a\n// network of state-specific sites needs \"is this traffic from the right\n// state\", not a picture of dots on a continent.\nfunction stateMatchStats(rows: { country_code: string; region: string; n: number }[], expectedState: string) {\n let matched = 0, otherUs = 0, nonUs = 0, unknown = 0, total = 0;\n for (const r of rows) {\n total += r.n;\n if (!r.country_code) unknown += r.n;\n else if (r.country_code !== \"US\") nonUs += r.n;\n else if (r.region === expectedState) matched += r.n;\n else otherUs += r.n;\n }\n const pct = (n: number) => (total === 0 ? 0 : Math.round((n / total) * 100));\n return {\n total,\n matched,\n otherUs,\n nonUs,\n unknown,\n matchedPct: pct(matched),\n otherUsPct: pct(otherUs),\n nonUsPct: pct(nonUs),\n unknownPct: pct(unknown),\n };\n}\n\nfunction locationRows(rows: { place: string; country_code: string | null; views: number }[]) {\n return rows.map((r) =>\n `${flag(r.country_code)} ${esc(r.place)}${r.views}`\n ).join(\"\");\n}\n\nfunction trafficRows(rows: { source: string; views: number }[]) {\n return rows.map((r) => `${esc(r.source)}${r.views}`).join(\n \"\",\n );\n}\n\nfunction deviceRows(rows: { device_type: string; browser: string; os: string; views: number }[]) {\n return rows.map((r) =>\n `${esc(r.device_type)}${esc(r.browser)}${esc(r.os)}${r.views}`\n ).join(\"\");\n}\n\nfunction campaignRows(\n rows: { utm_source: string | null; utm_medium: string | null; utm_campaign: string | null; views: number }[],\n) {\n return rows.map((r) =>\n `${esc(r.utm_campaign || \"—\")}${esc(r.utm_source || \"—\")}${\n esc(r.utm_medium || \"—\")\n }${r.views}`\n ).join(\"\");\n}\n\nfunction eventRows(rows: { event: string; event_label: string; n: number }[]) {\n return rows.map((r) =>\n `${esc(r.event)}${esc(r.event_label || \"—\")}${r.n}`\n ).join(\"\");\n}\n\nfunction liveRows(rows: Pageview[], showSite = true) {\n return rows.map((r) => {\n const place = [r.region, r.country_code ? r.country_code : r.country].filter(Boolean)\n .join(\", \") || \"Unknown\";\n return `\n ${showSite ? `${esc(siteLabel(r.site))}` : \"\"}\n ${esc(r.path)}\n ${flag(r.country_code)} ${esc(place)}\n ${esc(r.referrer || \"—\")}\n ${timeAgo(r.created_at)}\n `;\n }).join(\"\");\n}\n\nfunction navTabs(active: \"overview\" | \"reports\" | \"events\", site: string, keyQs: string) {\n const siteQs = site ? `&site=${encodeURIComponent(site)}` : \"\";\n const tab = (href: string, label: string, isActive: boolean) =>\n `${label}`;\n return `

${\n series.map((s) =>\n `${esc(s.label)}`\n ).join(\"\")\n }

\n This period\n Previous period\n

`;\n\n return svg + legend;\n}\n\nconst REPORT_RANGES = [7, 14, 30, 90];\nconst SOURCE_OPTIONS = [\n [\"\", \"All sources\"],\n [\"Direct\", \"Direct\"],\n [\"Organic Search\", \"Organic Search\"],\n [\"Paid\", \"Paid\"],\n [\"Social\", \"Social\"],\n [\"Social (campaign)\", \"Social (campaign)\"],\n [\"Email\", \"Email\"],\n [\"Referral\", \"Referral (any)\"],\n [\"Campaign\", \"Campaign (any)\"],\n];\nconst DEVICE_OPTIONS = [\n [\"\", \"All devices\"],\n [\"desktop\", \"Desktop\"],\n [\"mobile\", \"Mobile\"],\n [\"tablet\", \"Tablet\"],\n];\n\n// A GET form (no client JS framework needed) so every filter combination is\n// a plain, bookmarkable/shareable URL — ?days=&source=&device= alongside the\n// existing ?site=&key=. Each \n ${site ? `` : \"\"}\n Range\n Traffic source\n Device\n ${\n eventOptions\n ? `Event`\n : \"\"\n }\n Compare to previous period\n ${changed ? `Clear filters` : \"\"}\n `;\n}\n\napp.get(\"/reports\", async (c) => {\n const denied = requireKey(c);\n if (denied) return denied;\n const key = c.req.query(\"key\");\n\n const site = c.req.query(\"site\") || \"\";\n const keyQs = esc(key || \"\");\n const daysParam = parseInt(c.req.query(\"days\") || \"30\", 10);\n const days = REPORT_RANGES.includes(daysParam) ? daysParam : 30;\n const source = c.req.query(\"source\") || \"\";\n const device = c.req.query(\"device\") || \"\";\n const compare = c.req.query(\"compare\") === \"1\";\n const sinceSql = `-${days} day`;\n\n // `base` omits source/device so the two breakdown tables that populate\n // those filter dropdowns (traffic sources, devices) always show the full,\n // unfiltered picture — otherwise picking \"Mobile\" would collapse the\n // device table to a single row with nothing else to switch to.\n const base: ReportFilters = { sinceSql, site: site || undefined };\n const applied: ReportFilters = {\n sinceSql,\n site: site || undefined,\n trafficSource: source || undefined,\n deviceType: device || undefined,\n };\n // The N days immediately before the current period, same filters — GA4's\n // default \"preceding period\" comparison.\n const previous: ReportFilters = {\n ...applied,\n sinceSql: `-${days * 2} day`,\n untilSql: `-${days} day`,\n };\n\n const [\n dailyTimes,\n dailyBySiteRaw,\n sources,\n locations,\n devices,\n campaigns,\n events,\n nvr,\n top30,\n prevDailyTimes,\n prevNvr,\n ] = await Promise.all([\n dailyPageviewTimes(applied),\n site ? Promise.resolve([]) : dailySeriesRaw(applied),\n trafficSources(base),\n topLocations(applied),\n deviceBreakdown(base),\n topCampaigns(applied),\n eventBreakdown(applied),\n newVsReturning(applied),\n site ? topPaths(applied, 15) : Promise.resolve([]),\n compare ? dailyPageviewTimes(previous) : Promise.resolve([]),\n compare ? newVsReturning(previous) : Promise.resolve({ newVisitors: 0, returningVisitors: 0 }),\n ]);\n\n // Bucket into Eastern-time calendar days here in JS (Intl handles the\n // EST/EDT switch) rather than in SQL, which only ever knows UTC.\n const bucketByDay = (times: string[]) => {\n const counts = new Map();\n for (const t of times) {\n const k = easternDayKey(parseSqliteUtc(t));\n counts.set(k, (counts.get(k) ?? 0) + 1);\n }\n return Array.from(counts, ([day, views]) => ({ day, views }));\n };\n\n const daily = bucketByDay(dailyTimes);\n const totalViews = dailyTimes.length;\n const avgPerDay = Math.round(totalViews / days);\n const prevTotalViews = prevDailyTimes.length;\n const prevAvgPerDay = Math.round(prevTotalViews / days);\n const topPagesRows = top30.map((p) =>\n `${esc(p.path)}${p.views}`\n ).join(\"\");\n\n // Comparison mode swaps the by-site breakdown for a single this-vs-previous\n // overlay — a per-site x per-period grid stops being readable, so pick one.\n let chartHtml: string;\n if (compare) {\n chartHtml = buildComparisonChart(daily, bucketByDay(prevDailyTimes), days);\n } else if (site) {\n chartHtml = buildLineChart([{ label: siteLabel(site), color: CHART_COLORS[0], points: daily }], days);\n } else {\n const bySite = new Map();\n for (const r of dailyBySiteRaw) {\n if (isDevHost(r.site)) continue;\n if (!bySite.has(r.site)) bySite.set(r.site, []);\n bySite.get(r.site)!.push(r.created_at);\n }\n const ranked = Array.from(bySite.entries())\n .map(([s, times]) => [s, times.length] as const)\n .sort((a, b) => b[1] - a[1]);\n const chartSeries = ranked.map(([s], i) => ({\n label: siteLabel(s),\n color: CHART_COLORS[i % CHART_COLORS.length],\n points: bucketByDay(bySite.get(s)!),\n }));\n chartHtml = buildLineChart(chartSeries, days);\n }\n\n const filterNote = [\n source && `source: ${SOURCE_OPTIONS.find(([v]) => v === source)?.[1] ?? source}`,\n device && `device: ${DEVICE_OPTIONS.find(([v]) => v === device)?.[1] ?? device}`,\n compare && \"comparing to previous period\",\n ].filter(Boolean).join(\" · \");\n\n return c.html(page(`\n ${navTabs(\"reports\", site, keyQs)}\n ${site ? `

← All sites

` : \"\"}\n

Reports${site ? ` — ${esc(siteLabel(site))}` : \" — all sites\"}

${site ? esc(site) : \"Historical trends across the lead-gen network\"} · last ${days} days${\n filterNote ? ` · ${esc(filterNote)}` : \"\"\n }

\n\n ${filterBar(keyQs, site, days, source, device, compare)}\n\n

${totalViews}

pageviews, ${days}d

${\n compare ? deltaBadge(totalViews, prevTotalViews) : \"\"\n }

${avgPerDay}

avg / day

${\n compare ? deltaBadge(avgPerDay, prevAvgPerDay) : \"\"\n }

${nvr.newVisitors}

new visitors

${\n compare ? deltaBadge(nvr.newVisitors, prevNvr.newVisitors) : \"\"\n }

${nvr.returningVisitors}

returning visitors

${\n compare ? deltaBadge(nvr.returningVisitors, prevNvr.returningVisitors) : \"\"\n }

\n\n

Pageviews by day${compare ? \" — vs previous period\" : site ? \"\" : \", by site\"}

${chartHtml}

\n\n

Traffic sources (${days} days)

${\n trafficRows(sources) || ''\n }

Source	Views
No data yet

\n\n

Top locations (${days} days)

${\n locationRows(locations) || ''\n }

Place	Views
No data yet

\n\n

Devices & browsers (${days} days)

${\n deviceRows(devices) || ''\n }

Device	Browser	OS	Views
No data yet

\n\n

Campaigns (${days} days)

${\n campaignRows(campaigns) || ''\n }

Campaign	Source	Medium	Views
No UTM-tagged traffic yet

\n\n ${\n site\n ? `

Top pages (${days} days)

\n ${\n topPagesRows || ''\n }

Path	Views
No data yet

`\n : \"\"\n }\n\n

Custom events (${days} days)

${\n eventRows(events) ||\n ''\n }

Event	Label	Count
No custom events tracked yet — call window.tribecaTrack(name, label) from a page

\n `, false));\n});\n\nfunction eventsBySiteRows(rows: { site: string; n: number }[]) {\n return rows.map((r) =>\n `${esc(siteLabel(r.site))}${r.n}`\n ).join(\"\");\n}\n\napp.get(\"/events\", async (c) => {\n const denied = requireKey(c);\n if (denied) return denied;\n const key = c.req.query(\"key\");\n\n const site = c.req.query(\"site\") || \"\";\n const keyQs = esc(key || \"\");\n const daysParam = parseInt(c.req.query(\"days\") || \"30\", 10);\n const days = REPORT_RANGES.includes(daysParam) ? daysParam : 30;\n const source = c.req.query(\"source\") || \"\";\n const device = c.req.query(\"device\") || \"\";\n const eventName = c.req.query(\"event\") || \"\";\n const compare = c.req.query(\"compare\") === \"1\";\n const sinceSql = `-${days} day`;\n\n // `base` (no source/device/event) keeps the filter dropdowns — traffic\n // source, device, and the event-name list itself — showing every option\n // that exists in the window, the same reasoning as the Reports tab.\n const base: ReportFilters = { sinceSql, site: site || undefined };\n const applied: ReportFilters = {\n sinceSql,\n site: site || undefined,\n trafficSource: source || undefined,\n deviceType: device || undefined,\n };\n const previous: ReportFilters = { ...applied, sinceSql: `-${days * 2} day`, untilSql: `-${days} day` };\n\n const [rowsRaw, allEvents, prevRowsRaw] = await Promise.all([\n eventTimes(applied),\n eventBreakdown(base, 50),\n compare ? eventTimes(previous) : Promise.resolve([]),\n ]);\n const rows = rowsRaw.filter((r) => !isDevHost(r.site) && (!eventName || r.event === eventName));\n const prevRows = prevRowsRaw.filter((r) => !isDevHost(r.site) && (!eventName || r.event === eventName));\n\n const eventOptions = Array.from(new Set(allEvents.map((r) => r.event))).sort();\n\n const bucketByDay = (times: { created_at: string }[]) => {\n const counts = new Map();\n for (const t of times) {\n const k = easternDayKey(parseSqliteUtc(t.created_at));\n counts.set(k, (counts.get(k) ?? 0) + 1);\n }\n return Array.from(counts, ([day, views]) => ({ day, views }));\n };\n\n const totalEvents = rows.length;\n const avgPerDay = Math.round(totalEvents / days);\n const distinctTypes = new Set(rows.map((r) => r.event)).size;\n const prevTotalEvents = prevRows.length;\n const prevAvgPerDay = Math.round(prevTotalEvents / days);\n const prevDistinctTypes = new Set(prevRows.map((r) => r.event)).size;\n\n let chartHtml: string;\n if (compare) {\n chartHtml = buildComparisonChart(bucketByDay(rows), bucketByDay(prevRows), days);\n } else {\n const byEvent = new Map();\n for (const r of rows) {\n if (!byEvent.has(r.event)) byEvent.set(r.event, []);\n byEvent.get(r.event)!.push(r);\n }\n const ranked = Array.from(byEvent.entries())\n .map(([e, times]) => [e, times.length] as const)\n .sort((a, b) => b[1] - a[1]);\n const chartSeries = ranked.map(([e], i) => ({\n label: e,\n color: CHART_COLORS[i % CHART_COLORS.length],\n points: bucketByDay(byEvent.get(e)!),\n }));\n chartHtml = buildLineChart(chartSeries, days);\n }\n\n const bySite = new Map();\n for (const r of rows) bySite.set(r.site, (bySite.get(r.site) ?? 0) + 1);\n const bySiteRows = Array.from(bySite, ([s, n]) => ({ site: s, n })).sort((a, b) => b.n - a.n);\n\n const breakdownRows = allEvents.filter((r) => !eventName || r.event === eventName);\n\n // Outbound clicks get their own table — the generic event breakdown groups\n // by (event, label) already, but \"outbound_click\" rows are the ones worth\n // seeing ranked on their own, since every other event type is a small,\n // fixed set while outbound destinations are open-ended.\n const outboundCounts = new Map();\n for (const r of rows) {\n if (r.event !== \"outbound_click\") continue;\n outboundCounts.set(r.event_label, (outboundCounts.get(r.event_label) ?? 0) + 1);\n }\n const outboundRows = Array.from(outboundCounts, ([label, n]) => ({ label, n }))\n .sort((a, b) => b.n - a.n);\n\n const filterNote = [\n source && `source: ${SOURCE_OPTIONS.find(([v]) => v === source)?.[1] ?? source}`,\n device && `device: ${DEVICE_OPTIONS.find(([v]) => v === device)?.[1] ?? device}`,\n eventName && `event: ${eventName}`,\n compare && \"comparing to previous period\",\n ].filter(Boolean).join(\" · \");\n\n return c.html(page(`\n ${navTabs(\"events\", site, keyQs)}\n ${site ? `

← All sites

` : \"\"}\n

Events${site ? ` — ${esc(siteLabel(site))}` : \" — all sites\"}

${\n site ? esc(site) : \"Custom events across the lead-gen network\"\n } · last ${days} days${filterNote ? ` · ${esc(filterNote)}` : \"\"}

\n\n ${\n filterBar(keyQs, site, days, source, device, compare, {\n basePath: \"/events\",\n eventName,\n eventOptions,\n })\n }\n\n

${totalEvents}

events, ${days}d

${\n compare ? deltaBadge(totalEvents, prevTotalEvents) : \"\"\n }

${avgPerDay}

avg / day

${\n compare ? deltaBadge(avgPerDay, prevAvgPerDay) : \"\"\n }

${distinctTypes}

event types

${\n compare ? deltaBadge(distinctTypes, prevDistinctTypes) : \"\"\n }

\n\n

Events by day${compare ? \" — vs previous period\" : eventName ? \"\" : \", by event\"}

${chartHtml}

\n\n

Event breakdown (${days} days)

${\n eventRows(breakdownRows) ||\n ''\n }

Event	Label	Count
No custom events tracked yet — call window.tribecaTrack(name, label) from a page

\n\n

Outbound link clicks (${days} days)

${\n outboundRows.map((r) =>\n ``\n ).join(\"\") || ''\n }

Destination	Clicks
${esc(r.label)}	${r.n}
No outbound clicks yet

\n\n ${\n site\n ? \"\"\n : `

Events by site (${days} days)

${\n eventsBySiteRows(bySiteRows) || ''\n }

Site	Count
No data yet

`\n }\n `, false));\n});\n\napp.get(\"/dashboard\", async (c) => {\n const denied = requireKey(c);\n if (denied) return denied;\n const key = c.req.query(\"key\");\n\n const site = c.req.query(\"site\") || \"\";\n const keyQs = esc(key || \"\");\n\n if (site) {\n const f30: ReportFilters = { sinceSql: \"-30 day\", site };\n const expectedState = SITE_EXPECTED_STATE[site];\n const [d7, d30, top30, recent, live, locations, sources, devices, campaigns, events, nvr, geoRows] =\n await Promise.all([\n siteTotals(\"-7 day\").then((r) => r.find((x) => x.site === site)?.views ?? 0),\n siteTotals(\"-30 day\").then((r) => r.find((x) => x.site === site)?.views ?? 0),\n topPaths(f30, 15),\n recentViews(40, site),\n activeVisits(30, 100, site),\n topLocations(f30),\n trafficSources(f30),\n deviceBreakdown(f30),\n topCampaigns(f30),\n eventBreakdown(f30),\n newVsReturning(f30),\n expectedState ? regionBreakdown(f30) : Promise.resolve([]),\n ]);\n\n const topRows = top30.map((p) =>\n `${esc(p.path)}${p.views}`\n ).join(\"\");\n\n const recentRows = recent.map((r) => {\n const place = [r.region, r.country].filter(Boolean).join(\", \") || \"—\";\n return `${esc(r.path)}${flag(r.country_code)} ${esc(place)}${\n esc(r.referrer || \"—\")\n }${formatEastern(r.created_at)}`;\n }).join(\"\");\n\n const geoMatch = expectedState ? stateMatchStats(geoRows, expectedState) : null;\n\n return c.html(page(`\n ${navTabs(\"overview\", site, keyQs)}\n

← All sites

${esc(siteLabel(site))}

${esc(site)}

${d7}

pageviews, 7 days

${d30}

pageviews, 30 days

${live.length}

active, last 30 min

${nvr.newVisitors}

new visitors, 30d

${nvr.returningVisitors}

returning, 30d

\n\n ${\n geoMatch\n ? `

Geo match — is this traffic from ${esc(expectedState)}? (30 days)

Of ${geoMatch.total} pageviews, ${geoMatch.unknownPct}% carry no geo signal at all.

${geoMatch.matchedPct}%

from ${\n esc(expectedState)\n } (${geoMatch.matched})

${geoMatch.otherUsPct}%

other US (${geoMatch.otherUs})

${geoMatch.nonUsPct}%

non-US (${geoMatch.nonUs})

${geoMatch.unknownPct}%

unknown (${geoMatch.unknown})

`\n : \"\"\n }\n\n

Live — last 30 minutes

${\n liveRows(live, false) || ''\n }

Path	Location	Referrer	When
No active visitors right now

\n\n

Top locations (30 days)

${\n locationRows(locations) || ''\n }

Place	Views
No data yet

\n\n

Traffic sources (30 days)

${\n trafficRows(sources) || ''\n }

Source	Views
No data yet

\n\n

Devices & browsers (30 days)

${\n deviceRows(devices) || ''\n }

Device	Browser	OS	Views
No data yet

\n\n

Campaigns (30 days)

${\n campaignRows(campaigns) ||\n ''\n }

Campaign	Source	Medium	Views
No UTM-tagged traffic yet

\n\n

Custom events (30 days)

${\n eventRows(events) ||\n ''\n }

Event	Label	Count
No custom events tracked yet — call window.tribecaTrack(name, label) from the page

\n\n

Top pages (30 days)

\n ${\n topRows || ''\n }

Path	Views
No data yet

Recent pageviews

\n ${\n recentRows || ''\n }

Path	Location	Referrer	Time (ET)
No data yet

\n `\n ).join(\"\");\n\n const recentRows = recent.map((r) => {\n const place = [r.region, r.country].filter(Boolean).join(\", \") || \"—\";\n return `${esc(siteLabel(r.site))}${esc(r.path)}${\n flag(r.country_code)\n } ${esc(place)}${esc(r.referrer || \"—\")}${formatEastern(r.created_at)}`;\n }).join(\"\");\n\n return c.html(page(`\n ${navTabs(\"overview\", site, keyQs)}\n

Lead-gen network — visitor tracking

Self-hosted pageview tracker (1x1 pixel beacon) with approximate, IP-derived location. Click a site for its own page-level breakdown.

${\n siteCards ||\n '

No pageviews recorded yet — add the tracking snippet to a site and visit a page.

'\n }

\n\n

Live — active in the last 30 minutes (${live.length})

This page refreshes every 15 seconds. Location is US-state-level at best (country-only outside the US), derived from the visitor's IP address — not GPS, not exact.

${\n liveRows(live) || ''\n }

Site	Path	Location	Referrer	When
No active visitors right now

\n\n

Top locations (30 days, all sites)

${\n locationRows(locations) || ''\n }

Place	Views
No data yet

\n\n

New vs. returning visitors (30 days, all sites)

${nvr.newVisitors}

new

${nvr.returningVisitors}

returning

Only counts visits carrying a visitor ID — rows recorded before this feature existed aren't included.

\n\n

Traffic sources (30 days, all sites)

${\n trafficRows(sources) || ''\n }

Source	Views
No data yet

\n\n

Devices & browsers (30 days, all sites)

${\n deviceRows(devices) || ''\n }

Device	Browser	OS	Views
No data yet

\n\n

Campaigns (30 days, all sites)

${\n campaignRows(campaigns) || ''\n }

Campaign	Source	Medium	Views
No UTM-tagged traffic yet

\n\n

Custom events (30 days, all sites)

${\n eventRows(events) ||\n ''\n }

Event	Label	Count
No custom events tracked yet — call window.tribecaTrack(name, label) from any page

\n\n

Recent pageviews, all sites

\n ${\n recentRows || ''\n }

Site	Path	Location	Referrer	Time (ET)
No data yet

\n `, true));\n});\n\nfunction page(body: string, liveRefresh = false) {\n return `\n\n${liveRefresh ? '' : \"\"}\nSite Analytics\n\n${body}\n

\n\n`;\n}\n\nexport default app.fetch;\n",["D",1789226792184],"id","784adc29-f9cf-46ef-b3ad-c77fef19f98f","tribeca-site-analytics",["D",1789161858464],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","Pageview tracker + dashboard for the lead-gen site network","imageUrl","c5acd3b3-60a7-4deb-bc6d-528f83012ea9","pinnedFileId","pinPreview",true,"pinMigrated","screenshotVersion","likeCount",0,"orgId","019f8fe2-cdfc-775d-a845-a69af13d39cb","tursoName","784adc29f9cf46efb3adc77fef19f98f","tursoHostname","784adc29f9cf46efb3adc77fef19f98f-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789516253896],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_108":107,"_116":112},"org",{"_108":107,"_109":110,"_111":112,"_18":64,"_113":114,"_115":26},"branch",{"_13":97,"_33":98,"_52":43,"_99":53,"_100":-5,"_101":-5,"_102":-5,"_103":-5,"_104":-5,"_105":54,"_106":107},"isOwned",194,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","Tribeca_Rep","handle","displayName","M A","avatar","https://lh3.googleusercontent.com/a/ACg8ocI9znhXn2hWp1wjjyWzeuOyoO_JrDgJo8aD0umFraR7bAG3UAmc=s96-c","tier","free","canRestrictVals","profileImageUrl","branchName","","adoptOrgId",{"_52":53,"_33":54,"_39":186,"_56":-5,"_57":26,"_58":59,"_60":59,"_61":62,"_63":-5,"_18":64,"_65":47,"_66":67,"_68":-5,"_69":-5,"_70":71,"_72":73,"_74":75,"_76":77,"_78":187,"_80":-5,"_81":82,"_83":82,"_84":85,"_86":85,"_87":85,"_88":82,"_89":82,"_90":188,"_92":189,"_94":190,"_96":26},"tree",[173,174,175],"files",[173,174,175],"counts",{"_161":71,"_162":163,"_164":71,"_165":166,"_167":168},"contents",[139,140,141],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_52":157,"_33":29,"_31":32,"_145":-5,"_42":43,"_41":14,"_147":26,"_39":158,"_13":159,"_34":35,"_36":38,"_25":26,"_151":-5,"_28":160},{"_52":153,"_33":46,"_31":47,"_145":-5,"_42":43,"_41":14,"_147":26,"_39":154,"_13":155,"_34":48,"_36":50,"_25":26,"_151":-5,"_28":156},{"_52":142,"_33":143,"_31":144,"_145":-5,"_42":43,"_41":146,"_147":26,"_39":148,"_13":149,"_34":35,"_36":150,"_25":26,"_151":-5,"_28":152},"7767ced4-eb37-4c1b-9122-6a0e3c401caa","util.ts","f1a3c344-ae2f-11f1-a1c4-1607ee4eb77e","parentId",77,"latest",["D",1789177072653],3,"// Shared, dependency-free parsing helpers used both when recording a live\n// event (main.tsx) and when backfilling historical rows (db.ts) — kept here\n// once so the two never drift apart.\n\nexport function parseUA(ua: string): { deviceType: string; browser: string; os: string } {\n const isTablet = /iPad|Tablet|(Android(?!.*Mobile))/i.test(ua);\n const isMobile = !isTablet && /Mobi|Android|iPhone|iPod/i.test(ua);\n const deviceType = isTablet ? \"tablet\" : isMobile ? \"mobile\" : \"desktop\";\n\n // Checked first, not last: plenty of real crawlers (Googlebot's mobile UA\n // among them) also carry \"Chrome/x.y\" in the string to get treated like a\n // modern browser by sites that sniff for it, so a browser check ahead of\n // the bot check would misfile them as a person on Chrome.\n let browser = \"Other\";\n if (\n /bot|crawler|spider|bingpreview|facebookexternalhit|headlesschrome|phantomjs|puppeteer|playwright|curl\\/|wget\\/|python-requests|python-urllib|go-http-client|libwww-perl|scrapy/i\n .test(ua)\n ) browser = \"Bot\";\n else if (/Edg\\//.test(ua)) browser = \"Edge\";\n else if (/OPR\\//.test(ua) || /Opera/.test(ua)) browser = \"Opera\";\n else if (/Chrome\\//.test(ua) && !/Chromium/.test(ua)) browser = \"Chrome\";\n else if (/Firefox\\//.test(ua)) browser = \"Firefox\";\n else if (/Safari\\//.test(ua) && !/Chrome/.test(ua)) browser = \"Safari\";\n\n // iPhone/iPad UAs contain \"like Mac OS X\", so the iOS check must run before\n // the macOS check or every iPhone gets misfiled as a Mac.\n let os = \"Other\";\n if (/Windows/.test(ua)) os = \"Windows\";\n else if (/iPhone|iPad|iPod/.test(ua)) os = \"iOS\";\n else if (/Mac OS X/.test(ua)) os = \"macOS\";\n else if (/Android/.test(ua)) os = \"Android\";\n else if (/Linux/.test(ua)) os = \"Linux\";\n\n return { deviceType, browser, os };\n}\n\nconst SEARCH_ENGINES = /google|bing|yahoo|duckduckgo|baidu|yandex|ecosia/i;\nconst SOCIAL_NETWORKS = /facebook|instagram|twitter|x\\.com|t\\.co|linkedin|tiktok|pinterest|reddit|threads\\.net/i;\n\nexport function classifyTrafficSource(\n referrer: string,\n utmMedium?: string | null,\n utmSource?: string | null,\n): string {\n if (utmMedium) {\n if (/cpc|ppc|paid|display/i.test(utmMedium)) return \"Paid\";\n if (/social/i.test(utmMedium)) return \"Social (campaign)\";\n if (/email/i.test(utmMedium)) return \"Email\";\n return `Campaign (${utmMedium})`;\n }\n if (utmSource) return `Campaign (${utmSource})`;\n if (!referrer) return \"Direct\";\n try {\n const host = new URL(referrer).hostname.replace(/^www\\./, \"\");\n if (SEARCH_ENGINES.test(host)) return \"Organic Search\";\n if (SOCIAL_NETWORKS.test(host)) return \"Social\";\n return `Referral (${host})`;\n } catch {\n return \"Direct\";\n }\n}\n\n// All display times are Eastern (the business's own timezone), even though\n// everything is stored in SQLite as UTC (`datetime('now')`). Intl's\n// timeZone handling covers the EST/EDT switch automatically, which a fixed\n// UTC offset would get wrong for half the year.\nconst ET_TZ = \"America/New_York\";\nconst ET_DAY_KEY = new Intl.DateTimeFormat(\"en-CA\", {\n timeZone: ET_TZ,\n year: \"numeric\",\n month: \"2-digit\",\n day: \"2-digit\",\n});\nconst ET_DATETIME = new Intl.DateTimeFormat(\"en-US\", {\n timeZone: ET_TZ,\n month: \"short\",\n day: \"numeric\",\n year: \"numeric\",\n hour: \"numeric\",\n minute: \"2-digit\",\n second: \"2-digit\",\n hour12: true,\n});\nconst DAY_LABEL = new Intl.DateTimeFormat(\"en-US\", {\n timeZone: \"UTC\",\n month: \"short\",\n day: \"numeric\",\n year: \"numeric\",\n});\n\n// SQLite stores \"YYYY-MM-DD HH:MM:SS\" with an implicit UTC offset.\nexport function parseSqliteUtc(s: string): Date {\n return new Date(s.replace(\" \", \"T\") + \"Z\");\n}\n\n// en-CA formats as YYYY-MM-DD, which doubles as a sortable day key.\nexport function easternDayKey(d: Date): string {\n return ET_DAY_KEY.format(d);\n}\n\nexport function formatEastern(s: string): string {\n return ET_DATETIME.format(parseSqliteUtc(s)) + \" ET\";\n}\n\n// dayKey is a plain \"YYYY-MM-DD\" calendar label (already Eastern), not an\n// instant — format it at noon UTC so no further timezone shift can move it\n// to a different calendar day.\nexport function formatDayLabel(dayKey: string): string {\n return DAY_LABEL.format(new Date(dayKey + \"T12:00:00Z\"));\n}\n","label",[143],"c4dd4929-91b7-4238-9373-05f78be9f769",["D",1789226792184],74,[46],"2b6c495a-0c39-41d8-bf8f-fb7b3a843014",["D",1789226792184],11,[29],"pulls","branches",2,"forks","envVars",{"_169":171,"_172":71},"connections",{"_169":71,"_170":71},"count","unfilledCount",1,"emptyCount",{"_52":157,"_33":29,"_31":32,"_145":-5,"_42":43,"_41":14,"_147":26,"_39":183,"_13":159,"_34":35,"_36":38,"_25":26,"_151":-5,"_177":184,"_28":185},{"_52":153,"_33":46,"_31":47,"_145":-5,"_42":43,"_41":14,"_147":26,"_39":180,"_13":155,"_34":48,"_36":50,"_25":26,"_151":-5,"_177":181,"_28":182},{"_52":142,"_33":143,"_31":144,"_145":-5,"_42":43,"_41":146,"_147":26,"_39":176,"_13":149,"_34":35,"_36":150,"_25":26,"_151":-5,"_177":178,"_28":179},["D",1789177072653],"children",[],[143],["D",1789226792184],[],[46],["D",1789226792184],[],[29],["D",1789161858464],["D",1789516253896],{"_108":107,"_116":112},{"_108":107,"_109":110,"_111":112,"_18":64,"_113":114,"_115":26},{"_13":97,"_33":98,"_52":43,"_99":53,"_100":-5,"_101":-5,"_102":-5,"_103":-5,"_104":-5,"_105":54,"_106":107},"ctx",{"_90":-5,"_136":224,"_225":226,"_227":228,"_229":26,"_230":26,"_231":26,"_232":67,"_233":-5,"_234":-7,"_235":-7,"_236":71,"_237":26,"_238":26,"_239":-5,"_240":26,"_241":26,"_242":26,"_243":-5},"ENV",{"_195":196,"_197":198,"_199":98,"_200":201,"_202":203,"_204":205,"_206":207,"_208":209,"_210":211,"_212":213,"_214":215,"_216":217,"_218":219,"_220":221,"_222":223},"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 · v90