Data Model and Migrations

supabase migration new create_core_schema
# writes supabase/migrations/<timestamp>_create_core_schema.sql
# edit the file, then apply to your local stack:
supabase db reset   # replays every migration + seed from scratch
# a clean reset is itself a test: if it fails, a migration is wrong

-- Enums
create type member_role as enum ('owner', 'admin', 'member', 'client');
create type project_status as enum ('active', 'archived');
create type deliverable_status as enum
('draft', 'in_review', 'approved', 'changes_requested');
create type approval_decision as enum ('approved', 'changes_requested');

-- Orgs (tenants)
create table orgs (
id uuid primary key default gen_random_uuid(),
name text not null,
slug text unique not null,
created_at timestamptz not null default now()
);

-- Membership: which user is what, in which org
create table org_members (
org_id uuid not null references orgs (id) on delete cascade,
user_id uuid not null references auth.users (id) on delete cascade,
role member_role not null,
created_at timestamptz not null default now(),
primary key (org_id, user_id)
);

create table projects (
id uuid primary key default gen_random_uuid(),
org_id uuid not null references orgs (id) on delete cascade,
name text not null,
status project_status not null default 'active',
created_at timestamptz not null default now()
);

-- Client visibility grants (staff need no rows here)
create table project_access (
project_id uuid not null references projects (id) on delete cascade,
user_id uuid not null references auth.users (id) on delete cascade,
created_at timestamptz not null default now(),
primary key (project_id, user_id)
);

create table deliverables (
id uuid primary key default gen_random_uuid(),
org_id uuid not null references orgs (id) on delete cascade,
project_id uuid not null references projects (id) on delete cascade,
title text not null,
file_path text,
version integer not null default 1,
status deliverable_status not null default 'draft',
created_by uuid not null references auth.users (id),
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);

create table comments (
id uuid primary key default gen_random_uuid(),
org_id uuid not null references orgs (id) on delete cascade,
deliverable_id uuid not null
  references deliverables (id) on delete cascade,
author_id uuid not null references auth.users (id),
body text not null,
created_at timestamptz not null default now()
);

-- Approvals are append-only: no update, no delete policy exists.
create table approvals (
id uuid primary key default gen_random_uuid(),
org_id uuid not null references orgs (id) on delete cascade,
deliverable_id uuid not null
  references deliverables (id) on delete cascade,
decided_by uuid not null references auth.users (id),
decision approval_decision not null,
note text,
created_at timestamptz not null default now()
);

-- Helpful indexes for the tenancy filters (org_id is hit constantly)
create index on projects (org_id);
create index on deliverables (org_id, project_id);
create index on comments (org_id, deliverable_id);
create index on approvals (org_id, deliverable_id);
create index on project_access (user_id);

create schema if not exists private;

-- Is the current user staff (owner/admin/member) of this org?
create function private.is_org_staff(target_org uuid)
returns boolean
language sql
security definer
set search_path = ''
stable
as $$
select exists (
  select 1
  from public.org_members m
  where m.org_id = target_org
    and m.user_id = (select auth.uid())
    and m.role in ('owner', 'admin', 'member')
);
$$;

-- Is the current user an owner/admin (billing + member management)?
create function private.is_org_admin(target_org uuid)
returns boolean
language sql
security definer
set search_path = ''
stable
as $$
select exists (
  select 1
  from public.org_members m
  where m.org_id = target_org
    and m.user_id = (select auth.uid())
    and m.role in ('owner', 'admin')
);
$$;

-- Can the current user see this project? Staff see all org projects;
-- clients see only projects granted via project_access.
create function private.can_see_project(target_project uuid)
returns boolean
language sql
security definer
set search_path = ''
stable
as $$
select exists (
  select 1
  from public.projects p
  where p.id = target_project
    and (
      private.is_org_staff(p.org_id)
      or exists (
        select 1
        from public.project_access pa
        where pa.project_id = p.id
          and pa.user_id = (select auth.uid())
      )
    )
);
$$;

alter table orgs enable row level security;
alter table org_members enable row level security;
alter table projects enable row level security;
alter table project_access enable row level security;
alter table deliverables enable row level security;
alter table comments enable row level security;
alter table approvals enable row level security;

-- ORGS: any member reads their org; only admins rename it.
create policy orgs_select on orgs
for select to authenticated
using (private.is_org_staff(id)
  or exists (select 1 from org_members m
    where m.org_id = orgs.id and m.user_id = (select auth.uid())));
create policy orgs_update on orgs
for update to authenticated
using (private.is_org_admin(id))
with check (private.is_org_admin(id));

-- ORG_MEMBERS: you can read rows of orgs you belong to; admins write.
create policy members_select on org_members
for select to authenticated
using (exists (select 1 from org_members me
  where me.org_id = org_members.org_id
    and me.user_id = (select auth.uid())));
create policy members_write on org_members
for all to authenticated
using (private.is_org_admin(org_id))
with check (private.is_org_admin(org_id));

-- PROJECTS: staff see all org projects; clients see granted ones.
create policy projects_select on projects
for select to authenticated
using (private.can_see_project(id));
create policy projects_write on projects
for all to authenticated
using (private.is_org_staff(org_id))
with check (private.is_org_staff(org_id));

-- PROJECT_ACCESS: only staff manage grants; a client may read own grants.
create policy access_select on project_access
for select to authenticated
using (user_id = (select auth.uid())
  or exists (select 1 from projects p
    where p.id = project_access.project_id
      and private.is_org_staff(p.org_id)));
