thrilltrack-explorer/supabase/migrations/20251111020556_e7431e73-dd91-428d-b4af-1362acdc311a.sql

-- ML-based Anomaly Detection System

-- Table: Time-series metrics for anomaly detection
CREATE TABLE metric_time_series (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  metric_name TEXT NOT NULL,
  metric_category TEXT NOT NULL CHECK (metric_category IN ('system', 'database', 'rate_limit', 'moderation', 'api')),
  metric_value NUMERIC NOT NULL,
  timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  metadata JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Table: Detected anomalies
CREATE TABLE anomaly_detections (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  metric_name TEXT NOT NULL,
  metric_category TEXT NOT NULL,
  anomaly_type TEXT NOT NULL CHECK (anomaly_type IN ('spike', 'drop', 'trend_change', 'outlier', 'pattern_break')),
  severity TEXT NOT NULL CHECK (severity IN ('critical', 'high', 'medium', 'low')),
  baseline_value NUMERIC NOT NULL,
  anomaly_value NUMERIC NOT NULL,
  deviation_score NUMERIC NOT NULL,
  confidence_score NUMERIC NOT NULL CHECK (confidence_score >= 0 AND confidence_score <= 1),
  detection_algorithm TEXT NOT NULL,
  time_window_start TIMESTAMPTZ NOT NULL,
  time_window_end TIMESTAMPTZ NOT NULL,
  detected_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  alert_created BOOLEAN NOT NULL DEFAULT false,
  alert_id UUID,
  metadata JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Table: Anomaly detection configuration
CREATE TABLE anomaly_detection_config (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  metric_name TEXT NOT NULL UNIQUE,
  metric_category TEXT NOT NULL,
  enabled BOOLEAN NOT NULL DEFAULT true,
  sensitivity NUMERIC NOT NULL DEFAULT 3.0 CHECK (sensitivity > 0),
  lookback_window_minutes INTEGER NOT NULL DEFAULT 60,
  detection_algorithms TEXT[] NOT NULL DEFAULT ARRAY['z_score', 'moving_average', 'rate_of_change'],
  min_data_points INTEGER NOT NULL DEFAULT 10,
  alert_threshold_score NUMERIC NOT NULL DEFAULT 2.5,
  auto_create_alert BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- View: Recent anomalies with alert status
CREATE OR REPLACE VIEW recent_anomalies_view
WITH (security_invoker=on)
AS
SELECT 
  ad.id,
  ad.metric_name,
  ad.metric_category,
  ad.anomaly_type,
  ad.severity,
  ad.baseline_value,
  ad.anomaly_value,
  ad.deviation_score,
  ad.confidence_score,
  ad.detection_algorithm,
  ad.time_window_start,
  ad.time_window_end,
  ad.detected_at,
  ad.alert_created,
  ad.alert_id,
  sa.message as alert_message,
  sa.resolved_at as alert_resolved_at
FROM anomaly_detections ad
LEFT JOIN system_alerts sa ON sa.id = ad.alert_id::uuid
WHERE ad.detected_at > NOW() - INTERVAL '24 hours'
ORDER BY ad.detected_at DESC;

-- Insert default anomaly detection configurations
INSERT INTO anomaly_detection_config (metric_name, metric_category, sensitivity, lookback_window_minutes, detection_algorithms, alert_threshold_score) VALUES
('error_rate', 'system', 2.5, 60, ARRAY['z_score', 'moving_average'], 2.0),
('response_time', 'api', 3.0, 30, ARRAY['z_score', 'rate_of_change'], 2.5),
('database_connections', 'database', 2.0, 120, ARRAY['z_score', 'moving_average'], 3.0),
('rate_limit_violations', 'rate_limit', 2.5, 60, ARRAY['z_score', 'spike_detection'], 2.0),
('moderation_queue_size', 'moderation', 3.0, 120, ARRAY['z_score', 'trend_change'], 2.5),
('cpu_usage', 'system', 2.5, 30, ARRAY['z_score', 'moving_average'], 2.0),
('memory_usage', 'system', 2.5, 30, ARRAY['z_score', 'moving_average'], 2.0),
('request_rate', 'api', 3.0, 60, ARRAY['z_score', 'rate_of_change'], 2.5);

-- Create indexes
CREATE INDEX idx_metric_time_series_name_timestamp ON metric_time_series(metric_name, timestamp DESC);
CREATE INDEX idx_metric_time_series_category_timestamp ON metric_time_series(metric_category, timestamp DESC);
CREATE INDEX idx_anomaly_detections_detected_at ON anomaly_detections(detected_at DESC);
CREATE INDEX idx_anomaly_detections_alert_created ON anomaly_detections(alert_created) WHERE alert_created = false;
CREATE INDEX idx_anomaly_detections_metric ON anomaly_detections(metric_name, detected_at DESC);

-- Grant permissions
GRANT SELECT, INSERT ON metric_time_series TO authenticated;
GRANT SELECT ON anomaly_detections TO authenticated;
GRANT SELECT ON anomaly_detection_config TO authenticated;
GRANT SELECT ON recent_anomalies_view TO authenticated;

-- RLS Policies
ALTER TABLE metric_time_series ENABLE ROW LEVEL SECURITY;
ALTER TABLE anomaly_detections ENABLE ROW LEVEL SECURITY;
ALTER TABLE anomaly_detection_config ENABLE ROW LEVEL SECURITY;

-- System can insert metrics
CREATE POLICY system_insert_metrics ON metric_time_series
  FOR INSERT WITH CHECK (true);

-- Moderators can view all metrics
CREATE POLICY moderators_view_metrics ON metric_time_series
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM user_roles 
      WHERE user_id = auth.uid() 
      AND role IN ('moderator', 'admin', 'superuser')
    )
  );

-- Moderators can view anomalies
CREATE POLICY moderators_view_anomalies ON anomaly_detections
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM user_roles 
      WHERE user_id = auth.uid() 
      AND role IN ('moderator', 'admin', 'superuser')
    )
  );

-- System can insert anomalies
CREATE POLICY system_insert_anomalies ON anomaly_detections
  FOR INSERT WITH CHECK (true);

-- Admins can manage anomaly config
CREATE POLICY admins_manage_config ON anomaly_detection_config
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM user_roles 
      WHERE user_id = auth.uid() 
      AND role IN ('admin', 'superuser')
    )
  );