# SQL — API: Four Routes, One Envelope: SQL DDL on the CoreModels Transform API

| Route | Role | What it touches | |---|---|---| | `POST /graph/transform/schema/import/{projectId}` | Admin | writes the DDL's model into the project | | `POST /graph/transform/schema/export/{projectId}` | Viewer | reads the project, returns `CREATE TABLE` text | | `POST /graph/transform/schema/map/{projectId}` | Viewer (`ai`: Editor) | nothing - stateless, source in, target out | | `POST /graph/transform/plan/execute/{projectId}` | Viewer | nothing - replays a stored plan |

# Four Routes, One Envelope: SQL DDL on the CoreModels Transform API

| Route | Role | What it touches |
|---|---|---|
| `POST /graph/transform/schema/import/{projectId}` | Admin | writes the DDL's model into the project |
| `POST /graph/transform/schema/export/{projectId}` | Viewer | reads the project, returns `CREATE TABLE` text |
| `POST /graph/transform/schema/map/{projectId}` | Viewer (`ai`: Editor) | nothing - stateless, source in, target out |
| `POST /graph/transform/plan/execute/{projectId}` | Viewer | nothing - replays a stored plan |

That table is the whole SQL surface. Two routes move a model in and out of a CoreModels project; two run the mapping engine without touching the project at all. All four live under `https://coremodels.example.com/graph/transform/...` (no `api/` prefix), take `Authorization: Bearer $TOKEN` and `Content-Type: application/json`, and are scoped by a project id in the path.

The format key is `sql`, and it carries no direction caveat: it appears in both the decode list and the encode list, so it works as a source and as a target everywhere. Decode is dialect-tolerant - the parser reads PostgreSQL, MySQL, and SQL Server flavored DDL without being told which. Encode is dialect-specific - a `vendor` option picks the output. (For contrast, the same surface carries `odm`, which decodes only, and `synapse`, which encodes only and answers a decode attempt with `'synapse' is encode-only: a Synapse schema is plain draft-07 JSON Schema - decode it with the 'jsonschema' format.` No such limit applies to `sql`.)

Every response uses one envelope:

```json
{ "success": true, "lossiness": [], "errors": [], "schema": "..." }
```

`success: false` means it could not proceed and `errors` says why. `success: true` means it ran - read `lossiness` for what it cost. Each record has a `kind` (`StructuralDrop`, `TypeApproximation`, `ConstraintRelaxation`, `SemanticNarrowing`), a `path`, and an `explanation`.

## Import - DDL into a project

