Repository and implementation audit — 2026-10-16

Baseline: clean main at 75d01f8, following 3c9da6e, 3866453 and 4f68f47. No AGENTS.md was found in the workspace ancestry or repository. No existing user edits were replaced. No PHP authentication repository exists under /workspaces. No private runtime file or credential value was printed. Work remains uncommitted for review.

Observed architecture and initial gaps

FastAPI initially exposed diagnostics only. The default DB_ENGINE was PostgreSQL; readiness opened a new bounded driver connection for SELECT 1. MySQL had a probe but no application pool, authenticated service operations or qualified schema. The existing operator console is a separate authenticated loopback service exposed through the gateway, not an application service identity mechanism. Azure scripts support an existing local DB but managed bootstrap provisions PostgreSQL only.

Every root SQL file was scanned by tools/audit-sql.py: 73 files including install.sql, 492 static findings. The report records repeated table definitions, disabled FK checks, routines, embedded seeds, destructive operations and session tenant context. Legacy 08–12 filenames do not establish PostgreSQL RLS semantics. The root stream is primarily MySQL SQL and remains disabled; PostgreSQL policies belong to the separate experimental foundation. No mass keyword rewrite or legacy qualification claim was made. Full filename IDs already resolve duplicate 24/26 numeric prefixes in deterministic manifest order; historic names/checksums were preserved.

Static source inventory is in source-audit.json. It scans tracked/new public Python, shell, SQL, workflows, documentation and UI sources, excluding ignored credentials. This is inspection evidence and a review aid, not an exhaustive semantic or live security certification. The application files, SQL manifests, migration runner, configuration, console/worker, runtime preparation, proxy/service renderer, coordinator, dependency workflows and available tests received focused review.

Initial Python lacked pytest. Pinned dependencies were installed in /tmp/asi-audit-venv; the pre-change baseline suite then passed 40 tests, skipped eight PostgreSQL console tests (no port supplied), and passed 11 unittest subtests. Sandbox execution stalled on local test-client operations; tests were rerun outside the sandbox. The baseline PostgreSQL CI workflow referenced a missing tests/test_postgresql_integration.py. Nginx and ShellCheck binaries are absent.

Implemented changes

An explicit opt-in mysql-auth stream and fixed /api/v1 operations now implement users, dedicated credential context, authentication events/lockout, sessions, revocation and atomic signup/password-reset consumption. This is a new documented consumer contract based on existing user/token concepts; it does not claim the unavailable PHP application is already compatible or silently import legacy users. Default PostgreSQL diagnostics and deployment interfaces remain intact.

Service registry hashes, constant-time verification, expiry/revocation, scopes and server-derived tenant IDs protect operations. A separate administrator CLI registers new credential IDs and revokes old ones; secrets are written to exclusive 0600 files. Ordinary user results omit credential hashes. Composite tenant FKs and fixed query predicates protect isolation. The API has no unrestricted SQL route.

A bounded PyMySQL pool owns transactions and connection lifecycle. Server execution limits, socket/lock/acquisition timeouts, rollback and connection discard protect failure paths. Remote DB hosts remain blocked pending a verified transport contract. No schema installation or SQLite fallback occurs at startup. Readiness checks visible contract metadata without granting audit SELECT to the runtime account.

MySQL apply is available only for the separate auth stream. It uses GET_LOCK and an explicit running/applied/failed checksummed ledger. Changed, interrupted or non-prefix history is rejected. MySQL DDL partial commits are documented; automatic retry is refused. Legacy MySQL apply remains blocked. PostgreSQL advisory locking and transactional apply remain. The final-audit ignore check was repaired for the existing tracked local target file, retaining separate checks against tracked private runtime/credential paths. A missing PostgreSQL CI test was repaired with real disposable installation, RLS and balanced-ledger rollback tests.

Request handling now bounds streamed bodies, applies a process admission limit, returns input-free errors and no-store responses, generates correlation IDs and logs route templates/service/status/duration. Security-sensitive mutations and credential reads add transactional audit records. Runtime grants are tested without DDL, registry mutation, audit read/delete or migration-ledger access.

FastAPI-derived OpenAPI, a drift check, a MySQL/MariaDB CI matrix, a TLS-verified read-only operation probe, consumer mapping, PHP cURL example and operator guides were added. The existing UI/operator contract remains separate; its PHP integration contractVerified flag is false. Requirements and lockfile needed no additions: PyMySQL was already pinned in both.

Verification and deployment evidence

See verification-results.md for exact reproducible verification invocations and observed results, and changed-files.json for the final path inventory. Real local MySQL/MariaDB and PostgreSQL tests are independent of CI execution. Added CI jobs have not run on GitHub in this session.

