# Introduction

Audit trails are crucial for maintaining data integrity, compliance, and debugging in production applications. However, manually adding audit logging to every database operation can be tedious and error-prone. What if we could automate this process using Prisma's middleware system and Node.js AsyncLocalStorage?

In this article, we'll build an automated audit trail system that:
- Automatically logs all Create, Update, and Delete (CUD) operations
- Identifies the user performing each operation using AsyncLocalStorage
- Filters out unwanted models from audit logging
- Provides a clean, type-safe interface for audit data
- Handles system operations when no user context is available

## What is AsyncLocalStorage?

AsyncLocalStorage is a Node.js API that allows you to store data that is accessible throughout the entire lifecycle of an async operation. Think of it as a "request-scoped container" that follows your request through all async operations, making it perfect for storing user context that Prisma middleware can access.

## Understanding the Problem

Traditional audit trail implementations often require:

- Manual logging in every service function
- Passing user context through multiple function calls
- Risk of forgetting to log certain operations
- Inconsistent audit data structure
- Difficulty in filtering what should be audited

Our solution eliminates these issues by centralizing the audit logic in Prisma middleware.

# Implementation

## 1. Setting up the Audit Trail Types

First, let's define our audit trail structure:

```ts title="types/audit.ts"
export interface AuditTrailEntry {
  id: string;
  tableName: string;
  recordId: string;
  operation: 'CREATE' | 'UPDATE' | 'DELETE';
  oldValues?: Record<string, any>;
  newValues?: Record<string, any>;
  userId?: string;
  userEmail?: string;
  timestamp: Date;
}

export interface UserContext {
  id: string;
  email: string;
}

export interface AuditConfig {
  // Models to exclude from audit trail
  excludedModels: string[];
  // Fields to exclude from audit trail (sensitive data)
  excludedFields: Record<string, string[]>;
  // Whether to log the entire record or just changed fields
  logFullRecord: boolean;
}
```

## 2. Creating the AsyncLocalStorage Context

Next, we'll create a context manager for storing user information:

```ts title="lib/audit-context.ts"
import { AsyncLocalStorage } from 'node:async_hooks';

interface AuditContext {
  user?: UserContext;
  requestId: string;
  timestamp: Date;
}

const auditContextStorage = new AsyncLocalStorage<AuditContext>();

export class AuditContextManager {
  static runWithContext<T>(
    context: Partial<AuditContext>,
    fn: () => T | Promise<T>
  ): Promise<T> {
    const fullContext: AuditContext = {
      requestId: context.requestId || crypto.randomUUID(),
      timestamp: context.timestamp || new Date(),
      user: context.user,
    };

    return auditContextStorage.run(fullContext, fn);
  }

  static getCurrentContext(): AuditContext | undefined {
    return auditContextStorage.getStore();
  }

  static getCurrentUser(): UserContext | undefined {
    return auditContextStorage.getStore()?.user;
  }

  static setUser(user: UserContext): void {
    const context = auditContextStorage.getStore();
    if (context) {
      context.user = user;
    }
  }

  static clearUser(): void {
    const context = auditContextStorage.getStore();
    if (context) {
      context.user = undefined;
    }
  }
}
```

## 3. Creating the Audit Trail Service

Now let's build the service that handles audit trail creation:

```ts title="lib/audit-service.ts"
import { PrismaClient } from '@prisma/client';
import { AuditTrailEntry, AuditConfig, UserContext } from '../types/audit';

export class AuditService {
  constructor(
    private prisma: PrismaClient,
    private config: AuditConfig
  ) {}

  async logOperation(params: {
    tableName: string;
    recordId: string;
    operation: 'CREATE' | 'UPDATE' | 'DELETE';
    oldValues?: Record<string, any>;
    newValues?: Record<string, any>;
  }): Promise<void> {
    // Skip if model is excluded
    if (this.config.excludedModels.includes(params.tableName)) {
      return;
    }

    const context = AuditContextManager.getCurrentContext();
    const user = context?.user;

    // Filter out sensitive fields
    const filteredOldValues = this.filterSensitiveFields(
      params.tableName,
      params.oldValues
    );
    const filteredNewValues = this.filterSensitiveFields(
      params.tableName,
      params.newValues
    );

    const auditEntry: Omit<AuditTrailEntry, 'id' | 'timestamp'> = {
      tableName: params.tableName,
      recordId: params.recordId,
      operation: params.operation,
      oldValues: this.config.logFullRecord ? filteredOldValues : undefined,
      newValues: this.config.logFullRecord ? filteredNewValues : undefined,
      userId: user?.id,
      userEmail: user?.email,
    };

    try {
      await this.prisma.auditTrail.create({
        data: {
          ...auditEntry,
          timestamp: new Date(),
        },
      });
    } catch (error) {
      // Log error but don't fail the main operation
      console.error('Failed to create audit trail entry:', error);
    }
  }

  private filterSensitiveFields(
    tableName: string,
    values?: Record<string, any>
  ): Record<string, any> | undefined {
    if (!values) return values;

    const excludedFields = this.config.excludedFields[tableName] || [];
    const filtered = { ...values };

    excludedFields.forEach(field => {
      if (field in filtered) {
        filtered[field] = '[REDACTED]';
      }
    });

    return filtered;
  }

  async getAuditTrail(params: {
    tableName?: string;
    recordId?: string;
    userId?: string;
    operation?: 'CREATE' | 'UPDATE' | 'DELETE';
    startDate?: Date;
    endDate?: Date;
    limit?: number;
    offset?: number;
  }): Promise<AuditTrailEntry[]> {
    const where: any = {};

    if (params.tableName) where.tableName = params.tableName;
    if (params.recordId) where.recordId = params.recordId;
    if (params.userId) where.userId = params.userId;
    if (params.operation) where.operation = params.operation;
    if (params.startDate || params.endDate) {
      where.timestamp = {};
      if (params.startDate) where.timestamp.gte = params.startDate;
      if (params.endDate) where.timestamp.lte = params.endDate;
    }

    return this.prisma.auditTrail.findMany({
      where,
      orderBy: { timestamp: 'desc' },
      take: params.limit || 100,
      skip: params.offset || 0,
    });
  }
}
```

## 4. Implementing Prisma Middleware

The core of our solution is Prisma middleware that automatically intercepts all operations:

```ts title="lib/prisma-audit-middleware.ts"
import { PrismaClient } from '@prisma/client';
import { AuditService } from './audit-service';
import { AuditContextManager } from './audit-context';

export function createAuditMiddleware(auditService: AuditService) {
  return async (params: any, next: any) => {
    // Store original parameters for audit logging
    const originalParams = { ...params };
    
    // Execute the original operation
    const result = await next(params);
    
    // Get current user context
    const user = AuditContextManager.getCurrentUser();
    
    try {
      // Handle different operation types
      if (params.action === 'create') {
        await auditService.logOperation({
          tableName: params.model,
          recordId: result.id || result.id,
          operation: 'CREATE',
          newValues: params.data,
        });
      } else if (params.action === 'update') {
        await auditService.logOperation({
          tableName: params.model,
          recordId: params.args.where.id || params.args.where.id,
          operation: 'UPDATE',
          oldValues: params.args.data,
          newValues: params.args.data,
        });
      } else if (params.action === 'delete') {
        await auditService.logOperation({
          tableName: params.model,
          recordId: params.args.where.id || params.args.where.id,
          operation: 'DELETE',
          oldValues: result,
        });
      } else if (params.action === 'deleteMany') {
        // Handle bulk deletions
        const records = await params.model.findMany({
          where: params.args.where,
        });
        
        for (const record of records) {
          await auditService.logOperation({
            tableName: params.model,
            recordId: record.id,
            operation: 'DELETE',
            oldValues: record,
          });
        }
      } else if (params.action === 'updateMany') {
        // Handle bulk updates
        const records = await params.model.findMany({
          where: params.args.where,
        });
        
        for (const record of records) {
          await auditService.logOperation({
            tableName: params.model,
            recordId: record.id,
            operation: 'UPDATE',
            oldValues: record,
            newValues: params.args.data,
          });
        }
      }
    } catch (error) {
      // Log error but don't fail the main operation
      console.error('Audit middleware error:', error);
    }
    
    return result;
  };
}
```

## 5. Setting up the Prisma Client

Now let's configure our Prisma client with the audit middleware:

```ts title="lib/prisma.ts"
import { PrismaClient } from '@prisma/client';
import { AuditService } from './audit-service';
import { createAuditMiddleware } from './prisma-audit-middleware';

const auditConfig: AuditConfig = {
  excludedModels: ['AuditTrail', 'Sessions', 'TempFiles'],
  excludedFields: {
    Users: ['password', 'resetToken'],
    Profiles: ['ssn', 'creditCard'],
    Logs: ['sensitiveData'],
  },
  logFullRecord: false, // Only log changed fields for updates
};

const auditService = new AuditService(prisma, auditConfig);

export const prisma = new PrismaClient().$extends({
  query: {
    $allModels: {
      async $allOperations({ model, operation, args, query }) {
        // Apply audit middleware to all operations
        return createAuditMiddleware(auditService)({ 
          action: operation, 
          model, 
          args 
        }, query);
      },
    },
  },
});

export { auditService };
```

