Keep database schema docs in sync: comments in DDL, generated markdown, and a diff gate in CI

Every mainstream database supports comments as schema objects: COMMENT ON TABLE and COMMENT ON COLUMN in PostgreSQL, COMMENT clauses in MySQL and Snowflake DDL. A comment written in a migration travels with the schema through every environment, survives dumps and restores, and sits exactly where the next engineer looks when they wonder what orders.status means.

This beats documenting in the application layer for one structural reason: ORM-level descriptions, like help text on a model field, are visible only to that codebase. A column comment is visible to every client of the database: the BI tool, the analyst in a SQL editor, the catalog crawler, the DBA at 2am. Write the description once, in the migration that creates the column, and review it in the same pull request.

Comments cover the column level; people also need per-table pages with relationships and indexes. Generate those instead of writing them. tbls is the reference tool for this pattern: point it at a connection string and it writes a markdown document per table into a directory in your repo, including columns, types, comments, constraints, indexes and inferred relationships, driven by a .tbls.yml config. The command is a single line, tbls doc, and the output is plain markdown that renders in GitHub and every docs site.

Because the docs are generated from the database, they are correct at generation time by construction. The remaining question is keeping them regenerated, which is a CI problem, not a writing problem.

tbls diff compares the committed docs directory against the live schema and exits nonzero when they disagree. Run it in CI after migrations apply and the schema can no longer change without the docs changing in the same pull request: the build fails until someone reruns tbls doc and commits the result. tbls lint goes a step further with rules like requiring a comment on every table and column, which turns the undocumented-column problem into a failing check instead of a quarterly cleanup.

This is the same ratchet logic that works for code quality: the gate does not demand a backfill, it just refuses to let the gap grow.

For the backfill itself, ask the catalog. In PostgreSQL, comments live in pg_description, so a three-way join lists every column in a schema with no description attached. In MySQL the equivalent is one line, since information_schema.columns carries a COLUMN_COMMENT field you can filter on empty. Sort the result by table importance and you have a documentation debt list that is exact rather than estimated.

-- Documentation that travels with the migration
COMMENT ON TABLE orders IS 'One row per checkout attempt, including abandoned carts';
COMMENT ON COLUMN orders.status IS 'pending | paid | refunded | cancelled';

-- Every undocumented column in a PostgreSQL schema
SELECT c.relname AS table_name, a.attname AS column_name
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_description d
  ON d.objoid = a.attrelid AND d.objsubid = a.attnum
WHERE n.nspname = 'public'
  AND c.relkind = 'r'
  AND a.attnum > 0
  AND NOT a.attisdropped
  AND d.description IS NULL
ORDER BY 1, 2;

Everything above keeps the written record current: comments, per-table markdown, the CI gate. The entity-relationship diagram is a different artifact with a different audience, the picture people open to understand how the tables relate before they read any of them, and it has its own keep-current loop covered in keep ER diagrams in sync with the database.

The short version: generate the first diagram by pasting your DDL into the database schema diagram generator, then regenerate on migration merges, either by hand or through an agent connected to the Datadef MCP server that reads the changed DDL, as described in living diagram from SQL DDL. A diagram in a project shared public serves a permanent image URL that follows edits within minutes, so the copy in your README stays the current one. Datadef does not watch the repository, and the live embed requires that public sharing; for a confidential schema, keep the project private and export instead.

• Keep ER diagrams in sync with the database
• Living diagram from SQL DDL
• Database schema diagram generator
• Keep a data dictionary in sync with the schema