← zurueck zu den Schritten

Circles — Datenbank-Migration

Das hier legt die drei neuen Tabellen an und zieht die Leserechte nach. Re-run-sicher — zweimal ausfuehren schadet nicht.

1. Kopieren — Knopf unten.
2. SQL Editor oeffnen — Knopf unten, oeffnet direkt eine neue Query.
3. Einfuegen und Run druecken.

-- ============================================================
-- CIRCLE, Schritt 3: Circles (Gruppen), Beitritt per Code,
-- und – wichtiger Teil – Fragen, die nur ihre Circles sehen.
--
-- Re-run-sicher. Im Supabase SQL-Editor einmal komplett ausfuehren.
-- ============================================================

-- ---------- Tabellen ----------

-- Einladungscode: 6 Zeichen, ohne 0/O/1/I/L, damit ihn niemand abtippt und
-- daneben liegt.
create or replace function public.new_invite_code()
returns text language sql volatile as $$
  select string_agg(
    substr('23456789ABCDEFGHJKMNPQRSTUVWXYZ',
           floor(random() * 31 + 1)::int, 1), '')
  from generate_series(1, 6);
$$;

create table if not exists public.circles (
  id uuid primary key default gen_random_uuid(),
  name text not null check (char_length(trim(name)) between 1 and 40),
  emoji text not null default '👥',
  owner_id uuid not null references public.profiles(id) on delete cascade,
  invite_code text not null unique default public.new_invite_code(),
  created_at timestamptz not null default now()
);

create table if not exists public.circle_members (
  circle_id uuid not null references public.circles(id) on delete cascade,
  member_id uuid not null references public.profiles(id) on delete cascade,
  joined_at timestamptz not null default now(),
  primary key (circle_id, member_id)
);

-- Ohne Eintrag hier geht eine Frage an alle (bisheriges Verhalten).
create table if not exists public.question_circles (
  question_id uuid not null references public.questions(id) on delete cascade,
  circle_id uuid not null references public.circles(id) on delete cascade,
  primary key (question_id, circle_id)
);

create index if not exists circle_members_member_idx on public.circle_members(member_id);
create index if not exists question_circles_circle_idx on public.question_circles(circle_id);

-- ---------- Wer anlegt, ist drin ----------
create or replace function public.on_circle_created()
returns trigger language plpgsql security definer set search_path = public as $$
begin
  insert into public.circle_members (circle_id, member_id)
  values (new.id, new.owner_id)
  on conflict do nothing;
  return new;
end; $$;

drop trigger if exists on_circle_created_trg on public.circles;
create trigger on_circle_created_trg
  after insert on public.circles
  for each row execute function public.on_circle_created();

-- ---------- Helfer ----------
-- SECURITY DEFINER ist hier Absicht und noetig: wuerde die Policy auf
-- circle_members direkt wieder circle_members abfragen, dreht Postgres sich
-- in eine Endlosschleife. Die Funktion umgeht RLS bewusst und beantwortet
-- nur genau eine Ja/Nein-Frage ueber den *aufrufenden* Nutzer.
create or replace function public.is_circle_member(cid uuid)
returns boolean language sql security definer stable set search_path = public as $$
  select exists (
    select 1 from public.circle_members m
    where m.circle_id = cid and m.member_id = auth.uid()
  );
$$;

-- Darf der aufrufende Nutzer diese Frage sehen?
-- Ja, wenn: er sie gestellt hat, ODER sie an niemanden bestimmten gerichtet
-- ist, ODER er in mindestens einem der Ziel-Circles steckt.
create or replace function public.can_see_question(qid uuid)
returns boolean language sql security definer stable set search_path = public as $$
  select
    exists (select 1 from public.questions q
            where q.id = qid and q.author_id = auth.uid())
    or not exists (select 1 from public.question_circles qc
                   where qc.question_id = qid)
    or exists (select 1 from public.question_circles qc
               join public.circle_members m
                 on m.circle_id = qc.circle_id and m.member_id = auth.uid()
               where qc.question_id = qid);
$$;

grant execute on function public.is_circle_member(uuid)  to authenticated;
grant execute on function public.can_see_question(uuid)  to authenticated;

-- ---------- Beitreten per Code ----------
-- Laeuft ueber eine Funktion statt ueber eine INSERT-Policy: sonst muesste
-- jeder alle Circles lesen duerfen, um den Code zu pruefen.
create or replace function public.join_circle(code text)
returns table (id uuid, name text, emoji text)
language plpgsql security definer set search_path = public as $$
declare c public.circles%rowtype;
begin
  select * into c from public.circles
   where upper(invite_code) = upper(trim(code));
  if not found then
    raise exception 'circle_not_found' using errcode = 'P0002';
  end if;

  insert into public.circle_members (circle_id, member_id)
  values (c.id, auth.uid())
  on conflict do nothing;

  return query select c.id, c.name, c.emoji;
end; $$;

grant execute on function public.join_circle(text) to authenticated;

