The Cascade That Ate Our Data

The update was supposed to take a second. It did. Then we noticed the trial rows were gone.

We had run what looked like a routine change to an experiment's scenario list. The SQL was small. The intent was small. The blast radius was not small. Across two experiments and three runs, every individual trial row on the affected scenarios had quietly stopped existing. Not soft-deleted, not flagged, not archived. Gone. The run-level aggregate stats were still sitting there as if nothing had happened, which made the whole thing feel briefly like a UI glitch before it stopped feeling like a UI glitch at all.

This is the story of what we did, what we missed, and what we now believe everyone running a relational schema with ON DELETE CASCADE should keep in the front of their mind before they touch a parent row.

The cold open: aggregates without trials

Our experiment system, like a lot of evaluation harnesses, is shaped like a tree. An experiment owns runs. A run owns scenarios. A scenario owns trials, where a trial is the actual unit of work: one execution of one prompt against one configuration, with all of the metadata and outputs we care about hanging off it. On top of that tree we keep precomputed aggregates at the run level, the things you want to see in a dashboard without recomputing across thousands of trials every time someone opens a tab.

The change we made was to the scenario list on one experiment. The details of why are unremarkable: a few email scenarios had broken arguments (missing --to, --subject, --thread-id), and we called update_experiment to fix them. The kind of edit you make several times a week when you are tuning an experiment.

What we noticed, after the change, was that the run page looked half-correct. The summary numbers were intact. The per-scenario rollups were intact. The trial table was empty. Not "loading slowly." Empty.

The first reaction in the room was the reaction every engineer has the first time they look at a table that should have rows and doesn't: this is a query bug, this is a filter, this is a stale cache, this cannot be what it looks like. We checked the filter. We checked the query. We ran the underlying SELECT against the database directly. The trials were not there.

The first hypothesis: the application did it

Our first guess was that the update path in our application had done something wrong on the way through. We have an experiment service that mediates writes to this schema, and it does a few non-trivial things along the way: it validates scenario definitions, it triggers re-indexing, it writes audit rows. It was easy to imagine a bug in that path that, on a scenario list update, had decided to also clean up "orphan" trials and gotten the definition of orphan wrong.

We pulled the service logs around the timestamp of the change. The updateexperiment call had landed cleanly: ten new scenario rows inserted, all with identical createdat timestamps of 2026-05-25T15:40:02 UTC. No DELETE statements anywhere in the application logs. No batch cleanup. No re-indexing job that touched the trials table. The service did exactly the small thing it had been asked to do.

That should have been a clue, and in retrospect it was the clue. If the application did not issue the DELETE, and the DELETE happened, then the DELETE was issued by the database itself. Which meant we needed to look at the schema, not the code.

It still took us another half hour to actually look at the schema, because the human instinct in this situation is to keep reading the code. The code is the thing you wrote. The code is the thing you can blame. The schema is the thing that has been sitting there, apparently inert, for months. Schemas do not usually surprise you. This one was about to.

The turn: what does our foreign key actually say

We pulled the DDL for experimenttrials. The foreign key referencing experimentscenarios(id) was declared with ON DELETE CASCADE. There is also a chain: experiment_scenarios references experiments(id). The cascade path runs from scenarios down to trials.

The foreign key from experiment_trials to its parent was declared with ON DELETE CASCADE. That is not unusual. It is, in fact, the most common way people set up child tables when they want the database to do the housekeeping for them. If a parent row goes away, its children should go away with it. You do not want trial rows pointing at a scenario that no longer exists. Cascade delete is the textbook answer to that problem, and we had reached for the textbook answer when we built the schema, because at the time it was the right answer.

The thing that broke our model of what the update would do was the relationship between UPDATE on the parent and DELETE on the parent.

In our case, the change we ran on the scenario list was not just an in-place edit of a row's contents. The update_experiment API tool is designed as a wholesale replace: when you pass a scenarios array, it drops all existing scenario rows for that experiment and inserts the new set with fresh UUIDs. No ORM magic, no migration script. The tool's own implementation does the replace. That is the architectural decision that made this possible. From the outside, from the perspective of the person running the change, it looked like an update to a list. From the inside, from the perspective of the database, it was a DELETE followed by an INSERT.

The DELETE part triggered the cascade. Every trial row whose scenario_id pointed at one of the old UUIDs was immediately removed by the database, in the same transaction, before the new scenario rows even existed to be linked to. By the time the INSERT landed and the new scenarios appeared with their fresh UUIDs, there was nothing left in the trials table to associate with them.

The run-level aggregates survived because they are stored as columns directly on the experiment_runs row, not joined through scenarios. They are not children of the scenarios. They are properties of the runs, which we had not touched. The cascade ran exactly as declared, in exactly the place we had declared it, on exactly the rows we had told it to cascade through. The database did nothing wrong. It did the thing we had asked it to do, two years ago, in a CREATE TABLE statement nobody had looked at since.

