Multi-chapter affiliation data model
epic-admin-portal-user-management-task-002 — Design and implement the data model supporting NHF's requirement that a single user may belong to up to 5 local chapters simultaneously. Ensure queries return one user record with aggregated chapter affiliations rather than duplicate rows, and expose a clean data contract for UI consumption.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 1 - 540 tasks
Can start after Tier 0 completes
Implementation Notes
Schema design: `user_chapter_affiliations(id UUID PK, user_id UUID FK users.id, chapter_id UUID FK chapters.id, role_in_chapter TEXT, is_primary BOOLEAN DEFAULT false, joined_at TIMESTAMPTZ, created_at TIMESTAMPTZ DEFAULT now())`. Enforce max-5 with a trigger: `CREATE TRIGGER enforce_max_chapter_affiliations BEFORE INSERT ON user_chapter_affiliations FOR EACH ROW EXECUTE FUNCTION check_max_affiliations()`. For the PostgREST query, use Supabase's embedded resource syntax: `.select('*, chapter_affiliations:user_chapter_affiliations(*, chapter:chapters(id, name, region))')`. The `ChapterAffiliation` Dart model should include `chapterName` and `regionName` for display — avoid separate lookups.
NHF's duplicate-reporting concern (section 3.2 of likeperson.md) means the UI layer must display all chapter memberships prominently in the user detail screen so coordinators can see which other chapters claim the user. The data model here is the foundation for that display.
Testing Requirements
Unit tests: (1) ChapterAffiliation.fromJson() correctly parses all fields including nested chapter name; (2) AppUser.fromJson() with embedded affiliations array deserialises to correct List
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.