nexus migrations shell out to psql, forcing a CLI dependency into the runtime image

[roman1887]

What

nexus/app/migrations.py runs migrations by invoking the psql binary as a subprocess:

psql_command = [
    'psql',
    ...
]

This is the only thing that requires postgresql-client in the nexus runtime image.

Why it is worth changing

It caused a production outage. #241 hardened the image and replaced postgresql-client with libpq5 — correct for psycopg2 and asyncpg, which speak the wire protocol directly, but libpq5 does not ship the psql CLI. Every migration then failed with:

{"error": "[Errno 2] No such file or directory: 'psql'", "event": "Migration failed"}
RuntimeError: Database migrations failed

The failure mode was unusually hard to read: the lifespan raised, uvicorn exited 0, and Kubernetes reported reason: Completed rather than a crash. It presented as a reloader quirk, and identifying it required a cluster-side investigation. Fixed in #253 by reinstating the package.

The deeper problem is that the dependency exists at all:

• It is the only CLI binary the runtime image needs, and it pulls a package set unrelated to how the application otherwise reaches the database.
• It makes the image harder to slim down and blocks a straightforward move to a minimal base (Eliminate remaining CRITICAL CVEs: migrate nexus to an Alpine base #243, Investigate Alpine/musl base for schema-service and prediction workers #244).
• Nothing signals the dependency at build time. It is discoverable only by reading migrations.py or by watching the container fail at runtime.

The stated reason for using psql is to read applied migrations "to avoid connection locks". That is a real concern, but it is solvable in-process — the application already holds SQLAlchemy and asyncpg connections, and a short-lived separate connection achieves the same thing without a subprocess.

Suggested approach

1. Replace _get_applied_migrations_via_psql with a query over the existing asyncpg/SQLAlchemy connection, using a dedicated short-lived connection if lock avoidance genuinely requires one.
2. Confirm the original locking concern is addressed — worth checking the history behind that comment rather than assuming it was cosmetic.
3. Drop postgresql-client from nexus/Dockerfile.
4. Verify against a real PostgreSQL that all 35 migrations still apply, and that a second startup correctly detects them as already applied.

Not urgent

The image works today. This is about removing a fragile coupling that has already cost one outage, and unblocking the minimal-base work.

Related: #241, #253, #243