User Role
Data Entity
Description
Junction entity that assigns a specific role to a user within an organization context. Enables multi-tenancy by scoping all permissions and data access to an organization. A user may hold multiple roles across different organizations, but only one role per organization at a time.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key for the user_role assignment record | PKrequiredunique |
user_id |
uuid |
Foreign key referencing the user who holds this role | required |
organization_id |
uuid |
Foreign key referencing the organization context for this role assignment. NULL is permitted only for global_admin roles (Norse Digital Products staff with no org context). | - |
role_id |
uuid |
Foreign key referencing the role definition (peer_mentor, coordinator, org_admin, global_admin) | required |
role_slug |
enum |
Denormalized role identifier for fast RBAC lookups without joining the roles table. Kept in sync with roles.slug. | required |
is_active |
boolean |
Whether this role assignment is currently active. Set to false when a peer mentor pauses, is deactivated, or a role is revoked without deleting the record. | required |
is_paused |
boolean |
Peer mentor-specific pause state. True when a peer mentor temporarily deactivates without fully leaving. Coordinator is notified when this flips to true. | required |
paused_at |
datetime |
Timestamp when the peer mentor paused their role. NULL when not paused. | - |
paused_reason |
text |
Optional free-text reason provided by the peer mentor when pausing their role. | - |
assigned_by_user_id |
uuid |
User ID of the admin or coordinator who created this role assignment. Used for audit trails. | - |
local_association_id |
uuid |
Optional FK to the local_associations table. Scopes peer mentors and coordinators to a specific local branch within the organization. NULL means org-wide access. | - |
valid_from |
datetime |
When this role assignment becomes effective. Defaults to created_at. Allows future-dated assignments. | required |
valid_until |
datetime |
Expiry timestamp for the role assignment. NULL means indefinite. Used for certification-linked roles (e.g., HLF peer mentor certificate expiry). | - |
created_at |
datetime |
Record creation timestamp | required |
updated_at |
datetime |
Record last-updated timestamp | required |
Database Indexes
idx_user_roles_user_org
Columns: user_id, organization_id
idx_user_roles_user_org_role_unique
Columns: user_id, organization_id, role_id
idx_user_roles_org_slug
Columns: organization_id, role_slug
idx_user_roles_active
Columns: user_id, is_active
idx_user_roles_local_association
Columns: local_association_id, role_slug
idx_user_roles_valid_until
Columns: valid_until
Validation Rules
valid_role_id
error
Validation failed
valid_user_id
error
Validation failed
valid_organization_id
error
Validation failed
valid_local_association_id
error
Validation failed
valid_from_not_future_expiry
error
Validation failed
paused_state_peer_mentor_only
error
Validation failed
paused_at_required_when_paused
error
Validation failed
assigned_by_required_for_admin_assignments
warning
Validation failed
Business Rules
one_role_per_org
A user may hold only one role per organization. Attempting to assign a second role in the same org must replace or reject the existing one. The unique index on (user_id, organization_id, role_id) enforces this at the DB level.
global_admin_no_org
Users with role_slug='global_admin' must have organization_id=NULL. Global admins manage the system across all tenants but have no data access to any specific organization's operational data. This enforces tenant separation.
org_admin_requires_org
Users with role_slug='org_admin', 'coordinator', or 'peer_mentor' must have a non-null organization_id. Roles below global_admin are always org-scoped.
pause_notifies_coordinator
When is_paused flips to true for a peer_mentor role, the system must notify all active coordinators in the same organization (and local_association if set) via push/email notification.
certificate_expiry_deactivation
When valid_until is reached for a peer_mentor role, is_active is automatically set to false by the certificate expiry scheduler. This also removes the peer mentor from publicly visible local association listings (HLF requirement).
role_slug_sync
The denormalized role_slug field must always match the slug of the referenced role_id. Updated via trigger or enforced at application layer on every write.
coordinator_can_assign_peer_mentor_only
Coordinators may only assign the peer_mentor role within their own organization (and local association scope). Only org_admins and global_admins can assign coordinator or org_admin roles.
bulk_role_change_audit
Any bulk role assignment or revocation performed via bulk-action-service must generate an audit_log entry per affected user_role record, including the acting admin's user_id.