Certification
Data Entity
Description
A digital peer mentor certification record linked to a user and the course that issued it. Tracks issuance date, expiry date, and current status (active, expiring-soon, expired, revoked). Expired certifications trigger automatic peer mentor deactivation and coordinator alerts. HLF treats certifications as a prestigious credential ('adelsmerke').
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key — globally unique certification record identifier | PKrequiredunique |
user_id |
uuid |
FK to users table — the peer mentor who holds this certification | required |
peer_mentor_id |
uuid |
FK to peer_mentors table — the peer mentor profile for fast expiry lookups without joining through users | required |
course_id |
uuid |
FK to courses table — the training course that issued this certification | required |
organization_id |
uuid |
FK to organizations table — issuing organization for multi-tenancy scoping and coordinator-level queries | required |
certificate_number |
string |
Human-readable unique certificate identifier (e.g., HLF-2025-00384). Used on physical card equivalents and digital display. Mirrors the 'adelsmerke' physical card identifier for HLF. | requiredunique |
status |
enum |
Current lifecycle status of the certification. System transitions active→expiring_soon 30 days before expires_at, then expiring_soon→expired at expires_at. Revoked is terminal. | required |
issued_at |
datetime |
Timestamp when the certification was formally issued, typically upon successful course completion | required |
expires_at |
datetime |
Timestamp when the certification expires. After this point status becomes expired and peer mentor deactivation is triggered | required |
revoked_at |
datetime |
Timestamp when the certification was manually revoked by a coordinator or administrator. NULL unless status = revoked | - |
revocation_reason |
text |
Free-text reason for manual revocation. Required when revoked_at is set. Stored for audit purposes. | - |
issued_by_user_id |
uuid |
FK to users — the coordinator or administrator who issued this certification. NULL for auto-issued certs from course completion. | - |
renewal_count |
integer |
Number of times this certification has been renewed. Zero for initial issuance. Used to track certification continuity across renewal cycles. | required |
previous_certification_id |
uuid |
FK to certifications — references the certification this record renews or supersedes. NULL for initial issuance. Enables certification history chain. | - |
expiry_alert_30d_sent |
boolean |
Whether the 30-day expiry reminder has been dispatched for this certification. Prevents duplicate reminder delivery. | required |
expiry_alert_7d_sent |
boolean |
Whether the 7-day expiry reminder has been dispatched for this certification. Prevents duplicate reminder delivery. | required |
deactivation_triggered |
boolean |
Whether the automatic peer mentor deactivation flow was triggered for this expired or revoked certification. Prevents duplicate deactivation attempts. | required |
created_at |
datetime |
Record creation timestamp | required |
updated_at |
datetime |
Record last-modified timestamp, updated on every status transition | required |
Database Indexes
idx_certification_user_id
Columns: user_id
idx_certification_peer_mentor_id
Columns: peer_mentor_id
idx_certification_course_id
Columns: course_id
idx_certification_organization_id
Columns: organization_id
idx_certification_status
Columns: status
idx_certification_expires_at
Columns: expires_at
idx_certification_user_status
Columns: user_id, status
idx_certification_org_status_expires
Columns: organization_id, status, expires_at
idx_certification_active_per_user_course
Columns: user_id, course_id, status
idx_certification_certificate_number
Columns: certificate_number
idx_certification_expiry_alert_pending
Columns: expires_at, status, expiry_alert_30d_sent, expiry_alert_7d_sent
Validation Rules
expires_at_after_issued_at
error
Validation failed
revoked_at_required_when_revoked
error
Validation failed
valid_course_reference
error
Validation failed
valid_user_with_peer_mentor_profile
error
Validation failed
organization_scope_consistency
error
Validation failed
no_future_issued_at
error
Validation failed
renewal_count_non_negative
error
Validation failed
Business Rules
one_active_cert_per_course_per_user
A user may hold at most one non-revoked, non-expired certification per course at any point in time. Issuing a renewal supersedes the previous active record (status→expired) before activating the new one. Prevents duplicate active certifications for the same course.
expired_cert_triggers_peer_mentor_deactivation
When a certification transitions to expired status (via scheduled job or manual evaluation), the Certification Service invokes Pause Mentor Service to deactivate the peer mentor unless already paused. Sets deactivation_triggered = true to prevent re-firing.
expiry_reminders_at_configured_lead_times
The Certificate Expiry Scheduler dispatches expiry alert reminders at 30 days and 7 days before expires_at. Each alert is sent at most once per certification, tracked by expiry_alert_30d_sent and expiry_alert_7d_sent flags. Alert delivery is delegated to the Reminder Service.
revocation_requires_reason
Manual revocation of a certification must include a revocation_reason. Revoked certifications are terminal — they cannot be reactivated. A new certification must be issued to reinstate the peer mentor.
status_auto_transition_to_expiring_soon
Certifications with expires_at within the next 30 days and status = active are transitioned to expiring_soon by the nightly scheduled job. This status drives UI warnings on the peer mentor detail screen and certificate widget.
certification_linked_to_course_completion
Standard certification issuance is triggered only after a user's course enrollment reaches completed status. Manual issuance (by coordinators in the admin screen) is permitted with an explicit override flag for administrative corrections.
certificate_number_uniqueness
Each certification is assigned a unique certificate_number at creation time using a sequential, organization-scoped format (e.g., HLF-2025-00384). This number mirrors the physical peer mentor card identifier used by HLF and must be globally unique.
renewal_chain_integrity
When renewing a certification, the new record's previous_certification_id must reference a valid existing certification for the same user and course. The old record's status is updated to expired before the new record is activated, maintaining an unbroken renewal chain.
CRUD Operations
Storage Configuration
Entity Relationships
A certification generates expiry reminder records at configured lead times (30 days, 7 days before expiry)
A course issues certifications to users who successfully complete it
A peer mentor profile is directly associated with its certifications for fast expiry status lookups
A user (peer mentor) can hold multiple certifications across different courses and renewal cycles