Система аудита данных: реализация и лучшие практики

CREATE TABLE audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    entity_type VARCHAR(100) NOT NULL,
    entity_id UUID NOT NULL,
    action_type VARCHAR(20) NOT NULL, -- 'CREATE', 'UPDATE', 'DELETE'
    old_data JSONB,
    new_data JSONB,
    user_id UUID,
    user_name VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    ip_address INET,
    session_id VARCHAR(255),
    additional_info JSONB
);

CREATE INDEX idx_audit_logs_entity ON audit_logs(entity_type, entity_id);
CREATE INDEX idx_audit_logs_created_at ON audit_logs(created_at);
CREATE INDEX idx_audit_logs_action_type ON audit_logs(action_type);

model AuditLog {
  id              String   @id @default(cuid())
  entityType      String
  entityId        String
  actionType      AuditActionType
  oldData         Json?
  newData         Json?
  userId          String?
  userName        String?
  createdAt       DateTime @default(now())
  ipAddress       String?
  sessionId       String?
  additionalInfo  Json?

  @@map("audit_logs")
}

enum AuditActionType {
  CREATE
  UPDATE
  DELETE
}

interface AuditableEntity {
  id String @id @default(cuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

// utils/audit.ts
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

export async function createAuditLog(data: {
  entityType: string;
  entityId: string;
  actionType: 'CREATE' | 'UPDATE' | 'DELETE';
  oldData?: any;
  newData?: any;
  userId?: string;
  userName?: string;
  ipAddress?: string;
  sessionId?: string;
  additionalInfo?: any;
}) {
  return await prisma.auditLog.create({
    data: {
      ...data,
      createdAt: new Date(),
    },
  });
}

// middleware/auditMiddleware.ts
export function withAudit(modelName: string) {
  return async (args: any) => {
    const before = args.args.where ? await prisma[modelName].findFirst({
      where: args.args.where,
    }) : null;

    const result = await(args);

    const after = args.args.where ? await prisma[modelName].findFirst({
      where: args.args.where,
    }) : result;

    const actionType = 
      !before ? 'CREATE' :
      !after ? 'DELETE' : 'UPDATE';

    await createAuditLog({
      entityType: modelName,
      entityId: after?.id || before?.id,
      actionType,
      oldData: before,
      newData: after,
    });

    return result;
  };
}

// Enhanced Prisma Client
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

// Override create method
prisma.$use(async (params, next) => {
  if (params.model && ['User', 'Product', 'Order'].includes(params.model)) {
    const before = params.action === 'update' 
      ? await prisma[params.model].findFirst({
          where: params.args.where,
        })
      : null;

    const result = await next(params);

    const after = params.action === 'update'
      ? await prisma[params.model].findFirst({
          where: params.args.where,
        })
      : result;

    const actionType = 
      params.action === 'create' ? 'CREATE' :
      params.action === 'delete' ? 'DELETE' : 'UPDATE';

    await createAuditLog({
      entityType: params.model,
      entityId: result.id,
      actionType,
      oldData: before,
      newData: after,
      userId: 'current-user-id', // Get from context
      userName: 'current-user-name',
    });

    return result;
  }

  return next(params);
});

-- Композитные индексы для частых запросов
CREATE INDEX idx_audit_logs_entity_time ON audit_logs(entity_type, entity_id, created_at DESC);
CREATE INDEX idx_audit_logs_user_time ON audit_logs(user_id, created_at DESC);

-- Частичная индексация для уменьшения размера
CREATE INDEX idx_audit_logs_recent ON audit_logs(created_at DESC) WHERE created_at > NOW() - INTERVAL '30 days';

-- Партиционирование по времени
CREATE TABLE audit_logs (
  -- ... колонки
) PARTITION BY RANGE (created_at);

-- Создание партиций
CREATE TABLE audit_logs_2024 PARTITION OF audit_logs
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

// Использование очереди для асинхронного аудита
const auditQueue = new Queue('audit');

prisma.$use(async (params, next) => {
  // ... логика определения изменений
  
  // Асинхронное добавление в очередь
  auditQueue.add({
    entityType: params.model,
    entityId: result.id,
    actionType,
    oldData: before,
    newData: after,
  });

  return result;
});

// Пример использования брокера сообщений
import { EventEmitter } from 'events';

const auditEmitter = new EventEmitter();

prisma.$use(async (params, next) => {
  // ... логика определения изменений
  
  // Публикация события аудита
  auditEmitter.emit('audit', {
    entityType: params.model,
    entityId: result.id,
    actionType,
    oldData: before,
    newData: after,
    timestamp: new Date(),
  });

  return result;
});

// Регулярная архивация старых данных
async function archiveOldAuditLogs() {
  const oneYearAgo = new Date();
  oneYearAgo.setFullYear(oneYearAgo.getFullYear() - 1);

  const oldLogs = await prisma.auditLog.findMany({
    where: {
      createdAt: {
        lt: oneYearAgo,
      },
    },
  });

  // Перенос в архивную таблицу
  await prisma.auditLogArchive.createMany({
    data: oldLogs,
  });

  // Удаление из основной таблицы
  await prisma.auditLog.deleteMany({
    where: {
      createdAt: {
        lt: oneYearAgo,
      },
    },
  });
}

// Маскирование чувствительных данных
function maskSensitiveData(data: any, schema: any) {
  const masked = { ...data };
  
  Object.keys(schema).forEach(key => {
    if (schema[key].sensitive) {
      masked[key] = '***MASKED***';
    }
  });
  
  return masked;
}

// Хранение хеша данных для проверки целостности
import { createHash } from 'crypto';

function calculateDataHash(data: any): string {
  return createHash('sha256')
    .update(JSON.stringify(data))
    .digest('hex');
}

// Аналитика изменений
async function getEntityChangeStats(entityType: string, period: string) {
  return await prisma.auditLog.groupBy({
    by: ['actionType'],
    where: {
      entityType,
      createdAt: {
        gte: new Date(Date.now() - parsePeriod(period)),
      },
    },
    _count: {
      actionType: true,
    },
  });
}