ASI Database Node API

Verification results — 2026-10-16

Baseline main: 75d01f8. Work is uncommitted. Commands below were run from the repository root unless stated otherwise. Network/container/local-socket operations required sandbox escalation; no production credential values are included here.

Environment and dependency checks

python -m pytest -q
python -m venv /tmp/asi-audit-venv
/tmp/asi-audit-venv/bin/pip install -r backend/requirements.lock -r backend/requirements-dev.txt
/tmp/asi-audit-venv/bin/pip install pip-audit
/tmp/asi-audit-venv/bin/python -m pip_audit -r backend/requirements.lock --cache-dir /tmp/asi-pip-audit
/tmp/asi-audit-venv/bin/python -m pip check
/tmp/asi-audit-venv/bin/pip install -r tools/requirements-docs.txt

Initial system Python: no pytest. Sandboxed dependency download failed DNS; the same pinned install passed with escalation. Baseline pinned-environment tests: 40 passed, eight skipped, 11 subtests passed. pip-audit: no known vulnerabilities found in the runtime lockfile. pip check: no broken requirements. Audit tooling was installed only in the temporary environment, not production requirements.

Disposable database setup

docker info --format '{{.ServerVersion}}'
docker run -d --name asi-audit-maria -e MARIADB_RANDOM_ROOT_PASSWORD=1 -e MARIADB_DATABASE=asi_test -e MARIADB_USER=asi_test -e MARIADB_PASSWORD=disposable-test-password -p 127.0.0.1:13306:3306 mariadb:10.11
docker run -d --name asi-audit-mysql -e MYSQL_RANDOM_ROOT_PASSWORD=yes -e MYSQL_DATABASE=asi_test -e MYSQL_USER=asi_test -e MYSQL_PASSWORD=disposable-test-password -p 127.0.0.1:13307:3306 mysql:8.0
docker run -d --name asi-audit-pg -e POSTGRES_HOST_AUTH_METHOD=trust -p 127.0.0.1:15432:5432 postgres:16

Docker 29.8.0-1. Queried engine versions: MariaDB 10.11.19-MariaDB-ubu2204 and MySQL 8.0.46. PostgreSQL 16 container exercised the retained console/foundation. Trust authentication was used only in the disposable loopback PG instance. Do not use these test credentials/authentication settings on real services.

Temporary local helpers prepared restricted runtime grants and exercised the exact CI root-provisioning branch without printing generated disposable root credentials:

/tmp/asi-audit-venv/bin/python /tmp/asi-runtime-grants.py
/tmp/asi-audit-venv/bin/python /tmp/asi-ci-check.py
/tmp/asi-audit-venv/bin/python /tmp/asi_engine_versions.py

The CI provisioning branch passed 11 tests independently on each engine at that point. The checked-in fixture performs the same setup when TEST_MYSQL_ROOT_PASSWORD is supplied; TEST_MYSQL_RUNTIME=1 selects the prepared restricted test role.

Behavioral verification

