JSON blob vs 3NF child table  ·  front-office one-to-many data

Your question, verified against the live schema today: intake_questionnaire · pc.episode_questionnaire_answers · medications_catalog. Short answer: your instinct is right — but the fix isn't "always 3NF," it's a decision rule.

1 · WHAT WE ACTUALLY STORE  (not from memory — read the migrations just now)
Old intake form — flattened into JSON / TEXT
intake_questionnaire
  • JSONB[] allergies, current_medications, medical_conditions
  • TEXT surgical_history, family_history ← not even JSON, just a blob
New Patient Connect — went further
pc.episode_questionnaire_answers
  • ONE opaque answers_data JSONB for the whole form
  • By design: "backend NEVER interprets the answers"
...but normalized where it mattered
the instinct already exists in the code
  • cols blood_thinners + details + last_dose
  • int GIQuIC aspirin_nsaid / anticoag_tx
  • table medications_catalog (full formulary)
You're right that one-to-many is being squashed into JSON — and the codebase already half-agrees with you: the clinically-decisive things got promoted to real columns/tables. The rule was applied by gut, not consistently.
2 · WHY JSON GETS CHOSEN
  • Aggregate read/write — a questionnaire is loaded & saved as ONE document; you rarely query a single allergy at intake. JSON matches the aggregate boundary.
  • Snapshot fidelity — "what the patient attested to on this date" must freeze. A shared normalized table mutates underneath you.
  • Schema velocity — intake forms churn; add a field = code change, not a migration. (The SmartForms-from-JSON ethos.)
  • Clinical bright line — patient self-report must NOT be treated as structured clinical truth until a clinician reconciles it. Opaque storage is a feature here.
  • Wire-shape match — frontend owns the shape; fewer mapping layers.
3 · WHAT JSON COSTS YOU
  • Queryability — "which patients are on anticoagulants?" (the #1 GI bleeding-risk question) becomes a jsonb_path/GIN scan instead of an indexed FK join. Your readiness + bowel-prep engines need exactly this.
  • Referential integrity — a normalized med row FKs to medications_catalog (you already have it!). JSON can't — no coding, no CHECK, no NOT NULL.
  • Quality + billing — GIQuIC & CPT are "query structured clinical facts." Facts buried in JSON = every measure is a parse.
  • Partial update — editing one med = read-modify-write the whole blob → lost-update races.
  • The concrete gap: intake stores meds/allergies as JSON → nursing "confirms" them as a boolean only → a reconciled, queryable clinical list lands nowhere.
4 · THE RULE  not "always 3NF" — decide per field on access pattern + clinical weight
Keep as JSON / TEXT snapshot — when ALL hold
  • read & written only as a whole aggregate
  • never queried / aggregated by sub-field
  • never feeds billing, GIQuIC, or the med/prep engine
  • patient self-report, not yet clinician-reconciled
Promote to a 3NF child table — when ANY holds
  • you query across the sub-records
  • it FKs a coded catalog (meds, problems)
  • it drives a clinical decision (anticoagulants, allergies)
  • it feeds quality / billing / analytics
  • sub-records edit independently
SNAPSHOT (keep)
patient's blob as JSON — immutable record of what they said. Respects the bright line, survives form churn.
→
PROJECTION (add)  ← the recommended best-of-both
at the clinician-reconciliation step, project the 3-4 decisive facts into normalized child tables (meds → FK medications_catalog, allergies, anticoagulation) = what's true & queryable.
5 · SHOULD WE CODIFY IT?  your actual question
✗ A blanket "always 3NF for data-entry tables" instruction — too crude. It fights the PC bright line, the SmartForms-from-JSON model, and over-normalizes pure snapshots. It would make the code worse in real cases.
✓ Codify the decision rule above — yes. Put it in the pact-writer / createspec data-modeling checklist + one line in backend CLAUDE.md, so every new clinical table gets the question asked before a migration is written.
// data-modeling rule (proposed drop-in)
One-to-many clinical data defaults to a normalized child table.
A JSON column is allowed only when the data is read/written solely as a whole
aggregate AND never queried by sub-field AND never feeds billing / GIQuIC / the med-or-prep engines.
Clinically-decisive facts (medications, allergies, anticoagulation) are always normalized;
a JSON snapshot may sit alongside for point-in-time fidelity, but is not the source of truth.
My honest take: the win isn't ripping out JSON — it's adding the projection layer for the 3-4 facts the engines actually query. Anticoagulation is already half-promoted; finishing that one pattern (meds + allergies → child tables FK'd to the catalog) buys you the readiness engine, GIQuIC, and bleeding-risk queries with almost no downside. The rest of the questionnaire can stay a snapshot.