Data Layer

Purpose and Scope

The Data Layer provides the persistence foundation for the Light framework, implementing an ORM-based model system with automatic audit logging and revision tracking. This document covers the Light\Model base class, lifecycle hooks, the EventLog auditing system, the database schema definition in db.json, and core database models.

For GraphQL API integration with models, see GraphQL API. For authentication and user management features, see Authentication and Authorization. For database management CLI tools, see Development Tools.

Overview

The data layer is built on three core concepts:

1. Model Base Class: Light\Model extends Light\Db\Model from the mathsgod/light-db package, providing common functionality for all domain models
2. Automatic Auditing: Every insert, update, and delete operation is logged to the EventLog table via save/delete hooks
3. Schema-First Development: The db.json file serves as the single source of truth for database structure and code generation

Sources: src/Model.php1-239 db.json1-676 src/App.php103

Base Model Class (Light\Model)

The Light\Model class at src/Model.php14 provides common functionality for all domain models in the framework.

Class Definition and Inheritance

Sources: src/Model.php14

Common Fields

All models inheriting from Light\Model support standard audit fields when they exist in the table definition:

Field	Type	Purpose	Auto-Populated
created_time	datetime	Record creation timestamp	Yes, on insert
created_by	int	User ID who created the record	Yes, on insert
updated_time	datetime	Last update timestamp	Yes, on update
updated_by	int	User ID who updated the record	Yes, on update

These fields are automatically populated by the save() hook at src/Model.php156-169

Sources: src/Model.php156-169

GraphQL Field Accessors

The base model provides GraphQL field accessors using the #[Field] attribute:

• createdTime() src/Model.php42-45 - Returns the created_time value
• createdBy() src/Model.php47-55 - Resolves the creator's name from the User model
• updatedTime() src/Model.php57-60 - Returns the updated_time value
• updatedBy() src/Model.php62-70 - Resolves the updater's name from the User model

These methods convert database values into GraphQL-friendly formats and perform lazy loading of related User entities.

Sources: src/Model.php42-70

Permission Checks

Three permission check methods provide authorization hooks:

• canDelete(User $by) src/Model.php74-77 - Returns true if the user can delete this record
• canUpdate(User $by) src/Model.php85-97 - Returns true if the user can update this record
• canView(User $by) src/Model.php100-117 - Returns true if the user can view this record

These methods use the #[InjectUser] attribute to receive the current authenticated user and return boolean authorization decisions. The default implementation returns true, but subclasses can override these methods for custom authorization logic.

Sources: src/Model.php74-117

The bind() Method

The bind() method at src/Model.php28-39 provides a safe way to populate model fields from input data:

This method:

1. Gets the list of valid fields using __fields()
2. Filters out null values
3. Only assigns values to fields that exist in the table schema
4. Prevents mass-assignment vulnerabilities

Sources: src/Model.php28-39

Lifecycle Hooks and Auto-Auditing

The Light\Model class implements automatic audit logging through save() and delete() lifecycle hooks.

Save Hook Flow

Sources: src/Model.php144-238

Save Hook Implementation Details

The save() method at src/Model.php144-238 performs these operations:

1. User Context Retrieval src/Model.php146-151 - Gets the current user ID from the Auth\Service via the container
2. Action Determination src/Model.php154-170 - Checks if the primary key exists to determine Insert vs Update
3. Timestamp Management src/Model.php156-169 - Sets created_time/created_by for inserts or updated_time/updated_by for updates
4. Source Snapshot src/Model.php172-198 - For updates, loads the original record from the database
5. Blob Filtering src/Model.php176-210 - Filters out blob fields (longblob, mediumblob, tinyblob) from both source and target to avoid storing large binary data
6. Parent Save src/Model.php219 - Calls the parent class save() to perform the actual database operation
7. EventLog Entry src/Model.php221-236 - Inserts an audit record if logging is enabled for the action

Sources: src/Model.php144-238

Blob Field Filtering

Large binary fields are excluded from audit logs to prevent database bloat. The filtering logic at src/Model.php176-210 checks each column's data type:

Sources: src/Model.php176-210

Delete Hook Implementation

The delete() method at src/Model.php120-142 captures the record state before deletion:

1. User Context src/Model.php124-129 - Retrieves the current user ID
2. Snapshot src/Model.php136 - Serializes the current record state as the target
3. EventLog Entry src/Model.php131-139 - Inserts an audit record with action "Delete", source as null, and target containing the deleted record
4. Parent Delete src/Model.php141 - Calls the parent class delete() to remove the record

