Core Database Models

This document provides comprehensive documentation of the core database models that form the foundation of the Light framework. These models handle identity management, authorization, configuration, audit logging, and system data storage. All models extend from Light\Model which provides automatic auditing capabilities (see Auto-Auditing and Lifecycle Hooks for details).

For information about the base model class and its features, see Base Model Class (Light\Model). For database schema definitions and code generation, see Database Schema (db.json).

---

Model Overview and Relationships

The core models are defined in db.json1-676 and implement the data persistence layer for authentication, authorization, configuration, and audit functionality. The following diagram illustrates the relationships between these models:

Sources: db.json1-676

---

User Model

The User model is the central identity management entity with importance score 72.78. It handles authentication, session management, RBAC integration, and user preferences.

Schema Definition

The User table schema is defined in db.json3-139 with the following key fields:

Field	Type	Purpose
user_id	int (PK, auto_increment)	Primary identifier
username	varchar(255) (unique)	Login credential
password	varchar(255)	Hashed password
first_name, last_name	varchar(255)	Identity fields
email, phone	varchar(255)	Contact information
addr1, addr2, addr3	varchar(255)	Address fields
status	tinyint (default: 0)	Account status flag
language	varchar(5)	User's preferred language
default_page	varchar(255)	Landing page after login
join_date	date	Account creation date
expiry_date	date	Account expiration (optional)
secret	varchar(16)	TOTP 2FA secret
credential	json	WebAuthn credential storage
google, microsoft, facebook	varchar(255)	OAuth provider IDs
password_dt	datetime	Last password change timestamp
menu	json	Custom menu configuration
style	json	UI style preferences

Sources: db.json3-139

User Model Implementation

The User model class is located in src/Model/User.php1-390 and extends Light\Model. It exposes GraphQL fields via the #[Type] annotation src/Model/User.php28 and #[MagicField] annotations src/Model/User.php29-45

Sources: src/Model/User.php48-390

Session Management

The User model provides session management through integration with the UserLog table:

Active Session Retrieval: The getSessions() method src/Model/User.php57-82 returns all active sessions for the current user by querying UserLog records where:

• user_id matches the current user
• result is "SUCCESS"
• logout_dt is NULL
• login_dt is within the access token expiration window

Each session includes IP address, user agent, login timestamp, last access time, and geographic location via App::getIpLocation().

Session Revocation: The revokeSession() method src/Model/User.php50-54 sets the logout_dt timestamp for a specific JWT ID (jti), invalidating that session.

Last Access Tracking: The saveLastAccessTime() method src/Model/User.php201-204 updates the last_access_time field in UserLog on each authenticated request, as called by src/Auth/Service.php71

Sources: src/Model/User.php50-82 src/Model/User.php201-204 src/Auth/Service.php71

Brute Force Protection

The isAuthLocked() method src/Model/User.php206-227 implements IP-based account lockout by:

1. Querying UserLog for failed login attempts from the current IP
2. Filtering to records within the lockout duration window (default: 15 minutes from Config.auth_lockout_duration)
3. Counting consecutive failures in the most recent N attempts (default: 5 from Config.auth_lockout_attempts)
4. Returning true if the failure count meets or exceeds the threshold

Sources: src/Model/User.php206-227

RBAC Integration

The User model integrates with the RBAC system through several methods:

Role Assignment: The getRoles() method src/Model/User.php297-311 queries the UserRole table for all roles assigned to the user. If no roles are found, it defaults to the "Everyone" role.

Permission Checking:

• getPermissions() src/Model/User.php192-199 retrieves all permissions granted to the user via RBAC
• isGranted() src/Model/User.php283-291 checks if the user has a specific permission
• isGrantedRights() src/Model/User.php268-280 batch-checks multiple permissions
• is() src/Model/User.php337-340 checks if the user has a specific role

Path Authorization: The isAllowedPath() method src/Model/User.php97-123 checks if the user can access a specific menu path by:

1. Looking up the path in App::getFlatMenus()
2. Extracting the required permissions from the menu definition
3. Checking if the user has any of the required permissions via RBAC

Sources: src/Model/User.php97-123 src/Model/User.php192-311

Two-Factor Authentication

The User model supports TOTP-based 2FA:

Setup: The getMy2FA() method src/Model/User.php129-144 generates a new TOTP secret and returns:

• secret: The TOTP secret string
• host: The current HTTP host
• image: A data URI containing the QR code PNG for scanning

