phpengine/specs/conventions-database.md

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

Database Conventions

Connection

OpenMind\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

omlink.sql is the tracked schema source. It defines four tables:

Table	Engine	Role
users	InnoDB	Accounts, username-prefixed password hashes, serialized roles, optional avatar.
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 and is loaded with allowed_classes => false.
• 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 migration framework or seed command. Any schema change must account for the dump, existing mapper field names, and the database-level uniqueness constraint. Do not document a migration workflow that does not exist.

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