Loyal CASCADE: When Deleting an Account Takes the Content With It

The staging API returned 200 OK for a delete that should have been refused. I had asked for one citizen account to be removed, and the request went through without a whisper of resistance. The ErrHasContent guard, built precisely for this moment and ready to answer 409, never fired. Worse: every review, registration submission, and report that account had ever created disappeared with it.

My first guess was that the validation layer had rotted, or that some execution path bypassed the check entirely. I traced the delete flow from controller to repository and found everything in place: the guard existed, its condition existed, its call site existed. The problem lived one layer lower, in a contract that speaks before application code does. The foreign keys on reviews, registration_submissions, and reports still said ON DELETE CASCADE. InnoDB finished removing the child rows before the application ever got the chance to look at them. A guard that never receives its evidence is not a broken guard; it is a guard whose evidence was destroyed before the trial began.

Move the First Line of Defense into the FK Contract

The fix was not another layer of application validation. It was reversing the order of authority: the database refuses first, and the application translates that refusal. Migration 000067 flipped the three content-table foreign keys from CASCADE to ON DELETE RESTRICT, keeping the constraint names identical (fk_reviews_user, fk_regsub_user, fk_report_user) so the down migration is its exact mirror [1]. Since then, deleting an account that still owns content dies at the storage engine with error 1451, Cannot delete or update a parent row: a foreign key constraint fails [3], and only at that point does ErrHasContent finally fire, translating the rejection into an honest 409.

The uncomfortable part came from rereading the documentation: RESTRICT is not an exotic choice, it is MySQL's default. The manual states that for an unspecified ON DELETE or ON UPDATE, "the default action is always RESTRICT" [1], and even NO ACTION is treated as RESTRICT because MySQL has no deferred constraint checking [2]. Anyone who writes CASCADE is consciously stepping away from the safe default. In this project, that choice was planted by two old migrations, 000032 and 000054, back when the prototype was young and nobody had asked yet what happens to history.

The FK policy is now explicit per data class instead of uniform. Citizen content uses RESTRICT, because losing it means losing history. otp_codes keeps CASCADE, because one-time codes are ephemeral and dying with the account is exactly right. Actor attribution uses SET NULL, following the 000063 precedent: the event survives, the identity of the actor is released, and the record stays intact. One table, one decision, one reason that can be explained to anyone who asks why the data is gone. The policy is now written into the project's database rules, not just in someone's head.

Prove It in Both Directions, Not One

Changing the action in the up migration is half the job; the other half is proving the road back. Because the constraint names stayed identical, the down migration simply reverses each DROP FOREIGN KEY and ADD CONSTRAINT pair back to CASCADE, and both directions are checked through information_schema: after the up migration the three constraints must read RESTRICT, and after the down migration they must read CASCADE again. Five integration tests close the loop, one scenario per content table: seed a user with content, call delete, expect ErrHasContent, then confirm the account still exists and the content rows are untouched.

The pattern is cheap to write and expensive to skip: a reactive guard in the application without a refusal at the database level is a check that only works on data the database has not touched yet. Once CASCADE is in play, execution order always favors the database, never the validation. Content that carries history must not die silently with its author, and if the application guard is the only line of defense, it is exactly as strong as the data that has not been deleted yet.