# SQL — Automation: 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.

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

```bash
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**:

```bash
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:

```json
{
  "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:

```sql
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:

```json
{
  "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

```bash
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

```bash
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:

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