Mentor Pairing
Data Entity
Description
Records the formal mentorship pairing between an experienced peer mentor and a mentee in Blindeforbundet's structured mentorship scheme. Pairings track lifecycle status (active, paused, dissolved), matching criteria used, and milestone progress.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Immutable primary key for the pairing record, generated server-side on creation | PKrequiredunique |
mentor_id |
uuid |
Foreign key referencing the peer_mentors record for the experienced mentor acting as guide in the pairing | required |
mentee_id |
uuid |
Foreign key referencing the peer_mentors record for the newer peer mentor receiving guidance as the mentee | required |
organization_id |
uuid |
Foreign key scoping the pairing to a specific organization for RLS access control and coordinator oversight | required |
status |
enum |
Current lifecycle state of the pairing. Drives UI display and governs which operations are permitted | required |
matching_criteria |
json |
Structured record of the criteria used when forming this pairing, e.g. geographic proximity, shared specialisation area, language, or availability overlap. Stored as a JSON object for flexible schema evolution. | - |
milestone_progress |
json |
JSON array of milestone objects tracking programme checkpoints completed by the pairing (e.g. first meeting held, mid-programme review, final review). Each object contains milestone_id, label, completed boolean, and completed_at timestamp. | - |
created_by |
uuid |
Foreign key referencing the users.id of the coordinator who created the pairing. Used for audit trail and coordinator-scoped queries. | required |
created_at |
datetime |
UTC timestamp when the pairing record was created | required |
activated_at |
datetime |
UTC timestamp when the pairing transitioned to active status. Null if still pending. | - |
paused_at |
datetime |
UTC timestamp of the most recent pause transition. Null if never paused. | - |
dissolved_at |
datetime |
UTC timestamp when the pairing was formally dissolved. Null for all non-dissolved pairings. | - |
pause_reason |
text |
Optional free-text explanation provided by the coordinator when pausing the pairing | - |
dissolution_reason |
text |
Required free-text explanation provided by the coordinator when dissolving the pairing. Enforced at the service layer. | - |
notes |
text |
Internal coordinator notes on the pairing, not shared with mentor or mentee. May capture context about match rationale or ongoing observations. | - |
updated_at |
datetime |
UTC timestamp automatically updated on any row mutation, used for cache invalidation and change tracking | required |
Database Indexes
idx_mentor_pairings_mentor_id
Columns: mentor_id
idx_mentor_pairings_mentee_id
Columns: mentee_id
idx_mentor_pairings_organization_id
Columns: organization_id
idx_mentor_pairings_status
Columns: status
idx_mentor_pairings_org_status
Columns: organization_id, status
idx_mentor_pairings_created_by
Columns: created_by
idx_mentor_pairings_active_mentee
Columns: mentee_id, status
Validation Rules
mentor_id_references_valid_peer_mentor
error
Validation failed
mentee_id_references_valid_peer_mentor
error
Validation failed
organization_id_references_valid_org
error
Validation failed
dissolved_at_after_created_at
error
Validation failed
matching_criteria_json_schema
warning
Validation failed
milestone_progress_json_schema
error
Validation failed
status_enum_value_check
error
Validation failed
created_by_references_coordinator
error
Validation failed
Business Rules
single_active_pairing_per_mentee
A mentee can only have one pairing in active or pending status at a time. Enforced by a partial unique index on (mentee_id, status) WHERE status IN ('active', 'pending'). Prevents duplicate concurrent programme enrolments.
mentor_and_mentee_must_differ
The mentor_id and mentee_id fields must reference different peer mentor records. A peer mentor cannot be paired with themselves.
coordinator_only_create_dissolve
Only users with coordinator or admin role may create or dissolve pairings. Peer mentors may view their own pairings but cannot mutate them. Enforced via Supabase RLS policies tied to JWT role claims.
organization_scope_enforcement
Both mentor_id and mentee_id must belong to the same organization_id. Coordinators may only create pairings within their own organizational scope. Enforced by service-layer validation before write and by Supabase RLS on read.
dissolution_requires_reason
When dissolving a pairing (status → dissolved), the dissolution_reason field must be non-empty. This ensures audit trail completeness for programme review and Bufdir compliance.
status_transition_guard
Status transitions are restricted to valid paths: pending → active, pending → dissolved, active → paused, active → dissolved, paused → active, paused → dissolved. Any other transition is rejected at the service layer.
mentor_must_be_active_peer_mentor
The mentor_id must reference a peer mentor whose status is active (not paused or deactivated) at the time of pairing creation. Ensures the programme can begin immediately.
timestamp_cascade_on_status_change
When status changes, the corresponding timestamp field must be set: activated_at on → active, paused_at on → paused, dissolved_at on → dissolved. Set by service layer before persistence.
CRUD Operations
Storage Configuration
Entity Relationships
Mentor pairings are scoped to an organization for RLS access control and coordinator oversight
Each pairing references the experienced peer mentor acting as the guide in the mentorship scheme
Each pairing references the newer peer mentor receiving guidance as the mentee