前情提要

經過 Day 26 的 API 速率限制建置,我們已經有了完整的防濫用機制。今天我們要為 Kyo System 實作審計日誌(Audit Log)與合規追蹤系統。在企業級 SaaS 產品中,審計日誌不僅是安全要求,更是合規性(GDPR、SOC2、HIPAA)的必要條件。我們需要完整記錄「誰、何時、做了什麼、結果如何」,並確保日誌的完整性與不可篡改性。

審計日誌架構設計

/**
 * 審計日誌完整架構
 *
 * ┌──────────────────────────────────────────────┐
 * │         Audit Log Architecture              │
 * └──────────────────────────────────────────────┘
 *
 * 應用層
 * ┌─────────────────────────────────────┐
 * │  Fastify Request Handler            │
 * │  ↓                                  │
 * │  Audit Plugin (Hook)                │
 * │  ↓                                  │
 * │  Extract Context                    │
 * │  - User ID                          │
 * │  - Tenant ID                        │
 * │  - IP Address                       │
 * │  - User Agent                       │
 * │  - Request ID                       │
 * └─────────────────────────────────────┘
 *         ↓
 * 日誌層
 * ┌─────────────────────────────────────┐
 * │  Audit Logger Service               │
 * │  ↓                                  │
 * │  Structured Logging                 │
 * │  - Action Type                      │
 * │  - Resource Type                    │
 * │  - Resource ID                      │
 * │  - Changes (Before/After)           │
 * │  - Metadata                         │
 * │  - Result (Success/Failure)         │
 * └─────────────────────────────────────┘
 *         ↓
 * 儲存層
 * ┌─────────────────────────────────────┐
 * │  PostgreSQL (主要儲存)              │
 * │  - audit_logs table                 │
 * │  - Partitioned by date              │
 * │  - Indexed for queries              │
 * │                                      │
 * │  TimescaleDB (時間序列優化)        │
 * │  - Hypertable                       │
 * │  - Automatic partitioning           │
 * │  - Compression                      │
 * │                                      │
 * │  S3 (長期歸檔)                      │
 * │  - Daily export                     │
 * │  - Encrypted                        │
 * │  - Glacier transition               │
 * └─────────────────────────────────────┘
 *
 * 五個關鍵原則 (5W1H):
 * 1. Who - 誰執行了操作
 * 2. When - 何時執行
 * 3. What - 做了什麼操作
 * 4. Where - 從哪裡執行 (IP, Location)
 * 5. Why - 為何執行 (Business Context)
 * 6. How - 結果如何 (Success/Failure)
 */

審計日誌資料模型

// packages/kyo-core/src/audit/types.ts

/**
 * 審計事件類型
 */
export enum AuditAction {
  // 認證相關
  LOGIN = 'auth.login',
  LOGOUT = 'auth.logout',
  LOGIN_FAILED = 'auth.login_failed',
  PASSWORD_CHANGED = 'auth.password_changed',
  MFA_ENABLED = 'auth.mfa_enabled',
  MFA_DISABLED = 'auth.mfa_disabled',

  // 用戶管理
  USER_CREATED = 'user.created',
  USER_UPDATED = 'user.updated',
  USER_DELETED = 'user.deleted',
  USER_INVITED = 'user.invited',
  USER_ROLE_CHANGED = 'user.role_changed',

  // 資源操作
  RESOURCE_CREATED = 'resource.created',
  RESOURCE_UPDATED = 'resource.updated',
  RESOURCE_DELETED = 'resource.deleted',
  RESOURCE_VIEWED = 'resource.viewed',
  RESOURCE_EXPORTED = 'resource.exported',

  // 權限管理
  PERMISSION_GRANTED = 'permission.granted',
  PERMISSION_REVOKED = 'permission.revoked',
  ROLE_CREATED = 'role.created',
  ROLE_UPDATED = 'role.updated',
  ROLE_DELETED = 'role.deleted',

  // 設定變更
  SETTING_UPDATED = 'setting.updated',
  INTEGRATION_ADDED = 'integration.added',
  INTEGRATION_REMOVED = 'integration.removed',

