driver-sql (PG): JSON-field values are bound without JSON.stringify — non-empty arrays and bare strings 500, empty array silently stored as {}

[os-zhuang]

Found live on the cloud control plane (cloud.objectos.ai, Postgres/Neon via driver-sql) while auditing browser console errors. Filed here per contract-first: the mechanism is the driver's, every PG-backed deployment inherits it.

Reproduction matrix — one PATCH, only value changes

sys_user_preference.value is a JSON-typed field. PATCH /api/v1/data/sys_user_preference/:id with:

body value	result
{"a":1} (object)	200
42 (number)	200
{"items":[1,2]} (array nested in object)	200
[{"type":"app",…}] (non-empty array)	500 DATABASE_ERROR
"x" (string)	500 DATABASE_ERROR
[] (empty array)	200 — but silently stored as {} (see mechanism)

POST behaves identically (a create with an array value 500s).

Mechanism (inferred from the signature — verify at the driver)

The matrix is the classic node-postgres fingerprint for passing JSON-field values to the driver without JSON.stringify:

• JS object → node-pg serialises to JSON text → valid jsonb ✓
• JS array → node-pg converts to a Postgres array literal ({…}) → invalid JSON text → 500; except [], whose literal {} happens to be valid JSON — so an empty array is accepted and silently stored as an empty object. That last row is data corruption, not an error, and it will outlive any fix that only handles the 500s.
• JS string x → passed raw → not valid JSON text ("x" would be) → 500; a numeric string/number passes because 42 is valid JSON.

Expected: the SQL driver's PG path must JSON.stringify every JSON-field binding (insert AND update), exactly as it presumably does for objects via node-pg's default object handling — relying on node-pg's per-type defaults is the bug.

User-visible impact, today, on every control-plane deployment

The Console's "recent items" preference (ui.recent) is naturally a list. Every navigation writes it → every write 500s → a red DATABASE_ERROR in the browser console on every console visit, recents never persist, and the server logs a 5xx per page view. Both prod and staging control planes are PG, so this is not an environment quirk. (Tenant environments on Turso/SQLite take a different driver path, which is presumably why the seed/data suites never caught it.)

Note for the fix

• The 500 half needs the stringify; the []→{} half needs a regression test of its own — it is the case that stays wrong silently after the crash is fixed.
• Scalar JSON values ("x", true, null) are legal JSON documents and should round-trip; a fix that only handles arrays re-fails the string row of the matrix.

[huangyiirene]

Triage: lands in packages/drivers/driver-sql (PG binding path) ⇒ domain:engine, type Bug, queued pm:queue + priority:p0.

p0 rationale (queue-jump is priority, not exemption — claim protocol and hot-file serial still apply): this is live on every PG-backed deployment today, including both production and staging control planes — every console navigation writes ui.recent (a list) ⇒ every write 500s, a red DATABASE_ERROR per page view, recents never persist, plus the sharper half: [] is silently stored as {} — data corruption that survives any fix addressing only the 500s. Filed by the maintainer from a live-prod audit. The tenant-path Turso/SQLite drivers don't share the path, which is why the suites are blind — that blindness is part of the defect surface.

Dispatch notes for the engine seat:

• The mechanism is inferred from the node-postgres fingerprint — the card itself says verify at the driver. Treat "missing JSON.stringify on JSON-field bindings" as a mechanism assumption to confirm before coding, covering insert AND update.
• Required regression pins, per the card: (a) non-empty array round-trips, (b) bare string / scalar JSON ("x", true, null) round-trips, (c) [] round-trips as [] not {} — (c) is the one that stays silently wrong otherwise. If no PG is reachable from the dev container, pin at the knex-binding level (assert the bound parameter is JSON text) and say so in the PR.
• Hot-file: sql-driver.ts carries A compare-and-set where on a by-id update is silently inert — the extra predicate keys never reach the driver, and SqlHttpOutbox.redeliver's status guard is one of them #11009/driver-sql: SQLite introspection reports only the FIRST column of a composite primary key (pk === 1 vs SQLite's 1,2,3… numbering) #10997 (queued) — p0 does not exempt same-file serial; the seat sequences.

