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](identity/spec.md) and [integrations/mysql.md](integrations/mysql.md).