Teaching an Agent to Read DDL: the transform_schema MCP Tool

A partner attaches subscriber_dump.sql to a ticket. Backticks, an ENUM, a DATETIME, and one column named by a DBA who shouts. Your canonical model is a JSON Schema in a repo. Somebody has to reconcile the two, and historically that somebody spent an afternoon in a diff viewer.

With a CoreModels MCP connection, an agent does it in one tool call and gets back three things at once: the converted schema, the exact plan that produced it, and an itemized ledger of what could not survive the trip. This article is the operating manual for that tool with SQL DDL as the source — the arguments, a scenario that runs, and the three failures an agent should be taught to recognize by name.

Connecting

The MCP server speaks stateless streamable HTTP with OAuth 2.0. From Claude Code:

claude mcp add --transport http coremodels https://coremodels.example.com/mcp

Then run /mcp in the session to complete the OAuth flow. Any JSON-configured client works the same way:

{
  "mcpServers": {
    "coremodels": { "type": "http", "url": "https://coremodels.example.com/mcp" }
  }
}

transform_schema is read-only, so the public /mcp endpoint serves it. Nothing in this article needs the admin endpoint.

The contract

The tool is stateless. It decodes the source format, produces a mapping plan (an explicit guide, label-and-type inference, or an AI proposal that is gated exactly like any other plan), validates the plan through one universal gate, executes deterministically, and encodes into the target format. Nothing is read from or written to the project — graphProjectId scopes authorization only.

Four arguments are required: graphProjectId, sourceFormat, sourceSchema, targetFormat. The complete surface:

Argument	Meaning
graphProjectId	32 hex characters (^[a-f0-9]{32}$)
sourceFormat	jsonschema | shex | avro | jsonld | sql | osi | osi-json | owl | linkml | protobuf | odcs | odm
sourceSchema	the source schema text
targetFormat	jsonschema | shex | avro | jsonld | sql | osi | osi-json | owl | linkml | protobuf | odcs | synapse
vendor	for sql output: postgres (default), mysql, sqlserver
targetHintFormat / targetHintSchema	the schema to map toward; required for inferred mapping
mappingKind	inferred (default), explicit, ai
guide	for explicit, the SIA mapping-guide JSON as text; for ai, optional free-text guidance
caseInsensitive	inferred mapping: ignore label case (default true)
synapseOrg / synapseName / synapseVersion	Synapse output only; irrelevant to SQL

The input schema sets additionalProperties: false, so a misspelled argument is rejected rather than ignored.

Call one: the partner's dump toward your canonical shape

{
  "graphProjectId": "0f3c9a1b2d4e5f60718293a4b5c6d7e8",
  "sourceFormat": "sql",
  "sourceSchema": "CREATE TABLE `subscriber` (\n  `subscriber_id` INT NOT NULL,\n  `FULL_NAME` VARCHAR(255) NOT NULL,\n  `tier` ENUM('free','pro','enterprise'),\n  `joined_on` DATETIME\n);",
  "targetFormat": "linkml",
  "targetHintFormat": "jsonschema",
  "targetHintSchema": "{ \"$id\": \"Subscriber\", \"type\": \"object\", \"title\": \"Subscriber\", \"properties\": { \"subscriber_id\": { \"type\": \"integer\" }, \"full_name\": { \"type\": \"string\" } } }",
  "mappingKind": "inferred",
  "caseInsensitive": true
}

Note what the agent did not have to do. It did not declare a dialect — decode is dialect-tolerant and reads backticks, ENUM, and DATETIME as found. It did not normalize FULL_NAME — inferred matching ignores label case by default, so the column aligns with the hint's full_name. It did not describe the structure: the table becomes a type, INT NOT NULL becomes a required integer element, DATETIME becomes a date-time element, and the ENUM becomes a three-term governed vocabulary.

The result is one JSON document with three fields — schema, plan, lossiness:

id: https://coremodels.example.com/ns/sqlschema
name: sql-schema
prefixes:
  linkml: https://w3id.org/linkml/
imports:
  - linkml:types
default_range: string

classes:
  Subscriber:
    attributes:
      subscriber_id:
        range: integer
        required: true
      FULL_NAME:
        required: true

And the ledger:

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

