User Organization Role
Data Entity
Description
A join record binding a user to a specific role within a specific organization and optionally a specific local association. Supports multi-association membership — a single user can have multiple records here with different role or association scopes. This is the primary authorization record queried on every protected request.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Immutable surrogate primary key generated on insert (UUIDv4). Used as the stable reference across all foreign key relationships. | PKrequiredunique |
user_id |
uuid |
Foreign key referencing the users table. Identifies the platform account this role assignment belongs to. | required |
organization_id |
uuid |
Foreign key referencing the organizations table. Scopes this assignment to one of the four member organizations (NHF, Blindeforbundet, HLF, Barnekreftforeningen). | required |
local_association_id |
uuid |
Optional foreign key referencing the local_associations table. When set, further scopes the role to a specific local chapter. Required for Peer Mentor and Coordinator roles; NULL for Organization Administrator and Global Administrator roles. | - |
role_id |
uuid |
Foreign key referencing the roles table. Identifies the access level granted: Peer Mentor, Coordinator, Organization Administrator, or Global Administrator. | required |
status |
enum |
Lifecycle state of this specific role assignment. Active assignments are queried on every authenticated request. Inactive assignments are preserved for audit history but excluded from permission checks. | required |
invited_by_user_id |
uuid |
Foreign key referencing the users table. Records which administrator created this assignment. Used for audit trails and privilege escalation detection. | - |
deactivated_at |
datetime |
Timestamp when this assignment was deactivated or suspended. NULL for active assignments. Set on status transition to inactive or suspended. | - |
deactivated_by_user_id |
uuid |
Foreign key referencing the users table. Records the administrator who deactivated this assignment. NULL for active records. | - |
deactivation_reason |
text |
Optional free-text reason provided by the administrator when deactivating this assignment. Stored for audit purposes. | - |
created_at |
datetime |
Timestamp when this role assignment was created. Set once on insert, never updated. | required |
updated_at |
datetime |
Timestamp of the most recent modification to this record. Updated on any status change. | required |
Database Indexes
idx_user_organization_role_user_id
Columns: user_id
idx_user_organization_role_user_status
Columns: user_id, status
idx_user_organization_role_org_id
Columns: organization_id
idx_user_organization_role_local_assoc_id
Columns: local_association_id
idx_user_organization_role_role_id
Columns: role_id
idx_user_organization_role_unique_active_membership
Columns: user_id, organization_id, local_association_id, role_id
idx_user_organization_role_local_assoc_active
Columns: local_association_id, status
idx_user_organization_role_org_role_status
Columns: organization_id, role_id, status
Validation Rules
user_id_must_exist
error
Validation failed
organization_id_must_exist
error
Validation failed
role_id_must_exist
error
Validation failed
local_association_id_must_exist_when_provided
error
Validation failed
status_enum_validity
error
Validation failed
invited_by_must_have_sufficient_scope
error
Validation failed
deactivation_fields_consistent
error
Validation failed
association_context_membership_check
error
Validation failed
Business Rules
max_five_associations_per_user
A single user may hold active role assignments in at most five local associations simultaneously. This limit was identified by NHF as a hard operational constraint for peer mentors active across multiple chapters.
local_association_must_belong_to_organization
When local_association_id is provided, the referenced local association must be a child of the specified organization_id. Prevents cross-tenant data contamination.
no_privilege_escalation
An administrator may not assign a role with a higher privilege level than their own. Coordinators cannot assign Organization Administrator roles; Organization Administrators cannot assign Global Administrator roles.
peer_mentor_and_coordinator_require_local_association
Role assignments for Peer Mentor and Coordinator roles MUST include a local_association_id. These roles operate within a specific local chapter. Organization Administrator and Global Administrator may have NULL local_association_id.
no_duplicate_active_membership
A user may not hold two active records with the same combination of user_id, organization_id, local_association_id, and role_id. Enforced by the unique index and a service-level pre-check.
deactivation_audit_required
When a role assignment is transitioned to inactive or suspended, deactivated_at and deactivated_by_user_id must be set and an audit log entry must be written. This preserves the full history for compliance and dispute resolution.
global_admin_has_no_local_association_scope
Global Administrator role assignments must have NULL for both local_association_id and optionally for organization_id to reflect cross-organization access. Assigning a local association to a global admin is rejected.
active_role_required_for_authentication
The auth middleware and role guard service only consider records with status = 'active' when resolving user permissions. Inactive and suspended records are ignored for all authorization decisions.
CRUD Operations
Storage Configuration
Entity Relationships
A local association scopes user role assignments for coordinators and peer mentors operating within it
An organization has many user role assignments scoped to it
A user can have multiple role assignments across different organizations and associations