Design activity fingerprint schema
epic-organizational-hierarchy-management-duplicate-detection-task-001 — Define the data structure for activity fingerprints used in duplicate detection: participant ID, activity date, activity type, duration, coordinator ID, and unit level. Design the database schema for storing fingerprints and suspected duplicate pairs, including configurable similarity thresholds per organization.
Acceptance Criteria
Technical Requirements
Implementation Notes
Place all three migration SQL files in supabase/migrations/ with timestamp prefix (e.g., 20260329_activity_fingerprints.sql). Use UUIDs generated by gen_random_uuid() for all PKs. For the fingerprint hash, compute it as a SHA-256 of a UTF-8 encoded canonical JSON string of the enabled fields in alphabetical key order — this makes the hash deterministic regardless of field insertion order. Store the hash computation as a Supabase trigger function (BEFORE INSERT/UPDATE on activities) so that fingerprints are always in sync without requiring client-side logic.
The enabled_fields JSONB column in duplicate_detection_config allows each organization to include only the fields that are meaningful for their duplicate detection rules — for example, NHF might weight unit_id heavily while HLF might weight participant_id. Design the schema to be extensible: future fields (e.g., location, event_title) can be added to the fingerprint without a migration by simply adding them to enabled_fields. Document the schema decisions in a schema-decisions.md file adjacent to the migration files, covering: why JSONB for matched_fields (flexibility), why float for similarity_score (future fuzzy matching), and why organization-level thresholds (multi-tenant requirement from NHF's duplicate detection use case in the requirements doc).
Testing Requirements
Database migration tests: apply migration to a Supabase test project and assert all three tables exist with correct column types and constraints using a Dart test script or Supabase SQL query. RLS tests: authenticate as user from org A, attempt INSERT into activity_fingerprints with organization_id of org B, assert 403/RLS error returned. Dart unit tests: ActivityFingerprint.fromJson() correctly parses a fixture JSON map; toJson() produces the expected map; fingerprint hash is deterministic (same inputs always produce same hash). Unit test for hash canonicalization: field order in the JSON string used for hashing is always alphabetical to prevent hash collisions from field reordering.
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.
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.
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.