Database Schema (db.json)

The db.json file is the single source of truth for the database schema in the Light framework. It defines all tables, columns, indexes, and foreign key relationships in a declarative JSON format. The CLI tool uses this schema definition to generate database DDL scripts, PHP model classes, GraphQL types, and TypeScript definitions, ensuring consistency between the database structure and application code.

For information about the generated model classes and their behavior, see Base Model Class. For details on using the CLI commands that consume this schema, see Database Management Commands and Code Generation.

Sources: db.json1-676

---

Schema File Structure

The db.json file contains a JSON array of table definitions. Each table object specifies the table name, column definitions, primary keys, indexes, unique constraints, and foreign key relationships.

Table Structure Overview Diagram: Shows the hierarchical structure of the db.json schema format, mapping JSON properties to their semantic meaning in database design.

Sources: db.json1-676

---

Table Definition Format

Each table in the schema array is defined as a JSON object with the following top-level properties:

Property	Type	Required	Description
name	string	Yes	Table name (e.g., "User", "Role", "EventLog")
columns	array	Yes	Array of column definition objects
primary_key	array	Yes	Array of column names forming the primary key
keys	array	No	Array of index definitions (non-unique)
unique_keys	array	No	Array of unique constraint definitions
foreign_keys	array	No	Array of foreign key relationship definitions

Example: The User table definition structure from the schema:

Sources: db.json2-139

---

Column Definitions

Each column in the columns array defines a database field with its data type and constraints. The column definition object supports the following properties:

Property	Type	Required	Description
name	string	Yes	Column name (snake_case convention)
type	string	Yes	SQL data type (see supported types below)
length	integer	Conditional	Length for varchar/char types
nullable	boolean	No	Whether NULL values are allowed (default: true)
unsigned	boolean	No	For integer types, whether to use unsigned range
auto_increment	boolean	No	Whether column auto-increments (typically primary keys)
default	string	No	Default value for the column

Supported Data Types

The schema supports the following MySQL data types:

Type	Description	Length Required	Example
int	Integer (4 bytes)	No	User IDs, counts
tinyint	Small integer (1 byte)	No	Status flags, boolean values
varchar	Variable-length string	Yes	Usernames, emails, names
text	Long text	No	Configuration values, message bodies
json	JSON document	No	Settings, credentials, structured data
datetime	Date and time	No	Timestamps (created_time, updated_time)
date	Date only	No	Join dates, expiry dates

Example Column Definitions:

Sources: db.json4-126 db.json234-263 db.json339-380

---

Keys and Indexes

The schema supports three types of key definitions: primary keys, regular indexes, and unique constraints.

Primary Keys

The primary_key array specifies which column(s) form the table's primary key. Most tables use a single auto-incrementing integer column:

Composite primary keys are supported by listing multiple columns:

Sources: db.json128-130 db.json161-163

Regular Indexes (keys)

The keys array defines non-unique indexes to improve query performance. Each index has a name and a list of columns:

Example: The EventLog table uses indexes on class, user_id, and id columns for efficient audit trail queries:

Sources: db.json204-217 db.json385-404

Unique Constraints (unique_keys)

The unique_keys array enforces uniqueness on one or more columns. The structure is identical to regular indexes:

The User table enforces username uniqueness, while the Config table enforces unique configuration names.

Sources: db.json131-138 db.json255-262

---

Foreign Key Relationships

Foreign keys define referential integrity constraints between tables. Each foreign key relationship specifies source columns, the referenced table, referenced columns, and cascading behaviors.

Foreign Key Structure

Property	Type	Required	Description
columns	array	Yes	Column(s) in the current table
reference_table	string	Yes	Target table name
reference_columns	array	Yes	Column(s) in the target table
on_delete	string	Yes	Action on delete: CASCADE, SET NULL, RESTRICT
on_update	string	Yes	Action on update: CASCADE, SET NULL, RESTRICT

Cascading Behaviors

The schema uses different cascading strategies based on the relationship semantics:

CASCADE: When parent is deleted/updated, child records are also deleted/updated

• UserRole → User: Deleting a user removes all their role assignments
• UserLog → User: Deleting a user removes all their login history

SET NULL: When parent is deleted, foreign key column is set to NULL

• EventLog → User: Deleting a user preserves audit history but nullifies the user reference

Example Foreign Key Definitions:

Entity Relationship Diagram

