Prisma Soft Delete in NestJS: Correct Patterns and Pitfalls

Adding a deletedAt column is only the first step. A production implementation must also define active-row uniqueness, keep deleted rows out of reads, route application queries through the extended Prisma client, make cascade, restore, and retention behavior explicit, and distinguish notification events from authoritative audit evidence.

This guide uses @nestarc/soft-delete to build that boundary without hiding the database rules it depends on.

Problem 1: A Normal Unique Constraint Blocks Reuse

With a global unique constraint, a deleted row still owns its email address:

prisma

model User {
  id        Int       @id @default(autoincrement())
  email     String    @unique
  deletedAt DateTime?
}

Removing @unique and adding @@unique([email, deletedAt]) is not a safe fix. PostgreSQL treats NULL values as distinct, so multiple active rows with the same email and deletedAt = NULL can satisfy that composite constraint.

For PostgreSQL, keep the fields in the Prisma model and create a partial unique index in a reviewed migration:

prisma

model User {
  id        Int       @id @default(autoincrement())
  email     String
  deletedAt DateTime?
}

sql

CREATE UNIQUE INDEX users_email_active_unique
  ON "User" ("email")
  WHERE "deletedAt" IS NULL;

This enforces exactly the desired rule: one active row may own an email, while an email from a soft-deleted row can be reused. Remove the previous global unique constraint before adding the partial index, and check existing active rows for duplicates first.

SQLite also supports a partial unique index. MySQL requires a functional or generated-column strategy instead. See Cascade & Active-Row Uniqueness for database-specific DDL.

Problem 2: The Base Client Bypasses Soft Delete

Manual deletedAt: null filters are easy to miss. The package solves that with a Prisma Client Extension, but only queries made through the extended client are intercepted.

For Prisma 7, generate the client to an explicit output path and construct the runtime client with a driver adapter:

typescript

// prisma.service.ts
import { Injectable, OnModuleInit } from '@nestjs/common';
import { PrismaPg } from '@prisma/adapter-pg';
import { PrismaClient } from './generated/prisma/client';
import { createPrismaSoftDeleteExtension } from '@nestarc/soft-delete';

@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit {
  private readonly extended: ReturnType<typeof this.$extends>;

  constructor() {
    super({
      adapter: new PrismaPg({
        connectionString: process.env.DATABASE_URL!,
      }),
    });

    this.extended = this.$extends(
      createPrismaSoftDeleteExtension({
        softDeleteModels: ['User', 'Post', 'Comment'],
        deletedAtField: 'deletedAt',
      }),
    );
  }

  get client() {
    return this.extended;
  }

  async onModuleInit() {
    await this.$connect();
  }
}

typescript

SoftDeleteModule.forRoot({
  softDeleteModels: ['User', 'Post', 'Comment'],
  deletedAtField: 'deletedAt',
  prismaServiceToken: PrismaService,
});

Application code must use prisma.client:

typescript

// Soft delete: the extension rewrites delete() to an update.
await prisma.client.user.delete({ where: { id: userId } });

// Active-only read: the extension adds the deletedAt filter.
const users = await prisma.client.user.findMany();

Direct calls such as prisma.user.delete() use the base client and remain hard deletes. Likewise, prisma.user.findMany() does not receive the automatic active-row filter. Keep the base client out of normal application repositories and services.

When an authorized recovery endpoint needs a different read mode, decorators change the request-scoped behavior of the extended client:

typescript

@OnlyDeleted()
@Get('trash')
listDeletedUsers() {
  return this.prisma.client.user.findMany();
}

@WithDeleted()
@Get('all')
listAllUsers() {
  return this.prisma.client.user.findMany();
}

Problem 3: Cascade Needs Schema Metadata

If deleting a User should also soft-delete related Post and Comment rows, configure cascade on both the Prisma extension and the Nest module. Cascade relation lookup requires full Prisma DMMF metadata on Prisma 5, 6, and 7.

Pin @prisma/internals to the exact version of prisma, load the schema metadata in application-owned setup code, and reuse the same values:

typescript

// prisma.dmmf.ts
import { readFileSync } from 'node:fs';
import { getDMMF } from '@prisma/internals';

const datamodel = readFileSync('prisma/schema.prisma', 'utf8');

