Market Discovery & Consensus

X Market Intelligence • Updated every 3 hours

Last updated: —

Tracked accounts: —

Total analyzed posts: —

Consensus tickers: —

\n \n \n

\n \n \n \n

\n\n

Market Consensus Overview

\n\n

Top Consensus Stocks click a column to sort • unique independent accounts, not raw post count

\n \n \n \n

\n\n

Consensus — Agreement

Consensus — Disagreement

\n\n

Attention & Emerging Ideas attention/discovery only, NOT investment recommendations

\n \n \n \n

Ticker	Recent Mentions	Independent Accounts	Total Mentions	Change	Latest View

\n\n

Trader Monitor tracked X accounts

\n \n \n \n

Account	Latest Activity	Stocks Discussed	B	Be	U	Latest Thesis

\n\n

Recent Market Views chronological feed

\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",[],[249,241],"ui","3c6b10d0-0bb9-433d-9136-7a7b7431448d",["D",1788938429870],"directory",[200],[249],"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",[],[167,256],"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",[],[167,264],"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",[],[167,272],"3bd6b016-b42f-4db3-bc92-78e882cac64c","productionRefresh.ts","c8efec38-ad09-11f1-b680-1607ee4eb77e",143,["D",1789039296228],"// lib/productionRefresh.ts — Production orchestration for the Market Data\n// layer (Alpha Vantage fundamentals/valuation + Eulerpool price/momentum),\n// approved 2026-09-10 after separate NVDA-only validation of each provider\n// (see lib/alphavantage.ts and lib/eulerpool.ts). This file is the ONLY\n// place that decides WHICH tickers get refreshed and enforces the\n// once-daily Alpha Vantage cap; the provider files themselves stay\n// call-scoped and stateless about scheduling.\n//\n// Explicitly OUT OF SCOPE here, by design, matching the approved\n// architecture: FMP (never called), AnySearch as a scoring input (stays a\n// separate, on-demand, non-persisted enrichment lookup — see\n// fetchAnySearchEnrichment in lib/anysearch.ts), Eulerpool 20D/50D (no\n// column for 50D; confirmed capped at ~8-9 daily bars on this key/plan\n// anyway), and Capital Flow (left NULL — nothing populates avg_volume/flow\n// in this pipeline).\n//\n// NEVER touches cron.ts, the X collector, Anthropic/analysis, posts,\n// ticker_consensus, or discovery_candidates/discovery_runs directly — this\n// file only writes to market_data (via the provider files' merge-safe\n// writers) and its own small provider_refresh_log table. The EXISTING,\n// already-wired-in computeDiscovery() (lib/discoveryV2.ts) reads\n// market_data on its own next lazy recompute (15-minute freshness window)\n// — this file does not force a recompute, so the existing normalized\n// scoring logic and its cadence are left completely untouched.\n\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { WATCHLIST } from \"./discoveryV2.ts\";\nimport { ensureMarketDataSchema } from \"./marketData.ts\";\nimport { refreshAlphaVantageFundamentals, type AlphaVantageRefreshResult } from \"./alphavantage.ts\";\nimport { refreshEulerpoolPriceMomentum, type EulerpoolRefreshResult } from \"./eulerpool.ts\";\n\nconst ALPHAVANTAGE_MAX_PER_RUN = 20; // free tier: 25/day — leaves headroom for a manual re-run same day if ever needed\nconst EULERPOOL_MAX_PER_RUN = 40;\nconst ALPHAVANTAGE_MIN_GAP_MS = 20 * 60 * 60 * 1000; // ~20h — hard-enforces \"no more than once daily\" regardless of how often this route is called\n\nasync function ensureRefreshLogSchema(): Promise {\n await sqlite.batch([\n { sql: `CREATE TABLE IF NOT EXISTS provider_refresh_log (\n provider TEXT PRIMARY KEY, last_run_at TEXT, tickers_refreshed TEXT, calls_used INTEGER, notes TEXT\n )` },\n ]);\n}\n\nasync function getLastRunAt(provider: string): Promise {\n await ensureRefreshLogSchema();\n const res = await sqlite.execute({ sql: `SELECT last_run_at FROM provider_refresh_log WHERE provider = ?`, args: [provider] });\n return (res.rows[0] as any)?.last_run_at ?? null;\n}\n\nasync function recordRun(provider: string, tickersRefreshed: string[], callsUsed: number, notes: string): Promise {\n await ensureRefreshLogSchema();\n await sqlite.execute({\n sql: `INSERT INTO provider_refresh_log (provider, last_run_at, tickers_refreshed, calls_used, notes)\n VALUES (?,?,?,?,?)\n ON CONFLICT(provider) DO UPDATE SET last_run_at=excluded.last_run_at, tickers_refreshed=excluded.tickers_refreshed,\n calls_used=excluded.calls_used, notes=excluded.notes`,\n args: [provider, new Date().toISOString(), JSON.stringify(tickersRefreshed), callsUsed, notes.slice(0, 1000)],\n });\n}\n\n// Prioritization: the permanent CORE 15 watchlist first (always kept fresh),\n// then Tier A/B Discovery candidates by score (the names actually surfacing\n// as interesting right now), deduplicated, bounded to `limit`. Never\n// prioritizes Tier C or ETFs/baskets differently — a plain, transparent\n// ordering, not a hidden ranking model.\nexport async function getPrioritizedTickers(limit: number): Promise {\n await ensureMarketDataSchema();\n const core = [...WATCHLIST];\n let tiered: string[] = [];\n try {\n const res = await sqlite.execute(`SELECT ticker FROM discovery_candidates WHERE tier IN ('A','B') ORDER BY score_normalized DESC LIMIT 60`);\n tiered = (res.rows as any[]).map((r) => String(r.ticker).toUpperCase());\n } catch {\n // discovery_candidates may not exist yet on a fresh val — degrade to CORE-only, never throw.\n }\n const seen = new Set();\n const ordered: string[] = [];\n for (const t of [...core, ...tiered]) {\n const u = t.toUpperCase();\n if (!seen.has(u)) {\n seen.add(u);\n ordered.push(u);\n }\n }\n return ordered.slice(0, limit);\n}\n\nexport interface ProductionRefreshReport {\n generated_at: string;\n prioritized_tickers: string[];\n alphavantage: {\n ran: boolean;\n skipped_reason: string | null;\n tickers_attempted: string[];\n tickers_succeeded: string[];\n tickers_skipped_fresh: string[];\n tickers_failed: { ticker: string; reason: string }[];\n calls_used: number;\n errors: string[];\n };\n eulerpool: {\n ran: boolean;\n tickers_attempted: string[];\n tickers_succeeded: string[];\n tickers_failed: { ticker: string; reason: string }[];\n calls_used: number;\n errors: string[];\n };\n}\n\n// The single production entry point. `limit` bounds BOTH providers' ticker\n// lists (Alpha Vantage additionally hard-caps at 20 regardless of `limit`).\n// `forceAlphaVantage` exists only for the one bounded validation this\n// integration requires — production callers should never need it, since\n// the once-daily gate self-manages timing.\nexport async function runProductionMarketDataRefresh(\n opts: { limit?: number; forceAlphaVantage?: boolean } = {},\n): Promise {\n await ensureRefreshLogSchema();\n const limit = Math.min(Math.max(opts.limit ?? ALPHAVANTAGE_MAX_PER_RUN, 1), EULERPOOL_MAX_PER_RUN);\n const prioritized = await getPrioritizedTickers(limit);\n const avTickers = prioritized.slice(0, Math.min(limit, ALPHAVANTAGE_MAX_PER_RUN));\n const epTickers = prioritized.slice(0, limit);\n\n const report: ProductionRefreshReport = {\n generated_at: new Date().toISOString(),\n prioritized_tickers: prioritized,\n alphavantage: { ran: false, skipped_reason: null, tickers_attempted: [], tickers_succeeded: [], tickers_skipped_fresh: [], tickers_failed: [], calls_used: 0, errors: [] },\n eulerpool: { ran: false, tickers_attempted: [], tickers_succeeded: [], tickers_failed: [], calls_used: 0, errors: [] },\n };\n\n // ---- Alpha Vantage: hard once-daily gate (quota protection) ----\n const lastAv = await getLastRunAt(\"alphavantage_fundamentals\");\n const now = Date.now();\n if (!opts.forceAlphaVantage && lastAv && now - new Date(lastAv).getTime() < ALPHAVANTAGE_MIN_GAP_MS) {\n const hoursAgo = Math.round((now - new Date(lastAv).getTime()) / 3600000);\n report.alphavantage.skipped_reason = `Already refreshed ${hoursAgo}h ago (last run ${lastAv}); Alpha Vantage is capped to once/day for quota protection. No calls made this run.`;\n } else {\n report.alphavantage.ran = true;\n report.alphavantage.tickers_attempted = avTickers;\n const avResult: AlphaVantageRefreshResult = await refreshAlphaVantageFundamentals(avTickers);\n report.alphavantage.tickers_succeeded = avResult.succeeded;\n report.alphavantage.tickers_skipped_fresh = avResult.skipped_fresh;\n report.alphavantage.tickers_failed = avResult.failed;\n report.alphavantage.calls_used = avResult.callsUsed;\n report.alphavantage.errors = avResult.errors;\n await recordRun(\n \"alphavantage_fundamentals\",\n avResult.succeeded,\n avResult.callsUsed,\n `succeeded=${avResult.succeeded.length} skipped_fresh=${avResult.skipped_fresh.length} failed=${avResult.failed.length}`,\n );\n }\n\n // ---- Eulerpool: no documented daily quota; still bounded per run ----\n report.eulerpool.ran = true;\n report.eulerpool.tickers_attempted = epTickers;\n const epResult: EulerpoolRefreshResult = await refreshEulerpoolPriceMomentum(epTickers);\n report.eulerpool.tickers_succeeded = epResult.succeeded;\n report.eulerpool.tickers_failed = epResult.failed;\n report.eulerpool.calls_used = epResult.callsUsed;\n report.eulerpool.errors = epResult.errors;\n await recordRun(\n \"eulerpool_price_momentum\",\n epResult.succeeded,\n epResult.callsUsed,\n `succeeded=${epResult.succeeded.length} failed=${epResult.failed.length}`,\n );\n\n return report;\n}\n",[],[167,280],"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",[],[167,288],"cc44ad33-289d-4ccd-8c13-f83c709eaa62","new-file-4892.ts","9bcf9d98-ab88-11f1-b30f-1607ee4eb77e",70,["D",1788873864298],"",[],[167,296],"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",[],[167,304],"56c4728d-653a-4bf3-8af3-7984f03f2f9b","marketData.ts","ee7a8de4-ac34-11f1-ac60-1607ee4eb77e",123,["D",1788960949008],"// 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, now /stable/quote?symbol=... per the verified migration) before\n  // spending any further calls. If the key is rejected outright (401) or\n  // the plan denies it (403), stop immediately and report — per the\n  // instruction for this run, do not retry and do not call the remaining\n  // endpoints.\n  const quoteRaw = await call(\"quote\", `https://financialmodelingprep.com/stable/quote?symbol=${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 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 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 2 remaining endpoints (ratios, financial-growth).\");\n\n  // Step 2: authentication confirmed — 2 remaining calls (profile is no\n  // longer fetched: verified to contribute zero fields this schema needs —\n  // see lib/marketData.ts header comment in fetchData). Any endpoint that\n  // comes back 403 is a plan restriction (its fields stay NULL, not zero);\n  // reported per-endpoint below rather than aborting the whole test.\n  const q = Array.isArray(quoteRaw) ? quoteRaw[0] ?? {} : {};\n  const ratiosRaw = await call(\"ratios\", `https://financialmodelingprep.com/stable/ratios?symbol=${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/stable/financial-growth?symbol=${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.changePercentage === \"number\" ? q.changePercentage : null,\n    price_change_1w: null,\n    price_change_1m: null,\n    pe_ratio: typeof ratios.priceEarningsRatio === \"number\" ? ratios.priceEarningsRatio : null,\n    pb_ratio: typeof ratios.priceToBookRatio === \"number\" ? ratios.priceToBookRatio : null,\n    market_cap: typeof q.marketCap === \"number\" ? q.marketCap : null,\n    ev: null,\n    revenue_growth: typeof growth.revenueGrowth === \"number\" ? growth.revenueGrowth * 100 : null,\n    gross_margin: typeof ratios.grossProfitMargin === \"number\" ? ratios.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",[],[167,313],"7d9e251e-80a0-4227-b89f-7042350e1018","eulerpool.ts","3668ab18-acf8-11f1-869b-1607ee4eb77e",141,["D",1789039225650],"// lib/eulerpool.ts — Controlled, single-ticker (NVDA-only) validation of\n// Eulerpool as a candidate replacement for Alpha Vantage on Price /\n// Historical Price / Price Momentum ONLY. Explicitly out of scope: FMP,\n// AnySearch, X, Anthropic, cron, the full Discovery pipeline/universe, and\n// any ticker other than NVDA.\n//\n// MINIMUM-CALLS DESIGN: a single request to the OHLCV charting endpoint\n// (resolution=D, ~110 calendar days back) returns enough daily closes to\n// derive latest price AND 1D/5D/20D/50D returns in ONE call — no separate\n// \"latest quote\" call is needed, since the most recent daily close in the\n// OHLCV series already serves as \"latest available price\".\n//\n// Docs consulted before writing this (2026-09-10): https://eulerpool.com/developers,\n// https://eulerpool.com/developers/api/market/quotes/latest, and\n// https://eulerpool.com/developers/api/charting/ohlcv. Per those pages:\n//   - Base host: https://api.eulerpool.com\n//   - OHLCV endpoint: GET /api/1/charting/ohlcv/{identifier}?resolution=D&from=&to=\n//     (identifier accepts a plain ticker, e.g. NVDA). Response shape shown in\n//     docs: { t: number[], o: number[], h: number[], l: number[], c: number[], v: number[] }.\n//   - Auth: \"Authenticate with a query parameter (?token=YOUR_API_KEY) or an\n//     Authorization: Bearer header.\" This file uses the Bearer header. Per\n//     the minimum-calls instruction, if the Bearer header is rejected this\n//     test does NOT retry with the query-param form in the same run — it\n//     stops and reports the auth failure.\n//   - The docs pages did not show a populated example response (only an\n//     empty-array placeholder), so array ordering (ascending vs descending\n//     by time) is NOT assumed — this code detects and normalizes it from\n//     the returned timestamps themselves.\n//\n// market_data SCHEMA NOTE: the existing table (lib/marketData.ts) has\n// columns for price_change_1d / _1w / _1m only — there is no _20d or _50d\n// column. Per \"do not modify database schema\", this file does NOT add one.\n// 5D and 20D returns are stored into the existing _1w/_1m columns (the\n// closest existing approximation) and clearly labeled as such in the\n// report. 50D return has NO schema column to persist into — it is\n// computed and reported here as informational only, never stored, exactly\n// like the eps_growth_pct precedent in lib/alphavantage.ts.\n//\n// PERSISTENCE IS GENUINELY MERGE-SAFE: unlike persistMarketData() in\n// lib/marketData.ts (which overwrites every column, nulling out anything\n// not passed in), this file reads the existing NVDA row first and issues\n// an UPDATE that touches ONLY price, price_change_1d, price_change_1w,\n// price_change_1m, source, and fetched_at — every other column (pe_ratio,\n// pb_ratio, market_cap, ev, revenue_growth, gross_margin, roe,\n// debt_to_equity, avg_volume, flow) is left completely untouched by the\n// SQL statement itself, so any existing Alpha Vantage fundamental/\n// valuation values survive unconditionally.\n\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { ensureMarketDataSchema, scoreMarketDimensions, type MarketData } from \"./marketData.ts\";\n\nconst EULERPOOL_BASE = \"https://api.eulerpool.com\";\n\nfunction getKey(): string | null {\n  const k = Deno.env.get(\"EULERPOOL_API_KEY\");\n  return k && k.length > 0 ? k : null;\n}\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 interface EulerpoolReturnResult {\n  label: string; // \"1D\" | \"5D\" | \"20D\" | \"50D\"\n  pct: number | null;\n  bars_back: number;\n  available: boolean;\n  note: string;\n}\n\nexport interface EulerpoolTestReport {\n  ticker: \"NVDA\";\n  provider: \"eulerpool\";\n  provider_configured: boolean;\n  auth: { header_used: string; status: number | null; ok: boolean; result: string } | null;\n  api_calls: { endpoint: string; status: number | null; ok: boolean; note: string }[];\n  bars_returned: number;\n  earliest_bar_date: string | null;\n  latest_bar_date: string | null;\n  latest_price: number | null;\n  returns: EulerpoolReturnResult[];\n  momentum_dimension_score: number | null; // via existing scoreMarketDimensions(), reporting-only\n  dimensions_reliably_supplied: string[];\n  dimensions_not_supplied: string[];\n  merge_safe_write: {\n    attempted: boolean;\n    row_existed_before: boolean;\n    before: Record | null;\n    after: Record | null;\n    columns_written: string[];\n    columns_preserved_unchanged: string[];\n  };\n  errors: string[];\n}\n\nfunction computeReturn(closes: number[], barsBack: number, label: string): EulerpoolReturnResult {\n  const n = closes.length;\n  const idxNow = n - 1;\n  const idxThen = n - 1 - barsBack;\n  if (idxThen < 0) {\n    return { label, pct: null, bars_back: barsBack, available: false, note: `insufficient history: need ${barsBack + 1} daily bars, got ${n}` };\n  }\n  const now = closes[idxNow];\n  const then = closes[idxThen];\n  if (typeof now !== \"number\" || typeof then !== \"number\" || !Number.isFinite(now) || !Number.isFinite(then) || then === 0) {\n    return { label, pct: null, bars_back: barsBack, available: false, note: \"non-numeric or zero close in series\" };\n  }\n  return { label, pct: Math.round(((now - then) / then) * 100 * 100) / 100, bars_back: barsBack, available: true, note: \"ok\" };\n}\n\nexport async function runNvdaEulerpoolTest(): Promise {\n  const ticker = \"NVDA\";\n  const key = getKey();\n  const report: EulerpoolTestReport = {\n    ticker, provider: \"eulerpool\", provider_configured: !!key,\n    auth: null, api_calls: [], bars_returned: 0,\n    earliest_bar_date: null, latest_bar_date: null, latest_price: null,\n    returns: [], momentum_dimension_score: null,\n    dimensions_reliably_supplied: [], dimensions_not_supplied: [],\n    merge_safe_write: { attempted: false, row_existed_before: false, before: null, after: null, columns_written: [], columns_preserved_unchanged: [] },\n    errors: [],\n  };\n  if (!key) {\n    report.errors.push(\"EULERPOOL_API_KEY is not configured with a non-empty value; no request made.\");\n    return report;\n  }\n\n  // Single call: ~110 calendar days of daily OHLCV, comfortably covering\n  // 50+ trading days plus buffer for weekends/holidays.\n  const toTs = Math.floor(Date.now() / 1000);\n  const fromTs = toTs - 110 * 24 * 3600;\n  const endpointLabel = `GET /api/1/charting/ohlcv/${ticker} (resolution=D)`;\n  const requestUrl = `${EULERPOOL_BASE}/api/1/charting/ohlcv/${ticker}?resolution=D&from=${fromTs}&to=${toTs}`;\n\n  let json: any = null;\n  try {\n    const res = await fetch(requestUrl, {\n      headers: { Authorization: `Bearer ${key}`, Accept: \"application/json\" },\n    });\n    const note = noteForStatus(res.status);\n    report.api_calls.push({ endpoint: endpointLabel, status: res.status, ok: res.ok, note });\n    report.auth = { header_used: \"Authorization: Bearer\", status: res.status, ok: res.ok, result: note };\n    if (!res.ok) {\n      report.errors.push(\n        note === \"authentication_failed\"\n          ? \"Authentication FAILED (401) — EULERPOOL_API_KEY was rejected via the Authorization: Bearer header. Stopped after 1 call; the ?token= query-param form was not tried in this run.\"\n          : note === \"plan_restricted\"\n          ? \"Authenticated, but the OHLCV endpoint returned 403 — not available on this Eulerpool plan/key tier. Stopped after 1 call.\"\n          : `Unexpected status ${res.status} on the single OHLCV request. Stopped after 1 call.`,\n      );\n      return report;\n    }\n    try {\n      json = await res.json();\n    } catch {\n      report.errors.push(\"HTTP 200 but response body was not valid JSON.\");\n      return report;\n    }\n  } catch (err) {\n    report.api_calls.push({ endpoint: endpointLabel, status: null, ok: false, note: \"network_error\" });\n    report.auth = { header_used: \"Authorization: Bearer\", status: null, ok: false, result: \"network_error\" };\n    report.errors.push(`${endpointLabel}: ${(err as Error).message}`);\n    return report;\n  }\n\n  if (json && typeof json === \"object\" && \"s\" in json && json.s !== \"ok\") {\n    report.errors.push(`Response body reported status \"${json.s}\" (not \"ok\") — treated as no usable data, not zero.`);\n    return report;\n  }\n\n  let t: number[] = Array.isArray(json?.t) ? json.t.filter((x: unknown) => typeof x === \"number\") : [];\n  let c: number[] = Array.isArray(json?.c) ? json.c.filter((x: unknown) => typeof x === \"number\") : [];\n  if (t.length === 0 || c.length === 0 || t.length !== c.length) {\n    report.errors.push(`Response did not contain a usable, equal-length t[]/c[] series (t.length=${t.length}, c.length=${c.length}).`);\n    return report;\n  }\n\n  // Normalize to ascending-by-time; do not assume the API's native order.\n  if (t[0] > t[t.length - 1]) {\n    t = [...t].reverse();\n    c = [...c].reverse();\n  }\n\n  report.bars_returned = c.length;\n  report.earliest_bar_date = new Date(t[0] * 1000).toISOString().slice(0, 10);\n  report.latest_bar_date = new Date(t[t.length - 1] * 1000).toISOString().slice(0, 10);\n  report.latest_price = c[c.length - 1];\n\n  const r1d = computeReturn(c, 1, \"1D\");\n  const r5d = computeReturn(c, 5, \"5D\");\n  const r20d = computeReturn(c, 20, \"20D\");\n  const r50d = computeReturn(c, 50, \"50D\");\n  report.returns = [r1d, r5d, r20d, r50d];\n\n  for (const r of report.returns) {\n    if (r.available) report.dimensions_reliably_supplied.push(`${r.label} return`);\n    else report.dimensions_not_supplied.push(`${r.label} return (${r.note})`);\n  }\n  report.dimensions_reliably_supplied.unshift(\"Latest available price\");\n\n  // ---- Merge-safe persistence (price/momentum columns only) ----\n  await ensureMarketDataSchema();\n  const existingRes = await sqlite.execute({ sql: `SELECT * FROM market_data WHERE ticker = ?`, args: [ticker] });\n  const before = (existingRes.rows[0] as Record | undefined) ?? null;\n  report.merge_safe_write.attempted = true;\n  report.merge_safe_write.row_existed_before = !!before;\n  report.merge_safe_write.before = before;\n\n  const sourceNote = before\n    ? \"eulerpool (price/momentum only — merged); prior fundamentals/valuation source preserved\"\n    : \"eulerpool (price/momentum only)\";\n  const fetchedAt = new Date().toISOString();\n  // Schema mapping note: price_change_1w and price_change_1m are the\n  // closest existing columns to \"5D\" and \"20D\" — 50D has no column and is\n  // NOT persisted (reported above as informational only).\n  const priceChange1d = r1d.available ? r1d.pct : null;\n  const priceChange5dInto1wCol = r5d.available ? r5d.pct : null;\n  const priceChange20dInto1mCol = r20d.available ? r20d.pct : null;\n\n  if (before) {\n    await sqlite.execute({\n      sql: `UPDATE market_data SET price = ?, price_change_1d = ?, price_change_1w = ?, price_change_1m = ?, source = ?, fetched_at = ? WHERE ticker = ?`,\n      args: [report.latest_price, priceChange1d, priceChange5dInto1wCol, priceChange20dInto1mCol, sourceNote, fetchedAt, ticker],\n    });\n    report.merge_safe_write.columns_preserved_unchanged = [\n      \"pe_ratio\", \"pb_ratio\", \"market_cap\", \"ev\", \"revenue_growth\", \"gross_margin\", \"roe\", \"debt_to_equity\", \"avg_volume\", \"flow\",\n    ];\n  } else {\n    await sqlite.execute({\n      sql: `INSERT INTO market_data (ticker, price, price_change_1d, price_change_1w, price_change_1m, source, fetched_at) VALUES (?,?,?,?,?,?,?)`,\n      args: [ticker, report.latest_price, priceChange1d, priceChange5dInto1wCol, priceChange20dInto1mCol, sourceNote, fetchedAt],\n    });\n    report.merge_safe_write.columns_preserved_unchanged = []; // no prior row to preserve\n  }\n  report.merge_safe_write.columns_written = [\"price\", \"price_change_1d\", \"price_change_1w (holds 5D%)\", \"price_change_1m (holds 20D%)\", \"source\", \"fetched_at\"];\n\n  const afterRes = await sqlite.execute({ sql: `SELECT * FROM market_data WHERE ticker = ?`, args: [ticker] });\n  const after = (afterRes.rows[0] as Record | undefined) ?? null;\n  report.merge_safe_write.after = after;\n\n  // Reporting-only: run the EXISTING, unmodified scorer against the merged\n  // row so we can say whether Eulerpool-sourced inputs plug cleanly into\n  // Discovery V2's priceMomentum dimension. No write, no other ticker, no\n  // discovery_candidates/discovery_runs table touched.\n  const dims = scoreMarketDimensions(after as unknown as MarketData | null);\n  report.momentum_dimension_score = Number.isNaN(dims.priceMomentum) ? null : dims.priceMomentum;\n\n  report.dimensions_not_supplied.push(\n    \"Market Capitalization, Valuation metrics, other fundamentals — not requested/tested in this run (Alpha Vantage remains the source for those; not touched here)\",\n  );\n\n  return report;\n}\n\n// ---------------------------------------------------------------------------\n// FOLLOW-UP, READ-ONLY diagnostic: exactly ONE more OHLCV request for NVDA,\n// requesting a much deeper lookback window (400 calendar days instead of\n// 110) to determine whether the 8-bar result from runNvdaEulerpoolTest()\n// was a hard depth cap on this key/plan, or an artifact of the smaller\n// window. Makes NO database writes of any kind (no market_data read, no\n// market_data write, no other table touched) — purely diagnostic. Never\n// calls computeDiscovery, never touches any ticker other than NVDA, never\n// touches cron/collector/Anthropic/FMP/AnySearch/X.\n// ---------------------------------------------------------------------------\nexport interface EulerpoolDepthDiagnosticReport {\n  ticker: \"NVDA\";\n  provider: \"eulerpool\";\n  provider_configured: boolean;\n  requested_from_date: string | null;\n  requested_to_date: string | null;\n  requested_days_back: number;\n  api_calls: { endpoint: string; status: number | null; ok: boolean; note: string }[];\n  bars_returned: number;\n  earliest_bar_date: string | null;\n  latest_bar_date: string | null;\n  actual_trading_days_span: number | null;\n  comparison_to_prior_run: { prior_bars_returned: number; prior_days_requested: number };\n  conclusion: string;\n  errors: string[];\n}\n\nexport async function runNvdaEulerpoolDepthDiagnostic(): Promise {\n  const ticker = \"NVDA\";\n  const key = getKey();\n  const daysBack = 400;\n  const report: EulerpoolDepthDiagnosticReport = {\n    ticker, provider: \"eulerpool\", provider_configured: !!key,\n    requested_from_date: null, requested_to_date: null, requested_days_back: daysBack,\n    api_calls: [], bars_returned: 0, earliest_bar_date: null, latest_bar_date: null,\n    actual_trading_days_span: null,\n    comparison_to_prior_run: { prior_bars_returned: 8, prior_days_requested: 110 },\n    conclusion: \"\",\n    errors: [],\n  };\n  if (!key) {\n    report.errors.push(\"EULERPOOL_API_KEY is not configured with a non-empty value; no request made.\");\n    report.conclusion = \"Cannot diagnose — no API key configured.\";\n    return report;\n  }\n\n  const toTs = Math.floor(Date.now() / 1000);\n  const fromTs = toTs - daysBack * 24 * 3600;\n  report.requested_from_date = new Date(fromTs * 1000).toISOString().slice(0, 10);\n  report.requested_to_date = new Date(toTs * 1000).toISOString().slice(0, 10);\n  const endpointLabel = `GET /api/1/charting/ohlcv/${ticker} (resolution=D, ${daysBack}d lookback)`;\n  const requestUrl = `${EULERPOOL_BASE}/api/1/charting/ohlcv/${ticker}?resolution=D&from=${fromTs}&to=${toTs}`;\n\n  let json: any = null;\n  try {\n    const res = await fetch(requestUrl, { headers: { Authorization: `Bearer ${key}`, Accept: \"application/json\" } });\n    const note = noteForStatus(res.status);\n    report.api_calls.push({ endpoint: endpointLabel, status: res.status, ok: res.ok, note });\n    if (!res.ok) {\n      report.errors.push(`Request failed: HTTP ${res.status} (${note}). Stopped after 1 call.`);\n      report.conclusion = \"Inconclusive — the diagnostic request itself failed; unable to determine depth-cap vs. parameter issue.\";\n      return report;\n    }\n    try {\n      json = await res.json();\n    } catch {\n      report.errors.push(\"HTTP 200 but response body was not valid JSON.\");\n      report.conclusion = \"Inconclusive — response body unparseable.\";\n      return report;\n    }\n  } catch (err) {\n    report.api_calls.push({ endpoint: endpointLabel, status: null, ok: false, note: \"network_error\" });\n    report.errors.push(`${endpointLabel}: ${(err as Error).message}`);\n    report.conclusion = \"Inconclusive — network error on the diagnostic request.\";\n    return report;\n  }\n\n  if (json && typeof json === \"object\" && \"s\" in json && json.s !== \"ok\") {\n    report.errors.push(`Response body reported status \"${json.s}\" (not \"ok\").`);\n    report.conclusion = `Inconclusive — API returned a non-\"ok\" status (\"${json.s}\") rather than data.`;\n    return report;\n  }\n\n  let t: number[] = Array.isArray(json?.t) ? json.t.filter((x: unknown) => typeof x === \"number\") : [];\n  if (t.length === 0) {\n    report.errors.push(\"Response contained no usable t[] timestamp series.\");\n    report.conclusion = \"Inconclusive — no bars returned at all for the deeper window (unexpected given the prior run returned 8).\";\n    return report;\n  }\n  if (t[0] > t[t.length - 1]) t = [...t].reverse();\n\n  report.bars_returned = t.length;\n  report.earliest_bar_date = new Date(t[0] * 1000).toISOString().slice(0, 10);\n  report.latest_bar_date = new Date(t[t.length - 1] * 1000).toISOString().slice(0, 10);\n  report.actual_trading_days_span = Math.round((t[t.length - 1] - t[0]) / 86400);\n\n  if (report.bars_returned > report.comparison_to_prior_run.prior_bars_returned + 3) {\n    report.conclusion =\n      `RESOLVED: from/to IS respected — requesting ${daysBack}d back returned ${report.bars_returned} bars ` +\n      `(vs. 8 bars for a 110d request), spanning ${report.earliest_bar_date} to ${report.latest_bar_date}. ` +\n      `The original 8-bar result was NOT a hard cap; it was specific to how that smaller window was computed. ` +\n      (report.bars_returned >= 51\n        ? \"This deeper window comfortably covers 50+ trading days — 20D and 50D returns ARE computable from a properly sized request.\"\n        : `This deeper window still covers only ${report.bars_returned} bars, which is ${report.bars_returned >= 21 ? \"enough for 20D but not yet 50D\" : \"not yet enough for either 20D or 50D\"} — a still-larger lookback window would likely be needed.`);\n  } else {\n    report.conclusion =\n      `RESOLVED: this is a HARD DEPTH CAP, not a parameter issue — requesting ${daysBack}d back (vs. 110d previously) still returned only ` +\n      `${report.bars_returned} bars (spanning ${report.earliest_bar_date} to ${report.latest_bar_date}), effectively unchanged from the prior 8-bar result. ` +\n      `The from/to range is not the limiting factor; this Eulerpool key/plan appears to cap OHLCV daily history depth at ~${report.bars_returned} bars regardless of the requested window. ` +\n      `20D and 50D returns are NOT reliably obtainable from this endpoint under the current key/plan.`;\n  }\n\n  return report;\n}\n\n\n// ---------------------------------------------------------------------------\n// PRODUCTION: multi-ticker Latest Price / 1D / 5D refresher, generalized\n// from the validated NVDA-only test above. PRICE/SHORT-TERM MOMENTUM ONLY.\n// Confirmed by runNvdaEulerpoolDepthDiagnostic() that this key/plan caps\n// daily OHLCV history at ~8-9 bars regardless of the requested window, so\n// this function deliberately requests only a short (20 calendar day) lookback\n// — enough for 1D/5D, and does NOT attempt 20D/50D (no column exists for\n// 20D anyway beyond the existing price_change_1m re-purposing, and 50D has\n// no column at all — both are intentionally left NULL per the approved\n// production architecture). Does NOT parse or store the response's volume\n// (v[]) array — avg_volume/flow stay NULL, keeping the Capital Flow\n// dimension NULL as instructed, not silently populated as a side effect.\n// Genuinely merge-safe: reads the existing row first and UPDATEs ONLY\n// price/price_change_1d/price_change_1w, so Alpha Vantage's fundamentals/\n// valuation fields on that row are never touched by this function.\n// ---------------------------------------------------------------------------\nconst sleep = (ms: number): Promise => new Promise((resolve) => setTimeout(resolve, ms));\nconst EULERPOOL_HARD_CALL_CAP = 40;\n\nasync function mergeSafeWritePriceMomentum(\n  ticker: string,\n  row: { price: number | null; price_change_1d: number | null; price_change_1w: number | null },\n): Promise {\n  await ensureMarketDataSchema();\n  const existingRes = await sqlite.execute({ sql: `SELECT * FROM market_data WHERE ticker = ?`, args: [ticker] });\n  const before = existingRes.rows[0] as Record | undefined;\n  const fetchedAt = new Date().toISOString();\n  const hasAlphaVantage = !!(before && typeof before.source === \"string\" && (before.source as string).toLowerCase().includes(\"alphavantage\"));\n  const source = hasAlphaVantage\n    ? \"eulerpool (price/momentum); alphavantage (fundamentals/valuation)\"\n    : \"eulerpool (price/momentum)\";\n\n  if (before) {\n    await sqlite.execute({\n      sql: `UPDATE market_data SET price = ?, price_change_1d = ?, price_change_1w = ?, source = ?, fetched_at = ? WHERE ticker = ?`,\n      args: [row.price, row.price_change_1d, row.price_change_1w, source, fetchedAt, ticker],\n    });\n  } else {\n    await sqlite.execute({\n      sql: `INSERT INTO market_data (ticker, price, price_change_1d, price_change_1w, source, fetched_at) VALUES (?,?,?,?,?,?)`,\n      args: [ticker, row.price, row.price_change_1d, row.price_change_1w, source, fetchedAt],\n    });\n  }\n}\n\nexport interface EulerpoolRefreshResult {\n  succeeded: string[];\n  failed: { ticker: string; reason: string }[];\n  callsUsed: number;\n  errors: string[];\n}\n\nexport async function refreshEulerpoolPriceMomentum(tickers: string[]): Promise {\n  const key = getKey();\n  const result: EulerpoolRefreshResult = { succeeded: [], failed: [], callsUsed: 0, errors: [] };\n  if (!key) {\n    result.errors.push(\"EULERPOOL_API_KEY is not configured with a non-empty value; no requests made.\");\n    return result;\n  }\n\n  const bounded = [...new Set(tickers.map((t) => t.toUpperCase()))].slice(0, EULERPOOL_HARD_CALL_CAP);\n  const toTs = Math.floor(Date.now() / 1000);\n  const fromTs = toTs - 20 * 24 * 3600; // short window — only 1D/5D are needed; 20D/50D intentionally not attempted (see file header)\n\n  for (const ticker of bounded) {\n    if (result.callsUsed >= EULERPOOL_HARD_CALL_CAP) break;\n    if (result.callsUsed > 0) await sleep(150); // light pacing; be a good citizen even though docs claim sub-10ms CDN responses\n\n    try {\n      const res = await fetch(`${EULERPOOL_BASE}/api/1/charting/ohlcv/${ticker}?resolution=D&from=${fromTs}&to=${toTs}`, {\n        headers: { Authorization: `Bearer ${key}`, Accept: \"application/json\" },\n      });\n      result.callsUsed++;\n      if (!res.ok) {\n        result.failed.push({ ticker, reason: `http_${res.status}` });\n        continue;\n      }\n      let json: any;\n      try {\n        json = await res.json();\n      } catch {\n        result.failed.push({ ticker, reason: \"invalid_json\" });\n        continue;\n      }\n      if (json && typeof json === \"object\" && \"s\" in json && json.s !== \"ok\") {\n        result.failed.push({ ticker, reason: `status_${json.s}` });\n        continue;\n      }\n      let t: number[] = Array.isArray(json?.t) ? json.t.filter((x: unknown) => typeof x === \"number\") : [];\n      let c: number[] = Array.isArray(json?.c) ? json.c.filter((x: unknown) => typeof x === \"number\") : [];\n      if (t.length === 0 || c.length === 0 || t.length !== c.length) {\n        result.failed.push({ ticker, reason: \"no_usable_series\" });\n        continue;\n      }\n      if (t[0] > t[t.length - 1]) c = [...c].reverse();\n\n      const n = c.length;\n      const latest = c[n - 1];\n      const r1d = n >= 2 && c[n - 2] !== 0 ? Math.round(((c[n - 1] - c[n - 2]) / c[n - 2]) * 10000) / 100 : null;\n      const r5d = n >= 6 && c[n - 6] !== 0 ? Math.round(((c[n - 1] - c[n - 6]) / c[n - 6]) * 10000) / 100 : null;\n\n      await mergeSafeWritePriceMomentum(ticker, { price: latest, price_change_1d: r1d, price_change_1w: r5d });\n      result.succeeded.push(ticker);\n    } catch (err) {\n      result.errors.push(`${ticker}: ${(err as Error).message}`);\n    }\n  }\n\n  return result;\n}\n",[],[167,321],"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",[],[167,329],"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",[],[167,338],"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",[],[167,346],"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",[],[167,354],"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",[],[167,362],"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",[],[167,371],["D",1789039250352],[],[167,66],"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",[],[167,382],"6d5fda54-b6c2-4350-988e-d6b9bfdbe14e","alphavantage.ts","45f84900-ac5b-11f1-b9a0-1607ee4eb77e",140,["D",1789039205903],"// lib/alphavantage.ts — Controlled diagnostic + structured market-data\n// provider test using Alpha Vantage, as a candidate REPLACEMENT for the\n// still-unauthenticated FMP integration (lib/marketData.ts, untouched).\n//\n// Minimum-necessary-calls design: OVERVIEW (company fundamentals) returns\n// most of the 9 required dimensions in ONE request (MarketCapitalization,\n// PERatio, PriceToBookRatio, ReturnOnEquityTTM, QuarterlyRevenueGrowthYOY,\n// QuarterlyEarningsGrowthYOY, EPS, GrossProfitTTM + RevenueTTM). Only\n// current price / momentum requires a second call: GLOBAL_QUOTE. Neither\n// is a premium-only endpoint per official docs and two independent\n// third-party field references cross-checked before writing this file —\n// both are long-standing free-tier endpoints (free tier: 5 req/min,\n// 25 req/day, shared across all Alpha Vantage calls on this key).\n//\n// Honesty notes (verified, not assumed):\n//  - OVERVIEW has NO direct \"gross margin %\" field and NO debt-to-equity\n//    field of any kind. GrossProfitTTM and RevenueTTM ARE both returned\n//    as raw dollar figures, so gross margin is DERIVED here as\n//    GrossProfitTTM/RevenueTTM*100 — a transparent ratio of two\n//    directly-returned numbers, clearly labeled as derived, not a guess\n//    at an undocumented field name. Debt/equity has no underlying field\n//    at all from these 2 calls and stays NULL.\n//  - GLOBAL_QUOTE's raw JSON uses numbered-prefix keys (\"05. price\",\n//    \"10. change percent\"), confirmed against Alpha Vantage's own\n//    documented shape, not a normalized wrapper's renamed fields.\n//  - Alpha Vantage returns HTTP 200 even for a bad key or an exhausted\n//    rate limit, with \"Error Message\" / \"Note\" / \"Information\" keys\n//    instead of data — this file treats that as a failure, not silently\n//    as \"no data\".\n\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { ensureMarketDataSchema } from \"./marketData.ts\";\nimport type { MarketData } from \"./marketData.ts\";\n\nconst AV_BASE_URL = \"https://www.alphavantage.co/query\";\n\nexport interface AlphaVantageTestReport {\n  ticker: string;\n  provider: string;\n  provider_configured: boolean;\n  api_calls: { endpoint: string; status: number | null; ok: boolean; note: string }[];\n  fields_returned: Record;\n  fields_unavailable: string[];\n  derived_fields: Record;\n  row: Partial | null;\n  errors: string[];\n}\n\nfunction getKey(): string | null {\n  const k = Deno.env.get(\"ALPHAVANTAGE_API_KEY\");\n  return k && k.length > 0 ? k : null;\n}\n\nconst num = (v: unknown): number | null => {\n  if (typeof v !== \"string\" || v.trim() === \"\" || v.trim().toUpperCase() === \"NONE\") return null;\n  const cleaned = v.replace(/[%,]/g, \"\");\n  const n = Number(cleaned);\n  return Number.isFinite(n) ? n : null;\n};\n\nexport async function runNvdaAlphaVantageTest(): Promise {\n  const ticker = \"NVDA\";\n  const key = getKey();\n  const report: AlphaVantageTestReport = {\n    ticker,\n    provider: \"alphavantage\",\n    provider_configured: !!key,\n    api_calls: [],\n    fields_returned: {},\n    fields_unavailable: [],\n    derived_fields: {},\n    row: null,\n    errors: [],\n  };\n  if (!key) {\n    report.errors.push(\"ALPHAVANTAGE_API_KEY is not configured with a non-empty value; no request made.\");\n    return report;\n  }\n\n  const call = async (label: string, url: string): Promise => {\n    try {\n      const res = await fetch(url);\n      let json: any = null;\n      let bodyLooksLikeError = false;\n      try {\n        json = await res.json();\n        bodyLooksLikeError = !!(json && (json[\"Error Message\"] || json[\"Note\"] || json[\"Information\"]));\n      } catch {\n        // leave json null — handled below\n      }\n      const ok = res.ok && !bodyLooksLikeError;\n      const note = !res.ok ? `http_${res.status}` : bodyLooksLikeError ? \"api_error_in_body\" : \"ok\";\n      report.api_calls.push({ endpoint: label, status: res.status, ok, note });\n      if (bodyLooksLikeError) {\n        const msg = json[\"Error Message\"] || json[\"Note\"] || json[\"Information\"];\n        report.errors.push(`${label}: HTTP 200 but an error/limit body was returned: \"${String(msg).slice(0, 220)}\"`);\n        return null;\n      }\n      return res.ok ? json : 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  // Call 1: OVERVIEW. Doubles as the auth/availability check — for a real,\n  // liquid large-cap like NVDA, an empty object or error body is itself\n  // diagnostic of a bad key or exhausted limit, not genuine absence of data.\n  const overview = await call(\"OVERVIEW\", `${AV_BASE_URL}?function=OVERVIEW&symbol=${ticker}&apikey=${key}`);\n  const firstCall = report.api_calls[0];\n  if (!firstCall.ok || !overview || Object.keys(overview).length === 0) {\n    report.errors.push(\"OVERVIEW did not return usable data — stopped after 1 call; the second call (GLOBAL_QUOTE) was not made.\");\n    return report;\n  }\n\n  // Call 2: GLOBAL_QUOTE — only for price / daily change, which OVERVIEW\n  // does not provide.\n  const quoteRaw = await call(\"GLOBAL_QUOTE\", `${AV_BASE_URL}?function=GLOBAL_QUOTE&symbol=${ticker}&apikey=${key}`);\n  const gq = quoteRaw?.[\"Global Quote\"] ?? {};\n\n  const revenueTTM = num(overview.RevenueTTM);\n  const grossProfitTTM = num(overview.GrossProfitTTM);\n  const derivedGrossMargin = revenueTTM && grossProfitTTM != null && revenueTTM !== 0\n    ? (grossProfitTTM / revenueTTM) * 100\n    : null;\n  if (derivedGrossMargin !== null) {\n    report.derived_fields.gross_margin = \"GrossProfitTTM / RevenueTTM * 100 — both are directly-returned raw dollar figures; this ratio is computed here, not returned pre-computed by Alpha Vantage.\";\n  }\n\n  const changePercentRaw = gq[\"10. change percent\"];\n  const changePercent = typeof changePercentRaw === \"string\" ? num(changePercentRaw.replace(\"%\", \"\")) : null;\n\n  const roeRaw = num(overview.ReturnOnEquityTTM);\n  const revGrowthRaw = num(overview.QuarterlyRevenueGrowthYOY);\n  const epsGrowthRaw = num(overview.QuarterlyEarningsGrowthYOY);\n\n  const row: Partial = {\n    ticker,\n    price: num(gq[\"05. price\"]),\n    price_change_1d: changePercent,\n    price_change_1w: null,\n    price_change_1m: null,\n    pe_ratio: num(overview.PERatio),\n    pb_ratio: num(overview.PriceToBookRatio),\n    market_cap: num(overview.MarketCapitalization),\n    ev: null, // OVERVIEW gives EVToRevenue/EVToEBITDA ratios, not absolute EV — not derived here (would need a second backed-out figure)\n    revenue_growth: revGrowthRaw !== null ? revGrowthRaw * 100 : null,\n    gross_margin: derivedGrossMargin,\n    roe: roeRaw !== null ? roeRaw * 100 : null,\n    debt_to_equity: null, // no such field anywhere in OVERVIEW — verified against two independent field references before this test\n    avg_volume: num(gq[\"06. volume\"]), // latest daily volume, not a true average — same proxy usage the existing capitalFlow scorer already applies\n    flow: null,\n    source: \"alphavantage\",\n  };\n\n  const ALL_FIELDS = [\n    \"price\", \"price_change_1d\", \"price_change_1w\", \"price_change_1m\",\n    \"pe_ratio\", \"pb_ratio\", \"market_cap\", \"ev\", \"revenue_growth\",\n    \"gross_margin\", \"roe\", \"debt_to_equity\", \"avg_volume\", \"flow\",\n  ] as const;\n  for (const f of ALL_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;\n  }\n  // EPS growth is not a column in market_data / not consumed by\n  // scoreMarketDimensions today, but the user explicitly asked whether AV\n  // can supply it — surfaced here for the report without inventing a\n  // schema column for it.\n  report.fields_returned[\"eps_growth_pct (informational — not in market_data schema/scoring yet)\"] =\n    epsGrowthRaw !== null ? Math.round(epsGrowthRaw * 1000) / 10 : \"unavailable\";\n\n  report.row = row;\n  return report;\n}\n\n// ---------------------------------------------------------------------------\n// Follow-up, price-only test: exactly ONE GLOBAL_QUOTE request for NVDA,\n// standalone (does NOT re-call OVERVIEW — that data is already stored and\n// still valid, so re-fetching it would be a wasted call). Making only this\n// one call across the whole request, on its own turn, inherently respects\n// the free tier's \"1 request per second\" pacing — there is nothing else to\n// space it against.\n// ---------------------------------------------------------------------------\nexport interface AlphaVantageQuoteTestReport {\n  ticker: string;\n  provider: string;\n  provider_configured: boolean;\n  api_calls: { endpoint: string; status: number | null; ok: boolean; note: string }[];\n  fields_returned: Record;\n  fields_unavailable: string[];\n  price: number | null;\n  price_change_1d: number | null;\n  avg_volume: number | null;\n  errors: string[];\n}\n\nexport async function runNvdaGlobalQuoteTest(): Promise {\n  const ticker = \"NVDA\";\n  const key = getKey();\n  const report: AlphaVantageQuoteTestReport = {\n    ticker,\n    provider: \"alphavantage\",\n    provider_configured: !!key,\n    api_calls: [],\n    fields_returned: {},\n    fields_unavailable: [],\n    price: null,\n    price_change_1d: null,\n    avg_volume: null,\n    errors: [],\n  };\n  if (!key) {\n    report.errors.push(\"ALPHAVANTAGE_API_KEY is not configured with a non-empty value; no request made.\");\n    return report;\n  }\n\n  try {\n    const res = await fetch(`${AV_BASE_URL}?function=GLOBAL_QUOTE&symbol=${ticker}&apikey=${key}`);\n    let json: any = null;\n    let bodyLooksLikeError = false;\n    try {\n      json = await res.json();\n      bodyLooksLikeError = !!(json && (json[\"Error Message\"] || json[\"Note\"] || json[\"Information\"]));\n    } catch {\n      // leave json null\n    }\n    const ok = res.ok && !bodyLooksLikeError;\n    const note = !res.ok ? `http_${res.status}` : bodyLooksLikeError ? \"api_error_in_body\" : \"ok\";\n    report.api_calls.push({ endpoint: \"GLOBAL_QUOTE\", status: res.status, ok, note });\n\n    if (bodyLooksLikeError) {\n      const msg = json[\"Error Message\"] || json[\"Note\"] || json[\"Information\"];\n      report.errors.push(`GLOBAL_QUOTE: HTTP 200 but an error/limit body was returned: \"${String(msg).slice(0, 220)}\"`);\n      return report;\n    }\n    if (!res.ok) {\n      report.errors.push(`GLOBAL_QUOTE: HTTP ${res.status}.`);\n      return report;\n    }\n\n    const gq = json?.[\"Global Quote\"] ?? {};\n    if (!gq || Object.keys(gq).length === 0) {\n      report.errors.push(\"GLOBAL_QUOTE returned 200 with no \\\"Global Quote\\\" data (empty object) — treated as unavailable, not zero.\");\n      return report;\n    }\n\n    const changePercentRaw = gq[\"10. change percent\"];\n    report.price = num(gq[\"05. price\"]);\n    report.price_change_1d = typeof changePercentRaw === \"string\" ? num(changePercentRaw.replace(\"%\", \"\")) : null;\n    report.avg_volume = num(gq[\"06. volume\"]);\n\n    const fieldMap: Record = {\n      price: report.price,\n      price_change_1d: report.price_change_1d,\n      avg_volume: report.avg_volume,\n    };\n    for (const [k, v] of Object.entries(fieldMap)) {\n      if (v === null) report.fields_unavailable.push(k);\n      else report.fields_returned[k] = v;\n    }\n  } catch (err) {\n    report.api_calls.push({ endpoint: \"GLOBAL_QUOTE\", status: null, ok: false, note: \"network_error\" });\n    report.errors.push(`GLOBAL_QUOTE: ${(err as Error).message}`);\n  }\n\n  return report;\n}\n\n\n// ---------------------------------------------------------------------------\n// PRODUCTION: multi-ticker fundamentals/valuation refresher, generalized\n// from the validated NVDA-only test above. FUNDAMENTALS/VALUATION ONLY —\n// price/momentum is Eulerpool's job now (see lib/eulerpool.ts). Never\n// requests GLOBAL_QUOTE here. Bounded per call (default cap 20 tickers,\n// hard cap 25 to respect the free tier's 25/day limit with headroom) and\n// paced at 13s between requests (free tier: 5 req/min = 12s min spacing).\n// Genuinely merge-safe: reads the existing row first and UPDATEs ONLY the\n// fundamentals/valuation columns, so any Eulerpool price/momentum fields on\n// that row are never touched by this function.\n// ---------------------------------------------------------------------------\nconst sleep = (ms: number): Promise => new Promise((resolve) => setTimeout(resolve, ms));\nconst HARD_CALL_CAP = 25; // absolute ceiling regardless of caller-supplied ticker list length\n\nasync function mergeSafeWriteFundamentals(\n  ticker: string,\n  row: { pe_ratio: number | null; pb_ratio: number | null; market_cap: number | null; revenue_growth: number | null; gross_margin: number | null; roe: number | null },\n): Promise {\n  await ensureMarketDataSchema();\n  const existingRes = await sqlite.execute({ sql: `SELECT * FROM market_data WHERE ticker = ?`, args: [ticker] });\n  const before = existingRes.rows[0] as Record | undefined;\n  const fetchedAt = new Date().toISOString();\n  const hasEulerpool = !!(before && typeof before.source === \"string\" && (before.source as string).toLowerCase().includes(\"eulerpool\"));\n  const source = hasEulerpool\n    ? \"alphavantage (fundamentals/valuation); eulerpool (price/momentum)\"\n    : \"alphavantage (fundamentals/valuation)\";\n\n  if (before) {\n    await sqlite.execute({\n      sql: `UPDATE market_data SET pe_ratio = ?, pb_ratio = ?, market_cap = ?, revenue_growth = ?, gross_margin = ?, roe = ?, source = ?, fetched_at = ? WHERE ticker = ?`,\n      args: [row.pe_ratio, row.pb_ratio, row.market_cap, row.revenue_growth, row.gross_margin, row.roe, source, fetchedAt, ticker],\n    });\n  } else {\n    await sqlite.execute({\n      sql: `INSERT INTO market_data (ticker, pe_ratio, pb_ratio, market_cap, revenue_growth, gross_margin, roe, source, fetched_at) VALUES (?,?,?,?,?,?,?,?,?)`,\n      args: [ticker, row.pe_ratio, row.pb_ratio, row.market_cap, row.revenue_growth, row.gross_margin, row.roe, source, fetchedAt],\n    });\n  }\n}\n\nexport interface AlphaVantageRefreshResult {\n  succeeded: string[];\n  skipped_fresh: string[]; // already had an alphavantage-sourced row < 20h old — per-ticker defense in depth\n  failed: { ticker: string; reason: string }[];\n  callsUsed: number;\n  errors: string[];\n}\n\nexport async function refreshAlphaVantageFundamentals(tickers: string[]): Promise {\n  const key = getKey();\n  const result: AlphaVantageRefreshResult = { succeeded: [], skipped_fresh: [], failed: [], callsUsed: 0, errors: [] };\n  if (!key) {\n    result.errors.push(\"ALPHAVANTAGE_API_KEY is not configured with a non-empty value; no requests made.\");\n    return result;\n  }\n  await ensureMarketDataSchema();\n\n  const bounded = [...new Set(tickers.map((t) => t.toUpperCase()))].slice(0, HARD_CALL_CAP);\n\n  for (const ticker of bounded) {\n    // Per-ticker freshness guard (defense in depth on top of the once-daily\n    // gate in lib/productionRefresh.ts): never re-spend a call on a ticker\n    // whose fundamentals were already refreshed by Alpha Vantage in the\n    // last ~20h, even if this function is somehow invoked twice.\n    const existingRes = await sqlite.execute({ sql: `SELECT source, fetched_at FROM market_data WHERE ticker = ?`, args: [ticker] });\n    const existing = existingRes.rows[0] as Record | undefined;\n    if (\n      existing && typeof existing.source === \"string\" && (existing.source as string).toLowerCase().includes(\"alphavantage\") &&\n      typeof existing.fetched_at === \"string\" && (Date.now() - new Date(existing.fetched_at).getTime()) < 20 * 3600 * 1000\n    ) {\n      result.skipped_fresh.push(ticker);\n      continue;\n    }\n\n    if (result.callsUsed > 0) await sleep(13000); // respect 5 req/min free-tier pacing\n    if (result.callsUsed >= HARD_CALL_CAP) break;\n\n    try {\n      const res = await fetch(`${AV_BASE_URL}?function=OVERVIEW&symbol=${ticker}&apikey=${key}`);\n      result.callsUsed++;\n      let json: any = null;\n      let bodyLooksLikeError = false;\n      try {\n        json = await res.json();\n        bodyLooksLikeError = !!(json && (json[\"Error Message\"] || json[\"Note\"] || json[\"Information\"]));\n      } catch {\n        // leave json null\n      }\n      if (!res.ok || bodyLooksLikeError || !json || Object.keys(json).length === 0) {\n        result.failed.push({\n          ticker,\n          reason: bodyLooksLikeError ? `error/limit body: \"${String(json?.[\"Error Message\"] || json?.[\"Note\"] || json?.[\"Information\"]).slice(0, 160)}\"` : `http_${res.status}`,\n        });\n        continue; // never retry within this run — move on, don't burn more calls/time on this ticker\n      }\n\n      const revenueTTM = num(json.RevenueTTM);\n      const grossProfitTTM = num(json.GrossProfitTTM);\n      const derivedGrossMargin = revenueTTM && grossProfitTTM != null && revenueTTM !== 0 ? (grossProfitTTM / revenueTTM) * 100 : null;\n      const revGrowthRaw = num(json.QuarterlyRevenueGrowthYOY);\n      const roeRaw = num(json.ReturnOnEquityTTM);\n\n      await mergeSafeWriteFundamentals(ticker, {\n        pe_ratio: num(json.PERatio),\n        pb_ratio: num(json.PriceToBookRatio),\n        market_cap: num(json.MarketCapitalization),\n        revenue_growth: revGrowthRaw !== null ? revGrowthRaw * 100 : null,\n        gross_margin: derivedGrossMargin,\n        roe: roeRaw !== null ? roeRaw * 100 : null,\n      });\n      result.succeeded.push(ticker);\n    } catch (err) {\n      result.errors.push(`${ticker}: ${(err as Error).message}`);\n    }\n  }\n\n  return result;\n}\n",[],[167,390],"6b69522e-ee08-4e2e-aa5c-3074df0d9e73","untitled-folder-5076","a0f921cc-ab88-11f1-8e02-1607ee4eb77e",71,["D",1788873872966],[],[167,398],"72334838-2080-4896-9e3b-4958397be514",["D",1788705478068],[180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198],[167],["D",1788705462303],["D",1789485645684],{"_11":12,"_135":72},{"_11":12,"_130":131,"_71":72,"_30":70,"_132":133,"_134":24},{"_39":121,"_13":16,"_33":36,"_122":82,"_123":-5,"_124":-5,"_125":-5,"_126":-5,"_127":-5,"_128":14,"_129":12},"ctx",{"_54":-5,"_154":446,"_447":448,"_449":450,"_451":24,"_452":24,"_453":24,"_454":95,"_455":-5,"_456":-7,"_457":-7,"_458":26,"_459":24,"_460":24,"_461":-5,"_462":24,"_463":24,"_464":24,"_465":-5},"ENV",{"_417":418,"_419":420,"_421":16,"_422":423,"_424":425,"_426":427,"_428":429,"_430":431,"_432":433,"_434":435,"_436":437,"_438":439,"_440":441,"_442":443,"_444":445},"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"]

History · anysearch.ts