Soft Deletes vs Hard Deletes: When to Actually Use Each

Every database has a graveyard. The question is whether you bury your records with a headstone (soft delete) or toss them in a volcano (hard delete). We've done both, sometimes made the wrong call, and we're here to save you from the regret of a 2am 'wait, where did that user's data go?' message from your co-founder.

What We're Actually Talking About

A hard delete removes the row from the database. Gone. SELECT * won't find it. No take-backs unless you have a backup. A soft delete keeps the row but marks it as deleted — usually with a deleted_at timestamp column — so you can filter it out of normal queries while keeping the data around for audits, undo operations, or when your customer emails you three weeks later saying they deleted the wrong thing.

// Hard delete — the nuclear option
await db.delete(users).where(eq(users.id, userId));

// Soft delete — the diplomatic option
await db
  .update(users)
  .set({ deletedAt: new Date() })
  .where(eq(users.id, userId));

// Then filter it out everywhere
await db
  .select()
  .from(users)
  .where(isNull(users.deletedAt)); // easy to forget this part

That last comment is not a joke. The hardest part of soft deletes is the discipline of filtering deleted records out of every single query. Miss one, and you're serving zombie data to your users.

The Case for Soft Deletes

Soft deletes shine when your data has a lifecycle that matters beyond deletion. Think about these scenarios: a user deletes their account but comes back six months later and wants their history. A team member accidentally deletes a project. You need to comply with audit requirements that say you must show what happened to every record. In all these cases, hard deletes will hurt you.

• Undo/restore functionality — 'I deleted the wrong thing' is more common than you think
• Audit trails — who deleted what, and when
• Referential integrity without cascades — orders can still reference a deleted customer
• Analytics on historical data — churned users are still business data
• Regulatory compliance (GDPR adjacent, financial records, etc.)
• Multi-tenant apps where one tenant's mistake shouldn't be permanent

Here's the implementation we use in most SaaS apps. We add deleted_at to any table that needs soft delete behavior, and we lean on a utility to make filtering consistent:

// schema.ts — Drizzle example
import { pgTable, uuid, text, timestamp } from 'drizzle-orm/pg-core';

export const projects = pgTable('projects', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: text('name').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
  deletedAt: timestamp('deleted_at'), // null = alive, timestamp = dead
});

// db/utils.ts — reusable filter
import { isNull } from 'drizzle-orm';
import { projects } from './schema';

export const notDeleted = isNull(projects.deletedAt);

// Usage everywhere
const activeProjects = await db
  .select()
  .from(projects)
  .where(notDeleted);

// Restore is just an update
async function restoreProject(id: string) {
  return db
    .update(projects)
    .set({ deletedAt: null })
    .where(eq(projects.id, id));
}

The Costs Nobody Talks About

Soft deletes aren't free. The 'deleted_at IS NULL' filter has to live in every query, every ORM scope, every raw SQL you write. Forget it once and your deleted user shows up in search results. Add it to an index or forget to and your queries slow down. It's not hard to get right, but it requires consistent discipline across your entire codebase.

Unique constraints are another headache. If a user deletes their account and you soft-delete the row, their email is still 'taken' in the database. When they try to sign up again, your unique constraint blows up unless you handle it. You have a few options: use a partial unique index that only applies to non-deleted rows, or include deleted_at in the unique constraint. Postgres handles this well with partial indexes, but you have to know to do it.

-- Unique email only for non-deleted users
CREATE UNIQUE INDEX users_email_unique 
ON users (email) 
WHERE deleted_at IS NULL;

-- Now a deleted user's email is 'free' again
-- and someone else (or the same person) can re-register

Joins also get more complex. If you're joining orders to users, do you want deleted users' orders to appear? Probably yes for financial records. But do you want deleted users showing up in your active user counts? No. You end up adding that IS NULL filter to both sides of joins, and things get verbose fast.

When Hard Deletes Are the Right Call

Some data genuinely should be gone. Not for our convenience, but because keeping it creates real problems. GDPR's right to erasure is the obvious example — when a user exercises that right, you can't soft delete and call it a day. The data has to actually leave your systems, including backups within a reasonable timeframe. Soft deletes are legally insufficient here.

• GDPR 'right to erasure' requests — soft delete won't protect you legally
• Truly temporary data — session tokens, OTP codes, short-lived cache entries
• High-volume ephemeral records — logs, events, analytics rows (use retention policies instead)
• Data that has zero value once deleted — user notifications after they're read and dismissed
• When storage costs matter — millions of 'deleted' rows that will never be restored