  // API 操作
  API_KEY_CREATED = 'api.key_created',
  API_KEY_REVOKED = 'api.key_revoked',
  API_CALL = 'api.call',

  // 敏感操作
  SENSITIVE_DATA_ACCESSED = 'sensitive.data_accessed',
  SENSITIVE_DATA_EXPORTED = 'sensitive.data_exported',
  SENSITIVE_DATA_DELETED = 'sensitive.data_deleted',
}

/**
 * 資源類型
 */
export enum ResourceType {
  USER = 'user',
  TENANT = 'tenant',
  ROLE = 'role',
  PERMISSION = 'permission',
  API_KEY = 'api_key',
  OTP_REQUEST = 'otp_request',
  SETTING = 'setting',
  INTEGRATION = 'integration',
}

/**
 * 審計事件結果
 */
export enum AuditResult {
  SUCCESS = 'success',
  FAILURE = 'failure',
  PARTIAL = 'partial',
}

/**
 * 審計日誌條目
 */
export interface AuditLogEntry {
  id: string;
  timestamp: Date;

  // 執行者資訊
  actor: {
    userId: string;
    username?: string;
    email?: string;
    type: 'user' | 'system' | 'api_key';
  };

  // 租戶資訊 (多租戶隔離)
  tenantId: string;

  // 操作資訊
  action: AuditAction;
  result: AuditResult;

  // 資源資訊
  resource?: {
    type: ResourceType;
    id: string;
    name?: string;
  };

  // 變更內容 (Before/After)
  changes?: {
    before?: Record<string, any>;
    after?: Record<string, any>;
  };

  // 請求上下文
  context: {
    requestId: string;
    ipAddress: string;
    userAgent: string;
    location?: {
      country?: string;
      city?: string;
    };
  };

  // 額外元資料
  metadata?: Record<string, any>;

  // 錯誤資訊 (如果失敗)
  error?: {
    message: string;
    code?: string;
    stack?: string;
  };
}

/**
 * 審計日誌查詢參數
 */
export interface AuditLogQuery {
  tenantId?: string;
  userId?: string;
  action?: AuditAction | AuditAction[];
  resourceType?: ResourceType;
  resourceId?: string;
  result?: AuditResult;
  startDate?: Date;
  endDate?: Date;
  ipAddress?: string;
  limit?: number;
  offset?: number;
}

PostgreSQL 審計表設計

-- migrations/001_create_audit_logs.sql

/**
 * 審計日誌表
 *
 * 設計考量:
 * 1. Partitioning by date (按月分區)
 * 2. JSONB for flexible metadata
 * 3. GIN index for JSONB queries
 * 4. Composite index for common queries
 */

-- 建立 audit_logs 表
CREATE TABLE audit_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  -- Actor (執行者)
  actor_user_id UUID,
  actor_username TEXT,
  actor_email TEXT,
  actor_type TEXT NOT NULL CHECK (actor_type IN ('user', 'system', 'api_key')),

  -- Tenant (多租戶隔離)
  tenant_id UUID NOT NULL,

  -- Action
  action TEXT NOT NULL,
  result TEXT NOT NULL CHECK (result IN ('success', 'failure', 'partial')),

  -- Resource
  resource_type TEXT,
  resource_id TEXT,
  resource_name TEXT,

  -- Changes (Before/After)
  changes_before JSONB,
  changes_after JSONB,

  -- Context
  request_id TEXT NOT NULL,
  ip_address INET NOT NULL,
  user_agent TEXT,
  location_country TEXT,
  location_city TEXT,

  -- Metadata
  metadata JSONB,

  -- Error (if failure)
  error_message TEXT,
  error_code TEXT,
  error_stack TEXT

) PARTITION BY RANGE (created_at);

-- 建立索引

-- 1. 租戶隔離查詢
CREATE INDEX idx_audit_logs_tenant_created
ON audit_logs (tenant_id, created_at DESC);

-- 2. 用戶操作查詢
CREATE INDEX idx_audit_logs_user_created
ON audit_logs (actor_user_id, created_at DESC)
WHERE actor_user_id IS NOT NULL;