## 6. Middleware for User Context

To capture user context in your Express.js or Next.js application:

```ts title="middleware/auth-context.ts"
import { Request, Response, NextFunction } from 'express';
import { AuditContextManager } from '../lib/audit-context';

export function authContextMiddleware(req: Request, res: Response, next: NextFunction) {
  const user = req.user; // Assuming you have user info from auth middleware
  
  AuditContextManager.runWithContext(
    {
      user: user ? {
        id: user.id,
        email: user.email,
      } : undefined,
    },
    () => {
      next();
    }
  );
}
```

## 7. Usage Examples

Here's how to use the audit trail system:

```ts
// Example: User registration
app.post('/register', authContextMiddleware, async (req, res) => {
  try {
    const user = await prisma.user.create({
      data: {
        email: req.body.email,
        name: req.body.name,
        password: hashedPassword,
      },
    });
    
    // Audit trail is automatically created by middleware
    res.status(201).json({ message: 'User created successfully', userId: user.id });
  } catch (error) {
    res.status(400).json({ error: 'Registration failed' });
  }
});

// Example: Updating user profile
app.put('/profile/:id', authContextMiddleware, async (req, res) => {
  try {
    const user = await prisma.user.update({
      where: { id: req.params.id },
      data: {
        name: req.body.name,
        bio: req.body.bio,
      },
    });
    
    // Audit trail automatically logs the update
    res.json({ message: 'Profile updated successfully', user });
  } catch (error) {
    res.status(400).json({ error: 'Update failed' });
  }
});

// Example: Querying audit trail
app.get('/audit/:tableName/:recordId', async (req, res) => {
  try {
    const auditEntries = await auditService.getAuditTrail({
      tableName: req.params.tableName,
      recordId: req.params.recordId,
      limit: 50,
    });
    
    res.json(auditEntries);
  } catch (error) {
    res.status(500).json({ error: 'Failed to fetch audit trail' });
  }
});
```

## 8. Database Schema

You'll need to add an audit trail table to your Prisma schema:

```prisma title="prisma/schema.prisma"
model AuditTrail {
  id          String   @id @default(cuid())
  tableName   String
  recordId    String
  operation   String   // CREATE, UPDATE, DELETE
  oldValues   Json?    // Previous values (for updates/deletes)
  newValues   Json?    // New values (for creates/updates)
  userId      String?  // ID of user who performed the operation
  userEmail   String?  // Email of user for easier querying
  timestamp   DateTime @default(now())

  @@index([tableName, recordId])
  @@index([userId])
  @@index([timestamp])
  @@index([operation])
}
```

# Configuration Options

## Audit Configuration

```ts
const auditConfig: AuditConfig = {
  // Exclude system tables and temporary data
  excludedModels: [
    'AuditTrail',
    'Sessions', 
    'TempFiles',
    'Cache',
    'Logs'
  ],
  
  // Exclude sensitive fields from audit trail
  excludedFields: {
    Users: ['password', 'resetToken', 'apiKey'],
    Profiles: ['ssn', 'creditCard', 'passportNumber'],
    Orders: ['paymentToken', 'cvv'],
    Logs: ['sensitiveData', 'stackTrace']
  },
  
  // Only log changed fields for updates (more efficient)
  logFullRecord: false,
  
  // Additional options you could add:
  // maxAuditEntries: 10000, // Limit audit trail size
  // retentionDays: 365,     // Auto-delete old entries
  // batchSize: 100,         // Batch audit operations
};
```

## Environment-based Configuration

```ts title="config/audit.ts"
export const getAuditConfig = (): AuditConfig => {
  const isProduction = process.env.NODE_ENV === 'production';
  
  return {
    excludedModels: [
      'AuditTrail',
      'Sessions',
      ...(isProduction ? ['DebugLogs', 'TestData'] : [])
    ],
    excludedFields: {
      Users: ['password', 'resetToken'],
      ...(isProduction ? {
        Profiles: ['ssn', 'creditCard'],
        Orders: ['paymentToken']
      } : {})
    },
    logFullRecord: !isProduction, // Log full records in development
  };
};
```

# Advanced Features

## 1. Batch Operations Support

For bulk operations, you might want to batch audit entries:

