Build Organization Unit Repository
epic-organizational-hierarchy-management-admin-portal-task-003 — Implement the high-complexity Supabase repository for organization unit data. Support hierarchical queries (ancestors, descendants, siblings), node CRUD, member count aggregation per node, and search by name/type. Integrate with hierarchy cache and support recursive CTE queries for tree traversal.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 1 - 540 tasks
Can start after Tier 0 completes
Implementation Notes
Implement recursive tree traversal via PostgreSQL `WITH RECURSIVE` CTEs wrapped in Supabase RPC functions (e.g., `get_ancestors(unit_id uuid)`, `get_descendants(unit_id uuid)`). Call these via `supabase.rpc('get_ancestors', params: {'unit_id': id})` from Dart — do not attempt to build recursive logic client-side. Use a `ltree` extension in PostgreSQL if available on your Supabase tier for efficient path-based queries; otherwise use the adjacency list + CTE approach. For getMemberCountPerNode, use a single `select unit_id, count(*) from unit_assignments where unit_id = ANY($1) group by unit_id` RPC to avoid N+1.
Cache key strategy: use `'unit:$unitId'` for individual nodes and `'descendants:$unitId'` for subtree results. On any write (create/update/delete), invalidate both the node key and any ancestor/subtree cache keys.
Testing Requirements
Unit tests with MockSupabaseClient for all 8 repository methods. Test boundary conditions: empty tree, single-node tree, maximum-depth tree (5 levels), deleteUnit with existing children (expect typed error), getMemberCountPerNode with empty input list. Integration tests (local Supabase) for recursive CTE correctness: seed a 3-level tree, assert getAncestors and getDescendants return correct node sets. Test cache invalidation: assert that a read after createUnit fetches fresh data.
Test searchUnits with case-insensitive and partial-match queries.
If the AccessScopeService and the Supabase RLS policies use different logic to determine accessible units, a coordinator could see data in the client that RLS blocks server-side, causing confusing empty states, or worse, RLS could block data the scope service declares accessible.
Mitigation & Contingency
Mitigation: Define the canonical scope computation in a single Supabase Postgres function shared by both the RLS policies and the RPC endpoint called by AccessScopeService. The client-side service calls this RPC rather than reimplementing the logic, ensuring a single source of truth.
Contingency: Add integration tests that execute the same access decision through both the RLS policy path and the AccessScopeService path and assert identical results. Use these as regression guards in the CI pipeline.
When a user switches active chapter via the ChapterSwitcher, widgets that are already built may not receive the context-change event if they subscribe incorrectly to the ActiveChapterState BLoC, leading to stale data being displayed under the new chapter context.
Mitigation & Contingency
Mitigation: Use Riverpod's ref.watch on the active chapter provider at the root of each scoped data subtree rather than at individual leaf widgets. Trigger a global data refresh by invalidating all scoped providers when the chapter changes.
Contingency: Add an app-level chapter-change listener that forces a full navigation stack reset to the home screen on chapter switch, guaranteeing all widgets rebuild from scratch with the new context. Accept the UX cost of navigation reset for correctness.
Non-technical organization administrators may find the hierarchy management interface too complex for the structural changes they need to make frequently (e.g., chapter renaming, coordinator reassignment), leading to low adoption and continued reliance on manual processes.
Mitigation & Contingency
Mitigation: Conduct usability testing with at least one NHF administrator before finalizing the admin portal screen layout. Prioritize the most common operations (rename, reparent, add child) as primary actions in the UI. Include inline help text and confirmation dialogs with plain-language descriptions of consequences.
Contingency: Provide a simplified 'quick edit' mode that exposes only the three most common operations (rename, deactivate, add child) and hides advanced structural operations behind an 'Advanced' toggle.