83 lines
3.4 KiB
SQL
83 lines
3.4 KiB
SQL
-- Migration: 008_supplement_tables.sql
|
|
-- Supplement intelligence systems: coaching tendencies, player-out history,
|
|
-- evolution detections, unconventional validations.
|
|
-- Created: 2026-04-13
|
|
|
|
-- ============================================================
|
|
-- Coaching Tendencies
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS coaching_tendencies (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
coach_id TEXT NOT NULL,
|
|
team_id TEXT NOT NULL,
|
|
sport TEXT NOT NULL,
|
|
season TEXT NOT NULL,
|
|
tendencies JSONB NOT NULL,
|
|
games_sampled INTEGER DEFAULT 0,
|
|
last_updated DATE,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(coach_id, team_id, sport, season)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_ct_coach ON coaching_tendencies(coach_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ct_team ON coaching_tendencies(team_id);
|
|
|
|
-- ============================================================
|
|
-- Player-Out History (redistribution training data)
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS player_out_history (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
player_out_id TEXT NOT NULL,
|
|
team_id TEXT NOT NULL,
|
|
game_id TEXT NOT NULL,
|
|
game_date DATE NOT NULL,
|
|
beneficiary_stats JSONB NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_poh_player ON player_out_history(player_out_id);
|
|
CREATE INDEX IF NOT EXISTS idx_poh_team ON player_out_history(team_id);
|
|
|
|
-- ============================================================
|
|
-- Evolution Detections (timestamped accuracy ledger)
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS evolution_detections (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
player_id TEXT NOT NULL,
|
|
player_name TEXT,
|
|
sport TEXT NOT NULL,
|
|
detection_date DATE NOT NULL,
|
|
metrics JSONB NOT NULL,
|
|
market_adjusted_at DATE,
|
|
confirmed BOOLEAN,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_ed_player ON evolution_detections(player_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ed_date ON evolution_detections(detection_date);
|
|
|
|
-- ============================================================
|
|
-- Unconventional Factor Validation Log
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS unconventional_validations (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
factor_name TEXT NOT NULL,
|
|
validated BOOLEAN NOT NULL,
|
|
pearson_r DECIMAL,
|
|
p_value DECIMAL,
|
|
corrected_alpha DECIMAL,
|
|
sample_size INTEGER,
|
|
validated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_uv_factor ON unconventional_validations(factor_name);
|
|
|
|
-- ============================================================
|
|
-- Row Level Security
|
|
-- ============================================================
|
|
ALTER TABLE coaching_tendencies ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE player_out_history ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE evolution_detections ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE unconventional_validations ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY ct_svc ON coaching_tendencies FOR ALL USING (auth.role() = 'service_role');
|
|
CREATE POLICY poh_svc ON player_out_history FOR ALL USING (auth.role() = 'service_role');
|
|
CREATE POLICY ed_svc ON evolution_detections FOR ALL USING (auth.role() = 'service_role');
|
|
CREATE POLICY uv_svc ON unconventional_validations FOR ALL USING (auth.role() = 'service_role');
|