phpengine/specs/conventions-database.md

Read this when changing MySQL schema, F3 mappers, or data-integrity rules.

Database Conventions

Connection

Core\System\System creates the F3 DB\SQL connection from DSN, dbuser, and dbpass F3 globals. It uses PDO exceptions and enables persistent connections on Linux and all CLI requests. Keep credentials in configuration, not source code.

Schema Source

phengine.sql is the tracked schema source. It defines six tables:

Table	Engine	Role
users	InnoDB	Accounts, username-prefixed password hashes, serialized numeric role IDs, optional avatar.
roles	InnoDB	Persistent role catalog with stable IDs and editable labels.
inventory_items	InnoDB	Inventory Monitor demo SKU, name, quantity, and reorder-level records.
notifications	InnoDB	System notification records.
label_bindings	InnoDB	Legacy label/barcode data; no current PHP mapper uses it.
queuesystem	MEMORY	Legacy queued command records; no current PHP mapper uses it.

Active Mapper Rules

• User maps users; its roles column is a PHP-serialized array of unique integer role IDs and is loaded with allowed_classes => false.
• Role maps roles; role IDs are stable authorization keys while names and descriptions are editable metadata.
• InventoryItem maps the persistent Inventory Monitor demo table; SKU is unique and quantity/reorder level are non-negative integers.
• Notifications maps notifications; emptyAll() truncates the entire table.
• users.username is unique (uq_users_username). Keep it as the final concurrency safeguard even when application prechecks exist.
• UserController locks user rows with SELECT id FROM users FOR UPDATE inside transactions before checks that could remove the final administrator.

Change Constraints

There is no general migration framework. The explicit role-catalog migration creates missing role/inventory tables and seed records, then converts legacy serialized role names to numeric IDs; it must be run manually and is not part of application boot. Any schema change must account for the dump, existing mapper field names, and database-level uniqueness constraints.

See identity/spec.md and integrations/mysql.md.