User Organization Role
Data Entity
Description
Junction table binding a user to an organization with a specific role. Supports multi-organization membership (up to 5 for NHF), role-based access control across mobile and admin surfaces, and time-bounded global admin support access.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Surrogate primary key | PKrequiredunique |
user_id |
uuid |
Foreign key to users table | required |
organization_id |
uuid |
Foreign key to organizations table | required |
role |
enum |
The user's role within this organization | required |
is_active |
boolean |
Whether this role assignment is currently active. False when user is deactivated or paused. | required |
is_primary |
boolean |
Whether this is the user's primary organization membership (relevant for users in multiple orgs, e.g. NHF members in up to 5 local associations) | required |
support_access_expires_at |
datetime |
For global_admin roles only: timestamp after which support access to this org is revoked. Null means no time-bounded support access granted. | - |
invited_by_user_id |
uuid |
The org_admin or global_admin who created this role assignment | - |
activated_at |
datetime |
When the user first accepted/activated this role assignment | - |
deactivated_at |
datetime |
When this role assignment was deactivated | - |
created_at |
datetime |
Record creation timestamp | required |
updated_at |
datetime |
Record last-updated timestamp | required |
Database Indexes
idx_user_organization_roles_user_org
Columns: user_id, organization_id, role
idx_user_organization_roles_user_id
Columns: user_id
idx_user_organization_roles_organization_id
Columns: organization_id
idx_user_organization_roles_role_active
Columns: role, is_active
idx_user_organization_roles_support_expires
Columns: support_access_expires_at
Validation Rules
valid_role_enum
error
Validation failed
valid_user_id_ref
error
Validation failed
valid_organization_id_ref
error
Validation failed
support_access_expires_global_admin_only
error
Validation failed
support_access_expires_future_date
error
Validation failed
invited_by_must_be_admin
error
Validation failed
Business Rules
unique_user_org_role
A user may hold a given role only once per organization. The same user can hold different roles in the same org (e.g. peer_mentor + coordinator) but cannot duplicate the same role.
max_org_memberships_per_user
A user may belong to at most 5 organizations (NHF requirement — members in multiple local associations). Enforced at create time.
global_admin_support_access_expiry
When support_access_expires_at is set for a global_admin role, access is revoked immediately at expiry without requiring a manual update. Every support-access session is written to the org's audit log.
mobile_role_restriction
Only peer_mentor and coordinator roles may log in to the Mobile App. org_admin logs in to Mobile as a coordinator. global_admin is redirected to the no-access screen on mobile.
admin_portal_role_restriction
Only org_admin and global_admin may log in to the Admin Web Portal. peer_mentor and coordinator are managed inside the portal but cannot log in.
single_primary_org_per_user
Each user has exactly one primary organization membership (is_primary=true). Assigning a new primary must demote the previous primary.
deactivation_cascade
When is_active is set to false, the user's active sessions for that organization must be revoked and the change written to the audit log.
duplicate_activity_prevention_scope
Role membership defines the organization scope used by the duplicate activity detection service to flag cross-coordinator duplicates within the same org.