Implement RLS policies for certification status table
epic-peer-mentor-pause-management-foundation-task-002 — Define and apply Row Level Security policies on the cert_expiry_reminders table to enforce coordinator and admin access patterns. Ensure peer mentors can only read their own certification records, while coordinators can read records within their chapter scope.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 1 - 540 tasks
Can start after Tier 0 completes
Implementation Notes
The coordinator chapter membership check is the most complex policy. Use a pattern like: EXISTS (SELECT 1 FROM coordinator_chapters cc WHERE cc.coordinator_id = auth.uid() AND cc.chapter_id = cert_expiry_reminders.chapter_id). This requires the coordinator_chapters table to exist and be indexed on coordinator_id — confirm this dependency is met or add it. For role detection from JWT claims, Supabase custom claims are typically stored under auth.jwt() -> 'app_metadata' -> 'role'.
Agree on the exact claim path with the auth setup team before implementing. Use SECURITY DEFINER on helper functions if needed for the chapter membership check, but prefer direct subqueries to avoid function overhead on every row evaluation.
Testing Requirements
Test RLS policies using the Supabase SQL editor with SET ROLE and SET LOCAL request.jwt.claims. Create three test scenarios: (1) authenticate as a peer mentor JWT and confirm SELECT returns only their own row and zero rows for another mentor's id; (2) authenticate as a coordinator JWT and confirm SELECT returns all rows for their chapter but zero rows for a different chapter; (3) authenticate as an admin JWT and confirm full access. Verify that a direct INSERT from a peer mentor JWT is rejected with a 403/RLS violation. Document test queries in a test_rls.sql file committed alongside the migration.
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.
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.
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.