🏠 Overview › ✅ Implementation Tasks ›✅ epic-organizational-hierarchy-management-core-services-task-013

Author coordinator and peer mentor RLS read/write policies

epic-organizational-hierarchy-management-core-services-task-013 — Write the role-differentiated RLS policies: coordinators may read and write all units within their chapter subtree; peer mentors may only read their own assigned records. Author WITH CHECK policies for INSERT and UPDATE to prevent privilege escalation. Include policies for cross-chapter scenarios (NHF members in up to 5 chapters).

critical priority high complexity database pending database specialist Tier 3

Acceptance Criteria

SELECT policies created for coordinators: USING clause allows reading all rows where organization_unit_id is within the coordinator's chapter subtree (derived from JWT unit_ids claim)
SELECT policies created for peer mentors: USING clause allows reading only rows where peer_mentor_id = auth.uid() or contact_id maps to their own assignments
INSERT policies (WITH CHECK) for coordinators: prevent inserting rows with organization_unit_id outside the coordinator's chapter subtree
UPDATE policies (WITH CHECK) for coordinators: prevent updating rows to an organization_unit_id outside the coordinator's subtree (privilege escalation prevention)
INSERT and UPDATE policies for peer mentors: peer mentors may only create/update activity records where peer_mentor_id = auth.uid()
Peer mentors have no INSERT/UPDATE access to organization_units, unit_assignments, or user_roles tables
NHF cross-chapter scenario: a peer mentor assigned to multiple chapters (up to 5) can read their own assigned records across all chapters; JWT unit_ids array contains all chapter unit IDs
Global admin bypass: all tables have a separate policy USING ((auth.jwt() ->> 'role') = 'global_admin') that grants full read/write without unit restriction
All policies are named with a consistent convention: {table}_{role}_{operation} (e.g., activities_coordinator_select, activities_peer_mentor_insert)
WITH CHECK policies are explicitly separate from USING policies (not combined) for clarity and auditability
A policy audit document lists every policy name, table, role, and the SQL expression, for security review
Running the task-014 test suite against these policies results in zero cross-tenant data leakage findings

Technical Requirements

frameworks
Supabase PostgreSQL 15
Supabase CLI
apis
Supabase PostgreSQL 15 (DDL, auth.jwt())
Supabase Auth (JWT claims from task-012)
data models
activity
assignment
contact
contact_chapter
performance requirements
Coordinator subtree policies must use the pre-computed unit_ids array from JWT claims (not a recursive CTE on every query) to avoid performance degradation
Peer mentor policies must reference indexed columns (peer_mentor_id, auth.uid()) to ensure O(log n) row filtering
WITH CHECK policies should mirror the USING policy logic exactly to avoid asymmetric read/write access windows
security requirements
WITH CHECK policies are mandatory for all INSERT and UPDATE operations — omitting them would allow coordinators to insert records into other organisations by specifying a foreign unit_id
Role claim (auth.jwt() ->> 'role') must be sourced from app_metadata (server-injected in task-012), not from user_metadata which the client can modify
Privilege escalation via user_roles table: peer mentors must have zero INSERT/UPDATE/DELETE access to user_roles — enforced via explicit DENY-style policy absence (no permissive policy = no access)
Cross-organisation data access must be impossible even for coordinators — coordinator unit_ids in JWT are scoped to their own organisation by the hook in task-012
Policies must be reviewed by a second developer before deployment due to security-critical nature

Execution Context

Execution Tier
Tier 3

Tier 3 - 413 tasks

Can start after Tier 2 completes

Implementation Notes

For coordinator subtree policies, use the JWT unit_ids array directly: USING ((auth.jwt() -> 'app_metadata' -> 'unit_ids') @> to_jsonb(organization_unit_id::text)). This avoids recursive tree traversal on each query. The recursive subtree computation was already done at JWT issuance time in task-012's hook — trust the JWT, do not re-compute in policies. For the peer mentor SELECT on activities, the pattern is: USING (peer_mentor_id = auth.uid()).

