prisma-safe-delete

A Prisma generator that creates a type-safe wrapper for soft deletion with automatic cascade support. Designed to be a drop-in replacement that you configure once and (hopefully) never think about again.

Why This Library?

Soft deletion is a common pattern where records are marked as deleted (typically with a timestamp) rather than being permanently removed. This preserves data for auditing, recovery, and maintaining referential integrity.

The problem: Implementing soft deletion correctly is tedious and error-prone. You need to remember to filter out deleted records in every query, handle cascading deletes manually, and deal with unique constraint conflicts when "deleted" records still occupy unique values.

prisma-safe-delete solves this by:

• Automatically filtering deleted records from all read operations
• Cascading soft-deletes through your relation tree (following onDelete: Cascade)
• Mangling unique string fields to free them for reuse
• Providing escape hatches when you need to access deleted data

Features

• Automatic filter injection on all read operations, including nested include, select, _count, and relation filters (some/every/none)
• Cascade soft-delete following onDelete: Cascade relations, with detailed counts by model
• Unique constraint handling via mangling, sentinel dates, or manual partial indexes
• Escape hatches: $includingDeleted, $onlyDeleted, per-model overrides, and raw $prisma access
• Transaction support with full soft-delete API including escape hatches
• Restore operations including cascade restore matching by timestamp
• Audit logging with automatic event capture for create, update, and delete operations
• Compound key support for both primary and foreign keys

Installation

npm install prisma-safe-delete
# or
pnpm add prisma-safe-delete
# or
yarn add prisma-safe-delete

Quick Start

1. Add the generator to your Prisma schema

generator client {
  provider = "prisma-client"
  output   = "./generated/client"
}

generator softDelete {
  provider = "prisma-safe-delete"
  output   = "./generated/soft-delete"
}

datasource db {
  provider = "postgresql"
}

2. Add deleted_at to soft-deletable models

model User {
  id         String    @id @default(cuid())
  email      String    @unique
  name       String?
  posts      Post[]
  deleted_at DateTime?  // Makes this model soft-deletable
}

model Post {
  id         String    @id @default(cuid())
  title      String
  authorId   String
  author     User      @relation(fields: [authorId], references: [id], onDelete: Cascade)
  comments   Comment[]
  deleted_at DateTime?
}

model Comment {
  id         String    @id @default(cuid())
  content    String
  postId     String
  post       Post      @relation(fields: [postId], references: [id], onDelete: Cascade)
  deleted_at DateTime?
}

3. Generate and use

npx prisma generate

import { PrismaClient } from './generated/client';
import { PrismaPg } from '@prisma/adapter-pg';
import { Pool } from 'pg';
import { wrapPrismaClient } from './generated/soft-delete';

// Prisma 7 requires an adapter
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const adapter = new PrismaPg(pool);

const prisma = new PrismaClient({ adapter });
const safePrisma = wrapPrismaClient(prisma);

// All queries automatically filter out soft-deleted records
const users = await safePrisma.user.findMany();

// Soft delete with automatic cascade
const { record, cascaded } = await safePrisma.user.softDelete({ where: { id: 'user-1' } });
// ^ Soft-deletes the user AND all their posts AND all comments on those posts
console.log(cascaded); // { Post: 3, Comment: 7 }

API Overview

All read operations (findMany, findFirst, findUnique, count, aggregate, groupBy) automatically exclude soft-deleted records. Filters propagate into nested include, select, and _count.

Method	Description
softDelete	Soft-delete one record with cascade, returns { record, cascaded }
softDeleteMany	Soft-delete many records with cascade, returns { count, cascaded }
softDeletePreview	Preview cascade without changes, returns { wouldDelete }
restore	Restore one record (no cascade)
restoreMany	Restore many records
restoreCascade	Restore one record + cascade children (matched by timestamp)
__dangerousHardDelete	Permanently delete one record
__dangerousHardDeleteMany	Permanently delete many records
$includingDeleted	Query all records (propagates to relations)
$onlyDeleted	Query only deleted records (propagates to relations)
model.includingDeleted	Per-model override (does not propagate)
$prisma	Raw Prisma client (no filtering)
$transaction	Interactive transaction with full soft-delete API
$writeAuditEvent	Write a custom audit event (requires @audit-table)

For full API documentation with examples, see docs/api-reference.md.

Audit Logging

Mark models with /// @audit to automatically capture audit events for mutations. Requires an /// @audit-table model in your schema to store events.

