Create notifications table migration and RLS policies

epic-in-app-notification-centre-foundation-task-001 — Write Supabase migration to create the notifications table with columns: id, user_id, type (enum: reminder, expiry, scenario, pause, system), payload (JSONB), is_read, created_at, updated_at. Implement RLS policies so users only see their own notifications, with coordinator and org-admin expansion policies that allow scoped visibility into their managed peer mentors' notifications.

critical priority medium complexity database pending database specialist Tier 0

Acceptance Criteria

Migration creates a notifications table with exactly the following columns: id (uuid, primary key, default gen_random_uuid()), user_id (uuid, not null, references auth.users(id) on delete cascade), type (notification_type enum, not null), payload (jsonb, not null, default '{}'), is_read (boolean, not null, default false), created_at (timestamptz, not null, default now()), updated_at (timestamptz, not null, default now())

Migration creates a notification_type enum with values: reminder, expiry, scenario, pause, system

Migration creates an index on (user_id, created_at DESC) for efficient per-user chronological queries

Migration creates an index on (user_id, is_read) for efficient unread count queries

RLS is enabled on the notifications table

RLS SELECT policy 'users_own_notifications': authenticated users can select only rows where user_id = auth.uid()

RLS INSERT policy 'service_role_insert': only the service_role can insert notifications (no user self-insert)

RLS UPDATE policy 'users_mark_read': authenticated users can update only is_read on their own rows — no other columns

RLS SELECT policy 'coordinator_managed_notifications': coordinators can select notifications for peer mentors they manage, scoped by organisation — join through a coordinator_peer_mentor relationship table

RLS SELECT policy 'org_admin_notifications': org-admins can select all notifications for users within their organisation

Migration is idempotent — running it twice does not produce an error (use IF NOT EXISTS and DO $$ ... $$ blocks)

Migration includes a rollback (down migration) that cleanly drops the table, enum, indexes, and policies

All RLS policies are verified with at least one Supabase SQL test or pgTAP test per policy

Technical Requirements

frameworks

Supabase

PostgreSQL

apis

Supabase Admin API (for migration deployment)

data models

Notification

User

PeerMentor

CoordinatorPeerMentorRelationship

Organisation

performance requirements

Query for unread notification count for a single user must execute in under 10ms with an index on (user_id, is_read)

Query for 50 most recent notifications for a user must execute in under 20ms with an index on (user_id, created_at DESC)

RLS policies must not cause full table scans — all policies must use indexed columns

security requirements

Users must never be able to read another user's notifications via direct query — RLS enforces user_id = auth.uid()

Users must not be able to insert notifications directly — only service_role can insert

Users must not be able to update any field except is_read on their own rows

Coordinator RLS policy must be scoped to organisation — a coordinator from Org A cannot see notifications for peer mentors in Org B

Org-admin RLS policy must be scoped to their own organisation only

payload JSONB column must not store PII directly — reference user/mentor IDs only, with PII fetched at display time

All RLS policies must be tested with explicit role switching (SET ROLE) to verify enforcement

Execution Context

Execution Tier

Tier 0

Tier 0 - 440 tasks

View Full Execution Plan

Implementation Notes

Use a single migration file with both up and down sections, prefixed with timestamp (e.g., 20260329000001_create_notifications_table.sql). Create the notification_type enum before the table to avoid dependency issues. For the coordinator RLS policy, the join logic will reference a table that maps coordinators to their managed peer mentors (likely already exists — check existing schema for the coordinator relationship table name before writing the policy). Use auth.uid() not current_user in all RLS policies for Supabase compatibility.

The updated_at column should use a trigger (moddatetime extension or custom trigger) to auto-update on row modification — do not rely on application-level updates. Add a comment on the table and each column using COMMENT ON for self-documenting schema. Test the migration locally with supabase start and supabase migration up before committing.

Testing Requirements

Write pgTAP or Supabase SQL unit tests for each RLS policy. Test matrix: (1) peer mentor user can SELECT own notifications, (2) peer mentor user cannot SELECT another user's notifications, (3) peer mentor user cannot INSERT a notification, (4) peer mentor user can UPDATE is_read on own notification, (5) peer mentor user cannot UPDATE user_id or type on own notification, (6) coordinator can SELECT notifications for their managed peer mentors, (7) coordinator cannot SELECT notifications for peer mentors outside their organisation, (8) org-admin can SELECT all notifications within their org, (9) org-admin cannot SELECT notifications from another org, (10) service_role can INSERT notifications. Run migration in a local Supabase environment with supabase db reset and verify with psql queries. Include the migration in the project's supabase/migrations/ directory with a timestamp prefix.

Component

Notification Repository

data medium

Epic Risks (3)

high impact medium prob technical

Supabase Realtime channels on mobile networks can drop silently. If reconnection logic is flawed, users miss notifications without knowing it, undermining the audit-trail guarantee.

Mitigation & Contingency

Mitigation: Implement exponential-backoff reconnection with a maximum of 5 retries; expose a channel-status stream to the BLoC so it can trigger a full-fetch fallback when the channel reconnects after a gap.

Contingency: If Realtime reliability proves insufficient in production, fall back to polling the repository every 60 seconds as a background supplement to the Realtime channel.

high impact medium prob security

Coordinator and org-admin RLS expansions require joining user_roles and org_memberships tables. An incorrect policy could expose notifications to wrong users or block legitimate access entirely.

Mitigation & Contingency

Mitigation: Write dedicated RLS integration tests for each role (peer mentor, coordinator, org admin) using separate Supabase test projects. Review policies with the security checklist before merging.

Contingency: If an RLS defect is discovered post-deployment, disable the expanded-scope policy and revert to user-scoped-only access while a corrected migration is prepared and tested.

medium impact medium prob integration

JSONB payload structure may vary across notification types created by different Edge Functions (reminder, expiry, scenario, pause). Missing or renamed fields will cause runtime parse failures.

Mitigation & Contingency

Mitigation: Define a canonical NotificationPayload union type in a shared schema document. Each Edge Function must validate its payload against this schema before inserting. Add fallback parsing with default values in the domain model.

Contingency: Wrap all payload parsing in try/catch and log malformed payloads to a monitoring channel; render a generic notification item rather than crashing when the payload cannot be parsed.

Quick Links

All Tasks Execution Plan