Cross-Chapter Activity Query

This is a high-complexity data component with a targeted but critical scope — it exists solely to serve the duplicate detection service. Development requires careful query optimization to avoid N+1 fetch patterns across the contact_chapters and activities tables, which means a developer with strong SQL and Supabase PostgREST experience should be assigned. Testing must cover edge cases like contacts affiliated with many chapters and large activity histories, requiring realistic test data seeding. The component has a single upstream dependency on the supabase-contact-chapter-adapter, so that adapter must be stable before integration testing begins.

Performance benchmarks should be established early since slow queries here directly degrade the activity submission UX.

This component executes a join-based query across contact_chapters and activities tables, filtered by contactId with an optional DateRange parameter. It is consumed exclusively by the duplicate detection service and must return a unified List regardless of which chapter originated each activity. The primary implementation challenge is avoiding N+1 fetches — the query must use a single Supabase PostgREST join (e.g., select with embedded resource or RPC) rather than iterating chapters. It depends on supabase-contact-chapter-adapter for raw query execution.

The fetchActivitiesByTypeForContact interface supports targeted lookups for specific activity types on a given date, optimizing the common duplicate check path. Both mobile and backend execution contexts must be supported, so query construction must be environment-agnostic and rely on the adapter layer for connection management.