[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_253":254,"_255":256},"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",38,"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/collector.ts","latestPath","fileId","b4a2dfbc-aa00-11f1-b9bc-1607ee4eb77e","name","collector.ts","fileType","script","content","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","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 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","createdAt",["D",1788785329255],"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":248,"_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":249,"_73":-5,"_74":75,"_76":75,"_77":78,"_79":78,"_80":78,"_81":75,"_82":75,"_83":250,"_85":251,"_87":252,"_89":25},"tree",[180,187,188,189],"files",[180,181,182,183,184,185,186,187,188,189],"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},"6887c52d-a142-4df6-8146-fd10b605ed13","main.ts","parentId",33,"latest",["D",1788756513978],15,"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\";\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 // 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 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 return Response.json({\n ok: false,\n error: \"Not found\",\n routes: [\"/health\", \"/run-collector\", \"/posts\", \"/runs\", \"/consensus\", \"/consensus/latest\", \"/discovery/candidates\", \"/discovery/accounts\"],\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":245,"_13":64,"_34":167,"_36":-5,"_24":25,"_145":-5,"_191":246,"_27":247},{"_44":241,"_32":33,"_30":31,"_138":164,"_42":43,"_41":14,"_140":25,"_39":242,"_13":165,"_34":35,"_36":38,"_24":25,"_145":-5,"_191":243,"_27":244},{"_44":233,"_32":234,"_30":235,"_138":164,"_42":43,"_41":236,"_140":25,"_39":237,"_13":165,"_34":35,"_36":238,"_24":25,"_145":-5,"_191":239,"_27":240},{"_44":225,"_32":226,"_30":227,"_138":164,"_42":43,"_41":228,"_140":25,"_39":229,"_13":64,"_34":35,"_36":230,"_24":25,"_145":-5,"_191":231,"_27":232},{"_44":217,"_32":218,"_30":219,"_138":164,"_42":43,"_41":220,"_140":60,"_39":221,"_13":165,"_34":35,"_36":222,"_24":25,"_145":-5,"_191":223,"_27":224},{"_44":208,"_32":209,"_30":210,"_138":164,"_42":43,"_41":211,"_140":25,"_39":212,"_13":213,"_34":35,"_36":214,"_24":25,"_145":-5,"_191":215,"_27":216},{"_44":200,"_32":201,"_30":202,"_138":164,"_42":43,"_41":203,"_140":60,"_39":204,"_13":165,"_34":35,"_36":205,"_24":25,"_145":-5,"_191":206,"_27":207},{"_44":154,"_32":155,"_30":156,"_138":-5,"_42":43,"_41":157,"_140":60,"_39":197,"_13":159,"_34":20,"_36":160,"_24":25,"_145":-5,"_191":198,"_27":199},{"_44":147,"_32":148,"_30":149,"_138":-5,"_42":43,"_41":150,"_140":60,"_39":194,"_13":64,"_34":35,"_36":152,"_24":25,"_145":-5,"_191":195,"_27":196},{"_44":136,"_32":137,"_30":58,"_138":-5,"_42":43,"_41":139,"_140":25,"_39":190,"_13":142,"_34":143,"_36":144,"_24":25,"_145":-5,"_191":192,"_27":193},["D",1788756513978],"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,201],"28194c91-5f3b-4271-a65d-8a87152832a9","schema.ts","9e4e244c-aa00-11f1-a2c0-1607ee4eb77e",37,["D",1788785323359],4,"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",[],[163,209],"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,218],"24ff18b8-66ca-46d0-8cec-e246ad5f1c65","discovery.ts","6a1cfe3c-aa69-11f1-a8de-1607ee4eb77e",27,["D",1788750515362],"import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { TRACKED_ACCOUNTS } from \"../config.ts\";\n\n// ---------------------------------------------------------------------------\n// Phase 3: Trader Discovery Layer — MECHANICAL extraction pass only.\n//\n// This module finds candidate accounts using nothing but structural/text\n// signals already present in the stored posts — no invented data, no API\n// calls. It deliberately does NOT assign a final account_group,\n// specialization label, or finished relevance_score: those require reading\n// and understanding what the evidence actually says (is this genuine\n// analysis, is it promotional, what sector is it about), which is a\n// semantic judgment call — kept separate from mechanical extraction here,\n// the same mechanical/semantic split used throughout this project. The\n// mechanical output below is reviewed and finished by a semantic pass\n// before anything is written to discovered_accounts.\n//\n// Real signals available in the currently-collected data (documented\n// honestly because several signals the spec suggests are NOT available):\n//\n// 1. Classic retweets (post_type = 'repost'): the X API returns the FULL\n// original text prefixed \"RT @handle: ...\", so the retweeted account's\n// real handle is recoverable by regex directly from `text`. This is a\n// solid, real signal — the collector's own repost/quote/reply\n// classification already exists (lib/normalize.ts).\n//\n// 2. @mentions inside reply/quote/original text: recoverable by regex, but\n// much noisier — most are casual social replies to random accounts, not\n// market intelligence. Filtered hard below (see isSubstantive).\n//\n// NOT available in current data (a real limitation, reported honestly in\n// Phase 3's validation report rather than worked around):\n// - `referenced_author_id` is defined in the schema but was never actually\n// populated by the collector (normalizeTweet() never extracts the\n// author_id off `includes.users` for quote/reply targets) — so for the\n// 142 quote+reply posts, we know THAT they reference another tweet but\n// not WHO wrote it, unless a handle happens to appear in the reply/quote\n// text itself. Resolving this fully would need either a code fix to\n// capture `includes.users` from the existing API response (free — no new\n// credits) or, for older already-ingested rows, a follow-up GET\n// /2/tweets?ids= call to backfill authors of already-known\n// referenced_post_ids (costs API credits).\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}\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}\n\nexport interface MechanicalCandidate {\n handle: string;\n times_referenced: number;\n referencing_core_accounts: string[];\n evidence: EvidenceItem[];\n tickers_discussed: string[]; // mechanical, tentatively attributed from the citing/reposted content\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}\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 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 }>();\n\n const ensure = (h: string) => {\n if (!byHandle.has(h)) byHandle.set(h, { referencing: new Set(), evidence: [], tickers: new Set() });\n return byHandle.get(h)!;\n };\n\n const addEvidence = (handle: string, p: PostRow) => {\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 });\n for (const t of JSON.parse(p.mentioned_tickers || \"[]\")) acc.tickers.add(t);\n };\n\n for (const p of posts) {\n if (p.post_type === \"repost\") {\n // Signal 1: classic retweet — original author's handle is literally\n // in the text as \"RT @handle: ...\".\n const m = p.text.match(/^RT @(\\w+):/);\n if (m) addEvidence(m[1], p);\n } else {\n // Signal 2: @mentions anywhere in quote/reply/original commentary.\n const mentions = [...p.text.matchAll(/@(\\w+)/g)].map((m) => m[1]);\n for (const handle of new Set(mentions)) addEvidence(handle, p);\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 });\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 notes = [\n \"referenced_author_id is present in the schema but was never populated by the collector for quote/reply posts — discovery from those 142 posts relies entirely on @mentions appearing in the reply/quote text itself, not on a resolved author id.\",\n \"No new X API calls were made for this pass — every candidate here comes from text already stored from the original 10-account collection.\",\n \"Ticker/company attribution for repost-derived candidates (e.g. content originally authored by a reposted account) is tentative: it reflects the mechanical mentioned_tickers of the repost post, attributed to the original author whose words those are.\",\n ];\n\n return { candidates, rejected, notes };\n}\n",[],[163,226],"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,234],"0ec2cb33-69bf-46d7-ad2e-787d68631ff6",["D",1788785329255],[],[163,33],["D",1788705478068],[181,182,183,184,185,186],[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":286,"_287":288,"_289":290,"_291":25,"_292":25,"_293":25,"_294":60,"_295":-5,"_296":-7,"_297":-7,"_298":64,"_299":25,"_300":25,"_301":-5,"_302":25,"_303":25,"_304":25,"_305":-5},"ENV",{"_257":258,"_259":260,"_261":91,"_262":263,"_264":265,"_266":267,"_268":269,"_270":271,"_272":273,"_274":275,"_276":277,"_278":279,"_280":281,"_282":283,"_284":285},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","5015b8df46402d8bba4f485ecc37aec202b0e06b","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 · v38