Commit the Plan, Not the Guess: SQL Conversion Pipelines That Repeat

Database teams solved schema-as-code years ago. DDL lives in git, migrations get reviewed, nobody types ALTER TABLE into production by hand. Then the schema has to leave the database — become a JSON Schema for the API team, a contract for the platform team, DDL for a second warehouse — and the discipline evaporates into a conversion script that one person ran once on a laptop.

The CoreModels transform engine is built so that the conversion itself can be reviewed and repeated. Every mapping call returns the executed plan as an artifact. Every stored plan replays through the same validation gate as a live run. The executor is deterministic: same plan plus same source produces the same output. This article assembles those properties into pipelines with SQL DDL (format key sql) on one end.

Throughout: https://coremodels.example.com as the host, Authorization: Bearer $TOKEN and Content-Type: application/json on every request, and $PROJECT_ID a project you can read — both engine routes here are stateless, so Viewer is enough.

Step one: produce three artifacts, commit all three

A schema/map call decodes the DDL, produces a plan toward a target hint, gates it, executes it, and encodes the result. Split the response and everything you need for review is on disk:

mkdir -p plans artifacts

jq -n --rawfile s ddl/sensor_reading.sql --rawfile hint canonical/reading.schema.json '{
  sourceFormat: "sql",
  sourceSchema: $s,
  targetFormat: "jsonschema",
  targetHintFormat: "jsonschema",
  targetHintSchema: $hint,
  mapping: { kind: "inferred" }
}' | curl -sS -X POST \
    "https://coremodels.example.com/graph/transform/schema/map/$PROJECT_ID" \
    -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
    --data-binary @- > response.json

jq '.plan'      response.json > plans/sensor_reading.plan.json
jq '.schema'    response.json > artifacts/sensor_reading.schema.json
jq '.lossiness' response.json > artifacts/sensor_reading.lossiness.json

The plan is the reviewable object — a colleague reads what mapped to what without rerunning anything. The ledger is the honest diff. And because schema/map never writes to the project, running this in CI is safe by construction: every call is inherently a dry run.

Step two: stop guessing, start repeating

Inference is a bootstrapping tool, not a production dependency. Once a plan is reviewed and committed, the pipeline should ask the engine to repeat, not to guess. That is plan/execute — and note the plan travels as a JSON string:

jq -n --rawfile s ddl/sensor_reading.sql --rawfile p plans/sensor_reading.plan.json '{
  sourceFormat: "sql",
  sourceSchema: $s,
  plan: $p,
  targetFormat: "jsonschema"
}' | curl -sS -X POST \
    "https://coremodels.example.com/graph/transform/plan/execute/$PROJECT_ID" \
    -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
    --data-binary @- | jq '.schema' > artifacts/sensor_reading.schema.json

A stored plan earns no shortcut: it passes the same gate as a freshly produced one, then executes deterministically. If someone edits the DDL, the replay either still validates — the change was compatible — or fails at the gate with a path-carrying error naming the construct the plan can no longer find. Both outcomes are what CI is for. Neither is silent drift.

Plans are editable, and that is the point

Inference produces what it can defend and reports the rest. When the ledger says something was left behind, the fix belongs in the plan.

The clearest SQL case is an ENUM column. Inference matches by label, and a SQL enum arrives as an unnamed vocabulary, so it never matches — mapping the column without mapping its vocabulary stops execution with Referenced taxonomy 'sensorReadingQualityEnum' does not resolve. One added operation fixes it permanently:

{
  "operations": [
    { "kind": "TypeMapping", "origin": "Explicit", "sourceTypeId": "sensorReading", "targetTypeId": "sensorReading", "targetLabel": "sensor_reading" },
    { "kind": "ElementMapping", "origin": "Explicit", "sourceElementIds": ["sensorReadingReadingId"], "targetElementIds": ["sensorReadingReadingId"] },
    { "kind": "ElementMapping", "origin": "Explicit", "sourceElementIds": ["sensorReadingDeviceId"], "targetElementIds": ["sensorReadingDeviceId"] },
    { "kind": "ElementMapping", "origin": "Explicit", "sourceElementIds": ["sensorReadingTakenAt"], "targetElementIds": ["sensorReadingTakenAt"] },
    { "kind": "ElementMapping", "origin": "Explicit", "sourceElementIds": ["sensorReadingCelsius"], "targetElementIds": ["sensorReadingCelsius"] },
    { "kind": "ElementMapping", "origin": "Explicit", "sourceElementIds": ["sensorReadingQuality"], "targetElementIds": ["sensorReadingQuality"] },
    { "kind": "TaxonomyMapping", "origin": "Explicit", "sourceTaxonomyId": "sensorReadingQualityEnum", "targetTaxonomyId": "sensorReadingQualityEnum", "targetTreatment": "InlineEnum" }
  ]
}

Replay that with targetFormat: "sql" and vendor: "mysql" and the vocabulary comes back as a real column constraint:

CREATE TABLE `sensor_reading` (
  `reading_id` BIGINT NOT NULL,
  `device_id` VARCHAR(64) NOT NULL,
  `taken_at` TIMESTAMP NOT NULL,
  `celsius` DOUBLE PRECISION,
  `quality` ENUM('raw', 'validated', 'rejected')
);