-- 3. 操作類型查詢
CREATE INDEX idx_audit_logs_action_created
ON audit_logs (action, created_at DESC);

-- 4. 資源查詢
CREATE INDEX idx_audit_logs_resource
ON audit_logs (resource_type, resource_id, created_at DESC)
WHERE resource_type IS NOT NULL AND resource_id IS NOT NULL;

-- 5. IP 地址查詢
CREATE INDEX idx_audit_logs_ip
ON audit_logs (ip_address, created_at DESC);

-- 6. JSONB metadata 查詢 (GIN index)
CREATE INDEX idx_audit_logs_metadata
ON audit_logs USING GIN (metadata);

-- 7. 失敗操作查詢
CREATE INDEX idx_audit_logs_failures
ON audit_logs (result, created_at DESC)
WHERE result = 'failure';

-- 建立分區 (每月一個分區)
-- 2024年1月
CREATE TABLE audit_logs_2024_01 PARTITION OF audit_logs
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- 2024年2月
CREATE TABLE audit_logs_2024_02 PARTITION OF audit_logs
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- ... 依此類推

/**
 * 自動建立未來分區的函數
 */
CREATE OR REPLACE FUNCTION create_audit_log_partition()
RETURNS void AS $$
DECLARE
  partition_date DATE;
  partition_name TEXT;
  start_date DATE;
  end_date DATE;
BEGIN
  -- 建立未來 3 個月的分區
  FOR i IN 0..2 LOOP
    partition_date := DATE_TRUNC('month', NOW() + INTERVAL '1 month' * i);
    partition_name := 'audit_logs_' || TO_CHAR(partition_date, 'YYYY_MM');
    start_date := partition_date;
    end_date := partition_date + INTERVAL '1 month';

    -- 檢查分區是否已存在
    IF NOT EXISTS (
      SELECT 1 FROM pg_class WHERE relname = partition_name
    ) THEN
      EXECUTE format(
        'CREATE TABLE %I PARTITION OF audit_logs FOR VALUES FROM (%L) TO (%L)',
        partition_name,
        start_date,
        end_date
      );
      RAISE NOTICE 'Created partition: %', partition_name;
    END IF;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 定期執行 (透過 pg_cron 或應用層)
-- SELECT create_audit_log_partition();

Audit Logger Service 實作

// packages/kyo-core/src/audit/audit-logger.ts
import { Pool } from 'pg';
import { v4 as uuidv4 } from 'uuid';
import {
  AuditLogEntry,
  AuditAction,
  AuditResult,
  ResourceType,
  AuditLogQuery,
} from './types';

/**
 * PII 欄位遮罩設定
 */
const PII_FIELDS = [
  'password',
  'token',
  'secret',
  'apiKey',
  'creditCard',
  'ssn',
  'phoneNumber',
];

/**
 * Audit Logger Service
 *
 * 功能:
 * - 記錄審計事件
 * - PII 資料遮罩
 * - 批次寫入優化
 * - 查詢審計日誌
 */
export class AuditLogger {
  private pool: Pool;
  private batchBuffer: AuditLogEntry[] = [];
  private batchTimer: NodeJS.Timeout | null = null;
  private readonly BATCH_SIZE = 100;
  private readonly BATCH_INTERVAL = 5000; // 5 秒

  constructor(pool: Pool) {
    this.pool = pool;
  }

  /**
   * 記錄審計事件
   */
  async log(entry: Omit<AuditLogEntry, 'id' | 'timestamp'>): Promise<void> {
    const fullEntry: AuditLogEntry = {
      id: uuidv4(),
      timestamp: new Date(),
      ...entry,
    };

    // 遮罩 PII 資料
    this.maskPII(fullEntry);

    // 加入批次緩衝
    this.batchBuffer.push(fullEntry);

    // 如果達到批次大小,立即寫入
    if (this.batchBuffer.length >= this.BATCH_SIZE) {
      await this.flush();
    } else {
      // 否則等待計時器
      this.scheduleBatchWrite();
    }
  }

