Implement an Audit Trail on changes

[yuval-hazaz]

Hi
Is there an event or any other method to register for all changes committed to the DB in order to implement a generic audit trail on the DB?

I want to have an Audit Trail table in the DB to track all changes made on certain table. Something like:
TableName
RecordId
UserName
Data
ChangeType (Update/ Insert/ Delete)
OldValues (JSON)
NewValues (JSON)

I want to be able to register to an event just after (or before ) changes are committed to the DB in order to prepare the data based on the changes that were done and save it to the Audit Trail table.

To better understand my question, please see this example on how it can be done with Entity Framework
https://stackoverflow.com/questions/20961489/how-to-create-an-audit-trail-with-entity-framework-5-and-mvc-4

Thank you very much for your great work.

[nikolasburk]

Hey @yuval-hazaz 👋 unfortunately Prisma currently doesn't support this particular use case! However, we definitely want to enable this in the future.

For now I recommend that you open a feature request with a concrete description of your use case and ideally your "dream API" for what this feature could look like in Prisma 🙂

[yuval-hazaz]

Thank you very much. I will open a feature request with all the details.

[filipesperandio]

Coming here 2 years later: any updates in this front?

[ludralph]

Hi there,

To keep our discussions organized and focused on the most relevant topics, we’re reviewing and tidying up our backlog. As part of this process, we’re closing discussions that have already been marked as answered but remain open.

If this discussion still requires further input or clarification, feel free to reopen it or start a new one with updated details. Your contributions are invaluable to the community, and we’re here to help!

For more details about our priorities and vision for the future of Prisma ORM, check out our latest blog post: https://www.prisma.io/blog/prisma-orm-manifesto.

Thank you for your understanding and ongoing support of the Prisma community!

[kinthaiofficial]

Audit trails in Prisma are achievable with middleware. Here's a production-ready pattern:

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

// Extend Prisma with audit middleware
function withAuditTrail(prisma: PrismaClient) {
  prisma.$use(async (params: Prisma.MiddlewareParams, next) => {
    const AUDITED_OPERATIONS = ['create', 'update', 'delete', 'upsert'];
    const AUDITED_MODELS = ['User', 'Order', 'Payment'];  // opt-in per model
    
    if (!AUDITED_OPERATIONS.includes(params.action) || 
        !AUDITED_MODELS.includes(params.model ?? '')) {
      return next(params);
    }
    
    // For updates/deletes, capture before state
    let before: Record<string, unknown> | null = null;
    if (params.action === 'update' || params.action === 'delete') {
      before = await (prisma as any)[params.model!.toLowerCase()].findUnique({
        where: params.args.where,
      });
    }
    
    const result = await next(params);
    
    // Write audit record
    await prisma.auditLog.create({
      data: {
        model: params.model!,
        operation: params.action,
        recordId: result?.id ?? params.args.where?.id,
        before: before as Prisma.JsonValue,
        after: result as Prisma.JsonValue,
        userId: AsyncLocalStorage.getStore()?.userId,  // From request context
        timestamp: new Date(),
        ipAddress: AsyncLocalStorage.getStore()?.ip,
      },
    });
    
    return result;
  });
  
  return prisma;
}

// Audit log schema
// model AuditLog {
//   id        String   @id @default(cuid())
//   model     String
//   operation String
//   recordId  String?
//   before    Json?
//   after     Json?
//   userId    String?
//   ipAddress String?
//   timestamp DateTime @default(now())
//   @@index([model, recordId])
//   @@index([userId])
// }

The AsyncLocalStorage context for userId is key — you need to thread the current user through the request context without passing it through every function call. Next.js 13+ headers() or a middleware-set context works for this.

For immutability, consider write-only access for the audit table: separate Prisma client with restricted permissions that can only create on AuditLog.