Configure chapter-scoped RLS policies for duplicate tables
epic-duplicate-activity-detection-foundation-task-009 — Write and apply RLS policies on the activities table (for the duplicate_reviewed column) and the coordinator_duplicate_queue view scoped to chapter membership. Coordinators may only read and update records belonging to their chapter. Peer mentors may only read their own duplicate_reviewed status. Include policy migration scripts and verify policy enforcement with test role impersonation queries in Supabase.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 3 - 413 tasks
Can start after Tier 2 completes
Implementation Notes
Start by mapping out the existing RLS policies on the activities table to avoid conflicts. For coordinator chapter scoping, create a stable PostgreSQL function get_user_chapter_ids(user_id UUID) RETURNS SETOF UUID that queries the chapter_members table — this function can be reused across multiple policies and keeps USING expressions readable. For the coordinator_duplicate_queue view, if Supabase doesn't support RLS directly on views, add a WHERE chapter_id = ANY(get_user_chapter_ids(auth.uid())) condition to the view definition itself, scoping it at the view level rather than relying on a separate policy. Test each policy with role impersonation before committing the migration script to avoid locked-out states in the dev database.
Include a rollback script alongside each migration.
Testing Requirements
Write SQL test scripts (run via Supabase SQL editor or pgTAP) that: (1) impersonate a coordinator from chapter A and confirm SELECT returns only chapter A queue entries, (2) impersonate a coordinator from chapter A and confirm UPDATE of chapter B's duplicate_reviewed fails with permission denied, (3) impersonate a peer mentor and confirm they can read their own activity's duplicate_reviewed field, (4) impersonate a peer mentor and confirm UPDATE of duplicate_reviewed fails. Document each test query in the migration PR. Additionally, write a Dart integration test in flutter_test that exercises the repository against a local Supabase instance with multiple test users to verify the same RLS behaviour from the app perspective.
The `check_activity_duplicates` RPC may not meet the 500ms target on production-scale data if the composite index is not applied correctly or if Supabase RLS evaluation adds unexpected overhead, causing the duplicate check to noticeably delay activity submission.
Mitigation & Contingency
Mitigation: Write the RPC with an explicit EXPLAIN ANALYZE in development against a seeded dataset representative of a large chapter (10,000+ activities). Pin the index hint in the RPC body and verify the query plan in Supabase's SQL editor before merging.
Contingency: If the 500ms target cannot be met with the RPC approach, introduce an async post-submit check pattern where the activity is saved first and the duplicate warning is surfaced as a follow-up notification, preserving submission speed at the cost of real-time blocking UX.
RLS policies for the coordinator_duplicate_queue view must correctly scope results to the coordinator's chapters. Incorrect policies could expose duplicate records from other chapters (privacy violation) or hide legitimate duplicates (functional regression).
Mitigation & Contingency
Mitigation: Write explicit integration tests that verify RLS behaviour using at least three distinct coordinator + chapter combinations, including a peer mentor belonging to two chapters. Use Supabase's built-in RLS testing utilities.
Contingency: If RLS proves too complex for the queue view, move the chapter-scoping filter into the DuplicateQueueRepository query layer at the application level, trading database-enforced isolation for application-enforced scoping with full test coverage.
Adding the duplicate_reviewed column to the activities table and the composite index requires a migration against a live table. If the migration locks the table for an extended period, it could disrupt active coordinators submitting activities.
Mitigation & Contingency
Mitigation: Use PostgreSQL's `CREATE INDEX CONCURRENTLY` to avoid table lock. Add the duplicate_reviewed column with a DEFAULT false so no backfill update lock is required. Schedule the migration during a low-traffic window.
Contingency: If concurrent index creation fails or takes too long, fall back to a smaller partial index scoped to the last 90 days of activities, then expand it incrementally.