Audit Trails

What is an Audit Trail?

An Audit Trail (also called Audit Log) helps companies or organizations to keep a detailed, chronological record of their transactions, user activity, or other financial data for tracking purposes.

Maintaining a comprehensive and complete audit trail is crucial for businesses as a proof of compliance with industry standards, such as financial regulations, data protection laws, and other regulatory requirements, and to minimize the risk of penalties for non-compliance. They also help in identifying the source of problems or errors within a system through the review of system logs, including detecting any unauthorized access mishap to the system. The usability of the audit rule is so profound that the Ministry of Corporate Affairs (MCA) released a notice (Companies (Accounts) Amendment Rules, 2021) mandating that companies using accounting software must choose platforms equipped with a feature recording an audit trail for every transaction [note that it is not a mandatory requirement for all businesses].

The Design

By looking at the nature of usages audit rule has, we can clearly underline what all details an audit table should have, as listed below:

• a unique identifier for each entry into the table.

• a reference (foreign key relation) to the table on which audit is being made.

• a field to reflect the choice of change (insertion/updation/deletion) carried out on any entry of the referenced table.

• a json of the old details before any change was made on any data entry,

• a json of the new details post the changes to the data entry.

• a version field that keeps incrementing on each change carried on the records of the referenced table.

• timestamp of the change made.

• a field to track which user has made the changes to the data entry. Could be an optional field.

PLSQL Procedure: Now, we observe the PSQL procedure to make an entry into such an audit table created corresponding to a Student table-

CREATE OR REPLACE PROCEDURE insert_student_audit_entry (
    p_student_id INT,
    p_old_data JSONB,
    p_new_data JSONB,
    p_action VARCHAR,
    p_version INT,
    p_updated_on BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Insert a new entry into the student_audit table
    INSERT INTO student_audit (
        student_id, old_data, new_data, action, version, updated_on
    ) VALUES (
        p_student_id, p_old_data, p_new_data, p_action, p_version, p_updated_on
    );
END;
$$;

Parameters:

• p_student_id (INT): The ID of the student being audited (foreign key).

• p_old_data (JSONB): The old JSON data before the change.

• p_new_data (JSONB): The new JSON data after the change.

• p_action (VARCHAR): The action being performed (e.g., 'INSERT', 'UPDATE', 'DELETE').

• p_version (INT): The version number of the record being audited.

• p_updated_on (BIGINT): A timestamp representing when the update occurred.

Trigger Function: Then, a trigger function is needed to ensure an entry is made to the audit table whenever an insert, update, or delete is performed on the Student table.

DROP TRIGGER IF EXISTS student_audit_trigger ON student;

            CREATE OR REPLACE FUNCTION audit_student()
            RETURNS TRIGGER AS $$
            BEGIN
                IF TG_OP = 'DELETE' THEN
                    INSERT INTO student_audit(student_id, old_data, new_data, action, updated_on, version)
                    VALUES (NULL, row_to_json(OLD), NULL, 'DELETE', EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), OLD.version);
                    RETURN OLD;
                ELSIF TG_OP = 'UPDATE' THEN
                    INSERT INTO student_audit(student_id, old_data, new_data, action, updated_on, version)
                    VALUES (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 student_audit(student_id, old_data, new_data, action, updated_on, version)
                    VALUES (NEW.id, NULL, row_to_json(NEW), 'INSERT', EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), COALESCE(NEW.version, 1));
                    RETURN NEW;
                END IF;
            END;
            $$ LANGUAGE plpgsql;

            CREATE OR REPLACE TRIGGER student_audit_trigger
            AFTER UPDATE OR DELETE OR INSERT ON student
            FOR EACH ROW
            EXECUTE FUNCTION audit_student();

Here, the trigger function (audit_student) checks the type of operation (TG_OP), which could be INSERT, UPDATE, or DELETE. Depending on the operation, it logs the appropriate data:

• INSERT: Logs the new data only.

• UPDATE: Logs both the old and new data.

• DELETE: Logs the old data only.

For example, my Django project has Student and StudentAudit models defined as below-

class Student(models.Model):
    id = models.BigAutoField(primary_key=True)
    name = models.CharField(max_length=255, null=True, blank=True)
    registration = models.BigIntegerField(default=0)
    version = models.IntegerField(default=1)

    class Meta:
        db_table = 'student'

class StudentAudit(models.Model):
    id = models.BigAutoField(primary_key=True)
    student = models.ForeignKey(
        'Student',
        on_delete=models.SET_NULL,
        null=True,
        blank=True
    )
    version = models.IntegerField(default=1)
    old_data = models.JSONField(blank=True, null=True)
    new_data = models.JSONField(blank=True, null=True)
    action = models.CharField(max_length=20, blank=True, null=True)
    updated_on = models.BigIntegerField(default=0)

    class Meta:
        db_table = 'student_audit'

On creating a new entry into the Student table, the database will show the entry like this:

While the StudentAudit table in the database will also contain the log of this INSERT activity on Student table like below:

When this particular data entry is modified, the two tables- Student and StudentAudit will have entries changed/added like below:

Now, we move towards the DELETE operation. It would be interesting to observe that the student_id field in the StudentAudit table will get changed to NULL as the corresponding entry from Student table has been removed.

Here, the StudentAudit table is keeping a record of all the INSERT, UPDATE, DELETE operations performed on the Student table entries alongwith the timestamp of such operation. This could serve as a useful record to track in future all the changes done to the Student table.

Takeaway

In conclusion, setting up an audit trail using triggers in PostgreSQL offers a robust method to keep a comprehensive record of changes in your database. By tracking actions like INSERT, UPDATE, and DELETE, one can keep data safe and gain useful insights into how the organization's data changes over time. Implementing this rule not only strengthens security but also supports debugging and compliance efforts. If you have any query or suggestion or feedback about the topic, please feel free to reach me out on my linkedln account.