User Role Assignment
Data Entity
Description
Junction table that maps users to their assigned roles within a specific organizational context. Implements the many-to-many relationship between users and roles, scoping each assignment to an organization and optionally a local association. Supports soft-delete for audit trail preservation and captures actor identity for every state change.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key — surrogate identifier for the assignment record | PKrequiredunique |
user_id |
uuid |
Foreign key referencing the user receiving the role | required |
role_id |
uuid |
Foreign key referencing the role being assigned (Peer Mentor, Coordinator, Org Admin, Global Admin) | required |
organization_id |
uuid |
Organization to which this role assignment is scoped. NULL only for Global Admin assignments which span all organizations | - |
local_association_id |
uuid |
Local association to which this assignment is scoped. Set for Coordinators to constrain their operational scope to a specific association within their org | - |
is_active |
boolean |
Whether this assignment is currently in effect. Set to false on revocation rather than hard-deleting to preserve audit trail | required |
assigned_by |
uuid |
Foreign key referencing the admin user who created this assignment. Required for audit trail | required |
assigned_at |
datetime |
Timestamp when the role was assigned | required |
revoked_by |
uuid |
Foreign key referencing the admin user who revoked this assignment. NULL while is_active = true | - |
revoked_at |
datetime |
Timestamp when the assignment was revoked. NULL while is_active = true | - |
notes |
text |
Optional free-text notes added by the assigning admin, e.g. reason for a temporary or restricted assignment | - |
Database Indexes
idx_ura_user_id
Columns: user_id
idx_ura_role_id
Columns: role_id
idx_ura_organization_id
Columns: organization_id
idx_ura_user_org_active
Columns: user_id, organization_id, is_active
idx_ura_unique_active_assignment
Columns: user_id, role_id, organization_id
idx_ura_local_association_id
Columns: local_association_id
idx_ura_org_role_active
Columns: organization_id, role_id, is_active
Validation Rules
user_id_must_exist
error
Validation failed
role_id_must_exist
error
Validation failed
organization_id_must_exist_when_provided
error
Validation failed
local_association_belongs_to_organization
error
Validation failed
assigned_by_must_exist
error
Validation failed
revoked_at_requires_revoked_by
error
Validation failed
cannot_revoke_already_inactive_assignment
error
Validation failed
mobile_jwt_claims_must_match_active_assignments
error
Validation failed
Business Rules
no_role_escalation
An Org Admin cannot assign the Global Admin role. Only Global Admins may grant Global Admin privileges. Any attempt by an Org Admin to assign global-level roles must be rejected with a 403 error.
org_scoped_assignment
Role assignments for Peer Mentor, Coordinator, and Org Admin must have a non-null organization_id. Global Admin assignments must have a null organization_id. Violating this constraint is always rejected.
one_active_assignment_per_role_per_org
A user may only have one active assignment per (role, organization) combination. Attempting to assign the same role within the same org while a prior active assignment exists must fail. The unique partial index enforces this at the database level.
coordinator_requires_local_association
When assigning the Coordinator role, a local_association_id must be provided to define the coordinator's operational scope. Coordinator assignments without an association are rejected.
soft_delete_on_revocation
Role assignments are never hard-deleted. On revocation, is_active is set to false and revoked_at/revoked_by are populated. This preserves audit history and allows historical role queries.
actor_must_be_authorized_admin
Only users with Org Admin role (for org-scoped assignments) or Global Admin role (for global assignments) may create or revoke role assignments. Any other actor is rejected.
roles_updated_at_refresh_on_change
When a role assignment is created or revoked, the users.roles_updated_at field must be updated to signal that any cached JWT claims are stale and must be re-validated on next request.
audit_log_on_every_change
Every create and revoke operation on user_role_assignments must emit an audit log entry recording the actor, affected user, role, organization, and timestamp.