DATABASE_DIRECT_URL is documented and deployed but never read by any code

[Hazzng]

Summary

DATABASE_DIRECT_URL is documented in CLAUDE.md and injected by our deployment (aca.yaml:38-39, from the database-direct-url secret), but no code reads it:

$ grep -rn "DATABASE_DIRECT_URL" src/ scripts/
(no matches)

runMigrations resolves its connection through loadTenantConfig() (src/api/server.ts), which reads only TENANT_DATABASES / DATABASE_URL (src/api/tenants.ts:32-41).

Impact

The deployment declares an intent — "run DDL off the pooler" — that the application silently ignores. Migrations connect through whatever DATABASE_URL points at. Combined with the session-scoped advisory lock (see the migrations issue), multi-replica boot has no working mutual exclusion, and nobody reading aca.yaml would know.

A secret is also being provisioned and mounted for no purpose.

Decision needed

We have settled on always connecting through the pooler (Azure Postgres built-in PgBouncer; no direct connection). Given that, the two options are not equal:

• Preferred — remove it. Delete the CLAUDE.md row and the aca.yaml env + secret, and make migrations pooler-safe instead (single transaction + pg_advisory_xact_lock). This matches how we actually deploy and removes a misleading knob.
• Alternative — implement it. Have runMigrations prefer DATABASE_DIRECT_URL when set. Only worth doing if we ever want a non-pooled path for DDL; it does not fix the advisory-lock bug on its own.

Either way the doc, the deployment, and the code should stop disagreeing.

Provenance

Found during pre-merge load testing of #162. Not introduced by that PR.

[Hazzng]

Decision made: remove it.

We are standardising on always connecting through the pooler (Azure Postgres built-in PgBouncer); there is no direct connection in production. So the direct-URL path has no consumer, and implementing it would be building a knob we would never turn.

Verified that nothing else in the codebase needs a non-pooled connection — migrations.ts:55 is the only session-scoped Postgres usage anywhere:

$ grep -rn "pg_advisory_lock(\|LISTEN\|NOTIFY\|SET SESSION\|CREATE TEMP\|VACUUM\|REINDEX\|CONCURRENTLY\|ALTER SYSTEM" src/ --include="*.ts" --include="*.sql" | grep -v pg_advisory_xact_lock
src/api/migrations.ts:55:  await sql.unsafe(`SELECT pg_advisory_lock(${MIGRATION_LOCK_KEY})`)

Everything else — the dialects, the blob GC CLI (blob-gc.ts:52 via dialect.transaction), the RLS context (SET LOCAL / set_config(..., true)) and every app lock (pg_advisory_xact_lock) — is already pooler-safe.

So the work here is:

1. Fix Migrations use a session-scoped advisory lock that breaks under transaction pooling #164 (single transaction + pg_advisory_xact_lock). That is the one and only blocker to never needing a direct connection.
2. Delete the DATABASE_DIRECT_URL row from CLAUDE.md, and the env entry + database-direct-url secret from aca.yaml:38-39.

One forward-looking constraint to record: with migrations wrapped in a single transaction, a future migration cannot use CREATE INDEX CONCURRENTLY, VACUUM, or REINDEX, since those cannot run inside a transaction. None exist today (verified across all seven files in src/sql-fs/migrations/postgres/). If one is ever needed it must run as its own statement outside the wrapper — which still works through a transaction pooler, but the runner would need to support it.