TRUNCATE CASCADE does not delete related rows, it empties related tables

The command that resets tenant data in this project truncates the organisations table with CASCADE. That is a normal thing to have in a seeding script: clear the tenants, and everything hanging off them goes with them.

It also emptied the roles table. Every role, including the four built-in ones that are not owned by any tenant. And with them, every row granting a permission to a role, and every row assigning a role to a user.

The result is a database with users, staff, sites and credentials intact, and nobody able to do anything, because authorisation has been deleted. Every request returns a 403 naming a permission the user should have had.

Why the roles table was a child at all

Roles come in two kinds here. Four are built into the product — an administrator, a manager, a viewer and a staff-portal role — and they belong to no tenant. Tenants may also define their own.

Both live in one table, distinguished by a nullable tenant column: null means it is a system role, a value means it belongs to that tenant. That is a common and reasonable design. It keeps one table, one set of permission grants, one lookup path, and it lets a tenant role be handled by exactly the code that handles a system role.

The nullable column is a foreign key to the organisations table. It has to be, or a tenant role could name a tenant that does not exist.

And that is the whole mechanism. The roles table references the organisations table, therefore the roles table is a child of the organisations table, therefore TRUNCATE ... CASCADE empties it — all of it, including the rows whose tenant column is null and which reference nothing at all.

The distinction people get wrong

DELETE with cascading foreign keys deletes the related rows. Delete an organisation, and the rows that point at it go.

TRUNCATE ... CASCADE truncates the related tables. Not the related rows. The entire table, every row in it, whether or not it had any relationship to anything being truncated.

That is documented behaviour and it is the only behaviour that could work, because truncate does not scan rows — that is the point of it. It cannot find the subset to remove without doing the scan it exists to avoid, so it removes everything in any table that references the target, and recursively everything referencing those.

So a table with a nullable foreign key to something you truncate loses its unrelated rows too. In this case those unrelated rows were the entire authorisation model.

Truncate is not a fast delete. It is a different operation with different semantics, and the difference only shows up when a child table holds rows that are not children.

How it was found and what it left behind

The evidence is still in the codebase, in two places, and both are worth having.

The seeding script has a function that re-inserts the system roles and their permission grants after the truncate, with a comment saying in one sentence why it exists: cascading the truncate to the roles table wipes the system roles, so they have to be re-seeded. That is somebody hitting this, working it out, and writing it down where the next person will meet it. It is one line and it saves an afternoon.

There is also a verification script that runs after the schema is applied. Among a handful of counts, it counts the permission grants attached to the administrator role. That is not a general health check. That is a specific tripwire for this specific failure, placed by someone who knew the number can silently become zero.

Both of those are compensating controls rather than fixes, and they are correct in their place. The truncate is a legitimate operation on a development database, and re-seeding reference data after it is a legitimate response.

What I would change in the schema

The compensations work as long as everyone uses the script. The moment somebody runs the truncate by hand — during a demo reset, in a hurry — the re-seed does not happen and the tripwire is not run.

Two options, both structural.

Separate the tables. System roles and tenant roles are different things with different lifecycles; one is product reference data shipped with the schema, the other is customer data. Putting them in one table because they share a shape is the same instinct that produces a single table for users and service accounts, and it has the same consequence: an operation appropriate to one becomes catastrophic for the other.

Or keep one table and make the reset explicit. Truncate the tenant tables by name rather than relying on cascade. It is more typing, it needs maintaining as tables are added, and it never reaches somewhere you did not intend. A reset script that enumerates what it destroys is a script you can read before running.

The wider habit: a destructive command that relies on cascade is a command whose blast radius is defined by the schema, and the schema changes without the command being reviewed. Every foreign key added later widens it. Nobody adding a foreign key thinks about the reset script.

The related trap in the same design

While in the area, one more consequence of the nullable-tenant-column pattern, because it bites in a quieter way.

Uniqueness. You want a role key to be unique — one administrator role per scope. The obvious constraint is a unique index on the tenant column and the key together. That does not work, because in a unique index nulls are distinct from each other, so any number of system roles could share a key.

This schema handles it with two partial unique indexes: one on the key alone, restricted to rows where the tenant column is null; one on the pair, restricted to rows where it is not. Two indexes, mutually exclusive conditions, and together they express the rule the single index could not.

That is the right answer, and it is a second piece of complexity bought by the same decision to put two kinds of row in one table. Whenever you use a nullable discriminator like this, expect to pay for it twice — once in the uniqueness rules and once in every operation that treats the table as a whole.

The rules

Know which of your child tables contain rows that are not children. Those are the rows a cascading truncate takes with it, and they are usually the reference data everything else depends on.

Enumerate what a reset destroys. Cascade is convenient exactly in proportion to how little you know about what it reaches.

When you work out a footgun, leave the sentence behind. The one-line comment above the re-seed function in this codebase is worth more than the function.

The limit: the tables have not been separated. The reset script is correct, the tripwire is in place, and the risk that remains is a person running the truncate by hand. I have not found a way to prevent that other than not having the privilege, and on a development database everybody has the privilege.