News Alert System

Data flow, state machine, component map, and database schema

1 — End-to-End Pipeline
External News Sources
RSS feeds, APIs, financial news providers. Raw articles with title, URL, tickers, published date.
Pipeline NewsAlertPipeline
news_alert_pipeline.py
  • Fetch — NewsService.fetch_news_for_watchlist()
  • Evaluate — Haiku LLM scores significance, sentiment, summary
  • Filter — Keep high + critical only
  • Match — SubscriptionService.get_matching_users()
  • Fan-out — INSERT into user_alerts per user (deduped)
Supabase user_alerts
One row per user per article. Denormalized for O(1) card render. Status tracks user action lifecycle.
2 — Subscription Matching Logic
Table alert_subscriptions
Per-user filter rules. Each row is either a ticker watch or a topic watch with a minimum significance threshold.
Ticker match: sub.ticker IN article.tickers
Topic match: Map topic → ticker list, or fuzzy match in ai_analysis
Threshold: article.significance >= sub.min_significance
Special topics: "all" = match everything, "macro" = macro news type
Service SubscriptionService
subscription_service.py
Topic → Ticker Map (hardcoded):
  • AI semiconductors → NVDA, AMD, AVGO, INTC, QCOM, ARM, MRVL, TSM
  • AI infrastructure → NVDA, AMD, AVGO, SMCI, DELL, HPE, VRT, ANET
  • cloud → AMZN, MSFT, GOOGL, ORCL, CRM, SNOW, NET, DDOG
  • software → MSFT, CRM, NOW, ADBE, PLTR, SNOW, DDOG, MDB, PANW, CRWD
  • cybersecurity → PANW, CRWD, FTNT, ZS, S
Significance order: low(0) < medium(1) < high(2) < critical(3)
3 — Alert Status State Machine
active
Pipeline creates
→ Save →
saved
User defers
→ Analyze →
analyzed
Conversation created
active → Pass → dismissed
saved → Pass → dismissed
active → Analyze → analyzed
Inbox tab: active
Saved tab: saved
History tab: analyzed + dismissed
4 — Database Schema
Table user_alerts
idUUID PK
user_slugTEXT FK→team_members
news_idUUID FK→newsUNIQUE(user_slug, news_id)
subscription_idUUID FK→alert_subscriptions
titleTEXT NOT NULL
ai_summaryTEXTHaiku-generated
ai_analysisTEXTHaiku-generated
tickersTEXT[]
primary_tickerTEXT
significanceTEXT NOT NULLlow|medium|high|critical
sentimentTEXT
news_typeTEXT
source_urlTEXT
published_atTIMESTAMPTZ
statusTEXT NOT NULLactive|saved|analyzed|dismissed
conversation_idUUID FK→conversationsset on Analyze
is_readBOOLEANlegacy Phase 1
created_atTIMESTAMPTZ
Table alert_subscriptions
idUUID PK
user_slugTEXT FK→team_members
tickerTEXTe.g. NVDA
topicTEXTe.g. AI semiconductors
min_significanceTEXTdefault: high
channelsTEXT[]default: {webapp}
is_activeBOOLEAN
created_atTIMESTAMPTZ
CHECK: ticker IS NOT NULL OR topic IS NOT NULL
Ref Related Tables
team_membersslug PKHai, Sean, Rui
newsUUID PKingested articles
conversationsUUID PKchat threads
5 — Backend API Routes
FastAPI /api/user-alerts
backend/app/api/routes/user_alerts.py
Method
Path
Purpose
GET
/
List alerts (filter by user_slug, status, limit)
GET
/unread-count
Badge count (active + unread)
POST
/{id}/save
Set status=saved → 404 if invalid ID
POST
/{id}/pass
Set status=dismissed → 404 if invalid ID
POST
/{id}/analyze
Create seeded conversation → AlertChatService
POST
/{id}/read
Legacy: set is_read=true
GET
/subscriptions
List user's subscription rules
POST
/subscriptions
Create ticker or topic subscription
DEL
/subscriptions/{id}
Delete a subscription
POST
/pipeline/run
Manual trigger (dev/testing)
6 — Frontend Component Tree
/scan page
└─ ScanFeed Top-level tabs: My Alerts | All | Alerts | News | Catalysts
├─ MyAlertsFeed Sub-tabs: Inbox | Saved | History — uses useUserAlerts hook
├─ MyAlertCard mode={inbox|saved|history} — actions: Analyze, Save, Pass, SourceLink
└─ MyFeedSettings Inline ticker/topic subscription management
├─ AlertCard (regular scan feed card)
├─ NewsCard
└─ CatalystCard
7 — Realtime Data Flow
Hook useUserAlerts
frontend/lib/hooks/useUserAlerts.ts
  • On mount: fetch all alerts + unread count
  • Computed views: useMemo filters into inbox / saved / history
  • Polling: unread count every 60s (fallback)
  • Realtime: Supabase channel on INSERT/UPDATE
REST
WebSocket
Supabase Database
  • REST: PostgREST API for CRUD
  • Realtime: Postgres NOTIFY → WebSocket
  • Filter: user_slug=eq.{slug}
Service AlertChatService
alert_chat_service.py
Analyze action:
  • Load alert from DB
  • Check idempotency (existing conversation?)
  • Create conversation + seed first message
  • Set status=analyzed, link conversation_id
  • Return redirect URL → /chat?conversation_id=X
8 — User Interaction Flow
🔔
Bell Badge
Unread count
polls every 60s
→
📥
Inbox
Triage: scan title,
significance, tickers
→ Save
→ Analyze
→ Pass
🔖
Saved
Read later
💬
Analyzed
Chat created
→
📋
History
All processed alerts
View Chat or Passed
Legend
External source
Pipeline / Service
Database
Backend API
Frontend
Realtime
Written by Hai & Claude (claude-opus-4-6) | 2026-03-14