  /**
   * PII 資料遮罩
   */
  private maskPII(entry: AuditLogEntry): void {
    if (entry.changes?.before) {
      entry.changes.before = this.maskObject(entry.changes.before);
    }

    if (entry.changes?.after) {
      entry.changes.after = this.maskObject(entry.changes.after);
    }

    if (entry.metadata) {
      entry.metadata = this.maskObject(entry.metadata);
    }
  }

  /**
   * 遮罩物件中的敏感欄位
   */
  private maskObject(obj: Record<string, any>): Record<string, any> {
    const masked: Record<string, any> = {};

    for (const [key, value] of Object.entries(obj)) {
      if (PII_FIELDS.some((field) => key.toLowerCase().includes(field.toLowerCase()))) {
        // 遮罩敏感欄位
        masked[key] = '***MASKED***';
      } else if (typeof value === 'object' && value !== null) {
        // 遞迴處理巢狀物件
        masked[key] = this.maskObject(value);
      } else {
        masked[key] = value;
      }
    }

    return masked;
  }

  /**
   * 排程批次寫入
   */
  private scheduleBatchWrite(): void {
    if (this.batchTimer) return;

    this.batchTimer = setTimeout(async () => {
      await this.flush();
      this.batchTimer = null;
    }, this.BATCH_INTERVAL);
  }

  /**
   * 將緩衝區寫入資料庫
   */
  async flush(): Promise<void> {
    if (this.batchBuffer.length === 0) return;

    const entries = [...this.batchBuffer];
    this.batchBuffer = [];

    try {
      const client = await this.pool.connect();

      try {
        await client.query('BEGIN');

        // 批次插入
        const values: any[] = [];
        const placeholders: string[] = [];

        entries.forEach((entry, idx) => {
          const offset = idx * 20;
          placeholders.push(
            `($${offset + 1}, $${offset + 2}, $${offset + 3}, $${offset + 4}, $${offset + 5}, ` +
            `$${offset + 6}, $${offset + 7}, $${offset + 8}, $${offset + 9}, $${offset + 10}, ` +
            `$${offset + 11}, $${offset + 12}, $${offset + 13}, $${offset + 14}, $${offset + 15}, ` +
            `$${offset + 16}, $${offset + 17}, $${offset + 18}, $${offset + 19}, $${offset + 20})`
          );

          values.push(
            entry.id,
            entry.timestamp,
            entry.actor.userId,
            entry.actor.username,
            entry.actor.email,
            entry.actor.type,
            entry.tenantId,
            entry.action,
            entry.result,
            entry.resource?.type || null,
            entry.resource?.id || null,
            entry.resource?.name || null,
            entry.changes?.before ? JSON.stringify(entry.changes.before) : null,
            entry.changes?.after ? JSON.stringify(entry.changes.after) : null,
            entry.context.requestId,
            entry.context.ipAddress,
            entry.context.userAgent,
            entry.context.location?.country || null,
            entry.context.location?.city || null,
            entry.metadata ? JSON.stringify(entry.metadata) : null
          );
        });

        const query = `
          INSERT INTO audit_logs (
            id, created_at, actor_user_id, actor_username, actor_email, actor_type,
            tenant_id, action, result, resource_type, resource_id, resource_name,
            changes_before, changes_after, request_id, ip_address, user_agent,
            location_country, location_city, metadata
          ) VALUES ${placeholders.join(', ')}
        `;

        await client.query(query, values);
        await client.query('COMMIT');

        console.log(`Flushed ${entries.length} audit log entries`);
      } catch (error) {
        await client.query('ROLLBACK');
        console.error('Failed to flush audit logs:', error);
        // 重新加入緩衝區
        this.batchBuffer.unshift(...entries);
        throw error;
      } finally {
        client.release();
      }
    } catch (error) {
      console.error('Failed to get database connection:', error);
    }
  }