export const prismaDmmf = await getDMMF({ datamodel });
export const softDeleteCascade = {
  User: ['Post'],
  Post: ['Comment'],
};

typescript

// In PrismaService
this.extended = this.$extends(
  createPrismaSoftDeleteExtension({
    softDeleteModels: ['User', 'Post', 'Comment'],
    cascade: softDeleteCascade,
    maxCascadeDepth: 3,
    dmmf: prismaDmmf,
  }),
);

typescript

// In AppModule
SoftDeleteModule.forRoot({
  softDeleteModels: ['User', 'Post', 'Comment'],
  cascade: softDeleteCascade,
  maxCascadeDepth: 3,
  dmmf: prismaDmmf,
  prismaServiceToken: PrismaService,
});

The package fails early with CascadeDmmfMissingError when cascade is configured without metadata. Keep @prisma/internals version-pinned because it does not provide a semantic-versioning guarantee, and cover metadata loading with a startup or integration test.

Problem 4: Lifecycle Events Are Not Audit Evidence

SoftDeletedEvent, RestoredEvent, and PurgedEvent are useful for metrics, cache invalidation, and notifications. They are notification-only: a listener can run before an outer transaction commits, and event delivery is not a durable proof that the mutation committed.

For authoritative lifecycle evidence, soft-delete 0.7 provides an opt-in, fail-closed bridge to @nestarc/audit-log. Version 0.7.2 accepts audit-log's optional peer range ^0.4.1 || ^0.5.0; both lines must expose the atomic capability handshake. Apply extensions in the fixed tenancy → audit-log → soft-delete order. Replace the soft-delete-only PrismaService from Problem 2 with this fully composed client:

typescript

import { Injectable, OnModuleInit } from '@nestjs/common';
import { PrismaPg } from '@prisma/adapter-pg';
import { createAuditExtension } from '@nestarc/audit-log';
import { createPrismaSoftDeleteExtension } from '@nestarc/soft-delete';
import { createPrismaTenancyExtension, TenancyService } from '@nestarc/tenancy';
import { Prisma, PrismaClient } from './generated/prisma/client';

const prismaModule = { Prisma };

const lifecycleOptions = {
  softDeleteModels: ['User', 'Post', 'Comment'],
  cascade: softDeleteCascade,
  dmmf: prismaDmmf,
  auditLifecycle: 'atomic-required' as const,
  auditMaxBatchRecords: 1000,
};

@Injectable()
export class PrismaService implements OnModuleInit {
  readonly base = new PrismaClient({
    adapter: new PrismaPg({ connectionString: process.env.DATABASE_URL! }),
  });

  readonly client;

  constructor(tenancyService: TenancyService) {
    this.client = this.base
      .$extends(
        createPrismaTenancyExtension(tenancyService, {
          interactiveTransactionSupport: true,
          failClosed: true,
        }),
      )
      .$extends(
        createAuditExtension({
          consistency: 'atomic-required',
          trackedModels: ['User', 'Post', 'Comment'],
          maxBatchRecords: 1000,
          databaseMapping: {
            User: { tableName: 'User' },
            Post: { tableName: 'Post' },
            Comment: { tableName: 'Comment' },
          },
          prismaModule,
        }),
      )
      .$extends(createPrismaSoftDeleteExtension(lifecycleOptions));
  }

  async onModuleInit() {
    await this.base.$connect();
  }
}

Nest must inject that exact PrismaService.client into SoftDeleteModule. A provider that resolves to the base client or to a wrapper object with a .client property cannot join the ambient transaction:

typescript

export const EXTENDED_PRISMA = Symbol('EXTENDED_PRISMA');

@Global()
@Module({
  providers: [
    PrismaService,
    {
      provide: EXTENDED_PRISMA,
      inject: [PrismaService],
      useFactory: (prisma: PrismaService) => prisma.client,
    },
  ],
  exports: [PrismaService, EXTENDED_PRISMA],
})
export class PrismaModule {}

SoftDeleteModule.forRoot({
  ...lifecycleOptions,
  prismaServiceToken: EXTENDED_PRISMA,
});

Add every cascade child to audit-log's trackedModels and databaseMapping. Then keep root, cascade, service, and bulk lifecycle operations inside withAuditTransaction():

