Add mentor status columns to peer_mentors table
epic-peer-mentor-pause-foundation-task-001 — Create Supabase migration to add status enum (active, paused, inactive), pause_at timestamp, and pause_reason text column to the peer_mentors table. Define the PostgreSQL enum type mentor_status with all required values and apply default of 'active' to existing rows.
Acceptance Criteria
Technical Requirements
Implementation Notes
Define the enum type before altering the table in the same migration file. Use a single transaction block to ensure atomicity. The CHECK constraint logic (status='paused' → pause_at IS NOT NULL) can be written as: CONSTRAINT chk_pause_at_required CHECK (status != 'paused' OR pause_at IS NOT NULL). For the down migration: DROP the three columns with CASCADE only if no dependent objects exist, then DROP TYPE mentor_status.
Coordinate with task-002 author to ensure the enum type name matches what the audit table's status columns will use for consistency.
Testing Requirements
Run supabase db reset and verify via psql or Supabase Studio that: (1) the mentor_status enum type exists with the three correct values, (2) peer_mentors has the three new columns with correct types and nullability, (3) existing rows default to status='active', (4) inserting a row with status='paused' and pause_at=NULL raises a CHECK constraint violation, (5) the down migration cleanly removes the columns and enum type without errors. Include these checks as a migration smoke-test script in the repository.
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.
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.
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.