driver-sql: store the file family (`file` / `image` / `avatar` / `video` / `audio`) as the bare `sys_file` id in a string column — drop `FILE_REFERENCE_TYPES` from `JSON_COLUMN_TYPES`, per-deployment switch on the `adr-0104-file-references` flag (ruling on #15041, step 2)

[claude]

Blocked-by: #15041
Related: #15771 · #15769 · objectui#7699 · ADR-0104

Filed by the domain:spec execution seat (session_01M59rPZZFzqhfMUPFqqZTkf, 2026-09-05T17:37Z) as execution step 2 of the maintainer ruling on #15041 (5551135629, director seat, decision batch #49 item 1, maintainer verbatim 「15041 应该改为实际 id 保存。选A，其他同意」). The ruling's step 2, quoted verbatim:

Driver card (domain:engine, filed by the spec seat with the migration sketch from report 5550175673 / H1 table 5550175730, pm:blocked on the addendum): drop FILE_REFERENCE_TYPES from JSON_COLUMN_TYPES; isJsonField / formatInput / formatOutput stop treating the family as JSON, keyed on the deployment flag until every deployment is past it; the per-dialect unquote migration as a further step of os migrate files-to-references --apply (SQLite json_extract unquote; PG ALTER … USING (col #>> '{}'); MySQL JSON_UNQUOTE + MODIFY) — abort unless backfill + verify report zero blocking; the varcharColumnChars mirror; schema-drift (fold #15771 in or leave it adjacent, the driver card decides); pins per dialect for both encodings across the window. Clause-②: yes (published storage behaviour of @objectstack/driver-sql changes). Changeset per the repo's launch-window rule: BREAKING described under a banner at minor with an ADR-0087 disposition — the driver card's dev derives the exact level from the diff.

What is ruled

The physical column for the file family (file / image / avatar / video / audio, FILE_REFERENCE_TYPES in packages/spec/src/data/field-value.zod.ts) holds the actual sys_file id — a bare id string in a string column — not a JSON-quoted id in a JSON column. The driver is the side that moves; the generator's VARCHAR(2048) (packages/cli/src/commands/generate.ts) already states the ruled end-state and does not change. Options B (generator copies the driver's JSON column) and C (status quo) were rejected by the maintainer.

Measured state to start from (readings on origin/main 8e500f23e, 2026-09-05T06:58Z — re-measure on today's main before editing)

• The family enters JSON_COLUMN_TYPES only by the spread at packages/drivers/driver-sql/src/sql-driver.ts:237 (docblock at :228); isJsonField / formatInput / formatOutput are the three readers.
• SQLite (measured): a driver-written id is stored JSON-quoted ("file_01HXYZ") in a text column and reads back as the bare id; a raw bare id in the same column is JSON.parsed and fails. PG / MySQL read and write behaviour was reasoned from sql-driver.ts, not measured on a live cell — the H1 table (5550175730, row (c)) records the confidence gap; the dev measures before relying on it.
• The generator (generateMigrationSql → VARCHAR(2048); generateMigrationTs → table.string) and syncSchema on SQLite (text) already produce string columns; the driver's non-SQLite jsonColumn path is the only producer of a JSON column for the family (H1 row (d)).
• Every ADR-0104 D3 wave has landed in spec 17.0.0; the stored-value contract is id-only in spec and in the validator (field-value.zod.ts:515-522); an inline object is still admitted on deployments that have not run os migrate files-to-references --apply (H1 rows (b) / H3) — which is why the switch is per deployment.

Shape of the change (the addendum on #15041 is the contract; this card executes it)

1. sql-driver.ts: remove the FILE_REFERENCE_TYPES spread from JSON_COLUMN_TYPES; the three readers treat the family as a plain string column when the deployment carries the adr-0104-file-references flag, and as today's JSON column otherwise (the dual-encoding window the addendum declares, with its end condition).
2. os migrate files-to-references --apply gains a further step: the per-dialect unquote migration (SQLite json_extract(col, '$'); PG ALTER TABLE … ALTER COLUMN … TYPE varchar USING (col #>> '{}'); MySQL JSON_UNQUOTE + MODIFY), run only after backfill + verify report zero blocking rows — abort otherwise.
3. varcharColumnChars mirror for the family; schema-drift: fold 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 (the json-vs-text type_mismatch finding keyed to field.multiple only) in, or leave it adjacent — this card's dev decides and says which in the PR body.
4. Pins per dialect for both encodings across the window; a reverse verification that a bare id written under the flag reads back unchanged and a legacy JSON-quoted id still reads on an un-flagged deployment.
5. Changeset: @objectstack/driver-sql, launch-window **BREAKING** banner at minor, ADR-0087 disposition derived from the diff.

Sequencing and readers

• Blocked on [finding] FILE_REFERENCE_TYPES disagree about their column: driver-sql puts file/image/avatar/video/audio in JSON_COLUMN_TYPES, packages/cli generate.ts gives them VARCHAR(2048) — and neither side is obviously the one that should move #15041: the ADR-0104 addendum (governed docs/adr/**, human merge) declares the stored column, the per-deployment flag keying, the dual-encoding window and its end condition. This card is dispatchable when [finding] FILE_REFERENCE_TYPES disagree about their column: driver-sql puts file/image/avatar/video/audio in JSON_COLUMN_TYPES, packages/cli generate.ts gives them VARCHAR(2048) — and neither side is obviously the one that should move #15041 closes with the addendum merged — the unlock scan returns it to pm:queue; the domain:engine seat re-verifies the file face on the merged ref before dispatching.
• Reader: the domain:engine execution seat (dispatch), and the domain:spec seat when the addendum lands (it retires the generate-field-type-vocabulary.pin.test.ts block labelled 「recorded divergence, NOT coverage」 at :574-593 to coverage only when this card's PR lands, per the ruling's step 3).
• Adjacent, independent: [finding] field-value.zod.ts docblock over FILE_REFERENCE_TYPES still says the stored schema "deliberately admits both until D3 lands" — the classifier has admitted only the sys_file id since D3 wave 2 PR-5a (17.0.0) #15769 (stale class-doc prose in field-value.zod.ts, spec lane) and objectui#7699 (legacy blob submitted when no fileId) proceed on their own.

Labels applied by the filing seat per the ruling's own routing (domain:engine, pm:blocked, priority:p2, bug); triage corrects if the landing point differs.

---

Generated by Claude Code

[claude]

Carried from the addendum lap (PR #16014, ADR-0104 addendum 2026-09-05; domain:spec seat, 18:48Z) — a population the driver card must handle explicitly. Deployments that already hold the adr-0104-file-references sys_migration row from BEFORE the column step exists — every creation-attested store since 17.0 (attestFreshDatastore, including every dogfood boot) and any earlier os migrate files-to-references --apply run — hold the flag AND JSON-quoted ids in a JSON column, so the flag alone cannot key the driver's write arm on them. The addendum's hard rule: they stay on the JSON arm until step 3 (the per-dialect unquote + retype) runs on them, whatever the row says; the distinguishing mechanism (re-record the same row after the step vs observe the column's actual type) is THIS card's decision, must fail toward JSON, must be pinned in this card's PR, and must not be a second gate that can disagree with the row (the ADR's 2026-07-27 principle) — the details diagnostics column is not a contract key. Re-running --apply is idempotent and re-records the row. The addendum also fixes the window's end on the release axis (the JSON arm leaves in the first protocol major after this card's PR lands, with a loud boot refusal naming the command for a deployment that has not run its own step) — read the addendum before dispatch; it is under human review on #16014.

---

Generated by Claude Code