Certification Renewal Reminder
Data Entity
Description
Tracks scheduled reminders for peer mentor certification renewals, enabling automated notifications before certificate expiry and supporting the Certification Expiry Auto-Pause workflow.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key | PKrequiredunique |
certificate_id |
uuid |
Foreign key to peer_mentor_certificates | required |
user_id |
uuid |
Denormalized reference to the peer mentor for efficient querying without joining through certificates | required |
reminder_type |
enum |
When relative to expiry this reminder fires | required |
scheduled_at |
datetime |
UTC timestamp when this reminder is scheduled to fire | required |
sent_at |
datetime |
UTC timestamp when the reminder was actually dispatched; null if not yet sent | - |
status |
enum |
Lifecycle state of this reminder | required |
channel |
enum |
Delivery channel used for this reminder | required |
failure_reason |
text |
Error detail when status is failed | - |
retry_count |
integer |
Number of delivery attempts made | required |
certificate_expiry_date |
datetime |
Snapshot of the certificate expiry date at reminder creation time; avoids join during scheduler evaluation | required |
organization_id |
uuid |
Organization context for tenant isolation and coordinator notification routing | required |
coordinator_notified |
boolean |
Whether the peer mentor's coordinator was also notified for this reminder | required |
auto_pause_triggered |
boolean |
Whether the on_expiry or post_expiry reminder triggered an auto-pause of the peer mentor | required |
created_at |
datetime |
Record creation timestamp | required |
updated_at |
datetime |
Last modification timestamp | required |
Database Indexes
idx_certification_renewal_reminders_certificate_id
Columns: certificate_id
idx_certification_renewal_reminders_user_id
Columns: user_id
idx_certification_renewal_reminders_scheduled_status
Columns: scheduled_at, status
idx_certification_renewal_reminders_org_status
Columns: organization_id, status
idx_certification_renewal_reminders_certificate_type
Columns: certificate_id, reminder_type
Validation Rules
scheduled_at_before_expiry_for_pre_reminders
error
Validation failed
certificate_id_references_valid_certificate
error
Validation failed
status_transition_valid
error
Validation failed
sent_at_required_when_status_sent
error
Validation failed
retry_count_non_negative
error
Validation failed
Business Rules
reminder_series_created_on_certificate_issue
When a peer_mentor_certificate is issued or renewed, the scheduler creates the full set of reminder records (90/60/30/14/7/1 day before + on_expiry) in a single transaction.
unique_reminder_type_per_certificate
Only one reminder of each reminder_type may exist per certificate. Prevents duplicate reminder series if a certificate record is re-processed.
cancel_pending_reminders_on_renewal
When a certificate is renewed (new expiry date recorded), all pending reminders for the old certificate are cancelled and a new series is created for the new expiry date.
auto_pause_on_expiry_reminder
When the on_expiry reminder fires and the certificate has not been renewed, CertificationExpiryChecker transitions the peer mentor's status to paused and sets auto_pause_triggered=true on the reminder record.
coordinator_notified_at_30_days
At the 30-days-before reminder, the coordinator of the peer mentor's local association is also notified. coordinator_notified is set to true on the reminder record.
failed_reminders_retried_max_three_times
A reminder in failed status with retry_count < 3 is eligible for re-dispatch by the scheduler. After 3 failures the reminder remains failed and is flagged for manual review.