# AegisTrade AI — Database Schema & Data Models

## 1. Relational Entity Relationship Diagram

```mermaid
erDiagram
    USERS ||--o{ AUDIT_LOGS : performs
    MARKET_CANDLES ||--o{ FEATURES : derives
    SIGNALS ||--o{ RISK_DECISIONS : evaluated_by
    RISK_DECISIONS ||--o{ ORDERS : authorizes
    ORDERS ||--o{ ORDER_EXECUTIONS : fills
    ORDERS ||--o{ POSITIONS : alters
    POSITIONS ||--o{ BALANCE_SNAPSHOTS : affects
    MODELS ||--o{ SIGNALS : generates

    USERS {
        string id PK
        string username UK
        string password_hash
        string role
        boolean is_active
        datetime created_at
    }

    MODELS {
        string id PK
        string version UK
        string algorithm
        string status "CHALLENGER | CHAMPION | RETIRED"
        float brier_score
        float val_accuracy
        string metrics_json
        string artifact_path
        datetime trained_at
        datetime promoted_at
    }

    SIGNALS {
        string id PK
        string model_id FK
        string symbol
        string timeframe
        string action "LONG | SHORT | NO_TRADE"
        float raw_probability
        float calibrated_probability
        float expected_return
        float expected_risk
        float expected_value
        string regime
        string reason
        datetime timestamp
    }

    RISK_DECISIONS {
        string id PK
        string signal_id FK
        boolean approved
        string rejection_reason
        decimal equity_at_decision
        decimal risk_budget
        decimal approved_quantity
        decimal entry_price
        decimal stop_loss_price
        decimal take_profit_price
        decimal max_loss_estimate
        datetime timestamp
    }

    ORDERS {
        string id PK "client_order_id"
        string exchange_order_id
        string risk_decision_id FK
        string mode "BACKTEST | PAPER | BYBIT_DEMO | LIVE"
        string symbol
        string side "BUY | SELL"
        string order_type "LIMIT | MARKET"
        decimal quantity
        decimal price
        decimal stop_loss
        decimal take_profit
        string status "SUBMITTED | ACCEPTED | FILLED | CANCELLED | REJECTED"
        datetime created_at
        datetime updated_at
    }

    ORDER_EXECUTIONS {
        string id PK
        string order_id FK
        decimal exec_qty
        decimal exec_price
        decimal exec_fee
        string fee_asset
        datetime exec_time
    }

    POSITIONS {
        string id PK
        string symbol
        string mode
        string side "LONG | SHORT"
        decimal quantity
        decimal entry_price
        decimal current_price
        decimal stop_loss
        decimal take_profit
        decimal unrealized_pnl
        decimal realized_pnl
        boolean is_open
        datetime opened_at
        datetime closed_at
    }

    SYSTEM_STATE {
        string key PK
        string value_json
        datetime updated_at
    }

    AUDIT_LOGS {
        string id PK
        string user_id
        string action
        string entity_type
        string entity_id
        string details_json
        string ip_address
        datetime timestamp
    }
```

---

## 2. Table Specifications & Storage Strategies

1. **`SYSTEM_STATE`**:
   - Stores persistent key-value controls, notably `KILL_SWITCH_ACTIVE` (`true`/`false`), `KILL_SWITCH_REASON`, `KILL_SWITCH_TIMESTAMP`, `ACTIVE_TRADING_MODE`, and `DAILY_STARTING_EQUITY`.
   - Guarantees that emergency halts persist over app reboots.
2. **`SIGNALS` & `RISK_DECISIONS`**:
   - Provides full traceability. Every order can be traced directly backwards to the risk gatekeeper approval and the statistical model prediction that initiated it.
3. **Database Compatibility**:
   - Uses SQLAlchemy Core/ORM with SQLite (default local zero-configuration file `data/aegistrade.db`) and PostgreSQL for production deployments via `DATABASE_URL`.
   - All timestamps stored in UTC.