The existing staging plan passed with --13 --staging --plan --no-auto. Read-only staging preflight passed outside the network sandbox: Azure account/tenant checks, provider checks and selected VM-size checks succeeded; actual allocation remains subject to quota/policy. No deployment, migration, source publication, cleanup, production write, DNS change or infrastructure mutation was performed. No public TLS or authenticated staging-operation acceptance was observed. production-verify.sh was inspected but not executed against production.

Remaining acceptance gaps

• Legacy product modules and data migration remain unqualified. The isolated new schema is not a replacement for the whole historical SQL estate.
• Actual PHP SQL, identifiers, token formats, HTTP stack and transaction contract are unavailable. Refresh rotation/logout-all requirements remain unresolved.
• Staging requires a reviewed local MySQL/MariaDB target, installed schema, restricted accounts, service credentials and fixture users. Existing default managed PostgreSQL deployment cannot serve the new operations.
• New-schema backup/restore, abrupt migration process termination and every network failure mode were not exercised. Failed-ledger restart refusal was tested.
• Gateway quotas/private operator networking and retention policy require operator configuration. The admission guard is per process, not a distributed quota.
• Runtime owns cross-tenant DB access; database credential compromise crosses tenants. SQL-console administrators remain trusted and require separate access controls.
• No claim of production readiness, full legacy parity or live PHP integration is made.

The safest next step is to reconcile docs/php-integration.md with the actual PHP repository, review an isolated staging schema/data mapping, then run the documented migration and operation acceptance steps under separate operator authorization.

Follow-up: direct TLS plus HTTPS

The subsequent request requires simultaneous MySQL wire-protocol and HTTPS access, then explicitly authorizes any-public-IP reachability on TCP 3306 with strong individual accounts. That supersedes the earlier loopback-only/fixed-source policy for the direct interface; it does not authorize public administration.

Implemented: a common application transaction module, shared user lookup/rename routines, direct account registry and execute-only tenant wrappers; verified-TLS remote MySQL transport; database-coordinated per-service quotas; reusable separate PHP PDO and HTTPS clients; additive auth_002_direct/auth_003_limits migrations; non-root routine definer support with administrator trigger installation; listener/ network planning and an independent opt-in host installer; direct account CLI; TLS-aware backup client options; private operator gateway; and disposable real TLS/PHP/HTTPS acceptance tooling/CI. Existing edits were preserved. New clients and both interfaces use the same schema, not a second authentication database.

Observed tests: MySQL 8.0.46 and MariaDB 10.11.19 each passed 13 application/migration checks over verified TLS, plus scripted PHP direct/real-HTTPS consistency, rollback, credential rejection, tenant/privilege isolation, redirect non-forwarding and certificate rejection checks. Direct account provisioning for a separate core service passed on both. Valid server trust and hostname succeeded; wrong CA, wrong hostname, expired database certificate and explicitly plaintext connections were rejected. There was no TLS verification bypass or log-bin permission bypass. The first harness CA lacked strict-verification extensions; certificates were corrected rather than relaxing verification. MySQL trigger creation used elevated migration credentials and an explicit non-root definer.

Read-only production Azure inventory verified account/tenant, NIC/NSG, effective routes/rules and DNS. DNS and the VM public IP matched 20.226.56.146, with private NIC 10.30.1.4. Existing rules permit restricted SSH and public 80/443, without a 3306 rule. Certificate-verified public diagnostics returned 200; TCP 3306 timed out. Pinned SSH inventory timed out, so actual host listener/service/firewall/database engine and certificate installation remain unverified. The exact proposed public 3306 rule is documented in public-connectivity-plan.md and a private .state artifact. No cloud rules, production accounts, DNS, certificates, services or schema changed.

The direct contract covers lookup/profile update and explicitly authorized minimal credential context; remaining authentication/session/reset workflows use HTTPS. The reusable PHP HTTP client wraps lookup/rename, not every documented endpoint. The actual PHP application was not modified because its repository is absent. Legacy data mapping and migration, engine-specific abuse monitoring, restored routines/definer ownership, a new-schema restore drill and external production valid-user acceptance remain unresolved. The independent host installer was syntax checked but not exercised against a VM. These are deployment/acceptance limits, not implied successes from local tests.

The completion pass resumed from af02742 without discarding committed work. It adds a regression proving that a direct credential reader cannot erase its disclosure audit by rolling back its connection, and regenerates stale shipped documentation. Credential reads have an explicit standalone transaction boundary; profile lookup/ rename retain caller-controlled transactions. Current verification details and skip boundaries are in verification-results.md. Production host SSH remains a reported timeout blocker; no network/host change is inferred from user continuation.

The final Azure-agent fallback successfully inspected the production host after SSH timed out: configured engine PostgreSQL, PostgreSQL active, MySQL/MariaDB inactive, and no listener on TCP 3306. The listener command itself succeeded. The earlier "host unverified" finding is superseded by this bounded inventory; MySQL installation/TLS/accounts/data migration remain unperformed. Azure Run Command was used only for read-only inventory, with no configuration changes. The final direct audit-persistence regression passed on both tested engines.