Supabase RLS Policy Configuration

This component carries high implementation complexity because RLS policies must be designed, tested, and validated for every combination of role and table before any other admin feature can ship safely. It has no upstream dependencies, but virtually every admin-facing feature depends on it being correct, making it a critical-path item that must be completed early in the delivery schedule. QA effort is substantial: testers must verify policy coverage for each table and role pairing, including edge cases around org subtree boundaries and super_admin bypass. Any gap discovered late in the cycle requires a coordinated database migration, regression testing of all affected endpoints, and potential hotfix deployment.

Treat this as a foundational sprint-one deliverable and allocate dedicated security-review time before sign-off.

This infrastructure component encapsulates all Supabase Row-Level Security SQL for the org_admin and super_admin roles, targeting the users, activities, reimbursements, and organisations tables. The applyOrgAdminPolicies(supabase) function generates and executes USING clauses that walk the org hierarchy to restrict SELECT and UPDATE to rows whose organisation falls within the caller's subtree, typically via a recursive CTE or array-contains check on an org_path column. applySuperAdminPolicies(supabase) installs permissive bypass policies checked after the org_admin policies. validatePolicies() introspects pg_policies to confirm all expected policy names exist and are enabled, providing a health-check suitable for CI.

getPolicySql() returns the raw DDL for audit or migration tooling. Because Supabase enforces RLS at the Postgres session level, these policies apply to all clients including direct SDK calls and PostgREST, making them the authoritative enforcement boundary regardless of application-layer logic.