JSON vs JSONB in Production

Enterprise problem: Support engineers cannot explain a disputed answer because the current fixed columns discard provider-specific response details and tool evidence, while an unconstrained document would weaken tenant isolation and auditability.

Whole-course context: The production assistant already emits the versioned answer evidence and trace identifier established by the preceding production design; today persists that evidence as a queryable contract.

Today’s slice: Add stable relational columns plus a bounded PostgreSQL jsonb envelope for variable retrieval, model-provider, and tool-call metadata inside the generated-application data boundary.

End-of-day evidence: Produce reviewed DDL, validation and index choices, measured EXPLAIN (ANALYZE, BUFFERS) plans, and a reversible backfill report with rejected-row samples.

Still unsolved: Cross-system dashboards, LLM-quality evaluation, telemetry retention, and alert routing remain for the next day.

Thesis: json preserves the submitted text; jsonb gives PostgreSQL a processable, indexable representation. The assistant needs neither a document dump nor a column for every provider field—it needs a hybrid contract that preserves the semantics operators must trust.

The boundary is deliberate. This day covers one answer write, its tenant-safe explanation read, the schema and indexes that support those paths, and a reversible migration. It does not turn PostgreSQL into raw-payload archival storage or design tomorrow's observability system.

Without named customer jobs, a flexible column becomes a dumping ground and operators cannot tell which queries or recovery paths deserve support. These two use cases bound the evidence envelope and its operational ownership.

Use case ID	Actor	Customer job	Success outcome	Denial or recovery evidence
D39-UC-01	Tenant support engineer	Find the exact retrieval, model, and tool evidence behind a disputed assistant answer	One tenant-scoped lookup returns stable answer fields plus the matching evidence envelope and trace ID	Cross-tenant lookup returns no row; malformed envelopes are rejected with a constraint name and immutable ingestion ID
D39-UC-02	Database platform engineer	Introduce and operate variable evidence without destabilizing answer writes or incident queries	Backfill, measured indexes, and cutover complete inside declared latency, storage, and error budgets	Failed batches remain retryable; old reads stay available until reconciliation passes; rollback evidence names release and migration run IDs

Vague requirements such as "store the whole response" hide who must inspect or recover the data and what proof closes the work. Actor-centred stories convert both use cases into observations that can fail in review.

Story ID	Use case IDs	User story	Observable acceptance conditions
D39-US-01	D39-UC-01	As a tenant support engineer, I want to query an answer by tenant and trace ID, so that I can explain which evidence and tool result produced it without exposing another tenant	The response identifies evidence schema version, retrieval sources, provider/model, tool outcomes, and trace ID; a different tenant receives no record; invalid or oversized metadata is rejected
D39-US-02	D39-UC-02	As a database platform engineer, I want to backfill and index only proven access paths, so that evolving metadata remains queryable without unacceptable write amplification or an irreversible cutover	Batch counts reconcile, query plans and p95 latency are recorded before and after each index, lock and storage budgets hold, and disabling the new reader restores the prior path

Variable evidence crosses request handling, PostgreSQL validation, customer review, and migration control, so a happy write alone cannot prove the design. The flows start with visible product actions and finish with evidence an accountable actor can inspect.

