The Twelve Thousand Records I Did Not Delete

I was in my first year at a marketing company, the fast kind, where everything was due yesterday and nobody had time to explain the systems to the new guy.

I had been handed a client’s contest submissions to review. I was still learning the site. I ran a delete against roughly twelve thousand records, looked at the result, and understood — with total clarity — that I had just destroyed production data for a paying client.

I want to be accurate about what happened next, because the technical part of this article depends on it. I did not calmly assess the situation. I sat there and seriously considered picking up my bag and walking out of the building, because there was no version of the next conversation I could survive.

Then, breathing through what I can now comfortably call a panic attack, I checked again.

It was QA. I had confused the two environments. Production was untouched, the client’s submissions were fine, and nobody ever knew it happened.

The relief was physical. And the lesson landed exactly as hard as if I had actually done it.

From that day I have never written an outright hard delete. Everything soft deletes or archives.

The instinct was right, and it was not free

I want to be honest about what that vow actually bought me, because “always soft delete” gets repeated as though it settles the question. It does not. It relocates it.

Soft deleting means adding a column and agreeing, forever, that every query in the system must respect it:

ALTER TABLE submissions ADD COLUMN deleted_at timestamptz;

That is one line of DDL and an unbounded ongoing obligation. Most queries will honor it. The ones that forget produce the strangest class of bug I have encountered in production: data that is simultaneously gone and present, depending on which code path you arrive through.

A deleted submission missing from the list view but included in the export. A count that disagrees with the rows beneath it. A notification sent to an account the user believes they closed. None of these look like deletion bugs when they get reported. They look like unrelated inconsistencies, filed separately, fixed separately.

Three things soft deletes break that nobody warns you about

Unique constraints stop working the way you meant them. A user soft deletes their account and tries to sign up again with the same email. The row is still there, so the unique index rejects it. The fix is a partial index, and it is the kind of thing you only learn by shipping the bug first:

CREATE UNIQUE INDEX users_email_active_idx
  ON users (email)
  WHERE deleted_at IS NULL;

Indexes get worse as the table fills with corpses. Every query now carries WHERE deleted_at IS NULL, and on a table where most rows are deleted, the planner is walking past a lot of dead weight. Partial indexes fix this too, and almost nobody adds them until the query gets slow:

CREATE INDEX submissions_contest_active_idx
  ON submissions (contest_id, created_at DESC)
  WHERE deleted_at IS NULL;

Framework-level defaults hide the filter until it bites. In Rails, the tempting move is a default scope:

class Submission < ApplicationRecord
  default_scope { where(deleted_at: nil) }
end

This works beautifully and then surprises you, because a default scope follows the model everywhere — into joins you did not write, into unscoped blocks someone added to fix an unrelated bug, into associations that now silently exclude rows a report depends on. I still reach for it, but I no longer pretend it is free. An explicit .active scope is more typing and far easier to reason about six months later.

The part my vow did not cover

Here is what took me much longer to understand. Not hard deleting protects you from the mistake I nearly made. It does nothing about the harder question, which is what should disappear, and from where.

Because a record does not live in one place in any system old enough to matter. That submission row exists in the primary database, the read replica, last night’s backup, the search index, the analytics warehouse, whatever the CRM synced, and the CSV somebody scheduled to email themselves every Monday two years ago.

Setting deleted_at addresses exactly one of those.

That matters now in a way it did not back then, because “delete my data” arrived as a legal obligation with a deadline rather than as a feature request. A soft delete is not a deletion in that sense. It is a flag on one copy of the data, in one system, and the compliance question is about all of them.

And it runs the other direction too. Some records must be retained — financial history, audit trails that exist specifically to resist tampering. “Erase this person” and “preserve an immutable record of what occurred” are both real requirements, and reconciling them is a modeling decision, not a query.

What I actually do now

The vow held, but it grew a second half. Soft delete by default, and decide up front what the data is:

• User-owned and reversible — soft delete. Recovery is a real product need, and the fifteen-minute “undo” window justifies the column on its own.
• Shared — do not delete it with the owner. When someone leaves a workspace, their comments and edits are entangled with other people’s work. Anonymize the author, keep the artifact, and make that decision deliberately rather than discovering it in a cascade.
• Historical or financial — never delete, and stop calling it deletion. It is an archive with a retention policy, and it should be modeled as one.
• Genuinely required to vanish — hard delete, on purpose, with the downstream copies enumerated and handled. This is the case my younger self’s rule could not accommodate, and pretending otherwise just means the obligation goes unmet.

The last one is the concession my younger self would not have made. He was right that irreversible deletion is where juniors get destroyed and systems lose data they needed. He was wrong that avoiding it is a complete answer.

Closing

I have told this story a few times, and people usually expect it to end with an outage.

It ends with nothing happening at all. I confused two environments, felt the full weight of a consequence I had not actually caused, and changed how I write software permanently on the strength of a mistake that cost the company zero dollars.

That is the part worth keeping. The lesson did not require the catastrophe. It only required believing, for about ninety seconds, that the catastrophe was real.

Nearly twenty years later I still write deleted_at. I just no longer think the column is the whole answer.