Admin role, because it writes. The body is the format key, the DDL as a JSON string, and an optional `spaces` array of target space ids (omit it for the project's main space). Save it as `import.json`:

```json
{
  "format": "sql",
  "schema": "CREATE TABLE Customer (\n  customer_key INTEGER NOT NULL,\n  full_name VARCHAR(255) NOT NULL COMMENT '{\"x-maps-to\":{\"schema.org\":\"https://schema.org/name\"}}',\n  signup_date TIMESTAMP,\n  loyalty_points INTEGER\n);"
}
```

```bash
curl -sS -X POST \
  "https://coremodels.example.com/graph/transform/schema/import/$PROJECT_ID" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  --data-binary @import.json
```

```json
{ "success": true, "lossiness": [], "errors": [], "projectId": "..." }
```

An empty ledger, and four elements in the project: `customer_key` (Integer), `full_name` (String), `signup_date` (DateTime), `loyalty_points` (Integer), with requiredness on the first two. The comment on `full_name` parsed as a JSON object carrying `x-maps-to`, so it became a formal semantic mapping to `https://schema.org/name` rather than a string in a comment.

Element labels keep your column names; internal node ids are camelCased (`CustomerCustomerKey`), because node ids must be alphanumeric. Exports are written from labels, so your naming survives the trip.

Failures arrive in the `errors` channel, not as HTTP errors: `Body must include 'format' and 'schema'.` for a malformed body, `The SQL DDL is empty.` for whitespace, `No CREATE TABLE statement was found.` for text that has none (a file of `SELECT` statements lands here), and `Unknown schema format 'sqlite'. Use: jsonschema | shex | avro | jsonld | sql | osi | osi-json | owl | linkml | protobuf | odcs | odm.` for a bad key. A missing or expired token fails earlier, at the authorization layer, with a 401.

Table-level `PRIMARY KEY`, `UNIQUE`, and `CHECK` clauses never stop an import; each is reported as a `ConstraintRelaxation` at `Type[<table>]`.

## Export - a project back to DDL

Viewer role. This is where `vendor` lives:

```bash
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": "postgres" }'
```

The response carries the DDL as a string under `schema`. For the project we just imported:

```sql
CREATE TABLE "Customer" (
  "customer_key" INTEGER NOT NULL,
  "full_name" VARCHAR(255) NOT NULL,
  "signup_date" TIMESTAMP,
  "loyalty_points" INTEGER
);

COMMENT ON COLUMN Customer.full_name IS '{"x-maps-to":{"schema.org":"https://schema.org/name"}}';
```

Requiredness came back as `NOT NULL`, and the schema.org mapping came back as a trailing `COMMENT ON COLUMN` statement - PostgreSQL's annotation slot. Accepted vendors are `postgres` (default), `mysql`, and `sqlserver` (alias `mssql`); an unrecognized value falls back to `postgres`, so spell it carefully.

Vendor changes four things. Quoting: `"x"`, `` `x` ``, `[x]`. Comment placement: inline `COMMENT '...'` on MySQL, trailing `COMMENT ON COLUMN table.column IS '...'` statements otherwise. Enum handling: an element backed by a taxonomy becomes a real `ENUM('a', 'b')` column only on MySQL - the other two emit `VARCHAR(255)` plus a `ConstraintRelaxation` saying the allowed-value constraint is not enforced. And type selection, for models that carry no original SQL spelling:

| IR primitive | postgres | mysql | sqlserver |
|---|---|---|---|
| Integer | `INTEGER` | `INTEGER` | `INTEGER` |
| Double | `NUMERIC` | `NUMERIC` | `DECIMAL(18,2)` |
| Boolean | `BOOLEAN` | `TINYINT(1)` | `BIT` |
| DateTime | `TIMESTAMP` | `DATETIME` | `DATETIME2` |
| String and everything else | `VARCHAR(255)` | `VARCHAR(255)` | `NVARCHAR(255)` |

A model that has lived in a project exports through this table, because a project stores the modeled type, not the source spelling. A model handed straight to the stateless routes as DDL keeps its verbatim spellings instead - the distinction that decides what a SQL-to-SQL conversion looks like on each route.

## Map - the stateless engine route

Viewer role (`mapping.kind` of `ai` requires Editor, because it spends the server's model budget). Nothing is read from or written to the project unless you set `useProjectAsTargetHint`, which reads the project's schema as the hint. Here are two related tables going to JSON Schema:

```json
{
  "sourceFormat": "sql",
  "sourceSchema": "CREATE TABLE invoice (\n  invoice_id INTEGER NOT NULL,\n  issued_on TIMESTAMP NOT NULL,\n  total_amount NUMERIC NOT NULL\n);\n\nCREATE TABLE invoice_line (\n  line_id INTEGER NOT NULL,\n  invoice_id INTEGER NOT NULL REFERENCES invoice(invoice_id),\n  sku VARCHAR(32) NOT NULL,\n  quantity INTEGER NOT NULL\n);",
  "targetFormat": "jsonschema",
  "targetHintFormat": "sql",
  "targetHintSchema": "CREATE TABLE invoice (\n  invoice_id INTEGER NOT NULL,\n  issued_on TIMESTAMP NOT NULL,\n  total_amount NUMERIC NOT NULL\n);\n\nCREATE TABLE invoice_line (\n  line_id INTEGER NOT NULL,\n  invoice_id INTEGER NOT NULL REFERENCES invoice(invoice_id),\n  sku VARCHAR(32) NOT NULL,\n  quantity INTEGER NOT NULL\n);",
  "mapping": { "kind": "inferred" }
}
```

The response adds `schema` (a JSON object for JSON-shaped targets, a string for text ones) and `plan` - the executed plan, reviewable and replayable. It also carries a ledger entry that is worth the whole article:

```json
{
  "kind": "StructuralDrop",
  "path": "Type[invoiceLine].invoiceLineInvoiceId",
  "explanation": "Element is a member of the mapped type but no operation maps or drops it."
}
```

Inference indexes the hint's elements **by label across the whole schema, first match wins**. Both tables have a column called `invoice_id`; the index bound that label to `invoice.invoice_id`, an integer, which is not compatible with the foreign-key reference on `invoice_line.invoice_id` - so no operation was emitted for that column, and the executor reported the omission instead of quietly shipping a `$defs` entry with a missing field. That is the failure mode to expect from inference over relational DDL, where repeated column names are the norm.

## Replay - the plan as the fix

The repair is an edit to the plan, not a flag on the request. Add the one `ElementMapping` that inference could not defend, and send the plan back through `plan/execute` (Viewer role). Note the plan travels as a **string**:

```bash
jq -n --rawfile s invoice.sql --rawfile p invoice.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, .lossiness'
```

The added operation:

```json
{
  "kind": "ElementMapping",
  "origin": "Explicit",
  "sourceElementIds": ["invoiceLineInvoiceId"],
  "targetElementIds": ["invoiceLineInvoiceId"]
}
```

With it, the ledger comes back empty and the foreign key appears in the output as a reference:

```json
{
  "line_id": { "type": "integer" },
  "invoice_id": { "$ref": "#/$defs/invoice" },
  "sku": { "type": "string" },
  "quantity": { "type": "integer" }
}
```

A stored plan earns no shortcut: it goes through the same validation gate as a freshly produced one, then executes deterministically - same plan plus same source produces the same output. A missing `plan` key answers `Body must include 'plan' (the plan JSON produced by a mapping call).`; a plan that references a construct your DDL no longer has fails at the gate with a path-carrying error, which is exactly the signal you want when someone edits the source out from under a committed conversion.

## The rest of the surface, briefly

`POST /graph/transform/schema/mapImport/{projectId}` (Admin; dry-run needs only Viewer) maps a SQL source onto the project's *own* schema as the target and writes the result back when `dryRun` is false - always dry-run first and read the returned `summary` counts and `plan`. The data routes take `sql` as a *data* format too, reading and writing `INSERT` statements alongside `json`, `csv`, `jsonld`, and `avro`.

The roles compress to one line: writing needs Admin, reading and stateless work need Viewer, and `ai` mapping needs Editor anywhere it appears. The habit compresses to one more: check `success`, then read `lossiness` - that is the difference between a conversion you ran and a conversion you understand. Ready-to-paste bodies for every other format are in the transform API guide in the CoreModels docs.