Create update_mentor_status Supabase RPC function

epic-peer-mentor-pause-core-logic-task-002 — Implement the update_mentor_status PostgreSQL function as a Supabase RPC. The function must validate the requested transition against the state machine, reject invalid transitions with a descriptive error, atomically update the mentor's status in the peer_mentors table, and write an audit history record to mentor_status_history with timestamp, old status, new status, actor, and reason fields.

critical priority medium complexity database pending database specialist Tier 1

Acceptance Criteria

PostgreSQL function `update_mentor_status(p_mentor_id UUID, p_new_status TEXT, p_actor_id UUID, p_reason TEXT)` exists and is callable as a Supabase RPC

Function reads the current status from `peer_mentors` and validates the requested transition using the allowed set (active→paused, paused→active, active→inactive)

On invalid transition, function raises an exception with `SQLSTATE = 'P0001'` and a human-readable message such as 'Invalid status transition: paused → inactive'

On valid transition, function atomically updates `peer_mentors.status` and inserts a row into `mentor_status_history` within a single transaction

`mentor_status_history` row contains: `mentor_id`, `old_status`, `new_status`, `changed_by` (actor_id), `reason`, `changed_at` (DEFAULT now())

Function is defined with `SECURITY DEFINER` and a dedicated role so that the calling user cannot bypass RLS on the history table

Function returns a JSON result `{success: true, mentor_id, new_status, changed_at}` on success

Row-Level Security on `peer_mentors` still enforced — calling user must have access to the mentor row; function does not bypass RLS on the target record

Migration SQL file added to `supabase/migrations/` with a timestamped filename

Technical Requirements

frameworks

Supabase

PostgreSQL (PL/pgSQL)

apis

Supabase RPC (`supabase.rpc('update_mentor_status', {...})`)

data models

peer_mentors

mentor_status_history

MentorStatus

MentorStatusRecord

performance requirements

Function acquires a row-level lock (`SELECT ... FOR UPDATE`) on the mentor row to prevent concurrent status race conditions

Entire operation completes in under 100 ms under normal load

security requirements

SECURITY DEFINER with `search_path = public` explicitly set to prevent search_path injection

p_actor_id must match `auth.uid()` or the caller must hold the 'coordinator' or 'admin' role — validate within the function

p_reason is trimmed and truncated to 500 characters to prevent excessively large audit entries

Function grants EXECUTE only to authenticated role, not to anon role

Execution Context

Execution Tier

Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

View Full Execution Plan

Implementation Notes

Use `SELECT status INTO v_old_status FROM peer_mentors WHERE id = p_mentor_id FOR UPDATE` to lock the row and prevent TOCTOU race conditions. Define the valid transitions as a PL/pgSQL array check: `IF NOT (v_old_status, p_new_status) = ANY(ARRAY[('active','paused'),('paused','active'),('active','inactive')]::text[][]) THEN RAISE ...`. Wrap the UPDATE and INSERT in an explicit `BEGIN ... EXCEPTION ...

END` block to ensure atomicity even if the history insert fails. For the Dart client, call via `supabase.rpc('update_mentor_status', params: {'p_mentor_id': id, 'p_new_status': status, 'p_actor_id': actorId, 'p_reason': reason})` and handle `PostgrestException` with code `P0001` as a domain validation error displayed to the user.

Testing Requirements

Integration tests using Supabase local emulator (supabase start) or a dedicated test schema. Test cases: (1) valid transition active→paused succeeds and history row created; (2) valid transition paused→active succeeds; (3) valid transition active→inactive succeeds; (4) invalid transition paused→inactive raises exception with correct SQLSTATE; (5) concurrent calls with same mentor_id — second call sees updated status (lock test); (6) actor_id mismatch returns permission error; (7) mentor_id not found returns not-found error. All tests run in a transaction that is rolled back after each case to keep the test DB clean.

Component

Mentor Status Service

service medium

Dependencies (1)

Define the TypeScript/Dart interfaces and enums for the mentor status state machine. This includes the MentorStatus enum (active, paused, inactive), StatusTransition type, and the MentorStatusRecord interface. Establish the valid transition matrix that enforces active→paused, paused→active, and active→inactive as the only legal state changes. epic-peer-mentor-pause-core-logic-task-001

Epic Risks (3)

medium impact low prob technical

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.

high impact medium prob technical

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.

medium impact medium prob dependency

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.

Quick Links

All Tasks Execution Plan