Two silent failures when you move a JSON column into child rows

A parent entity Record carried a JSON column — lines: jsonb — holding an array of sub-items. It worked, but any code that needed to aggregate across those sub-items had to parse JSON, and you could never query a single line. Time to normalize: one record_lines row per line, with a small service owning the writes.

Two hard constraints made it interesting:

• The write API can’t change. Clients — including an offline outbox that replays queued requests much later — POST { lines: [...] }. Change that DTO and every queued request 400s on replay.
• The read API can’t change. Consumers read record.lines. The column is going away, but the response field must stay.

So: accept the same { lines } on write and split it into rows inside the service; re-assemble lines from ordered rows at every read boundary. Routine. The interesting part is the two ways it breaks without telling you.

Failure 1: the read boundary you forgot

You have to re-assemble lines at every place a response is built. Miss one and — once you drop the column — that endpoint returns null with no error at all. Grep helps, but grep misses the boundary in a file you didn’t think to search, and it can’t tell you which of six hits are reads versus the one write.

The type system can. Keep lines on the entity — but not as a mapped column:

// NOT a @Column — the ORM never selects or persists this.
// It exists only so response-assembly sites type-check, and
// so any read site we forgot to re-point fails to compile.
lines: RecordLine[] | null;

Now:

• The ORM never reads or writes it, so it can never hold stale data from the dropped column.
• Every response.lines = assemble(rows) still type-checks.
• Any code that still reads record.lines expecting the ORM to have populated it is now reading a field nothing sets — and the compiler (or a failing test) points straight at it.

The instinct here is binary: either keep the mapped column (and risk stale data surviving the migration) or delete the property entirely (and fall back to grep). The transient typed property is the middle path, and it’s the one that actually models reality: this field lives in the response contract but no longer in the table. Encode that, and the compiler becomes your migration checklist.

Failure 2: the aggregation that ranks differently

This is the one that actually scares me.

The response shape is the visible, easily-tested part. The dangerous consumer is the one that doesn’t read the shape at all — it reads an aggregation built from the same rows and then acts on the result.

In our case a ranking step computed standings across all the sub-items, and a downstream consumer acted on standings[0] — issuing a payout, firing a notification, flipping a state. When the aggregation switched from “sum the JSON array” to “sum the rows,” a miscount wouldn’t throw. It would quietly rank a different entity first, and the consumer would faithfully act on the wrong one. No error. No 500. A wrong action in production, looking exactly like a right one.

And a shape test — “does the response still contain lines?” — passes the entire time. It asserts the wrong thing.

The test that catches it asserts the outcome: given rows that make entity B the leader, the ranking still returns B, and the downstream consumer acts on B. Assert the decision, not the payload. When a migration reroutes the data that feeds a decision, the regression test belongs at the decision — not at the serialized edge, which is exactly where it’s most tempting to put it.

The takeaway

When you move data behind a stable contract:

1. Model “in the contract, not in the table” as a transient typed property, and let the compiler enumerate the read sites you missed.
2. Find every consumer that acts on an aggregate of the moved data, and regression-test the action’s outcome — because that’s the failure that ships silently while every shape test stays green.