Delete the row before the file

This site's admin can upload images for each project. The images live in object storage; the database holds a row per image with its URL. Deleting an image means removing both. It seems like it should not matter which goes first.

It matters a great deal, because the two operations cannot happen atomically — one of them will eventually succeed while the other fails. The order decides which failure you get.

The two failure modes

Delete the file first, then the row. If the row deletion fails, the database still points at a file that no longer exists. Any page that renders that record shows a broken image. If the project is published, visitors see it.

Delete the row first, then the file. If the file deletion fails, the file sits in storage with nothing pointing at it — an orphan. Nobody sees it. It costs a fraction of a cent per month.

Put that way, the choice is easy. The harm is asymmetric: a broken published reference costs credibility, an orphaned file costs almost nothing. So everything on this site that removes a file changes the database first.

Applied to the three operations

Replacing an image. Upload the new file first. Then update the row to point at it — in a single atomic statement that also returns the previous URL. Only after that has committed, delete the old file. At no moment does the row point at something missing.

Deleting an image. Delete the row, returning its URL in the same statement. Then delete the file.

Deleting a whole project. Unpublish it first and refresh the public cache, so no page references its images any more. Then delete the files. Then delete the row.

Keeping the orphan temporary

"Prefer the orphan" is only a good trade if orphans do not accumulate forever. So when a file deletion fails, the URL goes into a small cleanup queue rather than being forgotten.

That queue is retried after later admin actions and swept by a nightly scheduled job. Each attempt is counted, the last error is kept, and after five failures an entry stops being retried but stays recorded — so a persistent problem is visible instead of silently dropped.

One detail matters here: a failed file deletion is not reported to the editor as an error. The change they asked for — removing the image from the project — succeeded. Telling them it failed would be both inaccurate and confusing. The leftover is an operational concern, handled by the queue.

The general principle

Whenever two stores must change together and cannot do so atomically, list both possible partial failures and ask which one you would rather have. Then order the operations so that is the one you get, and add a way to clean up after it.

The question is never whether something will fail halfway. It is which half you can live with.