Create Supabase tables for certification status data
epic-peer-mentor-pause-management-foundation-task-001 — Create the cert_expiry_reminders Supabase database table with all required columns, indexes, and constraints. Define the schema for storing certification records, expiry dates, reminder flags, and mentor associations. Write and apply the initial migration file.
Acceptance Criteria
Technical Requirements
Implementation Notes
Use the Supabase CLI workflow: supabase migration new create_cert_expiry_reminders, then write the SQL in the generated file. For the updated_at auto-update trigger, use the standard Supabase pattern of creating a moddatetime extension trigger (already available in Supabase projects). The certification_type column should be constrained to a known set of values using a CHECK constraint or a separate enum type — consult with the domain team on valid values (e.g., 'peer_mentor_basic', 'peer_mentor_advanced') before finalizing. Use uuid_generate_v4() or gen_random_uuid() for the default PK depending on the PostgreSQL version available in the Supabase project.
Commit both the up and down migration files to version control.
Testing Requirements
After applying the migration against the local Supabase development instance (supabase start), verify: (1) all columns exist with correct types using \d cert_expiry_reminders in psql or the Supabase Studio table editor; (2) insert a test row and confirm updated_at auto-updates on modification; (3) attempt to insert a duplicate (mentor_id, certification_type) pair and confirm the unique constraint rejects it; (4) run EXPLAIN ANALYZE on a query filtering by expiry_date to confirm index usage. No Dart tests at this layer — schema validation is sufficient.
Supabase RLS policies for coordinator-scoped status queries may be difficult to express correctly, especially for peer mentors assigned to multiple coordinators or chapters, leading to data leakage or overly restrictive access blocking valid queries.
Mitigation & Contingency
Mitigation: Design RLS policies using security-definer RPCs rather than table-level policies for complex multi-coordinator scenarios. Write a comprehensive RLS test matrix covering all role and assignment permutations before marking complete.
Contingency: Fall back to application-level filtering in the repository layer with explicit coordinator_id parameter checks if RLS proves intractable, and document the trade-off for security review.
The HLF Dynamics portal API contract may be undocumented or subject to change, causing the DynamicsPortalClient to break during development or production rollout.
Mitigation & Contingency
Mitigation: Obtain the full Dynamics portal API specification and credentials early in the sprint. Build the client behind a well-defined interface so the HLF-specific implementation can be swapped without affecting upstream services.
Contingency: If the Dynamics API is unavailable or unstable, stub the client with a feature-flag-guarded no-op implementation so all other epics can proceed to completion independently.
Supabase Edge Functions used as the nightly scheduler host may have cold-start latency or execution time limits that prevent reliable nightly certification checks on large mentor rosters.
Mitigation & Contingency
Mitigation: Benchmark Edge Function execution time against the expected roster size. Design the expiry check to process in paginated batches to stay within execution limits. Use pg_cron with a direct database function as an alternative trigger if Edge Functions prove unreliable.
Contingency: Migrate the scheduler trigger to pg_cron invoking a Postgres function directly, removing the Edge Function dependency entirely for the scheduling layer.