ASI Database Node API

Isolated MySQL v14 deployment and acceptance

New deployments use MySQL 8.0 on Ubuntu 24.04, from Ubuntu's maintained security repository. MariaDB 10.11 is supported by the application adapter and auth migration stream; its managed server installation is not automated. PostgreSQL 16 remains a selectable application backend and retains its separate tenant/ledger foundation.

Ubuntu publishes MySQL 8.0 packages and security updates for 24.04: package inventory, security advisory, Ubuntu lifecycle. The installer records the observed server patch version in shared/mysql-server-version; no installed v14 version is claimed before deployment. Security package updates remain enabled; dependency Python versions remain pinned in requirements.lock.

Implementation checklist and audit scope

Engine configuration

DATABASE_ENGINE is canonical: mysql (default), mariadb or postgresql. DB_ENGINE is a retained compatibility alias; when both are present they must agree. Unknown engines fail before application listeners open. No engine is inferred from installed drivers. mysql/mariadb share PyMySQL's MySQL-compatible transport; the supported server families tested are MySQL 8.0 and MariaDB 10.11. This does not make vendor specific JSON, stored procedures or the legacy SQL catalog portable.

Use runtime.env.example with private credentials, mode 0600, outside source control. Set port 3306 for mysql/mariadb, 5432 for postgresql. DATABASE_ENGINE=postgresql selects Psycopg, UTC timestamps, bounded statement/lock/connect timeouts and the PostgreSQL auth stream. All application deployments require DB_NAME/USER/PASSWORD. Remote servers require DB_SSL_CA; adapters enforce CA and hostname verification. APPLICATION_API_ENABLED=1 requires the auth stream, including its migration ledger. Pool size defaults to 8 (maximum 64). Connections are closed after driver failures; requests are not transparently retried after uncertain writes. Shutdown drains idle connections. No schema creation happens in the HTTP worker.

Target and preservation

production:13 retains its original VM/group/domain/engine. Its preserve flag blocks cleanup. Existing interfaces, including ./run.sh --13 --staging, still parse. The local launcher containing --clean was deliberately left unchanged; cleanup now requires explicit version, --confirm-clean and, for production, --allow-production-destruction after reviewing --clean --plan.

production:14 selects RG-ASI-DATABASE-NODE-API-MYSQL-V14 and asi-database-node-api-mysql-v14, with independent networking, NSG, IP, disk and SSH key paths. The candidate validation name is asi-database-node-api-mysql-v14.arybit.co.ke. Confirm DNS control/availability before setting validation_dns_verified=true. Configure a reviewed age public backup recipient before provisioning; keep its private identity off-host.

The observed account/tenant and requested Brazil South SKU passed read-only preflight. As of the audit, v13's VM returned ResourceNotFound, while its resource group remained and canonical DNS still resolved to its previous IP. This work did not delete or change it. Restoring rollback availability requires separate operator investigation; do not recreate it using this migration target.

bash run.sh --14 --no-auto --plan
bash run.sh --14 --no-auto --preflight
# After recipient/DNS prerequisites and reviewed source commit:
bash run.sh --14 --no-auto --provision
# Complete/pin the dedicated SSH key pair and known_hosts through trusted Azure
# Run Command, and point ONLY the validation hostname at the observed new IP.
bash run.sh --14 --no-auto
# Safe candidate rerun with the same engine, database and retained credentials:
bash run.sh --14 --no-auto --core

--no-auto avoids publication and implicit account/network setup. Prepare the SSH key pair at the target's distinct paths before --provision (ssh-keygen -t ed25519), and restrict private-key mode to 0600. Deployment additionally requires a reviewed source commit; it will not package uncommitted implementation changes. Automatic setup retains explicit managed engines and v14's reviewed administrator CIDR.

Only 80/443 are public. SSH remains 13.71.3.99/32. The database and X protocol bind to loopback. Do not change SSH to the workstation's current address automatically. A blocked SSH route is a deployment prerequisite, not permission to broaden access. No v13 DNS record, certificate or database is touched by v14.