/// @audit
model Project {
  id         String    @id @default(cuid())
  name       String
  deleted_at DateTime?
}

/// @audit(create, delete)
model Webhook {
  id   String @id @default(cuid())
  url  String
}

/// @audit-table
model AuditEvent {
  id               String   @id @default(cuid())
  entity_type      String
  entity_id        String
  action           String
  actor_id         String?
  event_data       Json
  created_at       DateTime @default(now())
  parent_event_id  String?
}

const safePrisma = wrapPrismaClient(prisma, {
  auditContext: async () => ({ ip: req.ip, userAgent: req.headers['user-agent'] }),
});

// All mutations on audited models accept an optional actorId
await safePrisma.project.create({
  data: { name: 'New Project' },
  actorId: currentUserId,
});

Audit events capture before/after snapshots for updates, the full record for creates and deletes, and are written atomically in the same transaction as the mutation. All audited mutation methods also accept an optional per-call auditContext that merges with the global one. For full details, see docs/api-reference.md.

Unique Constraint Handling

Three strategies are available via the uniqueStrategy generator option:

• "mangle" (default): Appends __deleted_{pk} to unique string fields on soft-delete
• "none": No mangling; you handle uniqueness via partial indexes
• "sentinel": Uses deleted_at = 9999-12-31 for active records, enabling @@unique([field, deleted_at]) compound constraints

generator softDelete {
  provider       = "prisma-safe-delete"
  output         = "./generated/soft-delete"
  uniqueStrategy = "sentinel"  // or "mangle" (default) or "none"
}

For full details on each strategy, migration guides, and generator warnings, see docs/unique-strategies.md.

Cascade Behavior

Soft-delete cascades follow onDelete: Cascade relations. All cascaded records share the same deleted_at timestamp, and the entire operation is transactional.

To disable cascading entirely, set cascade = "false". With soft deletes the parent row still exists in the database, so foreign key constraints are never violated — cascading is a policy choice, not a data integrity requirement.

generator softDelete {
  provider = "prisma-safe-delete"
  output   = "./generated/soft-delete"
  cascade  = "false"  // default: "true"
}

When cascade is disabled, softDelete / softDeleteMany only affect the targeted model and always return cascaded: {}. Models that don't need unique field mangling (i.e., uniqueStrategy = "none" or "sentinel") also get the fast updateMany path instead of per-record transactions.

For cascade rules and performance characteristics, see docs/cascade-behavior.md.

Soft Delete Detection

Models are automatically detected as soft-deletable if they have a DateTime field named deleted_at or deletedAt matching one of these patterns:

• DateTime? (nullable) — used with mangle and none strategies
• DateTime @default(...) (non-nullable with default) — used with sentinel strategy

Known Limitations

• Fluent API: safePrisma.user.findUnique(...).posts() bypasses filtering. Use include instead.
• Raw queries: $queryRaw and $executeRaw bypass the wrapper entirely (by design).
• Upsert: Soft-deleted records are not found by upsert's where clause. With none strategy, the create branch will fail on unique constraint violation.
• $extends not available: $extends is intentionally omitted from SafePrismaClient because Prisma's $extends() returns a new unwrapped client, silently losing soft-delete behavior. See Using Prisma Extensions below.
• To-one includes (partial): Prisma doesn't support where on to-one relation includes. For most read operations, the wrapper post-processes results to nullify soft-deleted to-one relations. However, $includingDeleted and $prisma escape hatches do not apply this post-processing. See Limitations and Caveats below.
• Nested writes: connect, connectOrCreate, and nested create/delete within data bypass soft-delete logic.
• Sequential transactions: $transaction([...]) with a promise array skips return-value post-processing (e.g., to-one nullification). The where filters are still injected at call time. Use the interactive form $transaction(async (tx) => { ... }) for full wrapping.
• No database-level enforcement: The wrapper operates at the application layer only. Developers can bypass soft-delete via $prisma, __dangerousHardDelete, raw SQL, or by using PrismaClient directly. For strict enforcement, add database triggers or row-level security policies.
• Audit event atomicity: Audit events are written within the same database transaction as the operation they log. If the transaction fails, the audit event is also rolled back. For compliance-critical deployments where audit events must survive operation failures, consider adding an external audit sink (e.g., write-ahead log, event stream, or async replication).

Using Prisma Extensions

$extends is not available on SafePrismaClient. Prisma's $extends() returns a new client instance (it doesn't mutate the original), so calling it on the safe wrapper would return a raw client with no soft-delete behavior — a silent footgun.

