Implement time-windowed expiry queries (60/30/7-day)
epic-certificate-expiry-notifications-data-foundation-task-004 — Add the three threshold query methods to CertificationExpiryRepository: getMentorsExpiringWithinDays(60), getMentorsExpiringWithinDays(30), and getMentorsExpiringWithinDays(7). Each method must use efficient date-range SQL via Supabase RPC or filter chaining, returning only mentors whose certificate expires within the specified window but have NOT already received a notification for that threshold. Optimize with composite indexes.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 2 - 518 tasks
Can start after Tier 1 completes
Implementation Notes
PostgREST supports array containment via the `cs` (contains) and `not.cs` (not contains) operators. In Supabase Dart client: `.not('notification_thresholds_sent', 'cs', '{$days}')`. However, verify this syntax against the current supabase_flutter package version — if `not.cs` is not supported directly, use a Supabase RPC function (`select get_expiring_mentors(days int)`) that encapsulates the array containment logic in SQL for reliability.
The RPC approach is preferred for complex array logic as it avoids client-side PostgREST operator gymnastics. Regardless of approach, the date range must compute `now()` server-side (in SQL), not client-side, to avoid timezone/clock skew issues in edge functions. Do not add `limit` without pagination — the edge function may need all results; let the caller paginate if needed.
Testing Requirements
Unit tests with mocked Supabase client: (1) verify correct PostgREST filter parameters are passed (lte expiry_date, gte expiry_date, array not-contains threshold); (2) verify empty list returned when mock returns empty; (3) verify list is correctly deserialized from fixture JSON. Integration tests against local Supabase: seed records with expiry dates at now+5, now+25, now+55, now+90 days and notification_thresholds_sent of {60} for the 55-day record; assert getMentorsExpiringWithinDays(60) returns now+5, now+25, now+55 records; assert getMentorsExpiringWithinDays(30) returns now+5 and now+25 only; assert getMentorsExpiringWithinDays(7) returns only now+5; assert the 55-day record with {60} in thresholds_sent is excluded from the 60-day query. All tests in flutter_test.
The RLS policy predicate that checks certification_expiry_date and suppression_status on every coordinator list query could cause full table scans at scale, degrading response time for coordinator contact list screens across all chapters.
Mitigation & Contingency
Mitigation: Add a partial index on (certification_expiry_date, suppression_status) filtered to active mentors. Benchmark the policy predicate against a representative data set (500+ mentors) during development using EXPLAIN ANALYZE on Supabase staging.
Contingency: If the index does not resolve the performance issue, introduce a computed boolean column is_publicly_visible that is updated by the mentor_visibility_suppressor service and indexed separately, shifting the predicate cost to write time rather than read time.
FCM device tokens become invalid when users reinstall the app or switch devices. If the token management strategy does not handle token refresh reliably, notification delivery will silently fail for a significant portion of the user base without surfacing errors.
Mitigation & Contingency
Mitigation: Implement the FCM token refresh callback in the Flutter client to upsert the latest token to Supabase on every app launch. Store token with a last_refreshed_at timestamp. The FCM sender should handle UNREGISTERED error codes by deleting stale tokens.
Contingency: If token staleness becomes widespread, add a token health check that forces re-registration during the expiry check edge function run by querying mentors whose token was last refreshed more than 30 days ago and triggering a silent push to prompt re-registration.
The certification expiry and notification record tables may have column naming or constraint conflicts with existing tables in the peer mentor status and certification management features, causing migration failures in shared Supabase environments.
Mitigation & Contingency
Mitigation: Audit existing table schemas for user_roles, certifications, and notification tables before writing migrations. Prefix new columns with expiry_ to avoid collisions. Run migrations against a clean Supabase branch environment before merging.
Contingency: If a conflict is found post-merge, apply ALTER TABLE migrations to rename conflicting columns and issue a hotfix migration. Communicate schema changes to all dependent feature teams via a shared migration changelog.