178 lines
7.4 KiB
SQL
178 lines
7.4 KiB
SQL
-- Migration: 003_phase1_additions.sql
|
|
-- Phase 1 additions: Role profiles, activations, predictions, joint outcomes, discrepancy scores
|
|
-- Created: 2026-03-28
|
|
|
|
-- ============================================================
|
|
-- Table 1: player_role_profiles
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS player_role_profiles (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
player_id TEXT NOT NULL,
|
|
player_name TEXT NOT NULL,
|
|
sport TEXT NOT NULL DEFAULT 'NBA',
|
|
team TEXT,
|
|
role_profile JSONB NOT NULL DEFAULT '{}',
|
|
conditional_roles JSONB DEFAULT '{}',
|
|
dominant_role TEXT,
|
|
role_variance_score NUMERIC(4,3) DEFAULT 0,
|
|
effective_date DATE DEFAULT CURRENT_DATE,
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- ============================================================
|
|
-- Table 2: lineup_role_profiles
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS lineup_role_profiles (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
lineup_id TEXT NOT NULL,
|
|
player_id TEXT NOT NULL,
|
|
sport TEXT NOT NULL DEFAULT 'NBA',
|
|
team TEXT,
|
|
role_in_this_lineup TEXT,
|
|
historical_production JSONB DEFAULT '{}',
|
|
instances_tracked INTEGER DEFAULT 0,
|
|
last_game_date DATE,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- ============================================================
|
|
-- Table 3: player_role_activations
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS player_role_activations (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
game_id TEXT,
|
|
player_id TEXT NOT NULL,
|
|
activated_role TEXT NOT NULL,
|
|
activation_trigger TEXT,
|
|
activation_timestamp TIMESTAMPTZ DEFAULT NOW(),
|
|
pre_activation_line NUMERIC(6,2),
|
|
post_activation_line NUMERIC(6,2),
|
|
line_movement_after NUMERIC(6,2),
|
|
actual_result NUMERIC(6,2),
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- ============================================================
|
|
-- Table 4: model_predictions_extended
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS model_predictions_extended (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
prediction_id UUID REFERENCES picks(id),
|
|
role_stability_score NUMERIC(4,3),
|
|
role_variance_score NUMERIC(4,3),
|
|
active_role_tonight TEXT,
|
|
elevation_detected BOOLEAN DEFAULT FALSE,
|
|
evolution_detected BOOLEAN DEFAULT FALSE,
|
|
evolution_confidence NUMERIC(4,3),
|
|
similarity_instances_found INTEGER DEFAULT 0,
|
|
model_probability NUMERIC(5,4),
|
|
confidence_interval_low NUMERIC(6,2),
|
|
confidence_interval_high NUMERIC(6,2),
|
|
confidence_interval_asymmetric BOOLEAN DEFAULT FALSE,
|
|
optimal_alt_line NUMERIC(6,2),
|
|
optimal_alt_line_edge NUMERIC(5,4),
|
|
discrepancy_window_flag BOOLEAN DEFAULT FALSE,
|
|
clv_at_prediction NUMERIC(6,2),
|
|
clv_at_24hr NUMERIC(6,2),
|
|
clv_at_tip NUMERIC(6,2),
|
|
model_version TEXT DEFAULT '1.0',
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- ============================================================
|
|
-- Table 5: prediction_registry
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS prediction_registry (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
prediction_type TEXT NOT NULL,
|
|
player_id TEXT NOT NULL,
|
|
sport TEXT NOT NULL,
|
|
stat_type TEXT NOT NULL,
|
|
prediction_detail JSONB NOT NULL,
|
|
registered_at TIMESTAMPTZ DEFAULT NOW(),
|
|
outcome TEXT DEFAULT 'pending' CHECK (outcome IN ('pending', 'verified', 'false')),
|
|
verified_at TIMESTAMPTZ,
|
|
public_url TEXT
|
|
);
|
|
|
|
-- ============================================================
|
|
-- Table 6: joint_outcomes
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS joint_outcomes (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
game_id TEXT,
|
|
player_a_id TEXT NOT NULL,
|
|
player_a_stat TEXT NOT NULL,
|
|
player_a_result NUMERIC(6,2),
|
|
player_b_id TEXT NOT NULL,
|
|
player_b_stat TEXT NOT NULL,
|
|
player_b_result NUMERIC(6,2),
|
|
phi_coefficient NUMERIC(5,4),
|
|
sample_size INTEGER DEFAULT 0,
|
|
game_state_at_outcome TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- ============================================================
|
|
-- Table 7: discrepancy_reliability_scores
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS discrepancy_reliability_scores (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
prop_type TEXT NOT NULL,
|
|
sport TEXT NOT NULL,
|
|
reliability_score NUMERIC(4,3),
|
|
avg_resolution_minutes NUMERIC(6,1),
|
|
sample_size INTEGER DEFAULT 0,
|
|
last_updated TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- ============================================================
|
|
-- Indexes
|
|
-- ============================================================
|
|
CREATE INDEX IF NOT EXISTS idx_role_profiles_player ON player_role_profiles(player_id);
|
|
CREATE INDEX IF NOT EXISTS idx_role_profiles_sport_team ON player_role_profiles(sport, team);
|
|
CREATE INDEX IF NOT EXISTS idx_lineup_roles_player ON lineup_role_profiles(player_id);
|
|
CREATE INDEX IF NOT EXISTS idx_lineup_roles_lineup ON lineup_role_profiles(lineup_id);
|
|
CREATE INDEX IF NOT EXISTS idx_role_activations_player ON player_role_activations(player_id);
|
|
CREATE INDEX IF NOT EXISTS idx_role_activations_game ON player_role_activations(game_id);
|
|
CREATE INDEX IF NOT EXISTS idx_predictions_ext_prediction ON model_predictions_extended(prediction_id);
|
|
CREATE INDEX IF NOT EXISTS idx_prediction_registry_player ON prediction_registry(player_id, sport);
|
|
CREATE INDEX IF NOT EXISTS idx_prediction_registry_outcome ON prediction_registry(outcome);
|
|
CREATE INDEX IF NOT EXISTS idx_joint_outcomes_players ON joint_outcomes(player_a_id, player_b_id);
|
|
CREATE INDEX IF NOT EXISTS idx_joint_outcomes_game ON joint_outcomes(game_id);
|
|
CREATE INDEX IF NOT EXISTS idx_discrepancy_scores_prop ON discrepancy_reliability_scores(prop_type, sport);
|
|
|
|
-- ============================================================
|
|
-- Row Level Security
|
|
-- ============================================================
|
|
ALTER TABLE player_role_profiles ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE lineup_role_profiles ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE player_role_activations ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE model_predictions_extended ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE prediction_registry ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE joint_outcomes ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE discrepancy_reliability_scores ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Public read on prediction_registry
|
|
CREATE POLICY "prediction_registry_public_read" ON prediction_registry
|
|
FOR SELECT USING (true);
|
|
|
|
-- Service role write on prediction_registry
|
|
CREATE POLICY "prediction_registry_service_write" ON prediction_registry
|
|
FOR INSERT WITH CHECK (auth.role() = 'service_role');
|
|
|
|
-- Service role full access on all other tables
|
|
CREATE POLICY "role_profiles_service_access" ON player_role_profiles
|
|
FOR ALL USING (auth.role() = 'service_role');
|
|
CREATE POLICY "lineup_roles_service_access" ON lineup_role_profiles
|
|
FOR ALL USING (auth.role() = 'service_role');
|
|
CREATE POLICY "role_activations_service_access" ON player_role_activations
|
|
FOR ALL USING (auth.role() = 'service_role');
|
|
CREATE POLICY "predictions_ext_service_access" ON model_predictions_extended
|
|
FOR ALL USING (auth.role() = 'service_role');
|
|
CREATE POLICY "joint_outcomes_service_access" ON joint_outcomes
|
|
FOR ALL USING (auth.role() = 'service_role');
|
|
CREATE POLICY "discrepancy_scores_service_access" ON discrepancy_reliability_scores
|
|
FOR ALL USING (auth.role() = 'service_role');
|