Database Schema — Spec

users

Column	Type	Notes
id	Integer PK
username	String(50)	Unique, indexed
hashed_password	String(255)	bcrypt hash
is_active	Boolean	False until admin approves the account
is_admin	Boolean	Admins can approve new user registrations
created_at	DateTime	UTC

One-to-many → user_sessions (cascade delete) and portfolio_snapshots (cascade delete).

user_sessions

Column	Type	Notes
id	Integer PK
user_id	FK → users.id
token	String	secrets.token_urlsafe(32); unique, indexed
created_at	DateTime	UTC
expires_at	DateTime	UTC; 30-day TTL set at creation

tickers

Column	Type	Notes
symbol	String(20)	Plain symbol (US) or 6-digit code (KR)
market	String(5)	"US" or "KR"
name	String(100)	Display name
sector	String(50)	e.g. "big_tech", "semiconductor" (nullable)
active	Boolean	False = soft-deleted
added_at	DateTime	UTC

Unique constraint on (symbol, market). Index on (market, active).
Seeded from config.py by _seed_tickers() on every init_db() call (idempotent).

stock_prices

Column	Type	Notes
ticker	String(20)	Plain symbol (US) or 6-digit code (KR)
market	String(5)	"US" or "KR"
date	DateTime	UTC
open/high/low/close/volume	Float	OHLCV
created_at	DateTime	UTC

Index: ix_stock_prices_ticker_date on (ticker, date).

news_articles

Column	Type	Notes
ticker	String(20)	Primary ticker (nullable)
market	String(5)	"US" or "KR"
language	String(5)	"en" or "ko"
title	Text	Original headline
title_en	Text	Translation cache (nullable)
title_ko	Text	Translation cache (nullable)
url	Text	Unique — prevents duplicates
published_at	DateTime	UTC
fetched_at	DateTime	UTC

One-to-one → sentiment_scores via article.sentiment.
Index: ix_news_articles_ticker_published on (ticker, published_at).

sentiment_scores

Column	Type	Notes
article_id	FK → news_articles.id	One score per article
ticker	String(20)	Denormalized from article
score	Float	-1.0 to +1.0
label	String(10)	"positive" / "negative" / "neutral"
confidence	Float	Damped by evidence count for KO
model_used	String(50)	"en_finbert" / "kr_finbert" / "vader_finance" / "ko_lexicon" / "none"

Index: ix_sentiment_ticker_created on (ticker, created_at).

predictions

Column	Type	Notes
ticker	String(20)
market	String(5)	"US" or "KR"
horizon	String(5)	"1d" / "7d" / "30d"
direction	String(10)	"up" / "down" / "neutral"
confidence	Float	0.0–1.0 (raw proba)
sentiment_score_avg	Float	7-day avg sentiment at prediction time
momentum_score	Float	5-day momentum at prediction time
news_volume	Integer	Article count in 7-day window
target_date	DateTime	UTC; now + horizon days

Index: ix_predictions_ticker_horizon_created on (ticker, horizon, created_at).

prediction_results

Column	Type	Notes
prediction_id	FK → predictions.id	Unique — one result per prediction
ticker	String(20)	Denormalized
market	String(5)	"US" or "KR"
horizon	String(5)	"1d" / "7d" / "30d"
predicted_direction	String(10)	Direction from the original prediction
actual_return	Float	Realized return: (exit - entry) / entry
correct	Boolean	True if predicted direction matches realized return
evaluated_at	DateTime	UTC

Index: ix_prediction_results_ticker_horizon on (ticker, horizon).

alerts

Column	Type	Notes
ticker	String(20)
market	String(5)	"US" or "KR"
direction	String(10)	"up" / "down" / "neutral"
horizon	String(5)	"1d" / "7d" / "30d"
confidence	Float	Must exceed threshold in config.py
prediction_id	FK → predictions.id	Unique — one alert per prediction
triggered_at	DateTime	UTC
seen	Boolean	False until user dismisses in UI

Index: ix_alerts_seen on (seen).

trending_topics

Column	Type	Notes
sector	String(50)	Sector name
keyword	String(100)	Top keyword for this sector
frequency	Integer	Mention count in the aggregation window
avg_sentiment	Float	Average sentiment score across all articles
tickers_involved	Text	JSON list of ticker symbols
period_start / period_end	DateTime	UTC window
created_at	DateTime	UTC

fundamentals

Column	Type	Notes
ticker	String(20)
market	String(5)	"US" or "KR"
period_end	DateTime	Quarter/year end date
revenue	Float	Nullable
net_income	Float	Nullable
eps	Float	Nullable
total_debt	Float	Nullable
total_equity	Float	Nullable
pe_ratio	Float	Nullable
fetched_at	DateTime	UTC

Unique constraint on (ticker, period_end). Index on (ticker, market).
Populated by the fetch_fundamentals 24-hour scheduler job via financialdatasets.ai.

daily_summaries

Column	Type	Notes
id	Integer PK
generated_at	DateTime	UTC — when Claude generated this briefing
summary_text	Text	3–4 sentence plain-prose market briefing
model_used	String(50)	Claude model ID returned by the API
input_tokens	Integer	Prompt token count
output_tokens	Integer	Completion token count
market_data_json	Text	JSON snapshot of signals fed to the prompt (for auditability)

One row per generation run. The API always returns the most recent row. Populated by the generate_daily_summary 24-hour scheduler job. Requires ANTHROPIC_API_KEY to be set; job is a no-op when the key is absent.

telegram_notifications

