-- Game Night Live — initial schema -- Requires Postgres 15+ (Supabase default) for NULLS NOT DISTINCT. create extension if not exists pgcrypto; -- ---------- enums ---------- create type scoring_mode as enum ('judged', 'direct'); create type points_mode as enum ('raw', 'placement'); create type game_status as enum ('upcoming', 'live', 'done'); create type scoreboard_visibility as enum ('visible', 'frozen', 'hidden'); create type criterion_input as enum ('blocks', 'number'); create type judge_source as enum ('nominated', 'open_link'); -- token helper: 32 hex chars = 128 bits create or replace function new_token() returns text language sql volatile as $$ select encode(gen_random_bytes(16), 'hex') $$; -- ---------- tables ---------- create table events ( id uuid primary key default gen_random_uuid(), owner_id uuid not null references auth.users(id) on delete cascade, name text not null check (length(name) between 1 and 120), event_date date, scoreboard_token text not null unique default new_token(), scoreboard_visibility scoreboard_visibility not null default 'visible', frozen_snapshot jsonb, current_game_id uuid, reveal_active boolean not null default false, reveal_count int not null default 0, created_at timestamptz not null default now(), updated_at timestamptz not null default now() ); create table teams ( id uuid primary key default gen_random_uuid(), event_id uuid not null references events(id) on delete cascade, name text not null check (length(name) between 1 and 60), color text not null default '#3A86FF' check (color ~ '^#[0-9A-Fa-f]{6}$'), sort_order int not null default 0, created_at timestamptz not null default now() ); create index on teams(event_id); create table games ( id uuid primary key default gen_random_uuid(), event_id uuid not null references events(id) on delete cascade, title text not null check (length(title) between 1 and 120), description text, sort_order int not null default 0, scoring_mode scoring_mode not null default 'direct', points_mode points_mode not null default 'raw', multiplier numeric(6,2) not null default 1 check (multiplier >= 0), placement_points int[] not null default '{10,7,5,3}', status game_status not null default 'upcoming', admin_notes text, judge_notes text, open_token text not null unique default new_token(), open_link_enabled boolean not null default false, judging_locked boolean not null default false, spotlight_team_id uuid references teams(id) on delete set null, created_at timestamptz not null default now(), updated_at timestamptz not null default now() ); create index on games(event_id, sort_order); alter table events add constraint events_current_game_fk foreign key (current_game_id) references games(id) on delete set null; create table criteria ( id uuid primary key default gen_random_uuid(), game_id uuid not null references games(id) on delete cascade, label text not null check (length(label) between 1 and 60), description text, input_type criterion_input not null default 'blocks', min_value numeric(8,2) not null default 1, max_value numeric(8,2) not null default 5, step numeric(8,2) not null default 1 check (step > 0), weight numeric(6,2) not null default 1 check (weight >= 0), sort_order int not null default 0, check (max_value > min_value), -- blocks render one button per step; keep it tappable check (input_type <> 'blocks' or (max_value - min_value) / step <= 10) ); create index on criteria(game_id, sort_order); create table judges ( id uuid primary key default gen_random_uuid(), game_id uuid not null references games(id) on delete cascade, name text not null check (length(name) between 1 and 60), token text not null unique default new_token(), source judge_source not null default 'nominated', revoked boolean not null default false, created_at timestamptz not null default now() ); create index on judges(game_id); create table scores ( id uuid primary key default gen_random_uuid(), game_id uuid not null references games(id) on delete cascade, team_id uuid not null references teams(id) on delete cascade, criterion_id uuid not null references criteria(id) on delete cascade, judge_id uuid references judges(id) on delete cascade, -- NULL = admin score / override value numeric(8,2) not null, updated_at timestamptz not null default now(), constraint scores_one_per_cell unique nulls not distinct (team_id, criterion_id, judge_id) ); create index on scores(game_id); create table adjustments ( id uuid primary key default gen_random_uuid(), event_id uuid not null references events(id) on delete cascade, team_id uuid not null references teams(id) on delete cascade, game_id uuid references games(id) on delete set null, points numeric(8,2) not null check (points <> 0), reason text, created_at timestamptz not null default now() ); create index on adjustments(event_id); -- ---------- integrity triggers ---------- -- score must be in range, on step, and criterion must belong to the score's game create or replace function validate_score() returns trigger language plpgsql as $$ declare c criteria%rowtype; begin select * into c from criteria where id = new.criterion_id; if c.game_id <> new.game_id then raise exception 'criterion does not belong to game'; end if; if new.value < c.min_value or new.value > c.max_value then raise exception 'value % out of range [%, %]', new.value, c.min_value, c.max_value; end if; if mod(new.value - c.min_value, c.step) <> 0 then raise exception 'value % not on step %', new.value, c.step; end if; if new.judge_id is not null and (select game_id from judges where id = new.judge_id) <> new.game_id then raise exception 'judge does not belong to game'; end if; new.updated_at := now(); return new; end $$; create trigger scores_validate before insert or update on scores for each row execute function validate_score(); create or replace function touch_updated_at() returns trigger language plpgsql as $$ begin new.updated_at := now(); return new; end $$; create trigger events_touch before update on events for each row execute function touch_updated_at(); create trigger games_touch before update on games for each row execute function touch_updated_at(); -- ---------- RLS ---------- -- Only the authenticated owner can touch anything. Anon has NO policies. -- Public + judge access goes through server route handlers using the service role. create or replace function is_event_owner(eid uuid) returns boolean language sql stable security definer set search_path = public as $$ select exists (select 1 from events where id = eid and owner_id = auth.uid()) $$; create or replace function game_event(gid uuid) returns uuid language sql stable security definer set search_path = public as $$ select event_id from games where id = gid $$; alter table events enable row level security; alter table teams enable row level security; alter table games enable row level security; alter table criteria enable row level security; alter table judges enable row level security; alter table scores enable row level security; alter table adjustments enable row level security; create policy owner_all on events for all to authenticated using (owner_id = auth.uid()) with check (owner_id = auth.uid()); create policy owner_all on teams for all to authenticated using (is_event_owner(event_id)) with check (is_event_owner(event_id)); create policy owner_all on games for all to authenticated using (is_event_owner(event_id)) with check (is_event_owner(event_id)); create policy owner_all on adjustments for all to authenticated using (is_event_owner(event_id)) with check (is_event_owner(event_id)); create policy owner_all on criteria for all to authenticated using (is_event_owner(game_event(game_id))) with check (is_event_owner(game_event(game_id))); create policy owner_all on judges for all to authenticated using (is_event_owner(game_event(game_id))) with check (is_event_owner(game_event(game_id))); create policy owner_all on scores for all to authenticated using (is_event_owner(game_event(game_id))) with check (is_event_owner(game_event(game_id)));