---

Generated by Claude Code

[os-elon]

Claimed for dispatch — p0, and the same-file serial ruling for the sql-driver.ts trio

• Seat: PM dispatch (domain:engine), session session_019yDEhPBC3tcGkW9bkce1HM
• Branch: claude/issue-10995-pg-json-binding
• Tier: opus · Labels: pm:queue → pm:dispatched

Serial ruling (triage left it to this seat): sql-driver.ts carries three queued cards — #10995 (p0) first, then #10997 (SQLite composite-PK introspection) and #11009 (inert compare-and-set where), which stay pm:queue and unassigned until this lands. p0 buys queue position, not a same-file exemption; two PRs on one file collide regardless of priority.

⚠️ Cross-seat note: the epic seat (cloud#1521 Round A) holds packages/core/src/security/resolve-authz-context.ts. ⛔ Do not touch it.

⛔ The mechanism is INFERRED — confirm it before writing code

The card is explicit that the node-postgres fingerprint is a read of the symptoms, not a measurement of the driver: "Mechanism (inferred from the signature — verify at the driver)". So step one is to find the actual binding site and establish what it does, covering insert AND update. If what you find disagrees with "missing JSON.stringify on JSON-field bindings", that disagreement is the finding — report it rather than making the code match the card.

The reproduction matrix is the acceptance

One PATCH /api/v1/data/sys_user_preference/:id, only value changes:

body value	today	must become
{"a":1} object	200	200
42 number	200	200
{"items":[1,2]} array nested in object	200	200
[{"type":"app",…}] non-empty array	500 DATABASE_ERROR	200, round-trips
"x" bare string	500 DATABASE_ERROR	200, round-trips
[] empty array	200 — stored as {}	200, round-trips as []

⭐ The last row is the one that matters most and it is the easiest to miss. It does not crash; it silently stores an empty object where an empty array was written, because PG's array literal for [] is {} — which happens to be valid JSON. A fix that only chases the 500s leaves this wrong, silently, forever. It needs a regression pin of its own.

⭐ And the string row is the trap in the other direction: a fix that special-cases arrays re-fails "x". Scalar JSON documents ("x", true, null) are legal JSON and must round-trip.

Evidence bar

• Three pins, named separately: (a) non-empty array round-trips, (b) bare string / scalar JSON round-trips, (c) [] round-trips as [] not {}. All three are defect controls — they are red on the pre-fix tree, so ⛔ do not substitute an ablation. Show each red before, green after.
• Insert AND update. The card says POST behaves identically; verify both paths rather than fixing one.
• ⚠️ If no PG is reachable from the dev container, pin at the knex-binding level — assert the bound parameter is JSON text — and say so explicitly in the PR. ⛔ Do not report a green suite that never touched a PG binding as if it proved the matrix.
• ⭐ Name why the existing suites are blind, because that blindness is part of the defect surface: tenant environments run Turso/SQLite, a different driver path, so the seed/data suites never exercise this. If your fix's coverage lands only on the SQLite path, it protects nothing here — say which path each new test actually runs on.
• Expected NON-effects: the SQLite/Turso path unchanged; object and number bindings still 200; no change to non-JSON field binding.

Reason src/dist in both directions; prove restoration on disk. Gate union via node scripts/pm/dispatch-gates.mjs with no path arguments after the final commit; capture exits before any pipe.

Standing rules

Worktree-first before the first edit. ⛔ Never git stash. ⛔ Never edit content/docs/releases/. Baselines and debt ledgers may only shrink. Open the PR as a draft — the PM flips it. Fixes #10995. Write a changeset — this is a user-visible data-correctness fix on every PG deployment.

⚠️ GitHub's API GraphQL quota is shared by every agent on this identity and was exhausted twice today. If search_issues / issue_write fails with a rate limit, do not blind-file a finding — wait and run the duplicate search properly, as the #10842 dev correctly did.

---

Generated by Claude Code