Flow ID	Use case IDs	Path	Trigger	Numbered steps	Terminal evidence
D39-FLOW-01	D39-UC-01	Happy	A customer submits a support question and later opens "Explain this answer"	1. The API authorizes the tenant and assigns answer and trace IDs.<br>2. Retrieval, provider, and tool stages emit bounded metadata.<br>3. The writer validates the envelope and inserts typed columns plus jsonb atomically.<br>4. The explanation query filters by tenant and trace ID.<br>5. The product renders sources, model version, and tool outcomes.	Evidence view records actor, tenant, answer ID, trace ID, schema version, release, expected fields, observed fields, environment, and timestamp
D39-FLOW-02	D39-UC-01	Denied	A buggy provider adapter sends an unknown top-level key or oversized tool payload	1. The API forms the proposed envelope.<br>2. PostgreSQL CHECK constraints reject its shape or size.<br>3. The transaction rolls back without an answer row.<br>4. The adapter stores a redacted rejection event outside the response path.<br>5. A valid request for the same tenant succeeds as a positive control.	Constraint name, redacted payload hash, ingestion ID, trace ID, release, observed rollback, and unaffected positive-control answer ID
D39-FLOW-03	D39-UC-02	Happy	A platform engineer starts the approved expand-and-contract migration	1. Add nullable evidence_meta and validation without changing the old reader.<br>2. New writes populate old fields and the envelope.<br>3. A retryable worker backfills keyset batches.<br>4. Reconciliation compares counts and sampled values.<br>5. Measured indexes are built and the new reader is gradually enabled.	Migration run ID, batch watermarks, rejected-row count, reconciliation digest, index sizes, query plans, latency percentiles, and release ID
D39-FLOW-04	D39-UC-02	Recovery	Reconciliation or write latency breaches its threshold during rollout	1. Pause the worker at its committed watermark.<br>2. Disable the new reader while dual writes continue or are safely disabled.<br>3. Drop only an unneeded concurrently built index after confirming its target.<br>4. Repair rejected rows and resume from the watermark.<br>5. Re-run reconciliation before a later cutover.	Rollback time, last committed answer ID, preserved old-reader result, repaired-row sample, post-recovery latency, and immutable migration run ID

If ownership is inferred from a generic architecture picture, the assistant API, migration worker, and database can each assume another component validates the document. This design assigns one writer contract and derives every service from the numbered flows.

Use case ID	Entry point	Responsible services	Authoritative store	Failure evidence
D39-UC-01	Ask question and Explain this answer actions	Tenant gateway; assistant orchestrator; evidence-envelope builder; answer repository; support evidence view	PostgreSQL assistant_answer owned by the answer repository; typed columns own invariants and evidence_meta owns bounded variable evidence	Named constraint violation, rolled-back answer ID, redacted payload hash, trace ID, or tenant-filtered no-row result
D39-UC-02	Approved migration run and reader rollout control	Migration controller; keyset backfill worker; reconciliation job; query-plan benchmark; release controller	PostgreSQL migration ledger and assistant_answer rows owned by the database platform team; release controller owns reader flag	Batch watermark, reconciliation mismatch, lock timeout, p95 write breach, invalid-index state, release rollback event

If stable identity and policy fields disappear inside JSON, constraints, foreign keys, tenant filtering, and lifecycle operations become harder to enforce. The database therefore keeps invariants relational and grants flexibility only to a versioned evidence envelope.

Generated-application database: Required in this slice — the answer repository owns tenant-scoped assistant answers and their bounded production evidence in PostgreSQL.

Record or entity	Store and owner	Primary key	Foreign key or opaque reference	Tenant key	Material constraint	Lifecycle and deletion	Use case IDs
AssistantAnswer	PostgreSQL assistant_answer; answer repository	answer_id UUID	conversation_id local FK to the conversation; trace_id opaque correlation reference	tenant_id	Unique (tenant_id, answer_id); non-null status, timestamps, release, response text, and evidence schema version; row-level tenant predicate	Created with the answer; retained under support policy; tenant deletion removes or tombstones answer and envelope together	D39-UC-01, D39-UC-02
EvidenceEnvelope	assistant_answer.evidence_meta jsonb; answer repository	Same answer_id as owning row — envelope is not independently addressable	Retrieval document and provider request IDs are opaque references, not authority	Inherits tenant_id from owning row; tenant ID is forbidden inside the envelope	Top-level object; allowed keys only; bounded byte size; typed required subfields; version must equal relational evidence_schema_version	Inserted and deleted atomically with answer; redacted or compacted by policy; incompatible versions migrate through explicit transforms	D39-UC-01, D39-UC-02
MigrationRun	PostgreSQL evidence_migration_run; database platform team	migration_run_id UUID	Release ID and last processed answer ID are opaque references	tenant_scope identifies all tenants or a bounded tenant cohort	Monotonic keyset watermark; unique migration name plus release; terminal state requires reconciliation digest	Created before backfill; immutable terminal summary retained for audit; detailed batch logs expire on schedule	D39-UC-02
EvidenceRejection	Restricted operational event store; evidence-envelope builder	rejection_id UUID	Opaque answer attempt, trace, constraint, and payload-hash references	tenant_id	No raw prompt, response, credentials, or tool payload; reason code from controlled set	Short retention for adapter repair; deleted automatically; aggregate counts retained without customer content	D39-UC-01, D39-UC-02

