审计日志与操作追踪

审计日志是企业级 SaaS 的标配——SOC 2 审计要求、客户安全需求、问题排查都依赖它。审计日志回答"谁在什么时候做了什么"。本章从架构设计到查询面板，完整实现 SaaS 审计系统。

1. 审计日志架构

1.1 审计日志 vs 应用日志

维度	审计日志	应用日志
目的	合规、安全、追溯	调试、监控
受众	租户管理员、审计员	开发者
存储	持久化、不可删改	可轮转
内容	谁做了什么	系统运行状态
保留	1-7 年	30-90 天

1.2 数据模型

// lib/db/schema.ts
export const auditLogs = pgTable('audit_logs', {
  id: uuid('id').primaryKey().defaultRandom(),
  tenantId: uuid('tenant_id').references(() => tenants.id).notNull(),
  actorId: uuid('actor_id').references(() => users.id),
  actorEmail: text('actor_email'),       // 冗余存储，用户删除后仍可查
  action: text('action').notNull(),       // 'member.invited'
  targetType: text('target_type'),        // 'member', 'project', 'subscription'
  targetId: text('target_id'),            // 被操作对象 ID
  metadata: jsonb('metadata'),            // 变更详情
  ipAddress: text('ip_address'),
  userAgent: text('user_agent'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
})
 
// 索引：按租户+时间查询是最常见的访问模式
// CREATE INDEX idx_audit_tenant_time ON audit_logs (tenant_id, created_at DESC)

1.3 核心原则

原则	说明
不可变	审计日志只能写入，不能修改或删除
冗余	存储快照（如 actor_email），不依赖外键 JOIN
异步	日志写入不应阻塞业务操作
结构化	metadata 用 JSON，便于查询和分析

2. 日志采集

2.1 日志记录函数

// lib/audit.ts
import { headers } from 'next/headers'
 
interface AuditLogEntry {
  tenantId: string
  actorId: string
  actorEmail: string
  action: string
  targetType?: string
  targetId?: string
  metadata?: Record<string, any>
}
 
export async function logAudit(entry: AuditLogEntry) {
  const headerList = await headers()
 
  await db.insert(auditLogs).values({
    ...entry,
    ipAddress: headerList.get('x-forwarded-for')?.split(',')[0] || 'unknown',
    userAgent: headerList.get('user-agent') || 'unknown',
  })
}

2.2 Server Action 中使用

export async function removeMember(membershipId: string) {
  const { session, membership } = await requirePermission('member:remove')
 
  const [target] = await db.select().from(memberships)
    .innerJoin(users, eq(memberships.userId, users.id))
    .where(eq(memberships.id, membershipId))
 
  await db.delete(memberships).where(eq(memberships.id, membershipId))
 
  await logAudit({
    tenantId: membership.tenantId,
    actorId: session.user.id,
    actorEmail: session.user.email,
    action: 'member.removed',
    targetType: 'member',
    targetId: target.memberships.userId,
    metadata: { removedEmail: target.users.email, role: target.memberships.role },
  })
}

2.3 标准事件分类

类别	事件	严重级别
认证	`auth.login`, `auth.logout`, `auth.failed`	中
成员	`member.invited`, `member.removed`, `role.changed`	高
组织	`org.updated`, `org.deleted`	高
计费	`subscription.started`, `subscription.canceled`	高
数据	`project.created`, `project.deleted`	中
设置	`settings.changed`, `api_key.created`	中

2.4 异步日志写入

审计日志不应阻塞业务操作。在高频场景中，用 Inngest 异步写入：

// lib/inngest/functions/audit-log.ts
import { inngest } from '../client'
 
export const writeAuditLog = inngest.createFunction(
  { id: 'write-audit-log', retries: 3 },
  { event: 'audit/log' },
  async ({ event }) => {
    const { entry, ipAddress, userAgent } = event.data
 
    await db.insert(auditLogs).values({
      ...entry,
      ipAddress,
      userAgent,
    })
  }
)
 
// 异步版本的 logAudit
export async function logAuditAsync(entry: AuditLogEntry) {
  const headerList = await headers()
 
  await inngest.send({
    name: 'audit/log',
    data: {
      entry,
      ipAddress: headerList.get('x-forwarded-for')?.split(',')[0] || 'unknown',
      userAgent: headerList.get('user-agent') || 'unknown',
    },
  })
}

2.5 变更详情记录

对于设置变更等操作，metadata 中应记录变更前后的值：

export async function updateOrganizationSettings(formData: FormData) {
  const { session, membership } = await requirePermission('billing:manage')
 
  const [currentTenant] = await db.select().from(tenants)
    .where(eq(tenants.id, membership.tenantId))
 
  const newName = formData.get('name') as string
  const newSlug = formData.get('slug') as string
 
  await db.update(tenants)
    .set({ name: newName, slug: newSlug, updatedAt: new Date() })
    .where(eq(tenants.id, membership.tenantId))
 
  // 记录变更前后的差异
  await logAudit({
    tenantId: membership.tenantId,
    actorId: session.user.id,
    actorEmail: session.user.email,
    action: 'org.updated',
    targetType: 'organization',
    targetId: membership.tenantId,
    metadata: {
      changes: {
        name: { from: currentTenant.name, to: newName },
        slug: { from: currentTenant.slug, to: newSlug },
      },
    },
  })
}

2.6 登录事件记录

// 在登录成功/失败的回调中记录
export async function recordLoginEvent(
  userId: string | null,
  email: string,
  success: boolean,
  reason?: string
) {
  const headerList = await headers()
 
  await db.insert(auditLogs).values({
    tenantId: 'system',  // 登录事件不属于特定租户
    actorId: userId,
    actorEmail: email,
    action: success ? 'auth.login' : 'auth.failed',
    metadata: success ? undefined : { reason },
    ipAddress: headerList.get('x-forwarded-for')?.split(',')[0] || 'unknown',
    userAgent: headerList.get('user-agent') || 'unknown',
  })
}

3. 日志查询

3.1 查询 Server Action

// app/(dashboard)/settings/audit-log/actions.ts
export async function getAuditLogs(filters: {
  page?: number
  action?: string
  actorId?: string
  startDate?: Date
  endDate?: Date
}) {
  const { membership } = await requirePermission('billing:manage')
  const { page = 1, action, actorId, startDate, endDate } = filters
  const limit = 50
 
  const conditions = [eq(auditLogs.tenantId, membership.tenantId)]
  if (action) conditions.push(eq(auditLogs.action, action))
  if (actorId) conditions.push(eq(auditLogs.actorId, actorId))
  if (startDate) conditions.push(gte(auditLogs.createdAt, startDate))
  if (endDate) conditions.push(lte(auditLogs.createdAt, endDate))
 
  const [logs, [{ count }]] = await Promise.all([
    db.select().from(auditLogs)
      .where(and(...conditions))
      .orderBy(desc(auditLogs.createdAt))
      .limit(limit).offset((page - 1) * limit),
    db.select({ count: sql<number>`count(*)` }).from(auditLogs)
      .where(and(...conditions)),
  ])
 
  return { logs, total: count, page, totalPages: Math.ceil(count / limit) }
}

3.2 人类可读的事件描述

// lib/audit/format.ts
const ACTION_LABELS: Record<string, string> = {
  'auth.login': 'Signed in',
  'auth.logout': 'Signed out',
  'auth.failed': 'Failed sign-in attempt',
  'member.invited': 'Invited a member',
  'member.removed': 'Removed a member',
  'member.joined': 'Joined the organization',
  'role.changed': 'Changed member role',
  'org.created': 'Created organization',
  'org.updated': 'Updated organization settings',
  'org.deleted': 'Deleted organization',
  'project.created': 'Created a project',
  'project.deleted': 'Deleted a project',
  'subscription.started': 'Started a subscription',
  'subscription.canceled': 'Canceled subscription',
  'api_key.created': 'Created an API key',
  'api_key.revoked': 'Revoked an API key',
}
 
export function formatAuditAction(action: string): string {
  return ACTION_LABELS[action] || action
}
 
export function formatAuditDescription(log: AuditLog): string {
  const label = formatAuditAction(log.action)
  const actor = log.actorEmail || 'System'
 
  if (log.metadata?.changes) {
    const fields = Object.keys(log.metadata.changes).join(', ')
    return `${actor} ${label.toLowerCase()} (changed: ${fields})`
  }
 
  if (log.metadata?.removedEmail) {
    return `${actor} ${label.toLowerCase()}: ${log.metadata.removedEmail}`
  }
 
  return `${actor} ${label.toLowerCase()}`
}

3.3 审计日志 UI 页面

// app/(dashboard)/settings/audit-log/page.tsx
import { getAuditLogs } from './actions'
import { formatAuditAction } from '@/lib/audit/format'
 
export default async function AuditLogPage({
  searchParams,
}: {
  searchParams: { page?: string; action?: string }
}) {
  const page = Number(searchParams.page) || 1
  const { logs, total, totalPages } = await getAuditLogs({
    page,
    action: searchParams.action,
  })
 
  return (
    <div className="space-y-6">
      <div className="flex items-center justify-between">
        <h2 className="text-xl font-semibold">Audit Log</h2>
        <ExportButton />
      </div>
 
      <AuditFilters />
 
      <table className="w-full text-sm">
        <thead>
          <tr className="border-b text-left text-muted-foreground">
            <th className="pb-2">Time</th>
            <th className="pb-2">Actor</th>
            <th className="pb-2">Action</th>
            <th className="pb-2">IP Address</th>
          </tr>
        </thead>
        <tbody>
          {logs.map((log) => (
            <tr key={log.id} className="border-b">
              <td className="py-3">{log.createdAt.toLocaleString()}</td>
              <td className="py-3">{log.actorEmail}</td>
              <td className="py-3">
                <span className="rounded bg-muted px-2 py-1 text-xs">
                  {formatAuditAction(log.action)}
                </span>
              </td>
              <td className="py-3 text-muted-foreground">{log.ipAddress}</td>
            </tr>
          ))}
        </tbody>
      </table>
 
      <Pagination page={page} totalPages={totalPages} total={total} />
    </div>
  )
}

3.4 审计日志详情展开

// components/audit-log-detail.tsx
'use client'
 
import { useState } from 'react'
 
export function AuditLogRow({ log }: { log: AuditLog }) {
  const [expanded, setExpanded] = useState(false)
 
  return (
    <>
      <tr className="border-b cursor-pointer" onClick={() => setExpanded(!expanded)}>
        <td className="py-3">{log.createdAt.toLocaleString()}</td>
        <td className="py-3">{log.actorEmail}</td>
        <td className="py-3">{formatAuditAction(log.action)}</td>
        <td className="py-3">{log.ipAddress}</td>
      </tr>
      {expanded && log.metadata && (
        <tr>
          <td colSpan={4} className="bg-muted/50 p-4">
            <pre className="text-xs overflow-x-auto">
              {JSON.stringify(log.metadata, null, 2)}
            </pre>
            <p className="mt-2 text-xs text-muted-foreground">
              User Agent: {log.userAgent}
            </p>
          </td>
        </tr>
      )}
    </>
  )
}

3.5 筛选器组件

// components/audit-filters.tsx
'use client'
 
import { useRouter, useSearchParams } from 'next/navigation'
 
const ACTION_CATEGORIES = [
  { label: 'All', value: '' },
  { label: 'Authentication', value: 'auth' },
  { label: 'Members', value: 'member' },
  { label: 'Organization', value: 'org' },
  { label: 'Billing', value: 'subscription' },
  { label: 'Projects', value: 'project' },
]
 
export function AuditFilters() {
  const router = useRouter()
  const params = useSearchParams()
 
  function onFilterChange(action: string) {
    const newParams = new URLSearchParams(params.toString())
    if (action) newParams.set('action', action)
    else newParams.delete('action')
    newParams.set('page', '1')
    router.push(`?${newParams.toString()}`)
  }
 
  return (
    <div className="flex gap-2">
      {ACTION_CATEGORIES.map((cat) => (
        <button
          key={cat.value}
          onClick={() => onFilterChange(cat.value)}
          className={`rounded-full px-3 py-1 text-sm ${
            params.get('action') === cat.value
              ? 'bg-primary text-primary-foreground'
              : 'bg-muted'
          }`}
        >
          {cat.label}
        </button>
      ))}
    </div>
  )
}

4. 数据保留与合规

4.1 保留策略

合规标准	最低保留期
SOC 2	1 年
GDPR	数据最小化原则
HIPAA	6 年
PCI DSS	1 年

4.2 数据归档

大量审计日志会影响数据库性能。定期将旧数据归档到冷存储：

// lib/inngest/functions/archive-audit-logs.ts
export const archiveAuditLogs = inngest.createFunction(
  { id: 'archive-audit-logs' },
  { cron: '0 2 1 * *' },  // 每月 1 日凌晨 2 点
  async () => {
    const cutoffDate = new Date()
    cutoffDate.setMonth(cutoffDate.getMonth() - 12)  // 12 个月前
 
    // 1. 导出到 S3
    const logs = await db.select().from(auditLogs)
      .where(lt(auditLogs.createdAt, cutoffDate))
      .orderBy(auditLogs.createdAt)
 
    if (logs.length === 0) return { archived: 0 }
 
    const jsonl = logs.map(l => JSON.stringify(l)).join('\n')
    const key = `audit-archive/${cutoffDate.toISOString().slice(0, 7)}.jsonl`
 
    await s3.send(new PutObjectCommand({
      Bucket: process.env.ARCHIVE_BUCKET!,
      Key: key,
      Body: jsonl,
      ContentType: 'application/jsonl',
    }))
 
    // 2. 删除已归档的记录
    await db.delete(auditLogs).where(lt(auditLogs.createdAt, cutoffDate))
 
    return { archived: logs.length, key }
  }
)

4.3 数据导出

// app/(dashboard)/settings/audit-log/actions.ts
export async function exportAuditLogs(startDate: Date, endDate: Date) {
  const { membership } = await requirePermission('org:delete')
 
  const logs = await db.select().from(auditLogs)
    .where(and(
      eq(auditLogs.tenantId, membership.tenantId),
      gte(auditLogs.createdAt, startDate),
      lte(auditLogs.createdAt, endDate),
    ))
    .orderBy(auditLogs.createdAt)
 
  // 转为 CSV
  const csv = [
    'timestamp,actor,action,target_type,target_id,ip_address,metadata',
    ...logs.map(l =>
      [
        l.createdAt.toISOString(),
        l.actorEmail,
        l.action,
        l.targetType || '',
        l.targetId || '',
        l.ipAddress || '',
        l.metadata ? JSON.stringify(l.metadata) : '',
      ].map(v => `"${v}"`).join(',')
    ),
  ].join('\n')
 
  return csv
}

4.4 导出下载 API Route

// app/api/audit-log/export/route.ts
export async function GET(request: NextRequest) {
  const { membership } = await requirePermission('org:delete')
 
  const { searchParams } = request.nextUrl
  const startDate = new Date(searchParams.get('start') || Date.now() - 30 * 86400000)
  const endDate = new Date(searchParams.get('end') || Date.now())
 
  const csv = await exportAuditLogs(startDate, endDate)
 
  return new Response(csv, {
    headers: {
      'Content-Type': 'text/csv',
      'Content-Disposition': `attachment; filename="audit-log-${startDate.toISOString().slice(0, 10)}.csv"`,
    },
  })
}

4.5 不可变性保障

措施	说明
数据库权限	审计表只授 INSERT + SELECT，不授 UPDATE + DELETE
应用层	不提供修改/删除审计日志的 API
归档	过期日志归档到 S3/冷存储
校验	可选：Hash Chain 确保日志未被篡改

-- PostgreSQL 数据库级别限制
REVOKE UPDATE, DELETE ON audit_logs FROM app_user;
GRANT INSERT, SELECT ON audit_logs TO app_user;

5. 高级特性

5.1 Webhook 通知

对于高严重级别的审计事件，实时通知管理员：

// lib/audit/notify.ts
const HIGH_SEVERITY_ACTIONS = [
  'member.removed', 'org.deleted', 'role.changed',
  'subscription.canceled', 'api_key.created',
]
 
export async function notifyIfHighSeverity(entry: AuditLogEntry) {
  if (!HIGH_SEVERITY_ACTIONS.includes(entry.action)) return
 
  // 发送 Slack 通知
  if (process.env.SLACK_AUDIT_WEBHOOK) {
    await fetch(process.env.SLACK_AUDIT_WEBHOOK, {
      method: 'POST',
      body: JSON.stringify({
        text: `🔔 Audit Alert: ${entry.actorEmail} performed *${entry.action}*`,
        blocks: [
          {
            type: 'section',
            text: {
              type: 'mrkdwn',
              text: [
                `*Action:* ${formatAuditAction(entry.action)}`,
                `*Actor:* ${entry.actorEmail}`,
                `*Target:* ${entry.targetType}/${entry.targetId}`,
                `*Time:* ${new Date().toISOString()}`,
              ].join('\n'),
            },
          },
        ],
      }),
    })
  }
}

5.2 异常检测

// lib/audit/anomaly.ts
export async function detectAnomalies(tenantId: string) {
  const oneHourAgo = new Date(Date.now() - 60 * 60 * 1000)
 
  // 检测 1：同一用户短时间内大量操作
  const heavyUsers = await db.select({
    actorId: auditLogs.actorId,
    actorEmail: auditLogs.actorEmail,
    count: sql<number>`count(*)`,
  })
    .from(auditLogs)
    .where(and(
      eq(auditLogs.tenantId, tenantId),
      gte(auditLogs.createdAt, oneHourAgo),
    ))
    .groupBy(auditLogs.actorId, auditLogs.actorEmail)
    .having(sql`count(*) > 100`)
 
  // 检测 2：登录失败次数异常
  const failedLogins = await db.select({
    count: sql<number>`count(*)`,
  })
    .from(auditLogs)
    .where(and(
      eq(auditLogs.tenantId, tenantId),
      eq(auditLogs.action, 'auth.failed'),
      gte(auditLogs.createdAt, oneHourAgo),
    ))
 
  return {
    heavyUsers,
    failedLoginCount: failedLogins[0]?.count || 0,
    hasAnomalies: heavyUsers.length > 0 || (failedLogins[0]?.count || 0) > 10,
  }
}

5.3 统计汇总

// 审计日志仪表盘数据
export async function getAuditStats(tenantId: string) {
  const thirtyDaysAgo = new Date(Date.now() - 30 * 86400000)
 
  const [actionBreakdown, dailyTrend, topActors] = await Promise.all([
    // 按事件类型统计
    db.select({
      action: auditLogs.action,
      count: sql<number>`count(*)`,
    })
      .from(auditLogs)
      .where(and(
        eq(auditLogs.tenantId, tenantId),
        gte(auditLogs.createdAt, thirtyDaysAgo),
      ))
      .groupBy(auditLogs.action)
      .orderBy(sql`count(*) desc`),
 
    // 每日趋势
    db.select({
      date: sql<string>`date(${auditLogs.createdAt})`,
      count: sql<number>`count(*)`,
    })
      .from(auditLogs)
      .where(and(
        eq(auditLogs.tenantId, tenantId),
        gte(auditLogs.createdAt, thirtyDaysAgo),
      ))
      .groupBy(sql`date(${auditLogs.createdAt})`)
      .orderBy(sql`date(${auditLogs.createdAt})`),
 
    // 最活跃用户
    db.select({
      actorEmail: auditLogs.actorEmail,
      count: sql<number>`count(*)`,
    })
      .from(auditLogs)
      .where(and(
        eq(auditLogs.tenantId, tenantId),
        gte(auditLogs.createdAt, thirtyDaysAgo),
      ))
      .groupBy(auditLogs.actorEmail)
      .orderBy(sql`count(*) desc`)
      .limit(10),
  ])
 
  return { actionBreakdown, dailyTrend, topActors }
}

本章小结

数据模型：冗余存储 actor_email，不依赖外键 JOIN，metadata 记录变更前后差异
核心原则：不可变、冗余、异步、结构化
日志采集：logAudit() 同步入口 + logAuditAsync() 异步入口，自动获取 IP/UA
变更详情：metadata 中存储 { changes: { field: { from, to } } } 结构
登录审计：记录成功/失败登录，含 IP 和 User-Agent
查询面板：按时间/操作类型/人员过滤，分页 + 总数，可展开查看 metadata
筛选器：按类别筛选（认证/成员/组织/计费/项目）
合规：SOC 2 最少保留 1 年，数据库级 REVOKE UPDATE/DELETE
归档：Inngest 定时任务，12 个月前日志归档到 S3 后删除
导出：CSV 格式下载，含完整 metadata
高级：高严重事件 Slack 通知、异常检测（操作频率/登录失败）、统计汇总