Database migration: notification_log table
epic-assignment-follow-up-reminders-foundation-task-001 — Create the database migration script to add the notification_log table with columns for notification_id, assignment_id, org_id, notification_type, sent_at, channel (push/in-app), status, and metadata. Apply row-level security policies scoped to org_id so coordinators only see their org's records.
Acceptance Criteria
Technical Requirements
Implementation Notes
Use Supabase CLI (`supabase migration new`) to generate the migration file. Place RLS policies in the same migration file immediately after table creation for atomicity. The service role bypass for INSERT is critical — Edge Functions that dispatch notifications must use the service role key, never the anon key. Use `auth.jwt() ->> 'org_id'` or a helper function `get_my_org_id()` in RLS policies for cleaner policy definitions.
Avoid using `current_user` for RLS — always rely on JWT claims. The metadata JSONB column should store structured context like `{"reminder_day_threshold": 10, "triggered_by": "scheduled_job"}` for debugging without exposing PII. Coordinate with the Edge Function developer (task-005 dependency) to agree on the exact notification_type enum values before finalizing the CHECK constraint, or use TEXT without CHECK to allow future extension via application-level validation.
Testing Requirements
Write SQL-level integration tests using Supabase's pgTAP or a dedicated test schema: (1) Unit: verify table DDL matches expected schema using information_schema queries; (2) RLS policy tests: create two test orgs and two coordinator users, insert notifications for each org, assert each coordinator can only SELECT their own org's rows; (3) Constraint tests: attempt INSERT with invalid channel/status values and assert rejection; attempt INSERT with non-existent assignment_id and assert FK violation; (4) Index existence tests: query pg_indexes to assert all three expected indexes exist; (5) Idempotency test: run migration twice and assert no errors. All tests should run in CI before promotion to staging.
Adding last_contact_date to the assignments table may conflict with existing RLS policies or trigger-based logic that monitors the assignments table. If the migration is not carefully reviewed, existing assignment management features could break in production.
Mitigation & Contingency
Mitigation: Review all existing triggers, policies, and foreign key constraints on the assignments table before writing the migration. Run the migration against a staging Supabase instance with production-like data and execute the full existing test suite before merging.
Contingency: Roll back the migration using Supabase's versioned migration history. Apply the schema change as an additive-only migration (nullable column with default) to ensure zero downtime and reversibility.
The PushNotificationService wraps an existing FCM integration whose internal API contract may have changed or may not expose the payload formatting required for deep-link CTAs. Misalignment discovered late delays the dispatch service epic.
Mitigation & Contingency
Mitigation: Before implementing the wrapper, read the existing push notification integration code and confirm the method signatures, payload structure, and token management model. Agree on a stable interface contract in a shared Dart abstract class.
Contingency: If the existing service is incompatible, implement a thin adapter layer that translates reminder payloads to the existing service's format, isolating the reminder feature from upstream changes.
Incorrect RLS policies on notification_log could allow coordinators to read reminder records belonging to peer mentors in other chapters, exposing sensitive assignment information across organisational boundaries.
Mitigation & Contingency
Mitigation: Write explicit RLS policies with integration tests that assert cross-chapter queries return zero rows. Use Supabase's built-in auth.uid() and join through the org membership tables to scope all queries.
Contingency: If a policy gap is discovered post-merge, immediately disable the affected table's SELECT policy, deploy a corrected policy, and audit recent queries in Supabase logs for any cross-boundary reads.