The column the cascade forgot to clear

Why the extra columns saved me

If a foreign key is going to SET NULL when its parent disappears, it's worth checking what else the row is carrying. A column that points at a catalogue, or at anything else a cascade has no reason to touch, survives the delete. That column is the whole difference between knowing "some rows are wrong" and knowing "these exact rows are wrong, and here is where each of them belongs".

I didn't originally pick those columns with that in mind. They were denormalised for ordinary reasons, like rendering a location label without a join. But the first time a delete went somewhere it shouldn't have, they were the entire recovery. I still denormalise for speed, but now I pick those columns knowing I might one day have to read them as evidence.

The setting is a portfolio tracker I run as a small SaaS. Users record the properties they own, the storage bays inside them, and which vehicle is parked in which bay.

What the ownership model changed underneath

Ownership used to be an account-level fact, so a user owned a property. In June I made it a per-character fact (one login holds several characters, and each character owns its own properties and vehicles), and migration 0048 moved uniqueness along with it.

alter table public.user_owned_properties
  drop constraint if exists user_owned_properties_user_id_property_id_key;

create unique index if not exists user_owned_properties_character_property_key
  on public.user_owned_properties (character_id, property_id);

It's two statements. Two characters on the same login can each own the same property, and those are two different rows.

What's easy to miss is that this is more than a uniqueness change. It redefines what identity means for a row in that table, and so what every lookup, update and delete against it is allowed to match on. togglePropertyOwnership still found the existing row with .eq("user_id", …), which had been right the day before. Under the new model, if a second character bought a property the first character already owned, the lookup matched the first character's row and deleted it.

user_owned_vehicles.stored_in_property_id is ON DELETE SET NULL, so the same statement unparked every vehicle in that property. The toggle then reported the property as not owned, which by that point was true. Nothing errored, because there was nothing for the database to object to.

Three columns, and only one of them moved

A parked vehicle carries three pieces of location:

• stored_in_property_id: the owned-property row it lives in
• assigned_upgrade_id: the floor or bay it sits on, which is a catalogue row
• slot_number: its numbered space within that bay

Only the first one points at the deleted row. The other two point at the shared catalogue of properties and their upgrades, which no user owns and no cascade reaches. So the delete left behind rows that were unparked but still remembered a floor and a bay.

That works like a fingerprint, and it tells you more than a flag would. The upgrade it names belongs to one property only, so a vehicle that's lost its parent still knows which property it was in. That meant I could recover it with a join, with no backup or shadow log.

Writing the fingerprint down as a query

Migration 0050 puts that join in the schema instead of leaving it in a script on my laptop.

create or replace view public.orphaned_vehicle_assignments
with (security_invoker = true) as
select v.id as owned_vehicle_id, v.user_id, v.character_id,
       v.assigned_upgrade_id, v.slot_number,
       pu.property_id  as former_property_id,
       p.display_name  as former_property_name
from public.user_owned_vehicles v
left join public.property_upgrades pu on pu.id = v.assigned_upgrade_id
left join public.properties p on p.id = pu.property_id
where v.stored_in_property_id is null
  and (v.assigned_upgrade_id is not null or v.slot_number is not null);

It only reports. Seeing a broken row and deciding what to do about it are separate steps, and I wanted to keep them that way.

The diagnostic that was nearly a leak

This section is really about with (security_invoker = true).

A plain Postgres view runs with the privileges of its owner, not its caller. Row-level security on the underlying table is written against auth.uid(), and a view owned by the migration role has no caller identity to test, so those policies don't apply to reads made through it. Every account's rows would have come back to anyone who asked, and because this schema is published through an auto-generated REST API, anyone can ask. In effect the view was a new public endpoint.

So a diagnostic I added to investigate data loss would have shipped as cross-account data exposure, which is a worse problem than the one it was written to study, and just as hard to spot. Two clauses close it. security_invoker makes the table's own RLS apply to whoever queries the view, and the grants spell out what the view is for.

revoke all on public.orphaned_vehicle_assignments from anon, authenticated;
grant select on public.orphaned_vehicle_assignments to service_role;

A view doesn't automatically get the table's security, so I think it's worth checking every time you make one.

What the repair does and doesn't touch

The repair script is 141 lines and runs as a dry run by default, so writing anything needs --apply. It re-creates the owned-property rows, reinstalls the upgrades the vehicles reference (along with any prerequisite chain those depend on), and re-parks each vehicle into its original property with its slot and sub-slot untouched. Only the lost parent link gets rewritten, and re-running is safe, because an already-restored property is reused instead of duplicated.

Some rows I left alone on purpose. Vehicles whose only breadcrumb is a slot_number, with no assigned_upgrade_id, are not touched. A slot number says "space 12 of somewhere", and there's no honest way to turn that into a destination. Repairing them would mean inventing a location that looks just as authoritative as a recovered one, and afterwards nobody, me included, could tell the two apart.

Those rows do support a weaker claim, so a separate read-only script makes only that one. Slot numbers are unique within a bay, so two vehicles sharing a slot number prove there are two different bays. Greedy bucketing by slot collision gives you the minimum number of distinct bays involved, and which vehicles are in each. It doesn't repair anything, but a person can read it and act on it, and I think that's the right output once the software's own knowledge runs out.

What shipped

The fix and the tooling landed as one commit, 12 files, +518 / −37, and the read-only diagnosis script is 120 lines of that. I swept the same mismatch out of the other owned-asset actions, and the by-id mutations got a character_id filter as defence in depth. Migration 0050 also adopts any rows still holding a null character_id before those tighter filters went live. In production that verified as zero rows, so it shipped as a no-op safety net.