The Soft Delete That Deleted Nothing

A client implemented soft deletes across their entire data model for compliance reasons. Six months later, a GDPR request revealed they hadn't actually deleted anything — and the workarounds had introduced three new bugs.

The request seemed straightforward. A B2B SaaS client in the HR space wanted to add soft deletes across their platform. Their reasoning: they needed an audit trail for compliance, and their customers occasionally asked to restore accidentally deleted records. Hard deletes made both of those things impossible.

So the team added a deleted_at timestamp column to every table that held business data. Twenty-three tables in total. Every DELETE query became an UPDATE. Every SELECT query gained a WHERE deleted_at IS NULL clause. The ORM got a default scope. Done in a week, reviewed, merged, deployed.

For about six months, it worked fine. Then three things happened in rapid succession.

The email that couldn't exist

A customer admin deleted an employee account — standard offboarding flow. Two weeks later, the same employee was rehired. The admin tried to create a new account with the same email address.

ERROR: duplicate key value violates unique constraint "users_email_key"
Detail: Key (email)=(j.martinez@example.com) already exists.

The soft-deleted record still occupied the unique constraint. The user row was gone from every UI and API response, but PostgreSQL didn't care about the ORM's default scope. As far as the database was concerned, that email was taken.

The first fix was a partial unique index:

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

This worked, but it meant dropping the original unique constraint — and now nothing prevented two soft-deleted records from having the same email. That mattered later when we needed to figure out which "deleted" J. Martinez was which.

The team applied the same pattern to eleven other tables with unique constraints. Each one required a migration, a corresponding change in the application validation logic, and careful testing. The "done in a week" project was now in its second month of follow-up work.

The query that forgot

About three months in, a new developer joined the team and built a reporting endpoint. They wrote a raw SQL query — the ORM's query builder felt too limiting for the aggregation they needed. The query joined four tables. It did not include WHERE deleted_at IS NULL on any of them.

The report shipped to customers. For six weeks, customer-facing analytics included data from deleted employees. Nobody noticed because the numbers looked plausible — the counts were slightly higher than expected, but not enough to trigger alarm.

A customer's HR director caught it during an audit. They were comparing headcount numbers between the platform and their payroll system and found a discrepancy of 34 employees. All 34 had been terminated months earlier.

The team added a database view for each table that filtered out soft-deleted records, and pointed the reporting queries at those views. It helped. It also meant maintaining two mental models of each table — the "real" table with all records, and the "clean" view that most code should use.

The GDPR request that broke the illusion

Then came the email from a customer's DPO: a formal data subject erasure request under GDPR Article 17. An employee wanted their personal data deleted. Actually deleted. Not flagged with a timestamp and kept in the database indefinitely.

The client's legal team confirmed: soft deletes did not satisfy the right to erasure. The data was still there, still queryable with the right access, still included in database backups. Marking a row with a timestamp is not deletion in any legal sense that matters.

This was the moment the team realized they'd conflated two different requirements. The audit trail needed for compliance was about business events — who did what, when. It didn't require keeping the original data in place. The ability to restore records was a convenience feature, useful for maybe a week after deletion, not six months.

What we actually built

We spent two weeks redesigning the approach. The result had three pieces:

An event log captured the business-relevant facts before any deletion: who deleted what, when, and the key attributes that might be needed for audit purposes. This was append-only and contained no personal data that would itself be subject to erasure requests.

A grace period of 30 days used the existing soft delete mechanism. During this window, records could be restored. After 30 days, a scheduled job performed hard deletes and scrubbed the associated personal data.

For the GDPR erasure flow specifically, we skipped the grace period entirely. A deletion request triggered immediate anonymization of personal fields followed by a hard delete on the next nightly run.

-- Anonymize before hard delete
UPDATE users SET
  email = 'redacted-' || id || '@removed.invalid',
  full_name = 'REDACTED',
  phone = NULL
WHERE id = $1;
 
-- Hard delete in nightly job
DELETE FROM users
WHERE deleted_at < NOW() - INTERVAL '30 days';

The partial unique indexes stayed because they were genuinely useful during the grace period. The database views stayed because they'd already proven their worth for reporting. But the indefinite soft delete — the pattern that kept every record forever — was gone.

The uncomfortable part

The original soft delete implementation wasn't wrong in isolation. Each decision made sense at the time. Adding deleted_at is a well-known pattern. Filtering in the ORM is standard practice. The team moved fast and shipped something that mostly worked.

The problem was treating soft deletes as a universal solution to three distinct problems: audit trails, accidental deletion recovery, and data retention compliance. Each of those has its own requirements, its own lifecycle, and its own constraints. Collapsing them into a single boolean-ish column meant none of them were handled well.

I've seen this pattern at four different clients now. The trigger is always the same: someone asks "what if we need to undo a delete?" and the answer is always "just add a deleted_at column." It's one of those patterns that feels safe precisely because it defers every hard decision to later. The data stays. The schema barely changes. Nobody has to think about data lifecycle policies or retention windows or what "deleted" actually means in a legal context.

Until they do.