Certification Renewal
Data Entity
Description
Tracks renewal events for certifications approaching or past expiry, recording the renewal timestamp, new expiry date, and trigger type (user-initiated, coordinator override, or automatic re-enrollment). Used for audit and expiry notification scheduling.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Immutable primary key for the renewal record, generated server-side on insert. | PKrequiredunique |
certification_id |
uuid |
Foreign key referencing the certifications record this renewal belongs to. A single certification may accumulate many renewal records over its lifetime. | required |
renewed_at |
datetime |
UTC timestamp of when the renewal event was recorded. Set server-side at insert time; never client-supplied to prevent clock skew in audit records. | required |
previous_expiry_date |
datetime |
The expiry date of the certification immediately before this renewal was applied. Captured at write time for full audit trail without requiring a historical join to certifications. | required |
new_expiry_date |
datetime |
The updated expiry date applied to the certification as a result of this renewal. Must be strictly after renewed_at and after previous_expiry_date for standard renewals; may equal previous_expiry_date only for coordinator overrides that reset to original term. | required |
trigger_type |
enum |
Classifies what caused this renewal event. Drives audit reporting, downstream notification logic, and determines which actor fields are required. | required |
renewed_by |
uuid |
Foreign key referencing the users record of the actor who triggered the renewal. Required for user_initiated and coordinator_override trigger types. NULL for automatic_reenrollment events initiated by the certificate-expiry-scheduler system process. | - |
course_enrollment_id |
uuid |
Optional foreign key to course_enrollments when the renewal was triggered by successful re-enrollment in a certification course. Provides traceability from renewal back to the originating enrollment event. | - |
notes |
text |
Optional free-text field for coordinator or system notes about the renewal, such as exception reasons for override renewals or automatic scheduler run identifiers. | - |
created_at |
datetime |
UTC timestamp set by the database on row insertion. Distinct from renewed_at in that renewed_at reflects the business event time whereas created_at is the persistence time. | required |
Database Indexes
idx_certification_renewals_certification_id
Columns: certification_id
idx_certification_renewals_certification_id_renewed_at
Columns: certification_id, renewed_at
idx_certification_renewals_renewed_at
Columns: renewed_at
idx_certification_renewals_new_expiry_date
Columns: new_expiry_date
idx_certification_renewals_trigger_type
Columns: trigger_type
idx_certification_renewals_renewed_by
Columns: renewed_by
Validation Rules
certification_id_must_exist
error
Validation failed
new_expiry_date_format_and_range
error
Validation failed
trigger_type_enum_membership
error
Validation failed
renewed_by_must_be_valid_user
error
Validation failed
course_enrollment_id_must_be_valid_when_present
error
Validation failed
notes_length_constraint
error
Validation failed
automatic_reenrollment_requires_scheduler_context
error
Validation failed
Business Rules
immutable_audit_record
Certification renewal records are immutable once created. No UPDATE operations are permitted outside of administrative data correction by a global admin. All fields are set at insert time and reflect the state of the certification at the moment of renewal.
new_expiry_must_exceed_renewal_timestamp
The new_expiry_date must be strictly greater than renewed_at. A renewal that results in an already-expired date is rejected to prevent immediate re-expiry loops.
actor_required_for_non_automatic_triggers
When trigger_type is user_initiated or coordinator_override, renewed_by must reference a valid user. For automatic_reenrollment events generated by the certificate-expiry-scheduler, renewed_by must be NULL to distinguish system actions from human actions in audit reports.
coordinator_override_requires_coordinator_role
When trigger_type is coordinator_override, the renewed_by user must hold a coordinator or admin role in the same organization as the peer mentor who owns the certification. Peer mentors cannot create coordinator_override renewal records.
certification_expiry_date_updated_on_renewal
On successful creation of a certification_renewals record, the parent certifications.expiry_date must be updated to match new_expiry_date and certifications.is_expired must be re-evaluated. These two writes must occur in the same database transaction to prevent orphaned renewal records with stale parent state.
availability_status_restored_on_renewal
If the associated peer mentor's availability status is paused due to certificate expiry (as enforced by certificate-expiry-scheduler), a successful renewal with a future new_expiry_date must trigger re-evaluation of peer_mentor_availability. The availability status is not automatically restored — the coordinator is notified to manually reinstate.
rls_scoped_to_organization
Supabase RLS policies restrict read access to renewal records belonging to certifications owned by users within the authenticated actor's organizational scope. Peer mentors may only read their own renewal records. Coordinators may read renewals for peer mentors in their association. Global admins have full read access.
previous_expiry_captured_at_write_time
The previous_expiry_date must be populated by the service layer by reading certifications.expiry_date immediately before the renewal insert, within the same transaction. It must not be supplied by the client to prevent tampering with the audit trail.
CRUD Operations
Storage Configuration
Entity Relationships
A certification may be renewed multiple times with each renewal creating an immutable audit record