OG/db/policies.sql

-- RLS Policies — all 12 tables
-- Source: docs/04-database-schema.md section 3 (role matrix below), extended
-- consistently to 4 tables the matrix doesn't cover (orgs, departments,
-- sop_assignments, ai_log) — marked "(not in matrix)" at each one.
--
-- Coverage table (docs/04-database-schema.md section 3):
--
-- | Table             | staff                          | editor            | approver           | admin |
-- |--------------------|--------------------------------|--------------------|---------------------|-------|
-- | sops (read)        | published + assigned dept only | all in org         | all in org          | all   |
-- | sops (write)       | -                               | insert/update drafts | -                | all   |
-- | sop_versions       | read if assigned                | read               | read                | all   |
-- | acknowledgements   | insert own; read own            | read               | read                | read  |
-- | profiles           | read own + names in org         | read org           | read org            | all   |
-- | audit_log          | -                                | -                  | read                | read  |
-- | approvals          | -                                | read               | insert/read         | all   |
-- | translations       | read                             | read/write         | read                | all   |
-- | incidents          | insert own; read own             | read org           | read/update org     | all   |
--
-- Workflow mutations (publish, approve, assign) run via the service-role
-- client inside /api routes so drafts can be snapshotted and audit rows
-- written in one transaction; RLS remains the safety net for direct reads.
-- Default pattern in this file: RLS grants READ access per role; WRITE
-- access via RLS is only granted where the matrix explicitly says so
-- (insert own, read/write, etc). Everything else is mutated exclusively
-- by service-role /api routes, which bypass RLS entirely.
--
-- All checks below read role, org_id, and department_id from JWT claims
-- (auth.jwt() ->> '...') set by db/auth-hook.sql. No policy in this file
-- ever queries the profiles table — the sops/sop_versions staff-scoping
-- checks use the department_id claim directly instead of joining
-- profiles, avoiding any cross-table RLS dependency.

-- ============================================================
-- orgs (not in matrix — read own org only, no client write)
-- ============================================================
alter table orgs enable row level security;

drop policy if exists orgs_read on orgs;
create policy orgs_read on orgs
  for select using (
    id = (auth.jwt() ->> 'org_id')::uuid
  );
-- no insert/update/delete policy: single org, managed manually in Stage 1.

-- ============================================================
-- departments (not in matrix — read all in org, admin writes, FR-1.3)
-- ============================================================
alter table departments enable row level security;

drop policy if exists departments_read on departments;
create policy departments_read on departments
  for select using (
    org_id = (auth.jwt() ->> 'org_id')::uuid
  );

drop policy if exists departments_insert_admin on departments;
create policy departments_insert_admin on departments
  for insert with check (
    org_id = (auth.jwt() ->> 'org_id')::uuid
    and (auth.jwt() ->> 'role') = 'admin'
  );

drop policy if exists departments_update_admin on departments;
create policy departments_update_admin on departments
  for update using (
    org_id = (auth.jwt() ->> 'org_id')::uuid
    and (auth.jwt() ->> 'role') = 'admin'
  );

drop policy if exists departments_delete_admin on departments;
create policy departments_delete_admin on departments
  for delete using (
    org_id = (auth.jwt() ->> 'org_id')::uuid
    and (auth.jwt() ->> 'role') = 'admin'
  );
-- "delete blocked if department has users" (FR-1.3) is enforced by the
-- profiles.department_id foreign key, not by RLS.

-- ============================================================
-- profiles — own row always; org-wide read (table has no sensitive
-- fields beyond role/department, so org-wide read is a low-risk
-- simplification); admin writes via RLS as a safety net (actual
-- invites/role-changes go through the service-role /api/users route).
-- ============================================================
alter table profiles enable row level security;

-- CRITICAL: the JWT claims hook (db/auth-hook.sql) reads role/org_id/
-- department_id from this table on every login/token refresh, running
-- as supabase_auth_admin. Enabling RLS above blocks that read unless
-- this policy exists — without it, the hook silently returns null
-- claims and every other policy in this file fails closed. This grant
-- + policy is idempotent (grant is safe to re-run; the policy already
-- exists from db/auth-hook.sql, not recreated here to avoid a
-- duplicate-policy error).
grant select
  on table public.profiles
  to supabase_auth_admin;

drop policy if exists profiles_read on profiles;
create policy profiles_read on profiles
  for select using (
    org_id = (auth.jwt() ->> 'org_id')::uuid
  );

drop policy if exists profiles_update_admin on profiles;
create policy profiles_update_admin on profiles
  for update using (
    org_id = (auth.jwt() ->> 'org_id')::uuid
    and (auth.jwt() ->> 'role') = 'admin'
  );
-- no insert policy: profile rows are created by the signup flow
-- (service-role), not inserted directly by a client.

-- ============================================================
-- sops
-- ============================================================
alter table sops enable row level security;

drop policy if exists sops_read on sops;
create policy sops_read on sops
  for select using (
    org_id = (auth.jwt() ->> 'org_id')::uuid
    and (
      (auth.jwt() ->> 'role') in ('admin','approver','editor')
      or (
        (auth.jwt() ->> 'role') = 'staff'
        and status = 'published'
        and exists (
          select 1
          from sop_assignments sa
          where sa.sop_id = sops.id
            and sa.department_id = (auth.jwt() ->> 'department_id')::uuid
        )
      )
    )
  );

drop policy if exists sops_insert_editor on sops;
create policy sops_insert_editor on sops
  for insert with check (
    org_id = (auth.jwt() ->> 'org_id')::uuid
    and (auth.jwt() ->> 'role') in ('editor','admin')
  );

