critical priority medium complexity infrastructure pending infrastructure specialist Tier 1

Acceptance Criteria

RLS is enabled on the scenario_rules table (`ALTER TABLE scenario_rules ENABLE ROW LEVEL SECURITY`)
SELECT policy exists allowing authenticated users to read only rules where chapter_id matches their JWT chapter claim (auth.jwt() ->> 'chapter_id')
No INSERT, UPDATE, or DELETE policies exist for the authenticated role — those operations are exclusively permitted via service_role
A coordinator authenticated with chapter_id = 'A' cannot retrieve rows where chapter_id = 'B' via any Supabase client query
A peer mentor authenticated with chapter_id = 'A' cannot retrieve rows where chapter_id = 'B'
Service-role bypass is confirmed: inserting a rule with service_role token succeeds regardless of chapter
Policies are defined in a Supabase migration file (not applied ad-hoc via SQL editor) so they are version-controlled
Policy names follow the convention: `scenario_rules_select_own_chapter`, `scenario_rules_service_role_all`
Applying the migration to a clean local instance produces the policies visible in `supabase db diff`

Technical Requirements

frameworks
Supabase CLI
PostgreSQL RLS
Supabase Auth (JWT)
apis
Supabase Auth API (JWT claims)
Supabase Admin API (service_role)
data models
scenario_rules
chapters
user_profiles (chapter_id claim source)
performance requirements
RLS policy predicate on chapter_id must use an indexed column to avoid full table scans
JWT claim extraction (auth.jwt() ->> 'chapter_id') should be used directly — avoid subqueries in policy WHERE clause
security requirements
Default-deny: if no policy matches, access is denied — do not create a fallback permissive policy
Mutation operations (INSERT/UPDATE/DELETE) are strictly service_role only to prevent client-side data tampering
JWT chapter_id claim must be set at login time and verified server-side — document how this claim is populated
Policy must not rely on client-supplied chapter_id in query params — only trust the JWT claim

Execution Context

Execution Tier
Tier 1

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.

Component
Scenario Rule 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.