trading-platform-database-v2/ddl/schemas/ml/tables/03-predictions.sql

-- =====================================================
-- ML SCHEMA - PREDICTIONS TABLE
-- =====================================================
-- Description: ML model predictions and signals
-- Schema: ml
-- Author: Database Agent
-- Date: 2025-12-06
-- =====================================================

CREATE TABLE ml.predictions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Modelo y versión
  model_id UUID NOT NULL REFERENCES ml.models(id) ON DELETE CASCADE,
  model_version_id UUID NOT NULL REFERENCES ml.model_versions(id) ON DELETE CASCADE,

  -- Símbolo y timeframe
  symbol VARCHAR(20) NOT NULL,
  timeframe VARCHAR(10) NOT NULL,

  -- Tipo de predicción
  prediction_type ml.prediction_type NOT NULL,

  -- Resultado de predicción
  prediction_result ml.prediction_result,
  prediction_value DECIMAL(20,8), -- Para predicciones numéricas

  -- Confianza
  confidence_score DECIMAL(5,4) NOT NULL CHECK (confidence_score >= 0 AND confidence_score <= 1),

  -- Input features utilizados
  input_features JSONB NOT NULL,

  -- Output completo del modelo
  model_output JSONB, -- Raw output del modelo

  -- Contexto de mercado al momento de predicción
  market_price DECIMAL(20,8),
  market_timestamp TIMESTAMPTZ NOT NULL,

  -- Horizonte temporal
  prediction_horizon VARCHAR(20), -- 1h, 4h, 1d, 1w
  valid_until TIMESTAMPTZ,

  -- Metadata
  prediction_metadata JSONB,

  -- Procesamiento
  inference_time_ms INTEGER, -- Tiempo de inferencia en milisegundos

  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Índices
CREATE INDEX idx_predictions_model ON ml.predictions(model_id);
CREATE INDEX idx_predictions_version ON ml.predictions(model_version_id);
CREATE INDEX idx_predictions_symbol ON ml.predictions(symbol);
CREATE INDEX idx_predictions_symbol_time ON ml.predictions(symbol, market_timestamp DESC);
CREATE INDEX idx_predictions_type ON ml.predictions(prediction_type);
CREATE INDEX idx_predictions_created ON ml.predictions(created_at DESC);
-- Index for predictions with validity period (without time-based predicate for immutability)
CREATE INDEX idx_predictions_valid ON ml.predictions(valid_until)
  WHERE valid_until IS NOT NULL;

-- Particionamiento por fecha (opcional, para alto volumen)
-- CREATE INDEX idx_predictions_timestamp ON ml.predictions(market_timestamp DESC);

-- Comentarios
COMMENT ON TABLE ml.predictions IS 'ML model predictions and trading signals';
COMMENT ON COLUMN ml.predictions.prediction_type IS 'Type of prediction being made';
COMMENT ON COLUMN ml.predictions.prediction_result IS 'Categorical result (buy/sell/hold/up/down/neutral)';
COMMENT ON COLUMN ml.predictions.prediction_value IS 'Numeric prediction value (e.g., target price, probability)';
COMMENT ON COLUMN ml.predictions.confidence_score IS 'Model confidence in prediction (0.0 to 1.0)';
COMMENT ON COLUMN ml.predictions.input_features IS 'Feature values used for this prediction';
COMMENT ON COLUMN ml.predictions.prediction_horizon IS 'Time horizon for prediction validity';
COMMENT ON COLUMN ml.predictions.inference_time_ms IS 'Model inference latency in milliseconds';

-- Ejemplo de input_features JSONB:
COMMENT ON COLUMN ml.predictions.input_features IS
'Example: {
  "rsi_14": 65.42,
  "macd_signal": 0.0234,
  "volume_sma_20": 1234567.89,
  "price_change_1h": 0.0145,
  "sentiment_score": 0.72
}';

-- Ejemplo de model_output JSONB:
COMMENT ON COLUMN ml.predictions.model_output IS
'Example: {
  "probabilities": {"buy": 0.72, "sell": 0.15, "hold": 0.13},
  "raw_score": 0.5823,
  "feature_contributions": {...}
}';

-- =====================================================
-- PERFORMANCE COMPOSITE INDEXES (added 2026-02-03, ST-3.4)
-- =====================================================

-- Index 1: Symbol + Timeframe + Created (most common query pattern)
-- Use: WHERE symbol = ? AND timeframe = ? ORDER BY created_at DESC
CREATE INDEX idx_predictions_symbol_timeframe_created
ON ml.predictions(symbol, timeframe, created_at DESC);

-- Index 2: Symbol + Prediction Type + Created (filtered queries)
-- Use: WHERE symbol = ? AND prediction_type = ? ORDER BY created_at DESC
CREATE INDEX idx_predictions_symbol_type_created
ON ml.predictions(symbol, prediction_type, created_at DESC);

-- Index 3: Model + Symbol + Timeframe (model-specific queries)
-- Use: WHERE model_id = ? AND symbol = ? AND timeframe = ? ORDER BY created_at DESC
CREATE INDEX idx_predictions_model_symbol_timeframe
ON ml.predictions(model_id, symbol, timeframe, created_at DESC);

-- Index 4: High confidence predictions (partial index)
-- Use: WHERE symbol = ? AND timeframe = ? AND confidence_score >= 0.7
CREATE INDEX idx_predictions_high_confidence
ON ml.predictions(symbol, timeframe, created_at DESC)
WHERE confidence_score >= 0.7;

-- Index 5: Recent valid predictions (partial index for active queries)
-- Use: WHERE symbol = ? AND timeframe = ? AND valid_until IS NOT NULL
CREATE INDEX idx_predictions_recent_valid
ON ml.predictions(symbol, timeframe, prediction_type, created_at DESC)
WHERE valid_until IS NOT NULL;

-- Index 6: Overlay display (for chart rendering)
-- Use: WHERE symbol = ? AND timeframe = ? AND show_on_chart = true
CREATE INDEX idx_predictions_overlay_display
ON ml.predictions(symbol, timeframe, display_priority DESC, created_at DESC)
WHERE (chart_config->>'show_on_chart')::boolean = true;

-- Index 7: Symbol + Confidence Score (ranking queries)
-- Use: WHERE symbol = ? ORDER BY confidence_score DESC
CREATE INDEX idx_predictions_symbol_confidence
ON ml.predictions(symbol, confidence_score DESC, created_at DESC);

-- Index 8: Timeframe + Prediction Result (cross-symbol analysis)
-- Use: WHERE timeframe = ? AND prediction_result = ? ORDER BY created_at DESC
CREATE INDEX idx_predictions_timeframe_result_created
ON ml.predictions(timeframe, prediction_result, created_at DESC)
WHERE prediction_result IS NOT NULL;