Sources: src/Model.php120-142

Logging Control Flags

Three static properties control which operations are logged:

• $_log_insert src/Model.php16 - Default: true
• $_log_update src/Model.php18 - Default: true
• $_log_delete src/Model.php17 - Default: true

Subclasses can override these to disable logging for specific models or operations.

Sources: src/Model.php16-18

EventLog and Revision System

The EventLog table stores a complete audit trail of all model changes, enabling revision history and restoration capabilities.

EventLog Table Structure

From db.json337-418 the EventLog table has the following schema:

Column	Type	Purpose
eventlog_id	int (PK)	Unique identifier
class	varchar(64)	Fully qualified model class name
id	int	Primary key value of the affected record
action	varchar(64)	Operation type: "Insert", "Update", or "Delete"
source	json	Record state before the operation (null for inserts)
target	json	Record state after the operation
user_id	int (FK)	User who performed the operation
created_time	datetime	When the operation occurred

The table has indexes on class, id, and user_id for efficient querying, and a foreign key to the User table with ON DELETE SET NULL.

Sources: db.json337-418

Revision Type

The Light\Type\Revision class at src/Type/Revision.php13 wraps an EventLog entry and exposes revision information via GraphQL:

Sources: src/Type/Revision.php13-119

Revision GraphQL Fields

The Revision type exposes these fields:

Field	Return Type	Description	Source
revision_id	int	The EventLog ID	src/Type/Revision.php45-49
createdTime	string	When the change occurred	src/Type/Revision.php21-25
revisionBy	string	Name of the user who made the change	src/Type/Revision.php51-61
content	mixed	The complete record state (source field)	src/Type/Revision.php63-70
delta	mixed	Only the changed fields	src/Type/Revision.php72-87
diff	mixed	Unified diff format for changed fields	src/Type/Revision.php89-118

Sources: src/Type/Revision.php21-118

Querying Revision History

The getRevisionsByModel query at src/Controller/RevisionController.php32-42 retrieves all revisions for a specific model instance:

This query:

1. Filters EventLog by class name and record ID
2. Sorts by created_time descending (newest first)
3. Wraps each EventLog entry in a Revision type

Sources: src/Controller/RevisionController.php32-42

Restoring Revisions

The restoreRevision mutation at src/Controller/RevisionController.php21-25 allows selective field restoration:

The retoreFields() method at src/Type/Revision.php27-43 performs the restoration:

1. Loads the current model instance using the class and ID from EventLog
2. For each specified field, validates it exists in the model schema
3. Sets the field value to the value from the revision's source snapshot
4. Saves the model, which creates a new EventLog entry for the restoration

This creates a new audit entry showing the restoration, maintaining a complete audit trail.

Sources: src/Controller/RevisionController.php21-25 src/Type/Revision.php27-43

Revision Enable Check

The isRevisionEnabled() method at src/App.php789-802 checks if revision tracking is enabled for a specific model:

The revision configuration stores a comma-separated list of model class names in the Config table.

Sources: src/App.php789-802

Database Schema (db.json)

The db.json file at the repository root serves as the single source of truth for database structure. It defines tables, columns, keys, and foreign keys in a declarative JSON format.

Schema File Structure

Sources: db.json1-676

Table Definition Example

The User table definition at db.json3-139 demonstrates the structure:

Sources: db.json3-139

Column Type Mapping

The schema supports standard SQL column types:

db.json Type	MySQL Type	PHP Type
int	INT	int
varchar	VARCHAR	string
text	TEXT	string
datetime	DATETIME	string
date	DATE	string
tinyint	TINYINT	int
json	JSON	array/object
longblob	LONGBLOB	string
mediumblob	MEDIUMBLOB	string

Additional modifiers:

• unsigned - Creates unsigned integer columns
• nullable - Allows NULL values (default: true)
• default - Specifies default value
• auto_increment - Auto-incrementing primary key
• length - Character length for varchar/char types

Sources: db.json1-676

Foreign Key Constraints

Foreign keys are defined in the foreign_keys array. Example from UserRole at db.json218-230:

This creates a foreign key constraint with cascading deletes and updates.

Sources: db.json218-230

Schema Usage in Code Generation

The schema is used by CLI commands to generate code:

• DbInstallCommand - Creates/updates database tables from schema
• MakeModelCommand - Generates PHP model classes
• MakeTsCommand - Generates TypeScript interfaces

For details on these commands, see Development Tools.

Sources: bin/light1-24

