Automated Audit Trail in Prisma with AsyncLocalStorage

August 26, 2025

14 min read

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:

// 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:

// 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:

// 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:

// 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:

// 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:

// 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:

// 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/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

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

// 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:

// 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:

// 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:

// 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

// 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

// 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:

-- 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:

-- 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:

// 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.