User Role Assignments
Data Entity
Description
Junction table linking users to their assigned roles within a specific organization context. Each record represents one role granted to one user, enabling the many-to-many relationship between users and roles. Supports multi-role users (e.g., a coordinator who is also a peer mentor), role switching, JWT claim population, and audit-tracked role lifecycle management.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Unique identifier for the role assignment record | PKrequiredunique |
user_id |
uuid |
Foreign key referencing the user who is assigned this role | required |
role_id |
uuid |
Foreign key referencing the role being assigned to the user | required |
organization_id |
uuid |
Organization context in which this role assignment applies. Enforces tenant isolation so a user can hold different roles in different organizations. | required |
assigned_by |
uuid |
User ID of the administrator who granted this role assignment | required |
assigned_at |
datetime |
Timestamp when the role was assigned to the user | required |
revoked_at |
datetime |
Timestamp when the role was revoked. Null means the assignment is active. Soft-delete pattern preserves audit trail. | - |
revoked_by |
uuid |
User ID of the administrator who revoked this role assignment. Null if still active. | - |
is_primary |
boolean |
Whether this is the user's default/primary role for the organization. Used to determine the initial active role on login and the home dashboard variant. | required |
local_association_id |
uuid |
Optional local association scope for this role assignment. Coordinators and peer mentors are scoped to specific local associations within the organization hierarchy. | - |
created_at |
datetime |
Record creation timestamp | required |
updated_at |
datetime |
Last modification timestamp, updated on any field change | required |
Database Indexes
idx_user_role_assignments_user_role_org
Columns: user_id, role_id, organization_id
idx_user_role_assignments_user_id
Columns: user_id
idx_user_role_assignments_role_id
Columns: role_id
idx_user_role_assignments_organization_id
Columns: organization_id
idx_user_role_assignments_user_primary
Columns: user_id, organization_id, is_primary
idx_user_role_assignments_active
Columns: user_id, revoked_at
idx_user_role_assignments_local_association
Columns: local_association_id
Validation Rules
valid_user_reference
error
Validation failed
valid_role_reference
error
Validation failed
valid_organization_reference
error
Validation failed
valid_assigner_reference
error
Validation failed
revocation_fields_consistency
error
Validation failed
no_duplicate_active_assignment
error
Validation failed
valid_local_association_scope
error
Validation failed
immutable_assignment_fields
error
Validation failed
Business Rules
unique_active_role_per_org
A user cannot hold the same role twice within the same organization. The unique index on (user_id, role_id, organization_id) enforces this at the database level.
single_primary_role_per_org
A user may have at most one role marked as is_primary=true per organization. When a new primary role is set, the previous primary assignment must be demoted.
no_self_escalation
A user cannot assign themselves a higher-privilege role (e.g., peer mentor cannot self-assign coordinator or org admin). The assigning user must hold equal or higher privileges.
tenant_isolation
Role assignments are scoped to a single organization. A user's roles in Organization A have no bearing on their access in Organization B. All queries must filter by organization_id.
soft_delete_revocation
Role revocation sets revoked_at and revoked_by rather than deleting the record, preserving a complete audit trail of role changes for compliance.
jwt_invalidation_on_change
When a role assignment is created or revoked, the user's roles_updated_at timestamp must be updated, causing existing JWT tokens to be invalidated on next validation so that new tokens reflect the updated role set.
audit_log_on_role_change
Every role assignment or revocation must emit an audit log entry recording the actor, action, target user, role, and organization for compliance with GDPR and internal security policies.
coordinator_requires_local_association
When assigning the Coordinator role, a local_association_id should be provided to scope the coordinator's oversight to a specific local association within the organization hierarchy.
global_admin_no_org_context
Global Administrator role assignments are system-level and do not belong to a specific organization. The organization_id should reference the Norse Digital Products system organization, not a client organization.