schema.sql

-- Gatherline schema. Run this in Supabase SQL Editor (free tier is fine).

-- Content requests: one per client engagement. The share token IS the client's access.
create table public.requests (
  id uuid primary key default gen_random_uuid(),
  owner uuid not null references auth.users(id) on delete cascade,
  client_name text not null,
  client_email text not null,
  project_name text not null,
  token text not null unique default encode(gen_random_bytes(16), 'hex'),
  due_date date,
  reminder_every_days int not null default 3,
  last_reminded_at timestamptz,
  archived_at timestamptz,
  created_at timestamptz not null default now()
);

-- Items the client must provide. kind: 'text' | 'file'
create table public.items (
  id uuid primary key default gen_random_uuid(),
  request_id uuid not null references public.requests(id) on delete cascade,
  label text not null,
  kind text not null default 'text' check (kind in ('text','file')),
  hint text,
  answer_text text,
  file_path text,
  file_paths text[] not null default '{}',
  client_note text,
  completed_at timestamptz,
  position int not null default 0
);

alter table public.requests enable row level security;
alter table public.items enable row level security;

-- Owners manage their own requests/items via authenticated dashboard.
create policy "owner all requests" on public.requests
  for all using (auth.uid() = owner) with check (auth.uid() = owner);

create policy "owner all items" on public.items
  for all using (exists (select 1 from public.requests r where r.id = request_id and r.owner = auth.uid()))
  with check (exists (select 1 from public.requests r where r.id = request_id and r.owner = auth.uid()));

-- Clients never log in. The client page and cron use the service-role key
-- server-side only, scoped by token lookup. No anon policies needed.

-- Storage bucket for client uploads (private; server generates signed URLs).
insert into storage.buckets (id, name, public) values ('uploads', 'uploads', false);

create policy "service role only" on storage.objects
  for all using (false) with check (false);