Implement Supabase RPC for recursive subtree queries
epic-organizational-hierarchy-management-foundation-task-003 — Write and deploy Supabase PostgreSQL RPC functions for recursive hierarchy queries: get_subtree(unit_id) using a recursive CTE, get_ancestors(unit_id) for breadcrumb paths, and get_children(unit_id) for direct children. These RPCs must handle soft-deleted nodes and return typed result sets compatible with Dart model generation.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 1 - 540 tasks
Can start after Tier 0 completes
Implementation Notes
Use WITH RECURSIVE syntax for get_subtree and get_ancestors. For get_subtree, start the anchor member with the seed unit and the recursive member joins on parent_id = id. For get_ancestors, reverse the join direction (id = parent_id of the previous row). Attach a depth counter in the recursive member (depth + 1) to enable ordered breadcrumb display.
For soft-delete handling: filter deleted_at IS NULL in both the anchor and recursive members to prevent traversal into or through deleted subtrees. Decide and document whether a soft-deleted intermediate node causes its subtree to be excluded entirely or just that node — the recommended approach for organizational hierarchy is to exclude the entire subtree (fail-closed). The depth column in get_subtree is useful for indented tree rendering in the Flutter UI. For get_children, a simple non-recursive query suffices (WHERE parent_id = p_unit_id AND deleted_at IS NULL), but wrapping it as an RPC provides a consistent API surface.
Ensure the return type column names are stable — changing them later will break Dart fromJson deserialization across all clients. Add inline SQL comments explaining the recursive CTE structure for future maintainers.
Testing Requirements
Write integration tests against a local Supabase instance with a seeded hierarchy (at minimum: 1 national, 3 regions, 9 chapters, 2 soft-deleted nodes). Test scenarios: (1) get_subtree on national root returns all 13 active nodes. (2) get_subtree on a region returns that region and its 3 chapters. (3) get_subtree on a chapter returns only that chapter.
(4) get_subtree excludes soft-deleted nodes even when they are intermediate nodes (their children must also be excluded or handled consistently — document the chosen behavior). (5) get_ancestors on a chapter returns [national, region, chapter] in depth-ascending order. (6) get_ancestors on national returns only national. (7) get_children on national returns exactly 3 regions.
(8) get_children on a chapter returns empty result. (9) All results deserialize cleanly into Dart OrganizationUnit.fromJson without field mapping errors. Run tests with flutter_test + a Dart Supabase client pointed at local instance.
Recursive CTE queries for large hierarchies (1,400+ nodes) may exceed Supabase query timeouts or produce unacceptably slow responses, degrading tree load time beyond the 1-second target.
Mitigation & Contingency
Mitigation: Implement Supabase RPC functions for subtree fetches rather than client-side recursive calls. Use materialized path or closure table as a supplemental index for depth-first traversal. Benchmark with realistic NHF data volumes during development.
Contingency: Fall back to a pre-computed flat unit list stored in the hierarchy cache with client-side tree reconstruction, trading freshness for speed. Add a background refresh job to keep the cache warm.
Concurrent writes from multiple admin sessions could cause cache staleness, leading to stale tree views and incorrect ancestor path computations that corrupt aggregation results.
Mitigation & Contingency
Mitigation: Use optimistic versioning on cache entries with a short TTL (5 minutes) as a safety net. Subscribe to Supabase Realtime on the organization_units table to push invalidation events to all connected clients.
Contingency: Provide a manual 'Refresh Hierarchy' action in the admin portal that forces a full cache bust, and display a staleness warning banner when the cache age exceeds the TTL.
Persisting the flat unit list to local storage may expose organization structure data if the device is compromised or the storage is not properly encrypted, violating data protection requirements.
Mitigation & Contingency
Mitigation: Use flutter_secure_storage (AES-256 backed by Keychain/Keystore) for the local unit list cache rather than SharedPreferences. Include only unit IDs, names, and types — no member PII.
Contingency: Disable local-storage persistence entirely and rely on in-memory cache only. Accept the trade-off of no offline hierarchy access for the security guarantee.