🏠 Overview › ✅ Implementation Tasks ›✅ epic-peer-mentor-pause-foundation-task-003

Author RLS policies for mentor status fields

epic-peer-mentor-pause-foundation-task-003 — Write and review Supabase Row Level Security policies for the new status, pause_at, and pause_reason columns on peer_mentors and for the peer_mentor_status_history table. Coordinators may read and update status for mentors within their chapter; peer mentors may read their own status but not write it directly. Validate policies cover all CRUD paths.

critical priority medium complexity database pending database specialist Tier 2

Acceptance Criteria

RLS is enabled on both peer_mentors and peer_mentor_status_history tables
Peer mentors may SELECT their own row from peer_mentors (auth.uid() = id filter) including the status, pause_at, and pause_reason columns
Peer mentors may NOT UPDATE the status, pause_at, or pause_reason columns directly — any such UPDATE returns a permission denied error
Coordinators may SELECT rows from peer_mentors for all mentors within their chapter(s) as resolved via the contact_chapter table
Coordinators may UPDATE the status, pause_at, and pause_reason columns for mentors within their chapter — but NOT for mentors in other chapters
Coordinators may INSERT rows into peer_mentor_status_history for mentors within their chapter
Peer mentors may SELECT their own history rows from peer_mentor_status_history (mentor_id = auth.uid())
Peer mentors may NOT INSERT, UPDATE, or DELETE rows in peer_mentor_status_history
The service-role client bypasses all RLS policies — CertificationExpiryJob operations are unaffected
Organisation admins may read and update status for all mentors within their organisation
All policies are documented with inline SQL comments explaining the security intent
A policy test script validates all CRUD paths for each role using SET ROLE or SET LOCAL jwt.claims

Technical Requirements

frameworks
Supabase PostgreSQL 15
apis
Supabase Auth
Supabase PostgreSQL 15 RLS engine
data models
assignment
contact
certification
performance requirements
RLS policy subqueries (e.g. exists-in-chapter checks) must use indexed columns — coordinator chapter membership lookup must hit the (contact_id, organization_unit_id) index
Avoid correlated subqueries that execute per-row; use EXISTS with indexed joins instead
security requirements
All policies must be tested against the anon role — anon users must receive zero rows from both tables
The pause_reason column must not be readable by the peer mentor themselves to prevent leaking internal coordinator notes — restrict to coordinator and admin SELECT only
JWT claims used in policy expressions (e.g. auth.jwt() ->> 'role') must be validated to match expected enum values before use in comparisons
Policies must be reviewed by a second developer before merging — add a SECURITY REVIEW comment block at the top of the migration file
GDPR: pause_reason may contain sensitive operational data — RLS enforces data minimisation by restricting visibility to authorised roles only

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Implementation Notes

Use Supabase's built-in auth.uid() and auth.jwt() functions in policy expressions rather than custom functions to ensure compatibility with Supabase Auth token format. The coordinator chapter membership check should be written as: EXISTS (SELECT 1 FROM contact_chapter cc WHERE cc.contact_id = auth.uid() AND cc.organization_unit_id = peer_mentors.chapter_id). If the peer_mentors table does not have a direct chapter_id, resolve it through the assignments table. For the pause_reason visibility restriction, use a column-level security approach: create a separate restrictive policy on the column if PostgreSQL column-level security is available, or use a security-definer view that excludes pause_reason for the mentor role.

Document the chosen approach clearly since column-level RLS in PostgreSQL requires GRANT/REVOKE on individual columns which interacts with PostgREST's schema reflection.

Testing Requirements

Write a SQL-based policy test script (compatible with pgTAP or manual psql execution) that: (1) simulates a peer mentor JWT and asserts they can SELECT their own status but cannot UPDATE it, (2) simulates a coordinator JWT and asserts they can UPDATE status for an in-chapter mentor but receive 0 rows updated for an out-of-chapter mentor, (3) simulates an anon JWT and asserts zero rows returned from both tables, (4) simulates a service-role client and asserts all operations succeed. Also write a Dart integration test using the Supabase Flutter SDK that attempts an unauthorised status UPDATE from a mentor-role client and asserts it throws a PostgrestException with the expected error code.

Component
Mentor Status Repository
data low
Dependencies (1)
Create Supabase migration for the peer_mentor_status_history table capturing mentor_id, previous_status, new_status, changed_by, changed_at, and reason columns. Add foreign key constraints to peer_mentors and users tables. Create index on mentor_id and changed_at for efficient history queries. epic-peer-mentor-pause-foundation-task-002
Epic Risks (3)
high impact medium prob security

Supabase RLS policies for status reads and writes must correctly distinguish between a mentor editing their own status and a coordinator editing another mentor's status within the same chapter. Incorrect policies could allow cross-chapter data leakage or silently block legitimate status updates, causing hard-to-diagnose runtime failures.

Mitigation & Contingency

Mitigation: Write RLS policies with explicit role checks (auth.uid() = mentor_id OR chapter_coordinator_check()) and verify with integration tests that cover same-chapter coordinator access, cross-chapter denial, and self-access. Review policies with a second developer before merging.

Contingency: If policy errors surface after merge, temporarily widen policy to coordinator role globally while a targeted fix is authored; use Supabase audit logs to trace any unauthorised access during the interim.

medium impact medium prob integration

CoordinatorNotificationService must correctly resolve which coordinator(s) are responsible for a given mentor's chapter. If the chapter-coordinator mapping is incomplete or a mentor belongs to multiple chapters (as with NHF multi-chapter memberships), the service could fail to notify or duplicate notifications to the wrong coordinators.

Mitigation & Contingency

Mitigation: Use the existing chapter membership data model and query all active coordinator roles for each of the mentor's chapters. Add a de-duplication step before dispatch. Write integration tests with fixtures covering single-chapter, multi-chapter, and no-coordinator edge cases.

Contingency: If resolution logic proves too complex at this stage, fall back to notifying all coordinators in the organisation until a proper chapter-scoped resolver can be delivered in a follow-up task.

high impact low prob technical

Adding new columns to peer_mentors in production could conflict with existing application code that does SELECT * queries if new non-nullable columns without defaults are introduced, causing unexpected failures in unrelated screens.

Mitigation & Contingency

Mitigation: Make all new columns nullable or provide safe defaults. Use additive migration strategy with no column renames or drops. Run migration against a staging copy of production data before applying to live.

Contingency: Prepare a rollback migration script that drops only the new columns; coordinate with the team to deploy the rollback and hotfix immediately if production issues are detected.

Quick Links
All Tasks Execution Plan