  /**
   * 查詢審計日誌
   */
  async query(params: AuditLogQuery): Promise<{
    logs: AuditLogEntry[];
    total: number;
  }> {
    const conditions: string[] = [];
    const values: any[] = [];
    let paramIndex = 1;

    // 建構 WHERE 條件
    if (params.tenantId) {
      conditions.push(`tenant_id = $${paramIndex++}`);
      values.push(params.tenantId);
    }

    if (params.userId) {
      conditions.push(`actor_user_id = $${paramIndex++}`);
      values.push(params.userId);
    }

    if (params.action) {
      if (Array.isArray(params.action)) {
        conditions.push(`action = ANY($${paramIndex++})`);
        values.push(params.action);
      } else {
        conditions.push(`action = $${paramIndex++}`);
        values.push(params.action);
      }
    }

    if (params.resourceType) {
      conditions.push(`resource_type = $${paramIndex++}`);
      values.push(params.resourceType);
    }

    if (params.resourceId) {
      conditions.push(`resource_id = $${paramIndex++}`);
      values.push(params.resourceId);
    }

    if (params.result) {
      conditions.push(`result = $${paramIndex++}`);
      values.push(params.result);
    }

    if (params.startDate) {
      conditions.push(`created_at >= $${paramIndex++}`);
      values.push(params.startDate);
    }

    if (params.endDate) {
      conditions.push(`created_at <= $${paramIndex++}`);
      values.push(params.endDate);
    }

    if (params.ipAddress) {
      conditions.push(`ip_address = $${paramIndex++}`);
      values.push(params.ipAddress);
    }

    const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

    // 計算總數
    const countQuery = `SELECT COUNT(*) FROM audit_logs ${whereClause}`;
    const countResult = await this.pool.query(countQuery, values);
    const total = parseInt(countResult.rows[0].count, 10);

    // 查詢資料
    const limit = params.limit || 50;
    const offset = params.offset || 0;

    const dataQuery = `
      SELECT * FROM audit_logs
      ${whereClause}
      ORDER BY created_at DESC
      LIMIT $${paramIndex++} OFFSET $${paramIndex}
    `;

    const dataResult = await this.pool.query(dataQuery, [...values, limit, offset]);

    const logs: AuditLogEntry[] = dataResult.rows.map((row) => ({
      id: row.id,
      timestamp: row.created_at,
      actor: {
        userId: row.actor_user_id,
        username: row.actor_username,
        email: row.actor_email,
        type: row.actor_type,
      },
      tenantId: row.tenant_id,
      action: row.action,
      result: row.result,
      resource: row.resource_type
        ? {
            type: row.resource_type,
            id: row.resource_id,
            name: row.resource_name,
          }
        : undefined,
      changes: {
        before: row.changes_before,
        after: row.changes_after,
      },
      context: {
        requestId: row.request_id,
        ipAddress: row.ip_address,
        userAgent: row.user_agent,
        location: {
          country: row.location_country,
          city: row.location_city,
        },
      },
      metadata: row.metadata,
      error: row.error_message
        ? {
            message: row.error_message,
            code: row.error_code,
            stack: row.error_stack,
          }
        : undefined,
    }));

    return { logs, total };
  }

  /**
   * 關閉並清空緩衝區
   */
  async close(): Promise<void> {
    if (this.batchTimer) {
      clearTimeout(this.batchTimer);
      this.batchTimer = null;
    }
    await this.flush();
  }
}

Fastify Audit Plugin

// apps/kyo-otp-service/src/plugins/audit.ts
import { FastifyPluginAsync } from 'fastify';
import fp from 'fastify-plugin';
import { AuditLogger } from '@kyong/kyo-core/audit';
import { AuditAction, AuditResult } from '@kyong/kyo-core/audit/types';

declare module 'fastify' {
  interface FastifyRequest {
    audit: {
      log: (action: AuditAction, details?: any) => Promise<void>;
    };
  }

  interface FastifyInstance {
    auditLogger: AuditLogger;
  }
}

interface AuditPluginOptions {
  auditLogger: AuditLogger;
  excludePaths?: string[]; // 不記錄的路徑
}

