Add pagination and date-range filtering to activity query
epic-multi-chapter-membership-handling-data-layer-task-006 — Extend the CrossChapterActivityQuery implementation to support cursor-based pagination (limit + offset or keyset) and optional date-range filtering. This prevents unbounded result sets for contacts with long activity histories spanning multiple chapters. Expose these as optional parameters on the fetchActivitiesForContact method while keeping the single-query, N+1-safe architecture.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 3 - 413 tasks
Can start after Tier 2 completes
Implementation Notes
If the implementation uses PostgREST embedding, apply .range() on the embedded activities table using the referencedTable parameter. For date filters, use .gte('activity_date', fromDate.toIso8601String(), referencedTable: 'activities') and .lte('activity_date', toDate.toIso8601String(), referencedTable: 'activities'). If using an RPC stored procedure, add p_limit INT, p_offset INT, p_from_date TIMESTAMPTZ, p_to_date TIMESTAMPTZ parameters with NULL defaults and apply them conditionally in the SQL with CASE WHEN p_from_date IS NOT NULL THEN activity_date >= p_from_date ELSE TRUE END. For the BLoC layer consuming this query, consider implementing an infinite scroll pattern where the BLoC passes an incrementing offset to successive calls.
Document the recommended page size (50) in the CrossChapterActivityQueryOptions class-level doc comment.
Testing Requirements
Write unit tests with a mocked Supabase client: (1) verify that when options has limit=10, offset=0 the query chain includes .range(0, 9); (2) verify fromDate produces a .gte('activity_date', ...) call; (3) verify toDate produces a .lte('activity_date', ...) call; (4) verify combining fromDate + toDate produces both filters; (5) verify default options (null) results in limit=50, offset=0, no date filters. Write integration tests against a local Supabase instance: seed 60 activities, request page 1 (offset 0, limit 50) and page 2 (offset 50, limit 50), assert no duplicates between pages and total coverage; seed activities in 2024 and 2025, filter by fromDate=2025-01-01 and verify only 2025 activities are returned.
The Cross-Chapter Activity Query must avoid N+1 fetches across chapters. If naively implemented as a per-chapter loop, it will cause severe performance degradation for contacts affiliated with 5 chapters on poor mobile connections.
Mitigation & Contingency
Mitigation: Design the query as a single PostgREST join of contact_chapters and activities on contact_id from the start. Add a query performance test with 5 affiliations and 100+ activities to the integration test suite and enforce a maximum execution time threshold.
Contingency: If a performance regression is detected post-merge, introduce a Supabase RPC function (stored procedure) to move the join server-side, bypassing any client-side N+1 pattern.
If the Duplicate Warning Event Logger write fails silently (network error, RLS denial), audit entries will be missing from the Bufdir compliance record without the user being aware.
Mitigation & Contingency
Mitigation: Implement the logger with a local fallback queue: if the Supabase write fails, persist the event locally and retry on next launch. Log all failures to a verbose output channel.
Contingency: Add a reconciliation job that compares locally queued events to Supabase entries and re-submits any gaps. Provide a data export of the local queue for manual audit if reconciliation fails.
Two coordinators simultaneously adding the 5th chapter affiliation for the same contact could bypass the maximum enforcement check if both reads occur before either write completes.
Mitigation & Contingency
Mitigation: Enforce the 5-affiliation maximum as a database-level constraint (CHECK + trigger or RPC with a FOR UPDATE lock) rather than relying solely on application-layer validation.
Contingency: If a constraint violation is detected in production, run a corrective query to end the most recently created excess affiliation and notify the relevant coordinator.