supabase/schema.sql

-- Run in Supabase SQL Editor after creating a project.
-- If you see PGRST205 (table not in schema cache), create the table first — see INSTALL_SIMULATION_RUNS.sql
-- 1) Create Storage bucket named "reports" (private).
-- 2) Apply this script (or run INSTALL_SIMULATION_RUNS.sql for table+RLS only, then storage section below).

-- user_id is TEXT so it can hold auth.users UUID strings OR public."User" bigint ids as strings (e.g. "5").
create table if not exists public.simulation_runs (
  id uuid primary key,
  user_id text not null,
  backend_task_id text,
  report_id text,
  status text not null default 'pending',
  requirement_preview text,
  report_storage_path text,
  report_public_url text,
  simulation_id text,
  project_id text,
  error_message text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists simulation_runs_user_id_idx on public.simulation_runs (user_id);
create index if not exists simulation_runs_status_idx on public.simulation_runs (status);

alter table public.simulation_runs enable row level security;

drop policy if exists "simulation_runs_select_own" on public.simulation_runs;
-- JWT users (Supabase Auth) can read their own rows when user_id matches auth.uid() as text.
create policy "simulation_runs_select_own"
  on public.simulation_runs for select
  to authenticated
  using (auth.uid()::text = user_id);

-- Custom public."User" accounts use the backend API + service role; RLS still applies to anon.

-- Storage: allow signed URL downloads for files under the user's folder (JWT uid as first path segment).
drop policy if exists "reports_select_own" on storage.objects;
create policy "reports_select_own"
  on storage.objects for select
  to authenticated
  using (
    bucket_id = 'reports'
    and (storage.foldername(name))[1] = auth.uid()::text
  );

-- Optional: custom app table (manage in Table Editor). Example:
-- create table public."User" (
--   id bigint generated by default as identity primary key,
--   created_at timestamptz not null default now(),
--   "Username" text not null,
--   "Email" text not null unique,
--   "Password" text not null
-- );
-- Grant anon select/insert on "User" only if you accept client-side checks (prefer Edge Functions + hashing for production).