const auditPlugin: FastifyPluginAsync<AuditPluginOptions> = async (
  server,
  options
) => {
  const { auditLogger, excludePaths = [] } = options;

  // 註冊 Audit Logger
  server.decorate('auditLogger', auditLogger);

  /**
   * 請求開始時的 Hook
   */
  server.addHook('onRequest', async (request, reply) => {
    // 跳過排除路徑
    if (excludePaths.some((path) => request.url.startsWith(path))) {
      return;
    }

    // 為每個請求附加 audit.log 方法
    request.audit = {
      log: async (action: AuditAction, details?: any) => {
        await auditLogger.log({
          actor: {
            userId: request.user?.userId || 'anonymous',
            username: request.user?.username,
            email: request.user?.email,
            type: request.user ? 'user' : 'system',
          },
          tenantId: request.user?.tenantId || 'system',
          action,
          result: AuditResult.SUCCESS,
          resource: details?.resource,
          changes: details?.changes,
          context: {
            requestId: request.id,
            ipAddress: request.ip,
            userAgent: request.headers['user-agent'] || 'unknown',
          },
          metadata: details?.metadata,
        });
      },
    };
  });

  /**
   * 請求完成時的 Hook
   */
  server.addHook('onResponse', async (request, reply) => {
    // 自動記錄失敗的請求
    if (reply.statusCode >= 400) {
      await auditLogger.log({
        actor: {
          userId: request.user?.userId || 'anonymous',
          username: request.user?.username,
          email: request.user?.email,
          type: request.user ? 'user' : 'system',
        },
        tenantId: request.user?.tenantId || 'system',
        action: `${request.method}.${request.url}` as AuditAction,
        result: AuditResult.FAILURE,
        context: {
          requestId: request.id,
          ipAddress: request.ip,
          userAgent: request.headers['user-agent'] || 'unknown',
        },
        metadata: {
          statusCode: reply.statusCode,
          method: request.method,
          url: request.url,
        },
      });
    }
  });

  /**
   * 錯誤處理 Hook
   */
  server.addHook('onError', async (request, reply, error) => {
    await auditLogger.log({
      actor: {
        userId: request.user?.userId || 'anonymous',
        username: request.user?.username,
        email: request.user?.email,
        type: request.user ? 'user' : 'system',
      },
      tenantId: request.user?.tenantId || 'system',
      action: `${request.method}.${request.url}` as AuditAction,
      result: AuditResult.FAILURE,
      context: {
        requestId: request.id,
        ipAddress: request.ip,
        userAgent: request.headers['user-agent'] || 'unknown',
      },
      error: {
        message: error.message,
        code: error.code,
        stack: error.stack,
      },
      metadata: {
        method: request.method,
        url: request.url,
      },
    });
  });

  /**
   * 應用關閉時清空緩衝區
   */
  server.addHook('onClose', async () => {
    await auditLogger.close();
  });
};

export default fp(auditPlugin, {
  name: 'audit',
});

路由中使用審計日誌

// apps/kyo-otp-service/src/routes/users.ts
import { FastifyPluginAsync } from 'fastify';
import { AuditAction, ResourceType } from '@kyong/kyo-core/audit/types';

