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

Apply RLS policies and RPC functions for pause status

epic-peer-mentor-pause-management-foundation-task-005 — Define RLS policies on peer_mentor_status and peer_mentor_status_log tables. Implement RPC functions for atomic status transitions (activate_pause, deactivate_pause, get_active_pauses_for_chapter) to ensure safe concurrent updates and audit logging.

critical priority medium complexity database pending database specialist Tier 1

Acceptance Criteria

RLS policy on peer_mentor_status ensures a peer mentor can read only their own status row; coordinators can read all statuses within their organization_id; org admins can read all statuses across the organization.
RLS policy on peer_mentor_status_log ensures only the owning organization's users can read log entries; no direct INSERT/UPDATE/DELETE is permitted from the client — all mutations go through RPC functions.
RPC function activate_pause(peer_mentor_id uuid, reason text, expected_return_date date) atomically sets status to 'paused', inserts a log entry with actor_id from JWT claims, and returns the new status row. Concurrent calls for the same peer_mentor_id are serialized via FOR UPDATE lock.
RPC function deactivate_pause(peer_mentor_id uuid) atomically sets status to 'active', inserts a log entry, and returns the updated row.
RPC function get_active_pauses_for_chapter(organization_unit_id uuid) returns all peer mentors with active pauses in that chapter, respecting the caller's organization_id from JWT claims.
All RPC functions validate that the calling user's organization_id (from JWT claims) matches the target peer mentor's organization_id; mismatches raise a Postgres exception with code 'P0001'.
Audit log entry is inserted within the same transaction as the status update — no partial writes are possible.
RLS policies are covered by at least one pgTAP or Supabase test per policy rule (peer mentor self-read, coordinator read, cross-org rejection).
Migration script is idempotent (CREATE POLICY IF NOT EXISTS equivalent pattern) and rolls back cleanly if any statement fails.

Technical Requirements

frameworks
Supabase PostgreSQL 15
pgTAP (for RLS testing)
apis
Supabase Management API (migration deployment)
Supabase Auth JWT claims
data models
assignment
contact
performance requirements
RPC functions must complete within 100ms under normal load (single-row updates with index access).
get_active_pauses_for_chapter must use an index on (organization_unit_id, status) — ensure index exists in migration.
security requirements
JWT claims (sub, organization_id, role) must be verified inside each RPC via auth.jwt() — never trust client-supplied IDs for ownership checks.
Service role key must never be used client-side; RPC functions are invoked with the authenticated user's JWT only.
Row-Level Security must be enabled (ALTER TABLE ... ENABLE ROW LEVEL SECURITY) before policies are applied.
All RPC functions use SECURITY DEFINER only if strictly necessary; prefer SECURITY INVOKER where RLS covers access.
No PII is returned beyond what the calling role is permitted to see per RLS policy.

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

Use a single migration file (e.g., 20260330_pause_rls_and_rpcs.sql). Define RLS policies before granting EXECUTE on RPC functions to avoid a window where functions are callable without policies. For the atomic status transition, use a CTE pattern: WITH updated AS (UPDATE peer_mentor_status SET status='paused', ... WHERE id=...

RETURNING *) INSERT INTO peer_mentor_status_log SELECT ... FROM updated — this guarantees log insertion only if the update succeeds. Use pg_advisory_xact_lock(hashtext(peer_mentor_id::text)) inside activate_pause/deactivate_pause to serialize concurrent calls without table-level locks. Extract organization_id from (auth.jwt()->'user_metadata'->>'organization_id') consistently — document which claim key is used so the Dart repository layer matches.

Avoid SECURITY DEFINER unless cross-schema access is needed; SECURITY INVOKER with RLS is safer and auditable.

Testing Requirements

Write pgTAP tests covering: (1) peer mentor can SELECT their own status row, (2) peer mentor cannot SELECT another mentor's status, (3) coordinator can SELECT all statuses in their org, (4) cross-org SELECT is rejected, (5) activate_pause succeeds and inserts log entry atomically, (6) deactivate_pause succeeds and inserts log entry, (7) concurrent activate_pause calls for the same mentor serialize correctly, (8) mismatched organization_id raises exception. Integration tests in Dart using a Supabase test project should verify that calling activate_pause via supabase.rpc() with a valid JWT returns the expected status row and that an unauthorized JWT returns a Postgres error. Aim for 100% branch coverage on all RPC conditional paths.

Component
Peer Mentor Status Repository
data medium
Dependencies (1)
Create the peer_mentor_status and peer_mentor_status_log Supabase database tables. Define columns for pause state, reason, expected return date, activated_by, timestamps, and chapter association. Apply indexes on mentor_id and status fields for query performance. epic-peer-mentor-pause-management-foundation-task-004
Epic Risks (3)
high impact medium prob security

Supabase RLS policies for coordinator-scoped status queries may be difficult to express correctly, especially for peer mentors assigned to multiple coordinators or chapters, leading to data leakage or overly restrictive access blocking valid queries.

Mitigation & Contingency

Mitigation: Design RLS policies using security-definer RPCs rather than table-level policies for complex multi-coordinator scenarios. Write a comprehensive RLS test matrix covering all role and assignment permutations before marking complete.

Contingency: Fall back to application-level filtering in the repository layer with explicit coordinator_id parameter checks if RLS proves intractable, and document the trade-off for security review.

high impact medium prob dependency

The HLF Dynamics portal API contract may be undocumented or subject to change, causing the DynamicsPortalClient to break during development or production rollout.

Mitigation & Contingency

Mitigation: Obtain the full Dynamics portal API specification and credentials early in the sprint. Build the client behind a well-defined interface so the HLF-specific implementation can be swapped without affecting upstream services.

Contingency: If the Dynamics API is unavailable or unstable, stub the client with a feature-flag-guarded no-op implementation so all other epics can proceed to completion independently.

medium impact low prob technical

Supabase Edge Functions used as the nightly scheduler host may have cold-start latency or execution time limits that prevent reliable nightly certification checks on large mentor rosters.

Mitigation & Contingency

Mitigation: Benchmark Edge Function execution time against the expected roster size. Design the expiry check to process in paginated batches to stay within execution limits. Use pg_cron with a direct database function as an alternative trigger if Edge Functions prove unreliable.

Contingency: Migrate the scheduler trigger to pg_cron invoking a Postgres function directly, removing the Edge Function dependency entirely for the scheduling layer.

Quick Links
All Tasks Execution Plan