Postgres schema as first-class, diffable graph nodes

[elan-chels]

The add <ENV_NAME> postgres connector is great for one-shot capture. Would it be feasible to represent a relational schema as first-class graph nodes (tables and columns as nodes, foreign keys as edges) that persist and diff across re-syncs?

The use case is deriving a live ER view and detecting schema drift over time, rather than a point-in-time snapshot: on each sync, compare the captured schema against the previous one and surface what changed (added/dropped tables and columns, changed types, new/removed FKs).

If some of this already exists in what the connector emits today, pointers to the current node shape would help. Happy to share the node shape I'd want to query against if that is useful for the design.

[muthuishere]

Most of this already exists — here is the exact shape the postgres connector emits today (internal/sources/connectors/postgres.go), so you can wire against it now.

Nodes

kind	id	label	metadata
database	<base>	db name	host
schema	<base>/<schema>	schema name	—
table / view	<base>/<schema>/<table>	schema.table	partitions, index_count, indexes, comment; views also definition (400-char cap), materialized views materialized=true
column	<base>/<schema>/<table>/<col>	table.column	type, nullable, default, primary_key, comment

All carry file_type: "schema".

Edges

• contains — database → schema → table → column, the full chain.
• references — table → table, one edge per referenced-table pair, with every FK constraint aggregated into metadata.constraints as name(col→refcol, col→refcol); name2(…).

Both are EXTRACTED (catalog-certain, not inferred), so an ER diagram built from edges --relation references is parsed fact, not a guess.

That is your ER: nodes --kind table, nodes --kind column, edges --relation references, all --json.

The part that genuinely does not exist: diff over time

The store holds current state only. A re-capture does a producer-scoped Replace, so the previous schema is gone — there is no history to diff against, and I would rather say that plainly than have you discover it.

There is a real path to drift detection with what exists today, though, and it may be better than a built-in differ: the graph is sorted, git-diffable ndjson (that is a deliberate invariant — sorted output, atomic rename). So if PROOF commits the store, or pushes it through the remote lane into a git-backed location, then:

git diff HEAD~1 -- graph/nodes.ndjson graph/edges.ndjson

is your drift report — added/dropped tables and columns, changed types, new/removed FKs — with full history, blame, and no new feature required. Node ids are stable and deterministic, so the diffs are clean rather than churny.

If you wire that and it falls short, tell me where; a schema diff <ref> verb on top of it would then be a small, well-specified thing rather than a guess at what you need.

One caveat for an ER view

Two caps you should know about before you trust a rendered diagram: view definitions truncate at 400 chars, and index names list only the first 20 per table (the count is always exact). Tables, columns and FKs are not capped. System schemas are skipped and partitions collapse into the parent as partitions: N.