How to Document Your Database Schema for a Team

The short version

Document your schema in three layers:
(1) name things well so they're self-documenting,
(2) add inline column comments for the non-obvious stuff,
(3) generate a one-page visual diagram that shows the relationships. Skip the 40-page Confluence page nobody reads it.

Layer 1: Name things well

The fastest documentation is a good name. If your column names are clear, you barely need comments.

Tables

• Use plural nouns: users, orders, order_items.
• Use snake_case: order_items, not OrderItems.
• Be specific: payment_methods, not methods.

Columns

• Use created_at and updated_at for timestamps.
• Use {table}_id for foreign keys: user_id, not userId or user.
• Prefix booleans with is_ or has_: is_active, has_paid.
• Avoid abbreviations: customer_id, not cust_id.

Foreign keys

• Name them after the parent table: orders.user_id → users.id.
• Don't use generic names like ref_id or parent_id (unless it's a tree structure).

Layer 2: Inline comments

PostgreSQL supports column-level comments. Use them for anything that isn't obvious from the name:

COMMENT ON COLUMN orders.status IS
  'pending | confirmed | shipped | delivered | cancelled. Never delete orders set status to cancelled instead.';

COMMENT ON COLUMN users.phone_number IS
  'E.164 format (+1234567890). Required for SMS auth. May be null if user signed up via OAuth.';

COMMENT ON COLUMN products.sku IS
  'Stock Keeping Unit. Format: CATEGORY-NNNN (e.g., ELEC-0042). Must be unique across all products.';

What to comment

• Enum values: What are the possible values for status?
• Format requirements: What format does phone_number expect?
• Business rules: Why is email nullable? (OAuth users may not share it.)
• Deletion policy: Do you delete rows or soft-delete?

What NOT to comment

• id - everyone knows what id is.
• created_at / updated_at - self-explanatory.
• Foreign keys named after their parent table - user_id is obvious.

Layer 3: Visual diagram

A one-page ER diagram replaces 40 pages of documentation. Generate it automatically:

1. dbdiagramr - paste your connection string, get a visual schema in seconds. No signup required.
2. pgAdmin - built-in ERD tool for PostgreSQL.
3. DBeaver - database tool with auto-generated ER diagrams.

What to show

• Table names and primary keys.
• Foreign key relationships (the lines between tables).
• Column types for non-obvious fields (JSONB, arrays, enums).

What to skip

• Every single column - just show the important ones.
• Indexes - they're implementation details.
• Default values - put those in comments instead.

The one-page cheat sheet

Create a single markdown file in your repo called SCHEMA.md:

# Database Schema

## Overview
- 12 tables, 3 schemas (public, auth, storage)
- Last updated: 2026-09-01

## Tables

### users
Core user table. Created by Supabase Auth.
- `id` (uuid, PK) - Auth user ID
- `email` (text) - May be null for phone auth
- `raw_user_meta_data` (jsonb) - Profile data from OAuth

### orders
Customer orders. Never delete - use status instead.
- `id` (uuid, PK)
- `user_id` (uuid, FK → users) - Customer
- `status` (text) - pending | confirmed | shipped | delivered | cancelled
- `total_amount_cents` (integer) - Price in cents to avoid float rounding

...

ER Diagram

Naming conventions by team size

FAQ

Should I document every table?

No. Document the tables that matter: the ones new engineers will touch, the ones with complex business logic, and the ones with non-obvious schemas. Skip internal Django/Laravel/NextAuth tables framework docs cover those.

How often should I update the docs?

When you add or change a column. The best way to enforce this: put the SCHEMA.md update in your PR template as a checklist item.

What's the best tool for auto-generating schema docs?

dbdiagramr for visual diagrams. For text-based docs, use dbt's docs generate if you're already on dbt. For everything else, a markdown file in your repo beats any SaaS tool.

Can I generate schema docs from migration files?

Partially. Migration files show the changes, not the current state. You'd need to run pg_dump --schema-only or query information_schema to get the actual current schema, then generate docs from that.