Replay the identical plan with vendor: "postgres" and the column becomes VARCHAR(255) plus one ledger entry — ConstraintRelaxation at Element[sensorReadingQuality] — because PostgreSQL DDL has no inline enum. Same plan, same source, two vendors, two honest answers.

One rule for editing plans: copy ids, never invent them. Source ids come from the SQL coder — table plus column, camelCased and stripped of separators, with the original names kept as labels. sensor_reading plus taken_at is sensorReadingTakenAt; a shouty FULL_NAME in Customer is CustomerFULLNAME, not CustomerFullName. An enum column's vocabulary id is the element id plus Enum. Take them from the plan the engine already returned.

The three mapping kinds as pipeline stages

Inferred is the first pass. It aligns by label and compatible type against a hint, with caseInsensitive defaulting to true — which matters for warehouse DDL more than most, where FULL_NAME should meet a canonical full_name without a shim. Its one structural surprise: the hint is indexed by label across the whole schema and the first match wins, so a column name repeated across tables (invoice_id, created_at, tenant_id) binds to whichever table declared it first. Any column of a mapped table that the plan does not cover comes back as a StructuralDrop naming the exact member — never dropped quietly.

Explicit is the reviewed, committed state. The guide is a small JSON vocabulary — autoMatchByMapsTo, fieldMappings, taxonomyDirectives, drops — and it is parsed strictly:

{
  "autoMatchByMapsTo": true,
  "fieldMappings": [
    {
      "sourceElementIds": ["CustomerFullName"],
      "targetElementIds": ["firstName", "lastName"],
      "transformName": "SplitName"
    }
  ],
  "taxonomyDirectives": [
    { "sourceTaxonomyId": "CustomerStatusEnum", "targetTaxonomyId": "CustomerStatusEnum", "treatment": "InlineEnum" }
  ],
  "drops": ["CustomerLegacyCode"]
}

Two traps are worth knowing before you write one. A typo is rejected, not ignored: guide.fieldMapings: Unknown mapping-guide key 'fieldMapings'. Known keys: autoMatchByMapsTo, fieldMappings, taxonomyDirectives, drops. — a silently ignored key would execute a plan you did not author. And fieldMappings is for the multi-field cases: a one-to-one entry with no transformName reaches the gate as an unnamed transform and is rejected with Field transform '' is not registered. The registry ships six named transforms — SplitName, JoinName, CoerceStringToDateTime, CoerceDateTimeToString, CoerceIntegerToDouble, CoerceDoubleToInteger — each declaring whether it loses information; the lossy ones (splitting a name, narrowing a double to an integer) add a SemanticNarrowing record to the ledger the moment a plan uses them.

AI is for drafting, never for running. mapping.kind = "ai" has a server-side Claude proposer draft the plan; the same gate validates it with at most one repair attempt, it requires Editor or Admin membership plus a server-configured key, and self-reported confidence is advisory only — the gate never trusts it. The pipeline pattern that follows: invoke ai once, interactively, on a gnarly source; review the plan; commit the plan; let CI replay it forever after. The model leaves the loop the moment the plan is written down.

Batch shape one: many DDL files, one canonical target

for f in ddl/*.sql; do
  name=$(basename "$f" .sql)
  jq -n --rawfile s "$f" --rawfile hint canonical/reading.schema.json '{
    sourceFormat: "sql", sourceSchema: $s,
    targetFormat: "jsonschema",
    targetHintFormat: "jsonschema", targetHintSchema: $hint,
    mapping: { kind: "inferred" }
  }' | curl -sS -X POST \
      "https://coremodels.example.com/graph/transform/schema/map/$PROJECT_ID" \
      -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
      --data-binary @- > "artifacts/$name.response.json"
  jq '.plan' "artifacts/$name.response.json" > "plans/$name.plan.json"
done

The loop does not care which database each file came from — backticks, brackets, IF NOT EXISTS, stripped comments, and ENUM columns all decode without a dialect declaration.

Batch shape two: one governed model, a vendor matrix

for v in postgres mysql sqlserver; do
  curl -sS -X POST \
    "https://coremodels.example.com/graph/transform/schema/export/$PROJECT_ID" \
    -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
    -d "{ \"format\": \"sql\", \"vendor\": \"$v\" }" \
  | jq -r '.schema' > "dist/model.$v.sql"
done

Three files that differ exactly where the dialects differ: quoting, boolean and date-time types, string types, and enums — which only the MySQL file can express inline.

Gate on the ledger, not just the exit code

A pipeline that ignores lossiness is automating away the one thing the engine refuses to hide. The cheap policy is a committed baseline:

# Once, at review time - approve the current ledger:
jq -S '[.lossiness[] | {kind, path}]' response.json > baselines/sensor_reading.json

# On every run - compare:
jq -S '[.lossiness[] | {kind, path}]' response.json > ledger.now.json
diff baselines/sensor_reading.json ledger.now.json \
  || { echo "lossiness changed - review before merging"; exit 1; }

A new ConstraintRelaxation on a column that used to be clean is exactly the change a human should approve, and this turns it into a failing check instead of a surprise.

Two neighboring routes finish the picture. schema/mapImport (Admin; dry-run needs Viewer) maps a SQL source onto a project's own schema and writes the result back — always dry-run first, read the summary and the plan. And data/map runs a gated plan's field transforms over real records, with sql available as a data format there too (INSERT statements), so a schema conversion and its record migration can share one reviewed artifact. Route details and full request bodies are in the transform API guide in the CoreModels docs.