feat(competitions): Supabase saves table and public save-count trigger (SQL inline)

[AnayDhawan]

Saving a competition needs a private per-user table. Showing "61 people saved this" needs a public aggregate. Those two pull in opposite directions under row level security: if competition_saves is readable only by its owner, nobody can COUNT() it.

Two tables resolve it. Saves stay private, the aggregate is public, and only a trigger writes to it. No SECURITY DEFINER view, no exposing user rows.

Run this yourself

The three existing migrations in supabase/migrations/ ship as SQL files and are not applied automatically. Their own header comments say so. So this SQL is here in full: open the Supabase dashboard, go to the SQL editor, paste, run. You need dashboard access on the project; if you do not have it, say so on this issue before you start rather than working around it.

Also commit it as supabase/migrations/<YYYYMMDD>_create_competition_saves.sql so the repo record matches what is deployed, following the naming of 20260701_create_user_events.sql.

-- competition_saves: one row per (user, competition). Private to its owner.
create table if not exists public.competition_saves (
  user_id        uuid not null default auth.uid() references auth.users(id) on delete cascade,
  competition_id text not null,
  created_at     timestamptz not null default now(),
  primary key (user_id, competition_id)
);

create index if not exists competition_saves_competition_idx
  on public.competition_saves (competition_id);

alter table public.competition_saves enable row level security;

create policy "own saves are selectable" on public.competition_saves
  for select using (auth.uid() = user_id);
create policy "own saves are insertable" on public.competition_saves
  for insert with check (auth.uid() = user_id);
create policy "own saves are deletable" on public.competition_saves
  for delete using (auth.uid() = user_id);

-- competition_stats: public aggregate. Readable by anyone, written only by the trigger.
create table if not exists public.competition_stats (
  competition_id text primary key,
  save_count     int not null default 0 check (save_count >= 0),
  updated_at     timestamptz not null default now()
);

alter table public.competition_stats enable row level security;

create policy "stats are public" on public.competition_stats
  for select using (true);
-- Deliberately no insert/update/delete policy. Only the trigger below writes here,
-- and it runs as SECURITY DEFINER so it bypasses RLS.

create or replace function public.bump_competition_save_count()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  if (tg_op = 'INSERT') then
    insert into public.competition_stats (competition_id, save_count, updated_at)
    values (new.competition_id, 1, now())
    on conflict (competition_id)
      do update set save_count = competition_stats.save_count + 1, updated_at = now();
    return new;
  elsif (tg_op = 'DELETE') then
    update public.competition_stats
       set save_count = greatest(save_count - 1, 0), updated_at = now()
     where competition_id = old.competition_id;
    return old;
  end if;
  return null;
end;
$$;

drop trigger if exists competition_saves_count_trigger on public.competition_saves;
create trigger competition_saves_count_trigger
  after insert or delete on public.competition_saves
  for each row execute function public.bump_competition_save_count();

Why it is shaped this way

set search_path = public on a SECURITY DEFINER function is not optional. Without it the function resolves unqualified names against the caller's search path, which is a privilege escalation route. Do not drop that line.

greatest(save_count - 1, 0) stops the counter going negative if a delete ever fires without a matching insert.

The on conflict upsert means the stats row is created lazily on first save, so there is no seeding step and no row for a competition nobody has saved.

Verify after running

-- as an authenticated user, from the app
insert into competition_saves (competition_id) values ('breakthrough-junior-challenge');
select * from competition_stats where competition_id = 'breakthrough-junior-challenge';  -- save_count = 1
delete from competition_saves where competition_id = 'breakthrough-junior-challenge';
select * from competition_stats where competition_id = 'breakthrough-junior-challenge';  -- save_count = 0

Then confirm the isolation actually holds: sign in as a second user and check that select * from competition_saves returns only that user's own rows, while select * from competition_stats returns everything.

Acceptance criteria

• Both tables exist with RLS enabled
• A signed-in user cannot read another user's saves
• An anonymous visitor can read competition_stats
• An anonymous visitor cannot write to competition_stats directly
• Insert then delete leaves save_count at 0, never negative
• Migration file committed under supabase/migrations/
• SELF-HOSTING.md documents the new tables alongside the existing three