# Modelo de Base de Datos — PostgreSQL

## Diagrama ER

```mermaid
erDiagram
    users ||--o{ audit_logs : creates
    users ||--o{ bot_configs : manages
    symbols ||--o{ candles : has
    symbols ||--o{ market_snapshots : has
    symbols ||--o{ trades_market : has
    symbols ||--o{ signals : generates
    symbols ||--o{ orders : trades
    symbols ||--o{ positions : holds
    strategies ||--o{ signals : produces
    strategies ||--o{ backtest_runs : tested
    backtest_runs ||--o{ backtest_trades : contains
    orders ||--o{ order_fills : has
    positions ||--o{ position_events : tracks
    risk_profiles ||--o{ daily_risk_state : governs
    notifications ||--o{ notification_deliveries : sends

    users {
        uuid id PK
        string email UK
        string password_hash
        enum role
        boolean is_active
        timestamp created_at
    }

    symbols {
        uuid id PK
        string symbol UK
        string base_asset
        string quote_asset
        boolean is_active
        decimal min_qty
        decimal tick_size
    }

    candles {
        uuid id PK
        uuid symbol_id FK
        enum interval
        timestamp open_time
        decimal open
        decimal high
        decimal low
        decimal close
        decimal volume
        decimal quote_volume
        int trades_count
    }

    strategies {
        uuid id PK
        string name UK
        string strategy_type
        jsonb parameters
        boolean is_enabled
    }

    signals {
        uuid id PK
        uuid symbol_id FK
        uuid strategy_id FK
        enum action
        int confidence
        enum risk_level
        decimal suggested_size
        decimal stop_loss
        decimal take_profit
        jsonb supporting_indicators
        jsonb contradicting_indicators
        text mathematical_reason
        timestamp created_at
    }

    orders {
        uuid id PK
        uuid symbol_id FK
        uuid signal_id FK
        enum side
        enum order_type
        enum status
        enum trading_mode
        decimal quantity
        decimal price
        decimal stop_loss
        decimal take_profit
        string exchange_order_id
        timestamp created_at
        timestamp filled_at
    }

    positions {
        uuid id PK
        uuid symbol_id FK
        uuid entry_order_id FK
        enum status
        decimal entry_price
        decimal quantity
        decimal stop_loss
        decimal take_profit
        decimal unrealized_pnl
        decimal realized_pnl
        timestamp opened_at
        timestamp closed_at
    }

    backtest_runs {
        uuid id PK
        uuid strategy_id FK
        uuid symbol_id FK
        timestamp start_date
        timestamp end_date
        decimal initial_capital
        decimal final_capital
        decimal total_return_pct
        decimal max_drawdown
        decimal sharpe_ratio
        decimal sortino_ratio
        decimal win_rate
        decimal profit_factor
        decimal buy_hold_return
        jsonb metrics
        enum status
        timestamp created_at
    }

    risk_profiles {
        uuid id PK
        string name
        decimal max_risk_per_trade_pct
        decimal max_daily_loss_pct
        int max_trades_per_day
        int max_consecutive_losses
        decimal min_confidence_buy
        decimal ensemble_sell_threshold
        decimal kelly_cap
        boolean trailing_stop_enabled
    }

    daily_risk_state {
        uuid id PK
        date trading_date UK
        decimal starting_capital
        decimal current_capital
        decimal daily_pnl
        decimal daily_loss_pct
        int trades_count
        int consecutive_losses
        boolean is_locked
        enum lock_reason
    }

    audit_logs {
        uuid id PK
        uuid user_id FK
        string action
        string entity_type
        uuid entity_id
        jsonb old_value
        jsonb new_value
        string ip_address
        timestamp created_at
    }

    bot_configs {
        uuid id PK
        enum trading_mode
        boolean emergency_stop
        jsonb active_symbols
        jsonb strategy_weights
        uuid risk_profile_id FK
        timestamp updated_at
    }

    api_errors {
        uuid id PK
        string service
        string endpoint
        int status_code
        text error_message
        jsonb request_context
        timestamp created_at
    }

    notifications {
        uuid id PK
        enum channel
        enum event_type
        string title
        text message
        jsonb payload
        timestamp created_at
    }
```

## Índices Recomendados

```sql
CREATE UNIQUE INDEX idx_candles_symbol_interval_time 
  ON candles(symbol_id, interval, open_time);
CREATE INDEX idx_signals_symbol_created ON signals(symbol_id, created_at DESC);
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);
CREATE INDEX idx_positions_status ON positions(status) WHERE status = 'OPEN';
CREATE INDEX idx_audit_logs_created ON audit_logs(created_at DESC);
CREATE INDEX idx_api_errors_created ON api_errors(created_at DESC);
```

## Particionamiento (producción)

- Tabla `candles`: particionar por `open_time` mensual
- Tabla `audit_logs`: particionar por `created_at` mensual
- Retención: velas 1m → 90 días; 1h+ → indefinido

## Enumeraciones

| Enum | Valores |
|------|---------|
| `user_role` | admin, analyst, viewer |
| `trading_mode` | testnet, paper, production |
| `signal_action` | BUY, SELL, HOLD |
| `risk_level` | low, medium, high |
| `order_side` | BUY, SELL |
| `order_status` | PENDING, OPEN, FILLED, CANCELLED, REJECTED |
| `position_status` | OPEN, CLOSED |
| `candle_interval` | 1m, 5m, 15m, 1h, 4h, 1d |
| `notification_channel` | email, telegram, whatsapp |