critical priority medium complexity infrastructure pending infrastructure specialist Tier 2

Acceptance Criteria

RLS is enabled on the follow_up_notification_log table
SELECT policy for peer_mentor role: authenticated user can only read rows where peer_mentor_id = auth.uid() AND chapter_id matches their JWT chapter claim
SELECT policy for coordinator role: authenticated coordinator can read all rows where chapter_id matches their JWT chapter claim
No INSERT policy exists for authenticated role — only service_role may insert
No UPDATE or DELETE policies exist for any client role
A peer mentor cannot read log entries belonging to a different peer mentor in the same chapter
A coordinator can read all log entries within their chapter but not entries from other chapters
A peer mentor cannot insert a log entry directly via Supabase client — returns permission denied
Policies are version-controlled in a Supabase migration file
Policy names follow convention: `notification_log_select_own_mentor`, `notification_log_select_coordinator_chapter`

Technical Requirements

frameworks
Supabase CLI
PostgreSQL RLS
Supabase Auth (JWT)
apis
Supabase Auth API (JWT claims, auth.uid())
Supabase Admin API (service_role)
data models
follow_up_notification_log
user_profiles (role + chapter_id claims)
performance requirements
Both SELECT policies must filter on indexed columns (peer_mentor_id, chapter_id) to maintain sub-100ms query response
Coordinator policy scoped to chapter_id prevents full-table scans across chapters
security requirements
Delivery state (idempotency_key, status) must never be writable by client apps — prevents replay attack circumvention
Peer mentor isolation within a chapter prevents cross-mentor visibility of notification history
Role differentiation must be based on JWT claims, not client-supplied role parameters
No DELETE policy protects audit log integrity

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Implementation Notes

Two separate SELECT policies are required — one for `peer_mentor` role and one for `coordinator` role. Role detection can be done via a custom JWT claim (e.g. `auth.jwt() ->> 'app_role'`) or via a JOIN to a user_profiles table. The JWT claim approach is strongly preferred for performance (no extra query).

If role-based claims are not yet in the JWT, coordinate with the auth implementation to add them. Since INSERT is service_role only, the PromptHistoryRepository Dart class (task-007) must use the Supabase Admin client (service_role key) or call a Supabase Edge Function that uses service_role internally — the mobile client must NOT hold the service_role key. Consider using a Postgres function with SECURITY DEFINER as an alternative insert pathway. Document this security boundary explicitly in migration comments.

Testing Requirements

Integration tests must cover: (1) as peer mentor A in chapter X, query log — assert only own entries returned; (2) as peer mentor A in chapter X, query log with explicit filter peer_mentor_id = mentor_B_id — assert 0 rows returned; (3) as coordinator in chapter X, query log — assert all chapter X entries returned regardless of peer_mentor_id; (4) as coordinator in chapter X, query log filtering by chapter Y — assert 0 rows; (5) as peer mentor, attempt INSERT into follow_up_notification_log — assert permission denied; (6) as coordinator, attempt INSERT — assert permission denied; (7) as service_role, INSERT a record — assert success and row is visible via coordinator SELECT. Use seeded data with at least 2 chapters, 2 peer mentors per chapter, and pre-inserted log entries.

Component
Prompt History Repository
data medium
Epic Risks (2)
high impact medium prob security

Supabase RLS policies for chapter-scoped rule access may interact unexpectedly with service-role keys used by the Edge Function, potentially blocking backend reads or leaking cross-chapter data.

Mitigation & Contingency

Mitigation: Write and review RLS policies in isolation with automated policy tests before merging; define a dedicated service-role bypass policy scoped to the edge function's Postgres role.

Contingency: If RLS blocks the edge function, temporarily use a bypass policy with audit logging while a permanent fix is implemented; escalate to a Supabase security review.

medium impact high prob integration

FCM device tokens become invalid when users reinstall the app or revoke permissions; stale tokens cause silent delivery failures that are hard to detect without explicit error handling.

Mitigation & Contingency

Mitigation: Implement token invalidation handling in PushNotificationDispatcher that removes stale tokens from the database on FCM 404/410 responses; log all delivery failures with structured output.

Contingency: If token hygiene proves unreliable, add a periodic token refresh job that re-registers all active users' tokens via the FCM registration endpoint.