[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_242":243,"_244":245},"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",26,"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","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: 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","createdAt",["D",1788750228533],"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":237,"_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":238,"_73":-5,"_74":75,"_76":75,"_77":78,"_79":78,"_80":78,"_81":75,"_82":75,"_83":239,"_85":240,"_87":241,"_89":25},"tree",[179,185,186,187],"files",[179,180,181,182,183,184,185,186,187],"counts",{"_168":64,"_169":158,"_170":64,"_171":172,"_173":174},"contents",[132,133,134,135],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_44":161,"_32":162,"_30":163,"_138":-5,"_42":43,"_41":164,"_140":60,"_39":165,"_13":64,"_34":166,"_36":-5,"_24":25,"_145":-5,"_27":167},{"_44":154,"_32":155,"_30":156,"_138":-5,"_42":43,"_41":142,"_140":60,"_39":157,"_13":158,"_34":20,"_36":159,"_24":25,"_145":-5,"_27":160},{"_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},"13125f55-c4ee-49de-9b29-7c0e62b461a0","main.ts","parentId",25,"latest",["D",1788748800814],9,"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\";\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 const summary = await runCollector();\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 return Response.json({\n ok: false,\n error: \"Not found\",\n routes: [\"/health\", \"/run-collector\", \"/posts\", \"/runs\", \"/consensus\", \"/consensus/latest\"],\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",["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",[162],"pulls","branches","forks","envVars",{"_175":177,"_178":64},"connections",{"_175":64,"_176":64},"count","unfilledCount",7,"emptyCount",{"_44":161,"_32":162,"_30":163,"_138":-5,"_42":43,"_41":164,"_140":60,"_39":234,"_13":64,"_34":166,"_36":-5,"_24":25,"_145":-5,"_189":235,"_27":236},{"_44":226,"_32":227,"_30":228,"_138":163,"_42":43,"_41":229,"_140":25,"_39":230,"_13":158,"_34":35,"_36":231,"_24":25,"_145":-5,"_189":232,"_27":233},{"_44":218,"_32":219,"_30":220,"_138":163,"_42":43,"_41":221,"_140":25,"_39":222,"_13":164,"_34":35,"_36":223,"_24":25,"_145":-5,"_189":224,"_27":225},{"_44":210,"_32":211,"_30":212,"_138":163,"_42":43,"_41":213,"_140":25,"_39":214,"_13":64,"_34":35,"_36":215,"_24":25,"_145":-5,"_189":216,"_27":217},{"_44":206,"_32":33,"_30":31,"_138":163,"_42":43,"_41":14,"_140":25,"_39":207,"_13":150,"_34":35,"_36":38,"_24":25,"_145":-5,"_189":208,"_27":209},{"_44":198,"_32":199,"_30":200,"_138":163,"_42":43,"_41":201,"_140":25,"_39":202,"_13":158,"_34":35,"_36":203,"_24":25,"_145":-5,"_189":204,"_27":205},{"_44":154,"_32":155,"_30":156,"_138":-5,"_42":43,"_41":142,"_140":60,"_39":195,"_13":158,"_34":20,"_36":159,"_24":25,"_145":-5,"_189":196,"_27":197},{"_44":147,"_32":148,"_30":149,"_138":-5,"_42":43,"_41":150,"_140":60,"_39":192,"_13":64,"_34":35,"_36":152,"_24":25,"_145":-5,"_189":193,"_27":194},{"_44":136,"_32":137,"_30":58,"_138":-5,"_42":43,"_41":139,"_140":25,"_39":188,"_13":142,"_34":143,"_36":144,"_24":25,"_145":-5,"_189":190,"_27":191},["D",1788748800814],"children",[],[137],["D",1788705487606],[],[148],["D",1788705694497],[],[155],"7420946d-3c06-4f9f-b6ee-7d842a54021e","xapi.ts","a368c0c2-aa00-11f1-96d8-1607ee4eb77e",10,["D",1788706299437],"// 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// 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<{ tweets: XTweet[] }> {\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 { tweets: json.data ?? [] };\n}\n",[],[162,199],"9430b53c-5c43-4de8-b878-cf8fb069f84a",["D",1788750228533],[],[162,33],"20030970-e8b1-40a7-ab6f-65eef13ccfc8","normalize.ts","a897b31e-aa00-11f1-a970-1607ee4eb77e",6,["D",1788705523013],"import type { 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\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 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): NormalizedPost {\n const { postType, referencedPostId } = classifyPostType(tweet);\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 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",[],[162,211],"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",[],[162,219],"6f9d2cc6-7816-4314-9dff-5e7e534f77b3","collector.ts","b4a2dfbc-aa00-11f1-b9bc-1607ee4eb77e",11,["D",1788706557434],"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 { 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 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).\nexport async function runCollector(): Promise {\n await ensureSchema();\n const startedAt = new Date().toISOString();\n const errors: { username: string; message: string }[] = [];\n let postsIngested = 0;\n let accountsProcessed = 0;\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 } = await fetchUserTweets(row.account_id, {\n maxResults: MAX_RESULTS_PER_ACCOUNT,\n sinceId: row.last_post_id,\n });\n\n let newestId: string | null = row.last_post_id ?? null;\n\n for (const tweet of tweets) {\n const normalized = normalizeTweet(tweet, {\n username: row.username,\n name: row.display_name ?? row.username,\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, 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.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) postsIngested++;\n if (!newestId || BigInt(tweet.id) > BigInt(newestId)) newestId = tweet.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, errors };\n}\n",[],[162,227],["D",1788705478068],[180,181,182,183,184],[162],["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":275,"_276":277,"_278":279,"_280":25,"_281":25,"_282":25,"_283":60,"_284":-5,"_285":-7,"_286":-7,"_287":64,"_288":25,"_289":25,"_290":-5,"_291":25,"_292":25,"_293":25,"_294":-5},"ENV",{"_246":247,"_248":249,"_250":91,"_251":252,"_253":254,"_255":256,"_257":258,"_259":260,"_261":262,"_263":264,"_265":266,"_267":268,"_269":270,"_271":272,"_273":274},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","e87eb04e707cf64e7e532c8e8ec395f209e17f15","RENDER_GIT_BRANCH","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]

Diff · v26