At some point, every developer adds a `deleted_at` column to a table and feels very clever about it. 'I'll never lose data again,' they think, sipping their coffee. Then six months later they're debugging why their analytics are off by 40% because they forgot to add `WHERE deleted_at IS NULL` to three different queries. We've been there.
Soft deletes and hard deletes are both valid tools, but the choice has real consequences for your query performance, your data model, your compliance story, and your 2am debugging sessions. Let's break down when each actually makes sense instead of just defaulting to one because it feels safer.
What We're Actually Talking About
A hard delete is just `DELETE FROM users WHERE id = 123`. The row is gone. A soft delete keeps the row but marks it as deleted — typically with a `deleted_at TIMESTAMP` column or a boolean `is_deleted` flag. The row stays in the table, you just filter it out of normal queries.
-- Hard delete: row is gone
DELETE FROM orders WHERE id = 456;
-- Soft delete: row stays, just marked
UPDATE orders
SET deleted_at = NOW()
WHERE id = 456;
-- Every query now needs this filter
SELECT * FROM orders
WHERE user_id = 123
AND deleted_at IS NULL;The soft delete approach feels safer on the surface — you can always recover data, you can audit what happened, you keep referential integrity intact. But it comes with a set of costs that compound as your app grows.
The Hidden Costs of Soft Deletes
The biggest problem with soft deletes isn't the implementation — it's the consistency tax. Every query touching that table needs the `deleted_at IS NULL` filter. Miss it once and you're showing users deleted content, inflating analytics, or leaking data across tenants. In a multi-developer codebase, someone will forget. It's not a matter of if.
Performance is the second issue. Without proper indexing, that filter scans every row. With proper indexing, you now have partial indexes that need maintaining. Your 'users' table with 2 million rows technically only has 180k active users, but every query is working with the full dataset. Postgres can handle this fine with the right setup, but you have to be intentional about it.
-- Partial index for soft-deleted tables (Postgres)
CREATE INDEX idx_orders_active
ON orders (user_id, created_at)
WHERE deleted_at IS NULL;
-- This query will use the index efficiently
SELECT * FROM orders
WHERE user_id = 123
AND deleted_at IS NULL
ORDER BY created_at DESC;Unique constraints also get weird. If a user deletes their account and wants to sign up again with the same email, your `UNIQUE (email)` constraint will block them because the old soft-deleted row is still there. You end up with ugly workarounds like `UNIQUE (email, deleted_at)` — which allows duplicate emails as long as only one is non-deleted. It works, but it feels like a code smell wearing a suit.
- Every query needs consistent filtering — miss it once and data leaks through
- Table bloat: your 'active' data is buried in a table full of 'dead' rows
- Unique constraints get complicated (emails, usernames, slugs)
- Foreign key relationships still point to deleted rows — which can be confusing
- ORM magic like Prisma's soft delete middleware can hide problems instead of fixing them
When Soft Deletes Are Worth It
That said, soft deletes are genuinely the right call in specific situations. The question is whether the recovery value outweighs the query complexity cost.
Financial records are the obvious case. You should never hard delete an invoice, a transaction, or an order. Full stop. Even if a user requests account deletion, you keep the financial trail and anonymize the PII attached to it — those are two separate concerns. Audit logs for billing need to exist forever, or at least until your accountant stops needing them.
Anything with undo functionality benefits from soft deletes. Trello cards, Notion pages, email drafts, todo items — if your users expect a 'restore from trash' feature, soft deletes are the natural implementation. Just make sure you actually build a cleanup job to hard delete things that have been in the trash long enough.
Content that other records reference is another good candidate. If a user soft-deletes a blog post that has comments attached to it, keeping the post row around (even flagged as deleted) means you don't have to cascade delete the comments or orphan them. You can show 'this post has been removed' without losing the comment thread structure.
Soft deletes are a good fit when: you need audit trails, you need undo functionality, or deletion of one record would break references from other records you want to keep.
When Hard Deletes Are the Right Call
If you're operating in the EU (or serving EU users, which is basically everyone), GDPR gives users the 'right to erasure.' When a user asks you to delete their data, a soft delete with their email still sitting in the table is not compliant. You need to actually remove the PII or anonymize it. Hard deletes — or at minimum, a hard delete of the PII columns — become a legal requirement, not just a preference.
Ephemeral data doesn't need soft deletes. Sessions, one-time tokens, notification read receipts, event queue entries — if this data is operational rather than historical, hard delete it. The table stays lean, your queries stay fast, and you're not storing data you have no business keeping.
User-generated content that has no meaningful recovery path is also a candidate for hard deletion. If someone deletes a draft message they never sent, is there a real use case for recovering that in your app? Probably not. Build the confirmation dialog, then delete it for real.
- PII (emails, names, addresses) when a user requests deletion — GDPR compliance requires it
- Sessions and tokens — these are operational, not historical
- Temporary or ephemeral data (queue entries, rate limit windows, OTP codes)
- Anything where recovery has no user-facing value
- Data that was created by mistake and never seen by anyone
The Hybrid Approach That Actually Works
The cleanest pattern we've found is separating the 'why you're keeping data' from the deletion strategy. You don't have to pick one approach for your whole database — different tables warrant different treatment.
For user accounts specifically, the pattern we like is: soft delete the account (mark it as deactivated), immediately anonymize or null out the PII, and move the financial/audit records to a separate archive table that references an anonymized user ID. This satisfies GDPR's right to erasure while keeping your billing records intact.
// User deletion that's GDPR-compliant
async function deleteUserAccount(userId: string) {
await db.transaction(async (tx) => {
// 1. Anonymize PII immediately
await tx
.update(users)
.set({
email: `deleted_${userId}@removed.invalid`,
name: 'Deleted User',
avatarUrl: null,
deletedAt: new Date(),
})
.where(eq(users.id, userId));
// 2. Hard delete sessions and tokens
await tx.delete(sessions).where(eq(sessions.userId, userId));
await tx.delete(oauthAccounts).where(eq(oauthAccounts.userId, userId));
// 3. Soft delete user content (posts, comments) — preserve structure
await tx
.update(posts)
.set({ deletedAt: new Date() })
.where(eq(posts.authorId, userId));
// 4. Orders/invoices stay untouched — financial records needed
// They now reference the anonymized user row, which is fine
});
}The transaction wrapping this is important. You don't want to anonymize the email but fail halfway through and leave sessions active. All of it succeeds or all of it rolls back.
Implementing Soft Deletes in Drizzle Without Losing Your Mind
If you're using Drizzle (which we use in basically every peal.dev template), there's no built-in soft delete middleware like Prisma has. That's actually fine — the middleware approach can hide the filter in a way that makes it easy to forget about when you need to query deleted records intentionally. With Drizzle, being explicit is the default.
import { pgTable, text, timestamp } from 'drizzle-orm/pg-core';
import { isNull, and, eq } from 'drizzle-orm';
export const posts = pgTable('posts', {
id: text('id').primaryKey(),
title: text('title').notNull(),
authorId: text('author_id').notNull(),
createdAt: timestamp('created_at').defaultNow().notNull(),
deletedAt: timestamp('deleted_at'), // null = active
});
// Create a reusable filter
export const isActive = isNull(posts.deletedAt);
// Use it consistently
const activePosts = await db
.select()
.from(posts)
.where(and(isActive, eq(posts.authorId, userId)));
// When you actually need deleted records
const allPosts = await db
.select()
.from(posts)
.where(eq(posts.authorId, userId)); // no isActive filter — intentional
// Soft delete
await db
.update(posts)
.set({ deletedAt: new Date() })
.where(eq(posts.id, postId));The `isActive` helper makes the filter reusable and searchable in your codebase. When you need to audit which queries filter soft-deleted records and which don't, you can just grep for `isActive`. That's the kind of thing that saves you at 2am when users are reporting that deleted posts are showing up in search results.
Don't Forget the Cleanup Job
One thing that gets skipped 90% of the time: if you implement soft deletes with the intention of 'users can restore from trash for 30 days,' you need to actually hard delete the soft-deleted rows after 30 days. Otherwise your trash is infinite, your table keeps growing, and your original soft delete was just a slow hard delete.
// Run this as a cron job — daily is usually fine
async function purgeOldSoftDeletes() {
const thirtyDaysAgo = new Date();
thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);
const result = await db
.delete(posts)
.where(
and(
isNotNull(posts.deletedAt),
lt(posts.deletedAt, thirtyDaysAgo)
)
);
console.log(`Purged ${result.rowCount} soft-deleted posts`);
}Set up this job when you implement soft deletes, not later. 'Later' is when you have 2 million soft-deleted rows and your table has tripled in size. We know this because we've done 'later' and it's not fun to migrate.
A soft delete without a purge job is just a slow-motion data hoarding problem. Schedule the cleanup on day one.
Most of our templates at peal.dev use soft deletes for content and user-generated records, with hard deletes for sessions and tokens — and always include the purge job wired up to a cron route from day one, so you don't inherit the mess later.
The Decision Framework
When you're deciding how to handle deletion for a new table, run through these questions in order:
- Does this data have regulatory requirements (financial records, audit trails)? → Keep it, archive it, anonymize PII
- Does the user expect 'undo' or 'restore from trash'? → Soft delete with a purge job
- Does deleting this row break references from other rows you need? → Soft delete or cascade carefully
- Is this operational/ephemeral data (sessions, tokens, queue items)? → Hard delete
- Is this PII and the user has requested deletion? → Hard delete the PII, keep anonymized records for audit
The answer is almost never 'soft delete everything' or 'hard delete everything.' It's a mix, and the mix depends on what the data means to your business and your users.
Whatever you choose, be consistent within a table, document why you made the call, and add that partial index before you need it. Your future self — the one debugging a slow query at 2am — will be grateful you thought about this now instead of then.