[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8},"actionData","errors","root",{"_109":110,"_111":112},"routes/embed.x.$handle.$name.$",{"_9":10,"_11":12,"_13":14,"_15":16,"_17":18,"_19":20,"_21":-5},"handle","herschelrs","name","cl-triage","path","lib/db.ts","project",{"_22":48,"_11":12,"_35":49,"_50":-5,"_51":44,"_52":53,"_54":53,"_55":56,"_57":-5,"_58":59,"_60":61,"_62":34,"_63":-5,"_64":-5,"_65":66,"_67":68,"_69":70,"_71":72,"_73":74,"_75":-5,"_76":77,"_78":77,"_79":80,"_81":80,"_82":80,"_83":77,"_84":77,"_85":86,"_87":88,"_89":90,"_91":44},"file",{"_22":23,"_11":24,"_25":26,"_27":28,"_29":30,"_31":32,"_33":34,"_35":36,"_37":38,"_39":40,"_41":42,"_43":44,"_45":-5,"_13":46},"permalink","https://www.val.town/x/herschelrs/cl-triage/code/lib/db.ts","extension","id","7f6a03a8-6d6b-4ba3-a21b-7abd5535415a","db.ts","fileId","e881e32e-ace8-11f1-9915-1607ee4eb77e","parentId","e7cae0ca-ace8-11f1-b8ae-1607ee4eb77e","branchId","19036aae-467e-44f0-962d-88964ddc5361","branchVersion",60,"latest",true,"createdAt",["D",1789030162978],"version",11,"fileType","script","content","/**\n * Val-scoped SQLite. Note the import path ends in /main.ts — the legacy\n * https://esm.town/v/std/sqlite module is account-scoped and returns rows as arrays\n * instead of objects. Do not mix the two.\n *\n * Quota is 10 MB shared between SQLite and blob for the whole account, so this stores ids,\n * verdicts and short reasons only. Never image bytes.\n */\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\n\nexport type Row = Record;\n/** What the libsql client accepts as a bound parameter. */\nexport type Arg = string | number | bigint | boolean | null | Uint8Array;\n\nasync function run(sql: string, args: Arg[] = []): Promise {\n const res = await sqlite.execute({ sql, args });\n return (res.rows ?? []) as unknown as Row[];\n}\n\n/** Same, but for writes where the number of rows touched is the answer we need. */\nasync function write(sql: string, args: Arg[] = []): Promise {\n const res = await sqlite.execute({ sql, args });\n return Number(res.rowsAffected ?? 0);\n}\n\n/** Migrations are CREATE ... IF NOT EXISTS only; every entry point calls this first. */\nexport async function init(): Promise {\n await run(`CREATE TABLE IF NOT EXISTS posts (\n uuid TEXT PRIMARY KEY,\n pid INTEGER,\n repost_of INTEGER,\n search TEXT,\n title TEXT,\n price INTEGER,\n hood TEXT,\n lat REAL,\n lon REAL,\n image_ids TEXT,\n thumb_img_id TEXT,\n url TEXT,\n record_created INTEGER,\n status TEXT,\n verdict TEXT,\n score INTEGER,\n reason TEXT,\n flags TEXT,\n dup_of TEXT,\n prompt_len INTEGER,\n queued_at TEXT,\n judged_at TEXT,\n emailed_at TEXT\n )`);\n await run(`CREATE INDEX IF NOT EXISTS posts_pid ON posts (pid)`);\n await run(`CREATE INDEX IF NOT EXISTS posts_repost_of ON posts (repost_of)`);\n await run(`CREATE INDEX IF NOT EXISTS posts_status ON posts (status)`);\n await run(`CREATE TABLE IF NOT EXISTS blocked (\n key TEXT PRIMARY KEY, from_uuid TEXT, reason TEXT, at TEXT)`);\n await run(`CREATE TABLE IF NOT EXISTS feedback (\n uuid TEXT, kind TEXT, reason TEXT, at TEXT)`);\n await run(`CREATE INDEX IF NOT EXISTS feedback_uuid ON feedback (uuid)`);\n\n // Added after the table already existed on platform, so it cannot go in the CREATE above.\n // ALTER TABLE ADD COLUMN errors if the column is there, so ask first rather than swallowing.\n const cols = await run(`PRAGMA table_info(posts)`);\n const have = new Set(cols.map((c) => String(c.name)));\n if (!have.has(\"attempts\")) {\n await run(`ALTER TABLE posts ADD COLUMN attempts INTEGER DEFAULT 0`);\n console.log(\"MIGRATION posts.attempts added\");\n }\n if (!have.has(\"claim_at\")) {\n await run(`ALTER TABLE posts ADD COLUMN claim_at TEXT`);\n console.log(\"MIGRATION posts.claim_at added\");\n }\n}\n\nexport const now = () => new Date().toISOString();\n\nexport interface QueuedInsert {\n uuid: string;\n pid: number | null;\n search: string;\n title: string;\n price: number | null;\n hood: string | null;\n thumbImgId: string | null;\n url: string;\n}\n\nexport async function insertQueued(p: QueuedInsert): Promise {\n await run(\n `INSERT OR IGNORE INTO posts\n (uuid, pid, search, title, price, hood, thumb_img_id, url, status, queued_at)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'queued', ?)`,\n [p.uuid, p.pid, p.search, p.title, p.price, p.hood, p.thumbImgId, p.url, now()],\n );\n}\n\nexport async function getPost(uuid: string): Promise {\n const rows = await run(`SELECT * FROM posts WHERE uuid = ?`, [uuid]);\n return rows[0] ?? null;\n}\n\nexport async function existsPid(pid: number): Promise {\n const rows = await run(`SELECT uuid FROM posts WHERE pid = ? LIMIT 1`, [pid]);\n return rows[0] ? String(rows[0].uuid) : null;\n}\n\n/** Cheap pre-judge check: has this thumbnail already been seen on a post we know about? */\nexport async function existsImage(imgId: string): Promise {\n const rows = await run(\n `SELECT uuid FROM posts\n WHERE thumb_img_id = ? OR (image_ids IS NOT NULL AND instr(image_ids, ?) > 0)\n LIMIT 1`,\n [imgId, imgId],\n );\n return rows[0] ? String(rows[0].uuid) : null;\n}\n\nexport async function isBlocked(keys: string[]): Promise {\n for (const k of keys) {\n const rows = await run(`SELECT key FROM blocked WHERE key = ?`, [k]);\n if (rows[0]) return k;\n }\n return null;\n}\n\nexport async function addBlocked(keys: string[], fromUuid: string, reason: string | null) {\n for (const k of keys) {\n await run(\n `INSERT INTO blocked (key, from_uuid, reason, at) VALUES (?, ?, ?, ?)\n ON CONFLICT(key) DO UPDATE SET from_uuid = excluded.from_uuid, at = excluded.at`,\n [k, fromUuid, reason, now()],\n );\n }\n}\n\n/**\n * Attempts first, then arrival order: a post that has already failed twice must not keep\n * elbowing ahead of posts that have never been tried.\n *\n * `includeDry` is what stops a dry run from burning craigslist requests forever. A dry row\n * stays 'queued' on purpose (it has not been judged), but re-rehearsing it every 15 minutes\n * would cost a detail call plus five image fetches per row per cron — four rows left overnight\n * is ~2,300 requests a day, over craigslist's own written 1,000-page budget and for nothing.\n * So: dry run skips rows already rehearsed once; a run with a real key takes them.\n */\nexport async function listQueued(limit: number, includeDry: boolean): Promise {\n const dryClause = includeDry ? \"\" : ` AND COALESCE(verdict, '') != 'dry'`;\n return await run(\n `SELECT * FROM posts WHERE status = 'queued'${dryClause}\n ORDER BY COALESCE(attempts, 0) ASC, queued_at ASC LIMIT ?`,\n [limit],\n );\n}\n\n/**\n * The columns the dedup ladder needs, for every post we have already resolved.\n * 'working' rows are mid-judgement in some other isolate and 'gone' rows are left out on\n * purpose: they were never judged, and an item that comes back\n * after its earlier post expired deserves a fresh look rather than an inherited nothing.\n * DRY_RUN rows are left out for the same reason — 'dry' is not a verdict, and a repost must\n * not inherit it.\n */\nexport async function dedupRows(limit = 5000): Promise {\n return await run(\n `SELECT uuid, pid, repost_of, image_ids, title, price, lat, lon, verdict, status, dup_of\n FROM posts\n WHERE status NOT IN ('queued', 'working', 'gone') AND COALESCE(verdict, '') != 'dry'\n ORDER BY rowid DESC LIMIT ?`,\n [limit],\n );\n}\n\n/**\n * Take exclusive ownership of a queued row. The email handler now judges inline, so a cron run\n * and an inbound alert (or two alerts seconds apart) can be in the loop at the same time and\n * would otherwise both pick the same row. The `WHERE status = 'queued'` is the entire lock:\n * SQLite applies the UPDATE to one caller and reports 0 rows affected to the other.\n */\nexport async function claimPost(uuid: string): Promise {\n const n = await write(\n `UPDATE posts SET status = 'working', claim_at = ? WHERE uuid = ? AND status = 'queued'`,\n [now(), uuid],\n );\n return n === 1;\n}\n\n/**\n * Put back rows whose claim is older than the drain could possibly be — the isolate that\n * claimed them was killed mid-post. Returns how many were freed.\n */\nexport async function releaseAbandonedClaims(ttlMs: number): Promise {\n const cutoff = new Date(Date.now() - ttlMs).toISOString();\n return await write(\n `UPDATE posts SET status = 'queued'\n WHERE status = 'working' AND (claim_at IS NULL OR claim_at < ?)`,\n [cutoff],\n );\n}\n\nexport async function updatePost(uuid: string, fields: Record): Promise {\n const keys = Object.keys(fields);\n if (!keys.length) return;\n await run(\n `UPDATE posts SET ${keys.map((k) => `${k} = ?`).join(\", \")} WHERE uuid = ?`,\n [...keys.map((k) => fields[k]), uuid],\n );\n}\n\n/** A legacy pid-only row becomes a normal row once the 301 gives us its uuid. */\nexport async function renameUuid(oldUuid: string, newUuid: string): Promise {\n await run(`UPDATE posts SET uuid = ? WHERE uuid = ?`, [newUuid, oldUuid]);\n}\n\nexport async function addFeedback(uuid: string, kind: string, reason: string | null) {\n await run(`INSERT INTO feedback (uuid, kind, reason, at) VALUES (?, ?, ?, ?)`, [\n uuid,\n kind,\n reason,\n now(),\n ]);\n}\n\nexport interface Example {\n title: string;\n price: number | null;\n reason: string | null;\n}\n\n/** Few-shot examples for the judge: what he rejected (or liked) on this saved search. */\nexport async function feedbackExamples(\n search: string,\n kind: \"nope\" | \"yes\",\n limit: number,\n): Promise {\n // For a rejection the only honest reason is the one he typed. p.reason is the judge's own\n // argument for why the listing was GOOD — handing that back as the explanation for a \"nope\"\n // teaches exactly the wrong thing. A 'yes' may fall back to it: there it agrees with him.\n const reasonExpr = kind === \"nope\" ? \"f.reason\" : \"COALESCE(f.reason, p.reason)\";\n const rows = await run(\n `SELECT p.title AS title, p.price AS price, ${reasonExpr} AS reason\n FROM feedback f JOIN posts p ON p.uuid = f.uuid\n WHERE f.kind = ? AND p.search = ?\n ORDER BY f.at DESC LIMIT ?`,\n [kind, search, limit],\n );\n return rows.map((r) => ({\n title: String(r.title ?? \"\"),\n price: r.price === null ? null : Number(r.price),\n reason: r.reason === null ? null : String(r.reason),\n }));\n}\n\nexport async function dumpTables(limit = 50): Promise> {\n return {\n posts: await run(`SELECT * FROM posts ORDER BY rowid DESC LIMIT ?`, [limit]),\n blocked: await run(`SELECT * FROM blocked ORDER BY rowid DESC LIMIT ?`, [limit]),\n feedback: await run(`SELECT * FROM feedback ORDER BY rowid DESC LIMIT ?`, [limit]),\n };\n}\n\nexport async function hasFeedback(uuid: string, kind: string): Promise {\n const rows = await run(`SELECT 1 AS x FROM feedback WHERE uuid = ? AND kind = ? LIMIT 1`, [\n uuid,\n kind,\n ]);\n return rows.length > 0;\n}\n\nexport async function setFeedbackReason(uuid: string, kind: string, reason: string) {\n await run(`UPDATE feedback SET reason = ? WHERE uuid = ? AND kind = ?`, [reason, uuid, kind]);\n}\n\n","deleted",false,"label",[47,24],"lib","c358f270-b3fc-477f-a942-c58f7915cb3d",["D",1789025029995],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","","imageUrl","userId","019bcf70-5321-758b-8dfa-2be50f1fc9fb","pinnedFileId","e7e3fae2-ace8-11f1-8828-1607ee4eb77e","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","019bcf70-5321-758b-8dfa-2ea07f250c13","tursoName","c358f270b3fc477fa942c58f7915cb3d","tursoHostname","c358f270b3fc477fa942c58f7915cb3d-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789459281818],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_9":10,"_108":104},"org",{"_9":10,"_102":-5,"_103":104,"_58":59,"_105":106,"_107":44},"branch",{"_37":92,"_11":93,"_22":30,"_94":48,"_95":-5,"_96":-5,"_97":-5,"_98":-5,"_99":-5,"_100":12,"_101":10},"isOwned",62,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","avatar","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18zOFBxeE4yTklpRW16aUxzZzlSaTZRa3BWN0wifQ","tier","free","canRestrictVals","profileImageUrl","ctx",{"_85":-5,"_142":143,"_144":145,"_146":147,"_148":44,"_149":44,"_150":44,"_151":34,"_152":-5,"_153":-7,"_154":-7,"_155":66,"_156":44,"_157":44,"_158":-5,"_159":44,"_160":44,"_161":44,"_162":-5},"ENV",{"_113":114,"_115":116,"_117":93,"_118":119,"_120":121,"_122":123,"_124":125,"_126":127,"_128":129,"_130":131,"_132":133,"_134":135,"_136":137,"_138":139,"_140":141},"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"]