Unit Assignment
Data Entity
Description
Links a user to one or more organization units, defining their operational scope for activities and contacts. A coordinator may be assigned to up to 5 local chapters simultaneously. One assignment per user per organization is flagged as primary. Used for RLS policy enforcement and chapter-scoped data queries.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Surrogate primary key. Generated server-side via gen_random_uuid(). | PKrequiredunique |
user_id |
uuid |
FK to auth.users. Identifies the user (coordinator, peer mentor, or org admin) being assigned to the unit. | required |
organization_unit_id |
uuid |
FK to organization_units. The local chapter, regional office, or other hierarchical node this user is scoped to. | required |
organization_id |
uuid |
Denormalized FK to the root organization. Required for fast org-scoped RLS filtering without joining through organization_units. Must match the organization_id of the referenced organization_unit. | required |
is_primary |
boolean |
Flags the primary chapter for this user within the organization. Exactly one assignment per user per organization must be primary. The primary unit is used as the default RLS scope and the default active chapter on login. | required |
assigned_at |
datetime |
Timestamp (UTC) when this assignment was created. Used for audit trail and ordering when resolving the primary assignment. | required |
assigned_by |
uuid |
FK to auth.users. The coordinator or org admin who created this assignment. Null if system-generated (e.g., during org onboarding import). | - |
status |
enum |
Lifecycle status of the assignment. Active assignments are included in RLS scope evaluation. Inactive assignments are preserved for audit but excluded from scope resolution. | required |
deactivated_at |
datetime |
Timestamp (UTC) when the assignment was set to inactive. Null when status is active. | - |
deactivated_by |
uuid |
FK to auth.users. The actor who deactivated this assignment. Null when status is active. | - |
notes |
text |
Optional free-text note entered by the assigning coordinator or admin, e.g. reasoning for a multi-chapter assignment or temporary arrangement context. | - |
Database Indexes
idx_unit_assignment_user_org
Columns: user_id, organization_id
idx_unit_assignment_user_unit
Columns: user_id, organization_unit_id
idx_unit_assignment_unit_id
Columns: organization_unit_id
idx_unit_assignment_primary
Columns: user_id, organization_id, is_primary
idx_unit_assignment_status
Columns: status, organization_id
uq_unit_assignment_one_primary_per_user_org
Columns: user_id, organization_id
Validation Rules
user_id_must_exist
error
Validation failed
organization_unit_id_must_exist
error
Validation failed
organization_id_consistency
error
Validation failed
active_assignment_count_within_limit
error
Validation failed
is_primary_boolean_required
error
Validation failed
notes_max_length
error
Validation failed
deactivation_requires_deactivated_by
error
Validation failed
inactive_unit_not_assignable
error
Validation failed
Business Rules
max_five_assignments_per_user_per_org
A user may hold at most 5 active unit assignments within a single organization simultaneously. This models NHF's requirement that a coordinator can belong to up to 5 local chapters. Attempting to create a 6th active assignment must be rejected with a user-facing error.
exactly_one_primary_per_user_per_org
Exactly one assignment per user per organization must have is_primary = true at any given time. Setting a new assignment as primary must atomically unset the existing primary for the same (user_id, organization_id) pair. A user with at least one active assignment must always have a primary.
primary_reassignment_on_deactivation
If the primary assignment is deactivated and other active assignments exist for the same user in the organization, the system must automatically designate the oldest remaining active assignment as the new primary. If no active assignments remain, no primary is set.
unit_must_belong_to_same_organization
The organization_unit referenced by organization_unit_id must belong to the organization identified by organization_id. Cross-organization assignments are forbidden. The service layer must validate this before persisting.
no_duplicate_user_unit_pair
A user cannot be assigned to the same organization unit more than once, regardless of status. The unique index on (user_id, organization_unit_id) enforces this at the database level. Reactivation of an existing inactive assignment is preferred over creating a duplicate.
rls_scope_derived_from_active_assignments
Supabase RLS policies for all chapter-scoped tables derive the permitted unit IDs from the user's active unit assignments. Only active assignments (status = 'active') contribute to the RLS scope. Inactive assignments must be excluded from JWT claims and session variable injection.
active_chapter_defaults_to_primary
On login and after any assignment change, the in-app active chapter state (used for UI scoping and query filtering) defaults to the user's primary assignment unit. The user may switch to any other active assignment unit within the session via the chapter switcher.
assigned_by_must_have_sufficient_role
Only users with coordinator or org_admin role may create unit assignments. The service validates the calling user's role before persisting. Peer mentors cannot assign themselves or others to units.
CRUD Operations
Storage Configuration
Entity Relationships
A chapter or regional unit has multiple users (coordinators, peer mentors) assigned to it for scoped data access