[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_271":272,"_273":274},"routes/_app.x.$handle.$name",{"_101":100,"_32":46,"_110":91,"_27":111,"_13":14,"_112":-5,"_11":113,"_114":115,"_116":117,"_118":119,"_120":121,"_20":-5,"_122":-5,"_123":-5,"_124":25,"_125":126,"_127":60,"_128":-5,"_129":-5,"_130":131},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_44":45,"_32":46,"_39":47,"_48":-5,"_49":25,"_50":51,"_52":51,"_53":54,"_55":-5,"_18":56,"_57":58,"_59":60,"_61":-5,"_62":-5,"_63":64,"_65":66,"_67":68,"_69":70,"_71":72,"_73":-5,"_74":75,"_76":75,"_77":78,"_79":78,"_80":78,"_81":75,"_82":75,"_83":84,"_85":86,"_87":88,"_89":25},"version",54,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":23,"_24":25,"_26":25,"_27":28,"_29":28},"file",{"_30":31,"_32":33,"_34":35,"_36":38,"_39":40,"_41":14,"_42":43,"_24":25},"previous",{"_30":31,"_32":33,"_34":35,"_36":37},"deleted",false,"deletedHere","path","lib/schema.ts","latestPath","fileId","9e4e244c-aa00-11f1-a2c0-1607ee4eb77e","name","schema.ts","fileType","script","content","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","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","createdAt",["D",1788840389768],"branchVersion","branchId","012dced4-9569-4aa1-b4df-043c162f1aa5","id","5c601043-49d8-4da3-8595-d21725bb7d55","x-trader-consensus",["D",1788705462303],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","X account collector: normalized posts into SQLite","imageUrl","8f36435b-4ee1-42d7-a52e-11508b5c71b0","pinnedFileId","b9b4c5ce-aa00-11f1-b6d1-1607ee4eb77e","pinPreview",true,"pinMigrated","screenshotVersion","likeCount",0,"orgId","01a07719-4bcc-778e-997b-14b67712b6b9","tursoName","5c60104349d84da38595d21725bb7d55","tursoHostname","5c60104349d84da38595d21725bb7d55-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789572079973],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_101":100,"_109":105},"org",{"_101":100,"_102":103,"_104":105,"_18":56,"_106":107,"_108":25},"branch",{"_13":90,"_32":91,"_44":43,"_92":45,"_93":-5,"_94":-5,"_95":-5,"_96":-5,"_97":-5,"_98":46,"_99":100},"isOwned",146,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","efrenlin","handle","displayName","Efren Lin","avatar","https://lh3.googleusercontent.com/a/ACg8ocITAXmIR316-M-ReEEDUIUESLjIDNnYnCPqoEpOrKLJExkkkWU=s96-c","tier","free","canRestrictVals","profileImageUrl","branchName","","adoptOrgId",{"_44":45,"_32":46,"_39":266,"_48":-5,"_49":25,"_50":51,"_52":51,"_53":54,"_55":-5,"_18":56,"_57":58,"_59":60,"_61":-5,"_62":-5,"_63":64,"_65":66,"_67":68,"_69":70,"_71":267,"_73":-5,"_74":75,"_76":75,"_77":78,"_79":78,"_80":78,"_81":75,"_82":75,"_83":268,"_85":269,"_87":270,"_89":25},"tree",[180,189,190,191],"files",[180,181,182,183,184,185,186,187,188,189,190,191],"counts",{"_169":64,"_170":159,"_171":64,"_172":173,"_174":175},"contents",[132,133,134,135],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_44":162,"_32":163,"_30":164,"_138":-5,"_42":43,"_41":165,"_140":60,"_39":166,"_13":64,"_34":167,"_36":-5,"_24":25,"_145":-5,"_27":168},{"_44":154,"_32":155,"_30":156,"_138":-5,"_42":43,"_41":157,"_140":60,"_39":158,"_13":159,"_34":20,"_36":160,"_24":25,"_145":-5,"_27":161},{"_44":147,"_32":148,"_30":149,"_138":-5,"_42":43,"_41":150,"_140":60,"_39":151,"_13":64,"_34":35,"_36":152,"_24":25,"_145":-5,"_27":153},{"_44":136,"_32":137,"_30":58,"_138":-5,"_42":43,"_41":139,"_140":25,"_39":141,"_13":142,"_34":143,"_36":144,"_24":25,"_145":-5,"_27":146},"be991303-9b15-466e-8cbf-1499c8e3b3da","main.ts","parentId",53,"latest",["D",1788792296141],21,"http","import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { TRACKED_ACCOUNTS } from \"./config.ts\";\nimport { runCollector } from \"./lib/collector.ts\";\nimport { ensureSchema } from \"./lib/schema.ts\";\nimport { runConsensusAggregation } from \"./lib/consensus.ts\";\nimport { extractCandidates } from \"./lib/discovery.ts\";\nimport { runCandidateValidation } from \"./lib/candidateValidation.ts\";\nimport { applyFundamentals, runMarketDiscovery } from \"./lib/marketDiscovery.ts\";\n\n// No cron/interval is attached yet (by design — see README \"Phase 2\").\n// Every route here is manually triggered.\nexport default async function (req: Request): Promise {\n const url = new URL(req.url);\n\n if (url.pathname === \"/\" || url.pathname === \"/health\") {\n await ensureSchema();\n const hasToken = Boolean(Deno.env.get(\"X_BEARER_TOKEN\"));\n const accounts = await sqlite.execute(\n \"SELECT username, account_id, last_post_id, last_collected_at FROM accounts\",\n );\n const postCount = await sqlite.execute(\"SELECT COUNT(*) as n FROM posts\");\n const unanalyzed = await sqlite.execute(\n \"SELECT COUNT(*) as n FROM posts WHERE analyzed_at IS NULL\",\n );\n\n return Response.json({\n ok: true,\n x_bearer_token_configured: hasToken,\n tracked_accounts: TRACKED_ACCOUNTS,\n resolved_accounts: accounts.rows,\n total_posts_stored: (postCount.rows[0] as any).n,\n posts_awaiting_claude_analysis: (unanalyzed.rows[0] as any).n,\n note: hasToken\n ? \"Ready. GET /run-collector to ingest recent posts. GET /posts to inspect stored data.\"\n : \"X_BEARER_TOKEN is not set yet. See README.md for exactly where to add it.\",\n });\n }\n\n if (url.pathname === \"/run-collector\") {\n if (!Deno.env.get(\"X_BEARER_TOKEN\")) {\n return Response.json(\n {\n ok: false,\n error: \"X_BEARER_TOKEN is not set. Add it as a Val Town environment variable — see README.md.\",\n },\n { status: 400 },\n );\n }\n try {\n // Phase 3.1 validation only: ?reset_since_id=1 clears last_post_id for\n // the 10 Core Trader accounts before fetching, so the same recent\n // window (bounded by MAX_RESULTS_PER_ACCOUNT — 25/account, 10 calls\n // total) is re-fetched and already-ingested rows can be backfilled\n // with referenced_author_* provenance the collector previously\n // discarded. Never touches discovered-candidate accounts.\n const resetSinceId = url.searchParams.get(\"reset_since_id\") === \"1\";\n const summary = await runCollector({ resetSinceId });\n return Response.json({ ok: true, summary });\n } catch (err) {\n return Response.json({ ok: false, error: (err as Error).message }, { status: 500 });\n }\n }\n\n if (url.pathname === \"/posts\") {\n await ensureSchema();\n const account = url.searchParams.get(\"account\");\n const limit = Math.min(Number(url.searchParams.get(\"limit\") ?? \"50\") || 50, 200);\n\n const rows = account\n ? await sqlite.execute({\n sql: \"SELECT * FROM posts WHERE username = ? ORDER BY posted_at DESC LIMIT ?\",\n args: [account, limit],\n })\n : await sqlite.execute({\n sql: \"SELECT * FROM posts ORDER BY posted_at DESC LIMIT ?\",\n args: [limit],\n });\n\n return Response.json({ ok: true, count: rows.rows.length, posts: rows.rows });\n }\n\n if (url.pathname === \"/runs\") {\n await ensureSchema();\n const rows = await sqlite.execute(\n \"SELECT * FROM collector_runs ORDER BY id DESC LIMIT 20\",\n );\n return Response.json({ ok: true, runs: rows.rows });\n }\n\n // Phase 2: Trader Consensus Intelligence Layer aggregation.\n // Recomputes per-ticker consensus from currently-analyzed posts and\n // appends a new snapshot row per ticker into ticker_consensus.\n if (url.pathname === \"/consensus\" && (req.method === \"GET\" || req.method === \"POST\")) {\n await ensureSchema();\n try {\n const summary = await runConsensusAggregation();\n return Response.json({ ok: true, ...summary });\n } catch (err) {\n return Response.json({ ok: false, error: (err as Error).message }, { status: 500 });\n }\n }\n\n // Read-only: the most recent snapshot per ticker, without recomputing.\n if (url.pathname === \"/consensus/latest\") {\n await ensureSchema();\n const rows = await sqlite.execute(`\n SELECT tc.* FROM ticker_consensus tc\n INNER JOIN (\n SELECT ticker, MAX(computed_at) AS max_computed_at\n FROM ticker_consensus\n GROUP BY ticker\n ) latest ON tc.ticker = latest.ticker AND tc.computed_at = latest.max_computed_at\n ORDER BY tc.unique_accounts_mentioning DESC, tc.mention_count DESC\n `);\n return Response.json({ ok: true, count: rows.rows.length, consensus: rows.rows });\n }\n\n // Phase 3: Trader Discovery Layer — mechanical candidate extraction only.\n // Read-only, no writes to discovered_accounts here (that happens after a\n // separate semantic review step, per the mechanical/semantic split used\n // throughout this project).\n if (url.pathname === \"/discovery/candidates\") {\n await ensureSchema();\n const result = await extractCandidates();\n if (url.searchParams.get(\"summary\") === \"1\") {\n const reasonCounts: Record = {};\n for (const r of result.rejected) {\n const key = r.reason.split(\":\")[0];\n reasonCounts[key] = (reasonCounts[key] ?? 0) + 1;\n }\n return Response.json({\n ok: true,\n candidate_count: result.candidates.length,\n rejected_count: result.rejected.length,\n rejected_reason_breakdown: reasonCounts,\n rejected_handles: result.rejected.map((r) => ({ handle: r.handle, reason: r.reason })),\n notes: result.notes,\n });\n }\n if (url.searchParams.get(\"compact\") === \"1\") {\n const offset = Number(url.searchParams.get(\"offset\") ?? \"0\") || 0;\n const limit = Number(url.searchParams.get(\"limit\") ?? \"1000\") || 1000;\n const slice = result.candidates.slice(offset, offset + limit);\n return Response.json({\n ok: true,\n candidate_count: result.candidates.length,\n offset,\n returned: slice.length,\n candidates: slice.map((c) => ({\n handle: c.handle,\n times_referenced: c.times_referenced,\n referencing_core_accounts: c.referencing_core_accounts,\n tickers_discussed: c.tickers_discussed,\n has_ticker_evidence: c.has_ticker_evidence,\n promotional_flag: c.promotional_flag,\n evidence: c.evidence.map((e) => ({\n citing_account: e.citing_account,\n post_type: e.post_type,\n text: e.text.slice(0, 220),\n posted_at: e.posted_at,\n })),\n })),\n });\n }\n const handlesFilter = url.searchParams.get(\"handles\");\n if (handlesFilter) {\n const wanted = new Set(handlesFilter.split(\",\").map((h) => h.trim().toLowerCase()));\n return Response.json({\n ok: true,\n candidates: result.candidates.filter((c) => wanted.has(c.handle.toLowerCase())),\n rejected: result.rejected.filter((r) => wanted.has(r.handle.toLowerCase())),\n });\n }\n return Response.json({ ok: true, ...result });\n }\n\n // Phase 3.2: Discovered Trader Candidate Validation — controlled,\n // explicit-handles-only timeline collection for candidates already in\n // discovered_accounts. POST only (this makes X API calls and writes\n // data). ?handles=a,b,c (required, no default/\"all\" mode) and optional\n // ?max_results= (default 30, capped 5-100). Never touches `posts`\n // (Core Trader dataset), never touches discovered_accounts.\n if (url.pathname === \"/candidate-validation/run\" && req.method === \"POST\") {\n await ensureSchema();\n if (!Deno.env.get(\"X_BEARER_TOKEN\")) {\n return Response.json(\n { ok: false, error: \"X_BEARER_TOKEN is not set. Add it as a Val Town environment variable — see README.md.\" },\n { status: 400 },\n );\n }\n const handlesParam = url.searchParams.get(\"handles\");\n if (!handlesParam) {\n return Response.json(\n { ok: false, error: \"?handles=comma,separated,list is required — this route never runs for 'all candidates'.\" },\n { status: 400 },\n );\n }\n const wanted = handlesParam.split(\",\").map((h) => h.trim()).filter(Boolean);\n const maxResults = Number(url.searchParams.get(\"max_results\") ?? \"30\") || 30;\n\n const placeholders = wanted.map(() => \"?\").join(\",\");\n const rows = await sqlite.execute({\n sql: `SELECT handle, x_account_id, display_name FROM discovered_accounts WHERE handle IN (${placeholders})`,\n args: wanted,\n });\n const found = new Map((rows.rows as any[]).map((r) => [r.handle.toLowerCase(), r]));\n const missing = wanted.filter((h) => !found.has(h.toLowerCase()));\n const targets = wanted\n .filter((h) => found.has(h.toLowerCase()))\n .map((h) => {\n const r = found.get(h.toLowerCase())!;\n return { handle: r.handle, x_account_id: r.x_account_id, display_name: r.display_name };\n });\n\n try {\n const summary = await runCandidateValidation(targets, { maxResults });\n return Response.json({ ok: true, missing_from_discovered_accounts: missing, summary });\n } catch (err) {\n return Response.json({ ok: false, error: (err as Error).message }, { status: 500 });\n }\n }\n\n // Read-only inspection of a candidate validation sample.\n if (url.pathname === \"/candidate-validation/posts\") {\n await ensureSchema();\n const handle = url.searchParams.get(\"handle\");\n const limit = Math.min(Number(url.searchParams.get(\"limit\") ?? \"100\") || 100, 200);\n const rows = handle\n ? await sqlite.execute({\n sql: \"SELECT * FROM candidate_validation_posts WHERE candidate_handle = ? ORDER BY posted_at DESC LIMIT ?\",\n args: [handle, limit],\n })\n : await sqlite.execute({\n sql: \"SELECT * FROM candidate_validation_posts ORDER BY posted_at DESC LIMIT ?\",\n args: [limit],\n });\n return Response.json({ ok: true, count: rows.rows.length, posts: rows.rows });\n }\n\n if (url.pathname === \"/candidate-validation/runs\") {\n await ensureSchema();\n const rows = await sqlite.execute(\"SELECT * FROM candidate_validation_runs ORDER BY id DESC LIMIT 20\");\n return Response.json({ ok: true, runs: rows.rows });\n }\n\n if (url.pathname === \"/discovery/accounts\") {\n await ensureSchema();\n const group = url.searchParams.get(\"group\");\n const rows = group\n ? await sqlite.execute({\n sql: \"SELECT * FROM discovered_accounts WHERE account_group = ? ORDER BY relevance_score DESC\",\n args: [group],\n })\n : await sqlite.execute(\"SELECT * FROM discovered_accounts ORDER BY relevance_score DESC\");\n return Response.json({ ok: true, count: rows.rows.length, accounts: rows.rows });\n }\n\n // Phase 4: Market Discovery Score V1 — RESEARCH PRIORITIZATION only, never\n // a buy/sell recommendation. Step A: mechanical + semantic-lookup pass\n // over the full ticker universe (ticker_consensus UNION Validated\n // Discovery Signal tickers), persisted as a new append-only run. Never\n // writes to accounts / discovered_accounts / posts / ticker_consensus.\n if (url.pathname === \"/market-discovery/run\" && (req.method === \"GET\" || req.method === \"POST\")) {\n await ensureSchema();\n try {\n const finalistCount = Number(url.searchParams.get(\"finalists\") ?? \"20\") || 20;\n const summary = await runMarketDiscovery({ finalistCount });\n return Response.json({ ok: true, summary });\n } catch (err) {\n return Response.json({ ok: false, error: (err as Error).message }, { status: 500 });\n }\n }\n\n // Step B: apply the bounded Component E (Fundamental/Market Quality Gate)\n // lookup for a run's finalists. Body: { run_id, updates: [{ ticker,\n // fundamental_market_score (0-10 or null), fundamental_note }] }.\n if (url.pathname === \"/market-discovery/apply-fundamentals\" && req.method === \"POST\") {\n await ensureSchema();\n try {\n const body = await req.json();\n const runId = Number(body.run_id);\n const updates = Array.isArray(body.updates) ? body.updates : [];\n if (!runId || updates.length === 0) {\n return Response.json({ ok: false, error: \"Body must include run_id and a non-empty updates array.\" }, { status: 400 });\n }\n const result = await applyFundamentals(runId, updates);\n return Response.json({ ok: true, ...result });\n } catch (err) {\n return Response.json({ ok: false, error: (err as Error).message }, { status: 500 });\n }\n }\n\n // Read-only: latest run's full ranked snapshot.\n if (url.pathname === \"/market-discovery/latest\") {\n await ensureSchema();\n const runRow = await sqlite.execute(`SELECT * FROM market_discovery_runs ORDER BY id DESC LIMIT 1`);\n if (runRow.rows.length === 0) {\n return Response.json({ ok: true, run: null, tickers: [] });\n }\n const run = runRow.rows[0] as any;\n const rows = await sqlite.execute({\n sql: `SELECT * FROM market_discovery_snapshots WHERE run_id = ? ORDER BY discovery_score DESC`,\n args: [run.id],\n });\n return Response.json({ ok: true, run, count: rows.rows.length, tickers: rows.rows });\n }\n\n if (url.pathname === \"/market-discovery/runs\") {\n await ensureSchema();\n const rows = await sqlite.execute(`SELECT * FROM market_discovery_runs ORDER BY id DESC LIMIT 20`);\n return Response.json({ ok: true, runs: rows.rows });\n }\n\n return Response.json({\n ok: false,\n error: \"Not found\",\n routes: [\n \"/health\",\n \"/run-collector\",\n \"/posts\",\n \"/runs\",\n \"/consensus\",\n \"/consensus/latest\",\n \"/discovery/candidates\",\n \"/discovery/accounts\",\n \"/candidate-validation/run\",\n \"/candidate-validation/posts\",\n \"/candidate-validation/runs\",\n \"/market-discovery/run\",\n \"/market-discovery/apply-fundamentals\",\n \"/market-discovery/latest\",\n \"/market-discovery/runs\",\n ],\n }, {\n status: 404,\n });\n}\n","label",[137],"528c691a-d3cc-4071-b88a-d771bd29e8a1","config.ts","937ab4f4-aa00-11f1-a8fe-1607ee4eb77e",3,["D",1788705487606],"// Configuration for the X Trader Consensus collector.\n// Public account handles only — NO credentials live in this file or\n// anywhere else in source. The X API Bearer Token is read from a Val Town\n// environment variable at runtime — see README.md for where to add it.\n\nexport const TRACKED_ACCOUNTS: string[] = [\n \"aleabitoreddit\",\n \"jukan05\",\n \"octopusycc\",\n \"zephyr_z9\",\n \"KawzInvests\",\n \"michaelsikand\",\n \"ren_stocks\",\n \"unusual_whales\",\n \"StockMKTNewz\",\n \"DJTRadar\",\n];\n\n// Max tweets to pull per account per collector run (X API allows 5-100 for\n// the user tweets timeline endpoint). Kept modest for MVP + API cost control\n// — the X API now bills per resource read (see README \"API cost\" section).\nexport const MAX_RESULTS_PER_ACCOUNT = 25;\n\n// Minimum milliseconds to wait between X API calls, so a single run across\n// 10 accounts stays comfortably under per-endpoint rate limits.\nexport const REQUEST_SPACING_MS = 1200;\n",[148],"8dfc869b-1fc5-4ec2-906c-3fd1c2aca80b","README.md","84701602-aa00-11f1-a970-1607ee4eb77e",9,["D",1788705694497],1,"# X Trader Consensus Collector\n\nPhase 1 backend/data layer for a Market Discovery & Consensus system.\n\n```\nX API → Val Town collector → normalized storage (SQLite) → Claude → Market Discovery & Consensus\n```\n\nThis val is the first two boxes in that diagram (collector + storage). It does **not**\nrun a schedule yet, and it does **not** do any sentiment/thesis analysis — both are\nintentionally deferred (see \"Phase 2\" below).\n\n## What this val does right now\n\n- Tracks 10 public X accounts (`config.ts`): `@aleabitoreddit`, `@jukan05`, `@octopusycc`,\n `@zephyr_z9`, `@KawzInvests`, `@michaelsikand`, `@ren_stocks`, `@unusual_whales`,\n `@StockMKTNewz`, `@DJTRadar`.\n- Resolves each handle to a numeric X user ID via the official X API v2 (`GET /2/users/by`).\n- Pulls each account's recent posts (`GET /2/users/:id/tweets`), including original posts,\n replies, quotes, and reposts.\n- Normalizes and stores every post in SQLite (`posts` table), classifying `post_type` and\n extracting cashtag tickers mechanically.\n- Is fully manual for now — nothing runs on its own. You trigger it by hitting an HTTP route.\n\n## ⚠️ One credential is required — and it is NOT in this codebase\n\nThe X API requires an app-only **Bearer Token** to read tweets. This project reads it from\nan environment variable and never stores or accepts it any other way — it is never\nhard-coded, never logged, and was never pasted into this chat.\n\n👉 Add X_BEARER_TOKEN here: https://www.val.town/x/efrenlin/x-trader-consensus/environment-variables?key=X_BEARER_TOKEN\n\nSteps to get the token:\n\n1. Go to the X Developer Portal (developer.x.com) and open (or create) a Project + App.\n2. Under the app's \"Keys and tokens\" tab, generate/copy the **Bearer Token** (app-only OAuth 2.0).\n3. Paste it as the value for `X_BEARER_TOKEN` at the link above. Nowhere else.\n\n**Cost note:** the X API no longer has a simple free read tier — it now bills per-resource\non a pay-per-use credit basis (roughly $0.01 per user lookup, ~$0.005 per tweet read, at\ncurrent published rates). Reading 10 accounts × ~25 posts every few hours has a small but\nnon-zero ongoing cost, so make sure the developer account backing this token has billing/\ncredits enabled, or requests will fail with an auth/payment error. Confirm current pricing\nin the X Developer Portal before relying on these numbers — they change.\n\n## Project structure\n\n```\nconfig.ts Tracked account handles + tunables. No credentials.\nlib/schema.ts SQLite schema (idempotent CREATE TABLE IF NOT EXISTS)\nlib/xapi.ts X API v2 client (resolveUsernames, fetchUserTweets)\nlib/normalize.ts Mechanical normalization: post_type, ticker extraction\nlib/collector.ts Orchestrates one full collection pass across all accounts\nmain.ts (http) Manual trigger + inspection routes (see below)\n```\n\n## HTTP routes\n\nBase URL: `https://efrenlin--b9b4c5ceaa0011f1b6d11607ee4eb77e.web.val.run`\n\n- `GET /health` — status: is the token configured, which accounts are resolved, how many\n posts are stored, how many are still awaiting analysis.\n- `GET /run-collector` — runs one collection pass across all 10 accounts. Requires\n `X_BEARER_TOKEN` to be set; otherwise returns `400` with a clear message.\n- `GET /posts?account=&limit=` — inspect normalized posts (JSON), most recent\n first. Omit `account` to see all tracked accounts.\n- `GET /runs` — history of the last 20 collector runs (accounts processed, posts ingested,\n any per-account errors).\n\n## Normalized post schema (`posts` table)\n\nPer-post fields, matching the requested schema:\n\n| Field | Populated by | Notes |\n|---|---|---|\n| `account`, `username` | collector | display name / @handle |\n| `post_id`, `posted_at`, `original_url` | collector | |\n| `post_type` | collector | `original` \\| `reply` \\| `quote` \\| `repost`, from the API's `referenced_tweets` |\n| `referenced_post_id` | collector | underlying tweet id for reply/quote/repost — the join key for dedup |\n| `text` | collector | raw post text |\n| `mentioned_tickers` | collector | mechanical `$CASHTAG` regex + API cashtag entities — JSON array |\n| `engagement_metrics` | collector | retweet/reply/like/quote/impression counts — JSON object |\n| `ingested_at` | collector | when this row was written |\n| `explicit_stance` | **Claude (Phase 2)** | `Bullish` \\| `Bearish` \\| `Unclear` — left `NULL` until analyzed |\n| `evidence` | **Claude (Phase 2)** | supporting excerpt for the stance |\n| `thesis`, `catalyst`, `time_horizon` | **Claude (Phase 2)** | left `NULL` until analyzed |\n| `position_disclosure` | **Claude (Phase 2)** | only ever filled when the post *explicitly* states one |\n| `analyzed_at` | **Claude (Phase 2)** | timestamp once a row has been analyzed |\n\n### Why the analytical fields are NULL right now\n\nThe spec is explicit: \"Do not infer a position unless explicitly supported by the post,\"\nand \"Not Mentioned is NOT Neutral.\" A regex/keyword collector cannot safely make that\njudgment call — it would either under- or over-infer sentiment. So this collector only\ndoes the *mechanical* half of normalization (who/when/what type/which tickers were\nliterally mentioned/how the post is linked to an original). The semantic half\n(`explicit_stance`, `evidence`, `thesis`, `catalyst`, `time_horizon`, `position_disclosure`)\nis left for a dedicated Claude analysis pass that reads each post's `text` and fills those\ncolumns in — auditable, and never guessed by the pipeline itself.\n\n## Deduplication, as built so far\n\n- **Exact duplicates** (the same `post_id` seen twice, e.g. a re-run): prevented at the DB\n level — `post_id` is the primary key, inserts use `ON CONFLICT DO NOTHING`.\n- **Reposts and quote-tweets of the same original**: every reply/quote/repost stores\n `referenced_post_id`, the id of the underlying tweet. This is the join key a later\n consensus step uses to collapse \"5 accounts reposted the same tweet\" into one underlying\n event rather than 5 independent signals.\n- **Repeated references to the same underlying thesis/event across *different* original\n posts** (e.g. two different accounts each write their own tweet about the same earnings\n beat): this requires semantic clustering, not string matching — it's explicitly part of\n Phase 2 (Claude analysis), not something this collector attempts.\n\n## Consensus counting, as specified\n\n\"Consensus must ultimately count unique independent accounts, not raw post count.\" This\ncollector stores everything needed to compute that correctly later (`username` per post,\n`referenced_post_id` for de-duplicating shared reposts, `explicit_stance` once analyzed),\nbut does not compute a consensus score itself — that belongs to the \"Market Discovery &\nConsensus\" stage downstream, once `explicit_stance` is populated. Roughly:\n\n```sql\n-- Sketch only — meaningful once explicit_stance is populated by Phase 2.\nSELECT mentioned_tickers, explicit_stance, COUNT(DISTINCT username) AS unique_accounts\nFROM posts\nWHERE explicit_stance IS NOT NULL\nGROUP BY mentioned_tickers, explicit_stance;\n```\n\n## Phase 2 (not built yet, on purpose)\n\n1. **Claude analysis pass** — reads posts where `analyzed_at IS NULL`, and for each one\n (using only that post's own text/evidence) fills `explicit_stance`, `evidence`, `thesis`,\n `catalyst`, `time_horizon`, and `position_disclosure` (only if explicitly stated), then\n sets `analyzed_at`.\n2. **Consensus aggregation** — groups analyzed posts by ticker, counts unique independent\n accounts per stance (collapsing reposts/quotes of the same underlying post via\n `referenced_post_id`), and surfaces disagreement/consensus per ticker.\n3. **Whole-market discovery** — today `config.ts` hardcodes 10 accounts and ticker\n extraction is cashtag-only; broader discovery (more accounts, sourcing tickers beyond\n explicit cashtags, ranking/scoring) is future work once this pipeline is validated.\n4. **The 3-hour schedule** — intentionally not created. Once you've confirmed\n `/run-collector` works end-to-end with a real token, say the word and an `interval`\n file can be added on a cron (nothing else about this val needs to change).\n\n## Manual testing\n\n```\nGET /health → confirms token status + current row counts\nGET /run-collector → pulls new posts right now (needs the token)\nGET /posts?limit=20 → see what got normalized and stored\nGET /runs → see run history / per-account errors\n```\n",[155],"72334838-2080-4896-9e3b-4958397be514","lib","8dccd4ba-aa00-11f1-906c-1607ee4eb77e",2,["D",1788705478068],"directory",[163],"pulls","branches","forks","envVars",{"_176":178,"_179":64},"connections",{"_176":64,"_177":64},"count","unfilledCount",7,"emptyCount",{"_44":162,"_32":163,"_30":164,"_138":-5,"_42":43,"_41":165,"_140":60,"_39":263,"_13":64,"_34":167,"_36":-5,"_24":25,"_145":-5,"_193":264,"_27":265},{"_44":255,"_32":256,"_30":257,"_138":164,"_42":43,"_41":258,"_140":60,"_39":259,"_13":64,"_34":35,"_36":260,"_24":25,"_145":-5,"_193":261,"_27":262},{"_44":246,"_32":247,"_30":248,"_138":164,"_42":43,"_41":249,"_140":60,"_39":250,"_13":251,"_34":35,"_36":252,"_24":25,"_145":-5,"_193":253,"_27":254},{"_44":238,"_32":239,"_30":240,"_138":164,"_42":43,"_41":241,"_140":25,"_39":242,"_13":165,"_34":35,"_36":243,"_24":25,"_145":-5,"_193":244,"_27":245},{"_44":230,"_32":231,"_30":232,"_138":164,"_42":43,"_41":233,"_140":60,"_39":234,"_13":165,"_34":35,"_36":235,"_24":25,"_145":-5,"_193":236,"_27":237},{"_44":222,"_32":223,"_30":224,"_138":164,"_42":43,"_41":225,"_140":25,"_39":226,"_13":64,"_34":35,"_36":227,"_24":25,"_145":-5,"_193":228,"_27":229},{"_44":214,"_32":215,"_30":216,"_138":164,"_42":43,"_41":217,"_140":60,"_39":218,"_13":165,"_34":35,"_36":219,"_24":25,"_145":-5,"_193":220,"_27":221},{"_44":210,"_32":33,"_30":31,"_138":164,"_42":43,"_41":14,"_140":60,"_39":211,"_13":178,"_34":35,"_36":38,"_24":25,"_145":-5,"_193":212,"_27":213},{"_44":202,"_32":203,"_30":204,"_138":164,"_42":43,"_41":205,"_140":60,"_39":206,"_13":165,"_34":35,"_36":207,"_24":25,"_145":-5,"_193":208,"_27":209},{"_44":154,"_32":155,"_30":156,"_138":-5,"_42":43,"_41":157,"_140":60,"_39":199,"_13":159,"_34":20,"_36":160,"_24":25,"_145":-5,"_193":200,"_27":201},{"_44":147,"_32":148,"_30":149,"_138":-5,"_42":43,"_41":150,"_140":60,"_39":196,"_13":64,"_34":35,"_36":152,"_24":25,"_145":-5,"_193":197,"_27":198},{"_44":136,"_32":137,"_30":58,"_138":-5,"_42":43,"_41":139,"_140":25,"_39":192,"_13":142,"_34":143,"_36":144,"_24":25,"_145":-5,"_193":194,"_27":195},["D",1788792296141],"children",[],[137],["D",1788705487606],[],[148],["D",1788705694497],[],[155],"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",[],[163,203],"e4fe65e4-6d86-4f40-be1a-cce0dcf04989",["D",1788840389768],[],[163,33],"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",[],[163,215],"cab8c63c-66cc-48ca-a31e-d69e82087133","marketDiscovery.ts","a87f9822-aaca-11f1-b073-1607ee4eb77e",51,["D",1788792281213],"// 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\";\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}\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 const consensusRows = consensusRes.rows as unknown as ConsensusRow[];\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 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 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.`,\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 };\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",[],[163,223],"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",[],[163,231],"efc20e16-b698-43ce-ab1b-6da5be7f2df7","consensus.ts","f051bc72-aa0f-11f1-aadc-1607ee4eb77e",22,["D",1788748782210],"import { sqlite } from \"https://esm.town/v/std/sqlite/main.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 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 */\nexport function computeTickerConsensus(posts: PostRow[]): 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 ensure(t).mechPosts.push(p);\n }\n for (const a of safeParseSemantic(p.semantic_tickers)) {\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, 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 const results = computeTickerConsensus(posts);\n const computed_at = new Date().toISOString();\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 results,\n };\n}\n",[],[163,239],"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",[],[163,247],"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",[],[163,256],["D",1788705478068],[181,182,183,184,185,186,187,188],[163],["D",1788705462303],["D",1789572079973],{"_101":100,"_109":105},{"_101":100,"_102":103,"_104":105,"_18":56,"_106":107,"_108":25},{"_13":90,"_32":91,"_44":43,"_92":45,"_93":-5,"_94":-5,"_95":-5,"_96":-5,"_97":-5,"_98":46,"_99":100},"ctx",{"_83":-5,"_129":304,"_305":306,"_307":308,"_309":25,"_310":25,"_311":25,"_312":60,"_313":-5,"_314":-7,"_315":-7,"_316":64,"_317":25,"_318":25,"_319":-5,"_320":25,"_321":25,"_322":25,"_323":-5},"ENV",{"_275":276,"_277":278,"_279":91,"_280":281,"_282":283,"_284":285,"_286":287,"_288":289,"_290":291,"_292":293,"_294":295,"_296":297,"_298":299,"_300":301,"_302":303},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","fdb67791413f4386b4dbf18f354029a617f6160f","RENDER_GIT_BRANCH","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]

Diff · v54