The diagrams above add actors, recovery paths, services, and records, but the governing model remains three boxes: evidence enters, a storage contract preserves the right semantics, and a supported decision comes out. If a proposed field or index cannot be assigned to one of those responsibilities, it is probably accidental complexity.

Use the same disputed answer throughout the design review. tenant_id, answer_id, trace_id, status, and timestamps decide ownership and lifecycle, so they stay typed. Retrieval hits, provider details, and tool outcomes vary together as answer evidence, so they live in the bounded envelope. The support read and migration benchmark then prove whether that split is useful.

This gives a reusable rule: model invariants for enforcement, model variable evidence for evolution, and add physical optimization only after a real query proves it is needed. The following sections expand those three decisions without changing the model.

Choosing by the word "JSON" hides a material semantic difference: one type preserves input text while the other optimizes a normalized representation for processing. PostgreSQL's current JSON type documentation confirms that json stores the exact input text and must reparse it, while jsonb stores a decomposed form, supports indexing, and is the general recommendation unless exact textual representation matters. Neither type removes the need for an application schema.

Concern	json	jsonb	Production consequence
Stored form	Exact copy of input text	Decomposed binary representation	json must be reparsed for processing; jsonb pays conversion overhead on input but is faster to process
Whitespace and key order	Preserved	Not preserved	Use json only when the original textual representation itself is required
Duplicate object keys	All pairs preserved; processing treats the last as operative	Earlier duplicates discarded; last value kept	Neither is a safe substitute for rejecting ambiguous producer payloads before storage
Numeric input	Accepts JSON numbers as text	Maps numbers to PostgreSQL numeric and rejects values outside its range	Provider payloads can be valid JSON yet fail a jsonb cast; test limits before rollout
Operators and indexing	JSON functions exist, but no equivalent containment/indexing contract	Containment, existence, JSON path, and GIN indexing	jsonb fits evidence fields that support operational queries
Write cost	Lower input conversion cost	Slightly slower input plus index maintenance	Measure end-to-end write latency and WAL, not only SELECT speed

For the assistant, exact whitespace, key order, and duplicate keys are not business evidence; PostgreSQL documents that jsonb does not preserve those textual details and keeps only the last duplicate key. The original provider payload, when policy permits retaining it, belongs in a separately protected immutable object with a digest. The queryable envelope should be jsonb, but stable invariants such as tenant_id, answer_id, conversation_id, trace_id, release_id, status, and timestamps remain typed relational columns.

An unconstrained jsonb object permits silent producer drift, unbounded rows, and values whose apparent types change between releases. SQL constraints make the supported envelope small enough to operate and force incompatible changes through an explicit schema version.

CREATE FUNCTION evidence_top_level_keys_allowed(value jsonb)
RETURNS boolean
LANGUAGE sql
IMMUTABLE
STRICT
PARALLEL SAFE
AS $$
  SELECT NOT EXISTS (
    SELECT 1
    FROM jsonb_object_keys(value) AS key_name
    WHERE key_name NOT IN ('retrieval', 'model', 'tools')
  )
$$;

CREATE TABLE assistant_answer (
  answer_id uuid PRIMARY KEY,
  tenant_id uuid NOT NULL,
  conversation_id uuid NOT NULL REFERENCES support_conversation(conversation_id),
  trace_id text NOT NULL,
  release_id text NOT NULL,
  status text NOT NULL CHECK (status IN ('completed', 'refused', 'failed')),
  response_text text NOT NULL,
  evidence_schema_version smallint NOT NULL DEFAULT 1 CHECK (evidence_schema_version = 1),
  evidence_version integer NOT NULL DEFAULT 0 CHECK (evidence_version >= 0),
  evidence_meta jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, answer_id),
  CHECK (jsonb_typeof(evidence_meta) = 'object'),
  CHECK (octet_length(evidence_meta::text) <= 65536),
  CHECK (evidence_top_level_keys_allowed(evidence_meta)),
  CHECK (evidence_meta ? 'model'),
  CHECK (jsonb_typeof(evidence_meta->'model') = 'object'),
  CHECK (jsonb_typeof(evidence_meta->'tools') IS NULL
         OR jsonb_typeof(evidence_meta->'tools') = 'array'),
  CHECK (NOT evidence_meta ? 'tenant_id')
);

