🏠 Overview › ✅ Implementation Tasks ›✅ epic-certification-management-automation-task-002

Implement Expiry Window Query Logic

epic-certification-management-automation-task-002 — Write the database query logic inside the cron function to identify certifications expiring in 30, 14, and 7 days from the current date, as well as certifications that expired exactly on the current date. Use parameterised date arithmetic against the certifications table with proper index usage. Return structured result sets grouped by expiry window.

critical priority medium complexity backend pending backend specialist Tier 1

Acceptance Criteria

A queryExpiringCertifications(supabase, today: Date) function is exported from supabase/functions/certification-expiry-cron/db.ts
The function executes four separate parameterised queries targeting the certifications table: one per window (30d, 14d, 7d, 0d/expired today)
Each query uses date arithmetic of the form expires_at::date = (now() + interval 'N days')::date to match exactly that day's expiries
Queries are parameterised — the target date is passed as a variable, not interpolated into the SQL string
Each query selects at minimum: id, peer_mentor_id, organization_id, cert_type, expires_at
The function returns a typed object: { expiring30: CertificationRow[], expiring14: CertificationRow[], expiring7: CertificationRow[], expiredToday: CertificationRow[] }
Only certifications with status = 'active' or status = 'expiring_soon' are included — already-paused or revoked certifications are excluded
Multi-tenant isolation is enforced: queries include an organization_id filter derived from the function's execution context or process all organisations in a single query with organisation grouping
Queries use the existing expires_at index on the certifications table — confirmed by EXPLAIN ANALYZE showing Index Scan, not Sequential Scan
If any query returns a database error, the function logs the error and returns an empty array for that window rather than throwing
A Deno unit test at db_test.ts uses a mock Supabase client to assert the correct query parameters are passed for each window given a known today date

Technical Requirements

frameworks
Supabase Edge Functions (Deno)
supabase-js v2 (PostgREST query builder or rpc)
apis
Supabase PostgREST REST API
Supabase PostgreSQL 15 (direct via service role)
data models
certification
assignment
performance requirements
All four window queries must complete within 2 seconds total under normal dataset sizes (<100k certifications)
Queries must use index scans on expires_at — sequential scans on the certifications table are not acceptable
Consider batching queries in a single RPC call or SQL function if round-trip latency is a concern
security requirements
All queries use parameterised values — no string interpolation of date values into SQL
Service role is used server-side only — RLS policies are enforced at the Edge Function layer via explicit organization_id filters
No PII fields (names, contact details) selected in these queries — only IDs and date/status fields

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

Implement in supabase/functions/certification-expiry-cron/db.ts. Use the supabase-js query builder for clarity and automatic parameterisation rather than raw SQL strings. Example pattern: supabase.from('certifications').select('id, peer_mentor_id, organization_id, cert_type, expires_at').eq('status', 'active').filter('expires_at::date', 'eq', targetDateStr). If the PostgREST filter syntax does not support cast expressions, use a Supabase RPC call to a SQL function that accepts the target_date as a parameter — this is safer and more performant.

Define a CertificationRow TypeScript interface at the top of db.ts to type the return values. Use Promise.allSettled to run all four queries in parallel, reducing total latency. Log the count of results per window at INFO level for observability (e.g. console.log('[expiry-cron] 30d window: 12 certs found')).

Testing Requirements

Write Deno unit tests in supabase/functions/certification-expiry-cron/db_test.ts. Create a MockSupabaseClient that captures the query parameters passed to the .from('certifications').select().eq().filter() chain. Assert that for a given today = new Date('2026-03-26'), the 30-day query targets 2026-04-25, the 14-day query targets 2026-04-09, the 7-day query targets 2026-04-02, and the expired-today query targets 2026-03-26. Assert that status filter excludes 'paused' and 'revoked'.

Write an integration test against a local Supabase instance seeded with certifications at known expiry dates and verify the correct records appear in each result window. Run with deno test --allow-env --allow-net.

Component
Certification Expiry Nightly Cron Job
infrastructure medium
Dependencies (1)
Create the Supabase Edge Function skeleton for the nightly certification expiry cron job. Set up the Deno runtime entry point, configure the cron schedule trigger, establish the function's module structure, and wire up the Supabase client with service-role credentials. Ensure the function can be deployed and invoked manually for testing. epic-certification-management-automation-task-001
Epic Risks (2)
medium impact low prob technical

Supabase Edge Functions can have cold-start latency that causes the nightly cron to time out when processing large cohorts of expiring certifications, resulting in partial reminder dispatches.

Mitigation & Contingency

Mitigation: Batch the cron processing in chunks of 50 mentors per iteration. Use pagination with a cursor to resume processing if the function is re-invoked. Keep total invocation time well under the Edge Function timeout limit.

Contingency: If timeouts occur in production, split the cron into two separate functions: one for reminders and one for auto-pauses, each with its own schedule offset to reduce peak load.

low impact medium prob technical

Certification BLoC covers three distinct workflows (view, renew, enrol) which may lead to an overly complex state machine that is hard to test and maintain, particularly when error states from multiple concurrent operations need to be differentiated in the UI.

Mitigation & Contingency

Mitigation: Use separate sealed state classes per workflow (CertificationViewState, RenewalState, EnrolmentState) composed into a single BLoC state wrapper. Follow the existing BLoC patterns established in the codebase for consistency.

Contingency: If the BLoC grows too complex, split into two BLoCs: CertificationBLoC (view/load) and CertificationActionBLoC (mutations), connected via a shared stream.

Quick Links
All Tasks Execution Plan