okf-pg-tenant — an OKF producer for multi-tenant PostgreSQL that collapses schema-per-tenant databases

[back1ply]

okf-pg-tenant turns a PostgreSQL catalog into an OKF v0.2 bundle. It targets the shape most OKF Postgres producers assume away: multi-tenant, largely undocumented, and running on managed Postgres where you are not superuser.

Repo: https://github.com/back1ply/okf-pg-tenant · MIT · one runtime dependency (psycopg)

The problem it exists for

Many B2B SaaS products give every customer their own schema. A database with 800 tenants and 12 tables has 9,600 physical tables — and roughly 12 distinct ones.

A naive producer                    okf-pg-tenant
─────────────────                   ─────────────
9,600 markdown files                12 markdown files
one per tenant, per table           one per LOGICAL table
an index nobody can read            tenant count in frontmatter
drift invisible                     drifting tenants named

Collapsing throws information away, so each remaining feature puts back the piece that mattered:

tenancy:
  pattern: schema-per-tenant
  schemas: 797
  tenant_schemas_total: 800
  absent_from: 3          # present in only some tenants = a stalled migration
  column_drift: 3         # and the drift section names which ones
  reference_schema: <the schema the columns were read from>
  reference_ambiguous: false

The tenant-schema pattern is a regex (--tenant-pattern), defaulting to UUID.

Three things that might be useful to others here

It reads base catalogs, not information_schema. The docs are explicit that information_schema.tables shows "only those tables and views ... that the current user has access to", so a least-privilege account gets a silently incomplete picture. pg_class, pg_attribute, pg_namespace, pg_index and pg_description are world-readable and do not filter that way. Worth stressing that this is a property of those catalogs, not of pg_catalog as a whole — pg_stats deliberately shows only rows for tables you may read. My README claimed the blanket version until I checked it against the docs and found it wrong.

Every bundle ships a _diagnostics.md that says what the run could not do, and counts the relations it chose not to document. That is what stops column_drift: 0 from quietly meaning "zero among the things I bothered to look at". On the first real run it surfaced foreign tables spread unevenly across schemas — a genuine inconsistency in a relation kind the tool does not document.

Table names are treated as untrusted input. A name is whatever someone typed inside CREATE TABLE "...", and Postgres permits .. and /, so writing bundle/tenant/<table>.md straight from the catalog is a path traversal. Names are sanitised, and two that collide raise rather than one silently overwriting the other. Notably semgrep with 1,115 Python and security-audit rules reports nothing on the vulnerable pattern — a database column is not a taint source those rules model.

What it does not do

• Column drift only. Defaults, primary keys, unique/check constraints, indexes, foreign keys, identity columns, triggers and RLS are not compared. Two tenants reported identical can still accept different writes. Every drift section in the output repeats this, so a reader of a single document cannot miss it. Widening the signature is the top roadmap item.
• Only ordinary tables are documented (relkind = 'r'). Views, materialized views, foreign tables and partitioned tables are counted and flagged, never documented or drift-checked. Partitioning is the one most likely to surprise.
• Descriptions come from COMMENT ON only. Where a database has none the field stays empty, which is the honest outcome rather than a generated sentence.
• verified: is never emitted. v0.2 separates who produced a document from who confirmed it; nothing here confirms anything, and filling that field is the exact failure the split exists to prevent.
• stale_after has no default. How fast a schema snapshot goes stale is a property of your release cadence, not of this tool.

Evidence

23 tests (plain asserts, no framework, no database), 81% line coverage where the uncovered remainder is the DB and CLI shell, and a mutation gate that plants seventeen deliberate bugs and fails if any survives. CI runs the suite on 3.9/3.12/3.13 plus an end-to-end job that stands up real Postgres, produces a bundle and asserts the collapse arithmetic.

The mutation gate is the part I would recommend to anyone else building a producer. A green suite says the tests ran, not that they check anything — and it has already caught a real hole here: the function that keeps credentials out of the sources URI had no test at all, which only surfaced because the mutant that broke it survived.

Prior art, as far as I can tell

migra, Liquibase, pgquarrel and apgdiff all do pairwise DDL diff and compare considerably more than this does. Atlas has real multi-tenant support, but for applying migrations rather than documenting. DataHub's Postgres source puts the schema name in the dataset URN, so 800 tenant schemas emit 800× the datasets. I could not find anything that collapses identical tenant schemas for documentation — happy to be pointed at prior art I missed.

Feedback welcome, particularly on the drift signature and on whether the collapse is the right default at all.