Define pause status database schema and migrations
epic-peer-mentor-pause-management-core-workflows-task-001 — Create the database schema for peer mentor pause status tracking, including the pause_records table with columns for mentor_id, status (active/paused/expired_cert), pause_reason, expected_return_date, paused_at, reactivated_at, and coordinator_id. Write migration scripts and ensure RLS policies are applied.
Acceptance Criteria
Technical Requirements
Implementation Notes
Apply the migration via Supabase CLI (`supabase migration new pause_records`). Use `supabase db reset` locally to verify idempotency. The partial unique index (`WHERE status IN ('paused', 'expired_cert')`) is the correct pattern to allow a mentor to have multiple historical rows (active periods) while enforcing only one live paused/expired row. Do NOT use a single row per mentor design — retaining history is required for audit trails and Bufdir reporting.
The coordinator_id column records who last managed the status, not a permanent assignment; chapter membership is the source of truth for coordinator–mentor relationships. Ensure the migration runs before any application code referencing the table is deployed.
Testing Requirements
Write SQL-level integration tests using Supabase's pgTAP or a local Supabase instance. Test cases: (1) Insert valid row with all required fields succeeds. (2) Insert with invalid status value fails CHECK constraint. (3) Insert second paused row for same mentor fails partial unique index.
(4) Mentor JWT can SELECT own row, cannot SELECT another mentor's row. (5) Coordinator JWT can SELECT rows of their mentors. (6) Mentor JWT cannot UPDATE status column. (7) updated_at trigger fires on UPDATE.
(8) Rollback script successfully removes all objects. Run all tests in CI against a clean Supabase local instance.
Concurrent status transitions (e.g., coordinator and automated scheduler both attempting to update the same mentor's status simultaneously) may produce race conditions or inconsistent state in the database, leading to audit log gaps or incorrect notifications.
Mitigation & Contingency
Mitigation: Implement all status transitions as atomic Postgres RPC functions with optimistic locking (version column or updated_at check). Use database-level constraints rather than application-level guards as the final enforcement point.
Contingency: Add a compensation job that reconciles status and log table consistency on each nightly scheduler run, surfacing any discrepancies to coordinator dashboards.
The coordinator-to-mentor assignment relationship may not always be 1:1 or may be stale (coordinator reassigned after a pause was set), causing notifications to be sent to the wrong coordinator or not sent at all.
Mitigation & Contingency
Mitigation: Query the assignment relationship at notification dispatch time rather than caching it at pause creation time. Add a fallback to notify the chapter administrator if no active coordinator assignment exists.
Contingency: Log all undeliverable notification attempts with the originating mentor ID so administrators can manually follow up, and surface undelivered notification counts on the coordinator dashboard.
The CoordinatorPauseRosterScreen may load slowly for coordinators managing large rosters with many concurrent certification expiry queries, degrading usability on low-bandwidth mobile connections.
Mitigation & Contingency
Mitigation: Use a single Supabase RPC that joins mentor status, certification expiry, and assignment data in one query rather than N+1 individual calls. Implement pagination with a configurable page size and skeleton loading states.
Contingency: Add an offline cache of the last-fetched roster state using Riverpod with SharedPreferences, ensuring coordinators can at minimum view stale data when connectivity is poor.