Composable filter query builder
epic-admin-portal-user-management-task-003 — Build the composable filter query builder that supports simultaneous filtering by role, chapter, account status, and certification state against Supabase RLS-enforced views. Filters must be combinable in any combination and applied server-side to avoid loading all users into memory.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 2 - 518 tasks
Can start after Tier 1 completes
Implementation Notes
Model FilterParams as an immutable Dart class with copyWith. Each filter dimension should be nullable — null means 'no constraint'. The buildQuery method should chain `.filter()` / `.eq()` / `.contains()` calls on the Supabase query builder only when the corresponding FilterParams field is non-null. For the chapter dimension, if the Supabase column stores chapters as a JSONB array or a Postgres array, use `.contains([chapterId])` (PostgREST @> operator) to match users who belong to at least one of the selected chapters — avoid client-side array intersection.
Keep the filter builder as a pure function or static method with no side effects so it is trivially testable. Do not embed this logic in the BLoC; the BLoC delegates to this builder. Coordinate with task-001/002 owners to confirm the exact Supabase view name, column names, and array column type before implementation.
Testing Requirements
Unit tests (flutter_test): test FilterParams equality, test buildQuery output string for each of the 4 filter types individually, test all-filters-combined, test null/empty FilterParams produces no filter clauses. Integration tests against a seeded Supabase test project: confirm row counts match expected intersections for each filter combination. Edge-case tests: user in 3 chapters — appears once; certification_state null treated as 'uncertified'; deactivated user not returned when status filter is 'active'. Minimum 90% branch coverage on the query builder module.
Displaying NHF users with membership in up to 5 local chapters in a flat list view without duplicating entries requires a non-trivial aggregation query. Incorrect query design could result in duplicated user rows or missing chapter affiliations, confusing admins and causing incorrect role assignments.
Mitigation & Contingency
Mitigation: Design the user list query to GROUP BY user_id and aggregate chapter affiliations as an array field. Use AdminRepository's typed models to surface this aggregated structure to the UI. Validate with a test dataset containing users in 5 chapters.
Contingency: If aggregation query complexity proves too high for real-time filtering, implement a separate multi-chapter affiliation fetch triggered only when a specific user row is expanded, reducing query complexity for the base list.
Composable multi-dimensional filters (role + chapter + status + certification state) applied server-side against an org with 2,000+ users may produce slow queries, particularly when filtering by certification state requires joining an additional table.
Mitigation & Contingency
Mitigation: Ensure the relevant filter columns (role, status, chapter_id, certification_expiry) are indexed in Supabase. Use cursor-based pagination rather than OFFSET to maintain consistent performance at high page numbers. Profile filter query combinations against a large dataset during development.
Contingency: If multi-filter performance degrades in production, introduce a denormalised search index table updated on user status changes, allowing the list query to filter from a single table.
Deactivating a user account that has ongoing activity assignments, open expense claims, or active chapter affiliations may leave orphaned records or break downstream workflows if the deactivation does not trigger correct cascade handling.
Mitigation & Contingency
Mitigation: Define and document the expected state of each dependent record type on user deactivation before implementing the toggle. Implement deactivation as a UserManagementService operation that checks for and warns about open dependencies before persisting. Write integration tests covering each dependency type.
Contingency: If orphaned record issues are discovered post-launch, provide an admin-accessible reconciliation view that surfaces users with inconsistent dependency states and allows manual resolution without requiring a code deploy.