rckrdmrd
45e77e9a9c
DDL schemas for Trading Platform: - User management - Authentication - Payments - Education - ML predictions - Trading data Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
108 lines
4.9 KiB
SQL
108 lines
4.9 KiB
SQL
-- =====================================================
|
|
-- ML SCHEMA - LLM PREDICTIONS TABLE
|
|
-- =====================================================
|
|
-- Description: Predictions generated by the LLM Trading Agent
|
|
-- Schema: ml
|
|
-- Author: Database Agent
|
|
-- Date: 2026-01-04
|
|
-- Module: OQI-010-llm-trading-integration
|
|
-- =====================================================
|
|
|
|
CREATE TABLE ml.llm_predictions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
-- Symbol and timeframe
|
|
symbol VARCHAR(20) NOT NULL,
|
|
timeframe VARCHAR(10) NOT NULL,
|
|
|
|
-- AMD Phase prediction
|
|
amd_phase VARCHAR(50),
|
|
amd_confidence DECIMAL(5,4) CHECK (amd_confidence >= 0 AND amd_confidence <= 1),
|
|
|
|
-- Signal prediction
|
|
signal_direction VARCHAR(10),
|
|
signal_confidence DECIMAL(5,4) CHECK (signal_confidence >= 0 AND signal_confidence <= 1),
|
|
|
|
-- Price levels
|
|
entry_price DECIMAL(20,8),
|
|
stop_loss DECIMAL(20,8),
|
|
take_profit DECIMAL(20,8),
|
|
|
|
-- ICT/SMC context
|
|
killzone VARCHAR(50),
|
|
ote_zone VARCHAR(20),
|
|
|
|
-- Confluence score (0.0 to 1.0)
|
|
confluence_score DECIMAL(5,4) CHECK (confluence_score >= 0 AND confluence_score <= 1),
|
|
|
|
-- LLM generated explanation
|
|
explanation TEXT,
|
|
|
|
-- Model metadata
|
|
model_version VARCHAR(50) NOT NULL DEFAULT 'v1.0',
|
|
|
|
-- Timestamps
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
-- Constraints
|
|
CONSTRAINT chk_llm_predictions_signal_direction CHECK (
|
|
signal_direction IS NULL OR signal_direction IN ('LONG', 'SHORT', 'HOLD')
|
|
),
|
|
CONSTRAINT chk_llm_predictions_amd_phase CHECK (
|
|
amd_phase IS NULL OR amd_phase IN (
|
|
'accumulation', 'manipulation', 'distribution',
|
|
're_accumulation', 're_distribution', 'markup', 'markdown'
|
|
)
|
|
),
|
|
CONSTRAINT chk_llm_predictions_killzone CHECK (
|
|
killzone IS NULL OR killzone IN (
|
|
'asian_session', 'london_open', 'london_session',
|
|
'new_york_open', 'new_york_session', 'london_close', 'none'
|
|
)
|
|
),
|
|
CONSTRAINT chk_llm_predictions_ote_zone CHECK (
|
|
ote_zone IS NULL OR ote_zone IN ('premium', 'discount', 'equilibrium')
|
|
),
|
|
CONSTRAINT chk_llm_predictions_price_levels CHECK (
|
|
(entry_price IS NULL AND stop_loss IS NULL AND take_profit IS NULL) OR
|
|
(entry_price IS NOT NULL AND stop_loss IS NOT NULL)
|
|
)
|
|
);
|
|
|
|
-- Indices
|
|
CREATE INDEX idx_llm_predictions_symbol ON ml.llm_predictions(symbol);
|
|
CREATE INDEX idx_llm_predictions_symbol_time ON ml.llm_predictions(symbol, created_at DESC);
|
|
CREATE INDEX idx_llm_predictions_timeframe ON ml.llm_predictions(timeframe);
|
|
CREATE INDEX idx_llm_predictions_created ON ml.llm_predictions(created_at DESC);
|
|
CREATE INDEX idx_llm_predictions_amd_phase ON ml.llm_predictions(amd_phase)
|
|
WHERE amd_phase IS NOT NULL;
|
|
CREATE INDEX idx_llm_predictions_signal ON ml.llm_predictions(signal_direction)
|
|
WHERE signal_direction IS NOT NULL;
|
|
CREATE INDEX idx_llm_predictions_confluence ON ml.llm_predictions(confluence_score DESC)
|
|
WHERE confluence_score IS NOT NULL;
|
|
CREATE INDEX idx_llm_predictions_model_version ON ml.llm_predictions(model_version);
|
|
|
|
-- High confluence active signals
|
|
CREATE INDEX idx_llm_predictions_active_signals ON ml.llm_predictions(symbol, confluence_score DESC)
|
|
WHERE confluence_score >= 0.6 AND signal_direction IN ('LONG', 'SHORT');
|
|
|
|
-- Comments
|
|
COMMENT ON TABLE ml.llm_predictions IS 'Predictions generated by the LLM Trading Agent with AMD phase detection and ICT/SMC context';
|
|
|
|
COMMENT ON COLUMN ml.llm_predictions.id IS 'Unique identifier for the prediction';
|
|
COMMENT ON COLUMN ml.llm_predictions.symbol IS 'Trading symbol (e.g., XAUUSD, BTCUSDT)';
|
|
COMMENT ON COLUMN ml.llm_predictions.timeframe IS 'Timeframe for analysis (e.g., 5m, 15m, 1h, 4h)';
|
|
COMMENT ON COLUMN ml.llm_predictions.amd_phase IS 'AMD cycle phase: accumulation, manipulation, distribution, re_accumulation, etc.';
|
|
COMMENT ON COLUMN ml.llm_predictions.amd_confidence IS 'Confidence score for AMD phase detection (0.0 to 1.0)';
|
|
COMMENT ON COLUMN ml.llm_predictions.signal_direction IS 'Trade direction: LONG, SHORT, or HOLD';
|
|
COMMENT ON COLUMN ml.llm_predictions.signal_confidence IS 'Confidence score for signal direction (0.0 to 1.0)';
|
|
COMMENT ON COLUMN ml.llm_predictions.entry_price IS 'Recommended entry price level';
|
|
COMMENT ON COLUMN ml.llm_predictions.stop_loss IS 'Recommended stop loss price level';
|
|
COMMENT ON COLUMN ml.llm_predictions.take_profit IS 'Recommended take profit price level';
|
|
COMMENT ON COLUMN ml.llm_predictions.killzone IS 'ICT Killzone active during prediction';
|
|
COMMENT ON COLUMN ml.llm_predictions.ote_zone IS 'Optimal Trade Entry zone: premium, discount, or equilibrium';
|
|
COMMENT ON COLUMN ml.llm_predictions.confluence_score IS 'Overall confluence score from all ML models (0.0 to 1.0)';
|
|
COMMENT ON COLUMN ml.llm_predictions.explanation IS 'Natural language explanation generated by the LLM';
|
|
COMMENT ON COLUMN ml.llm_predictions.model_version IS 'Version of the LLM model used for prediction';
|
|
COMMENT ON COLUMN ml.llm_predictions.created_at IS 'Timestamp when prediction was generated';
|