Create cert_notification_log idempotency table
epic-certification-management-foundation-task-002 — Design and migrate the cert_notification_log table used to prevent duplicate push notifications. Columns: id, certification_id, notification_type (expiry_warning_90d, expiry_warning_30d, expiry_warning_7d, expired), sent_at, recipient_id, idempotency_key (unique). Add index on (certification_id, notification_type) for fast deduplication lookups.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 1 - 540 tasks
Can start after Tier 0 completes
Implementation Notes
The idempotency_key is the primary deduplication mechanism — design it to be deterministic from the cron job side. A recommended format: SHA-256 of '{certification_id}|{notification_type}|{YYYY-MM-DD}' (where the date is the scheduled run date, not the actual send time) ensures the cron job can safely retry within the same day without resending. Use ON CONFLICT (idempotency_key) DO NOTHING in the cron job's INSERT statement. Keep this table append-only; do not update rows.
Consider a periodic cleanup job (e.g. DELETE WHERE sent_at < now() - interval '1 year') to prevent unbounded growth.
Testing Requirements
pgTAP tests: (1) assert that inserting two rows with the same idempotency_key raises a unique_violation; (2) assert that deleting a peer_mentor_certifications row cascades to delete its notification log rows; (3) assert that inserting an invalid notification_type raises a check_violation. Integration: simulate the cron job inserting a duplicate notification attempt and confirm the ON CONFLICT DO NOTHING (or upsert) path returns cleanly without error.
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.