create policy access_write on project_access
for all to authenticated
using (exists (select 1 from projects p
  where p.id = project_access.project_id
    and private.is_org_staff(p.org_id)))
with check (exists (select 1 from projects p
  where p.id = project_access.project_id
    and private.is_org_staff(p.org_id)));

-- DELIVERABLES: visible if you can see the project.
-- Staff insert/update; nobody updates across the project boundary.
create policy deliverables_select on deliverables
for select to authenticated
using (private.can_see_project(project_id));
create policy deliverables_insert on deliverables
for insert to authenticated
with check (private.is_org_staff(org_id));
create policy deliverables_update on deliverables
for update to authenticated
using (private.can_see_project(project_id))
with check (private.can_see_project(project_id));

-- COMMENTS: anyone who can see the deliverable can read and add;
-- the author must be the current user.
create policy comments_select on comments
for select to authenticated
using (exists (select 1 from deliverables d
  where d.id = comments.deliverable_id
    and private.can_see_project(d.project_id)));
create policy comments_insert on comments
for insert to authenticated
with check (author_id = (select auth.uid())
  and exists (select 1 from deliverables d
    where d.id = comments.deliverable_id
      and private.can_see_project(d.project_id)));

-- APPROVALS: readable by anyone who can see the deliverable;
-- insert only, decided_by must be the current user. No update/delete
-- policy exists, so the audit trail is append-only under RLS.
create policy approvals_select on approvals
for select to authenticated
using (exists (select 1 from deliverables d
  where d.id = approvals.deliverable_id
    and private.can_see_project(d.project_id)));
create policy approvals_insert on approvals
for insert to authenticated
with check (decided_by = (select auth.uid())
  and exists (select 1 from deliverables d
    where d.id = approvals.deliverable_id
      and private.can_see_project(d.project_id)));

begin;
select plan(8);

-- Test helpers (installed per the Supabase advanced pgTAP guide).
create extension if not exists "basejump-supabase_test_helpers"
version '0.0.6';

-- Two agencies, two users each.
select tests.create_supabase_user('a_owner');
select tests.create_supabase_user('a_client');
select tests.create_supabase_user('b_owner');

-- Seed as the service role (bypasses RLS) so setup is not the test.
insert into orgs (id, name, slug) values
('00000000-0000-0000-0000-00000000000a', 'Agency A', 'a'),
('00000000-0000-0000-0000-00000000000b', 'Agency B', 'b');
insert into org_members (org_id, user_id, role) values
('00000000-0000-0000-0000-00000000000a',
  tests.get_supabase_uid('a_owner'), 'owner'),
('00000000-0000-0000-0000-00000000000b',
  tests.get_supabase_uid('b_owner'), 'owner');
insert into projects (id, org_id, name) values
('00000000-0000-0000-0000-0000000000a1',
  '00000000-0000-0000-0000-00000000000a', 'A project');
insert into deliverables (id, org_id, project_id, title, created_by)
values ('00000000-0000-0000-0000-0000000000d1',
  '00000000-0000-0000-0000-00000000000a',
  '00000000-0000-0000-0000-0000000000a1', 'A deliverable',
  tests.get_supabase_uid('a_owner'));

-- 1. A's owner sees A's one project.
select tests.authenticate_as('a_owner');
select is(
(select count(*)::int from projects),
1, 'A owner sees exactly A''s projects');

-- 2. THE LEAK TEST: B's owner sees zero of A's projects.
select tests.authenticate_as('b_owner');
select is(
(select count(*)::int from projects),
0, 'B owner sees none of A''s projects (cross-tenant read denied)');

-- 3. B's owner sees zero of A's deliverables.
select is(
(select count(*)::int from deliverables),
0, 'B owner sees none of A''s deliverables');

-- 4. B's owner cannot insert into A's org.
select throws_ok($$
insert into projects (org_id, name) values
  ('00000000-0000-0000-0000-00000000000a', 'B injected')
$$, '42501', null,
'B owner cannot write into A''s org (RLS insert denied)');

-- 5. A un-granted client of A sees zero projects (client visibility).
select tests.create_supabase_user('a_client2');
insert into org_members (org_id, user_id, role) values
('00000000-0000-0000-0000-00000000000a',
  tests.get_supabase_uid('a_client2'), 'client');
select tests.authenticate_as('a_client2');
select is(
(select count(*)::int from projects),
0, 'un-granted client sees no projects even in own org');

-- 6. Grant access, and the same client now sees exactly that project.
select tests.authenticate_as_service_role();
insert into project_access (project_id, user_id) values
('00000000-0000-0000-0000-0000000000a1',
  tests.get_supabase_uid('a_client2'));
select tests.authenticate_as('a_client2');
select is(
(select count(*)::int from projects),
1, 'granted client sees exactly the granted project');

-- 7. Approvals are append-only: no update policy, so update is denied.
select tests.authenticate_as('a_owner');
insert into approvals (org_id, deliverable_id, decided_by, decision)
values ('00000000-0000-0000-0000-00000000000a',
  '00000000-0000-0000-0000-0000000000d1',
  tests.get_supabase_uid('a_owner'), 'approved');
select is(
(select count(*)::int from approvals),
1, 'owner can record an approval');

-- 8. Even the owner cannot mutate the audit record.
select throws_ok($$
update approvals set decision = 'changes_requested'
$$, '42501', null,
'no update policy: approvals are immutable under RLS');

select * from finish();
rollback;