Back to Details

soft-delete

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

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
  • 用户账号删除（GDPR恢复期）
  • 误删数据恢复
  • 审计追踪需求
  • 保留引用完整性
  • 撤销功能

How It Works

工作原理

sql
-- 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;
sql
-- 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

TypeScript 实现

Prisma Schema

Prisma 模型

prisma
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")
}
prisma
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

软删除扩展

typescript
// 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);
typescript
// 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

包含软删除操作的服务

typescript
// 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;
  }
}
typescript
// 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

API 路由

typescript
// 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();
});
typescript
// 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

Python 实现

python
undefined
python
undefined

models/soft_delete.py

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
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

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)
undefined
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)
undefined

SQLAlchemy Service

SQLAlchemy 服务

python
undefined
python
undefined

services/user_service.py

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()
undefined
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()
undefined

Database Schema

数据库 Schema

sql
-- 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;
sql
-- 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

定时清理任务

typescript
// 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();
typescript
// 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

最佳实践

  1. Index deleted_at column - Queries filter on it constantly
  2. Use partial indexes - For active records only
  3. Set retention period - Don't keep deleted data forever
  4. Handle cascades - What happens to related records?
  5. Consider unique constraints - Deleted email should be reusable
  1. 为deleted_at列建立索引 - 该字段会被频繁用于过滤查询
  2. 使用部分索引 - 仅针对活跃记录
  3. 设置保留期限 - 不要永久保留删除的数据
  4. 处理级联关系 - 关联记录该如何处理？
  5. 考虑唯一约束 - 已删除的邮箱应该可以被重新使用

Handling Unique Constraints

处理唯一约束

sql
-- 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'));
sql
-- 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
  • 忘记在所有查询中过滤已删除记录
  • 未处理唯一约束
  • 永久保留已删除数据
  • 未为deleted_at建立索引
  • 级联软删除处理不当