An allowed-key test based on contained-by (<@) with null placeholders would be wrong because object containment compares values as well as keys. The immutable validation function compares the actual top-level key set instead:

CHECK (NOT EXISTS (
  SELECT 1
  FROM jsonb_object_keys(evidence_meta) AS key_name
  WHERE key_name NOT IN ('retrieval', 'model', 'tools')
))

PostgreSQL does not allow a subquery directly inside a CHECK, so the DDL wraps that key-set test in an immutable function. In production, schema-qualify the function and lock down its schema search path; application validation should mirror it for fast feedback. The database constraint remains the final guard because not every writer necessarily runs the same application code.

Distinguish three states deliberately: SQL NULL means no envelope value at all, a missing JSON key means the producer did not supply that field, and JSON null means the producer explicitly supplied null. Here the column is non-null; queries distinguish missing from explicit null with existence and JSON type checks:

SELECT
  answer_id,
  evidence_meta ? 'retrieval' AS retrieval_present,
  jsonb_typeof(evidence_meta->'retrieval') AS retrieval_json_type
FROM assistant_answer
WHERE tenant_id = $1;

Indexing the whole envelope before observing queries increases storage, write latency, vacuum work, and write-ahead-log volume without proving that a useful plan will choose the index. PostgreSQL's documented jsonb indexing trade-offs show why targeted expression indexes can be smaller and faster while a whole-document GIN index supports a broader operator set. Begin with tenant and trace lookups, then add the smallest index that matches an actual repeated predicate.

-- Stable invariant lookup: ordinary B-tree.
CREATE INDEX CONCURRENTLY assistant_answer_tenant_trace_idx
  ON assistant_answer (tenant_id, trace_id);

-- Containment: answers whose model provider is AcmeAI.
SELECT answer_id, trace_id
FROM assistant_answer
WHERE tenant_id = $1
  AND evidence_meta @> '{"model":{"provider":"AcmeAI"}}'::jsonb;

-- Containment applied to the indexed tools-array expression.
SELECT answer_id
FROM assistant_answer
WHERE (evidence_meta->'tools')
      @> '[{"name":"crm.lookup_customer"}]'::jsonb;

CREATE INDEX CONCURRENTLY assistant_answer_tools_gin_idx
  ON assistant_answer USING GIN ((evidence_meta->'tools'));

An expression index is attractive when one path dominates: it stores only values under that expression and is usually smaller than indexing every key and value. The example's expression GIN supports containment against the array of tool objects. The existence operator ? would test top-level keys or string array elements, so it would not find the name field inside these objects. A default whole-column GIN index is broader and supports ?, ?|, ?&, @>, @?, and @@ when those operators apply directly to the indexed column.

CREATE INDEX CONCURRENTLY assistant_answer_evidence_gin_idx
  ON assistant_answer USING GIN (evidence_meta);

CREATE INDEX CONCURRENTLY assistant_answer_evidence_path_idx
  ON assistant_answer USING GIN (evidence_meta jsonb_path_ops);

jsonb_path_ops supports containment and JSON-path matches (@>, @?, @@) but not key-existence operators. It is usually smaller and more specific than the default jsonb_ops, yet it creates no entries for structures containing no values, such as {"retrieval":{}}; those searches can degrade to a full index scan. Choose it only when measured predicates fit that operator set.

Capture evidence before and after, with representative tenant selectivity and production-like data volume:

EXPLAIN (ANALYZE, BUFFERS, WAL, FORMAT TEXT)
SELECT answer_id
FROM assistant_answer
WHERE tenant_id = '11111111-1111-1111-1111-111111111111'
  AND evidence_meta @> '{"model":{"provider":"AcmeAI"}}'::jsonb;