Core Schema Entity Relationships: Shows the relationships between primary tables in the Light framework, including foreign key constraints and their cascading behaviors.

Sources: db.json218-230 db.json323-335 db.json405-417

---

Code Generation Pipeline

The db.json schema drives multiple code generation processes through the CLI tool (bin/light). The schema is consumed by various commands to maintain consistency between database structure and application code.

Schema-Driven Code Generation Pipeline: Illustrates how the db.json schema file flows through CLI commands to generate database DDL, PHP models, GraphQL controllers, validation types, and TypeScript definitions.

Generated Code Artifacts

Command	Output	Description
db:install	SQL DDL	Generates CREATE TABLE statements and executes them via migration tools
make:model	PHP classes	Generates model classes in src/Model/ with typed properties matching columns
make:controller	PHP classes	Generates GraphQL controller boilerplate with CRUD operations
make:input	PHP classes	Generates input validation classes for mutations
make:ts	TypeScript files	Generates TypeScript interface definitions for frontend type safety

Sources: bin/light1-24

---

Core Schema Tables

The db.json file defines the following core tables used throughout the Light framework:

Authentication and Authorization Tables

Table	Purpose	Key Columns
User	User accounts and profiles	user_id, username, password, email, credential, google, microsoft, facebook
Role	Role definitions with hierarchy	role_id, name, child
UserRole	User-to-role assignments	user_role_id, user_id, role
Permission	Role permissions	permission_id, role, value
UserLog	Session tracking and login history	userlog_id, user_id, jti, ip, login_dt, logout_dt

Configuration and System Tables

Table	Purpose	Key Columns
Config	Application settings (key-value store)	config_id, name, value
SystemValue	System-wide values with i18n support	systemvalue_id, name, value, language
Translate	Translation strings	translate_id, language, name, value
CustomField	Dynamic field definitions	custom_field_id, name, model, type, options, validation

Audit and Logging Tables

Table	Purpose	Key Columns
EventLog	Audit trail for all model changes	eventlog_id, class, id, action, source, target, user_id
MailLog	Email delivery log	maillog_id, from, to, subject, created_time

Table Usage Patterns

User Identity: The User table supports multiple authentication methods through dedicated columns:

• password: Local password hash
• credential: WebAuthn credentials (JSON)
• google, microsoft, facebook: OAuth provider IDs
• secret: TOTP secret for 2FA

Session Tracking: The UserLog table tracks all login attempts and active sessions:

• jti: JWT token ID for session identification
• login_dt, logout_dt: Session lifecycle timestamps
• last_access_time: Updated on each API request for session monitoring
• result: Login attempt result (success/failure reason)

Audit Trail: The EventLog table captures all create/update/delete operations:

• class: Model class name (e.g., "User", "Config")
• id: Primary key value of the modified record
• action: Operation type (insert, update, delete)
• source: State before modification (JSON)
• target: State after modification (JSON)
• user_id: User who performed the action (SET NULL on user deletion)

Sources: db.json1-139 db.json140-178 db.json179-231 db.json338-417 db.json476-499 db.json500-549 db.json550-605 db.json606-675

---

Schema Evolution and Best Practices

Schema Modification Workflow

1. Edit db.json: Modify the schema definition (add/remove tables, columns, keys)
2. Run db:install: Execute bin/light db:install to apply changes to the database
3. Regenerate Models: Run bin/light make:model to update PHP model classes
4. Update Controllers: Manually update or regenerate controllers if API changes are needed
5. Update TypeScript: Run bin/light make:ts to sync frontend type definitions

Naming Conventions

The schema follows consistent naming conventions throughout:

• Table Names: PascalCase (e.g., User, EventLog, CustomField)
• Column Names: snake_case (e.g., user_id, created_time, last_name)
• Primary Keys: {table_name_singular}_id (e.g., user_id, config_id, eventlog_id)
• Foreign Keys: Match the referenced column name (e.g., user_id references User.user_id)
• Indexes: Named after the column(s) they index

Common Column Patterns

The schema consistently uses these column names across tables:

• Primary Keys: {table}_id with auto_increment
• Audit Timestamps: created_time (datetime) for record creation
• Status Fields: status (tinyint) for active/inactive flags
• Language Fields: language (varchar(5)) for locale codes (e.g., "en-US")
• Descriptive Names: name (varchar) for human-readable identifiers
• JSON Storage: setting, options, credential for structured configuration

Sources: db.json1-676