Naming & Documentation Standards

Consistent naming and in-database documentation reduce onboarding time and prevent incompatible migrations across teams. ER diagrams in git stay versioned with the schema they describe.

Recipe

Quick-reference recipe card - copy-paste ready.

COMMENT ON TABLE orders IS 'Customer purchase header; partitioned by created_at quarterly.';
COMMENT ON COLUMN orders.status IS 'open|paid|shipped|closed; see orders_status_enum.';
 
COMMENT ON COLUMN orders.legacy_total IS
  'DEPRECATED 2026-03: use total_cents. Drop after release 4.2.';

# Export schema diagram in CI (example: schemaspy, dbml-cli)
dbml2sql schema.dbml -o docs/erd/orders.dbml.sql
git add docs/erd/orders.dbml

When to reach for this: New service bootstrap, acquisition integration, or council mandate for doc coverage.

Working Example

New subscriptions table ships with comments and ERD PR.

CREATE TABLE subscriptions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  plan_code text NOT NULL,
  started_at timestamptz NOT NULL DEFAULT now(),
  ends_at timestamptz
);
 
COMMENT ON TABLE subscriptions IS 'Active billing subscriptions per tenant.';
COMMENT ON COLUMN subscriptions.plan_code IS 'FK to plans.code; not uuid for human support tools.';

// docs/erd/billing.dbml
Table subscriptions {
  id uuid [pk]
  tenant_id uuid [ref: > tenants.id]
  plan_code text
}

What this demonstrates:

Comments explain why, not only what column type is.
Deprecation dates in COMMENT ON COLUMN for contract phase tracking.
DBML (or similar) in git for reviewable ERD diffs.

Deep Dive

Naming Rules

Object	Convention	Example
Table	plural snake_case	`invoice_line_items`
Column	snake_case	`created_at`
Index	`{table}_{columns}_{suffix}`	`orders_tenant_created_idx`
Constraint	`{table}_{desc}_{type}`	`orders_total_positive_chk`
Enum type	`{domain}_{field}_enum`	`order_status_enum`

Avoid abbreviations except id, url, uuid.

Required Documentation

Every public table: COMMENT ON TABLE.
Every non-obvious column: business meaning or enum reference.
FK relationships visible in ERD and enforced in DDL.
Breaking changes noted in comment before drop.

ERD in Git Workflow

1. Author updates schema.dbml in feature branch
2. PR shows ERD diff image from CI
3. Migration SQL generated or hand-synced to match
4. Merge order: ADR (if needed) → ERD → migration

Gotchas

Comments drift from reality - Nobody updates after rename. Fix: Migration PR must update comment in same commit.
ERD only in Lucidchart - Not in review. Fix: DBML/Mermaid in repo; link from README.
Hungarian notation - strCustomerName in SQL. Fix: Lint in SQL review checklist.
Generic names - data, value, info columns. Fix: Require domain term in column name.
Undocumented enums - App-only constants. Fix: Postgres ENUM or check constraint documented in comment.

Alternatives

Alternative	Use When	Don't Use When
Postgres ENUM	Stable closed set	Values change weekly
Check constraint	Flexible validation	Need type export to ORM
External data catalog	Enterprise metadata	Small team overhead
OpenAPI only	API-first service	DB is integration point

FAQs

Are comments visible to ORMs?

Some tools read them; primary audience is humans and data catalog exporters.

DBML vs pgModeler?

DBML diffs well in git; choose tool that exports text format.

Comment length limit?

PostgreSQL allows long strings; keep under 500 chars for readability.

Document views and MVs?

Yes; include refresh schedule in comment for materialized views.

Non-English column names?

English canonical in schema; localize in app layer.

Auto-generate comments from ORM?

Hand-written business meaning still required; codegen insufficient alone.

Sensitive column docs?

Comment "PII - subject to retention policy" without example values.

Who approves naming exceptions?

Database council with ADR for non-standard table names.

Schema diff in PR?

Use migra or atlas schema diff against staging snapshot.

What should I read next?

See PostgreSQL Rules Naming.

Governance Basics - program overview
Naming and Style Rules - team rules
ADR Template for Postgres - exceptions
Git for Database Work Basics - versioned schema

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Recipe

