[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_212":213,"_214":215},"routes/_app.x.$handle.$name",{"_99":98,"_32":45,"_108":89,"_27":109,"_13":14,"_110":-5,"_11":111,"_112":113,"_114":115,"_116":117,"_118":119,"_20":-5,"_120":-5,"_121":-5,"_122":25,"_123":124,"_125":58,"_126":-5,"_127":-5,"_128":129},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_43":44,"_32":45,"_38":46,"_47":-5,"_48":25,"_49":50,"_51":50,"_52":53,"_54":-5,"_18":55,"_56":31,"_57":58,"_59":-5,"_60":-5,"_61":62,"_63":64,"_65":66,"_67":68,"_69":70,"_71":-5,"_72":73,"_74":73,"_75":76,"_77":76,"_78":76,"_79":73,"_80":73,"_81":82,"_83":84,"_85":86,"_87":25},"version",186,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":23,"_24":25,"_26":25,"_27":28,"_29":28},"file",{"_30":31,"_32":28,"_33":34,"_35":37,"_38":39,"_40":14,"_41":42,"_24":25},"previous",{"_30":31,"_32":28,"_33":34,"_35":36},"deleted",false,"deletedHere","path","main.tsx","latestPath","fileId","25c3e054-ae27-11f1-936e-1607ee4eb77e","name","fileType","http","content","import { Hono } from \"npm:hono@4\";\nimport type { Context } from \"npm:hono@4\";\nimport {\n activeVisits,\n backfillHistoricalRows,\n botShare,\n checkIngestRate,\n dailyPageviewTimes,\n dailySeriesRaw,\n deviceBreakdown,\n eventBreakdown,\n eventTimes,\n engagementStats,\n eventUserCounts,\n firstTouchChannels,\n getCachedRegion,\n type Geo,\n INGEST_RATE_LIMIT_PER_MINUTE,\n ingestRateSummary,\n KEY_EVENTS,\n landingPages,\n newVsReturning,\n type Pageview,\n recentViews,\n recordEvent,\n regionBreakdown,\n type ReportFilters,\n pagesBreakdown,\n sessionRawHits,\n setCachedRegion,\n siteTotals,\n techBreakdown,\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 { buildSessions, groupSessionsByChannel, summarize } from \"./sessions.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.\n//\n// Accuracy caveat, worth remembering before anyone treats a Geo match\n// percentage as a measurement: region comes from a single ipapi.co lookup\n// per /24, cached 30 days (see lookupGeo). A /24 can straddle a state line,\n// and mobile/carrier IPs often resolve to the carrier's exit point rather\n// than the visitor's actual location — neither is rare. The card is\n// directionally useful (\"is this roughly working\"), not a precise\n// per-visitor measurement, and a percentage built on a small or\n// mostly-unknown sample (check the \"no geo signal\" line above the card)\n// is weaker evidence than the same percentage on a week of traffic.\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\nconst KEY_EVENT_SET = new Set(KEY_EVENTS);\n\n// GA4-style \"value (share%)\" cell. Cheap, and most of why GA4 tables read\n// well compared to a bare count — every number gets a sense of scale\n// against the same total without a second lookup.\nfunction withShare(n: number, total: number): string {\n if (total <= 0) return String(n);\n const pct = ((n / total) * 100).toFixed(2);\n return `${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// Shared by both ingest routes below (/px.gif's query-string GET and\n// /collect's JSON POST) — the actual \"is this a real, allowed hit, and what\n// do we derive from it\" logic doesn't depend on how the fields arrived, only\n// on what they are. Kept here (not db.ts) since isAllowedHit/lookupGeo,\n// which it calls, are also main.tsx-local.\ninterface HitInput {\n site: string;\n path: string;\n ref: string;\n ua: string;\n ip: string;\n countryCode: string | null;\n utmSource: string | null;\n utmMedium: string | null;\n utmCampaign: string | null;\n utmTerm: string | null;\n utmContent: string | null;\n viewport: string | null;\n visitorId: string | null;\n eventName: string;\n eventLabel: string | null;\n}\n\nasync function ingestHit(h: HitInput): Promise {\n if (!h.site || !isAllowedHit(h.site, h.ref)) return;\n // Item 17: the actual control against a sustained/deliberate flood\n // targeting a real, allowed site — isAllowedHit above only screens out\n // garbage/disallowed sites, for free, before this. Gates the expensive\n // work below it (a geo lookup that can hit ipapi.co, plus the DB write),\n // not the cheap allowlist check above. Silently dropped, no error\n // surfaced to the client either way (/px.gif still returns its GIF,\n // /collect still returns 204) — same \"degrades silently\" posture as the\n // rest of this beacon, and deliberately gives an attacker probing the\n // limit no observable signal to calibrate against.\n const rate = await checkIngestRate(ipPrefix(h.ip));\n if (!rate.allowed) return;\n try {\n const geo = await lookupGeo(h.ip, h.countryCode);\n const { deviceType, browser, os } = parseUA(h.ua);\n const trafficSource = classifyTrafficSource(h.ref, h.utmMedium, h.utmSource);\n await recordEvent({\n site: h.site,\n path: h.path,\n referrer: h.ref,\n ua: h.ua,\n geo,\n utmSource: h.utmSource,\n utmMedium: h.utmMedium,\n utmCampaign: h.utmCampaign,\n utmTerm: h.utmTerm,\n utmContent: h.utmContent,\n deviceType,\n browser,\n os,\n viewport: h.viewport,\n visitorId: h.visitorId,\n trafficSource,\n event: h.eventName,\n eventLabel: h.eventLabel,\n });\n } catch (err) {\n console.error(\"hit ingest failed:\", err);\n }\n}\n\n// The original tracking beacon. A 1x1 GIF via , not fetch() — sidesteps\n// CORS entirely, works even on a strict _headers file, and degrades silently\n// if blocked. Kept indefinitely alongside /collect below (not deprecated):\n// any page whose HTML still carries the older inline snippet keeps working\n// exactly as before, forever — the migration to /t.js + POST /collect is\n// additive, not a cutover, so there's no rollout window where anything\n// breaks.\napp.get(\"/px.gif\", async (c) => {\n const q = (key: string, max = 300) => (c.req.query(key) || \"\").slice(0, max);\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 await ingestHit({\n site: q(\"s\", 200).toLowerCase().replace(/^www\\./, \"\"),\n path: q(\"p\") || \"/\",\n ref: q(\"r\"),\n ua,\n ip,\n countryCode,\n // GA4-style flexible signals. All optional — the beacon still works (as\n // a plain pageview) if a site's snippet is the older, simpler version.\n utmSource: q(\"utm_source\") || null,\n utmMedium: q(\"utm_medium\") || null,\n utmCampaign: q(\"utm_campaign\") || null,\n utmTerm: q(\"utm_term\") || null,\n utmContent: q(\"utm_content\") || null,\n viewport: q(\"vp\", 20) || null,\n 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 eventName: q(\"ev\", 60) || \"pageview\",\n eventLabel: q(\"el\", 200) || null,\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// New ingest path for the /t.js beacon below: POST, JSON-shaped body sent as\n// text/plain (so a cross-origin sendBeacon/fetch call counts as a CORS\n// \"simple request\" and never needs an OPTIONS preflight route). Same fields,\n// same allowlist/geo/UA pipeline as /px.gif via ingestHit — the only\n// difference is where the fields come from and that nothing is returned to\n// render (sendBeacon never reads the response anyway).\napp.post(\"/collect\", async (c) => {\n let body: Record;\n try {\n body = JSON.parse(await c.req.text());\n } catch {\n return c.json({ ok: false }, 400);\n }\n const str = (v: unknown, max = 300) => typeof v === \"string\" ? v.slice(0, max) : \"\";\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 await ingestHit({\n site: str(body.s, 200).toLowerCase().replace(/^www\\./, \"\"),\n path: str(body.p) || \"/\",\n ref: str(body.r),\n ua,\n ip,\n countryCode,\n utmSource: str(body.utm_source) || null,\n utmMedium: str(body.utm_medium) || null,\n utmCampaign: str(body.utm_campaign) || null,\n utmTerm: str(body.utm_term) || null,\n utmContent: str(body.utm_content) || null,\n viewport: str(body.vp, 20) || null,\n visitorId: str(body.vid, 64) || null,\n eventName: str(body.ev, 60) || \"pageview\",\n eventLabel: str(body.el, 200) || null,\n });\n\n c.header(\"Access-Control-Allow-Origin\", \"*\");\n return c.body(null, 204);\n});\n\n// The tracker as a shared script instead of ~50 copies of the same inline\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 botShare,\n checkIngestRate,\n dailyPageviewTimes,\n dailySeriesRaw,\n deviceBreakdown,\n eventBreakdown,\n eventTimes,\n engagementStats,\n eventUserCounts,\n firstTouchChannels,\n getCachedRegion,\n type Geo,\n INGEST_RATE_LIMIT_PER_MINUTE,\n ingestRateSummary,\n KEY_EVENTS,\n landingPages,\n newVsReturning,\n type Pageview,\n recentViews,\n recordEvent,\n regionBreakdown,\n type ReportFilters,\n pagesBreakdown,\n sessionRawHits,\n setCachedRegion,\n siteTotals,\n techBreakdown,\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 { buildSessions, groupSessionsByChannel, summarize } from \"./sessions.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.\n//\n// Accuracy caveat, worth remembering before anyone treats a Geo match\n// percentage as a measurement: region comes from a single ipapi.co lookup\n// per /24, cached 30 days (see lookupGeo). A /24 can straddle a state line,\n// and mobile/carrier IPs often resolve to the carrier's exit point rather\n// than the visitor's actual location — neither is rare. The card is\n// directionally useful (\"is this roughly working\"), not a precise\n// per-visitor measurement, and a percentage built on a small or\n// mostly-unknown sample (check the \"no geo signal\" line above the card)\n// is weaker evidence than the same percentage on a week of traffic.\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\nconst KEY_EVENT_SET = new Set(KEY_EVENTS);\n\n// GA4-style \"value (share%)\" cell. Cheap, and most of why GA4 tables read\n// well compared to a bare count — every number gets a sense of scale\n// against the same total without a second lookup.\nfunction withShare(n: number, total: number): string {\n if (total <= 0) return String(n);\n const pct = ((n / total) * 100).toFixed(2);\n return `${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// Shared by both ingest routes below (/px.gif's query-string GET and\n// /collect's JSON POST) — the actual \"is this a real, allowed hit, and what\n// do we derive from it\" logic doesn't depend on how the fields arrived, only\n// on what they are. Kept here (not db.ts) since isAllowedHit/lookupGeo,\n// which it calls, are also main.tsx-local.\ninterface HitInput {\n site: string;\n path: string;\n ref: string;\n ua: string;\n ip: string;\n countryCode: string | null;\n utmSource: string | null;\n utmMedium: string | null;\n utmCampaign: string | null;\n utmTerm: string | null;\n utmContent: string | null;\n viewport: string | null;\n visitorId: string | null;\n eventName: string;\n eventLabel: string | null;\n}\n\nasync function ingestHit(h: HitInput): Promise {\n if (!h.site || !isAllowedHit(h.site, h.ref)) return;\n // Item 17: the actual control against a sustained/deliberate flood\n // targeting a real, allowed site — isAllowedHit above only screens out\n // garbage/disallowed sites, for free, before this. Gates the expensive\n // work below it (a geo lookup that can hit ipapi.co, plus the DB write),\n // not the cheap allowlist check above. Silently dropped, no error\n // surfaced to the client either way (/px.gif still returns its GIF,\n // /collect still returns 204) — same \"degrades silently\" posture as the\n // rest of this beacon, and deliberately gives an attacker probing the\n // limit no observable signal to calibrate against.\n const rate = await checkIngestRate(ipPrefix(h.ip));\n if (!rate.allowed) return;\n try {\n const geo = await lookupGeo(h.ip, h.countryCode);\n const { deviceType, browser, os } = parseUA(h.ua);\n const trafficSource = classifyTrafficSource(h.ref, h.utmMedium, h.utmSource);\n await recordEvent({\n site: h.site,\n path: h.path,\n referrer: h.ref,\n ua: h.ua,\n geo,\n utmSource: h.utmSource,\n utmMedium: h.utmMedium,\n utmCampaign: h.utmCampaign,\n utmTerm: h.utmTerm,\n utmContent: h.utmContent,\n deviceType,\n browser,\n os,\n viewport: h.viewport,\n visitorId: h.visitorId,\n trafficSource,\n event: h.eventName,\n eventLabel: h.eventLabel,\n });\n } catch (err) {\n console.error(\"hit ingest failed:\", err);\n }\n}\n\n// The original tracking beacon. A 1x1 GIF via , not fetch() — sidesteps\n// CORS entirely, works even on a strict _headers file, and degrades silently\n// if blocked. Kept indefinitely alongside /collect below (not deprecated):\n// any page whose HTML still carries the older inline snippet keeps working\n// exactly as before, forever — the migration to /t.js + POST /collect is\n// additive, not a cutover, so there's no rollout window where anything\n// breaks.\napp.get(\"/px.gif\", async (c) => {\n const q = (key: string, max = 300) => (c.req.query(key) || \"\").slice(0, max);\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 await ingestHit({\n site: q(\"s\", 200).toLowerCase().replace(/^www\\./, \"\"),\n path: q(\"p\") || \"/\",\n ref: q(\"r\"),\n ua,\n ip,\n countryCode,\n // GA4-style flexible signals. All optional — the beacon still works (as\n // a plain pageview) if a site's snippet is the older, simpler version.\n utmSource: q(\"utm_source\") || null,\n utmMedium: q(\"utm_medium\") || null,\n utmCampaign: q(\"utm_campaign\") || null,\n utmTerm: q(\"utm_term\") || null,\n utmContent: q(\"utm_content\") || null,\n viewport: q(\"vp\", 20) || null,\n 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 eventName: q(\"ev\", 60) || \"pageview\",\n eventLabel: q(\"el\", 200) || null,\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// New ingest path for the /t.js beacon below: POST, JSON-shaped body sent as\n// text/plain (so a cross-origin sendBeacon/fetch call counts as a CORS\n// \"simple request\" and never needs an OPTIONS preflight route). Same fields,\n// same allowlist/geo/UA pipeline as /px.gif via ingestHit — the only\n// difference is where the fields come from and that nothing is returned to\n// render (sendBeacon never reads the response anyway).\napp.post(\"/collect\", async (c) => {\n let body: Record;\n try {\n body = JSON.parse(await c.req.text());\n } catch {\n return c.json({ ok: false }, 400);\n }\n const str = (v: unknown, max = 300) => typeof v === \"string\" ? v.slice(0, max) : \"\";\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 await ingestHit({\n site: str(body.s, 200).toLowerCase().replace(/^www\\./, \"\"),\n path: str(body.p) || \"/\",\n ref: str(body.r),\n ua,\n ip,\n countryCode,\n utmSource: str(body.utm_source) || null,\n utmMedium: str(body.utm_medium) || null,\n utmCampaign: str(body.utm_campaign) || null,\n utmTerm: str(body.utm_term) || null,\n utmContent: str(body.utm_content) || null,\n viewport: str(body.vp, 20) || null,\n visitorId: str(body.vid, 64) || null,\n eventName: str(body.ev, 60) || \"pageview\",\n eventLabel: str(body.el, 200) || null,\n });\n\n c.header(\"Access-Control-Allow-Origin\", \"*\");\n return c.body(null, 204);\n});\n\n// The tracker as a shared script instead of ~50 copies of the same inline\n// \n`;\n}\n\nexport default app.fetch;\n","createdAt",["D",1789237497337],"branchVersion","branchId","0e49238c-239c-4e36-95d6-7f0c7c028ce1","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",{"_99":98,"_107":103},"org",{"_99":98,"_100":101,"_102":103,"_18":55,"_104":105,"_106":25},"branch",{"_13":88,"_32":89,"_43":42,"_90":44,"_91":-5,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":45,"_97":98},"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",{"_43":44,"_32":45,"_38":207,"_47":-5,"_48":25,"_49":50,"_51":50,"_52":53,"_54":-5,"_18":55,"_56":31,"_57":58,"_59":-5,"_60":-5,"_61":62,"_63":64,"_65":66,"_67":68,"_69":208,"_71":-5,"_72":73,"_74":73,"_75":76,"_77":76,"_78":76,"_79":73,"_80":73,"_81":209,"_83":210,"_85":211,"_87":25},"tree",[186,187,188,189,190],"files",[186,187,188,189,190],"counts",{"_174":62,"_175":176,"_177":62,"_178":179,"_180":181},"contents",[130,131,132,133,134],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_43":166,"_32":167,"_30":168,"_138":-5,"_41":42,"_40":169,"_140":58,"_38":170,"_13":171,"_33":143,"_35":172,"_24":25,"_145":-5,"_27":173},{"_43":162,"_32":28,"_30":31,"_138":-5,"_41":42,"_40":14,"_140":25,"_38":163,"_13":164,"_33":34,"_35":37,"_24":25,"_145":-5,"_27":165},{"_43":154,"_32":155,"_30":156,"_138":-5,"_41":42,"_40":157,"_140":58,"_38":158,"_13":159,"_33":143,"_35":160,"_24":25,"_145":-5,"_27":161},{"_43":147,"_32":148,"_30":149,"_138":-5,"_41":42,"_40":150,"_140":58,"_38":151,"_13":142,"_33":143,"_35":152,"_24":25,"_145":-5,"_27":153},{"_43":135,"_32":136,"_30":137,"_138":-5,"_41":42,"_40":139,"_140":58,"_38":141,"_13":142,"_33":143,"_35":144,"_24":25,"_145":-5,"_27":146},"9f4b399e-65a9-4be7-9d82-1d404849c36b","util.ts","f1a3c344-ae2f-11f1-a1c4-1607ee4eb77e","parentId",179,"latest",["D",1789237333126],4,"script","// 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 // Not exhaustive — this only catches a crawler/client that identifies\n // itself, which is the low bar that matters here (see two real gaps\n // found by inspecting actual \"Direct\" traffic on this network:\n // \"GoogleOther\" and \"InternetArchive-NOD-thumbnailer\" neither contain\n // bot/crawler/spider, so they sailed through as ordinary Chrome users\n // until these were added). A scraper that deliberately rotates through\n // genuine browser UA strings to evade exactly this kind of check is a\n // different, harder problem no UA regex can solve — that's what the\n // ingest-rate visibility (checkIngestRate/ingestRateSummary) is for.\n /bot|crawler|spider|bingpreview|facebookexternalhit|headlesschrome|phantomjs|puppeteer|playwright|curl\\/|wget\\/|python-requests|python-urllib|go-http-client|libwww-perl|scrapy|googleother|google-inspectiontool|mediapartners-google|apis-google|duplexweb-google|feedfetcher-google|google-read-aloud|google-safety|internetarchive|archive\\.org|node-fetch|axios\\/|okhttp|postmanruntime|insomnia|apache-httpclient|java\\/\\d/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",[136],"2d72cc38-5e96-47cb-b1ad-4722494fd743","sessions_test.ts","f069804c-aec3-11f1-a9ca-1607ee4eb77e",165,["D",1789236392016],"// Table-driven tests for sessions.ts, run via run_file (no test-framework\n// dependency — just asserts that throw with a clear message on failure, and\n// a final PASS log on success). Covers the three edge classes called out in\n// the task: the inactivity window, campaign change, and the midnight\n// boundary — plus the engaged-session predicate and anonymous-hit handling.\nimport {\n buildSessions,\n groupSessionsByChannel,\n isEngaged,\n type RawHit,\n splitIntoSessions,\n summarize,\n} from \"./sessions.ts\";\n\nlet failures = 0;\nfunction check(name: string, cond: boolean, detail?: string) {\n if (!cond) {\n failures++;\n console.error(`FAIL: ${name}${detail ? \" — \" + detail : \"\"}`);\n } else {\n console.log(`ok ${name}`);\n }\n}\n\nfunction hit(overrides: Partial): RawHit {\n return {\n visitor_id: \"v1\",\n created_at: \"2026-01-01 00:00:00\",\n event: \"pageview\",\n traffic_source: \"Direct\",\n utm_source: null,\n utm_medium: null,\n utm_campaign: null,\n site: \"example.com\",\n ...overrides,\n };\n}\n\n// --- Gap rule -----------------------------------------------------------\n{\n const hits = [\n hit({ created_at: \"2026-06-01 12:00:00\" }),\n hit({ created_at: \"2026-06-01 12:10:00\" }), // +10 min, same session\n hit({ created_at: \"2026-06-01 12:45:00\" }), // +35 min from previous, new session\n ];\n const sessions = splitIntoSessions(hits);\n check(\"gap rule: 35-minute gap splits into 2 sessions\", sessions.length === 2, `got ${sessions.length}`);\n check(\"gap rule: first session has 2 hits\", sessions[0]?.hits.length === 2);\n check(\"gap rule: second session has 1 hit\", sessions[1]?.hits.length === 1);\n}\n{\n const hits = [\n hit({ created_at: \"2026-06-01 12:00:00\" }),\n hit({ created_at: \"2026-06-01 12:29:00\" }), // +29 min, within window\n ];\n const sessions = splitIntoSessions(hits);\n check(\"gap rule: 29-minute gap stays one session\", sessions.length === 1, `got ${sessions.length}`);\n}\n{\n const hits = [\n hit({ created_at: \"2026-06-01 12:00:00\" }),\n hit({ created_at: \"2026-06-01 12:30:01\" }), // exactly 30:01, over the boundary\n ];\n const sessions = splitIntoSessions(hits);\n check(\n \"gap rule: 30min01s gap (just over threshold) splits\",\n sessions.length === 2,\n `got ${sessions.length}`,\n );\n}\n\n// --- Campaign rule --------------------------------------------------------\n{\n const hits = [\n hit({ created_at: \"2026-06-01 12:00:00\", utm_source: \"google\", utm_medium: \"cpc\" }),\n hit({ created_at: \"2026-06-01 12:05:00\", utm_source: \"google\", utm_medium: \"cpc\" }),\n hit({ created_at: \"2026-06-01 12:10:00\", utm_source: \"facebook\", utm_medium: \"cpc\" }), // changed\n ];\n const sessions = splitIntoSessions(hits);\n check(\n \"campaign rule: mid-visit UTM change splits despite short gap\",\n sessions.length === 2,\n `got ${sessions.length}`,\n );\n check(\"campaign rule: first session keeps the first 2 hits\", sessions[0]?.hits.length === 2);\n}\n{\n // Regression case for the actual bug this fixes: a visitor whose utm_source\n // changes on every hit (claude_test, claude_test2, claude_test3) must\n // produce one session PER hit, not one session with contradictory\n // attribution.\n const hits = [\n hit({ created_at: \"2026-06-01 12:00:00\", utm_source: \"claude_test\", traffic_source: \"Campaign (claude_test)\" }),\n hit({\n created_at: \"2026-06-01 12:00:08\",\n utm_source: \"claude_test2\",\n traffic_source: \"Campaign (claude_test2)\",\n }),\n hit({\n created_at: \"2026-06-01 12:00:21\",\n utm_source: \"claude_test3\",\n traffic_source: \"Campaign (claude_test3)\",\n }),\n ];\n const sessions = splitIntoSessions(hits);\n check(\"campaign rule: 3 distinct campaigns -> 3 sessions\", sessions.length === 3, `got ${sessions.length}`);\n check(\n \"campaign rule: each session's trafficSource matches its own hit\",\n sessions[0]?.trafficSource === \"Campaign (claude_test)\" &&\n sessions[1]?.trafficSource === \"Campaign (claude_test2)\" &&\n sessions[2]?.trafficSource === \"Campaign (claude_test3)\",\n );\n}\n\n// --- Midnight boundary ----------------------------------------------------\n{\n // Eastern midnight on 2026-01-02 is 05:00:00 UTC (EST, UTC-5, winter).\n // 04:50 UTC = Jan 1 23:50 ET; 05:05 UTC = Jan 2 00:05 ET. 15 minutes apart.\n const hits = [\n hit({ created_at: \"2026-01-02 04:50:00\" }),\n hit({ created_at: \"2026-01-02 05:05:00\" }),\n ];\n const sessions = splitIntoSessions(hits);\n check(\n \"midnight rule: a session spanning Eastern midnight is NOT split\",\n sessions.length === 1,\n `got ${sessions.length}`,\n );\n check(\n \"midnight rule: dayKey attributes to the day of the FIRST hit (Jan 1, not Jan 2)\",\n sessions[0]?.dayKey === \"2026-01-01\",\n `got ${sessions[0]?.dayKey}`,\n );\n}\n\n// --- Anonymous (no visitor_id) hits ---------------------------------------\n{\n const rows: RawHit[] = [\n hit({ visitor_id: null, created_at: \"2026-06-01 12:00:00\" }),\n hit({ visitor_id: null, created_at: \"2026-06-01 12:01:00\" }), // 1 min later, still NOT merged\n ];\n const sessions = buildSessions(rows);\n check(\n \"anonymous hits: two null-visitor hits never merge into one session\",\n sessions.length === 2,\n `got ${sessions.length}`,\n );\n check(\n \"anonymous hits: each is a 1-hit session tagged visitorId null\",\n sessions.every((s) => s.visitorId === null && s.hits.length === 1),\n );\n}\n\n// --- isEngaged -------------------------------------------------------------\n{\n const short = splitIntoSessions([\n hit({ created_at: \"2026-06-01 12:00:00\" }),\n hit({ created_at: \"2026-06-01 12:00:05\" }), // 5s, 1 pageview total... wait this is 2 pageviews\n ]);\n // Two pageviews 5s apart -> engaged via the >=2-pageviews rule regardless of duration.\n check(\"engaged: >=2 pageviews engages even if very short\", isEngaged(short[0]));\n}\n{\n const oneShort = splitIntoSessions([hit({ created_at: \"2026-06-01 12:00:00\" })]);\n check(\"engaged: single pageview, 0 duration, no key event -> NOT engaged\", !isEngaged(oneShort[0]));\n}\n{\n // A single pageview whose session (by construction, one hit only) has\n // durationMs 0 can only become engaged via key events, not duration --\n // duration for a true single-hit session is always 0. Verify a key event\n // on its own engages a session even with 0 duration and 0 pageviews.\n const keyOnly = splitIntoSessions([hit({ created_at: \"2026-06-01 12:00:00\", event: \"outbound_click\" })]);\n check(\n \"engaged: a single key-event hit (0 pageviews, 0 duration) still engages\",\n isEngaged(keyOnly[0]),\n );\n}\n{\n const long = splitIntoSessions([\n hit({ created_at: \"2026-06-01 12:00:00\" }),\n ]);\n check(\n \"engaged: single pageview with 0 duration and no key event is not engaged (sanity)\",\n !isEngaged(long[0]),\n );\n}\n\n// --- summarize -------------------------------------------------------------\n{\n const sessions = [\n ...splitIntoSessions([hit({ created_at: \"2026-06-01 12:00:00\" })]), // not engaged, 1 pv, 0s\n ...splitIntoSessions([\n hit({ visitor_id: \"v2\", created_at: \"2026-06-01 12:00:00\" }),\n hit({ visitor_id: \"v2\", created_at: \"2026-06-01 12:00:20\" }), // engaged: 2 pv\n ]),\n ];\n const stats = summarize(sessions);\n check(\"summarize: sessions count\", stats.sessions === 2, `got ${stats.sessions}`);\n check(\"summarize: engagedSessions count\", stats.engagedSessions === 1, `got ${stats.engagedSessions}`);\n check(\"summarize: engagementRate = 0.5\", stats.engagementRate === 0.5, `got ${stats.engagementRate}`);\n check(\"summarize: bounceRate = 0.5\", stats.bounceRate === 0.5, `got ${stats.bounceRate}`);\n check(\n \"summarize: pageviewsPerSession = (1+2)/2 = 1.5\",\n stats.pageviewsPerSession === 1.5,\n `got ${stats.pageviewsPerSession}`,\n );\n check(\n \"summarize: avgDurationSec = (0+20)/2 = 10\",\n stats.avgDurationSec === 10,\n `got ${stats.avgDurationSec}`,\n );\n}\n\n// --- groupSessionsByChannel -------------------------------------------------\n{\n // Traffic-acquisition style: group by each session's OWN trafficSource.\n const sessions = [\n ...splitIntoSessions([hit({ traffic_source: \"Direct\", created_at: \"2026-06-01 12:00:00\" })]),\n ...splitIntoSessions([\n hit({ visitor_id: \"v2\", traffic_source: \"Paid\", created_at: \"2026-06-01 12:00:00\" }),\n hit({ visitor_id: \"v2\", traffic_source: \"Paid\", created_at: \"2026-06-01 12:00:20\" }), // engaged, 2pv\n ]),\n ...splitIntoSessions([hit({ visitor_id: \"v3\", traffic_source: \"Paid\", created_at: \"2026-06-01 13:00:00\" })]),\n ];\n const rows = groupSessionsByChannel(sessions, (s) => s.trafficSource ?? \"Unknown\");\n check(\"groupByChannel: 2 distinct channels\", rows.length === 2, `got ${rows.length}`);\n check(\n \"groupByChannel: sorted by session count desc (Paid=2 before Direct=1)\",\n rows[0]?.channel === \"Paid\" && rows[0]?.sessions === 2 && rows[1]?.channel === \"Direct\" &&\n rows[1]?.sessions === 1,\n JSON.stringify(rows),\n );\n check(\n \"groupByChannel: Paid bucket engagement rate = 0.5 (1 engaged of 2)\",\n rows[0]?.engagementRate === 0.5,\n `got ${rows[0]?.engagementRate}`,\n );\n check(\n \"groupByChannel: keyEvents summed per bucket (0 here)\",\n rows[0]?.keyEvents === 0 && rows[1]?.keyEvents === 0,\n );\n}\n{\n // User-acquisition style: group by a channel looked up per-visitor (first\n // touch), NOT by the session's own source — simulates a visitor whose\n // first-ever session was Paid later returning via Direct; both sessions\n // must land in the Paid bucket.\n const firstTouchByVisitor = new Map([[\"v1\", \"Paid\"]]);\n const sessions = [\n ...splitIntoSessions([hit({ visitor_id: \"v1\", traffic_source: \"Paid\", created_at: \"2026-06-01 12:00:00\" })]),\n ...splitIntoSessions([\n hit({ visitor_id: \"v1\", traffic_source: \"Direct\", created_at: \"2026-06-02 12:00:00\" }), // later, different source\n ]),\n ];\n const rows = groupSessionsByChannel(sessions, (s) => (s.visitorId ? firstTouchByVisitor.get(s.visitorId)! : \"Unknown\"));\n check(\n \"groupByChannel (first-touch): both sessions credited to Paid, not split by session source\",\n rows.length === 1 && rows[0]?.channel === \"Paid\" && rows[0]?.sessions === 2,\n JSON.stringify(rows),\n );\n}\n{\n const rows = groupSessionsByChannel([], (s) => s.trafficSource ?? \"Unknown\");\n check(\"groupByChannel: empty input -> empty output\", rows.length === 0, `got ${rows.length}`);\n}\n\n// --- site field --------------------------------------------------------\n{\n // A session's site comes from its first hit and is what lets an\n // all-sites view group sessions per-site without a second query (see\n // main.tsx's chart-by-metric switcher) — never mixed across visitors,\n // since visitor_id itself (localStorage) can't span two origins.\n const sessions = splitIntoSessions([\n hit({ site: \"example.com\", created_at: \"2026-06-01 12:00:00\" }),\n hit({ site: \"example.com\", created_at: \"2026-06-01 12:05:00\" }),\n ]);\n check(\"site: carried onto the session from its first hit\", sessions[0]?.site === \"example.com\", sessions[0]?.site);\n}\n{\n const sessions = buildSessions([\n hit({ visitor_id: \"v1\", site: \"a.com\", created_at: \"2026-06-01 12:00:00\" }),\n hit({ visitor_id: \"v2\", site: \"b.com\", created_at: \"2026-06-01 12:00:00\" }),\n ]);\n const bySite = new Set(sessions.map((s) => s.site));\n check(\n \"site: two visitors on two different sites keep distinct site tags\",\n bySite.has(\"a.com\") && bySite.has(\"b.com\") && sessions.length === 2,\n JSON.stringify(sessions.map((s) => s.site)),\n );\n}\n\nconsole.log(failures === 0 ? `\\nPASS — all checks passed` : `\\nFAIL — ${failures} check(s) failed`);\nif (failures > 0) throw new Error(`${failures} sessionization test check(s) failed`);\n",[148],"d119db11-243f-407f-ac7f-02ad7e29a42a","sessions.ts","d6311168-aec3-11f1-a9c2-1607ee4eb77e",163,["D",1789236378607],3,"// Sessionization. Pure functions only — no sqlite import here on purpose,\n// so the entire engine can be unit-tested with fixed fixtures (see\n// sessions_test.ts) without touching the database. db.ts fetches the raw\n// rows (sessionRawHits); main.tsx calls buildSessions on the result.\n//\n// A session is a run of hits from one visitor, split by whichever of these\n// fires first:\n// 1. Gap rule — more than 30 minutes since that visitor's last hit.\n// 2. Campaign rule — this hit's (utm_source, utm_medium, utm_campaign)\n// differs from what the CURRENT SESSION started with (not just the\n// previous hit — a session's attribution is pinned at its first hit,\n// and any drift from that pinned value ends it). This is what\n// actually fixes item 12: a visitor whose UTM params change mid-visit\n// no longer produces one session with contradictory attribution: the\n// change itself closes the session and opens a new, correctly\n// attributed one.\n// 3. Different visitor_id — trivial, since hits are grouped per visitor\n// before splitting.\n//\n// Midnight boundary rule: a session is NOT split at midnight. It is\n// attributed, for day-bucketed reporting, to the Eastern calendar day of\n// its FIRST hit — even if later hits in the same session land after\n// midnight. This matches GA4's current behavior (a session may span\n// midnight); it deliberately does NOT match older Universal Analytics,\n// which force-split at midnight. Stated explicitly because it is a real\n// choice, not the only defensible one — and because every day-bucketed\n// session metric (sessions/day, engagement rate/day, etc.) inherits this\n// rule.\n//\n// A hit with no visitor_id (still ~1 in 5 rows on this network — see item\n// 10) cannot be grouped with anything: two anonymous hits are not\n// necessarily the same visitor, so treating them as one session would\n// fabricate a relationship that isn't there. Each becomes its own\n// single-hit session instead, tagged unknown-visitor, rather than being\n// silently dropped (which would undercount) or silently merged (which\n// would miscount).\nimport { easternDayKey, parseSqliteUtc } from \"./util.ts\";\nimport { KEY_EVENTS } from \"./db.ts\";\n\nexport interface RawHit {\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\nexport interface Session {\n visitorId: string | null; // null => unknown-visitor singleton session\n hits: RawHit[];\n startedAt: number; // epoch ms, first hit\n endedAt: number; // epoch ms, last hit\n durationMs: number;\n pageviews: number;\n keyEvents: number;\n trafficSource: string | null; // from the session's first hit\n dayKey: string; // Eastern day of the session's first hit\n // From the session's first hit, like trafficSource. Safe to treat as\n // constant across a whole session: visitor_id comes from localStorage,\n // which is per-origin, so one visitor_id can never span two sites'\n // hits — a session's site never actually changes hit-to-hit.\n site: string;\n}\n\nexport const SESSION_GAP_MS = 30 * 60 * 1000;\nexport const ENGAGED_MIN_DURATION_MS = 10 * 1000;\n\nconst KEY_EVENT_SET = new Set(KEY_EVENTS);\n\nfunction campaignKey(h: RawHit): string {\n return `${h.utm_source ?? \"\"} ${h.utm_medium ?? \"\"} ${h.utm_campaign ?? \"\"}`;\n}\n\nfunction finishSession(hits: RawHit[]): Session {\n const first = hits[0];\n const last = hits[hits.length - 1];\n const startedAt = parseSqliteUtc(first.created_at).getTime();\n const endedAt = parseSqliteUtc(last.created_at).getTime();\n let pageviews = 0, keyEvents = 0;\n for (const h of hits) {\n if (h.event === \"pageview\") pageviews++;\n else if (KEY_EVENT_SET.has(h.event)) keyEvents++;\n }\n return {\n visitorId: first.visitor_id,\n hits,\n startedAt,\n endedAt,\n durationMs: endedAt - startedAt,\n pageviews,\n keyEvents,\n trafficSource: first.traffic_source,\n dayKey: easternDayKey(new Date(startedAt)),\n site: first.site,\n };\n}\n\n// Splits ONE visitor's hits (already in chronological order) into sessions\n// by the gap and campaign rules. Exported on its own — separate from the\n// grouping/anonymous-handling in buildSessions — specifically so tests can\n// feed it a single visitor's hit sequence directly.\nexport function splitIntoSessions(hits: RawHit[]): Session[] {\n const out: Session[] = [];\n let bucket: RawHit[] = [];\n let bucketCampaign = \"\";\n let lastMs = 0;\n for (const h of hits) {\n const ms = parseSqliteUtc(h.created_at).getTime();\n const gapExceeded = bucket.length > 0 && ms - lastMs > SESSION_GAP_MS;\n const campaignChanged = bucket.length > 0 && campaignKey(h) !== bucketCampaign;\n if (bucket.length === 0 || gapExceeded || campaignChanged) {\n if (bucket.length) out.push(finishSession(bucket));\n bucket = [h];\n bucketCampaign = campaignKey(h);\n } else {\n bucket.push(h);\n }\n lastMs = ms;\n }\n if (bucket.length) out.push(finishSession(bucket));\n return out;\n}\n\n// Groups raw rows (any order) by visitor_id, then splits each visitor's\n// hits into sessions. Rows are sorted here by created_at so\n// splitIntoSessions can assume chronological order regardless of what\n// order the caller's query returned them in.\nexport function buildSessions(rows: RawHit[]): Session[] {\n const byVisitor = new Map();\n const anonymous: RawHit[] = [];\n for (const row of rows) {\n if (row.visitor_id === null) {\n anonymous.push(row);\n continue;\n }\n if (!byVisitor.has(row.visitor_id)) byVisitor.set(row.visitor_id, []);\n byVisitor.get(row.visitor_id)!.push(row);\n }\n const sessions: Session[] = [];\n for (const hits of byVisitor.values()) {\n hits.sort((a, b) => parseSqliteUtc(a.created_at).getTime() - parseSqliteUtc(b.created_at).getTime());\n sessions.push(...splitIntoSessions(hits));\n }\n for (const row of anonymous) {\n sessions.push(finishSession([row]));\n }\n return sessions;\n}\n\n// GA4's definition, exactly: a session lasting >10s, OR with >=2 pageviews,\n// OR with >=1 key event. Encoded once — engagement rate, bounce rate\n// (1 - engagementRate), and \"engaged sessions per user\" all derive from\n// this single predicate rather than each re-deriving their own notion of\n// \"real\" traffic.\nexport function isEngaged(s: Session): boolean {\n return s.durationMs > ENGAGED_MIN_DURATION_MS || s.pageviews >= 2 || s.keyEvents >= 1;\n}\n\nexport interface SessionStats {\n sessions: number;\n engagedSessions: number;\n engagementRate: number; // 0..1\n bounceRate: number; // 1 - engagementRate\n avgDurationSec: number;\n pageviewsPerSession: number;\n}\n\nexport function summarize(sessions: Session[]): SessionStats {\n const total = sessions.length;\n if (total === 0) {\n return {\n sessions: 0,\n engagedSessions: 0,\n engagementRate: 0,\n bounceRate: 0,\n avgDurationSec: 0,\n pageviewsPerSession: 0,\n };\n }\n let engaged = 0, durationSum = 0, pageviewSum = 0;\n for (const s of sessions) {\n if (isEngaged(s)) engaged++;\n durationSum += s.durationMs;\n pageviewSum += s.pageviews;\n }\n const engagementRate = engaged / total;\n return {\n sessions: total,\n engagedSessions: engaged,\n engagementRate,\n bounceRate: 1 - engagementRate,\n avgDurationSec: durationSum / total / 1000,\n pageviewsPerSession: pageviewSum / total,\n };\n}\n\nexport interface ChannelSessionStats extends SessionStats {\n channel: string;\n keyEvents: number;\n}\n\n// Buckets sessions by whatever channel key `channelOf` returns, then runs\n// `summarize` per bucket. Used two ways from main.tsx with the SAME session\n// list (no second DB query needed):\n// - Traffic acquisition (GA4): channelOf = session's OWN traffic_source\n// (the channel that started THIS session).\n// - User acquisition (GA4): channelOf = the session's visitor's\n// FIRST-EVER-TOUCH channel (looked up separately, see firstTouchChannels\n// in db.ts) — every session a visitor ever has is credited to whatever\n// channel originally brought them in, not to each session's own source.\n// Sorted by session count descending so the biggest channel reads first,\n// same convention as every other ranked table in this file/main.tsx.\nexport function groupSessionsByChannel(\n sessions: Session[],\n channelOf: (s: Session) => string,\n): ChannelSessionStats[] {\n const byChannel = new Map();\n for (const s of sessions) {\n const ch = channelOf(s);\n if (!byChannel.has(ch)) byChannel.set(ch, []);\n byChannel.get(ch)!.push(s);\n }\n const rows: ChannelSessionStats[] = [];\n for (const [channel, list] of byChannel) {\n const stats = summarize(list);\n const keyEvents = list.reduce((sum, s) => sum + s.keyEvents, 0);\n rows.push({ channel, keyEvents, ...stats });\n }\n return rows.sort((a, b) => b.sessions - a.sessions);\n}\n",[155],"a697fc35-a1ce-41bd-a29c-04a515009b9a",["D",1789237497337],140,[28],"4ce3cd94-c510-4e63-b32d-80e53a387893","db.ts","2c6db772-ae27-11f1-a5ee-1607ee4eb77e",181,["D",1789237428848],31,"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",[167],"pulls","branches",2,"forks","envVars",{"_182":184,"_185":62},"connections",{"_182":62,"_183":62},"count","unfilledCount",1,"emptyCount",{"_43":166,"_32":167,"_30":168,"_138":-5,"_41":42,"_40":169,"_140":58,"_38":204,"_13":171,"_33":143,"_35":172,"_24":25,"_145":-5,"_192":205,"_27":206},{"_43":162,"_32":28,"_30":31,"_138":-5,"_41":42,"_40":14,"_140":25,"_38":201,"_13":164,"_33":34,"_35":37,"_24":25,"_145":-5,"_192":202,"_27":203},{"_43":154,"_32":155,"_30":156,"_138":-5,"_41":42,"_40":157,"_140":58,"_38":198,"_13":159,"_33":143,"_35":160,"_24":25,"_145":-5,"_192":199,"_27":200},{"_43":147,"_32":148,"_30":149,"_138":-5,"_41":42,"_40":150,"_140":58,"_38":195,"_13":142,"_33":143,"_35":152,"_24":25,"_145":-5,"_192":196,"_27":197},{"_43":135,"_32":136,"_30":137,"_138":-5,"_41":42,"_40":139,"_140":58,"_38":191,"_13":142,"_33":143,"_35":144,"_24":25,"_145":-5,"_192":193,"_27":194},["D",1789237333126],"children",[],[136],["D",1789236392016],[],[148],["D",1789236378607],[],[155],["D",1789237497337],[],[28],["D",1789237428848],[],[167],["D",1789161858464],["D",1789516253896],{"_99":98,"_107":103},{"_99":98,"_100":101,"_102":103,"_18":55,"_104":105,"_106":25},{"_13":88,"_32":89,"_43":42,"_90":44,"_91":-5,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":45,"_97":98},"ctx",{"_81":-5,"_127":245,"_246":247,"_248":249,"_250":25,"_251":25,"_252":25,"_253":58,"_254":-5,"_255":-7,"_256":-7,"_257":62,"_258":25,"_259":25,"_260":-5,"_261":25,"_262":25,"_263":25,"_264":-5},"ENV",{"_216":217,"_218":219,"_220":89,"_221":222,"_223":224,"_225":226,"_227":228,"_229":230,"_231":232,"_233":234,"_235":236,"_237":238,"_239":240,"_241":242,"_243":244},"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 · v186