SystemRoles Docs

Reference

Database Schema and Models

On this page

All foreign keys use ON DELETE RESTRICT and ON UPDATE RESTRICT: a row cannot be deleted while another row points at it. Every model lives in BabeRuka\SystemRoles\Models.

Relationship diagram#

erDiagram
    users ||--o| user_roles : "has one"
    system_roles ||--o{ user_roles : "user_role"
    user_roles ||--o{ user_roles_history : "logged in"
    system_roles ||--o{ system_roles_in : "permission matrix"
    system_roles_in ||--o{ user_roles_in : "snapshot"
    users ||--o{ user_roles_in : ""
    system_roles ||--o{ system_classes_in : ""
    system_classes ||--o{ system_classes_in : ""
    system_roles ||--o{ system_routes_in : ""
    system_routes ||--o{ system_routes_in : ""
    system_menus ||--o{ system_menu_items : ""
    system_menus ||--o{ system_menus_in : ""

In words:

  • system_roles is the centre. Permissions (system_roles_in), controller access (system_classes_in) and route access (system_routes_in) each have one row per role.
  • user_roles links each user to one role. Changes are logged in user_roles_history.
  • user_roles_in copies a user's permissions when their role is assigned.
  • The menu tables hang off system_menus and point at system_routes by route_id (no foreign key).

system_roles — model SystemRoles#

Column Type Notes
role_id bigint unsigned, PK
role_name varchar(191), unique Display name
role_guard_name varchar(191), unique Code name, lower snake case
role_description varchar(255), null
role_lang_name enum(eng) Reserved
role_role_class enum(RolesController), null Reserved
role_sequence int, null Sort order
created_at, updated_at timestamp

Relationships: permissions() → many SystemRolesIn; userRoles() → many UserRoles.

system_roles_in — model SystemRolesIn#

The permission matrix. One row per permission per role.

Column Type Notes
in_id bigint unsigned, PK
role_id bigint unsigned, FK → system_roles.role_id
in_name varchar(191) Permission name. Unique with role_id
in_guard_name varchar(191) Permission key. Unique with role_id
in_role enum(0,1,2), default 0 1 = on
in_sequence int, null Display order
created_at, updated_at timestamp

Relationships: role() → SystemRoles; userPermissions() → many UserRolesIn.

user_roles — model UserRoles#

One row per user.

Column Type Notes
role_id bigint unsigned, PK The row id (not the role!)
user_id bigint unsigned, FK → users.id
user_role bigint unsigned, FK → system_roles.role_id The user's role
role_admin int, null Id of the user who assigned it
role_type varchar(255), null Free label, system by default
created_at, updated_at timestamp

Relationships: role() → SystemRoles (via user_role); user() → your user model.

Watch the naming: in user_roles, role_id is the row's own id and user_role is the role. Use SystemRolesService::roleIdForUser() to avoid mixing them up.

user_roles_history — model UserRoleHistory#

Column Type Notes
history_id bigint unsigned, PK
role_id int, null The user_roles row id
user_id int, null
user_role bigint unsigned, null The role given
role_admin int, null Who gave it
role_type varchar(255), null
created_at, updated_at timestamp

No foreign keys, so history survives user deletion. Relationships: userRole(), role(), user(). The model has no $fillable; set attributes one by one.

user_roles_in — model UserRolesIn#

Snapshot of a user's permissions when their role was last assigned.

Column Type Notes
perm_id bigint unsigned, PK
user_id bigint unsigned, FK → users.id
in_id bigint unsigned, FK → system_roles_in.in_id
in_role int, null Copied from the matrix
created_at, updated_at timestamp

Relationships: user(), permission() → SystemRolesIn.

system_classes — model SystemClasses#

Column Type Notes
class_id bigint unsigned, PK
class_name varchar(191) Short class name
class_filename varchar(191) File name
class_description varchar(255), null Generated
class_namespace varchar(255) Unique together with class_name
created_at, updated_at timestamp

Relationship: permissions() → many SystemClassesIn.

system_classes_in — model SystemClassesIn#

Column Type Notes
in_id bigint unsigned, PK
role_id bigint unsigned, FK → system_roles.role_id
class_id bigint unsigned, FK → system_classes.class_id Unique with role_id
in_role enum(0,1,2), default 0 1 = granted
created_at, updated_at timestamp

system_routes — model SystemRoutes#

Column Type Notes
route_id bigint unsigned, PK
route_name varchar(191) May be empty
route_prefix varchar(255), null
route_method varchar(191) e.g. `GET
route_url varchar(255), null URI pattern
route_middleware varchar(255), null Comma-separated
route_desc varchar(255), null METHOD /uri
route_action varchar(255) Controller@method or Closure
route_data json, null Full scan result
created_at, updated_at timestamp

Relationships: routesIn() → many SystemRoutesIn; menusIn() → many SystemMenusIn.

system_routes_in — model SystemRoutesIn#

Column Type Notes
in_id bigint unsigned, PK
role_id bigint unsigned, FK → system_roles.role_id, indexed
route_id bigint unsigned, FK → system_routes.route_id Unique with role_id
in_route enum(0,1,2), default 0 1 = allowed
created_at, updated_at timestamp

Relationships: systemRoute(), systemRole().

system_menus — model SystemMenus#

Column Type
menu_id bigint unsigned, PK
menu_name varchar(255), null
menu_desc varchar(255), null
menu_type enum(main,secondary), default secondary
created_at varchar(255), null
updated_at datetime, null

system_menu_items — model SystemMenuItems#

Column Type
item_id bigint unsigned, PK
menu_id bigint unsigned, FK → system_menus, indexed
route_id bigint unsigned, null, indexed
parent_id bigint unsigned, null
item_name varchar(255), null
item_type enum(parent,child), default child
item_icon varchar(255), null
item_sequence int, null
created_at varchar(255), null
updated_at datetime, null

system_menus_in — model SystemMenusIn#

Column Type
in_id bigint unsigned, PK
menu_id bigint unsigned, FK → system_menus, indexed
route_id bigint unsigned, null (unique with role_id)
item_id bigint unsigned, null
role_id int, null
in_role enum(0,1,2), default 0
created_at, updated_at timestamp, null

Useful queries#

Users per role:

SQL
SELECT r.role_name, COUNT(ur.user_id) AS users
FROM system_roles r
LEFT JOIN user_roles ur ON ur.user_role = r.role_id
GROUP BY r.role_id, r.role_name
ORDER BY r.role_sequence;

The permission matrix as a grid:

SQL
SELECT ri.in_name, r.role_guard_name, ri.in_role
FROM system_roles_in ri
JOIN system_roles r ON r.role_id = ri.role_id
ORDER BY ri.in_name, r.role_sequence;

Users without a role:

SQL
SELECT u.id, u.email
FROM users u
LEFT JOIN user_roles ur ON ur.user_id = u.id
WHERE ur.role_id IS NULL;