drop policy if exists sops_update_editor on sops;
create policy sops_update_editor on sops
  for update using (
    org_id = (auth.jwt() ->> 'org_id')::uuid
    and (
      (auth.jwt() ->> 'role') = 'admin'
      or ( (auth.jwt() ->> 'role') = 'editor' and status = 'draft' )
    )
  );

-- ============================================================
-- sop_versions — read only via RLS; every version is written by the
-- service-role publish route (AD-4), never directly, not even by admin.
-- ============================================================
alter table sop_versions enable row level security;

drop policy if exists sop_versions_read on sop_versions;
create policy sop_versions_read on sop_versions
  for select using (
    org_id = (auth.jwt() ->> 'org_id')::uuid
    and (
      (auth.jwt() ->> 'role') in ('admin','approver','editor')
      or (
        (auth.jwt() ->> 'role') = 'staff'
        and exists (
          select 1
          from sop_assignments sa
          where sa.sop_id = sop_versions.sop_id
            and sa.department_id = (auth.jwt() ->> 'department_id')::uuid
        )
      )
    )
  );

-- ============================================================
-- approvals — a decision, once made, is never editable or deletable.
-- ============================================================
alter table approvals enable row level security;

drop policy if exists approvals_read on approvals;
create policy approvals_read on approvals
  for select using (
    org_id = (auth.jwt() ->> 'org_id')::uuid
    and (auth.jwt() ->> 'role') in ('editor','approver','admin')
  );

drop policy if exists approvals_insert on approvals;
create policy approvals_insert on approvals
  for insert with check (
    org_id = (auth.jwt() ->> 'org_id')::uuid
    and (auth.jwt() ->> 'role') in ('approver','admin')
    and decided_by = auth.uid()
  );

-- ============================================================
-- sop_assignments (not in matrix — read all in org, no client write;
-- set only via the service-role /api/sops/:id/assign route)
-- ============================================================
alter table sop_assignments enable row level security;

drop policy if exists sop_assignments_read on sop_assignments;
create policy sop_assignments_read on sop_assignments
  for select using (
    org_id = (auth.jwt() ->> 'org_id')::uuid
  );

-- ============================================================
-- acknowledgements — immutable once written (FR-4.3): insert only,
-- never update or delete.
-- ============================================================
alter table acknowledgements enable row level security;

drop policy if exists ack_insert_own on acknowledgements;
create policy ack_insert_own on acknowledgements
  for insert with check (
    user_id = auth.uid()
    and org_id = (auth.jwt() ->> 'org_id')::uuid
  );

drop policy if exists ack_read on acknowledgements;
create policy ack_read on acknowledgements
  for select using (
    org_id = (auth.jwt() ->> 'org_id')::uuid
    and ( user_id = auth.uid()
          or (auth.jwt() ->> 'role') in ('admin','approver','editor') )
  );

-- ============================================================
-- sop_translations
-- ============================================================
alter table sop_translations enable row level security;

drop policy if exists translations_read on sop_translations;
create policy translations_read on sop_translations
  for select using (
    org_id = (auth.jwt() ->> 'org_id')::uuid
  );

drop policy if exists translations_insert_editor on sop_translations;
create policy translations_insert_editor on sop_translations
  for insert with check (
    org_id = (auth.jwt() ->> 'org_id')::uuid
    and (auth.jwt() ->> 'role') in ('editor','admin')
  );

drop policy if exists translations_update_editor on sop_translations;
create policy translations_update_editor on sop_translations
  for update using (
    org_id = (auth.jwt() ->> 'org_id')::uuid
    and (auth.jwt() ->> 'role') in ('editor','admin')
  );

-- ============================================================
-- audit_log — append-only. INSERT + SELECT policies only.
-- No UPDATE policy. No DELETE policy. Ever. (FR-3.4)
-- ============================================================
alter table audit_log enable row level security;

drop policy if exists audit_read on audit_log;
create policy audit_read on audit_log
  for select using (
    org_id = (auth.jwt() ->> 'org_id')::uuid
    and (auth.jwt() ->> 'role') in ('approver','admin')
  );

drop policy if exists audit_insert on audit_log;
create policy audit_insert on audit_log
  for insert with check (
    org_id = (auth.jwt() ->> 'org_id')::uuid
  );

-- ============================================================
-- ai_log (not in matrix — admin-only read for cost tracking, no FRD
-- screen for it; no client write, only the service-role AI routes write)
-- ============================================================
alter table ai_log enable row level security;

drop policy if exists ai_log_read_admin on ai_log;
create policy ai_log_read_admin on ai_log
  for select using (
    org_id = (auth.jwt() ->> 'org_id')::uuid
    and (auth.jwt() ->> 'role') = 'admin'
  );

-- ============================================================
-- incidents — FR-7.1 says "staff+" (any role) can report, so insert-own
-- is granted to every role, not just staff.
-- ============================================================
alter table incidents enable row level security;

drop policy if exists incidents_insert_own on incidents;
create policy incidents_insert_own on incidents
  for insert with check (
    reporter_id = auth.uid()
    and org_id = (auth.jwt() ->> 'org_id')::uuid
  );

drop policy if exists incidents_read on incidents;
create policy incidents_read on incidents
  for select using (
    org_id = (auth.jwt() ->> 'org_id')::uuid
    and ( reporter_id = auth.uid()
          or (auth.jwt() ->> 'role') in ('editor','approver','admin') )
  );

drop policy if exists incidents_update on incidents;
create policy incidents_update on incidents
  for update using (
    org_id = (auth.jwt() ->> 'org_id')::uuid
    and (auth.jwt() ->> 'role') in ('approver','admin')
  );