软删除与审计字段

要点

  • 软删除通过 deletedAt 字段标记删除，而不是真正从数据库删除
  • 审计字段 createdAt 和 updatedAt 记录数据的创建和修改时间
  • 软删除便于数据恢复和审计，但会增加查询复杂度
  • Prisma 和 Drizzle 可以通过中间件或辅助函数自动处理软删除
  • 审计字段应该在数据库层面设置默认值，而不是应用层
  • 软删除的数据需要定期清理，避免无限增长

1. 软删除

1.1 Schema 定义

// Drizzle
import { pgTable, uuid, text, timestamp } from 'drizzle-orm/pg-core'
 
export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
  deletedAt: timestamp('deleted_at'),  // null 表示未删除
})
// Prisma
model User {
  id        String    @id @default(uuid())
  email     String    @unique
  name      String
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  deletedAt DateTime?
 
  @@map("users")
}

1.2 软删除操作

// Drizzle: 软删除
await db
  .update(users)
  .set({ deletedAt: new Date() })
  .where(eq(users.id, 'user-1'))
// Prisma: 软删除
await prisma.user.update({
  where: { id: 'user-1' },
  data: { deletedAt: new Date() },
})

1.3 查询时过滤

// Drizzle: 查询未删除的数据
const activeUsers = await db
  .select()
  .from(users)
  .where(isNull(users.deletedAt))
// Prisma: 查询未删除的数据
const activeUsers = await prisma.user.findMany({
  where: { deletedAt: null },
})

1.4 自动过滤

每次查询都要加 where(isNull(users.deletedAt)) 很麻烦。可以通过辅助函数简化：

// Drizzle: 辅助函数
function active<T extends Record<string, any>>(table: T) {
  return isNull(table.deletedAt)
}
 
const activeUsers = await db
  .select()
  .from(users)
  .where(active(users))
// Prisma: 扩展 Client
const xprisma = prisma.$extends({
  query: {
    user: {
      findMany({ args, query }) {
        args.where = { ...args.where, deletedAt: null }
        return query(args)
      },
    },
  },
})
 
// 使用
const activeUsers = await xprisma.user.findMany()  // 自动过滤已删除

1.5 恢复软删除

// Drizzle
await db
  .update(users)
  .set({ deletedAt: null })
  .where(eq(users.id, 'user-1'))
// Prisma
await prisma.user.update({
  where: { id: 'user-1' },
  data: { deletedAt: null },
})

1.6 硬删除

// Drizzle
await db.delete(users).where(eq(users.id, 'user-1'))
// Prisma
await prisma.user.delete({
  where: { id: 'user-1' },
})

2. 审计字段

2.1 createdAt

记录数据创建时间：

// Drizzle
createdAt: timestamp('created_at').defaultNow().notNull()
 
// Prisma
createdAt DateTime @default(now())

2.2 updatedAt

记录数据最后修改时间：

// Drizzle
updatedAt: timestamp('updated_at').defaultNow().notNull()
 
// Prisma
updatedAt DateTime @updatedAt

Prisma 的 @updatedAt 会在每次更新时自动更新这个字段。

Drizzle 需要手动设置：

await db
  .update(users)
  .set({ name: 'Alice Smith', updatedAt: new Date() })
  .where(eq(users.id, 'user-1'))

2.3 数据库层面设置

审计字段应该在数据库层面设置默认值，而不是应用层：

-- PostgreSQL
CREATE TABLE users (
  id UUID PRIMARY KEY,
  email TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
 
-- 自动更新 updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

这样即使绕过 ORM 直接操作数据库，审计字段也能正确更新。

3. 软删除的优缺点

3.1 优点

  • 数据可恢复：误删后可以恢复
  • 审计友好：可以看到数据的历史
  • 关联数据完整：外键关系不会因为删除而断裂
  • 合规要求：某些行业要求保留数据

3.2 缺点

  • 查询复杂：每次查询都要过滤 deletedAt
  • 数据增长：数据不会真正删除，表会越来越大
  • 唯一约束冲突：删除的记录仍然占用唯一约束
  • 性能下降：表变大后查询变慢

4. 软删除的替代方案

4.1 归档

把删除的数据移到归档表：

// 归档用户
await db.transaction(async (tx) => {
  // 插入归档表
  await tx.insert(archivedUsers).values({
    ...user,
    deletedAt: new Date(),
  })
 
  // 从原表删除
  await tx.delete(users).where(eq(users.id, 'user-1'))
})

这样原表保持干净，归档表保存历史。

4.2 版本历史

记录数据的完整历史：

// 用户历史表
export const userHistory = pgTable('user_history', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').notNull(),
  data: jsonb('data').notNull(),  // 完整的用户数据
  changedAt: timestamp('changed_at').defaultNow().notNull(),
  changedBy: uuid('changed_by'),
})

每次更新用户时，插入一条历史记录。

5. 定期清理

软删除的数据需要定期清理，避免无限增长：

// 删除 30 天前软删除的数据
await db
  .delete(users)
  .where(
    and(
      isNotNull(users.deletedAt),
      lt(users.deletedAt, new Date(Date.now() - 30 * 24 * 60 * 60 * 1000))
    )
  )

可以用定时任务（cron）定期执行清理。

6. 审计日志

除了审计字段，还可以记录详细的审计日志：

export const auditLogs = pgTable('audit_logs', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id'),
  action: text('action').notNull(),  // create, update, delete
  entityType: text('entity_type').notNull(),  // user, conversation
  entityId: text('entity_id').notNull(),
  changes: jsonb('changes'),  // 变更内容
  createdAt: timestamp('created_at').defaultNow().notNull(),
})
// 记录审计日志
await db.insert(auditLogs).values({
  userId: 'user-1',
  action: 'update',
  entityType: 'user',
  entityId: 'user-1',
  changes: { name: { from: 'Alice', to: 'Alice Smith' } },
})

审计日志可以帮助排查问题、满足合规要求。

总结

软删除和审计字段是数据管理的重要机制。软删除通过 deletedAt 标记删除，审计字段记录创建和修改时间。

这一节涉及到的几个实践：

  1. 软删除：deletedAt 字段标记删除，查询时过滤
  2. 自动过滤：通过辅助函数或 ORM 扩展自动过滤已删除数据
  3. 审计字段：createdAt 和 updatedAt 在数据库层面设置默认值
  4. 优缺点：软删除便于恢复和审计，但增加查询复杂度
  5. 替代方案：归档表、版本历史
  6. 定期清理：避免软删除数据无限增长
  7. 审计日志：记录详细的变更历史

合理使用软删除和审计字段，可以提高数据的可追溯性和安全性。

下一篇看迁移脚本管理——schema 版本管理、回滚策略、CI/CD 集成。