Server storage, accounts and schema

The coordinator verifies the owned named data disk at Azure LUN 0 before remote work. The installer formats only an explicitly enabled, unmounted disk with no filesystem, children, mounts or wipefs signatures. It records the filesystem UUID and target owner. Existing unrecognized filesystems/data directories fail closed. It mounts by UUID at /var/lib/mysql and requires that mount for mysql.service. Back up /etc/fstab and retain this volume when replacing the VM.

The server sets utf8mb4/utf8mb4_bin, durable commit/binlog flushing, connection limits and idle/lock timeouts. Package defaults supply database log rotation. The OS API account is unprivileged. V14 operator pages require independent HTTP Basic authentication over HTTPS; protected credentials are retained in shared/operator-ui.env and never printed. After successful promotion the bootstrap setup service is disabled and its initial token removed. Maintenance uses SSH and the protected CLI; re-enabling bootstrap is an explicit operator action. Root provisioning uses the Unix socket. A separate local schema owner has database-scoped DDL rights and owns routine execution; the runtime receives table-specific SELECT/INSERT/UPDATE, append-only audit INSERT, schema-ledger SELECT and two routine EXECUTE grants. It cannot create users or schemas or modify service identities. No root password is exposed. Runtime grants are checked for global/DDL/delete privileges on retries. Existing passwords are never reset. Independent schema/runtime secrets remain in protected shared files; the API receives only runtime credentials.

Managed v14 installs the four mysql-auth migrations with a locked, checksummed ledger. MySQL DDL is not transactional: interrupted/changed history blocks retries for operator reconciliation. PostgreSQL auth has two transactional migrations. The PostgreSQL tenant/ledger stream is independently tested and must use a separate database/history from postgresql-auth. Existing manifests are not edited in place.

python Database/migrate.py validate --engine mysql --stream auth
python Database/migrate.py validate --engine mariadb --stream auth
python Database/migrate.py validate --engine postgresql --stream auth
# Reviewed private migration environment; production additionally requires a
# verified backup and DATABASE_CONFIRM_PRODUCTION_MIGRATIONS=1.
DB_MIGRATION_ENV_FILE=/secure/schema.env python Database/migrate.py apply --engine postgresql --stream auth --confirm

Managed initialization is only for the new schema. Never point it at a legacy production database to "convert" it. Before upgrading a populated v14 database, create and restore-test an encrypted backup, then apply reviewed additive migrations. Managed populated-schema upgrades require a matching reviewed restore-acceptance marker and create a fresh encrypted backup before pending migrations. The provisioner's initial schema install does not prove production recovery.

Application contract and security

User lookup/create/update, status context, login failure lockout, sessions/revocation and atomic verification/reset storage run on both engines. All scopes derive from registered server-side tenant identities. UUID/field validation, parameterization, request limits, shared per-service quotas and non-sensitive errors remain enabled. GET /api/v1/status requires diagnostics:read and reports the configured engine, observed server version and verified schema readiness without connection details.

Credential context no longer returns password_hash. This is an intentional security change: existing clients expecting a hash must be reconciled before rollout. The service does not implement a new password-verification algorithm. It accepts only encoded bcrypt/Argon2 hashes for storage. The actual authentication client's hashing, refresh lifecycle and request contract remain unverified; do not onboard it until its owner supplies that contract and consumer tests pass.

Use backend/service-identity.py with the separate protected schema environment to register narrowly scoped credentials. It supports both engines. Rotate by registering a new ID into a new 0600 output file, updating the consumer over a trusted channel, then revoking the old ID. No production secret is generated into source control.

Direct access

API-only is the v14 default. Existing MySQL listener/account planning tools remain an explicitly reviewed opt-in path; no public database-port rule is applied here. Use docs/dual-interface.md and ops/plan-mysql-interface.py only after actual server, private interface, source CIDRs and an independent database TLS certificate are verified. All-public 0.0.0.0/0 needs separate authorization. Direct PostgreSQL NSG, pg_hba.conf and TLS provisioning are not automated; its application backend is supported independently. HTTPS success never proves database TLS.