```ts title="lib/batch-audit-service.ts"
export class BatchAuditService extends AuditService {
  private batchQueue: Array<() => Promise<void>> = [];
  private batchTimeout: NodeJS.Timeout | null = null;
  private readonly batchDelay = 100; // ms

  async logOperation(params: any): Promise<void> {
    return new Promise((resolve) => {
      this.batchQueue.push(async () => {
        await super.logOperation(params);
        resolve();
      });

      this.scheduleBatch();
    });
  }

  private scheduleBatch(): void {
    if (this.batchTimeout) return;

    this.batchTimeout = setTimeout(async () => {
      const operations = [...this.batchQueue];
      this.batchQueue = [];
      this.batchTimeout = null;

      // Execute all operations in parallel
      await Promise.all(operations.map(op => op()));
    }, this.batchDelay);
  }
}
```

## 2. Audit Trail Cleanup

Implement automatic cleanup of old audit entries:

```ts title="lib/audit-cleanup.ts"
export class AuditCleanupService {
  constructor(private prisma: PrismaClient) {}

  async cleanupOldEntries(retentionDays: number = 365): Promise<number> {
    const cutoffDate = new Date();
    cutoffDate.setDate(cutoffDate.getDate() - retentionDays);

    const result = await this.prisma.auditTrail.deleteMany({
      where: {
        timestamp: {
          lt: cutoffDate,
        },
      },
    });

    return result.count;
  }

  async cleanupByTable(tableName: string, retentionDays: number): Promise<number> {
    const cutoffDate = new Date();
    cutoffDate.setDate(cutoffDate.getDate() - retentionDays);

    const result = await this.prisma.auditTrail.deleteMany({
      where: {
        tableName,
        timestamp: {
          lt: cutoffDate,
        },
      },
    });

    return result.count;
  }
}
```

## 3. Audit Trail Analytics

Add analytics capabilities to your audit trail:

```ts title="lib/audit-analytics.ts"
export class AuditAnalyticsService {
  constructor(private prisma: PrismaClient) {}

  async getOperationStats(startDate: Date, endDate: Date) {
    const stats = await this.prisma.auditTrail.groupBy({
      by: ['operation', 'tableName'],
      where: {
        timestamp: {
          gte: startDate,
          lte: endDate,
        },
      },
      _count: {
        id: true,
      },
    });

    return stats;
  }

  async getTopUsers(startDate: Date, endDate: Date, limit: number = 10) {
    const users = await this.prisma.auditTrail.groupBy({
      by: ['userId', 'userEmail'],
      where: {
        timestamp: {
          gte: startDate,
          lte: endDate,
        },
        userId: { not: null },
      },
      _count: {
        id: true,
      },
      orderBy: {
        _count: {
          id: 'desc',
        },
      },
      take: limit,
    });

    return users;
  }

  async getTableActivity(startDate: Date, endDate: Date) {
    const activity = await this.prisma.auditTrail.groupBy({
      by: ['tableName', 'operation'],
      where: {
        timestamp: {
          gte: startDate,
          lte: endDate,
        },
      },
      _count: {
        id: true,
      },
      orderBy: {
        _count: {
          id: 'desc',
        },
      },
    });

    return activity;
  }
}
```

# Testing Your Audit Trail

## Unit Tests

```ts title="tests/audit-service.test.ts"
import { AuditService } from '../lib/audit-service';
import { AuditContextManager } from '../lib/audit-context';

describe('AuditService', () => {
  let auditService: AuditService;
  let mockPrisma: any;

  beforeEach(() => {
    mockPrisma = {
      auditTrail: {
        create: jest.fn(),
      },
    };
    
    auditService = new AuditService(mockPrisma, {
      excludedModels: ['TestModel'],
      excludedFields: {},
      logFullRecord: true,
    });
  });

  it('should log CREATE operation', async () => {
    const user = { id: 'user1', email: 'test@example.com' };
    
    await AuditContextManager.runWithContext({ user }, async () => {
      await auditService.logOperation({
        tableName: 'Users',
        recordId: 'user1',
        operation: 'CREATE',
        newValues: { email: 'test@example.com' },
      });
    });

    expect(mockPrisma.auditTrail.create).toHaveBeenCalledWith({
      data: expect.objectContaining({
        tableName: 'Users',
        operation: 'CREATE',
        userId: 'user1',
        userEmail: 'test@example.com',
      }),
    });
  });

  it('should exclude sensitive fields', async () => {
    const auditService = new AuditService(mockPrisma, {
      excludedModels: [],
      excludedFields: {
        Users: ['password'],
      },
      logFullRecord: true,
    });

    await auditService.logOperation({
      tableName: 'Users',
      recordId: 'user1',
      operation: 'UPDATE',
      newValues: { email: 'test@example.com', password: 'secret' },
    });

    expect(mockPrisma.auditTrail.create).toHaveBeenCalledWith({
      data: expect.objectContaining({
        newValues: { email: 'test@example.com', password: '[REDACTED]' },
      }),
    });
  });
});
```

