Model Registry

-- Register a new model
INSERT INTO ml_models (
  model_name,
  model_type,
  version,
  framework,
  algorithm,
  hyperparameters,
  training_dataset,
  feature_groups,
  created_by
) VALUES (
  'fraud_detection',
  'classification',
  '1.0.0',
  'scikit-learn',
  'RandomForestClassifier',
  '{"n_estimators": 100, "max_depth": 10, "random_state": 42}',
  'transactions_2024_q1',
  ARRAY['user_features', 'transaction_features'],
  'data_scientist@example.com'
);

-- Store model artifacts
INSERT INTO model_artifacts (
  model_id,
  artifact_type,
  artifact_path,
  file_size_bytes,
  checksum
) VALUES (
  (SELECT id FROM ml_models WHERE model_name = 'fraud_detection' AND version = '1.0.0'),
  'model_pickle',
  's3://ml-models/fraud_detection/v1.0.0/model.pkl',
  15728640,
  'sha256:abc123...'
);

-- Register model metrics
INSERT INTO model_metrics (
  model_id,
  metric_name,
  metric_value,
  dataset_type
) VALUES 
  (1, 'accuracy', 0.9542, 'validation'),
  (1, 'precision', 0.9123, 'validation'),
  (1, 'recall', 0.8765, 'validation'),
  (1, 'f1_score', 0.8938, 'validation'),
  (1, 'auc_roc', 0.9678, 'validation');

-- Semantic versioning for models
-- Format: MAJOR.MINOR.PATCH

-- Create new version
INSERT INTO ml_models (
  model_name,
  version,
  parent_version,
  changes,
  ...
) VALUES (
  'fraud_detection',
  '1.1.0',  -- Minor version: new features
  '1.0.0',
  'Added device fingerprinting features',
  ...
);

-- View version history
SELECT 
  version,
  created_at,
  created_by,
  changes,
  status
FROM ml_models
WHERE model_name = 'fraud_detection'
ORDER BY created_at DESC;

-- Compare model versions
SELECT 
  m1.version as version_1,
  m2.version as version_2,
  m1.hyperparameters as params_1,
  m2.hyperparameters as params_2,
  (
    SELECT jsonb_object_agg(metric_name, metric_value)
    FROM model_metrics
    WHERE model_id = m1.id AND dataset_type = 'validation'
  ) as metrics_1,
  (
    SELECT jsonb_object_agg(metric_name, metric_value)
    FROM model_metrics
    WHERE model_id = m2.id AND dataset_type = 'validation'
  ) as metrics_2
FROM ml_models m1
CROSS JOIN ml_models m2
WHERE m1.model_name = 'fraud_detection'
  AND m2.model_name = 'fraud_detection'
  AND m1.version = '1.0.0'
  AND m2.version = '1.1.0';

-- Tag versions
UPDATE ml_models
SET tags = tags || '{"stage": "production"}'
WHERE model_name = 'fraud_detection' 
  AND version = '1.1.0';

-- Promote to production
UPDATE ml_models
SET status = 'production',
    production_deployed_at = NOW()
WHERE model_name = 'fraud_detection'
  AND version = '1.1.0';

-- Track model deployments
INSERT INTO model_deployments (
  model_id,
  environment,
  endpoint_url,
  deployment_config,
  deployed_by
) VALUES (
  (SELECT id FROM ml_models WHERE model_name = 'fraud_detection' AND version = '1.1.0'),
  'production',
  'https://api.example.com/predict/fraud',
  '{
    "instance_type": "ml.m5.xlarge",
    "min_instances": 2,
    "max_instances": 10,
    "traffic_percentage": 100
  }',
  'mlops@example.com'
);

-- View active deployments
SELECT 
  m.model_name,
  m.version,
  d.environment,
  d.endpoint_url,
  d.deployed_at,
  d.deployment_config->>'traffic_percentage' as traffic_pct
FROM model_deployments d
JOIN ml_models m ON d.model_id = m.id
WHERE d.status = 'active'
ORDER BY d.deployed_at DESC;

-- Canary deployment (gradual rollout)
-- Deploy new version to 10% of traffic
INSERT INTO model_deployments (
  model_id,
  environment,
  deployment_config
) VALUES (
  (SELECT id FROM ml_models WHERE model_name = 'fraud_detection' AND version = '1.2.0'),
  'production',
  '{"traffic_percentage": 10, "canary": true}'
);

-- Update existing to 90%
UPDATE model_deployments
SET deployment_config = deployment_config || '{"traffic_percentage": 90}'
WHERE model_id = (SELECT id FROM ml_models WHERE model_name = 'fraud_detection' AND version = '1.1.0')
  AND environment = 'production'
  AND status = 'active';

-- Rollback deployment
UPDATE model_deployments
SET status = 'rolled_back',
    rolled_back_at = NOW(),
    rollback_reason = 'Elevated error rate detected'
