# a flexible schema moves the work, it does not remove it

Somebody on your team is going to propose a JSON column this quarter, and the pitch is always the same and always half right. The shapes vary too much for a table, so store the shape as data and let code decide what it means.

I have taken that deal and I would take it again. What I want written down is what the deal costs, because the bill lands in three places and none of them is the migration.

## The column stops checking your work

A column declared INT NOT NULL holds against every writer that will ever exist. Your code, someone else's code, a backfill script, a psql session at two in the morning. It is a promise the engine keeps whether or not anybody remembers it.

-- what a typed column promises, to everyone, forever
retry_limit  INT    NOT NULL
endpoints    JSONB  NOT NULL

-- what a config column promises
config       JSONB  NOT NULL

A JSONB column keeps one promise. It parses.

Everything past that is yours to enforce, in code, at every door into the row. That is a swap and it is worth naming as one: a declarative constraint the engine applies everywhere, traded for an imperative call that runs where you remember to put it.

So count the doors before you agree. The API, the admin edit screen, the importer, the backfill, and whatever the next integration adds at eleven on a Friday.

## Two shapes the storage cannot tell apart

The version of this that bites hardest is the split between what you store and what you hand back. A stored config holds an API token, a scoring key, an internal threshold. The one a browser is allowed to see holds a subset of that.

Storage sees no difference between them. Both are JSON.

So the distinction has to live in the type system: a server schema, a client schema derived from it by an explicit omit, and a function signature that compiles only when it returns the narrow one. It works, and its reach ends exactly where the type information ends.

// runtime check, real type out
const config = serverSchema.parse(row.config);

// compiles, checks nothing, reviews identically
const config = row.config as ServerConfig;

Prisma hands a JSON column back as Prisma.JsonValue, a union describing any JSON at all, so getting a typed value out of a row takes a deliberate act. Two ways exist and one of them is honest.

One keyword separates them and it vanishes in a diff of any size. Background jobs, admin exports, a fifteen-line script to patch a field across old rows: each one is a place where somebody reaches into the column and the compiler asks nothing.

A codebase in this shape owes itself a lint rule banning the bare cast. The alternative is trusting every contributor who never read the design doc to reach for the parse call on their own.

## Filtering goes from a clause to a loop

This is the bill people see coming and underprice anyway. Two kinds of record keep the same idea under different keys, because they were written a year apart by people who were not talking to each other.

-- typed columns: one clause, one ordinary index
SELECT id FROM webhook WHERE retry_limit > 4;

-- config column: a path that exists for some rows and not others
SELECT id FROM integration
WHERE kind = 'WEBHOOK' AND (config->>'retryLimit')::int > 4;

The second query is fine on its own. Write it once per kind, add an expression index for every path you care about, and the per-kind work you deleted from the controller has quietly reassembled itself in the schema.

The alternative is easier to write and worse to live with. Select the candidates, parse every config, hand each one to whatever knows how to read it, keep the survivors.

Pagination is where that stings. A database cannot apply LIMIT to a predicate living in application code, so page two rescans everything page one already scanned.

## What the build catches and what it waves through

A map from a kind enum to a handler, typed as Record<Kind, Handler>, refuses to compile while a key is missing. That turns we shipped a kind and forgot to wire it up into a red build with a useful error, and it is the cheapest good idea in the whole design.

Sitting right beside it is a gap the build has no opinion about.

// the build rejects this: one kind has no handler
export const handlers: Record<Kind, Handler> = {
  [Kind.WEBHOOK]: new WebhookHandler(),
};

// the build accepts this: a client schema that omits nothing
const clientSchema = serverSchema;

A client schema is supposed to be narrower than the server one, and nothing in the type system is aware of that. Point the alias straight at the server schema and every signature still lines up, because the two inferred types are now one type.

The narrowing is the entire point of keeping a pair, and it can be undone by an edit that reads in review as a simplification. The only thing standing between you and that is a test asserting the secret field is absent, written by hand, once per kind.

## What I check before agreeing to it

Count the queries that filter on the flexible part. If the honest answer is zero, and there is reason to believe it stays zero, the column is cheap and the interface above it is worth every hour it costs.

Then price the validation at its real rate. Three schemas and a spec file per kind, paid again on every kind that arrives after the migration ships, for as long as the system lives.

What I would never hand back is the narrowed return type. One place to be right about beats one place per kind, and how the rows are stored underneath never entered into that.

The question I want answered while the typed columns are still standing is the filtering one. That is the last moment at which splitting a field back out is a migration instead of a rewrite.