Extend before wrapping (recommended): apply extensions to the raw client, then wrap the result:

import { PrismaClient } from '@prisma/client';
import { wrapPrismaClient } from './generated/safe-delete';

const extended = new PrismaClient().$extends({
  /* your extension */
});

const safePrisma = wrapPrismaClient(extended as unknown as PrismaClient);

The as unknown as PrismaClient cast is necessary because Prisma's extended client type is nominally incompatible with PrismaClient. The cast signals that you take responsibility for structural compatibility.

One-off raw access: if you need $extends for a specific operation, use the $prisma escape hatch:

const rawExtended = safePrisma.$prisma.$extends({ /* ... */ });
// rawExtended is a raw Prisma client — no soft-delete wrapping

Limitations and Caveats

To-one relation includes

Prisma does not support where on to-one relation includes (prisma/prisma#16049). The wrapper works around this by post-processing query results: after Prisma returns data, soft-deleted to-one relations are automatically nullified.

const user = await safePrisma.user.findFirst({
  include: {
    posts: true,    // ✓ Soft-deleted posts filtered via WHERE clause
    profile: true,  // ✓ Soft-deleted profile nullified via post-processing
  }
});
// user.profile is null if the profile was soft-deleted

Edge cases where post-processing does not apply:

• $includingDeleted — intentionally returns all records including deleted to-one relations
• $onlyDeleted — preserves deleted to-one relations (since the caller explicitly wants deleted data)
• $prisma — raw Prisma client, no wrapping at all
• If you use select on a to-one relation without including deleted_at, the post-processor cannot determine deletion status and will leave the relation as-is

Concurrent operations and isolation levels

Cascade and restore operations use transactions at the default isolation level (READ COMMITTED). Under heavy concurrent access to the same records, this can lead to:

• Restore conflicts: The conflict check (findFirst) and the actual restore (update) are not atomic — another transaction can insert a conflicting record between these steps.
• Cascade inconsistency: New child records created between the parent's findMany and the cascade updates may be missed.
• Restore timestamp matching: restoreCascade identifies cascade-deleted children by matching the exact deleted_at timestamp. If two unrelated cascade deletes occur within the same millisecond, restoring one could incorrectly restore children belonging to the other.

If your application performs concurrent soft-deletes or restores on overlapping records, use SERIALIZABLE isolation:

await safePrisma.$transaction(async (tx) => {
  await tx.user.softDelete({ where: { id: 'user-1' } });
}, { isolationLevel: 'Serializable' });

Sentinel strategy and date range queries

With the sentinel strategy, active records have deleted_at = 9999-12-31. Any raw query or $prisma escape hatch that uses date range comparisons on deleted_at will match active records unexpectedly:

-- This matches ALL active records (sentinel = 9999-12-31)
SELECT * FROM "User" WHERE deleted_at > '2024-01-01';

This only affects raw queries and $prisma — the wrapper handles sentinel comparisons correctly for all wrapped operations.

Test Coverage

Scenario	Status
findUnique rewritten correctly	Tested
include/select nested 2-3 levels deep	Tested
Relation filters (some/every/none) with deleted children	Tested
_count correctness	Tested
groupBy/aggregate exclude deleted	Tested
update/updateMany filter out soft-deleted records	Tested
Cascade with mixed children (some soft-deletable, some not)	Tested
Self-referential relations (cycles) handled safely	Tested
Deep cascade chains (4+ levels)	Tested
Wide cascade (multiple child types simultaneously)	Tested
Cascade result counts accurate (including partial cascades)	Tested
Compound primary key mangling stable	Tested
Idempotent softDelete (re-deleting is safe)	Tested
restore unmangles unique fields	Tested
restoreCascade restores parent + children with counts	Tested
Restore conflict detection	Tested
Interactive transactions receive wrapped clients	Tested
Cascade results correct in transaction context	Tested
Compile-time enforcement of return types	Tested
Fast-path optimization for leaf models	Tested
Fluent API bypass confirmed (documented limitation)	Tested
Audit events written for audited model mutations	Tested
Audit-only models (no soft-delete) with actorId	Tested
Selective audit actions (@audit(create, delete))	Tested
Audit context propagation via WrapOptions	Tested

Run the full test suite:

pnpm test

Requirements

• Node.js >= 18
• Prisma >= 7.0.0
• TypeScript >= 5.0 (recommended)

Development

# Start Postgres
docker compose up -d

# Run tests
pnpm test

# Stop Postgres
docker compose down

License

MIT