Stop jamming AI-agent state into a metadata JSON column
Every soft field in a JSONB blob looks like flexibility. By month six it's a defensive ?? walk on the frontend and a JSON_EXTRACT join the planner can't index. Three forces pull fields out of JSON — and the migration shape that needs no freeze.
AppX team ·
Every AI-agent loop accumulates a turns table — one row per architect-or-executor cycle: user input, model output, tools fired, files changed, duration, cost. The schema designer faces a recurring question: when a new field shows up — durationMs, creditsDeducted, a plan summary, an intent classification — do you add a column, or stuff it into metadata JSON?
JSON wins the first ten times. No migration, no coordination, ship the writer and move on. By month six, that column has rotted into a pile. The shape of the rot is always the same. This post argues for promoting fields out of the JSON blob aggressively, with a migration shape that needs no freeze.
The pattern that decays
The seductive starting point:
CREATE TABLE agent_turns (
id CHAR(36) PRIMARY KEY,
session_id CHAR(36) NOT NULL,
user_message TEXT NOT NULL,
status VARCHAR(32) NOT NULL,
metadata JSON NOT NULL,
created_at DATETIME(3) NOT NULL,
INDEX (session_id, created_at)
);
metadata starts empty. Six months later, a representative production row looks like:
{
"modelId": "sonnet-4-6",
"durationMs": 8420,
"creditsDeducted": 12,
"intent": "modify",
"planSummary": "Refactor the dashboard layout to use a grid",
"toolCallCount": 7,
"filesTouched": ["app/dashboard/page.tsx", "app/dashboard/grid.tsx"],
"errorMessage": null,
"errorCode": null,
"feedback": null,
"architectPlan": { "steps": [/* variable shape */] },
"editorIterations": [ /* array of variable-shape objects */ ],
"toolArgs": { /* provider-defined */ }
}
Three of those keys belong in JSON. The other ten should have been columns from day one. Every reader of this table now does this:
const duration =
meta?.durationMs ??
meta?.processingTimeMs ??
meta?.timing?.totalMs ??
0;
const errorMessage =
meta?.error?.message ??
meta?.errorMessage ??
meta?.failure?.reason ??
null;
Each fallback is a fossil of a writer rename nobody dared remove, because some old rows still carry the old key. The reader has become the schema's documentation, scattered across a dozen files.
Three forces that pull fields out of JSON
Compile-time safety
Typed columns referenced through a query builder (Drizzle, Prisma, Kysely, jOOQ — pick your stack) are checked at build time. Rename the column, every reference fails. Rename a JSON key, nothing fails — the reader walks the old path, silently returns undefined, and the ?? chain papers over it.
We shipped a JSON-key rename and didn't notice for two months because the fallback chain quietly served stale values. The build was green. A typed column would have failed tsc in the same PR. Typed columns make your schema part of the type system; JSON keys make it an undocumented runtime contract.
Indexability
The day someone asks "show me all failed turns from the last 24 hours grouped by model" — and someone always asks — JSON forces a full scan:
-- Un-indexable in any practical sense:
SELECT COUNT(*)
FROM agent_turns
WHERE JSON_EXTRACT(metadata, '$.modelId') = 'sonnet-4-6'
AND JSON_EXTRACT(metadata, '$.status') = 'failed'
AND created_at > NOW() - INTERVAL 1 DAY;
Postgres jsonb GIN indexes and MySQL generated-column indexes work, technically — slower than a B-tree, fussy about query shape, and they index a specific path, which already concedes the field has a stable shape and should have been a column.
-- Indexed equality, sub-millisecond:
SELECT COUNT(*)
FROM agent_turns
WHERE model_id = 'sonnet-4-6'
AND status = 'failed'
AND created_at > NOW() - INTERVAL 1 DAY;
Rule of thumb: the first time a field appears in a WHERE, JOIN, or GROUP BY clause, it should be a typed column. If you're reaching for JSON_EXTRACT in a query, you've already lost.
Reader/writer drift
JSON has no enforced contract. Writers change shape, add keys, rename, nest, flatten. Readers get no compile-time signal — they stay in sync only through documentation, which decays fast when the agent loop evolves weekly.
Five readers of the same blob will negotiate five slightly different schemas. New keys get adopted at different speeds. Old keys never get removed because "some rows still have them." The blob accretes. You cannot grep for a JSON key with the same confidence as a column rename.
The break-even math is fixed: write the migration, run it, deploy. JSON drift compounds with every reader, every deploy, every new dev re-learning the implicit shape. For any field with more than five readers or any indexed query, the migration pays back inside two deploys.
What still belongs in JSON (the actual JSON cases)
JSON is not always wrong. The rule is shape, not frequency.
- Architect plans where step shape varies by step type.
- Tool-call argument blobs defined by the model provider — you don't control the shape, you store what arrives.
- Editor iteration histories: arrays of variable-shape objects, fields differ per tool.
- Third-party API responses you cache but don't query.
- User-provided form payloads with optional, expanding fields you don't control.
The discriminator: if every row has the same logical shape and you're avoiding a migration, it's a typed-column case. If the shape varies per row, JSON is correct.
The promoted schema
Same table after promotion:
CREATE TABLE agent_turns (
id CHAR(36) PRIMARY KEY,
session_id CHAR(36) NOT NULL,
user_message TEXT NOT NULL,
status VARCHAR(32) NOT NULL,
model_id VARCHAR(64),
intent VARCHAR(32),
duration_ms INT,
credits_deducted INT,
tool_call_count SMALLINT,
plan_summary TEXT,
error_code VARCHAR(64),
error_message TEXT,
feedback_rating TINYINT,
files_touched JSON, -- array, but uniform-shape; debatable
architect_plan JSON, -- variable-shape: stays JSON
editor_iterations JSON, -- variable-shape: stays JSON
tool_args JSON, -- provider-defined: stays JSON
created_at DATETIME(3) NOT NULL,
INDEX (session_id, created_at),
INDEX (model_id, status, created_at),
INDEX (status, created_at)
);
Ten typed columns, three JSON columns, three new indexes that didn't exist before because they couldn't.
Migration shape: how to promote without a freeze
This is not a stop-the-world migration:
- Add the typed column NULLable. No backfill yet. Deploy.
- Shadow-write window. Writers populate both the JSON key and the typed column — one line in the writer. Deploy.
- Soak. New rows accumulate with both populated. Two days to two weeks depending on traffic.
- Reader cutover. Readers prefer typed column, fall back to JSON for old rows. Deploy.
- Backfill decision. Either copy the JSON key into the typed column for old rows, or accept NULL. Backfill if you need historical aggregates; don't if old rows are operationally dead.
- Drop the JSON key. Writer stops populating it. Readers drop the fallback. The typed column is now the only contract.
Step 5 is the only step with judgment. The rest is mechanical, and the whole sequence runs concurrent with feature work — no freeze, no big-bang cutover. At every step, both old and new rows are readable, and any rollback leaves the system functioning. The shadow-write window is what buys this. Skip it and you've recreated the drift problem you're escaping.
Takeaways
- Treat
metadataJSON as a holding pen, not a destination. Every field there is paying interest. - The promotion trigger is shape, not frequency. Uniform shape across rows → column. Variable shape → JSON. Frequency just tells you how urgent.
- Compile-time safety, indexability, and reader/writer drift are three independent forces. Any one justifies a column.
- Shadow-write migration: add nullable, shadow-write, soak, cut over readers, backfill (or don't), drop the JSON key. No coordinated deploys required.
- Your schema is your data contract. JSON is a contract you didn't write down — every reader writes a different version of it.
- The debt math: read count × deploys-affected-by-drift versus one migration. For frequently-read fields, it pays back inside two deploys. Stop deferring.