One line in our Postgres config would have renamed the keys inside our JSONB

Our database client is about seventy lines, and roughly fifty of them are comments. That ratio looked embarrassing until I counted how many of the settings would be reverted by the next person to read the file without them.

Here is the one that nearly got us, sitting at the bottom of the options object with no code attached to it at all:

// NOTE: Do NOT enable transform: postgres.camel.
// postgres-js applies it to JSONB column contents too, which breaks
// SessionConfig keys (scoring_mode → scoringMode, etc.).

Why that is tempting in the first place

Our schema is snake_case, because Postgres is. Our TypeScript is camelCase, because TypeScript is. So you write session.current_question_index in a React component and it looks wrong, and you keep writing it, and eventually you notice that postgres-js ships exactly the fix:

postgres(url, { transform: postgres.camel })

One line, every column name mapped on the way out, no more snake_case leaking into your components. It is the right call in a lot of codebases.

It is the wrong call in ours, and the reason is a design decision two layers away.

A live quiz session carries a config column, and that column is JSONB. It holds the things that are settings rather than state: the scoring mode, whether the quiz advances automatically or on the host's click, whether late joiners are allowed, and an array of per-question states recording when each question was launched and closed. Keys like scoring_mode, advance_mode, question_states, launched_at, closed_at.

transform: postgres.camel does not stop at column names. It walks into JSONB values and renames their keys too. So the moment that line is added:

• config.scoring_mode becomes config.scoringMode
• config.question_states becomes config.questionStates
• every launched_at and closed_at inside that array is renamed as well

And nothing crashes. That is the part worth sitting with. config.question_states on an object that now has questionStates is undefined, and our code reads it through a guard that was written for old sessions that genuinely have no states yet:

function statesOf(config: SessionConfig): SessionConfig['question_states'] {
    return Array.isArray(config.question_states) ? config.question_states : []
}

undefined is not an array, so you get []. An empty array of question states means no question has ever been launched. The transition guards that read that array to decide what move is legal now believe the quiz has not started. TypeScript is satisfied throughout, because the type says question_states exists and the runtime shape no longer matches the type. The failure surfaces as a question that will not advance, in a pub, in front of fifty people, with a clean error log.

The lesson generalises past this library: any transform that rewrites keys needs to know the difference between a column name, which is schema, and a key inside a value, which is data. Column names are yours to rename. The contents of a JSONB document are not, because something wrote them with specific keys and something else is going to read them back expecting those keys.

The rest of the file is about Vercel suspending us

The other settings all come from one problem, which took a while to see as one problem. In production this app runs as Node serverless functions on Vercel, talking to Supabase's transaction pooler.

postgres-js is good at what it does, and what it does is hold TCP connections open between queries so the next query does not pay for a handshake. That is correct for a long-running server and actively harmful here, because Vercel can suspend a function instance at any moment and resurrect it later for a new request. The instance comes back with its JavaScript heap intact, including a postgres.Sql object that is certain it has a live socket. It does not. The socket died while the instance was frozen.

The symptom is memorable because it does not look like a database problem:

It works, and then it breaks after a couple of pages.

First request after a cold start: fine, fresh connection. Next few: fine, same instance, same live connection. Then a suspend, a resume, and an immediate failure on a connection that no longer exists. Reload and it works again, because that reload got a different instance. Nothing reproduces locally, where the Next dev server never gets suspended.

So production is configured to not hold anything:

max: isProduction ? 1 : 5,
idle_timeout: isProduction ? 0 : 30,
max_lifetime: isProduction ? 60 * 5 : 60 * 30,

max: 1 because a serverless function handles one request at a time, so a pool of five is five chances to hand out a zombie instead of one. idle_timeout: 0 means close the connection as soon as it goes idle, which is the actual fix: a connection that is already closed cannot be a stale connection, and the pooler slot is returned before the instance is reused. max_lifetime of five minutes is the belt to that braces, rotating connections that somehow never go idle.

Reconnecting per request sounds expensive and is not, against a pooler. That is the trade the transaction pooler exists to make.

Development is the opposite shape and needs the opposite settings. The dev server is one long-lived process, so a small pool is free. What it does have is hot reload, which re-evaluates modules and would build a new client on every save until the connection limit is gone:

const client: postgres.Sql = global.__db_client ?? createClient();
if (!isProduction) {
  global.__db_client = client;
}

globalThis survives module re-evaluation. The assignment is guarded rather than unconditional because in production it would be pure overhead: a cold start has an empty global anyway, and caching a client we want closed is the thing we are trying to avoid.

Two settings the pooler requires, not prefers

prepare: false,
fetch_types: false,

The first is not optional. Supavisor and PgBouncer in transaction mode give you a different backend connection per transaction, so a prepared statement created on one is not there on the next. You do not get a slow query, you get an error about an unknown prepared statement, intermittently, at whatever rate your pooler happens to be shuffling backends.

The second is smaller and worth knowing anyway. postgres-js normally sends a Describe on first connect to learn type OIDs. With idle_timeout: 0 we are reconnecting often, so "once per connection" is close to "once per request", and that round-trip is on the path of every cold start. Some poolers also mishandle the message. Turning it off costs us nothing we use.

And one that is purely about not shouting in production logs:

onnotice: isProduction ? undefined : console.log,

Where this shows up in the product

The thing all of this protects is boring and important: a quiz that is running right now has rows being written to it on every answer, and a quiz that finished last Tuesday has to still be readable. Session history is that second half, every past quiz with its final standings kept, and it is the feature that would most obviously have been a liar if the config column had been quietly renamed, because the per-question timings it reads back live in exactly that JSONB.

The settings a host actually chooses, scoring mode and whether questions advance on a timer or on a click, are the keys in that document. You can see the choices themselves on the quiz builder page, where each question gets its own point value and its own answer window. That per-question shape is the reason the config is JSONB rather than fifteen columns, and the reason a key-renaming transform was never going to be safe here.

If you take one thing from the file: write the comment for the setting that is absent. max: 1 explains itself eventually. "Do not add transform: postgres.camel" cannot be inferred from the code, because the code is the absence of a line, and the next person to find snake_case in a React component is going to reach for it.