🏠 Overview › ✅ Implementation Tasks ›✅ epic-scenario-push-engagement-foundation-task-001

Create notification preferences Supabase table and RLS

epic-scenario-push-engagement-foundation-task-001 — Create the notification_preferences Postgres table in Supabase with columns for user_id, scenario_type, opted_in (default true), updated_at, and org_id. Define RLS policies so users can only read/write their own row and coordinators can read preferences within their org scope. Include migration script.

critical priority low complexity database pending database specialist Tier 0

Acceptance Criteria

Table notification_preferences exists with columns: id (uuid primary key default gen_random_uuid()), user_id (uuid not null references auth.users), scenario_type (text not null), opted_in (boolean not null default true), updated_at (timestamptz not null default now()), org_id (uuid not null)
Unique constraint exists on (user_id, scenario_type) to prevent duplicate preference rows per scenario
RLS is enabled on the table
Policy 'users_own_preferences' allows SELECT and UPDATE for rows where auth.uid() = user_id
Policy 'coordinators_read_org_preferences' allows SELECT for users with coordinator role where org_id matches the coordinator's org
Service role bypasses all RLS policies (default Supabase behavior — verify it is not overridden)
Migration script runs idempotently (CREATE TABLE IF NOT EXISTS, CREATE POLICY IF NOT EXISTS or equivalent DROP/CREATE pattern)
updated_at column is auto-updated via a trigger or application-layer on every UPDATE
Table creation and RLS policies are verified by running the migration on the test Supabase project without errors

Technical Requirements

frameworks
Supabase Postgres migrations
pgSQL
apis
Supabase Dashboard SQL editor or Supabase CLI migrations
data models
notification_preferences (id, user_id, scenario_type, opted_in, updated_at, org_id)
performance requirements
Index on (user_id) for single-user preference lookups
Index on (org_id) for coordinator org-scoped reads
security requirements
RLS must be enabled — table must not be publicly readable
Users may not read or write other users' preferences
Coordinator read policy must be scoped to org_id — no cross-org access
opted_in defaults to true — opt-out is an explicit action, never default denial

Execution Context

Execution Tier
Tier 0

Tier 0 - 440 tasks

Implementation Notes

Use Supabase CLI migration files (supabase/migrations/YYYYMMDDHHMMSS_create_notification_preferences.sql) so schema is version-controlled. For the coordinator read policy, the coordinator's org_id must be retrievable from a user profile or role table — confirm the correct join table (e.g., user_roles or profiles) before writing the policy. Use a moddatetime extension trigger for updated_at auto-update, or handle it at the application layer in the repository. The scenario_type column should be constrained to known values — consider a CHECK constraint with the allowed scenario types ('inactivity', 'milestone', 'certification_expiry') to prevent invalid data at the database level.

Testing Requirements

Manual verification steps after migration: (1) Log in as a test peer mentor user and confirm SELECT returns only their own rows. (2) Attempt SELECT as peer mentor user with another user's user_id filter — confirm empty result. (3) Log in as coordinator and confirm SELECT returns all rows for their org_id. (4) Confirm coordinator cannot UPDATE a preference row (only SELECT).

(5) Confirm INSERT with opted_in omitted creates a row with opted_in = true. Write these as a checklist in the PR description. Automated RLS policy tests can be added as Supabase pgTAP tests if the project uses them.

Component
Notification Preferences Repository
data low
Epic Risks (3)
high impact medium prob dependency

FCM service account key and APNs certificate configuration may be missing or misconfigured in the Supabase Edge Function secrets store, blocking end-to-end push delivery testing until resolved by the infrastructure owner.

Mitigation & Contingency

Mitigation: Raise a credentials-setup task in the project board at epic start; document the exact secret names required in scenario-evaluation-config so the infrastructure owner can provision them independently of development work.

Contingency: Implement a mock push-notification-dispatcher stub that records payloads to the database for local testing, allowing the rest of the feature to proceed while credentials are obtained.

high impact low prob security

Incorrect RLS policies on the scenario_notifications or notification_preferences tables could allow one user to read or modify another user's notification records, constituting a data privacy breach.

Mitigation & Contingency

Mitigation: Write dedicated RLS policy tests using Supabase's built-in test framework before any application code touches the tables; require a peer security review of all policy definitions before merging.

Contingency: If a policy gap is discovered post-merge, immediately disable the affected table's read policy, notify the security lead, and deploy a hotfix with corrected policies before re-enabling access.

medium impact medium prob dependency

Norwegian Bokmål ARB localisation strings for all scenario message templates may not be available at implementation time, causing content-builder tests to fail and delaying integration.

Mitigation & Contingency

Mitigation: Define all required ARB message keys in a tracked document shared with the content owner at epic kickoff; use English placeholder strings that follow the final format so template injection logic can be tested independently.

Contingency: Ship with English-only strings in the first release and gate Norwegian strings behind a feature flag that is enabled once translations are reviewed and approved.

Quick Links
All Tasks Execution Plan