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