Soft Deletes: The Feature That Costs More Than It Saves

Soft deletes look simple: add a deleted_at column, filter it in every query, ship it. They're in every ORM tutorial, every SaaS boilerplate, every "how to build a multi-tenant app" guide. They feel like free insurance. In practice, they're a slow-motion schema disaster that corrupts indexes, breaks constraints, and creates invisible data leaks that compound for years.

The appeal is real — hard deletes are scary, and deleted_at gives you an undo button. But there's a significant gap between what soft deletes promise and what they cost at scale.

The Index Problem

When you add deleted_at to a table with millions of rows, every existing index becomes wrong. A users table index on email now covers both active and deleted rows — but 99% of your queries only care about the active ones. The index grows with every tombstone you accumulate, and selectivity degrades over time.

PostgreSQL's partial indexes solve this, but they introduce a new invariant you must maintain forever:

-- ❌ Covers all rows — makes every active-user lookup scan deleted junk
CREATE INDEX idx_users_email ON users(email);
 
-- ✅ Partial index — only indexes rows you actually query
CREATE INDEX idx_users_email_active ON users(email)
WHERE deleted_at IS NULL;

The catch: the query planner only uses a partial index if the query's WHERE clause implies the index predicate. Miss deleted_at IS NULL in a single query and you fall back to a sequential scan. Every developer, every migration script, every analytics query must remember the filter — or the index is decorative.

Unique Constraints Break Silently

This is where teams get badly burned. You have a unique constraint on users(email). A user deletes their account. Three months later, someone registers with the same address. Constraint violation — even though there's no active record with that email.

-- ❌ Prevents re-registration — the unique constraint sees deleted rows too
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);
 
-- ✅ Partial unique index — enforces uniqueness only among active rows
CREATE UNIQUE INDEX users_email_active_unique
  ON users(email)
  WHERE deleted_at IS NULL;

PostgreSQL supports partial unique indexes and this works cleanly. MySQL does not — you're forced to enforce uniqueness in application code, which means race conditions under concurrent load. Two requests can both pass the check before either commits.

Query Pollution Compounds Over Time

The moment deleted_at IS NULL becomes a requirement, it belongs in every query. Every ORM scope, every raw SQL, every subquery, every join condition. Miss it once and you're returning deleted data to users.

Teams address this in one of three ways: default ORM scopes (which cause confusion when you legitimately need deleted rows), trusting developers to remember (they won't), or views that filter by default (reasonable, but adds another layer of indirection). None of these fully close the gap.

// ❌ Easy to forget — forgetting exposes deleted records silently
const users = await db
  .select()
  .from(usersTable)
  .where(eq(usersTable.organizationId, orgId));
 
// ✅ Explicit default scope baked into a repository boundary
async function getActiveUsers(orgId: string): Promise<User[]> {
  return db
    .select()
    .from(usersTable)
    .where(
      and(
        eq(usersTable.organizationId, orgId),
        isNull(usersTable.deletedAt),
      ),
    );
}

Even with a repository layer protecting application queries, raw SQL still surfaces in migrations, one-off scripts, admin tooling, and analytics pipelines. The filter must exist everywhere it's needed, or it's not reliable.

The Cascade Problem

Foreign keys with ON DELETE CASCADE are one of the best features relational databases provide — delete a parent row and children disappear automatically, atomically, without any application logic. Soft deletes disable this entirely. The database thinks nothing was deleted, so cascades never fire.

Now you need application-level cascade logic. Delete an organization? Find all its users, workspaces, invitations, and API keys, then soft-delete each entity in a transaction. And update that logic every time a new child table is added.

// ❌ Manual cascade — someone will add a new table and forget this function
async function softDeleteOrganization(orgId: string, tx: Transaction) {
  await tx.update(usersTable)
    .set({ deletedAt: new Date() })
    .where(eq(usersTable.organizationId, orgId));
 
  await tx.update(workspacesTable)
    .set({ deletedAt: new Date() })
    .where(eq(workspacesTable.organizationId, orgId));
 
  // Invitations table added six months later — already a leak
  await tx.update(organizationsTable)
    .set({ deletedAt: new Date() })
    .where(eq(organizationsTable.id, orgId));
}

Every new child table introduced after this function was written is a potential data leak. The database enforced referential integrity for free. You now maintain it by hand across an unbounded number of entity types.

What to Use Instead

Before reaching for deleted_at, ask what problem you're actually solving:

Archive Tables

For compliance requirements, a background job moves deleted rows to a users_archive table before hard-deleting from users. The main table stays clean, constraints work, indexes stay lean, and the archive is write-once and never touched in the hot path.

-- Archive first, then hard delete — main table stays clean
BEGIN;
 
INSERT INTO users_archive
SELECT *, NOW() AS archived_at
FROM users
WHERE id = $1;
 
DELETE FROM users WHERE id = $1;
 
COMMIT;

Append-Only Audit Logs

If the requirement is "what did this record look like six months ago?", an audit log gives you full history — not just whether the row was deleted. You can reconstruct any past state by replaying events, and the production table has no tombstones.

// Write the event, then hard delete — full history without polluting the table
async function deleteUser(userId: string, actorId: string, tx: Transaction) {
  const user = await tx.query.usersTable.findFirst({
    where: eq(usersTable.id, userId),
  });
 
  await tx.insert(auditLogTable).values({
    entityType: "user",
    entityId: userId,
    action: "deleted",
    payload: JSON.stringify(user),
    actorId,
    createdAt: new Date(),
  });
 
  await tx.delete(usersTable).where(eq(usersTable.id, userId));
}

When Soft Deletes Are Acceptable

They're not always wrong. A deleted_at column is defensible when the table is small and bounded in growth, when you're explicitly building a recycle-bin feature with a scheduled expiry job, or when the uniqueness and cascade concerns genuinely don't apply to the entity. The mistake isn't the pattern itself — it's applying it by default to every table in the schema without accounting for what it breaks.

Key Takeaways

1. Partial indexes help but require total discipline — every query must include the filter predicate or the index is ignored
2. Unique constraints and soft deletes are incompatible on MySQL and require partial unique indexes on PostgreSQL — check your database before committing
3. Application-level cascades rot — every new child table is a potential data leak unless the cascade function is kept in sync
4. Archive tables solve the compliance requirement cleanly — main table stays normalized, archive is append-only and off the hot path
5. Audit logs give you full history instead of just a deletion timestamp — and they compose well with event sourcing
6. Match the tool to the actual requirement: recycle bin → deleted_at with expiry; compliance → archive table; audit trail → append-only event log