In any application, tracking changes in the database is often crucial for accountability and transparency. Whether it's for debugging, security, or compliance, knowing what changes were made, by whom, and when can be essential. In this blog, we will explore how to create an Audit Log in PostgreSQL that tracks every INSERT
, UPDATE
, and DELETE
operation on tables using triggers and a dedicated audit log table.
We'll cover:
Designing the Audit Table
Creating the Audit Function
Implementing Triggers for Auditing
Demonstration with Sample Tables
Conclusion
1. Designing the Audit Table
The first step in building an audit log is to design the table where we'll store all the change data. Here's how our audit table looks:
CREATE TABLE audit_logs (
id BIGSERIAL PRIMARY KEY, -- Auto-incrementing ID for the audit log entry
entity_name VARCHAR(50) NOT NULL, -- Name of the table being audited
entity_id BIGINT NOT NULL, -- ID of the entity being modified
version INT NOT NULL, -- Version of the data (to track changes over time)
old_data JSONB, -- Snapshot of the old data (before update or delete)
new_data JSONB, -- Snapshot of the new data (after insert or update)
action VARCHAR(20) NOT NULL, -- Type of action (INSERT, UPDATE, DELETE)
updated_on BIGINT NOT NULL -- Timestamp of when the action took place
);
Here, we store critical information for auditing:
entity_name
: The name of the table on which the action was performed.version
: Helps keep track of data versions after multiple updates.old_data
&new_data
: JSONB fields for storing the old and new records.action
: Specifies if it was anINSERT
,UPDATE
, orDELETE
operation.updated_on
: An epoch timestamp to record when the change occurred.
2. Creating the Audit Function
Next, we need a function that writes to this audit_logs
table whenever a change occurs in one of our target tables. This function, written in PL/pgSQL, dynamically handles all three types of operations: INSERT
, UPDATE
, and DELETE
.
CREATE OR REPLACE FUNCTION audit_log_fxn()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'DELETE' THEN
INSERT INTO audit_logs(entity_name, entity_id, old_data, new_data, action, updated_on, version)
VALUES (TG_TABLE_NAME, OLD.id, row_to_json(OLD), NULL, 'DELETE', EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), OLD.version);
RETURN OLD;
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO audit_logs(entity_name, entity_id, old_data, new_data, action, updated_on, version)
VALUES (TG_TABLE_NAME, OLD.id, row_to_json(OLD), row_to_json(NEW), 'UPDATE', EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), NEW.version);
RETURN NEW;
ELSIF TG_OP = 'INSERT' THEN
INSERT INTO audit_logs(entity_name, entity_id, old_data, new_data, action, updated_on, version)
VALUES (TG_TABLE_NAME, NEW.id, NULL, row_to_json(NEW), 'INSERT', EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), COALESCE(NEW.version, 1));
RETURN NEW;
END IF;
END;
$$ LANGUAGE plpgsql;
In this function:
For
INSERT
, it records only thenew_data
.For
UPDATE
, bothold_data
andnew_data
are logged.For
DELETE
, onlyold_data
is logged.
The trigger automatically populates the entity_name
with the name of the table using TG_TABLE_NAME
, and we use EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)
to store the timestamp in UNIX format.
3. Implementing Triggers for Auditing
We now create triggers that will invoke the audit_log_fxn
function whenever an INSERT
, UPDATE
, or DELETE
happens on specific tables. Let's assume we have three tables: student
, faculty
, and courses
.
Create Tables
CREATE TABLE student (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(100),
age INT,
version INT DEFAULT 1
);
CREATE TABLE faculty (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50),
version INT DEFAULT 1
);
CREATE TABLE courses (
id BIGSERIAL PRIMARY KEY,
course_name VARCHAR(100),
credits INT,
version INT DEFAULT 1
);
CREATE OR REPLACE TRIGGER student_audit_trigger
AFTER UPDATE OR DELETE OR INSERT ON student
FOR EACH ROW
EXECUTE FUNCTION audit_log_fxn();
Trigger for the faculty
Table
CREATE OR REPLACE TRIGGER faculty_audit_trigger
AFTER UPDATE OR DELETE OR INSERT ON faculty
FOR EACH ROW
EXECUTE FUNCTION audit_log_fxn();
Trigger for the courses
Table
CREATE OR REPLACE TRIGGER courses_audit_trigger
AFTER UPDATE OR DELETE OR INSERT ON courses
FOR EACH ROW
EXECUTE FUNCTION audit_log_fxn();
These triggers ensure that whenever a row in the student
, faculty
, or courses
table is modified, the audit function is called, and the changes are logged in the audit_logs
table.
4. Demonstration with Sample Tables
Let's create the student
, faculty
, and courses
tables, perform some operations, and see how the audit log captures them.
Insert Operation
-- Insert into student table
INSERT INTO student (name, age) VALUES ('Alice', 21);
-- Update record in student table
UPDATE student SET age = 22 WHERE name = 'Alice';
-- Delete record from student table
DELETE FROM student where name = 'Alice';
-- Insert into faculty table
INSERT INTO faculty (name, department) VALUES ('Dr. Smith', 'Mathematics');
-- Update record in faculty table
UPDATE faculty SET department = 'Physics' WHERE name = 'Dr. Smith';
-- Delete record from faculty table
DELETE FROM faculty WHERE name = 'Dr. Smith';
-- Insert into courses table
INSERT INTO courses (course_name, credits) VALUES ('Algebra', 4);
-- Update record in courses table
UPDATE courses SET credits = 5 WHERE course_name = 'Algebra';
-- Delete record from courses table
DELETE FROM courses WHERE course_name = 'Algebra';
Audit log will capture this as:
id | entity_name | entity_id | old_data | new_data | action | updated_on |
47 | student | 7 | {"id": 7, "age": 21, "name": "Alice", "version": 1} | INSERT | 1728047533 | |
48 | student | 7 | {"id": 7, "age": 21, "name": "Alice", "version": 1} | {"id": 7, "age": 22, "name": "Alice", "version": 1} | UPDATE | 1728047533 |
49 | student | 7 | {"id": 7, "age": 22, "name": "Alice", "version": 1} | DELETE | 1728047533 | |
50 | faculty | 5 | {"id": 5, "name": "Dr. Smith", "version": 1, "department": "Mathematics"} | INSERT | 1728047533 | |
51 | faculty | 5 | {"id": 5, "name": "Dr. Smith", "version": 1, "department": "Mathematics"} | {"id": 5, "name": "Dr. Smith", "version": 1, "department": "Physics"} | UPDATE | 1728047533 |
52 | faculty | 5 | {"id": 5, "name": "Dr. Smith", "version": 1, "department": "Physics"} | DELETE | 1728047533 | |
53 | courses | 5 | {"id": 5, "credits": 4, "version": 1, "course_name": "Algebra"} | INSERT | 1728047533 | |
54 | courses | 5 | {"id": 5, "credits": 4, "version": 1, "course_name": "Algebra"} | {"id": 5, "credits": 5, "version": 1, "course_name": "Algebra"} | UPDATE | 1728047533 |
55 | courses | 5 | {"id": 5, "credits": 5, "version": 1, "course_name": "Algebra"} | DELETE | 1728047533 |
5. Conclusion
By setting up an audit log with PostgreSQL triggers, we can ensure that every data modification—whether it's an INSERT
, UPDATE
, or DELETE
—is recorded systematically. This not only provides transparency but also allows for efficient debugging and tracking of application behaviour. The use of JSONB
fields for old_data
and new_data
gives us flexibility to capture all the changes in a structured format.
With this approach, you now have a robust auditing mechanism for your PostgreSQL database, easily adaptable to any use case where data integrity and traceability are essential.