Audit Trail in PostgreSQL using Prisma

Introduction

Safeguarding the integrity and security of databases is a paramount concern for organizations across various sectors. Central to this mission is the meticulous monitoring of alterations made within databases, a task streamlined through establishing an audit trail.

This comprehensive manual will explore the intricacies of integrating an audit trail into PostgreSQL using the versatile Prisma ORM. Renowned for its intuitive nature and robust functionalities, Prisma ORM is an optimal solution for database management, including audit trail implementation.

In software development, meticulous oversight of alterations within code repositories, project configurations, and deployment pipelines is a cornerstone for effective collaboration and quality assurance. An audit trail is vital in this regard, meticulously logging modifications to codebases, merge requests, and deployment activities. Such comprehensive documentation aids in pinpointing the origins of errors, monitoring project progression, and ensuring adherence to coding standards.

Understanding the Importance of an Audit Trail

An audit trail, a log or history table, is a system for documenting changes executed within a database. It records crucial details, including the user initiating the modification, the change's precise timestamp, and the alteration's specific nature. By establishing an audit trail, enterprises can elevate transparency levels, streamline adherence to regulatory standards, streamline debugging processes, and fortify security protocols. Additionally, an audit trail is invaluable for forensic examination, empowering organizations to trace the evolution of data modifications and flag potential irregularities or unauthorized actions.

Step-by-Step Implementation with Prisma ORM

Prisma ORM simplifies database interactions and empowers developers to integrate advanced features like an audit trail seamlessly. Let's walk through the step-by-step process of setting up an audit trail using Prisma ORM in conjunction with PostgreSQL.

Step 1: Setting Up Prisma ORM

To start, verify that Prisma is installed within your project directory. If it's not already installed, you can effortlessly do so using npm or yarn, following the example provided below:

npm install @prisma/cli @prisma/client

or

yarn add @prisma/cli @prisma/client

Next, initialize Prisma within your project by executing the following command:

npx prisma init

This command initializes Prisma and generates configuration files within a directory named. prisma.

Step 2: Defining Audit Trail Tables

To implement an audit trail, we'll create dedicated tables to store audit trail information for each table requiring tracking. Suppose we have a table named users that necessitates audit trail functionality. In that case, we'll create a corresponding audit_users table to capture relevant changes.

CREATE TABLE audit_users (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  action VARCHAR(10) NOT NULL,
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  old_data JSONB,
  new_data JSONB
);

The audit_users table incorporates fields to store the user_id of the modified record, the action performed (e.g., insert, update, delete), the timestamp of the action, and the old and new data in JSONB format.

Step 3: Implementing Auditing Logic

Subsequently, we'll define a function responsible for logging changes made to the database. This function will be invoked whenever a modification occurs.

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

async function auditChanges(userId, action, oldData, newData) {
  await prisma.audit_users.create({
    data: {
      user_id: userId,
      action: action,
      old_data: oldData,
      new_data: newData
    }
  });
}

module.exports = { auditChanges };

The auditChanges function accepts parameters such as the user_id of the modified record, the action performed, and the old and new data. It utilizes Prisma's create method to insert a new entry into the audit_users table.

Step 4: Triggering Audit Trail on Database Operations

We'll set up triggers on the tables requiring auditing to automate the logging process. In this case, let's create triggers for the users table.

CREATE OR REPLACE FUNCTION audit_users()
RETURNS TRIGGER AS $$
BEGIN
  IF (TG_OP = 'DELETE') THEN
    PERFORM auditChanges(old.user_id, 'delete', row_to_json(OLD), null);
    RETURN OLD;
  ELSIF (TG_OP = 'UPDATE') THEN
    PERFORM auditChanges(old.user_id, 'update', row_to_json(OLD), row_to_json(NEW));
    RETURN NEW;
  ELSIF (TG_OP = 'INSERT') THEN
    PERFORM auditChanges(new.user_id, 'insert', null, row_to_json(NEW));
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_users_trigger
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_users();

The audit_users function serves as a trigger, executing after each insert, update, or delete operation on the users table. It determines the type of operation (insert, update, delete) and invokes the auditChanges function accordingly, passing the requisite parameters.

Conclusion

In this comprehensive guide, we've outlined tmplementing an audit trail in PostgreSQL using the Prisma ORM. By establishing dedicated audit trail tables, defining auditing logic, and configuring triggers on database operations, organizations can effectively track and monitor changes made to their databases.

Leveraging the capabilities of Prisma ORM streamlines the implementation process, enabling developers to enhance data integrity and security seamlessly. With an audit trail, organizations can bolster compliance efforts, fortify security measures, and foster trust among stakeholders, thus safeguarding their valuable data assets. Implementing an audit trail is a regulatory requirement and a proactive measure to uphold data integrity and protect against potential threats or breaches.

Thank you for reading this article. Catch you in the next one.