/tmp/asi-audit-venv/bin/python -m pytest -q
TEST_MYSQL_PORTS=13306,13307 /tmp/asi-audit-venv/bin/python -m pytest backend/tests/test_application.py -q
TEST_MYSQL_PORTS=13306,13307 TEST_MYSQL_RUNTIME=1 /tmp/asi-audit-venv/bin/python -m pytest backend/tests/test_application.py -q
TEST_POSTGRES_DSN=postgresql://postgres@127.0.0.1:15432/postgres TEST_CONSOLE_POSTGRES_PORT=15432 /tmp/asi-audit-venv/bin/python -m pytest backend/tests/test_console_database.py tests/test_postgresql_integration.py -q
TEST_POSTGRES_DSN=postgresql://postgres@127.0.0.1:15432/postgres TEST_CONSOLE_POSTGRES_PORT=15432 TEST_MYSQL_PORTS=13306,13307 TEST_MYSQL_RUNTIME=1 /tmp/asi-audit-venv/bin/python -m pytest -q -rs
/tmp/asi-audit-venv/bin/python -m pytest backend/tests/test_security.py -q
/tmp/asi-audit-venv/bin/python -m unittest discover -s tests -v
for source in ui/js/*.js; do node --check "$source"; done

Final combined suite: 72 passed, no skipped tests, 11 subtests passed. A final focused logging/configuration suite passed 10 tests after flattening structured log fields. The separate unittest command passed 22 tests. JavaScript syntax passed. One dependency warning remains: Starlette deprecates its httpx-based test client; current pinned tests still pass. No dependency pin was changed to conceal it.

Earlier failures found and repaired: MySQL standalone SLEEP returns an interruption value, so timeout verification now uses an aggregate workload that raises on interruption; PostgreSQL ledger triggers require UPDATE privilege for row locking, which the disposable role fixture now grants; MySQL/MariaDB metadata casing differed, so readiness uses explicit aliases. Assertions requiring timeouts, rollback and readiness success were preserved. Rejected privilege operations fail safely.

Integration covers application routes, scopes/invalid/disabled/expired keys, tenant isolation, uniqueness, no credential leakage through ordinary user responses, input/body limits, signup/reset replay, concurrent one-time consumption, session revocation, atomic lockout, rollback, expiry, query timeout/recovery, migration history checks, restricted runtime privileges and credential CLI rotation.

Static, contract and documentation checks

/tmp/asi-audit-venv/bin/python tools/audit-sql.py
/tmp/asi-audit-venv/bin/python tools/audit-source.py
/tmp/asi-audit-venv/bin/python tools/verify.py
DATABASE_PYTHON=/tmp/asi-audit-venv/bin/python bash deploy-database-api/verify-scripts.sh
/tmp/asi-audit-venv/bin/python Database/migrate.py dry-run --engine mysql --stream auth
/tmp/asi-audit-venv/bin/python tools/export-openapi.py
/tmp/asi-audit-venv/bin/python tools/export-openapi.py --check
/tmp/asi-audit-venv/bin/python tools/build-docs.py
/tmp/asi-audit-venv/bin/python tools/build-docs.py --check
/tmp/asi-audit-venv/bin/python tools/final-audit.py
git diff --check

All applicable checks passed. verify.py checks Bash/Python syntax, workflows, public JSON, manifests and jq compilation; it reports missing ShellCheck. SQL audit read 73 root SQL files and recorded 492 findings; static findings are not runtime qualification. OpenAPI matches generated routes/schemas. Generated documentation includes the application guide, PHP assumptions and new SQL stream references. Final audit initially failed because Git check-ignore omits the existing tracked deployment.targets.json even though an ignore rule exists. The check now uses --no-index to validate ignore rules and separately rejects tracked private runtime and .private paths. Existing tracked target settings remain excluded from release packaging; no operator settings were untracked or changed. Final audit checks the changed-file inventory, private-key markers, ignore rules, unchanged SQL license and whitespace. See changed-files.json for exact file paths.

Deployment verification

DATABASE_PYTHON=/tmp/asi-audit-venv/bin/python bash run.sh --13 --staging --plan --no-auto
DATABASE_PYTHON=/tmp/asi-audit-venv/bin/python bash test-preflight.sh --13 --staging --no-auto

Plan passed. Sandboxed preflight failed Azure network access; the identical read-only preflight passed outside the sandbox. Account/tenant/provider and selected VM-size checks passed for staging v13. No capacity allocation was attempted.

CI workflows were edited but not executed on GitHub. No staging or production deployment, public TLS test, production-verify.sh execution, live application operation probe, PHP end-to-end test, production migration, backup restore or cloud mutation was performed. No Nginx binary was available for tools/test-nginx.py. tools/test-postgres.py requires a local binary directory and was not invoked; its PostgreSQL acceptance target was repaired and run directly against Docker instead. Legacy MySQL migrations remain blocked. These are explicit acceptance limitations.

Temporary test containers were removed with:

docker rm -f -v asi-audit-maria asi-audit-mysql asi-audit-pg

Private production/staging configuration and all production database content were left untouched. The temporary Python environment remains available for review.

Dual-interface follow-up results

Commands executed after the initial 72-test baseline:

docker build -f tests/dual/Dockerfile.php -t asi-audit-php .
docker build -f tests/dual/Dockerfile.api -t asi-audit-api .
/tmp/asi-audit-venv/bin/python tools/test-dual-interface.py
/tmp/asi-audit-venv/bin/python -m pytest -q -rs
/tmp/asi-audit-venv/bin/python -m pytest tests/test_direct_planning.py backend/tests/test_security.py -q
/tmp/asi-audit-venv/bin/python -m pytest tests/test_direct_planning.py -q
/tmp/asi-audit-venv/bin/python tools/inspect-dual-network.py --target production:13 --output .state/dual-network-audit.json
/tmp/asi-audit-venv/bin/python tools/inspect-dual-host.py --target production:13 --network-audit .state/dual-network-audit.json --output .state/dual-host-audit.json
bash -n deploy-database-api/ops/install-mysql-listener.sh

The final dual harness passed on both MariaDB 10.11.19 and MySQL 8.0.46: 13 pytest checks per engine, plus actual PHP PDO/cURL checks against real TLS database and HTTPS API listeners. The harness also exercised the account provisioning CLI for a separate asi-core-node-api identity, native prepared calls, commit/rollback, direct/API mutual visibility, tenant denial, no base-table/admin rights, invalid password/unknown user, untrusted CA, hostname mismatch, expired server certificate, explicit plaintext rejection, HTTPS redirect refusal without credential forwarding and response-type validation. Containers and disposable volumes were removed in finally. This is independent of GitHub CI, which has not run in this session.

Intermediate failures were repaired: strict Python TLS required CA/server authority extensions; literal % in test account SQL needed escaping; PyMySQL's default TLS PREFERRED mode meant the plaintext negative test needed explicit ssl_disabled=True; and MySQL binary-logged trigger creation required elevated migration privileges with an explicit non-root definer. No certificate verification or database security check was disabled to obtain a pass.

The no-database regression run passed 50 tests and 11 subtests, skipping 20 explicitly gated database tests; the separate harness supplied the MySQL/MariaDB test evidence. A focused planning/security run passed 16 tests before the final planning checks were added. Final follow-up check counts are recorded below after completion. PostgreSQL integration was not rerun in this follow-up; its earlier disposable result remains historical evidence for the preserved path.

Azure network/DNS inspection passed read-only. Existing public HTTPS health/readiness returned 200 with certificate and hostname verification; TCP 3306 timed out from the workspace network. Pinned SSH inventory failed with SSH connection timeout; no host service/listener/certificate or production account inspection was completed. No external successful MySQL login, public MySQL TLS verification, production client cutover, new-schema restore, live application operation or deployment occurred.

The proposed network changes are reviewable in docs/public-connectivity-plan.md. The independent listener installer is source/syntax validated, not VM-execution verified. Existing private settings/credentials and existing production resources were not modified. API and server private keys are excluded from Docker build context; disposable PHP tests mount only client/test source and temporary fixtures.

Resumed completion checks

The workspace was resumed at committed baseline af02742 with a clean working tree; the previous temporary Python environment had been cleared. Restored it with:

python -m venv /tmp/asi-audit-venv
/tmp/asi-audit-venv/bin/pip install -r backend/requirements.lock -r backend/requirements-dev.txt -r tools/requirements-docs.txt

The completion regression command python -m pytest -q -rs under that environment passed 51 tests and 11 subtests, with 20 explicit database-gated skips. The real dual-interface harness independently runs 13 application tests on each engine, plus the PHP/TLS/consistency/security checks. PostgreSQL was not rerun in this completion pass. The existing Starlette test-client deprecation warning remains.

Added a direct credential audit regression: provision a dedicated authentication account with credential capability, fetch only its tenant's verification material, roll back the caller connection, then independently assert that the disclosure audit remains committed. The stored credential operation is deliberately standalone; PHP refuses it inside a profile transaction. This prevents a credential reader from erasing its disclosure event by rolling back. No password/hash is printed.

Source/manifests, OpenAPI drift, pip check, PHP client/test syntax and UI JavaScript syntax passed. Documentation initially failed its drift check and was rebuilt from source. Final source/SQL inventories and changed-file hygiene were refreshed. The current changed-file inventory describes changes after af02742; that commit contains the earlier implementation. No existing commit or operator setting was discarded.

Final dual harness result: passed on both MySQL 8.0.46 and MariaDB 10.11.19, including the additional dedicated direct credential capability and audit-after- rollback regression. Each engine's 13 pytest checks and PHP/TLS checks passed.

The production host inventory was completed using the read-only Azure-agent fallback:

/tmp/asi-audit-venv/bin/python tools/inspect-dual-host.py --target production:13 --network-audit .state/dual-network-audit.json --output .state/dual-host-audit.json --via-run-command

Subscription/tenant verification and the host listener command succeeded. Observed: configured PostgreSQL, PostgreSQL active, MySQL/MariaDB inactive, no TCP 3306 listener. This supersedes the earlier lack of host inventory. SSH itself still timed out. The Azure VM-agent invocation executed only the fixed bounded inspection; no service/account/schema/certificate/firewall change occurred. New MySQL production installation and external valid-user acceptance remain outstanding.