database-documentation-gen
Process use when you need to work with database documentation. This skill provides automated documentation generation with comprehensive guidance and automation. Trigger with phrases like "generate docs", "document schema", or "create database documentation".
Allowed Tools
Provided by Plugin
database-documentation-gen
Database plugin for database-documentation-gen
Installation
This skill is included in the database-documentation-gen plugin:
/plugin install database-documentation-gen@claude-code-plugins-plusClick to copy
Instructions
Database Documentation Generator
Overview
Generate comprehensive database documentation by introspecting live PostgreSQL or MySQL schemas, extracting table structures, column descriptions, relationships, indexes, constraints, stored procedures, and views. Produces human-readable documentation in Markdown format including entity-relationship descriptions, data dictionary, and column-level metadata.
Prerequisites
- Database credentials with read access to
informationschema,pgcatalog(PostgreSQL), or system tables (MySQL) psqlormysqlCLI for executing introspection queries- Target output directory for generated documentation files
- Existing column comments (
COMMENT ON COLUMN) enhance output quality significantly - Knowledge of the business domain for meaningful table/column descriptions
Instructions
- Extract the complete table inventory:
SELECT tablename, objdescription((tableschema || '.' || tablename)::regclass) AS tablecomment FROM informationschema.tables WHERE tableschema = 'public' AND tabletype = 'BASE TABLE' ORDER BY tablename(PostgreSQL). For MySQL:SELECT TABLENAME, TABLECOMMENT FROM informationschema.TABLES WHERE TABLE_SCHEMA = DATABASE().
- For each table, extract column details:
SELECT c.columnname, c.datatype, c.charactermaximumlength, c.isnullable, c.columndefault, pgd.description AS columncomment FROM informationschema.columns c LEFT JOIN pgcatalog.pgdescription pgd ON pgd.objsubid = c.ordinalposition AND pgd.objoid = (c.tableschema || '.' || c.tablename)::regclass WHERE c.tablename = 'targettable' ORDER BY c.ordinalposition.
- Extract primary key and unique constraint definitions:
SELECT tc.constraintname, tc.constrainttype, kcu.columnname FROM informationschema.tableconstraints tc JOIN informationschema.keycolumnusage kcu ON tc.constraintname = kcu.constraintname WHERE tc.tablename = 'targettable' AND tc.constraint_type IN ('PRIMARY KEY', 'UNIQUE').
- Extract foreign key relationships to build the relationship map:
SELECT tc.tablename AS childtable, kcu.columnname AS childcolumn, ccu.tablename AS parenttable, ccu.columnname AS parentcolumn, rc.deleterule, rc.updaterule FROM informationschema.tableconstraints tc JOIN informationschema.keycolumnusage kcu ON tc.constraintname = kcu.constraintname JOIN informationschema.referentialconstraints rc ON tc.constraintname = rc.constraintname JOIN informationschema.constraintcolumnusage ccu ON rc.uniqueconstraintname = ccu.constraintname WHERE tc.constrainttype = 'FOREIGN KEY'.
- Extract index definitions:
SELECT indexname, indexdef FROM pgindexes WHERE schemaname = 'public' ORDER BY tablename, indexname(PostgreSQL). For MySQL:SELECT TABLENAME, INDEXNAME, COLUMNNAME, NONUNIQUE, SEQININDEX FROM informationschema.STATISTICS WHERE TABLESCHEMA = DATABASE() ORDER BY TABLENAME, INDEXNAME, SEQIN_INDEX.
- Extract views and their definitions:
SELECT viewname, definition FROM pg_views WHERE schemaname = 'public'. Document each view with its purpose, source tables, and any filtering logic.
- Extract functions and stored procedures:
SELECT routinename, routinetype, datatype AS returntype FROM informationschema.routines WHERE routineschema = 'public'. Include function signatures and parameter descriptions.
- Generate the data dictionary in Markdown format with one section per table containing: table description, column table (name, type, nullable, default, description), primary key, foreign keys with referenced table, indexes, and any check constraints.
- Generate an entity-relationship summary listing all relationships:
parenttable (parentcolumn) -> childtable (childcolumn)with cardinality (one-to-many, many-to-many via junction tables).
- Generate table statistics for context:
SELECT relname, nlivetup AS rowcount, pgsizepretty(pgtotalrelationsize(relid)) AS totalsize FROM pgstatusertables ORDER BY nlivetup DESC. Include approximate row counts and table sizes in the documentation.
Output
- Data dictionary (Markdown) with complete column-level documentation for every table
- Entity-relationship description listing all foreign key relationships with cardinality
- Index catalog documenting all indexes with their columns and purpose
- View definitions with source table references and business logic descriptions
- Schema statistics including table sizes, row counts, and index sizes
Error Handling
| Error | Cause | Solution |
|---|---|---|
| Missing column comments | COMMENT ON COLUMN not used in the database |
Generate inferred descriptions based on column name patterns; flag columns needing manual description |
| Permission denied on pg_catalog | Restricted database user without catalog access | Request pgreadallsettings role; or use pgdump --schema-only as an alternative schema source |
| Large schema with 500+ tables | Documentation generation takes too long or produces unmanageable output | Generate per-schema or per-module documentation; create a table-of-contents index; filter to specific table prefixes |
| Custom types not resolved | PostgreSQL domain types or composite types not in standard introspection | Query pg_type for custom type definitions; include type documentation in a separate section |
| Stale documentation after schema change | Documentation not regenerated after migration | Integrate documentation generation into CI/CD pipeline; run after migration step |
Examples
Generating documentation for a 50-table e-commerce database: Introspect all tables in the public schema, producing a 200-line Markdown data dictionary. Each table section includes column descriptions derived from COMMENT ON COLUMN annotations, foreign key relationship arrows, and index listings. Junction tables are identified and documented as many-to-many relationships.
Creating onboarding documentation for a new team member: Generate schema documentation with table sizes and row counts to help new developers understand which tables are central (large, many relationships) and which are auxiliary (small, few references). The relationship map shows the core entity graph: users -> orders -> order_items -> products.
Audit-ready documentation for compliance: Generate documentation including all constraints, check rules, and default values for each column. Flag columns containing PII (matching patterns like email, phone, ssn, address) and document their data protection controls. Output includes timestamp of generation and database version.
Resources
- PostgreSQL system catalogs: https://www.postgresql.org/docs/current/catalogs.html
- PostgreSQL information_schema: https://www.postgresql.org/docs/current/information-schema.html
- MySQL information_schema reference: https://dev.mysql.com/doc/refman/8.0/en/information-schema.html
- SchemaSpy (database documentation tool): https://schemaspy.org/
- dbdocs.io (database documentation hosting): https://dbdocs.io/