Parse SQL Migrations Into a Column Reference, Then Sign Retention and PII Outside the Model

A data dictionary compiled from reviewed SQL migrations is more trustworthy than a chat-written schema overview. Every column row can be traced to a migration file and a content checksum before publish. The model may only restate comments and check constraints that already exist in those files. Retention windows, PII class, and rollback notes stay in human-owned blocks that fail the build if unsigned.

Why schema prose drifts away from CREATE TABLE

Teams often paste a schema dump into a prompt and request friendly documentation for onboarding. The output usually invents purpose, mixes environments, and hides nullability changes from older migrations. Reviewers then argue with fluent prose instead of arguing with the CREATE TABLE that actually shipped. A compiler that reads migrations restores one mechanical source, and a signed file carries claims no parser should invent.

That split is the entire method, and it should be enforced in continuous integration rather than in review comments. Generated rows describe names, types, nullability, defaults, and comments that already live in the repository. Signed rows describe retention, PII classification, encryption expectations, and rollback procedure at the column grain. Mixing those lanes is how a fluent paragraph becomes an unverifiable statement about customer data.

Decision table: draftable fields versus signed claims

Use this table as the contract for the pipeline, not as a style guide for writers. If a field is absent from the compiler column, a model must not emit it in nearby prose either.

Field	Evidence source	Writer	Publish rule
table, column, type, nullability	CREATE / ALTER in db/migrations/	compiler	fail when HEAD schema lacks the row
default, CHECK, SQL comment	same SQL files	compiler; optional restatement	restatement must cite the comment digest
example SELECT	synthetic literals only	compiler or model	reject real-looking emails, tokens, or primary keys
retention window	policy owners	human	require column_risk.yaml signature
PII class	security or legal review	human	require signature; never infer from names
rollback notes	on-call owners	human	require signature
“safe to log”	security	human	model-authored text is a hard fail

The table is deliberately boring, because boring fields are the ones a parser can prove. Exciting fields are the ones that create incidents when a model fills them from naming conventions.

Step 1 — Inventory migrations in a stable order

1. Keep one directory of forward migrations with a sortable prefix, such as db/migrations/20260916_143000_add_users_email.sql.
2. Forbid out-of-band CREATE TABLE in application boot paths, because undocumented DDL cannot enter the dictionary.
3. Record the git blob hash of each migration file in the generated facts document, so reviewers can see which SQL produced which row.
4. Treat squash or rebase of already-shipped migrations as a dictionary incident, not as a cleanup chore.

A worked listing command looks like the following, and it should run in CI before any model call. Label this as an example workflow rather than a measured production benchmark.

# Example: stable inventory used as compiler input
find db/migrations -name '*.sql' | sort | sha256sum > docs/_generated/migrations.sha256
git ls-files -s db/migrations > docs/_generated/migrations.index

If the index changes without a corresponding facts-file change, the later gate must fail. That pairing is what stops a “docs only” commit from describing a table that no longer exists.

Step 2 — Parse CREATE and ALTER into a facts file

The parser below is a limited example for simple PostgreSQL-like CREATE TABLE blocks. It is not a substitute for a real SQL engine, and dialect-specific types will need extra rules. Keep the output as JSON so Markdown rendering cannot hide missing fields.

# Example compiler (not a full SQL parser).
# Reads db/migrations/*.sql and writes docs/_generated/columns.json.
from __future__ import annotations

import hashlib, json, re
from pathlib import Path

CREATE_RE = re.compile(
    r"CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?([a-zA-Z0-9_.]+)\s*\((.*?)\)\s*;",
    re.I | re.S,
)
COL_RE = re.compile(
    r"^\s*([a-zA-Z0-9_]+)\s+([a-zA-Z0-9_(),\s]+?)(?:\s+DEFAULT\s+([^,]+))?"
    r"(.*?)$",
    re.I,
)

def digest(text: str) -> str:
    return hashlib.sha256(text.encode()).hexdigest()[:16]

def parse_file(path: Path) -> list[dict]:
    sql = path.read_text()
    rows = []
    for table, body in CREATE_RE.findall(sql):
        for raw in body.split(","):
            line = " ".join(raw.split())
            if not line or line.upper().startswith(("PRIMARY", "UNIQUE", "CONSTRAINT", "CHECK", "FOREIGN")):
                continue
            m = COL_RE.match(line)
            if not m:
                continue
            name, coltype, default, tail = m.groups()
            comment_m = re.search(r"--\s*(.+)$", raw)
            comment = comment_m.group(1).strip() if comment_m else ""
            rows.append({
                "table": table.lower(),
                "column": name.lower(),
                "type": " ".join(coltype.split()),
                "nullable": "NOT NULL" not in tail.upper(),
                "default": (default or "").strip() or None,
                "sql_comment": comment,
                "comment_digest": digest(comment) if comment else None,
                "source": str(path),
                "source_digest": digest(sql),
            })
    return rows

def main() -> None:
    mig = sorted(Path("db/migrations").glob("*.sql"))
    facts = [row for path in mig for row in parse_file(path)]
    out = Path("docs/_generated/columns.json")
    out.parent.mkdir(parents=True, exist_ok=True)
    out.write_text(json.dumps(facts, indent=2, sort_keys=True) + "\n")

if __name__ == "__main__":
    main()

