[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8},"actionData","errors","root",{"_105":106,"_107":108},"routes/embed.x.$handle.$name.$",{"_9":10,"_11":12,"_13":14,"_15":16,"_17":18,"_19":20,"_21":-5},"handle","yuxiaoy","name","smzdm","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":-5,"_58":42,"_59":-5,"_60":-5,"_61":62,"_63":64,"_65":66,"_67":68,"_69":70,"_71":-5,"_72":73,"_74":73,"_75":76,"_77":76,"_78":76,"_79":73,"_80":73,"_81":82,"_83":84,"_85":86,"_87":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/yuxiaoy/smzdm/code/db.ts","extension","id","1febd27d-9bee-4863-b9af-fd6494353665","fileId","1b89192c-8f57-11f1-a00b-1607ee4eb77e","parentId","branchId","8f6ec682-59da-11f1-9bcd-ee650bb23af1","branchVersion",45,"latest",true,"createdAt",["D",1786638456082],"version",5,"fileType","script","content","// Persistence layer, backed by val.town's SQLite (std/sqlite).\n\nimport { sqlite } from \"https://esm.town/v/std/sqlite\";\nimport {\n DEFAULT_SETTINGS,\n EMPTY_STATS,\n nowSec,\n SEEN_LIMIT,\n type Settings,\n type Stats,\n} from \"./shared.ts\";\n\nconst DEALS_TABLE = \"smzdm_deals_v1\";\nconst KV_TABLE = \"smzdm_kv_v1\";\n\nexport interface SentDeal {\n articleId: string;\n title: string;\n url: string;\n mall: string;\n priceText: string;\n priceNum: number | null;\n bestPrice: number | null;\n worthy: number;\n publishedAt: number;\n firstSentAt: number;\n lastSentAt: number;\n sendCount: number;\n}\n\ninterface ExecuteResult {\n columns?: string[];\n rows?: unknown[];\n rowsAffected?: number;\n}\n\n/** Everything we bind is a scalar, which keeps us inside libSQL's InValue. */\ntype SqlArg = string | number | null;\n\n/**\n * std/sqlite returns rows as positional arrays; some versions also expose\n * named access. Normalise to plain objects so callers never depend on it.\n */\nfunction toObjects(result: ExecuteResult): Record[] {\n const columns = result.columns ?? [];\n const rows = result.rows ?? [];\n return rows.map((row) => {\n const record: Record = {};\n columns.forEach((column, index) => {\n record[column] = Array.isArray(row)\n ? row[index]\n : (row as Record)[column];\n });\n return record;\n });\n}\n\nasync function query(\n sql: string,\n args: SqlArg[] = [],\n): Promise[]> {\n const result = await sqlite.execute({ sql, args });\n return toObjects(result as unknown as ExecuteResult);\n}\n\nasync function run(sql: string, args: SqlArg[] = []): Promise {\n await sqlite.execute({ sql, args });\n}\n\nasync function batch(\n statements: { sql: string; args: SqlArg[] }[],\n): Promise {\n if (statements.length === 0) return;\n const client = sqlite as {\n execute: (statement: { sql: string; args: SqlArg[] }) => Promise;\n batch?: (statements: { sql: string; args: SqlArg[] }[]) => Promise;\n };\n if (statements.length > 1 && client.batch) {\n await client.batch(statements);\n return;\n }\n for (const statement of statements) await client.execute(statement);\n}\n\nlet ready: Promise | undefined;\n\nexport function initDb(): Promise {\n // A failed init must not be cached, or one transient error would break the\n // isolate for its whole lifetime.\n ready ??= createSchema().catch((error) => {\n ready = undefined;\n throw error;\n });\n return ready;\n}\n\nasync function createSchema(): Promise {\n await run(`\n CREATE TABLE IF NOT EXISTS ${DEALS_TABLE} (\n article_id TEXT PRIMARY KEY,\n title TEXT NOT NULL,\n url TEXT NOT NULL,\n mall TEXT NOT NULL DEFAULT '',\n price_text TEXT NOT NULL DEFAULT '',\n price_num REAL,\n best_price REAL,\n worthy INTEGER NOT NULL DEFAULT 0,\n published_at INTEGER NOT NULL DEFAULT 0,\n first_sent_at INTEGER NOT NULL,\n last_sent_at INTEGER NOT NULL,\n send_count INTEGER NOT NULL DEFAULT 1\n )\n `);\n await run(\n `CREATE INDEX IF NOT EXISTS idx_${DEALS_TABLE}_first_sent\n ON ${DEALS_TABLE}(first_sent_at)`,\n );\n await run(`\n CREATE TABLE IF NOT EXISTS ${KV_TABLE} (\n key TEXT PRIMARY KEY,\n value TEXT NOT NULL\n )\n `);\n}\n\n// --- Key/value settings -----------------------------------------------------\n\nasync function readKv(key: string): Promise {\n const rows = await query(\n `SELECT value FROM ${KV_TABLE} WHERE key = ?`,\n [key],\n );\n if (rows.length === 0) return undefined;\n try {\n return JSON.parse(String(rows[0].value)) as T;\n } catch {\n return undefined;\n }\n}\n\nasync function writeKv(key: string, value: unknown): Promise {\n await saveKv({ [key]: value });\n}\n\nexport async function saveKv(entries: Record): Promise {\n const statements = Object.entries(entries).map(([key, value]) => ({\n sql: `INSERT INTO ${KV_TABLE} (key, value) VALUES (?, ?)\n ON CONFLICT(key) DO UPDATE SET value = excluded.value`,\n args: [key, JSON.stringify(value)] as SqlArg[],\n }));\n await batch(statements);\n}\n\nexport interface Snapshot {\n settings: Settings;\n rules: string[];\n cursor: number;\n seenIds: string[];\n stats: Stats;\n}\n\n/** One round-trip for every KV the spider needs. */\nexport async function loadSnapshot(): Promise {\n const rows = await query(`SELECT key, value FROM ${KV_TABLE}`);\n const map = new Map();\n for (const row of rows) {\n try {\n map.set(String(row.key), JSON.parse(String(row.value)));\n } catch {\n // Ignore a corrupt row rather than failing the whole run.\n }\n }\n return {\n settings: {\n ...DEFAULT_SETTINGS,\n ...(map.get(\"settings\") as Partial | undefined),\n },\n rules: Array.isArray(map.get(\"rules\")) ? map.get(\"rules\") as string[] : [],\n cursor: Number(map.get(\"cursor\") ?? 0) || 0,\n seenIds: Array.isArray(map.get(\"seen_ids\"))\n ? map.get(\"seen_ids\") as string[]\n : [],\n stats: {\n ...EMPTY_STATS,\n ...(map.get(\"stats\") as Partial | undefined),\n },\n };\n}\n\nexport async function getSettings(): Promise {\n const stored = await readKv>(\"settings\");\n return { ...DEFAULT_SETTINGS, ...(stored ?? {}) };\n}\n\nexport async function patchSettings(\n patch: Partial,\n): Promise {\n const merged = { ...(await getSettings()), ...patch };\n await writeKv(\"settings\", merged);\n return merged;\n}\n\nexport async function getRules(): Promise {\n return (await readKv(\"rules\")) ?? [];\n}\n\nexport async function setRules(rules: string[]): Promise {\n await writeKv(\"rules\", rules);\n}\n\nexport async function getCursor(): Promise {\n return (await readKv(\"cursor\")) ?? 0;\n}\n\nexport async function setCursor(publishedAt: number): Promise {\n await writeKv(\"cursor\", publishedAt);\n}\n\nexport async function getSeenIds(): Promise {\n return (await readKv(\"seen_ids\")) ?? [];\n}\n\nexport function mergeSeen(previous: string[], ids: string[]): string[] {\n const have = new Set(previous);\n const next = [...previous];\n for (const id of ids) {\n if (have.has(id)) continue;\n have.add(id);\n next.push(id);\n }\n return next.length > SEEN_LIMIT ? next.slice(next.length - SEEN_LIMIT) : next;\n}\n\nexport async function getStats(): Promise {\n const stored = await readKv>(\"stats\");\n return { ...EMPTY_STATS, ...(stored ?? {}) };\n}\n\nexport async function setStats(stats: Stats): Promise {\n await writeKv(\"stats\", stats);\n}\n\n// --- Push history -----------------------------------------------------------\n\nfunction toSentDeal(row: Record): SentDeal {\n const number = (value: unknown): number | null =>\n value === null || value === undefined ? null : Number(value);\n return {\n articleId: String(row.article_id),\n title: String(row.title ?? \"\"),\n url: String(row.url ?? \"\"),\n mall: String(row.mall ?? \"\"),\n priceText: String(row.price_text ?? \"\"),\n priceNum: number(row.price_num),\n bestPrice: number(row.best_price),\n worthy: Number(row.worthy ?? 0),\n publishedAt: Number(row.published_at ?? 0),\n firstSentAt: Number(row.first_sent_at ?? 0),\n lastSentAt: Number(row.last_sent_at ?? 0),\n sendCount: Number(row.send_count ?? 0),\n };\n}\n\n/** One round-trip lookup for a whole batch of candidates. */\nexport async function findSentDeals(\n articleIds: string[],\n): Promise> {\n const found = new Map();\n if (articleIds.length === 0) return found;\n\n const batchSize = 200;\n for (let start = 0; start < articleIds.length; start += batchSize) {\n const batch = articleIds.slice(start, start + batchSize);\n const placeholders = batch.map(() => \"?\").join(\",\");\n const rows = await query(\n `SELECT * FROM ${DEALS_TABLE} WHERE article_id IN (${placeholders})`,\n batch,\n );\n for (const row of rows) {\n const deal = toSentDeal(row);\n found.set(deal.articleId, deal);\n }\n }\n return found;\n}\n\nexport interface RecordInput {\n articleId: string;\n title: string;\n url: string;\n mall: string;\n priceText: string;\n priceNum: number | null;\n worthy: number;\n publishedAt: number;\n}\n\nconst UPSERT_DEAL = `\nINSERT INTO ${DEALS_TABLE} (\n article_id, title, url, mall, price_text, price_num, best_price,\n worthy, published_at, first_sent_at, last_sent_at, send_count\n) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1)\nON CONFLICT(article_id) DO UPDATE SET\n title = excluded.title,\n url = excluded.url,\n mall = excluded.mall,\n price_text = excluded.price_text,\n price_num = excluded.price_num,\n best_price = CASE\n WHEN excluded.price_num IS NOT NULL\n AND (best_price IS NULL OR excluded.price_num < best_price)\n THEN excluded.price_num ELSE best_price END,\n worthy = excluded.worthy,\n last_sent_at = excluded.last_sent_at,\n send_count = send_count + 1`;\n\nfunction upsertArgs(input: RecordInput, timestamp: number): SqlArg[] {\n return [\n input.articleId,\n input.title,\n input.url,\n input.mall,\n input.priceText,\n input.priceNum,\n input.priceNum,\n input.worthy,\n input.publishedAt,\n timestamp,\n timestamp,\n ];\n}\n\nexport async function recordSent(input: RecordInput): Promise {\n await recordSentMany([input]);\n}\n\nexport async function recordSentMany(inputs: RecordInput[]): Promise {\n if (inputs.length === 0) return;\n const timestamp = nowSec();\n await batch(\n inputs.map((input) => ({\n sql: UPSERT_DEAL,\n args: upsertArgs(input, timestamp),\n })),\n );\n}\n\nexport async function listSentDeals(\n limit: number,\n offset: number,\n): Promise {\n const rows = await query(\n `SELECT * FROM ${DEALS_TABLE}\n ORDER BY first_sent_at DESC LIMIT ? OFFSET ?`,\n [limit, offset],\n );\n return rows.map(toSentDeal);\n}\n\nexport async function countSentDeals(): Promise {\n const rows = await query(`SELECT COUNT(*) AS total FROM ${DEALS_TABLE}`);\n return Number(rows[0]?.total ?? 0);\n}\n\nexport async function clearSentDeals(): Promise {\n await run(`DELETE FROM ${DEALS_TABLE}`);\n}\n\n/** Ages out old rows so the table cannot grow without bound. */\nexport async function pruneSentDeals(retentionDays: number): Promise {\n await run(\n `DELETE FROM ${DEALS_TABLE} WHERE first_sent_at < ?`,\n [nowSec() - retentionDays * 86_400],\n );\n}\n","deleted",false,"label",[14],"8f6e1d7c-59da-11f1-9bcd-ee650bb23af1",["D",1779893066929],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","","imageUrl","userId","96c2793d-57c8-41d2-a49a-d1ee82f9dd24","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","93515af8-c7b7-459c-9bf2-3bb3df310a75","tursoName","8f6e1d7c59da11f19bcdee650bb23af1","tursoHostname","8f6e1d7c59da11f19bcdee650bb23af1-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789494036282],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_9":10,"_104":100},"org",{"_9":10,"_98":-5,"_99":100,"_55":56,"_101":102,"_103":42},"branch",{"_35":88,"_11":89,"_22":28,"_90":45,"_91":-5,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":12,"_97":10},"isOwned",51,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","avatar","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJObXd1QWdCZmVtaUh0bGU5TkswOG1sM0xheiIsImluaXRpYWxzIjoiRlkifQ","tier","free","canRestrictVals","profileImageUrl","ctx",{"_81":-5,"_138":139,"_140":141,"_142":143,"_144":42,"_145":42,"_146":42,"_147":32,"_148":-5,"_149":-7,"_150":-7,"_151":62,"_152":42,"_153":42,"_154":-5,"_155":42,"_156":42,"_157":42,"_158":-5},"ENV",{"_109":110,"_111":112,"_113":89,"_114":115,"_116":117,"_118":119,"_120":121,"_122":123,"_124":125,"_126":127,"_128":129,"_130":131,"_132":133,"_134":135,"_136":137},"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"]