173 lines
6.6 KiB
SQL
173 lines
6.6 KiB
SQL
-- Migration: 007_lineup_odds_trust_health.sql
|
|
-- Lineup updates, reporter trust, odds warehouse, line movements,
|
|
-- reporter-line correlation, API health log, global calibration, joint outcomes.
|
|
-- Created: 2026-04-13
|
|
|
|
-- ============================================================
|
|
-- Lineup Updates
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS lineup_updates (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
sport TEXT NOT NULL,
|
|
team_id TEXT NOT NULL,
|
|
player_name TEXT NOT NULL,
|
|
player_id TEXT,
|
|
status TEXT NOT NULL,
|
|
source TEXT NOT NULL,
|
|
trust_level TEXT NOT NULL,
|
|
reporter_handle TEXT,
|
|
confidence DECIMAL,
|
|
raw_data JSONB,
|
|
game_date DATE NOT NULL,
|
|
detected_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_lu_team ON lineup_updates(team_id);
|
|
CREATE INDEX IF NOT EXISTS idx_lu_date ON lineup_updates(game_date);
|
|
|
|
-- ============================================================
|
|
-- Reporter Trust
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS reporter_trust (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
handle TEXT NOT NULL UNIQUE,
|
|
sport TEXT NOT NULL,
|
|
team_id TEXT NOT NULL,
|
|
outlet TEXT,
|
|
source_type TEXT NOT NULL DEFAULT 'beat_writer',
|
|
trust_level TEXT DEFAULT 'unverified',
|
|
starting_trust TEXT,
|
|
total_tracked INTEGER DEFAULT 0,
|
|
correct_count INTEGER DEFAULT 0,
|
|
accuracy DECIMAL DEFAULT 0.0,
|
|
last_tracked_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_rt_handle ON reporter_trust(handle);
|
|
CREATE INDEX IF NOT EXISTS idx_rt_trust ON reporter_trust(trust_level);
|
|
|
|
-- ============================================================
|
|
-- Odds Warehouse
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS odds_warehouse (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
sport TEXT NOT NULL,
|
|
game_id TEXT NOT NULL,
|
|
game_date DATE NOT NULL,
|
|
player_name TEXT NOT NULL,
|
|
player_id TEXT,
|
|
market TEXT NOT NULL,
|
|
bookmaker TEXT NOT NULL,
|
|
line DECIMAL,
|
|
price INTEGER,
|
|
over_under TEXT,
|
|
scan_type TEXT NOT NULL,
|
|
fetched_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_ow_sport_date ON odds_warehouse(sport, game_date);
|
|
CREATE INDEX IF NOT EXISTS idx_ow_player ON odds_warehouse(player_name);
|
|
CREATE INDEX IF NOT EXISTS idx_ow_scan ON odds_warehouse(scan_type);
|
|
|
|
-- ============================================================
|
|
-- Line Movements (ship version — new table, not conflicting with existing)
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS ship_line_movements (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
sport TEXT NOT NULL,
|
|
player_name TEXT NOT NULL,
|
|
market TEXT NOT NULL,
|
|
bookmaker TEXT NOT NULL,
|
|
opening_line DECIMAL,
|
|
current_line DECIMAL,
|
|
movement DECIMAL,
|
|
direction TEXT,
|
|
game_date DATE NOT NULL,
|
|
detected_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_slm_sport_date ON ship_line_movements(sport, game_date);
|
|
|
|
-- ============================================================
|
|
-- Reporter-Line Correlation
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS reporter_line_correlation (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
reporter_handle TEXT NOT NULL,
|
|
sport TEXT NOT NULL,
|
|
player_name TEXT NOT NULL,
|
|
tweet_content TEXT,
|
|
tweet_time TIMESTAMPTZ NOT NULL,
|
|
line_movement_time TIMESTAMPTZ,
|
|
gap_minutes DECIMAL,
|
|
prop_affected TEXT,
|
|
line_before DECIMAL,
|
|
line_after DECIMAL,
|
|
bookmaker TEXT,
|
|
game_date DATE NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_rlc_reporter ON reporter_line_correlation(reporter_handle);
|
|
CREATE INDEX IF NOT EXISTS idx_rlc_date ON reporter_line_correlation(game_date);
|
|
|
|
-- ============================================================
|
|
-- API Health Log
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS api_health_log (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
api_name TEXT NOT NULL,
|
|
error_message TEXT,
|
|
games_tonight INTEGER DEFAULT 0,
|
|
failed_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_ahl_api ON api_health_log(api_name);
|
|
CREATE INDEX IF NOT EXISTS idx_ahl_time ON api_health_log(failed_at);
|
|
|
|
-- ============================================================
|
|
-- Global Calibration
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS global_calibration (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
sport TEXT NOT NULL UNIQUE,
|
|
offset_value DECIMAL DEFAULT 0.0,
|
|
brier_score DECIMAL,
|
|
sample_size INTEGER DEFAULT 0,
|
|
blind_spots JSONB,
|
|
calculated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- ============================================================
|
|
-- Joint Outcomes (ship version)
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS ship_joint_outcomes (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
player_a_id TEXT NOT NULL,
|
|
player_b_id TEXT NOT NULL,
|
|
stat_a TEXT NOT NULL,
|
|
stat_b TEXT NOT NULL,
|
|
hit_a BOOLEAN NOT NULL,
|
|
hit_b BOOLEAN NOT NULL,
|
|
game_date DATE NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_sjo_players ON ship_joint_outcomes(player_a_id, player_b_id);
|
|
CREATE INDEX IF NOT EXISTS idx_sjo_date ON ship_joint_outcomes(game_date);
|
|
|
|
-- ============================================================
|
|
-- Row Level Security
|
|
-- ============================================================
|
|
ALTER TABLE lineup_updates ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE reporter_trust ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE odds_warehouse ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE ship_line_movements ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE reporter_line_correlation ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE api_health_log ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE global_calibration ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE ship_joint_outcomes ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY lu_svc ON lineup_updates FOR ALL USING (auth.role() = 'service_role');
|
|
CREATE POLICY rt_svc ON reporter_trust FOR ALL USING (auth.role() = 'service_role');
|
|
CREATE POLICY ow_svc ON odds_warehouse FOR ALL USING (auth.role() = 'service_role');
|
|
CREATE POLICY slm_svc ON ship_line_movements FOR ALL USING (auth.role() = 'service_role');
|
|
CREATE POLICY rlc_svc ON reporter_line_correlation FOR ALL USING (auth.role() = 'service_role');
|
|
CREATE POLICY ahl_svc ON api_health_log FOR ALL USING (auth.role() = 'service_role');
|
|
CREATE POLICY gc_svc ON global_calibration FOR ALL USING (auth.role() = 'service_role');
|
|
CREATE POLICY sjo_svc ON ship_joint_outcomes FOR ALL USING (auth.role() = 'service_role');
|