## Integration Tests

```ts title="tests/audit-integration.test.ts"
describe('Audit Trail Integration', () => {
  it('should automatically log user creation', async () => {
    const user = await prisma.user.create({
      data: {
        email: 'test@example.com',
        name: 'Test User',
      },
    });

    const auditEntry = await prisma.auditTrail.findFirst({
      where: {
        tableName: 'User',
        recordId: user.id,
        operation: 'CREATE',
      },
    });

    expect(auditEntry).toBeTruthy();
    expect(auditEntry?.newValues).toMatchObject({
      email: 'test@example.com',
      name: 'Test User',
    });
  });

  it('should handle system operations without user context', async () => {
    // No user context set
    const user = await prisma.user.create({
      data: {
        email: 'system@example.com',
        name: 'System User',
      },
    });

    const auditEntry = await prisma.auditTrail.findFirst({
      where: {
        tableName: 'User',
        recordId: user.id,
        operation: 'CREATE',
      },
    });

    expect(auditEntry).toBeTruthy();
    expect(auditEntry?.userId).toBeNull();
    expect(auditEntry?.userEmail).toBeNull();
  });
});
```

# Performance Considerations

## 1. Database Indexing

Ensure your audit trail table is properly indexed:

```sql
-- Add these indexes to your audit trail table
CREATE INDEX idx_audit_trail_table_record ON audit_trail(table_name, record_id);
CREATE INDEX idx_audit_trail_user ON audit_trail(user_id);
CREATE INDEX idx_audit_trail_timestamp ON audit_trail(timestamp);
CREATE INDEX idx_audit_trail_operation ON audit_trail(operation);
CREATE INDEX idx_audit_trail_user_timestamp ON audit_trail(user_id, timestamp);
```

## 2. Partitioning for Large Tables

For high-traffic applications, consider partitioning your audit trail table:

```sql
-- Partition by month for better performance
CREATE TABLE audit_trail_2024_01 PARTITION OF audit_trail
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE audit_trail_2024_02 PARTITION OF audit_trail
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
```

## 3. Async Processing

For better performance, consider processing audit entries asynchronously:

```ts title="lib/async-audit-service.ts"
export class AsyncAuditService extends AuditService {
  async logOperation(params: any): Promise<void> {
    // Don't await - fire and forget
    setImmediate(async () => {
      try {
        await super.logOperation(params);
      } catch (error) {
        console.error('Async audit logging failed:', error);
      }
    });
  }
}
```

# Best Practices

## 1. Security Considerations

- **Never log sensitive data**: Always filter out passwords, tokens, and PII
- **Encrypt audit logs**: Consider encrypting sensitive audit data at rest
- **Access control**: Limit who can view audit trails
- **Data retention**: Implement proper data retention policies

## 2. Performance Best Practices

- **Index your audit table**: Ensure fast queries on common fields
- **Batch operations**: Use batch processing for high-volume operations
- **Async logging**: Don't block main operations for audit logging
- **Regular cleanup**: Implement automated cleanup of old audit entries

## 3. Monitoring and Alerting

- **Monitor audit log size**: Set up alerts for rapidly growing audit tables
- **Track failed audits**: Monitor and alert on audit logging failures
- **Performance metrics**: Track audit logging performance impact

# Conclusion

Implementing an automated audit trail using Prisma middleware and AsyncLocalStorage provides a robust, maintainable solution for tracking data changes in your application. The key benefits of this approach are:

- **Automatic logging**: No need to manually add audit calls throughout your codebase
- **User context**: Automatic user identification without passing context through function calls
- **Performance**: Minimal impact on your main application operations
- **Flexibility**: Easy to configure what gets audited and what doesn't
- **Maintainability**: Centralized audit logic that's easy to modify and extend

This system scales well from small applications to enterprise-level systems and provides the foundation for compliance, debugging, and business intelligence needs. By leveraging Prisma's middleware system and Node.js AsyncLocalStorage, you get the best of both worlds: powerful database operations and comprehensive audit trails without the maintenance overhead.

Remember to:
- Start with a simple configuration and expand as needed
- Monitor performance impact and adjust accordingly
- Implement proper data retention and cleanup policies
- Test thoroughly, especially edge cases like system operations
- Consider your compliance requirements when designing the audit schema

With this implementation, you'll have a production-ready audit trail system that automatically tracks all your important data changes while maintaining excellent application performance.