Create fcm_tokens Supabase table schema
epic-push-notification-delivery-foundation-task-002 — Define and migrate the fcm_tokens table in Supabase with columns for user_id, token (text), platform (ios/android), device_fingerprint, is_active boolean, registered_at, last_refreshed_at, and revoked_at. Include unique constraint on (user_id, device_fingerprint) and index on token for fast lookups.
Acceptance Criteria
Technical Requirements
Implementation Notes
Keep revoked_at and last_refreshed_at as nullable timestamps rather than booleans — this gives a full audit timeline. The is_active flag is redundant with revoked_at but is kept for query simplicity (WHERE is_active = true is more readable than WHERE revoked_at IS NULL). The NotificationTriggerService (task-014) will query SELECT token FROM fcm_tokens WHERE user_id = $1 AND is_active = true — the (user_id, is_active) index is critical for this hot path. device_fingerprint should be populated from flutter_device_info hashed with SHA-256 to avoid storing raw device identifiers.
Do not store the FCM server key in this table or any client-accessible table.
Testing Requirements
pgTAP or Supabase test suite covering: (1) successful insert with platform='ios' and platform='android', (2) rejected insert with platform='web' confirming CHECK violation, (3) duplicate (user_id, device_fingerprint) insert confirms UNIQUE violation, (4) insert of two tokens for the same user on different devices succeeds, (5) cascade delete: remove user from auth.users, confirm all their fcm_tokens rows are deleted, (6) query by token column returns the correct row confirming index is used (EXPLAIN ANALYZE). Tests stored in supabase/tests/.
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.