Audit Logging
Hello prisma people. I would like to setup Audit logging on all of the tables in my DB. On the prisma docs/github I see two main ways to achieve this:
1. Using prisma client extensions.
2. Using custom triggers from migrations + client extensions. (~2 years old)
First issue is that I am using in some places the
prisma.$transaction() function, which apparently is not happy when I try to create the extension on the client for it. Then, the second and last issue I am running into is linking the change in the audit table to the user responsible for this change. I see that in the example provided here: https://github.com/prisma/prisma-client-extensions/tree/main/audit-log-context#2-create-audit-log-triggers, that the "current user" being sent to log in the audit table is sent through client extensions and that seems to be one of the ways to achieve this.
Anyways, what is the most up to date and proper way to do this? Furthermore, is there a fix for auditing queries done with the transaction client?
Thank you!3 Replies
You chose to compile with the bot. One moment while I piece together your answer…
Based on the knowledge sources provided, I can offer some insights on audit logging with Prisma, though there are some limitations and challenges to consider.
1. Regarding the use of Prisma Client Extensions for audit logging:
Client extensions can be used for audit logging, as demonstrated in the Prisma Client Extensions example. However, there are known issues with transactions when using client extensions. 2. Transactions and Client Extensions:
There's a reported issue where client extensions that require the use of client-level methods silently ignore transactions. This means that operations performed within a transaction using an extended client might not be rolled back as expected. This is discussed in this GitHub issue. 3. Linking changes to users:
The example you mentioned does use client extensions to pass the current user's context to the audit log. However, this approach may not work reliably within transactions due to the issues mentioned above. 4. Most up-to-date and proper way:
As of now, there isn't a perfect solution that works seamlessly with transactions and user context. Some developers have opted for database-level solutions: - One user mentioned moving audit logging to the database with triggers, stating it caused "Way fewer headaches" GitHub comment. - Another suggestion is to use Change Data Capture (CDC) for a more exhaustive solution, though it comes with infrastructure overhead GitHub comment. 5. Fix for auditing queries done with the transaction client:
There isn't a straightforward fix provided in the knowledge sources. However, one user shared a workaround that involves overriding the
Client extensions can be used for audit logging, as demonstrated in the Prisma Client Extensions example. However, there are known issues with transactions when using client extensions. 2. Transactions and Client Extensions:
There's a reported issue where client extensions that require the use of client-level methods silently ignore transactions. This means that operations performed within a transaction using an extended client might not be rolled back as expected. This is discussed in this GitHub issue. 3. Linking changes to users:
The example you mentioned does use client extensions to pass the current user's context to the audit log. However, this approach may not work reliably within transactions due to the issues mentioned above. 4. Most up-to-date and proper way:
As of now, there isn't a perfect solution that works seamlessly with transactions and user context. Some developers have opted for database-level solutions: - One user mentioned moving audit logging to the database with triggers, stating it caused "Way fewer headaches" GitHub comment. - Another suggestion is to use Change Data Capture (CDC) for a more exhaustive solution, though it comes with infrastructure overhead GitHub comment. 5. Fix for auditing queries done with the transaction client:
There isn't a straightforward fix provided in the knowledge sources. However, one user shared a workaround that involves overriding the
$transaction method [GitHub comment](https://github.com/prisma/prisma/iss
ues/20678). This approach is described as "hacky" and should be used with caution.
In conclusion, while Prisma offers ways to implement audit logging, there are still challenges, especially when it comes to transactions and user context. The most reliable current approach might be to implement audit logging at the database level using triggers or CDC, rather than relying on Prisma Client Extensions. However, this goes against the database-agnostic philosophy of Prisma.
For the most up-to-date information and official recommendations, I suggest checking the latest Prisma documentation or opening a discussion on the Prisma GitHub repository to get insights from the Prisma team and community.
🤔 Alright, thanks for letting us know that there's still some help needed. A team member will reply to you shortly.
In the meantime, please let us know any additional info, questions, or which parts of the answer may need clarification.Hi @Yoni
Anyways, what is the most up to date and proper way to do this?At the moment, the example in the repo you linked is the preferred way to do this at the moment.
Furthermore, is there a fix for auditing queries done with the transaction client?Unfortunately, there is no fix for this yet.
Interesting. would you say that the next best way to do this is to actually write to the audit table ourselves once the transaction is successfully completed? I don't want to mix business logic with the auditing, especially since failing queries to the audit table might cause the actual business logic to fail... Is there a common alternatative solution?
@RaphaelEtim