SELECT
  pg_size_pretty(pg_relation_size('assistant_answer_evidence_gin_idx')) AS index_size,
  idx_scan,
  idx_tup_read
FROM pg_stat_user_indexes
WHERE indexrelname = 'assistant_answer_evidence_gin_idx';

Keep an index only when plans, p50/p95/p99 latency, index size, write latency, WAL bytes, and scan counts justify it. A sequential scan can be correct for a small table or a predicate matching most rows.

A one-shot column conversion can hold locks, amplify writes, reject an unexpected numeric value, and leave old application instances unable to read new state. Expand-and-contract keeps both representations usable until reconciliation proves the new contract.

ALTER TABLE assistant_answer
  ADD COLUMN evidence_meta jsonb;

-- Worker repeats this transaction with the returned max(answer_id) as watermark.
WITH batch AS (
  SELECT answer_id
  FROM assistant_answer
  WHERE evidence_meta IS NULL
    AND answer_id > $1
  ORDER BY answer_id
  LIMIT 1000
  FOR UPDATE SKIP LOCKED
)
UPDATE assistant_answer AS target
SET evidence_meta = jsonb_build_object(
  'retrieval', target.legacy_retrieval,
  'model', target.legacy_model,
  'tools', COALESCE(target.legacy_tools, '[]'::jsonb)
)
FROM batch
WHERE target.answer_id = batch.answer_id
RETURNING target.answer_id;

The worker commits small batches, records a monotonic watermark, and isolates cast failures so one bad provider number does not restart the whole migration. Dual-write is not proof of equality: reconciliation compares populated counts, deterministic projections, tenant samples, and rows rejected by the target constraints. Read preference moves by tenant cohort; dual-read logs mismatches but serves from the old representation during the rollback window. Only after reconciliation and stable latency does the contract phase enforce NOT NULL and retire old fields.

Large or frequently updated envelopes can create latency even when reads are indexed because PostgreSQL locks the whole row for an update and may move large values to TOAST, its out-of-line storage mechanism. Rewriting a small nested key can still produce a new row version, new large-value storage, GIN maintenance, and additional write-ahead log.

Keep the envelope bounded, prefer append-only child rows for independently changing high-volume tool events, and avoid a single ever-growing conversation document. When two workers may update one answer, use optimistic concurrency or an atomic update with a version predicate rather than read-modify-write in application memory:

UPDATE assistant_answer
SET evidence_meta = jsonb_set(
      evidence_meta,
      '{model,finish_reason}',
      to_jsonb($1::text),
      true
    ),
    evidence_version = evidence_version + 1
WHERE tenant_id = $2
  AND answer_id = $3
  AND evidence_version = $4
RETURNING evidence_version;

Zero returned rows means another writer won; reload, revalidate, and retry only if the operation is safe. Do not assume two nested-key updates avoid contention: PostgreSQL concurrency control still applies to the containing row.

Track pg_column_size(evidence_meta), table and TOAST growth, GIN size, WAL bytes per answer, dead tuples, vacuum duration, lock waits, and write latency. If tool events dominate churn, normalize them into an assistant_tool_event table keyed by answer and sequence while retaining a compact summary in the envelope.

Allowing each provider adapter to invent its own database shape spreads validation across callers and makes recovery evidence inconsistent. A single TypeScript builder normalizes provider-specific inputs into the versioned envelope before PostgreSQL performs its independent final checks.

type EvidenceEnvelopeV1 = {
  retrieval?: {
    sources: Array<{ evidenceId: string; rank: number; score?: number }>;
  };
  model: {
    provider: string;
    model: string;
    requestId?: string;
    finishReason?: string | null;
  };
  tools?: Array<{
    name: string;
    callId: string;
    outcome: 'succeeded' | 'denied' | 'failed';
  }>;
};

