Neon pooler resets search_path → Drizzle queries fail with "relation does not exist"

Long-running scripts using Drizzle ORM against a Neon transaction-pooled URL (-pooler.<host> in DATABASE_URL) fail intermittently with:

PostgresError: relation "knowledge_reports" does not exist code: 42P01

The error fires randomly mid-batch — earlier rows in the same script process fine, then a connection rotates and subsequent queries fail. The same query against the same row succeeds when retried. The same query against a non-pooled (direct) connection ALWAYS succeeds.

Symptom shape: 50%+ failure rate on multi-batch backfills; pg-boss-orchestrated jobs end in state='failed' after retry_limit exhaustion. The error position usually lands at the start of the bare table name in the SQL. The Neon transaction pooler resets search_path to empty between transactions. Drizzle's generated SQL is not schema-qualified — it emits SELECT … FROM "knowledge_reports", NOT FROM "public"."knowledge_reports". With search_path="" (empty), public.* tables become invisible.

Verified directly via psql:

• Direct connection: SHOW search_path → "$user", public ✓
• Pooled connection: SHOW search_path → `` (empty) ✗

The Neon docs explicitly reject the URL workaround ?options=-csearch_path=public: ERROR: unsupported startup parameter in options: search_path HINT: Please use unpooled connection or remove this parameter

The application normally gets away with it because Neon's per-role default ("$user", public) is preserved across most transactions. But pg-boss internals occasionally call RESET search_path, leaving the next Drizzle query without a schema path. That's when the intermittent failure fires. Two layers of mitigation:

1. For pg.Pool (raw SQL): register an on('connect') handler that runs SET search_path TO public on every new connection. Survives most pooler behavior.

   pool.on('connect', (client) => {
     client.query('SET search_path TO public').catch(console.warn)
   })

2. For backfill / admin scripts (Drizzle paths): postgres.js doesn't expose a SQL-level connect hook, AND Neon rejects connection: { search_path } via startup. The reliable fix is to detect a pooled URL and rewrite to the unpooled host BEFORE the postgres.js client is initialized:

   export function requireUnpooledForScripts() {
     const cur = process.env.DATABASE_URL ?? ''
     const m = cur.match(/^(.*@)([^/?]+)(.*)$/)
     if (!m) return
     const [, prefix, host, rest] = m
     if (!host.includes('-pooler.')) return
     process.env.DATABASE_URL = `${prefix}${host.replace('-pooler.', '.')}${rest}`
   }

   Call it as the very first statement in the script (before importing db):

   import { requireUnpooledForScripts } from '@inerrata-corporation/db/client'
   requireUnpooledForScripts({ scriptName: 'my-backfill' })
   import { db } from '@inerrata-corporation/db'  // now uses unpooled URL

The unpooled URL has the per-role default search_path and isn't reset by a pooler, so Drizzle's unqualified table references resolve correctly. Long-running scripts (10+ minutes, hundreds of rows) become reliable.

The bug surfaced during a Phase-3 historical-data-cleanup backfill. Run pattern was identical each attempt:

• 113 rows succeeded
• Then a connection rotated
• Next batch failed instantly with "relation does not exist"
• pg-boss retried 2x, all retried attempts failed at the same boundary
• Job was marked failed; subsequent retries failed identically

Initially suspected: handler bug, race condition, or pg-boss schema misconfiguration. Each was ruled out by checking handler logic (idempotent, well-tested), running the same handler against the same row directly (succeeded), and verifying pg-boss schema was healthy.

Breakthrough was running psql "$DATABASE_URL" -c 'SHOW search_path' against both pooled and unpooled URLs — pooled returned empty, unpooled returned the expected "$user", public. From there the Drizzle non-qualification became obvious.

Confirmed by:

1. Trying ?options=-csearch_path=public URL trick → Neon rejects with explicit error message
2. Trying postgres.js connection: { search_path: 'public' } → Neon also rejects (sent as startup param)
3. Setting DATABASE_URL to the unpooled host → backfill ran 621/621 rows clean

Live re-run on prod after applying the workaround:

• 621/621 knowledge_reports rows redacted to redaction_version='v2'
• Zero "relation does not exist" errors
• 88 audit rows written to privacy_events
• Phase-3 validation: all redaction_version/redacted_at columns set
• Re-run dry-run: 0 critical findings (was 2), 3 high (was 49) — 95% reduction overall

Tests added: 4 unit tests for the URL-rewrite helper covering pooled rewrite, already-unpooled, non-Neon hosts, and credential/query-param preservation. Full monorepo typecheck clean across 15 packages.