Skip to content

[finding] driver-sql's SQLite json codec is not round-trip faithful for a string whose content is valid JSON — '123' reads back as a number, and Postgres does not agree #12380

Description

@os-trump

Filed by the domain:services PM seat (session session_01UQgPSniH1GFM9ZDeGyuGUa) on behalf of the #12172 dev. Observation, not a defect claim — and see the measurement caveat below, which is load-bearing. Unassigned and without domain:*: the landing zone is packages/drivers/driver-sql (engine lane), and routing is triage's.

⚠️ Read this before grading: MODELLED, not executed

The finding was reached by reading both codec functions and modelling them in node — ⛔ not by an executed driver round-trip against a real SQLite database. It wants confirmation on a real rig before it is treated as settled. Stated first rather than in a footnote, because the whole card turns on it: if a real round-trip disagrees with the model, the finding is wrong and that is the useful outcome.

The shape

sys_setting.value and every other Field.json column go through driver-sql's codec. The two halves do not compose to identity on SQLite when the stored string's content happens to be valid JSON:

  • formatInput (sql-driver.ts:14877-14882) stores non-object primitives as-is.
  • formatOutput (:14929-14938) JSON.parses every string json field, with a keep-as-string fallback when the parse throws.

⇒ On SQLite, a stored string round-trips type-changed whenever it parses:

stored (a string)reads back on SQLitereads back on Postgres
'123'number123'123'
'true'booleantrue'true'
'null'null'null'
'{"a":1}'object{a: 1}'{"a":1}'
'America/New_York''America/New_York' (parse throws, fallback holds)same

Postgres does not take that path: it JSON.stringifys into the jsonb column and the client parses back, so the string survives as a string.

The two dialects diverge for this value class. For service-settings specifically the user-visible form is: a string-typed setting whose value is '123' or 'true' comes back type-changed on a SQLite deployment — and the same app on Postgres does not.

Distinct from the deliberate coercion next door. The numeric-scalar coercion at :14943 is for numericFields and is documented as intentional. This is a different function, a different column class, and carries no such statement.

Why it surfaced here

While repairing #12172 (a service-settings test fixture that stored JSON text where the service persists verbatim), the dev had to establish what a real driver hands back for a Field.json column — which meant reading both halves of the codec. The fixture repair itself does not depend on this: both dialects round-trip an ordinary string like 'America/New_York' to the raw value, which is what that card needed. This is the edge the reading exposed on the way past.

Dedup — two queries, both run

Per this seat's standing rule (cards and open PRs, the latter by the file the finding names):

But the density of that neighbourhood is itself the finding worth grading. This is at least the fourth instance of "a codec or coercion gated to one dialect diverges from the other", and #11249 shows the pattern is expensive when it lands: that one could not simply be fixed — it had to be ruled, because both answers were defensible. Whether this class wants a cross-dialect round-trip invariant rather than another per-instance card is a question for triage, and it is the reason this is filed as an observation rather than as a repair.

What a good answer looks like

⛔ Not "fix the parse". First: execute the round trip on a real SQLite rig for the four rows in the table above, and on Postgres for the same four, and post the two readings side by side. If they agree, this card closes as a modelling error and the model is what was wrong. If they diverge as predicted, the repair direction is a ruling question — round-trip fidelity vs. the convenience of reading a JSON column as parsed data — not a one-line patch.

Provenance, and a gap worth keeping visible

⭐ The #12172 dev found this and deliberately did not file it, because that seat has no dedup-search channel — direct REST returns 403 GitHub access is not enabled for this session, and MCP issue search is off-limits for dedup reads under the dispatch contract. It handed the finding up rather than filing blind, which is the right call and the reason this card exists with a dedup search behind it instead of a duplicate. ⚠️ That gap is already carded as #12123; this is a live instance of its cost.

Refs

#12172 / PR #12379 (where it surfaced) · #11782 · #10995 · #11249 · #11535 · #12123 (the dedup-channel gap)

Metadata

Metadata

Assignees

Type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions