pgfathom

pgfathom is a read‑only command‑line utility for PostgreSQL schema archaeology. It finds relationships that are not declared as foreign keys by examining the catalog, usage evidence from views and functions, naming conventions, and planner statistics. The tool never writes to the database and is designed for safe operation on production systems. The problem it solves is that many legacy PostgreSQL databases carry hidden referential integrity – columns that point to other tables but lack constraints, or constraints that were created NOT VALID and never validated. This makes the model invisible to tools like \\u2206 and allows orphan rows to accumulate unnoticed. What pgfathom does: * In discover mode it produces a terminal report with verdicts (broken, confirmed, weak, unvalidated, detected), a versioned JSON model, and reviewable .sql files. Confirmed relationships receive DDL with VALIDATE CONSTRAINT split out to avoid heavy locks, and CREATE INDEX CONCURRENTLY when needed. Broken relationships are emitted as commented DDL so the user can decide what to do with orphans. * It supports naming profiles (pt‑br, en, es) and can read affix conventions directly from the keys the schema still declares. Users can supply custom TOML profiles. * Join predicates are mined from view definitions and function bodies (and, when available, pg_stat_statements) to find relationships that name matching cannot reach. * The validation pipeline consists of six stages: reading the catalog, mining usage evidence, generating candidates using the profile, scoring on metadata, prefiltering with statistics, and final validation against data using aggregates. Installation options include DEB/RPM packages, Homebrew, a Docker image, or building from source with Go. The tool is read‑only, respects statement and lock timeouts, caps concurrency, and reports coverage of schemas and tables so silence is never mistaken for a clean bill of health. Scope defaults to the public schema but can be extended with --all-schemas, --schema, --exclude-schema, and --exclude flags. The setup command guides first‑time users through connection, schema selection, validation depth, and output location. Correctness is measured by recovery rate: a schema with complete foreign keys is stripped of them, pgfathom runs, and the fraction recovered is reported. Results are published on a public corpus covering GitLab, municipal systems, and Discourse, showing performance in partial (half the keys removed) and greenfield (no keys declared) regimes. The tool also reports how much each stage contributes – profile alone, naming detection, and join mining. Output includes a terminal summary with counts per verdict, tables analyzed, and statistics prefilter results; a JSON model versioned with timestamp; and .sql files that contain the DDL for confirmed keys (ready to run after review) and commented DDL for broken keys (to be examined before any action). All generated SQL is meant to be reviewed before execution. In short, pgfathom turns a silent, undocumented schema into a visible, actionable model, providing the exact queries needed to resolve broken integrity and the safe DDL to restore missing constraints.