Certification Expiry Repository
Component Detail
Description
Data access component responsible for querying and managing certification expiry dates stored in the peer mentor profile or user roles table. Supports the daily scheduled edge function by providing efficient queries for upcoming expirations within configurable time windows.
certification-expiry-repository
Summaries
The Certification Expiry Repository is the foundational data layer that makes proactive certification management possible. Without efficient, reliable access to expiry dates across the entire peer mentor workforce, the system cannot detect approaching deadlines or trigger timely remediation workflows. This component enables the organization to shift from reactive compliance management—discovering lapses after they occur—to a proactive model that surfaces risks at 60, 30, and 7-day horizons. The direct business impact is a reduction in unplanned certification gaps that disrupt service delivery and create regulatory exposure.
Suppression status management also ensures that expired mentors are automatically removed from active workflows, protecting service quality and organizational credibility.
Medium complexity with backend-only execution. This component has no upstream dependencies, making it a good candidate for early development and independent testing. It is, however, a critical dependency for the expiry-check-edge-function and potentially other notification components, so it should be prioritized in the build sequence to unblock downstream work.
Key delivery considerations include confirming the database schema for suppression status (boolean flag on the mentor profile or a separate suppression table) and agreeing on the configurable time-window parameters (60/30/7 days) with stakeholders before implementation. Performance testing is recommended for getUpcomingExpirations and getExpiredMentors, as these run against the full mentor dataset on a daily schedule and must complete within the edge function's execution timeout.
This repository abstracts all SQL queries against the peer_mentor_profile and certificate tables related to expiry state. getUpcomingExpirations(withinDays) executes a range query on the expiry_date column filtered by NOW() and NOW() + withinDays interval, returning ExpiryRecord projections. getExpiredMentors filters for expiry_date < NOW(). Suppression status (setSuppressedStatus, isSuppressed) maps to a boolean column on the mentor profile, toggled atomically.
updateCertificationExpiryDate handles renewal writes and should invalidate any cached expiry state if caching is introduced. Index coverage on expiry_date and mentor_id is essential for acceptable query performance at scale. The configurable time-window values should be driven by environment variables or a config table rather than hardcoded constants to support operational tuning without redeployment.
Responsibilities
- Query peer mentors with expiry dates within 60/30/7 day windows
- Retrieve certification expiry date for a specific mentor
- Update certification expiry date upon renewal
- Support suppression status read/write operations
Interfaces
getUpcomingExpirations(withinDays: int) -> List<ExpiryRecord>
getCertificationExpiryDate(mentorId: String) -> DateTime?
updateCertificationExpiryDate(mentorId: String, newDate: DateTime)
getExpiredMentors() -> List<PeerMentor>
setSuppressedStatus(mentorId: String, suppressed: bool)
isSuppressed(mentorId: String) -> bool
Relationships
Dependents (4)
Components that depend on this component
Related Data Entities (2)
Data entities managed by this component