27 lines
949 B
SQL
27 lines
949 B
SQL
-- Migration: 005_lineup_scheme_data.sql
|
|
-- Lineup scheme data for nightly play-by-play collection.
|
|
-- Created: 2026-04-13
|
|
|
|
CREATE TABLE IF NOT EXISTS lineup_scheme_data (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
game_id TEXT NOT NULL,
|
|
game_date DATE NOT NULL,
|
|
team_id TEXT NOT NULL,
|
|
lineup_hash TEXT NOT NULL,
|
|
player_ids TEXT[] NOT NULL,
|
|
play_type TEXT NOT NULL,
|
|
possessions INTEGER DEFAULT 0,
|
|
points INTEGER DEFAULT 0,
|
|
fg_made INTEGER DEFAULT 0,
|
|
fg_attempted INTEGER DEFAULT 0,
|
|
turnovers INTEGER DEFAULT 0,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_ls_team ON lineup_scheme_data(team_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ls_hash ON lineup_scheme_data(lineup_hash);
|
|
CREATE INDEX IF NOT EXISTS idx_ls_date ON lineup_scheme_data(game_date);
|
|
|
|
ALTER TABLE lineup_scheme_data ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY ls_svc ON lineup_scheme_data FOR ALL USING (auth.role() = 'service_role');
|