Database qualification

Application contract target: MySQL 8.0 and MariaDB 10.11, using pinned PyMySQL 1.2.3. Real disposable instances of both engine lines passed the operation tests on 2026-10-16. This evidence applies to Database/mysql-auth and its fixed API queries; it does not qualify the 72 disabled legacy migrations or establish support for all future engine versions. PostgreSQL remains a separate experimental foundation.

The new stream uses InnoDB, AUTO_INCREMENT audit IDs, validated CHAR(36) UUIDs, JSON service scopes, UTC DATETIME(6), binary ASCII email/hash/UUID keys, composite tenant foreign keys and unique tenant/email and tenant/token keys. It uses no RETURNING, JSONB, policies, PostgreSQL casts or implicit RLS. Generated UUIDs come from the API rather than engine UUID functions. Schema collation controls username uniqueness; normalized email equality is explicit. Existing global legacy user uniqueness and company ownership are not silently converted.

python Database/migrate.py validate --engine mysql
python Database/migrate.py validate --engine postgresql
python Database/migrate.py validate --engine mysql --stream auth
python Database/migrate.py dry-run --engine mysql --stream auth

Offline modes validate checksums and exact file coverage; they do not prove SQL execution. Full filename stems are unique deterministic migration identities, so duplicate numeric prefixes 24 and 26 retain legitimate historical content without renumbering/checksum changes. Manifest order, rather than numeric prefix alone, is authoritative. Legacy install.sql is not a portable installer and must not be used to bypass the disabled manifest.

Application schema installation is a separate operator action. After target review, a verified backup and isolated restore drill, supply a mode-0600 administrator env with ENV, DB_ENGINE=mysql, DB_HOST/PORT/NAME/USER/PASSWORD:

DB_MIGRATION_ENV_FILE=/secure/migration.env python Database/migrate.py apply --engine mysql --stream auth --confirm

No production migration was run. Production additionally requires the existing DATABASE_CONFIRM_PRODUCTION_MIGRATIONS=1 gate. This is an operator assertion of backup/target review, not automatic proof that a backup can be restored.

MySQL obtains a session GET_LOCK; its asi_auth_migrations ledger records ID, checksum, running/applied/failed status, start time and completion time. MySQL DDL implicitly commits: failed or interrupted migrations may leave tables behind. Subsequent apply refuses changed, unknown, non-prefix or incomplete history. Do not delete history or blindly rerun. Inspect committed objects and restore an isolated backup or have an operator reconcile a forward recovery migration before resuming. Successful apply is safely repeatable. This stream permits reviewed additive CREATE TABLE/PROCEDURE/TRIGGER statements; no automatic destructive migration escape is offered.

PostgreSQL uses advisory locking and per-migration transactional DDL/history. Its credential file must explicitly specify DB_ENGINE=postgresql. No PostgreSQL statement is sent to MySQL. Preserve its existing policies and experimental status.

Tests cover fresh schema creation, repeat apply, ledger tampering/interruption, foreign-tenant operations, uniqueness, single-use concurrency, rollback, timeouts, connection recovery and restricted runtime privilege denials. Backup/restore of the new contract, interrupted-process fault injection, full legacy module semantics and PHP production compatibility remain unverified. See audit.

Authoritative semantics: MySQL named locks, implicit commits, and PyMySQL connection options.

Dual-interface extension

The auth stream now additionally contains auth_002_direct and auth_003_limits. 002 adds a direct-account registry, shared user lookup/rename routines, restricted session-user wrappers and an atomic profile audit trigger. 003 adds a bounded-row per-service rate counter table. The migration parser recognizes standalone DELIMITER directives for reviewed additive routines/triggers; it does not execute a MySQL client command stream or arbitrary SQL submitted by an API caller.

When an administrator creates routines/triggers, specify a retained non-root DB_ROUTINE_DEFINER in the protected migration file. MySQL binary-logged trigger installation requires appropriate migration privileges; runtime accounts do not receive them. Existing auth_001 checksums were preserved. Prior installations require the additive migrations before the revised API readiness succeeds. No legacy migration was enabled and no existing application data was imported.

Both engine lines passed real direct TLS/PHP/HTTPS consistency tests; see dual-interface documentation. Safe restart still refuses failed/partial history.