Run the compiler on every change to db/migrations/**. Do not let a chat session invent column types after the JSON exists, because that reintroduces the drift this pipeline is designed to remove.

Step 3 — Keep risk claims in a separate signed YAML file

Human-owned fields live in docs/_signed/column_risk.yaml. The generated JSON never receives those keys, so a model that only sees the facts file cannot “complete” a retention sentence by accident.

# Human-owned. CI fails if a generated column lacks a non-pending record on main.
users.email:
  pii: direct-identifier
  retention: account-lifetime-plus-30d
  rollback: dropping this column breaks login; dual-read for one release
  safe_to_log: false
  signed_by: schema-owners
  signed_at: "2026-09-16"
users.created_at:
  pii: none
  retention: account-lifetime-plus-30d
  rollback: safe to re-add as timestamptz default now()
  safe_to_log: true
  signed_by: schema-owners
  signed_at: "2026-09-16"

Pending rows are allowed on feature branches so design can proceed. They are not allowed on main, because an unsigned PII class is equivalent to an unpublished dictionary. Stale keys that name dropped columns must also fail, or the signed file becomes fan fiction about deleted data.

Step 4 — Optional restatement of comments that already exist

Plain-language restatements are useful when SQL comments are terse. They are not useful when no comment exists, because the model then guesses business meaning from the identifier. Feed the model only table, column, type, and sql_comment, and require the comment digest in the output object.

When that restatement step needs a model, MonkeyCode's free model access and free server option can host the isolated job without mixing it into application runtimes. Disclosure: This article was prepared as part of MonkeyCode's product outreach. The parser still runs as ordinary Python, and the model only receives comments that already exist in SQL. Do not send live row samples, connection strings, or production dumps to that job.

# Example request payload. Do not add sample rows or secrets.
payload = {
    "task": "restate_sql_comment",
    "column": {
        "table": row["table"],
        "column": row["column"],
        "type": row["type"],
        "sql_comment": row["sql_comment"],
        "comment_digest": row["comment_digest"],
    },
    "rules": [
        "If sql_comment is empty, return restatement=null.",
        "Do not add retention, PII, encryption, or logging claims.",
        "Echo comment_digest unchanged.",
    ],
}

A restatement that cannot echo the digest is discarded. A restatement that introduces words such as PII, GDPR, retain, or log is discarded even when the digest matches, because those claims belong in the signed file.

Step 5 — Render Markdown with locked human regions

Render a page that makes the two lanes visible to reviewers. Generated tables can be overwritten on every build. Human sections are included by reference and must not be rewritten by the renderer.

<!-- generated:columns -->
| Table | Column | Type | Null | Default | Comment restatement |
| --- | --- | --- | --- | --- | --- |
| users | email | text | no | | Login identifier stored as text |
<!-- /generated:columns -->

## Risk register (human-owned, do not generate)

See `docs/_signed/column_risk.yaml`. Retention, PII, logging, and rollback are not restated here by a model.

Numbered publish checks belong in the same job that renders the page:

1. Every facts-file column has a signed record whose status is not pending on main.
2. Every signed key maps to a facts-file column still present in HEAD migrations.
3. Generated Markdown between the generated:columns markers matches a hash of columns.json.
4. Human regions contain no model fence, and a simple term scan rejects PII language inside generated cells.

# Example gate fragment.
FORBIDDEN_IN_GENERATED = ("pii", "retain", "gdpr", "hipaa", "safe to log")

def assert_generated_is_mechanical(cells: list[str]) -> None:
    blob = " ".join(cells).lower()
    for term in FORBIDDEN_IN_GENERATED:
        if term in blob:
            raise SystemExit(f"generated lane contains signed-claim term: {term}")

Step 6 — Wire the compiler and gate into CI

Keep the make target boring so the policy is obvious in the pull request.

.PHONY: docs-dictionary
docs-dictionary:
    python tools/parse_migrations.py
    python tools/restate_comments.py --only-if-comment
    python tools/render_dictionary.py
    python tools/gate_column_risk.py --branch-policy

Fail the job on the first broken invariant rather than collecting a long essay of warnings. Reviewers should read the signed YAML diff, not a regenerated paragraph that hid a nullability change inside friendlier wording.

Limitations

The sample parser understands a narrow slice of CREATE TABLE syntax and will miss many ALTER TABLE forms. Teams with heavy ORM-generated SQL, partitioned tables, or vendor-specific types need a dialect-aware extractor before this dictionary is trustworthy. SQL comments can be stale even when the compiler copies them faithfully, so restatement quality cannot exceed comment quality.

The forbidden-term scan is a seatbelt, not a privacy program. It will not catch clever paraphrases, and it will not classify PII. Free-model restatement can still be wrong about units, time zones, or identifier formats; the digest check only proves the comment was in the prompt. None of this replaces a data-retention schedule that legal and security have actually approved.

Who should not use this approach

Skip this pipeline if the repository has no ordered migrations and the live database is the only schema. Skip it if the goal is to have a model infer PII from column names such as email or ssn. Skip it if counsel must author the generated lane, because a compiler cannot carry a legal signature. Skip it if example queries must use production-shaped fixtures, because those fixtures do not belong in a model prompt or a public dictionary.

The method is for teams that already review SQL and need onboarding docs to stop lying about that SQL. It is not a shortcut around schema review, and it is not a compliance control by itself.

If compile jobs already run off laptops, the free-server option is a reasonable place to execute this parser and restatement pair together.