Database Documentation: Generating ERD and Schema Metadata

Previously in this course, we covered the Finalizing the SaaS Database Architecture: A Production Checklist. Now that our schema is production-ready, this lesson adds the "human layer"—creating the documentation required for team collaboration and long-term maintainability.

Why Documentation Matters for Database Design

When you’re building a SaaS product, your database is the single source of truth. However, raw SQL DDL is rarely sufficient for a new engineer trying to understand why a subscription_id exists in the users table or how the features junction table functions. Effective documentation prevents tribal knowledge and ensures that when your team grows, your schema remains accessible.

We focus on two pillars:

1. Visual Documentation (ERD): A high-level roadmap of your entities and relationships.
2. Metadata Documentation: Embedding descriptions directly into your database engine so they are always in sync with the live schema.

Generating ERD Documentation

As we discussed in our Introduction to ERD Notation: Visualizing Your Data Model, an Entity Relationship Diagram (ERD) is the primary tool for communicating design. While we drafted these by hand during the early phases, you should now transition to "live" ERDs generated from your schema.

Most modern tools (like DBeaver, pgAdmin, or dedicated modeling software) can reverse-engineer your SQL database into an ERD. This is superior to static diagrams because it updates automatically when you add a column or a constraint.

The "Living" Documentation Workflow

1. Reverse-Engineer: Point your database tool at your development instance.
2. Layout: Group related tables (e.g., Auth, Subscriptions, Audit) into logical areas of the canvas.
3. Export: Export as a PDF or SVG and store it in your repository’s /docs folder.

By keeping the ERD in your git repository, you create a history of your database evolution alongside your code.

Describing Table Relationships and Constraints

Beyond the diagram, your SQL code should explain itself. If a colleague asks, "What does the status column in subscriptions actually represent?" they shouldn't have to guess or search the codebase for the application logic.

As shown in Database Documentation Standards: Mastering SQL Comments, you can use native SQL commands to document your schema.

Worked Example: Self-Documenting SQL

In PostgreSQL, you can attach comments to tables and columns. This metadata is stored in the pg_description system catalog, making it accessible to any developer with access to the DB.

SQL
-- Documenting the table
COMMENT ON TABLE subscriptions IS 'Stores current and historical subscription data for accounts.';

-- Documenting columns
COMMENT ON COLUMN subscriptions.status IS 'The state of the sub: active, cancelled, or past_due.';
COMMENT ON COLUMN subscriptions.account_id IS 'Foreign key linking to the account (tenant).';

-- Documenting constraints
-- Note: While standard SQL doesn't have a direct "COMMENT ON CONSTRAINT" 
-- in every engine, we use descriptive naming conventions for constraints.
ALTER TABLE subscriptions 
ADD CONSTRAINT fk_subscriptions_account 
FOREIGN KEY (account_id) REFERENCES accounts(id);

Hands-on Exercise: Audit Your Schema

For your current SaaS project, perform the following two steps:

1. Generate your ERD: Use your database management tool to generate a diagram of your current tables. Ensure the users, accounts, and subscriptions relationships match the models we built in Mapping SaaS Users and Accounts: Entity Design for Scalability.
2. Add Metadata: Add COMMENT statements to at least three tables and five columns in your local database environment. Check that your IDE displays these comments when you hover over the columns.

Common Pitfalls

• Documentation Drift: The biggest risk is a beautiful diagram that doesn't match the actual database. Always make your ERD generation part of your deployment or development workflow.
• Over-Documenting: Don't document the obvious. COMMENT ON COLUMN users.email IS 'The email of the user' is noise. Document the intent (e.g., "Used for login and notifications, unique across the platform").
• Ignoring Constraints: Use descriptive names for your constraints (e.g., chk_positive_price instead of sys_c00123). This acts as built-in documentation for anyone viewing the DDL.

FAQ

Q: How often should I update the ERD? A: Every time you run a migration that changes the schema structure (adding tables or changing primary/foreign key relationships).

Q: Should I put documentation in the code or a Wiki? A: Both. The database-level comments stay with the data (useful for DBAs), while a README or Wiki is better for high-level architectural decisions and "why" we chose a specific approach.

Q: Can I automate the ERD generation? A: Yes. Look into tools like SchemaSpy or dbdocs.io which can automatically generate visual documentation from your live database schema, perfect for CI/CD pipelines.

Recap

Good documentation turns a "black box" database into a collaborative asset. By maintaining an up-to-date ERD and using internal SQL comments to clarify your database design, you ensure your team understands the "why" behind the structure. These steps are essential for long-term collaboration and system health.

Up next: Security Fundamentals in Design.