Sanitize U+0000 (NUL) at the JSONB_VARIANT type boundary#

  • Status: accepted

  • Date: 2026-06-23

  • Related: ops-db#110, ops-db#107 (existing-row backfill migration), ops-db#100, data-transfer#173 (regression test), ADR-0002 (JSONB_VARIANT introduction)

Context#

PostgreSQL jsonb cannot store a NUL (U+0000). A value carrying a \x00 anywhere in its string content raises on insert: DataError: unsupported Unicode escape sequence ... � cannot be converted to text. Plain json and SQLite tolerate the byte, so the failure is invisible in the SQLite test suite and only fires on the Postgres backend (staging/prod).

The bytes arrive through legitimate writers: error breadcrumbs (error_context), tracebacks, captured stderr, and arbitrary external strings can contain a NUL. After ADR-0002 routed ~28 columns through a single JSONB_VARIANT type, every one of those columns is exposed to this failure. Defending per-writer would mean remembering to scrub at N call sites across data-transfer, ops-db-api, workflow-manager, and obs_implementation — exactly the kind of distributed obligation ADR-0002 set out to remove.

Decision#

Enforce the constraint once at the type boundary. JSONB_VARIANT is now a TypeDecorator (NulSafeJSONB, cache_ok = True) wrapping the existing JSON().with_variant(JSONB(), "postgresql") impl. Its process_bind_param recursively walks the bound value and replaces every U+0000 in a str (values and dict keys) with the sentinel ␀ (U+2400). The public name JSONB_VARIANT and the rendered DDL (jsonb on Postgres, JSON on SQLite) are unchanged, so models.py needs no edits.

A module-level NUL_SENTINEL = "␀" constant is exported for reuse by the #107 existing-row backfill migration and the data-transfer#173 regression test.

Sentinel ␀ vs. bare stripping#

NUL is replaced, not dropped. ␀ (SYMBOL FOR NULL) preserves the diagnostic location of the offending byte — an operator reading an error_context still sees where the control byte was, instead of two strings silently fused. The sentinel is an ordinary printable character, harmless in any consumer.

All dialects, not Postgres-only#

The scrub runs on every dialect. SQLite stores NUL natively and would not need it, but gating on Postgres would mean the SQLite test suite and local dev never exercise the scrub, reintroducing the dev/prod divergence that hid the original bug. Running it everywhere gives parity and lets the SQLite suite prove the behavior.

Cheap clean path#

The common case is NUL-free. The decorator first does a cheap recursive scan ("\x00" in s on the raw Python strings); only if a NUL is actually present does it build a scrubbed copy. The caller’s object is never mutated in place.

NUL detection is performed on the raw Python strings during the walk, not on json.dumps(value): json.dumps escapes NUL to the six-character text �, so a real chr(0) never survives into the dumped text and a "\x00" in dumped scan would always be False — a silent miss. Detecting on the live strings is the only correct option.

Visible, not silent#

When it substitutes, the decorator logs a WARNING with the replacement count and the top-level dict keys affected. The column name is not available inside process_bind_param, but count + keys give an operator enough to trace which writer emitted the illegal byte. Clean data logs nothing.

Considered and rejected#

  • Scrub per writer — defeats the single-chokepoint rationale of ADR-0002; every new writer is a fresh chance to forget, and the failure only surfaces on Postgres.

  • Bare stripping of NUL — loses the diagnostic location and can silently fuse adjacent tokens; the sentinel costs nothing extra.

  • Postgres-only gate — keeps the SQLite suite blind to the scrub, perpetuating the dev/prod gap.

  • Detect via json.dumps scan — wrong: NUL is escaped to � text and never appears as a real char in the dump.

Consequences#

  • Every jsonb column is NUL-safe on write with zero per-writer effort; a NUL can no longer crash an insert on Postgres.

  • Existing rows written before this change may already hold a raw NUL (only possible on SQLite / pre-jsonb data); ops-db#107 backfills them to NUL_SENTINEL using the exported constant.

  • A NUL surfacing as ␀ in stored data is an intentional, traceable signal (with a WARNING in the logs), not data corruption.

  • The clean path adds one recursive in-scan per write; negligible for the small breadcrumb/config payloads these columns hold.