Soft Delete

Delete data without actually deleting it.

When to Use This Skill

• User account deletion (GDPR recovery period)

• Accidental deletion recovery

• Audit trail requirements

• Referential integrity preservation

• Undo functionality

How It Works

-- Instead of DELETE UPDATE users SET deleted_at = NOW() WHERE id = '123';

-- All queries filter out deleted records SELECT * FROM users WHERE deleted_at IS NULL;

TypeScript Implementation

Prisma Schema

model User { id String @id @default(uuid()) email String @unique name String deletedAt DateTime? @map("deleted_at") createdAt DateTime @default(now()) @map("created_at") updatedAt DateTime @updatedAt @map("updated_at")

@@map("users") }

Soft Delete Extension

// prisma/soft-delete.ts import { Prisma, PrismaClient } from '@prisma/client';

// Models that support soft delete const softDeleteModels = ['User', 'Post', 'Comment', 'Organization'];

export function softDeleteExtension(prisma: PrismaClient) { return prisma.$extends({ query: { $allModels: { async findMany({ model, operation, args, query }) { if (softDeleteModels.includes(model)) { args.where = { ...args.where, deletedAt: null }; } return query(args); }, async findFirst({ model, operation, args, query }) { if (softDeleteModels.includes(model)) { args.where = { ...args.where, deletedAt: null }; } return query(args); }, async findUnique({ model, operation, args, query }) { if (softDeleteModels.includes(model)) { // Convert to findFirst to add deletedAt filter const result = await prisma[model].findFirst({ where: { ...args.where, deletedAt: null }, }); return result; } return query(args); }, async count({ model, operation, args, query }) { if (softDeleteModels.includes(model)) { args.where = { ...args.where, deletedAt: null }; } return query(args); }, async delete({ model, operation, args, query }) { if (softDeleteModels.includes(model)) { // Soft delete instead of hard delete return prisma[model].update({ where: args.where, data: { deletedAt: new Date() }, }); } return query(args); }, async deleteMany({ model, operation, args, query }) { if (softDeleteModels.includes(model)) { return prisma[model].updateMany({ where: args.where, data: { deletedAt: new Date() }, }); } return query(args); }, }, }, }); }

// Usage const prisma = new PrismaClient(); export const db = softDeleteExtension(prisma);

Service with Soft Delete Operations

// services/user-service.ts class UserService { // Normal operations automatically filter deleted async findById(id: string): Promise<User | null> { return db.user.findUnique({ where: { id } }); }

async findAll(): Promise<User[]> { return db.user.findMany(); }

// Soft delete async delete(id: string): Promise<User> { return db.user.delete({ where: { id } }); }

// Restore deleted user async restore(id: string): Promise<User> { return db.$transaction(async (tx) => { // Use raw query to find deleted user const user = await tx.user.findFirst({ where: { id, deletedAt: { not: null } }, });

  if (!user) {
    throw new NotFoundError('User', id);
  }

  return tx.user.update({
    where: { id },
    data: { deletedAt: null },
  });
});

}

// Find deleted users (admin only) async findDeleted(): Promise<User[]> { return prisma.user.findMany({ where: { deletedAt: { not: null } }, }); }

// Permanent delete (after retention period) async permanentDelete(id: string): Promise<void> { await prisma.user.delete({ where: { id } }); }

// Purge old deleted records async purgeDeleted(olderThanDays: number = 30): Promise<number> { const cutoff = new Date(); cutoff.setDate(cutoff.getDate() - olderThanDays);

const result = await prisma.user.deleteMany({
  where: {
    deletedAt: { lt: cutoff },
  },
});

return result.count;

} }

API Routes

// routes/users.ts router.delete('/users/:id', async (req, res) => { await userService.delete(req.params.id); res.status(204).send(); });

router.post('/users/:id/restore', async (req, res) => { const user = await userService.restore(req.params.id); res.json(user); });

// Admin: view deleted users router.get('/admin/users/deleted', adminOnly, async (req, res) => { const users = await userService.findDeleted(); res.json(users); });

// Admin: permanent delete router.delete('/admin/users/:id/permanent', adminOnly, async (req, res) => { await userService.permanentDelete(req.params.id); res.status(204).send(); });

Python Implementation

models/soft_delete.py

from sqlalchemy import Column, DateTime, event from sqlalchemy.orm import Query from datetime import datetime

class SoftDeleteMixin: deleted_at = Column(DateTime, nullable=True, index=True)

def soft_delete(self):
    self.deleted_at = datetime.utcnow()

def restore(self):
    self.deleted_at = None

@property
def is_deleted(self) -> bool:
    return self.deleted_at is not None

Custom query class that filters deleted by default

class SoftDeleteQuery(Query): def new(cls, *args, **kwargs): obj = super().new(cls) obj._with_deleted = kwargs.pop('_with_deleted', False) return obj

def __init__(self, *args, **kwargs):
    kwargs.pop('_with_deleted', None)
    super().__init__(*args, **kwargs)

def with_deleted(self):
    return self.__class__(
        self._entity_from_pre_ent_zero().entity,
        session=self.session,
        _with_deleted=True,
    )

def _compile_context(self, *args, **kwargs):
    if not self._with_deleted:
        # Auto-filter deleted records
        self = self.filter_by(deleted_at=None)
    return super()._compile_context(*args, **kwargs)

SQLAlchemy Service

services/user_service.py

class UserService: def init(self, session: Session): self.session = session

def find_all(self) -> list[User]:
    # Automatically excludes deleted
    return self.session.query(User).all()

def delete(self, user_id: str) -> None:
    user = self.session.query(User).get(user_id)
    if user:
        user.soft_delete()
        self.session.commit()

def restore(self, user_id: str) -> User:
    user = self.session.query(User).with_deleted().get(user_id)
    if not user or not user.is_deleted:
        raise NotFoundError("User", user_id)
    user.restore()
    self.session.commit()
    return user

def find_deleted(self) -> list[User]:
    return self.session.query(User).with_deleted().filter(
        User.deleted_at.isnot(None)
    ).all()

def permanent_delete(self, user_id: str) -> None:
    self.session.query(User).with_deleted().filter(
        User.id == user_id
    ).delete()
    self.session.commit()

Database Schema

-- Add soft delete column ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP; CREATE INDEX idx_users_deleted_at ON users(deleted_at);

-- Partial index for active records (PostgreSQL) CREATE INDEX idx_users_active ON users(id) WHERE deleted_at IS NULL;

Scheduled Cleanup Job

// jobs/purge-deleted.ts import { CronJob } from 'cron';

// Run daily at 3 AM const purgeJob = new CronJob('0 3 * * *', async () => { const services = [userService, postService, commentService];

for (const service of services) { const count = await service.purgeDeleted(30); // 30 day retention console.log(Purged ${count} deleted records from ${service.constructor.name}); } });

purgeJob.start();

Best Practices

• Index deleted_at column - Queries filter on it constantly

• Use partial indexes - For active records only

• Set retention period - Don't keep deleted data forever

• Handle cascades - What happens to related records?

• Consider unique constraints - Deleted email should be reusable

Handling Unique Constraints

-- Option 1: Partial unique index (PostgreSQL) CREATE UNIQUE INDEX idx_users_email_active ON users(email) WHERE deleted_at IS NULL;

-- Option 2: Include deleted_at in unique constraint ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email, COALESCE(deleted_at, '1970-01-01'));

Common Mistakes

• Forgetting to filter in all queries

• Not handling unique constraints

• Keeping deleted data forever

• Not indexing deleted_at

• Cascading soft deletes incorrectly