The root cause, in one sentence

A foreign key with ON DELETE CASCADE does not care whether the parent was deleted because you wrote DELETE, or because something further up your stack chose to model an update to that parent as a delete-and-reinsert. To the database, those are the same event. If your code path replaces a parent row's identity, you are issuing a DELETE on the parent, and every cascade rooted at that parent will fire.

That is the whole bug, expressed structurally. Everything else is consequences.

The fix, and what it cost

The run-level aggregate stats were intact on the experimentruns row (arm A 62.5%, arm B 56.3%, delta −6.3 pp, p = 0.7189), so the headline results were not lost. But the per-trial detail and individual thought transcripts were gone from experimenttrials. Recovery would have required Supabase point-in-time recovery (PITR), restoring the database to a snapshot before 15:40:02 UTC. We did not attempt PITR. Instead we re-ran the experiments from scratch, which completed cleanly and gave us fresh trial data. The cost there was compute time and a few hours of waiting, not permanent data loss, and no customer-facing experiment was on the affected schema. That is the part where we got lucky, and we are saying that out loud, because the same bug shape on a production-facing dataset would not have been a clean re-run.

The structural fix has not shipped yet, but two options are on the table. The first is to add an update_scenario tool that patches individual scenarios without replacing the full set, so parent UUIDs are never swapped. The second is to switch from hard-delete to soft-delete on old scenario rows, marking them inactive rather than dropping them, so trial foreign key references survive even after edits. Both approaches address the same root cause from different angles: the first eliminates the DELETE event entirely, the second makes the DELETE non-destructive to children.

We also did a sweep through the rest of our schema for every ON DELETE CASCADE we had declared, asking, for each one: is there any code path that could cause the parent to be deleted as a side effect of something the caller would not have called a delete? Several came back yes. Some are guarded today by paths that happened to already exist rather than by anything we added, and some are still open tickets. We are working through them.

The broader lesson: cascades are silent verbs

There is a category of bug here that is older than us and that we expect to keep meeting, in some form, for as long as we run on a relational database. The category is: powerful database features whose effects are not visible at the call site that triggers them.

ON DELETE CASCADE is a contract you sign once, in a schema definition, and then the database honors it on every relevant write, forever, without further notice. That is the feature. It is also the failure mode. The caller who triggers a cascade is usually not the person who wrote the cascade, often not even on the same team, and very often not aware that the operation they just performed is going to recurse through several child tables removing rows they have never thought about.

The way this bites you is almost always the same shape:

1. Someone declares ON DELETE CASCADE on a child table, correctly, because in the steady state that is what they want.
2. Time passes. The schema becomes load-bearing. The cascade becomes invisible because it never has to be thought about during normal reads and writes.
3. Someone, somewhere in the stack, writes a code path that, from the application's perspective, "updates" a parent row, but which, in the database, executes as a DELETE plus an INSERT.
4. The cascade fires. Child data is gone. The caller has no idea, because nothing in their code said the word DELETE.

The defenses are not exotic. They are the same family of defenses you would apply to any case where a small action at one layer has a large effect at another.

• Audit your cascades the way you audit your auth. Every ON DELETE CASCADE in your schema is a piece of code that runs on writes you may not be looking at. List them. Know them. Treat additions to that list as a real review, not a default.
• Treat parent identity as load-bearing. Any operation that changes a parent row's primary key, or that an ORM might model as a delete-and-reinsert, is in practice a DELETE on the parent. If your schema has cascades hanging off that parent, you are not doing an update. You are doing a controlled wipe of every child.
• Make destructive operations visibly destructive. A write that is going to remove rows in another table should not be expressible as the same kind of call that adds a column to a list. We are moving toward explicit, named operations for the changes that have downstream cascade implications, so the caller sees the verb and not just the noun.
• Know your recovery path before you need it. We did not restore. We re-ran, because this was experiment data and re-running was cheap. That was a property of the dataset, not a plan. Ask what you would have done if these had been customer rows, and whether you have ever rehearsed it.

A grounded close

The thing we keep coming back to about this bug is that the database did nothing surprising. It honored the schema. The schema was a thing we had written. The gap was between what the schema said it would do on a DELETE and what our code, two layers up, was actually issuing as a DELETE without naming it that way.

If you are building on a relational store, and especially if you are building the kind of experiment, evaluation, or agent system where parent-child relationships run several layers deep, the question to ask about every cascade in your schema is the same question we should have asked about ours: under what circumstances, including ones that do not look like deletions from the outside, can this cascade fire. If you cannot list those circumstances, the cascade is going to surprise you at some point. We would rather you find out from a blog post than from a missing trials table.