# PostgreSQL validation

Phase 6 milestone 3 adds a repeatable PostgreSQL 17 backend regression runner and populated migration/backup-restore checks. It uses synthetic accounts, stock, approvals and security state. The existing SQLite preview and legacy MySQL/PHP website remain separate.

## Run locally

Install the API dependencies and PostgreSQL 17 binaries. From `platform/`:

```sh
apps/api/.venv/bin/python scripts/check-postgres.py --repeat-concurrency 5
```

The default binary directory is `/opt/homebrew/opt/postgresql@17/bin`. On another machine, set `PB_POSTGRES_BIN` to the PostgreSQL 17 bin directory. The runner checks the major version; it does not install PostgreSQL or register a system service.

For a focused rerun, pass test paths or pytest node IDs:

```sh
apps/api/.venv/bin/python scripts/check-postgres.py apps/api/tests/test_mfa.py apps/api/tests/test_inventory.py
```

The runner creates its own marked cluster under ignored `.local/postgres-validation`, binds only `127.0.0.1:55437`, disables Unix sockets, generates a private owner-only password and uses SCRAM authentication. It creates `pb_test_validation`, runs every Alembic migration and `alembic check`, then runs pytest. The cluster stops in `finally` after success or failure. It refuses an unmarked existing cluster or one already running. The cluster directory remains for later runs; no system PostgreSQL service is started.

API configuration for the test process points to the isolated database. Live email, WhatsApp and AI are disabled; integration tests supply their existing synthetic adapters/configuration. No external model or message service is needed. The runner does not change preview credentials, the preview database, private MFA keys or the live site.

## Database isolation and coverage

`PB_TEST_POSTGRES_URL` explicitly selects the test backend. The helper refuses non-loopback hosts, drivers other than psycopg, query options and database names outside the `pb_test_*` pattern. Guard tests enforce these restrictions. Ordinary runs without that variable keep SQLite behavior.

Most database tests use a fresh, randomly named PostgreSQL schema with independent pooled connections and bounded lock/statement timeouts. The same application tests and assertions run on both databases, including the existing concurrency cases. Schema cleanup drops only names created by the current test process. These fixtures use ORM-created schemas; the separate migration checks exercise actual Alembic migrations on PostgreSQL.

A few tests remain deliberately database-independent or SQLite-specific, including SQLite byte-preservation/migration checks. The terminal reports how many test cases used the PostgreSQL schema fixture. A separate PostgreSQL-only case uses disposable databases for populated migration and logical backup restoration. Passing the suite is not a claim that every pure unit or SQLite file check queried PostgreSQL.

Repeated concurrent scenarios cover competing reservations for the same device/demand, quotation edits versus approvals, order mutations, warehouse movements, returns, payment ledger changes, worker leases, duplicate imports/webhooks, cross-administrator deactivation, sign-in versus disable, and one-time MFA/recovery consumption. Repetition increases confidence in these specific interleavings; it is not a production capacity benchmark, exhaustive scheduling proof or high-availability test.

## Populated upgrade and logical restore

The storage test creates a disposable database at revision `a25d439bef76`, seeds synthetic users, password hashes, an existing session and a company, then upgrades to the current head and compares every existing table value. `alembic check` must pass.

It then runs actual API quotation approval/order confirmation and MFA enrollment against the migrated database, dumps it using PostgreSQL 17 `pg_dump`, restores into another disposable database using `pg_restore`, and compares all table contents. With the corresponding private test encryption key available, restored API checks prove that the order still reserves the selected IMEIs, password-only sign-in fails, and a backup code works once. Both temporary databases are removed afterward. This tests a logical database backup with the same encryption key supplied; it does not test production key escrow or attachment/object-store restoration.

## Compatibility correction

The initial PostgreSQL run exposed a company-history error: malformed JSON text aborted the report during a cast, while SQLite safely excluded it. History now guards the value with PostgreSQL's `IS JSON` predicate inside a `CASE` before casting, preserving the original missing-attribution behavior. A filter alone would not establish safe expression evaluation order. See [PostgreSQL JSON testing](https://www.postgresql.org/docs/17/functions-json.html) and [locking behavior](https://www.postgresql.org/docs/17/explicit-locking.html). No schema change or business-data rewrite was needed.

## Remaining pilot work

The local test role can create/drop schemas and databases; it is not a production runtime role. Hosted staging still needs restricted database roles, TLS/network policy, secrets management, monitored workers, deployment/restart procedures, representative load tests and operator recovery procedures. Production backups need retention, off-host copies, encryption-key recovery, attachments and timed restore drills. Physical iOS/Android testing, signing/distribution, independent security review and the deferred live integrations remain pending. See VALIDATION.md for exact run results and ROADMAP.md for the remaining phases.