joshuaandClaude Opus 5.5
bbcf84bbc3
Each team gets a buzzer link (/b/[token]); a game opts in with a
"Teams buzz in" checkbox. The database's clock orders the buzzes, so
the result doesn't depend on phone clocks. The control room lists the
order and clears it per question (B); the scoreboard shows who buzzed
first with a ding.
Buzzes follow the existing rules: token-gated route handlers with the
service-role client, RLS on the new table, and data-free realtime pings
on a new buzzer:{eventId} channel.
Needs supabase/migrations/0003_buzzer.sql applied before deploying.
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
45 lines
1.8 KiB
PL/PgSQL
45 lines
1.8 KiB
PL/PgSQL
-- Game Night Live — team buzzers.
|
|
-- Each team gets its own buzzer link; a game opts in with buzzer_enabled.
|
|
-- Run after 0002_timer.sql.
|
|
--
|
|
-- A buzz is one row per (game, team). The database's clock stamps it, so the order never
|
|
-- depends on a phone's clock or on which server instance handled the request.
|
|
-- Clearing the buzzers for the next question = deleting the game's rows.
|
|
|
|
alter table games
|
|
add column buzzer_enabled boolean not null default false;
|
|
|
|
-- new_token() is volatile, so every existing team gets its own token here.
|
|
alter table teams
|
|
add column buzzer_token text not null unique default new_token();
|
|
|
|
create table buzzes (
|
|
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,
|
|
-- clock_timestamp(), not now(): the real moment of the insert, not the transaction's start.
|
|
buzzed_at timestamptz not null default clock_timestamp(),
|
|
constraint buzzes_one_per_team unique (game_id, team_id)
|
|
);
|
|
|
|
-- the team must be in the same event as the game
|
|
create or replace function validate_buzz() returns trigger
|
|
language plpgsql as $$
|
|
begin
|
|
if (select event_id from teams where id = new.team_id)
|
|
is distinct from (select event_id from games where id = new.game_id) then
|
|
raise exception 'team does not belong to the game''s event';
|
|
end if;
|
|
return new;
|
|
end $$;
|
|
|
|
create trigger buzzes_validate
|
|
before insert or update on buzzes
|
|
for each row execute function validate_buzz();
|
|
|
|
-- Same rule as every other table: owner only, anon has no policies.
|
|
alter table buzzes enable row level security;
|
|
|
|
create policy owner_all on buzzes for all to authenticated
|
|
using (is_event_owner(game_event(game_id))) with check (is_event_owner(game_event(game_id)));
|