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.