Column	Type	Notes
id	Integer PK
kind	String(20)	"alert" / "sentiment_spike" / "test"
ref_id	Integer	Alert.id for "alert" kind; null for other kinds
ticker	String(20)	Nullable — set for per-ticker alerts
sector	String(50)	Nullable — set for sentiment spike notifications
message_text	Text	Full HTML message text that was sent
sent_at	DateTime	UTC — when the send was attempted
success	Boolean	True if Telegram API returned 2xx
error	Text	Nullable — error message on failure

Indexes: ix_tgnotif_kind_ref on (kind, ref_id) for deduplication lookups; ix_tgnotif_sent_at on (sent_at) for last-sent queries.
Used by notifications/telegram.py. Every send attempt is recorded regardless of success — failures have success=False and a non-null error. The "alert" dedup guard queries by (kind, ref_id, success=True). The "sentiment_spike" dedup guard queries by (kind, sector, sent_at >= now-12h, success=True).

earnings_events

Column	Type	Notes
id	Integer PK
ticker	String(20)	US symbol (e.g. "AAPL")
market	String(5)	"US" only — KRX earnings not fetched via yfinance
earnings_date	DateTime	UTC midnight on earnings release date
eps_estimate	Float	Analyst consensus EPS estimate; nullable
eps_actual	Float	Reported EPS; null until the quarter has been reported
surprise_pct	Float	(actual - estimate) / |estimate| * 100; null when either value is missing
fetched_at	DateTime	UTC — when this row was last refreshed

Unique constraint uq_earnings_ticker_date on (ticker, earnings_date) enables clean upserts.
Indexes: ix_earnings_ticker_date on (ticker, earnings_date); ix_earnings_date on (earnings_date) for calendar-window queries.
Populated by the fetch_earnings 24-hour scheduler job via data/earnings_fetcher.py (yfinance get_earnings_dates()). Covers ~3 months back and ~4 months forward.

political_trades

Column	Type	Notes
id	Integer PK
trader_name	String(100)	Full name; from POLITICAL_WATCHLIST in config.py
trader_role	String(100)	E.g. "House (D-CA)", "DOGE / Tesla insider" (nullable)
ticker	String(20)	US symbol (e.g. "NVDA")
market	String(5)	"US" (only US disclosures tracked currently)
trade_type	String(10)	"buy" or "sell"
amount_low	Float	Lower bound of STOCK Act–disclosed range (USD); nullable
amount_high	Float	Upper bound; nullable; equals amount_low for EDGAR exact amounts
traded_at	DateTime	UTC midnight on trade date
filed_at	DateTime	UTC midnight on STOCK Act filing date; nullable
source	String(50)	"capitol_trades" or "edgar_form4"
source_url	Text	Direct link to the filing or politician page; nullable
fetched_at	DateTime	UTC — when this row was scraped

Unique constraint uq_political_trade on (trader_name, ticker, traded_at, trade_type) prevents duplicate inserts across polling cycles.
Indexes: ix_political_trades_ticker_traded on (ticker, traded_at); ix_political_trades_trader on (trader_name).
Populated by the fetch_political_trades 6-hour scheduler job (scraper/political_trades.py). Congressional trades are delayed up to 45 days by STOCK Act law; EDGAR Form 4 insiders must file within 2 business days.

portfolio_snapshots

Column	Type	Notes
id	Integer PK
user_id	FK → users.id	Nullable
imported_at	DateTime	UTC
total_assets_krw	Float	Total portfolio value in KRW
today_pnl_krw / today_pnl_pct	Float	Day P&L
kr_purchase_krw / kr_current_krw / kr_pnl_krw / kr_return_pct	Float	Korean holdings summary
us_purchase_krw / us_current_krw / us_pnl_krw / us_return_pct	Float	US holdings summary (KRW-converted)
cash_krw / cash_usd	Float	Cash balances
usd_krw_rate	Float	USD/KRW FX rate at snapshot time
tokens_input / tokens_output / tokens_total	Integer	Gemini token counts

One-to-many → portfolio_holdings (cascade delete).

portfolio_holdings

Column	Type	Notes
id	Integer PK
snapshot_id	FK → portfolio_snapshots.id
market	String(5)	"US" or "KR"
ticker	String(20)	KRX code or US symbol; None if unmappable
name_kr	String(100)	Korean name as shown in brokerage app
quantity	Integer
current_price / avg_cost	Float	Local currency
pnl_local / pnl_pct	Float	P&L in local currency and %
current_value_krw / purchase_value_krw	Float	KRW-converted values

Index: ix_portfolio_holdings_snapshot_market on (snapshot_id, market).

trade_logs

Column	Type	Notes
id	Integer PK
user_id	FK → users.id	Nullable
ticker	String(20)
market	String(5)	"US" or "KR"
action	String(10)	"buy" or "sell"
quantity	Float
price	Float	Local currency
trade_date	Date
notes	Text	Nullable — free-text journal entry
created_at	DateTime	UTC

Indexes: ix_trade_logs_user_ticker on (user_id, ticker); ix_trade_logs_user_date on (user_id, trade_date).
Populated by the Trade Journal UI.

macro_indicators

Column	Type	Notes
id	Integer PK
date	Date	Unique; one row per trading day
vix	Float	CBOE VIX closing value; nullable
yield_10y	Float	10-year Treasury yield (%); nullable
yield_3m	Float	3-month Treasury bill yield (%); nullable
yield_spread	Float	yield_10y − yield_3m; nullable
fetched_at	DateTime	UTC

Unique constraint on date. Index on date.
Populated by the fetch_macro 24-hour scheduler job via data/macro_fetcher.py (yfinance ^VIX, ^TNX, ^IRX). On first run (empty table) the job backfills 180 days of history so training samples have aligned macro context. Feeds vix_level and yield_spread_norm features in models/predictor.py (US tickers only).