Soft Delete Done Right: Predicates, RowsAffected, and Restore
Soft delete is not just DELETE swapped for UPDATE. Predicates on every query, a RowsAffected alarm, and a mirrored restore guard keep it honest.
TL;DR
Switching DELETE to soft delete with an UPDATE on deleted_at left deleted rows visible because SELECTs weren't filtered. Fixing it means adding deleted_at IS NULL to queries, checking RowsAffected to return 404, and mirroring logic for restores. You also need to invalidate cache on every state change, otherwise complexity and leaks quickly outweigh benefits.
I just ran the test suite for the delete category endpoint on my production server. The results were all green. But when I opened the admin page to check the category list, the category that should have been gone was still right there, complete with all its data.
Initially, I thought this was just a Redis caching issue or a serializer holding onto old state. I even manually flushed the cache and restarted the service, but the data still appeared. I started wondering if this was a database replication bug. Or maybe a trigger accidentally rolled back the delete transaction? After digging deeper into the logs, I finally realized the problem wasn't in the infrastructure, but in the application logic itself.
My first guess at the time was: "Oh, maybe the DELETE query failed silently, or the ORM didn't actually send the delete command to the database." I immediately checked the query logs. It turned out the query running wasn't DELETE FROM categories, but UPDATE categories SET deleted_at = NOW().
Ah, soft delete. I had intentionally changed the delete logic to soft delete a few days earlier so data wouldn't be permanently lost. But I forgot one crucial thing: soft delete isn't just about changing a DELETE command to an UPDATE.
The Initial Guess That Missed the Mark
It turns out, soft delete forces us into a strict state machine mode. Every SELECT query that was previously safe is now a ticking time bomb if we forget to add the deleted_at IS NULL predicate.
If we forget to add that condition in even one endpoint, data that should be hidden will leak to the user. This isn't just about code neatness; it's about data security. Research from Brandur [1] and analysis from Cultured Systems [2] also remind us that soft delete can weaken foreign key and unique constraint guarantees if not handled with discipline.
This often becomes a classic trap. Developers think, "The data is still there, so the relations remain safe." However, from the user's perspective, that data is gone. Imagine a products table with a foreign key to categories. When a monthly report is generated and includes categories that have been deleted, that's where business problems start. Data that should be isolated in the "trash" gets counted in aggregations because the database still sees the relation as valid.
The Solution: Predicate Discipline and RowsAffected
So, how do we fix it? First, the update query for deletion must explicitly check that the data hasn't already been deleted.
UPDATE categories
SET deleted_at = NOW()
WHERE id = ? AND deleted_at IS NULL;In Go, we can't just rely on the err variable from db.Exec. We must check how many rows were actually updated using RowsAffected(). This is the official way from the database/sql package to know the execution impact [3]. If rows == 0, it means the ID doesn't exist or was already deleted, and we must return a 404 error.
result, err := db.Exec(query, id)
if err != nil {
return err
}
rows, _ := result.RowsAffected()
if rows == 0 {
return ErrCategoryNotFound
}It's worth noting that in MySQL, a standard DELETE statement does return the number of deleted rows [4]. But since we are using UPDATE for soft delete, RowsAffected() becomes the only valid indicator that our operation actually touched the intended data.
The Restore Logic That Often Gets Overlooked
The problem doesn't end there. What if the user requests to restore a deleted category? We need a mirror query that does the opposite.
UPDATE categories
SET deleted_at = NULL
WHERE id = ? AND deleted_at IS NOT NULL;Notice the WHERE clause. We must ensure deleted_at IS NOT NULL. Why? Because without it, we might accidentally reset deleted_at to NULL for data that is already active. This is useless and highly risks causing logic inconsistencies in larger systems.
One more thing that often gets overlooked: after a successful restore, we must bust or clear the public cache storing the category list. I once encountered a case where the restore succeeded in the database, but the list category endpoint still didn't show the data for 15 minutes because the cache TTL hadn't expired. The user complained, "Why isn't the category I restored showing up?" Even though the database was correct. Expensive lesson: every operation that changes the deleted_at state, whether to NOW() or NULL, must trigger synchronous cache invalidation, not just rely on natural expiration time.
Honestly, I personally prefer hard delete for data that truly lacks important historical relations. Soft delete adds complexity to every layer of the application. But if the project genuinely needs a "trash" feature or audit trail, we have no choice but to be disciplined about adding the predicate to every query. There are no shortcuts.
The concept of soft delete done right isn't about having a cool restore feature, but about how strictly we guard state consistency across the entire codebase.