Blame for db/schema.sql

-- Vion Training — Supabase schema (Phase 1)
-- Run this in the Supabase SQL Editor (Dashboard → SQL Editor → New query → paste → Run).
-- Safe to re-run: uses IF NOT EXISTS / OR REPLACE throughout.
--
-- Auth itself (email/password, password reset, JWT) is handled by Supabase's
-- built-in `auth.users` table. Everything below is OUR application data, keyed
-- to that user id. Row Level Security (RLS) ensures a user can only read their
-- own rows, while admins can read everyone's.

-- ──────────────────────────────────────────────────────────────────────────
-- 1. Profiles — one row per auth user, holding app-level fields + role.
-- ──────────────────────────────────────────────────────────────────────────
create table if not exists public.profiles (
  id                   uuid primary key references auth.users(id) on delete cascade,
  email                text,
  full_name            text,
  role                 text not null default 'user' check (role in ('user', 'admin')),
  language_preference  text not null default 'en' check (language_preference in ('en', 'de')),
  theme                text not null default 'system' check (theme in ('light', 'dark', 'system')),
  notifications_enabled boolean not null default true,
  created_at           timestamptz not null default now()
);

-- Auto-create a profile row whenever a new auth user signs up.
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = public
as $$
begin
  insert into public.profiles (id, email, full_name)
  values (new.id, new.email, new.raw_user_meta_data->>'full_name')
  on conflict (id) do nothing;
  return new;
end;
$$;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute function public.handle_new_user();

-- Helper: is the current request from an admin? (security definer avoids RLS recursion)
create or replace function public.is_admin()
returns boolean
language sql
security definer set search_path = public
stable
as $$
  select exists (
    select 1 from public.profiles
    where id = auth.uid() and role = 'admin'
  );
$$;

-- ──────────────────────────────────────────────────────────────────────────
-- 2. Sessions — one row per training call.
-- ──────────────────────────────────────────────────────────────────────────
create table if not exists public.sessions (
  id           uuid primary key default gen_random_uuid(),
  user_id      uuid not null references public.profiles(id) on delete cascade,
  scenario_id  text not null,
  persona_id   text,
  language     text not null default 'en',
  status       text not null default 'created'
                 check (status in ('created', 'active', 'ended', 'error')),
  transcript   jsonb not null default '[]'::jsonb,
  user_notes   text,                          -- the trainee's own notes on this call
  started_at   timestamptz,
  ended_at     timestamptz,
  created_at   timestamptz not null default now()
);
create index if not exists sessions_user_id_idx  on public.sessions(user_id);
create index if not exists sessions_created_at_idx on public.sessions(created_at desc);

-- ──────────────────────────────────────────────────────────────────────────
-- 3. Evaluations — the LLM-scored result of a call (1:1 with a session).
-- ──────────────────────────────────────────────────────────────────────────
create table if not exists public.evaluations (
  id            uuid primary key default gen_random_uuid(),
  session_id    uuid not null unique references public.sessions(id) on delete cascade,
  user_id       uuid not null references public.profiles(id) on delete cascade,
  score         int  check (score between 0 and 100),
  passed        boolean,
  summary       text,
  good_points   jsonb not null default '[]'::jsonb,   -- ["...", "..."]
  improvements  jsonb not null default '[]'::jsonb,   -- ["...", "..."]
  created_at    timestamptz not null default now()
);
create index if not exists evaluations_user_id_idx on public.evaluations(user_id);

-- ──────────────────────────────────────────────────────────────────────────
-- 4. Feedback — free-text app feedback from the "Feedback" dashboard item.
-- ──────────────────────────────────────────────────────────────────────────
create table if not exists public.feedback (
  id         uuid primary key default gen_random_uuid(),
  user_id    uuid not null references public.profiles(id) on delete cascade,
  body       text not null,
  created_at timestamptz not null default now()
);

-- ──────────────────────────────────────────────────────────────────────────
-- 5. Row Level Security — lock every table to its owner; admins see all.
-- ──────────────────────────────────────────────────────────────────────────
alter table public.profiles    enable row level security;
alter table public.sessions    enable row level security;
alter table public.evaluations enable row level security;
alter table public.feedback    enable row level security;

-- Profiles: a user reads/updates their own row; admins read all.
drop policy if exists profiles_select_own on public.profiles;
create policy profiles_select_own on public.profiles
  for select using (id = auth.uid() or public.is_admin());

drop policy if exists profiles_update_own on public.profiles;
create policy profiles_update_own on public.profiles
  for update using (id = auth.uid());

-- Sessions: owner full access; admins read-only.
drop policy if exists sessions_owner_all on public.sessions;
create policy sessions_owner_all on public.sessions
  for all using (user_id = auth.uid()) with check (user_id = auth.uid());

drop policy if exists sessions_admin_read on public.sessions;
create policy sessions_admin_read on public.sessions
  for select using (public.is_admin());

-- Evaluations: owner reads; admins read. (Writes happen server-side via service key.)
drop policy if exists evaluations_owner_read on public.evaluations;
create policy evaluations_owner_read on public.evaluations
  for select using (user_id = auth.uid() or public.is_admin());

-- Feedback: owner inserts/reads own; admins read all.
drop policy if exists feedback_owner on public.feedback;
create policy feedback_owner on public.feedback
  for all using (user_id = auth.uid()) with check (user_id = auth.uid());

drop policy if exists feedback_admin_read on public.feedback;
create policy feedback_admin_read on public.feedback
  for select using (public.is_admin());

-- NOTE: the FastAPI backend uses the SERVICE ROLE key, which bypasses RLS, to
-- write sessions/evaluations on the user's behalf. The anon key (frontend) is
-- always subject to the policies above.

-- ──────────────────────────────────────────────────────────────────────────
-- 6. Grants — table-level privileges for the API roles.
-- Postgres requires BOTH a table privilege AND (for anon/authenticated) a
-- passing RLS policy. RLS alone is not enough; without these grants every
-- request 403s with "permission denied for table". Supabase normally adds
-- these automatically, but tables created via raw SQL can miss them.
-- ──────────────────────────────────────────────────────────────────────────
grant usage on schema public to anon, authenticated, service_role;

-- service_role bypasses RLS and does all server-side writes — full access.
grant all privileges on all tables    in schema public to service_role;
grant all privileges on all sequences in schema public to service_role;
grant all privileges on all functions in schema public to service_role;

-- Logged-in users act as `authenticated`; RLS still restricts them to own rows.
grant select, insert, update, delete on all tables in schema public to authenticated;
grant usage, select on all sequences in schema public to authenticated;

-- Make the same grants apply to any tables we add later.
alter default privileges in schema public
  grant all privileges on tables to service_role;
alter default privileges in schema public
  grant select, insert, update, delete on tables to authenticated;