Models and Table Relationships (Technical Documentation)

The BabeRuka/systemroles Laravel package provides a robust and granular access control system built around roles, permissions, and class-based access. This documentation details the database schema and the relationships between its models, assuming a standard Laravel installation with an existing users table and authentication controllers.

---

Core Role and Permission Management

These tables define the roles available in your application, the individual permissions associated with them, and how these roles and permissions are assigned to users.

SystemRoles Model (system_roles table)

This is the central table for defining all the roles in your application. Each record represents a distinct role that can be assigned to users, such as “Super Admin” or “Manager.”

• Key Columns:
  • role_id (Primary Key): The unique identifier for each role.
  • role_name (Unique): The human-readable name of the role (e.g., ‘Super Admin’, ‘Admin’).
  • role_guard_name (Unique): A unique, programmatic key for the role (e.g., ‘super_admin’). This is used for global checks and assignments, especially for high-privilege roles like ‘super_admin’.
  • role_description: An optional field for a brief explanation of the role’s purpose.
  • role_lang_name: Specifies the language for the role name (default ‘eng’).
  • role_role_class: Indicates a controller class (e.g., ‘RolesController’) likely responsible for further management of this role type.
  • role_sequence: An integer used to determine the display order of roles in lists.
• Trigger: The protect_roles_deletion trigger is implemented on this table. It prevents the deletion of crucial ‘super_admin’ and ‘admin’ roles, ensuring system integrity and preventing accidental lockout.
• Relationship:
  • One-to-Many with user_roles: A single role can be assigned to many users.
  • One-to-Many with system_roles_in: A single role can have many specific permissions defined for it.
  • One-to-Many with system_classes_in: A single role can be granted access to many specific application classes.

SystemRolesIn Model (system_roles_in table)

This table stores the individual permissions assigned to each role. These permissions dictate what actions a user with a given role can perform within the application’s views (e.g., ‘edit’, ‘delete’, ‘approve’).

• Key Columns:
  • in_id (Primary Key): The unique identifier for a specific permission entry within a role.
  • role_id (Foreign Key): Links to system_roles.role_id, indicating which role this permission belongs to.
  • in_name: The human-readable name of the permission (e.g., ‘View’, ‘Create’, ‘Update’).
  • in_guard_name: A programmatic key for the permission (e.g., ‘VIEW’, ‘CREATE’, ‘UPDATE’).
  • in_role (Enum ‘0’,’1′,’2′): This enum likely represents the permission status or level of access for this specific permission within the role.
    • 0: Denied / No access
    • 1: Granted / Yes access
    • 2: (Potentially) Elevated access or specific sub-permission (if applicable)
  • in_sequence: An integer for ordering permissions display.
• Relationships:
  • Many-to-One with SystemRoles: Each permission entry belongs to one specific role.
  • One-to-Many with UserRolesIn: A single permission definition can be assigned directly to multiple users.

UserRoles Model (user_roles table)

This is a pivot table that establishes the many-to-many relationship between your application’s User model (from the default Laravel users table) and the SystemRoles. It’s where you map which role(s) each user is assigned.

• Key Columns:
  • role_id (Primary Key): This is the primary key for the entry within this pivot table, uniquely identifying each user-role assignment record.
  • user_id (Foreign Key): Links to your application’s users.id, identifying the user.
  • user_role (Foreign Key): Links to system_roles.role_id, indicating the specific role assigned to the user.
  • role_admin: An integer field, potentially indicating if this role assignment grants administrative privileges (e.g., 1 for admin-level, 0 otherwise).
  • role_type: A string field for categorizing the type of role assignment.
• Relationships:
  • Many-to-One with User (external users table): Each record belongs to one user.
  • Many-to-One with SystemRoles: Each record points to one defined role.
  • Logs to UserRoleHistory: Changes to entries in this table are typically logged.

UserRolesIn Model (user_roles_in table)

This table allows for direct, granular permission assignment to individual users, overriding or supplementing their role-based permissions. For example, a user might not have ‘Manage’ permission through their role, but could be granted it specifically via this table.

• Key Columns:
  • perm_id (Primary Key): Unique identifier for a direct user-permission assignment.
  • user_id (Foreign Key): Links to your application’s users.id, identifying the user.
  • in_id (Foreign Key): Links to system_roles_in.in_id, indicating the specific permission definition being assigned directly to the user.
  • in_role: An integer, defining the status or level of this directly assigned permission for the user.
• Relationships:
  • Many-to-One with User (external users table): Each record belongs to one user.
  • Many-to-One with SystemRolesIn: Each record points to one specific permission definition.

---

Class-Based Access Control

These tables are dedicated to controlling access to your application’s PHP classes, typically controllers, based on assigned roles.

SystemClasses Model (system_classes table)

This table acts as a registry for your application’s controllers and other relevant PHP classes that can be protected by the SystemRoles package. The SystemClassesController scans your controller_directory (defined in systemroles.php) and populates this table.

• Key Columns:
  • class_id (Primary Key): Unique identifier for each scanned class.
  • class_name (Unique): The unqualified class name (e.g., ‘AdminDashboardController’).
  • class_filename (Unique): The filename of the class.
  • class_description: An optional description.
  • class_namespace: The full PHP namespace of the class.
• Relationship:
  • One-to-Many with system_classes_in: A single class can have access rules defined for many roles.

SystemClassesIn Model (system_classes_in table)

This pivot table maps roles to specific application classes, enabling class-level access control. It determines which roles are allowed to execute a particular controller.

• Key Columns:
  • in_id (Primary Key): Unique identifier for a role’s access permission to a class.
  • role_id (Foreign Key): Links to system_roles.role_id, indicating the role being granted access.
  • class_id (Foreign Key): Links to system_classes.class_id, indicating the specific class being controlled.
  • in_role (Enum ‘0’,’1′,’2′): This enum defines the level of access a role has to a specific class.
    • 0: No access / Denied
    • 1: Basic access (e.g., ability to execute the controller)
    • 2: Full access (e.g., implying broader access if the class has sub-permissions)
• Relationships:
  • Many-to-One with SystemRoles: Each class access rule belongs to a specific role.
  • Many-to-One with SystemClasses: Each class access rule pertains to a specific registered class.

---

History Table

UserRoleHistory Model (user_roles_history table)

This table serves as an audit log for changes in user-role assignments. It tracks historical data about which roles were assigned to users over time, providing valuable information for security audits and debugging.

• Key Columns:
  • history_id (Primary Key): Unique identifier for each historical record.
  • role_id: The ID of the user_roles pivot table entry that was affected.
  • user_id: The ID of the user whose role assignment was logged.
  • user_role: The role_id from system_roles that was assigned to the user at that time.
  • role_admin: The role_admin status at the time of the log.
  • role_type: The role_type at the time of the log.
  • created_at, updated_at: Timestamps for when the historical record was created or updated.
• Note: While logical relationships exist, direct foreign key constraints are not explicitly defined in the provided schema for this table, meaning data integrity for these fields relies on the application’s logic.