Why Schema Diagrams Go Stale (and the Fix)

The short version

Schema diagrams go stale because they're static snapshots of a moving target. A migration adds a column, someone renames a table, and suddenly your beautiful diagram is wrong. The fix: generate diagrams from the live database, not from a file. Run it on every deploy or PR.

Why it happens

Reason 1: Migrations don't update diagrams

You run ALTER TABLE orders ADD COLUMN shipping_cost_cents integer; and the migration succeeds. But your ER diagram still shows the old schema. Nobody thinks to update the diagram because the migration "just works."

Reason 2: Diagrams live in the wrong place

If your diagram is in a Confluence page, a Figma file, or a static image in your repo, it's disconnected from the code. The code changes, the diagram doesn't.

Reason 3: Nobody owns it

Diagram maintenance falls between "frontend" and "backend" and "DevOps." Everyone assumes someone else will update it. Nobody does.

The three fixes

Fix 1: Generate from the live database

Instead of maintaining a static diagram, generate it from the actual database state:

# Using dbdiagramr CLI (or paste your connection string at dbdiagramr.space)
# Your schema is always current -- no manual updates needed.

This is the nuclear option. Your diagram is always 100% accurate because it's generated from the source of truth.

Fix 2: Generate in CI/CD

Add a schema diagram step to your CI pipeline:

# GitHub Actions example
- name: Generate schema diagram
  run: |
    pg_dump --schema-only $DATABASE_URL > schema.sql
    # Generate diagram from schema.sql
    # Upload as artifact or commit to repo

Run it on every push to main. The diagram is always one commit behind, but it's close enough.

Fix 3: One-page SCHEMA.md

Maintain a single markdown file with the schema overview. Update it in the same PR that adds or changes a column:

# SCHEMA.md

## orders
- `id` (uuid, PK)
- `user_id` (uuid, FK → users)
- `status` (text) -- pending | confirmed | shipped | delivered
- `total_amount_cents` (integer) -- added 2026-09-15
- `shipping_cost_cents` (integer) -- added 2026-09-20

Put the update in your PR checklist:

## PR checklist
- [ ] Tests pass
- [ ] SCHEMA.md updated (if schema changed)
- [ ] Migration is reversible

What doesn't work

Manual updates to Figma/Confluence

Nobody does it. The diagram is always 3 months out of date.

Screenshots of pgAdmin

The moment you take the screenshot, it's stale. And you can't diff a screenshot.

Auto-generated docs from migration files

Migration files show the history of changes, not the current state. You'd need to replay all migrations to get the current schema, which is slow and error-prone.

The self-healing diagram

The ideal setup:

1. Source of truth: the live database.
2. Diagram generation: runs on every deploy (or on-demand).
3. Storage: committed to the repo as an SVG or markdown.
4. Enforcement: CI fails if the diagram doesn't match the schema.

This is overkill for most projects. But if you've ever debugging a "why does the diagram show a column that doesn't exist?" issue, it's worth it.

FAQ

How often should I update my schema diagram?

Every time you add, remove, or rename a column. If you're using a tool like dbdiagramr that generates from the live database, it's always current.

Should I commit the diagram to git?

Yes. Commit it as an SVG or markdown file. That way it's versioned with your code and you can see when it changed.

What's the minimum viable schema documentation?

A one-page SCHEMA.md file in your repo with table names, key columns, and relationships. Update it in the same PR that changes the schema.

Can I auto-generate schema docs from Prisma/Drizzle schema files?

Partially. Prisma's prisma-docs-generator and Drizzle's drizzle-kit generate can create basic docs. But they miss business rules, enum values, and deletion policies. Use them as a starting point, not the final doc.