Phase 1 backend/data layer for a Market Discovery & Consensus system.

X API  →  Val Town collector  →  normalized storage (SQLite)  →  Claude  →  Market Discovery & Consensus

This val is the first two boxes in that diagram (collector + storage). It does not run a schedule yet, and it does not do any sentiment/thesis analysis — both are intentionally deferred (see "Phase 2" below).

• Tracks 10 public X accounts (config.ts): @aleabitoreddit, @jukan05, @octopusycc, @zephyr_z9, @KawzInvests, @michaelsikand, @ren_stocks, @unusual_whales, @StockMKTNewz, @DJTRadar.
• Resolves each handle to a numeric X user ID via the official X API v2 (GET /2/users/by).
• Pulls each account's recent posts (GET /2/users/:id/tweets), including original posts, replies, quotes, and reposts.
• Normalizes and stores every post in SQLite (posts table), classifying post_type and extracting cashtag tickers mechanically.
• Is fully manual for now — nothing runs on its own. You trigger it by hitting an HTTP route.

The X API requires an app-only Bearer Token to read tweets. This project reads it from an environment variable and never stores or accepts it any other way — it is never hard-coded, never logged, and was never pasted into this chat.

👉 Add X_BEARER_TOKEN here: https://www.val.town/x/efrenlin/x-trader-consensus/environment-variables?key=X_BEARER_TOKEN

Steps to get the token:

1. Go to the X Developer Portal (developer.x.com) and open (or create) a Project + App.
2. Under the app's "Keys and tokens" tab, generate/copy the Bearer Token (app-only OAuth 2.0).
3. Paste it as the value for X_BEARER_TOKEN at the link above. Nowhere else.

Cost note: the X API no longer has a simple free read tier — it now bills per-resource on a pay-per-use credit basis (roughly $0.01 per user lookup, ~$0.005 per tweet read, at current published rates). Reading 10 accounts × ~25 posts every few hours has a small but non-zero ongoing cost, so make sure the developer account backing this token has billing/ credits enabled, or requests will fail with an auth/payment error. Confirm current pricing in the X Developer Portal before relying on these numbers — they change.

config.ts            Tracked account handles + tunables. No credentials.
lib/schema.ts         SQLite schema (idempotent CREATE TABLE IF NOT EXISTS)
lib/xapi.ts           X API v2 client (resolveUsernames, fetchUserTweets)
lib/normalize.ts       Mechanical normalization: post_type, ticker extraction
lib/collector.ts       Orchestrates one full collection pass across all accounts
main.ts (http)         Manual trigger + inspection routes (see below)

Base URL: https://efrenlin--b9b4c5ceaa0011f1b6d11607ee4eb77e.web.val.run

• GET /health — status: is the token configured, which accounts are resolved, how many posts are stored, how many are still awaiting analysis.
• GET /run-collector — runs one collection pass across all 10 accounts. Requires X_BEARER_TOKEN to be set; otherwise returns 400 with a clear message.
• GET /posts?account=<username>&limit=<n> — inspect normalized posts (JSON), most recent first. Omit account to see all tracked accounts.
• GET /runs — history of the last 20 collector runs (accounts processed, posts ingested, any per-account errors).

Per-post fields, matching the requested schema:

The spec is explicit: "Do not infer a position unless explicitly supported by the post," and "Not Mentioned is NOT Neutral." A regex/keyword collector cannot safely make that judgment call — it would either under- or over-infer sentiment. So this collector only does the mechanical half of normalization (who/when/what type/which tickers were literally mentioned/how the post is linked to an original). The semantic half (explicit_stance, evidence, thesis, catalyst, time_horizon, position_disclosure) is left for a dedicated Claude analysis pass that reads each post's text and fills those columns in — auditable, and never guessed by the pipeline itself.

• Exact duplicates (the same post_id seen twice, e.g. a re-run): prevented at the DB level — post_id is the primary key, inserts use ON CONFLICT DO NOTHING.
• Reposts and quote-tweets of the same original: every reply/quote/repost stores referenced_post_id, the id of the underlying tweet. This is the join key a later consensus step uses to collapse "5 accounts reposted the same tweet" into one underlying event rather than 5 independent signals.
• Repeated references to the same underlying thesis/event across different original posts (e.g. two different accounts each write their own tweet about the same earnings beat): this requires semantic clustering, not string matching — it's explicitly part of Phase 2 (Claude analysis), not something this collector attempts.

"Consensus must ultimately count unique independent accounts, not raw post count." This collector stores everything needed to compute that correctly later (username per post, referenced_post_id for de-duplicating shared reposts, explicit_stance once analyzed), but does not compute a consensus score itself — that belongs to the "Market Discovery & Consensus" stage downstream, once explicit_stance is populated. Roughly:

-- Sketch only — meaningful once explicit_stance is populated by Phase 2.
SELECT mentioned_tickers, explicit_stance, COUNT(DISTINCT username) AS unique_accounts
FROM posts
WHERE explicit_stance IS NOT NULL
GROUP BY mentioned_tickers, explicit_stance;

1. Claude analysis pass — reads posts where analyzed_at IS NULL, and for each one (using only that post's own text/evidence) fills explicit_stance, evidence, thesis, catalyst, time_horizon, and position_disclosure (only if explicitly stated), then sets analyzed_at.
2. Consensus aggregation — groups analyzed posts by ticker, counts unique independent accounts per stance (collapsing reposts/quotes of the same underlying post via referenced_post_id), and surfaces disagreement/consensus per ticker.
3. Whole-market discovery — today config.ts hardcodes 10 accounts and ticker extraction is cashtag-only; broader discovery (more accounts, sourcing tickers beyond explicit cashtags, ranking/scoring) is future work once this pipeline is validated.
4. The 3-hour schedule — intentionally not created. Once you've confirmed /run-collector works end-to-end with a real token, say the word and an interval file can be added on a cron (nothing else about this val needs to change).

GET /health           → confirms token status + current row counts
GET /run-collector     → pulls new posts right now (needs the token)
GET /posts?limit=20    → see what got normalized and stored
GET /runs              → see run history / per-account errors