Status Check:

• has2FA() src/Model/User.php162-165 returns whether 2FA is enabled
• isTwoFactorEnabled() src/Model/User.php255-261 is an alias

The secret field db.json80-83 stores the TOTP secret after setup.

Sources: src/Model/User.php129-165 db.json80-83

WebAuthn/FIDO2 Support

The credential JSON field db.json93-95 stores WebAuthn credential data. The getWebAuthn() method src/Model/User.php151-158 deserializes these credentials into Light\Type\WebAuthn objects for biometric authentication.

Sources: src/Model/User.php151-158 db.json93-95

OAuth Integration

The model stores OAuth provider identifiers in dedicated fields:

• google db.json102-105 - Google account ID
• microsoft db.json97-100 - Microsoft account ID
• facebook db.json107-110 - Facebook account ID

These fields enable social login and account linking functionality.

Sources: db.json97-110

User Preferences

Menu Customization: The menu JSON field db.json120-122 stores user-specific menu overrides. The getMenu() method src/Model/User.php88-94 returns this custom menu or an empty array.

UI Styles: The style JSON field db.json124-126 stores UI preferences. Methods:

• getStyles() src/Model/User.php233-239 deserializes the style object
• updateStyle() src/Model/User.php241-252 updates a specific style property and saves

Sources: src/Model/User.php88-94 src/Model/User.php233-252 db.json120-126

Authorization Helpers

The model includes permission-aware helpers:

Update Permission: canUpdate() src/Model/User.php350-368 determines if another user can modify this user's record:

• Users can always update themselves
• Administrators can only be updated by other administrators
• Administrators and Power Users can update everyone else

Delete Permission: canDelete() src/Model/User.php317-335 enforces similar rules for deletion, with the additional constraint that users cannot delete themselves. This method uses #[Right("user.delete")] annotation src/Model/User.php315 to require the base permission first.

Sources: src/Model/User.php315-368

---

Role and Permission Models

The RBAC system uses three models to manage roles and permissions:

Role Model

Defined in db.json141-178 the Role table stores role definitions:

Field	Type	Purpose
role_id	int (PK)	Primary identifier
name	varchar(255)	Role name (indexed)
child	varchar(255)	Child role for inheritance (indexed)

The child field enables role hierarchy, where a role can inherit permissions from another role (e.g., "Power Users" inherits from "Users").

Sources: db.json141-178

Permission Model

Defined in db.json477-499 the Permission table maps roles to permission strings:

Field	Type	Purpose
permission_id	int (PK)	Primary identifier
role	varchar(255)	Role name
value	varchar(100)	Permission string (e.g., "user.delete")

Permissions are loaded from both permissions.yml and the database, with database permissions taking precedence.

Sources: db.json477-499

UserRole Model

Defined in db.json180-231 the UserRole table implements the many-to-many relationship between users and roles:

Field	Type	Purpose
user_role_id	int (PK)	Primary identifier
user_id	int (FK)	References User table
role	varchar(255)	Role name (indexed)

The foreign key constraint db.json218-230 uses CASCADE on both delete and update, ensuring role assignments are removed when users are deleted.

Sources: db.json180-231

Sources: src/Model/User.php297-311 db.json141-231 db.json477-499

---

Config Model

The Config model provides key-value configuration storage in the database, enabling runtime-configurable application settings without code deployment.

Schema Definition

Defined in db.json233-263 the Config table has a simple structure:

Field	Type	Purpose
config_id	int (PK)	Primary identifier
name	varchar(255) (unique)	Configuration key
value	text	Configuration value

The unique constraint on name db.json255-261 ensures each configuration key exists at most once.

Sources: db.json233-263

Config Model Implementation

The Config model class is located in src/Model/Config.php1-29 and provides a static helper method for value retrieval:

This method src/Model/Config.php15-27:

1. Queries for a record with the given name
2. Returns the value field if found and non-empty
3. Returns the $default parameter if not found or if value is NULL/empty string

Usage Example:

Sources: src/Model/Config.php15-27 src/Model/User.php213

Common Configuration Keys

The framework uses Config for various settings referenced throughout the codebase:

Key	Purpose	Default	Reference
auth_lockout_duration	Lockout duration in minutes	15	src/Model/User.php213
auth_lockout_attempts	Failed attempts before lockout	5	src/Model/User.php215
access_token_expire	JWT access token TTL (seconds)	900	App usage
refresh_token_expire	JWT refresh token TTL (seconds)	604800	App usage
mode	Application mode (dev/prod)	-	App usage
tfa_enable	Enable 2FA requirement	-	App usage
password_expiration_enable	Enable password expiration	-	App usage
password_expiration_days	Password validity period	-	App usage
fs	Filesystem configurations (JSON)	-	App usage

Sources: src/Model/User.php213-215

---

UserLog Model

The UserLog model tracks all authentication attempts and maintains session state for JWT tokens.

Schema Definition

Defined in db.json265-336 the UserLog table structure:

Field	Type	Purpose
userlog_id	int (PK)	Primary identifier
user_id	int (FK)	User reference (CASCADE on delete)
login_dt	datetime	Login attempt timestamp
logout_dt	datetime	Logout timestamp (NULL if active)
ip	varchar(45)	Client IP address (IPv4/IPv6)
result	varchar(255)	"SUCCESS" or "FAIL"
user_agent	text	Client user agent string
jti	varchar(100)	JWT ID for session tracking
last_access_time	datetime	Last API request timestamp

The foreign key db.json323-335 cascades deletes, removing all login history when a user is deleted.

Sources: db.json265-336

Usage Patterns

Session Tracking: Each successful login creates a UserLog record with a unique jti. The Auth service src/Auth/Service.php56 extracts the jti from the JWT payload and uses it to:

• Identify the specific session
• Check for revocation via cache lookup src/Auth/Service.php58-60
• Update last access time src/Auth/Service.php71

Brute Force Detection: The isAuthLocked() method src/Model/User.php206-227 queries UserLog for recent failed attempts from the same IP address to implement account lockout.

Session Management: The getSessions() method src/Model/User.php57-82 queries active sessions (where logout_dt IS NULL and within token expiration window) to display all active login sessions to the user.

Sources: src/Auth/Service.php56-71 src/Model/User.php57-82 src/Model/User.php206-227

---

EventLog Model

The EventLog model provides comprehensive audit trails by capturing all data modifications. This is the foundation of the revision system (see Revision System).

Schema Definition

Defined in db.json338-418 the EventLog table structure:

Field	Type	Purpose
eventlog_id	int (PK)	Primary identifier
class	varchar(64)	Fully qualified model class name (indexed)
id	int	Primary key value of the affected record (indexed)
action	varchar(64)	"INSERT", "UPDATE", or "DELETE"
source	json	Record state before modification (NULL for INSERT)
target	json	Record state after modification (NULL for DELETE)
user_id	int (FK, nullable)	User who performed the action
created_time	datetime	Timestamp of the action

The foreign key constraint db.json405-417 uses SET NULL ON DELETE, preserving audit history even when user accounts are removed.

Sources: db.json338-418

Audit Trail Structure

The EventLog captures state snapshots based on the action type:

Action Types:

• INSERT: source is NULL, target contains the newly created record
• UPDATE: source contains the previous state, target contains the new state
• DELETE: source contains the final state before deletion, target is NULL

This structure enables:

• Complete audit history of all changes
• Diff calculation between states
• Point-in-time restoration via source snapshots
• User attribution for compliance requirements

Sources: db.json338-418

Integration with Light\Model

The Light\Model base class automatically creates EventLog entries on save and delete operations through lifecycle hooks. The user_id field is populated from the authenticated user in the DI container.

User Event Queries: The User model provides getEventLog() method src/Model/User.php385-388 to retrieve all events performed by a specific user, with filter and sort support.

Sources: src/Model/User.php385-388

---

MailLog Model

The MailLog model maintains a complete history of all emails sent by the system.

Schema Definition

Defined in db.json420-475 the MailLog table structure:

Field	Type	Purpose
maillog_id	int (PK)	Primary identifier
created_time	datetime	Timestamp when email was sent
from	varchar(255)	Sender email address
to	varchar(255)	Recipient email address
body	text	Email body (HTML or plain text)
subject	varchar(255)	Email subject line
from_name	varchar(255)	Sender display name
to_name	varchar(255)	Recipient display name
altbody	text	Plain text alternative body
host	varchar(255)	SMTP host used for sending

This table has no foreign keys, maintaining email history independent of user account lifecycle.

Sources: db.json420-475

---

SystemValue Model

The SystemValue model provides localized system values for dropdown lists, configuration options, and other enumerated data that varies by language.

Schema Definition

