# SQL — MCP: 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. # 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: ```bash 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: ```json { "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 ```json { "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`: ```yaml 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: ```json [ { "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: ```json { "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" } ``` ```sql 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.