const usersRoutes: FastifyPluginAsync = async (server) => {
  /**
   * 建立用戶
   */
  server.post('/users', async (request, reply) => {
    const { email, name, role } = request.body as any;

    // 建立用戶
    const user = await server.db.user.create({
      data: { email, name, role },
    });

    // 記錄審計日誌
    await request.audit.log(AuditAction.USER_CREATED, {
      resource: {
        type: ResourceType.USER,
        id: user.id,
        name: user.email,
      },
      changes: {
        after: {
          email: user.email,
          name: user.name,
          role: user.role,
        },
      },
      metadata: {
        invitedBy: request.user.userId,
      },
    });

    return { user };
  });

  /**
   * 更新用戶
   */
  server.patch('/users/:id', async (request, reply) => {
    const { id } = request.params as any;
    const updates = request.body as any;

    // 取得舊資料
    const oldUser = await server.db.user.findUnique({ where: { id } });

    if (!oldUser) {
      throw server.httpErrors.notFound('User not found');
    }

    // 更新用戶
    const updatedUser = await server.db.user.update({
      where: { id },
      data: updates,
    });

    // 記錄審計日誌
    await request.audit.log(AuditAction.USER_UPDATED, {
      resource: {
        type: ResourceType.USER,
        id: updatedUser.id,
        name: updatedUser.email,
      },
      changes: {
        before: oldUser,
        after: updatedUser,
      },
    });

    return { user: updatedUser };
  });

  /**
   * 刪除用戶
   */
  server.delete('/users/:id', async (request, reply) => {
    const { id } = request.params as any;

    const user = await server.db.user.findUnique({ where: { id } });

    if (!user) {
      throw server.httpErrors.notFound('User not found');
    }

    await server.db.user.delete({ where: { id } });

    // 記錄審計日誌
    await request.audit.log(AuditAction.USER_DELETED, {
      resource: {
        type: ResourceType.USER,
        id: user.id,
        name: user.email,
      },
      changes: {
        before: user,
      },
      metadata: {
        reason: request.body?.reason,
      },
    });

    return { success: true };
  });

  /**
   * 變更用戶角色
   */
  server.post('/users/:id/role', async (request, reply) => {
    const { id } = request.params as any;
    const { role } = request.body as any;

    const oldUser = await server.db.user.findUnique({ where: { id } });

    if (!oldUser) {
      throw server.httpErrors.notFound('User not found');
    }

    const updatedUser = await server.db.user.update({
      where: { id },
      data: { role },
    });

    // 記錄審計日誌 (角色變更是敏感操作)
    await request.audit.log(AuditAction.USER_ROLE_CHANGED, {
      resource: {
        type: ResourceType.USER,
        id: updatedUser.id,
        name: updatedUser.email,
      },
      changes: {
        before: { role: oldUser.role },
        after: { role: updatedUser.role },
      },
      metadata: {
        changedBy: request.user.userId,
        reason: request.body?.reason,
      },
    });

    return { user: updatedUser };
  });
};

export default usersRoutes;

合規報告生成

// packages/kyo-core/src/audit/compliance-report.ts
import { Pool } from 'pg';
import { AuditLogQuery } from './types';

export interface ComplianceReport {
  reportType: 'GDPR' | 'SOC2' | 'HIPAA';
  generatedAt: Date;
  period: {
    start: Date;
    end: Date;
  };
  summary: {
    totalEvents: number;
    successfulEvents: number;
    failedEvents: number;
    uniqueUsers: number;
    uniqueIPs: number;
  };
  details: {
    dataAccess: any[];
    dataModification: any[];
    dataExport: any[];
    datadeletion: any[];
    securityEvents: any[];
    failedAttempts: any[];
  };
}

/**
 * 合規報告生成器
 */
export class ComplianceReportGenerator {
  constructor(private pool: Pool) {}

  /**
   * 生成 GDPR 合規報告
   */
  async generateGDPRReport(
    startDate: Date,
    endDate: Date
  ): Promise<ComplianceReport> {
    const summary = await this.getSummary(startDate, endDate);

    // GDPR 關注的事件
    const dataAccess = await this.getEventsByPattern(
      ['resource.viewed', 'sensitive.data_accessed'],
      startDate,
      endDate
    );

    const dataModification = await this.getEventsByPattern(
      ['resource.updated', 'user.updated'],
      startDate,
      endDate
    );

    const dataExport = await this.getEventsByPattern(
      ['resource.exported', 'sensitive.data_exported'],
      startDate,
      endDate
    );

    const dataDeletion = await this.getEventsByPattern(
      ['resource.deleted', 'user.deleted', 'sensitive.data_deleted'],
      startDate,
      endDate
    );

    return {
      reportType: 'GDPR',
      generatedAt: new Date(),
      period: { start: startDate, end: endDate },
      summary,
      details: {
        dataAccess,
        dataModification,
        dataExport,
        dataDelete: dataDeletion,
        securityEvents: [],
        failedAttempts: [],
      },
    };
  }

