Making delete reversible: soft deletes, purge ordering and the keys we never reuse

The bug report said: "deleting one project deleted all of them."

It was not true. The delete was correctly scoped to one row. But the investigation turned up something worse than the report. Deleting a project in Reliable was instant, total and irreversible. It took every event, session, error, user and replay with it, and it left files in object storage that nothing would ever clean up. The user in the report had simply misclicked, and nothing could bring their data back.

This post is about how we made delete reversible for 30 days, what had to change for that to be safe, and the ordering rule that makes the final purge survive crashes.

#What delete used to do

A frontend project in Reliable is the root of a large tree. Events, sessions, error groups, end users and session replays all reference it, and all of those foreign keys were declared ON DELETE CASCADE. Deleting the project row made Postgres walk the whole tree and delete it in one statement.

Two parts of that tree do not live in Postgres. Session replays and uploaded sourcemaps are files in R2, our object storage. The database only holds their storage keys. The cascade deleted those rows along with everything else, so by the time anything could have cleaned up the files, the list of which files to clean up was gone. Every deleted project left orphaned files in the bucket, invisible and still billed.

%% caption: Figure 1. One project is the root of everything under it. Two branches point at files outside the database.
flowchart TD
    P["frontend_projects row"] --> E["events, sessions, error groups, end users"]
    P --> R["session_replays rows"]
    P --> L["releases"]
    L --> M["sourcemap_assets rows"]
    R -.->|"storage key"| RB["replay files in R2"]
    M -.->|"storage key"| SB["sourcemap files in R2"]

#What we wanted instead

We wrote the requirements down before picking a design:

1. Reversible for a while. A misclick should be undoable for 30 days.
2. Gone immediately from every point of view. A deleted project must vanish from the dashboard and stop accepting data from the SDK straight away.
3. Eventually really gone, including the files in R2.
4. Safe under crashes and races. A job dying halfway, or two people clicking at once, must not lose data we meant to keep or keep data we meant to lose.

#Why one flag on one row is enough

There are a few common ways to build this. You can move deleted rows into archive tables, or schedule the real delete to happen in 30 days, or mark rows as deleted and leave them in place.

Archive tables would mean copying the whole tree, which includes some of our largest tables, out on delete and back in on restore. A delayed delete alone satisfies requirement 1 but not requirement 2, because the data stays visible until the timer fires.

Marking is the simplest of the three, and the tree structure makes it cheaper than it sounds. Every child row is only reachable through its project. So we do not need to mark the children at all. We add one column to the root:

ALTER TABLE frontend_projects ADD COLUMN deleted_at TIMESTAMPTZ;   -- NULL = live

Deleting sets deleted_at = NOW(). Nothing cascades, so every foreign key keeps pointing at a parent that still exists and no relationship has to change. As long as every way into the tree checks the root, the whole tree disappears with a single-row update. Restoring is setting it back to NULL.

#The part that is easy to get wrong: every entry point

The first commit only marked the row. That is not enough, because a marked row is still a row. The dashboard would keep listing the project, and worse, the ingest middleware looks projects up by their public key. It would have kept accepting events from the SDK and writing them into a project the user believed was gone.

The next commit went through every place that resolves a project from user or SDK input and added deleted_at IS NULL. There were twelve: the ingest guard (the important one), the dashboard read paths, the metrics resolver, token management, replays, sourcemap lookups, and two joins that reach projects through error events.

Three places were left alone on purpose, and the commit says why. The WebSocket metrics service, the WebSocket sketch recorder and the sourcemap pruner all work from the internal numeric ID, and they only ever run downstream of a gate that already filters. The pruner in particular has to keep working on projects that are on their way out.

The lesson generalises: with soft deletes, the flag is the easy part. The real work is the list of entry points, and every new query that resolves a project has to carry the same filter.

#Uniqueness means something different now

Project slugs, like marketing-site, had to be unique within an account. With soft delete, a deleted project would keep holding its slug forever, and nobody could ever create a new project with that name. So the constraint became a partial unique index that only covers live rows:

CREATE UNIQUE INDEX uq_frontend_projects_master_slug_live
    ON frontend_projects (master_project_id, slug)
    WHERE deleted_at IS NULL;

