The schema I couldn't reproduce

The hardest part of maintaining a schema-aware tool is not fixing bugs. It is reproducing them. A bug report on satus arrives as a stack trace plus a sentence, and the thing that actually caused it is a CREATE TABLE we have never seen, on a Postgres version we are not running, in a schema the reporter usually cannot share. Every hour spent on such a ticket before the first local reproduction is an hour of guessing. This is a field note on the two reproductions that cost us the most, and on the four artifacts we now reach for first.

We do not have a satus repro command, and this post is not announcing one. What we have is a workflow assembled from pieces that already exist in the repo, and being explicit about that workflow is more useful than shipping a subcommand that only wraps it.

Why schema bugs resist reproduction

A crash in a general-purpose CLI usually depends on the arguments. A crash in satus depends on the catalog. The input is not a string the reporter can paste; it is the transitive closure of every table, type, constraint, partition, and extension in their target schema, on their server version, with their identifier quoting conventions.

Three properties make that hard to transport:

Property	Why it blocks reproduction
The schema is often confidential	Table and column names leak product roadmap; the reporter cannot attach the DDL
pg_dump is lossy for our purposes	Planner statistics, extension-owned objects, and constraint placement do not survive round-tripping the way you expect (earlier post)
The trigger is a feature combination	Any one of partitioning, quoted mixed-case identifiers, deferrable FKs, or a third-party extension is easy. The interaction of two is not

The third property is the expensive one. Our test corpus covers each feature in isolation, so a report that lands on a pair we never combined reads, at first, as an impossible failure.

Reproduction one: a partitioned table with per-partition foreign keys

The longest reproduction we have had was not a customer schema at all. It was pagila, which sits in our audit corpus and started failing our self-test after we widened the free-tier table cap.

Two facts had to be true simultaneously for the failure to appear. Pagila's payment table is declared as a partitioned parent, and its foreign keys are declared on the partition children rather than on the parent. In pg_catalog, the parent carries relkind = 'p' and each child carries relispartition = true, and the same logical constraint appears once per child under a distinct conname. Our introspection walked pg_constraint without accounting for either, so a single logical FK edge arrived at the planner as N duplicated edges pointing at tables the planner had also enumerated as insert targets. Postgres routes an INSERT on the parent to the correct partition, so inserting into the children directly is both unnecessary and wrong.

What made the reproduction slow was that neither fact is visible in a stack trace, and neither is unusual on its own. The fix, once we could see it, is small and lives in packages/cli/src/generate/introspect.ts: exclude partition children from the table list, re-attribute any FK declared on a child back to the topmost ancestor via pg_partition_root(), and aggregate the duplicated constraint rows with bool_or so the deferrability flags survive the collapse.

The free-tier table cap interacts with the same code path, and the interaction is worth stating precisely: the cap slices the topologically ordered table list, not the catalog order, so the retained prefix always contains a table's parents before the table itself. Deferred back-edges are then filtered down to the retained set, so the runner never tries to patch a table it never wrote. Cap a schema on any other ordering and you can seed a child whose parents were trimmed, which produces an unsatisfiable plan that looks, from the outside, exactly like an introspection bug.

Reproduction two: quoted mixed-case identifiers

The 0.3.5 fix was a single column alias in the catalog introspection query. Schemas containing quoted identifiers with mixed case failed; schemas without them worked. Every schema in our corpus is lower-snake-case, which is idiomatic Postgres and also, in this instance, a blind spot: an entire class of real-world schema, the ones generated by ORMs that preserve camelCase, was structurally absent from everything we tested against.

The lesson we took from it is not "add a camelCase fixture", though we did. It is that a corpus assembled from open-source projects inherits those projects' conventions, and the conventions are correlated. Five well-run OSS Postgres schemas agree with each other about quoting, naming, and constraint style far more than five randomly drawn production schemas would.

The four artifacts

None of these are new, and none of them are a subcommand. They are the things we actually open, in this order, when a report arrives.

1. --dry-run, for a reporter who cannot share DDL

satus generate --dry-run introspects the live catalog, substitutes a deterministic offline provider for the LLM, and runs the real validator. It needs no API key, sends no tokens, and writes no rows. That means we can ask a reporter to run it against their own confidential schema and send us the output, which is the cheapest way to get a signal off a database we will never see. The mechanics are covered in A $0 dry-run.

2. --json, for a machine-readable failure envelope

--json puts exactly one JSON object on stdout and pushes human output to stderr. A pasted terminal screenshot loses the parts we need; a JSON object does not.

3. Opt-in schema fingerprints, for clustering

Since 0.3.3, telemetry.share_failure_fingerprints (default false, and satus init defaults the prompt to no) reports a SHA-256 of the normalised shape of a schema alongside the validator rule that fired first. The normalisation deliberately drops table names, column names, and default expressions, and preserves the sorted multiset of column types per table, the FK edge list by position, primary-key arity, and single-column unique arity. See packages/cli/src/generate/fingerprint.ts.

The point is not to identify a schema. It is that two reports carrying the same fingerprint and the same validator_class are the same bug, and reproducing one reproduces both. The point is also that the fingerprint alone will never let us reconstruct the schema, which is why the knob can honestly default to off.

4. A runnable failure fixture, for anything extension-shaped

When the trigger is an extension, prose is not enough. examples/extension-pitfalls/ contains three idempotent SQL scripts that provoke and then correct the PostGIS, pgvector, and pgcrypto failure modes described in Postgres extensions that trip up seeders, plus a Docker runner. Handing a reporter a script that fails identically on their machine and ours converts an argument about behaviour into a diff.

The minimal repro we ask for

If you are filing an issue, this is the shortest path to a fix. Everything here is redactable, and none of it requires sharing your real schema.

# 1. Version and server, verbatim.
satus --version
psql "$DSN" -c 'select version()'

# 2. Machine-readable failure envelope.
satus generate --dsn "$DSN" --schema <your_schema> \
  --profile saas --rows 5 --dry-run --json > satus-failure.json

# 3. Structural shape only. No data, no grants, no owners.
#    Redact identifiers freely; keep types, constraints, and partitioning.
pg_dump --schema-only --no-owner --no-privileges \
  --schema=<your_schema> "$DSN" > schema.sql

Step 3 is the one people skip, and it is the one that decides whether a ticket takes an hour or a week. A schema-only dump with every identifier renamed to t1, c1, c2 is still a perfect reproduction for us, because the bugs above were never about the names. They were about relkind, relispartition, condeferrable, and quoting. All of those survive redaction intact.

What we are not claiming

We have not eliminated the slow ticket. A feature combination we have never seen will land again, and the first hours will still be guesswork. What the four artifacts change is the shape of the guesswork: we now start from a validator class, a structural fingerprint, and a schema-only dump instead of a sentence and a stack trace. That is a real reduction, and it is a smaller claim than a subcommand named repro would have implied.

References

• PostgreSQL documentation, System Information Functions, for pg_partition_root().
• PostgreSQL documentation, pg_constraint and pg_class, for relkind, relispartition, and condeferrable.
• PostgreSQL documentation, Table Partitioning, on routing inserts through the parent.
• PostgreSQL documentation, pg_dump, for --schema-only, --no-owner, --no-privileges.
• pagila—the partitioned schema in our audit corpus.
• examples/extension-pitfalls/—runnable PostGIS, pgvector, and pgcrypto fixtures.
• What pg_dump doesn't tell you about your own schema.
• A $0 dry-run that catches FK and constraint bugs before the LLM call.
• Postgres extensions that trip up seeders.

—the satus.sh team