Medical Templates Framework —
From PL/SQL Sprawl to Metadata.

Clínica Alemana de Santiago is one of Chile's largest private hospitals. Inside its EHR, doctors use more than 320 medical templates — forms for hospitalization, surgery, oncology follow-up, home care, every clinical workflow has its own. Each template has dozens of variables that change as protocols evolve.

The legacy system was a constellation of PL/SQL stored procedures — one per template, each one a PRC_PLANT_* with thousands of hardcoded IF-ELSIF branches mapping vertical key-value rows to relational columns. When a doctor added a new variable in production, the procedure either failed silently or dropped the data on the floor.

Every change required engineering intervention. The data team became a bottleneck for clinical operations.

The bet was simple: treat the template structure as metadata in BigQuery, not as columns in physical tables. A single ingestion engine reads the metadata, normalizes names to snake_case, and emits idempotent SQL deltas — the same engine handles 320 templates without a single line of template-specific code.

Variable values live as json_general objects per module/group, leveraging BigQuery's native PARSE_JSON, JSON_KEYS and JSON_TYPE. This means one physical table supports all templates without ever altering the schema. Adding a new template is a data event, not a deploy.

Plantilla 329 has 67 fields spread across 5 clinical modules. The doctor fills the form in the EHR, Oracle stores it vertically, the framework normalizes it to JSON, BigQuery flattens it to a view, and Looker renders it. At no point does a human write IF variable_id = 41892 THEN nombre_antibiotico_3. The metadata drives everything.

The engine — plantilla_ingestor_v5.py — compares the new Oracle JSON against the current BigQuery state and decides, per field, whether it's new, updated, deactivated, or unchanged. The hard part isn't the comparison; it's that the catalog has been written by multiple versions of the script over time. UUID vs MD5 IDs, stopword stripping that changed, module names that drifted (datos_paciente vs datos_de_paciente).

A naive join produces 87 false positives where there are 35 real changes. The fix: a three-level resolver that tries id_registro first, falls back to natural key, and finally to drift-tolerant matching.

What follows is a real run on plantilla 329 (Hospital Home Care). On the left, the JSON exported from Oracle — vertical, flat, semantically opaque without context. On the right, what the engine produces — categorized, hashed, idempotent SQL ready to merge into BigQuery.

Nothing was hand-edited between input and output. The same engine handles all 320 templates with this exact contract.

Note on language: Production code is in Spanish (hospital standard). Field names like plan_tratamiento and nombre_antibiotico_3 translate to treatment_plan and antibiotic_name_3. The framework preserves whatever naming convention Oracle uses — no translation layer.

The final view for plantilla 329 has 67 flattened columns. Each one is a JSON_VALUE extraction from json_general. Without the framework, those 67 columns would have been 67 hardcoded IF-ELSIF branches in PL/SQL. With the framework, they're generated automatically by reading the JSON structure that's already stored in BigQuery.

This is reverse engineering as a design pattern. The schema doesn't dictate the data — the data dictates the schema. When Oracle adds a field, it appears in the JSON. The DDL constructor reads the JSON keys, writes the new column, and rebuilds the view. No human touches the SQL.

The constructor runs once per template whenever the catalog changes. It reads the actual JSON stored in BigQuery, discovers the structure with JSON_KEYS, and writes the DDL. The analyst copies the output, pastes it into BigQuery, and the view rebuilds.

This is the opposite of schema-first design. The data defines the schema. When Oracle evolves, the JSON evolves, the constructor re-reads it, and the DDL regenerates. Zero migrations, zero ALTER TABLE, zero manual column mapping.

Clinical analysts run this — not engineers. The original argparse CLI required memorizing 8 flags and remembering output paths. The wizard asks one question at a time, validates each answer in place, and confirms the plan before executing. Output goes to the script's folder, period — no "where do you want this?" prompt that shifts cognitive load to the operator.

The naive matcher would have reported 87 new fields and 79 deletions for plantilla 329 — a panic-inducing diff. The 3-level resolver tells the truth: 35 genuinely new fields, 52 silent renames preserved, 34 actual soft-deletes. Most of the apparent churn was schema drift between script versions, not Oracle drift.

Before the SQL ever touches BigQuery, the analyst opens the auto-generated Excel and reviews what's about to change. Green is new, amber is updated, red is soft-delete. The collisions tab flags fields whose name appears in more than one module — the engine resolves them with deterministic prefixes, but the human gets the final say.

The Friday CDC is a Scheduled Query that compares the current catalog against the previous week's snapshot and writes detected changes into registro_plantillas_cdc_log with state PENDIENTE. The dashboard shows it. The next Monday, the analyst downloads the state for those exact templates — no need to know which templates need attention, the CDC tells them.

The orchestrator generates the closing SQL itself — every run produces a cierre_cdc.sql that flips the processed records to PROCESADO_INGESTA. Without that file, the next CDC re-detects the same changes as pending. The cycle's self-closing is what makes the system operational at scale.

The original argparse CLI required the operator to remember 8 flags. That was wrong for the user — clinical analysts run this, not engineers. run_plantilla.py now opens an interactive wizard: step-by-step questions, smart defaults, validation in-place. No flags, no manual, no decisions about output paths.

Every execution writes to a deterministic folder structure ({id}/ for individual runs, bloque_{date}/ for batches), logs to a single ejecuciones.jsonl audit file with SHA-256 hashes of every input, and ends by printing the next 4 manual steps in execution order.