Implement check_activity_duplicates Supabase RPC

epic-duplicate-activity-detection-foundation-task-002 — Create the PostgreSQL function check_activity_duplicates as a Supabase RPC. The function accepts peer_mentor_id, activity_type_id, date, and an optional exclusion ID, and returns candidate rows ordered by similarity score. Must respect chapter-scoped RLS policies so coordinators can only query within their chapter membership. Validate query plan uses the composite index.

critical priority medium complexity database pending database specialist Tier 1

Acceptance Criteria

Function signature: check_activity_duplicates(p_peer_mentor_id uuid, p_activity_type_id uuid, p_date date, p_exclude_id uuid DEFAULT NULL) RETURNS TABLE(...) with columns: activity_id uuid, peer_mentor_id uuid, activity_type_id uuid, date date, status text, similarity_score numeric

Function is defined as SECURITY DEFINER with explicit GRANT EXECUTE to authenticated role only, ensuring RLS context is respected

Chapter-scoped access control: function filters results to activities belonging to chapters where the calling coordinator is a member, enforced via a subquery joining coordinator_chapter_memberships

Optional exclusion: when p_exclude_id is provided, the activity with that ID is excluded from the result set

Results are ordered by similarity_score DESC so the most likely duplicates appear first

Similarity score is calculated based on matching fields (same peer_mentor_id + activity_type_id + date = high score; partial matches = lower score) using a deterministic numeric formula documented in a SQL comment

EXPLAIN ANALYZE on the function confirms use of the composite index from task-001 (Index Scan node present)

Function returns an empty result set (not an error) when no candidates are found

Function is exposed as a Supabase RPC endpoint and callable via the Dart Supabase client using supabase.rpc('check_activity_duplicates', params: {...})

Migration file for the function is idempotent: uses CREATE OR REPLACE FUNCTION

Function is covered by at least 3 pgTAP or manual SQL test cases: no candidates, exact duplicate found, exclusion ID filters correctly

Technical Requirements

frameworks

Supabase CLI

PostgreSQL

pgTAP (optional)

apis

Supabase RPC

data models

activities

coordinator_chapter_memberships

performance requirements

Function executes in under 500ms on datasets of 10,000+ activity rows

Query plan must use the composite index (peer_mentor_id, activity_type_id, date, status) — confirm via EXPLAIN ANALYZE

security requirements

SECURITY DEFINER with GRANT EXECUTE to authenticated only — never to anon

Chapter-scoped filtering enforced inside the function; coordinators cannot retrieve activities outside their chapter memberships

Function must not expose activity data for soft-deleted or archived activities unless explicitly in scope

Execution Context

Execution Tier

Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

View Full Execution Plan

Implementation Notes

Define the similarity score as a deterministic integer or decimal: full match on all three key fields (peer_mentor_id + activity_type_id + date) = 1.0; partial matches (e.g., same peer_mentor_id + activity_type_id but different date within ±7 days) = 0.7; same peer_mentor_id + date only = 0.4. Document the scoring formula in a SQL block comment above the function. Use SECURITY DEFINER so the function runs with elevated privileges but filter chapter membership inside the function body using auth.uid() to maintain logical row-level security. Avoid using SECURITY INVOKER if RLS on activities does not cover the coordinator's view adequately.

The function should use a CTE for readability: first CTE resolves the coordinator's chapter memberships, second CTE fetches candidate activities, third applies scoring. This structure also allows the query planner to optimise each CTE independently.

Testing Requirements

Write SQL test scripts (or pgTAP tests) covering: (1) call with no matching activities returns empty set; (2) call with exact duplicate present returns that activity with highest similarity_score; (3) call with p_exclude_id equal to the duplicate's ID returns empty set; (4) call by a coordinator not in the activity's chapter returns empty set (RLS enforcement); (5) EXPLAIN ANALYZE output shows Index Scan on idx_activities_peer_mentor_type_date_status. Run tests via `supabase db reset && psql -f tests/rpc/check_activity_duplicates_test.sql`. Also test Dart integration: call via supabase.rpc() in an integration test environment and assert the returned list maps correctly to DuplicateCandidate models.

Component

Duplicate Check Repository

data medium

Dependencies (1)

Write and apply a Supabase migration script that creates the composite index on (peer_mentor_id, activity_type_id, date, status) on the activities table. The index must enable sub-500ms query performance on the check_activity_duplicates RPC even against large chapter datasets. Include index naming conventions and verify the migration is idempotent. epic-duplicate-activity-detection-foundation-task-001

Epic Risks (3)

high impact medium prob technical

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.

high impact medium prob security

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.

medium impact low prob dependency

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.

Quick Links

All Tasks Execution Plan