User Role
Data Entity
Description
Assigns a specific role (peer_mentor, coordinator, org_admin, global_admin) to a user within an organization context. Role assignments determine which screens, actions, and data are accessible. A user may hold multiple role assignments across different organizations simultaneously.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Immutable primary key generated at assignment creation time | PKrequiredunique |
user_id |
uuid |
Foreign key referencing the authenticated user account that holds this role assignment | required |
organization_id |
uuid |
Foreign key referencing the organization within which this role is active. Scopes the role to a single tenant, enabling multi-org membership. | required |
role_type |
enum |
The specific role assigned. peer_mentor can log activities and view own data; coordinator manages peer mentors and registers proxy activities; org_admin has full visibility over their organization; global_admin is blocked from the mobile app and redirected to the web portal. | required |
is_primary |
boolean |
Indicates whether this is the user's primary role within the given organization. Exactly one assignment per user per organization may be primary. Used to determine the default home screen and navigation tabs shown at login. | required |
granted_at |
datetime |
UTC timestamp recording when this role was assigned. Immutable after creation. Used for audit trail and grant-period calculations. | required |
granted_by |
uuid |
User ID of the administrator or system process that created this role assignment. Null when seeded during initial import. References users(id) with set null on delete. | - |
revoked_at |
datetime |
UTC timestamp set when the role assignment is deactivated. Null means currently active. Soft-delete pattern — the row is retained for audit and Bufdir reporting purposes. | - |
revoked_by |
uuid |
User ID of the administrator who revoked this role assignment. Null until role is revoked. | - |
is_active |
boolean |
Computed convenience flag: true when revoked_at is null. Used in RLS policies and query filters to exclude revoked assignments without requiring a null check on every query. | required |
metadata |
json |
Extensible JSON bag for role-specific context: e.g., which chapter units the coordinator is scoped to, or whether a peer_mentor has completed onboarding. Structured as a flat key-value map. Not queried directly — resolved by application layer. | - |
created_at |
datetime |
Row creation timestamp, equivalent to granted_at for new assignments. Separate field to support Supabase audit infrastructure. | required |
updated_at |
datetime |
Last modification timestamp. Updated automatically by Supabase trigger on any column change. Used for cache invalidation and sync freshness checks. | required |
Database Indexes
idx_user_role_user_id
Columns: user_id
idx_user_role_organization_id
Columns: organization_id
idx_user_role_user_org
Columns: user_id, organization_id
idx_user_role_user_org_type_unique
Columns: user_id, organization_id, role_type
idx_user_role_primary_per_org
Columns: user_id, organization_id, is_primary
idx_user_role_active_by_org
Columns: organization_id, is_active, role_type
idx_user_role_granted_at
Columns: granted_at
Validation Rules
role_type_in_allowed_set
error
Validation failed
user_id_references_valid_user
error
Validation failed
organization_id_references_active_org
error
Validation failed
granted_at_not_in_future
error
Validation failed
revoked_at_after_granted_at
error
Validation failed
is_active_consistent_with_revoked_at
error
Validation failed
metadata_is_valid_json_object
error
Validation failed
granted_by_valid_when_not_null
warning
Validation failed
Business Rules
unique_role_per_user_per_org
A user may hold each role_type at most once per organization. Duplicate (user_id, organization_id, role_type) combinations are rejected. A user may hold multiple different role types within the same organization (e.g., coordinator and org_admin simultaneously) if explicitly granted.
single_primary_role_per_org
Exactly one role assignment per user per organization may have is_primary = true. Setting is_primary = true on a new or updated assignment must automatically demote any existing primary for that (user_id, organization_id) pair.
global_admin_blocked_from_mobile
Users whose only active role is global_admin must be redirected to the no-access screen immediately after authentication. They must not be able to access any mobile app functionality. The admin portal URL for their organization must be displayed instead.
multi_org_role_isolation
Role assignments are strictly scoped to their organization_id. A user with coordinator role in Organization A must not gain coordinator privileges in Organization B. All data queries must apply the organization_id scope derived from the active role.
role_determines_navigation
The active role_type drives which bottom navigation tabs, home screen variant, and route branches are rendered. peer_mentor sees their own activity and contacts; coordinator sees roster management and proxy registration; org_admin sees the admin portal.
soft_delete_only
Role assignments must never be hard-deleted. Revocation sets revoked_at and is_active = false. Revoked rows are retained permanently for Bufdir audit trail and proxy activity attribution history.
coordinator_scope_enforced_for_proxy
Coordinators may only register proxy activities on behalf of peer mentors who are assigned to the same organization and unit scope. The coordinator's role assignment is verified before any proxy registration is persisted.
role_required_before_home_screen
The application must not render any feature screen until at least one active role assignment has been resolved for the authenticated user. If role resolution fails, the user is shown an error with logout option.
CRUD Operations
Storage Configuration
Entity Relationships
A user may hold multiple role assignments across different organizations (peer_mentor in one org, coordinator in another)
Each role assignment is scoped to a specific organization, enabling users to hold different roles in different organizations