Two other columns went the opposite way on purpose. A project's uuid and public_key stay unique across all rows, deleted or not, forever. The public key is compiled into the JavaScript of apps that are still running in browsers. If a deleted project's key were ever reissued, a stranger's events would start flowing into someone else's account. Reserving those values permanently is the point.

#Restoring without races

The partial index creates a new edge case. While a project sits in the bin, someone can create a new project with the same slug. Restoring the old one would then break the index.

The restore service checks for a live project with that slug first, so it can return a message the user can act on: rename the other project, then restore. A check followed by an update is a race, though. Another project could claim the slug in the milliseconds between the two. So the update also catches Postgres error 23505 (unique violation) and turns it into the same friendly message. The update itself includes AND deleted_at IS NOT NULL, so two people restoring at the same moment cannot both succeed.

Delete is guarded the same way. Deleting a project that is already deleted returns a 404 instead of quietly succeeding, because any caller that gets there is working from stale state and should know.

#The purge, and the one rule that makes it safe

Data does have to go eventually. A repeating job runs once every 24 hours. It has a fixed job ID, so however many server instances start up, only one sweep is ever scheduled. It finds projects deleted more than 30 days ago (the retention is a setting), using a partial index that only covers deleted rows. Then for each project:

1. Collect the storage keys of its replay files and, through its releases, its sourcemap files. Replays record their own bucket, so keys are grouped by bucket.
2. Delete those files from R2 in batches of 1,000, the most one S3-style DeleteObjects request accepts. If a batch fails, fall back to deleting its keys one at a time, so one bad key does not block the rest.
3. Only if every file is gone, delete the project row. That fires the original cascade and removes the rest of the tree. If any file refused to go, the row stays and tomorrow's sweep tries again.

%% caption: Figure 2. Files first, row last. The row is the only record of which files belong to the project, so it outlives them.
flowchart TD
    A["Find projects deleted more than 30 days ago"] --> B["Collect replay and sourcemap keys"]
    B --> C["Delete files from R2, 1,000 keys per request"]
    C --> D{"Every file deleted?"}
    D -->|"yes"| E["DELETE the project row, cascade removes the tree"]
    D -->|"no"| F["Keep the row, retry on the next daily sweep"]
    C -.->|"process crashes"| F

The order matters more than anything else in this post. Think of it as an invariant: as long as a file might still exist, the row that lists it still exists.

• Files first, then the row. If the job crashes at any point, the row is still there, so the next sweep finds the project and runs again. Deleting a file that is already gone is a no-op in S3 semantics, so running again is always safe.
• Row first, then files. If the job crashes between the two steps, the only list of files is gone. They are stranded forever. That is exactly the bug the old delete had, just slower.

Put together, deleted data is gone between 30 and 31 days after the click (30 days of retention plus up to one day waiting for the next sweep), or later if R2 refuses some deletes and they have to be retried.

%% caption: Figure 3. The life of a project row.
stateDiagram-v2
    [*] --> Live
    Live --> Deleted: delete (deleted_at set)
    Deleted --> Live: restore within 30 days
    Deleted --> Purged: daily sweep after 30 days, files first
    Purged --> [*]

#Telling users the truth

None of this matters if the product still acts as if delete is final. The confirm dialog used to say the delete could not be undone. It now says the project can be restored for 30 days. A "Recently deleted" panel on the dashboard lists deleted projects with the days left and a Restore button. It shows up even when no live projects remain, which is exactly when someone is hunting for a project that vanished. Deleting also refreshes that list straight away, so the project visibly moves from live to restorable instead of just disappearing.

#What we got wrong

• The original hard delete. Cascading deletes are convenient, but combined with a one-click button they turned a misclick into data loss, and they destroyed the only record of which files to clean up.
• Marking without filtering. The first soft-delete commit alone would have left deleted projects accepting new events. A soft delete is only as good as the list of entry points that respect it.

#What to take away

• If your data forms a tree with one root, a soft delete needs one column on the root, plus a filter on every entry point into the tree.
• Soft delete changes what "unique" means. Decide on purpose which values are freed on delete and which must stay reserved forever.
• When deleting rows that point at external files, delete the files first and the rows last, and make every step safe to repeat.
• Check-then-act needs a backstop. Catch the constraint violation too.