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

Create peer_mentor_status_history audit table

epic-peer-mentor-pause-foundation-task-002 — 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.

critical priority low complexity database pending database specialist Tier 1

Acceptance Criteria

A new table peer_mentor_status_history is created with columns: id UUID PRIMARY KEY DEFAULT gen_random_uuid(), mentor_id UUID NOT NULL, previous_status mentor_status NULL (null for initial status records), new_status mentor_status NOT NULL, changed_by UUID NULL (null for system/job-triggered changes), changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), reason TEXT NULL, job_run_id UUID NULL
A foreign key constraint links mentor_id to peer_mentors(id) with ON DELETE CASCADE so history is cleaned up if a mentor record is hard-deleted
A foreign key constraint links changed_by to auth.users(id) with ON DELETE SET NULL so history survives user deletion
A composite index on (mentor_id, changed_at DESC) is created for efficient timeline queries
A partial index on (job_run_id) WHERE job_run_id IS NOT NULL is created for efficient job audit queries
The table has a column-level comment documenting that changed_by=NULL indicates an automated system action
The migration is reversible with a down migration that drops the table
Running supabase db reset with both task-001 and task-002 migrations applied completes without errors

Technical Requirements

frameworks
Supabase PostgreSQL 15
apis
Supabase CLI
data models
certification
assignment
performance requirements
The composite index on (mentor_id, changed_at DESC) must be created CONCURRENTLY in production to avoid locking — use a separate migration for index creation if supabase does not support concurrent index creation in migration scripts
The table is append-only by design — no UPDATE statements should ever be issued against it
security requirements
The reason column must not log health-related PII — only predefined codes like 'certification_expired', 'coordinator_manual_pause', 'self_pause'
Row-level security will be applied in task-003 — this migration must not define RLS policies
The table must not be exposed via PostgREST auto-generated API without explicit RLS policies in place

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

Reuse the mentor_status enum type defined in task-001 for both previous_status and new_status columns. The job_run_id column links audit records back to automated job executions (from CertificationExpiryJob) — this is critical for correlating bulk auto-pause events. The ON DELETE CASCADE on mentor_id is a pragmatic choice for a GDPR right-to-erasure scenario where a mentor's entire record is removed; however, in practice soft-deletes are preferred — document this in the column comment.

Ensure the migration references the enum type after it has been created (i.e. this migration must declare its dependency on task-001's migration timestamp in the file naming convention).

Testing Requirements

Post-migration smoke tests: (1) INSERT a test history record with all required fields and assert it is retrievable, (2) INSERT a record with changed_by=NULL (system action) and assert it succeeds, (3) DELETE the parent peer_mentor row and assert the history rows are also deleted (CASCADE), (4) DELETE the changed_by user and assert the history row's changed_by becomes NULL (SET NULL), (5) run EXPLAIN on SELECT * FROM peer_mentor_status_history WHERE mentor_id = $1 ORDER BY changed_at DESC and assert the composite index is used. Include these as a migration smoke-test script.

Component
Mentor Status Repository
data low
Dependencies (1)
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. epic-peer-mentor-pause-foundation-task-001
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