TimescaleDB Schema
All persistent state lives in TimescaleDB (PostgreSQL 17 + time-series extension). Three tables are hypertables partitioned by time. All timestamps are UTC TIMESTAMPTZ.
Entity Relationship Diagram
Table Schemas
market_datahypertableTimescaleDB hypertable — OHLCV + indicators per ticker per timestamp.
| Column | Type | Notes |
|---|---|---|
| ticker | VARCHAR(20) | e.g. SPY, BTC-USDT |
| timestamp | TIMESTAMPTZ | UTC — partition key |
| open / high / low / close | NUMERIC(18,8) | OHLCV fields |
| volume | NUMERIC(18,2) | |
| rsi / macd / bb_upper / bb_lower | NUMERIC(12,6) | Pre-computed indicators |
| asset_class | VARCHAR(20) | crypto / forex / equity |
signalsTrade signal records from the prediction engine, including ensemble breakdown.
| Column | Type | Notes |
|---|---|---|
| id | UUID PK | |
| ticker | VARCHAR(20) | |
| direction | VARCHAR(10) | LONG / SHORT / HOLD |
| confidence | NUMERIC(5,4) | 0.0 – 1.0 |
| ensemble_breakdown | JSONB | Per-model scores + tier weights |
| status | VARCHAR(20) | PENDING / APPROVED / REJECTED / EXPIRED |
| expires_at | TIMESTAMPTZ | Auto-reject after 15 min if HIL pending |
| created_at | TIMESTAMPTZ |
ordersPlaced orders with full lifecycle tracking from submission to fill.
| Column | Type | Notes |
|---|---|---|
| id | UUID PK | |
| signal_id | UUID FK → signals | |
| ticker | VARCHAR(20) | |
| side | VARCHAR(10) | BUY / SELL |
| quantity | NUMERIC(18,8) | |
| price | NUMERIC(18,8) | Limit or market fill price |
| broker | VARCHAR(30) | binance / ibkr / mt4 / paper |
| status | VARCHAR(20) | PENDING / FILLED / CANCELLED / FAILED |
| broker_order_id | VARCHAR(100) | External reference |
| created_at / filled_at | TIMESTAMPTZ |
positionsOpen and closed positions with PnL tracking.
| Column | Type | Notes |
|---|---|---|
| id | UUID PK | |
| ticker | VARCHAR(20) | |
| side | VARCHAR(10) | LONG / SHORT |
| size | NUMERIC(18,8) | |
| entry_price / current_price | NUMERIC(18,8) | |
| unrealized_pnl / realized_pnl | NUMERIC(18,8) | |
| stop_loss / take_profit | NUMERIC(18,8) | |
| status | VARCHAR(20) | OPEN / CLOSED |
| opened_at / closed_at | TIMESTAMPTZ |
pnl_snapshotshypertableTimescaleDB hypertable — periodic NAV + PnL snapshots for portfolio tracking.
| Column | Type | Notes |
|---|---|---|
| timestamp | TIMESTAMPTZ | Partition key |
| total_nav | NUMERIC(18,2) | Net Asset Value |
| daily_pnl / cumulative_pnl | NUMERIC(18,8) | |
| drawdown | NUMERIC(8,6) | Current drawdown fraction |
| open_positions | INTEGER | |
| snapshot_type | VARCHAR(20) | HOURLY / DAILY / EOD |
model_metricsPer-model performance scores used by the PPO meta-controller for weight adjustment.
| Column | Type | Notes |
|---|---|---|
| id | UUID PK | |
| model_name | VARCHAR(50) | arima, xgboost, finbert, etc. |
| accuracy / sharpe / hit_rate | NUMERIC(8,4) | |
| current_weight | NUMERIC(6,4) | Runtime weight (PPO-adjusted) |
| computed_at | TIMESTAMPTZ |
Hypertable Configuration
| Table | Chunk Interval | Compression | Retention |
|---|---|---|---|
| market_data | 7 days | After 7 days | 3 years |
| pnl_snapshots | 1 day | After 30 days | 5 years |
| microstructure_signals | 1 day | After 7 days | 1 year |
Continuous Aggregates
| Materialized View | Source | Interval | Refresh |
|---|---|---|---|
| market_data_hourly | market_data | 1 hour | Real-time |
| market_data_daily | market_data | 1 day | EOD |
| market_data_weekly | market_data | 7 days | Weekly |
Index Strategy
| Table | Columns | Type | Purpose |
|---|---|---|---|
| market_data | (ticker, timestamp) | PK / Hypertable | Primary lookup |
| market_data | (asset_class, timestamp) | BTREE | Asset-class scans |
| orders | (ticker, created_at) | BTREE | Order history by ticker |
| orders | (status, created_at) | BTREE | PENDING order monitoring |
| signals | (ticker, created_at) | BTREE | Recent signal lookup |
| signals | (status, expires_at) | BTREE | Signal expiry cleanup |
| positions | (status, ticker) | BTREE | Open position queries |
| model_metrics | (model_name, computed_at) | BTREE | Scorer lookups |
JSONB Fields
signals.ensemble_breakdown stores per-model confidence scores and tier weights as JSONB — enabling flexible schema evolution without migrations as new models are added. orders.metadata holds broker-specific raw responses for audit trails.