driver-sql: single→multi-value field change never migrates the existing column type (varchar/text kept), arrays silently stored as stringified literals — data corruption on Postgres

[baozhoutao]

Summary

When a field is changed from single-value to multi-value (e.g. a lookup gaining multiple: true), a fresh database creates the column as json — but on an existing PostgreSQL database the old varchar/text column is kept forever, silently. Writes then store the array as a stringified literal ('["id1","id2"]') into the old column, reads return that raw string, and downstream code (hooks copying the value into child records) treats the whole string as a single id — i.e. silent data corruption, not just a display glitch.

Environment

• @objectstack/*@17.2.0 (driver-sql), PostgreSQL 16
• App project with a long-lived database that has been through several metadata upgrades

Reproduction

1. Define an object with a single-value lookup field on Postgres; boot; insert a record (column is created as varchar).
2. Change the field to multi-value (multiple: true); reboot.
3. Observe: no migration, no warning. information_schema.columns still shows character varying; a fresh DB of the same metadata shows json.
4. Create a record selecting 2 users → stored value is the literal string ["userA","userB"]; list/detail UI renders the raw string; any hook reading the field receives a string and (in our case) copied it verbatim into a child record's single-lookup column, producing a reference to a non-existent sys_user#'["…"]'.

Root cause (as far as we can see)

The schema-drift repair in driver-sql 17.2 supports only: relax/tighten_not_null, widen/narrow_varchar, drop_column, drop_column_default, plus index actions. There is no "change column base type" action, and the mismatch is not reported either. Additionally the old single-value btree index on the column is left behind (json cannot carry btree; after our manual ALTER, the drift log correctly flags that index as orphaned).

Write path formatInput JSON.stringifies for json fields on non-SQLite drivers, but the read path only JSON.parses on SQLite — on Postgres it relies on the pg driver's column-type-based decoding, which a stale varchar column defeats. SQLite dev environments therefore never expose the bug.

Expected

Either migrate the column type when a field becomes multi-value (ALTER TABLE … TYPE json USING …, dropping/rebuilding incompatible indexes), or at minimum fail loudly / log a drift error so operators know the column must be migrated by hand before data gets corrupted.

Workaround we applied in production

Manual, inside one transaction: drop the legacy btree index, then
ALTER TABLE t ALTER COLUMN col TYPE json USING (CASE WHEN col = '' THEN NULL WHEN col LIKE '[%' THEN col::json ELSE to_json(col) END),
then repair the child rows that had already been corrupted. Works, but every project with a long-lived DB will hit this on any single→multi field change.

[os-zhuang]

Triage (Routine seat, hourly round): → pm:queue + domain:engine, type Bug. P0-suspect — flagged to the engine seat for front-of-queue treatment (silent data corruption: arrays stored as stringified literals into an unmigrated varchar/text column on Postgres; the release-board four-type test's "shipped-data shape you cannot repair after release" class). Not stamping priority:p0 from triage — the engine seat should confirm reachability (single→multi field change through os migrate on a live table) and escalate if it reproduces.

Family serial note: part of this round's driver-sql group (#11535 #11541 #11550 #11565 + blocked #11431) — fold-or-serial answered under the five gates at dispatch.

---

Generated by Claude Code

[huangyiirene]

Claim: PM loop round 2
Session: session_01VK8rFDtg8eREaxBGX99Csn
Branch: claude/issue-11535-drift-detect-base-type-mismatch
Worktree: objectstack-issue-11535
Domain: domain:engine
File surface: packages/drivers/driver-sql/src/schema-drift.ts + its test files (stop on breach; explain in the report)
Container & model: M, mode:subagent, model: opus (tier: dispatch-gates.mjs --tier returns no path-derived mandate. Judgement tier because the severity/category choice has deployment consequences — see the premise below.)
Clause-②: no — this adds detection and reporting of a physical/declared mismatch. It does not move the metadata contract's accept set in either direction and widens no public surface; it is declared ≠ enforced catching up on the enforcement side.
Serial constraints cleared: landing point is schema-drift.ts (1563 lines), measured — no open PR touches that file (all open PRs enumerated, diffs read). File-disjoint from the sibling dispatched in the same round (#11379, sql-driver.ts introspectFor" + "eignKeys). The two sql-driver.ts holds (#6009, #8740) name symbols in that other file and are not reachable from this surface.

⛔ This card is SPLIT, and the split is the point

This is a user-filed production report — silent data corruption on a long-lived Postgres database, with a manual workaround already applied in production. It deserves a fix now, and the card itself frames two routes:

Either migrate the column type when a field becomes multi-value (ALTER TABLE … TYPE json USING …, dropping/rebuilding incompatible indexes), or at minimum fail loudly / log a drift error so operators know the column must be migrated by hand before data gets corrupted.

Only the second route is dispatched. The first is not mine to authorise:

• An automatic ALTER TABLE … TYPE … USING … over existing rows, with index drop/rebuild, is the shape of a migration over existing data and a destructive, hard-to-roll-back action. Both sit squarely on the maintainer's floor, and neither an execution seat nor triage may adjudicate them.
• The second route is a different thing entirely: today the drift detector's action vocabulary is relax/tighten_not_null, widen/narrow_varchar, drop_column, drop_column_default — verified at schema-drift.ts:129-133 — and a base-type mismatch is not reported at all. Making it visible restores an invariant rather than deciding anything, which is explicitly not an escalation.

The auto-migration half is filed as its own decision card for the maintainer, with the four-facet analysis; this claim links it once filed.

⛔ Therefore: do NOT implement a column-type migration in this PR. Not a partial one, not one behind a flag, not a "prepare the op type but don't run it" version that quietly lands the vocabulary. If you conclude detection is impossible without it, that is a fork — report it and stop; ⛔ do not push through and ⛔ do not silently fall back to the other route.

Ruling, with its premise — verify the premise before you build on it

The route above is decided. It hangs on one named, falsifiable premise:

PREMISE: a new error-severity drift finding can be emitted for a base-type mismatch without changing whether an existing deployment boots.

⚠️ This matters more than it looks. Every affected database is, by the card's own account, already running with a stale varchar column. If an error drift finding refuses the boot, then "reporting loudly" converts a currently-serving (if corrupt) deployment into a refusing one — that is a breaking change for exactly the users this card is meant to help, and it would need the maintainer, not me.

So: measure what an error-severity finding actually does in this codebase before choosing a severity. If it blocks boot, ⛔ do not ship that, and report the fork with what you measured. schema-drift.ts:392 already describes narrow_varchar as severity error, category destructive — read how that is consumed, do not infer it from the words.

What good looks like

• The mismatch is detected and named precisely — the table, the column, the declared type, the physical type, and the remedy an operator must run by hand.
• The orphaned single-value btree index the card describes is part of the picture; the reporter notes the drift log flags it correctly after a manual ALTER, which is a clue about what the detector can already see.
• ⚠️ A pin that only asserts "a finding was produced" is weak. What distinguishes this defect is that the finding is produced for the right column with the right diagnosis on a shape that currently produces silence.
• SQLite dev environments never expose this (the card explains why: the read path only JSON.parses on SQLite). A pin that passes only on SQLite would be exactly the false green this repo keeps paying for. Live Postgres is available in the container — ⛔ check first and start it if down, never assume it is running.

Reporting keyword

Use Part of #11535, ⛔ not Fixes. The card's remaining half is a live maintainer decision, and a merge that silently closed this user's corruption report would be wrong. CI enforces the pairing (Part-of PR must not also close its card).

---

Generated by Claude Code