  /**
   * 生成 SOC2 合規報告
   */
  async generateSOC2Report(
    startDate: Date,
    endDate: Date
  ): Promise<ComplianceReport> {
    const summary = await this.getSummary(startDate, endDate);

    // SOC2 關注的事件
    const securityEvents = await this.getEventsByPattern(
      [
        'auth.login',
        'auth.logout',
        'auth.login_failed',
        'auth.password_changed',
        'auth.mfa_enabled',
        'auth.mfa_disabled',
      ],
      startDate,
      endDate
    );

    const failedAttempts = await this.getFailedEvents(startDate, endDate);

    const dataAccess = await this.getEventsByPattern(
      ['sensitive.data_accessed', 'sensitive.data_exported'],
      startDate,
      endDate
    );

    return {
      reportType: 'SOC2',
      generatedAt: new Date(),
      period: { start: startDate, end: endDate },
      summary,
      details: {
        dataAccess,
        dataModification: [],
        dataExport: [],
        dataDeletion: [],
        securityEvents,
        failedAttempts,
      },
    };
  }

  /**
   * 取得摘要統計
   */
  private async getSummary(startDate: Date, endDate: Date) {
    const query = `
      SELECT
        COUNT(*) as total_events,
        COUNT(*) FILTER (WHERE result = 'success') as successful_events,
        COUNT(*) FILTER (WHERE result = 'failure') as failed_events,
        COUNT(DISTINCT actor_user_id) as unique_users,
        COUNT(DISTINCT ip_address) as unique_ips
      FROM audit_logs
      WHERE created_at BETWEEN $1 AND $2
    `;

    const result = await this.pool.query(query, [startDate, endDate]);
    const row = result.rows[0];

    return {
      totalEvents: parseInt(row.total_events, 10),
      successfulEvents: parseInt(row.successful_events, 10),
      failedEvents: parseInt(row.failed_events, 10),
      uniqueUsers: parseInt(row.unique_users, 10),
      uniqueIPs: parseInt(row.unique_ips, 10),
    };
  }

  /**
   * 按模式查詢事件
   */
  private async getEventsByPattern(
    actions: string[],
    startDate: Date,
    endDate: Date
  ) {
    const query = `
      SELECT * FROM audit_logs
      WHERE action = ANY($1)
        AND created_at BETWEEN $2 AND $3
      ORDER BY created_at DESC
    `;

    const result = await this.pool.query(query, [actions, startDate, endDate]);
    return result.rows;
  }

  /**
   * 查詢失敗事件
   */
  private async getFailedEvents(startDate: Date, endDate: Date) {
    const query = `
      SELECT * FROM audit_logs
      WHERE result = 'failure'
        AND created_at BETWEEN $1 AND $2
      ORDER BY created_at DESC
    `;

    const result = await this.pool.query(query, [startDate, endDate]);
    return result.rows;
  }
}

今日總結

我們今天完成了 Kyo System 的審計日誌與合規追蹤系統:

核心功能

1. 完整審計架構: 5W1H 原則
2. PostgreSQL 分區: 按月自動分區
3. PII 遮罩: 自動識別並遮罩敏感資料
4. 批次寫入: 效能優化
5. Fastify 整合: 自動記錄請求
6. 查詢 API: 靈活的日誌查詢
7. 合規報告: GDPR、SOC2 自動生成

技術分析

PostgreSQL vs TimescaleDB:

• PostgreSQL: 原生支援、分區表
• TimescaleDB: 時間序列優化、自動壓縮
• 💡 Kyo 選擇 PostgreSQL + 手動分區

批次寫入優化:

• 緩衝區: 100 條或 5 秒
• 批次插入: 減少 DB 往返
• 錯誤重試: 失敗重新加入緩衝
• 💡 平衡即時性與效能

PII 遮罩策略:

• 關鍵字匹配 (password, token...)
• 遞迴處理巢狀物件
• 保留結構,遮罩值
• 💡 合規性與可用性平衡

合規報告:

• GDPR: 資料存取、修改、刪除
• SOC2: 安全事件、失敗嘗試
• HIPAA: 醫療資料存取
• 💡 自動化稽核流程

審計日誌檢查清單

• ✅ 資料模型設計
• ✅ PostgreSQL 表與索引
• ✅ 分區策略
• ✅ Audit Logger Service
• ✅ PII 遮罩
• ✅ 批次寫入
• ✅ Fastify Plugin
• ✅ 路由整合
• ✅ 查詢 API
• ✅ 合規報告生成