🏠 Overview › ✅ Implementation Tasks ›✅ epic-organizational-hierarchy-management-duplicate-detection-task-008

Implement RLS policies for duplicate queue

epic-organizational-hierarchy-management-duplicate-detection-task-008 — Configure Row Level Security policies on the suspected_duplicates table so that admin users can read and update all records for their organization, coordinators can read records involving their own submitted activities, and no user can delete records (only mark as resolved). Verify that cross-organization data leakage is impossible.

high priority medium complexity infrastructure pending infrastructure specialist Tier 2

Acceptance Criteria

Admin users (role = 'admin') can SELECT all suspected_duplicates rows where org_id matches their own organization
Admin users can UPDATE the status/resolution fields on any row within their organization
Coordinator users can SELECT only rows where activity_a_id or activity_b_id references an activity they submitted (submitted_by = auth.uid())
Coordinators cannot UPDATE suspected_duplicate rows — read-only access enforced at DB level
No user role (admin, coordinator, peer_mentor) can DELETE rows from suspected_duplicates — DELETE policy is explicitly denied
A user from organization A receives zero rows when querying records belonging to organization B — cross-org leakage test passes
Service role (used by Edge Functions/triggers) bypasses RLS and can INSERT and UPDATE freely
All RLS policies are enabled via ALTER TABLE suspected_duplicates ENABLE ROW LEVEL SECURITY
Policy names are descriptive and follow the pattern: suspected_duplicates_{role}_{operation}
Supabase dashboard Policy viewer shows all 5+ policies correctly attached to the table
Attempting a direct REST API call with a coordinator JWT for another org's record returns 0 rows (not an error, empty result)

Technical Requirements

frameworks
Supabase RLS (PostgreSQL Row Level Security)
apis
Supabase Auth (auth.uid(), auth.jwt())
Supabase REST API for policy verification
data models
suspected_duplicates
activities
organization_units
user_profiles
performance requirements
RLS policies must use indexed columns (org_id, submitted_by) to avoid sequential scans
Policy expressions should reference auth.uid() directly — avoid subqueries that hit unindexed columns
Confirm query plan for admin SELECT uses index scan on org_id (EXPLAIN ANALYZE)
security requirements
RLS must be ENABLED on the table before any policies are created
FORCE ROW LEVEL SECURITY must also be set so table owner cannot bypass policies
Service role key must never be exposed to client-side Flutter code
All policy definitions peer-reviewed before deployment to production
Cross-organization isolation verified with a dedicated SQL test script using two separate JWT tokens

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Implementation Notes

Use Supabase's JWT claims to drive RLS. The org_id and role should be embedded in the JWT via a custom claims hook (database hook or Supabase Auth hook). Policy pattern for admin read: `(auth.jwt() ->> 'org_id')::uuid = org_id AND (auth.jwt() ->> 'role') = 'admin'`. For coordinator read: use EXISTS subquery joining to activities table on submitted_by = auth.uid().

For the no-delete policy, simply create no DELETE policy — absence of a policy denies by default when RLS is enabled. Use `FORCE ROW LEVEL SECURITY` to prevent the table owner from accidentally bypassing. Test policies in Supabase SQL editor using `SET LOCAL role = authenticated; SET LOCAL request.jwt.claims = '...';` before running queries.

Testing Requirements

Write a SQL-level test script (executed via Supabase CLI or pg_tap) that: (1) creates two test organizations and two JWT tokens with differing org_id claims, (2) asserts admin from org A gets rows only for org A, (3) asserts coordinator from org A sees only their own submitted activity rows, (4) confirms DELETE attempt from any role returns permission denied, (5) confirms service role can INSERT and UPDATE without restriction. Additionally, write a Flutter integration test that calls the Supabase client with a coordinator session and verifies the returned list contains only the coordinator's own records.

Document all test cases in a markdown table.

Component
Duplicate Activity Detector
infrastructure high
Dependencies (1)
Create the Supabase database table for storing flagged duplicate activity pairs, including fields for both activity IDs, similarity score, detection timestamp, review status (pending/confirmed_duplicate/false_positive), and reviewer ID. Add indexes for efficient querying by organization and review status. epic-organizational-hierarchy-management-duplicate-detection-task-003
Epic Risks (3)
medium impact high prob technical

Fingerprint-based similarity matching may produce high false-positive rates for common activity types (e.g., weekly group sessions with the same participants), causing alert fatigue among coordinators and undermining trust in the detection system.

Mitigation & Contingency

Mitigation: Start with conservative, high-confidence thresholds (exact peer mentor match + same date + same activity type) before adding looser fuzzy matching. Allow NHF administrators to tune thresholds based on observed false-positive rates. Log all detection decisions for retrospective threshold calibration.

Contingency: Introduce a snooze mechanism allowing coordinators to dismiss false positives for a configurable period. Track dismissal rates per activity type and automatically raise the similarity threshold for activity types with high dismissal rates.

medium impact medium prob technical

A database trigger on the activities insert path adds synchronous overhead to every activity registration. For HLF peer mentors with 380 annual registrations or coordinators doing bulk proxy registration, this could create perceptible latency or lock contention.

Mitigation & Contingency

Mitigation: Implement the trigger as a DEFERRED constraint trigger (fires after the transaction commits) or replace it with a LISTEN/NOTIFY pattern that queues detection work asynchronously via an Edge Function, completely decoupling detection from the registration write path.

Contingency: Disable the synchronous trigger entirely and rely solely on the scheduled Edge Function for batch detection. Accept a detection delay of up to the scheduling interval (e.g., 15 minutes) in exchange for zero impact on registration latency.

medium impact medium prob dependency

The duplicate detection logic must be validated and approved by NHF before go-live, including agreement on threshold values and the review workflow. NHF stakeholder availability for sign-off may delay this epic's release independently of technical readiness.

Mitigation & Contingency

Mitigation: Gate the feature behind the NHF-specific feature flag so technical deployment can proceed independently of business approval. Involve an NHF administrator in threshold calibration sessions during QA, reducing the formal sign-off surface to policy and workflow rather than technical details.

Contingency: Release the detection system in 'silent mode' — flagging duplicates internally without surfacing notifications to coordinators — until NHF approves the workflow. Use the silent period to collect real data on false-positive rates and refine thresholds before activating notifications.

Quick Links
All Tasks Execution Plan