[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8},"actionData","errors","root",{"_107":108,"_109":110},"routes/embed.x.$handle.$name.$",{"_9":10,"_11":12,"_13":14,"_15":16,"_17":18,"_19":20,"_21":-5},"handle","Tribeca_Rep","name","tribeca-chat","path","db.ts","project",{"_22":45,"_11":12,"_33":46,"_47":-5,"_48":42,"_49":50,"_51":50,"_52":53,"_54":-5,"_55":56,"_57":58,"_59":32,"_60":-5,"_61":-5,"_62":63,"_64":65,"_66":67,"_68":69,"_70":71,"_72":-5,"_73":74,"_75":74,"_76":77,"_78":77,"_79":77,"_80":74,"_81":74,"_82":83,"_84":85,"_86":87,"_88":42},"file",{"_22":23,"_11":14,"_24":25,"_26":-5,"_27":28,"_29":30,"_31":32,"_33":34,"_35":36,"_37":38,"_39":40,"_41":42,"_43":-5,"_13":44},"permalink","https://www.val.town/x/Tribeca_Rep/tribeca-chat/code/db.ts","extension","id","0837a0a7-67fa-4dd3-8ddb-c634688483d6","fileId","fcd6841a-a5a4-11f1-abb7-1607ee4eb77e","parentId","branchId","7f3f9c05-d6ad-413b-a869-bc1a5f60764f","branchVersion",661,"latest",true,"createdAt",["D",1788796634372],"version",103,"fileType","script","content","// Tribeca Chat — storage layer\n//\n// Built to replacement spec for tawk.to, run in parallel until the team trusts\n// the notifications. See conversations.status for the AI/human handoff model.\n//\n// Val-scoped SQLite. Every write goes through here so the SSE fan-out in\n// main.ts has exactly one place to hook.\n\nimport { sqlite as rawSqlite } from \"https://esm.town/v/std/sqlite\";\n\n/**\n * RETRY A PLATFORM RATE LIMIT, NOT A REAL ERROR.\n *\n * Val Town's own sqlite service throttles at 1,000 authenticated requests\n * per minute per account. Confirmed live 9/4/2026: a burst of traffic hit\n * that ceiling and every call in this file started throwing a 429 for about\n * ten seconds straight — session start, presence, agent login, all of it,\n * for every visitor and every agent, not just one. The Anthropic side\n * already gets one retry for exactly this shape of transient failure; the\n * database deserves the same rather than a raw 500 reaching a customer over\n * something that clears itself in a few seconds.\n *\n * Retries up to twice, ONLY for a 429 — any other error (a real syntax\n * mistake, a missing table) is rethrown on the first try, because retrying\n * an actual bug three times only delays finding out about it. The error body\n * names its own cooldown (\"Retry again in N seconds\"); that is used instead\n * of guessing, capped so one slow platform window cannot turn into a visitor\n * waiting ten seconds for a heartbeat ping to come back.\n */\nasync function withRetry(fn: () => Promise): Promise {\n for (let attempt = 0; ; attempt++) {\n try {\n return await fn();\n } catch (err) {\n const msg = String((err as any)?.message ?? err);\n const is429 = msg.includes(\"statusCode\\\":429\") || msg.includes(\"Too Many Requests\");\n if (!is429 || attempt >= 2) throw err;\n const m = msg.match(/Retry again in (\\d+) seconds?/);\n const waitMs = Math.min(5000, (m ? Number(m[1]) : attempt + 1) * 1000 + 200);\n await new Promise((r) => setTimeout(r, waitMs));\n }\n }\n}\n\nconst sqlite = {\n execute: (opts: any) => withRetry(() => rawSqlite.execute(opts)),\n batch: (stmts: any) => withRetry(() => rawSqlite.batch(stmts)),\n};\n\n// Bump when the shape changes; init() is safe to call on every request.\nconst SCHEMA_VERSION = 1;\n\nlet ready: Promise | null = null;\n\nexport function init(): Promise {\n // Memoised, not because the CREATEs are expensive, but because Val Town can\n // serve several requests from one isolate and re-running them per request\n // shows up as latency on the visitor's first message.\n if (ready) return ready;\n\n ready = (async () => {\n await sqlite.batch([\n `CREATE TABLE IF NOT EXISTS visitors (\n id TEXT PRIMARY KEY,\n name TEXT,\n email TEXT,\n phone TEXT,\n zip TEXT,\n company TEXT,\n wp_user_id TEXT,\n -- Salesforce answers are cached on the visitor, not looked up per\n -- message. The lookup is two SOQL queries behind a Val Town cold\n -- start; doing it per turn would be felt by the customer.\n sf_account TEXT,\n sf_next_delivery TEXT,\n sf_checked_at INTEGER,\n first_seen INTEGER NOT NULL,\n last_seen INTEGER NOT NULL,\n page_url TEXT,\n user_agent TEXT,\n country TEXT\n )`,\n\n `CREATE TABLE IF NOT EXISTS conversations (\n id TEXT PRIMARY KEY,\n visitor_id TEXT NOT NULL,\n -- ai AI is answering\n -- needs_human handoff requested, nobody has claimed it yet\n -- human an agent has taken over; the AI must stay silent\n -- closed ended\n status TEXT NOT NULL DEFAULT 'ai',\n assigned_to TEXT,\n -- Set when status first becomes needs_human, so we can measure how\n -- long customers actually wait. That number is the whole argument for\n -- or against cutting over from tawk.\n handoff_at INTEGER,\n claimed_at INTEGER,\n created_at INTEGER NOT NULL,\n last_message_at INTEGER NOT NULL,\n FOREIGN KEY (visitor_id) REFERENCES visitors(id)\n )`,\n\n `CREATE TABLE IF NOT EXISTS messages (\n id TEXT PRIMARY KEY,\n conversation_id TEXT NOT NULL,\n -- visitor | ai | agent | system\n sender TEXT NOT NULL,\n -- Display name for agent messages; null for visitor/ai/system.\n sender_name TEXT,\n body TEXT NOT NULL,\n created_at INTEGER NOT NULL,\n FOREIGN KEY (conversation_id) REFERENCES conversations(id)\n )`,\n\n // The dashboard's default view is \"conversations needing attention,\n // newest first\", and the visitor stream polls by conversation. Both are\n // hot paths on every keystroke, so they get indexes from day one.\n `CREATE INDEX IF NOT EXISTS idx_msg_conv ON messages(conversation_id, created_at)`,\n `CREATE INDEX IF NOT EXISTS idx_conv_status ON conversations(status, last_message_at DESC)`,\n `CREATE INDEX IF NOT EXISTS idx_conv_visitor ON conversations(visitor_id)`,\n\n `CREATE TABLE IF NOT EXISTS meta (k TEXT PRIMARY KEY, v TEXT)`,\n\n // WHAT THE VISITOR DID, as opposed to what they said.\n //\n // A SEPARATE TABLE FROM messages, AND THAT IS THE WHOLE POINT.\n // A 'system' message would have been less code, but system messages are\n // rendered in the widget — the customer would watch us narrate their own\n // browsing back at them, which is the single creepiest thing this\n // product could do. Events live here instead, are returned only by the\n // agent endpoints, and have no path to the visitor's screen at all.\n // That is a structural guarantee rather than a rule someone has to\n // remember when writing the next endpoint.\n //\n // page_url is overwritten on every heartbeat, so before this table the\n // sequence a visitor moved through was destroyed as it happened: the\n // console could say \"7 pages viewed\" but never which seven.\n `CREATE TABLE IF NOT EXISTS visitor_events (\n id TEXT PRIMARY KEY,\n visitor_id TEXT NOT NULL,\n kind TEXT NOT NULL,\n detail TEXT,\n created_at INTEGER NOT NULL\n )`,\n // Ordered lookup per visitor — the only query this table ever serves.\n `CREATE INDEX IF NOT EXISTS idx_events_visitor\n ON visitor_events(visitor_id, created_at)`,\n\n // ONE CREDENTIAL PER PERSON, instead of one shared key for everybody.\n //\n // The console can read every visitor's name, email, phone and transcript,\n // and it can send messages to customers AS TRIBECA. A single shared\n // secret guarding that has three failures that only show up later: there\n // is no answer to \"who sent that\", one person leaving means rotating for\n // everyone, and shared secrets end up pasted into WhatsApp. None of those\n // are fixed by making the secret longer.\n //\n // THE RAW TOKEN IS NEVER STORED. Only its SHA-256 lands here, so this\n // table leaking does not hand anyone a working login — the same reason\n // passwords are not stored either. It is shown once, at creation, and\n // cannot be recovered afterwards; a lost token is replaced, not looked up.\n `CREATE TABLE IF NOT EXISTS agent_tokens (\n id TEXT PRIMARY KEY,\n person TEXT NOT NULL,\n token_hash TEXT NOT NULL UNIQUE,\n created_at INTEGER NOT NULL,\n last_used_at INTEGER,\n -- Set rather than deleted, so a revoked token stays visible in the\n -- list. \"This token was revoked on the 4th\" is a fact worth keeping;\n -- a row that simply vanishes tells nobody anything.\n revoked_at INTEGER\n )`,\n `CREATE INDEX IF NOT EXISTS idx_token_hash ON agent_tokens(token_hash)`,\n\n // WHERE TO PUSH A NOTIFICATION. One row per installed phone.\n //\n // The endpoint is a URL the browser's push service handed us — Apple's\n // for an iPhone, Google's for Android. It IS the credential: anyone\n // holding it can push to that device, which is why it is only ever\n // written by the signed-in owner and never handed back out.\n //\n // Tied to a token_id, so revoking somebody's access takes their phone's\n // notifications with it rather than leaving a device buzzing with\n // customer names after they have left.\n `CREATE TABLE IF NOT EXISTS push_subs (\n id TEXT PRIMARY KEY,\n token_id TEXT NOT NULL,\n endpoint TEXT NOT NULL UNIQUE,\n created_at INTEGER NOT NULL,\n last_ok_at INTEGER,\n -- A push service returns 404/410 once an app is uninstalled or the\n -- subscription lapses. Counting failures lets a dead device be dropped\n -- instead of being retried on every conversation for ever.\n fails INTEGER DEFAULT 0\n )`,\n ]);\n\n // Additive migrations. CREATE TABLE IF NOT EXISTS does nothing to a table\n // that already exists, so each new column is added on its own and its\n // \"duplicate column\" error swallowed. Cheaper than a migration framework\n // for a handful of columns, and safe to run on every cold start.\n for (const stmt of [\n `ALTER TABLE visitors ADD COLUMN referrer TEXT`,\n `ALTER TABLE visitors ADD COLUMN landing_url TEXT`,\n `ALTER TABLE visitors ADD COLUMN visits INTEGER DEFAULT 1`,\n `ALTER TABLE visitors ADD COLUMN session_started INTEGER`,\n // Coarse device, derived from the User-Agent and stored INSTEAD of it.\n // The raw UA string is a fingerprint we have no use for; \"Windows\" and\n // \"Chrome\" is the entire operational value of it.\n `ALTER TABLE visitors ADD COLUMN os TEXT`,\n `ALTER TABLE visitors ADD COLUMN browser TEXT`,\n `ALTER TABLE visitors ADD COLUMN page_views INTEGER DEFAULT 0`,\n `ALTER TABLE visitors ADD COLUMN time_on_site_ms INTEGER DEFAULT 0`,\n // Set when the browser sent Global Privacy Control or Do Not Track. A\n // stored flag, so it is auditable rather than merely honoured in passing.\n `ALTER TABLE visitors ADD COLUMN dnt INTEGER DEFAULT 0`,\n // Stamped when the idle sweep has nudged a conversation. Its only job is\n // to make \"at most once, ever\" a property of the data rather than\n // something the sweep has to remember.\n `ALTER TABLE conversations ADD COLUMN nudged_at INTEGER`,\n // Is the panel open on their screen right now, or have they minimised it?\n // An agent deciding whether to say something needs to know whether anyone\n // is actually looking at the window.\n `ALTER TABLE visitors ADD COLUMN chat_open INTEGER DEFAULT 0`,\n // WHAT THE VISITOR WAS ACTUALLY SHOWN. The opener and the page-specific\n // follow-up are rendered in the browser and never stored as messages, so\n // the console displayed an empty thread for someone who had been spoken\n // to twice. Recorded here so an agent can see what they are replying to.\n `ALTER TABLE visitors ADD COLUMN shown_opener TEXT`,\n `ALTER TABLE visitors ADD COLUMN shown_nudge TEXT`,\n // WHICH OPENER VARIANT THIS VISITOR WAS ASSIGNED. shown_opener already\n // holds the words, so this looks redundant — it is not. Grouping a split\n // test on prose means the first typo fix silently splits one variant into\n // two rows and severs the history either side of the edit. A short id\n // survives rewording, which a test meant to run for weeks needs.\n `ALTER TABLE visitors ADD COLUMN opener_variant TEXT`,\n // WHEN THE BROWSER SAID IT WAS LEAVING, as opposed to when we last heard\n // from it. Before this, \"gone\" could only ever be a guess from a missing\n // heartbeat, and a missing heartbeat is also what switching tabs, locking\n // a phone, or a throttled timer looks like. A pagehide beacon is the one\n // moment the browser will actually tell us. Cleared by the next ping, so\n // clicking through to another page on the site does not read as leaving.\n `ALTER TABLE visitors ADD COLUMN left_at INTEGER`,\n // THE VISITOR CLOSED THE CHAT, so the widget will not open itself at them\n // again for a week. It lives in their localStorage, which means the\n // console had no way to know — and \"browsing, never contacted\" looked\n // identical whether we had failed to speak to someone or they had told us\n // to stop. Reported on the heartbeat so the reason is on the row.\n `ALTER TABLE visitors ADD COLUMN chat_dismissed INTEGER DEFAULT 0`,\n // How many times we have asked this conversation for a name or a number\n // after a handoff. Capped, because the one thing worse than not getting\n // a callback number is a bot asking a third time for something the\n // person has decided not to give.\n `ALTER TABLE conversations ADD COLUMN contact_asks INTEGER DEFAULT 0`,\n // DID THE PAGE THEY ARRIVED ON ACTUALLY EXIST?\n //\n // A real customer on 9/2/2026 landed on /my-account/invoices/, which is a\n // 404, went to the login screen, and was still not signed in three page\n // views later. The console showed \"Anonymous, browsing\" — every fact\n // needed to read that as \"customer locked out\" was on the row already and\n // none of it was legible.\n //\n // Reported by the widget rather than guessed here: it is running inside\n // the page and can simply read WordPress's own `error404` body class.\n // Nothing else can know it — the console cannot fetch the URL to check,\n // and a list of known-dead paths would be stale within a week.\n //\n // Deliberately nullable, not DEFAULT 0, so COALESCE keeps the FIRST\n // answer — which is the landing page, the one worth knowing about.\n `ALTER TABLE visitors ADD COLUMN landing_404 INTEGER`,\n // WHICH CHAT THIS VISITOR WAS ACTUALLY SHOWN — 'ours' or 'tawk'.\n //\n // In split mode the site assigns each visitor one of the two and they\n // keep it. Our widget reports presence in BOTH arms (it loads either way\n // and simply renders nothing for the tawk half), which is the whole\n // reason the comparison is possible from this side at all: tawk's own\n // dashboard can tell you how many people talked to tawk, but only this\n // can tell you how many were OFFERED it and did not.\n //\n // COALESCE on write: the first answer is the assignment, and it is\n // sticky by design. A later NULL from an older cached widget build must\n // not erase it.\n `ALTER TABLE visitors ADD COLUMN chat_arm TEXT`,\n // WHAT THE TAWK HALF ACTUALLY DID.\n //\n // Without these the split is only half measurable: this database knows\n // how many people were OFFERED tawk but nothing about whether they used\n // it, so the tawk arm's engagement reads 0 by construction and the two\n // arms cannot be compared on the same numerator.\n //\n // Tawk exposes its own callbacks (onChatMaximized, onChatMessageVisitor),\n // and in split mode WE inject tawk, so we can attach to them before it\n // loads. That puts both arms' engagement in one table, measured the same\n // way, instead of asking someone to reconcile two dashboards by eye.\n `ALTER TABLE visitors ADD COLUMN tawk_opened_at INTEGER`,\n `ALTER TABLE visitors ADD COLUMN tawk_messaged_at INTEGER`,\n // DID THIS VISITOR ACTUALLY BUY, AND FOR HOW MUCH.\n //\n // Engagement is a proxy. The question worth answering about two chat\n // widgets is which one sold more water, and until now nothing here could\n // answer it: the checkout snippet identified who someone WAS and threw\n // away what they had just bought.\n //\n // FIRST ORDER ONLY, by COALESCE. A returning customer's second order is\n // not attributable to the chat they saw on their first visit, and\n // counting it would flatter whichever arm happened to catch the regulars.\n `ALTER TABLE visitors ADD COLUMN order_id TEXT`,\n `ALTER TABLE visitors ADD COLUMN order_total REAL`,\n `ALTER TABLE visitors ADD COLUMN order_at INTEGER`,\n // A LEAD, NOT AN ACCOUNT. Someone who filled out the website popup gets\n // a real Salesforce Lead with no link back to this visitor row at all —\n // confirmed 9/3/2026 checking a real one end to end. These columns are\n // how the chat closes that gap once it learns a phone or email that\n // matches one, so the bot can acknowledge it instead of asking for\n // details already on file somewhere else.\n `ALTER TABLE visitors ADD COLUMN sf_lead_company TEXT`,\n `ALTER TABLE visitors ADD COLUMN sf_lead_status TEXT`,\n `ALTER TABLE visitors ADD COLUMN sf_lead_checked_at INTEGER`,\n // A PASSCODE PER PERSON, so signing in on a phone does not mean typing\n // 32 hex characters. The token still works and is still the strong\n // credential; this is the humane one.\n //\n // It is deliberately NOT a shared password. The point of the whole table\n // is that a message can be attributed and one person's access can be\n // withdrawn without disturbing anyone else, and a passcode everybody\n // knows throws both of those away while looking like a small convenience.\n //\n // PBKDF2, not a bare SHA-256, because unlike the tokens this is a value a\n // human chose and is therefore in somebody's dictionary. The salt is per\n // person so two people picking the same passcode do not collide visibly.\n `ALTER TABLE agent_tokens ADD COLUMN passcode_hash TEXT`,\n `ALTER TABLE agent_tokens ADD COLUMN passcode_salt TEXT`,\n // Failed attempts, for backoff. Kept ON THE ROW rather than in a\n // transient keyed on IP — which is exactly what locked the owner out of\n // the /chat control page on 9/2/2026, because behind the host's proxy\n // every visitor shares one address and the limit became global.\n `ALTER TABLE agent_tokens ADD COLUMN fails INTEGER DEFAULT 0`,\n `ALTER TABLE agent_tokens ADD COLUMN last_fail_at INTEGER`,\n // HOW MANY TIMES we have picked a stalled conversation back up.\n //\n // nudged_at alone made it once per conversation for ever: someone who\n // went quiet, got a nudge, came back, talked for five minutes and then\n // stalled again — mid-signup — was left there. That second stall is the\n // expensive one, because by then they have shown they want it.\n `ALTER TABLE conversations ADD COLUMN nudge_count INTEGER DEFAULT 0`,\n // BACKFILL, and it matters. A new column defaults to 0, so every\n // conversation already nudged under the old scheme would have read as\n // \"never nudged\" and been sent a second message. Guarded so it is a\n // no-op on every run after the first.\n `UPDATE conversations SET nudge_count = 1\n WHERE nudged_at IS NOT NULL AND COALESCE(nudge_count, 0) = 0`,\n // ACTIVITY ONLY, NEVER CONTENT. This records that the visitor's\n // textarea has keystrokes in it right now — not what they say, not\n // even whether they end up sending it. A console showing a live draft\n // of unset text is a materially different (and much more invasive)\n // thing than this, and deliberately not what got built.\n `ALTER TABLE conversations ADD COLUMN visitor_typing_at INTEGER`,\n ]) {\n try {\n await sqlite.execute(stmt);\n } catch (_e) {\n // Column already present. Nothing to do.\n }\n }\n\n await sqlite.execute({\n sql: `INSERT INTO meta (k, v) VALUES ('schema_version', ?)\n ON CONFLICT(k) DO UPDATE SET v = excluded.v`,\n args: [String(SCHEMA_VERSION)],\n });\n })();\n\n return ready;\n}\n\nexport function now(): number {\n return Date.now();\n}\n\n// Prefixed so an id is self-describing in logs and in the dashboard URL.\nexport function id(prefix: string): string {\n return prefix + \"_\" + crypto.randomUUID().replace(/-/g, \"\").slice(0, 20);\n}\n\n/**\n * std/sqlite returns each row as an ARRAY of values, not an object.\n *\n * Everything downstream — the widget, both consoles, and the model's turn\n * builder — reads rows by column name, so they all silently saw `undefined`\n * and produced nothing. Converting here, at the single place every query\n * passes through, rather than teaching four callers about tuple order.\n */\nfunction rowsToObjects(result: any): any[] {\n const cols: string[] = result?.columns ?? [];\n return (result?.rows ?? []).map((row: any) =>\n Array.isArray(row)\n ? Object.fromEntries(cols.map((c, i) => [c, row[i]]))\n : row\n );\n}\n\n/* -------------------------------------------------------- agent tokens */\n\n/**\n * SHA-256, hex. Used for token storage, never for anything a user picks.\n *\n * A plain hash is right HERE and would be wrong for a password: these tokens\n * are 128 bits of crypto-random, so there is no dictionary to run and no work\n * factor worth paying. What the hash buys is that a copy of this table is not\n * a set of working logins.\n */\nexport async function sha256hex(s: string): Promise {\n const buf = await crypto.subtle.digest(\"SHA-256\", new TextEncoder().encode(s));\n return [...new Uint8Array(buf)].map((b) => b.toString(16).padStart(2, \"0\")).join(\"\");\n}\n\n/**\n * Mint a token for one person. The raw value is returned ONCE and never again.\n *\n * 128 bits from crypto.getRandomValues. Not Math.random, which is seeded from\n * something predictable and would make every token guessable from any other.\n */\nexport async function createAgentToken(person: string): Promise<{ id: string; token: string }> {\n const bytes = crypto.getRandomValues(new Uint8Array(16));\n const token = [...bytes].map((b) => b.toString(16).padStart(2, \"0\")).join(\"\");\n const tid = id(\"t\");\n\n await sqlite.execute({\n sql: `INSERT INTO agent_tokens (id, person, token_hash, created_at)\n VALUES (?, ?, ?, ?)`,\n args: [tid, person.slice(0, 60), await sha256hex(token), now()],\n });\n\n return { id: tid, token };\n}\n\n/** Resolve a raw token to the person holding it, or null. Revoked tokens fail. */\nexport async function resolveAgentToken(token: string) {\n const r = await sqlite.execute({\n sql: `SELECT id, person FROM agent_tokens\n WHERE token_hash = ? AND revoked_at IS NULL`,\n args: [await sha256hex(token)],\n });\n return rowsToObjects(r)[0] ?? null;\n}\n\n/**\n * Resolve a token ID from an already-signed session cookie.\n *\n * Checked on EVERY request rather than trusted from the cookie's signature\n * alone. The signature only proves we issued it; this proves it is still\n * valid — which is what makes revocation take effect immediately instead of\n * whenever the holder's 30-day cookie happens to lapse.\n */\nexport async function agentTokenById(tid: string) {\n const r = await sqlite.execute({\n sql: `SELECT id, person FROM agent_tokens\n WHERE id = ? AND revoked_at IS NULL`,\n args: [tid],\n });\n return rowsToObjects(r)[0] ?? null;\n}\n\n/**\n * PBKDF2-SHA256, 150k iterations.\n *\n * Slow ON PURPOSE, which is the opposite of what the tokens want. A token is\n * 128 random bits and nobody is going to guess it, so hashing speed is\n * irrelevant. A passcode is a word somebody thought of, so the only thing\n * standing between a stolen database and a working login is how long each\n * guess costs. 150k iterations is a few hundred milliseconds once at sign-in\n * and a wall for anyone working through a list.\n */\nexport async function passcodeHash(passcode: string, saltHex: string): Promise {\n const salt = Uint8Array.from(saltHex.match(/.{2}/g)!.map((h) => parseInt(h, 16)));\n const key = await crypto.subtle.importKey(\n \"raw\",\n new TextEncoder().encode(passcode),\n \"PBKDF2\",\n false,\n [\"deriveBits\"],\n );\n const bits = await crypto.subtle.deriveBits(\n { name: \"PBKDF2\", salt, iterations: 150000, hash: \"SHA-256\" },\n key,\n 256,\n );\n return [...new Uint8Array(bits)].map((b) => b.toString(16).padStart(2, \"0\")).join(\"\");\n}\n\n/** Set (or replace) one person's passcode. Clears any accumulated backoff. */\nexport async function setPasscode(tid: string, passcode: string) {\n const saltBytes = crypto.getRandomValues(new Uint8Array(16));\n const salt = [...saltBytes].map((b) => b.toString(16).padStart(2, \"0\")).join(\"\");\n await sqlite.execute({\n sql: `UPDATE agent_tokens\n SET passcode_hash = ?, passcode_salt = ?, fails = 0, last_fail_at = NULL\n WHERE id = ?`,\n args: [await passcodeHash(passcode, salt), salt, tid],\n });\n}\n\n/**\n * The row for a named person, for passcode sign-in.\n *\n * Case- and space-insensitive on the name, because the person typing it on a\n * phone is typing their own name from memory, not copying an identifier.\n */\nexport async function agentByPerson(person: string) {\n const r = await sqlite.execute({\n sql: `SELECT id, person, passcode_hash, passcode_salt, fails, last_fail_at\n FROM agent_tokens\n WHERE LOWER(TRIM(person)) = LOWER(TRIM(?))\n AND revoked_at IS NULL\n AND passcode_hash IS NOT NULL\n LIMIT 1`,\n args: [person],\n });\n return rowsToObjects(r)[0] ?? null;\n}\n\nexport async function notePasscodeFail(tid: string) {\n await sqlite.execute({\n sql: `UPDATE agent_tokens\n SET fails = COALESCE(fails, 0) + 1, last_fail_at = ?\n WHERE id = ?`,\n args: [now(), tid],\n });\n}\n\nexport async function clearPasscodeFails(tid: string) {\n await sqlite.execute({\n sql: `UPDATE agent_tokens SET fails = 0, last_fail_at = NULL WHERE id = ?`,\n args: [tid],\n });\n}\n\n/** Last-used stamp, so a token nobody uses is visibly safe to revoke. */\nexport async function touchAgentToken(tid: string) {\n await sqlite.execute({\n sql: `UPDATE agent_tokens SET last_used_at = ? WHERE id = ?`,\n args: [now(), tid],\n });\n}\n\nexport async function listAgentTokens() {\n const r = await sqlite.execute({\n // token_hash is deliberately NOT selected. It is not usable as a login,\n // but there is no reason for it to travel to a browser either.\n sql: `SELECT id, person, created_at, last_used_at, revoked_at\n FROM agent_tokens ORDER BY created_at DESC`,\n args: [], // Required — see livePushSubs.\n });\n return rowsToObjects(r);\n}\n\nexport async function revokeAgentToken(tid: string) {\n await sqlite.execute({\n sql: `UPDATE agent_tokens SET revoked_at = COALESCE(revoked_at, ?) WHERE id = ?`,\n args: [now(), tid],\n });\n}\n\n/* -------------------------------------------------------- visitor events */\n\n/**\n * Record something the visitor DID. Agent-facing only — see the table comment.\n *\n * DEDUPED AGAINST THE LAST EVENT OF THE SAME KIND. Presence pings arrive every\n * few seconds and each one carries the current page, so without this a visitor\n * sitting still on one page would generate an identical row every heartbeat and\n * the timeline would be unreadable within a minute.\n */\nexport async function recordEvent(\n visitorId: string,\n kind: string,\n detail?: string | null,\n) {\n const last = await sqlite.execute({\n sql: `SELECT kind, detail FROM visitor_events\n WHERE visitor_id = ? ORDER BY created_at DESC LIMIT 1`,\n args: [visitorId],\n });\n const prev = rowsToObjects(last)[0];\n if (prev && String(prev[\"kind\"]) === kind && String(prev[\"detail\"] ?? \"\") === String(detail ?? \"\")) {\n return;\n }\n\n await sqlite.execute({\n sql: `INSERT INTO visitor_events (id, visitor_id, kind, detail, created_at)\n VALUES (?, ?, ?, ?, ?)`,\n args: [id(\"e\"), visitorId, kind, detail ?? null, now()],\n });\n}\n\n/** The trail, oldest first, for interleaving into a thread by timestamp. */\nexport async function getEvents(visitorId: string, limit = 200) {\n const r = await sqlite.execute({\n sql: `SELECT kind, detail, created_at FROM visitor_events\n WHERE visitor_id = ? ORDER BY created_at ASC LIMIT ?`,\n args: [visitorId, limit],\n });\n return rowsToObjects(r);\n}\n\n/* ---------------------------------------------------- push subscriptions */\n\n/** Remember a phone. Re-subscribing the same device updates rather than duplicates. */\nexport async function addPushSub(tokenId: string, endpoint: string) {\n await sqlite.execute({\n sql: `INSERT INTO push_subs (id, token_id, endpoint, created_at)\n VALUES (?, ?, ?, ?)\n ON CONFLICT(endpoint) DO UPDATE SET\n token_id = excluded.token_id,\n fails = 0`,\n args: [id(\"ps\"), tokenId, endpoint, now()],\n });\n}\n\n/**\n * Every phone that should be buzzed.\n *\n * JOINed against agent_tokens so a REVOKED person's device stops receiving\n * customer names immediately. Without the join, revocation would take away\n * someone's ability to open the console while still pushing them every\n * customer's name and message — which is the wrong half.\n */\nexport async function livePushSubs() {\n const r = await sqlite.execute({\n sql: `SELECT p.id, p.endpoint\n FROM push_subs p\n JOIN agent_tokens t ON t.id = p.token_id\n WHERE t.revoked_at IS NULL\n AND COALESCE(p.fails, 0) < 5`,\n // args IS REQUIRED even with no placeholders. std/sqlite rejects a\n // statement object without it — \"body/statement must have required\n // property 'args'\" — and the failure surfaces far from here, as a push\n // that never sends.\n args: [],\n });\n return rowsToObjects(r);\n}\n\nexport async function pushOk(id: string) {\n await sqlite.execute({\n sql: `UPDATE push_subs SET last_ok_at = ?, fails = 0 WHERE id = ?`,\n args: [now(), id],\n });\n}\n\n/** A gone device is deleted outright; a flaky one just counts up. */\nexport async function pushFailed(id: string, gone: boolean) {\n await sqlite.execute(\n gone\n ? { sql: `DELETE FROM push_subs WHERE id = ?`, args: [id] }\n : { sql: `UPDATE push_subs SET fails = COALESCE(fails,0) + 1 WHERE id = ?`, args: [id] },\n );\n}\n\nexport async function removePushSub(endpoint: string) {\n await sqlite.execute({\n sql: `DELETE FROM push_subs WHERE endpoint = ?`,\n args: [endpoint],\n });\n}\n\n/** Does this person already have a phone registered? Drives the button state. */\nexport async function hasPushSub(tokenId: string): Promise {\n const r = await sqlite.execute({\n sql: `SELECT COUNT(*) AS n FROM push_subs WHERE token_id = ?`,\n args: [tokenId],\n });\n return Number(rowsToObjects(r)[0]?.[\"n\"] ?? 0) > 0;\n}\n\n/* ------------------------------------------------------------------ visitors */\n\nexport async function upsertVisitor(v: {\n id: string;\n name?: string | null;\n email?: string | null;\n phone?: string | null;\n zip?: string | null;\n company?: string | null;\n wpUserId?: string | null;\n pageUrl?: string | null;\n country?: string | null;\n os?: string | null;\n browser?: string | null;\n}) {\n const t = now();\n\n // COALESCE on update, so a later page view that knows less about the visitor\n // never blanks out what an earlier one established. Enrichment only ever\n // adds. The exception is page_url, which should always be the current page.\n await sqlite.execute({\n sql: `INSERT INTO visitors\n (id, name, email, phone, zip, company, wp_user_id,\n first_seen, last_seen, page_url, country, os, browser)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n ON CONFLICT(id) DO UPDATE SET\n name = COALESCE(excluded.name, visitors.name),\n email = COALESCE(excluded.email, visitors.email),\n phone = COALESCE(excluded.phone, visitors.phone),\n zip = COALESCE(excluded.zip, visitors.zip),\n company = COALESCE(excluded.company, visitors.company),\n wp_user_id = COALESCE(excluded.wp_user_id, visitors.wp_user_id),\n last_seen = excluded.last_seen,\n page_url = excluded.page_url,\n country = COALESCE(excluded.country, visitors.country),\n os = COALESCE(excluded.os, visitors.os),\n browser = COALESCE(excluded.browser, visitors.browser)`,\n args: [\n v.id, v.name ?? null, v.email ?? null, v.phone ?? null, v.zip ?? null,\n v.company ?? null, v.wpUserId ?? null, t, t,\n v.pageUrl ?? null, v.country ?? null, v.os ?? null, v.browser ?? null,\n ],\n });\n}\n\n/**\n * Record a phone number the visitor typed in the chat.\n *\n * Separate from upsertVisitor because that one always overwrites page_url, and\n * this is called mid-conversation where we know nothing else about the page.\n *\n * COALESCE, so a number WordPress already verified for a signed-in customer is\n * not replaced by one typed into a chat box. The typed number is still used for\n * the lookup either way — this only decides what the agent panel keys off.\n */\n/**\n * A name the visitor typed into the chat, usually after being asked for one so\n * the team can call back.\n *\n * COALESCE, like the phone: a name WordPress already had for a signed-in\n * customer outranks anything guessed out of a sentence. Deliberately does NOT\n * touch page_url, which upsertVisitor always overwrites.\n */\nexport async function setVisitorName(visitorId: string, name: string) {\n await sqlite.execute({\n sql: `UPDATE visitors SET name = COALESCE(name, ?) WHERE id = ?`,\n args: [name, visitorId],\n });\n}\n\n/** Count an ask, and hand back the new total so the caller can stop at the cap. */\nexport async function bumpContactAsks(cid: string): Promise {\n await sqlite.execute({\n sql: `UPDATE conversations SET contact_asks = COALESCE(contact_asks,0) + 1 WHERE id = ?`,\n args: [cid],\n });\n const r = await sqlite.execute({\n sql: `SELECT contact_asks FROM conversations WHERE id = ?`,\n args: [cid],\n });\n return Number(rowsToObjects(r)[0]?.[\"contact_asks\"] ?? 0);\n}\n\nexport async function setVisitorPhone(visitorId: string, phone: string) {\n await sqlite.execute({\n sql: `UPDATE visitors SET phone = COALESCE(phone, ?) WHERE id = ?`,\n args: [phone, visitorId],\n });\n}\n\n/**\n * Same shape as setVisitorPhone, for the same reason: an email scanned out\n * of a chat message was previously used ONLY for the one-off Lead lookup in\n * enrich() and then discarded — never written to the visitor row itself, so\n * it never reached VERIFIED ACCOUNT, the agent console, or anything built\n * later off \"do we have an email for this person.\" COALESCE so a real one\n * from WordPress is never overwritten by a typo typed into the chat box.\n */\nexport async function setVisitorEmail(visitorId: string, email: string) {\n await sqlite.execute({\n sql: `UPDATE visitors SET email = COALESCE(email, ?) WHERE id = ?`,\n args: [email, visitorId],\n });\n}\n\n/**\n * Record the opener at the moment the SERVER hands it over.\n *\n * Better than waiting for the widget to report it, for two reasons. It works\n * for every visitor immediately rather than only those who have picked up a new\n * widget.js past its five-minute cache — which was nobody for the first several\n * minutes. And it cannot disagree with what was displayed: the widget draws the\n * greeting on exactly the condition the caller checks here, an empty thread.\n *\n * COALESCE, so the first opener a visitor saw is the one kept.\n */\nexport async function setShownOpener(\n visitorId: string,\n text: string,\n variant?: string | null,\n) {\n await sqlite.execute({\n sql: `UPDATE visitors\n SET shown_opener = COALESCE(shown_opener, ?),\n opener_variant = COALESCE(opener_variant, ?)\n WHERE id = ?`,\n args: [text, variant ?? null, visitorId],\n });\n}\n\n/**\n * The split test's scoreboard.\n *\n * REPLIED means the visitor sent at least one message of their own — the only\n * thing an opener can be judged on. Not opens, not time on page: whether a real\n * person answered.\n *\n * Counted per VISITOR, not per conversation, because assignment is per visitor:\n * someone who returns a week later keeps their variant, and counting their\n * second conversation separately would weight the regulars.\n *\n * The `since` argument matters more than it looks. Every row written before the\n * test started has a null variant, and a \"no variant\" bucket sitting at the top\n * of the table is the kind of thing that gets read as a result.\n */\nexport async function openerReport(since = 0) {\n const r = await sqlite.execute({\n sql: `SELECT COALESCE(v.opener_variant, '(pre-test)') AS variant,\n COUNT(*) AS shown,\n SUM(CASE WHEN EXISTS (\n SELECT 1 FROM messages m\n JOIN conversations c ON c.id = m.conversation_id\n WHERE c.visitor_id = v.id AND m.sender = 'visitor')\n THEN 1 ELSE 0 END) AS replied,\n -- One example of the wording, so the report is readable\n -- without going and looking up what \"price_zip\" says.\n MAX(v.shown_opener) AS example\n FROM visitors v\n WHERE v.shown_opener IS NOT NULL\n AND v.first_seen >= ?\n GROUP BY variant\n ORDER BY shown DESC`,\n args: [since],\n });\n\n return rowsToObjects(r).map((row: any) => ({\n ...row,\n // Computed here rather than in the caller: two consoles and a shell would\n // otherwise each do this division slightly differently.\n reply_rate: Number(row[\"shown\"]) ? Number(row[\"replied\"]) / Number(row[\"shown\"]) : 0,\n }));\n}\n\n/**\n * The split-test scoreboard: which chat did better.\n *\n * The comparison this makes possible is the one tawk's own dashboard cannot:\n * tawk knows how many people TALKED to tawk, but not how many were offered it\n * and walked away. Our widget loads in both arms — invisibly for the tawk half\n * — so this side knows the denominator for both.\n *\n * \"Engaged\" means the VISITOR sent a message. That only exists for our arm; a\n * conversation held in tawk is not in this database. So the honest reading is:\n * ours — offered, and how many of them talked to us\n * tawk — offered, and you must read tawk's dashboard for the other half\n * Stated in the response rather than left for someone to misread as a like-for\n * -like win.\n */\n/**\n * Record that the tawk half opened its chat, or typed into it.\n *\n * COALESCE, so the FIRST time is kept. Someone who opens tawk six times is one\n * person who engaged, and overwriting would turn \"when did they engage\" into\n * \"when did they last click\", which answers a question nobody asked.\n */\n/**\n * Attribute an order to the visitor who placed it.\n *\n * COALESCE on all three, so the FIRST order sticks. The chat a person saw on\n * the visit where they bought is the one that earned it; their next order six\n * weeks later belongs to nothing in particular.\n */\nexport async function setVisitorOrder(visitorId: string, orderId: string, total: number | null) {\n await sqlite.execute({\n sql: `UPDATE visitors\n SET order_id = COALESCE(order_id, ?),\n order_total = COALESCE(order_total, ?),\n order_at = COALESCE(order_at, ?)\n WHERE id = ?`,\n args: [orderId, total, now(), visitorId],\n });\n}\n\nexport async function markTawkEvent(visitorId: string, event: \"opened\" | \"messaged\") {\n const col = event === \"messaged\" ? \"tawk_messaged_at\" : \"tawk_opened_at\";\n await sqlite.execute({\n sql: `UPDATE visitors SET ${col} = COALESCE(${col}, ?) WHERE id = ?`,\n args: [now(), visitorId],\n });\n}\n\nexport async function armReport(since = 0) {\n const r = await sqlite.execute({\n sql: `SELECT v.chat_arm AS arm,\n COUNT(*) AS visitors,\n -- THE SAME QUESTION ASKED OF BOTH ARMS: did a chat window\n -- open in front of them, and did they type into it.\n --\n -- For our arm that is chat_open and a visitor message; for\n -- tawk it is its own onChatMaximized and onChatMessageVisitor\n -- callbacks, reported by the page. Different plumbing, same\n -- event, one column each — which is what makes the two rows\n -- of this report comparable rather than merely adjacent.\n SUM(CASE\n WHEN v.chat_arm = 'tawk' THEN (v.tawk_opened_at IS NOT NULL)\n ELSE (v.chat_open = 1 OR v.shown_opener IS NOT NULL)\n END) AS opened,\n SUM(CASE\n WHEN v.chat_arm = 'tawk' THEN (v.tawk_messaged_at IS NOT NULL)\n ELSE EXISTS (\n SELECT 1 FROM messages m\n JOIN conversations c ON c.id = m.conversation_id\n WHERE c.visitor_id = v.id AND m.sender = 'visitor')\n END) AS engaged,\n -- Time on site, as a second opinion. A chat that keeps people\n -- on the page is worth something even when nobody types.\n CAST(AVG(v.time_on_site_ms) / 1000 AS INTEGER) AS avg_seconds,\n SUM(CASE WHEN v.left_at IS NOT NULL THEN 1 ELSE 0 END) AS left_site,\n -- THE NUMBER THAT ACTUALLY DECIDES THIS. Engagement is a\n -- proxy for the thing the business cares about; orders are\n -- the thing itself. A chat that gets fewer conversations and\n -- more customers wins, and only these columns can say so.\n SUM(CASE WHEN v.order_id IS NOT NULL THEN 1 ELSE 0 END) AS orders,\n ROUND(COALESCE(SUM(v.order_total), 0), 2) AS revenue\n FROM visitors v\n WHERE v.chat_arm IS NOT NULL\n AND v.first_seen >= ?\n GROUP BY v.chat_arm`,\n args: [since],\n });\n\n return rowsToObjects(r).map((row: any) => {\n const n = Number(row[\"visitors\"]) || 0;\n return {\n ...row,\n opened_rate: n ? Number(row[\"opened\"]) / n : 0,\n engaged_rate: n ? Number(row[\"engaged\"]) / n : 0,\n // SAY WHEN A NUMBER IS NOT YET WORTH READING. A rate over a handful of\n // visitors looks like a finding and is noise; without this someone acts\n // on the first hour of data.\n confidence: n < 100 ? \"too early — under 100 visitors in this arm\" : \"readable\",\n };\n });\n}\n\n/**\n * The browser told us it is going away. Recorded from a pagehide beacon.\n *\n * NOT PROOF THEY CLOSED THE SITE. pagehide also fires on an ordinary click\n * through to the next page, and on iOS when the tab is merely backgrounded. It\n * is only ever \"this document is being put away\" — which is why the next\n * heartbeat clears it, and why the console waits a few seconds before believing\n * it. What it does give is the thing a missing heartbeat never could: a\n * positive signal at the moment it happens, rather than a silence you have to\n * wait 45 seconds to notice.\n */\nexport async function markLeft(visitorId: string) {\n await sqlite.execute({\n sql: `UPDATE visitors SET left_at = ? WHERE id = ?`,\n args: [now(), visitorId],\n });\n}\n\nexport async function setSalesforce(\n visitorId: string,\n account: string | null,\n nextDelivery: string | null,\n) {\n await sqlite.execute({\n sql: `UPDATE visitors\n SET sf_account = ?, sf_next_delivery = ?, sf_checked_at = ?\n WHERE id = ?`,\n args: [account, nextDelivery, now(), visitorId],\n });\n}\n\n/**\n * Record a Lead match — or the absence of one, which matters just as much.\n *\n * COALESCE-FREE, unlike setSalesforce's sibling fields: a Lead can go stale\n * (someone submitted the popup, then later actually signed up, at which\n * point the account match in verifiedBlock should take over and this should\n * stop being mentioned) or simply turn out to have been a one-off check that\n * found nothing. sf_checked_at-style COALESCE is for a fact that only ever\n * gets MORE true; this one is re-verified each time instead.\n */\nexport async function setLead(\n visitorId: string,\n company: string | null,\n status: string | null,\n) {\n await sqlite.execute({\n sql: `UPDATE visitors\n SET sf_lead_company = ?, sf_lead_status = ?, sf_lead_checked_at = ?\n WHERE id = ?`,\n args: [company, status, now(), visitorId],\n });\n}\n\nexport async function getVisitor(visitorId: string) {\n const r = await sqlite.execute({\n sql: `SELECT * FROM visitors WHERE id = ?`,\n args: [visitorId],\n });\n return rowsToObjects(r)[0] ?? null;\n}\n\n/* ------------------------------------------------------- conversations */\n\nexport async function openConversation(visitorId: string): Promise {\n // One live conversation per visitor. Reusing it means a customer who\n // refreshes mid-chat lands back in the same thread instead of orphaning it\n // — orphaned threads are how a handoff gets silently dropped.\n const existing = await sqlite.execute({\n sql: `SELECT id FROM conversations\n WHERE visitor_id = ? AND status != 'closed'\n ORDER BY last_message_at DESC LIMIT 1`,\n args: [visitorId],\n });\n\n const prior = rowsToObjects(existing)[0];\n if (prior) return String(prior[\"id\"]);\n\n const cid = id(\"c\");\n const t = now();\n await sqlite.execute({\n sql: `INSERT INTO conversations\n (id, visitor_id, status, created_at, last_message_at)\n VALUES (?, ?, 'ai', ?, ?)`,\n args: [cid, visitorId, t, t],\n });\n return cid;\n}\n\nexport async function getConversation(cid: string) {\n const r = await sqlite.execute({\n sql: `SELECT c.*, v.name, v.email, v.phone, v.zip, v.company,\n v.sf_account, v.sf_next_delivery, v.page_url, v.country,\n v.os, v.browser, v.visits, v.page_views, v.time_on_site_ms,\n v.referrer, v.landing_url, v.landing_404, v.session_started, v.dnt,\n v.chat_open, v.shown_opener, v.shown_nudge, v.wp_user_id,\n -- Needed by /api/shown-nudge so the follow-up it stores is the\n -- one that does not repeat this visitor's opener.\n v.opener_variant, v.left_at,\n -- Carried into the thread view so the phone console can show\n -- a sale on the conversation it belongs to.\n v.order_id, v.order_total, v.first_seen, v.last_seen,\n -- Which chat they were offered, and whether they shut it. The\n -- detail panel claims to show both and could not: neither was\n -- selected here, so the fields simply never arrived.\n v.chat_arm, v.chat_dismissed,\n v.sf_lead_company, v.sf_lead_status\n FROM conversations c\n JOIN visitors v ON v.id = c.visitor_id\n WHERE c.id = ?`,\n args: [cid],\n });\n return rowsToObjects(r)[0] ?? null;\n}\n\n/**\n * Stamp \"keystrokes right now,\" nothing else. Called on a throttled timer\n * from the widget while the visitor's textarea is non-empty, and cleared\n * implicitly by staleness — the consoles treat anything older than a few\n * seconds as not-typing rather than requiring an explicit clear call, so a\n * closed tab or dead connection can't leave the indicator stuck on.\n */\nexport async function setTyping(cid: string) {\n await sqlite.execute({\n sql: `UPDATE conversations SET visitor_typing_at = ? WHERE id = ?`,\n args: [now(), cid],\n });\n}\n\nexport async function setStatus(\n cid: string,\n status: \"ai\" | \"needs_human\" | \"human\" | \"closed\",\n agent?: string | null,\n) {\n const t = now();\n // handoff_at is stamped only on the FIRST transition into needs_human, so a\n // customer who asks twice doesn't have their wait time reset to zero.\n await sqlite.execute({\n sql: `UPDATE conversations\n SET status = ?,\n assigned_to = COALESCE(?, assigned_to),\n handoff_at = CASE\n WHEN ? = 'needs_human' AND handoff_at IS NULL THEN ?\n ELSE handoff_at END,\n claimed_at = CASE\n WHEN ? = 'human' AND claimed_at IS NULL THEN ?\n ELSE claimed_at END\n WHERE id = ?`,\n args: [status, agent ?? null, status, t, status, t, cid],\n });\n}\n\n/** Conversation list for the dashboard, newest activity first. */\nexport async function listConversations(opts: {\n limit?: number;\n since?: number;\n includeClosed?: boolean;\n} = {}) {\n const { limit = 50, since = 0, includeClosed = false } = opts;\n const r = await sqlite.execute({\n sql: `SELECT c.id, c.status, c.assigned_to, c.last_message_at, c.handoff_at,\n c.visitor_typing_at,\n v.name, v.email, v.phone, v.sf_account, v.country,\n -- The live \"on site\" counter needs a start and a way to tell\n -- a real session from a single page load, exactly as the\n -- visitor list does. Without these the conversation rows can\n -- only say when someone last spoke, not whether they are\n -- still sitting there waiting for an answer.\n v.session_started, v.first_seen, v.visits, v.referrer,\n -- WHETHER THIS PERSON IS STILL ON THE SITE. left_at is the\n -- pagehide beacon and is cleared by the next heartbeat, so a\n -- value here means they closed the page; last_seen tells the\n -- console how long ago we last heard anything at all.\n -- Without both, a conversation the visitor walked away from\n -- ten minutes ago looks exactly like one still in progress,\n -- and an agent types a reply into an empty room.\n v.left_at, v.last_seen, v.chat_open,\n -- DID THIS CONVERSATION END IN AN ORDER. The single most\n -- useful thing to know about a thread after the fact, and\n -- the only one this list could not previously show.\n v.order_id, v.order_total,\n (SELECT body FROM messages m\n WHERE m.conversation_id = c.id\n ORDER BY m.created_at DESC LIMIT 1) AS preview,\n -- When the VISITOR last spoke, specifically. last_message_at\n -- moves for our own replies too, so it cannot tell the console\n -- the one thing worth making a noise about: a real person\n -- answering.\n (SELECT MAX(m2.created_at) FROM messages m2\n WHERE m2.conversation_id = c.id AND m2.sender = 'visitor')\n AS last_visitor_at,\n -- HOW MUCH WAS ACTUALLY SAID, BROKEN DOWN BY WHO SAID IT.\n --\n -- Every row in this list reads \"Anonymous\" with an AI badge,\n -- so a conversation somebody from the team spent ten minutes\n -- on is indistinguishable from an opener that got no reply.\n -- The split is what makes it legible: replies from the\n -- VISITOR say whether a real person engaged at all, and\n -- messages from an AGENT say whether one of the team was in\n -- it — which is the one an agent scanning the list is\n -- actually looking for.\n --\n -- Counted in the same query rather than in the console,\n -- because the console only receives the single most recent\n -- message per conversation and cannot count what it has not\n -- been sent.\n (SELECT COUNT(*) FROM messages m3\n WHERE m3.conversation_id = c.id\n AND m3.sender != 'system') AS msg_count,\n (SELECT COUNT(*) FROM messages m4\n WHERE m4.conversation_id = c.id\n AND m4.sender = 'visitor') AS visitor_msgs,\n (SELECT COUNT(*) FROM messages m5\n WHERE m5.conversation_id = c.id\n AND m5.sender = 'agent') AS agent_msgs\n FROM conversations c\n JOIN visitors v ON v.id = c.visitor_id\n WHERE (? = 1 OR c.status != 'closed')\n -- ENGAGED HISTORY, NOT JUST WHAT'S STILL OPEN. Left at 0 this\n -- clause is always true and nothing here changes from before —\n -- the default call from the Conversations tab is untouched. The\n -- Engaged tab passes its own 24-hour cutoff, and needs closed\n -- threads included above for the same reason: a conversation\n -- someone genuinely engaged with and an agent later closed is\n -- still exactly what \"engaged\" means, and should not vanish\n -- from a 24-hour review the moment it is wrapped up.\n AND (? = 0 OR EXISTS (\n SELECT 1 FROM messages m6\n WHERE m6.conversation_id = c.id\n AND m6.sender = 'visitor'\n AND m6.created_at >= ?\n ))\n ORDER BY\n -- Anyone waiting on a human sorts to the top regardless of\n -- recency. A customer waiting eight minutes must not be pushed\n -- down the list by a bot conversation that just ticked.\n CASE c.status WHEN 'needs_human' THEN 0 ELSE 1 END,\n c.last_message_at DESC\n LIMIT ?`,\n args: [includeClosed ? 1 : 0, since, since, limit],\n });\n return rowsToObjects(r);\n}\n\n/**\n * Conversations that went quiet after the visitor had engaged.\n *\n * EVERY CLAUSE HERE EXISTS TO STOP THIS BEING NAGGING. In order:\n *\n * - status 'ai' a human who has taken over does not want the bot\n * interrupting, and a closed thread is finished.\n * - nudged_at NULL once per conversation, ever. Twice is pestering.\n * - the idle WINDOW a floor so we are not talking over someone who is still\n * typing, and a CEILING because reopening a conversation\n * that died an hour ago reads as creepy rather than\n * helpful. Outside the window they are simply left alone.\n * - visitor spoke \"some engagement\" is the whole premise. Somebody who\n * opened the widget and never typed gets nothing.\n * - we spoke LAST if the visitor spoke last we owe them an answer, not a\n * follow-up question. That case is a bug, not an idle chat.\n * - no pay link an order already went through, so \"need a hand ordering?\"\n * would be nonsense to the one person we most want to look\n * competent in front of.\n */\nexport async function listNudgeCandidates(\n idleAtLeastMs: number,\n idleAtMostMs: number,\n limit = 5,\n) {\n const t = now();\n const r = await sqlite.execute({\n sql: `SELECT c.id, c.visitor_id, COALESCE(c.nudge_count, 0) AS nudge_count\n FROM conversations c\n WHERE c.status = 'ai'\n AND c.last_message_at <= ?\n AND c.last_message_at >= ?\n -- UP TO TWO PICK-UPS, and the second has to be earned.\n --\n -- First stall: nothing sent yet, go ahead. Second stall: only if\n -- they actually CAME BACK and spoke after the first nudge. Someone\n -- who ignored it has answered already, and asking twice is\n -- pestering; someone who replied and then trailed off mid-signup is\n -- the person worth catching.\n AND (\n COALESCE(c.nudge_count, 0) = 0\n OR (COALESCE(c.nudge_count, 0) = 1\n AND EXISTS (SELECT 1 FROM messages m4\n WHERE m4.conversation_id = c.id\n AND m4.sender = 'visitor'\n AND m4.created_at > c.nudged_at))\n )\n AND EXISTS (SELECT 1 FROM messages m\n WHERE m.conversation_id = c.id AND m.sender = 'visitor')\n AND (SELECT m2.sender FROM messages m2\n WHERE m2.conversation_id = c.id\n ORDER BY m2.created_at DESC LIMIT 1) = 'ai'\n AND NOT EXISTS (SELECT 1 FROM messages m3\n WHERE m3.conversation_id = c.id\n AND m3.body LIKE '%/payments/%')\n -- THE CONVERSATION ALREADY SAID GOODBYE. Confirmed live 9/7/2026: a\n -- customer wrote \"thank you!!\", got a warm close, and 47 seconds\n -- later got ANOTHER \"let me know if anything else comes up\" nudge\n -- anyway — reopening a door the visitor had just shut. Checked on\n -- the LAST visitor message specifically, not the whole thread: a\n -- \"thanks\" in the middle of a conversation that is still going\n -- (thanking the bot for a price, say) must not silence a nudge the\n -- conversation still earns later.\n AND NOT EXISTS (\n SELECT 1 FROM messages m6\n WHERE m6.conversation_id = c.id AND m6.sender = 'visitor'\n AND m6.created_at = (SELECT MAX(m7.created_at) FROM messages m7\n WHERE m7.conversation_id = c.id AND m7.sender = 'visitor')\n AND (LOWER(m6.body) LIKE '%thank%' OR LOWER(m6.body) LIKE '%thx%'\n OR LOWER(m6.body) LIKE '%appreciate it%' OR LOWER(m6.body) LIKE '%bye%')\n )\n ORDER BY c.last_message_at ASC\n LIMIT ?`,\n args: [t - idleAtLeastMs, t - idleAtMostMs, limit],\n });\n return rowsToObjects(r);\n}\n\n/**\n * Stamped BEFORE the nudge is written, not after.\n *\n * If the model call then fails, this conversation is skipped for good — which\n * is the right way round. A crash between the two must not leave a thread\n * eligible on every subsequent sweep, quietly nudging the same person forever.\n */\n/**\n * THE FUNNEL PAST \"DID THEY REPLY.\"\n *\n * Every opener/nudge metric in this file stops at reply rate, which cannot\n * see where a conversation that DID reply then fell out — and measured\n * 9/7/2026, every sales conversation in a 5-conversation spot-check stalled\n * before signup, which the reply-rate numbers had no way to show.\n *\n * THESE ARE TEXT-PATTERN PROXIES, NOT A TRACKED STATE MACHINE. There is no\n * column anywhere recording \"this conversation reached the quoting stage\" —\n * that would mean either the model stamping its own progress (trusting it to\n * self-report, the exact thing this project avoids everywhere else) or a\n * larger schema change. So each stage after \"replied\" is inferred from a\n * pattern in the bot's own messages that reliably appears at that point in a\n * real conversation:\n * region_confirmed — the bot says \"we cover\" or \"we deliver\" only once a\n * ZIP has resolved to a servable region (see\n * openingGreeting / the AI's own coverage-confirmation\n * habit)\n * quoted — the bot's reply contains a dollar sign at all\n * signed_up — a pay link went out, the same signal\n * listNudgeCandidates already trusts to mean \"this\n * conversation is a completed order, leave it alone\"\n * Every stage from region_confirmed onward also requires visitor_msgs > 0 —\n * without that, an opener or nudge that happens to mention price before the\n * visitor ever says a word would count as \"quoted,\" which is not a funnel,\n * it's just counting messages. Each stage is a subset of the one before it.\n *\n * Read the numbers as directional, not exact — a conversation that got a\n * region confirmed by some other phrasing is undercounted, not missing.\n */\nexport async function funnelReport(sinceMs: number) {\n const r = await sqlite.execute({\n sql: `WITH conv_stats AS (\n SELECT c.id AS conv_id,\n MIN(m.created_at) AS started_at,\n SUM(CASE WHEN m.sender = 'visitor' THEN 1 ELSE 0 END) AS visitor_msgs,\n SUM(CASE WHEN m.sender = 'ai' AND\n (LOWER(m.body) LIKE '%we cover%' OR LOWER(m.body) LIKE '%we deliver%')\n THEN 1 ELSE 0 END) AS region_confirms,\n SUM(CASE WHEN m.sender = 'ai' AND m.body LIKE '%$%' THEN 1 ELSE 0 END) AS price_mentions,\n SUM(CASE WHEN m.body LIKE '%/payments/%' THEN 1 ELSE 0 END) AS paylink_sent\n FROM conversations c\n JOIN messages m ON m.conversation_id = c.id\n GROUP BY c.id\n )\n SELECT\n (SELECT COUNT(*) FROM visitors WHERE shown_opener IS NOT NULL AND first_seen >= ?) AS shown,\n (SELECT COUNT(*) FROM conv_stats WHERE started_at >= ? AND visitor_msgs > 0) AS replied,\n (SELECT COUNT(*) FROM conv_stats WHERE started_at >= ? AND visitor_msgs > 0 AND region_confirms > 0) AS region_confirmed,\n (SELECT COUNT(*) FROM conv_stats WHERE started_at >= ? AND visitor_msgs > 0 AND price_mentions > 0) AS quoted,\n (SELECT COUNT(*) FROM conv_stats WHERE started_at >= ? AND visitor_msgs > 0 AND paylink_sent > 0) AS signed_up`,\n args: [sinceMs, sinceMs, sinceMs, sinceMs, sinceMs],\n });\n return rowsToObjects(r)[0] ?? {\n shown: 0, replied: 0, region_confirmed: 0, quoted: 0, signed_up: 0,\n };\n}\n\nexport async function markNudged(cid: string) {\n // COUNTS UP rather than latching. The old form only wrote when nudged_at was\n // null, which is what made a second pick-up impossible: the stamp never moved,\n // so \"how long since we last nudged\" was unanswerable after the first one.\n await sqlite.execute({\n sql: `UPDATE conversations\n SET nudged_at = ?, nudge_count = COALESCE(nudge_count, 0) + 1\n WHERE id = ?`,\n args: [now(), cid],\n });\n}\n\n/* ----------------------------------------------------------- messages */\n\nexport async function addMessage(\n cid: string,\n sender: \"visitor\" | \"ai\" | \"agent\" | \"system\",\n body: string,\n senderName?: string | null,\n): Promise<{ id: string; created_at: number }> {\n const mid = id(\"m\");\n const t = now();\n\n await sqlite.batch([\n {\n sql: `INSERT INTO messages\n (id, conversation_id, sender, sender_name, body, created_at)\n VALUES (?, ?, ?, ?, ?, ?)`,\n args: [mid, cid, sender, senderName ?? null, body, t],\n },\n {\n sql: `UPDATE conversations SET last_message_at = ? WHERE id = ?`,\n args: [t, cid],\n },\n ]);\n\n return { id: mid, created_at: t };\n}\n\n/**\n * How many messages the VISITOR has sent in this conversation.\n *\n * Used to spot the first one. \"Someone has started chatting\" is worth a push;\n * \"someone sent their fourth message\" is the same conversation buzzing a phone\n * again, and that is how a useful alert becomes one people mute.\n */\nexport async function visitorMessageCount(cid: string): Promise {\n const r = await sqlite.execute({\n sql: `SELECT COUNT(*) AS n FROM messages\n WHERE conversation_id = ? AND sender = 'visitor'`,\n args: [cid],\n });\n return Number(rowsToObjects(r)[0]?.[\"n\"] ?? 0);\n}\n\nexport async function getMessages(cid: string, sinceMs = 0) {\n const r = await sqlite.execute({\n sql: `SELECT id, sender, sender_name, body, created_at\n FROM messages\n WHERE conversation_id = ? AND created_at > ?\n ORDER BY created_at ASC`,\n args: [cid, sinceMs],\n });\n return rowsToObjects(r);\n}\n\n/* ----------------------------------------------------------- presence */\n\n/**\n * A gap this long means they left and came back, so it counts as a new visit.\n * Thirty minutes is the usual analytics convention and matches how people\n * actually browse — a tab left open over lunch is not two visits.\n */\nconst SESSION_GAP_MS = 30 * 60 * 1000;\n\n/**\n * How long a visitor who never spoke to us is kept.\n *\n * Browsing data has a short useful life and a long liability tail. Ninety days\n * covers \"have they been back since we quoted them\" and nothing beyond it.\n * Anyone who actually held a conversation is exempt — that is a customer\n * record, not analytics.\n */\nconst RETENTION_MS = 90 * 24 * 60 * 60 * 1000;\n\n/**\n * Heartbeat. Runs on every page view, so it stays a single UPDATE.\n *\n * referrer and landing_url use COALESCE because only the FIRST answer is\n * interesting: how they found you, and where they came in. Overwriting them on\n * every page would just leave you with the previous page of your own site.\n */\nexport async function recordPresence(\n visitorId: string,\n pageUrl: string | null,\n referrer: string | null,\n opts: {\n country?: string | null;\n os?: string | null;\n browser?: string | null;\n optedOut?: boolean;\n chatOpen?: boolean;\n chatDismissed?: boolean;\n notFound?: boolean;\n arm?: string | null;\n shownOpener?: string | null;\n shownNudge?: string | null;\n } = {},\n) {\n const v = await getVisitor(visitorId);\n if (!v) return { newSession: false };\n\n const t = now();\n const last = Number(v[\"last_seen\"] ?? 0);\n const first = Number(v[\"first_seen\"] ?? 0);\n const gap = last ? t - last : 0;\n\n // The row was inserted milliseconds ago by this same request, so first_seen\n // and last_seen are the same stamp. That is a first page view, NOT a second\n // session — counting it as one would have every visitor arrive at \"visit 2\",\n // and it would bank the insert-to-update milliseconds as time on site.\n const brandNew = !last || last === first;\n\n const isNew = !brandNew && gap > SESSION_GAP_MS ? 1 : 0;\n\n // Global Privacy Control, honoured. They still appear on the monitoring\n // screen and chat works exactly as normal — the console would be useless\n // otherwise, and refusing to talk to someone is not a privacy feature. What\n // stops is the profile: no counters, no device, no country, ever.\n if (opts.optedOut) {\n // chat_open is recorded even here. It is not a profile — it is whether a\n // window is open on screen this second, which is what an agent needs to\n // decide whether saying something would reach anybody.\n await sqlite.execute({\n sql: `UPDATE visitors\n SET last_seen = ?, page_url = COALESCE(?, page_url), dnt = 1,\n chat_open = ?,\n -- Cleared here too. Whether the window in front of someone is\n -- still open is not a profile, and an opted-out visitor shown\n -- as permanently \"left\" would be worse served, not better.\n left_at = NULL\n WHERE id = ?`,\n args: [t, pageUrl, opts.chatOpen ? 1 : 0, visitorId],\n });\n return { newSession: isNew === 1 };\n }\n\n // Page views count PAGES, not heartbeats. The widget pings every 45 seconds,\n // so counting pings would report eighty views for one long article and make\n // the number worse than useless — it would look like engagement.\n const samePage = !!pageUrl && String(v[\"page_url\"] ?? \"\") === pageUrl;\n const viewed = brandNew || isNew || !samePage ? 1 : 0;\n\n // Dwell time accrues from the gap between pings, and only within a session.\n // The hours between two visits are not time on site, and a tab left open\n // over a weekend must not report as a three-day reading session.\n const dwell = brandNew || isNew ? 0 : Math.min(gap, SESSION_GAP_MS);\n\n await sqlite.execute({\n sql: `UPDATE visitors\n SET last_seen = ?,\n page_url = COALESCE(?, page_url),\n referrer = COALESCE(referrer, ?),\n landing_url = COALESCE(landing_url, ?),\n -- COALESCE, like landing_url beside it: the first answer is\n -- kept, and the first answer is about the page they arrived\n -- on. A 404 three clicks later is browsing; a 404 on arrival\n -- is a broken link somebody sent them.\n landing_404 = COALESCE(landing_404, ?),\n -- The arm is sticky in the browser, so the first answer is the\n -- answer. COALESCE also means a visitor from before this\n -- column existed is not retro-labelled by a later ping.\n chat_arm = COALESCE(chat_arm, ?),\n visits = COALESCE(visits, 1) + ?,\n page_views = COALESCE(page_views, 0) + ?,\n time_on_site_ms = COALESCE(time_on_site_ms, 0) + ?,\n country = COALESCE(?, country),\n os = COALESCE(?, os),\n browser = COALESCE(?, browser),\n session_started = CASE WHEN ? = 1 THEN ?\n ELSE COALESCE(session_started, ?) END,\n chat_open = ?,\n -- Overwritten, not COALESCEd: this one can go back to 0 when\n -- the week lapses or they open the chat again, and a sticky\n -- \"they closed it\" would then be a lie the console repeats.\n chat_dismissed = ?,\n -- THEY ARE BACK. A ping is proof the document is alive, so any\n -- earlier pagehide was a tab switch, a bfcache restore, or a\n -- click through to the next page of the site — not a departure.\n -- Without this the console would keep calling a live visitor\n -- gone for as long as they stayed.\n left_at = NULL,\n -- COALESCE: the FIRST thing they were shown is the interesting\n -- one. Later pings send the same text, and a page change would\n -- otherwise overwrite the line they actually read.\n shown_opener = COALESCE(shown_opener, ?),\n shown_nudge = COALESCE(shown_nudge, ?)\n WHERE id = ?`,\n args: [\n t, pageUrl, referrer, pageUrl,\n // undefined means \"this widget build does not report it\" — stored as\n // NULL so COALESCE can still take a real answer from a later ping,\n // rather than freezing an assumed 0 in place.\n opts.notFound === undefined ? null : (opts.notFound ? 1 : 0),\n opts.arm === \"ours\" || opts.arm === \"tawk\" ? opts.arm : null,\n isNew, viewed, dwell,\n opts.country ?? null, opts.os ?? null, opts.browser ?? null,\n isNew, t, t,\n opts.chatOpen ? 1 : 0, opts.chatDismissed ? 1 : 0,\n opts.shownOpener ?? null, opts.shownNudge ?? null,\n visitorId,\n ],\n });\n\n return { newSession: isNew === 1 };\n}\n\n/**\n * Delete browsing rows past their retention window.\n *\n * Throttled through `meta` rather than run on a cron, so it cannot fire on\n * every request and needs no second scheduled file. Once a day is far more\n * often than ninety-day retention requires.\n *\n * Visitors with any conversation are excluded outright. Deleting those would\n * orphan the messages and lose the account match an agent worked for.\n */\nexport async function purgeStaleVisitors(): Promise {\n const t = now();\n\n // visitor_events is swept with everything else — see the DELETE below. It is\n // the highest-volume table in here (a row per page change, against a row per\n // message) and it holds browsing history, so it is the LAST thing that should\n // outlive the retention window.\n const seen = await sqlite.execute({\n sql: `SELECT v FROM meta WHERE k = 'last_purge'`,\n // Missing args here made this THROW on every call, so the ninety-day\n // retention sweep has never actually deleted anything. Found 9/2/2026\n // while chasing the same omission in the push queries.\n args: [],\n });\n const lastRun = Number(rowsToObjects(seen)[0]?.[\"v\"] ?? 0);\n if (lastRun && t - lastRun < 24 * 60 * 60 * 1000) return 0;\n\n // Stamp BEFORE deleting, so a failure cannot put this in a retry loop that\n // runs a table scan on every page view of the storefront.\n await sqlite.execute({\n sql: `INSERT INTO meta (k, v) VALUES ('last_purge', ?)\n ON CONFLICT(k) DO UPDATE SET v = excluded.v`,\n args: [String(t)],\n });\n\n const res = await sqlite.execute({\n sql: `DELETE FROM visitors\n WHERE last_seen < ?\n AND id NOT IN (SELECT visitor_id FROM conversations)`,\n args: [t - RETENTION_MS],\n });\n\n // Events for visitors who no longer exist, plus anything past the window on\n // its own. Two conditions rather than one: a visitor kept because they have\n // a conversation still accumulates page-by-page browsing history, and there\n // is no reason to hold ninety days of that after the chat is long over.\n await sqlite.execute({\n sql: `DELETE FROM visitor_events\n WHERE created_at < ?\n OR visitor_id NOT IN (SELECT id FROM visitors)`,\n args: [t - RETENTION_MS],\n });\n\n const n = Number((res as any)?.rowsAffected ?? 0);\n if (n) console.log(\"retention: purged \" + n + \" visitor rows past 90 days\");\n return n;\n}\n\n/**\n * Who is on the site right now.\n *\n * A visitor counts as present if they pinged within the window the caller\n * passes — roughly twice the heartbeat interval, so one missed ping does not\n * make someone vanish from an agent's screen mid-thought.\n */\nexport async function listActiveVisitors(activeSince: number) {\n const r = await sqlite.execute({\n sql: `SELECT v.id, v.name, v.email, v.phone, v.company, v.zip,\n -- Proof of a real WooCommerce session, not a guess: the\n -- WordPress snippet sets it only when is_user_logged_in().\n -- The console needs it to tell a customer browsing their\n -- account from someone stuck on the login form.\n v.wp_user_id,\n v.sf_account, v.sf_next_delivery,\n v.page_url, v.country, v.first_seen, v.last_seen,\n v.referrer, v.landing_url, v.landing_404, v.chat_arm,\n v.visits, v.session_started,\n v.os, v.browser, v.page_views, v.time_on_site_ms, v.dnt,\n v.chat_open, v.chat_dismissed,\n v.shown_opener, v.shown_nudge, v.opener_variant,\n -- Set by the pagehide beacon and cleared by the next ping, so a\n -- value here means the last thing this browser did was go away.\n v.left_at,\n -- THIS VISIT ENDED IN A SALE. Worth a badge on the row for its\n -- own sake: the console otherwise shows the most valuable\n -- person on the site — someone who has just paid — exactly\n -- like everyone else who happens to be browsing.\n v.order_id, v.order_total, v.order_at,\n (SELECT c.id FROM conversations c\n WHERE c.visitor_id = v.id AND c.status != 'closed'\n ORDER BY c.last_message_at DESC LIMIT 1) AS conversation_id,\n (SELECT COUNT(*) FROM messages m\n JOIN conversations c2 ON c2.id = m.conversation_id\n WHERE c2.visitor_id = v.id) AS message_count\n FROM visitors v\n WHERE v.last_seen > ?\n -- NEWEST ARRIVAL FIRST, not most recent heartbeat.\n --\n -- Sorting on last_seen looked like the same thing and is not: every\n -- live visitor pings every 45 seconds, so the top of the list was\n -- whoever happened to have pinged most recently, which is close to\n -- arbitrary among people who are all still here. Someone half an\n -- hour into a session sat above someone who had just landed.\n --\n -- Arrival order is the one an agent can act on. A visitor who\n -- arrived twenty seconds ago is the one still deciding whether to\n -- stay; a visitor thirty minutes in has already made that call. The\n -- row still says how long each has been idle, so nobody stale is\n -- mistaken for live just because they arrived recently.\n --\n -- COALESCE because session_started is only stamped once presence has\n -- run; a row inserted moments ago has none yet, and without the\n -- fallback the newest visitor of all would sort to the bottom.\n ORDER BY COALESCE(v.session_started, v.first_seen) DESC\n LIMIT 100`,\n args: [activeSince],\n });\n return rowsToObjects(r);\n}\n\n/** Existing open conversation, WITHOUT creating one. */\nexport async function findOpenConversation(visitorId: string): Promise {\n const r = await sqlite.execute({\n sql: `SELECT id FROM conversations\n WHERE visitor_id = ? AND status != 'closed'\n ORDER BY last_message_at DESC LIMIT 1`,\n args: [visitorId],\n });\n const row = rowsToObjects(r)[0];\n return row ? String(row[\"id\"]) : null;\n}\n\n/**\n * Has anyone spoken to this visitor yet?\n *\n * Used by the presence ping to decide whether to pop the widget open. A chat\n * the visitor started themselves must not re-open on every page view — only an\n * agent reaching out unprompted should do that.\n */\nexport async function hasAgentMessage(conversationId: string): Promise {\n const r = await sqlite.execute({\n sql: `SELECT COUNT(*) AS n FROM messages\n WHERE conversation_id = ? AND sender = 'agent'`,\n args: [conversationId],\n });\n const row = rowsToObjects(r)[0];\n return Number(row?.[\"n\"] ?? 0) > 0;\n}\n","deleted",false,"label",[14],"53b5cfec-2084-4c81-ba38-78b35a1e8625",["D",1788226289223],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","Chat widget, AI answering, human takeover, Salesforce","imageUrl","userId","c5acd3b3-60a7-4deb-bc6d-528f83012ea9","pinnedFileId","db056860-a5a4-11f1-8f50-1607ee4eb77e","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","019f8fe2-cdfc-775d-a845-a69af13d39cb","tursoName","53b5cfec20844c81ba3878b35a1e8625","tursoHostname","53b5cfec20844c81ba3878b35a1e8625-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789484509295],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_9":10,"_106":102},"org",{"_9":10,"_99":100,"_101":102,"_55":56,"_103":104,"_105":42},"branch",{"_35":89,"_11":90,"_22":28,"_91":45,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":-5,"_97":12,"_98":10},"isOwned",709,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","M A","avatar","https://lh3.googleusercontent.com/a/ACg8ocI9znhXn2hWp1wjjyWzeuOyoO_JrDgJo8aD0umFraR7bAG3UAmc=s96-c","tier","free","canRestrictVals","profileImageUrl","ctx",{"_82":-5,"_140":141,"_142":143,"_144":145,"_146":42,"_147":42,"_148":42,"_149":32,"_150":-5,"_151":-7,"_152":-7,"_153":63,"_154":42,"_155":42,"_156":-5,"_157":42,"_158":42,"_159":42,"_160":-5},"ENV",{"_111":112,"_113":114,"_115":90,"_116":117,"_118":119,"_120":121,"_122":123,"_124":125,"_126":127,"_128":129,"_130":131,"_132":133,"_134":135,"_136":137,"_138":139},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","e87eb04e707cf64e7e532c8e8ec395f209e17f15","RENDER_GIT_BRANCH","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town","orgs",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]