For contacts, peer mentors should see contacts they are assigned to: USING (EXISTS (SELECT 1 FROM assignments a WHERE a.contact_id = contacts.id AND a.peer_mentor_id = auth.uid() AND a.status != 'inactive')). Ensure all policy files are separate migration files from the base RLS enablement (task-011) so they can be rolled back independently. Use CREATE POLICY ... AS PERMISSIVE (the default) for all allow policies — do not use RESTRICTIVE policies unless you fully understand how they interact with permissive policies in PostgreSQL's RLS evaluation model.

Testing Requirements

SQL integration tests extending the task-014 test suite: (1) Coordinator in chapter-A attempts INSERT into activities with unit_id from chapter-B — assert rejected by WITH CHECK. (2) Coordinator in chapter-A attempts UPDATE to reassign an activity to chapter-B unit — assert rejected. (3) Peer mentor attempts INSERT into user_roles — assert permission denied. (4) Peer mentor with assignments in 3 chapters (NHF scenario) can SELECT their own activities across all 3 chapters.

(5) Peer mentor cannot SELECT another peer mentor's activities even within the same chapter. (6) Global admin can SELECT and INSERT into all tables. (7) All tests run via supabase db test in CI. Additionally, perform a manual policy audit: print all policies via SELECT * FROM pg_policies and compare against the policy audit document.

100% of policies in the audit document must exist in the database and vice versa.

Component
RLS Policy Manager
infrastructure high
Dependencies (1)
Implement the server-side mechanism (Supabase Edge Function or database hook) that injects the authenticated user's organization unit IDs and role into the JWT as custom claims on each session. RLS USING clauses will reference auth.jwt() -> 'unit_ids' to enforce tenant isolation without application-layer filtering. epic-organizational-hierarchy-management-core-services-task-012
Epic Risks (4)
high impact medium prob security

Injecting all unit assignment IDs into JWT claims for users assigned to many units (up to 5 for NHF peer mentors, many more for national coordinators) may exceed JWT size limits, causing authentication failures.

Mitigation & Contingency

Mitigation: Store unit IDs in a Supabase session variable or a dedicated Postgres function rather than embedding them directly in the JWT payload. Use set_config('app.unit_ids', ...) within RLS helper functions querying the assignments table at policy evaluation time.

Contingency: Fall back to querying the unit_assignments table directly within RLS policies using the authenticated user ID, accepting a small per-query overhead in exchange for removing the JWT size constraint.

medium impact medium prob technical

Rendering 1,400+ nodes in a recursive Flutter tree widget may cause jank or memory pressure on lower-end devices used by field peer mentors, degrading the admin experience.

Mitigation & Contingency

Mitigation: Implement lazy tree expansion — only the root level is rendered on initial load. Child nodes are rendered on demand when the parent is expanded. Use const constructors and ListView.builder for all node lists to minimize rebuild scope.

Contingency: Add a search/filter bar that scopes the visible tree to matching nodes, reducing the visible node count. Provide a 'flat list' fallback view for administrators who prefer searching over browsing the tree.

medium impact medium prob scope

Requirements for what constitutes a valid hierarchy structure may expand during NHF sign-off (e.g., mandatory coordinator assignments per chapter, minimum member counts per region), requiring repeated validator redesign.

Mitigation & Contingency

Mitigation: Design the validator as a pluggable rule engine where each check is a discrete, independently testable function. New rules can be added without changing the core validation orchestration. Surface all rules in a configuration table per organization.

Contingency: Defer non-blocking validation rules to warning-level feedback rather than hard blocks, allowing structural changes to proceed while flagging potential issues for admin review.

high impact low prob integration

Deploying RLS policy migrations to a shared Supabase project used by multiple organizations simultaneously could lock tables or interrupt active sessions, causing downtime during production migration.

Mitigation & Contingency

Mitigation: Write all RLS policies as CREATE POLICY IF NOT EXISTS statements. Schedule migrations during off-peak hours. Use Supabase's migration preview environment to validate policies against production data shapes before applying.

Contingency: Prepare rollback migration scripts for every RLS policy. If a migration causes issues, execute the rollback immediately and re-test the policy logic in staging before reattempting.

Quick Links
All Tasks Execution Plan