CentercodePorygon
DittoPorygonAPI

Soft Delete

Preserve data integrity with recoverable deletions

Safe

Overview

Never hard delete user data. Soft delete marks records as deleted while preserving the data for potential recovery. Cascade deletes track their source for proper restoration.

What it is

A deletion pattern using deletedAt, deletedBy, and deletedVia columns instead of removing records from the database.

Why we use it

Data recovery for accidental deletions, audit trail for compliance, and protection against malicious data destruction.

When to use

All user-created content. Register entity in soft-delete-registry.ts and add the required columns.

Retention Timeline

PeriodStatusActions
0-30 daysIn TrashCan restore
30-90 daysPast WindowCannot restore
90+ daysPurgedHard deleted by cleanup job

Quick Start

Schema Setup

Add soft delete columns to your Prisma model.

schema.prisma
// Prisma schema - add these columns to soft-deletable models
model Entity {
  id          String    @id @default(cuid())
  name        String
  // ... other fields

  // Soft delete columns
  deletedAt   DateTime?
  deletedBy   String?
  deletedVia  String?   // 'direct' | 'cascade:entityType:entityId'

  @@index([deletedAt])
}

Basic Soft Delete

Update instead of delete.

// Soft delete - mark as deleted, don't remove
await prisma.program.update({
  where: { id },
  data: {
    deletedAt: new Date(),
    deletedBy: userId,
    deletedVia: 'direct',
  },
});

Patterns

Query Filtering

All queries must filter out deleted records.

// Query filtering - always exclude deleted items
const programs = await prisma.program.findMany({
  where: { deletedAt: null },
});

// Trash query - only directly deleted items (not cascade)
const trashed = await prisma.program.findMany({
  where: {
    deletedAt: { not: null },
    deletedVia: 'direct',
  },
});

Cascade Delete

Track cascade source for proper restoration.

// Cascade delete with tracking
await prisma.$transaction(async (tx) => {
  const now = new Date();

  // Children first - mark as cascade deleted
  await tx.project.updateMany({
    where: { programId, deletedAt: null },
    data: {
      deletedAt: now,
      deletedBy: userId,
      deletedVia: `cascade:program:${programId}`,
    },
  });

  // Parent last - mark as directly deleted
  await tx.program.update({
    where: { id: programId },
    data: {
      deletedAt: now,
      deletedBy: userId,
      deletedVia: 'direct',
    },
  });
});

Restore

Validate 30-day window before restoring.

// Restore - validate 30-day window first
const deletedDaysAgo = Math.floor(
  (Date.now() - entity.deletedAt.getTime()) / (1000 * 60 * 60 * 24)
);

if (deletedDaysAgo > 30) {
  throw new ValidationError('Restoration period expired');
}

// Clear soft delete columns
await prisma.program.update({
  where: { id },
  data: {
    deletedAt: null,
    deletedBy: null,
    deletedVia: null,
  },
});

Registry

Register entities for cleanup job processing.

// src/lib/db/soft-delete-registry.ts
export const softDeleteRegistry: SoftDeleteEntity[] = [
  // Order by FK dependencies: lower = deleted first
  { model: 'file', displayName: 'File', order: 10, beforeHardDelete: cleanupBlob },
  { model: 'resource', displayName: 'Resource', order: 20 },
  { model: 'project', displayName: 'Project', order: 60 },
  { model: 'program', displayName: 'Program', order: 70 },
];

Watch Out

Never hard delete user data

Don't

// WRONG - Hard delete loses data permanently
await prisma.program.delete({
  where: { id },
});

Do

// RIGHT - Soft delete preserves data
await prisma.program.update({
  where: { id },
  data: {
    deletedAt: new Date(),
    deletedBy: userId,
    deletedVia: 'direct',
  },
});

Always filter queries to exclude deleted items

Don't

// WRONG - Returns deleted items
const programs = await prisma.program.findMany();

Do

// RIGHT - Excludes deleted items
const programs = await prisma.program.findMany({
  where: { deletedAt: null },
});
  • Cascade deletes must set deletedVia to track the source entity
  • Add @@index([deletedAt]) for query performance on soft delete columns
  • Register entity in soft-delete-registry.ts for cleanup job

API Endpoints

MethodEndpointDescription
DELETE/api/v1/{entity}/{id}Soft delete
POST/api/v1/{entity}/{id}/restoreRestore from trash
GET/api/v1/{entity}/trashList trashed items

Related

Prisma

Database patterns

Background Jobs

Cleanup job

Audit Logging

Deletion tracking