This is the moment that separates a useful agent from a confident one. The canonical hint named only two properties, so tier and joined_on matched nothing — and rather than vanishing, each was reported at its exact path. The class took the hint's name (Subscriber) while the mapped attributes kept the source's labels. A well-prompted agent reports that back verbatim: converted two of four columns; tier and joined_on have no home in the canonical shape — extend the hint or drop them deliberately. It does not say "done."

plan is the executed mapping, operation by operation. An agent that stores it next to the output has an auditable record of how the conversion was derived, replayable later through the HTTP plan/execute route without re-running inference.

Call two: back out as DDL

Reverse the direction and the SQL-specific argument comes into play. Here the canonical JSON Schema becomes SQL Server DDL for a provisioning script. Inference still needs something to map toward, so the source doubles as its own hint:

{
  "graphProjectId": "0f3c9a1b2d4e5f60718293a4b5c6d7e8",
  "sourceFormat": "jsonschema",
  "sourceSchema": "{ \"$id\": \"Subscriber\", \"type\": \"object\", \"title\": \"Subscriber\", \"properties\": { \"subscriber_id\": { \"type\": \"integer\" }, \"full_name\": { \"type\": \"string\" }, \"active\": { \"type\": \"boolean\" } }, \"required\": [\"subscriber_id\"] }",
  "targetFormat": "sql",
  "vendor": "sqlserver",
  "targetHintFormat": "jsonschema",
  "targetHintSchema": "{ \"$id\": \"Subscriber\", \"type\": \"object\", \"title\": \"Subscriber\", \"properties\": { \"subscriber_id\": { \"type\": \"integer\" }, \"full_name\": { \"type\": \"string\" }, \"active\": { \"type\": \"boolean\" } }, \"required\": [\"subscriber_id\"] }",
  "mappingKind": "inferred"
}

CREATE TABLE [Subscriber] (
  [subscriber_id] INTEGER NOT NULL,
  [full_name] NVARCHAR(255),
  [active] BIT
);

Change vendor to postgres and the same call emits "Subscriber", VARCHAR(255), and BOOLEAN; mysql gives backticks and TINYINT(1). The vendor selector governs quoting, type defaults, comment placement, and whether a governed vocabulary can be expressed as an inline ENUM(...) — MySQL is the only output where it can; the others emit VARCHAR(255) and add a ConstraintRelaxation record saying the allowed-value constraint is not enforced. The agent does not need to know any of that in advance, because the ledger reports it per call.

Three failures worth naming

Errors come back with the stage in the message, so an agent can say where a conversion died instead of retrying blindly.

No hint. Leave targetHintFormat/targetHintSchema out with the default mapping kind and you get Could not produce a mapping plan: inference: The inference resolver requires a target IR to match against. The fix is one line: pass the source as its own hint for a straight format conversion, or the canonical schema when you mean to align to it.

A mapped ENUM column. If the hint does cover an enum column, inference maps the column but has no way to match the vocabulary behind it — SQL enums arrive unnamed — and execution stops with Execution failed: target:Element[Subscriber::tier].ValueType: Referenced taxonomy 'subscriberTierEnum' does not resolve. Two honest routes around it: leave the enum column out of the hint and accept the reported StructuralDrop (call one did exactly that), or move the table through a project with schema/import and schema/export, which do not run the mapping engine and carry enums end to end.

mappingKind: "ai". The AI proposer needs a server-configured Anthropic key and Editor or Admin membership on the scoping project, because it spends the server's paid model budget; without membership the tool answers mappingKind=ai requires Editor or Admin project membership (it invokes the server-side Anthropic API). It also sends the schema content to the Anthropic API server-side, which is why the tool declares an open-world hint. Whatever the model proposes goes through the same gate as every other plan, with at most one repair attempt — a rejected repair is a rejection, and any self-reported confidence is advisory only.

Malformed input fails earlier and just as plainly: Could not decode the source schema: $: No CREATE TABLE statement was found.

The companion tool

When the source is a governed project rather than a DDL string, export_sql covers that direction: graphProjectId, an optional spaceId, and optional nodeIds (specific type nodes; omit for all), returning the project's model as CREATE TABLE text. It has no vendor selector — reach for transform_schema when the dialect matters.

Put one rule in the system prompt: never report a transform as clean without reading lossiness. The tool is built so honesty is machine-readable; the agent's only job is to pass it on. Connection details, OAuth setup, and the full tool inventory are in the CoreModels MCP quickstart in the docs.