Certification
Data Entity
Description
A peer mentor's earned certification record with validity status, issue and expiry dates, and physical card issuance flag. Certifications are operationally critical at HLF where expired certifications trigger automatic availability status change and removal from active pools.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key — auto-generated UUID uniquely identifying the certification record | PKrequiredunique |
user_id |
uuid |
Foreign key referencing the users table; identifies the peer mentor who holds this certification | required |
course_id |
uuid |
Foreign key referencing the courses table; identifies the course whose successful completion issued this certification | required |
status |
enum |
Current validity state of the certification. 'expiring_soon' is a computed transitional state set by the scheduler when expiry is within the configured lead-time window. 'revoked' is set by a coordinator or admin action. | required |
issued_at |
datetime |
Timestamp when the certification was formally issued, typically upon successful course completion confirmation by a coordinator or the system | required |
expires_at |
datetime |
Timestamp when the certification expires. Nullable for certifications with indefinite validity. When populated, the certificate-expiry-scheduler evaluates this field daily to trigger status transitions and coordinator notifications. | - |
has_physical_card |
boolean |
Indicates whether a physical certification card has been issued and mailed to the peer mentor. HLF treats the physical card as a prestige 'adelsmerke' (mark of distinction) that runs in parallel with the digital certificate. | required |
asset_ref |
string |
Supabase Storage path to the digital certificate file (PDF or image) managed by certificate-storage. Used to generate signed download URLs. Null until a digital certificate asset has been generated and uploaded. | - |
certificate_number |
string |
Human-readable certificate identifier, optionally issued by the organization for reference on the physical card or in official correspondence. Format is org-defined (e.g., 'HLF-2025-001423'). | - |
issued_by |
uuid |
Foreign key referencing the users table, identifying the coordinator or system actor that formally issued the certification. Nullable for system-auto-issued certificates. | - |
renewal_count |
integer |
Running count of how many times this certification has been renewed. Incremented each time a certification_renewals record is created for this certification. Used for audit visibility and honorarium threshold analysis. | required |
last_notified_at |
datetime |
Timestamp of the most recent expiry notification sent for this certification. Used by certificate-expiry-scheduler to prevent duplicate notifications within the same notification tier (60d, 30d, 7d). | - |
last_notified_tier |
enum |
The notification tier most recently dispatched for this certification, preventing the scheduler from re-sending the same tier on subsequent daily runs. | - |
organization_id |
uuid |
Foreign key referencing the organizations table; scopes the certification to the issuing organization for RLS policy enforcement and multi-org membership scenarios. Required for correct data isolation. | required |
revocation_reason |
text |
Free-text explanation provided by the coordinator or admin when revoking a certification. Null unless status is 'revoked'. Retained for audit purposes. | - |
created_at |
datetime |
Record creation timestamp, set automatically by the database on INSERT. Distinct from issued_at which reflects the business event. | required |
updated_at |
datetime |
Record last-modified timestamp, maintained via a Supabase trigger on every UPDATE. Used for cache invalidation and optimistic concurrency. | required |
Database Indexes
idx_certifications_user_id
Columns: user_id
idx_certifications_course_id
Columns: course_id
idx_certifications_status
Columns: status
idx_certifications_expires_at
Columns: expires_at
idx_certifications_user_course_active
Columns: user_id, course_id, status
idx_certifications_organization_id
Columns: organization_id
idx_certifications_expiry_status_check
Columns: expires_at, status
Validation Rules
expires_at_after_issued_at
error
Validation failed
valid_user_ref
error
Validation failed
valid_course_ref
error
Validation failed
valid_status_transition
error
Validation failed
asset_ref_storage_path_format
error
Validation failed
renewal_count_non_negative
error
Validation failed
issued_at_not_future
error
Validation failed
certificate_number_uniqueness_within_org
error
Validation failed
Business Rules
expired_cert_triggers_auto_pause
When a certification's expires_at is reached and status transitions to 'expired', the peer mentor's availability in peer_mentor_availability is automatically set to 'paused'. This is HLF policy: expired certifications remove the peer mentor from active assignment pools and from the organization's public listings. The certificate-expiry-scheduler runs this check daily.
one_active_cert_per_user_per_course
A user may not hold more than one certification with status 'active' or 'expiring_soon' for the same course at the same time. Renewal creates a new record and sets the previous one to 'expired' atomically within a transaction. This prevents duplicates and ambiguity in prerequisite checks.
prerequisite_cert_gate
Course enrollment is blocked if the user lacks an active certification for all prerequisite courses linked to the target course. The course-enrollment-service queries certifications by user_id and status='active' before writing the enrollment record.
expiry_notification_tiered_schedule
Push notifications are dispatched to the peer mentor and their assigned coordinator at three configured thresholds before expiry: 60 days, 30 days, and 7 days. The certificate-expiry-scheduler checks last_notified_tier to prevent duplicate notifications within the same tier across daily runs.
physical_card_parallel_system
has_physical_card is managed independently of the digital status. A peer mentor may have a valid digital certificate without a physical card, or vice versa. The physical card field is not used to gate any system access — it is informational only, reflecting HLF's practice of issuing a physical 'adelsmerke' alongside the digital record.
revocation_requires_reason
Setting status to 'revoked' requires a non-empty revocation_reason to be supplied. Revocation is a hard action with audit implications and cannot be performed without justification. Only coordinator and admin roles may revoke.
cert_renewal_immutable_history
Renewing a certification does not overwrite the existing record. Instead, the existing record is transitioned to 'expired' and a new 'active' certification record is created, with a corresponding certification_renewals audit entry. This preserves the full validity history for Bufdir compliance and coordinator audit views.
org_scoped_rls_enforcement
All read and write operations on certifications are scoped to the authenticated user's organization via Supabase RLS. A peer mentor may only read their own certifications. A coordinator may read certifications for peer mentors within their organizational scope. Global admins have full read access.
CRUD Operations
Storage Configuration
Entity Relationships
A certification may be renewed multiple times with each renewal creating an immutable audit record
Each certification is issued upon successful completion of a specific course
A peer mentor may hold multiple certifications for different courses, with HLF policy gating availability on active certificates
A user may hold multiple certifications for different courses with independent validity periods