Store JSON columns as jsonb via a generic-JSON-with-postgresql-variant type#

  • Status: accepted

  • Date: 2026-06-22

  • Related: ops-db#92 (epic), obs_implementation#25 (double-encoding follow-up), PR #85 (introduced the error_context json columns)

Context#

ops-db stored ~38 columns across ~25 tables as PostgreSQL plain json. The json type has no equality operator, so any SELECT DISTINCT / GROUP BY / UNION over a row that includes a json column fails on Postgres with UndefinedFunction: could not identify an equality operator for type json.

This crash-looped the data_transfer_package and archive managers in data-transfer on staging (2026-06-20): after PR #85 added error_context = Column(JSON) to the operation models, their session.query(RawDataPackage).join(...).distinct().all() dedup query began raising every poll cycle, tripping the consecutive-error CRITICAL alert. It was invisible in CI because the SQLite test DB treats json as text and happily applies DISTINCT.

Decision#

Migrate these columns to jsonb (which has equality, indexing, and containment), fixing the footgun at the root instead of per-query in every consumer.

Declare the columns through a single shared type:

# ccat_ops_db/types.py
from sqlalchemy import JSON
from sqlalchemy.dialects.postgresql import JSONB

JSONB_VARIANT = JSON().with_variant(JSONB(), "postgresql")

This maps to jsonb on PostgreSQL and generic JSON on SQLite, so the SQLite-backed test suite and local dev keep working. A bare postgresql.JSONB swap would break them — SQLite has no jsonb.

The migration is phased (ops-db#92): operation error_context columns first (closes the incident class), then the remaining proper-object columns, then the double-encoded planning columns. Each Alembic revision uses explicit postgresql_using in both directions (col::jsonb / col::json).

Double-encoded columns are cast as-is. Some planning columns are seeded via json.dumps(...) into a Column(JSON) (e.g. init_obs_units.py, utils.py), so they are stored as JSON string scalars, not objects. col::jsonb faithfully preserves that (a jsonb string scalar still has an equality operator, so DISTINCT works). Re-encoding them to proper objects is a separate decision tracked in obs_implementation#25 — deliberately not done in this epic.

Considered and rejected#

  • Bare postgresql.JSONB on the columns — simplest, but breaks the entire SQLite test suite and local dev (SQLite has no jsonb).

  • Keep json, dedupe per query in Python (the data-transfer deduplicate_by_id band-aid, PR #138) — patches known call sites but not the root; the next DISTINCT over an operation row would re-introduce the failure. Retained as a harmless DB-agnostic helper, but not the fix.

Consequences#

  • jsonb reorders object keys, collapses insignificant whitespace, and collapses duplicate keys. This is safe here: an audit of ops-db-api, data-transfer, workflow-manager, obs_implementation, and ops-db-api-client found no consumer that relies on serialized JSON text or key order — all access is key-based on parsed dicts.

  • Because SQLite cannot reproduce the json-equality failure, a focused Postgres-only regression test (.distinct() over each operation model, run via a -m postgres CI step against the existing PG service) guards the class going forward. The general SQLite-vs-Postgres functional gap (running the full suite on Postgres) is tracked as a separate hardening issue.