[{"_1":2,"_181":-5,"_182":-5},"loaderData",{"_3":4,"_93":94},"root",{"_5":6,"_36":37,"_66":67},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":-5,"_32":33,"_34":20,"_35":20},"auth",{"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20,"_21":22},"sessionClaims","sessionId","sessionStatus","userId","actor","orgId","orgRole","orgSlug","orgPermissions","factorVerificationAge","isAuthenticated",false,"tokenType","session_token","user","preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesChatPanelState","notificationCount",0,"flagDenols","isTeamOrgMember","ENV",{"_38":39,"_40":41,"_42":43,"_44":45,"_46":47,"_48":49,"_50":51,"_52":53,"_54":55,"_56":57,"_58":59,"_60":61,"_62":63,"_64":65},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","6050acf0705e6826bb1b68e27572c5b8979c5bd9","RENDER_GIT_BRANCH","main","CLERK_PUBLISHABLE_KEY","pk_live_Y2xlcmsudmFsLnRvd24k","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","API_URL","https://api.val.town","clerkState",{"_68":69},"__internal_clerk_state",{"_70":71,"_72":47,"_73":74,"_75":74,"_76":20,"_77":74,"_78":74,"_79":74,"_80":74,"_81":82,"_91":20,"_92":20},"__clerk_ssr_state",{"_21":22,"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20},"__publishableKey","__proxyUrl","","__domain","__isSatellite","__signInUrl","__signUpUrl","__afterSignInUrl","__afterSignUpUrl","__clerk_debug",{"_83":20,"_19":20,"_84":74,"_85":86,"_87":74,"_88":47,"_89":20,"_90":74},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_95":96,"_97":98,"_99":100,"_101":102,"_155":156,"_178":179,"_180":-5},"handle","vvampa","name","dns","path","lib/db.ts","project",{"_103":104,"_97":98,"_105":106,"_107":-5,"_108":20,"_109":110,"_111":-5,"_112":-5,"_12":113,"_114":-5,"_115":20,"_116":-5,"_117":-5,"_118":33,"_14":119,"_120":121,"_122":123,"_124":125,"_126":125,"_127":128,"_129":128,"_130":128,"_131":125,"_132":125,"_23":133,"_136":137,"_141":142,"_154":20},"id","0fef6ee0-060e-11f1-b9bd-42dde27851f2","createdAt",["D",1770679289177],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","4014c640-61ba-11f0-852d-15ceec715ebe","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount","4014c641-61ba-11f0-852d-15ceec715ebe","tursoName","0fef6ee0060e11f1b9bd42dde27851f2","tursoHostname","0fef6ee0060e11f1b9bd42dde27851f2-val-town.aws-us-east-2.turso.io","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_95":96,"_134":135},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJ6dlV1b1h4VzRxbTBVblhueldOUjc4R3N1ViJ9","org",{"_95":96,"_138":135,"_12":113,"_139":140},"avatar","tier","free","branch",{"_143":144,"_97":45,"_103":145,"_146":104,"_147":-5,"_148":-5,"_149":-5,"_150":-5,"_151":-5,"_152":98,"_153":96},"version",67,"0ff07f1a-060e-11f1-b9bd-42dde27851f2","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_103":157,"_97":158,"_159":160,"_161":162,"_163":145,"_164":165,"_166":167,"_105":168,"_143":169,"_170":171,"_172":173,"_174":20,"_175":-5,"_99":176},"6991b64a-0610-11f1-b35d-42dde27851f2","db.ts","fileId","4007c4ae-0610-11f1-8142-42dde27851f2","parentId","34b6594e-0610-11f1-966e-42dde27851f2","branchId","branchVersion",29,"latest",true,["D",1770680298548],2,"fileType","script","content","import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { nowMs } from \"./util.ts\";\n\nlet schemaReady = false;\n\nexport async function ensureSchema(): Promise {\n if (schemaReady) return;\n\n await sqlite.execute(`\n create table if not exists runs (\n run_id text primary key,\n kind text not null,\n status text not null,\n parent_run_id text,\n params_json text,\n created_at integer not null,\n updated_at integer not null,\n error text\n );\n `);\n\n await sqlite.execute(`\n create table if not exists run_events (\n run_id text not null,\n ts integer not null,\n phase text,\n ticker text,\n message text,\n data_json text\n );\n `);\n\n await sqlite.execute(`\n create index if not exists idx_run_events_run_ts\n on run_events(run_id, ts);\n `);\n\n await sqlite.execute(`\n create table if not exists run_holdings (\n run_id text not null,\n portfolio text,\n ticker text not null,\n buy_below text not null,\n primary key(run_id, ticker)\n );\n `);\n\n await sqlite.execute(`\n create table if not exists run_news (\n run_id text not null,\n ticker text not null,\n idx integer not null,\n title text,\n source text,\n date text,\n url text,\n primary key(run_id, ticker, idx)\n );\n `);\n\n await sqlite.execute(`\n create table if not exists selections (\n run_id text not null,\n ticker text not null,\n url text not null,\n primary key(run_id, ticker, url)\n );\n `);\n\n await sqlite.execute(`\n create table if not exists summaries (\n run_id text not null,\n ticker text not null,\n summary text not null,\n body_only text,\n buy_below text,\n urls_json text,\n sources_text text,\n local_validation_json text,\n audit_json text,\n created_at integer not null,\n primary key(run_id, ticker)\n );\n `);\n\n await sqlite.execute(`\n create table if not exists seen (\n ticker text not null,\n url text not null,\n title text,\n source text,\n date text,\n seen_at integer not null,\n primary key(ticker, url)\n );\n `);\n\n await sqlite.execute(`\n create index if not exists idx_seen_ticker_seenat\n on seen(ticker, seen_at desc);\n `);\n\n await sqlite.execute(`\n create table if not exists last_seen (\n ticker text primary key,\n last_seen_day text\n );\n `);\n\n await sqlite.execute(`\n create table if not exists article_cache (\n url text primary key,\n fetched_at integer not null,\n markdown text,\n title text,\n chars integer\n );\n `);\n\n await sqlite.execute(`\n create index if not exists idx_article_cache_fetched\n on article_cache(fetched_at);\n `);\n\n schemaReady = true;\n}\n\nexport async function logEvent(\n runId: string,\n phase: string,\n ticker: string | null,\n message: string,\n data?: unknown,\n) {\n await sqlite.execute({\n sql: `insert into run_events(run_id, ts, phase, ticker, message, data_json)\n values(?, ?, ?, ?, ?, ?)`,\n args: [\n runId,\n nowMs(),\n phase,\n ticker,\n message,\n data ? JSON.stringify(data) : null,\n ],\n });\n}\n\nexport async function createRun(\n kind: string,\n params: unknown,\n parentRunId: string | null,\n): Promise {\n const runId = crypto.randomUUID();\n const t = nowMs();\n await sqlite.execute({\n sql:\n `insert into runs(run_id, kind, status, parent_run_id, params_json, created_at, updated_at)\n values(?, ?, 'running', ?, ?, ?, ?)`,\n args: [runId, kind, parentRunId, JSON.stringify(params ?? {}), t, t],\n });\n return runId;\n}\n\nexport async function getRun(runId: string): Promise {\n const r = await sqlite.execute({\n sql:\n `select run_id, kind, status, parent_run_id, params_json, created_at, updated_at, error\n from runs where run_id = ?`,\n args: [runId],\n });\n const rows = r.rows as any[];\n return rows?.[0] || null;\n}\n\nexport async function updateRun(\n runId: string,\n patch: { status?: string; error?: string | null },\n) {\n const fields: string[] = [];\n const args: unknown[] = [];\n\n if (patch.status !== undefined) {\n fields.push(\"status = ?\");\n args.push(patch.status);\n }\n if (patch.error !== undefined) {\n fields.push(\"error = ?\");\n args.push(patch.error);\n }\n\n fields.push(\"updated_at = ?\");\n args.push(nowMs());\n args.push(runId);\n\n await sqlite.execute({\n sql: `update runs set ${fields.join(\", \")} where run_id = ?`,\n args,\n });\n}\n\nexport async function listEvents(\n runId: string,\n afterTs: number,\n): Promise {\n const r = await sqlite.execute({\n sql: `select run_id, ts, phase, ticker, message, data_json\n from run_events\n where run_id = ? and ts > ?\n order by ts asc\n limit 500`,\n args: [runId, afterTs],\n });\n\n return (r.rows as any[]).map((x) => ({\n run_id: String(x.run_id),\n ts: Number(x.ts),\n phase: x.phase ? String(x.phase) : \"\",\n ticker: x.ticker ? String(x.ticker) : \"\",\n message: x.message ? String(x.message) : \"\",\n data: x.data_json\n ? (() => {\n try {\n return JSON.parse(String(x.data_json));\n } catch {\n return null;\n }\n })()\n : null,\n }));\n}\n\n// holdings\nexport async function upsertRunHolding(\n runId: string,\n portfolio: string,\n ticker: string,\n buyBelow: string,\n) {\n await sqlite.execute({\n sql: `insert into run_holdings(run_id, portfolio, ticker, buy_below)\n values(?, ?, ?, ?)\n on conflict(run_id, ticker) do update set buy_below = excluded.buy_below`,\n args: [runId, portfolio, ticker, buyBelow],\n });\n}\n\nexport async function getRunHoldings(\n runId: string,\n): Promise> {\n const r = await sqlite.execute({\n sql:\n `select ticker, buy_below from run_holdings where run_id = ? order by ticker asc`,\n args: [runId],\n });\n return (r.rows as any[]).map((x) => ({\n ticker: String(x.ticker),\n buy_below: String(x.buy_below),\n }));\n}\n\n// run_news\nexport async function clearRunNews(runId: string) {\n await sqlite.execute({\n sql: `delete from run_news where run_id = ?`,\n args: [runId],\n });\n}\n\nexport async function insertRunNews(\n runId: string,\n ticker: string,\n idx: number,\n row: { title: string; source: string; date: string; url: string },\n) {\n await sqlite.execute({\n sql: `insert into run_news(run_id, ticker, idx, title, source, date, url)\n values(?, ?, ?, ?, ?, ?, ?)`,\n args: [runId, ticker, idx, row.title, row.source, row.date, row.url],\n });\n}\n\nexport async function getPreparedNews(\n runId: string,\n): Promise> {\n const r = await sqlite.execute({\n sql: `select ticker, idx, title, source, date, url\n from run_news where run_id = ?\n order by ticker asc, idx asc`,\n args: [runId],\n });\n\n const grouped: Record = {};\n for (const row of r.rows as any[]) {\n const t = String(row.ticker);\n if (!grouped[t]) grouped[t] = [];\n grouped[t].push({\n idx: Number(row.idx),\n title: row.title ? String(row.title) : \"\",\n source: row.source ? String(row.source) : \"\",\n date: row.date ? String(row.date) : \"\",\n url: row.url ? String(row.url) : \"\",\n });\n }\n return grouped;\n}\n\nexport async function getRunNewsMeta(\n prepareRunId: string,\n ticker: string,\n url: string,\n): Promise<{ title: string; source: string; date: string } | null> {\n const r = await sqlite.execute({\n sql: `select title, source, date from run_news\n where run_id = ? and ticker = ? and url = ?\n limit 1`,\n args: [prepareRunId, ticker, url],\n });\n const row = (r.rows as any[])?.[0];\n if (!row) return null;\n return {\n title: row.title ? String(row.title) : \"\",\n source: row.source ? String(row.source) : \"\",\n date: row.date ? String(row.date) : \"\",\n };\n}\n\n// selections\nexport async function storeSelections(\n runId: string,\n selections: Record,\n) {\n await sqlite.execute({\n sql: `delete from selections where run_id = ?`,\n args: [runId],\n });\n\n for (const [ticker, urls] of Object.entries(selections)) {\n if (!Array.isArray(urls)) continue;\n for (const u of urls) {\n const url = (u || \"\").trim();\n if (!url) continue;\n await sqlite.execute({\n sql: `insert into selections(run_id, ticker, url)\n values(?, ?, ?)\n on conflict(run_id, ticker, url) do nothing`,\n args: [runId, ticker, url],\n });\n }\n }\n}\n\nexport async function getSelections(\n runId: string,\n): Promise> {\n const r = await sqlite.execute({\n sql:\n `select ticker, url from selections where run_id = ? order by ticker asc`,\n args: [runId],\n });\n\n const m = new Map();\n for (const row of r.rows as any[]) {\n const t = String(row.ticker);\n const u = String(row.url || \"\").trim();\n if (!u) continue;\n if (!m.has(t)) m.set(t, []);\n m.get(t)!.push(u);\n }\n return m;\n}\n\n// summaries\nexport async function upsertSummary(runId: string, row: {\n ticker: string;\n summary: string;\n body_only: string;\n buy_below: string;\n urls_json: string;\n sources_text: string;\n local_validation_json: string;\n audit_json: string;\n}) {\n await sqlite.execute({\n sql:\n `insert into summaries(run_id, ticker, summary, body_only, buy_below, urls_json, sources_text, local_validation_json, audit_json, created_at)\n values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n on conflict(run_id, ticker) do update set\n summary = excluded.summary,\n body_only = excluded.body_only,\n buy_below = excluded.buy_below,\n urls_json = excluded.urls_json,\n sources_text = excluded.sources_text,\n local_validation_json = excluded.local_validation_json,\n audit_json = excluded.audit_json,\n created_at = excluded.created_at`,\n args: [\n runId,\n row.ticker,\n row.summary,\n row.body_only,\n row.buy_below,\n row.urls_json,\n row.sources_text,\n row.local_validation_json,\n row.audit_json,\n nowMs(),\n ],\n });\n}\n\nexport async function getSummaries(\n runId: string,\n): Promise> {\n const r = await sqlite.execute({\n sql:\n `select ticker, summary, buy_below from summaries where run_id = ? order by ticker asc`,\n args: [runId],\n });\n return (r.rows as any[]).map((x) => ({\n ticker: String(x.ticker),\n summary: String(x.summary),\n buy_below: x.buy_below ? String(x.buy_below) : \"\",\n }));\n}\n\nexport async function getSummaryRow(\n runId: string,\n ticker: string,\n): Promise {\n const r = await sqlite.execute({\n sql:\n `select summary, body_only, sources_text from summaries where run_id = ? and ticker = ? limit 1`,\n args: [runId, ticker],\n });\n return (r.rows as any[])?.[0] || null;\n}\n\n// seen + last_seen\nexport async function getLastSeenDay(ticker: string): Promise {\n const r = await sqlite.execute({\n sql: `select last_seen_day from last_seen where ticker = ?`,\n args: [ticker],\n });\n const rows = r.rows as any[];\n const v = rows?.[0]?.last_seen_day;\n return v ? String(v) : null;\n}\n\nexport async function setLastSeenDay(\n ticker: string,\n day: string,\n): Promise {\n await sqlite.execute({\n sql: `insert into last_seen(ticker, last_seen_day)\n values(?, ?)\n on conflict(ticker) do update set last_seen_day = excluded.last_seen_day`,\n args: [ticker, day],\n });\n}\n\nexport async function getSeenUrlSet(\n ticker: string,\n limit = 5000,\n): Promise> {\n const r = await sqlite.execute({\n sql: `select url from seen where ticker = ? order by seen_at desc limit ?`,\n args: [ticker, limit],\n });\n const set = new Set();\n for (const row of r.rows as any[]) {\n const u = row?.url ? String(row.url).trim() : \"\";\n if (u) set.add(u);\n }\n return set;\n}\n\nexport async function upsertSeen(\n ticker: string,\n row: { title: string; source: string; date: string; url: string },\n): Promise {\n const url = String(row.url || \"\").trim();\n if (!url) return;\n await sqlite.execute({\n sql: `insert into seen(ticker, url, title, source, date, seen_at)\n values(?, ?, ?, ?, ?, ?)\n on conflict(ticker, url) do update set\n title = excluded.title,\n source = excluded.source,\n date = excluded.date,\n seen_at = excluded.seen_at`,\n args: [ticker, url, row.title, row.source, row.date, nowMs()],\n });\n}\n\n// article cache\nexport async function getArticleCache(\n url: string,\n): Promise<{ markdown: string; title?: string; fetched_at: number } | null> {\n const r = await sqlite.execute({\n sql: `select markdown, title, fetched_at from article_cache where url = ?`,\n args: [url],\n });\n const row = (r.rows as any[])?.[0];\n if (!row?.markdown) return null;\n return {\n markdown: String(row.markdown),\n title: row.title ? String(row.title) : undefined,\n fetched_at: Number(row.fetched_at),\n };\n}\n\nexport async function putArticleCache(\n url: string,\n markdown: string,\n title: string | undefined,\n) {\n await sqlite.execute({\n sql: `insert into article_cache(url, fetched_at, markdown, title, chars)\n values(?, ?, ?, ?, ?)\n on conflict(url) do update set\n fetched_at = excluded.fetched_at,\n markdown = excluded.markdown,\n title = excluded.title,\n chars = excluded.chars`,\n args: [url, nowMs(), markdown, title || \"\", markdown.length],\n });\n}","deleted","label",[177,158],"lib","permalink","https://www.val.town/x/vvampa/dns/code/lib/db.ts","extension","actionData","errors"]