Define CrossChapterActivityQuery interface and result model
epic-multi-chapter-membership-handling-data-layer-task-004 — Create the abstract CrossChapterActivityQuery interface with a fetchActivitiesForContact(contactId) method signature. Define the CrossChapterActivityResult model that represents a merged, deduplicated activity feed entry including the source chapter ID and chapter name for display. Document that implementations must avoid N+1 patterns by using a single JOIN query across contact_chapters and the activities table.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 1 - 540 tasks
Can start after Tier 0 completes
Implementation Notes
Place CrossChapterActivityQuery in lib/domain/queries/cross_chapter_activity_query.dart and CrossChapterActivityResult in lib/domain/models/cross_chapter_activity_result.dart. Consider using the freezed package for CrossChapterActivityResult to get copyWith, equality, and toString for free — this is already the project pattern if freezed is in use. The fetchActivitiesForContact signature should accept optional pagination and filter parameters as a separate object (CrossChapterActivityQueryOptions) to keep the interface stable when task-006 adds pagination — define that options class as an empty placeholder now so callers are not broken later. Add a @immutable annotation to CrossChapterActivityResult.
The doc comment on the interface should also mention that implementations must respect RLS — the authenticated user's JWT is used implicitly by the Supabase client, so the implementation must not bypass auth.
Testing Requirements
Write unit tests in flutter_test for the CrossChapterActivityResult model: (1) verify equality returns true for two instances with the same activityId regardless of other field values; (2) verify copyWith correctly overrides individual fields; (3) verify the model can be serialized to and from a Map
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.