export function buildEvidenceEnvelope(input: EvidenceEnvelopeV1): EvidenceEnvelopeV1 {
  const allowed = new Set(['retrieval', 'model', 'tools']);
  for (const key of Object.keys(input)) {
    if (!allowed.has(key)) throw new Error(`unsupported evidence key: ${key}`);
  }
  if (!input.model.provider || !input.model.model) {
    throw new Error('model provider and model are required');
  }
  const bytes = Buffer.byteLength(JSON.stringify(input), 'utf8');
  if (bytes > 65_536) throw new Error('evidence envelope exceeds 64 KiB');
  return input;
}

The adapter records only bounded identifiers and outcomes by default. Raw prompts, responses, retrieved text, and tool arguments require a separate purpose, access policy, redaction path, and retention decision; putting them in jsonb does not make them safe.

A rollback claim is weak unless it preserves the exact failed input class, the unaffected path, and the database state after recovery. Run failure drills before contracting the old schema.

Failure injection	Expected containment	Recovery action	Required proof
Provider returns a number outside PostgreSQL numeric range	Proposed jsonb cast fails and answer transaction rolls back	Store redacted hash and provider request ID; transform or reject according to contract; retry with same idempotency key	SQLSTATE, constraint or cast error, trace ID, no partial row, retry answer ID
Unknown top-level key arrives	Database validator rejects producer drift	Update adapter or introduce a reviewed schema version; never silently accept	Rejection ID, release, key name, payload hash, valid positive control
New GIN index raises write p95 above budget	Reader remains on old plan; answer writes continue	Mark index invalid/unused as appropriate, verify target, then remove it concurrently; remeasure	Before/after plans, write latency, WAL, index size, rollback timestamp
Backfill process crashes	Committed batches remain valid; uncommitted batch rolls back	Resume after last durable keyset watermark	Migration run ID, watermark, counts before and after, no duplicate mutation
Two workers update the same envelope	Version predicate lets one writer win	Losing worker reloads and applies a safe merge or stops	One incremented version, zero-row loser result, final envelope, both trace IDs

Claims about storage semantics, containment, concurrency, and JSON indexing should be checked against the database version actually deployed. These official references define the capabilities used here.

• PostgreSQL: JSON Types
• PostgreSQL: GIN Indexes

Without a compact decision record, teams may remember that jsonb is flexible but forget the constraints and operating evidence that make it safe. These points preserve the production trade-offs that should survive design review.

• json preserves the input text, whitespace, key order, and duplicate keys; jsonb stores a decomposed representation, normalizes those details, rejects numbers outside PostgreSQL numeric, costs more on input, and supports efficient processing and indexes.
• Keep tenant, identity, lifecycle, authorization, and common join fields relational; use a bounded jsonb envelope only for variable metadata with an owned version and database validation.
• Distinguish SQL NULL, a missing JSON key, and JSON null; each carries different evidence.
• Choose expression, default GIN, or jsonb_path_ops indexes from repeated predicates and measured plans, sizes, latency, WAL, and write cost.
• Migrate through expand, dual-write, keyset backfill, reconciliation, cohort reads, rollback window, and contract; make failed batches and rejected rows independently visible.
• A nested jsonb update still contends on and versions the containing row; bound size and extract independently mutable event streams when write amplification dominates.

An apparently complete schema can still fail under malformed provider data, an unselective index, or an interrupted backfill. Use this checklist to demand observable proof before removing the old representation.

• [ ] Stable tenant, identity, trace, release, status, and lifecycle fields are typed columns rather than hidden in the envelope.
• [ ] The envelope has an owner, schema version, allowed-key and type validation, a byte limit, and explicit deletion and retention behaviour.
• [ ] Producer tests cover duplicate keys, out-of-range numbers, unknown fields, missing keys, JSON null, and SQL null.
• [ ] Every supported query applies the intended operator to the indexed column or expression and includes the tenant predicate.
• [ ] EXPLAIN (ANALYZE, BUFFERS, WAL) and production-shaped measurements justify every added index.
• [ ] The migration records batch watermarks, rejected rows, reconciliation evidence, reader cohorts, and rollback results before old fields are removed.
• [ ] Operational dashboards track row and TOAST size, GIN growth, dead tuples, vacuum, locks, write latency, and WAL per answer.
• [ ] Failure drills prove malformed writes roll back, backfill resumes, concurrent updates do not overwrite each other, and an old read path remains available.