Certificate
Data Entity
Description
Represents a digital peer mentor certificate issued to a user upon successful completion of a course enrollment. Tracks issuance, validity period, revocation state, and generated PDF/badge assets. Drives peer mentor visibility on local chapter pages — an expired or revoked certificate removes the peer mentor from public listings automatically.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key — surrogate UUID generated at issuance | PKrequiredunique |
enrollment_id |
uuid |
FK to course_enrollments. One-to-one: each enrollment produces at most one certificate. Enforced by unique constraint. | requiredunique |
user_id |
uuid |
Denormalized FK to users. Allows direct queries for all certificates belonging to a user without joining through course_enrollments. | required |
course_id |
uuid |
Denormalized FK to courses. Allows filtering certificates by course type without a double join. | required |
certificate_number |
string |
Human-readable, organization-scoped certificate identifier (e.g. 'HLF-2025-00143'). Printed on the PDF and shown in the viewer. Generated at issuance time. | requiredunique |
status |
enum |
Lifecycle state of the certificate. Controls peer mentor visibility in public listings and access to certified-only features. | required |
issued_at |
datetime |
UTC timestamp when the certificate was officially issued. Set once at creation; never updated. | required |
expires_at |
datetime |
UTC timestamp when the certificate expires. Null means the certificate does not expire (e.g. lifetime achievement certificates). The expiry job compares this against current time to set status = expired. | - |
pdf_asset_key |
string |
Object storage key for the generated certificate PDF. Null until PDF generation completes asynchronously after issuance. The certificate-service resolves this to a signed URL on demand. | - |
badge_asset_key |
string |
Object storage key for the digital badge image (PNG). Used in the certificate viewer screen and shareable digital badge flow. | - |
issued_by_user_id |
uuid |
FK to users — the admin or system account that triggered certificate issuance. Null if auto-issued by the system upon enrollment completion. | - |
revoked_at |
datetime |
UTC timestamp when the certificate was revoked. Null unless status = revoked. | - |
revoked_reason |
text |
Free-text reason entered by the admin when revoking a certificate. Required when revoked_at is set. | - |
expiry_notified_at |
datetime |
UTC timestamp when the expiry warning notification was last sent to the certificate holder. Used by certificate-expiry-job to avoid duplicate notifications. | - |
metadata |
json |
Flexible bag for organization-specific certificate attributes (e.g. training hours, instructor name, accreditation body reference). Validated against a per-course JSON schema at issuance time. | - |
created_at |
datetime |
Row creation timestamp. Set by the database default. | required |
updated_at |
datetime |
Row last-modified timestamp. Updated on every write. | required |
Database Indexes
idx_certificates_enrollment_id
Columns: enrollment_id
idx_certificates_user_id
Columns: user_id
idx_certificates_status
Columns: status
idx_certificates_expires_at_status
Columns: expires_at, status
idx_certificates_course_id
Columns: course_id
Validation Rules
expires_at_after_issued_at
error
Validation failed
certificate_number_format
error
Validation failed
revoked_reason_required_on_revocation
error
Validation failed
metadata_schema_validation
error
Validation failed
user_id_matches_enrollment_user
error
Validation failed
asset_key_format
error
Validation failed
Business Rules
one_certificate_per_enrollment
Each course_enrollment record may produce at most one certificate. A second issuance attempt for the same enrollment_id must be rejected. Enforced by the unique index on enrollment_id and a pre-insert check in certificate-service.
issue_only_on_completed_enrollment
A certificate may only be issued when the referenced course_enrollment has status = completed. Attempting to issue against an in-progress or cancelled enrollment is rejected.
expiry_removes_public_visibility
When status transitions to expired, the peer mentor is no longer surfaced in local chapter public listings. The certificate-expiry-job sets status = expired and the application layer filters out non-active certificates when building coordinator and public views.
revocation_requires_reason
Setting status = revoked requires a non-empty revoked_reason and sets revoked_at to the current UTC timestamp. Revocation is irreversible without explicit admin re-issuance.
revoked_certificate_not_reactivatable
A certificate with status = revoked cannot be set back to active or suspended directly. Re-certification requires creating a new certificate record via a new or re-opened enrollment.
expiry_warning_notification
The certificate-expiry-job sends an expiry warning push notification when expires_at is within 30 days and expiry_notified_at is null or older than 7 days. Updates expiry_notified_at after dispatch to prevent duplicates.
physical_certificate_parallel_existence
The digital certificate in this table coexists with a physical card issued by the organization (per HLF requirement). The system must not block or replace the physical card process — it records the digital state only.