driver-sql(schema-drift): ADR-0104 ruled end-state media column is reported `type_mismatch`/error with a remedy that reverses the ruling

[claude]

What is wrong

ADR-0104's addendum rules that a media field (FILE_REFERENCE_TYPES) ends up in a
string column — VARCHAR(2048) on Postgres and MySQL — holding the bare
sys_file id. detectTableDrift now reports exactly that end-state as an error,
and offers a remedy that undoes it.

The three conjuncts are all satisfied by a media field over a varchar column, read on
today's origin/main (packages/drivers/driver-sql/src/schema-drift.ts):

• JSON_COLUMN_FIELD_TYPES is seeded ...STRUCTURED_JSON_TYPES, ...FILE_REFERENCE_TYPES, ...MULTI_OPTION_TYPES, 'object', 'array' — so a file / image field is a member.
• declaresJsonColumn = JSON_COLUMN_FIELD_TYPES.has(declaredType) || field.multiple === true
  ⇒ true for a single-value file.
• jsonColumnTypeIsLoadBearing(dialect) ⇒ true on postgres and mysql.
• acceptsStringifiedJson(col.type) is /char|text/i ⇒ true for varchar(2048).

⇒ the finding is emitted with kind: 'type_mismatch', severity: 'error',
category: 'needs_confirm', and
op: { type: 'manual_column_type_change', to: 'json', from: '<the varchar>' }.

That op is the reverse of the ruling. Every deployment that has reached — or is told
to reach — ADR-0104's end-state is instructed by its own tooling, at error severity, to
convert the column back to json.

Because the field is single-value, declaresArray is false, so the message takes the
half that "carries neither the command nor the statement" — the operator gets an error
with no runnable repair, only the manual_column_type_change op pointing the wrong way.

Why this is live now, and not only after step 3

This became reachable for single-value media tonight, when #15771's widening landed
as PR #16073: before it, declaresJsonColumn was field.multiple === true alone and a
single-value file was out of scope. It is now in scope, and there are two populations
that can present a varchar media column on Postgres/MySQL:

1. After step 3 of os migrate files-to-references --apply — the ruled end-state,
   by construction.
2. ⚠️ Possibly already today: the ADR records that the generator's
   FIELD_TYPE_SQL_MAP already emits VARCHAR(2048) for all five media members, and
   its pin block is labelled "Recorded divergence, NOT coverage" precisely because that
   divergence is true on every deployment until the driver moves.

⛔ Population 2 is REASONED, NOT MEASURED, and it is the first thing this card must
settle. The conjunct analysis above is a reading of the source, not an observation of a
running datastore. Measure it before anything else: stand up a generator-created
datastore on live Postgres (the Temporal Conformance (live PG + MySQL) job's
postgres:16 is the in-repo route) and read what detectTableDrift actually returns for
a file field. Publish the finding object you get, or publish that you got none and why.
If population 2 turns out to be empty, this card is a blocker on step 3 rather than a
live defect — say which one it is, with the measurement, and let that decide the
priority.

What the fix has to reconcile

The two halves are both right about their own population and cannot both keep the same
predicate:

• A media column that is still JSON-encoded (a deployment before step 3, or without
  the flag) over a varchar column is driver-sql schema-drift: the json-vs-text type_mismatch finding is keyed to field.multiple only, so a SINGLE-value JSON-class column (file family, STRUCTURED_JSON_TYPES) on a char/text column is never reported — the column a hand-run generated migration creates today #15771's silent corruption and must stay a finding.
• A media column that is bare-id encoded (after step 3, on the flag) over a varchar
  column is the ruled end-state and must be silent.

⇒ The discriminator is the deployment's adr-0104-file-references flag, not the
field type — the same key the driver's own encoding arm is ruled to use. Whatever shape
the fix takes, detectTableDrift has to be able to ask that question, and today it has
no such input. ⛔ Do not "fix" this by dropping FILE_REFERENCE_TYPES from
JSON_COLUMN_FIELD_TYPES: that set is pinned in both directions against the driver's own
isJsonField by schema-drift.json-column-parity.test.ts, and unpinning it re-opens the
fork that module exists to close.

⚠️ Note the ordering trap this creates: isJsonField's answer is frozen into
jsonFields[object] at registration time, so keying the readers on a per-deployment
flag is an architecture change, not a three-line edit. That was measured on the #15989
round and is why the driver work is being dispatched as separate pieces.

Boundaries

• ⛔ This must be settled before step 3 ships, or every migrated deployment is told by
  its own tooling to undo the migration.
• ⛔ Do not edit docs/adr/** — the ruling is not in question here, only the detector's
  agreement with it.
• ⛔ Do not skip, disable or quarantine schema-drift.json-column-parity.test.ts or any
  other pin to make this quiet.

Provenance

Found by the #15989 execution round; recorded in the ruling on that card:
#15989 (comment)

Part-of #15989. Related: #15771 (closed, PR #16073 — the widening that made this
reachable).

---

Generated by Claude Code