How would you implement soft delete and data filtering with Prisma middleware?

Soft Delete with Prisma

Approach 1: Prisma Client Extensions (Recommended — Prisma 4.16+)

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

const prisma = new PrismaClient().$extends({
  name: "softDelete",
  model: {
    jobApplication: {
      async findMany(args) {
        return prisma.jobApplication.findMany({
          ...args,
          where: { deletedAt: null, ...args?.where },
        });
      },
      async delete(args) {
        return prisma.jobApplication.update({
          where: args.where,
          data: { deletedAt: new Date() },
        });
      },
      async deleteMany(args) {
        return prisma.jobApplication.updateMany({
          ...args,
          data: { deletedAt: new Date() },
        });
      },
    },
  },
});

Approach 2: Base Schema Pattern

model JobApplication {
  id         String    @id @default(auto()) @map("_id") @db.ObjectId
  company    String
  position   String
  status     String    @default("APPLIED")
  deletedAt  DateTime? // NULL = active, has value = soft deleted
  userId     String    @db.ObjectId
  user       User      @relation(fields: [userId], references: [id])
  createdAt  DateTime  @default(now())
  updatedAt  DateTime  @updatedAt

  @@index([userId, deletedAt]) // Efficient filtering
}

Usage in API Routes

// GET /api/applications — auto-filters soft-deleted
const apps = await prisma.jobApplication.findMany({
  where: { userId: session.userId },
  include: { interviews: true },
});
// Client extension automatically adds deletedAt: null filter

// DELETE /api/applications/[id] — soft delete
await prisma.jobApplication.delete({
  where: { id: applicationId },
});
// Client extension converts this to UPDATE deletedAt = now()

// Analytics — bypass soft delete filter
const allApps = await prisma.jobApplication.findMany({
  where: { deletedAt: { not: null } }, // Explicitly query deleted
});

Restoring Soft-Deleted Records

await prisma.jobApplication.update({
  where: { id: applicationId },
  data: { deletedAt: null },
});

Production Considerations

AspectDetail

Unique constraintsMust include deletedAt — @@unique([company, position, userId, deletedAt])
Index performanceAdd deletedAt to composite indexes
Storage growthSchedule cleanup jobs for old soft-deleted records
GDPR complianceAdd a separate "hard delete" for user data requests
Count queriesAlways filter: count({ where: { deletedAt: null } })

When to Use What

PatternUse Case

Soft deleteUser-facing deletions, analytics retention
Hard deleteGDPR requests, test data cleanup
Archive tableRegulatory compliance (move to separate table)
Event sourcingAudit trail as primary data model