c651fe5a30
ST-1.1: financial.refunds - Already exists with approval flow ST-1.2: education.instructors - Created with GIN indexes ST-1.3: trading.price_alerts - FK exists, idempotent migration added ST-1.4: ml.prediction_overlays - New table + overlay columns New files: - ddl/schemas/education/tables/17-instructors.sql - ddl/schemas/ml/tables/12-prediction_overlays.sql - migrations/2026-02-03_add_predictions_overlay.sql - migrations/2026-02-03_add_price_alerts_symbol_fk.sql Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
81 lines
2.8 KiB
SQL
81 lines
2.8 KiB
SQL
-- =====================================================
|
|
-- MIGRATION: Add overlay columns to ml.predictions
|
|
-- =====================================================
|
|
-- Gap: GAP-DDL-006
|
|
-- Task: TASK-2026-02-03-ANALISIS-DDL-MODELADO / ST-1.4
|
|
-- Created: 2026-02-03
|
|
-- Updated: 2026-02-03
|
|
-- Description: Complete overlay structure for ML predictions visualization on trading charts
|
|
-- =====================================================
|
|
|
|
-- ===========================================
|
|
-- 1. ADD OVERLAY COLUMNS
|
|
-- ===========================================
|
|
|
|
-- Add overlay visualization data
|
|
ALTER TABLE ml.predictions
|
|
ADD COLUMN IF NOT EXISTS overlay_data JSONB DEFAULT '{}';
|
|
|
|
-- Add chart configuration
|
|
ALTER TABLE ml.predictions
|
|
ADD COLUMN IF NOT EXISTS chart_config JSONB DEFAULT '{
|
|
"show_on_chart": true,
|
|
"color": "#4CAF50",
|
|
"line_style": "dashed",
|
|
"opacity": 0.8
|
|
}';
|
|
|
|
-- Add display priority for ordering multiple predictions
|
|
ALTER TABLE ml.predictions
|
|
ADD COLUMN IF NOT EXISTS display_priority INTEGER DEFAULT 0;
|
|
|
|
-- ===========================================
|
|
-- 2. CREATE INDEXES FOR OVERLAY QUERIES
|
|
-- ===========================================
|
|
|
|
-- Index for overlay queries - predictions visible on chart ordered by priority
|
|
CREATE INDEX IF NOT EXISTS idx_predictions_overlay
|
|
ON ml.predictions(symbol, timeframe, display_priority DESC)
|
|
WHERE (chart_config->>'show_on_chart')::boolean = true;
|
|
|
|
-- Index for fetching overlay data efficiently
|
|
CREATE INDEX IF NOT EXISTS idx_predictions_overlay_data
|
|
ON ml.predictions USING GIN (overlay_data)
|
|
WHERE overlay_data IS NOT NULL AND overlay_data != '{}';
|
|
|
|
-- ===========================================
|
|
-- 3. COLUMN COMMENTS
|
|
-- ===========================================
|
|
|
|
COMMENT ON COLUMN ml.predictions.overlay_data IS
|
|
'JSON data for chart overlay visualization. Example:
|
|
{
|
|
"price_levels": [1850.00, 1865.50, 1880.25],
|
|
"zones": [
|
|
{"type": "support", "low": 1845.00, "high": 1850.00, "color": "#4CAF50"},
|
|
{"type": "resistance", "low": 1875.00, "high": 1880.00, "color": "#F44336"}
|
|
],
|
|
"trend_lines": [
|
|
{"start": {"time": "2026-02-01T10:00:00Z", "price": 1840.00}, "end": {"time": "2026-02-03T10:00:00Z", "price": 1870.00}}
|
|
],
|
|
"annotations": [
|
|
{"time": "2026-02-02T14:00:00Z", "text": "Breakout signal", "position": "above"}
|
|
]
|
|
}';
|
|
|
|
COMMENT ON COLUMN ml.predictions.chart_config IS
|
|
'Configuration for how to display this prediction on charts. Example:
|
|
{
|
|
"show_on_chart": true,
|
|
"color": "#4CAF50",
|
|
"line_style": "dashed",
|
|
"opacity": 0.8,
|
|
"label_visible": true,
|
|
"z_index": 10
|
|
}';
|
|
|
|
COMMENT ON COLUMN ml.predictions.display_priority IS
|
|
'Priority for ordering when multiple predictions exist (higher = more prominent).
|
|
Default: 0, Range: -100 to 100.
|
|
Use negative values for background overlays, positive for foreground.';
|