The constraint names remembered a table that no longer existed
A table in this schema is called something about production runs. Its primary key constraint is called something about batch material usage. Its five foreign keys are all named after the same vanished table.
Nothing is broken. ALTER TABLE ... RENAME renames the table and leaves every constraint carrying
its old name, forever, unless someone renames those too — and nobody renames those, because they are
invisible until a constraint violation puts one in an error message. Which is where I met this one:
an insert failed, and the error named a table that does not appear anywhere in the codebase.
That is a free history lesson if you read it as one. The constraint names in a mature schema are the only part of it that cannot be tidied by a refactor, and they record what the tables were called when each one was created.
What the rename was hiding
The interesting part was not the rename. It was the output table next to it.
Production works in two levels. A batch is a planned quantity of one raw material to be processed. A run is one pass through the press. A batch contains several runs, because the machine takes a fixed charge and a batch is bigger than a charge.
The output table — the rows recording how much product came out — originally hung off the batch. Its constraint names still say so. Today it has a run column too, and that column is nullable while the batch column is still not null.
Read those two facts together and the migration writes itself. Output used to be recorded against the batch. Someone needed it against the run. The new column could not be made mandatory because existing rows had no run to point at, so it went in nullable, the old column stayed, and the code started writing both.
That is the correct way to do it and it leaves a permanent ambiguity in the schema. Every query against that table now has to know whether it is asking a batch-level question or a run-level question, and for rows older than the migration the run-level question has no answer. The nullable column is not a design choice, it is a date.
Why the level mattered
Yield is the reason. Yield is output divided by input, and input is recorded on the run — each run records how much raw material it consumed. With output attached to the batch, you can compute the yield of a batch and you cannot compute the yield of a run.
That distinction is the whole point of the measurement. Batch yield tells you what a lot of raw material produced, which is useful for costing and useless for operations. Run yield tells you that the third pass of the day is worse than the first two, which is a machine setting, a temperature or an operator. The person who asked for the change was not asking for a schema improvement. They were asking to see which pass was losing them product.
When a metric is a ratio, the two sides of it must be recorded at the same level, and the level must be the one where the decision is taken. Getting this wrong is not detectable from the schema. Both designs store true numbers. One of them just cannot answer the question anyone wanted to ask.
The duplicate
The output table has two foreign keys on the run column. Both point at the same table, both cascade on delete, both are identical in behaviour, and they differ only in the naming convention used — one in the camel-case style of the application’s generated migrations, one in the snake-case style of a hand-written one.
Two people, or one person twice, added the same constraint under two conventions and the database accepted both, because Postgres does not deduplicate constraints. The cost is small and real: every insert validates the same reference twice, and any future attempt to drop “the” foreign key will succeed and appear to do nothing.
The lesson is not about the wasted check. It is that a schema with two naming conventions has two authors who could not see each other’s work, and duplicated constraints are the cheapest symptom of that to detect. A query over the catalogue for constraints with identical definitions and different names takes a minute and will find every place two migration paths overlapped.
How to read a schema you did not write
Four things carry history, and none of them appear in the model files:
Constraint and index names tell you the original table names, so a name that does not match its table is a rename, and the old name tells you what the concept used to be.
Nullable foreign keys next to non-nullable ones pointing into the same hierarchy tell you the hierarchy changed level and the old level was kept for compatibility.
Two soft-delete conventions — this schema has both a nullable deletion timestamp and a plain boolean deleted flag, on different tables — tell you which tables were built in which era, and warn you that a generic “exclude deleted rows” helper will be wrong on half of them.
Default values that are business figures tell you what the first customer’s numbers were. A tax rate sitting as a column default is a fossil of the first jurisdiction the product shipped in.
None of that is documentation and all of it is evidence. On a project with no written history, the catalogue is the history.
The rule
When you move a record from one parent to another, write down the date the code started populating the new parent, and put it in a comment on the column. The nullable column will outlive everyone who remembers what the null means, and a query that treats missing as zero will be wrong for every row written before that date.
The honest limit here: those old rows were never backfilled, because there is no information anywhere that says which run produced which output. The data to reconstruct it does not exist. That means the run-level yield report starts on a date, and the report says so on its face, which is the only correct thing to do with a number that cannot be computed for the earlier period.