typescript

// Root soft-delete and its cascades.
await prisma.client.withAuditTransaction((tx) =>
  tx.user.delete({ where: { id: userId } }),
);

// Service-backed lifecycle operations join the same ambient transaction.
await prisma.client.withAuditTransaction(() =>
  softDelete.restore('User', { id: userId }),
);

await prisma.client.withAuditTransaction(() =>
  softDelete.restoreMany('User', { where: { organizationId } }),
);

await prisma.client.withAuditTransaction(() =>
  softDelete.purge('User', { olderThan: cutoff, where: { organizationId } }),
);

The business rows and deterministic record actions (Model.softDeleted, Model.restored, and Model.purged) commit or roll back together. Calls outside the helper, an incorrect extension order, a best-effort audit client, or a different injected client fail before mutation. auditMaxBatchRecords bounds record-level deleteMany and restoreMany conversion; align it with audit-log's independent maxBatchRecords limit.

Restore Through `SoftDeleteService`

restore() clears the deletion fields and restores timestamp-matched descendants when cascade is configured:

typescript

@Post(':id/restore')
restore(@Param('id') id: string) {
  return this.prisma.client.withAuditTransaction(() =>
    this.softDelete.restore('User', { id: +id }),
  );
}

For a bounded bulk restore, use restoreMany():

typescript

const result = await this.prisma.client.withAuditTransaction(() =>
  this.softDelete.restoreMany('User', {
    where: { organizationId, role: 'GUEST' },
  }),
);

These wrappers are required when auditLifecycle: 'atomic-required' is enabled. Restoring an active value can conflict with the partial unique index if another active row has claimed it. Treat restore as an authorized workflow and handle that conflict explicitly.

Purge Only Rows Older Than the Retention Cutoff

purge() permanently deletes soft-deleted rows. Its current argument shape requires olderThan; an optional where can narrow the operation further:

typescript

const cutoff = new Date(Date.now() - 90 * 24 * 60 * 60 * 1000);

const result = await this.prisma.client.withAuditTransaction(() =>
  this.softDelete.purge('User', {
    olderThan: cutoff,
    where: { organizationId },
  }),
);

console.log(`Purged ${result.count} users`);

Run purge from a restricted administrative or scheduled workflow, bound optional filters carefully, and test cascade and retention behavior on production-like data before enabling it.

Implementation Checklist

Use a database-specific active-row unique index; do not use @@unique([email, deletedAt]).
Send all normal application reads and writes through the extended client.
Keep base-client hard deletes limited to deliberate administrative paths.
Pass the same explicit DMMF and cascade map to the extension and Nest module.
For authoritative evidence, use tenancy → audit-log → soft-delete, inject the exact composed client, and keep lifecycle writes inside withAuditTransaction().
Keep auditMaxBatchRecords aligned with audit-log's maxBatchRecords and test cap overflow.
Treat lifecycle events as notification-only.
Test delete filtering, cascade, restore conflicts, and retention-based purge.

Next Steps

Installation — complete Prisma 7 and module setup
Cascade & Active-Row Uniqueness — metadata and database-specific indexes
Restore, Force Delete & Purge — current recovery and deletion APIs
Decorators — request-scoped query modes
PostgreSQL partial indexes — authoritative active-row uniqueness building block
Prisma Client extensions — official extension model

Prisma Soft Delete in NestJS: Correct Patterns and Pitfalls ​

Problem 1: A Normal Unique Constraint Blocks Reuse ​

Problem 2: The Base Client Bypasses Soft Delete ​

Problem 3: Cascade Needs Schema Metadata ​

Problem 4: Lifecycle Events Are Not Audit Evidence ​

Restore Through SoftDeleteService ​

Purge Only Rows Older Than the Retention Cutoff ​

Implementation Checklist ​

Next Steps ​

Prisma Soft Delete in NestJS: Correct Patterns and Pitfalls

Problem 1: A Normal Unique Constraint Blocks Reuse

Problem 2: The Base Client Bypasses Soft Delete

Problem 3: Cascade Needs Schema Metadata

Problem 4: Lifecycle Events Are Not Audit Evidence

Restore Through `SoftDeleteService`

Purge Only Rows Older Than the Retention Cutoff

Implementation Checklist

Next Steps