WHERE model_id = (SELECT id FROM ml_models WHERE version = '1.2.0')
  AND environment = 'production';

-- Create A/B test
INSERT INTO ab_tests (
  test_name,
  model_a_id,
  model_b_id,
  traffic_split,
  start_date,
  end_date,
  success_metric
) VALUES (
  'fraud_v1.1_vs_v1.2',
  (SELECT id FROM ml_models WHERE version = '1.1.0'),
  (SELECT id FROM ml_models WHERE version = '1.2.0'),
  '{"A": 50, "B": 50}',
  NOW(),
  NOW() + INTERVAL '7 days',
  'f1_score'
);

-- Log predictions for A/B test
INSERT INTO ab_test_predictions (
  test_id,
  variant,
  user_id,
  prediction,
  actual,
  latency_ms
) VALUES (
  1, 'A', 12345, 0.85, 1, 45
);

-- Analyze A/B test results
SELECT 
  variant,
  COUNT(*) as prediction_count,
  AVG(CASE WHEN prediction > 0.5 AND actual = 1 THEN 1 ELSE 0 END) as true_positive_rate,
  AVG(CASE WHEN prediction <= 0.5 AND actual = 0 THEN 1 ELSE 0 END) as true_negative_rate,
  AVG(latency_ms) as avg_latency
FROM ab_test_predictions
WHERE test_id = 1
GROUP BY variant;

-- Statistical significance test
WITH test_stats AS (
  SELECT 
    variant,
    COUNT(*) as n,
    AVG(CASE WHEN prediction > 0.5 AND actual = 1 THEN 1 ELSE 0 END) as success_rate
  FROM ab_test_predictions
  WHERE test_id = 1
  GROUP BY variant
),
pooled AS (
  SELECT 
    SUM(n * success_rate) / SUM(n) as p_pooled,
    SUM(n) as n_total
  FROM test_stats
)
SELECT 
  ts.variant,
  ts.success_rate,
  (ts.success_rate - p.p_pooled) / 
    SQRT(p.p_pooled * (1 - p.p_pooled) * (1.0/ts.n + 1.0/(p.n_total - ts.n))) 
    as z_score
FROM test_stats ts
CROSS JOIN pooled p;

-- End A/B test and promote winner
UPDATE ab_tests
SET status = 'completed',
    winner_variant = 'B',
    completed_at = NOW()
WHERE test_name = 'fraud_v1.1_vs_v1.2';

-- Log predictions for monitoring
INSERT INTO model_predictions (
  model_id,
  prediction_id,
  input_features,
  prediction,
  prediction_proba,
  latency_ms,
  timestamp
) VALUES (
  1,
  gen_random_uuid(),
  '{"age": 35, "transaction_amount": 250.0, "hour_of_day": 14}',
  0,  -- Not fraud
  0.15,  -- 15% probability
  42,
  NOW()
);

-- Monitor prediction distribution
SELECT 
  DATE(timestamp) as date,
  AVG(prediction) as avg_prediction,
  STDDEV(prediction) as stddev_prediction,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY prediction_proba) as median_proba
FROM model_predictions
WHERE model_id = 1
  AND timestamp > NOW() - INTERVAL '7 days'
GROUP BY DATE(timestamp)
ORDER BY date;

-- Detect prediction drift
WITH baseline AS (
  SELECT 
    AVG(prediction_proba) as mean_proba,
    STDDEV(prediction_proba) as stddev_proba
  FROM model_predictions
  WHERE model_id = 1
    AND timestamp BETWEEN '2024-01-01' AND '2024-01-31'
),
current AS (
  SELECT 
    AVG(prediction_proba) as mean_proba,
    STDDEV(prediction_proba) as stddev_proba
  FROM model_predictions
  WHERE model_id = 1
    AND timestamp > NOW() - INTERVAL '7 days'
)
SELECT 
  ABS(c.mean_proba - b.mean_proba) / b.stddev_proba as drift_z_score,
  CASE 
    WHEN ABS(c.mean_proba - b.mean_proba) / b.stddev_proba > 3 THEN 'ALERT'
    WHEN ABS(c.mean_proba - b.mean_proba) / b.stddev_proba > 2 THEN 'WARNING'
    ELSE 'OK'
  END as drift_status
FROM baseline b
CROSS JOIN current c;

-- Performance over time
SELECT 
  DATE(p.timestamp) as date,
  COUNT(*) as prediction_count,
  AVG(CASE WHEN p.prediction = a.actual THEN 1 ELSE 0 END) as accuracy,
  AVG(p.latency_ms) as avg_latency
FROM model_predictions p
LEFT JOIN actuals a ON p.prediction_id = a.prediction_id
WHERE p.model_id = 1
  AND p.timestamp > NOW() - INTERVAL '30 days'
GROUP BY DATE(p.timestamp)
ORDER BY date;