Create Supabase schema for certification expiry tracking
epic-certificate-expiry-notifications-data-foundation-task-001 — Design and apply the Supabase database migration that creates the certification_expiry_tracking table. Include columns for peer_mentor_id, certificate_type, expiry_date, last_notified_at, and notification_thresholds_sent (array of integers representing 60/30/7-day markers). Add appropriate indexes on expiry_date and peer_mentor_id for efficient time-windowed queries.
Acceptance Criteria
Technical Requirements
Implementation Notes
Use `supabase migration new create_certification_expiry_tracking` to generate the timestamped file. The `notification_thresholds_sent` column as `int[]` is intentional — it acts as a denormalized set of already-sent thresholds (e.g., `{60, 30}` means 60-day and 30-day notifications have been dispatched). This avoids a separate join table for simple threshold tracking and supports GIN-indexed `@>` containment queries. For certificate_type, prefer a CHECK constraint over a lookup table at this stage to keep the schema simple; it can be migrated to a FK later.
Ensure the migration sets `default '{}'::int[]` so the array is never NULL, simplifying application-layer logic. The composite index (expiry_date, peer_mentor_id) is the primary query path for edge function scans — order matters, put expiry_date first since it is the range predicate.
Testing Requirements
Apply the migration to a local Supabase instance and verify with `psql` or the Supabase Studio UI that all columns, types, constraints, and indexes exist as specified. Write SQL test scripts that: (1) insert a sample row and verify defaults; (2) attempt to insert an invalid certificate_type and confirm the CHECK constraint rejects it; (3) run EXPLAIN ANALYZE on a date-range query and confirm index usage; (4) verify RLS blocks a peer mentor from reading another mentor's row using a JWT with a different sub claim; (5) apply the rollback migration and confirm the table is dropped cleanly. These SQL tests should be committed alongside the migration file.
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.