Create notification_preferences Supabase table schema
epic-push-notification-delivery-foundation-task-001 — Define and migrate the notification_preferences table in Supabase with columns for user_id, org_id, category (activity_reminder, certification_expiry, pause_status, scenario_prompt, system), enabled boolean, and updated_at timestamp. Include appropriate indexes and foreign key constraints to the users and organizations tables.
Acceptance Criteria
Technical Requirements
Implementation Notes
Use supabase migration new notification_preferences to generate the migration file. Define the category CHECK inline on the column rather than as a named constraint to keep the DDL readable. The UNIQUE constraint on (user_id, org_id, category) doubles as the index for uniqueness checks, so a separate index on those three columns is not needed — add only the (user_id, org_id) partial index for read performance. Set DEFAULT true on enabled so that initializeDefaultPreferences() in task-004 can insert rows without explicitly setting the value.
Do not create the Realtime publication in this migration — that belongs in the application layer or a separate infrastructure migration to avoid coupling schema and runtime config.
Testing Requirements
Write a Supabase policy test (pgTAP or Supabase's built-in test runner) that: (1) inserts a valid row for each of the 5 categories and confirms success, (2) attempts to insert a row with an invalid category (e.g. 'unknown') and confirms it is rejected with a CHECK violation, (3) attempts to insert a duplicate (user_id, org_id, category) triple and confirms UNIQUE violation, (4) deletes a user from auth.users and confirms the cascade removes all their preference rows. These tests should be stored in supabase/tests/ and run in CI.
iOS only allows one system permission prompt per app install. If the rationale dialog timing or content is wrong the user may permanently deny permissions during onboarding, permanently blocking push delivery for that device with no recovery path short of manual system settings navigation.
Mitigation & Contingency
Mitigation: Design and user-test the rationale dialog content and trigger point (after onboarding value-demonstration step, not at first launch). Implement the settings-deep-link fallback in NotificationPermissionManager so the permission state screen always offers a path to system settings if denied.
Contingency: If denial rates are high in TestFlight testing, revise the rationale copy and trigger timing before production release. Ensure the in-app notification centre provides full value without push so denied users are not blocked from the feature.
FCM token rotation callbacks can fire at any time, including during app termination or network outage. If the token rotation is not persisted reliably the backend trigger service will dispatch to a stale token, resulting in silent notification failures that are hard to diagnose.
Mitigation & Contingency
Mitigation: Persist token rotation updates with a local queue that retries on next app foreground if network is unavailable. Use Supabase upsert by (user_id, device_id) to prevent duplicate token rows and ensure the latest token always wins.
Contingency: If token staleness is observed in production, add a token validity check on each app foreground and force a re-registration if the stored token does not match the FCM-reported current token.
Incorrect RLS policies on notification_preferences or fcm_tokens could expose one user's preferences or device tokens to another user, or could block the backend Edge Function service role from reading token lists needed for dispatch, silently dropping all notifications.
Mitigation & Contingency
Mitigation: Write explicit RLS policy tests using the Supabase test harness covering user-scoped read/write, service-role read for dispatch, and cross-user access denial. Review policies during code review with a security checklist.
Contingency: Maintain a rollback migration that reverts the RLS changes, and add an integration test in CI that asserts the service role can query all tokens and that a normal user JWT cannot access another user's token rows.