[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8},"actionData","errors","root",{"_107":108,"_109":110},"routes/embed.x.$handle.$name.$",{"_9":10,"_11":12,"_13":14,"_15":16,"_17":18,"_19":20,"_21":-5},"handle","Tribeca_Rep","name","tribeca-site-analytics","path","db.ts","project",{"_22":45,"_11":12,"_33":46,"_47":-5,"_48":42,"_49":50,"_51":50,"_52":53,"_54":-5,"_55":56,"_57":58,"_59":32,"_60":-5,"_61":-5,"_62":63,"_64":65,"_66":67,"_68":69,"_70":71,"_72":-5,"_73":74,"_75":74,"_76":77,"_78":77,"_79":77,"_80":74,"_81":74,"_82":83,"_84":85,"_86":87,"_88":42},"file",{"_22":23,"_11":14,"_24":25,"_26":-5,"_27":28,"_29":30,"_31":32,"_33":34,"_35":36,"_37":38,"_39":40,"_41":42,"_43":-5,"_13":44},"permalink","https://www.val.town/x/Tribeca_Rep/tribeca-site-analytics/code/db.ts","extension","id","4ce3cd94-c510-4e63-b32d-80e53a387893","fileId","2c6db772-ae27-11f1-a5ee-1607ee4eb77e","parentId","branchId","0e49238c-239c-4e36-95d6-7f0c7c028ce1","branchVersion",181,"latest",true,"createdAt",["D",1789237428848],"version",31,"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 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// Per-IP-prefix ingest rate tracking (item 17 from the security review:\n// isAllowedHit in main.tsx is explicitly NOT a forgery control, just a\n// noise filter — this is the actual control against a sustained or\n// deliberate flood). One row per (prefix, minute); `count` is every hit\n// seen from that prefix in that minute (including ones this same check\n// goes on to block), `blocked` is how many of those were over the limit —\n// keeping both is what makes ingestRateSummary below useful for actually\n// seeing what's happening, not just enforcing a cutoff blindly. Keyed on\n// the same /24-or-/64 prefix as geo_cache above, for the same reason: it's\n// the shared-network unit that actually matters (an attacker rotating\n// addresses within one small block doesn't get a free pass), and it's\n// consistent with the one other place this file already buckets by IP.\nawait sqlite.execute(`CREATE TABLE IF NOT EXISTS ingest_rate (\n ip_prefix TEXT NOT NULL,\n minute_bucket TEXT NOT NULL,\n count INTEGER NOT NULL DEFAULT 0,\n blocked INTEGER NOT NULL DEFAULT 0,\n PRIMARY KEY (ip_prefix, minute_bucket)\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, includeBots = false) {\n const result = await sqlite.execute({\n sql:\n `SELECT site, COUNT(*) as views FROM pageviews WHERE created_at >= datetime('now', ?) AND event != 'user_engagement'${\n includeBots ? \"\" : \" AND (browser IS NULL OR browser != 'Bot')\"\n } 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 // Both default OFF (excluded) — Cloudflare Pages preview domains and bot\n // traffic used to be filtered ad hoc, in JS, in only 3 of the ~10 places\n // that read from this table, so the same page could show one total in a\n // chart and a different one in a table below it. Centralizing the\n // exclusion here means every query shares one definition; the explicit\n // `true` to opt back in is what makes the filter visible rather than\n // silent — a caller has to ask for preview/bot traffic on purpose.\n includeDevHost?: boolean;\n includeBots?: boolean;\n}\n\nfunction buildWhere(\n f: ReportFilters,\n opts?: { includeEngagementPings?: boolean },\n): { 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 if (!f.includeDevHost) {\n clauses.push(`site NOT LIKE '%.pages.dev'`);\n }\n if (!f.includeBots) {\n // NULL-safe: a row with no browser classification yet is not the same\n // as a bot, and `browser != 'Bot'` alone would silently drop it (SQL\n // WHERE treats NULL as neither true nor false).\n clauses.push(`(browser IS NULL OR browser != 'Bot')`);\n }\n // Unconditional, unlike devhost/bots above: a \"user_engagement\" row (the\n // /t.js beacon's periodic engagement-time ping — see main.tsx) is never a\n // \"hit\" in the sense every other query here means it. It isn't a\n // pageview, isn't a session-splitting event, and isn't a custom event a\n // person did — it's telemetry ABOUT an earlier hit, sent on a delay that\n // can be arbitrarily long (a backgrounded tab flushes on close, however\n // much later that is). Nothing needs an opt-IN flag for it the way\n // includeDevHost/includeBots exist, because there's no report that wants\n // it mixed into hit-counting; the one place that does want these rows\n // (engagementStats, below) asks for them explicitly via this opts param.\n if (!opts?.includeEngagementPings) {\n clauses.push(`event != 'user_engagement'`);\n }\n return { where: clauses.join(\" AND \"), args };\n}\n\n// Average engagement time (GA4's \"average engagement time\" column), sourced\n// from the /t.js beacon's periodic \"user_engagement\" pings — event_label\n// carries the elapsed-ms-while-visible as a string (see main.tsx: sessions.ts\n// isn't involved here, this is wall-clock foreground time, not the\n// gap-inferred session duration). The one caller that wants these rows, so\n// it deliberately overrides buildWhere's default exclusion.\nexport async function engagementStats(f: ReportFilters): Promise<{ avgEngagementMs: number; pings: number }> {\n const { where, args } = buildWhere(f, { includeEngagementPings: true });\n const result = await sqlite.execute({\n sql:\n `SELECT AVG(CAST(event_label AS REAL)) as avg_ms, COUNT(*) as pings\n FROM pageviews WHERE ${where} AND event = 'user_engagement' AND event_label IS NOT NULL`,\n args,\n });\n const row = result.rows[0] as unknown as { avg_ms: number | null; pings: number } | undefined;\n return { avgEngagementMs: row?.avg_ms ?? 0, pings: row?.pings ?? 0 };\n}\n\n// The event names this network treats as conversions — GA4 calls the same\n// idea \"key events\" (renamed from \"conversions\"). Centralized so every\n// report that needs a key-event count/rate shares one definition.\nexport const KEY_EVENTS = [\"cta_click\", \"outbound_click\"];\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\n// \"Pages and screens\" — views plus distinct visitors per path.\n// active_users under-counts relative to views for exactly the visitor_id\n// gap already tracked elsewhere (item 10): a NULL visitor_id is excluded\n// from COUNT(DISTINCT ...) entirely rather than miscounted, so it never\n// shows as a person who isn't one.\n//\n// Grouped by (site, path), not path alone: every site on this network\n// shares path strings like \"/\", \"/privacy/\", \"/disclaimer/\" — grouping by\n// path only, with no site filter applied, would silently sum seven\n// unrelated homepages into one meaningless \"/\" row. site is always\n// selected so the all-sites view can label each row; a site-scoped caller\n// just gets the same site repeated on every row, which main.tsx's\n// renderer drops rather than show a redundant column.\nexport async function pagesBreakdown(f: ReportFilters, limit = 20) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql:\n `SELECT site, path, COUNT(*) as views, COUNT(DISTINCT visitor_id) as active_users\n FROM pageviews WHERE ${where} AND event = 'pageview'\n GROUP BY site, path ORDER BY views DESC LIMIT ?`,\n args: [...args, limit],\n });\n return result.rows as unknown as { site: string; path: string; views: number; active_users: number }[];\n}\n\n// \"Tech details\" — active users / event count / key events for one\n// dimension at a time (GA4 lets you switch Browser/OS/device category; this\n// only ever renders Browser today — the dimension param exists so adding a\n// switcher later is a call-site change, not a query rewrite). \"New users\"\n// (GA4 has it here too) isn't included — computing it per dimension value\n// needs the same first-hit logic as landingPages below, and wasn't worth\n// duplicating for a column this report can live without for now.\nexport async function techBreakdown(\n f: ReportFilters,\n dimension: \"browser\" | \"os\" | \"device_type\",\n limit = 20,\n) {\n const { where, args } = buildWhere(f);\n const keyPlaceholders = KEY_EVENTS.map(() => \"?\").join(\",\");\n const result = await sqlite.execute({\n sql:\n `SELECT COALESCE(${dimension}, 'unknown') as value,\n COUNT(DISTINCT visitor_id) as active_users,\n COUNT(*) as event_count,\n SUM(CASE WHEN event IN (${keyPlaceholders}) THEN 1 ELSE 0 END) as key_events\n FROM pageviews WHERE ${where}\n GROUP BY value ORDER BY active_users DESC LIMIT ?`,\n args: [...KEY_EVENTS, ...args, limit],\n });\n return result.rows as unknown as\n { value: string; active_users: number; event_count: number; key_events: number }[];\n}\n\n// \"Landing page\" — the path of a visitor's lifetime-first recorded\n// pageview, paired with whether that same visitor ever fired a key event\n// in the window. This is visitor-scoped, not session-scoped: sessions\n// don't exist yet (item 8), so there's no per-visit landing page to\n// attribute to. The approximation is exact today because \"returning\n// visitors\" has measured 0 in every window checked so far on this\n// network — a visitor who comes back a second time would be\n// mis-attributed to their original landing page rather than getting a\n// fresh one, but that case doesn't currently occur here.\n//\n// Grouped by (site, path) for the same reason as pagesBreakdown above —\n// \"/\" is every site's homepage, so an all-sites view grouping on path\n// alone would merge seven unrelated landing pages into one row.\nexport async function landingPages(f: ReportFilters, limit = 20) {\n const { where, args } = buildWhere(f);\n const keyPlaceholders = KEY_EVENTS.map(() => \"?\").join(\",\");\n const [landingResult, keyResult] = await Promise.all([\n sqlite.execute({\n sql:\n `SELECT p.site as site, p.path as path, p.visitor_id as visitor_id\n FROM pageviews p\n JOIN (\n SELECT visitor_id, MIN(id) as first_id\n FROM pageviews\n WHERE visitor_id IS NOT NULL AND event = 'pageview' AND ${where}\n GROUP BY visitor_id\n ) fh ON fh.first_id = p.id`,\n args,\n }),\n sqlite.execute({\n sql:\n `SELECT DISTINCT visitor_id FROM pageviews\n WHERE visitor_id IS NOT NULL AND event IN (${keyPlaceholders}) AND ${where}`,\n args: [...KEY_EVENTS, ...args],\n }),\n ]);\n const keySet = new Set(\n (keyResult.rows as unknown as { visitor_id: string }[]).map((r) => r.visitor_id),\n );\n const byPath = new Map();\n for (const r of landingResult.rows as unknown as { site: string; path: string; visitor_id: string }[]) {\n const groupKey = `${r.site} ${r.path}`;\n const e = byPath.get(groupKey) ?? { site: r.site, path: r.path, visitors: 0, keyVisitors: 0 };\n e.visitors += 1;\n if (keySet.has(r.visitor_id)) e.keyVisitors += 1;\n byPath.set(groupKey, e);\n }\n return Array.from(byPath.values())\n .sort((a, b) => b.visitors - a.visitors)\n .slice(0, limit);\n}\n\n// Each visitor's first-ever-recorded traffic_source, for \"User acquisition\"\n// (GA4: first-touch/\"First user default channel grouping\" — every session a\n// visitor ever has is credited to whichever channel brought them in\n// originally, not to that session's own source). Same visitor-scoped,\n// lifetime-first approximation as landingPages above, and for the same\n// reason: exact today because returning visitors measure 0 on this network.\n// Restricted to event='pageview' for the \"first hit\" determination, matching\n// landingPages, since the beacon always fires a pageview before any other\n// event on a given visit.\nexport async function firstTouchChannels(f: ReportFilters) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql:\n `SELECT p.visitor_id as visitor_id, p.traffic_source as traffic_source\n FROM pageviews p\n JOIN (\n SELECT visitor_id, MIN(id) as first_id\n FROM pageviews\n WHERE visitor_id IS NOT NULL AND event = 'pageview' AND ${where}\n GROUP BY visitor_id\n ) fh ON fh.first_id = p.id`,\n args,\n });\n return result.rows as unknown as { visitor_id: string; traffic_source: string | null }[];\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 = ? AND (browser IS NULL OR browser != 'Bot') AND event != 'user_engagement' ORDER BY id DESC LIMIT ?`\n : `SELECT * FROM pageviews WHERE (browser IS NULL OR browser != 'Bot') AND event != 'user_engagement' 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', ?) AND (browser IS NULL OR browser != 'Bot') AND event != 'user_engagement' ORDER BY id DESC LIMIT ?`\n : `SELECT * FROM pageviews WHERE created_at >= datetime('now', ?) AND (browser IS NULL OR browser != 'Bot') AND event != 'user_engagement' 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\n// Hits per prefix per minute before this network's own beacon starts\n// dropping them. Picked against this network's real traffic (a handful of\n// pageviews a day across ALL 7 sites combined — see MEMORY/project notes),\n// not a generic default: a single real visit plus click events plus one\n// engagement ping is maybe 3-6 rows, so even a busy shared office network\n// hitting double digits a minute would be unusual. 30/minute leaves a wide\n// margin above anything genuine while still stopping a scripted flood fast\n// (a bad actor sending hundreds/minute hits this within seconds). Adjust\n// using ingestRateSummary's real data below if it ever proves wrong in\n// either direction.\nexport const INGEST_RATE_LIMIT_PER_MINUTE = 30;\n// A bit over the 60-minute window ingestRateSummary shows, so pruning never\n// deletes a bucket that visibility might still want to display.\nconst INGEST_RATE_RETENTION_MINUTES = 65;\n\n// Bumps this prefix's counter for the current UTC minute (computed by SQLite\n// itself, not JS Date, so it can never drift from the `datetime('now')` the\n// rest of this file uses) and reports whether this hit is within budget.\n// Soft/best-effort by design: two concurrent requests from the same prefix\n// in the same instant could each read the count before the other's write\n// lands, occasionally letting one extra hit through right at the boundary.\n// That's an acceptable gap for a rate LIMIT on a low-traffic network, not a\n// hard security boundary — see isAllowedHit's own comment on what this is\n// and isn't a defense against.\nexport async function checkIngestRate(ipPrefix: string): Promise<{ allowed: boolean; count: number }> {\n try {\n await sqlite.execute({\n sql: `DELETE FROM ingest_rate WHERE minute_bucket < strftime('%Y-%m-%d %H:%M', 'now', ?)`,\n args: [`-${INGEST_RATE_RETENTION_MINUTES} minutes`],\n });\n } catch (err) {\n console.error(\"ingest_rate prune failed:\", err);\n }\n await sqlite.execute({\n sql:\n `INSERT INTO ingest_rate (ip_prefix, minute_bucket, count)\n VALUES (?, strftime('%Y-%m-%d %H:%M', 'now'), 1)\n ON CONFLICT(ip_prefix, minute_bucket) DO UPDATE SET count = count + 1`,\n args: [ipPrefix],\n });\n const result = await sqlite.execute({\n sql: `SELECT count FROM ingest_rate WHERE ip_prefix = ? AND minute_bucket = strftime('%Y-%m-%d %H:%M', 'now')`,\n args: [ipPrefix],\n });\n const count = (result.rows[0] as unknown as { count: number } | undefined)?.count ?? 1;\n const allowed = count <= INGEST_RATE_LIMIT_PER_MINUTE;\n if (!allowed) {\n await sqlite.execute({\n sql:\n `UPDATE ingest_rate SET blocked = blocked + 1\n WHERE ip_prefix = ? AND minute_bucket = strftime('%Y-%m-%d %H:%M', 'now')`,\n args: [ipPrefix],\n });\n }\n return { allowed, count };\n}\n\n// The \"visibility\" half of item 17: what's actually coming in, not just the\n// cutoff enforcing itself silently. Not filtered by ReportFilters — this is\n// about the raw ingest pipe (which can include prefixes rejected by\n// isAllowedHit entirely, e.g. a forged `s`), not report data scoped to\n// known sites. Ordered by volume so a flood is the first row, not buried.\nexport async function ingestRateSummary(minutes = 60) {\n const result = await sqlite.execute({\n sql:\n `SELECT ip_prefix, SUM(count) as hits, SUM(blocked) as blocked\n FROM ingest_rate\n WHERE minute_bucket >= strftime('%Y-%m-%d %H:%M', 'now', ?)\n GROUP BY ip_prefix ORDER BY hits DESC LIMIT 20`,\n args: [`-${minutes} minutes`],\n });\n return result.rows as unknown as { ip_prefix: string; hits: number; blocked: number }[];\n}\n\n// Non-US rows are forced to country grain even though some older rows carry\n// a region value from before lookupGeo stopped calling ipapi.co for non-US\n// traffic (main.tsx) — region is only ever looked up for US hits now, so a\n// mixed grain here (city-name for an old French visitor, country-only for a\n// new one) would silently read as \"foreign region data broke\" rather than\n// \"the definition changed on this date\". One grain per country, always.\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(CASE WHEN country_code = 'US' THEN region ELSE NULL END, country, 'Unknown') as place,\n 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\n// Bot share of a window, regardless of the caller's own includeBots setting\n// — always computed against the full (bots-included) population, so a\n// network that's mostly bot traffic doesn't get to hide that fact behind\n// the same toggle that excludes bots from the other panels.\nexport async function botShare(f: ReportFilters): Promise<{ botCount: number; totalCount: number }> {\n const { where, args } = buildWhere({ ...f, includeBots: true });\n const result = await sqlite.execute({\n sql:\n `SELECT SUM(CASE WHEN browser = 'Bot' THEN 1 ELSE 0 END) as bot_count, COUNT(*) as total_count\n FROM pageviews WHERE ${where}`,\n args,\n });\n const row = result.rows[0] as unknown as { bot_count: number | null; total_count: number } | undefined;\n return { botCount: row?.bot_count ?? 0, totalCount: row?.total_count ?? 0 };\n}\n\n// Raw per-hit fields needed to build sessions (see sessions.ts). The shape\n// here isn't imported from sessions.ts and vice versa to avoid a circular\n// dependency (sessions.ts imports KEY_EVENTS from this file) — TypeScript's\n// structural typing means the two line up without either file needing to\n// know about the other's types.\nexport async function sessionRawHits(f: ReportFilters) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql:\n `SELECT visitor_id, created_at, event, traffic_source, utm_source, utm_medium, utm_campaign, site\n FROM pageviews WHERE ${where} ORDER BY id`,\n args,\n });\n return result.rows as unknown as {\n visitor_id: string | null;\n created_at: string;\n event: string;\n traffic_source: string | null;\n utm_source: string | null;\n utm_medium: string | null;\n utm_campaign: string | null;\n site: string;\n }[];\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// Per-event-NAME (not event+label like eventBreakdown above — this matches\n// GA4's Events report dimension, which is just the name) count and distinct\n// visitors, for \"event count per active user\" and the key-event flag.\nexport async function eventUserCounts(f: ReportFilters) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql:\n `SELECT event, COUNT(*) as n, COUNT(DISTINCT visitor_id) as active_users\n FROM pageviews WHERE ${where} AND event IS NOT NULL AND event != 'pageview'\n GROUP BY event ORDER BY n DESC`,\n args,\n });\n return result.rows as unknown as { event: string; n: number; active_users: 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 (plus visitor_id, for the chart's \"unique visitors\"\n// metric — see main.tsx) for the Reports tab's trend chart, one row per\n// pageview in the window. Bucketing into calendar days happens in main.tsx\n// using 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, visitor_id FROM pageviews WHERE ${where}`,\n args,\n });\n return result.rows as unknown as { created_at: string; visitor_id: string | null }[];\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, visitor_id FROM pageviews WHERE ${where}`,\n args,\n });\n return result.rows as unknown as { site: string; created_at: string; visitor_id: string | null }[];\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","deleted",false,"label",[14],"784adc29-f9cf-46ef-b3ad-c77fef19f98f",["D",1789161858464],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","Pageview tracker + dashboard for the lead-gen site network","imageUrl","userId","c5acd3b3-60a7-4deb-bc6d-528f83012ea9","pinnedFileId","25c3e054-ae27-11f1-936e-1607ee4eb77e","pinPreview","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",{"_9":10,"_106":102},"org",{"_9":10,"_99":100,"_101":102,"_55":56,"_103":104,"_105":42},"branch",{"_35":89,"_11":90,"_22":28,"_91":45,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":-5,"_97":12,"_98":10},"isOwned",194,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","M A","avatar","https://lh3.googleusercontent.com/a/ACg8ocI9znhXn2hWp1wjjyWzeuOyoO_JrDgJo8aD0umFraR7bAG3UAmc=s96-c","tier","free","canRestrictVals","profileImageUrl","ctx",{"_82":-5,"_140":141,"_142":143,"_144":145,"_146":42,"_147":42,"_148":42,"_149":32,"_150":-5,"_151":-7,"_152":-7,"_153":63,"_154":42,"_155":42,"_156":-5,"_157":42,"_158":42,"_159":42,"_160":-5},"ENV",{"_111":112,"_113":114,"_115":90,"_116":117,"_118":119,"_120":121,"_122":123,"_124":125,"_126":127,"_128":129,"_130":131,"_132":133,"_134":135,"_136":137,"_138":139},"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","orgs",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]