Market Discovery & Consensus

[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_367":368,"_369":370},"routes/_app.x.$handle.$name",{"_101":100,"_32":46,"_110":91,"_27":111,"_13":14,"_112":-5,"_11":113,"_114":115,"_116":117,"_118":119,"_120":121,"_20":-5,"_122":-5,"_123":-5,"_124":25,"_125":126,"_127":60,"_128":-5,"_129":-5,"_130":131},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_44":45,"_32":46,"_39":47,"_48":-5,"_49":25,"_50":51,"_52":51,"_53":54,"_55":-5,"_18":56,"_57":58,"_59":60,"_61":-5,"_62":-5,"_63":64,"_65":66,"_67":68,"_69":70,"_71":72,"_73":-5,"_74":75,"_76":75,"_77":78,"_79":78,"_80":78,"_81":75,"_82":75,"_83":84,"_85":86,"_87":88,"_89":25},"version",122,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":23,"_24":25,"_26":25,"_27":28,"_29":28},"file",{"_30":31,"_32":33,"_34":35,"_36":38,"_39":40,"_41":14,"_42":43,"_24":25},"previous",{"_30":31,"_32":33,"_34":35,"_36":37},"deleted",false,"deletedHere","path","lib/marketData.ts","latestPath","fileId","ee7a8de4-ac34-11f1-ac60-1607ee4eb77e","name","marketData.ts","fileType","script","content","// lib/marketData.ts — Market Data / Fundamentals layer for Discovery V2.\n//\n// ADDITIVE and OPTIONAL: provides price, momentum, valuation, fundamentals\n// and capital-flow signals to fill the four Discovery Score dimensions that\n// have been \"insufficient data\" (capitalFlow, fundamentalQuality, valuation,\n// priceMomentum).\n//\n// DESIGN:\n// - Provider ABSTRACTION: swap providers without touching the scorer or\n// Discovery V2. The default provider reads an env var (MARKETDATA_API_KEY)\n// and returns null when unset — so with no key, NO network call ever\n// happens and the four dimensions stay \"insufficient data\" (never 0).\n// - Data is persisted to its OWN `market_data` table, kept fully separate\n// from trader-consensus / posts data.\n// - refreshMarketData() is only ever invoked from an explicit manual route;\n// it is NOT part of the cron and is never auto-called.\n// - Missing fields stay NULL; scoring returns NaN for absent inputs (which\n// Discovery V2 treats as dimension-unavailable, never zero-as-negative).\n//\n// NO live provider call is made from this file during validation.\n\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\n\n// Reads the FMP key under its documented name (MARKETDATA_API_KEY). Falls\n// back to FPMKEY only because that is what is actually set on this val right\n// now (the key was rotated via a differently-named env var rather than an\n// in-place value update) — this keeps the provider working without\n// silently masking the mismatch; see the /market-data/test-ticker report,\n// which always names which var it actually found.\nfunction getApiKey(): { key: string | null; varName: string | null } {\n const primary = Deno.env.get(\"MARKETDATA_API_KEY\");\n if (primary && primary.length > 0) return { key: primary, varName: \"MARKETDATA_API_KEY\" };\n const fallback = Deno.env.get(\"FPMKEY\");\n if (fallback && fallback.length > 0) return { key: fallback, varName: \"FPMKEY\" };\n return { key: null, varName: null };\n}\n\nexport interface MarketData {\n ticker: string;\n price: number | null;\n price_change_1d: number | null;\n price_change_1w: number | null;\n price_change_1m: number | null;\n pe_ratio: number | null;\n pb_ratio: number | null;\n market_cap: number | null;\n ev: number | null;\n revenue_growth: number | null;\n gross_margin: number | null;\n roe: number | null;\n debt_to_equity: number | null;\n avg_volume: number | null;\n flow: number | null;\n source: string | null;\n fetched_at: string | null;\n}\n\n// ---- Provider abstraction ----\nexport interface MarketDataProvider {\n readonly name: string;\n /** Fetch market data for the given ticker symbols (bounded by caller). */\n fetchData(symbols: string[]): Promise[]>;\n}\n\n// Default provider (Financial Modeling Prep). Swap by replacing the factory.\nclass FmpProvider implements MarketDataProvider {\n readonly name = \"fmp\";\n private get key(): string | null {\n return getApiKey().key;\n }\n async fetchData(symbols: string[]): Promise[]> {\n const key = this.key;\n if (!key) return [];\n const out: Partial[] = [];\n for (const sym of symbols) {\n try {\n // Quote (price, change, pe, pb, market cap, ev, volume)\n const quote = await fetch(`https://financialmodelingprep.com/api/v3/quote/${sym}?apikey=${key}`).then((r) => r.ok ? r.json() : []);\n const q = Array.isArray(quote) ? quote[0] ?? {} : {};\n // Profile (industry, mktcap) + ratios (margin, roe, d/e) + growth (revenue growth)\n const profile = await fetch(`https://financialmodelingprep.com/api/v3/profile/${sym}?apikey=${key}`).then((r) => r.ok ? r.json() : []).then((a) => (Array.isArray(a) ? a[0] ?? {} : {}));\n const ratios = await fetch(`https://financialmodelingprep.com/api/v3/ratios/${sym}?period=quarter&limit=1&apikey=${key}`).then((r) => r.ok ? r.json() : []).then((a) => (Array.isArray(a) ? a[0] ?? {} : {}));\n const growth = await fetch(`https://financialmodelingprep.com/api/v3/financial-growth/${sym}?period=quarter&limit=1&apikey=${key}`).then((r) => r.ok ? r.json() : []).then((a) => (Array.isArray(a) ? a[0] ?? {} : {}));\n\n out.push({\n ticker: sym.toUpperCase(),\n price: typeof q.price === \"number\" ? q.price : null,\n price_change_1d: typeof q.changesPercentage === \"number\" ? q.changesPercentage : null,\n price_change_1w: null, // FMP free doesn't give 1w directly; momentum uses 1m/others if available\n price_change_1m: null,\n pe_ratio: typeof q.pe === \"number\" ? q.pe : null,\n pb_ratio: typeof q.pb === \"number\" ? q.pb : null,\n market_cap: typeof q.marketCap === \"number\" ? q.marketCap : null,\n ev: typeof q.ev === \"number\" ? q.ev : null,\n revenue_growth: typeof growth.revenueGrowth === \"number\" ? growth.revenueGrowth * 100 : null, // % \n gross_margin: typeof profile.grossProfitMargin === \"number\" ? profile.grossProfitMargin * 100 : null,\n roe: typeof ratios.returnOnEquity === \"number\" ? ratios.returnOnEquity * 100 : null,\n debt_to_equity: typeof ratios.debtToEquity === \"number\" ? ratios.debtToEquity / 100 : null, // normalized to 1=100%\n avg_volume: typeof q.volume === \"number\" ? q.volume : null,\n flow: null, // no reliable flow source; leave null -> dimension stays insufficient\n source: this.name,\n });\n } catch {\n // Per-symbol tolerance: a failure leaves that symbol out; never crash the batch.\n }\n }\n return out;\n }\n}\n\nexport async function getProvider(): Promise {\n if (!getApiKey().key) return null; // no key -> never auto-fetch\n return new FmpProvider();\n}\n\nexport async function ensureMarketDataSchema(): Promise {\n await sqlite.batch([\n { sql: `CREATE TABLE IF NOT EXISTS market_data (\n ticker TEXT PRIMARY KEY,\n price REAL, price_change_1d REAL, price_change_1w REAL, price_change_1m REAL,\n pe_ratio REAL, pb_ratio REAL, market_cap REAL, ev REAL,\n revenue_growth REAL, gross_margin REAL, roe REAL, debt_to_equity REAL,\n avg_volume REAL, flow REAL, source TEXT, fetched_at TEXT\n )` },\n ]);\n}\n\n// Persist provider rows into market_data (upsert). Only invoked via the\n// explicit manual refresh route — never by the cron.\nexport async function persistMarketData(rows: Partial[]): Promise {\n await ensureMarketDataSchema();\n const stmts = rows.filter((r) => r.ticker).map((r) => ({\n sql: `INSERT INTO market_data (\n ticker, price, price_change_1d, price_change_1w, price_change_1m, pe_ratio, pb_ratio,\n market_cap, ev, revenue_growth, gross_margin, roe, debt_to_equity, avg_volume, flow, source, fetched_at)\n VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)\n ON CONFLICT(ticker) DO UPDATE SET\n price=excluded.price, price_change_1d=excluded.price_change_1d, price_change_1w=excluded.price_change_1w,\n price_change_1m=excluded.price_change_1m, pe_ratio=excluded.pe_ratio, pb_ratio=excluded.pb_ratio,\n market_cap=excluded.market_cap, ev=excluded.ev, revenue_growth=excluded.revenue_growth,\n gross_margin=excluded.gross_margin, roe=excluded.roe, debt_to_equity=excluded.debt_to_equity,\n avg_volume=excluded.avg_volume, flow=excluded.flow, source=excluded.source, fetched_at=excluded.fetched_at`,\n args: [r.ticker!.toUpperCase(), r.price ?? null, r.price_change_1d ?? null, r.price_change_1w ?? null,\n r.price_change_1m ?? null, r.pe_ratio ?? null, r.pb_ratio ?? null, r.market_cap ?? null, r.ev ?? null,\n r.revenue_growth ?? null, r.gross_margin ?? null, r.roe ?? null, r.debt_to_equity ?? null,\n r.avg_volume ?? null, r.flow ?? null, r.source ?? null, new Date().toISOString()],\n }));\n for (let i = 0; i < stmts.length; i += 20) await sqlite.batch(stmts.slice(i, i + 20));\n return stmts.length;\n}\n\n// Manual refresh: bounded list of symbols. Returns immediately with a note if\n// the provider key is not configured (no live call).\nexport async function refreshMarketData(symbols: string[]): Promise<{ updated: number; note?: string }> {\n const provider = await getProvider();\n if (!provider) return { updated: 0, note: \"MARKETDATA_API_KEY is not configured; no market data fetched. Dimensions remain insufficient data.\" };\n const rows = await provider.fetchData(symbols);\n const updated = await persistMarketData(rows);\n return { updated, note: `provider: ${provider.name}` };\n}\n\n// ---------------------------------------------------------------------------\n// Controlled single-ticker diagnostic test (manual, explicit route only).\n// Performs the SAME 4 FMP calls as FmpProvider.fetchData for exactly ONE\n// symbol, but captures per-endpoint HTTP status/ok for reporting. Reuses\n// persistMarketData (so nulls stay null, never zeroed) and\n// scoreMarketDimensions (so the four enriched-dimension scores are computed\n// with the exact same logic Discovery V2 uses). Never invoked automatically;\n// never touches any symbol other than the one passed in.\n// ---------------------------------------------------------------------------\nexport interface ApiCallResult {\n endpoint: string;\n status: number | null;\n ok: boolean;\n note: string; // \"ok\" | \"authentication_failed\" | \"plan_restricted\" | \"network_error\" | \"error_
\"\n}\n\nexport interface SingleTickerTestReport {\n  ticker: string;\n  provider: string;\n  provider_configured: boolean;\n  auth_check: { status: number | null; ok: boolean; result: string } | null;\n  api_calls: ApiCallResult[];\n  fields_returned: Record;\n  fields_unavailable: string[];\n  stored: MarketData | null;\n  dimension_scores: Record;\n  errors: string[];\n}\n\nconst ALL_MARKET_FIELDS = [\n  \"price\", \"price_change_1d\", \"price_change_1w\", \"price_change_1m\",\n  \"pe_ratio\", \"pb_ratio\", \"market_cap\", \"ev\",\n  \"revenue_growth\", \"gross_margin\", \"roe\", \"debt_to_equity\",\n  \"avg_volume\", \"flow\",\n] as const;\n\nfunction noteForStatus(status: number | null): string {\n  if (status === null) return \"network_error\";\n  if (status >= 200 && status < 300) return \"ok\";\n  if (status === 401) return \"authentication_failed\";\n  if (status === 403) return \"plan_restricted\";\n  return `error_${status}`;\n}\n\nexport async function runSingleTickerMarketDataTest(ticker: string): Promise {\n  const sym = ticker.toUpperCase();\n  const { key, varName } = getApiKey();\n  const report: SingleTickerTestReport = {\n    ticker: sym, provider: \"fmp\", provider_configured: !!key,\n    auth_check: null,\n    api_calls: [], fields_returned: {}, fields_unavailable: [], stored: null,\n    dimension_scores: { fundamentalQuality: null, valuation: null, priceMomentum: null, capitalFlow: null },\n    errors: [],\n  };\n  if (!key) {\n    report.errors.push(\"Neither MARKETDATA_API_KEY nor FPMKEY is configured with a non-empty value; no API calls made.\");\n    return report;\n  }\n  if (varName !== \"MARKETDATA_API_KEY\") {\n    report.errors.push(`Note: MARKETDATA_API_KEY is not set — the key was read from the \"${varName}\" env var instead (fallback). Consider renaming it to MARKETDATA_API_KEY for consistency with the rest of this val's code/docs.`);\n  }\n\n  const call = async (label: string, url: string): Promise => {\n    try {\n      const res = await fetch(url);\n      const note = noteForStatus(res.status);\n      report.api_calls.push({ endpoint: label, status: res.status, ok: res.ok, note });\n      if (!res.ok) return null;\n      try { return await res.json(); } catch { return null; }\n    } catch (err) {\n      report.api_calls.push({ endpoint: label, status: null, ok: false, note: \"network_error\" });\n      report.errors.push(`${label}: ${(err as Error).message}`);\n      return null;\n    }\n  };\n\n  // Step 1: minimum-possible authentication test — exactly ONE request\n  // (quote) before spending any further calls. If the key is rejected\n  // outright (401) or the plan denies it (403), stop immediately and\n  // report — never chase a broken key through the other 3 endpoints.\n  const quoteRaw = await call(\"quote\", `https://financialmodelingprep.com/api/v3/quote/${sym}?apikey=${key}`);\n  const quoteCall = report.api_calls[0];\n  report.auth_check = { status: quoteCall.status, ok: quoteCall.ok, result: quoteCall.note };\n  if (!quoteCall.ok) {\n    report.errors.push(\n      quoteCall.note === \"authentication_failed\"\n        ? \"Authentication FAILED (401) on the minimal quote request — MARKETDATA_API_KEY was rejected by FMP. Stopped after 1 call; the other 3 endpoints were never called.\"\n        : quoteCall.note === \"plan_restricted\"\n        ? \"Authenticated, but quote access returned 403 — not included in this FMP plan tier. Stopped after 1 call; the other 3 endpoints were never called.\"\n        : `Unexpected status ${quoteCall.status ?? \"(network error)\"} on the auth-check quote request. Stopped after 1 call.`,\n    );\n    return report;\n  }\n  report.errors.push(\"Authentication check passed (200 on quote) — continuing with the 3 remaining endpoints.\");\n\n  // Step 2: authentication confirmed — the remaining 3 calls, each covering\n  // fields for the 4 required dimensions. Any individual endpoint that comes\n  // back 403 is a plan restriction (its fields stay NULL, not zero); this is\n  // reported per-endpoint below rather than aborting the whole test.\n  const q = Array.isArray(quoteRaw) ? quoteRaw[0] ?? {} : {};\n  const profileRaw = await call(\"profile\", `https://financialmodelingprep.com/api/v3/profile/${sym}?apikey=${key}`);\n  const profile = Array.isArray(profileRaw) ? profileRaw[0] ?? {} : {};\n  const ratiosRaw = await call(\"ratios\", `https://financialmodelingprep.com/api/v3/ratios/${sym}?period=quarter&limit=1&apikey=${key}`);\n  const ratios = Array.isArray(ratiosRaw) ? ratiosRaw[0] ?? {} : {};\n  const growthRaw = await call(\"financial-growth\", `https://financialmodelingprep.com/api/v3/financial-growth/${sym}?period=quarter&limit=1&apikey=${key}`);\n  const growth = Array.isArray(growthRaw) ? growthRaw[0] ?? {} : {};\n\n  for (const c of report.api_calls.slice(1)) {\n    if (!c.ok) {\n      report.errors.push(\n        c.note === \"plan_restricted\"\n          ? `${c.endpoint} endpoint returned 403 (plan restriction) — unavailable on this FMP plan; its fields stay NULL, not zero.`\n          : c.note === \"authentication_failed\"\n          ? `${c.endpoint} endpoint returned 401 despite the quote auth-check passing — inconsistent per-endpoint auth; its fields stay NULL.`\n          : `${c.endpoint} endpoint returned ${c.status ?? \"a network error\"}; its fields stay NULL.`,\n      );\n    }\n  }\n\n  const row: Partial = {\n    ticker: sym,\n    price: typeof q.price === \"number\" ? q.price : null,\n    price_change_1d: typeof q.changesPercentage === \"number\" ? q.changesPercentage : null,\n    price_change_1w: null,\n    price_change_1m: null,\n    pe_ratio: typeof q.pe === \"number\" ? q.pe : null,\n    pb_ratio: typeof q.pb === \"number\" ? q.pb : null,\n    market_cap: typeof q.marketCap === \"number\" ? q.marketCap : null,\n    ev: typeof q.ev === \"number\" ? q.ev : null,\n    revenue_growth: typeof growth.revenueGrowth === \"number\" ? growth.revenueGrowth * 100 : null,\n    gross_margin: typeof profile.grossProfitMargin === \"number\" ? profile.grossProfitMargin * 100 : null,\n    roe: typeof ratios.returnOnEquity === \"number\" ? ratios.returnOnEquity * 100 : null,\n    debt_to_equity: typeof ratios.debtToEquity === \"number\" ? ratios.debtToEquity / 100 : null,\n    avg_volume: typeof q.volume === \"number\" ? q.volume : null,\n    flow: null,\n    source: \"fmp\",\n  };\n\n  for (const f of ALL_MARKET_FIELDS) {\n    const v = (row as any)[f];\n    if (v === null || v === undefined) report.fields_unavailable.push(f);\n    else report.fields_returned[f] = v as number | string;\n  }\n\n  await persistMarketData([row]);\n  const stored = await sqlite.execute({ sql: `SELECT * FROM market_data WHERE ticker = ?`, args: [sym] });\n  report.stored = (stored.rows[0] as any) ?? null;\n\n  const dims = scoreMarketDimensions(report.stored as unknown as MarketData | null);\n  report.dimension_scores = {\n    fundamentalQuality: Number.isNaN(dims.fundamentalQuality) ? null : dims.fundamentalQuality,\n    valuation: Number.isNaN(dims.valuation) ? null : dims.valuation,\n    priceMomentum: Number.isNaN(dims.priceMomentum) ? null : dims.priceMomentum,\n    capitalFlow: Number.isNaN(dims.capitalFlow) ? null : dims.capitalFlow,\n  };\n\n  return report;\n}\n\n// Read persisted rows (read-only).\nexport async function marketDataApi(opts: { ticker?: string; limit?: number } = {}): Promise<{ rows: MarketData[]; configured: boolean; count: number }> {\n  await ensureMarketDataSchema();\n  const limit = Math.min(opts.limit ?? 200, 500);\n  const configured = !!getApiKey().key;\n  let res: any;\n  if (opts.ticker) {\n    res = await sqlite.execute({ sql: `SELECT * FROM market_data WHERE ticker = ?`, args: [opts.ticker.toUpperCase()] });\n  } else {\n    res = await sqlite.execute({ sql: `SELECT * FROM market_data ORDER BY fetched_at DESC LIMIT ?`, args: [limit] });\n  }\n  return { rows: res.rows as any[], configured, count: (res.rows as any[]).length };\n}\n\n// ---- Dimension scoring (transparent, deterministic, coverage-aware) ----\n// Returns NaN for any dimension lacking the required fields. Discovery V2\n// treats NaN as \"insufficient data\" — never a zero-that-looks-negative.\nfunction clamp(v: number, lo: number, hi: number): number { return Math.max(lo, Math.min(hi, v)); }\nfunction normScore(parts: { pts: number; max: number }[], weight: number): number {\n  const tot = parts.reduce((a, p) => a + p.pts, 0);\n  const mx = parts.reduce((a, p) => a + p.max, 0);\n  if (mx <= 0) return NaN;\n  return Math.round((weight * (tot / mx)) * 10) / 10;\n}\n\nexport function scoreMarketDimensions(md: MarketData | null): Record {\n  const s: Record = { fundamentalQuality: NaN, valuation: NaN, priceMomentum: NaN, capitalFlow: NaN };\n  if (!md) return s;\n\n  // Fundamental Quality (weight 10): revenue growth, gross margin, ROE, leverage.\n  const fParts: { pts: number; max: number }[] = [];\n  if (md.revenue_growth != null) fParts.push({ pts: clamp((md.revenue_growth + 20) / 40, 0, 1) * 4, max: 4 });\n  if (md.gross_margin != null) fParts.push({ pts: clamp(md.gross_margin / 60, 0, 1) * 3, max: 3 });\n  if (md.roe != null) fParts.push({ pts: clamp(md.roe / 30, 0, 1) * 2, max: 2 });\n  if (md.debt_to_equity != null) fParts.push({ pts: md.debt_to_equity <= 1 ? 1 : md.debt_to_equity <= 2 ? 0.5 : 0, max: 1 });\n  s.fundamentalQuality = normScore(fParts, 10);\n\n  // Valuation (weight 5): lower P/E and P/B => more attractive. Absence => NaN.\n  const vParts: { pts: number; max: number }[] = [];\n  if (md.pe_ratio != null && md.pe_ratio > 0) vParts.push({ pts: clamp((40 - md.pe_ratio) / 25, 0, 1), max: 3 });\n  if (md.pb_ratio != null && md.pb_ratio > 0) vParts.push({ pts: clamp((10 - md.pb_ratio) / 8, 0, 1), max: 2 });\n  s.valuation = normScore(vParts, 5);\n\n  // Price Momentum (weight 10): 1w and 1m % moves.\n  const mParts: { pts: number; max: number }[] = [];\n  if (md.price_change_1m != null) mParts.push({ pts: clamp(md.price_change_1m / 20, 0, 1) * 4, max: 4 });\n  if (md.price_change_1w != null) mParts.push({ pts: clamp(md.price_change_1w / 10, 0, 1) * 6, max: 6 });\n  if (md.price_change_1d != null && mParts.length === 0) mParts.push({ pts: clamp(md.price_change_1d / 5, 0, 1) * 10, max: 10 });\n  s.priceMomentum = normScore(mParts, 10);\n\n  // Market / Capital Flow (weight 10): volume proxy (or explicit flow when a\n  // provider supplies it). Absence => NaN (stays insufficient data).\n  if (md.flow != null) {\n    s.capitalFlow = Math.min(10, Math.round(clamp(md.flow, 0, 1) * 10 * 10) / 10);\n  } else if (md.avg_volume != null && md.avg_volume > 0) {\n    // Volume magnitude proxy: scales ~0 at ~1e5, ~5 at ~1e7, ~10 at ~1e9.\n    s.capitalFlow = Math.min(10, Math.round(Math.log10(1 + md.avg_volume / 100000) * 2.5 * 10) / 10);\n  }\n\n  return s;\n}\n","// lib/marketData.ts — Market Data / Fundamentals layer for Discovery V2.\n//\n// ADDITIVE and OPTIONAL: provides price, momentum, valuation, fundamentals\n// and capital-flow signals to fill the four Discovery Score dimensions that\n// have been \"insufficient data\" (capitalFlow, fundamentalQuality, valuation,\n// priceMomentum).\n//\n// DESIGN:\n//  - Provider ABSTRACTION: swap providers without touching the scorer or\n//    Discovery V2. The default provider reads an env var (MARKETDATA_API_KEY)\n//    and returns null when unset — so with no key, NO network call ever\n//    happens and the four dimensions stay \"insufficient data\" (never 0).\n//  - Data is persisted to its OWN `market_data` table, kept fully separate\n//    from trader-consensus / posts data.\n//  - refreshMarketData() is only ever invoked from an explicit manual route;\n//    it is NOT part of the cron and is never auto-called.\n//  - Missing fields stay NULL; scoring returns NaN for absent inputs (which\n//    Discovery V2 treats as dimension-unavailable, never zero-as-negative).\n//\n// NO live provider call is made from this file during validation.\n\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\n\n// Reads the FMP key under its documented name (MARKETDATA_API_KEY). Falls\n// back to FPMKEY only because that is what is actually set on this val right\n// now (the key was rotated via a differently-named env var rather than an\n// in-place value update) — this keeps the provider working without\n// silently masking the mismatch; see the /market-data/test-ticker report,\n// which always names which var it actually found.\nfunction getApiKey(): { key: string | null; varName: string | null } {\n  const primary = Deno.env.get(\"MARKETDATA_API_KEY\");\n  if (primary && primary.length > 0) return { key: primary, varName: \"MARKETDATA_API_KEY\" };\n  const fallback = Deno.env.get(\"FPMKEY\");\n  if (fallback && fallback.length > 0) return { key: fallback, varName: \"FPMKEY\" };\n  return { key: null, varName: null };\n}\n\nexport interface MarketData {\n  ticker: string;\n  price: number | null;\n  price_change_1d: number | null;\n  price_change_1w: number | null;\n  price_change_1m: number | null;\n  pe_ratio: number | null;\n  pb_ratio: number | null;\n  market_cap: number | null;\n  ev: number | null;\n  revenue_growth: number | null;\n  gross_margin: number | null;\n  roe: number | null;\n  debt_to_equity: number | null;\n  avg_volume: number | null;\n  flow: number | null;\n  source: string | null;\n  fetched_at: string | null;\n}\n\n// ---- Provider abstraction ----\nexport interface MarketDataProvider {\n  readonly name: string;\n  /** Fetch market data for the given ticker symbols (bounded by caller). */\n  fetchData(symbols: string[]): Promise[]>;\n}\n\n// Default provider (Financial Modeling Prep). Swap by replacing the factory.\nclass FmpProvider implements MarketDataProvider {\n  readonly name = \"fmp\";\n  private get key(): string | null {\n    return getApiKey().key;\n  }\n  async fetchData(symbols: string[]): Promise[]> {\n    const key = this.key;\n    if (!key) return [];\n    const out: Partial[] = [];\n    for (const sym of symbols) {\n      try {\n        // Migrated from legacy /api/v3/ to current /stable/ (symbol as a\n        // query param, not a path segment). Field mapping verified against\n        // official FMP documentation/examples before this migration:\n        //  - /stable/quote has NO pe/pb/ev fields (verified via an official\n        //    FMP article's full example response) — those come from ratios.\n        //  - quote's percent-change field is \"changePercentage\" under\n        //    /stable/ (v3 used \"changesPercentage\" — different name, not\n        //    just a path change).\n        //  - /stable/profile has no margin/fundamental fields at all\n        //    (verified: every source describing its contents lists only\n        //    price/mktcap/company-info/identifiers) — grossProfitMargin was\n        //    being read from the wrong endpoint even before this migration.\n        //    Dropped entirely: it contributed zero usable fields, so\n        //    removing it also cuts this from 4 provider calls/symbol to 3.\n        const quote = await fetch(`https://financialmodelingprep.com/stable/quote?symbol=${sym}&apikey=${key}`).then((r) => r.ok ? r.json() : []);\n        const q = Array.isArray(quote) ? quote[0] ?? {} : {};\n        const ratios = await fetch(`https://financialmodelingprep.com/stable/ratios?symbol=${sym}&period=quarter&limit=1&apikey=${key}`).then((r) => r.ok ? r.json() : []).then((a) => (Array.isArray(a) ? a[0] ?? {} : {}));\n        const growth = await fetch(`https://financialmodelingprep.com/stable/financial-growth?symbol=${sym}&period=quarter&limit=1&apikey=${key}`).then((r) => r.ok ? r.json() : []).then((a) => (Array.isArray(a) ? a[0] ?? {} : {}));\n\n        out.push({\n          ticker: sym.toUpperCase(),\n          price: typeof q.price === \"number\" ? q.price : null,\n          price_change_1d: typeof q.changePercentage === \"number\" ? q.changePercentage : null,\n          price_change_1w: null, // not provided by /stable/quote; unchanged from prior behavior\n          price_change_1m: null,\n          pe_ratio: typeof ratios.priceEarningsRatio === \"number\" ? ratios.priceEarningsRatio : null, // verified field name; sourced from ratios, not quote\n          pb_ratio: typeof ratios.priceToBookRatio === \"number\" ? ratios.priceToBookRatio : null, // best-available field name by naming-convention analogy — NOT verbatim-confirmed in official docs; if absent this stays null, never fabricated\n          market_cap: typeof q.marketCap === \"number\" ? q.marketCap : null,\n          ev: null, // /stable/quote has no \"ev\" field (verified); a dedicated enterprise-values call is out of scope for this migration\n          revenue_growth: typeof growth.revenueGrowth === \"number\" ? growth.revenueGrowth * 100 : null, // %\n          gross_margin: typeof ratios.grossProfitMargin === \"number\" ? ratios.grossProfitMargin * 100 : null, // moved from profile (verified wrong source) to ratios (verified correct source)\n          roe: typeof ratios.returnOnEquity === \"number\" ? ratios.returnOnEquity * 100 : null,\n          debt_to_equity: typeof ratios.debtToEquity === \"number\" ? ratios.debtToEquity / 100 : null, // normalized to 1=100%\n          avg_volume: typeof q.volume === \"number\" ? q.volume : null,\n          flow: null, // no reliable flow source; leave null -> dimension stays insufficient\n          source: this.name,\n        });\n      } catch {\n        // Per-symbol tolerance: a failure leaves that symbol out; never crash the batch.\n      }\n    }\n    return out;\n  }\n}\n\nexport async function getProvider(): Promise {\n  if (!getApiKey().key) return null; // no key -> never auto-fetch\n  return new FmpProvider();\n}\n\nexport async function ensureMarketDataSchema(): Promise {\n  await sqlite.batch([\n    { sql: `CREATE TABLE IF NOT EXISTS market_data (\n        ticker TEXT PRIMARY KEY,\n        price REAL, price_change_1d REAL, price_change_1w REAL, price_change_1m REAL,\n        pe_ratio REAL, pb_ratio REAL, market_cap REAL, ev REAL,\n        revenue_growth REAL, gross_margin REAL, roe REAL, debt_to_equity REAL,\n        avg_volume REAL, flow REAL, source TEXT, fetched_at TEXT\n      )` },\n  ]);\n}\n\n// Persist provider rows into market_data (upsert). Only invoked via the\n// explicit manual refresh route — never by the cron.\nexport async function persistMarketData(rows: Partial[]): Promise {\n  await ensureMarketDataSchema();\n  const stmts = rows.filter((r) => r.ticker).map((r) => ({\n    sql: `INSERT INTO market_data (\n      ticker, price, price_change_1d, price_change_1w, price_change_1m, pe_ratio, pb_ratio,\n      market_cap, ev, revenue_growth, gross_margin, roe, debt_to_equity, avg_volume, flow, source, fetched_at)\n      VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)\n      ON CONFLICT(ticker) DO UPDATE SET\n        price=excluded.price, price_change_1d=excluded.price_change_1d, price_change_1w=excluded.price_change_1w,\n        price_change_1m=excluded.price_change_1m, pe_ratio=excluded.pe_ratio, pb_ratio=excluded.pb_ratio,\n        market_cap=excluded.market_cap, ev=excluded.ev, revenue_growth=excluded.revenue_growth,\n        gross_margin=excluded.gross_margin, roe=excluded.roe, debt_to_equity=excluded.debt_to_equity,\n        avg_volume=excluded.avg_volume, flow=excluded.flow, source=excluded.source, fetched_at=excluded.fetched_at`,\n    args: [r.ticker!.toUpperCase(), r.price ?? null, r.price_change_1d ?? null, r.price_change_1w ?? null,\n      r.price_change_1m ?? null, r.pe_ratio ?? null, r.pb_ratio ?? null, r.market_cap ?? null, r.ev ?? null,\n      r.revenue_growth ?? null, r.gross_margin ?? null, r.roe ?? null, r.debt_to_equity ?? null,\n      r.avg_volume ?? null, r.flow ?? null, r.source ?? null, new Date().toISOString()],\n  }));\n  for (let i = 0; i < stmts.length; i += 20) await sqlite.batch(stmts.slice(i, i + 20));\n  return stmts.length;\n}\n\n// Manual refresh: bounded list of symbols. Returns immediately with a note if\n// the provider key is not configured (no live call).\nexport async function refreshMarketData(symbols: string[]): Promise<{ updated: number; note?: string }> {\n  const provider = await getProvider();\n  if (!provider) return { updated: 0, note: \"MARKETDATA_API_KEY is not configured; no market data fetched. Dimensions remain insufficient data.\" };\n  const rows = await provider.fetchData(symbols);\n  const updated = await persistMarketData(rows);\n  return { updated, note: `provider: ${provider.name}` };\n}\n\n// ---------------------------------------------------------------------------\n// Controlled single-ticker diagnostic test (manual, explicit route only).\n// Performs the SAME 4 FMP calls as FmpProvider.fetchData for exactly ONE\n// symbol, but captures per-endpoint HTTP status/ok for reporting. Reuses\n// persistMarketData (so nulls stay null, never zeroed) and\n// scoreMarketDimensions (so the four enriched-dimension scores are computed\n// with the exact same logic Discovery V2 uses). Never invoked automatically;\n// never touches any symbol other than the one passed in.\n// ---------------------------------------------------------------------------\nexport interface ApiCallResult {\n  endpoint: string;\n  status: number | null;\n  ok: boolean;\n  note: string; // \"ok\" | \"authentication_failed\" | \"plan_restricted\" | \"network_error\" | \"error_\"\n}\n\nexport interface SingleTickerTestReport {\n  ticker: string;\n  provider: string;\n  provider_configured: boolean;\n  auth_check: { status: number | null; ok: boolean; result: string } | null;\n  api_calls: ApiCallResult[];\n  fields_returned: Record;\n  fields_unavailable: string[];\n  stored: MarketData | null;\n  dimension_scores: Record;\n  errors: string[];\n}\n\nconst ALL_MARKET_FIELDS = [\n  \"price\", \"price_change_1d\", \"price_change_1w\", \"price_change_1m\",\n  \"pe_ratio\", \"pb_ratio\", \"market_cap\", \"ev\",\n  \"revenue_growth\", \"gross_margin\", \"roe\", \"debt_to_equity\",\n  \"avg_volume\", \"flow\",\n] as const;\n\nfunction noteForStatus(status: number | null): string {\n  if (status === null) return \"network_error\";\n  if (status >= 200 && status < 300) return \"ok\";\n  if (status === 401) return \"authentication_failed\";\n  if (status === 403) return \"plan_restricted\";\n  return `error_${status}`;\n}\n\nexport async function runSingleTickerMarketDataTest(ticker: string): Promise {\n  const sym = ticker.toUpperCase();\n  const { key, varName } = getApiKey();\n  const report: SingleTickerTestReport = {\n    ticker: sym, provider: \"fmp\", provider_configured: !!key,\n    auth_check: null,\n    api_calls: [], fields_returned: {}, fields_unavailable: [], stored: null,\n    dimension_scores: { fundamentalQuality: null, valuation: null, priceMomentum: null, capitalFlow: null },\n    errors: [],\n  };\n  if (!key) {\n    report.errors.push(\"Neither MARKETDATA_API_KEY nor FPMKEY is configured with a non-empty value; no API calls made.\");\n    return report;\n  }\n  if (varName !== \"MARKETDATA_API_KEY\") {\n    report.errors.push(`Note: MARKETDATA_API_KEY is not set — the key was read from the \"${varName}\" env var instead (fallback). Consider renaming it to MARKETDATA_API_KEY for consistency with the rest of this val's code/docs.`);\n  }\n\n  const call = async (label: string, url: string): Promise => {\n    try {\n      const res = await fetch(url);\n      const note = noteForStatus(res.status);\n      report.api_calls.push({ endpoint: label, status: res.status, ok: res.ok, note });\n      if (!res.ok) return null;\n      try { return await res.json(); } catch { return null; }\n    } catch (err) {\n      report.api_calls.push({ endpoint: label, status: null, ok: false, note: \"network_error\" });\n      report.errors.push(`${label}: ${(err as Error).message}`);\n      return null;\n    }\n  };\n\n  // Step 1: minimum-possible authentication test — exactly ONE request\n  // (quote) before spending any further calls. If the key is rejected\n  // outright (401) or the plan denies it (403), stop immediately and\n  // report — never chase a broken key through the other 3 endpoints.\n  const quoteRaw = await call(\"quote\", `https://financialmodelingprep.com/api/v3/quote/${sym}?apikey=${key}`);\n  const quoteCall = report.api_calls[0];\n  report.auth_check = { status: quoteCall.status, ok: quoteCall.ok, result: quoteCall.note };\n  if (!quoteCall.ok) {\n    report.errors.push(\n      quoteCall.note === \"authentication_failed\"\n        ? \"Authentication FAILED (401) on the minimal quote request — MARKETDATA_API_KEY was rejected by FMP. Stopped after 1 call; the other 3 endpoints were never called.\"\n        : quoteCall.note === \"plan_restricted\"\n        ? \"Authenticated, but quote access returned 403 — not included in this FMP plan tier. Stopped after 1 call; the other 3 endpoints were never called.\"\n        : `Unexpected status ${quoteCall.status ?? \"(network error)\"} on the auth-check quote request. Stopped after 1 call.`,\n    );\n    return report;\n  }\n  report.errors.push(\"Authentication check passed (200 on quote) — continuing with the 3 remaining endpoints.\");\n\n  // Step 2: authentication confirmed — the remaining 3 calls, each covering\n  // fields for the 4 required dimensions. Any individual endpoint that comes\n  // back 403 is a plan restriction (its fields stay NULL, not zero); this is\n  // reported per-endpoint below rather than aborting the whole test.\n  const q = Array.isArray(quoteRaw) ? quoteRaw[0] ?? {} : {};\n  const profileRaw = await call(\"profile\", `https://financialmodelingprep.com/api/v3/profile/${sym}?apikey=${key}`);\n  const profile = Array.isArray(profileRaw) ? profileRaw[0] ?? {} : {};\n  const ratiosRaw = await call(\"ratios\", `https://financialmodelingprep.com/api/v3/ratios/${sym}?period=quarter&limit=1&apikey=${key}`);\n  const ratios = Array.isArray(ratiosRaw) ? ratiosRaw[0] ?? {} : {};\n  const growthRaw = await call(\"financial-growth\", `https://financialmodelingprep.com/api/v3/financial-growth/${sym}?period=quarter&limit=1&apikey=${key}`);\n  const growth = Array.isArray(growthRaw) ? growthRaw[0] ?? {} : {};\n\n  for (const c of report.api_calls.slice(1)) {\n    if (!c.ok) {\n      report.errors.push(\n        c.note === \"plan_restricted\"\n          ? `${c.endpoint} endpoint returned 403 (plan restriction) — unavailable on this FMP plan; its fields stay NULL, not zero.`\n          : c.note === \"authentication_failed\"\n          ? `${c.endpoint} endpoint returned 401 despite the quote auth-check passing — inconsistent per-endpoint auth; its fields stay NULL.`\n          : `${c.endpoint} endpoint returned ${c.status ?? \"a network error\"}; its fields stay NULL.`,\n      );\n    }\n  }\n\n  const row: Partial = {\n    ticker: sym,\n    price: typeof q.price === \"number\" ? q.price : null,\n    price_change_1d: typeof q.changesPercentage === \"number\" ? q.changesPercentage : null,\n    price_change_1w: null,\n    price_change_1m: null,\n    pe_ratio: typeof q.pe === \"number\" ? q.pe : null,\n    pb_ratio: typeof q.pb === \"number\" ? q.pb : null,\n    market_cap: typeof q.marketCap === \"number\" ? q.marketCap : null,\n    ev: typeof q.ev === \"number\" ? q.ev : null,\n    revenue_growth: typeof growth.revenueGrowth === \"number\" ? growth.revenueGrowth * 100 : null,\n    gross_margin: typeof profile.grossProfitMargin === \"number\" ? profile.grossProfitMargin * 100 : null,\n    roe: typeof ratios.returnOnEquity === \"number\" ? ratios.returnOnEquity * 100 : null,\n    debt_to_equity: typeof ratios.debtToEquity === \"number\" ? ratios.debtToEquity / 100 : null,\n    avg_volume: typeof q.volume === \"number\" ? q.volume : null,\n    flow: null,\n    source: \"fmp\",\n  };\n\n  for (const f of ALL_MARKET_FIELDS) {\n    const v = (row as any)[f];\n    if (v === null || v === undefined) report.fields_unavailable.push(f);\n    else report.fields_returned[f] = v as number | string;\n  }\n\n  await persistMarketData([row]);\n  const stored = await sqlite.execute({ sql: `SELECT * FROM market_data WHERE ticker = ?`, args: [sym] });\n  report.stored = (stored.rows[0] as any) ?? null;\n\n  const dims = scoreMarketDimensions(report.stored as unknown as MarketData | null);\n  report.dimension_scores = {\n    fundamentalQuality: Number.isNaN(dims.fundamentalQuality) ? null : dims.fundamentalQuality,\n    valuation: Number.isNaN(dims.valuation) ? null : dims.valuation,\n    priceMomentum: Number.isNaN(dims.priceMomentum) ? null : dims.priceMomentum,\n    capitalFlow: Number.isNaN(dims.capitalFlow) ? null : dims.capitalFlow,\n  };\n\n  return report;\n}\n\n// Read persisted rows (read-only).\nexport async function marketDataApi(opts: { ticker?: string; limit?: number } = {}): Promise<{ rows: MarketData[]; configured: boolean; count: number }> {\n  await ensureMarketDataSchema();\n  const limit = Math.min(opts.limit ?? 200, 500);\n  const configured = !!getApiKey().key;\n  let res: any;\n  if (opts.ticker) {\n    res = await sqlite.execute({ sql: `SELECT * FROM market_data WHERE ticker = ?`, args: [opts.ticker.toUpperCase()] });\n  } else {\n    res = await sqlite.execute({ sql: `SELECT * FROM market_data ORDER BY fetched_at DESC LIMIT ?`, args: [limit] });\n  }\n  return { rows: res.rows as any[], configured, count: (res.rows as any[]).length };\n}\n\n// ---- Dimension scoring (transparent, deterministic, coverage-aware) ----\n// Returns NaN for any dimension lacking the required fields. Discovery V2\n// treats NaN as \"insufficient data\" — never a zero-that-looks-negative.\nfunction clamp(v: number, lo: number, hi: number): number { return Math.max(lo, Math.min(hi, v)); }\nfunction normScore(parts: { pts: number; max: number }[], weight: number): number {\n  const tot = parts.reduce((a, p) => a + p.pts, 0);\n  const mx = parts.reduce((a, p) => a + p.max, 0);\n  if (mx <= 0) return NaN;\n  return Math.round((weight * (tot / mx)) * 10) / 10;\n}\n\nexport function scoreMarketDimensions(md: MarketData | null): Record {\n  const s: Record = { fundamentalQuality: NaN, valuation: NaN, priceMomentum: NaN, capitalFlow: NaN };\n  if (!md) return s;\n\n  // Fundamental Quality (weight 10): revenue growth, gross margin, ROE, leverage.\n  const fParts: { pts: number; max: number }[] = [];\n  if (md.revenue_growth != null) fParts.push({ pts: clamp((md.revenue_growth + 20) / 40, 0, 1) * 4, max: 4 });\n  if (md.gross_margin != null) fParts.push({ pts: clamp(md.gross_margin / 60, 0, 1) * 3, max: 3 });\n  if (md.roe != null) fParts.push({ pts: clamp(md.roe / 30, 0, 1) * 2, max: 2 });\n  if (md.debt_to_equity != null) fParts.push({ pts: md.debt_to_equity <= 1 ? 1 : md.debt_to_equity <= 2 ? 0.5 : 0, max: 1 });\n  s.fundamentalQuality = normScore(fParts, 10);\n\n  // Valuation (weight 5): lower P/E and P/B => more attractive. Absence => NaN.\n  const vParts: { pts: number; max: number }[] = [];\n  if (md.pe_ratio != null && md.pe_ratio > 0) vParts.push({ pts: clamp((40 - md.pe_ratio) / 25, 0, 1), max: 3 });\n  if (md.pb_ratio != null && md.pb_ratio > 0) vParts.push({ pts: clamp((10 - md.pb_ratio) / 8, 0, 1), max: 2 });\n  s.valuation = normScore(vParts, 5);\n\n  // Price Momentum (weight 10): 1w and 1m % moves.\n  const mParts: { pts: number; max: number }[] = [];\n  if (md.price_change_1m != null) mParts.push({ pts: clamp(md.price_change_1m / 20, 0, 1) * 4, max: 4 });\n  if (md.price_change_1w != null) mParts.push({ pts: clamp(md.price_change_1w / 10, 0, 1) * 6, max: 6 });\n  if (md.price_change_1d != null && mParts.length === 0) mParts.push({ pts: clamp(md.price_change_1d / 5, 0, 1) * 10, max: 10 });\n  s.priceMomentum = normScore(mParts, 10);\n\n  // Market / Capital Flow (weight 10): volume proxy (or explicit flow when a\n  // provider supplies it). Absence => NaN (stays insufficient data).\n  if (md.flow != null) {\n    s.capitalFlow = Math.min(10, Math.round(clamp(md.flow, 0, 1) * 10 * 10) / 10);\n  } else if (md.avg_volume != null && md.avg_volume > 0) {\n    // Volume magnitude proxy: scales ~0 at ~1e5, ~5 at ~1e7, ~10 at ~1e9.\n    s.capitalFlow = Math.min(10, Math.round(Math.log10(1 + md.avg_volume / 100000) * 2.5 * 10) / 10);\n  }\n\n  return s;\n}\n","createdAt",["D",1788960923026],"branchVersion","branchId","012dced4-9569-4aa1-b4df-043c162f1aa5","id","5c601043-49d8-4da3-8595-d21725bb7d55","x-trader-consensus",["D",1788705462303],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","X account collector: normalized posts into SQLite","imageUrl","8f36435b-4ee1-42d7-a52e-11508b5c71b0","pinnedFileId","b9b4c5ce-aa00-11f1-b6d1-1607ee4eb77e","pinPreview",true,"pinMigrated","screenshotVersion","likeCount",0,"orgId","01a07719-4bcc-778e-997b-14b67712b6b9","tursoName","5c60104349d84da38595d21725bb7d55","tursoHostname","5c60104349d84da38595d21725bb7d55-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789485645684],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_101":100,"_109":105},"org",{"_101":100,"_102":103,"_104":105,"_18":56,"_106":107,"_108":25},"branch",{"_13":90,"_32":91,"_44":43,"_92":45,"_93":-5,"_94":-5,"_95":-5,"_96":-5,"_97":-5,"_98":46,"_99":100},"isOwned",146,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","efrenlin","handle","displayName","Efren Lin","avatar","https://lh3.googleusercontent.com/a/ACg8ocITAXmIR316-M-ReEEDUIUESLjIDNnYnCPqoEpOrKLJExkkkWU=s96-c","tier","free","canRestrictVals","profileImageUrl","branchName","","adoptOrgId",{"_44":45,"_32":46,"_39":362,"_48":-5,"_49":25,"_50":51,"_52":51,"_53":54,"_55":-5,"_18":56,"_57":58,"_59":60,"_61":-5,"_62":-5,"_63":64,"_65":66,"_67":68,"_69":70,"_71":363,"_73":-5,"_74":75,"_76":75,"_77":78,"_79":78,"_80":78,"_81":75,"_82":75,"_83":364,"_85":365,"_87":366,"_89":25},"tree",[196,212,214,215,216,217],"files",[196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217],"counts",{"_185":64,"_186":170,"_187":64,"_188":189,"_190":191},"contents",[132,133,134,135,136,137],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_44":180,"_32":181,"_30":182,"_140":-5,"_42":43,"_41":154,"_142":60,"_39":183,"_13":64,"_34":178,"_36":-5,"_24":25,"_147":-5,"_27":184},{"_44":173,"_32":174,"_30":175,"_140":-5,"_42":43,"_41":176,"_142":60,"_39":177,"_13":64,"_34":178,"_36":-5,"_24":25,"_147":-5,"_27":179},{"_44":165,"_32":166,"_30":167,"_140":-5,"_42":43,"_41":168,"_142":60,"_39":169,"_13":170,"_34":20,"_36":171,"_24":25,"_147":-5,"_27":172},{"_44":158,"_32":159,"_30":160,"_140":-5,"_42":43,"_41":161,"_142":60,"_39":162,"_13":64,"_34":35,"_36":163,"_24":25,"_147":-5,"_27":164},{"_44":149,"_32":150,"_30":151,"_140":-5,"_42":43,"_41":152,"_142":60,"_39":153,"_13":154,"_34":155,"_36":156,"_24":25,"_147":-5,"_27":157},{"_44":138,"_32":139,"_30":58,"_140":-5,"_42":43,"_41":141,"_142":25,"_39":143,"_13":144,"_34":145,"_36":146,"_24":25,"_147":-5,"_27":148},"abda4067-4dc6-434d-859c-d4e5abf77645","main.ts","parentId",115,"latest",["D",1788958682299],37,"http","import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { TRACKED_ACCOUNTS } from \"./config.ts\";\nimport { runCollector } from \"./lib/collector.ts\";\nimport { ensureSchema } from \"./lib/schema.ts\";\nimport { runConsensusAggregation } from \"./lib/consensus.ts\";\nimport { extractCandidates } from \"./lib/discovery.ts\";\nimport { runCandidateValidation } from \"./lib/candidateValidation.ts\";\nimport { applyFundamentals, runMarketDiscovery } from \"./lib/marketDiscovery.ts\";\nimport { getLatestTickerIdentityReviews, recordTickerIdentityReview } from \"./lib/tickerIdentity.ts\";\nimport { readFile } from \"https://esm.town/v/std/utils/index.ts\";\nimport { buildDashboardApi, buildStockDrilldown, searchMarket } from \"./lib/dashboard.ts\";\nimport { discoveryApi, claudeContext, claudeMarketBrief, watchlistView, buildEmergingAccounts, computeDiscovery, WATCHLIST } from \"./lib/discoveryV2.ts\";\nimport { refreshMarketData, marketDataApi, runSingleTickerMarketDataTest } from \"./lib/marketData.ts\";\n\n// No cron/interval is attached yet (by design — see README \"Phase 2\").\n// Every route here is manually triggered.\nexport default async function (req: Request): Promise {\n  const url = new URL(req.url);\n\n  if (url.pathname === \"/\") {\n    try {\n      const html = await readFile(\"ui/index.html\");\n      return new Response(html, { headers: { \"content-type\": \"text/html; charset=utf-8\" } });\n    } catch (err) {\n      return Response.json({ ok: false, error: \"Dashboard asset missing: \" + (err as Error).message }, { status: 500 });\n    }\n  }\n\n  // Dashboard JSON APIs — read-only, assembled from existing SQLite data.\n  if (url.pathname === \"/api/dashboard\") {\n    const period = url.searchParams.get(\"period\") ?? \"daily\";\n    const p = [\"daily\", \"weekly\", \"monthly\"].includes(period) ? period : \"daily\";\n    try {\n      const data = await buildDashboardApi(p as any);\n      return Response.json({ ok: true, ...data });\n    } catch (err) {\n      return Response.json({ ok: false, error: (err as Error).message }, { status: 500 });\n    }\n  }\n  if (url.pathname === \"/api/stock\") {\n    const ticker = (url.searchParams.get(\"ticker\") ?? \"\").trim();\n    if (!ticker) return Response.json({ ok: false, error: \"ticker query param required.\" }, { status: 400 });\n    try {\n      const d = await buildStockDrilldown(ticker);\n      return Response.json({ ok: true, ...d });\n    } catch (err) {\n      return Response.json({ ok: false, error: (err as Error).message }, { status: 500 });\n    }\n  }\n  // ---- V2: Market Discovery, Themes, Emerging Accounts, Watchlist, Claude ----\n  if (url.pathname === \"/api/discovery\") {\n    try {\n      const tier = url.searchParams.get(\"tier\") ?? undefined;\n      const ticker = url.searchParams.get(\"ticker\") ?? undefined;\n      const period = url.searchParams.get(\"period\") ?? \"daily\";\n      const d = await discoveryApi({ tier, ticker, period });\n      return Response.json({ ok: true, ...d });\n    } catch (err) {\n      return Response.json({ ok: false, error: (err as Error).message }, { status: 500 });\n    }\n  }\n  if (url.pathname === \"/api/themes\") {\n    try {\n      const d = await discoveryApi({ period: url.searchParams.get(\"period\") ?? \"daily\" });\n      return Response.json({ ok: true, themes: d.themes, watchlistKeys: WATCHLIST });\n    } catch (err) {\n      return Response.json({ ok: false, error: (err as Error).message }, { status: 500 });\n    }\n  }\n  if (url.pathname === \"/api/accounts\") {\n    try {\n      const emerging = await buildEmergingAccounts();\n      const core = TRACKED_ACCOUNTS.map((h) => ({ handle: h, category: \"Core Trader\", promotion_status: \"Tracked\" }));\n      return Response.json({ ok: true, core, emerging });\n    } catch (err) {\n      return Response.json({ ok: false, error: (err as Error).message }, { status: 500 });\n    }\n  }\n  if (url.pathname === \"/api/watchlist\") {\n    try {\n      const wl = await watchlistView();\n      return Response.json({ ok: true, watchlist: wl, keys: WATCHLIST });\n    } catch (err) {\n      return Response.json({ ok: false, error: (err as Error).message }, { status: 500 });\n    }\n  }\n  if (url.pathname === \"/api/claude/context\") {\n    try {\n      const ctx = await claudeContext();\n      return Response.json({ ok: true, ...ctx });\n    } catch (err) {\n      return Response.json({ ok: false, error: (err as Error).message }, { status: 500 });\n    }\n  }\n  if (url.pathname === \"/api/claude/market-brief\") {\n    try {\n      const brief = await claudeMarketBrief();\n      return Response.json({ ok: true, ...brief });\n    } catch (err) {\n      return Response.json({ ok: false, error: (err as Error).message }, { status: 500 });\n    }\n  }\n\n\n  // Market / Fundamentals layer — read persisted rows (read-only) or trigger\n  // a MANUAL bounded refresh (only runs if MARKETDATA_API_KEY is set; no key\n  // -> graceful note, never a network call).\n  if (url.pathname === \"/api/market\") {\n    try {\n      const m = await marketDataApi({ ticker: url.searchParams.get(\"ticker\") ?? undefined, limit: url.searchParams.get(\"limit\") ? Number(url.searchParams.get(\"limit\")) : undefined });\n      return Response.json({ ok: true, ...m });\n    } catch (err) {\n      return Response.json({ ok: false, error: (err as Error).message }, { status: 500 });\n    }\n  }\n  if (url.pathname === \"/api/market/refresh\") {\n    try {\n      const symbols = (url.searchParams.get(\"symbols\") ?? \"\").split(\",\").map((s) => s.trim().toUpperCase()).filter(Boolean).slice(0, 30);\n      if (symbols.length === 0) return Response.json({ ok: false, error: \"?symbols=A,B,C required (max 30).\" }, { status: 400 });\n      const r = await refreshMarketData(symbols);\n      return Response.json({ ok: true, ...r });\n    } catch (err) {\n      return Response.json({ ok: false, error: (err as Error).message }, { status: 500 });\n    }\n  }\n\n  // Controlled single-ticker Market Data / Fundamentals test (manual only,\n  // never cron, never the collector). ?ticker= defaults to NVDA and is the\n  // ONLY symbol this route will ever touch — no batching, no expansion.\n  // Validates the 4 FMP-backed dimensions (Fundamental Quality, Valuation,\n  // Price Momentum, Market/Capital Flow): makes exactly 4 provider calls,\n  // stores only the fields FMP actually returned (unavailable fields stay\n  // NULL, never zeroed — see lib/marketData.ts), then runs the enriched\n  // Discovery V2 calculation SCOPED to this one ticker via\n  // computeDiscovery({ onlyTickers }) — every other ticker's\n  // discovery_candidates/discovery_signals row, the shared themes table,\n  // and discovery_runs history are left completely untouched. Never\n  // re-analyzes posts, never calls Anthropic, never modifies cron.ts,\n  // the collector, or any other scheduled task.\n  if (url.pathname === \"/market-data/test-ticker\" && (req.method === \"GET\" || req.method === \"POST\")) {\n    await ensureSchema();\n    const ticker = (url.searchParams.get(\"ticker\") ?? \"NVDA\").trim().toUpperCase();\n    try {\n      const marketReport = await runSingleTickerMarketDataTest(ticker);\n      const discoveryResult = await computeDiscovery({ onlyTickers: [ticker] });\n      const enriched = await sqlite.execute({\n        sql: `SELECT ticker, score_normalized, score_available, available_max, coverage, tier, direction,\n                     independent_consensus, bullish, bearish, unclear, last_updated\n              FROM discovery_candidates WHERE ticker = ?`,\n        args: [ticker],\n      });\n      return Response.json({\n        ok: true,\n        ticker,\n        market_data_test: marketReport,\n        enriched_discovery: {\n          computed_at: discoveryResult.generatedAt,\n          scoped_to: [ticker],\n          row: enriched.rows[0] ?? null,\n        },\n        approximate_api_calls_used: marketReport.api_calls.length,\n      });\n    } catch (err) {\n      return Response.json({ ok: false, error: (err as Error).message }, { status: 500 });\n    }\n  }\n\n  if (url.pathname === \"/api/search\") {\n    try {\n      const s = await searchMarket(url.searchParams.get(\"q\") ?? \"\");\n      return Response.json({ ok: true, ...s });\n    } catch (err) {\n      return Response.json({ ok: false, error: (err as Error).message }, { status: 500 });\n    }\n  }\n\n  // Health/status — the former root JSON now lives at /api/status; /health is retained.\n  if (url.pathname === \"/api/status\" || url.pathname === \"/health\") {\n    await ensureSchema();\n    const hasToken = Boolean(Deno.env.get(\"X_BEARER_TOKEN\"));\n    const accounts = await sqlite.execute(\n      \"SELECT username, account_id, last_post_id, last_collected_at FROM accounts\",\n    );\n    const postCount = await sqlite.execute(\"SELECT COUNT(*) as n FROM posts\");\n    const unanalyzed = await sqlite.execute(\n      \"SELECT COUNT(*) as n FROM posts WHERE analyzed_at IS NULL\",\n    );\n\n    return Response.json({\n      ok: true,\n      x_bearer_token_configured: hasToken,\n      tracked_accounts: TRACKED_ACCOUNTS,\n      resolved_accounts: accounts.rows,\n      total_posts_stored: (postCount.rows[0] as any).n,\n      posts_awaiting_claude_analysis: (unanalyzed.rows[0] as any).n,\n      note: hasToken\n        ? \"Ready. GET /run-collector to ingest recent posts. GET /posts to inspect stored data.\"\n        : \"X_BEARER_TOKEN is not set yet. See README.md for exactly where to add it.\",\n    });\n  }\n\n  if (url.pathname === \"/run-collector\") {\n    if (!Deno.env.get(\"X_BEARER_TOKEN\")) {\n      return Response.json(\n        {\n          ok: false,\n          error: \"X_BEARER_TOKEN is not set. Add it as a Val Town environment variable — see README.md.\",\n        },\n        { status: 400 },\n      );\n    }\n    try {\n      // Phase 3.1 validation only: ?reset_since_id=1 clears last_post_id for\n      // the 10 Core Trader accounts before fetching, so the same recent\n      // window (bounded by MAX_RESULTS_PER_ACCOUNT — 25/account, 10 calls\n      // total) is re-fetched and already-ingested rows can be backfilled\n      // with referenced_author_* provenance the collector previously\n      // discarded. Never touches discovered-candidate accounts.\n      const resetSinceId = url.searchParams.get(\"reset_since_id\") === \"1\";\n      const summary = await runCollector({ resetSinceId });\n      return Response.json({ ok: true, summary });\n    } catch (err) {\n      return Response.json({ ok: false, error: (err as Error).message }, { status: 500 });\n    }\n  }\n\n  if (url.pathname === \"/posts\") {\n    await ensureSchema();\n    const account = url.searchParams.get(\"account\");\n    const limit = Math.min(Number(url.searchParams.get(\"limit\") ?? \"50\") || 50, 200);\n\n    const rows = account\n      ? await sqlite.execute({\n          sql: \"SELECT * FROM posts WHERE username = ? ORDER BY posted_at DESC LIMIT ?\",\n          args: [account, limit],\n        })\n      : await sqlite.execute({\n          sql: \"SELECT * FROM posts ORDER BY posted_at DESC LIMIT ?\",\n          args: [limit],\n        });\n\n    return Response.json({ ok: true, count: rows.rows.length, posts: rows.rows });\n  }\n\n  if (url.pathname === \"/runs\") {\n    await ensureSchema();\n    const rows = await sqlite.execute(\n      \"SELECT * FROM collector_runs ORDER BY id DESC LIMIT 20\",\n    );\n    return Response.json({ ok: true, runs: rows.rows });\n  }\n\n  // Phase 2: Trader Consensus Intelligence Layer aggregation.\n  // Recomputes per-ticker consensus from currently-analyzed posts and\n  // appends a new snapshot row per ticker into ticker_consensus.\n  if (url.pathname === \"/consensus\" && (req.method === \"GET\" || req.method === \"POST\")) {\n    await ensureSchema();\n    try {\n      const summary = await runConsensusAggregation();\n      return Response.json({ ok: true, ...summary });\n    } catch (err) {\n      return Response.json({ ok: false, error: (err as Error).message }, { status: 500 });\n    }\n  }\n\n  // Read-only: the most recent snapshot per ticker, without recomputing.\n  if (url.pathname === \"/consensus/latest\") {\n    await ensureSchema();\n    const rows = await sqlite.execute(`\n      SELECT tc.* FROM ticker_consensus tc\n      INNER JOIN (\n        SELECT ticker, MAX(computed_at) AS max_computed_at\n        FROM ticker_consensus\n        GROUP BY ticker\n      ) latest ON tc.ticker = latest.ticker AND tc.computed_at = latest.max_computed_at\n      ORDER BY tc.unique_accounts_mentioning DESC, tc.mention_count DESC\n    `);\n    return Response.json({ ok: true, count: rows.rows.length, consensus: rows.rows });\n  }\n\n  // Phase 3: Trader Discovery Layer — mechanical candidate extraction only.\n  // Read-only, no writes to discovered_accounts here (that happens after a\n  // separate semantic review step, per the mechanical/semantic split used\n  // throughout this project).\n  if (url.pathname === \"/discovery/candidates\") {\n    await ensureSchema();\n    const result = await extractCandidates();\n    if (url.searchParams.get(\"summary\") === \"1\") {\n      const reasonCounts: Record = {};\n      for (const r of result.rejected) {\n        const key = r.reason.split(\":\")[0];\n        reasonCounts[key] = (reasonCounts[key] ?? 0) + 1;\n      }\n      return Response.json({\n        ok: true,\n        candidate_count: result.candidates.length,\n        rejected_count: result.rejected.length,\n        rejected_reason_breakdown: reasonCounts,\n        rejected_handles: result.rejected.map((r) => ({ handle: r.handle, reason: r.reason })),\n        notes: result.notes,\n      });\n    }\n    if (url.searchParams.get(\"compact\") === \"1\") {\n      const offset = Number(url.searchParams.get(\"offset\") ?? \"0\") || 0;\n      const limit = Number(url.searchParams.get(\"limit\") ?? \"1000\") || 1000;\n      const slice = result.candidates.slice(offset, offset + limit);\n      return Response.json({\n        ok: true,\n        candidate_count: result.candidates.length,\n        offset,\n        returned: slice.length,\n        candidates: slice.map((c) => ({\n          handle: c.handle,\n          times_referenced: c.times_referenced,\n          referencing_core_accounts: c.referencing_core_accounts,\n          tickers_discussed: c.tickers_discussed,\n          has_ticker_evidence: c.has_ticker_evidence,\n          promotional_flag: c.promotional_flag,\n          evidence: c.evidence.map((e) => ({\n            citing_account: e.citing_account,\n            post_type: e.post_type,\n            text: e.text.slice(0, 220),\n            posted_at: e.posted_at,\n          })),\n        })),\n      });\n    }\n    const handlesFilter = url.searchParams.get(\"handles\");\n    if (handlesFilter) {\n      const wanted = new Set(handlesFilter.split(\",\").map((h) => h.trim().toLowerCase()));\n      return Response.json({\n        ok: true,\n        candidates: result.candidates.filter((c) => wanted.has(c.handle.toLowerCase())),\n        rejected: result.rejected.filter((r) => wanted.has(r.handle.toLowerCase())),\n      });\n    }\n    return Response.json({ ok: true, ...result });\n  }\n\n  // Phase 3.2: Discovered Trader Candidate Validation — controlled,\n  // explicit-handles-only timeline collection for candidates already in\n  // discovered_accounts. POST only (this makes X API calls and writes\n  // data). ?handles=a,b,c (required, no default/\"all\" mode) and optional\n  // ?max_results= (default 30, capped 5-100). Never touches `posts`\n  // (Core Trader dataset), never touches discovered_accounts.\n  if (url.pathname === \"/candidate-validation/run\" && req.method === \"POST\") {\n    await ensureSchema();\n    if (!Deno.env.get(\"X_BEARER_TOKEN\")) {\n      return Response.json(\n        { ok: false, error: \"X_BEARER_TOKEN is not set. Add it as a Val Town environment variable — see README.md.\" },\n        { status: 400 },\n      );\n    }\n    const handlesParam = url.searchParams.get(\"handles\");\n    if (!handlesParam) {\n      return Response.json(\n        { ok: false, error: \"?handles=comma,separated,list is required — this route never runs for 'all candidates'.\" },\n        { status: 400 },\n      );\n    }\n    const wanted = handlesParam.split(\",\").map((h) => h.trim()).filter(Boolean);\n    const maxResults = Number(url.searchParams.get(\"max_results\") ?? \"30\") || 30;\n\n    const placeholders = wanted.map(() => \"?\").join(\",\");\n    const rows = await sqlite.execute({\n      sql: `SELECT handle, x_account_id, display_name FROM discovered_accounts WHERE handle IN (${placeholders})`,\n      args: wanted,\n    });\n    const found = new Map((rows.rows as any[]).map((r) => [r.handle.toLowerCase(), r]));\n    const missing = wanted.filter((h) => !found.has(h.toLowerCase()));\n    const targets = wanted\n      .filter((h) => found.has(h.toLowerCase()))\n      .map((h) => {\n        const r = found.get(h.toLowerCase())!;\n        return { handle: r.handle, x_account_id: r.x_account_id, display_name: r.display_name };\n      });\n\n    try {\n      const summary = await runCandidateValidation(targets, { maxResults });\n      return Response.json({ ok: true, missing_from_discovered_accounts: missing, summary });\n    } catch (err) {\n      return Response.json({ ok: false, error: (err as Error).message }, { status: 500 });\n    }\n  }\n\n  // Read-only inspection of a candidate validation sample.\n  if (url.pathname === \"/candidate-validation/posts\") {\n    await ensureSchema();\n    const handle = url.searchParams.get(\"handle\");\n    const limit = Math.min(Number(url.searchParams.get(\"limit\") ?? \"100\") || 100, 200);\n    const rows = handle\n      ? await sqlite.execute({\n          sql: \"SELECT * FROM candidate_validation_posts WHERE candidate_handle = ? ORDER BY posted_at DESC LIMIT ?\",\n          args: [handle, limit],\n        })\n      : await sqlite.execute({\n          sql: \"SELECT * FROM candidate_validation_posts ORDER BY posted_at DESC LIMIT ?\",\n          args: [limit],\n        });\n    return Response.json({ ok: true, count: rows.rows.length, posts: rows.rows });\n  }\n\n  if (url.pathname === \"/candidate-validation/runs\") {\n    await ensureSchema();\n    const rows = await sqlite.execute(\"SELECT * FROM candidate_validation_runs ORDER BY id DESC LIMIT 20\");\n    return Response.json({ ok: true, runs: rows.rows });\n  }\n\n  if (url.pathname === \"/discovery/accounts\") {\n    await ensureSchema();\n    const group = url.searchParams.get(\"group\");\n    const rows = group\n      ? await sqlite.execute({\n          sql: \"SELECT * FROM discovered_accounts WHERE account_group = ? ORDER BY relevance_score DESC\",\n          args: [group],\n        })\n      : await sqlite.execute(\"SELECT * FROM discovered_accounts ORDER BY relevance_score DESC\");\n    return Response.json({ ok: true, count: rows.rows.length, accounts: rows.rows });\n  }\n\n  // Phase 4: Market Discovery Score V1 — RESEARCH PRIORITIZATION only, never\n  // a buy/sell recommendation. Step A: mechanical + semantic-lookup pass\n  // over the full ticker universe (ticker_consensus UNION Validated\n  // Discovery Signal tickers), persisted as a new append-only run. Never\n  // writes to accounts / discovered_accounts / posts / ticker_consensus.\n  if (url.pathname === \"/market-discovery/run\" && (req.method === \"GET\" || req.method === \"POST\")) {\n    await ensureSchema();\n    try {\n      const finalistCount = Number(url.searchParams.get(\"finalists\") ?? \"20\") || 20;\n      const summary = await runMarketDiscovery({ finalistCount });\n      return Response.json({ ok: true, summary });\n    } catch (err) {\n      return Response.json({ ok: false, error: (err as Error).message }, { status: 500 });\n    }\n  }\n\n  // Step B: apply the bounded Component E (Fundamental/Market Quality Gate)\n  // lookup for a run's finalists. Body: { run_id, updates: [{ ticker,\n  // fundamental_market_score (0-10 or null), fundamental_note }] }.\n  if (url.pathname === \"/market-discovery/apply-fundamentals\" && req.method === \"POST\") {\n    await ensureSchema();\n    try {\n      const body = await req.json();\n      const runId = Number(body.run_id);\n      const updates = Array.isArray(body.updates) ? body.updates : [];\n      if (!runId || updates.length === 0) {\n        return Response.json({ ok: false, error: \"Body must include run_id and a non-empty updates array.\" }, { status: 400 });\n      }\n      const result = await applyFundamentals(runId, updates);\n      return Response.json({ ok: true, ...result });\n    } catch (err) {\n      return Response.json({ ok: false, error: (err as Error).message }, { status: 500 });\n    }\n  }\n\n  // Read-only: latest run's full ranked snapshot.\n  if (url.pathname === \"/market-discovery/latest\") {\n    await ensureSchema();\n    const runRow = await sqlite.execute(`SELECT * FROM market_discovery_runs ORDER BY id DESC LIMIT 1`);\n    if (runRow.rows.length === 0) {\n      return Response.json({ ok: true, run: null, tickers: [] });\n    }\n    const run = runRow.rows[0] as any;\n    const rows = await sqlite.execute({\n      sql: `SELECT * FROM market_discovery_snapshots WHERE run_id = ? ORDER BY discovery_score DESC`,\n      args: [run.id],\n    });\n    return Response.json({ ok: true, run, count: rows.rows.length, tickers: rows.rows });\n  }\n\n  if (url.pathname === \"/market-discovery/runs\") {\n    await ensureSchema();\n    const rows = await sqlite.execute(`SELECT * FROM market_discovery_runs ORDER BY id DESC LIMIT 20`);\n    return Response.json({ ok: true, runs: rows.rows });\n  }\n\n  // Ticker/Entity Identity Review layer (production-data cleanup +\n  // reusable safeguard). POST records a new review (append-only — see\n  // lib/tickerIdentity.ts for the exact fields and how exclusion works).\n  // Body: { ticker, classification, resolved_entity?, intended_entity?,\n  // exclude_from_consensus, evidence?, flag_reasons?, notes? }.\n  if (url.pathname === \"/ticker-identity/review\" && req.method === \"POST\") {\n    await ensureSchema();\n    try {\n      const body = await req.json();\n      if (!body.ticker || !body.classification || typeof body.exclude_from_consensus !== \"boolean\") {\n        return Response.json(\n          { ok: false, error: \"Body must include ticker, classification, and boolean exclude_from_consensus.\" },\n          { status: 400 },\n        );\n      }\n      await recordTickerIdentityReview(body);\n      return Response.json({ ok: true });\n    } catch (err) {\n      return Response.json({ ok: false, error: (err as Error).message }, { status: 500 });\n    }\n  }\n\n  // Read-only: the latest review per ticker (append-only table — latest\n  // reviewed_at wins). Shows every classification + whether it's currently\n  // excluding that ticker from consensus/Discovery Score calculations.\n  if (url.pathname === \"/ticker-identity/reviews\") {\n    await ensureSchema();\n    const reviews = await getLatestTickerIdentityReviews();\n    return Response.json({ ok: true, count: reviews.size, reviews: [...reviews.values()] });\n  }\n\n  // Secure Analysis Write API — POST /analysis\n  // Accepts batch analysis results for EXISTING posts only. Updates ONLY the\n  // analytical columns consumed by lib/consensus.ts; never modifies raw X\n  // data (text, account/username, post_id, posted_at, engagement metrics,\n  // mentioned_tickers) and never inserts a new post row.\n  if (url.pathname === \"/analysis\" && req.method === \"POST\") {\n    const expected = Deno.env.get(\"ANALYSIS_WRITE_KEY\");\n    if (!expected) {\n      return Response.json(\n        { ok: false, error: \"ANALYSIS_WRITE_KEY is not configured on this val.\" },\n        { status: 500 },\n      );\n    }\n    const provided = req.headers.get(\"x-analysis-write-key\") ?? \"\";\n    const timingSafeEqual = (a: string, b: string): boolean => {\n      if (a.length !== b.length) return false;\n      let diff = 0;\n      for (let i = 0; i < a.length; i++) diff |= a.charCodeAt(i) ^ b.charCodeAt(i);\n      return diff === 0;\n    };\n    if (!timingSafeEqual(provided, expected)) {\n      return Response.json({ ok: false, error: \"Unauthorized: invalid or missing ANALYSIS_WRITE_KEY.\" }, { status: 401 });\n    }\n\n    await ensureSchema();\n\n    // Protected columns that this endpoint must never write, even if a\n    // caller tries to smuggle them in the payload.\n    const PROTECTED_KEYS = new Set([\n      \"post_id\", \"account\", \"username\", \"posted_at\", \"post_type\",\n      \"referenced_post_id\", \"referenced_author_id\", \"referenced_author_handle\",\n      \"referenced_author_name\", \"referenced_author_source\", \"text\",\n      \"mentioned_tickers\", \"engagement_metrics\", \"original_url\", \"ingested_at\",\n    ]);\n\n    let body: any;\n    try {\n      body = await req.json();\n    } catch {\n      return Response.json({ ok: false, error: \"Malformed JSON body.\" }, { status: 400 });\n    }\n    if (!body || !Array.isArray(body.posts) || body.posts.length === 0) {\n      return Response.json({ ok: false, error: \"Body must be { posts: [...] } with at least one post.\" }, { status: 400 });\n    }\n    if (body.posts.length > 500) {\n      return Response.json({ ok: false, error: \"Batch limited to 500 posts per request.\" }, { status: 400 });\n    }\n\n    const ALLOWED_STANCES = new Set([\"Bullish\", \"Bearish\", \"Unclear\"]);\n    const ALLOWED_CONFIDENCE = new Set([\"High\", \"Medium\", \"Low\"]);\n    const cleanText = (v: unknown, field: string, maxLen = 2000): string | null => {\n      if (v === null || v === undefined) return null;\n      if (typeof v !== \"string\") throw new Error(`${field} must be a string or null.`);\n      if (v.length > maxLen) throw new Error(`${field} exceeds max length ${maxLen}.`);\n      return v;\n    };\n\n    // Validate every item first; if any is invalid, reject the whole batch\n    // atomically (no partial writes).\n    const validated: Array> = [];\n    const errors: string[] = [];\n    for (let i = 0; i < body.posts.length; i++) {\n      const p = body.posts[i];\n      if (!p || typeof p !== \"object\" || Array.isArray(p)) {\n        errors.push(`posts[${i}]: must be an object.`);\n        continue;\n      }\n      for (const key of Object.keys(p)) {\n        if (PROTECTED_KEYS.has(key)) {\n          errors.push(`posts[${i}]: field \"${key}\" is read-only and may not be set through this endpoint.`);\n        }\n      }\n      if (typeof p.post_id !== \"string\" || p.post_id.length === 0) {\n        errors.push(`posts[${i}]: post_id is required and must be a non-empty string.`);\n      }\n      try {\n        const stance = p.stance ?? null;\n        if (stance !== null && !ALLOWED_STANCES.has(stance)) {\n          errors.push(`posts[${i}]: stance must be one of Bullish|Bearish|Unclear or null.`);\n        }\n\n        let stanceTickers: string | null = \"[]\";\n        if (p.stance_tickers !== undefined) {\n          if (p.stance_tickers === null) stanceTickers = \"[]\";\n          else if (Array.isArray(p.stance_tickers) &&\n                   p.stance_tickers.every((t: unknown) => typeof t === \"string\" && t.length > 0)) {\n            stanceTickers = JSON.stringify(p.stance_tickers.map((t: string) => t.toUpperCase()));\n          } else {\n            errors.push(`posts[${i}]: stance_tickers must be an array of non-empty ticker strings or null.`);\n          }\n        }\n\n        let semanticTickers: string | null = \"[]\";\n        if (p.semantic_tickers !== undefined) {\n          if (p.semantic_tickers === null) semanticTickers = \"[]\";\n          else if (Array.isArray(p.semantic_tickers)) {\n            const cleaned: unknown[] = [];\n            for (const s of p.semantic_tickers) {\n              if (!s || typeof s !== \"object\" || typeof s.ticker !== \"string\" ||\n                  typeof s.company_name !== \"string\" || typeof s.evidence !== \"string\" ||\n                  !ALLOWED_CONFIDENCE.has(s.confidence) || typeof s.recognition_reason !== \"string\") {\n                errors.push(`posts[${i}]: each semantic_tickers entry must have ticker, company_name, evidence, confidence (High|Medium|Low), recognition_reason.`);\n                break;\n              }\n              cleaned.push({\n                ticker: s.ticker.toUpperCase(),\n                company_name: s.company_name,\n                evidence: s.evidence,\n                confidence: s.confidence,\n                recognition_reason: s.recognition_reason,\n              });\n            }\n            if (errors.length === 0 || !errors.some((e) => e.includes(`posts[${i}]: each semantic_tickers`))) {\n              semanticTickers = JSON.stringify(cleaned);\n            } else {\n              semanticTickers = null;\n            }\n          } else {\n            errors.push(`posts[${i}]: semantic_tickers must be an array or null.`);\n          }\n        }\n\n        validated.push({\n          post_id: p.post_id,\n          explicit_stance: stance,\n          evidence: cleanText(p.evidence, `posts[${i}].evidence`),\n          thesis: cleanText(p.thesis, `posts[${i}].thesis`),\n          catalyst: cleanText(p.catalyst, `posts[${i}].catalyst`),\n          time_horizon: cleanText(p.time_horizon, `posts[${i}].time_horizon`),\n          position_disclosure: cleanText(p.position_disclosure, `posts[${i}].position_disclosure`),\n          stance_tickers: stanceTickers,\n          semantic_tickers: semanticTickers,\n          analyzed_at: p.analyzed_at === null || p.analyzed_at === undefined ? null : (() => {\n            if (typeof p.analyzed_at !== \"string\" || Number.isNaN(Date.parse(p.analyzed_at))) {\n              errors.push(`posts[${i}]: analyzed_at must be a valid ISO-8601 datetime string or null.`);\n              return null;\n            }\n            return new Date(p.analyzed_at).toISOString();\n          })(),\n        });\n      } catch (err) {\n        errors.push(`posts[${i}]: ${(err as Error).message}`);\n      }\n    }\n\n    if (errors.length > 0) {\n      return Response.json({ ok: false, error: \"Validation failed; no posts were updated.\", details: errors }, { status: 400 });\n    }\n\n    let updated = 0;\n    let missing: string[] = [];\n    try {\n      for (const v of validated) {\n        const res = await sqlite.execute({\n          sql: `UPDATE posts SET\n                  explicit_stance = ?, evidence = ?, thesis = ?, catalyst = ?,\n                  time_horizon = ?, position_disclosure = ?, stance_tickers = ?,\n                  semantic_tickers = ?, analyzed_at = ?\n                WHERE post_id = ?`,\n          args: [\n            v.explicit_stance, v.evidence, v.thesis, v.catalyst,\n            v.time_horizon, v.position_disclosure, v.stance_tickers,\n            v.semantic_tickers, v.analyzed_at, v.post_id,\n          ],\n        });\n        if (res.rowsAffected > 0) updated++;\n        else missing.push(v.post_id as string);\n      }\n    } catch (err) {\n      return Response.json({ ok: false, error: \"Write failed.\", detail: (err as Error).message }, { status: 500 });\n    }\n\n    return Response.json({\n      ok: true,\n      submitted: validated.length,\n      updated,\n      not_found_skipped: missing, // never created — only existing rows are updated\n    });\n  }\n\n\n  return Response.json({\n    ok: false,\n    error: \"Not found\",\n    routes: [\n      \"/health\",\n      \"/run-collector\",\n      \"/posts\",\n      \"/runs\",\n      \"/consensus\",\n      \"/consensus/latest\",\n      \"/discovery/candidates\",\n      \"/discovery/accounts\",\n      \"/candidate-validation/run\",\n      \"/candidate-validation/posts\",\n      \"/candidate-validation/runs\",\n      \"/market-discovery/run\",\n      \"/market-discovery/apply-fundamentals\",\n      \"/market-discovery/latest\",\n      \"/market-discovery/runs\",\n      \"/ticker-identity/review\",\n      \"/ticker-identity/reviews\",\n      \"/analysis\",\n      \"/api/status\",\n      \"/api/dashboard\",\n      \"/api/stock\",\n      \"/api/search\",\n      \"/api/discovery\",\n      \"/api/themes\",\n      \"/api/accounts\",\n      \"/api/watchlist\",\n      \"/api/claude/context\",\n      \"/api/claude/market-brief\",\n      \"/api/market\",\n      \"/api/market/refresh\",\n    ],\n  }, {\n    status: 404,\n  });\n}\n","label",[139],"924aeea3-ff38-41cc-adcd-a17563dff252","cron.ts","0dd5e0b4-ab89-11f1-84ff-1607ee4eb77e",93,["D",1788939934307],2,"interval","import { runCollector } from \"./lib/collector.ts\";\nimport { analyzeUnanalyzedPosts } from \"./lib/analyze.ts\";\nimport { runConsensusAggregation } from \"./lib/consensus.ts\";\n\n// The Val Town plan caps a run at ~1 minute. We target a maximum runtime of\n// ~45 seconds so collection + bounded analysis + consensus all finish safely\n// below the hard limit, even with scheduler/network variance. The 3-hour\n// schedule is unchanged; anything left over is deferred to the next run.\nconst TARGET_RUNTIME_MS = 45000;\n// Reserve kept after collection to fund analysis, and after analysis to fund\n// consensus + logging, so the run never drifts toward the hard plan limit.\nconst CONSENSUS_RESERVE_MS = 8000;\n\nexport default async function () {\n  const runStarted = Date.now();\n\n  if (!Deno.env.get(\"X_BEARER_TOKEN\")) {\n    console.log(\n      \"Skipping scheduled collector run: X_BEARER_TOKEN is not set. Add it as a Val Town environment variable — see README.md.\",\n    );\n    return;\n  }\n\n  const report = {\n    accountsProcessed: 0,\n    postsIngested: 0,\n    postsAnalyzed: 0,\n    postsSkipped: 0,\n    analysisErrors: 0,\n    consensusTickers: 0,\n    totalRunTime: 0,\n  };\n\n  try {\n    // 1. Collect (bounded by the collector itself: 10 accounts, spaced calls).\n    const collected = await runCollector();\n    report.accountsProcessed = collected.accountsProcessed;\n    report.postsIngested = collected.postsIngested;\n\n    // 2. Zero new posts: stop cleanly — no Anthropic, no consensus snapshot.\n    if (collected.postsIngested === 0) {\n      report.totalRunTime = Date.now() - runStarted;\n      console.log(\"Cron run: no new posts ingested; analysis skipped.\", JSON.stringify(report));\n      return;\n    }\n\n    // Wall-clock budget left for analysis after collection, minus the reserve\n    // needed to close the run cleanly.\n    const elapsed = Date.now() - runStarted;\n    const analysisBudgetMs = Math.max(0, TARGET_RUNTIME_MS - elapsed - CONSENSUS_RESERVE_MS);\n\n    // If the collector already consumed too much time, defer analysis rather\n    // than risk hitting the hard plan limit mid-batch.\n    if (analysisBudgetMs < 4000) {\n      report.totalRunTime = Date.now() - runStarted;\n      console.log(\"Cron run: collection exceeded analysis budget; analysis deferred to next run.\", JSON.stringify(report));\n      return;\n    }\n\n    // 3. Bounded analysis: small batches, hard-cap of MAX_ANALYZE_PER_RUN\n    //    (20 posts = max 2 Anthropic calls), and a wall-clock stop so any\n    //    remaining analyzed_at IS NULL posts wait for the next 3-hour run.\n    const analysis = await analyzeUnanalyzedPosts({ timeBudgetMs: analysisBudgetMs });\n    report.postsAnalyzed = analysis.postsAnalyzed;\n    report.postsSkipped = analysis.postsSkipped;\n    report.analysisErrors = analysis.analysisErrors;\n\n    // 4. Consensus ONLY if new analyses landed AND enough time remains within\n    //    the target; otherwise defer consensus safely to the next cycle.\n    const elapsedAfterAnalysis = Date.now() - runStarted;\n    const timeForConsensus = TARGET_RUNTIME_MS - elapsedAfterAnalysis;\n    if (analysis.postsAnalyzed > 0 && timeForConsensus >= CONSENSUS_RESERVE_MS) {\n      const consensus = await runConsensusAggregation();\n      report.consensusTickers = consensus.tickers_computed ?? consensus.results?.length ?? 0;\n    } else {\n      console.log(\"Cron run: consensus skipped/deferred (no new analyses landed or insufficient time remains).\");\n    }\n  } catch (err) {\n    report.totalRunTime = Date.now() - runStarted;\n    console.error(\"Cron run failed:\", (err as Error).message, JSON.stringify(report));\n    throw err;\n  }\n\n  report.totalRunTime = Date.now() - runStarted;\n  console.log(\"Cron run complete:\", JSON.stringify(report));\n}\n",[150],"528c691a-d3cc-4071-b88a-d771bd29e8a1","config.ts","937ab4f4-aa00-11f1-a8fe-1607ee4eb77e",3,["D",1788705487606],"// Configuration for the X Trader Consensus collector.\n// Public account handles only — NO credentials live in this file or\n// anywhere else in source. The X API Bearer Token is read from a Val Town\n// environment variable at runtime — see README.md for where to add it.\n\nexport const TRACKED_ACCOUNTS: string[] = [\n  \"aleabitoreddit\",\n  \"jukan05\",\n  \"octopusycc\",\n  \"zephyr_z9\",\n  \"KawzInvests\",\n  \"michaelsikand\",\n  \"ren_stocks\",\n  \"unusual_whales\",\n  \"StockMKTNewz\",\n  \"DJTRadar\",\n];\n\n// Max tweets to pull per account per collector run (X API allows 5-100 for\n// the user tweets timeline endpoint). Kept modest for MVP + API cost control\n// — the X API now bills per resource read (see README \"API cost\" section).\nexport const MAX_RESULTS_PER_ACCOUNT = 25;\n\n// Minimum milliseconds to wait between X API calls, so a single run across\n// 10 accounts stays comfortably under per-endpoint rate limits.\nexport const REQUEST_SPACING_MS = 1200;\n",[159],"8dfc869b-1fc5-4ec2-906c-3fd1c2aca80b","README.md","84701602-aa00-11f1-a970-1607ee4eb77e",9,["D",1788705694497],1,"# X Trader Consensus Collector\n\nPhase 1 backend/data layer for a Market Discovery & Consensus system.\n\n```\nX API  →  Val Town collector  →  normalized storage (SQLite)  →  Claude  →  Market Discovery & Consensus\n```\n\nThis val is the first two boxes in that diagram (collector + storage). It does **not**\nrun a schedule yet, and it does **not** do any sentiment/thesis analysis — both are\nintentionally deferred (see \"Phase 2\" below).\n\n## What this val does right now\n\n- Tracks 10 public X accounts (`config.ts`): `@aleabitoreddit`, `@jukan05`, `@octopusycc`,\n  `@zephyr_z9`, `@KawzInvests`, `@michaelsikand`, `@ren_stocks`, `@unusual_whales`,\n  `@StockMKTNewz`, `@DJTRadar`.\n- Resolves each handle to a numeric X user ID via the official X API v2 (`GET /2/users/by`).\n- Pulls each account's recent posts (`GET /2/users/:id/tweets`), including original posts,\n  replies, quotes, and reposts.\n- Normalizes and stores every post in SQLite (`posts` table), classifying `post_type` and\n  extracting cashtag tickers mechanically.\n- Is fully manual for now — nothing runs on its own. You trigger it by hitting an HTTP route.\n\n## ⚠️ One credential is required — and it is NOT in this codebase\n\nThe X API requires an app-only **Bearer Token** to read tweets. This project reads it from\nan environment variable and never stores or accepts it any other way — it is never\nhard-coded, never logged, and was never pasted into this chat.\n\n👉 Add X_BEARER_TOKEN here: https://www.val.town/x/efrenlin/x-trader-consensus/environment-variables?key=X_BEARER_TOKEN\n\nSteps to get the token:\n\n1. Go to the X Developer Portal (developer.x.com) and open (or create) a Project + App.\n2. Under the app's \"Keys and tokens\" tab, generate/copy the **Bearer Token** (app-only OAuth 2.0).\n3. Paste it as the value for `X_BEARER_TOKEN` at the link above. Nowhere else.\n\n**Cost note:** the X API no longer has a simple free read tier — it now bills per-resource\non a pay-per-use credit basis (roughly $0.01 per user lookup, ~$0.005 per tweet read, at\ncurrent published rates). Reading 10 accounts × ~25 posts every few hours has a small but\nnon-zero ongoing cost, so make sure the developer account backing this token has billing/\ncredits enabled, or requests will fail with an auth/payment error. Confirm current pricing\nin the X Developer Portal before relying on these numbers — they change.\n\n## Project structure\n\n```\nconfig.ts            Tracked account handles + tunables. No credentials.\nlib/schema.ts         SQLite schema (idempotent CREATE TABLE IF NOT EXISTS)\nlib/xapi.ts           X API v2 client (resolveUsernames, fetchUserTweets)\nlib/normalize.ts       Mechanical normalization: post_type, ticker extraction\nlib/collector.ts       Orchestrates one full collection pass across all accounts\nmain.ts (http)         Manual trigger + inspection routes (see below)\n```\n\n## HTTP routes\n\nBase URL: `https://efrenlin--b9b4c5ceaa0011f1b6d11607ee4eb77e.web.val.run`\n\n- `GET /health` — status: is the token configured, which accounts are resolved, how many\n  posts are stored, how many are still awaiting analysis.\n- `GET /run-collector` — runs one collection pass across all 10 accounts. Requires\n  `X_BEARER_TOKEN` to be set; otherwise returns `400` with a clear message.\n- `GET /posts?account=&limit=` — inspect normalized posts (JSON), most recent\n  first. Omit `account` to see all tracked accounts.\n- `GET /runs` — history of the last 20 collector runs (accounts processed, posts ingested,\n  any per-account errors).\n\n## Normalized post schema (`posts` table)\n\nPer-post fields, matching the requested schema:\n\n| Field | Populated by | Notes |\n|---|---|---|\n| `account`, `username` | collector | display name / @handle |\n| `post_id`, `posted_at`, `original_url` | collector | |\n| `post_type` | collector | `original` \\| `reply` \\| `quote` \\| `repost`, from the API's `referenced_tweets` |\n| `referenced_post_id` | collector | underlying tweet id for reply/quote/repost — the join key for dedup |\n| `text` | collector | raw post text |\n| `mentioned_tickers` | collector | mechanical `$CASHTAG` regex + API cashtag entities — JSON array |\n| `engagement_metrics` | collector | retweet/reply/like/quote/impression counts — JSON object |\n| `ingested_at` | collector | when this row was written |\n| `explicit_stance` | **Claude (Phase 2)** | `Bullish` \\| `Bearish` \\| `Unclear` — left `NULL` until analyzed |\n| `evidence` | **Claude (Phase 2)** | supporting excerpt for the stance |\n| `thesis`, `catalyst`, `time_horizon` | **Claude (Phase 2)** | left `NULL` until analyzed |\n| `position_disclosure` | **Claude (Phase 2)** | only ever filled when the post *explicitly* states one |\n| `analyzed_at` | **Claude (Phase 2)** | timestamp once a row has been analyzed |\n\n### Why the analytical fields are NULL right now\n\nThe spec is explicit: \"Do not infer a position unless explicitly supported by the post,\"\nand \"Not Mentioned is NOT Neutral.\" A regex/keyword collector cannot safely make that\njudgment call — it would either under- or over-infer sentiment. So this collector only\ndoes the *mechanical* half of normalization (who/when/what type/which tickers were\nliterally mentioned/how the post is linked to an original). The semantic half\n(`explicit_stance`, `evidence`, `thesis`, `catalyst`, `time_horizon`, `position_disclosure`)\nis left for a dedicated Claude analysis pass that reads each post's `text` and fills those\ncolumns in — auditable, and never guessed by the pipeline itself.\n\n## Deduplication, as built so far\n\n- **Exact duplicates** (the same `post_id` seen twice, e.g. a re-run): prevented at the DB\n  level — `post_id` is the primary key, inserts use `ON CONFLICT DO NOTHING`.\n- **Reposts and quote-tweets of the same original**: every reply/quote/repost stores\n  `referenced_post_id`, the id of the underlying tweet. This is the join key a later\n  consensus step uses to collapse \"5 accounts reposted the same tweet\" into one underlying\n  event rather than 5 independent signals.\n- **Repeated references to the same underlying thesis/event across *different* original\n  posts** (e.g. two different accounts each write their own tweet about the same earnings\n  beat): this requires semantic clustering, not string matching — it's explicitly part of\n  Phase 2 (Claude analysis), not something this collector attempts.\n\n## Consensus counting, as specified\n\n\"Consensus must ultimately count unique independent accounts, not raw post count.\" This\ncollector stores everything needed to compute that correctly later (`username` per post,\n`referenced_post_id` for de-duplicating shared reposts, `explicit_stance` once analyzed),\nbut does not compute a consensus score itself — that belongs to the \"Market Discovery &\nConsensus\" stage downstream, once `explicit_stance` is populated. Roughly:\n\n```sql\n-- Sketch only — meaningful once explicit_stance is populated by Phase 2.\nSELECT mentioned_tickers, explicit_stance, COUNT(DISTINCT username) AS unique_accounts\nFROM posts\nWHERE explicit_stance IS NOT NULL\nGROUP BY mentioned_tickers, explicit_stance;\n```\n\n## Phase 2 (not built yet, on purpose)\n\n1. **Claude analysis pass** — reads posts where `analyzed_at IS NULL`, and for each one\n   (using only that post's own text/evidence) fills `explicit_stance`, `evidence`, `thesis`,\n   `catalyst`, `time_horizon`, and `position_disclosure` (only if explicitly stated), then\n   sets `analyzed_at`.\n2. **Consensus aggregation** — groups analyzed posts by ticker, counts unique independent\n   accounts per stance (collapsing reposts/quotes of the same underlying post via\n   `referenced_post_id`), and surfaces disagreement/consensus per ticker.\n3. **Whole-market discovery** — today `config.ts` hardcodes 10 accounts and ticker\n   extraction is cashtag-only; broader discovery (more accounts, sourcing tickers beyond\n   explicit cashtags, ranking/scoring) is future work once this pipeline is validated.\n4. **The 3-hour schedule** — intentionally not created. Once you've confirmed\n   `/run-collector` works end-to-end with a real token, say the word and an `interval`\n   file can be added on a cron (nothing else about this val needs to change).\n\n## Manual testing\n\n```\nGET /health           → confirms token status + current row counts\nGET /run-collector     → pulls new posts right now (needs the token)\nGET /posts?limit=20    → see what got normalized and stored\nGET /runs              → see run history / per-account errors\n```\n",[166],"3c6b10d0-0bb9-433d-9136-7a7b7431448d","ui","6d7913f1-ce37-4994-987e-75e18a9353c7",86,["D",1788938429870],"directory",[174],"72334838-2080-4896-9e3b-4958397be514","lib","8dccd4ba-aa00-11f1-906c-1607ee4eb77e",["D",1788705478068],[181],"pulls","branches","forks","envVars",{"_192":194,"_195":64},"connections",{"_192":64,"_193":64},"count","unfilledCount",7,"emptyCount",{"_44":180,"_32":181,"_30":182,"_140":-5,"_42":43,"_41":154,"_142":60,"_39":359,"_13":64,"_34":178,"_36":-5,"_24":25,"_147":-5,"_219":360,"_27":361},{"_44":352,"_32":353,"_30":354,"_140":182,"_42":43,"_41":355,"_142":60,"_39":356,"_13":64,"_34":178,"_36":-5,"_24":25,"_147":-5,"_219":357,"_27":358},{"_44":344,"_32":345,"_30":346,"_140":182,"_42":43,"_41":347,"_142":60,"_39":348,"_13":332,"_34":35,"_36":349,"_24":25,"_147":-5,"_219":350,"_27":351},{"_44":336,"_32":337,"_30":338,"_140":182,"_42":43,"_41":339,"_142":60,"_39":340,"_13":64,"_34":35,"_36":341,"_24":25,"_147":-5,"_219":342,"_27":343},{"_44":327,"_32":328,"_30":329,"_140":182,"_42":43,"_41":330,"_142":60,"_39":331,"_13":332,"_34":35,"_36":333,"_24":25,"_147":-5,"_219":334,"_27":335},{"_44":319,"_32":320,"_30":321,"_140":182,"_42":43,"_41":322,"_142":60,"_39":323,"_13":194,"_34":35,"_36":324,"_24":25,"_147":-5,"_219":325,"_27":326},{"_44":311,"_32":312,"_30":313,"_140":182,"_42":43,"_41":314,"_142":60,"_39":315,"_13":64,"_34":35,"_36":316,"_24":25,"_147":-5,"_219":317,"_27":318},{"_44":303,"_32":304,"_30":305,"_140":182,"_42":43,"_41":306,"_142":60,"_39":307,"_13":154,"_34":35,"_36":308,"_24":25,"_147":-5,"_219":309,"_27":310},{"_44":294,"_32":295,"_30":296,"_140":182,"_42":43,"_41":297,"_142":60,"_39":298,"_13":299,"_34":35,"_36":300,"_24":25,"_147":-5,"_219":301,"_27":302},{"_44":289,"_32":33,"_30":31,"_140":182,"_42":43,"_41":14,"_142":25,"_39":290,"_13":291,"_34":35,"_36":38,"_24":25,"_147":-5,"_219":292,"_27":293},{"_44":280,"_32":281,"_30":282,"_140":182,"_42":43,"_41":283,"_142":60,"_39":284,"_13":285,"_34":35,"_36":286,"_24":25,"_147":-5,"_219":287,"_27":288},{"_44":273,"_32":274,"_30":275,"_140":182,"_42":43,"_41":276,"_142":60,"_39":277,"_13":64,"_34":35,"_36":111,"_24":25,"_147":-5,"_219":278,"_27":279},{"_44":265,"_32":266,"_30":267,"_140":182,"_42":43,"_41":268,"_142":60,"_39":269,"_13":154,"_34":35,"_36":270,"_24":25,"_147":-5,"_219":271,"_27":272},{"_44":257,"_32":258,"_30":259,"_140":182,"_42":43,"_41":260,"_142":60,"_39":261,"_13":194,"_34":35,"_36":262,"_24":25,"_147":-5,"_219":263,"_27":264},{"_44":249,"_32":250,"_30":251,"_140":182,"_42":43,"_41":252,"_142":60,"_39":253,"_13":64,"_34":35,"_36":254,"_24":25,"_147":-5,"_219":255,"_27":256},{"_44":241,"_32":242,"_30":243,"_140":182,"_42":43,"_41":244,"_142":60,"_39":245,"_13":154,"_34":35,"_36":246,"_24":25,"_147":-5,"_219":247,"_27":248},{"_44":173,"_32":174,"_30":175,"_140":-5,"_42":43,"_41":176,"_142":60,"_39":238,"_13":64,"_34":178,"_36":-5,"_24":25,"_147":-5,"_219":239,"_27":240},{"_44":231,"_32":232,"_30":233,"_140":175,"_42":43,"_41":176,"_142":60,"_39":234,"_13":64,"_34":20,"_36":235,"_24":25,"_147":-5,"_219":236,"_27":237},{"_44":165,"_32":166,"_30":167,"_140":-5,"_42":43,"_41":168,"_142":60,"_39":228,"_13":170,"_34":20,"_36":171,"_24":25,"_147":-5,"_219":229,"_27":230},{"_44":158,"_32":159,"_30":160,"_140":-5,"_42":43,"_41":161,"_142":60,"_39":225,"_13":64,"_34":35,"_36":163,"_24":25,"_147":-5,"_219":226,"_27":227},{"_44":149,"_32":150,"_30":151,"_140":-5,"_42":43,"_41":152,"_142":60,"_39":222,"_13":154,"_34":155,"_36":156,"_24":25,"_147":-5,"_219":223,"_27":224},{"_44":138,"_32":139,"_30":58,"_140":-5,"_42":43,"_41":141,"_142":25,"_39":218,"_13":144,"_34":145,"_36":146,"_24":25,"_147":-5,"_219":220,"_27":221},["D",1788958682299],"children",[],[139],["D",1788939934307],[],[150],["D",1788705487606],[],[159],["D",1788705694497],[],[166],"d95fde23-ddde-4c5a-bbcb-0ef25087ae2f","index.html","efe35d7a-ac1e-11f1-a79e-1607ee4eb77e",["D",1788938429870],"\n\n\n\n\nMarket Discovery & Consensus\n\n\n\n\n  \n    \n      Market Discovery & Consensus
\n      X Market Intelligence • Updated every 3 hours
\n      \n        Last updated: —
\n        Tracked accounts: —
\n        Total analyzed posts: —
\n        Consensus tickers: —
\n      
\n    
\n    \n      \n        \n        
\n      
\n      \n        \n        \n        \n      
\n    
\n  
\n\n  \n    Market Consensus Overview
\n    
\n  
\n\n  \n    Top Consensus Stocks click a column to sort • unique independent accounts, not raw post count
\n    \n      \n        \n        \n      
\n    
\n  
\n\n  \n    \n      Consensus — Agreement
\n      
\n    
\n    \n      Consensus — Disagreement
\n      
\n    
\n  
\n\n  \n    Attention & Emerging Ideas attention/discovery only, NOT investment recommendations
\n    \n      \n        \n        \n      Ticker Recent Mentions Independent Accounts Total Mentions Change Latest View
\n    
\n  
\n\n  \n    Trader Monitor tracked X accounts
\n    \n      \n        \n        \n      Account Latest Activity Stocks Discussed B Be U Latest Thesis
\n    
\n  
\n\n  \n    Recent Market Views chronological feed
\n    
\n  
\n\n  \n    Not Mentioned is never treated as Neutral. Only High-confidence semantic ticker mappings affect consensus.\n  
\n
\n\n\n  
\n
\n\n\n\n\n",[],[174,232],["D",1788938429870],[213],[174],"7dd6e16c-2985-401a-93b3-e53e435c720e","xapi.ts","a368c0c2-aa00-11f1-96d8-1607ee4eb77e",34,["D",1788785291415],"// Thin client for the official X (Twitter) API v2. Authenticates with an\n// app-only Bearer Token read from an environment variable — never hard-code\n// a credential here or anywhere else in this project.\n\nconst API_BASE = \"https://api.twitter.com/2\";\n\nfunction getBearerToken(): string {\n  let token = Deno.env.get(\"X_BEARER_TOKEN\");\n  if (!token) {\n    throw new Error(\n      \"X_BEARER_TOKEN is not set. Add it as a Val Town environment variable — see README.md.\",\n    );\n  }\n  // Defensively clean up common copy/paste artifacts: surrounding\n  // whitespace/newlines, and accidental wrapping quotes.\n  token = token.trim().replace(/^['\"]|['\"]$/g, \"\");\n\n  // A Bearer Token must be a plain ASCII byte string (it goes straight into\n  // an HTTP header). If it isn't, fail with a clear, specific message\n  // instead of letting `fetch` throw its cryptic \"not a valid ByteString\".\n  for (let i = 0; i < token.length; i++) {\n    if (token.charCodeAt(i) > 255) {\n      throw new Error(\n        `X_BEARER_TOKEN contains a non-ASCII character at position ${i} (likely a smart quote or ` +\n          `invisible character introduced by copy/paste). Re-copy the token directly from the X ` +\n          `Developer Portal's \"Keys and tokens\" page and re-paste it into the Val Town env var.`,\n      );\n    }\n  }\n  console.log(`X_BEARER_TOKEN loaded: length=${token.length}`);\n  return token;\n}\n\nasync function xFetch(path: string, params: Record): Promise {\n  const url = new URL(`${API_BASE}${path}`);\n  for (const [k, v] of Object.entries(params)) url.searchParams.set(k, v);\n\n  const res = await fetch(url.toString(), {\n    headers: { Authorization: `Bearer ${getBearerToken()}` },\n  });\n\n  if (res.status === 429) {\n    const reset = res.headers.get(\"x-rate-limit-reset\");\n    const when = reset ? new Date(Number(reset) * 1000).toISOString() : \"unknown\";\n    throw new Error(`X API rate limited on ${path}. Resets at ${when}.`);\n  }\n  if (!res.ok) {\n    const body = await res.text();\n    throw new Error(`X API error ${res.status} on ${path}: ${body}`);\n  }\n  return res.json();\n}\n\nexport interface XUser {\n  id: string;\n  username: string;\n  name: string;\n}\n\n// Resolve up to 100 usernames to numeric X user IDs in a single call.\nexport async function resolveUsernames(usernames: string[]): Promise {\n  if (usernames.length === 0) return [];\n  const json = await xFetch(\"/users/by\", {\n    usernames: usernames.join(\",\"),\n    \"user.fields\": \"id,username,name\",\n  });\n  return json.data ?? [];\n}\n\nexport interface XTweet {\n  id: string;\n  text: string;\n  created_at: string;\n  author_id: string;\n  referenced_tweets?: { type: \"retweeted\" | \"quoted\" | \"replied_to\"; id: string }[];\n  public_metrics?: Record;\n  entities?: { cashtags?: { tag: string }[] };\n}\n\n// The `referenced_tweets.id` expansion returns the referenced tweet objects\n// themselves (id + author_id, at minimum) in `includes.tweets`. The\n// `referenced_tweets.id.author_id` expansion additionally returns those\n// authors' user objects in `includes.users`. Both are already being\n// requested (see `expansions` below) — Phase 3.1 fixes the collector to\n// actually keep this data instead of discarding it.\nexport interface XIncludeTweet {\n  id: string;\n  author_id: string;\n}\n\nexport interface XIncludeUser {\n  id: string;\n  username: string;\n  name: string;\n}\n\nexport interface XTweetsResult {\n  tweets: XTweet[];\n  includes: {\n    tweets: XIncludeTweet[];\n    users: XIncludeUser[];\n  };\n}\n\n// Fetch recent posts from one account's timeline. Deliberately does NOT\n// exclude replies/retweets/quotes — the whole point is to classify and keep\n// all four post types (original/reply/quote/repost) per the spec.\nexport async function fetchUserTweets(\n  userId: string,\n  opts: { maxResults: number; sinceId?: string | null },\n): Promise {\n  const params: Record = {\n    max_results: String(opts.maxResults),\n    \"tweet.fields\": \"created_at,public_metrics,entities,referenced_tweets,author_id\",\n    expansions: \"referenced_tweets.id,referenced_tweets.id.author_id\",\n  };\n  if (opts.sinceId) params.since_id = opts.sinceId;\n\n  const json = await xFetch(`/users/${userId}/tweets`, params);\n  return {\n    tweets: json.data ?? [],\n    // Previously discarded entirely (`return { tweets: json.data ?? [] }`),\n    // which is why referenced_author_id was always NULL downstream — this\n    // is the same API response we already pay for, just no longer thrown\n    // away. No new request is made to get this.\n    includes: {\n      tweets: json.includes?.tweets ?? [],\n      users: json.includes?.users ?? [],\n    },\n  };\n}\n",[],[181,242],"7f58e66c-654c-4e11-b4dd-a0fabcec5ec4","tickerIdentity.ts","c3d761ac-ab3a-11f1-bcd5-1607ee4eb77e",55,["D",1788840430714],"// Ticker/Entity Identity Review + reusable identity-collision safeguard.\n//\n// Problem this addresses: a mechanically-extracted cashtag (e.g. $SIVE) can\n// be a REAL, listed security that is nonetheless the WRONG company relative\n// to what the trader is actually discussing — a different company entirely\n// uses that symbol (a US OTC shell, a foreign exchange listing, a crypto\n// token, a common-word collision). Component A/D of the Discovery Score and\n// the Trader Consensus layer have no way to know this on their own — they\n// just count mechanical/semantic ticker assignments.\n//\n// This module is a governance layer ON TOP of (never inside) the existing\n// mechanical extraction: it NEVER touches posts.mentioned_tickers,\n// posts.stance_tickers, or posts.semantic_tickers (those stay exactly as\n// mechanically/semantically extracted, for auditability), and it NEVER\n// deletes a post. It only lets a later stage (computeTickerConsensus,\n// runMarketDiscovery) know \"treat this ticker's assignment as excluded from\n// scoring\" once a human/semantic review has explicitly said so.\n//\n// Two halves:\n//  1. A reviewed-record store (ticker_identity_reviews) — append-only,\n//     latest-row-per-ticker wins, exactly like every other snapshot table\n//     in this project.\n//  2. A REUSABLE, text-only heuristic safeguard (detectPotentialIdentityCollision\n//     / runIdentitySafeguardSweep) that FLAGS likely collisions for future,\n//     not-yet-seen tickers — it never guesses a verdict and never sets\n//     exclude_from_consensus itself. It only ever writes classification\n//     'Unreviewed - Flagged', which has zero effect on scoring until a human\n//     (or a subsequent semantic-review pass) turns it into a real\n//     classification.\n\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { ensureSchema } from \"./schema.ts\";\n\nexport type IdentityClassification =\n  | \"Confirmed Correct\"\n  | \"Confirmed False Positive\"\n  | \"Ambiguous/Exclude\"\n  | \"Unreviewed - Flagged\";\n\nexport interface TickerIdentityReviewInput {\n  ticker: string;\n  classification: IdentityClassification;\n  resolved_entity?: string | null;\n  intended_entity?: string | null;\n  exclude_from_consensus: boolean;\n  evidence?: { post_id: string; quote: string }[];\n  flag_reasons?: string[];\n  notes?: string | null;\n}\n\nexport async function recordTickerIdentityReview(r: TickerIdentityReviewInput): Promise {\n  await ensureSchema();\n  await sqlite.execute({\n    sql: `INSERT INTO ticker_identity_reviews (\n      ticker, classification, resolved_entity, intended_entity, exclude_from_consensus,\n      evidence, flag_reasons, reviewed_by, reviewed_at, notes\n    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n    args: [\n      r.ticker.toUpperCase(),\n      r.classification,\n      r.resolved_entity ?? null,\n      r.intended_entity ?? null,\n      r.exclude_from_consensus ? 1 : 0,\n      JSON.stringify(r.evidence ?? []),\n      JSON.stringify(r.flag_reasons ?? []),\n      \"claude_semantic_review\",\n      new Date().toISOString(),\n      r.notes ?? null,\n    ],\n  });\n}\n\n// Latest review row per ticker (append-only table — MAX(reviewed_at) wins,\n// same pattern used for ticker_consensus / market_discovery_snapshots).\nexport async function getLatestTickerIdentityReviews(): Promise> {\n  await ensureSchema();\n  const res = await sqlite.execute(`\n    SELECT t.* FROM ticker_identity_reviews t\n    INNER JOIN (\n      SELECT ticker, MAX(reviewed_at) AS max_reviewed_at\n      FROM ticker_identity_reviews GROUP BY ticker\n    ) latest ON t.ticker = latest.ticker AND t.reviewed_at = latest.max_reviewed_at\n  `);\n  const map = new Map();\n  for (const row of res.rows as any[]) map.set(row.ticker, row);\n  return map;\n}\n\n// The set of tickers whose LATEST review explicitly excludes them from\n// consensus/Discovery Score math. Only 'Confirmed False Positive' and\n// 'Ambiguous/Exclude' reviews ever set exclude_from_consensus=1 (an\n// 'Unreviewed - Flagged' row from the automated safeguard never does).\nexport async function getExcludedTickers(): Promise> {\n  const reviews = await getLatestTickerIdentityReviews();\n  const excluded = new Set();\n  for (const [ticker, row] of reviews.entries()) {\n    if (Number(row.exclude_from_consensus) === 1) excluded.add(ticker);\n  }\n  return excluded;\n}\n\n// ---------------------------------------------------------------------------\n// Reusable ticker-identity safeguard (heuristic, text-only). FLAGS likely\n// collisions for human review — it never resolves or excludes anything by\n// itself. Prefers, per the spec: explicit $TICKER + matching company\n// context; verified company/entity names; surrounding semantic context. It\n// is deliberately conservative (a short, precise set of triggers actually\n// observed to matter in this project's data) rather than an aggressive\n// dictionary scan, because over-flagging is its own failure mode (\"flag\n// rather than guess\" cuts both ways — a safeguard that cries wolf on every\n// short ticker is not a usable safeguard).\n// ---------------------------------------------------------------------------\n\n// Tickers that are ALSO common English words / generic abbreviations —\n// inherently ambiguous as a mechanical cashtag match regardless of context.\nconst COMMON_WORD_OR_GENERIC_ABBREVIATION_TICKERS = new Set([\n  \"CP\", \"IT\", \"SO\", \"ON\", \"GO\", \"ALL\", \"ARE\", \"US\", \"ME\", \"OK\", \"PM\", \"AN\", \"BY\", \"OR\", \"NOW\",\n]);\n\n// Crypto/token-economy vocabulary. When this language co-occurs with a\n// cashtag that also resolves (or could resolve) to an ordinary listed\n// equity, it's a strong signal the cashtag is being used for a crypto\n// project/token rather than the equity security of the same symbol.\nconst CRYPTO_LANGUAGE_MARKERS: RegExp[] = [\n  /\\bTGE\\b/i,\n  /\\btoken generation event\\b/i,\n  /\\btokenomics\\b/i,\n  /\\bairdrop\\b/i,\n  /\\bmainnet\\b/i,\n  /\\btestnet\\b/i,\n  /\\bwhitepaper\\b/i,\n  /\\bstaking\\b/i,\n  /\\bcluster\\s+\\$/i, // \"Cluster $CP\"-style crypto-project naming pattern seen in this dataset\n];\n\nexport interface IdentityFlagResult {\n  flagged: boolean;\n  reasons: string[];\n}\n\n/**\n * Text-only heuristic pass over a ticker's evidence posts. Never fabricates\n * a verdict — only raises reasons for a human (or a subsequent semantic\n * review pass) to look at. A ticker with zero flagged reasons is NOT thereby\n * \"Confirmed Correct\" — absence of a flag is not evidence of correctness\n * (same \"absence is not neutral\" principle used throughout this project).\n */\nexport function detectPotentialIdentityCollision(\n  ticker: string,\n  evidenceTexts: string[],\n  resolvedCompanyName?: string | null,\n): IdentityFlagResult {\n  const reasons: string[] = [];\n  const upperTicker = ticker.toUpperCase();\n  const combinedText = evidenceTexts.join(\" \\n \");\n\n  if (COMMON_WORD_OR_GENERIC_ABBREVIATION_TICKERS.has(upperTicker)) {\n    reasons.push(`Ticker \"${upperTicker}\" is also a common word/generic abbreviation — mechanical extraction cannot by itself confirm it refers to the security.`);\n  }\n\n  if (CRYPTO_LANGUAGE_MARKERS.some((re) => re.test(combinedText))) {\n    reasons.push(\"Post text uses crypto/token-economy language (e.g. TGE, tokenomics, mainnet, staking) alongside this cashtag — possible crypto-token vs. listed-equity collision.\");\n  }\n\n  // Explicit \" / $TICKER\" or \" $TICKER\" juxtaposition where the\n  // named entity does not textually overlap with the resolved company name\n  // is a strong sign the trader is drawing a shorthand/slash equivalence\n  // between two DIFFERENT companies (e.g. \"Samsung / $SKHY\").\n  const slashPattern = new RegExp(`([A-Z][A-Za-z]+)\\\\s*/\\\\s*\\\\$${upperTicker}\\\\b`);\n  const slashMatch = combinedText.match(slashPattern);\n  if (slashMatch) {\n    const namedEntity = slashMatch[1];\n    const overlaps = resolvedCompanyName\n      ? resolvedCompanyName.toLowerCase().includes(namedEntity.toLowerCase())\n      : false;\n    if (!overlaps) {\n      reasons.push(\n        `Post text directly juxtaposes a different named entity (\"${namedEntity}\") with $${upperTicker} (e.g. \"${namedEntity} / $${upperTicker}\"), which does not textually match the resolved company name${\n          resolvedCompanyName ? ` (\"${resolvedCompanyName}\")` : \" (unknown/unverified)\"\n        } — contextually inconsistent company risk.`,\n      );\n    }\n  }\n\n  // A ticker resolves to a company whose name shares no recognizable word\n  // with any text discussing it — possible foreign-listing or unrelated\n  // company symbol collision. Only fires when a resolved name is available\n  // to compare against (never guesses a name).\n  if (resolvedCompanyName) {\n    const resolvedTokens = resolvedCompanyName.toLowerCase().split(/\\W+/).filter((t) => t.length > 3);\n    const textLower = combinedText.toLowerCase();\n    const anyResolvedTokenPresent = resolvedTokens.some((t) => textLower.includes(t));\n    if (!anyResolvedTokenPresent && resolvedTokens.length > 0) {\n      reasons.push(\n        `Resolved company name (\"${resolvedCompanyName}\") shares no recognizable word with the post text discussing $${upperTicker} — possible foreign-listing or unrelated-company symbol collision.`,\n      );\n    }\n  }\n\n  return { flagged: reasons.length > 0, reasons };\n}\n\n/**\n * Runs the heuristic safeguard over tickers that have no existing\n * ticker_identity_reviews row yet, and records a lightweight\n * 'Unreviewed - Flagged' row for any that trip a heuristic —\n * exclude_from_consensus stays false (flag only; never auto-excludes). This\n * surfaces likely collisions for human review on the next reporting pass\n * WITHOUT altering that ticker's current consensus/Discovery Score\n * treatment. Idempotent per ticker: skips any ticker that already has ANY\n * review row (reviewed OR previously flagged), so it never re-flags or\n * spams duplicate rows run after run.\n */\nexport async function runIdentitySafeguardSweep(\n  tickerEvidence: Map,\n): Promise<{ newly_flagged: { ticker: string; reasons: string[] }[] }> {\n  await ensureSchema();\n  const existingReviews = await getLatestTickerIdentityReviews();\n  const newlyFlagged: { ticker: string; reasons: string[] }[] = [];\n\n  for (const [ticker, { texts, resolvedCompanyName }] of tickerEvidence.entries()) {\n    if (existingReviews.has(ticker.toUpperCase())) continue; // already reviewed or already flagged — don't spam\n    const result = detectPotentialIdentityCollision(ticker, texts, resolvedCompanyName);\n    if (result.flagged) {\n      await recordTickerIdentityReview({\n        ticker,\n        classification: \"Unreviewed - Flagged\",\n        resolved_entity: resolvedCompanyName ?? null,\n        intended_entity: null,\n        exclude_from_consensus: false,\n        evidence: [],\n        flag_reasons: result.reasons,\n        notes: \"Auto-flagged by the ticker-identity safeguard heuristic. NOT excluded from scoring — requires explicit human/semantic review to classify.\",\n      });\n      newlyFlagged.push({ ticker: ticker.toUpperCase(), reasons: result.reasons });\n    }\n  }\n\n  return { newly_flagged: newlyFlagged };\n}\n",[],[181,250],"e4fe65e4-6d86-4f40-be1a-cce0dcf04989","schema.ts","9e4e244c-aa00-11f1-a2c0-1607ee4eb77e",54,["D",1788840389768],"import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\n\n// Idempotent schema setup — safe to call on every request/run.\nexport async function ensureSchema() {\n  await sqlite.batch([\n    {\n      sql: `CREATE TABLE IF NOT EXISTS accounts (\n        username TEXT PRIMARY KEY,      -- the @handle, exactly as configured\n        account_id TEXT UNIQUE,         -- numeric X user ID, resolved via API\n        display_name TEXT,\n        last_post_id TEXT,              -- newest post_id ingested so far (since_id cursor)\n        last_collected_at TEXT,\n        resolved_at TEXT\n      )`,\n    },\n    {\n      // Normalized post schema per spec. The six analytical fields\n      // (explicit_stance, evidence, thesis, catalyst, time_horizon,\n      // position_disclosure) are deliberately left NULL by the collector.\n      // Per the architecture (X API -> collector -> storage -> Claude ->\n      // consensus), a separate Claude analysis pass reads unanalyzed rows\n      // (analyzed_at IS NULL) and fills these in from the post text only —\n      // never inferred mechanically, and never guessed by the collector.\n      sql: `CREATE TABLE IF NOT EXISTS posts (\n        post_id TEXT PRIMARY KEY,\n        account TEXT NOT NULL,               -- display name\n        username TEXT NOT NULL,              -- @handle\n        posted_at TEXT NOT NULL,\n        post_type TEXT NOT NULL,             -- original | reply | quote | repost\n        referenced_post_id TEXT,             -- underlying tweet id for reply/quote/repost\n        referenced_author_id TEXT,\n        text TEXT NOT NULL,\n        mentioned_tickers TEXT NOT NULL DEFAULT '[]',  -- JSON array, mechanical cashtag extraction\n        explicit_stance TEXT,                 -- Bullish | Bearish | Unclear | NULL (not yet analyzed)\n        evidence TEXT,                        -- supporting quote/excerpt for the stance\n        thesis TEXT,\n        catalyst TEXT,\n        time_horizon TEXT,\n        position_disclosure TEXT,             -- ONLY filled when the post explicitly states one\n        engagement_metrics TEXT NOT NULL DEFAULT '{}', -- JSON: retweet/reply/like/quote/impression counts\n        original_url TEXT NOT NULL,\n        ingested_at TEXT NOT NULL,\n        analyzed_at TEXT                      -- set by the (future) Claude analysis pass\n      )`,\n    },\n    { sql: `CREATE INDEX IF NOT EXISTS idx_posts_account ON posts(username)` },\n    { sql: `CREATE INDEX IF NOT EXISTS idx_posts_referenced ON posts(referenced_post_id)` },\n    { sql: `CREATE INDEX IF NOT EXISTS idx_posts_analyzed ON posts(analyzed_at)` },\n    {\n      sql: `CREATE TABLE IF NOT EXISTS collector_runs (\n        id INTEGER PRIMARY KEY AUTOINCREMENT,\n        started_at TEXT NOT NULL,\n        finished_at TEXT,\n        accounts_processed INTEGER DEFAULT 0,\n        posts_ingested INTEGER DEFAULT 0,\n        errors TEXT\n      )`,\n    },\n    // Phase 2 (additive, non-destructive): a snapshot table for ticker-level\n    // consensus. Each analysis run appends new rows here rather than\n    // overwriting — this is what lets stance-change-over-time (1D/7D/28D)\n    // and attention_acceleration eventually be computed by diffing\n    // snapshots, once enough runs have accumulated history.\n    {\n      sql: `CREATE TABLE IF NOT EXISTS ticker_consensus (\n        id INTEGER PRIMARY KEY AUTOINCREMENT,\n        ticker TEXT NOT NULL,\n        computed_at TEXT NOT NULL,\n        window_start TEXT,\n        window_end TEXT,\n        unique_accounts_mentioning INTEGER NOT NULL,\n        bullish_accounts INTEGER NOT NULL,\n        bearish_accounts INTEGER NOT NULL,\n        unclear_accounts INTEGER NOT NULL,\n        bullish_ratio REAL,\n        bearish_ratio REAL,\n        consensus_direction TEXT,\n        disagreement_level TEXT,\n        disagreement_score REAL,\n        mention_count INTEGER NOT NULL,\n        attention_acceleration REAL,\n        attention_note TEXT,\n        stance_change_1d TEXT,\n        stance_change_7d TEXT,\n        stance_change_28d TEXT\n      )`,\n    },\n    { sql: `CREATE INDEX IF NOT EXISTS idx_ticker_consensus_ticker ON ticker_consensus(ticker, computed_at)` },\n  ]);\n\n  // Additive column migration for posts.stance_tickers (Phase 2). Kept\n  // separate from mentioned_tickers per spec: mentioned_tickers is the\n  // MECHANICAL cashtag extraction (untouched, from Phase 1); stance_tickers\n  // is the SEMANTIC subset of those tickers that the analyzed stance\n  // actually applies to. Wrapped in try/catch because ALTER TABLE ADD\n  // COLUMN errors if the column already exists on a prior deploy.\n  try {\n    await sqlite.execute(\n      `ALTER TABLE posts ADD COLUMN stance_tickers TEXT NOT NULL DEFAULT '[]'`,\n    );\n  } catch {\n    // Column already exists — no-op.\n  }\n\n  // Phase 2.5 (additive, non-destructive): posts.semantic_tickers.\n  // Each element is a full assignment object:\n  //   { ticker, company_name, evidence, confidence: High|Medium|Low, recognition_reason }\n  // This is entity/company recognition WITHOUT a cashtag — kept fully\n  // distinguishable from mentioned_tickers (mechanical, untouched) and from\n  // stance_tickers (mechanical-ticker subset the post's stance applies to).\n  // A ticker never appears in both mentioned_tickers and semantic_tickers\n  // for the same post (dedup rule) — semantic_tickers only covers companies\n  // discovered WITHOUT a cashtag.\n  try {\n    await sqlite.execute(\n      `ALTER TABLE posts ADD COLUMN semantic_tickers TEXT NOT NULL DEFAULT '[]'`,\n    );\n  } catch {\n    // Column already exists — no-op.\n  }\n\n  // Phase 2.5: transparency columns on ticker_consensus so a snapshot shows\n  // WHERE its attention/consensus numbers came from — mechanical cashtags\n  // vs. high-confidence semantic recognition — without collapsing the\n  // distinction the spec requires (\"mechanical always remains\n  // distinguishable from semantic\").\n  for (\n    const col of [\n      `ALTER TABLE ticker_consensus ADD COLUMN discovery_method TEXT`,\n      `ALTER TABLE ticker_consensus ADD COLUMN mechanical_mention_count INTEGER`,\n      `ALTER TABLE ticker_consensus ADD COLUMN semantic_high_mention_count INTEGER`,\n      `ALTER TABLE ticker_consensus ADD COLUMN semantic_medium_flagged_count INTEGER`,\n    ]\n  ) {\n    try {\n      await sqlite.execute(col);\n    } catch {\n      // Column already exists — no-op.\n    }\n  }\n\n  // Phase 3.1 (additive, non-destructive): posts.referenced_author_handle /\n  // referenced_author_name / referenced_author_source. referenced_author_id\n  // already existed as a column (it was just never populated — see\n  // lib/xapi.ts and lib/normalize.ts for the fix). These three are new:\n  // handle/name are the resolved identity when API provenance was\n  // available; source distinguishes 'api' (resolved from the X API's own\n  // includes.tweets/includes.users expansion data) from 'text_fallback'\n  // (recovered only by regex-parsing \"RT @handle:\" text when API\n  // provenance was unavailable) — kept clearly distinguishable per the\n  // spec, never conflated.\n  for (\n    const col of [\n      `ALTER TABLE posts ADD COLUMN referenced_author_handle TEXT`,\n      `ALTER TABLE posts ADD COLUMN referenced_author_name TEXT`,\n      `ALTER TABLE posts ADD COLUMN referenced_author_source TEXT`,\n    ]\n  ) {\n    try {\n      await sqlite.execute(col);\n    } catch {\n      // Column already exists — no-op.\n    }\n  }\n\n  // Phase 3: Trader Discovery Layer. Fully separate from `accounts` (the\n  // permanent Core Trader Set, untouched) and from `posts`/`ticker_consensus`\n  // (Core Trader Consensus) — a discovered account can NEVER affect\n  // consensus math until a human explicitly promotes it (out of scope for\n  // this phase; promotion_status is always 'Not Promoted' here).\n  await sqlite.batch([\n    {\n      sql: `CREATE TABLE IF NOT EXISTS discovered_accounts (\n        id INTEGER PRIMARY KEY AUTOINCREMENT,\n        handle TEXT NOT NULL UNIQUE,             -- as first observed (casing preserved)\n        x_account_id TEXT,                       -- NULL: not resolvable from text-only signals (see README limitations)\n        display_name TEXT,                       -- NULL: unknown until/unless the account is ever collected directly\n        account_group TEXT NOT NULL,             -- 'discovered_market_voices' | 'sector_specialists' | 'emerging_accounts'\n        discovered_at TEXT NOT NULL,\n        discovery_source TEXT NOT NULL,          -- JSON array of source descriptors, e.g. [\"reposted_by:KawzInvests\"]\n        discovery_evidence TEXT NOT NULL,        -- JSON array of {citing_account, post_id, post_type, text, original_url}\n        sectors_discussed TEXT NOT NULL DEFAULT '[]',   -- JSON array, heuristic (from co-occurring context)\n        tickers_discussed TEXT NOT NULL DEFAULT '[]',   -- JSON array, mechanical, tentative attribution from citing posts\n        unique_tickers_discussed INTEGER NOT NULL DEFAULT 0,\n        original_post_ratio REAL,                -- NULL: requires the candidate's OWN timeline (not yet collected)\n        repost_ratio REAL,                       -- NULL: same limitation\n        posting_activity TEXT,                   -- NULL: same limitation\n        market_relevant_post_count INTEGER NOT NULL DEFAULT 0,\n        times_referenced INTEGER NOT NULL DEFAULT 0,\n        referencing_core_accounts TEXT NOT NULL DEFAULT '[]', -- JSON array of Core Trader usernames\n        evidence_quality TEXT NOT NULL,          -- High | Medium | Low\n        stance_clarity TEXT NOT NULL,            -- Clear | Mixed | Unknown (Unknown unless a fragment shows a clear directional claim)\n        specialization TEXT,                     -- NULL if no discernible theme across evidence\n        relevance_score REAL NOT NULL,           -- 0-100, transparent components (see lib/discovery.ts)\n        confidence TEXT NOT NULL,                -- Low | Medium | High (confidence in the discovery/classification itself)\n        last_evaluated TEXT NOT NULL,\n        promotion_status TEXT NOT NULL DEFAULT 'Not Promoted' -- never auto-changed by any code path\n      )`,\n    },\n    { sql: `CREATE INDEX IF NOT EXISTS idx_discovered_accounts_group ON discovered_accounts(account_group)` },\n    {\n      sql: `CREATE TABLE IF NOT EXISTS discovery_rejections (\n        id INTEGER PRIMARY KEY AUTOINCREMENT,\n        candidate_handle TEXT NOT NULL,\n        reason TEXT NOT NULL,\n        evidence TEXT,                           -- JSON: {citing_account, post_id, text}\n        evaluated_at TEXT NOT NULL\n      )`,\n    },\n  ]);\n\n  // Phase 3.2: Discovered Trader Candidate Validation. Fully separate from\n  // `posts` (Core Trader dataset) so provenance never blends the two — a\n  // candidate's own timeline sample lives only here until/unless a human\n  // promotion decision (out of scope for this phase) moves anything.\n  // Nothing here can ever affect Core Trader consensus math.\n  await sqlite.batch([\n    {\n      sql: `CREATE TABLE IF NOT EXISTS candidate_validation_posts (\n        post_id TEXT PRIMARY KEY,\n        candidate_handle TEXT NOT NULL,          -- as recorded in discovered_accounts\n        posted_at TEXT NOT NULL,\n        post_type TEXT NOT NULL,                 -- original | reply | quote | repost\n        referenced_post_id TEXT,\n        referenced_author_id TEXT,\n        referenced_author_handle TEXT,\n        referenced_author_name TEXT,\n        referenced_author_source TEXT,           -- 'api' | 'text_fallback' | NULL\n        text TEXT NOT NULL,\n        mentioned_tickers TEXT NOT NULL DEFAULT '[]',\n        engagement_metrics TEXT NOT NULL DEFAULT '{}',\n        original_url TEXT NOT NULL,\n        ingested_at TEXT NOT NULL,\n        validation_run_id INTEGER\n      )`,\n    },\n    { sql: `CREATE INDEX IF NOT EXISTS idx_cvp_handle ON candidate_validation_posts(candidate_handle)` },\n    {\n      sql: `CREATE TABLE IF NOT EXISTS candidate_validation_runs (\n        id INTEGER PRIMARY KEY AUTOINCREMENT,\n        started_at TEXT NOT NULL,\n        finished_at TEXT,\n        candidates_requested TEXT NOT NULL,      -- JSON array of handles requested\n        max_results_per_candidate INTEGER NOT NULL,\n        api_requests_made INTEGER NOT NULL DEFAULT 0,\n        posts_retrieved INTEGER NOT NULL DEFAULT 0,\n        posts_stored_new INTEGER NOT NULL DEFAULT 0,\n        errors TEXT\n      )`,\n    },\n    {\n      // Populated by a separate semantic-review pass (same mechanical/\n      // semantic split used throughout this project) — never by the\n      // collector itself. classification/eligibility are human judgment\n      // calls over the stored evidence, not mechanically inferred.\n      sql: `CREATE TABLE IF NOT EXISTS candidate_validation_results (\n        id INTEGER PRIMARY KEY AUTOINCREMENT,\n        candidate_handle TEXT NOT NULL,\n        evaluated_at TEXT NOT NULL,\n        posts_analyzed INTEGER NOT NULL,\n        original_post_ratio REAL,\n        repost_ratio REAL,\n        reply_ratio REAL,\n        quote_ratio REAL,\n        market_relevant_post_ratio REAL,\n        distinct_tickers_discussed INTEGER,\n        tickers_list TEXT NOT NULL DEFAULT '[]',\n        explicit_thesis_post_count INTEGER,\n        evidence_catalyst_quality TEXT,          -- High | Medium | Low\n        noise_banter_ratio REAL,\n        independence_note TEXT,\n        sector_specialization TEXT,\n        adds_new_info_vs_core10 TEXT,            -- Yes | Partial | No, with note\n        classification TEXT NOT NULL,            -- Strong Candidate | Watch Candidate | Reject / Noise\n        proposed_account_group TEXT,\n        eligible_for_promotion_review INTEGER NOT NULL DEFAULT 0,\n        notes TEXT\n      )`,\n    },\n  ]);\n\n  // Phase 4: Market Discovery Score V1. A RESEARCH PRIORITIZATION layer on\n  // top of the existing Trader Consensus (`ticker_consensus`, untouched —\n  // Component A reads it read-only) and the Validated Discovery Signal\n  // (`candidate_validation_results`, also untouched and NEVER merged into\n  // Core Trader consensus math — it only contributes to Components C/D/F\n  // here). Append-only: every /market-discovery/run call gets a new\n  // run_id and a fresh set of snapshot rows; nothing here is ever\n  // overwritten in place except via the explicit, same-run\n  // apply-fundamentals step that fills in the bounded Component E lookup\n  // for that run's finalists. Never writes to `accounts`,\n  // `discovered_accounts`, `posts`, or `ticker_consensus`.\n  await sqlite.batch([\n    {\n      sql: `CREATE TABLE IF NOT EXISTS market_discovery_runs (\n        id INTEGER PRIMARY KEY AUTOINCREMENT,\n        generated_at TEXT NOT NULL,\n        ticker_universe_size INTEGER NOT NULL,\n        core_ticker_count INTEGER NOT NULL,        -- tickers with Core Trader (ticker_consensus) coverage\n        new_discovery_count INTEGER NOT NULL,       -- tickers with ZERO Core Trader coverage, Validated Discovery Signal only\n        finalist_count INTEGER NOT NULL DEFAULT 0,  -- how many rows are queued for bounded Component E lookup\n        fundamentals_applied_count INTEGER NOT NULL DEFAULT 0,\n        core_traders_count INTEGER NOT NULL,        -- snapshot of accounts table row count at run time (must stay 10)\n        promoted_discovered_accounts INTEGER NOT NULL, -- snapshot of discovered_accounts w/ promotion_status != 'Not Promoted' (must stay 0)\n        notes TEXT\n      )`,\n    },\n    {\n      // One row per ranked ticker per run. Component scores always sum to\n      // discovery_score for that row: when fundamental_market_score is\n      // NULL (Component E not yet checked — bounded to that run's\n      // finalists per the cost constraint in the Phase 4 spec),\n      // discovery_score is the 90-point sum of the other five components\n      // and fundamental_market_score stays NULL rather than a fabricated\n      // placeholder (\"missing fundamental data must remain missing\").\n      sql: `CREATE TABLE IF NOT EXISTS market_discovery_snapshots (\n        id INTEGER PRIMARY KEY AUTOINCREMENT,\n        run_id INTEGER NOT NULL,\n        ticker TEXT NOT NULL,\n        company_name TEXT,\n        discovery_score REAL NOT NULL,\n        max_possible_score REAL NOT NULL,          -- 100 once Component E is applied, else 90 (provisional)\n        trader_consensus_score REAL NOT NULL,      -- Component A, 0-25\n        attention_acceleration_score REAL NOT NULL,-- Component B, 0-20 (V1 temporary proxy — see attention_note)\n        catalyst_score REAL NOT NULL,               -- Component C, 0-20\n        cross_source_score REAL NOT NULL,           -- Component D, 0-15\n        fundamental_market_score REAL,              -- Component E, 0-10, NULL until applied for this row\n        novelty_score REAL NOT NULL,                -- Component F, 0-10\n        bullish_unique_accounts INTEGER NOT NULL DEFAULT 0,\n        bearish_unique_accounts INTEGER NOT NULL DEFAULT 0,\n        unclear_unique_accounts INTEGER NOT NULL DEFAULT 0,\n        consensus_state TEXT NOT NULL,              -- Bullish | Bearish | Disagreement | No Core Trader Coverage | ...\n        catalyst_tier TEXT,                         -- High-Multi | High-Single | Medium | Low | None\n        key_thesis TEXT,\n        key_catalyst TEXT,\n        evidence TEXT NOT NULL DEFAULT '[]',        -- JSON array of {source_layer, account_or_source, quote}\n        source_layers TEXT NOT NULL DEFAULT '[]',   -- JSON array subset of [\"core_trader\",\"validated_discovery\",\"general_market\"]\n        attention_note TEXT NOT NULL,\n        fundamental_note TEXT,\n        watchlist_status TEXT NOT NULL,             -- 'Existing Watchlist' | 'New Discovery'\n        is_new_discovery_ticker INTEGER NOT NULL DEFAULT 0, -- 1 = zero Core Trader ticker_consensus coverage\n        is_finalist INTEGER NOT NULL DEFAULT 0,     -- 1 = selected for bounded Component E lookup this run\n        generated_at TEXT NOT NULL\n      )`,\n    },\n    { sql: `CREATE INDEX IF NOT EXISTS idx_mds_run ON market_discovery_snapshots(run_id, discovery_score)` },\n    { sql: `CREATE INDEX IF NOT EXISTS idx_mds_ticker ON market_discovery_snapshots(ticker, run_id)` },\n  ]);\n\n  // Ticker/Entity Identity Review layer (production-data cleanup). Fully\n  // additive, append-only (same pattern as market_discovery_snapshots): a\n  // review is never overwritten in place, so history/auditability is\n  // preserved — the LATEST row per ticker (by reviewed_at) is what governs.\n  // This table is consulted (read-only) by computeTickerConsensus/\n  // runConsensusAggregation and by runMarketDiscovery's universe builder to\n  // exclude a ticker's assignment from consensus/Discovery Score math\n  // WITHOUT ever touching posts.mentioned_tickers or deleting a post — the\n  // raw mechanical extraction always stays intact for auditability.\n  // classification is one of: 'Confirmed Correct' | 'Confirmed False Positive'\n  // | 'Ambiguous/Exclude' | 'Unreviewed - Flagged' (the last is written only\n  // by the automated heuristic safeguard below — it FLAGS, it never sets\n  // exclude_from_consensus itself; only an explicit human/semantic review\n  // with classification 'Confirmed False Positive' or 'Ambiguous/Exclude'\n  // sets exclude_from_consensus = 1).\n  await sqlite.batch([\n    {\n      sql: `CREATE TABLE IF NOT EXISTS ticker_identity_reviews (\n        id INTEGER PRIMARY KEY AUTOINCREMENT,\n        ticker TEXT NOT NULL,\n        classification TEXT NOT NULL,\n        resolved_entity TEXT,             -- what the mechanical ticker symbol actually/officially resolves to\n        intended_entity TEXT,             -- what the post evidence indicates the trader(s) actually meant\n        exclude_from_consensus INTEGER NOT NULL DEFAULT 0, -- 1 = exclude this ticker from consensus + Discovery Score calculations\n        evidence TEXT NOT NULL DEFAULT '[]',      -- JSON array of {post_id, quote}\n        flag_reasons TEXT NOT NULL DEFAULT '[]',  -- JSON array of heuristic reasons (populated by the automated safeguard)\n        reviewed_by TEXT NOT NULL DEFAULT 'claude_semantic_review',\n        reviewed_at TEXT NOT NULL,\n        notes TEXT\n      )`,\n    },\n    { sql: `CREATE INDEX IF NOT EXISTS idx_tir_ticker ON ticker_identity_reviews(ticker, reviewed_at)` },\n  ]);\n}\n",[],[181,258],"8d07863c-5033-494a-9d5a-e3bbd0e64c07","normalize.ts","a897b31e-aa00-11f1-a970-1607ee4eb77e",36,["D",1788785315011],"import type { XIncludeTweet, XIncludeUser, XTweet } from \"./xapi.ts\";\n\n// Mechanical cashtag extraction (e.g. \"$AAPL\", \"$BRK.B\"). This is a\n// mechanical signal only — it says a ticker was mentioned, nothing about\n// sentiment. \"Not Mentioned is NOT Neutral\": absence from this list must\n// never be read as a bearish or neutral signal downstream.\nconst CASHTAG_RE = /\\$[A-Za-z]{1,6}(?:\\.[A-Za-z]{1,3})?\\b/g;\n\nexport function extractTickers(text: string, entities?: XTweet[\"entities\"]): string[] {\n  const set = new Set();\n  for (const m of text.matchAll(CASHTAG_RE)) {\n    set.add(m[0].slice(1).toUpperCase());\n  }\n  for (const c of entities?.cashtags ?? []) {\n    set.add(c.tag.toUpperCase());\n  }\n  return [...set].sort();\n}\n\n// Classifies post type from the X API's referenced_tweets array.\n// Priority when multiple references exist: repost > quote > reply, since a\n// pure retweet is definitionally a repost regardless of other flags.\nexport function classifyPostType(tweet: XTweet): {\n  postType: \"original\" | \"reply\" | \"quote\" | \"repost\";\n  referencedPostId: string | null;\n} {\n  const refs = tweet.referenced_tweets ?? [];\n  const retweeted = refs.find((r) => r.type === \"retweeted\");\n  if (retweeted) return { postType: \"repost\", referencedPostId: retweeted.id };\n  const quoted = refs.find((r) => r.type === \"quoted\");\n  if (quoted) return { postType: \"quote\", referencedPostId: quoted.id };\n  const replied = refs.find((r) => r.type === \"replied_to\");\n  if (replied) return { postType: \"reply\", referencedPostId: replied.id };\n  return { postType: \"original\", referencedPostId: null };\n}\n\n// Phase 3.1: resolves a referenced_post_id to its actual author using the\n// `includes` data the X API already returns for the\n// referenced_tweets.id / referenced_tweets.id.author_id expansions —\n// previously requested but discarded (see lib/xapi.ts). Built once per\n// API response (one fetchUserTweets call) and reused across every tweet\n// in that response.\nexport interface ReferencedAuthorLookup {\n  authorIdByTweetId: Map;\n  userByAuthorId: Map;\n}\n\nexport function buildReferencedAuthorLookup(\n  includes: { tweets?: XIncludeTweet[]; users?: XIncludeUser[] },\n): ReferencedAuthorLookup {\n  const authorIdByTweetId = new Map();\n  for (const t of includes.tweets ?? []) authorIdByTweetId.set(t.id, t.author_id);\n  const userByAuthorId = new Map();\n  for (const u of includes.users ?? []) userByAuthorId.set(u.id, { username: u.username, name: u.name });\n  return { authorIdByTweetId, userByAuthorId };\n}\n\nexport interface NormalizedPost {\n  post_id: string;\n  account: string;\n  username: string;\n  posted_at: string;\n  post_type: string;\n  referenced_post_id: string | null;\n  referenced_author_id: string | null;\n  referenced_author_handle: string | null;\n  referenced_author_name: string | null;\n  // 'api': resolved from the X API's own includes.tweets/includes.users\n  // expansion data — high confidence, this is X telling us who wrote it.\n  // 'text_fallback': recovered by regex from \"RT @handle: ...\" text because\n  // API provenance was unavailable (e.g. the referenced tweet/author fell\n  // outside what the API included) — lower confidence, kept clearly\n  // distinguishable per the spec.\n  // null: no reference, or a reference we could not resolve either way.\n  referenced_author_source: \"api\" | \"text_fallback\" | null;\n  text: string;\n  mentioned_tickers: string[];\n  engagement_metrics: Record;\n  original_url: string;\n  ingested_at: string;\n}\n\n// Produces the MECHANICAL portion of the normalized schema only.\n// explicit_stance / evidence / thesis / catalyst / time_horizon /\n// position_disclosure are intentionally NOT set here — see lib/schema.ts\n// for why. Filling those in here would risk inferring a position from\n// keywords, which the spec explicitly forbids.\nexport function normalizeTweet(\n  tweet: XTweet,\n  account: { username: string; name: string },\n  lookup?: ReferencedAuthorLookup,\n): NormalizedPost {\n  const { postType, referencedPostId } = classifyPostType(tweet);\n\n  let referencedAuthorId: string | null = null;\n  let referencedAuthorHandle: string | null = null;\n  let referencedAuthorName: string | null = null;\n  let referencedAuthorSource: \"api\" | \"text_fallback\" | null = null;\n\n  if (referencedPostId && lookup) {\n    const authorId = lookup.authorIdByTweetId.get(referencedPostId);\n    if (authorId) {\n      referencedAuthorId = authorId;\n      referencedAuthorSource = \"api\";\n      const user = lookup.userByAuthorId.get(authorId);\n      if (user) {\n        referencedAuthorHandle = user.username;\n        referencedAuthorName = user.name;\n      }\n    }\n  }\n\n  // Fallback ONLY when API provenance did not resolve an author, and ONLY\n  // for classic reposts, where \"RT @handle: ...\" is a literal quote of the\n  // original account's own handle straight from X's tweet text (not an\n  // inference from reply/ticker content — rule 6). Never runs when API\n  // provenance already succeeded, so a lower-confidence signal can never\n  // override a higher-confidence one.\n  if (!referencedAuthorId && postType === \"repost\") {\n    const m = tweet.text.match(/^RT @(\\w+):/);\n    if (m) {\n      referencedAuthorHandle = m[1];\n      referencedAuthorSource = \"text_fallback\";\n    }\n  }\n\n  return {\n    post_id: tweet.id,\n    account: account.name,\n    username: account.username,\n    posted_at: tweet.created_at,\n    post_type: postType,\n    referenced_post_id: referencedPostId,\n    referenced_author_id: referencedAuthorId,\n    referenced_author_handle: referencedAuthorHandle,\n    referenced_author_name: referencedAuthorName,\n    referenced_author_source: referencedAuthorSource,\n    text: tweet.text,\n    mentioned_tickers: extractTickers(tweet.text, tweet.entities),\n    engagement_metrics: tweet.public_metrics ?? {},\n    original_url: `https://x.com/${account.username}/status/${tweet.id}`,\n    ingested_at: new Date().toISOString(),\n  };\n}\n",[],[181,266],"cc44ad33-289d-4ccd-8c13-f83c709eaa62","new-file-4892.ts","9bcf9d98-ab88-11f1-b30f-1607ee4eb77e",70,["D",1788873864298],[],[181,274],"5b46193f-b0fe-4796-9685-4f5001378f54","marketDiscovery.ts","a87f9822-aaca-11f1-b073-1607ee4eb77e",66,["D",1788840533731],6,"// Phase 4: Market Discovery Score V1.\n//\n// A RESEARCH PRIORITIZATION engine, not an investment recommendation engine.\n// Follows the same mechanical/semantic split used throughout this project:\n//  - Components A, B, D, F are computed MECHANICALLY here from data already\n//    in the database (ticker_consensus for Core Trader signal;\n//    candidate_validation_results for the Validated Discovery Signal layer).\n//  - Component C (Catalyst / Information Quality) is a SEMANTIC judgment —\n//    it cannot be derived from cashtag counts, so it is supplied as an\n//    explicit, evidence-quoted lookup table (CATALYST_TABLE below) built by\n//    reading the actual analyzed post text, exactly like the Phase 2.5\n//    semantic ticker pass. A ticker absent from CATALYST_TABLE gets tier\n//    \"None\" / 0 points — silence is never treated as evidence.\n//  - Component E (Fundamental / Market Quality Gate) is intentionally NOT\n//    computed here. It requires an external data source (FMP) that this val\n//    has no credentials for, and the Phase 4 spec explicitly bounds its use\n//    (\"avoid forcing expensive new integrations\"). It is filled in by a\n//    separate, explicit /market-discovery/apply-fundamentals call for a\n//    run's finalists only — every other row keeps fundamental_market_score\n//    NULL rather than a fabricated placeholder.\n//\n// CRITICAL: this module NEVER writes to `accounts`, `discovered_accounts`,\n// `posts`, or `ticker_consensus`. Validated Discovery Signal tickers are\n// scored 0 on Component A (Trader Consensus) by construction — their\n// evidence contributes only to C, D, and F, never merged into Core Trader\n// consensus math, per the Phase 4 spec.\n\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { ensureSchema } from \"./schema.ts\";\nimport { getExcludedTickers, getLatestTickerIdentityReviews } from \"./tickerIdentity.ts\";\n\n// ---------------------------------------------------------------------------\n// Static reference data (all [stated]/observed, not fabricated)\n// ---------------------------------------------------------------------------\n\n// The user's permanent 15-ticker watchlist (source: user's own memory file,\n// not derived from this project's data). Used ONLY for the watchlist_status\n// LABEL — never as a scoring input, per the spec's explicit instruction not\n// to penalize or reward watchlist membership.\nconst WATCHLIST_15 = new Set([\n  \"QQQ\", \"TSLA\", \"PYPL\", \"META\", \"CAKE\", \"AMZN\", \"NVDA\", \"SE\", \"GOOGL\",\n  \"AVGO\", \"XYZ\", \"SOXX\", \"AMD\", \"TSM\", \"ABSI\",\n]);\n\n// Structurally large / \"permanently popular\" mega-caps. Used ONLY to damp\n// Component F (Novelty) per the spec's explicit instruction not to reward\n// stocks \"merely permanently popular\" — independent of watchlist status.\nconst MEGA_CAP_SET = new Set([\n  \"AAPL\", \"MSFT\", \"GOOGL\", \"AMZN\", \"META\", \"NVDA\", \"AVGO\", \"TSLA\",\n]);\n\n// Tickers that appear ONLY in the Validated Discovery Signal layer (Strong\n// Candidates pequityresearch / sssjeffpu's own timelines from Phase 3.2),\n// with ZERO Core Trader (ticker_consensus) coverage. Derived by set\n// subtraction against the live ticker_consensus universe at run time — see\n// buildUniverse(). Listed here as the raw Validated Discovery Signal ticker\n// set (Strong Candidates only — Watch/Reject candidates' mentions are NOT\n// evidence of investment quality per the Phase 3.2 rule and are excluded).\n// Source posts: candidate_validation_posts for pequityresearch/sssjeffpu.\n// NOTE: pequityresearch's post also mechanically cashtagged $DRAM and $DISK\n// — these are not real ticker symbols (sector-label pseudo-cashtags, not\n// securities) and are deliberately EXCLUDED here; this is flagged as a data\n// limitation in the Phase 4 report rather than silently dropped.\nconst VALIDATED_DISCOVERY_TICKERS: Record = {\n  AAOI: [\"pequityresearch\"],\n  COHR: [\"pequityresearch\"],\n  LITE: [\"pequityresearch\", \"sssjeffpu\"],\n  NVDA: [\"pequityresearch\", \"sssjeffpu\"],\n  MU: [\"pequityresearch\"],\n  EWY: [\"pequityresearch\"],\n  MRVL: [\"sssjeffpu\"],\n  DELL: [\"sssjeffpu\"],\n  CRDO: [\"sssjeffpu\"],\n  SMTC: [\"sssjeffpu\"],\n  UMC: [\"sssjeffpu\"],\n  MTK: [\"sssjeffpu\"],\n  INTC: [\"sssjeffpu\"],\n  SMCI: [\"sssjeffpu\"],\n  AMD: [\"sssjeffpu\"],\n};\n\ntype CatalystTier = \"High-Multi\" | \"High-Single\" | \"Medium\" | \"Low\" | \"None\";\nconst TIER_POINTS: Record = {\n  \"High-Multi\": 20,\n  \"High-Single\": 16,\n  \"Medium\": 10,\n  \"Low\": 4,\n  \"None\": 0,\n};\n\ninterface CatalystEntry {\n  tier: CatalystTier;\n  key_catalyst: string | null;\n  key_thesis: string | null;\n  evidence: { account_or_source: string; quote: string }[];\n}\n\n// Semantic catalyst/thesis-quality review — built by reading every analyzed\n// post's evidence/thesis/catalyst text (Core Trader `posts`) and every\n// Validated Discovery Signal candidate post (`candidate_validation_posts`)\n// for pequityresearch and sssjeffpu (Phase 3.2 Strong Candidates only).\n// Duplicate posts describing the SAME underlying real-world event (e.g. the\n// three BE / S&P 500 inclusion posts from 2 accounts) are collapsed to ONE\n// catalyst credit here, per the \"do not double-count the same thesis/event\"\n// rule. A ticker with only \"Unclear\" stance posts or mention-only evidence\n// is intentionally absent (tier \"None\").\nconst CATALYST_TABLE: Record = {\n  NVDA: {\n    tier: \"High-Multi\",\n    key_catalyst: \"F2Q27 earnings beat + $3.5B Nvidia investment in MediaTek\",\n    key_thesis: \"Supply-constrained AI revenue growth (~70% projected, 100%+ potential) reinforced by an actual F2Q27 beat and Nvidia's first Taiwan-listed equity investment\",\n    evidence: [\n      { account_or_source: \"aleabitoreddit (Core Trader)\", quote: \"$NVDA 70% supply constrained revenue growth projections (would be 100%+)\" },\n      { account_or_source: \"sssjeffpu (Validated Discovery)\", quote: \"F2Q actuals beat sell-side/buy-side consensus... Total revenue $96.2bn (+18% QoQ, +106% YoY)\" },\n      { account_or_source: \"sssjeffpu (Validated Discovery)\", quote: \"Nvidia to invest $3.5bn in MediaTek via its $3.9bn CB... Nvidia's first investment in a Taiwan-listed company\" },\n    ],\n  },\n  BE: {\n    tier: \"High-Multi\",\n    key_catalyst: \"S&P 500 index inclusion\",\n    key_thesis: \"Long-standing bullish BE thesis validated by index inclusion; expects continued growth\",\n    evidence: [\n      { account_or_source: \"KawzInvests (Core Trader)\", quote: \"BOOM $BE ADDED TO THE S&P 500\" },\n      { account_or_source: \"michaelsikand (Core Trader)\", quote: \"$BE to $100B\" },\n    ],\n  },\n  AVGO: {\n    tier: \"High-Multi\",\n    key_catalyst: \"Broadcom earnings commentary: Anthropic scaling to largest XPU customer 2027-2028\",\n    key_thesis: \"AI accelerator (XPU) demand scaling sharply; AI revenue growing 100%+ YoY for next 2 years (~$50B -> $230B)\",\n    evidence: [\n      { account_or_source: \"KawzInvests (Core Trader)\", quote: \"Anthropic is on track to become our largest XPU customer in 2027 and sustain that in 2028\" },\n      { account_or_source: \"aleabitoreddit (Core Trader)\", quote: \"$AVGO AI revenue growing 100%+ Y/Y for next 2 years from mid ~$50B -> $230B\" },\n    ],\n  },\n  AAOI: {\n    tier: \"High-Multi\",\n    key_catalyst: \"Evercore ISI: CPO at scale delayed to 2027/2028; AEC/pluggables to remain prominent\",\n    key_thesis: \"CW laser supply-demand imbalance underlies the bullish long thesis\",\n    evidence: [\n      { account_or_source: \"aleabitoreddit (Core Trader)\", quote: \"my research on AAOI's CW laser supply-demand imbalance\" },\n      { account_or_source: \"pequityresearch (Validated Discovery)\", quote: \"Evercore ISI: CPO at scale to not occur before 2027/2028 with yield and cost concerns\" },\n    ],\n  },\n  MU: {\n    tier: \"High-Multi\",\n    key_catalyst: \"pequityresearch: HBM4 pricing $2.9-4.5/Gb; sssjeffpu tech-tour turning positive on DRAM\",\n    key_thesis: \"Structural memory supply bottleneck intact despite short-term sentiment swings\",\n    evidence: [\n      { account_or_source: \"aleabitoreddit (Core Trader)\", quote: \"Good to have a reminder that the memory bottleneck hasn't changed!\" },\n      { account_or_source: \"pequityresearch (Validated Discovery)\", quote: \"Constructive 2027 view on tight supply + Chinese GPU demand. HBM4 asking ranging from $2.9-4.5/Gb\" },\n      { account_or_source: \"sssjeffpu (Validated Discovery)\", quote: \"Recent supply chain tour + SEMICON 2026 point to stronger DRAM outlook than feared... Turning positive on DRAM\" },\n    ],\n  },\n  LITE: {\n    tier: \"High-Multi\",\n    key_catalyst: \"Goldman Sachs 9x TAM growth forecast (GB300 NVL72) + Evercore ISI CPO-delay note\",\n    key_thesis: \"Interconnect/optical names remain structurally attractive despite sector rotation\",\n    evidence: [\n      { account_or_source: \"ren_stocks (Core Trader)\", quote: \"The aggregate value TAM across scale up and scale out would increase by 9x\" },\n      { account_or_source: \"pequityresearch (Validated Discovery)\", quote: \"AECs and pluggable form factors are expected to remain prominent for at least the next couple of years\" },\n      { account_or_source: \"sssjeffpu (Validated Discovery)\", quote: \"Abstract of SMIC and LITE Earnings Notes\" },\n    ],\n  },\n  CRDO: {\n    tier: \"High-Multi\",\n    key_catalyst: \"F1Q27 actuals: revenue $479M (+115% YoY), EPS beat, F2Q guide $525-535M, FY27E >85% YoY\",\n    key_thesis: \"CRDO invented the AEC cable and controls 88% of that market\",\n    evidence: [\n      { account_or_source: \"KawzInvests (Core Trader)\", quote: \"$CRDO invented the AEC cable, and now controls 88% of that market\" },\n      { account_or_source: \"sssjeffpu (Validated Discovery)\", quote: \"Revenue $479m (+115% YoY, +10% QoQ)... F2Q27 guide: Revenue $525-535m... FY27E growth: Mgmt expects >85% YoY revenue growth\" },\n    ],\n  },\n  COHR: {\n    tier: \"High-Multi\",\n    key_catalyst: \"Evercore ISI CPO-delay note + Goldman Sachs 9x TAM growth forecast\",\n    key_thesis: \"Beneficiary of AEC/pluggable prominence through 2027/2028\",\n    evidence: [\n      { account_or_source: \"pequityresearch (Validated Discovery)\", quote: \"AECs and pluggable form factors are expected to remain prominent for at least the next couple of years\" },\n      { account_or_source: \"ren_stocks (Core Trader)\", quote: \"The aggregate value TAM across scale up and scale out would increase by 9x\" },\n    ],\n  },\n  MRVL: {\n    tier: \"High-Multi\",\n    key_catalyst: \"F2Q actual results (rev $2.74B +37% YoY, FY27E raised to ~$12B) + maintained Buy, $290 PT\",\n    key_thesis: \"Custom silicon positioning with Google/AWS/MSFT; DSP ASP upside from shortages\",\n    evidence: [\n      { account_or_source: \"ren_stocks (Core Trader)\", quote: \"The aggregate value TAM across scale up and scale out would increase by 9x\" },\n      { account_or_source: \"sssjeffpu (Validated Discovery)\", quote: \"F2Q largely inline: $2.74bn rev (+13% QoQ / +37% YoY)... Raised FY27E rev to ~$12bn\" },\n      { account_or_source: \"sssjeffpu (Validated Discovery)\", quote: \"Maintained Buy rating with a $290 price target based on 20x FY29E EPS of $14.50\" },\n    ],\n  },\n  DELL: {\n    tier: \"High-Multi\",\n    key_catalyst: \"F2Q27E preview: AI server guidance raise to ~$75B + Dell family's $6.25B Trump Accounts pledge\",\n    key_thesis: \"AI server demand ramp alongside a high-profile capital-commitment signal\",\n    evidence: [\n      { account_or_source: \"aleabitoreddit (Core Trader)\", quote: \"The Dell family pledged $6.25B / $250 each to 25M children through Trump Accounts\" },\n      { account_or_source: \"sssjeffpu (Validated Discovery)\", quote: \"Expect AI server guidance raise to ~$75bn... NVL72 shipments cut to 17k\" },\n    ],\n  },\n  NOK: {\n    tier: \"High-Single\",\n    key_catalyst: \"€2.8B AI/cloud orders last quarter (CEO comments)\",\n    key_thesis: \"Optical/AI business seeing incredible growth tied to AI datacenter buildouts\",\n    evidence: [{ account_or_source: \"KawzInvests (Core Trader)\", quote: \"Optical business seeing incredible growth. €2.8B in AI and cloud orders last quarter\" }],\n  },\n  SIVE: {\n    tier: \"High-Single\",\n    key_catalyst: \">100m CW DFB extraordinary capacity disclosure\",\n    key_thesis: \"Capacity disclosure is a strong bullish signal given industry capacity context\",\n    evidence: [{ account_or_source: \"aleabitoreddit (Core Trader)\", quote: \"SIVE >100m CW DFB extraordinary capacity disclosure\" }],\n  },\n  INTC: {\n    tier: \"High-Single\",\n    key_catalyst: \"$20B upsized share offering (from $15B) priced at $95, institutional demand >$100B, CEO family subscribed ~$12M\",\n    key_thesis: \"Strong capital-raise demand signal plus 18A foundry progress\",\n    evidence: [{ account_or_source: \"sssjeffpu (Validated Discovery)\", quote: \"share offering upsized to $20B (from $15B), priced at $95... Institutional demand reportedly >$100B. CEO + family member subscribed ~$12M\" }],\n  },\n  SMTC: {\n    tier: \"High-Single\",\n    key_catalyst: \"F2Q revenue $341.9M (+33% YoY) beat, EPS beat, record gross margin 54.5%\",\n    key_thesis: \"Maintain Buy, target price $162 (22x FY28E EPS)\",\n    evidence: [{ account_or_source: \"sssjeffpu (Validated Discovery)\", quote: \"F2Q revenue $341.9mn (+33% YoY, +17% QoQ), beat consensus... Non-GAAP gross margin record at 54.5%\" }],\n  },\n  UMC: {\n    tier: \"High-Single\",\n    key_catalyst: \"PIC/TFLN acceleration, target price lifted to NT$160\",\n    key_thesis: \"High-margin PIC business acceleration driven by Hyperlight collaboration\",\n    evidence: [{ account_or_source: \"sssjeffpu (Validated Discovery)\", quote: \"Buy | TP lifted to NT$160 - PIC/TFLN upside unlocks a higher-margin cycle\" }],\n  },\n  MTK: {\n    tier: \"High-Single\",\n    key_catalyst: \"Nvidia $3.5B investment in MediaTek via $3.9B convertible bond (Alphabet co-participating)\",\n    key_thesis: \"Nvidia's first Taiwan-listed investment; expanding TPU collaboration\",\n    evidence: [{ account_or_source: \"sssjeffpu (Validated Discovery)\", quote: \"Nvidia to invest $3.5bn in MediaTek via its $3.9bn CB. Alphabet also participating\" }],\n  },\n  SMCI: {\n    tier: \"High-Single\",\n    key_catalyst: \"Target price raised to $54; F4Q26 gross-margin beat (17.6%); FY27 guide $65-72B\",\n    key_thesis: \"Structural 10%+ GM target with a $60B backlog and no further dilution\",\n    evidence: [{ account_or_source: \"sssjeffpu (Validated Discovery)\", quote: \"Raised TP to $54. Strong F4Q26 GM beat (17.6%) + higher FY27 guide ($65-72bn)... $60bn backlog\" }],\n  },\n  ORCL: {\n    tier: \"Medium\",\n    key_catalyst: \"OpenAI GPT-6 'Astra' release\",\n    key_thesis: \"ORCL well positioned to benefit from the Astra breakthrough and rising odds of hosting the best AI model\",\n    evidence: [{ account_or_source: \"michaelsikand (Core Trader)\", quote: \"$ORCL looks well positioned for a\" }],\n  },\n  SONY: {\n    tier: \"Medium\",\n    key_catalyst: null,\n    key_thesis: \"Value driver is monetizing the installed PS5 base via add-on content, not console unit growth\",\n    evidence: [{ account_or_source: \"michaelsikand (Core Trader)\", quote: \"the stock's clear path to $30+\" }],\n  },\n  ALMU: {\n    tier: \"Medium\",\n    key_catalyst: null,\n    key_thesis: \"Bearish: multiple compression hits hardest where cash flows are furthest out (quantum/photonic moonshots)\",\n    evidence: [{ account_or_source: \"KawzInvests (Core Trader)\", quote: \"quantum stocks and early-stage photonic moonshots such as $ALMU, $POET, $LWLG gave back their entire runs\" }],\n  },\n  LWLG: {\n    tier: \"Medium\",\n    key_catalyst: null,\n    key_thesis: \"Bearish: same multiple-compression thesis as $ALMU/$POET (same post/event)\",\n    evidence: [{ account_or_source: \"KawzInvests (Core Trader)\", quote: \"quantum stocks and early-stage photonic moonshots such as $ALMU, $POET, $LWLG gave back their entire runs\" }],\n  },\n  POET: {\n    tier: \"Medium\",\n    key_catalyst: null,\n    key_thesis: \"Bearish: same multiple-compression thesis as $ALMU/$LWLG (same post/event)\",\n    evidence: [{ account_or_source: \"KawzInvests (Core Trader)\", quote: \"quantum stocks and early-stage photonic moonshots such as $ALMU, $POET, $LWLG gave back their entire runs\" }],\n  },\n  CP: {\n    tier: \"Medium\",\n    key_catalyst: \"TGE (token generation event)\",\n    key_thesis: \"Unified AI infrastructure with real usage traction, not a single-model AI app\",\n    evidence: [{ account_or_source: \"octopusycc (Core Trader)\", quote: \"already has real usage; not a single AI application, but unified AI infrastructure\" }],\n  },\n  SNDK: {\n    tier: \"Medium\",\n    key_catalyst: null,\n    key_thesis: \"Structural memory supply bottleneck intact (same post/thesis as MU)\",\n    evidence: [{ account_or_source: \"aleabitoreddit (Core Trader)\", quote: \"Good to have a reminder that the memory bottleneck hasn't changed!\" }],\n  },\n  EWY: {\n    tier: \"Medium\",\n    key_catalyst: null,\n    key_thesis: \"Korea-listed memory/semiconductor exposure positioned for a constructive 2027 tight-supply view\",\n    evidence: [{ account_or_source: \"pequityresearch (Validated Discovery)\", quote: \"Constructive 2027 view on tight supply + Chinese GPU demand\" }],\n  },\n  AMD: {\n    tier: \"Low\",\n    key_catalyst: \"Added to a third-party (GF Securities) preferred list — relayed via reply, not the candidate's own analysis\",\n    key_thesis: null,\n    evidence: [{ account_or_source: \"sssjeffpu (Validated Discovery)\", quote: \"AMD, Lumentum, others added to GF Securities preferred list, as AI rebound to continue\" }],\n  },\n  HOOD: {\n    tier: \"Low\",\n    key_catalyst: null,\n    key_thesis: \"Korean retail sentiment favors HOOD's tokenization approach over a rival CEO's stance (sentiment-based, no concrete catalyst)\",\n    evidence: [{ account_or_source: \"aleabitoreddit (Core Trader)\", quote: \"Korean retail is firmly siding with $HOOD over the $AMC CEO\" }],\n  },\n  COIN: {\n    tier: \"Low\",\n    key_catalyst: null,\n    key_thesis: \"Market repricing COIN beyond simple crypto-price beta, as a growing crypto user gateway\",\n    evidence: [{ account_or_source: \"octopusycc (Core Trader)\", quote: \"Coinbase is becoming an increasingly large crypto user gateway\" }],\n  },\n};\n\n// Best-effort company names for well-known tickers only — left null where\n// not reliably known rather than guessed, per the spec (\"company name if\n// reliably available\").\nconst COMPANY_NAMES: Record = {\n  NVDA: \"NVIDIA Corporation\", BE: \"Bloom Energy Corporation\", HOOD: \"Robinhood Markets, Inc.\",\n  AVGO: \"Broadcom Inc.\", AAOI: \"Applied Optoelectronics, Inc.\", AAPL: \"Apple Inc.\",\n  MU: \"Micron Technology, Inc.\", GOOGL: \"Alphabet Inc.\", LITE: \"Lumentum Holdings Inc.\",\n  CRDO: \"Credo Technology Group Holding Ltd\", MSFT: \"Microsoft Corporation\", SNDK: \"SanDisk Corporation\",\n  AMC: \"AMC Entertainment Holdings, Inc.\", ORCL: \"Oracle Corporation\", NBIS: \"Nebius Group N.V.\",\n  MSTR: \"Strategy Inc. (MicroStrategy)\", NOK: \"Nokia Corporation\", META: \"Meta Platforms, Inc.\",\n  ASML: \"ASML Holding N.V.\", DELL: \"Dell Technologies Inc.\", CRM: \"Salesforce, Inc.\",\n  SONY: \"Sony Group Corporation\", TTWO: \"Take-Two Interactive Software, Inc.\", VST: \"Vistra Corp.\",\n  AMZN: \"Amazon.com, Inc.\", COIN: \"Coinbase Global, Inc.\", CRCL: \"Circle Internet Group, Inc.\",\n  AI: \"C3.ai, Inc.\", HPE: \"Hewlett Packard Enterprise Company\", SNOW: \"Snowflake Inc.\",\n  COHR: \"Coherent Corp.\", MRVL: \"Marvell Technology, Inc.\", GPRO: \"GoPro, Inc.\",\n  TTD: \"The Trade Desk, Inc.\", AMD: \"Advanced Micro Devices, Inc.\", INTC: \"Intel Corporation\",\n  SMTC: \"Semtech Corporation\", UMC: \"United Microelectronics Corporation\", SMCI: \"Super Micro Computer, Inc.\",\n  EWY: \"iShares MSCI South Korea ETF\", MTK: \"MediaTek Inc.\",\n};\n\n// ---------------------------------------------------------------------------\n// Component computations (all mechanical/deterministic given DB state)\n// ---------------------------------------------------------------------------\n\ninterface ConsensusRow {\n  ticker: string;\n  unique_accounts_mentioning: number;\n  bullish_accounts: number;\n  bearish_accounts: number;\n  unclear_accounts: number;\n  mention_count: number;\n  disagreement_level: string;\n  window_end: string | null;\n}\n\nfunction componentA(row: ConsensusRow | null): { score: number; consensus_state: string } {\n  if (!row) return { score: 0, consensus_state: \"No Core Trader Coverage\" };\n  const accounts = row.unique_accounts_mentioning;\n  const directional = row.bullish_accounts + row.bearish_accounts;\n  const breadthPts = (Math.min(accounts, 5) / 5) * 10;\n  const strengthPts = directional > 0 ? (Math.max(row.bullish_accounts, row.bearish_accounts) / directional) * 10 : 0;\n  const qualityPts = directional >= 1 ? 5 : accounts >= 1 ? 2 : 0;\n  const score = Math.round((breadthPts + strengthPts + qualityPts) * 10) / 10;\n\n  let consensus_state: string;\n  if (directional === 0) consensus_state = accounts > 0 ? \"Mention-Only / Unclear\" : \"No Core Trader Coverage\";\n  else if (row.bullish_accounts > 0 && row.bearish_accounts > 0) consensus_state = \"Disagreement\";\n  else if (row.bullish_accounts > row.bearish_accounts) consensus_state = \"Bullish\";\n  else consensus_state = \"Bearish\";\n\n  return { score: Math.min(score, 25), consensus_state };\n}\n\nfunction componentB(\n  row: ConsensusRow | null,\n  fallbackWindowEnd: string | null,\n  fallbackMentionCount: number,\n  nowMs: number,\n): { score: number; note: string } {\n  const windowEnd = row?.window_end ?? fallbackWindowEnd;\n  const mentionCount = row?.mention_count ?? fallbackMentionCount;\n  const note =\n    \"V1 temporary proxy (mention intensity + recency of last observed activity) — only one ticker_consensus snapshot exists, so true 3h/24h/7d attention acceleration cannot be computed yet. Schema (attention_acceleration, stance_change_1d/7d/28d on ticker_consensus) is already preserved for when >=2 snapshots accumulate to diff against.\";\n  if (!windowEnd) return { score: 0, note };\n\n  const hoursSince = (nowMs - new Date(windowEnd).getTime()) / 3_600_000;\n  let recencyPts = 0;\n  if (hoursSince <= 24) recencyPts = 10;\n  else if (hoursSince <= 48) recencyPts = 7;\n  else if (hoursSince <= 72) recencyPts = 4;\n  else if (hoursSince <= 168) recencyPts = 2;\n  const intensityPts = (Math.min(mentionCount, 8) / 8) * 10;\n  return { score: Math.min(Math.round((recencyPts + intensityPts) * 10) / 10, 20), note };\n}\n\nfunction componentC(ticker: string): { score: number; tier: CatalystTier; key_catalyst: string | null; key_thesis: string | null; evidence: CatalystEntry[\"evidence\"] } {\n  const entry = CATALYST_TABLE[ticker];\n  if (!entry) return { score: 0, tier: \"None\", key_catalyst: null, key_thesis: null, evidence: [] };\n  return { score: TIER_POINTS[entry.tier], tier: entry.tier, key_catalyst: entry.key_catalyst, key_thesis: entry.key_thesis, evidence: entry.evidence };\n}\n\nfunction componentD(row: ConsensusRow | null, hasValidatedDiscovery: boolean): { score: number; layers: string[] } {\n  const layers: string[] = [];\n  let layerCount = 0;\n  if (row) {\n    const directional = row.bullish_accounts + row.bearish_accounts;\n    if (directional >= 1) {\n      layerCount += 1;\n      layers.push(\"core_trader\");\n    }\n    if (directional >= 2) layerCount += 1; // second independent Core Trader account corroborating\n  }\n  if (hasValidatedDiscovery) {\n    layerCount += 1;\n    layers.push(\"validated_discovery\");\n  }\n  const score = Math.min((layerCount / 3) * 15, 15);\n  return { score: Math.round(score * 10) / 10, layers };\n}\n\nfunction componentF(ticker: string, row: ConsensusRow | null, isNewDiscoveryOnly: boolean): number {\n  if (isNewDiscoveryOnly) return 10;\n  if (MEGA_CAP_SET.has(ticker)) return 2;\n  if (row && row.unique_accounts_mentioning <= 1) return 7;\n  return 5;\n}\n\n// ---------------------------------------------------------------------------\n// Run orchestration\n// ---------------------------------------------------------------------------\n\nexport interface MarketDiscoveryRunSummary {\n  runId: number;\n  generatedAt: string;\n  tickerUniverseSize: number;\n  coreTickerCount: number;\n  newDiscoveryCount: number;\n  finalistCount: number;\n  finalistTickers: string[];\n  coreTradersCount: number;\n  promotedDiscoveredAccounts: number;\n  excludedNonTickers: string[];\n  identityExclusions: { ticker: string; classification: string; resolved_entity: string | null; intended_entity: string | null }[];\n}\n\n// Step A: mechanical + semantic-lookup pass. Computes A/B/C/D/F for the\n// full ticker universe, persists one snapshot row per ticker with\n// fundamental_market_score = NULL, and marks the top N (by the 90-point\n// provisional subtotal) as finalists for the bounded Component E lookup.\nexport async function runMarketDiscovery(opts: { finalistCount?: number } = {}): Promise {\n  await ensureSchema();\n  const finalistTarget = Math.min(Math.max(opts.finalistCount ?? 20, 1), 30);\n  const generatedAt = new Date().toISOString();\n  const nowMs = Date.now();\n\n  const consensusRes = await sqlite.execute(`\n    SELECT tc.* FROM ticker_consensus tc\n    INNER JOIN (\n      SELECT ticker, MAX(computed_at) AS max_computed_at FROM ticker_consensus GROUP BY ticker\n    ) latest ON tc.ticker = latest.ticker AND tc.computed_at = latest.max_computed_at\n  `);\n\n  // Ticker/Entity Identity exclusion layer (production-data cleanup): a\n  // ticker whose LATEST ticker_identity_reviews row is 'Confirmed False\n  // Positive' or 'Ambiguous/Exclude' is filtered OUT of the universe here,\n  // regardless of what its (possibly stale, pre-cleanup) ticker_consensus\n  // row still says — this guarantees it never enters Discovery Score\n  // calculations even if runConsensusAggregation hasn't re-run since the\n  // review was recorded. See lib/tickerIdentity.ts.\n  const identityExcludedTickers = await getExcludedTickers();\n  const identityReviews = await getLatestTickerIdentityReviews();\n  const consensusRows = (consensusRes.rows as unknown as ConsensusRow[]).filter(\n    (r) => !identityExcludedTickers.has(r.ticker),\n  );\n  const consensusByTicker = new Map(consensusRows.map((r) => [r.ticker, r]));\n\n  // Validated Discovery Signal: only Strong Candidates (eligible_for_promotion_review=1\n  // AND classification='Strong Candidate') count as this layer, per Phase 3.2.\n  const strongCandidatesRes = await sqlite.execute(\n    `SELECT candidate_handle FROM candidate_validation_results WHERE classification = 'Strong Candidate' AND eligible_for_promotion_review = 1`,\n  );\n  const strongHandles = new Set((strongCandidatesRes.rows as any[]).map((r) => String(r.candidate_handle).toLowerCase()));\n\n  const newestCandidatePostRes = await sqlite.execute(\n    `SELECT MAX(posted_at) as max_posted_at FROM candidate_validation_posts`,\n  );\n  const candidateFallbackWindowEnd = (newestCandidatePostRes.rows[0] as any)?.max_posted_at ?? null;\n\n  // Universe = every ticker with Core Trader coverage UNION every ticker in\n  // VALIDATED_DISCOVERY_TICKERS whose citing candidate is still a confirmed\n  // Strong Candidate right now (guards against a stale hardcoded list if\n  // Phase 3.2 results are ever re-run with different classifications).\n  const universe = new Set(consensusRows.map((r) => r.ticker));\n  const newDiscoveryTickers: string[] = [];\n  for (const [ticker, handles] of Object.entries(VALIDATED_DISCOVERY_TICKERS)) {\n    if (identityExcludedTickers.has(ticker)) continue; // ticker-identity exclusion — see lib/tickerIdentity.ts\n    const stillValidated = handles.some((h) => strongHandles.has(h.toLowerCase()));\n    if (!stillValidated) continue;\n    if (!universe.has(ticker)) {\n      universe.add(ticker);\n      newDiscoveryTickers.push(ticker);\n    }\n  }\n\n  const excludedNonTickers = [\"DRAM\", \"DISK\"]; // mechanical cashtag false-positives — see module header note\n\n  type Row = {\n    ticker: string;\n    A: number; B: number; C: number; D: number; F: number;\n    consensus_state: string; attention_note: string; catalyst_tier: CatalystTier;\n    key_catalyst: string | null; key_thesis: string | null; evidence: CatalystEntry[\"evidence\"];\n    source_layers: string[]; bullish: number; bearish: number; unclear: number;\n    is_new_discovery: boolean;\n  };\n\n  const rows: Row[] = [];\n  for (const ticker of universe) {\n    const consensusRow = consensusByTicker.get(ticker) ?? null;\n    const isNewDiscoveryOnly = newDiscoveryTickers.includes(ticker);\n    const hasValidatedDiscovery = Object.keys(VALIDATED_DISCOVERY_TICKERS).includes(ticker) &&\n      VALIDATED_DISCOVERY_TICKERS[ticker].some((h) => strongHandles.has(h.toLowerCase()));\n\n    const a = componentA(consensusRow);\n    const b = componentB(consensusRow, candidateFallbackWindowEnd, 1, nowMs);\n    const c = componentC(ticker);\n    const d = componentD(consensusRow, hasValidatedDiscovery);\n    const f = componentF(ticker, consensusRow, isNewDiscoveryOnly);\n\n    const source_layers = [...d.layers];\n    rows.push({\n      ticker,\n      A: a.score, B: b.score, C: c.score, D: d.score, F: f,\n      consensus_state: a.consensus_state, attention_note: b.note, catalyst_tier: c.tier,\n      key_catalyst: c.key_catalyst, key_thesis: c.key_thesis, evidence: c.evidence,\n      source_layers, bullish: consensusRow?.bullish_accounts ?? 0, bearish: consensusRow?.bearish_accounts ?? 0,\n      unclear: consensusRow?.unclear_accounts ?? 0, is_new_discovery: isNewDiscoveryOnly,\n    });\n  }\n\n  rows.sort((x, y) => (y.A + y.B + y.C + y.D + y.F) - (x.A + x.B + x.C + x.D + x.F));\n  const finalistTickers = new Set(rows.slice(0, finalistTarget).map((r) => r.ticker));\n\n  const coreTradersCountRes = await sqlite.execute(`SELECT COUNT(*) as n FROM accounts`);\n  const promotedRes = await sqlite.execute(`SELECT COUNT(*) as n FROM discovered_accounts WHERE promotion_status != 'Not Promoted'`);\n  const coreTradersCount = (coreTradersCountRes.rows[0] as any).n as number;\n  const promotedDiscoveredAccounts = (promotedRes.rows[0] as any).n as number;\n\n  const identityExclusions = [...identityExcludedTickers].map((ticker) => {\n    const review = identityReviews.get(ticker);\n    return {\n      ticker,\n      classification: review?.classification ?? \"Unknown\",\n      resolved_entity: review?.resolved_entity ?? null,\n      intended_entity: review?.intended_entity ?? null,\n    };\n  });\n  const identityExclusionNote = identityExclusions.length > 0\n    ? ` Ticker-identity exclusions applied (production-data cleanup): ${\n      identityExclusions.map((e) => `${e.ticker} (${e.classification})`).join(\", \")\n    } — excluded from this run's universe entirely; see /ticker-identity/reviews.`\n    : \"\";\n\n  const runInsert = await sqlite.execute({\n    sql: `INSERT INTO market_discovery_runs (generated_at, ticker_universe_size, core_ticker_count, new_discovery_count, finalist_count, fundamentals_applied_count, core_traders_count, promoted_discovered_accounts, notes) VALUES (?,?,?,?,?,?,?,?,?)`,\n    args: [\n      generatedAt, universe.size, consensusRows.length, newDiscoveryTickers.length,\n      finalistTickers.size, 0, coreTradersCount, promotedDiscoveredAccounts,\n      `Excluded non-ticker mechanical cashtag false-positives: ${excludedNonTickers.join(\", \")}. Component E (fundamental_market_score) is bounded to the ${finalistTickers.size} finalists via a separate apply-fundamentals call.${identityExclusionNote}`,\n    ],\n  });\n  const runId = Number(runInsert.lastInsertRowid);\n\n  const statements = rows.map((r) => {\n    const isFinalist = finalistTickers.has(r.ticker);\n    const discovery_score = r.A + r.B + r.C + r.D + r.F;\n    const watchlist_status = WATCHLIST_15.has(r.ticker) ? \"Existing Watchlist\" : \"New Discovery\";\n    return {\n      sql: `INSERT INTO market_discovery_snapshots (\n        run_id, ticker, company_name, discovery_score, max_possible_score,\n        trader_consensus_score, attention_acceleration_score, catalyst_score, cross_source_score,\n        fundamental_market_score, novelty_score,\n        bullish_unique_accounts, bearish_unique_accounts, unclear_unique_accounts,\n        consensus_state, catalyst_tier, key_thesis, key_catalyst, evidence, source_layers,\n        attention_note, fundamental_note, watchlist_status, is_new_discovery_ticker, is_finalist, generated_at\n      ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,\n      args: [\n        runId, r.ticker, COMPANY_NAMES[r.ticker] ?? null, discovery_score, 90,\n        r.A, r.B, r.C, r.D,\n        null, r.F,\n        r.bullish, r.bearish, r.unclear,\n        r.consensus_state, r.catalyst_tier, r.key_thesis, r.key_catalyst, JSON.stringify(r.evidence), JSON.stringify(r.source_layers),\n        r.attention_note, null, watchlist_status, r.is_new_discovery ? 1 : 0, isFinalist ? 1 : 0, generatedAt,\n      ],\n    };\n  });\n  // sqlite.batch has practical statement-count limits observed elsewhere in\n  // this project's own collector code; chunk defensively.\n  for (let i = 0; i < statements.length; i += 20) {\n    await sqlite.batch(statements.slice(i, i + 20));\n  }\n\n  return {\n    runId,\n    generatedAt,\n    tickerUniverseSize: universe.size,\n    coreTickerCount: consensusRows.length,\n    newDiscoveryCount: newDiscoveryTickers.length,\n    finalistCount: finalistTickers.size,\n    finalistTickers: [...finalistTickers],\n    coreTradersCount,\n    promotedDiscoveredAccounts,\n    excludedNonTickers,\n    identityExclusions,\n  };\n}\n\n// Step B: applies Component E (Fundamental / Market Quality Gate) for a\n// bounded set of finalist tickers in an existing run, and recomputes\n// discovery_score / max_possible_score for just those rows. Never touches\n// rows outside the provided list.\nexport interface FundamentalUpdate {\n  ticker: string;\n  fundamental_market_score: number | null; // 0-10, or null if genuinely unavailable\n  fundamental_note: string;\n}\n\nexport async function applyFundamentals(runId: number, updates: FundamentalUpdate[]): Promise<{ applied: number }> {\n  await ensureSchema();\n  let applied = 0;\n  for (const u of updates) {\n    const existing = await sqlite.execute({\n      sql: `SELECT trader_consensus_score, attention_acceleration_score, catalyst_score, cross_source_score, novelty_score FROM market_discovery_snapshots WHERE run_id = ? AND ticker = ?`,\n      args: [runId, u.ticker],\n    });\n    if (existing.rows.length === 0) continue;\n    const row = existing.rows[0] as any;\n    const baseSum = row.trader_consensus_score + row.attention_acceleration_score + row.catalyst_score + row.cross_source_score + row.novelty_score;\n    const discovery_score = baseSum + (u.fundamental_market_score ?? 0);\n    const max_possible_score = u.fundamental_market_score === null ? 90 : 100;\n    await sqlite.execute({\n      sql: `UPDATE market_discovery_snapshots SET fundamental_market_score = ?, fundamental_note = ?, discovery_score = ?, max_possible_score = ? WHERE run_id = ? AND ticker = ?`,\n      args: [u.fundamental_market_score, u.fundamental_note, discovery_score, max_possible_score, runId, u.ticker],\n    });\n    applied += 1;\n  }\n  await sqlite.execute({\n    sql: `UPDATE market_discovery_runs SET fundamentals_applied_count = fundamentals_applied_count + ? WHERE id = ?`,\n    args: [applied, runId],\n  });\n  return { applied };\n}\n",[],[181,281],"a3b25694-3959-4f76-9242-54e56cbf9f8e",["D",1788960923026],8,[],[181,33],"6ce6c942-04fb-45fd-8f0a-b968401c7cba","discoveryV2.ts","8e3de3bc-ac26-11f1-ab14-1607ee4eb77e",112,["D",1788958592929],11,"// lib/discoveryV2.ts — ADDITIVE \"Market Discovery & Consensus V2\" engine.\n//\n// Builds on the validated V1 data (existing analyzed posts, consensus,\n// Discovery candidates from lib/discovery.ts). It NEVER re-analyzes posts,\n// never runs the collector, never calls Anthropic, never touches the 3-hour\n// cron, and never modifies V1 tables.\n//\n// PERFORMANCE ARCHITECTURE (precomputed + cached):\n//  - computeDiscovery() runs a bounded full computation ONLY when the stored\n//    V2 results are stale (empty or older than FRESH_MS). Otherwise the\n//    read-only endpoints read the precomputed V2 tables directly — no\n//    whole-dataset rescan per request.\n//\n// Honesty rules enforced throughout:\n//  - Only dimensions with real evidence are scored; everything else stays\n//    \"insufficient data\" (never fabricated, never zeroed as if negative).\n//  - Semantic mappings require High confidence to influence scoring.\n//  - Not Mentioned is never Neutral.\n//  - Discovery tiers are research priorities, NOT buy recommendations.\n\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { TRACKED_ACCOUNTS } from \"../config.ts\";\nimport { computeTickerConsensus } from \"./consensus.ts\";\nimport { getExcludedTickers } from \"./tickerIdentity.ts\";\nimport { extractCandidates } from \"./discovery.ts\";\nimport { scoreMarketDimensions, ensureMarketDataSchema } from \"./marketData.ts\";\nimport type { MarketData } from \"./marketData.ts\";\n\nexport const WATCHLIST = [\n  \"QQQ\", \"TSLA\", \"PYPL\", \"META\", \"CAKE\", \"AMZN\", \"NVDA\", \"SE\",\n  \"GOOGL\", \"AVGO\", \"XYZ\", \"SOXX\", \"AMD\", \"TSM\", \"ABSI\",\n];\n\n// Reliable company names only; never guessed. Unknown -> null.\nconst KNOWN_COMPANIES: Record = {\n  NVDA: \"NVIDIA Corporation\", MU: \"Micron Technology\", INTC: \"Intel Corporation\",\n  AMD: \"Advanced Micro Devices\", BE: \"Bloom Energy\", HOOD: \"Robinhood Markets\",\n  AVGO: \"Broadcom Inc.\", AAPL: \"Apple Inc.\", AMZN: \"Amazon.com\", META: \"Meta Platforms\",\n  GOOGL: \"Alphabet Inc.\", MSFT: \"Microsoft Corporation\", TSLA: \"Tesla Inc.\",\n  QCOM: \"Qualcomm Inc.\", ORCL: \"Oracle Corporation\", CRDO: \"Credo Technology\",\n  LITE: \"Lumentum Holdings\", COHR: \"Coherent Corp.\", MRVL: \"Marvell Technology\",\n  AAOI: \"Applied Optoelectronics\", DELL: \"Dell Technologies\", SMCI: \"Super Micro\",\n  SE: \"Sea Limited\", PYPL: \"PayPal Holdings\", TSM: \"Taiwan Semiconductor\",\n  SOXX: \"iShares Semiconductor ETF\", QQQ: \"Invesco QQQ Trust\", ABSI: \"Absci Corporation\",\n};\n\n// How long a computed V2 snapshot is considered fresh before recompute.\nconst FRESH_MS = 15 * 60 * 1000;\n\n// Dimension weights sum to 100 (the 0-100 score scale).\nconst DIM = {\n  traderConsensus: 15,\n  independentConsensus: 15,\n  attentionAcceleration: 15,\n  newsCatalyst: 10,\n  capitalFlow: 10,       // scored from market_data when present, else insufficient\n  fundamentalQuality: 10, // scored from market_data when present, else insufficient\n  valuation: 5,          // scored from market_data when present, else insufficient\n  priceMomentum: 10,     // scored from market_data when present, else insufficient\n  themeFit: 10,\n} as const;\nconst TOTAL_MAX = Object.values(DIM).reduce((a, b) => a + b, 0); // 100\n\nconst safeParseArray = (json: string | null | undefined): string[] => {\n  if (!json) return [];\n  try { const v = JSON.parse(json); return Array.isArray(v) ? v.filter((x) => typeof x === \"string\") : []; }\n  catch { return []; }\n};\nconst safeParseSemantic = (json: string | null | undefined): any[] => {\n  if (!json) return [];\n  try { const v = JSON.parse(json); return Array.isArray(v) ? v : []; }\n  catch { return []; }\n};\nfunction tickersForPost(p: any): string[] {\n  const s = new Set();\n  safeParseArray(p.mentioned_tickers).forEach((t) => s.add(t.toUpperCase()));\n  safeParseArray(p.stance_tickers).forEach((t) => s.add(t.toUpperCase()));\n  safeParseSemantic(p.semantic_tickers).forEach((x) => x && x.ticker && s.add(String(x.ticker).toUpperCase()));\n  return [...s];\n}\nfunction postMentions(p: any, ticker: string): boolean {\n  const t = ticker.toUpperCase();\n  return safeParseArray(p.mentioned_tickers).some((x) => x.toUpperCase() === t) ||\n    safeParseArray(p.stance_tickers).some((x) => x.toUpperCase() === t) ||\n    safeParseSemantic(p.semantic_tickers).some((x) => x && x.ticker && String(x.ticker).toUpperCase() === t);\n}\n\nexport async function ensureV2Schema(): Promise {\n  await sqlite.batch([\n    { sql: `CREATE TABLE IF NOT EXISTS discovery_candidates (\n        ticker TEXT PRIMARY KEY, company_name TEXT,\n        score_normalized INTEGER, score_available REAL, available_max INTEGER, coverage REAL,\n        confidence TEXT, tier TEXT, why_discovered TEXT,\n        direction TEXT, disagreement_level TEXT, independent_consensus INTEGER,\n        bullish INTEGER, bearish INTEGER, unclear INTEGER,\n        attention_trend TEXT, catalysts TEXT, theme TEXT, evidence TEXT,\n        sources TEXT, first_detected TEXT, last_updated TEXT, is_watchlist INTEGER DEFAULT 0\n      )` },\n    { sql: `CREATE TABLE IF NOT EXISTS discovery_signals (\n        id INTEGER PRIMARY KEY AUTOINCREMENT, ticker TEXT NOT NULL, dimension TEXT NOT NULL,\n        source TEXT NOT NULL, evidence TEXT, confidence TEXT, signal_time TEXT NOT NULL, payload TEXT\n      )` },\n    { sql: `CREATE INDEX IF NOT EXISTS idx_dsig_ticker ON discovery_signals(ticker, signal_time)` },\n    { sql: `CREATE TABLE IF NOT EXISTS discovery_runs (\n        id INTEGER PRIMARY KEY AUTOINCREMENT, computed_at TEXT NOT NULL,\n        candidates INTEGER NOT NULL, themes INTEGER NOT NULL, avg_coverage REAL\n      )` },\n    { sql: `CREATE TABLE IF NOT EXISTS emerging_accounts (\n        handle TEXT PRIMARY KEY, category TEXT NOT NULL, times_referenced INTEGER DEFAULT 0,\n        referencing_accounts TEXT NOT NULL DEFAULT '[]', tickers TEXT NOT NULL DEFAULT '[]',\n        evidence TEXT NOT NULL DEFAULT '[]', promo_flagged INTEGER DEFAULT 0,\n        confidence TEXT, repost_ratio REAL, last_evaluated TEXT,\n        promotion_status TEXT NOT NULL DEFAULT 'Not Promoted', notes TEXT\n      )` },\n    { sql: `CREATE TABLE IF NOT EXISTS themes (\n        id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, label TEXT,\n        trend TEXT, related_tickers TEXT NOT NULL DEFAULT '[]', related_accounts TEXT NOT NULL DEFAULT '[]',\n        evidence TEXT NOT NULL DEFAULT '[]', attention INTEGER DEFAULT 0,\n        first_detected TEXT, last_updated TEXT\n      )` },\n    { sql: `CREATE TABLE IF NOT EXISTS watchlist (\n        ticker TEXT PRIMARY KEY, name TEXT, source TEXT NOT NULL DEFAULT 'user', added_at TEXT NOT NULL\n      )` },\n  ]);\n  // Seed the user's existing watchlist (idempotent) in one batched statement.\n  const now = new Date().toISOString();\n  const seed = WATCHLIST.map((t) => ({\n    sql: `INSERT OR IGNORE INTO watchlist (ticker, name, source, added_at) VALUES (?, ?, 'user', ?)`,\n    args: [t, KNOWN_COMPANIES[t] ?? null, now],\n  }));\n  await sqlite.batch(seed);\n  await ensureMarketDataSchema();\n}\n\nasync function loadAnalyzedPosts(): Promise {\n  const res = await sqlite.execute(\n    `SELECT post_id, username, post_type, posted_at, text, mentioned_tickers, stance_tickers,\n            explicit_stance, semantic_tickers, evidence, thesis, catalyst, original_url\n     FROM posts WHERE analyzed_at IS NOT NULL`,\n  );\n  return res.rows as unknown as any[];\n}\n\nfunction attention(posts: any[], t: string) {\n  const rel = posts.filter((p) => postMentions(p, t));\n  if (rel.length < 2) return { recent: rel.length, prior: 0, pct: null, rising: false };\n  rel.sort((a, b) => (a.posted_at < b.posted_at ? -1 : 1));\n  const first = new Date(rel[0].posted_at).getTime();\n  const last = new Date(rel[rel.length - 1].posted_at).getTime();\n  const span = Math.max(last - first, 1);\n  const mid = first + span * 0.25;\n  const recent = rel.filter((p) => new Date(p.posted_at).getTime() >= mid).length;\n  const prior = rel.length - recent;\n  const pct = prior > 0 ? Math.round(((recent - prior) / prior) * 100) : (recent > 0 ? 100 : 0);\n  return { recent, prior, pct, rising: recent > prior };\n}\n\nfunction stanceChanges(posts: any[], t: string): number {\n  const byAcct = new Map();\n  for (const p of posts) {\n    if (!postMentions(p, t)) continue;\n    const st = p.explicit_stance;\n    if (st !== \"Bullish\" && st !== \"Bearish\") continue;\n    const a = byAcct.get(p.username) ?? {};\n    if (!a.first) a.first = st;\n    a.last = st;\n    byAcct.set(p.username, a);\n  }\n  let flips = 0;\n  for (const a of byAcct.values()) if (a.first && a.last && a.first !== a.last) flips++;\n  return flips;\n}\n\nfunction buildThemes(posts: any[]): any[] {\n  const postTickers = posts.map((p) => ({ p, ticks: tickersForPost(p) })).filter((x) => x.ticks.length >= 1);\n  const co = new Map>();\n  for (const { ticks } of postTickers) {\n    for (const a of ticks) for (const b of ticks) {\n      if (a === b) continue;\n      const m = co.get(a) ?? new Map();\n      m.set(b, (m.get(b) ?? 0) + 1);\n      co.set(a, m);\n    }\n  }\n  const parent = new Map();\n  const root = (x: string) => { while (parent.get(x) !== x) { parent.set(x, parent.get(parent.get(x)!)!); x = parent.get(x)!; } return x; };\n  const allT = new Set();\n  postTickers.forEach((x) => x.ticks.forEach((t) => allT.add(t)));\n  allT.forEach((t) => parent.set(t, t));\n  for (const [a, m] of co) for (const [b, n] of m) if (n >= 2) { const ra = root(a), rb = root(b); if (ra !== rb) parent.set(rb, ra); }\n  const clusters = new Map>();\n  for (const t of allT) { const r = root(t); if (!clusters.has(r)) clusters.set(r, new Set()); clusters.get(r)!.add(t); }\n\n  const out: any[] = [];\n  for (const members of clusters.values()) {\n    if (members.size < 2) continue;\n    const ticks = [...members].sort();\n    const related: any[] = [];\n    postTickers.forEach(({ p, ticks: pt }) => {\n      const hit = pt.filter((x) => members.has(x));\n      if (hit.length >= 2) related.push({ account: p.username, tickers: hit, evidence: p.evidence ?? p.catalyst ?? p.thesis ?? p.text?.slice(0, 160), postedAt: p.posted_at, url: p.original_url });\n    });\n    const accounts = new Set(related.map((r) => r.account));\n    const names = ticks.map((t) => `${t} (${KNOWN_COMPANIES[t] ?? \"—\"})`).slice(0, 4);\n    out.push({\n      name: ticks.join(\" · \"), label: names.join(\" · \"), related_tickers: ticks, related_accounts: [...accounts],\n      evidence: related.slice(0, 5), attention: related.length,\n      first_detected: related.length ? related.map((r) => r.postedAt).sort()[0] : null,\n      last_updated: related.length ? related.map((r) => r.postedAt).sort().slice(-1)[0] : null,\n      trend: `attention ${related.length} posts across ${accounts.size} accounts`,\n    });\n  }\n  return out.sort((a, b) => b.attention - a.attention);\n}\n\n// Bounded full computation: builds candidates + signals + themes, persists\n// them to the V2 tables using batched writes so it completes quickly.\nexport async function computeDiscovery(\n  opts: { onlyTickers?: string[] } = {},\n): Promise<{ generatedAt: string; candidates: number; themes: number }> {\n  await ensureV2Schema();\n  const posts = await loadAnalyzedPosts();\n  const excluded = await getExcludedTickers();\n  const rows = posts.map((p) => ({\n    post_id: p.post_id, username: p.username, post_type: p.post_type, posted_at: p.posted_at,\n    text: p.text, mentioned_tickers: p.mentioned_tickers ?? \"[]\", stance_tickers: p.stance_tickers ?? \"[]\",\n    explicit_stance: p.explicit_stance, semantic_tickers: p.semantic_tickers ?? \"[]\",\n  }));\n  // Scoped mode: opts.onlyTickers restricts which tickers get a\n  // discovery_candidates/discovery_signals row written this call (e.g. a\n  // controlled single-ticker market-data test). Read-only derivations\n  // (consensus math, attention, themes) still use the full analyzed-post\n  // set — only WHICH rows get WRITTEN is scoped, so every other ticker's\n  // row is left completely untouched. Never re-analyzes posts, never calls\n  // Anthropic, never touches ticker_consensus.\n  const onlyTickers = opts.onlyTickers ? new Set(opts.onlyTickers.map((t) => t.toUpperCase())) : null;\n  const consensus = computeTickerConsensus(rows, excluded)\n    .filter((c) => !excluded.has(c.ticker))\n    .filter((c) => !onlyTickers || onlyTickers.has(c.ticker));\n  const themes = buildThemes(posts);\n  const watchRow = new Set(((await sqlite.execute(`SELECT ticker FROM watchlist`)).rows as any[]).map((r) => r.ticker.toUpperCase()));\n  const now = new Date().toISOString();\n  const threeDaysAgo = new Date(Date.now() - 3 * 86400000).toISOString();\n  // Persisted market/fundamental data (separate table). Tick without a row\n  // keeps the four market dimensions unavailable (NaN) — scores unchanged.\n  const marketRows = (await sqlite.execute(`SELECT * FROM market_data`)).rows as any[];\n  const marketByTicker = new Map(marketRows.map((r) => [r.ticker.toUpperCase(), r as unknown as MarketData]));\n\n  const candStmts: any[] = [];\n  const sigStmts: any[] = [];\n\n  for (const c of consensus) {\n    const t = c.ticker;\n    const rel = posts.filter((p) => postMentions(p, t));\n    const attn = attention(posts, t);\n    const flips = stanceChanges(posts, t);\n    const firstDetected = rel.length ? rel.map((p) => p.posted_at).sort()[0] : null;\n    const lastUpdated = rel.length ? rel.map((p) => p.posted_at).sort().slice(-1)[0] : now;\n    const isNew = firstDetected ? firstDetected >= threeDaysAgo : false;\n    const catalysts = [...new Set(rel.map((p) => p.catalyst).filter(Boolean))];\n    const theses = [...new Set(rel.map((p) => p.thesis).filter(Boolean))];\n    const theme = themes.find((th) => th.related_tickers.includes(t));\n\n    const scores: Record = {};\n    const strength = c.bullish_accounts + c.bearish_accounts > 0 ? Math.max(c.bullish_ratio ?? 0, c.bearish_ratio ?? 0) : 0;\n    scores.traderConsensus = Math.min(15, Math.round(strength * 7.5 + (c.bullish_accounts + c.bearish_accounts >= 2 ? 7.5 : 0)));\n    scores.independentConsensus = Math.min(15, (c.bullish_accounts + c.bearish_accounts) * 3);\n    if (attn.pct === null) scores.attentionAcceleration = NaN; else scores.attentionAcceleration = attn.rising ? Math.min(15, 6 + attn.pct / 10) : 2;\n    scores.newsCatalyst = Math.min(10, catalysts.length * 5);\n    scores.themeFit = theme ? Math.min(10, Math.ceil((theme.related_accounts.length + theme.related_tickers.length) / 2)) : 0;\n    // Market/fundamental dims score ONLY when a persisted market_data row exists\n    // for this ticker; otherwise NaN => dimension unavailable (never zero).\n    const mds = scoreMarketDimensions(marketByTicker.get(t) ?? null);\n    scores.fundamentalQuality = mds.fundamentalQuality;\n    scores.valuation = mds.valuation;\n    scores.priceMomentum = mds.priceMomentum;\n    scores.capitalFlow = mds.capitalFlow;\n    // No hardcoded \"unavailable\" list: NaN dims are excluded automatically, so\n    // existing scores are unchanged until market data is actually provided.\n    const unavailable: string[] = [];\n\n    const availableMax = Object.entries(DIM).filter(([k]) => !unavailable.includes(k) && !Number.isNaN(scores[k] as number)).reduce((a, [, w]) => a + w, 0);\n    const availableScore = Object.entries(scores).filter(([k, v]) => !unavailable.includes(k) && !Number.isNaN(v)).reduce((a, [, v]) => a + (v as number), 0);\n    const normalized = Math.round(availableMax > 0 ? (availableScore / availableMax) * 100 : 0);\n    const coverage = availableMax / TOTAL_MAX;\n    const confidence = Math.min(1, coverage * (0.5 + 0.1 * Math.min(c.unique_accounts_mentioning, 5)));\n\n    let tier: string;\n    if (normalized >= 70 && c.unique_accounts_mentioning >= 3 && coverage >= 0.6) tier = \"A\";\n    else if (normalized >= 40 && c.unique_accounts_mentioning >= 2) tier = \"B\";\n    else tier = \"C\";\n\n    const why: string[] = [];\n    if (isNew) why.push(\"newly emerging (first seen within 3 days)\");\n    if (attn.rising) why.push(`attention rising (${attn.pct}%)`);\n    if (flips > 0) why.push(`${flips} account stance change(s)`);\n    if (c.unique_accounts_mentioning >= 3) why.push(`${c.unique_accounts_mentioning} independent accounts`);\n    if (c.consensus_direction === \"Bullish\" || c.consensus_direction === \"Bearish\") why.push(`${c.consensus_direction} trader consensus`);\n    if (c.disagreement_level === \"High\") why.push(\"high disagreement\");\n    if (catalysts.length) why.push(\"news/catalyst present\");\n    if (theme) why.push(`theme: ${theme.name}`);\n\n    const rec = {\n      ticker: t, company_name: KNOWN_COMPANIES[t] ?? null,\n      score_normalized: normalized, score_available: Math.round(availableScore * 10) / 10,\n      available_max: availableMax, coverage: Math.round(coverage * 100) / 100,\n      confidence: confidence >= 0.66 ? \"High\" : confidence >= 0.4 ? \"Medium\" : \"Low\", tier,\n      why_discovered: why.length ? why.join(\"; \") : \"mentioned by tracked accounts\",\n      direction: c.consensus_direction, disagreement_level: c.disagreement_level,\n      independent_consensus: c.unique_accounts_mentioning, bullish: c.bullish_accounts,\n      bearish: c.bearish_accounts, unclear: c.unclear_accounts,\n      attention_trend: attn.pct === null ? null : `${attn.rising ? \"rising\" : \"falling\"} ${Math.abs(attn.pct)}%`,\n      catalysts: JSON.stringify(catalysts.slice(0, 4)),\n      theme: theme ? theme.name : null,\n      evidence: JSON.stringify([...new Set([...catalysts, ...theses])].slice(0, 3)),\n      sources: JSON.stringify([\n        { source: \"trader_consensus\", evidence: `${c.unique_accounts_mentioning} unique accounts`, confidence: \"high\" },\n        { source: \"attention_acceleration\", evidence: attn.pct === null ? \"insufficient\" : `${attn.pct}% recent vs prior`, confidence: attn.pct === null ? \"low\" : \"medium\" },\n      ]),\n      first_detected: firstDetected, last_updated: lastUpdated,\n      is_watchlist: watchRow.has(t) ? 1 : 0,\n    };\n\n    candStmts.push({\n      sql: `INSERT INTO discovery_candidates (\n        ticker, company_name, score_normalized, score_available, available_max, coverage, confidence, tier,\n        why_discovered, direction, disagreement_level, independent_consensus, bullish, bearish, unclear,\n        attention_trend, catalysts, theme, evidence, sources, first_detected, last_updated, is_watchlist)\n        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)\n        ON CONFLICT(ticker) DO UPDATE SET\n          score_normalized=excluded.score_normalized, score_available=excluded.score_available,\n          available_max=excluded.available_max, coverage=excluded.coverage, confidence=excluded.confidence,\n          tier=excluded.tier, why_discovered=excluded.why_discovered, direction=excluded.direction,\n          disagreement_level=excluded.disagreement_level, independent_consensus=excluded.independent_consensus,\n          bullish=excluded.bullish, bearish=excluded.bearish, unclear=excluded.unclear,\n          attention_trend=excluded.attention_trend, catalysts=excluded.catalysts, theme=excluded.theme,\n          evidence=excluded.evidence, sources=excluded.sources, first_detected=excluded.first_detected,\n          last_updated=excluded.last_updated, is_watchlist=excluded.is_watchlist`,\n      args: [rec.ticker, rec.company_name, rec.score_normalized, rec.score_available,\n        rec.available_max, rec.coverage, rec.confidence, rec.tier, rec.why_discovered,\n        rec.direction, rec.disagreement_level, rec.independent_consensus, rec.bullish,\n        rec.bearish, rec.unclear, rec.attention_trend, rec.catalysts, rec.theme,\n        rec.evidence, rec.sources, rec.first_detected, rec.last_updated, rec.is_watchlist],\n    });\n\n    for (const dim of Object.keys(DIM)) {\n      const scr = (scores as any)[dim];\n      if (Number.isNaN(scr)) continue;\n      sigStmts.push({\n        sql: `INSERT INTO discovery_signals (ticker, dimension, source, evidence, confidence, signal_time, payload) VALUES (?,?,?,?,?,?,?)`,\n        args: [t, dim, \"discovery_v2_compute\", `score ${scr}/${DIM[dim as keyof typeof DIM]}`, coverage >= 0.6 ? \"high\" : \"medium\", now, JSON.stringify({ dimension_max: DIM[dim as keyof typeof DIM] })],\n      });\n    }\n  }\n\n  // Persist candidates + signals in batched writes (fast, no per-row awaits).\n  for (let i = 0; i < candStmts.length; i += 20) await sqlite.batch(candStmts.slice(i, i + 20));\n  for (let i = 0; i < sigStmts.length; i += 20) await sqlite.batch(sigStmts.slice(i, i + 20));\n\n  // Scoped mode (onlyTickers set, e.g. a single-ticker controlled test):\n  // never touch the shared themes table or append a production\n  // discovery_runs row — blast radius stays limited to the requested\n  // ticker(s)' own rows in discovery_candidates/discovery_signals\n  // (upserted above via ON CONFLICT(ticker), so no other ticker's row is\n  // read, written, or otherwise affected).\n  if (!onlyTickers) {\n    await sqlite.execute(`DELETE FROM themes`);\n    const thStmts = themes.map((th) => ({\n      sql: `INSERT INTO themes (name, label, trend, related_tickers, related_accounts, evidence, attention, first_detected, last_updated)\n            VALUES (?,?,?,?,?,?,?,?,?)`,\n      args: [th.name, th.label, th.trend, JSON.stringify(th.related_tickers), JSON.stringify(th.related_accounts),\n        JSON.stringify(th.evidence), th.attention, th.first_detected, th.last_updated],\n    }));\n    for (let i = 0; i < thStmts.length; i += 20) await sqlite.batch(thStmts.slice(i, i + 20));\n\n    await sqlite.execute({\n      sql: `INSERT INTO discovery_runs (computed_at, candidates, themes, avg_coverage) VALUES (?,?,?,?)`,\n      args: [now, consensus.length, themes.length, Math.round((65 / TOTAL_MAX) * 100) / 100],\n    });\n  }\n\n  return { generatedAt: now, candidates: consensus.length, themes: themes.length };\n}\n\nasync function isFresh(): Promise {\n  const cand = await sqlite.execute(`SELECT COUNT(*) c FROM discovery_candidates`);\n  const last = await sqlite.execute(`SELECT MAX(computed_at) m FROM discovery_runs`);\n  const m = (last.rows[0] as any)?.m ?? null;\n  return Number((cand.rows[0] as any).c) > 0 && !!m && (Date.now() - new Date(m).getTime()) < FRESH_MS;\n}\n\nasync function ensureDiscoveryComputed(): Promise {\n  await ensureV2Schema();\n  if (await isFresh()) return;\n  await computeDiscovery(); // only runs when stale/empty\n}\n\nasync function ensureEmergingAccounts(): Promise {\n  await ensureV2Schema();\n  const last = await sqlite.execute(`SELECT MAX(last_evaluated) m FROM emerging_accounts`);\n  const m = (last.rows[0] as any)?.m ?? null;\n  if (m && (Date.now() - new Date(m).getTime()) < FRESH_MS) return;\n  await buildEmergingAccounts();\n}\n\nexport async function buildEmergingAccounts(): Promise {\n  await ensureV2Schema();\n  const { candidates } = await extractCandidates();\n  const now = new Date().toISOString();\n  const stmts: any[] = [];\n  for (const c of candidates) {\n    if (TRACKED_ACCOUNTS.some((h) => h.toLowerCase() === c.handle.toLowerCase())) continue;\n    const category = c.has_ticker_evidence\n      ? (c.times_referenced >= 3 ? \"Independent Market Voice\" : \"Sector Specialist\")\n      : \"Emerging Account\";\n    const confidence = c.api_resolved_evidence_count > 0 ? (c.times_referenced >= 3 ? \"Medium\" : \"Low\") : \"Low\";\n    const promo = c.promotional_flag ? 1 : 0;\n    stmts.push({\n      sql: `INSERT INTO emerging_accounts (\n        handle, category, times_referenced, referencing_accounts, tickers, evidence, promo_flagged,\n        confidence, repost_ratio, last_evaluated, promotion_status, notes)\n        VALUES (?,?,?,?,?,?,?,?,?,?,'Not Promoted',?)\n        ON CONFLICT(handle) DO UPDATE SET category=excluded.category, times_referenced=excluded.times_referenced,\n          referencing_accounts=excluded.referencing_accounts, tickers=excluded.tickers, evidence=excluded.evidence,\n          promo_flagged=excluded.promo_flagged, confidence=excluded.confidence, repost_ratio=excluded.repost_ratio,\n          last_evaluated=excluded.last_evaluated, notes=excluded.notes`,\n      args: [c.handle, category, c.times_referenced, JSON.stringify(c.referencing_core_accounts),\n        JSON.stringify(c.tickers_discussed), JSON.stringify(c.evidence.slice(0, 3)), promo,\n        confidence, null, now,\n        promo ? \"Flagged promotional — requires explicit review before any consideration.\" : \"Not promoted; explicit approval required to move toward tracked status.\"],\n    });\n  }\n  for (let i = 0; i < stmts.length; i += 20) await sqlite.batch(stmts.slice(i, i + 20));\n  const rows = (await sqlite.execute(`SELECT * FROM emerging_accounts ORDER BY times_referenced DESC LIMIT 60`)).rows as any[];\n  return rows.map((r) => ({ ...r, referencing_accounts: JSON.parse(r.referencing_accounts || \"[]\"), tickers: JSON.parse(r.tickers || \"[]\"), evidence: JSON.parse(r.evidence || \"[]\") }));\n}\n\nexport async function watchlistView(): Promise {\n  await ensureV2Schema();\n  const res = await sqlite.execute(`SELECT ticker, name, source, added_at FROM watchlist`);\n  const cand = await sqlite.execute(`SELECT ticker, score_normalized, tier, direction, independent_consensus FROM discovery_candidates`);\n  const candMap = new Map((cand.rows as any[]).map((r) => [r.ticker.toUpperCase(), r]));\n  return (res.rows as any[]).map((w) => {\n    const c = candMap.get(w.ticker.toUpperCase());\n    return { ticker: w.ticker, name: w.name ?? null, source: w.source, added_at: w.added_at,\n      status: c ? \"Existing Watchlist\" : \"Watchlist (no consensus data yet)\",\n      score_normalized: c?.score_normalized ?? null, tier: c?.tier ?? null,\n      direction: c?.direction ?? null, independent_consensus: c?.independent_consensus ?? 0 };\n  });\n}\n\nfunction decode(r: any) {\n  return { ...r, catalysts: JSON.parse(r.catalysts || \"[]\"), evidence: JSON.parse(r.evidence || \"[]\"), sources: JSON.parse(r.sources || \"[]\"), is_watchlist: !!r.is_watchlist };\n}\n\nexport async function discoveryApi(opts: { tier?: string; ticker?: string; period?: string } = {}) {\n  await ensureDiscoveryComputed();\n  await ensureEmergingAccounts();\n  const rows = (await sqlite.execute(`SELECT * FROM discovery_candidates ORDER BY score_normalized DESC, independent_consensus DESC LIMIT 300`)).rows as any[];\n  const themes = (await sqlite.execute(`SELECT * FROM themes ORDER BY attention DESC LIMIT 100`)).rows as any[];\n  const emerging = (await sqlite.execute(`SELECT * FROM emerging_accounts ORDER BY times_referenced DESC LIMIT 60`)).rows as any[];\n  const lastRun = (await sqlite.execute(`SELECT computed_at FROM discovery_runs ORDER BY id DESC LIMIT 1`)).rows as any[];\n  const wl = await watchlistView();\n\n  let list = rows.map(decode);\n  if (opts.tier) list = list.filter((c) => c.tier === opts.tier.toUpperCase());\n  if (opts.ticker) list = list.filter((c) => c.ticker.toUpperCase() === opts.ticker!.toUpperCase());\n\n  const tiers = { A: list.filter((c) => c.tier === \"A\").length, B: list.filter((c) => c.tier === \"B\").length, C: list.filter((c) => c.tier === \"C\").length };\n  const newToday = list.filter((c) => c.first_detected && c.first_detected >= new Date(Date.now() - 86400000).toISOString()).length;\n  const attentionRising = list.filter((c) => c.attention_trend && c.attention_trend.startsWith(\"rising\")).length;\n  const highDisagreement = list.filter((c) => c.disagreement_level === \"High\" || c.disagreement_level === \"Moderate\").length;\n  const nonWatchlist = list.filter((c) => !c.is_watchlist).length;\n\n  const themesOut = themes.map((t) => ({ ...t, related_tickers: JSON.parse(t.related_tickers || \"[]\"), related_accounts: JSON.parse(t.related_accounts || \"[]\"), evidence: JSON.parse(t.evidence || \"[]\") }));\n\n  return {\n    generatedAt: lastRun[0]?.computed_at ?? null, period: opts.period ?? \"daily\",\n    summary: { totalCandidates: list.length, tiers, newToday, attentionRising, highDisagreement, nonWatchlist },\n    candidates: list, themes: themesOut,\n    emergingAccounts: emerging, watchlist: wl, watchlistKeys: WATCHLIST,\n  };\n}\n\nexport async function claudeContext(): Promise {\n  await ensureDiscoveryComputed();\n  await ensureEmergingAccounts();\n  const d = await sqlite.execute(`SELECT * FROM discovery_candidates ORDER BY score_normalized DESC LIMIT 8`);\n  const top = (d.rows as any[]).map((r) => ({ ticker: r.ticker, score: r.score_normalized, tier: r.tier, direction: r.direction, accounts: r.independent_consensus, watch: !!r.is_watchlist }));\n  const consensus = await sqlite.execute(`SELECT tc.ticker, tc.consensus_direction, tc.disagreement_level, tc.unique_accounts_mentioning\n    FROM ticker_consensus tc INNER JOIN (SELECT ticker, MAX(computed_at) mc FROM ticker_consensus GROUP BY ticker) l\n    ON tc.ticker=l.ticker AND tc.computed_at=l.mc ORDER BY tc.unique_accounts_mentioning DESC LIMIT 10`);\n  const themes = await sqlite.execute(`SELECT name, attention, related_tickers FROM themes ORDER BY attention DESC LIMIT 6`);\n  const acct = await sqlite.execute(`SELECT handle, category, promotion_status, promo_flagged FROM emerging_accounts WHERE promo_flagged=0 ORDER BY times_referenced DESC LIMIT 8`);\n  const lastRun = await sqlite.execute(`SELECT computed_at FROM discovery_runs ORDER BY id DESC LIMIT 1`);\n  const mdCount = Number((await sqlite.execute(`SELECT COUNT(*) c FROM market_data`)).rows[0]?.c ?? 0);\n  const mdcfg = !!Deno.env.get(\"MARKETDATA_API_KEY\");\n  const miss = mdcfg\n    ? (mdCount > 0\n      ? `${mdCount} tickers have market-data rows; price/fundamentals/valuation/flow dimensions are scored where data exists.`\n      : \"Market-data provider configured, but no data fetched yet — those dimensions remain insufficient data (not zero).\")\n    : \"Market-data provider key (MARKETDATA_API_KEY) not configured — price/fundamentals/valuation/flow dimensions remain insufficient data (not zero).\";\n  return {\n    system_status: { ok: true, last_discovery_computed: (lastRun.rows[0] as any)?.computed_at ?? null },\n    last_update: (await sqlite.execute(`SELECT MAX(analyzed_at) m FROM posts WHERE analyzed_at IS NOT NULL`)).rows[0]?.m ?? null,\n    top_consensus_stocks: consensus.rows,\n    top_discovery_candidates: top,\n    emerging_themes: (themes.rows as any[]).map((r) => ({ theme: r.name, attention: r.attention, tickers: JSON.parse(r.related_tickers || \"[]\") })),\n    disagreement_major: (await sqlite.execute(`SELECT tc.ticker, tc.disagreement_level, tc.disagreement_score\n      FROM ticker_consensus tc INNER JOIN (SELECT ticker, MAX(computed_at) mc FROM ticker_consensus GROUP BY ticker) l\n      ON tc.ticker=l.ticker AND tc.computed_at=l.mc WHERE tc.disagreement_level='High' OR tc.disagreement_level='Moderate' LIMIT 6`)).rows,\n    new_candidates_today: (await sqlite.execute(`SELECT ticker, score_normalized, tier FROM discovery_candidates WHERE first_detected >= datetime('now','-1 day') LIMIT 10`)).rows,\n    emerging_accounts_to_review: acct.rows,\n    data_coverage_warnings: miss,\n  };\n}\n\nexport async function claudeMarketBrief(): Promise {\n  const ctx: any = await claudeContext();\n  const d = await sqlite.execute(`SELECT ticker, attention_trend, score_normalized, tier FROM discovery_candidates ORDER BY score_normalized DESC`);\n  const cands = (d.rows as any[]);\n  return {\n    as_of: ctx.last_update,\n    discovery: {\n      daily: { brief: \"Daily Market Discovery Brief — top new candidates, attention shifts, disagreements.\", candidates: cands.filter((c) => c.tier === \"A\" || c.tier === \"B\").slice(0, 12) },\n      weekly: { brief: \"Weekly Market Discovery Review — consensus strengthen/weaken, emerging accounts/themes.\", candidates: cands.slice(0, 15) },\n      monthly: { brief: \"Monthly Consensus Review — sustained themes, momentum, watchlist vs new.\", candidates: cands.slice(0, 20) },\n    },\n    top_candidates: ctx.top_discovery_candidates,\n    themes: ctx.emerging_themes,\n    acknowledgements: [\"Not investment advice; research prioritization only. Dimensions without data are marked insufficient, never zeroed as a negative signal.\"],\n  };\n}\n",[],[181,295],"64cc639b-da7c-493d-b4ef-73120e2d99b9","discovery.ts","6a1cfe3c-aa69-11f1-a8de-1607ee4eb77e",44,["D",1788787613031],"import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { TRACKED_ACCOUNTS } from \"../config.ts\";\n\n// ---------------------------------------------------------------------------\n// Phase 3 / 3.1: Trader Discovery Layer — MECHANICAL extraction pass only.\n//\n// This module finds candidate accounts using nothing but structural signals\n// already present in the stored posts — no invented data, no new API calls.\n// It deliberately does NOT assign a final account_group, specialization\n// label, or finished relevance_score: those require reading and\n// understanding what the evidence actually says, which is a semantic\n// judgment call kept separate from mechanical extraction here. The\n// mechanical output below is reviewed and finished by a semantic pass\n// before anything is written to discovered_accounts.\n//\n// Phase 3.1 change: identity resolution for WHO is being referenced now\n// comes from `posts.referenced_author_id` / `referenced_author_handle`,\n// which lib/xapi.ts + lib/normalize.ts populate from the X API's own\n// includes.tweets/includes.users expansion data (see those files) —\n// previously requested from the API but discarded, so this column was\n// always NULL and discovery had to guess from @mention text instead.\n//\n// Signal priority, per requirement \"do not infer an author from reply text\n// ... when API provenance exists\":\n//\n// 1. repost (post_type='repost'): candidate = referenced_author_handle,\n//    whatever its source. The evidence text IS the candidate's own words\n//    (X returns the full original tweet text for a classic retweet), so\n//    this remains the one post type where evidence content (including any\n//    tickers in it) can be genuinely attributed to the candidate.\n//    - source='api': resolved from X's own includes data — high\n//      confidence, and we now also know the candidate's real numeric\n//      x_account_id.\n//    - source='text_fallback': API includes did not cover this particular\n//      referenced tweet (e.g. it fell outside the bounded window of this\n//      validation re-fetch, or X's expansion omitted it), so\n//      lib/normalize.ts fell back to regex-parsing \"RT @handle:\" from the\n//      tweet text itself. Kept, but explicitly lower-confidence — never\n//      allowed to overwrite an 'api' resolution (enforced upstream).\n//\n// 2. quote / reply: candidate = referenced_author_handle ONLY when\n//    source='api'. This identifies WHO was quoted/replied to reliably, but\n//    the post's `text` is still the CITING Core Trader's own words, not\n//    the candidate's — so this evidence is tagged 'relationship' (a real,\n//    API-confirmed interaction) rather than 'own_words', and its tickers\n//    are never folded into the candidate's tickers_discussed (that was the\n//    exact misattribution problem found during Phase 3's manual semantic\n//    review — e.g. \"$HOOD\"/\"$NVDA\" appearing in a Core Trader's own reply\n//    was previously attributed to the person being replied to). When\n//    source is anything else (no API resolution — the referenced tweet\n//    fell outside this run's re-fetch), the post is skipped entirely: no\n//    more @mention-regex guessing for quote/reply, which was the single\n//    largest source of noise/misattribution in the Phase 3 candidate set.\n//\n// 3. original: no referenced_post_id exists (nothing was replied to), so\n//    the only available signal is a loose @mention appearing in the\n//    account's own post. Kept as the lowest-confidence tier, tagged\n//    'mention_text', and — same rule as (2) — never contributes tickers to\n//    the mentioned account, since the words are the posting account's own.\n// ---------------------------------------------------------------------------\n\ninterface PostRow {\n  post_id: string;\n  username: string;\n  post_type: string;\n  text: string;\n  mentioned_tickers: string;\n  posted_at: string;\n  original_url: string;\n  referenced_author_id: string | null;\n  referenced_author_handle: string | null;\n  referenced_author_name: string | null;\n  referenced_author_source: string | null; // 'api' | 'text_fallback' | null\n}\n\nexport type EvidenceType = \"own_words\" | \"relationship\" | \"mention_text\";\nexport type EvidenceProvenance = \"api\" | \"text_fallback\" | \"mention_regex\";\n\nexport interface EvidenceItem {\n  citing_account: string;\n  post_id: string;\n  post_type: string;\n  text: string;\n  original_url: string;\n  posted_at: string;\n  evidence_type: EvidenceType;\n  provenance: EvidenceProvenance;\n}\n\nexport interface MechanicalCandidate {\n  handle: string;\n  times_referenced: number;\n  referencing_core_accounts: string[];\n  evidence: EvidenceItem[];\n  tickers_discussed: string[]; // mechanical; ONLY from genuine own-words (repost) evidence — never from relationship/mention_text evidence\n  has_ticker_evidence: boolean;\n  max_evidence_length: number; // longest single piece of evidence text — crude substantiveness proxy\n  promotional_flag: boolean;\n  promotional_matches: string[];\n  // Phase 3.1: populated only when at least one piece of evidence resolved\n  // via API provenance — previously always unknown/null.\n  resolved_x_account_id: string | null;\n  resolved_display_name: string | null;\n  api_resolved_evidence_count: number;\n  text_fallback_evidence_count: number;\n}\n\nexport interface RejectedCandidate {\n  handle: string;\n  reason: string;\n  evidence: EvidenceItem[];\n}\n\nconst CORE_HANDLES = new Set(TRACKED_ACCOUNTS.map((h) => h.toLowerCase()));\n\n// Conservative, explicit keyword list for \"promotional/pump behavior should\n// be flagged\" — matched against evidence text, case-insensitive. Deliberately\n// narrow (real paid-community/pump phrasing seen in the actual dataset)\n// rather than broad enough to catch legitimate analysis by accident.\nconst PROMO_PATTERNS: { label: string; re: RegExp }[] = [\n  { label: \"paid-service pitch\", re: /\\bjoin (today|now)\\b/i },\n  { label: \"paid-service pitch\", re: /\\bbuy (them |it )?at my price\\b/i },\n  { label: \"paid community name-drop\", re: /\\bthe assembly\\b/i },\n  { label: \"paid community name-drop\", re: /@InTheAssembly/i },\n  { label: \"subscription pitch\", re: /\\bsubscrib(e|ing|er)\\b/i },\n  { label: \"discount/sale pitch\", re: /\\b\\d{1,3}%\\s*off\\b/i },\n  { label: \"discount/sale pitch\", re: /\\bsale is live\\b/i },\n  { label: \"paid-service testimonial\", re: /\\bbest paid service\\b/i },\n];\n\nfunction detectPromotional(text: string): string[] {\n  const hits: string[] = [];\n  for (const p of PROMO_PATTERNS) {\n    if (p.re.test(text)) hits.push(p.label);\n  }\n  return hits;\n}\n\n// Crude, deliberately strict substantiveness filter: strips emoji, URLs, and\n// whitespace, then requires a minimum amount of real alphabetic content.\n// This is what keeps \"@optionscjp 🔥🔥🔥\" and \"@markgurman 👀\" out of the\n// candidate pool without a full NLP pass — a single non-substantive\n// interaction is not evidence of anything (rule: \"one viral post is\n// insufficient for promotion\", extended here to \"one non-post is definitely\n// not evidence\").\nfunction isSubstantive(text: string): boolean {\n  const stripped = text\n    .replace(/https?:\\/\\/\\S+/g, \"\")\n    .replace(/[\\u{1F300}-\\u{1FAFF}\\u{2600}-\\u{27BF}]/gu, \"\")\n    .replace(/@\\w+/g, \"\")\n    .trim();\n  return stripped.length >= 30;\n}\n\nexport async function extractCandidates(): Promise<{\n  candidates: MechanicalCandidate[];\n  rejected: RejectedCandidate[];\n  notes: string[];\n}> {\n  const rows = await sqlite.execute(\n    `SELECT post_id, username, post_type, text, mentioned_tickers, posted_at, original_url,\n            referenced_author_id, referenced_author_handle, referenced_author_name, referenced_author_source\n     FROM posts\n     WHERE post_type IN ('repost', 'quote', 'reply', 'original')`,\n  );\n  const posts = rows.rows as unknown as PostRow[];\n\n  // handle -> accumulator\n  const byHandle = new Map;\n    evidence: EvidenceItem[];\n    tickers: Set;\n    xAccountId: string | null;\n    displayName: string | null;\n    apiResolvedCount: number;\n    textFallbackCount: number;\n  }>();\n\n  const ensure = (h: string) => {\n    if (!byHandle.has(h)) {\n      byHandle.set(h, {\n        referencing: new Set(),\n        evidence: [],\n        tickers: new Set(),\n        xAccountId: null,\n        displayName: null,\n        apiResolvedCount: 0,\n        textFallbackCount: 0,\n      });\n    }\n    return byHandle.get(h)!;\n  };\n\n  const addEvidence = (\n    handle: string,\n    p: PostRow,\n    evidenceType: EvidenceType,\n    provenance: EvidenceProvenance,\n    opts: { attributeTickers: boolean; xAccountId?: string | null; displayName?: string | null },\n  ) => {\n    if (CORE_HANDLES.has(handle.toLowerCase())) return; // already Core Trader — not a discovery\n    if (handle.toLowerCase() === p.username.toLowerCase()) return; // self-citation, skip\n    const acc = ensure(handle);\n    acc.referencing.add(p.username);\n    acc.evidence.push({\n      citing_account: p.username,\n      post_id: p.post_id,\n      post_type: p.post_type,\n      text: p.text,\n      original_url: p.original_url,\n      posted_at: p.posted_at,\n      evidence_type: evidenceType,\n      provenance,\n    });\n    if (opts.attributeTickers) {\n      for (const t of JSON.parse(p.mentioned_tickers || \"[]\")) acc.tickers.add(t);\n    }\n    if (provenance === \"api\") acc.apiResolvedCount++;\n    if (provenance === \"text_fallback\") acc.textFallbackCount++;\n    if (opts.xAccountId && !acc.xAccountId) acc.xAccountId = opts.xAccountId;\n    if (opts.displayName && !acc.displayName) acc.displayName = opts.displayName;\n  };\n\n  for (const p of posts) {\n    if (p.post_type === \"repost\") {\n      // Signal 1 (own-words, highest confidence): the reposted account's\n      // real identity, resolved from referenced_author_handle — API when\n      // available, text_fallback (\"RT @handle:\" regex, run once already in\n      // lib/normalize.ts) otherwise. Evidence text IS the candidate's own\n      // words, so tickers in it are genuinely attributable.\n      if (p.referenced_author_handle) {\n        addEvidence(\n          p.referenced_author_handle,\n          p,\n          \"own_words\",\n          p.referenced_author_source === \"api\" ? \"api\" : \"text_fallback\",\n          {\n            attributeTickers: true,\n            xAccountId: p.referenced_author_source === \"api\" ? p.referenced_author_id : null,\n            displayName: p.referenced_author_source === \"api\" ? p.referenced_author_name : null,\n          },\n        );\n      }\n      // If neither API nor text_fallback resolved a handle, this repost is\n      // simply skipped — no other signal exists to identify who was\n      // reposted (see notes[] for how many posts this affects).\n    } else if (p.post_type === \"quote\" || p.post_type === \"reply\") {\n      // Signal 2 (relationship only, API-confirmed identity required): who\n      // was quoted/replied to is now known reliably via referenced_author_*,\n      // but the post's `text` is the CITING account's own words — never the\n      // candidate's. No @mention-regex guessing is used here any more\n      // (that was the dominant source of noise/misattribution in Phase 3).\n      if (p.referenced_author_source === \"api\" && p.referenced_author_handle) {\n        addEvidence(p.referenced_author_handle, p, \"relationship\", \"api\", {\n          attributeTickers: false,\n          xAccountId: p.referenced_author_id,\n          displayName: p.referenced_author_name,\n        });\n      }\n      // Unresolved quote/reply (referenced tweet fell outside this run's\n      // re-fetch, or the API genuinely didn't return it) is skipped rather\n      // than falling back to @mention text — per requirement 6.\n    } else {\n      // Signal 3 (lowest confidence): @mentions inside an ORIGINAL post's\n      // own commentary. There is no referenced_post_id for an original\n      // post (nothing was replied to), so this is the only possible\n      // signal here — unchanged from Phase 3, still text-only, still\n      // never attributes tickers to the mentioned account.\n      const mentions = [...p.text.matchAll(/@(\\w+)/g)].map((m) => m[1]);\n      for (const handle of new Set(mentions)) {\n        addEvidence(handle, p, \"mention_text\", \"mention_regex\", { attributeTickers: false });\n      }\n    }\n  }\n\n  const candidates: MechanicalCandidate[] = [];\n  const rejected: RejectedCandidate[] = [];\n\n  for (const [handle, acc] of byHandle.entries()) {\n    const promoMatches = new Set();\n    for (const e of acc.evidence) {\n      for (const label of detectPromotional(e.text)) promoMatches.add(label);\n    }\n    if (promoMatches.size > 0) {\n      rejected.push({\n        handle,\n        reason: `Promotional/pump pattern detected: ${[...promoMatches].join(\", \")}`,\n        evidence: acc.evidence,\n      });\n      continue;\n    }\n\n    const substantiveEvidence = acc.evidence.filter((e) => isSubstantive(e.text));\n    const timesReferenced = acc.evidence.length;\n    const hasTickerEvidence = acc.tickers.size > 0;\n\n    // Minimum-evidence gate: keep if referenced 2+ times (recurrence itself\n    // is a signal even if any single mention is short), OR referenced once\n    // with genuinely substantive content. Otherwise reject as noise —\n    // this is what screens out the ~50 one-off emoji/social replies.\n    if (timesReferenced < 2 && substantiveEvidence.length === 0) {\n      rejected.push({\n        handle,\n        reason: \"Single occurrence with no substantive market-relevant content (emoji reaction, social banter, or too short to evaluate).\",\n        evidence: acc.evidence,\n      });\n      continue;\n    }\n\n    const maxLen = Math.max(...acc.evidence.map((e) => e.text.replace(/https?:\\/\\/\\S+/g, \"\").trim().length));\n\n    candidates.push({\n      handle,\n      times_referenced: timesReferenced,\n      referencing_core_accounts: [...acc.referencing].sort(),\n      evidence: acc.evidence,\n      tickers_discussed: [...acc.tickers].sort(),\n      has_ticker_evidence: hasTickerEvidence,\n      max_evidence_length: maxLen,\n      promotional_flag: false,\n      promotional_matches: [],\n      resolved_x_account_id: acc.xAccountId,\n      resolved_display_name: acc.displayName,\n      api_resolved_evidence_count: acc.apiResolvedCount,\n      text_fallback_evidence_count: acc.textFallbackCount,\n    });\n  }\n\n  candidates.sort((a, b) => b.times_referenced - a.times_referenced || b.referencing_core_accounts.length - a.referencing_core_accounts.length);\n\n  const referencedTotal = posts.filter((p) => p.post_type !== \"original\").length;\n  const referencedResolvedApi = posts.filter((p) => p.referenced_author_source === \"api\").length;\n  const referencedResolvedFallback = posts.filter((p) => p.referenced_author_source === \"text_fallback\").length;\n  const referencedUnresolved = referencedTotal - referencedResolvedApi - referencedResolvedFallback;\n\n  const notes = [\n    `Phase 3.1: referenced_author_id/handle is now populated from the X API's own includes.tweets/includes.users expansion data (previously requested but discarded — see lib/xapi.ts). Of ${referencedTotal} reply/quote/repost posts, ${referencedResolvedApi} resolved via API provenance, ${referencedResolvedFallback} via RT-text fallback (repost only), ${referencedUnresolved} remain unresolved (the referenced tweet fell outside this validation pass's bounded re-fetch window, or the API's expansion did not include it).`,\n    \"No new X API calls were made beyond the bounded Core Trader re-collection already run to validate the fix (10 accounts, existing MAX_RESULTS_PER_ACCOUNT cap) — no discovered candidate's own timeline was collected.\",\n    \"@mention-regex guessing is no longer used for quote/reply identity resolution (Phase 3's largest source of noise/misattribution) — those posts now require API-confirmed referenced_author_handle or are skipped entirely. @mention text is still used, at lowest confidence, only for original posts (which have no reference to resolve).\",\n    \"Ticker attribution is restricted to genuine own-words evidence (reposts) only — relationship (quote/reply) and mention_text (original) evidence never contributes tickers to a candidate, since that text belongs to the citing/posting account, not the candidate.\",\n  ];\n\n  return { candidates, rejected, notes };\n}\n",[],[181,304],"10417439-1277-4df4-bdfd-6e48091b75b5","dashboard.ts","dbf3c70a-ac1e-11f1-b666-1607ee4eb77e",85,["D",1788938396425],"// lib/dashboard.ts — server-side assembly for the Market Discovery &\n// Consensus dashboard UI. Reads ONLY existing SQLite data. Never writes,\n// never calls Anthropic, never re-analyzes. Reuses the exact consensus\n// semantics from lib/consensus.ts (unique independent accounts, repost\n// exclusion, one-account-one-vote, High-confidence-semantic-only votes,\n// ticker-identity exclusions, \"Unclear ≠ Neutral\").\n\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { TRACKED_ACCOUNTS } from \"../config.ts\";\nimport { computeTickerConsensus } from \"./consensus.ts\";\nimport type { TickerConsensusResult } from \"./consensus.ts\";\nimport { getExcludedTickers } from \"./tickerIdentity.ts\";\n\nexport type Period = \"daily\" | \"weekly\" | \"monthly\";\n\nconst PERIOD_HOURS: Record = {\n  daily: 24,\n  weekly: 24 * 7,\n  monthly: 24 * 28,\n};\n\nconst safeParseStrArray = (json: string | null | undefined): string[] => {\n  if (!json) return [];\n  try {\n    const v = JSON.parse(json);\n    return Array.isArray(v) ? v.filter((x) => typeof x === \"string\") : [];\n  } catch {\n    return [];\n  }\n};\n\ninterface SemanticTicker {\n  ticker: string;\n  company_name: string;\n  evidence: string;\n  confidence: \"High\" | \"Medium\" | \"Low\";\n  recognition_reason: string;\n}\nconst safeParseSemantic = (json: string | null | undefined): SemanticTicker[] => {\n  if (!json) return [];\n  try {\n    const v = JSON.parse(json);\n    return Array.isArray(v)\n      ? v.filter((x) => x && typeof x === \"object\" && typeof x.ticker === \"string\" && typeof x.confidence === \"string\")\n      : [];\n  } catch {\n    return [];\n  }\n};\n\nexport function tickersForPost(row: {\n  mentioned_tickers?: string | null;\n  stance_tickers?: string | null;\n  semantic_tickers?: string | null;\n}): string[] {\n  const set = new Set();\n  safeParseStrArray(row.mentioned_tickers).forEach((t) => set.add(t.toUpperCase()));\n  safeParseStrArray(row.stance_tickers).forEach((t) => set.add(t.toUpperCase()));\n  safeParseSemantic(row.semantic_tickers).forEach((s) => set.add(s.ticker.toUpperCase()));\n  return [...set];\n}\n\nexport function postMentionsTicker(row: {\n  mentioned_tickers?: string | null;\n  stance_tickers?: string | null;\n  semantic_tickers?: string | null;\n}, ticker: string): boolean {\n  const t = ticker.toUpperCase();\n  return safeParseStrArray(row.mentioned_tickers).some((x) => x.toUpperCase() === t) ||\n    safeParseStrArray(row.stance_tickers).some((x) => x.toUpperCase() === t) ||\n    safeParseSemantic(row.semantic_tickers).some((s) => s.ticker.toUpperCase() === t);\n}\n\nfunction cutoffIso(period: Period): string {\n  const hours = PERIOD_HOURS[period] ?? 24;\n  return new Date(Date.now() - hours * 3600 * 1000).toISOString();\n}\n\n// Full analyzed posts within a window (period-filtered on posted_at).\nasync function analyzedPostsInWindow(cutoff: string): Promise {\n  const res = await sqlite.execute({\n    sql: `SELECT post_id, username, post_type, posted_at, text,\n                 mentioned_tickers, stance_tickers, explicit_stance, semantic_tickers,\n                 evidence, thesis, catalyst, time_horizon, position_disclosure, original_url\n          FROM posts\n          WHERE analyzed_at IS NOT NULL AND posted_at >= ?\n          ORDER BY posted_at ASC`,\n    args: [cutoff],\n  });\n  return res.rows as unknown as any[];\n}\n\n// Recompute consensus for a set of analyzed posts using the EXISTING engine.\nasync function consensusFor(posts: any[]): Promise {\n  const excluded = await getExcludedTickers();\n  const rows = posts.map((p) => ({\n    post_id: p.post_id,\n    username: p.username,\n    post_type: p.post_type,\n    posted_at: p.posted_at,\n    text: p.text,\n    mentioned_tickers: p.mentioned_tickers ?? \"[]\",\n    stance_tickers: p.stance_tickers ?? \"[]\",\n    explicit_stance: p.explicit_stance,\n    semantic_tickers: p.semantic_tickers ?? \"[]\",\n  }));\n  return computeTickerConsensus(rows, excluded);\n}\n\n// Attention proxy: split a ticker's mentions in the window into a recent\n// quarter vs the prior three quarters of time. Returns a % change and a\n// \"rising\" flag. null when there is insufficient data to form a trend.\nfunction attentionChange(posts: any[], ticker: string, cutoff: string): { changePct: number | null; rising: boolean; recent: number; prior: number } {\n  const related = posts.filter((p) => postMentionsTicker(p, ticker));\n  if (related.length < 2) return { changePct: null, rising: false, recent: related.length, prior: 0 };\n  const nowMs = Date.now();\n  const cutoffMs = new Date(cutoff).getTime();\n  const midMs = cutoffMs + ((nowMs - cutoffMs) / 4);\n  const recent = related.filter((p) => new Date(p.posted_at).getTime() >= midMs).length;\n  const prior = related.length - recent;\n  const changePct = prior > 0 ? Math.round(((recent - prior) / prior) * 100) : (recent > 0 ? 100 : 0);\n  return { changePct, rising: recent > prior, recent, prior };\n}\n\n// Unique active traders (accounts) mentioning a ticker within the window.\nfunction uniqueAccounts(posts: any[], ticker: string): number {\n  return new Set(posts.filter((p) => postMentionsTicker(p, ticker)).map((p) => p.username)).size;\n}\n\nfunction latestView(posts: any[], ticker: string): string | null {\n  const related = posts.filter((p) => postMentionsTicker(p, ticker));\n  if (related.length === 0) return null;\n  related.sort((a, b) => (a.posted_at < b.posted_at ? 1 : -1));\n  return related[0].posted_at;\n}\n\nexport async function buildDashboardApi(period: Period) {\n  const cutoff = cutoffIso(period);\n  const posts = await analyzedPostsInWindow(cutoff);\n  const consensus = await consensusFor(posts);\n\n  // Header meta (all-time, not period-filtered)\n  let totalAnalyzedRes = await sqlite.execute(\"SELECT COUNT(*) AS n FROM posts WHERE analyzed_at IS NOT NULL\");\n  const totalAnalyzed = Number((totalAnalyzedRes.rows[0] as any).n || 0);\n  let latestRes = await sqlite.execute(\"SELECT MAX(posted_at) AS m FROM posts WHERE analyzed_at IS NOT NULL\");\n  const lastUpdated = (latestRes.rows[0] as any)?.m ?? null;\n  let acctRes = await sqlite.execute(\"SELECT COUNT(*) AS n FROM accounts\");\n  const trackedAccounts = Math.max(Number((acctRes.rows[0] as any).n || 0), TRACKED_ACCOUNTS.length);\n\n  // Overview cards\n  const bullishTickers = consensus.filter((c) => c.consensus_direction === \"Bullish\");\n  const bearishTickers = consensus.filter((c) => c.consensus_direction === \"Bearish\");\n  const unclearTickers = consensus.filter((c) => c.consensus_direction === \"No directional consensus (all Unclear or mention-only)\");\n  const agreementTickers = consensus.filter((c) => (c.consensus_direction === \"Bullish\" || c.consensus_direction === \"Bearish\") && (c.disagreement_level === \"None\" || c.disagreement_level === \"Low\"));\n  const disagreementTickers = consensus.filter((c) => c.consensus_direction === \"Mixed / Disputed\" || c.disagreement_level === \"Moderate\" || c.disagreement_level === \"High\");\n  const attentionRising = consensus.filter((c) => attentionChange(posts, c.ticker, cutoff).rising);\n\n  // Top Consensus Stocks (sortable client-side; server default order)\n  const topStocks = consensus.map((c) => {\n    const attn = attentionChange(posts, c.ticker, cutoff);\n    const dominant = c.bullish_accounts > c.bearish_accounts ? c.bullish_ratio : (c.bearish_accounts > c.bullish_accounts ? c.bearish_ratio : null);\n    const strength = dominant === null || dominant === undefined ? null : Math.round(dominant * 100);\n    return {\n      ticker: c.ticker,\n      mentions: c.mention_count,\n      bullishAccounts: c.bullish_accounts,\n      bearishAccounts: c.bearish_accounts,\n      unclear: c.unclear_accounts,\n      uniqueAccounts: c.unique_accounts_mentioning,\n      direction: c.consensus_direction,\n      disagreement: c.disagreement_level,\n      disagreementScore: c.disagreement_score,\n      strength,\n      attentionChangePct: attn.changePct,\n      attentionRising: attn.rising,\n      attentionRecent: attn.recent,\n      attentionPrior: attn.prior,\n      latestView: latestView(posts, c.ticker),\n    };\n  });\n\n  // Consensus / Disagreement sections\n  const agreement = agreementTickers.map((c) => ({\n    ticker: c.ticker, direction: c.consensus_direction, uniqueAccounts: c.unique_accounts_mentioning,\n    bullish: c.bullish_accounts, bearish: c.bearish_accounts, unclear: c.unclear_accounts, latestView: latestView(posts, c.ticker),\n  }));\n  const disagreement = disagreementTickers.map((c) => ({\n    ticker: c.ticker, direction: c.consensus_direction, level: c.disagreement_level, score: c.disagreement_score,\n    bullish: c.bullish_accounts, bearish: c.bearish_accounts, unclear: c.unclear_accounts, uniqueAccounts: c.unique_accounts_mentioning, latestView: latestView(posts, c.ticker),\n  }));\n\n  // Trader Monitor\n  const traders = [];\n  for (const handle of TRACKED_ACCOUNTS) {\n    const reposts = posts.filter((p) => p.username.toLowerCase() === handle.toLowerCase());\n    const stocks = new Set();\n    reposts.forEach((p) => tickersForPost(p).forEach((t) => stocks.add(t)));\n    const stanceCounts = { Bullish: 0, Bearish: 0, Unclear: 0 };\n    reposts.forEach((p) => {\n      const s = p.explicit_stance as string;\n      if (s === \"Bullish\" || s === \"Bearish\" || s === \"Unclear\") stanceCounts[s]++;\n    });\n    const thesisPosts = reposts.filter((p) => p.thesis).sort((a, b) => (a.posted_at < b.posted_at ? 1 : -1));\n    const latest = [...reposts].sort((a, b) => (a.posted_at < b.posted_at ? 1 : -1))[0];\n    traders.push({\n      account: handle,\n      latestActivity: latest?.posted_at ?? null,\n      stocksDiscussed: [...stocks].sort(),\n      counts: stanceCounts,\n      latestThesis: thesisPosts[0] ? { thesis: thesisPosts[0].thesis, posted_at: thesisPosts[0].posted_at, url: thesisPosts[0].original_url } : null,\n    });\n  }\n\n  // Attention & Emerging Ideas — rank by recent activity, independent accounts, acceleration\n  const attention = consensus.map((c) => {\n    const attn = attentionChange(posts, c.ticker, cutoff);\n    return {\n      ticker: c.ticker,\n      recentMentions: attn.recent,\n      uniqueAccounts: c.unique_accounts_mentioning,\n      mentions: c.mention_count,\n      latestView: latestView(posts, c.ticker),\n      changePct: attn.changePct,\n      direction: c.consensus_direction,\n    };\n  }).sort((a, b) => (b.recentMentions - a.recentMentions) || (b.uniqueAccounts - a.uniqueAccounts));\n\n  // Recent Market Views feed (analyzed posts with a stance or tickers)\n  const feedPosts = posts\n    .filter((p) => p.explicit_stance || postMentionsTicker(p, \"___\")) // keep only analyzed-with-stance or ticker-bearing\n    .filter((p) => p.explicit_stance !== null && (postMentionsTicker(p, \"PLACEHOLDER\") || !!p.thesis || p.explicit_stance === \"Bullish\" || p.explicit_stance === \"Bearish\" || p.explicit_stance === \"Unclear\"))\n    .sort((a, b) => (a.posted_at < b.posted_at ? 1 : -1)).slice(0, 60);\n  const recentViews = feedPosts.map((p) => ({\n    account: p.username,\n    postType: p.post_type,\n    tickers: tickersForPost(p),\n    stance: p.explicit_stance,\n    evidence: p.evidence,\n    thesis: p.thesis,\n    postedAt: p.posted_at,\n    url: p.original_url,\n  }));\n\n  return {\n    period,\n    cutoff,\n    meta: { lastUpdated, trackedAccounts, totalAnalyzed, consensusTickers: consensus.length },\n    overview: {\n      bullishTickers: bullishTickers.length,\n      bearishTickers: bearishTickers.length,\n      unclearTickers: unclearTickers.length,\n      consensus: agreementTickers.length,\n      disagreement: disagreementTickers.length,\n      attentionRising: attentionRising.length,\n    },\n    topStocks,\n    agreement,\n    disagreement,\n    traders,\n    attention,\n    recentViews,\n  };\n}\n\n// Stock drilldown: all analyzed posts for one ticker (not period-limited so\n// stance history and mention trend are complete and attributable).\nexport async function buildStockDrilldown(ticker: string): Promise {\n  const t = ticker.toUpperCase();\n  const all = await analyzedPostsInWindow(\"1970-01-01T00:00:00.000Z\"); // all-time analyzed\n  const related = all.filter((p) => postMentionsTicker(p, t));\n  const excluded = await getExcludedTickers();\n  if (excluded.has(t)) {\n    return { ticker: t, excluded: true, note: \"Ticker is excluded from consensus by the ticker-identity safeguard.\" };\n  }\n\n  const rows = related.map((p) => ({\n    post_id: p.post_id, username: p.username, post_type: p.post_type, posted_at: p.posted_at, text: p.text,\n    mentioned_tickers: p.mentioned_tickers ?? \"[]\", stance_tickers: p.stance_tickers ?? \"[]\",\n    explicit_stance: p.explicit_stance, semantic_tickers: p.semantic_tickers ?? \"[]\",\n  }));\n  const c = computeTickerConsensus(rows, excluded).find((r) => r.ticker === t) ?? null;\n\n  // Mention trend: bucket by time into up to 8 buckets (all-time span).\n  const trend = (() => {\n    if (related.length === 0) return [{ label: \"No data\", count: 0 }];\n    const times = related.map((p) => new Date(p.posted_at).getTime()).sort((a, b) => a - b);\n    const min = times[0], max = Math.max(Date.now(), times[times.length - 1]);\n    const span = Math.max(max - min, 1);\n    const buckets: { label: string; count: number }[] = [];\n    const nBuckets = Math.min(8, Math.max(2, related.length));\n    for (let i = 0; i < nBuckets; i++) {\n      const start = min + (span / nBuckets) * i;\n      const end = min + (span / nBuckets) * (i + 1);\n      const count = related.filter((p) => {\n        const tms = new Date(p.posted_at).getTime();\n        return tms >= start && (i === nBuckets - 1 ? tms <= end : tms < end);\n      }).length;\n      buckets.push({ label: new Date(start).toISOString().slice(0, 10), count });\n    }\n    return buckets;\n  })();\n\n  const latestViews = related.sort((a, b) => (a.posted_at < b.posted_at ? 1 : -1)).slice(0, 25).map((p) => ({\n    account: p.username, postType: p.post_type, stance: p.explicit_stance, evidence: p.evidence, thesis: p.thesis,\n    catalyst: p.catalyst, timeHorizon: p.time_horizon, positionDisclosure: p.position_disclosure,\n    postedAt: p.posted_at, url: p.original_url, text: p.text?.slice(0, 300) ?? null,\n  }));\n\n  const stanceHistory = related.sort((a, b) => (a.posted_at < b.posted_at ? 1 : -1)).slice(0, 30).map((p) => ({\n    account: p.username, stance: p.explicit_stance, postedAt: p.posted_at, url: p.original_url, postType: p.post_type,\n  }));\n\n  // Latest distinct thesis / catalyst / horizon / position disclosure (only when explicitly present)\n  const withThesis = related.filter((p) => p.thesis).sort((a, b) => (a.posted_at < b.posted_at ? 1 : -1));\n  const withCat = related.filter((p) => p.catalyst).sort((a, b) => (a.posted_at < b.posted_at ? 1 : -1));\n  const withHor = related.filter((p) => p.time_horizon).sort((a, b) => (a.posted_at < b.posted_at ? 1 : -1));\n  const withPos = related.filter((p) => p.position_disclosure).sort((a, b) => (a.posted_at < b.posted_at ? 1 : -1));\n\n  return {\n    ticker: t,\n    excluded: false,\n    consensus: c,\n    relatedPosts: related.length,\n    uniqueAccounts: new Set(related.map((p) => p.username)).size,\n    trend,\n    latestViews,\n    stanceHistory,\n    detail: {\n      thesis: withThesis[0]?.thesis ?? null,\n      catalyst: withCat[0]?.catalyst ?? null,\n      timeHorizon: withHor[0]?.time_horizon ?? null,\n      positionDisclosure: withPos[0]?.position_disclosure ?? null, // only when explicitly stated\n    },\n  };\n}\n\n// Search by ticker or trader.\nexport async function searchMarket(q: string): Promise {\n  const query = (q ?? \"\").trim().toLowerCase();\n  if (!query) return { tickers: [], traders: [] };\n\n  const all = await analyzedPostsInWindow(\"1970-01-01T00:00:00.000Z\");\n  const consensus = await consensusFor(all);\n\n  // Ticker matches\n  const tickerMatches = new Set();\n  for (const c of consensus) {\n    if (c.ticker.toLowerCase().includes(query)) tickerMatches.add(c.ticker);\n  }\n  for (const p of all) {\n    if (tickForPostHas(p, query)) tickerMatches.add(query.toUpperCase());\n  }\n\n  // Trader matches\n  const traderMatches = TRACKED_ACCOUNTS.filter((h) => h.toLowerCase().includes(query)).map((h) => h);\n\n  return {\n    query,\n    tickers: [...tickerMatches].sort(),\n    traders: traderMatches,\n  };\n}\n\nfunction tickForPostHas(p: any, q: string): boolean {\n  return tickersForPost(p).some((t) => t.toLowerCase() === q);\n}\n",[],[181,312],"bc982c59-db31-40f9-bc8e-24a87fce3cc7","consensus.ts","f051bc72-aa0f-11f1-aadc-1607ee4eb77e",60,["D",1788840482255],"import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { getExcludedTickers, runIdentitySafeguardSweep } from \"./tickerIdentity.ts\";\n\ninterface SemanticAssignment {\n  ticker: string;\n  company_name: string;\n  evidence: string;\n  confidence: \"High\" | \"Medium\" | \"Low\";\n  recognition_reason: string;\n}\n\ninterface PostRow {\n  post_id: string;\n  username: string;\n  post_type: string;\n  posted_at: string;\n  text?: string; // raw post text — only selected/used for the ticker-identity safeguard sweep\n  mentioned_tickers: string; // JSON array string (mechanical cashtag extraction)\n  stance_tickers: string; // JSON array string (mechanical-ticker subset the post's stance applies to)\n  explicit_stance: string | null; // Bullish | Bearish | Unclear | null\n  semantic_tickers: string; // JSON array string of SemanticAssignment objects (Phase 2.5)\n}\n\ninterface AccountVote {\n  username: string;\n  vote: \"Bullish\" | \"Bearish\" | \"Unclear\";\n  posted_at: string;\n}\n\nexport interface TickerConsensusResult {\n  ticker: string;\n  window_start: string;\n  window_end: string;\n  unique_accounts_mentioning: number;\n  bullish_accounts: number;\n  bearish_accounts: number;\n  unclear_accounts: number;\n  bullish_ratio: number | null;\n  bearish_ratio: number | null;\n  consensus_direction: string;\n  disagreement_level: string;\n  disagreement_score: number;\n  mention_count: number;\n  discovery_method: \"mechanical\" | \"semantic\" | \"both\";\n  mechanical_mention_count: number;\n  semantic_high_mention_count: number;\n  semantic_medium_flagged_count: number;\n}\n\nfunction safeParseArray(json: string | null): string[] {\n  if (!json) return [];\n  try {\n    const v = JSON.parse(json);\n    return Array.isArray(v) ? v.filter((x) => typeof x === \"string\") : [];\n  } catch {\n    return [];\n  }\n}\n\nfunction safeParseSemantic(json: string | null): SemanticAssignment[] {\n  if (!json) return [];\n  try {\n    const v = JSON.parse(json);\n    return Array.isArray(v)\n      ? v.filter((x) => x && typeof x === \"object\" && typeof x.ticker === \"string\" && typeof x.confidence === \"string\")\n      : [];\n  } catch {\n    return [];\n  }\n}\n\n/** Does `post` provide a ticker-specific Bullish/Bearish stance for `ticker`?\n * True when EITHER:\n *  - ticker is in the post's stance_tickers (mechanical cashtag -> stance link, Phase 2), or\n *  - ticker has a High-confidence semantic assignment on this post (Phase 2.5 —\n *    \"High confidence can enter consensus aggregation\"), AND the post's overall\n *    explicit_stance is Bullish or Bearish.\n * Medium/Low-confidence semantic assignments never satisfy this (rule: only\n * High enters consensus; Medium is stored/flagged but excluded; Low never\n * affects consensus at all).\n */\nfunction isStanceLinked(post: PostRow, ticker: string): boolean {\n  if (post.explicit_stance !== \"Bullish\" && post.explicit_stance !== \"Bearish\") return false;\n  const stanceTickers = safeParseArray(post.stance_tickers);\n  if (stanceTickers.includes(ticker)) return true;\n  const semantic = safeParseSemantic(post.semantic_tickers);\n  return semantic.some((a) => a.ticker === ticker && a.confidence === \"High\");\n}\n\n/**\n * Compute per-ticker consensus from already-analyzed posts, now incorporating\n * BOTH mechanical cashtag extraction (Phase 2) and semantic company/ticker\n * recognition (Phase 2.5). Methodology, kept explicit so it stays auditable:\n *\n * - Ticker universe: a ticker gets a result row if it has at least one\n *   MECHANICAL mention (mentioned_tickers) OR at least one HIGH-confidence\n *   SEMANTIC assignment (semantic_tickers). Medium-confidence-only tickers\n *   never get their own row — Medium is tracked (semantic_medium_flagged_count)\n *   but must not, by itself, create consensus coverage. Low-confidence\n *   assignments are ignored entirely at this stage (never affect consensus).\n *\n * - mention_count / mechanical_mention_count / semantic_high_mention_count\n *   (ATTENTION signals): raw post counts, INCLUDING reposts. \"Mentioned\"\n *   (mechanically OR semantically) never by itself implies a stance.\n *\n * - unique_accounts_mentioning / bullish_accounts / bearish_accounts /\n *   unclear_accounts (CONSENSUS signal): computed ONLY from non-repost posts\n *   (original/reply/quote) — a repost is never an independent stance from\n *   that account (rule 7), regardless of whether the ticker link is\n *   mechanical or semantic.\n *\n * - One account = exactly one vote per ticker (rule 8), never one vote per\n *   post. For each account, all qualifying (non-repost) posts mentioning the\n *   ticker (mechanically or via high-confidence semantic recognition) are\n *   sorted newest-first; the first one that is stance-linked to this ticker\n *   (see isStanceLinked) sets the account's vote (Bullish/Bearish). If none\n *   qualify, the account's vote is \"Unclear\" — it mentioned/was linked to the\n *   ticker without ever taking an explicit, ticker-specific directional\n *   stance on it. This is NOT \"Neutral\" (rule 2).\n *\n * - Mechanical and semantic signals always stay distinguishable in the\n *   output (discovery_method + separate mention-count breakdown), even\n *   though they're combined for the purposes of computing one ticker's\n *   overall consensus numbers.\n *\n * - Ticker/Entity Identity exclusion (production-data cleanup layer,\n *   ADDITIVE on top of the rules above): `excludedTickers`, when provided,\n *   is a set of tickers whose LATEST ticker_identity_reviews row explicitly\n *   classified them as 'Confirmed False Positive' or 'Ambiguous/Exclude' —\n *   i.e. the mechanically/semantically extracted symbol has been determined\n *   NOT to reliably refer to the security it nominally names. Such tickers\n *   are skipped entirely when building consensus buckets: they contribute\n *   ZERO mechanical/semantic mentions and ZERO votes, exactly as if the\n *   cashtag had never been extracted — WITHOUT ever touching the underlying\n *   post rows (mentioned_tickers/stance_tickers/semantic_tickers on\n *   `posts` are never modified; the raw mechanical extraction stays intact\n *   for auditability). See lib/tickerIdentity.ts.\n */\nexport function computeTickerConsensus(posts: PostRow[], excludedTickers: Set = new Set()): TickerConsensusResult[] {\n  interface TickerBucket {\n    mechPosts: PostRow[];\n    semHighPosts: PostRow[];\n    semMediumCount: number;\n  }\n  const byTicker = new Map();\n  const ensure = (t: string): TickerBucket => {\n    if (!byTicker.has(t)) byTicker.set(t, { mechPosts: [], semHighPosts: [], semMediumCount: 0 });\n    return byTicker.get(t)!;\n  };\n\n  for (const p of posts) {\n    for (const t of safeParseArray(p.mentioned_tickers)) {\n      if (excludedTickers.has(t)) continue; // ticker-identity exclusion — see lib/tickerIdentity.ts\n      ensure(t).mechPosts.push(p);\n    }\n    for (const a of safeParseSemantic(p.semantic_tickers)) {\n      if (excludedTickers.has(a.ticker)) continue; // ticker-identity exclusion — see lib/tickerIdentity.ts\n      if (a.confidence === \"High\") {\n        ensure(a.ticker).semHighPosts.push(p);\n      } else if (a.confidence === \"Medium\") {\n        ensure(a.ticker).semMediumCount += 1;\n      }\n      // Low confidence: intentionally not counted anywhere in consensus math.\n    }\n  }\n\n  const results: TickerConsensusResult[] = [];\n\n  for (const [ticker, bucket] of byTicker.entries()) {\n    // Medium-confidence-only tickers (no mechanical mention, no High-confidence\n    // semantic assignment) never get a result row — Medium must not, by\n    // itself, create consensus coverage. Their counts are still tracked and\n    // reported separately (see medium_confidence_flagged in runConsensusAggregation).\n    if (bucket.mechPosts.length === 0 && bucket.semHighPosts.length === 0) {\n      continue;\n    }\n\n    // De-dupe by post_id in case a post ever contributed the same ticker via\n    // both paths (shouldn't happen given the mechanical/semantic dedup rule,\n    // but guarded here defensively).\n    const seen = new Map();\n    for (const p of bucket.mechPosts) seen.set(p.post_id, p);\n    for (const p of bucket.semHighPosts) seen.set(p.post_id, p);\n    const tickerPosts = [...seen.values()];\n\n    const mechanical_mention_count = bucket.mechPosts.length;\n    const semantic_high_mention_count = bucket.semHighPosts.length;\n    const semantic_medium_flagged_count = bucket.semMediumCount;\n    const mention_count = tickerPosts.length; // combined, deduped, includes reposts\n\n    const discovery_method: \"mechanical\" | \"semantic\" | \"both\" = mechanical_mention_count > 0 && semantic_high_mention_count > 0\n      ? \"both\"\n      : mechanical_mention_count > 0\n      ? \"mechanical\"\n      : \"semantic\";\n\n    const nonRepost = tickerPosts.filter((p) => p.post_type !== \"repost\");\n    const byAccount = new Map();\n    for (const p of nonRepost) {\n      if (!byAccount.has(p.username)) byAccount.set(p.username, []);\n      byAccount.get(p.username)!.push(p);\n    }\n\n    const votes: AccountVote[] = [];\n    for (const [username, accPosts] of byAccount.entries()) {\n      const sorted = [...accPosts].sort((a, b) => (a.posted_at < b.posted_at ? 1 : -1));\n      let vote: AccountVote | null = null;\n      for (const p of sorted) {\n        if (isStanceLinked(p, ticker)) {\n          vote = { username, vote: p.explicit_stance as \"Bullish\" | \"Bearish\", posted_at: p.posted_at };\n          break;\n        }\n      }\n      if (!vote) {\n        vote = { username, vote: \"Unclear\", posted_at: sorted[0].posted_at };\n      }\n      votes.push(vote);\n    }\n\n    const unique_accounts_mentioning = votes.length;\n    const bullish_accounts = votes.filter((v) => v.vote === \"Bullish\").length;\n    const bearish_accounts = votes.filter((v) => v.vote === \"Bearish\").length;\n    const unclear_accounts = votes.filter((v) => v.vote === \"Unclear\").length;\n\n    const bullish_ratio = unique_accounts_mentioning > 0 ? bullish_accounts / unique_accounts_mentioning : null;\n    const bearish_ratio = unique_accounts_mentioning > 0 ? bearish_accounts / unique_accounts_mentioning : null;\n\n    let consensus_direction: string;\n    if (bullish_accounts === 0 && bearish_accounts === 0) {\n      consensus_direction = \"No directional consensus (all Unclear or mention-only)\";\n    } else if (bullish_accounts > bearish_accounts) {\n      consensus_direction = \"Bullish\";\n    } else if (bearish_accounts > bullish_accounts) {\n      consensus_direction = \"Bearish\";\n    } else {\n      consensus_direction = \"Mixed / Disputed\";\n    }\n\n    let disagreement_score = 0;\n    if (bullish_accounts > 0 && bearish_accounts > 0) {\n      const mn = Math.min(bullish_accounts, bearish_accounts);\n      const mx = Math.max(bullish_accounts, bearish_accounts);\n      disagreement_score = (2 * mn) / (mn + mx); // 0 = one-sided, 1 = perfectly split\n    }\n    let disagreement_level: string;\n    if (bullish_accounts === 0 || bearish_accounts === 0) disagreement_level = \"None\";\n    else if (disagreement_score < 0.34) disagreement_level = \"Low\";\n    else if (disagreement_score < 0.67) disagreement_level = \"Moderate\";\n    else disagreement_level = \"High\";\n\n    const allDates = tickerPosts.map((p) => p.posted_at).sort();\n    const window_start = allDates[0];\n    const window_end = allDates[allDates.length - 1];\n\n    results.push({\n      ticker,\n      window_start,\n      window_end,\n      unique_accounts_mentioning,\n      bullish_accounts,\n      bearish_accounts,\n      unclear_accounts,\n      bullish_ratio,\n      bearish_ratio,\n      consensus_direction,\n      disagreement_level,\n      disagreement_score,\n      mention_count,\n      discovery_method,\n      mechanical_mention_count,\n      semantic_high_mention_count,\n      semantic_medium_flagged_count,\n    });\n  }\n\n  results.sort((a, b) =>\n    b.unique_accounts_mentioning - a.unique_accounts_mentioning || b.mention_count - a.mention_count\n  );\n  return results;\n}\n\n/**\n * Run the aggregation against ALL analyzed posts currently stored and\n * append one snapshot row per ticker into ticker_consensus (append-only —\n * \"do not overwrite raw source data\" extended here to not overwrite prior\n * snapshots either, so history can accumulate).\n *\n * Also reports a before/after ticker-coverage comparison: \"before\" is the\n * distinct ticker count already sitting in ticker_consensus from the most\n * recent prior run (i.e. mechanical-only, from Phase 2); \"after\" is this\n * run's count (mechanical + high-confidence semantic, Phase 2.5). Plus a\n * transparency list of Medium-confidence semantic assignments, which are\n * stored on the posts themselves but deliberately excluded from every\n * consensus number above.\n *\n * attention_acceleration and stance_change_1d/7d/28d are intentionally left\n * NULL on every row: they require diffing against a PRIOR snapshot for the\n * same ticker, and the available post history currently spans only a few\n * days. A future run, once multiple snapshots exist, can populate these by\n * comparing computed_at rows per ticker.\n */\nexport async function runConsensusAggregation() {\n  const beforeCountRow = await sqlite.execute(`SELECT COUNT(DISTINCT ticker) as n FROM ticker_consensus`);\n  const tickers_covered_before = Number((beforeCountRow.rows[0] as any)?.n ?? 0);\n\n  const rows = await sqlite.execute(\n    `SELECT post_id, username, post_type, posted_at, text, mentioned_tickers, stance_tickers, explicit_stance, semantic_tickers\n     FROM posts\n     WHERE mentioned_tickers != '[]' OR semantic_tickers != '[]'`,\n  );\n\n  const posts = rows.rows as unknown as PostRow[];\n\n  // Ticker/Entity Identity exclusion layer (production-data cleanup): any\n  // ticker with a 'Confirmed False Positive' / 'Ambiguous/Exclude' review\n  // never enters consensus math below. See lib/tickerIdentity.ts.\n  const excludedTickers = await getExcludedTickers();\n  const results = computeTickerConsensus(posts, excludedTickers);\n  const computed_at = new Date().toISOString();\n\n  // Reusable ticker-identity safeguard: sweep every MECHANICALLY-mentioned\n  // ticker that has no ticker_identity_reviews row yet (i.e. never reviewed\n  // AND never previously auto-flagged) and flag likely identity collisions\n  // for human review. Flag-only — never excludes anything itself, and never\n  // touches already-reviewed/already-flagged tickers.\n  const evidenceByTicker = new Map();\n  for (const p of posts) {\n    for (const t of safeParseArray(p.mentioned_tickers)) {\n      if (!p.text) continue;\n      if (!evidenceByTicker.has(t)) evidenceByTicker.set(t, { texts: [] });\n      evidenceByTicker.get(t)!.texts.push(p.text);\n    }\n  }\n  const safeguardSweep = await runIdentitySafeguardSweep(evidenceByTicker);\n\n  // Transparency: every Medium-confidence semantic assignment, flat, with\n  // its source post — stored/reportable but never merged into consensus math.\n  const medium_confidence_flagged: Array<\n    { post_id: string; ticker: string; company_name: string; evidence: string; recognition_reason: string }\n  > = [];\n  for (const p of posts) {\n    for (const a of safeParseSemantic(p.semantic_tickers)) {\n      if (a.confidence === \"Medium\") {\n        medium_confidence_flagged.push({\n          post_id: p.post_id,\n          ticker: a.ticker,\n          company_name: a.company_name,\n          evidence: a.evidence,\n          recognition_reason: a.recognition_reason,\n        });\n      }\n    }\n  }\n\n  if (results.length > 0) {\n    await sqlite.batch(\n      results.map((r) => ({\n        sql: `INSERT INTO ticker_consensus (\n          ticker, computed_at, window_start, window_end,\n          unique_accounts_mentioning, bullish_accounts, bearish_accounts, unclear_accounts,\n          bullish_ratio, bearish_ratio, consensus_direction, disagreement_level, disagreement_score,\n          mention_count, attention_acceleration, attention_note,\n          stance_change_1d, stance_change_7d, stance_change_28d,\n          discovery_method, mechanical_mention_count, semantic_high_mention_count, semantic_medium_flagged_count\n        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NULL, ?, NULL, NULL, NULL, ?, ?, ?, ?)`,\n        args: [\n          r.ticker,\n          computed_at,\n          r.window_start,\n          r.window_end,\n          r.unique_accounts_mentioning,\n          r.bullish_accounts,\n          r.bearish_accounts,\n          r.unclear_accounts,\n          r.bullish_ratio,\n          r.bearish_ratio,\n          r.consensus_direction,\n          r.disagreement_level,\n          r.disagreement_score,\n          r.mention_count,\n          \"Not yet computable: requires a prior snapshot for this ticker to diff against. Will populate once multiple runs accumulate history.\",\n          r.discovery_method,\n          r.mechanical_mention_count,\n          r.semantic_high_mention_count,\n          r.semantic_medium_flagged_count,\n        ],\n      })),\n      \"write\",\n    );\n  }\n\n  return {\n    computed_at,\n    tickers_computed: results.length,\n    tickers_covered_before,\n    tickers_covered_after: results.length,\n    new_tickers_from_semantic_recognition: results.filter((r) => r.discovery_method === \"semantic\").map((r) => r.ticker),\n    medium_confidence_flagged,\n    excluded_tickers_identity_cleanup: [...excludedTickers],\n    identity_safeguard_newly_flagged: safeguardSweep.newly_flagged,\n    results,\n  };\n}\n",[],[181,320],"de9c4ac1-e38b-4cc3-a2f2-687dbbf339db","collector.ts","b4a2dfbc-aa00-11f1-b9bc-1607ee4eb77e",41,["D",1788785371956],5,"import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { MAX_RESULTS_PER_ACCOUNT, REQUEST_SPACING_MS, TRACKED_ACCOUNTS } from \"../config.ts\";\nimport { fetchUserTweets, resolveUsernames } from \"./xapi.ts\";\nimport { buildReferencedAuthorLookup, normalizeTweet } from \"./normalize.ts\";\nimport { ensureSchema } from \"./schema.ts\";\n\nfunction sleep(ms: number) {\n  return new Promise((r) => setTimeout(r, ms));\n}\n\n// Resolves any configured accounts that aren't in the accounts table yet\n// (first run, or a newly added handle). Stores accounts keyed by the exact\n// casing in config.ts so later lookups always match.\nasync function ensureAccountsResolved(): Promise {\n  const existing = await sqlite.execute(\"SELECT username FROM accounts\");\n  const known = new Set((existing.rows as any[]).map((r) => String(r.username).toLowerCase()));\n  const missing = TRACKED_ACCOUNTS.filter((u) => !known.has(u.toLowerCase()));\n  if (missing.length === 0) return;\n\n  const resolved = await resolveUsernames(missing);\n  const byLower = new Map(resolved.map((u) => [u.username.toLowerCase(), u]));\n  const now = new Date().toISOString();\n\n  for (const configUsername of missing) {\n    const match = byLower.get(configUsername.toLowerCase());\n    if (!match) {\n      console.log(\n        `Could not resolve X account @${configUsername} — check spelling, or the account may be suspended/protected.`,\n      );\n      continue;\n    }\n    await sqlite.execute({\n      sql: `INSERT INTO accounts (username, account_id, display_name, resolved_at)\n            VALUES (?, ?, ?, ?)\n            ON CONFLICT(username) DO UPDATE SET\n              account_id = excluded.account_id,\n              display_name = excluded.display_name,\n              resolved_at = excluded.resolved_at`,\n      args: [configUsername, match.id, match.name, now],\n    });\n  }\n}\n\nexport interface CollectorSummary {\n  startedAt: string;\n  finishedAt: string;\n  accountsProcessed: number;\n  postsIngested: number;\n  // Phase 3.1: rows that already existed (same post_id) but got their\n  // referenced_author_* provenance fields backfilled from this run's API\n  // response. Tracked separately from postsIngested so that count keeps\n  // meaning \"genuinely new posts\" — see the per-tweet loop below.\n  postsBackfilled: number;\n  errors: { username: string; message: string }[];\n}\n\n// Runs one full collection pass across every tracked account. Idempotent:\n// re-running is safe (post_id is the primary key; since_id keeps each\n// account's fetch incremental once a baseline exists).\n//\n// opts.resetSinceId: Phase 3.1 validation only. When true, clears\n// last_post_id for the Core Trader accounts BEFORE fetching, so the X API\n// returns the same recent window again (bounded by MAX_RESULTS_PER_ACCOUNT\n// per account — no unbounded backfill) instead of nothing. This is what\n// lets already-ingested rows pick up referenced_author_* provenance that\n// the collector previously discarded, without an unbounded re-collection.\n// Scoped strictly to the 10 Core Trader accounts already being collected —\n// never used for discovered candidates.\nexport async function runCollector(\n  opts: { resetSinceId?: boolean } = {},\n): Promise {\n  await ensureSchema();\n  const startedAt = new Date().toISOString();\n  const errors: { username: string; message: string }[] = [];\n  let postsIngested = 0;\n  let postsBackfilled = 0;\n  let accountsProcessed = 0;\n\n  if (opts.resetSinceId) {\n    const placeholders = TRACKED_ACCOUNTS.map(() => \"?\").join(\",\");\n    await sqlite.execute({\n      sql: `UPDATE accounts SET last_post_id = NULL WHERE username IN (${placeholders})`,\n      args: TRACKED_ACCOUNTS,\n    });\n  }\n\n  try {\n    await ensureAccountsResolved();\n  } catch (err) {\n    // A fatal error here (e.g. the X API rejecting the very first call —\n    // auth failure, credits depleted, etc.) means no account could be\n    // processed at all. Record it as a run so /runs shows the history\n    // instead of the request just 500-ing with nothing logged.\n    const finishedAt = new Date().toISOString();\n    const fatal = { username: \"*all*\", message: (err as Error).message };\n    await sqlite.execute({\n      sql: `INSERT INTO collector_runs (started_at, finished_at, accounts_processed, posts_ingested, errors)\n            VALUES (?, ?, ?, ?, ?)`,\n      args: [startedAt, finishedAt, 0, 0, JSON.stringify([fatal])],\n    });\n    throw err;\n  }\n\n  const placeholders = TRACKED_ACCOUNTS.map(() => \"?\").join(\",\");\n  const accountRows = await sqlite.execute({\n    sql: `SELECT username, account_id, display_name, last_post_id\n          FROM accounts WHERE username IN (${placeholders})`,\n    args: TRACKED_ACCOUNTS,\n  });\n\n  for (const row of accountRows.rows as any[]) {\n    accountsProcessed++;\n\n    if (!row.account_id) {\n      errors.push({ username: row.username, message: \"Account could not be resolved to an X user ID.\" });\n      continue;\n    }\n\n    try {\n      const { tweets, includes } = await fetchUserTweets(row.account_id, {\n        maxResults: MAX_RESULTS_PER_ACCOUNT,\n        sinceId: row.last_post_id,\n      });\n      const lookup = buildReferencedAuthorLookup(includes);\n\n      // With resetSinceId, newestId must not regress past whatever was\n      // already recorded (only relevant if this account happens to return\n      // zero tweets on the reset pass) — start from null here since we\n      // deliberately cleared last_post_id above, and let the normal\n      // max-of-fetched-ids logic below recompute it from what comes back.\n      let newestId: string | null = opts.resetSinceId ? null : (row.last_post_id ?? null);\n\n      for (const tweet of tweets) {\n        const normalized = normalizeTweet(\n          tweet,\n          { username: row.username, name: row.display_name ?? row.username },\n          lookup,\n        );\n\n        const result = await sqlite.execute({\n          sql: `INSERT INTO posts (\n                  post_id, account, username, posted_at, post_type,\n                  referenced_post_id, referenced_author_id, referenced_author_handle,\n                  referenced_author_name, referenced_author_source, text, mentioned_tickers,\n                  engagement_metrics, original_url, ingested_at\n                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n                ON CONFLICT(post_id) DO NOTHING`,\n          args: [\n            normalized.post_id,\n            normalized.account,\n            normalized.username,\n            normalized.posted_at,\n            normalized.post_type,\n            normalized.referenced_post_id,\n            normalized.referenced_author_id,\n            normalized.referenced_author_handle,\n            normalized.referenced_author_name,\n            normalized.referenced_author_source,\n            normalized.text,\n            JSON.stringify(normalized.mentioned_tickers),\n            JSON.stringify(normalized.engagement_metrics),\n            normalized.original_url,\n            normalized.ingested_at,\n          ],\n        });\n\n        if (result.rowsAffected > 0) {\n          postsIngested++;\n        } else if (normalized.referenced_author_id || normalized.referenced_author_handle) {\n          // Row already existed (e.g. this is a resetSinceId validation\n          // pass re-fetching already-known posts). Backfill ONLY the new\n          // provenance columns, and ONLY where they're currently NULL —\n          // never overwrite existing data (requirement 5).\n          const backfill = await sqlite.execute({\n            sql: `UPDATE posts SET\n                    referenced_author_id = COALESCE(referenced_author_id, ?),\n                    referenced_author_handle = COALESCE(referenced_author_handle, ?),\n                    referenced_author_name = COALESCE(referenced_author_name, ?),\n                    referenced_author_source = COALESCE(referenced_author_source, ?)\n                  WHERE post_id = ?\n                    AND referenced_author_id IS NULL\n                    AND referenced_author_handle IS NULL`,\n            args: [\n              normalized.referenced_author_id,\n              normalized.referenced_author_handle,\n              normalized.referenced_author_name,\n              normalized.referenced_author_source,\n              normalized.post_id,\n            ],\n          });\n          if (backfill.rowsAffected > 0) postsBackfilled++;\n        }\n        if (!newestId || BigInt(tweet.id) > BigInt(newestId)) newestId = tweet.id;\n      }\n\n      // Never let a resetSinceId validation pass regress the normal\n      // since_id cursor below what it already was.\n      if (opts.resetSinceId && row.last_post_id && (!newestId || BigInt(row.last_post_id) > BigInt(newestId))) {\n        newestId = row.last_post_id;\n      }\n\n      await sqlite.execute({\n        sql: `UPDATE accounts SET last_post_id = ?, last_collected_at = ? WHERE username = ?`,\n        args: [newestId, new Date().toISOString(), row.username],\n      });\n    } catch (err) {\n      errors.push({ username: row.username, message: (err as Error).message });\n    }\n\n    await sleep(REQUEST_SPACING_MS);\n  }\n\n  const finishedAt = new Date().toISOString();\n  await sqlite.execute({\n    sql: `INSERT INTO collector_runs (started_at, finished_at, accounts_processed, posts_ingested, errors)\n          VALUES (?, ?, ?, ?, ?)`,\n    args: [startedAt, finishedAt, accountsProcessed, postsIngested, JSON.stringify(errors)],\n  });\n\n  return { startedAt, finishedAt, accountsProcessed, postsIngested, postsBackfilled, errors };\n}\n",[],[181,328],"64888309-8621-4531-8a32-e8672209815f","candidateValidation.ts","e6480650-aac3-11f1-8be9-1607ee4eb77e",46,["D",1788789378356],"import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { fetchUserTweets } from \"./xapi.ts\";\nimport { buildReferencedAuthorLookup, normalizeTweet } from \"./normalize.ts\";\nimport { ensureSchema } from \"./schema.ts\";\n\nfunction sleep(ms: number) {\n  return new Promise((r) => setTimeout(r, ms));\n}\n\nconst REQUEST_SPACING_MS = 1200; // same spacing as the Core Trader collector\n\nexport interface CandidateTarget {\n  handle: string; // as recorded in discovered_accounts\n  x_account_id: string | null;\n  display_name: string | null;\n}\n\nexport interface CandidateValidationSummary {\n  runId: number;\n  startedAt: string;\n  finishedAt: string;\n  candidatesRequested: string[];\n  maxResultsPerCandidate: number;\n  apiRequestsMade: number;\n  postsRetrieved: number;\n  postsStoredNew: number;\n  errors: { handle: string; message: string }[];\n  perCandidate: Record<\n    string,\n    { postsRetrieved: number; postsStoredNew: number; error?: string }\n  >;\n}\n\n// Phase 3.2: Discovered Trader Candidate Validation — controlled, limited\n// collection for ONLY the explicitly-passed candidate handles (never \"all\n// discovered candidates\"). One X API request per candidate, no pagination,\n// no since_id chaining (this is a one-shot recent-timeline sample, not an\n// ongoing incremental collector like the Core Trader one). Results are\n// written to candidate_validation_posts — never to `posts` — so Core\n// Trader provenance and candidate provenance can never blend.\nexport async function runCandidateValidation(\n  targets: CandidateTarget[],\n  opts: { maxResults?: number } = {},\n): Promise {\n  await ensureSchema();\n  const maxResults = Math.min(Math.max(opts.maxResults ?? 30, 5), 100);\n  const startedAt = new Date().toISOString();\n  const errors: { handle: string; message: string }[] = [];\n  const perCandidate: CandidateValidationSummary[\"perCandidate\"] = {};\n  let apiRequestsMade = 0;\n  let postsRetrieved = 0;\n  let postsStoredNew = 0;\n\n  // Log the run first so a runId exists to tag rows with, and so a crash\n  // partway through still leaves an auditable record (same pattern as\n  // collector_runs).\n  const runInsert = await sqlite.execute({\n    sql: `INSERT INTO candidate_validation_runs\n            (started_at, candidates_requested, max_results_per_candidate)\n          VALUES (?, ?, ?)`,\n    args: [startedAt, JSON.stringify(targets.map((t) => t.handle)), maxResults],\n  });\n  const runId = Number(runInsert.lastInsertRowid);\n\n  for (const target of targets) {\n    perCandidate[target.handle] = { postsRetrieved: 0, postsStoredNew: 0 };\n\n    if (!target.x_account_id) {\n      const msg = \"No resolved x_account_id on file (discovered_accounts) — cannot fetch a timeline.\";\n      errors.push({ handle: target.handle, message: msg });\n      perCandidate[target.handle].error = msg;\n      continue;\n    }\n\n    try {\n      const { tweets, includes } = await fetchUserTweets(target.x_account_id, {\n        maxResults,\n        sinceId: null, // one-shot sample, not an incremental collector\n      });\n      apiRequestsMade++;\n      const lookup = buildReferencedAuthorLookup(includes);\n\n      let storedNew = 0;\n      for (const tweet of tweets) {\n        const normalized = normalizeTweet(\n          tweet,\n          { username: target.handle, name: target.display_name ?? target.handle },\n          lookup,\n        );\n\n        const result = await sqlite.execute({\n          sql: `INSERT INTO candidate_validation_posts (\n                  post_id, candidate_handle, posted_at, post_type,\n                  referenced_post_id, referenced_author_id, referenced_author_handle,\n                  referenced_author_name, referenced_author_source, text, mentioned_tickers,\n                  engagement_metrics, original_url, ingested_at, validation_run_id\n                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n                ON CONFLICT(post_id) DO NOTHING`,\n          args: [\n            normalized.post_id,\n            target.handle,\n            normalized.posted_at,\n            normalized.post_type,\n            normalized.referenced_post_id,\n            normalized.referenced_author_id,\n            normalized.referenced_author_handle,\n            normalized.referenced_author_name,\n            normalized.referenced_author_source,\n            normalized.text,\n            JSON.stringify(normalized.mentioned_tickers),\n            JSON.stringify(normalized.engagement_metrics),\n            normalized.original_url,\n            normalized.ingested_at,\n            runId,\n          ],\n        });\n        if (result.rowsAffected > 0) storedNew++;\n      }\n\n      postsRetrieved += tweets.length;\n      postsStoredNew += storedNew;\n      perCandidate[target.handle].postsRetrieved = tweets.length;\n      perCandidate[target.handle].postsStoredNew = storedNew;\n    } catch (err) {\n      const msg = (err as Error).message;\n      errors.push({ handle: target.handle, message: msg });\n      perCandidate[target.handle].error = msg;\n    }\n\n    await sleep(REQUEST_SPACING_MS);\n  }\n\n  const finishedAt = new Date().toISOString();\n  await sqlite.execute({\n    sql: `UPDATE candidate_validation_runs\n          SET finished_at = ?, api_requests_made = ?, posts_retrieved = ?, posts_stored_new = ?, errors = ?\n          WHERE id = ?`,\n    args: [finishedAt, apiRequestsMade, postsRetrieved, postsStoredNew, JSON.stringify(errors), runId],\n  });\n\n  return {\n    runId,\n    startedAt,\n    finishedAt,\n    candidatesRequested: targets.map((t) => t.handle),\n    maxResultsPerCandidate: maxResults,\n    apiRequestsMade,\n    postsRetrieved,\n    postsStoredNew,\n    errors,\n    perCandidate,\n  };\n}\n",[],[181,337],"067fae87-6b16-4583-8bab-8247de5f9163","analyze.ts","341f4876-ac0d-11f1-a5c8-1607ee4eb77e",92,["D",1788939924216],"// lib/analyze.ts — Claude-based post-analysis for the Market Discovery &\n// Consensus pipeline. Fills ONLY the existing analytical columns that\n// lib/consensus.ts already consumes. Never touches raw X data (text,\n// post_id, username, posted_at, engagement metrics, mentioned_tickers),\n// never inserts/drops rows, and is fully idempotent: it only ever reads\n// rows where analyzed_at IS NULL and only ever writes via an\n// analyzed_at IS NULL-guarded UPDATE.\n//\n// This file is intentionally NOT invoked at import — it exports pure\n// functions the cron trigger calls. The ANTHROPIC_API_KEY secret is read\n// from the environment at run time and is never logged, returned, or\n// leaked into any response.\n\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\n\n// Production model — locked explicitly in code and validated via the\n// controlled single-post test. Kept as a constant (no env override) so it\n// can never silently drift; change it deliberately with a redeploy.\nconst MODEL = \"claude-sonnet-4-5\";\n\n// Small, cost/prompt controlled batch size — one API call per batch of new\n// posts, so token usage stays bounded and total calls stay low, and no\n// single batch can exceed the ~45s targeted production runtime.\nexport const BATCH_SIZE = 10;\n\n// Hard, deterministic cap on posts analyzed per cron run. Combined with\n// BATCH_SIZE this bounds API calls to MAX_ANALYZE_PER_RUN / BATCH_SIZE = 2\n// calls maximum per run, keeping the run safely inside the 1-minute plan\n// limit. Posts left over (analyzed_at IS NULL) are naturally picked up on the\n// next 3-hour run — idempotent, no backfill, no historical reprocessing.\nexport const MAX_ANALYZE_PER_RUN = 20;\n\nconst VALID_STANCES = new Set([\"Bullish\", \"Bearish\", \"Unclear\"]);\nconst VALID_CONFIDENCE = new Set([\"High\", \"Medium\", \"Low\"]);\n\nexport interface EligiblePost {\n  post_id: string;\n  post_type: string; // original | reply | quote | repost\n  text: string;\n  mentioned_tickers: string; // JSON array string (mechanical cashtags)\n}\n\nexport interface AnalyzeResult {\n  postsAnalyzed: number;\n  postsSkipped: number;\n  analysisErrors: number;\n  /** how many eligible rows were found at the start of this run */\n  eligible: number;\n}\n\nasync function callClaude(\n  batch: EligiblePost[],\n): Promise>> {\n  const key = Deno.env.get(\"ANTHROPIC_API_KEY\");\n  if (!key) {\n    throw new Error(\n      \"ANTHROPIC_API_KEY is not set. Add it as a Val Town environment variable before analysis can run.\",\n    );\n  }\n\n  // Compact representation: only what classification needs. No historical\n  // context, no engagement metrics, no URLs.\n  const compact = batch.map((p) => ({\n    post_id: p.post_id,\n    post_type: p.post_type,\n    text: p.text,\n    mentioned_tickers: JSON.parse(p.mentioned_tickers || \"[]\"),\n  }));\n\n  const system =\n    \"You classify trader posts feeding a market-consensus engine. Follow these rules exactly. \" +\n    \"1) Respond with strict machine-readable JSON only — an array, one object per post_id. No prose, no markdown. \" +\n    `2) Each object: {\"post_id\":string,\"stance\":\"Bullish\"|\"Bearish\"|\"Unclear\",\"evidence\":string|null,\"thesis\":string|null,\"catalyst\":string|null,\"time_horizon\":string|null,\"position_disclosure\":string|null,\"stance_tickers\":string[],\"semantic_tickers\":[{\"ticker\":string,\"company_name\":string,\"evidence\":string,\"confidence\":\"High\"|\"Medium\"|\"Low\",\"recognition_reason\":string}]}. ` +\n    \"3) stance means an EXPLICIT directional claim. Use Unclear when the post has no explicit directional view. \" +\n    \"Not being mentioned is NOT neutral/negative: absence of a claim is never a vote. Quote text exactly as evidence when a claim exists. \" +\n    \"4) NEVER infer ownership, buying, selling, shorting, or holding unless the post explicitly states it; never fabricate a position. \" +\n    \"5) Reposts (post_type=\\\"repost\\\") must have stance \\\"Unclear\\\" and stance_tickers [] — they may not cast an independent directional vote. \" +\n    \"6) stance_tickers: only tickers (from the provided mentioned_tickers, mechanical cashtags) that this specific stance applies to; [] if none. \" +\n    \"7) semantic_tickers: recognize companies/entities referenced WITHOUT a $cashtag. Keep these fully separate from mechanical cashtags. \" +\n    \"   confidence High only when the mapping is unambiguous and supports consensus; Medium/Low are reported but never drive votes. \" +\n    \"8) Keep unified thesis/catalyst/time_horizon/evidence brief; null when not present. position_disclosure only when the post explicitly states a position/ownership. \" +\n    \"9) Preserve Original/Reply/Quote/Repost as given — do not change post_type.\";\n\n  const res = await fetch(\"https://api.anthropic.com/v1/messages\", {\n    method: \"POST\",\n    headers: {\n      \"content-type\": \"application/json\",\n      \"x-api-key\": key,\n      \"anthropic-version\": \"2023-06-01\",\n    },\n    body: JSON.stringify({\n      model: MODEL,\n      max_tokens: 4000,\n      system,\n      messages: [\n        {\n          role: \"user\",\n          content:\n            \"Analyze exactly these posts and return the JSON array (only the array):\\n\" +\n            JSON.stringify(compact),\n        },\n      ],\n    }),\n  });\n\n  if (!res.ok) {\n    const bodyText = await res.text();\n    throw new Error(`Anthropic API error ${res.status}: ${bodyText.slice(0, 400)}`);\n  }\n\n  const json = await res.json();\n  const text = json?.content?.[0]?.text;\n  if (typeof text !== \"string\" || text.trim().length === 0) {\n    throw new Error(\"Anthropic API returned an empty/unexpected response.\");\n  }\n  // Defensively strip any leading/trailing markdown fence the model may add.\n  const cleaned = text.trim().replace(/^```(?:json)?\\s*/i, \"\").replace(/\\s*```$/i, \"\");\n  const parsed = JSON.parse(cleaned);\n  if (!Array.isArray(parsed)) {\n    throw new Error(\"Anthropic API response was not a JSON array.\");\n  }\n  return parsed as Array>;\n}\n\n// Strict, field-by-field validation of a single analyzed post object. Returns\n// a validated write record or null (with a reason) if the object is invalid.\nfunction validateAnalysis(\n  raw: Record,\n  knownIds: Set,\n): { record: Record; error: string | null } {\n  const idErr = (msg: string) => ({ record: null as never, error: msg });\n\n  const postId = raw.post_id;\n  if (typeof postId !== \"string\" || !knownIds.has(postId)) {\n    return idErr(`post_id \"${String(postId)}\" is missing or not an analyzed candidate.`);\n  }\n\n  const stance = raw.stance === null || raw.stance === undefined ? \"Unclear\" : raw.stance;\n  if (typeof stance !== \"string\" || !VALID_STANCES.has(stance)) {\n    return idErr(`post ${postId}: invalid stance \"${String(raw.stance)}\".`);\n  }\n\n  const strField = (v: unknown, max = 2000): string | null => {\n    if (v === null || v === undefined) return null;\n    if (typeof v !== \"string\") throw new Error(`must be a string or null`);\n    return v.length > max ? null : v; // truncate-safe: reject over-long instead of storing noise\n  };\n  const arrStr = (v: unknown): string => {\n    if (v === null || v === undefined) return \"[]\";\n    if (!Array.isArray(v) || !v.every((x) => typeof x === \"string\" && x.length > 0)) {\n      throw new Error(`must be an array of non-empty ticker strings`);\n    }\n    return JSON.stringify([...new Set(v.map((t) => (t as string).toUpperCase()))]);\n  };\n  const arrSem = (v: unknown): string => {\n    if (v === null || v === undefined) return \"[]\";\n    if (!Array.isArray(v) || v.length === 0) return \"[]\";\n    const out: unknown[] = [];\n    for (const s of v) {\n      if (!s || typeof s !== \"object\") throw new Error(`semantic entry must be an object`);\n      const o = s as Record;\n      if (typeof o.ticker !== \"string\" || typeof o.company_name !== \"string\" ||\n          typeof o.evidence !== \"string\" || typeof o.recognition_reason !== \"string\" ||\n          typeof o.confidence !== \"string\" || !VALID_CONFIDENCE.has(o.confidence)) {\n        throw new Error(`semantic entry must have ticker, company_name, evidence, confidence (High|Medium|Low), recognition_reason`);\n      }\n      out.push({\n        ticker: (o.ticker as string).toUpperCase(),\n        company_name: o.company_name,\n        evidence: o.evidence,\n        confidence: o.confidence,\n        recognition_reason: o.recognition_reason,\n      });\n    }\n    return JSON.stringify(out);\n  };\n\n  try {\n    return {\n      record: {\n        post_id: postId,\n        explicit_stance: stance,\n        evidence: strField(raw.evidence),\n        thesis: strField(raw.thesis),\n        catalyst: strField(raw.catalyst),\n        time_horizon: strField(raw.time_horizon, 500),\n        position_disclosure: strField(raw.position_disclosure, 1000),\n        stance_tickers: arrStr(raw.stance_tickers),\n        semantic_tickers: arrSem(raw.semantic_tickers),\n        analyzed_at: new Date().toISOString(),\n      },\n      error: null,\n    };\n  } catch (err) {\n    return idErr(`post ${postId}: ${(err as Error).message}.`);\n  }\n}\n\n/**\n * Analyze every eligible (analyzed_at IS NULL) post that is still unanalyzed,\n * in small batches, up to MAX_ANALYZE_PER_RUN. Idempotent end-to-end:\n *  - never reads analyzed rows\n *  - re-checks analyzed_at IS NULL right before each UPDATE\n *  - a failed/validated-out post is skipped in-place and never corrupts the batch\n *  - consecutive runs only ever retry rows that are still analyzed_at IS NULL\n */\nexport async function analyzeUnanalyzedPosts(\n  opts: { limit?: number; timeBudgetMs?: number } = {},\n): Promise {\n  await (await import(\"./schema.ts\")).ensureSchema();\n\n  // Optional explicit cap (used for controlled single-post tests); always\n  // bounded above by MAX_ANALYZE_PER_RUN so production cost stays capped.\n  const limit = Math.min(opts.limit ?? MAX_ANALYZE_PER_RUN, MAX_ANALYZE_PER_RUN);\n\n  // Optional wall-clock budget: stop scheduling new batches once it nears the\n  // budget, leaving any remaining analyzed_at IS NULL rows for the next run.\n  // Never aborts an already-in-flight batch — small batches keep that bounded.\n  const startedAt = Date.now();\n  const timeBudgetMs = opts.timeBudgetMs ?? Number.POSITIVE_INFINITY;\n  const withinBudget = () => Date.now() - startedAt < timeBudgetMs;\n\n  const eligibleRes = await sqlite.execute({\n    sql: `SELECT post_id, post_type, text, mentioned_tickers\n          FROM posts\n          WHERE analyzed_at IS NULL\n          ORDER BY posted_at ASC\n          LIMIT ?`,\n    args: [limit],\n  });\n  const eligible = (eligibleRes.rows as unknown as EligiblePost[]).map((r) => ({\n    post_id: r.post_id,\n    post_type: r.post_type,\n    text: r.text,\n    mentioned_tickers: r.mentioned_tickers ?? \"[]\",\n  }));\n\n  if (eligible.length === 0) {\n    return { postsAnalyzed: 0, postsSkipped: 0, analysisErrors: 0, eligible: 0 };\n  }\n\n  const knownIds = new Set(eligible.map((p) => p.post_id));\n  let analyzed = 0;\n  let skipped = 0;\n  let analysisErrors = 0;\n\n  for (let i = 0; i < eligible.length; i += BATCH_SIZE) {\n    // Stop scheduling new batches before the time budget is exhausted;\n    // remaining posts stay analyzed_at IS NULL for the next scheduled run.\n    if (!withinBudget()) break;\n    const batch = eligible.slice(i, i + BATCH_SIZE);\n    let parsed: Array>;\n    try {\n      parsed = await callClaude(batch);\n    } catch (err) {\n      // Whole batch failed (e.g. API down, auth, malformed response). Count\n      // the batch as skipped/errors and move on — never crash the cron run.\n      analysisErrors += 1;\n      skipped += batch.length;\n      console.error(`analysis batch failed (${(err as Error).message}) — ${batch.length} post(s) skipped.`);\n      continue;\n    }\n\n    const byId = new Map(parsed.map((p) => [p.post_id, p]));\n    for (const candidate of batch) {\n      const raw = byId.get(candidate.post_id);\n      if (!raw) {\n        analysisErrors += 1;\n        skipped += 1;\n        console.error(`analysis missing result for post ${candidate.post_id} — skipped.`);\n        continue;\n      }\n      const { record, error } = validateAnalysis(raw, new Set([candidate.post_id]));\n      if (error || !record) {\n        analysisErrors += 1;\n        skipped += 1;\n        console.error(`analysis validation failed for post ${candidate.post_id}: ${error ?? \"unknown\"}`);\n        continue;\n      }\n\n      // Idempotency guard: only write if still unanalyzed.\n      const res = await sqlite.execute({\n        sql: `UPDATE posts SET\n                explicit_stance = ?, evidence = ?, thesis = ?, catalyst = ?,\n                time_horizon = ?, position_disclosure = ?, stance_tickers = ?,\n                semantic_tickers = ?, analyzed_at = ?\n              WHERE post_id = ? AND analyzed_at IS NULL`,\n        args: [\n          record.explicit_stance, record.evidence, record.thesis, record.catalyst,\n          record.time_horizon, record.position_disclosure, record.stance_tickers,\n          record.semantic_tickers, record.analyzed_at, record.post_id,\n        ],\n      });\n      if (res.rowsAffected > 0) analyzed++;\n      else skipped++; // taken by a concurrent run between select and update\n    }\n  }\n\n  return { postsAnalyzed: analyzed, postsSkipped: skipped, analysisErrors, eligible: eligible.length };\n}\n",[],[181,345],"6b69522e-ee08-4e2e-aa5c-3074df0d9e73","untitled-folder-5076","a0f921cc-ab88-11f1-8e02-1607ee4eb77e",71,["D",1788873872966],[],[181,353],["D",1788705478068],[197,198,199,200,201,202,203,204,205,206,207,208,209,210,211],[181],["D",1788705462303],["D",1789485645684],{"_101":100,"_109":105},{"_101":100,"_102":103,"_104":105,"_18":56,"_106":107,"_108":25},{"_13":90,"_32":91,"_44":43,"_92":45,"_93":-5,"_94":-5,"_95":-5,"_96":-5,"_97":-5,"_98":46,"_99":100},"ctx",{"_83":-5,"_129":400,"_401":402,"_403":404,"_405":25,"_406":25,"_407":25,"_408":60,"_409":-5,"_410":-7,"_411":-7,"_412":64,"_413":25,"_414":25,"_415":-5,"_416":25,"_417":25,"_418":25,"_419":-5},"ENV",{"_371":372,"_373":374,"_375":91,"_376":377,"_378":379,"_380":381,"_382":383,"_384":385,"_386":387,"_388":389,"_390":391,"_392":393,"_394":395,"_396":397,"_398":399},"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",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]
Diff · v122
