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:

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

{
  "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);"
}

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

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

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:

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:

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

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

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:

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

{
  "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.