Add database transition guard for invalid status changes
epic-peer-mentor-pause-core-logic-task-003 — Implement a PostgreSQL trigger function on the peer_mentors table that enforces the state machine at the database level. The trigger fires BEFORE UPDATE on the status column and raises an exception if the transition is not in the allowed set (active→paused, paused→active, active→inactive). This acts as a final safety net independent of application-layer validation.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 1 - 540 tasks
Can start after Tier 0 completes
Implementation Notes
Trigger function body should use a simple `CASE` or `IF` block checking `(OLD.status::text, NEW.status::text)` against the allowed pairs. Example structure: `IF OLD.status IS NOT DISTINCT FROM NEW.status THEN RETURN NEW; END IF; IF NOT ((OLD.status, NEW.status) = ANY(ARRAY[('active','paused'),('paused','active'),('active','inactive')])) THEN RAISE EXCEPTION 'Invalid mentor status transition: % → %', OLD.status, NEW.status USING ERRCODE = 'P0001'; END IF; RETURN NEW;`. Name the trigger `check_mentor_status_transition_trigger` (distinct from the function name) following Supabase naming conventions. Add a comment on the trigger and function explaining the intentional layering: application → RPC → trigger, so future developers understand the defence-in-depth design.
Document in the migration file that this trigger must be temporarily disabled before any bulk status migration scripts run.
Testing Requirements
Integration tests against the local Supabase emulator. Test cases: (1) direct SQL UPDATE active→paused succeeds; (2) direct SQL UPDATE paused→active succeeds; (3) direct SQL UPDATE active→inactive succeeds; (4) direct SQL UPDATE paused→inactive raises P0001 exception with correct message; (5) direct SQL UPDATE inactive→active raises P0001 exception; (6) UPDATE with no status change (same value) succeeds silently; (7) UPDATE setting status to NULL raises exception; (8) verify trigger fires even when bypassing the RPC function (raw UPDATE); (9) verify RPC + trigger combination does not double-write audit history. Each test runs in a rolled-back transaction.
The status state machine must handle race conditions where two concurrent callers (e.g., a mentor self-pausing and a coordinator force-pausing simultaneously) attempt to update the same mentor's status. Without a concurrency guard, both writes could succeed, leaving the audit log in an inconsistent state.
Mitigation & Contingency
Mitigation: Use a Supabase RPC with a row-level lock (SELECT FOR UPDATE) inside a transaction so only one transition wins. Return a clear error to the losing caller. Test with concurrent requests in the integration test suite.
Contingency: If row-level locking proves unreliable in the Supabase environment, add an optimistic-locking version field to peer_mentors and have the service retry up to three times on version conflict before surfacing an error to the caller.
If the CertificationExpiryJob Edge Function fails silently (network timeout, Supabase cold start), HLF mentors with expired certifications could remain in active status and continue appearing on the chapter website, creating a compliance breach.
Mitigation & Contingency
Mitigation: Implement structured error logging inside the Edge Function, write a monitoring query that checks for mentors with expired certifications still in active status, and set up an alert if any are detected 30 minutes after the scheduled nightly run.
Contingency: Provide a coordinator-accessible manual trigger for the expiry check that can be invoked via the admin interface if the scheduled job is known to have failed. Document the manual recovery procedure for HLF coordinators.
pg_cron registration in Supabase requires superuser-level access that may not be available in all environments (local dev, staging, CI). If the cron job cannot be registered automatically, the Edge Function will never execute on schedule, breaking the HLF certification expiry workflow.
Mitigation & Contingency
Mitigation: Use Supabase's recommended pg_cron setup via the SQL editor migration script and document the exact commands. Validate cron registration in the staging environment as part of the epic's deployment checklist.
Contingency: If pg_cron is unavailable, switch to a Supabase scheduled Edge Function invocation via an external cron service (e.g., a GitHub Actions scheduled workflow calling the Edge Function endpoint with a service-role key) until the pg_cron approach is resolved.