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).
trending_topics¶
Sector/keyword aggregations. tickers_involved stored as a JSON list string.
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).