← read more

JSON Columns: The Escape Hatch That Doesn't Have to Become a Time Bomb

Every project ends up storing data whose shape you don't control - provider payloads, settings, audit diffs. Where a JSON column earns its place in a Laravel app, the rules that keep it from rotting, and the point where you extract it back into real columns.

Every app has one table where the shape changes

A webhook body from a payment provider. A settings object that differs per tenant. A provider API response you keep for debugging. An import file you're staging row by row. None of these have a stable schema, and none of them deserve one.

The two reflex responses are both bad. Serializing into a TEXT column makes the data unqueryable and untypeable. A 40-column EAV table with one row per attribute will grind every report to a halt and make the codebase unreadable. A jsonb column is the middle path: type-checked, queryable, indexable. Whether it stays an escape hatch or turns into a trap depends on discipline, not on the column type.

Where jsonb earns its keep

Four cases cover almost every legitimate use we've hit:

  • External payloads you don't control. Webhook bodies, provider API responses, raw CSV rows during import. The provider will change the shape on you; storing the whole thing lets you re-parse later when you discover a field you now need.
  • Settings and preferences that vary per record. Notification defaults, integration tokens, layout config. Forcing these into columns means migrations every time a product feature adds one toggle.
  • Audit diffs. The before/after snapshot in an audit log is naturally JSON, read rarely and filtered even more rarely.
  • "I don't know what I'll need yet" data. The early days of a feature, before you know which fields matter.

Rules that keep it from rotting

The failure mode is always the same: the flexible column becomes the junk drawer, then the junk drawer becomes the schema. Four rules hold the line:

  • Primary business fields get real columns. If a value needs a foreign key, a unique constraint, a NOT NULL rule, or shows up in a WHERE clause on a hot path, it's a column. JSON can't enforce any of that, so app code has to, and it will forget somewhere.
  • Validate on write. A cast class or DTO that checks the payload shape when the row is saved. Garbage-in-garbage-out is worse with JSON because the garbage is invisible until it's queried.
  • Extract when a query pattern stabilizes. The first time you write the same meta->>'plan' query in three places, that field has become schema. Add the column, backfill with UPDATE ... SET plan = meta->>'plan', drop the key in the next deploy.
  • Document the shape. An empty jsonb column with no example anywhere means the next developer invents a key name that collides with yours.

Querying and indexing

In Laravel, jsonb comes free with json column casts:

Schema::table('webhook_events', function (Blueprint $t) {
    $t->jsonb('payload')->nullable();
});

Querying a path is a raw expression:

$events = DB::table('webhook_events')
    ->whereRaw("payload->>'type' = ?", ['checkout.session.completed'])
    ->get();

Without an index this scans the table, which is fine at hundreds of rows and not fine at millions. The fix is an expression index over the specific path:

Schema::table('webhook_events', function (Blueprint $t) {
    $t->rawIndex("(payload->>'type')", 'webhook_events_type_idx');
});

Index the two or three paths you actually filter on, not the whole document. A blanket GIN index on the entire payload bloats fast and slows every insert for queries you may never run.

The tell-tale sign it's time to move out

The column did its job when you can look at the data and list the fields that matter. That list is your migration. Pull those fields into proper columns with constraints, leave the rest as a jsonb blob for the auditor, and update the casts. This is routine work: one migration, one backfill loop in a queued job, one deploy note.

JSON columns are a staging area for schema, not a permanent schema. Used that way they save weeks of churn when a provider payload shifts or a feature changes shape mid-build. Left forever they produce queries with three levels of path access and integrity the database can't defend.