game-scoreboard/supabase/migrations/0005_cohosts.sql

-- Game Night Live — sharing an event with other admins (co-hosts).
-- Run after 0004_buzz_result.sql.
--
-- The owner shares an event by email. A co-host can do everything in the event (setup, control
-- room, scores) except delete it or change who it is shared with.
--
-- Shares are keyed by email, not user id, so an admin can be added before their first login.
-- The email comes from the signed JWT, which is only as trustworthy as the project's auth
-- settings: keep "Confirm email" on (magic links confirm it anyway). ADMIN_EMAILS relies on
-- the same claim.

create table event_cohosts (
  id          uuid primary key default gen_random_uuid(),
  event_id    uuid not null references events(id) on delete cascade,
  email       text not null check (email = lower(email) and length(email) between 3 and 254),
  created_at  timestamptz not null default now(),
  constraint event_cohosts_one_per_email unique (event_id, email)
);
create index on event_cohosts(email);

-- ---------- helpers ----------
-- security definer: they read events / event_cohosts without going back through RLS.

create or replace function is_event_cohost(eid uuid) returns boolean
language sql stable security definer set search_path = public as $$
  select exists (
    select 1 from event_cohosts
    where event_id = eid and email = lower(auth.jwt() ->> 'email')
  )
$$;

create or replace function is_event_admin(eid uuid) returns boolean
language sql stable security definer set search_path = public as $$
  select is_event_owner(eid) or is_event_cohost(eid)
$$;

-- A co-host may update the event row, so the policy can't be what protects the owner column.
create or replace function keep_event_owner() returns trigger
language plpgsql as $$
begin
  if new.owner_id is distinct from old.owner_id then
    raise exception 'the owner of an event cannot be changed';
  end if;
  return new;
end $$;

create trigger events_keep_owner
before update on events
for each row execute function keep_event_owner();

-- ---------- RLS ----------
-- Still no anon policies anywhere.

alter table event_cohosts enable row level security;

-- Co-hosts can see who else is on the event; only the owner changes the list.
create policy admin_read   on event_cohosts for select to authenticated
  using (is_event_admin(event_id));
create policy owner_insert on event_cohosts for insert to authenticated
  with check (is_event_owner(event_id));
create policy owner_delete on event_cohosts for delete to authenticated
  using (is_event_owner(event_id));

-- events: the owner check stays on the column (not is_event_admin) so that
-- INSERT ... RETURNING can see the row it has just inserted.
drop policy owner_all on events;
create policy admin_read   on events for select to authenticated
  using (owner_id = auth.uid() or is_event_cohost(id));
create policy owner_insert on events for insert to authenticated
  with check (owner_id = auth.uid());
create policy admin_update on events for update to authenticated
  using (owner_id = auth.uid() or is_event_cohost(id))
  with check (owner_id = auth.uid() or is_event_cohost(id));
create policy owner_delete on events for delete to authenticated
  using (owner_id = auth.uid());

-- everything inside an event: owner or co-host
drop policy owner_all on teams;
drop policy owner_all on games;
drop policy owner_all on adjustments;
drop policy owner_all on criteria;
drop policy owner_all on judges;
drop policy owner_all on scores;
drop policy owner_all on buzzes;

create policy admin_all on teams       for all to authenticated
  using (is_event_admin(event_id)) with check (is_event_admin(event_id));
create policy admin_all on games       for all to authenticated
  using (is_event_admin(event_id)) with check (is_event_admin(event_id));
create policy admin_all on adjustments for all to authenticated
  using (is_event_admin(event_id)) with check (is_event_admin(event_id));
create policy admin_all on criteria    for all to authenticated
  using (is_event_admin(game_event(game_id))) with check (is_event_admin(game_event(game_id)));
create policy admin_all on judges      for all to authenticated
  using (is_event_admin(game_event(game_id))) with check (is_event_admin(game_event(game_id)));
create policy admin_all on scores      for all to authenticated
  using (is_event_admin(game_event(game_id))) with check (is_event_admin(game_event(game_id)));
create policy admin_all on buzzes      for all to authenticated
  using (is_event_admin(game_event(game_id))) with check (is_event_admin(game_event(game_id)));