Core Database Models

The framework includes several core models that provide essential functionality.

User Model

The User table at db.json3-139 is the central authentication and authorization entity:

Key Fields:

• user_id - Primary key
• username - Unique login identifier
• password - Hashed password
• first_name, last_name - User name components
• email, phone - Contact information
• addr1, addr2, addr3 - Address fields
• status - User account status (0=inactive, 1=active)
• language - User's preferred language code
• default_page - Starting page after login
• join_date - Account creation date
• secret - TOTP 2FA secret
• setting - JSON user preferences
• expiry_date - Account expiration date
• credential - WebAuthn credential data (JSON)
• microsoft, google, facebook - Social login identifiers
• password_dt - Password last changed timestamp
• created_time - Account creation timestamp
• menu - Custom menu configuration (JSON)
• style - UI style preferences (JSON)

Sources: db.json3-139

Role and UserRole Models

The role system uses two tables:

Role db.json141-178 defines role hierarchies:

• role_id - Primary key
• name - Parent role name
• child - Child role name

This allows hierarchical role inheritance (e.g., "Power Users" inherits from "Administrators").

UserRole db.json180-231 maps users to roles:

• user_role_id - Primary key
• user_id - Foreign key to User
• role - Role name assigned to the user

Sources: db.json141-231

Config Model

The Config table at db.json233-263 provides key-value storage for application settings:

• config_id - Primary key
• name - Configuration key (unique)
• value - Configuration value (text)

Common configuration keys:

• mode - "dev" or "prod"
• file_manager - Enable/disable file manager
• revision - Comma-separated list of models with revision tracking
• two_factor_authentication - Enable/disable 2FA
• access_token_expire - JWT access token lifetime (seconds)
• refresh_token_expire - JWT refresh token lifetime (seconds)
• fs - Filesystem configurations (JSON)
• menus - Custom menu definitions (JSON)

Sources: db.json233-263

EventLog Model

The EventLog table at db.json338-418 stores the complete audit trail:

• eventlog_id - Primary key
• class - Model class name
• id - Record primary key
• action - "Insert", "Update", or "Delete"
• source - Record state before change (JSON)
• target - Record state after change (JSON)
• user_id - User who performed the action
• created_time - Timestamp

All indexes and foreign keys are described in the EventLog and Revision System section above.

Sources: db.json338-418

UserLog Model

The UserLog table at db.json265-336 tracks user sessions:

• userlog_id - Primary key
• user_id - Foreign key to User
• login_dt - Login timestamp
• logout_dt - Logout timestamp
• ip - Client IP address
• result - "SUCCESS" or "FAILURE"
• user_agent - Browser user agent string
• jti - JWT token identifier (for session tracking)
• last_access_time - Last activity timestamp

Used for session management, login history, and account lockout logic.

Sources: db.json265-336

Permission Model

The Permission table at db.json477-499 stores role and user-specific permissions:

• permission_id - Primary key
• role - Role name to grant permission to
• value - Permission string (e.g., "user.create")

Permissions can be assigned to roles or individual users. The RBAC system loads these at startup.

Sources: db.json477-499

Translate Model

The Translate table at db.json551-605 provides internationalization:

• translate_id - Primary key
• language - 5-character language code
• name - Translation key
• value - Translated text

Indexed on language and name for efficient lookup.

Sources: db.json551-605

CustomField Model

The CustomField table at db.json607-675 enables runtime schema extension:

• custom_field_id - Primary key
• name - Field name
• label - Display label
• model - Model class to extend
• type - Field type (text, select, etc.)
• placeholder - Placeholder text
• options - Field options (JSON)
• validation - Validation rules
• default_value - Default value (JSON)
• order - Display order
• help - Help text

This allows adding custom fields to models without schema changes.

Sources: db.json607-675

SystemValue Model

The SystemValue table at db.json501-549 stores lookup values:

• systemvalue_id - Primary key
• name - Category name
• value - Lookup value
• status - Active/inactive flag
• language - Language code for localized values

Used for dropdowns, select lists, and other enumerated values.

Sources: db.json501-549

MailLog Model

The MailLog table at db.json420-475 logs all outgoing emails:

• maillog_id - Primary key
• created_time - Send timestamp
• from - Sender email address
• to - Recipient email address
• body - Email body (HTML)
• subject - Email subject
• from_name - Sender display name
• to_name - Recipient display name
• altbody - Plain text alternative
• host - SMTP host used

Provides email delivery audit trail and debugging information.

Sources: db.json420-475