-- ---------- Row Level Security ----------
alter table public.circles          enable row level security;
alter table public.circle_members   enable row level security;
alter table public.question_circles enable row level security;

drop policy if exists "circles_read"   on public.circles;
drop policy if exists "circles_insert" on public.circles;
drop policy if exists "circles_update" on public.circles;
drop policy if exists "circles_delete" on public.circles;
-- Kein "using (true)": einen Circle sieht nur, wer drin ist.
create policy "circles_read"   on public.circles for select
  using (public.is_circle_member(id));
create policy "circles_insert" on public.circles for insert
  with check (owner_id = auth.uid());
create policy "circles_update" on public.circles for update
  using (owner_id = auth.uid()) with check (owner_id = auth.uid());
create policy "circles_delete" on public.circles for delete
  using (owner_id = auth.uid());

drop policy if exists "circle_members_read"   on public.circle_members;
drop policy if exists "circle_members_delete" on public.circle_members;
-- Mitglieder sehen einander; Beitritt laeuft ausschliesslich ueber
-- join_circle(), deshalb bewusst KEINE insert-Policy.
create policy "circle_members_read" on public.circle_members for select
  using (public.is_circle_member(circle_id));
-- Austreten darf man selbst; der Besitzer darf jemanden entfernen.
create policy "circle_members_delete" on public.circle_members for delete
  using (
    member_id = auth.uid()
    or exists (select 1 from public.circles c
               where c.id = circle_id and c.owner_id = auth.uid())
  );

drop policy if exists "question_circles_read"   on public.question_circles;
drop policy if exists "question_circles_insert" on public.question_circles;
create policy "question_circles_read" on public.question_circles for select
  using (public.is_circle_member(circle_id));
create policy "question_circles_insert" on public.question_circles for insert
  with check (
    exists (select 1 from public.questions q
            where q.id = question_id and q.author_id = auth.uid())
    and public.is_circle_member(circle_id)
  );

-- ---------- Bestehende Policies nachziehen ----------
-- Bisher galt ueberall "using (true)" – damit waere eine Frage an einen
-- Circle zwar gefiltert, ihre Optionen und Kommentare aber weiter fuer alle
-- lesbar. Deshalb haengen jetzt alle vier an can_see_question().
drop policy if exists "questions_read" on public.questions;
create policy "questions_read" on public.questions for select
  using (public.can_see_question(id));

drop policy if exists "options_read" on public.options;
create policy "options_read" on public.options for select
  using (public.can_see_question(question_id));

drop policy if exists "comments_read" on public.comments;
create policy "comments_read" on public.comments for select
  using (public.can_see_question(question_id));

drop policy if exists "comments_insert" on public.comments;
create policy "comments_insert" on public.comments for insert
  with check (author_id = auth.uid() and public.can_see_question(question_id));

-- Abstimmen laeuft ueber cast_vote() (SECURITY DEFINER), nicht ueber eine
-- INSERT-Policy – die Pruefung gehoert also in die Funktion. Bewusst KEINE
-- votes_insert-Policy anlegen: das wuerde den direkten Weg erst oeffnen.
create or replace function public.cast_vote(p_option_id uuid)
returns void language plpgsql security definer set search_path = public as $$
declare
  v_question uuid;
  v_uid uuid := auth.uid();
  v_today date := (now() at time zone 'utc')::date;
  v_last date;
  v_streak int;
begin
  if v_uid is null then raise exception 'not authenticated'; end if;
  select question_id into v_question from public.options where id = p_option_id;
  if v_question is null then raise exception 'option not found'; end if;

  -- neu: nicht fuer Fragen abstimmen, die man gar nicht sehen darf
  if not public.can_see_question(v_question) then
    raise exception 'not allowed' using errcode = '42501';
  end if;

  -- verhindert Doppel-Abstimmung (unique question_id+voter_id)
  insert into public.votes (question_id, option_id, voter_id)
    values (v_question, p_option_id, v_uid);

  update public.options set votes = votes + 1 where id = p_option_id;

  select last_vote_day, streak into v_last, v_streak
    from public.profiles where id = v_uid;
  if v_last = v_today then
    null;
  elsif v_last = v_today - 1 then
    v_streak := coalesce(v_streak, 0) + 1;
  else
    v_streak := 1;
  end if;

  update public.profiles
    set credits = credits + 1, votes_cast = votes_cast + 1,
        streak = v_streak, last_vote_day = v_today
    where id = v_uid;
end; $$;

grant execute on function public.cast_vote(uuid) to authenticated;

-- ---------- Realtime ----------
-- "add table" wirft einen Fehler, wenn die Tabelle schon drin ist – deshalb
-- geprueft, damit die Migration wirklich mehrfach laufen kann.
do $$
begin
  if not exists (
    select 1 from pg_publication_tables
    where pubname = 'supabase_realtime'
      and schemaname = 'public' and tablename = 'circle_members'
  ) then
    alter publication supabase_realtime add table public.circle_members;
  end if;
end $$;