critical priority high complexity database pending database specialist Tier 1

Acceptance Criteria

A user_chapter_affiliations join table exists with columns: id, user_id, chapter_id, role_in_chapter, is_primary, joined_at, created_at
Database-level constraint enforces maximum 5 chapter affiliations per user (CHECK constraint or trigger)
A Supabase view or PostgREST embedded resource aggregates chapter affiliations as a JSON array on the user record
AppUser Dart model contains a `chapterAffiliations: List<ChapterAffiliation>` field that deserialises correctly from the aggregated response
UserRepository.fetchUsers() from task-001 includes chapter affiliations in the same query (no N+1 queries)
Adding a 6th affiliation returns a clear error: `MaxChapterAffiliationsExceeded` with HTTP 422
Removing an affiliation correctly deletes the join row without affecting the user record
Primary chapter flag: exactly one affiliation per user must have `is_primary = true` (enforced by partial unique index)
RLS on user_chapter_affiliations table ensures admin can only see affiliations for users in their own organisation
NHF's duplicate-reporting concern is addressed: two coordinators in different chapters cannot independently register the same user's activity — the affiliation model makes the user's multi-chapter membership explicit and visible

Technical Requirements

frameworks
Flutter
Dart
Supabase Flutter SDK
apis
Supabase PostgREST — user_chapter_affiliations table
Supabase PostgREST — chapters/local_chapters table
data models
AppUser
ChapterAffiliation
LocalChapter
Organisation
performance requirements
Composite index on (user_id, chapter_id) for fast affiliation lookup
Aggregated chapter data must be returned in a single PostgREST query using embedded resources (no separate round trips)
Constraint check (max 5 affiliations) must not require a full table scan — use a counter or partial index
security requirements
RLS on user_chapter_affiliations must prevent cross-org access
Only org_admin and super_admin roles can modify chapter affiliations — peer_mentor and coordinator roles are read-only
Audit log: all affiliation changes must be recorded in an audit_log table with actor_id and timestamp

Execution Context

Execution Tier
Tier 1

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; (3) adding a 6th affiliation via repository throws MaxChapterAffiliationsExceeded. Integration tests against local Supabase: (1) seed a user with 3 affiliations, fetchUsers returns user with exactly 3 affiliations in list; (2) verify N+1 query prevention — fetching 20 users with affiliations must produce exactly 1 PostgREST request (check via Supabase logs or network interceptor); (3) attempt to insert a 6th affiliation via direct SQL and confirm constraint violation; (4) confirm RLS prevents org_b admin from reading org_a user's affiliations; (5) confirm primary affiliation partial unique index rejects a second is_primary=true row for the same user.

Component
User Account Management Screen
ui high
Epic Risks (3)
medium impact medium prob technical

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.

medium impact medium prob technical

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.

medium impact medium prob integration

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.