MOTIR-3454 · (motir-core) The job listener LISTENs on the POOLED url — a transaction-mode pooler cannot hold a session, so the NOTIFY wake never arrives

MOTIR-3454Done

(motir-core) The job listener LISTENs on the POOLED url — a transaction-mode pooler cannot hold a session, so the NOTIFY wake never arrives

The Postgres job engine's latency path is dead in production, and its own design notes explain why without noticing that they do.

lib/jobs/engine/notify.ts:79 resolves the listening connection as:

const connectionString = opts?.connectionString ?? process.env['DATABASE_URL'];

and scripts/worker.ts:61 calls listenForQueuedJobs(() => worker.notify()) with no options, so that fallback is what production uses. Per README.md:155, DATABASE_URL is the pooled Neon endpoint; DATABASE_URL_UNPOOLED is the direct connection. Neon's pooler is PgBouncer in transaction mode, where LISTEN is not supported — it binds to a session, and a transaction-mode pooler returns that session to its pool the moment the statement ends.

Only ONE half is broken, and the asymmetry is the fix

half	path	pooler-safe
sender — `dispatcher.ts:141`	`tx.$executeRawUnsafe('NOTIFY motir_job_queue')` via Prisma → pooled	✅ yes
listener — `notify.ts:79`	`new Client({ connectionString: DATABASE_URL })` → pooled	❌ no

NOTIFY executes on a real backend and Postgres broadcasts server-side to every session in that database holding a matching LISTEN. Neon's pooled and direct endpoints front the same compute, so a listener on the direct endpoint receives a NOTIFY sent through the pooled one. The sender stays pooled; only the listener moves.

The fix

const connectionString =
  opts?.connectionString ??
  process.env['DATABASE_URL_UNPOOLED'] ??
  process.env['DATABASE_URL'];

It belongs in notify.ts rather than in scripts/worker.ts. The module already argues "IT NEEDS ITS OWN CONNECTION, and not for style — LISTEN binds to a SESSION and the session must stay open, which is the opposite of what a pool does." This is that same sentence one layer out: Prisma's pool and Neon's pooler break it for identical reasons. In the module, the next caller inherits the reasoning; in the entrypoint, the next caller re-derives it.

No new secret and no fly.toml change. DATABASE_URL_UNPOOLED is already a Fly secret on motir-core (read from fly secrets list, 2026-08-25), Fly secrets are app-wide across process groups, and the Dockerfile already sets a build placeholder for it — tests/fly-runtime-config.test.ts:183 asserts exactly that. The fallback to DATABASE_URL keeps local dev and CI working, where there is no pooler and the two name the same database.

⚠️ Hypothesis, NOT verified — how the failure presents

Two behaviours are possible and they were not distinguished at authoring time, because neither is reproducible locally:

the pooler rejects LISTEN → connect()'s catch fires → [job-listener] could not start listening; polling only, and connected reads false
the pooler accepts and drops it → no error, connected reads true, notifications simply never arrive

Verify from the deployed worker's logs before closing — fly logs -a motir-core after the worker group exists (MOTIR-3425). If it is the second, the defect is silent, which raises the value of the regression test below rather than changing the fix.

Acceptance criteria

lib/jobs/engine/notify.ts contains DATABASE_URL_UNPOOLED in the connectionString fallback chain, ahead of DATABASE_URL.
A test in tests/jobs/engine-units.test.ts fails on origin/main and asserts the preference behaviourally rather than by spying: point DATABASE_URL_UNPOOLED at a refusing address, leave DATABASE_URL working, and assert listener.connected === false. A listener that comes up chose the pooled url, which is the bug.
The comment in notify.ts states that the SENDER stays pooled deliberately, so a later reader does not "fix" the dispatcher too.
The comment records that in production DATABASE_URL_UNPOOLED is the owner role (rolbypassrls = true, per scripts/detectStrayDesignResults.mjs:209), and that this client is safe only because it issues exactly one statement — LISTEN — and reads notifications. It must never be reused for queries.

Why the E2E lane cannot catch this

playwright.config.ts runs one local Postgres with no pooler, so both env vars hold the same string and the defect does not exist there. That is why it survived MOTIR-3421's review, and it is the reason the regression test asserts the url choice rather than the delivery — delivery is only falsifiable against a real pooler.

Context refs

lib/jobs/engine/notify.ts — the listener, and the fix
lib/jobs/engine/dispatcher.ts:141 — the sender, deliberately unchanged
lib/jobs/engine/worker.ts:101 — IDLE_MAX_MS = 5_000, the latency this costs
README.md:155 — which url is pooled
MOTIR-3425 — the card whose deploy first makes this observable in production