Create peer_mentor_certifications Supabase table
epic-certification-management-foundation-task-001 — Design and migrate the peer_mentor_certifications table in Supabase with all required columns: id, peer_mentor_id, certification_type, issued_at, expires_at, status (active/expired/suspended), and renewal_history as a JSONB column storing the full longitudinal qualification record. Apply RLS policies to restrict row access by organisation and role.
Acceptance Criteria
Technical Requirements
Implementation Notes
Use supabase/migrations/ with a timestamped SQL file (e.g. 20260326000001_create_peer_mentor_certifications.sql). Define RLS policies using auth.uid() for peer mentor self-access and a helper function get_user_organisation_id() that is reused across tables. Store renewal_history as a JSONB array rather than a separate table to keep the full longitudinal record atomic and avoid N+1 queries on the nightly cron job.
Document the expected JSONB shape in a comment inside the migration file. Avoid using serial/integer PKs — use uuid throughout for consistency with Supabase auth.users. Add a database-level comment (COMMENT ON TABLE) describing the table purpose for future developers.
Testing Requirements
Write Supabase database tests using pgTAP or the Supabase test helpers: (1) unit test each RLS policy by authenticating as a peer mentor, coordinator, and org admin and asserting correct row visibility; (2) test that a peer mentor cannot read another mentor's certifications; (3) test that the CHECK constraint on status rejects invalid values; (4) test that the updated_at trigger fires on UPDATE; (5) test that the migration is idempotent by running it twice. Integration tests in Flutter (flutter_test) should confirm that the Supabase client returns only the authenticated user's certifications when queried with normal credentials.
HLF Dynamics portal webhook API contract may be undocumented, subject to change, or require a separate authentication flow not yet agreed upon with HLF. If the contract changes post-implementation, the sync service silently fails and expired peer mentors remain on public listings.
Mitigation & Contingency
Mitigation: Obtain the official Dynamics webhook specification and test credentials from HLF before starting HLFDynamicsSyncService implementation. Agree on a versioned webhook contract and request a staging endpoint for integration testing.
Contingency: If the contract is unavailable, stub the sync service behind a feature flag and ship without Dynamics sync initially. Queue sync events locally and replay once the contract is confirmed.
Supabase RLS policies for certifications must correctly scope data to the coordinator's chapter without leaking cross-organisation data, particularly complex in multi-chapter membership scenarios. A misconfigured policy could expose peer mentor PII to wrong coordinators.
Mitigation & Contingency
Mitigation: Write RLS policies against the established org-hierarchy schema used by other tables. Peer review all policies before migration deployment. Add integration tests that assert cross-organisation data isolation using test accounts with different org scopes.
Contingency: If a policy gap is discovered post-merge, immediately disable the affected query endpoint and apply a hotfix migration. Audit access logs in Supabase for any cross-org data access events.
Storing renewal history as a JSONB field rather than a normalised table simplifies queries but makes retrospective schema changes (adding fields to history entries) harder and could cause issues if history grows very large for long-tenured mentors.
Mitigation & Contingency
Mitigation: Define a versioned JSONB entry schema (include a schema_version field in each entry) so future migrations can transform old entries. Add a size guard in the repository to warn if renewal_history exceeds 500 entries.
Contingency: If JSONB approach proves limiting, add a normalised certification_renewal_events table and migrate history entries in a background job, keeping the JSONB field as a read cache.