critical priority high complexity backend pending backend specialist Tier 2

Acceptance Criteria

OrganizationUnitRepository is an abstract class (or interface) with a concrete SupabaseOrganizationUnitRepository implementation, enabling test doubles
fetchUnit(String id) returns Future<OrganizationUnit?> — returns null when not found, throws OrganizationUnitNotFoundException when the record exists but is soft-deleted
fetchSubtree(String unitId) calls the get_subtree RPC and returns Future<OrganizationUnitTree> built from the flat list result
fetchChildren(String unitId) calls the get_children RPC and returns Future<List<OrganizationUnit>>
createUnit(OrganizationUnit unit) inserts a new row and returns the created OrganizationUnit with server-generated id and timestamps
updateUnit(OrganizationUnit unit) updates name and unit_type fields and returns the updated OrganizationUnit
softDeleteUnit(String id) sets deleted_at = now() and returns void; throws OrganizationUnitNotFoundException if id does not exist
restoreUnit(String id) sets deleted_at = null and returns the restored OrganizationUnit
All read methods filter WHERE deleted_at IS NULL unless explicitly fetching deleted records
All Supabase errors are caught and mapped to typed domain exceptions: NetworkException, PermissionException, OrganizationUnitNotFoundException, OrganizationUnitConstraintException
Repository is registered as a Riverpod provider and injectable into BLoC/use-case layer
All public methods have correct return types with no dynamic escaping the repository boundary

Technical Requirements

frameworks
Flutter (Dart)
Supabase Flutter SDK (supabase_flutter)
Riverpod (provider registration)
BLoC (consumed by BLoC layer)
apis
Supabase REST API (table: organization_units)
Supabase RPC: get_subtree, get_children, get_ancestors
data models
OrganizationUnit
OrganizationUnitType
OrganizationUnitTree
performance requirements
fetchSubtree must not perform N+1 queries — use a single RPC call that returns the entire subtree
fetchChildren must use a single RPC or filtered query, not multiple round-trips
Repository methods must complete within 2 seconds on a standard mobile connection
security requirements
Never expose raw Supabase error messages to callers — map to domain exceptions with sanitized messages
Ensure all write operations (create, update, softDelete, restore) require the caller to be authenticated — Supabase RLS handles enforcement, but the repository should not attempt writes when the auth session is absent
Do not log personally identifiable information in error messages

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Implementation Notes

Define OrganizationUnitRepository as an abstract class so BLoC and use-case code depends on the abstraction, not the Supabase implementation. This is essential for unit testing the BLoC layer without a real database. Use Riverpod's Provider or AsyncNotifierProvider to expose the repository — follow the existing provider pattern in the project. Error mapping: wrap all Supabase calls in try/catch, inspect the exception type (PostgrestException, SocketException, etc.) and rethrow as domain exceptions.

For soft-delete filter: use .filter('deleted_at', 'is', null) in Supabase queries — do not rely on a database view unless one already exists. For restoreUnit, use .update({'deleted_at': null}) — confirm Supabase Flutter SDK handles null updates correctly (it does as of supabase_flutter 2.x). The repository should not contain business logic (e.g., permission checks beyond what RLS enforces) — keep it a thin data access layer. Do not cache data in the repository; caching belongs in the BLoC or use-case layer if needed.

Testing Requirements

Write unit tests using flutter_test with a mock Supabase client (implement a FakeOrganizationUnitRepository conforming to the abstract interface). Test scenarios: (1) fetchUnit returns OrganizationUnit on success. (2) fetchUnit returns null when Supabase returns empty list. (3) fetchUnit throws OrganizationUnitNotFoundException when record is soft-deleted.

(4) fetchSubtree builds correct OrganizationUnitTree from flat RPC response. (5) createUnit returns created entity with server-assigned id. (6) softDeleteUnit succeeds and subsequent fetchUnit returns null. (7) Supabase network error maps to NetworkException.

(8) Supabase 403 maps to PermissionException. Write at least one integration test against a local Supabase instance that exercises the full round-trip: create → fetch → softDelete → confirm deleted. Use flutter_test for all tests.

Component
Organization Unit Repository
data high
Epic Risks (3)
high impact medium prob technical

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.

medium impact low prob technical

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.

high impact low prob security

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.