Here's the thing about GDPR that trips people up: you can have soft deletes for operational data and a separate hard deletion process for compliance. When a user requests erasure, you run a job that scrubs the personal data from those soft-deleted rows (or hard deletes them), while potentially keeping anonymized records for analytics. Don't conflate 'deleting a record from the UI' with 'fulfilling a data deletion request'.

GDPR erasure means the personal data is gone, not just hidden. A soft delete with name, email, and address still intact is not compliance. It's a false sense of security.

A Pattern That Actually Works: The Hybrid Approach

In practice, most SaaS apps need both. Here's how we think about it: soft delete by default for anything user-created (projects, documents, team members, items). Hard delete for personally identifiable information when a compliance request comes in. And for some tables — like sessions, OTP codes, temporary tokens — just hard delete immediately, there's no reason to keep them.

// actions/delete-user.ts
import { db } from '@/db';
import { users, sessions, otpCodes } from '@/db/schema';

export async function deleteUserAccount(userId: string) {
  await db.transaction(async (tx) => {
    // Hard delete sessions and OTPs — no reason to keep these
    await tx.delete(sessions).where(eq(sessions.userId, userId));
    await tx.delete(otpCodes).where(eq(otpCodes.userId, userId));

    // Soft delete the user — keeps referential integrity for orders, etc.
    await tx
      .update(users)
      .set({
        deletedAt: new Date(),
        // Optionally anonymize PII immediately for soft-deleted users
        email: `deleted-${userId}@void.invalid`,
        name: 'Deleted User',
      })
      .where(eq(users.id, userId));
  });
}

// For GDPR erasure requests, go further:
export async function eraseUserData(userId: string) {
  await db.transaction(async (tx) => {
    // Hard delete the user row entirely
    await tx.delete(users).where(eq(users.id, userId));
    
    // Keep orders but strip personal data
    await tx
      .update(orders)
      .set({ billingName: 'Erased', billingEmail: null })
      .where(eq(orders.userId, userId));
  });
}

This pattern lets you have useful 'undo' behavior for accidental deletions while still being able to properly handle data erasure requests when they come in. The key insight is that deletion from the UI (soft delete) and deletion for compliance (hard delete of PII) are two different operations with different requirements.

Don't Forget to Actually Clean Up

One thing that bites teams: soft deletes without a cleanup strategy turns into a data hoarding problem. If you soft delete a million rows a year and never clean them up, after five years you've got five million phantom rows slowing down your queries and eating storage. Set a retention policy from day one.

// A cleanup job — run this as a cron every night
// Delete rows that have been soft-deleted for more than 90 days
export async function purgeOldSoftDeletes() {
  const cutoff = new Date();
  cutoff.setDate(cutoff.getDate() - 90);

  const result = await db
    .delete(projects)
    .where(
      and(
        isNotNull(projects.deletedAt),
        lt(projects.deletedAt, cutoff)
      )
    );

  console.log(`Purged ${result.rowCount} old soft-deleted projects`);
  return result.rowCount;
}

// In Next.js, wire this up to a route handler or an external cron
// POST /api/cron/purge-deleted  (protected with a CRON_SECRET header)

90 days is a common window. Long enough that someone can recover a mistake, short enough that you're not storing stale data indefinitely. Adjust based on your business — financial records might need 7 years, user-generated content might only need 30 days.

The Decision Checklist

When you're adding a delete operation to your app, run through these questions before defaulting to one or the other:

• Can a user accidentally delete this and want it back? → Soft delete
• Do you need an audit trail of what got deleted? → Soft delete
• Does this data reference other records that should stay intact? → Soft delete
• Is this PII that users have a right to erase? → Hard delete (or anonymize then hard delete)
• Is this ephemeral data with no long-term value? → Hard delete
• Will keeping this row cause unique constraint problems you can't easily work around? → Hard delete
• Do you have a GDPR compliance story if you soft delete? → Think harder before choosing soft delete for user data

Most apps end up with a mix. Core user content gets soft deleted with a restore window. Sessions and temp data get hard deleted immediately. PII gets scrubbed when compliance requires it. This isn't over-engineering — it's just being honest about the fact that different data has different deletion semantics.

We've built this pattern into the database schemas that ship with peal.dev templates. The users table comes with deleted_at, a partial unique index on email, and a ready-to-wire cron endpoint for purging old records. It's the kind of thing that's easy to retrofit but much nicer when it's there from day one.

Default to soft deletes for anything user-created. Default to hard deletes for anything ephemeral or compliance-sensitive. Add a purge job from day one. That's it — the whole decision framework.

The regret asymmetry matters here: if you soft delete when you could have hard deleted, the cost is a bit more storage and some extra query complexity. If you hard delete when you should have soft deleted, the cost is telling your customer their data is permanently gone and there's nothing you can do. One of these conversations is much worse to have than the other. When in doubt, keep the headstone.