ProxyDuplicateDetector: triple-key duplicate query

epic-bulk-and-proxy-registration-services-task-011 — Implement the core query in ProxyDuplicateDetector that checks existing activity records for each candidate mentor on the combination of (peer_mentor_id, activity_type, date). Accept a list of MentorActivityCandidate objects so both single-proxy and bulk flows share the same query path. Use a single Supabase query with an IN clause on peer_mentor_ids to avoid N+1 calls when processing bulk registrations.

high priority medium complexity backend pending backend specialist Tier 2

Acceptance Criteria

checkDuplicates(candidates: List<MentorActivityCandidate>) is the single public method, accepting both single-element lists (proxy flow) and multi-element lists (bulk flow)

Issues exactly one Supabase query regardless of candidate list size — uses .in_('peer_mentor_id', mentorIds) combined with activity_type and date filters

Triple-key match logic: a record is considered a duplicate only when peer_mentor_id AND activity_type AND date all match a candidate in the input list

Returns a raw List<DuplicateQueryResult> (or equivalent internal DTO) for further processing by the formatter in task-012

Passing an empty candidates list returns an empty result list immediately without issuing any Supabase query

Date comparison is date-only (not datetime) — time-of-day differences on the same calendar date are still duplicates

Query selects at minimum: id (existing record UUID), peer_mentor_id, activity_type, date, recorded_by_user_id — enough for the warning formatter to build MentorDuplicateWarning

Method is async and returns Future<List<DuplicateQueryResult>>

Supabase query errors are propagated as typed exceptions (not swallowed), allowing the caller to distinguish network failures from clean empty results

MentorActivityCandidate is a value object with at minimum: mentorId, activityType, date fields

Technical Requirements

frameworks

Flutter

Dart

supabase_flutter

apis

Supabase PostgREST API — .from('activities').select(...).in_('peer_mentor_id', ids).eq('activity_type', type).eq('date', date)

data models

MentorActivityCandidate

DuplicateQueryResult

ActivityRecord

performance requirements

Single database round-trip for any batch size using IN clause — no iteration with individual queries

Query should complete in under 500ms for batches up to 50 mentors on a typical mobile connection

Select only the columns needed for duplicate warning construction — avoid SELECT *

security requirements

Supabase RLS must restrict the query to records within the coordinator's organisation — do not rely on application-layer filtering alone

peer_mentor_ids in the IN clause must be validated as non-empty UUIDs before the query is issued

No raw SQL string concatenation — use the supabase_flutter typed query builder exclusively

Execution Context

Execution Tier

Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

View Full Execution Plan

Implementation Notes

The key challenge is constructing a single Supabase query that efficiently handles the triple-key match across multiple mentors. Since Supabase PostgREST does not support complex OR conditions with multiple column combinations natively in a single filter call, use the following approach: query with .in_('peer_mentor_id', mentorIds).eq('activity_type', activityType).gte('date', startOfDay).lte('date', endOfDay) — this works when all candidates share the same activity_type and date (the typical bulk registration scenario). For heterogeneous batches (different types or dates per mentor), consider filtering in-memory after fetching by mentorId IN clause, or make separate queries per unique (activity_type, date) combination — document this tradeoff in code comments. MentorActivityCandidate should be an immutable Dart class (freezed or const constructor).

DuplicateQueryResult is an internal DTO — keep it package-private. For date-only comparison, normalise to ISO 8601 date string (yyyy-MM-dd) before querying.

Testing Requirements

Unit tests are handled in task-013. For implementation testability, the Supabase client must be injected as a constructor dependency so it can be replaced with a mock in tests. The query construction logic should be isolated enough that tests can verify the correct filters are applied (mentorIds, activityType, date) without a live database. Consider extracting the query builder into a private method (_buildDuplicateQuery) so it can be tested in isolation.

Integration tests against a real Supabase test project are covered in task-014.

Component

Proxy Duplicate Detector

service medium

Dependencies (1)

Implement the ProxyActivityRepository Dart class that extends or wraps the existing ActivityRepository. Override the insert and query methods to carry the recorded_by_user_id field through every Supabase call. Ensure all existing activity repository tests still pass and that the dual-identity contract is enforced at the type level via a ProxyActivityRecord model distinct from the base ActivityRecord. epic-bulk-and-proxy-registration-services-task-002

Epic Risks (2)

high impact low prob security

The Proxy Registration Service must verify that the coordinator has a legitimate assignment relationship with the target peer mentor before creating a record. If this check is implemented only in application code and not enforced at the DB/RLS level, a compromised or buggy client could bypass it by calling the Supabase endpoint directly, creating fraudulent proxy records for arbitrary peer mentors.

Mitigation & Contingency

Mitigation: Implement permission validation at two levels: (1) application-layer check in Proxy Registration Service that queries the assignments table before constructing the payload, and (2) RLS policy on the activities table that restricts INSERT to rows where recorded_by_user_id matches the authenticated user AND peer_mentor_id is in the set of peer mentors assigned to that coordinator. The RLS policy is the authoritative guard; the service-layer check provides early user-facing feedback.

Contingency: If RLS policy implementation is blocked by Supabase plan constraints, implement a Supabase Edge Function as a proxy endpoint that enforces the permission check server-side before forwarding to the DB. Disable direct client inserts entirely for proxy activities.

medium impact medium prob technical

For a bulk session with 30 selected peer mentors, the Proxy Duplicate Detector must query existing activities for each mentor. If implemented as 30 sequential Supabase queries, round-trip latency could make the bulk confirmation screen feel slow (>3s), degrading coordinator experience and potentially causing timeouts.

Mitigation & Contingency

Mitigation: Implement the duplicate check as a single Supabase query using an IN clause on peer_mentor_id combined with the activity_type and date filters, returning all potential duplicates for the entire batch in one network round-trip. Group results client-side by mentor ID to produce the per-mentor warning structure.

Contingency: If the single-query approach returns too much data for very large chapters, add a database index on (peer_mentor_id, activity_type, date) and profile query time. If still insufficient, accept a short loading state on the confirmation screen with a progress indicator rather than pre-loading duplicates before navigation.

Quick Links

All Tasks Execution Plan