Backup, retention, restore and monitoring

Set backup_recipient to the operator's age public key. The private key belongs in off-host recovery custody. Managed MySQL uses the separate schema identity for consistent single-transaction data/routine/trigger dumps; tablespaces are excluded. No global runtime grant is needed. PostgreSQL pg_dump uses a separate identity. Dumps and runtime metadata are packed inside the protected host boundary, encrypted before leaving it, and checksummed. Temporary plaintext is removed after encryption. The daily systemd timer runs at 02:00 UTC (05:00 Nairobi), plus a random delay up to 15 minutes. Unit failure triggers a local daemon.alert syslog entry and is visible in journald/systemctl; external alert delivery and off-host backup replication are operator prerequisites, not implemented claims.

At least 2 GiB free space is checked before backup; size larger volumes for dump and archive scratch overhead. A five-minute systemd storage monitor fails below 2 GiB or 10% free space on application/database filesystems. Thirty-day retention is configured for v14, but deletion remains disabled until a protected shared/restore-acceptance.json records matching owner and passed=true after an operator-reviewed restore. Always retain at least seven newest backups. Do not create this marker from a checksum-only check or disposable CI result.

Restore to an empty asi_restore_* database using ENV=test and a separate protected RESTORE_ENV_FILE, never the active database:

RESTORE_ENV_FILE=/secure/restore.env python deploy-database-api/ops/restore-backup.py /secure/backup.tar.age --identity /secure/recovery.agekey --apply --confirm

Recreate non-root routine definers/grants on the isolated server. MySQL binary logging may require temporary log_bin_trust_function_creators on that isolated restore server for trigger creation; restore the setting afterward. Do not grant SUPER to the runtime or relax the active production database for this drill. Validate row counts, constraints, ledger checksums, representative values and application operations after import. Test-only automation is available as python tools/test-backup-restore.py --help; it never proves production recovery.

Acceptance and cutover

python tools/verify-application.py --base-url https://asi-database-node-api-mysql-v14.arybit.co.ke --expected-engine mysql --expected-revision REVIEWED_COMMIT --credential-file /secure/acceptance.credential --user-id FIXTURE_UUID

Each gate reports pass/fail/blocked. Diagnostics scope verifies the deployed engine/authenticated database/schema. A fixture read and unauthorized denial alone leave full application acceptance blocked. Consumer compatibility, live backup, restore, firewall/listeners and certificate-renewal evidence remain separate gates; untested checks cannot be marked passed by supplying an arbitrary JSON report.

No live-data export/import or DNS cutover was performed. Before any future migration:

  1. Inventory PostgreSQL schemas/tables, constraints, indexes, sequences, row counts, RLS policies, data types and all consumer queries through a read-only identity.
  2. Map each required object to reviewed MySQL migrations. Map UUIDs to canonical 36-character strings, UTC timestamp semantics explicitly, JSON operations and indexes separately, and case-sensitive uniqueness to binary collations. RLS and deferred ledger constraints have no automatic MySQL equivalent here.
  3. Create target auth schema. If product/legacy data is needed, design an explicit exporter/importer after this inventory; no generic converter is implemented.
  4. Quiesce writes for a consistent snapshot or implement tested change capture. Copy hashes verbatim without logging them. Validate counts, keys, indexes, representative records and consumer behavior; reconcile writes before release.
  5. Verify encrypted production recovery, credentials, TLS, firewall/listeners, certificate renewal and rollback availability. Obtain cutover authorization.
  6. Lower DNS TTL through the actual DNS provider, save the old record/value/TTL, then replace only the canonical A record with the observed accepted v14 IP. Provider-specific commands cannot be supplied until its account/zone is known.
  7. On rollback restore the saved A record and consumer configuration, then verify old HTTPS/readiness. Do not replay target-only writes without a reconciliation plan. Keep both databases and backups throughout the acceptance window.

The old VM is currently missing, so a tested rollback target is an unresolved gate. No zero-downtime or consumer-compatibility claim is made.