[{"_1":2,"_180":-5,"_181":-5},"loaderData",{"_3":4,"_95":96},"root",{"_5":6,"_36":37,"_68":69},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":-5,"_32":33,"_34":20,"_35":20},"auth",{"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20,"_21":22},"sessionClaims","sessionId","sessionStatus","userId","actor","orgId","orgRole","orgSlug","orgPermissions","factorVerificationAge","isAuthenticated",false,"tokenType","session_token","user","preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesChatPanelState","notificationCount",0,"flagDenols","isBusinessOrgMember","ENV",{"_38":39,"_40":41,"_42":43,"_44":45,"_46":47,"_48":49,"_50":51,"_52":53,"_54":55,"_56":57,"_58":59,"_60":61,"_62":63,"_64":65,"_66":67},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","c82f8f7f511b45da3b6daf08ba8af1c21e3c5623","RENDER_GIT_BRANCH","main","CLERK_PUBLISHABLE_KEY","pk_live_Y2xlcmsudmFsLnRvd24k","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","API_URL","https://api.val.town","clerkState",{"_70":71},"__internal_clerk_state",{"_72":73,"_74":47,"_75":76,"_77":76,"_78":20,"_79":76,"_80":76,"_81":76,"_82":76,"_83":84,"_93":20,"_94":20},"__clerk_ssr_state",{"_21":22,"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20},"__publishableKey","__proxyUrl","","__domain","__isSatellite","__signInUrl","__signUpUrl","__afterSignInUrl","__afterSignUpUrl","__clerk_debug",{"_85":20,"_19":20,"_86":76,"_87":88,"_89":76,"_90":47,"_91":20,"_92":76},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_97":98,"_99":100,"_101":102,"_103":104,"_157":158,"_177":178,"_179":-5},"handle","c15r","name","testler","path","db.ts","project",{"_105":106,"_99":100,"_107":108,"_109":-5,"_110":20,"_111":112,"_113":114,"_115":-5,"_12":116,"_117":-5,"_118":20,"_119":-5,"_120":-5,"_121":33,"_14":122,"_123":-5,"_124":-5,"_125":-5,"_126":127,"_128":127,"_129":130,"_131":130,"_132":130,"_133":127,"_134":127,"_23":135,"_138":139,"_143":144,"_156":20},"id","9c66b236-2cf9-11f1-97d2-42dde27851f2","createdAt",["D",1774958600763],"deletedAt","legacyEnvVars","privacy","public","description","Zero-config behavioral flow discovery engine","imageUrl","21f2e37e-8066-4730-9dc7-d27cf8d3c9f3","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount","0630d977-f678-453e-926c-52043293f59f","tursoName","tursoHostname","pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_97":98,"_136":137},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yb2RJNlc1czZ1NlpZRmoxdGJ2eFVNNVhmeTYifQ","org",{"_97":98,"_140":137,"_12":116,"_141":142},"avatar","tier","pro","branch",{"_145":146,"_99":45,"_105":147,"_148":106,"_149":-5,"_150":-5,"_151":-5,"_152":-5,"_153":-5,"_154":100,"_155":98},"version",59,"9c66f08e-2cf9-11f1-97d2-42dde27851f2","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_105":159,"_99":102,"_160":161,"_162":-5,"_163":147,"_164":165,"_166":167,"_107":168,"_145":169,"_170":171,"_172":173,"_174":20,"_175":-5,"_101":176},"e8396588-2d1b-11f1-9329-42dde27851f2","fileId","a60ac50c-2cf9-11f1-bf3d-42dde27851f2","parentId","branchId","branchVersion",45,"latest",true,["D",1774973330853],4,"fileType","script","content","import { sqlite } from \"https://esm.town/v/std/sqlite\";\n\nexport async function ensureSchema() {\n await sqlite.execute(\"CREATE TABLE IF NOT EXISTS events (id INTEGER PRIMARY KEY AUTOINCREMENT, site TEXT NOT NULL, session_id TEXT NOT NULL, timestamp INTEGER NOT NULL, event_type TEXT NOT NULL, url TEXT NOT NULL, path TEXT NOT NULL, domain TEXT, selector TEXT, semantic TEXT, element_type TEXT, element_text TEXT, meta TEXT, batch_id TEXT)\");\n await sqlite.execute(\"CREATE INDEX IF NOT EXISTS idx_events_site_session ON events(site, session_id)\");\n await sqlite.execute(\"CREATE INDEX IF NOT EXISTS idx_events_site_ts ON events(site, timestamp)\");\n await sqlite.execute(\"CREATE TABLE IF NOT EXISTS sites (id TEXT PRIMARY KEY, name TEXT NOT NULL, created_at INTEGER NOT NULL DEFAULT (unixepoch()), url_patterns TEXT DEFAULT '[]', public_key TEXT)\");\n await sqlite.execute(\"CREATE TABLE IF NOT EXISTS encrypted_batches (id TEXT PRIMARY KEY, site TEXT NOT NULL, session_id TEXT NOT NULL, ciphertext TEXT NOT NULL, iv TEXT NOT NULL, wrapped_key TEXT NOT NULL, event_count INTEGER NOT NULL, created_at INTEGER NOT NULL DEFAULT (unixepoch()))\");\n await sqlite.execute(\"CREATE INDEX IF NOT EXISTS idx_batches_site ON encrypted_batches(site)\");\n // Migrations\n try { await sqlite.execute(\"ALTER TABLE events ADD COLUMN domain TEXT\"); } catch (_) {}\n try { await sqlite.execute(\"ALTER TABLE events ADD COLUMN batch_id TEXT\"); } catch (_) {}\n try { await sqlite.execute(\"ALTER TABLE sites ADD COLUMN public_key TEXT\"); } catch (_) {}\n}\n\nexport interface RawEvent {\n site: string;\n session_id: string;\n timestamp: number;\n event_type: string;\n url: string;\n path: string;\n domain?: string;\n selector?: string;\n semantic?: string;\n element_type?: string;\n element_text?: string;\n meta?: Record;\n}\n\nexport interface EncryptedBatch {\n ciphertext: string;\n iv: string;\n wrappedKey: string;\n}\n\nexport async function insertEvents(events: RawEvent[], batchId?: string): Promise {\n if (events.length === 0) return [];\n const ids: number[] = [];\n for (const e of events) {\n let domain = e.domain;\n if (!domain && e.url) {\n try { domain = new URL(e.url).hostname; } catch (_) {}\n }\n const result = await sqlite.execute({\n sql: \"INSERT INTO events (site, session_id, timestamp, event_type, url, path, domain, selector, semantic, element_type, element_text, meta, batch_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\",\n args: [e.site, e.session_id, e.timestamp, e.event_type, e.url, e.path, domain ?? null, e.selector ?? null, e.semantic ?? null, e.element_type ?? null, e.element_text ?? null, e.meta ? JSON.stringify(e.meta) : null, batchId ?? null],\n });\n if (result.lastInsertRowid) ids.push(Number(result.lastInsertRowid));\n }\n return ids;\n}\n\nexport async function insertEncryptedBatch(id: string, site: string, sessionId: string, envelope: EncryptedBatch, eventCount: number) {\n await sqlite.execute({\n sql: \"INSERT INTO encrypted_batches (id, site, session_id, ciphertext, iv, wrapped_key, event_count) VALUES (?, ?, ?, ?, ?, ?, ?)\",\n args: [id, site, sessionId, envelope.ciphertext, envelope.iv, envelope.wrappedKey, eventCount],\n });\n}\n\nexport interface StoredEvent {\n id: number;\n site: string;\n session_id: string;\n timestamp: number;\n event_type: string;\n url: string;\n path: string;\n domain: string | null;\n selector: string | null;\n semantic: string | null;\n element_type: string | null;\n element_text: string | null;\n meta: string | null;\n batch_id: string | null;\n}\n\nfunction mapRows(result: { columns: string[]; rows: unknown[][] }): Record[] {\n const cols = result.columns;\n return result.rows.map((row) => {\n const obj: Record = {};\n for (let i = 0; i < cols.length; i++) {\n obj[cols[i]] = row[i];\n }\n return obj;\n });\n}\n\nexport async function getSessionEvents(site: string, since?: number, until?: number): Promise {\n const conditions = [\"site = ?\"];\n const args: (string | number)[] = [site];\n if (since) { conditions.push(\"timestamp >= ?\"); args.push(since); }\n if (until) { conditions.push(\"timestamp <= ?\"); args.push(until); }\n const result = await sqlite.execute({\n sql: \"SELECT * FROM events WHERE \" + conditions.join(\" AND \") + \" ORDER BY session_id, timestamp\",\n args,\n });\n return mapRows(result) as unknown as StoredEvent[];\n}\n\nexport async function getEncryptedBatches(site: string): Promise> {\n const result = await sqlite.execute({\n sql: \"SELECT id, session_id, ciphertext, iv, wrapped_key, event_count FROM encrypted_batches WHERE site = ? ORDER BY created_at\",\n args: [site],\n });\n return mapRows(result) as unknown as Array<{ id: string; session_id: string; ciphertext: string; iv: string; wrapped_key: string; event_count: number }>;\n}\n\nexport async function getEventIdsByBatch(batchId: string): Promise {\n const result = await sqlite.execute({\n sql: \"SELECT id FROM events WHERE batch_id = ? ORDER BY timestamp\",\n args: [batchId],\n });\n return mapRows(result).map((r) => r.id as number);\n}\n\nexport async function getDomains(site: string): Promise> {\n const result = await sqlite.execute({\n sql: \"SELECT domain, COUNT(DISTINCT session_id) as count FROM events WHERE site = ? AND domain IS NOT NULL GROUP BY domain ORDER BY count DESC\",\n args: [site],\n });\n return mapRows(result) as unknown as Array<{ domain: string; count: number }>;\n}\n\nexport async function createSite(id: string, name: string, publicKeyJwk?: string) {\n await sqlite.execute({\n sql: \"INSERT OR REPLACE INTO sites (id, name, public_key) VALUES (?, ?, ?)\",\n args: [id, name, publicKeyJwk ?? null],\n });\n}\n\nexport async function ensureSite(id: string, name: string) {\n await sqlite.execute({ sql: \"INSERT OR IGNORE INTO sites (id, name) VALUES (?, ?)\", args: [id, name] });\n}\n\nexport async function getSite(id: string): Promise<{ id: string; name: string; public_key: string | null } | null> {\n const result = await sqlite.execute({ sql: \"SELECT * FROM sites WHERE id = ?\", args: [id] });\n const rows = mapRows(result);\n return rows.length > 0 ? rows[0] as unknown as { id: string; name: string; public_key: string | null } : null;\n}\n\nexport async function getSites() {\n const result = await sqlite.execute(\"SELECT * FROM sites ORDER BY created_at DESC\");\n return mapRows(result);\n}\n\nexport async function getSiteStats(site: string) {\n const result = await sqlite.execute({\n sql: \"SELECT COUNT(*) as total_events, COUNT(DISTINCT session_id) as total_sessions, COUNT(DISTINCT domain) as total_domains FROM events WHERE site = ?\",\n args: [site],\n });\n return mapRows(result)[0] as { total_events: number; total_sessions: number; total_domains: number };\n}\n","deleted","label",[102],"permalink","https://www.val.town/x/c15r/testler/code/db.ts","extension","actionData","errors"]