Implement RLS policies for notification tables

epic-push-notification-delivery-foundation-task-003 — Write and apply Row Level Security policies for both notification_preferences and fcm_tokens tables. Users may only read and update their own preferences and tokens. Backend service role bypasses RLS for server-side operations. Coordinators have read-only access to notification_preferences for users in their chapter. Validate policies via Supabase policy tests.

critical priority medium complexity database pending database specialist Tier 1

Acceptance Criteria

RLS is enabled on both notification_preferences and fcm_tokens tables via ALTER TABLE ... ENABLE ROW LEVEL SECURITY

notification_preferences: SELECT policy allows a user to read rows WHERE user_id = auth.uid()

notification_preferences: UPDATE policy allows a user to update rows WHERE user_id = auth.uid() (only enabled and updated_at columns; user_id and org_id are immutable)

notification_preferences: INSERT policy allows a user to insert rows WHERE user_id = auth.uid()

notification_preferences: DELETE policy is denied for all authenticated users (preferences are soft-managed, not deleted by users)

notification_preferences: Coordinator SELECT policy allows reading preferences for users WHERE org_id matches a chapter the coordinator manages (via a coordinator_chapters join or equivalent)

fcm_tokens: SELECT policy allows a user to read their own rows WHERE user_id = auth.uid()

fcm_tokens: INSERT policy allows a user to insert rows WHERE user_id = auth.uid()

fcm_tokens: UPDATE policy allows a user to update is_active, last_refreshed_at, revoked_at on their own rows

fcm_tokens: DELETE policy is denied for authenticated users (revocation uses UPDATE, not DELETE)

Service role (used by Supabase Edge Functions and trigger service) bypasses all RLS policies without explicit policies needed

Policy tests confirm: user A cannot read user B's preferences or tokens, coordinator can read but not modify preferences of their chapter's users, unauthenticated requests are rejected on both tables

Technical Requirements

frameworks

Supabase

apis

Supabase Auth (auth.uid(), auth.role())

Supabase Migrations CLI

data models

notification_preferences

fcm_tokens

coordinator_chapters

organizations

performance requirements

RLS policies must use indexed columns (user_id) in WHERE clauses to avoid full table scans on policy evaluation

Coordinator policy join must be bounded — a coordinator manages a finite set of chapters; subquery must be indexed

security requirements

Principle of least privilege: users cannot read other users' FCM tokens under any circumstances

Coordinators get read-only access to preferences only, never to FCM tokens

Service role access is implicit in Supabase — do not create service-role policies that authenticated users could impersonate

Policy names must be descriptive (e.g. 'notification_preferences_user_select') for auditability

Test that anon role (unauthenticated) has zero access to both tables

Execution Context

Execution Tier

Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

View Full Execution Plan

Implementation Notes

Apply RLS policies in a new migration file (separate from the table creation migrations) so each migration is single-responsibility. Use USING clauses for SELECT/DELETE/UPDATE and WITH CHECK clauses for INSERT/UPDATE to enforce both read and write restrictions. For the coordinator policy, use a subquery: EXISTS (SELECT 1 FROM coordinator_chapters cc WHERE cc.coordinator_id = auth.uid() AND cc.org_id = notification_preferences.org_id). If coordinator_chapters table is not yet created, stub it or add a TODO comment with a feature-flag approach.

Do not use security definer functions for RLS — keep policies as plain SQL expressions for transparency. The service role bypass is automatic in Supabase when connecting with the service_role key — no explicit policy needed, but document this in the migration file comments.

Testing Requirements

Supabase policy tests (pgTAP) with the following scenarios: (1) authenticated user reads own preferences — expect rows returned; (2) authenticated user reads another user's preferences — expect 0 rows; (3) authenticated user reads another user's FCM tokens — expect 0 rows; (4) authenticated user attempts DELETE on notification_preferences — expect permission denied; (5) coordinator reads preferences for a user in their chapter — expect rows returned; (6) coordinator reads preferences for a user outside their chapter — expect 0 rows; (7) coordinator attempts UPDATE on preferences — expect permission denied; (8) unauthenticated request to both tables — expect 0 rows. All 8 scenarios must pass in CI before merge.

Component

Notification Preferences Repository

data low

Dependencies (2)

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. epic-push-notification-delivery-foundation-task-001 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. epic-push-notification-delivery-foundation-task-002

Epic Risks (3)

high impact medium prob scope

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.

medium impact medium prob technical

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.

high impact low prob security

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.

Quick Links

All Tasks Execution Plan