The Column Nobody Can Explain

A partner feed starts rejecting rows at two in the morning. The cause is one column: status. Someone runs a SELECT DISTINCT against production and gets six values back. The application's enumeration has five. The integration document the partner was handed lists four. The column is declared VARCHAR(32), so all six values are perfectly legal — the database was never told otherwise.

No one made a mistake you could point at. Every value in that column was written by code someone reviewed. The column simply never carried a rule, and the rule in three people's heads was never the same rule twice.

This is the problem SQL DDL support in CoreModels exists to solve, and it is worth stating precisely. It is not that relational databases are old or that DDL is badly designed. It is that the one artifact in your platform that cannot be out of date is also the one that cannot say what it means.

The only description that is guaranteed true

Wikis go stale. Entity-relationship diagrams describe the system somebody hoped to build. Data dictionaries confidently document a table that was dropped two quarters ago. CREATE TABLE statements are different in kind: they are enforced. Whatever the DDL says is, by construction, exactly what the database will accept tonight.

That makes DDL the most trustworthy schema artifact most organizations have, and it makes its silence expensive. DDL's vocabulary is storage, not meaning. VARCHAR(255) describes a byte budget; INTEGER describes an encoding; neither describes a concept. There is no standard place in DDL to record that a column is a person's name in the sense a public vocabulary defines it, or that these six values are the complete legal set, or that this field is the same field the finance system calls cust_nm.

The pieces of DDL that do carry business meaning are trapped. A foreign key encodes a genuine relationship — but only inside that engine. A CHECK constraint encodes a rule — and nothing downstream parses it. So meaning migrates out of the DDL into application code, tickets, spreadsheets, and memory, and the instant it leaves it starts drifting away from what the database is actually enforcing.

Three engines, three grammars

The second half of the problem is that "SQL DDL" is not one language.

Identifiers are quoted with double quotes in PostgreSQL, backticks in MySQL, square brackets in SQL Server. A boolean lands as BOOLEAN on one engine, TINYINT(1) on another, BIT on the third; a timestamp is spelled TIMESTAMP, DATETIME, or DATETIME2 by the same accident of vendor; text on SQL Server usually wants to be NVARCHAR. Small, dull differences — and every one of them load-bearing.

The consequence is that the one guaranteed-true artifact cannot be handed to the next system. It has to be re-typed — by a person, under a deadline, translating a type system they know into one they half know. Every consolidation and every acquisition pays this cost, and pays it again next time.

The re-derivation tax

The streaming team needs a record definition, so someone reads the DDL and rewrites it by hand in another format. A partner asks for a contract, so someone types it from the API documentation, which was itself typed from the DDL a year ago. Each transcription is a chance for three specific things to quietly disappear: the allowed-value list, the difference between "nullable" and "optional", and the relationship a foreign key was encoding.

What the tax really buys is not conversion errors. It buys unanswerable questions. Is this column required? What are the legal values? Which table does this actually point at? Every one of those should be a lookup. In most estates, each one is archaeology.

What we do with a CREATE TABLE

CoreModels treats SQL DDL as a first-class schema format — format key sql — and it works in both directions. You can import CREATE TABLE statements into a governed model, and you can export a governed model back out as DDL in the dialect you need.

The import is structural and faithful. A table becomes a Type. Each column becomes an Element. Integer families — including BIGINT, SMALLINT, and the serial types — become integers; DECIMAL, NUMERIC, FLOAT, REAL, and MONEY become doubles; BOOLEAN, BOOL, and BIT become booleans; the date and timestamp variants become genuine date-times; everything else — VARCHAR, TEXT, UUID, JSON — lands as a string. In every case the original SQL type text is carried alongside, so a stateless SQL-to-SQL conversion can reproduce it exactly rather than approximating it back (an export from a governed project commits to the target dialect's own type spellings instead, because a project stores the modeled type rather than the source text). NOT NULL becomes a required flag. A FOREIGN KEY, or an inline REFERENCES clause, becomes a real reference between Types — the relationship your DDL was quietly encoding all along. A MySQL ENUM column becomes a governed vocabulary: a named list of allowed values you can document, reuse, and hold other systems to.

The decoder is deliberately dialect-tolerant, because meeting DDL where it lives is the whole point. It accepts identifiers quoted in any of the three vendor styles, handles IF NOT EXISTS, strips line and block comments, takes schema-qualified table names in stride, and understands multi-word types like DOUBLE PRECISION and CHARACTER VARYING. Column names survive as labels exactly as written.

Where it cannot help, it says so

A governed model does not represent everything DDL can declare. A table-level PRIMARY KEY, UNIQUE, or CHECK clause has no home there — and it does not vanish quietly. Each comes back as a recorded constraint relaxation naming the table it came from, in a lossiness report attached to the response. That report is the review checklist; an empty one is a clean conversion.

Failure is equally plain. Paste an empty string and the import tells you the DDL is empty. Paste a SELECT statement and it tells you no CREATE TABLE was found — not a successful-looking response over an empty model.

The smallest loop worth running

Take the table with the column nobody can explain. Import its DDL. Turn that VARCHAR(32) into a governed vocabulary with the values you actually mean and a sentence about each. Export it back.

On MySQL the vocabulary returns as an inline ENUM list. On PostgreSQL it returns as VARCHAR, with a recorded note that the allowed-value constraint is not enforced there — which, read the other way, is the answer to why production accepted six values in the first place.

That loop takes minutes and runs on your schema rather than ours. For the exact request shapes and a step-by-step walkthrough, see the Schema Transformation guide in the CoreModels docs.