Hard deletes are one of the most common causes of data integrity nightmares in modern SaaS applications. When a user clicks “Delete Account” and your database physically removes the row, you lose audit trails, break foreign key relationships, and often violate compliance requirements. This is where Prisma soft delete comes in.

In this practical tutorial, we’ll walk through how to implement soft deletes in PostgreSQL using Prisma ORM with the deletedAt column pattern, filter queries automatically using Client Extensions (the modern replacement for middleware), and handle restoration of deleted records. Everything shown here is production-ready and battle-tested in real SaaS applications.

Why Soft Deletes Matter for SaaS Applications

Before diving into code, let’s understand why hard deletes cause real problems in production: There’s a good explainer over at tistory.com.

• Audit and compliance: GDPR, SOC 2, and HIPAA often require retention windows before actual deletion.
• Data integrity: Foreign key cascades can wipe out related records (invoices, logs, comments) unintentionally.
• User recovery: Users make mistakes. A “restore” feature is a huge UX win.
• Analytics: You still want deleted records visible in historical reporting.
• Support debugging: Being able to see what a user deleted is invaluable when investigating tickets.

Hard Delete vs Soft Delete at a Glance

Aspect	Hard Delete	Soft Delete
Recoverable	No	Yes
Storage	Frees space	Keeps row
Referential integrity	Risky cascades	Preserved
Compliance	Immediate	Retention-friendly

Step 1: Adding the deletedAt Column to Your Prisma Schema

The most common and flexible pattern is a nullable deletedAt timestamp. When NULL, the record is active. When populated, it’s soft-deleted.

// prisma/schema.prisma

model User {
  id        String    @id @default(cuid())
  email     String    @unique
  name      String?
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  deletedAt DateTime?

  posts     Post[]

  @@index([deletedAt])
}

model Post {
  id        String    @id @default(cuid())
  title     String
  content   String?
  authorId  String
  author    User      @relation(fields: [authorId], references: [id])
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  deletedAt DateTime?

  @@index([deletedAt])
}

Pro tip: Always add an index on deletedAt. Your queries will constantly filter by deletedAt IS NULL, and without an index PostgreSQL will do full table scans as your dataset grows.

Then run:

npx prisma migrate dev --name add_soft_delete

Step 2: Using Prisma Client Extensions (the Modern Way)

You may have seen older tutorials using prisma.$use() middleware. As of Prisma 5+, middleware is deprecated in favor of Client Extensions ($extends). This is the recommended approach in 2026.

We’ll build an extension that:

1. Intercepts delete and deleteMany calls and converts them to updates on deletedAt.
2. Automatically filters out soft-deleted rows from findUnique, findFirst, findMany, count, and aggregate.
3. Exposes helpers for restoring and for permanently deleting when truly needed.

Creating the Soft Delete Extension

// lib/prisma.ts
import { PrismaClient } from '@prisma/client'

const SOFT_DELETE_MODELS = ['User', 'Post'] as const

const basePrisma = new PrismaClient()

export const prisma = basePrisma.$extends({
  name: 'softDelete',
  query: {
    $allModels: {
      async delete({ model, args, query }) {
        if (!SOFT_DELETE_MODELS.includes(model as any)) {
          return query(args)
        }
        return (basePrisma as any)[model.charAt(0).toLowerCase() + model.slice(1)].update({
          ...args,
          data: { deletedAt: new Date() },
        })
      },
      async deleteMany({ model, args, query }) {
        if (!SOFT_DELETE_MODELS.includes(model as any)) {
          return query(args)
        }
        return (basePrisma as any)[model.charAt(0).toLowerCase() + model.slice(1)].updateMany({
          ...args,
          data: { deletedAt: new Date() },
        })
      },
      async findUnique({ model, args, query }) {
        if (!SOFT_DELETE_MODELS.includes(model as any)) return query(args)
        const result = await query(args)
        if (result && (result as any).deletedAt) return null
        return result
      },
      async findFirst({ model, args, query }) {
        if (!SOFT_DELETE_MODELS.includes(model as any)) return query(args)
        args.where = { ...args.where, deletedAt: null }
        return query(args)
      },
      async findMany({ model, args, query }) {
        if (!SOFT_DELETE_MODELS.includes(model as any)) return query(args)
        args.where = { ...args.where, deletedAt: null }
        return query(args)
      },
      async count({ model, args, query }) {
        if (!SOFT_DELETE_MODELS.includes(model as any)) return query(args)
        args.where = { ...args.where, deletedAt: null }
        return query(args)
      },
    },
  },
})

Now anywhere you call prisma.user.delete() in your app, it will actually run an UPDATE setting deletedAt, and all standard read queries will automatically skip deleted rows.

Step 3: Handling Restoration of Soft-Deleted Records

Restoration is straightforward once the pattern is in place. Because our extension filters by deletedAt: null, we need to bypass it when reading deleted records. The cleanest approach is to expose model-level helper methods. This guide goes deeper on it.

export const prismaExtended = prisma.$extends({
  name: 'softDeleteHelpers',
  model: {
    $allModels: {
      async restore<T>(this: T, where: any) {
        const context = (this as any)
        return context.updateMany({
          where,
          data: { deletedAt: null },
        })
      },
      async findManyWithDeleted<T>(this: T, args: any = {}) {
        const context = (this as any)
        const { where = {}, ...rest } = args
        // Bypass by using the raw base client
        return basePrisma[context.$name.toLowerCase()].findMany({ where, ...rest })
      },
      async hardDelete<T>(this: T, where: any) {
        const context = (this as any)
        return basePrisma[context.$name.toLowerCase()].delete({ where })
      },
    },
  },
})

