PostgreSQL Schema Knowledge Graph

Understanding relational database schemas across hundreds of tables with complex foreign keys is cognitively demanding without interactive lineage graphs.

Problem

Database schema documentation is frequently stale or buried in dense DDL migration files, obscuring referential integrity and data dependencies.

Operational impact

Automatically maps PostgreSQL catalog tables and foreign key dependencies into an interactive graph network, enabling instant relationship exploration and visual schema audit.

Constraints

• Operates directly against standard PostgreSQL information_schema catalogs without external database modifications.
• Generates standardized Obsidian wikilink markdown notes for offline graph visualization.

Automated extraction of PostgreSQL schema relationships, constraints and foreign keys into interconnected Obsidian Graph notes and Markdown documentation.

Inputs

• PostgreSQL information_schema tables and key column usage catalogs
• Foreign key constraint definitions and referential actions
• Table column definitions, data types, nullability, and primary key metadata

Implementation

• SQLAlchemy and PLpgSQL inspectors extract table entities, primary keys, and foreign key dependencies.
• Graph transformation engine maps relational tuples into a bidirectional directed acyclic network.
• Markdown emitter outputs individual table notes with frontmatter metadata and wikilinks compatible with Obsidian Graph View.
• Python and SQLAlchemy schema inspector querying PostgreSQL information_schema.
• Automated graph exporter emitting frontmatter markdown files with relational wikilinks.

• Map foreign keys as directional wikilinksBidirectional linking allows exploring upstream dependencies and downstream consumers organically.
• Preserve exact DDL definitions in note frontmatterProvides immediate technical inspection without navigating away from the knowledge graph view.

The artifact above is the committed evidence for this project. More screenshots and files are in the public repository.

Evidence classes distinguish what can be inspected directly from what is documented, simulated or reconstructed.

• VERIFIED CODE
  PostgreSQL schema inspection query

  src/schema_map/inspector.py

• EXPERIMENTAL
  Obsidian knowledge graph generator

  src/schema_map/exporter.py

• Experimental research utility; requires read-only access to PostgreSQL catalog.
• Designed for analytical schema exploration rather than transactional schema migration orchestration.