Apply RLS policies for follow_up_notification_log
epic-scenario-based-follow-up-prompts-foundation-task-004 — Define and apply Row Level Security policies on the follow_up_notification_log table to enforce chapter-scoped isolation. Peer mentors may only read their own log entries. Coordinators may read all entries within their chapter. Inserts are restricted to the service role to prevent client-side manipulation of delivery state. Confirm with integration tests.
Acceptance Criteria
Technical Requirements
Execution Context
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.
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.
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.