Implement UnitAssignmentRepository with uniqueness enforcement

epic-organizational-hierarchy-management-foundation-task-007 — Implement the UnitAssignmentRepository with methods: fetchAssignmentsForUser(userId), fetchAssignmentsForUnit(unitId), assignUserToUnit(userId, unitId, isPrimary), revokeAssignment(assignmentId), and setPrimaryAssignment(assignmentId). Enforce uniqueness and primary-assignment constraints at query level before touching the database. Return typed models and domain exceptions on constraint violations.

critical priority medium complexity backend pending backend specialist Tier 3

Acceptance Criteria

UnitAssignmentRepository is an abstract class with a concrete SupabaseUnitAssignmentRepository implementation

fetchAssignmentsForUser(String userId) returns Future<List<UnitAssignment>> for all active (revokedAt IS NULL) assignments for the given user

fetchAssignmentsForUnit(String unitId) returns Future<List<UnitAssignment>> for all active assignments for the given unit

assignUserToUnit(String userId, String unitId, {bool isPrimary = false}) first checks for an existing active assignment for the same user+unit pair; if one exists, throws DuplicateAssignmentException instead of inserting

assignUserToUnit with isPrimary = true first checks for an existing active primary assignment for the user; if one exists, throws PrimaryAssignmentConflictException with the conflicting assignment in the exception payload

revokeAssignment(String assignmentId) sets revokedAt = now() and returns the updated UnitAssignment; throws AssignmentNotFoundException if assignmentId does not exist or is already revoked

setPrimaryAssignment(String assignmentId) atomically unsets is_primary on any current primary assignment and sets is_primary = true on the target assignment, both in a single Supabase RPC call or transaction

All read methods filter WHERE revoked_at IS NULL

All Supabase and constraint errors are mapped to domain exceptions: DuplicateAssignmentException, PrimaryAssignmentConflictException, AssignmentNotFoundException, PermissionException, NetworkException

Repository is registered as a Riverpod provider

Integration test confirms that the database-level partial unique index on primary assignments is the ultimate backstop — application-level checks are defense-in-depth, not the sole enforcement

Technical Requirements

frameworks

Flutter (Dart)

Supabase Flutter SDK (supabase_flutter)

Riverpod

BLoC

apis

Supabase REST API (table: user_unit_assignments)

Supabase RPC (for atomic setPrimaryAssignment if implemented server-side)

data models

UnitAssignment

UnitAssignmentStatus

performance requirements

Pre-insertion uniqueness checks must use targeted queries (WHERE user_id = ? AND unit_id = ? AND revoked_at IS NULL) with existing indexes — not full table scans

setPrimaryAssignment must be atomic — use a Supabase RPC or a PostgreSQL function to avoid a race condition between unsetting the old primary and setting the new one

security requirements

Only coordinators and admins may call assignUserToUnit and revokeAssignment — enforce via Supabase RLS, document the expected RLS policy

Users may only read their own assignments via fetchAssignmentsForUser — RLS enforces this; the repository must not add application-level bypass

All exception messages must be sanitized — do not leak internal Supabase error codes or SQL details to the caller

Execution Context

Execution Tier

Tier 3

Tier 3 - 413 tasks

Can start after Tier 2 completes

View Full Execution Plan

Implementation Notes

The pre-insertion uniqueness check (step before database write) is defense-in-depth: it improves error messages and avoids round-trips for obvious conflicts, but the database unique partial index is the authoritative constraint. Always handle PostgrestException with code '23505' (unique violation) as a fallback in case of a race condition between two concurrent requests. For setPrimaryAssignment, the race-free approach is a PostgreSQL function that runs inside a transaction: UPDATE user_unit_assignments SET is_primary = false WHERE user_id = (SELECT user_id FROM user_unit_assignments WHERE id = p_id) AND is_primary = true AND revoked_at IS NULL; UPDATE user_unit_assignments SET is_primary = true WHERE id = p_id. Deploy this as a Supabase RPC named set_primary_assignment(p_assignment_id uuid) and call it from the repository.

Without the transaction, two concurrent setPrimaryAssignment calls for the same user could result in two active primaries before the unique index fires. Document the RPC requirement as a dependency alongside the database tasks. Follow the same abstract class + Riverpod provider pattern as OrganizationUnitRepository for consistency across the data layer.

Testing Requirements

Write unit tests using flutter_test with a FakeUnitAssignmentRepository. Test scenarios: (1) fetchAssignmentsForUser returns only active assignments. (2) assignUserToUnit succeeds when no existing assignment exists. (3) assignUserToUnit throws DuplicateAssignmentException when same user+unit active assignment already exists.

(4) assignUserToUnit with isPrimary=true throws PrimaryAssignmentConflictException when user already has an active primary. (5) revokeAssignment succeeds and the assignment's revokedAt is set. (6) revokeAssignment throws AssignmentNotFoundException for unknown id. (7) setPrimaryAssignment correctly promotes the target and demotes the previous primary.

(8) Network error maps to NetworkException. Write one integration test against local Supabase: assign → verify primary constraint via database → revoke → reassign as primary. All tests use flutter_test.

Component

Unit Assignment Repository

data medium

Dependencies (2)

Create typed Dart model classes for UnitAssignment including all junction table fields (id, userId, unitId, isPrimary, assignedAt, assignedBy, revokedAt). Include fromJson/toJson, copyWith, equality operators. Add UnitAssignmentStatus enum for active/revoked states used throughout the assignment lifecycle. epic-organizational-hierarchy-management-foundation-task-006 Create the user_unit_assignments junction table schema with columns for id, user_id, unit_id, is_primary, assigned_at, assigned_by, and revoked_at. Add unique constraints to enforce one primary assignment per user. Include foreign keys to auth.users and organization_units with appropriate cascade behavior. epic-organizational-hierarchy-management-foundation-task-002

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.

Quick Links

All Tasks Execution Plan