# 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:

```python
# 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.