Usage becomes clean and predictable:

// Soft delete
await prisma.user.delete({ where: { id: userId } })

// Restore
await prisma.user.restore({ id: userId })

// Query including deleted records (admin dashboards)
await prisma.user.findManyWithDeleted()

// Permanent removal (GDPR right-to-be-forgotten after retention)
await prisma.user.hardDelete({ id: userId })

Step 4: Dealing with Unique Constraints

Here’s a subtle but important issue. Say a user with email [email protected] is soft-deleted, and later Alice tries to sign up again. Because the row still exists, your @unique constraint on email will reject the new signup.

There are three common approaches:

1. Partial unique index: PostgreSQL supports partial indexes, so you can enforce uniqueness only where deletedAt IS NULL.
2. Compound uniqueness: Include deletedAt in the unique constraint.
3. Rename on delete: Append the deletion timestamp to the unique field (e.g., [email protected]).

Option 1 is the cleanest in PostgreSQL. Since Prisma doesn’t natively support partial indexes in the schema, add one with a raw migration:

-- prisma/migrations/xxx_partial_unique_email/migration.sql
DROP INDEX IF EXISTS "User_email_key";
CREATE UNIQUE INDEX "User_email_key" ON "User" ("email") WHERE "deletedAt" IS NULL;

You’ll also want to remove @unique from the Prisma schema field or Prisma will try to recreate the regular index.

Step 5: Cascading Soft Deletes

When you soft delete a User, should their Post records be soft deleted too? In many SaaS apps, yes. You have two options:

• Application-level cascade: In your extension’s delete hook, run a transaction that updates related records.
• Database triggers: Use PostgreSQL triggers to propagate deletedAt to child rows.

Here’s an application-level example:

async delete({ model, args, query }) {
  if (model !== 'User') return query(args)
  const now = new Date()
  return basePrisma.$transaction([
    basePrisma.post.updateMany({
      where: { authorId: (args.where as any).id, deletedAt: null },
      data: { deletedAt: now },
    }),
    basePrisma.user.update({
      where: args.where,
      data: { deletedAt: now },
    }),
  ])
}

Step 6: A Purge Job for Retention Policies

Soft deletes aren’t a substitute for real deletion. For GDPR and storage reasons, you should permanently delete records after a retention period (commonly 30 or 90 days). Set up a cron job: Source: https://thisdot.co.

// jobs/purge-soft-deleted.ts
import { basePrisma } from '../lib/prisma'

const RETENTION_DAYS = 90

export async function purgeSoftDeleted() {
  const cutoff = new Date()
  cutoff.setDate(cutoff.getDate() - RETENTION_DAYS)

  const { count } = await basePrisma.user.deleteMany({
    where: { deletedAt: { lt: cutoff } },
  })

  console.log(`Purged ${count} users deleted before ${cutoff.toISOString()}`)
}

Schedule this with your job runner of choice (BullMQ, Inngest, Trigger.dev, or a simple cron).

Alternatives: Community Extensions

If you’d rather not maintain your own extension, two well-known community packages implement this pattern:

• prisma-extension-soft-delete by Olivier Wilkinson (the modern extension version)
• prisma-soft-delete-middleware (legacy middleware version, useful if you’re still on older Prisma)

These are great starting points, but rolling your own gives you full control over cascades, restoration semantics, and unique constraint handling, which matters a lot in a real SaaS codebase.

Best Practices Checklist

• Always index the deletedAt column.
• Use partial unique indexes to avoid duplicate constraint conflicts.
• Prefer Client Extensions over deprecated middleware.
• Provide explicit findManyWithDeleted and hardDelete escape hatches for admins.
• Cascade soft deletes carefully, ideally inside transactions.
• Implement a purge job to comply with data retention policies.
• Write integration tests that verify deleted records are actually filtered out.

FAQ

Is Prisma middleware still supported in 2026?

Prisma middleware ($use) is deprecated and slated for removal. All new soft delete implementations should use Client Extensions via $extends, which is what we’ve used throughout this guide.

Does soft delete work with Prisma’s raw queries?

No. Client Extensions only intercept Prisma Client operations. If you use $queryRaw or $executeRaw, you must add WHERE "deletedAt" IS NULL manually.

How do I handle soft deletes with unique constraints?

Use a PostgreSQL partial unique index that only enforces uniqueness where deletedAt IS NULL. This lets users “reuse” an email after deletion without breaking your schema.

Should I cascade soft deletes to related tables?

It depends on your domain. For parent-child relationships (User -> Posts), yes. For loose associations (User -> AuditLog), you typically want to keep the logs intact. Handle cascade logic explicitly in your extension or with database triggers.

Can I use soft delete with Prisma Accelerate or Edge?

Yes. Client Extensions are fully compatible with Accelerate and edge runtimes. The extension runs client-side before queries are sent, so it works regardless of the underlying transport.

What’s the performance impact of soft deletes?

Minimal if you index deletedAt. The main cost is that your tables grow larger over time, which is why a retention-based purge job is essential.

Wrapping Up

Implementing Prisma soft delete with the deletedAt pattern is one of the highest-ROI changes you can make in a SaaS codebase. It protects your data, keeps your users happy when they inevitably click the wrong button, and keeps your compliance team even happier. By combining Client Extensions with partial unique indexes and a scheduled purge job, you get a robust, production-grade soft delete system that scales with your product.

At Box Software, we help teams design database patterns like this every day. If you’re building a SaaS on PostgreSQL and Prisma and want a second pair of eyes on your data architecture, get in touch.