Quick-reference recipe card - copy-paste ready.

COMMENT ON TABLE orders IS 'Customer purchase header; partitioned by created_at quarterly.';
COMMENT ON COLUMN orders.status IS 'open|paid|shipped|closed; see orders_status_enum.';
 
COMMENT ON COLUMN orders.legacy_total IS
  'DEPRECATED 2026-03: use total_cents. Drop after release 4.2.';

# Export schema diagram in CI (example: schemaspy, dbml-cli)
dbml2sql schema.dbml -o docs/erd/orders.dbml.sql
git add docs/erd/orders.dbml

When to reach for this: New service bootstrap, acquisition integration, or council mandate for doc coverage.

Working Example

New subscriptions table ships with comments and ERD PR.

CREATE TABLE subscriptions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  plan_code text NOT NULL,
  started_at timestamptz NOT NULL DEFAULT now(),
  ends_at timestamptz
);
 
COMMENT ON TABLE subscriptions IS 'Active billing subscriptions per tenant.';
COMMENT ON COLUMN subscriptions.plan_code IS 'FK to plans.code; not uuid for human support tools.';

// docs/erd/billing.dbml
Table subscriptions {
  id uuid [pk]
  tenant_id uuid [ref: > tenants.id]
  plan_code text
}

What this demonstrates:

Comments explain why, not only what column type is.
Deprecation dates in COMMENT ON COLUMN for contract phase tracking.
DBML (or similar) in git for reviewable ERD diffs.

Deep Dive

Naming Rules

Object	Convention	Example
Table	plural snake_case	`invoice_line_items`
Column	snake_case	`created_at`
Index	`{table}_{columns}_{suffix}`	`orders_tenant_created_idx`
Constraint	`{table}_{desc}_{type}`	`orders_total_positive_chk`
Enum type	`{domain}_{field}_enum`	`order_status_enum`

Avoid abbreviations except id, url, uuid.

Required Documentation

Every public table: COMMENT ON TABLE.
Every non-obvious column: business meaning or enum reference.
FK relationships visible in ERD and enforced in DDL.
Breaking changes noted in comment before drop.

ERD in Git Workflow

1. Author updates schema.dbml in feature branch
2. PR shows ERD diff image from CI
3. Migration SQL generated or hand-synced to match
4. Merge order: ADR (if needed) → ERD → migration

Gotchas

Comments drift from reality - Nobody updates after rename. Fix: Migration PR must update comment in same commit.
ERD only in Lucidchart - Not in review. Fix: DBML/Mermaid in repo; link from README.
Hungarian notation - strCustomerName in SQL. Fix: Lint in SQL review checklist.
Generic names - data, value, info columns. Fix: Require domain term in column name.
Undocumented enums - App-only constants. Fix: Postgres ENUM or check constraint documented in comment.

Alternatives

Alternative	Use When	Don't Use When
Postgres ENUM	Stable closed set	Values change weekly
Check constraint	Flexible validation	Need type export to ORM
External data catalog	Enterprise metadata	Small team overhead
OpenAPI only	API-first service	DB is integration point

FAQs

Are comments visible to ORMs?

Some tools read them; primary audience is humans and data catalog exporters.

DBML vs pgModeler?

DBML diffs well in git; choose tool that exports text format.

Comment length limit?

PostgreSQL allows long strings; keep under 500 chars for readability.

Document views and MVs?

Yes; include refresh schedule in comment for materialized views.

Non-English column names?

English canonical in schema; localize in app layer.

Auto-generate comments from ORM?

Hand-written business meaning still required; codegen insufficient alone.

Sensitive column docs?

Comment "PII - subject to retention policy" without example values.

Who approves naming exceptions?

Database council with ADR for non-standard table names.

Schema diff in PR?

Use migra or atlas schema diff against staging snapshot.

What should I read next?

See PostgreSQL Rules Naming.

Governance Basics - program overview
Naming and Style Rules - team rules
ADR Template for Postgres - exceptions
Git for Database Work Basics - versioned schema

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Naming & Documentation Standards

Search Documentation

Naming & Documentation Standards