User Role
Data Entity
Description
Role assignment record linking a user to a specific role (global admin, organization admin, coordinator, peer mentor) within an organizational scope. Multiple records may exist per user across different organizational contexts, enabling role-switching without re-authentication.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Unique identifier for the role assignment record | PKrequiredunique |
user_id |
uuid |
Foreign key reference to the users table identifying the role holder | required |
role_type |
enum |
The type of role assigned. global_admin has no organizational scope; all other roles are scoped to an organization hierarchy level | required |
organization_id |
uuid |
Foreign key to the organizations table indicating the organization within which this role is active. NULL is only permitted for global_admin role_type | - |
scope_level |
enum |
Hierarchical depth at which this role operates. Determines data visibility and query scoping for reporting, approvals, and member overviews | required |
local_association_id |
uuid |
Foreign key to local_associations when scope_level is 'local'. Narrows the role's operational scope below the organization level | - |
granted_at |
datetime |
Timestamp of when this role was assigned. Used for audit trail and time-based role queries | required |
granted_by |
uuid |
Foreign key to the users table identifying the administrator who granted this role. Preserved even if the granting user is later deactivated | required |
is_active |
boolean |
Whether this role assignment is currently active. Inactive records are retained for audit history. A user may have multiple inactive records of the same role type | required |
expires_at |
datetime |
Optional expiry timestamp for time-limited role grants. The role is automatically treated as inactive after this date. Used for temporary coordinator or admin access | - |
revoked_at |
datetime |
Timestamp of manual role revocation. Set by admin operations; distinct from expiry to preserve audit clarity | - |
revoked_by |
uuid |
Foreign key to the user who revoked this role. NULL if role expired naturally via expires_at or was never revoked | - |
metadata |
json |
Optional key-value bag for role-specific configuration such as custom permission flags, onboarding state, or organization-level feature overrides. Not used for core authorization logic | - |
created_at |
datetime |
Record creation timestamp managed by the database. Equivalent to granted_at but immutable | required |
updated_at |
datetime |
Last modification timestamp, updated on is_active changes, revocation, or metadata updates | required |
Database Indexes
idx_user_roles_user_id
Columns: user_id
idx_user_roles_user_org_role_active
Columns: user_id, organization_id, role_type, is_active
idx_user_roles_active_unique_per_context
Columns: user_id, role_type, organization_id
idx_user_roles_organization_id
Columns: organization_id
idx_user_roles_role_type_active
Columns: role_type, is_active
idx_user_roles_local_association_id
Columns: local_association_id
idx_user_roles_expires_at
Columns: expires_at
Validation Rules
role_type_valid_enum
error
Validation failed
scope_level_valid_enum
error
Validation failed
organization_id_references_existing_org
error
Validation failed
local_association_id_references_existing_local_assoc
error
Validation failed
user_id_references_existing_user
error
Validation failed
granted_by_references_valid_admin
error
Validation failed
expires_at_must_be_in_future
error
Validation failed
revocation_fields_consistent
error
Validation failed
metadata_valid_json
error
Validation failed
Business Rules
global_admin_has_no_org_scope
A role record with role_type 'global_admin' must have organization_id set to NULL. All other role types require a non-null organization_id. Enforced via database CHECK constraint and application-level validation
one_active_role_per_user_per_org_context
A user may hold at most one active role of a given role_type within a specific organization_id context. The unique index on (user_id, role_type, organization_id) with is_active=true enforces this. Inactive (historical) records of the same combination are permitted
role_grant_requires_admin_authorization
Only users with an active 'global_admin' or 'organization_admin' role within the same or parent organizational scope may create new role assignments. The granted_by field must reference a valid admin user
role_switch_without_reauthentication
When a user has multiple active role records across different organizational contexts, the Role Authorization Service must allow switching the active role without triggering a new authentication challenge. The switch is purely a client-side state change backed by existing JWT claims
scope_level_must_match_role_type
The scope_level value must be consistent with the role_type: global_admin must be 'global', organization_admin must be 'national' or 'regional', coordinator must be 'regional' or 'local', peer_mentor must be 'local'
inactive_roles_retained_for_audit
Role records are never hard-deleted during normal operations. Deactivation sets is_active to false and records revoked_at and revoked_by. Hard delete is reserved for GDPR erasure flows executed via admin-management-service only
expired_roles_auto_deactivated
When expires_at is reached, the role is treated as inactive for all authorization checks. A scheduled Supabase Edge Function or RLS-level check enforces this without requiring a polling process. is_active may also be explicitly set to false before expiry by a revocation action
coordinator_scope_bounded_by_membership
A coordinator role assignment's organization_id must correspond to an organization to which the coordinator user also holds a user_organization_memberships record. Coordinators cannot be assigned to organizations they do not belong to
role_change_audit_logged
Every role creation, deactivation, and revocation must produce an immutable audit log entry via the admin-audit-edge-function. This includes the before/after state, actor (granted_by or revoked_by), and timestamp
CRUD Operations
Storage Configuration
Entity Relationships
A user may hold multiple role assignments across different organizational contexts