Peer Mentor Availability
Data Entity
Description
Tracks current and historical availability status of peer mentors, including active, paused, and inactive states with optional pause reasons and scheduled resume dates. Drives automatic removal from assignment pools and triggers coordinator notifications when status changes.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Unique identifier for this availability record (one record per status transition, enabling full audit history) | PKrequiredunique |
peer_mentor_id |
uuid |
Foreign key referencing the peer_mentors table. Each peer mentor may have many availability records over time. | required |
status |
enum |
Current availability status of the peer mentor for this record. 'active' means available for assignment dispatch. 'paused' means temporarily deactivated and excluded from assignment pools. 'inactive' means formally deactivated (e.g., certificate expired, voluntary exit). | required |
reason |
text |
Optional free-text explanation for why the peer mentor's availability changed. Required when status transitions to 'paused' or 'inactive'. Used in coordinator notifications and audit logs. | - |
resume_date |
datetime |
Optional scheduled date/time when the peer mentor intends to return to active status from a pause. Used by the scheduler to prompt auto-resume or coordinator follow-up. Must be a future timestamp at time of record creation. | - |
is_current |
boolean |
Marks whether this record represents the peer mentor's current availability status. Only one record per peer_mentor_id may have is_current = true at any time. Set to false on all prior records when a new status change is recorded. | required |
change_source |
enum |
Identifies what triggered this availability status change, enabling audit differentiation between self-initiated pauses, coordinator overrides, and system-automated changes such as certificate expiry. | required |
updated_by |
uuid |
Foreign key referencing the users table. Identifies the user (peer mentor, coordinator, admin) or system process that created this availability record. | required |
notification_sent |
boolean |
Tracks whether the coordinator push notification for this status change has been dispatched. Prevents duplicate notifications on retry and supports monitoring. | required |
created_at |
datetime |
Timestamp of when this availability record was inserted. Serves as the effective start time of this status period in the audit history. | required |
updated_at |
datetime |
Timestamp of the last update to this record. Primarily updated when notification_sent flag is toggled after coordinator dispatch. | required |
Database Indexes
idx_peer_mentor_availability_mentor_current
Columns: peer_mentor_id, is_current
Primary lookup index for fetching a peer mentor's current availability status efficiently. Partial index candidate: WHERE is_current = true.
idx_peer_mentor_availability_mentor_id
Columns: peer_mentor_id
Supports full history queries for a given peer mentor, used by audit views and profile pages.
idx_peer_mentor_availability_status
Columns: status
Supports coordinator dashboards filtering by availability status across all mentors in scope.
idx_peer_mentor_availability_resume_date
Columns: resume_date
Supports the scheduler querying for paused mentors with a resume_date on or before today to prompt auto-resume or coordinator follow-up.
idx_peer_mentor_availability_created_at
Columns: peer_mentor_id, created_at
Supports chronological history retrieval per mentor for audit timeline views.
idx_peer_mentor_availability_notification_pending
Columns: notification_sent, status
Supports the coordinator notification service querying for unsent notifications to handle retry scenarios.
Validation Rules
resume_date_must_be_future
error
Validation failed
resume_date_only_when_paused
error
Validation failed
peer_mentor_id_must_exist
error
Validation failed
updated_by_must_be_valid_user
error
Validation failed
reason_max_length
error
Validation failed
status_value_in_allowed_enum
error
Validation failed
change_source_value_in_allowed_enum
error
Validation failed
Business Rules
single_current_record_per_mentor
At any point in time, exactly one peer_mentor_availability record per peer_mentor_id may have is_current = true. When a new status record is inserted, all previous records for the same peer_mentor_id must have is_current set to false. Enforced via a Supabase database trigger or transactional RPC to prevent race conditions.
valid_status_transition
Status transitions must follow the allowed state machine: active → paused, active → inactive, paused → active, paused → inactive, inactive → active. Direct transitions such as inactive → paused are not allowed. Prevents illogical audit histories and ensures coordinator workflows make sense.
coordinator_notification_on_status_change
Whenever a peer mentor transitions to 'paused' or 'inactive' status, the assigned coordinator must receive a push notification containing the mentor's name and the optional pause reason. The notification_sent flag must be set to true after dispatch. Retry is supported by querying records where notification_sent = false.
auto_pause_on_certificate_expiry
When a peer mentor's active certification expires, the certificate-expiry-scheduler automatically inserts a new availability record with status = 'inactive' and change_source = 'system_certificate_expiry'. This removes the mentor from all assignment pools and triggers coordinator notification. This rule implements HLF's requirement that expired certificate holders disappear from active lists automatically.
assignment_pool_exclusion_on_pause
When is_current = true and status is 'paused' or 'inactive', the peer mentor must be excluded from all assignment dispatch queries, geographic matching results, and mentor selection widgets. Enforced via Supabase RLS policies and application-level filtering in geographic-matching-service and coordinator-overview-service.
reason_required_for_pause_or_inactive
When a coordinator or admin sets a peer mentor's status to 'paused' or 'inactive', a reason must be provided. Self-initiated pauses by the peer mentor may include an optional reason but it is not mandatory. This ensures coordinators always have context for availability changes in their dashboards.
rls_scope_enforcement
Peer mentors may only read and write their own availability records. Coordinators may read availability records for all peer mentors within their organizational scope. Admins have full read/write access. Global admin access is unrestricted. Enforced via Supabase Row-Level Security policies using auth.uid() and JWT role claims.
CRUD Operations
Storage Configuration
Entity Relationships
A peer mentor has a history of availability status changes tracked for audit and coordinator alerts