Defined in db.json501-549 the SystemValue table structure:

Field	Type	Purpose
systemvalue_id	int (PK)	Primary identifier
name	varchar(64)	Value category/type (indexed)
value	text	Actual value or label
status	int (default: 0)	Active/inactive flag
language	varchar(5)	Language code (indexed)

The dual indexing on name and language db.json542-548 enables efficient queries for localized value sets.

Sources: db.json501-549

Use Cases

SystemValue supports:

• Dropdown option lists that vary by user language
• System configuration enumerations
• Status code labels
• Any data that requires language-specific representation

The status field allows values to be soft-deleted or temporarily disabled without removing them from the database.

Sources: db.json501-549

---

Translate Model

The Translate model implements the internationalization (i18n) system by storing translated strings for multiple languages.

Schema Definition

Defined in db.json551-605 the Translate table structure:

Field	Type	Purpose
translate_id	int (PK)	Primary identifier
language	varchar(5)	Language code (e.g., "en", "zh") (indexed)
name	varchar(255)	Translation key (indexed)
value	varchar(255)	Translated string

The compound indexing on language and name db.json579-591 optimizes translation lookups during request processing.

Sources: db.json551-605

Integration with User Model

The User model stores language preference in the language field db.json65-68 which is exposed via GraphQL src/Model/User.php41:

This preference determines which translations are served to the user in the application UI.

Sources: db.json65-68 src/Model/User.php41

---

CustomField Model

The CustomField model enables dynamic schema extension by allowing administrators to define additional fields for existing models without database migrations.

Schema Definition

Defined in db.json607-675 the CustomField table structure:

Field	Type	Purpose
custom_field_id	int (PK)	Primary identifier
name	varchar(100)	Field identifier
label	varchar(100)	Display label for UI
model	varchar(45)	Target model class name (indexed)
type	varchar(45)	Field data type (text, number, date, etc.)
placeholder	varchar(100)	Input placeholder text
options	json	Field configuration options
validation	varchar(100)	Validation rules
default_value	json	Default value for new records
order	int (default: 0)	Display order in forms
help	varchar(1000)	Help text for the field

The index on model db.json668-673 enables efficient retrieval of all custom fields for a specific model class.

Sources: db.json607-675

Use Cases

CustomField supports:

• CRM Extensions: Adding industry-specific fields to User model (e.g., "Customer Segment", "Account Manager")
• Product Catalogs: Extending product models with custom attributes
• Form Builders: Dynamic form generation with user-defined fields
• Multi-tenant Systems: Per-tenant schema customization without code changes

The options JSON field can store type-specific configuration such as:

• Dropdown choices for select fields
• Min/max values for numeric fields
• Date range constraints
• Regular expression patterns

Sources: db.json607-675

---

Model Code Generation

All core models are generated from the db.json1-676 schema using the CLI tool at bin/light1-24 The code generation commands include:

Model Generation:

This command generates a model class in src/Model/ with:

• Property annotations matching database columns
• GraphQL #[Type] and #[MagicField] annotations
• Typed properties for IDE support
• Automatic relationship methods for foreign keys

Controller Generation:

Generates a GraphQL controller with CRUD operations and permission annotations.

Input Validation Generation:

Generates input type classes for GraphQL mutations with validation rules.

Sources: bin/light1-24 db.json1-676

---

Summary Table of Core Models

Model	Primary Key	Purpose	Key Relationships	Schema Reference
User	user_id	Identity management, authentication, preferences	→ UserRole, UserLog, EventLog	db.json3-139
Role	role_id	Role definitions with hierarchy	→ Permission, UserRole	db.json141-178
UserRole	user_role_id	User-to-role many-to-many mapping	User ←, → Role	db.json180-231
Permission	permission_id	Role-to-permission mappings	→ Role	db.json477-499
Config	config_id	Application configuration key-value store	None	db.json233-263
UserLog	userlog_id	Authentication attempts and session tracking	User ←	db.json265-336
EventLog	eventlog_id	Comprehensive audit trail of all data changes	User ← (nullable)	db.json338-418
MailLog	maillog_id	Email dispatch history	None	db.json420-475
SystemValue	systemvalue_id	Localized system values and enumerations	None	db.json501-549
Translate	translate_id	Internationalization translations	None	db.json551-605
CustomField	custom_field_id	Dynamic schema extension definitions	None	db.json607-675

Sources: db.json1-676