Peer Mentor Certification
Data Entity
Description
Tracks the HLF certification status of a peer mentor including issue date, expiry date, certification type, and a JSONB renewal history log. Expired certifications trigger automatic status transitions to paused, suppression from public chapter listings, and Dynamics portal synchronization. The physical certification card is tracked alongside the digital record.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Surrogate primary key, generated server-side via gen_random_uuid() | PKrequiredunique |
peer_mentor_id |
uuid |
Foreign key to peer_mentors.id. Enforces the one-to-one relationship: each peer mentor has at most one current certification record. | requiredunique |
organization_id |
uuid |
Foreign key to organizations.id. Used for RLS tenant scoping. Certification is currently HLF-specific but modeled generically to avoid schema changes if other orgs adopt it. | required |
cert_type |
enum |
Classification of the HLF certification type. Determines visual presentation, renewal requirements, and Bufdir reporting category. Maps to the physical card variant issued. | required |
issued_at |
datetime |
UTC timestamp of original or most recent certificate issuance. Updated on each renewal together with expires_at. The renewal_history log preserves the full issuance chain. | required |
expires_at |
datetime |
UTC timestamp of certificate expiry. The nightly cron job compares NOW() against this field to trigger status transitions, notifications at 60/30/7-day thresholds, and Dynamics portal sync. | required |
renewal_history |
json |
Immutable JSONB append-only log of renewal events. Each entry records: {renewed_at: ISO8601, previous_issued_at: ISO8601, previous_expires_at: ISO8601, renewed_by_user_id: uuid, notes: string|null}. Never mutated in place — entries are appended only. | - |
is_active |
boolean |
Logical active flag. Set to false when the certification is superseded by a new record or revoked by an admin. Distinct from expiry: a certificate can be active but expired (triggering auto-pause) or inactive and non-expired (revoked). | required |
physical_card_issued |
boolean |
Tracks whether the corresponding physical HLF certification card has been printed and dispatched. The physical card is a valued artefact ('adelsmerke') and must be tracked alongside the digital record. | required |
physical_card_number |
string |
Identifier printed on the physical certification card for cross-reference and loss/replacement tracking. Null until the card is dispatched. | - |
suppressed_from_public_listing |
boolean |
Flag set to true when an expired certification causes the peer mentor to be hidden from the HLF public chapter website via Dynamics portal sync. Reset to false on successful renewal. | required |
last_reminder_sent_at |
datetime |
UTC timestamp of the most recent expiry reminder notification dispatched by the nightly cron. Used by the reminder service to enforce per-threshold cooldowns and avoid duplicate push notifications. | - |
last_reminder_threshold_days |
integer |
The day threshold (60, 30, or 7) of the last dispatched reminder. Prevents re-sending the same threshold notification if the cron runs multiple times within the same window. | - |
dynamics_synced_at |
datetime |
UTC timestamp of the last successful Dynamics portal synchronization. Used for audit trails and retry logic when sync failures occur. | - |
created_at |
datetime |
UTC timestamp of record creation, set once server-side. | required |
updated_at |
datetime |
UTC timestamp of last record modification, maintained by a Supabase trigger. | required |
Database Indexes
idx_certification_peer_mentor_id
Columns: peer_mentor_id
idx_certification_organization_id
Columns: organization_id
idx_certification_expires_at
Columns: expires_at
idx_certification_org_expires
Columns: organization_id, expires_at
idx_certification_is_active_expires
Columns: is_active, expires_at
idx_certification_suppressed
Columns: suppressed_from_public_listing
Validation Rules
issued_at_before_expires_at
error
Validation failed
expires_at_must_be_future_on_create
error
Validation failed
cert_type_valid_enum
error
Validation failed
peer_mentor_id_exists
error
Validation failed
renewal_history_entry_schema
error
Validation failed
physical_card_number_max_length
error
Validation failed
reminder_threshold_valid_values
error
Validation failed
new_expires_at_after_current_expires_at
error
Validation failed
Business Rules
one_certification_per_mentor
Each peer mentor may have exactly one certification record. A new renewal updates the existing record's issued_at and expires_at fields and appends to renewal_history rather than creating a new row. The UNIQUE constraint on peer_mentor_id enforces this at the database level.
expired_cert_triggers_auto_pause
When expires_at < NOW() and the peer mentor status is not already paused or resigned, the nightly cron auto-transitions the mentor to 'expired_cert' status. This prevents unqualified mentors from appearing in active assignment pools.
expired_cert_suppresses_public_listing
On expiry, suppressed_from_public_listing is set to true and the HLF Dynamics portal is called to hide the mentor from the public chapter website. On successful renewal, suppressed_from_public_listing is reset to false and the mentor is restored in Dynamics.
renewal_appends_to_history
Recording a renewal never overwrites existing renewal_history entries. The service appends a structured object {renewed_at, previous_issued_at, previous_expires_at, renewed_by_user_id, notes} to the JSONB array. The previous values are captured before the update.
expiry_reminders_at_configured_thresholds
The nightly cron dispatches push and in-app notifications at 60, 30, and 7 days before expires_at. Each threshold is sent exactly once, enforced by comparing last_reminder_threshold_days with the current applicable threshold. Both the peer mentor and their assigned coordinator receive notifications.
renewal_reactivates_paused_mentor
When a renewal is recorded for a mentor whose status is 'expired_cert', the certification-management-service calls the pause-management-service to reactivate the mentor, resets suppressed_from_public_listing to false, and triggers Dynamics sync to restore the mentor on the public website.
hlf_org_scoped
Certification records are HLF-specific. RLS policies restrict creation and reads to users authenticated under the HLF organization_id. Non-HLF coordinators and mentors have no access to this table.
physical_card_tracked_separately
The physical_card_issued flag and physical_card_number must be maintained independently of the digital certification lifecycle. A digital renewal does not automatically mark physical_card_issued — coordinators must explicitly record physical card dispatch.
CRUD Operations
Storage Configuration
Entity Relationships
A certification record belongs to exactly one peer mentor and tracks the full HLF certification lifecycle including renewal history
An HLF peer mentor has exactly one current certification record tracking issue date, expiry, and renewal history