Work / System utility

PostgreSQL Schema Knowledge Graph

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

My implementation: Python and SQLAlchemy schema inspector querying PostgreSQL information_schema. Markdown exporter emitting table details and relational wikilinks.

The script reads PostgreSQL metadata and counts table rows; SELECT access to those tables is required.

PostgreSQL Schema Knowledge Graph · Project artifact

Committed Obsidian screenshot showing Pagila table notes and their links, including rental, payment, customer and film. — SCHEMA
Actual repository screenshot: Obsidian Graph View of generated Pagila notes. The interactive viewer below is a separate illustration.

Context

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.

What it enables

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

Constraints

The script reads PostgreSQL metadata and counts table rows; SELECT access to those tables is required.
Generates standardized Obsidian wikilink markdown notes for offline graph visualization.

System

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

Illustrative schema and row estimates. This viewer is not connected to a live database.

Relational Foreign Keys

→ Outgoing FK:customer_id references [[customers]]
← Referenced by:[[order_items]] via order_id
← Referenced by:[[payment_transactions]] via order_id

Generated Obsidian Note

---
type: database_table
table: orders
schema: public
estimated_records: 185,000
tags: [#entity/fact, #domain/commerce]
---

# orders (PostgreSQL Schema)

## Relational Dependencies
- Foreign Key: `customer_id` → [[customers]]
- Referenced by: [[order_items]] (`order_id`)
- Referenced by: [[payment_transactions]] (`order_id`)

## DDL Schema
```sql
CREATE TABLE public.orders (
  id               UUID PRIMARY KEY,
  customer_id      UUID REFERENCES customers(id),
  order_date       TIMESTAMPTZ,
  total_amount     NUMERIC(12,2),
  status           VARCHAR(40)
);
```

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

A Python SQLAlchemy inspector reads public-schema tables, keys, columns and indexes.
An incoming-key index complements outgoing foreign keys; the relationships are not assumed to form an acyclic graph.
The script writes Markdown tables, relation lists, row counts and an index note for Obsidian.
Python and SQLAlchemy schema inspector querying PostgreSQL information_schema.
Markdown exporter emitting table details and relational wikilinks.

Decisions

Map foreign keys as directional wikilinksBidirectional linking allows exploring upstream dependencies and downstream consumers organically.
Keep columns, keys and indexes beside the relation linksGenerated notes make the schema details available without reconnecting to the database at question time.

Artifacts

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

Evidence

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

VERIFIED CODE
PostgreSQL schema inspection query
schema_to_obsidian.py
VERIFIED ARTIFACT
Committed Pagila Markdown notes
schema_filemd/

Limitations

Snapshot utility: re-run after schema changes. Row counts require table SELECT access and may be costly on large tables.
Relationship labels use a heuristic and require review for composite keys. The portfolio viewer is illustrative, not connected to a database.

Discuss a system like this.

Start a work enquiry All work