Certification
Data Entity
Description
Represents a peer mentor's earned certification record, including issuance date, expiry, status lifecycle, and linkage to a certification type. Used for digital peer mentor certificates (e.g., the HLF 'adelsmerke'), access gating, and gamification triggers.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key — surrogate UUID generated at record creation | PKrequiredunique |
user_id |
uuid |
Foreign key referencing the peer mentor who holds this certification | required |
certification_type_id |
uuid |
Foreign key referencing the certification type (e.g., 'Digital Peer Mentor Certificate', 'Advanced Coordinator') | required |
organization_id |
uuid |
Organization that issued the certification — enforces multi-tenant scoping | required |
certificate_number |
string |
Human-readable unique identifier for the certificate, e.g. 'NHF-2025-00312'. Used for sharing and physical printout reference. | requiredunique |
status |
enum |
Lifecycle state of the certification record | required |
issued_at |
datetime |
Timestamp when the certification was formally issued | required |
expires_at |
datetime |
Expiry timestamp after which the certification is no longer valid. Null for lifetime certifications. | - |
issued_by_user_id |
uuid |
User ID (coordinator or org admin) who issued the certification. Null if system-issued via completed course. | - |
source |
enum |
How the certification was acquired — completion of a course or manual grant by an admin | required |
course_registration_id |
uuid |
Reference to the course_registrations record that triggered this certification. Null for manually granted certs. | - |
revoked_at |
datetime |
Timestamp when the certification was revoked. Null if not revoked. | - |
revoked_by_user_id |
uuid |
User ID of the admin who performed the revocation | - |
revocation_reason |
text |
Free-text explanation for revocation, required when revoked_at is set | - |
expiry_warning_sent_at |
datetime |
Timestamp when the expiry-approaching push notification was last dispatched. Used to prevent duplicate reminders. | - |
notes |
text |
Internal administrative notes about the certification (e.g., exceptional award context) | - |
created_at |
datetime |
Row creation timestamp, set by the database | required |
updated_at |
datetime |
Row last-updated timestamp, maintained by update trigger | required |
Database Indexes
idx_certification_user_id
Columns: user_id
idx_certification_type_id
Columns: certification_type_id
idx_certification_organization_id
Columns: organization_id
idx_certification_status
Columns: status
idx_certification_expires_at
Columns: expires_at
idx_certification_user_type_active
Columns: user_id, certification_type_id, status
idx_certification_certificate_number
Columns: certificate_number
idx_certification_org_expires
Columns: organization_id, expires_at
Validation Rules
expires_at_after_issued_at
error
Validation failed
valid_status_transition
error
Validation failed
course_registration_source_consistency
error
Validation failed
organization_scoped_type
error
Validation failed
issued_by_role_check
error
Validation failed
certificate_number_format
error
Validation failed
Business Rules
one_active_cert_per_user_per_type
A user may only hold one non-revoked, non-expired certification of a given type at a time. Attempting to issue a duplicate active certification for the same (user_id, certification_type_id) pair must be rejected.
revocation_requires_reason
When status is set to 'revoked', revocation_reason must be provided and revoked_by_user_id must reference a valid coordinator or admin.
revoked_cert_cannot_be_reactivated
Once status is 'revoked', it cannot transition to any other status except for audit/migration purposes by a global admin.
expiry_warning_notification
When expires_at is within the configured warning window (default: 30 days), the status must be updated to 'expiring_soon' and a push notification dispatched via course-notification-adapter. expiry_warning_sent_at is set to prevent duplicate dispatches.
auto_expire_past_expiry_date
When the current timestamp exceeds expires_at and status is 'active' or 'expiring_soon', the status must be transitioned to 'expired'. This is evaluated at read time and enforced via a scheduled background job.
tenant_isolation
All certification records must be scoped to an organization_id. No certification may be read or modified by users outside the issuing organization, except global admins.
badge_trigger_on_issuance
Issuing a new certification triggers badge eligibility evaluation for the receiving user (e.g., 'First Certification' badge). badge-evaluation-job must be invoked after a successful create.
certificate_number_uniqueness
certificate_number must be globally unique across all organizations. Generation follows the format '{ORG_PREFIX}-{YEAR}-{SEQUENCE}' and is assigned by the service layer at creation time.