Apply Supabase RLS policies for scenario_rules
epic-scenario-based-follow-up-prompts-foundation-task-003 — Define and apply Row Level Security policies on the scenario_rules table to enforce chapter-scoped data isolation. Coordinators and peer mentors may only read rules belonging to their chapter. Only service-role or admin tokens may insert, update, or delete rules. Validate policies using Supabase SQL editor and confirm cross-chapter access is blocked.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 1 - 540 tasks
Can start after Tier 0 completes
Implementation Notes
The chapter_id JWT claim must be injected during Supabase Auth sign-in using a custom claims hook (database webhook or Edge Function triggered on `auth.users` insert/update). Confirm this claim population mechanism is already in place from earlier auth tasks before implementing these policies. Write policies in a dedicated migration file named e.g. `add_rls_scenario_rules`.
Use `USING` clause for SELECT policies and explicitly do NOT create INSERT/UPDATE/DELETE policies for `authenticated` role — the absence of a permissive policy is itself the denial mechanism. For the service_role bypass, no explicit policy is needed as `service_role` bypasses RLS by default in Supabase — document this in the migration comments to prevent future developers from adding a redundant policy. Test cross-chapter isolation carefully: a misconfigured policy might return an empty set instead of an error, which is correct behavior (RLS silently filters) — make assertions on row count, not on error type.
Testing Requirements
Integration tests must cover: (1) authenticate as coordinator in chapter A, query scenario_rules — assert only chapter A rows returned; (2) authenticate as peer mentor in chapter A, query scenario_rules — assert only chapter A rows returned; (3) authenticate as coordinator in chapter A, attempt INSERT into scenario_rules — assert permission denied error; (4) authenticate as coordinator in chapter A, attempt to read a row with chapter_id = B by explicit filter — assert 0 rows returned (not a permission error, which would leak schema info); (5) use service_role token to INSERT a rule — assert success; (6) use service_role token to read rules from any chapter — assert unrestricted access. Tests should run against a local Supabase instance with seeded multi-chapter data.
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.