Audit Table Functions/Triggers

At ClassPass, depending on the kind of data we're storing it's not uncommon to record changes made to our tables in either an append-only table or an audit log table. Each kind of table has its own pros and cons which I won't do a deep dive on here other than saying at a high level audit tables take up more disk space, and append-only tables may have query performance downsides to consider depending on your implementation. Furthermore, UPDATE or DELETE on audit table source tables can result in a large number of queries being executed against the audit table so keep that in mind when choosing between the two.

The Goal

We've been using an audit log trigger at ClassPass which had some some downsides I wanted to improve. I won't enumerate them here or share the trigger because that's not what you're here for—instead I'll explain what a good audit log trigger function should do:

Is stupid simple to create.
Doesn't need to be updated when adding new columns to the source table.
Complains when you add a new column to the source table and forget to add it to the audit table.

So, without further ado let's get into it.

Creating the Table & Audit Table

Let's imagine we have a table we'd like to create an audit table for:

In order to create the audit table we just make a copy of it and add audit metadata columns. With this method you don't need to manually specify all the columns from the source table be added to the audit table because we're just copying it. You'll notice that the inventory_audit.id represents inventory.id and it has it's own audit_id primary key.

The metadata columns have the following purposes

audit_id - A primary auto-incrementing key for querying the audit table.
_deleted - If this record represents a deletion this column will be true.
_created_on - If this record represents a deletion this column will be true.
idx_inventory_audit_id - When querying the audit table by the source table's id you'll want this index.

The Function

Here is the trigger function itself, it does a few things:

Copies the columns from the source to the audit table 1:1 in the same order. If you just try and do an INSERT INTO inventory_audit SELECT * FROM inventory WHERE id=OLD.id;
Handles INSERT, UPDATE, and DELETE
Sets _deleted to true on DELETE

So, this trigger function is dope because when you add new columns to the source table & audit table the audit trigger will automatically copy the new column's values across. One of the issues with our old trigger function was that you had to re-define it whenever you modified the schema.

Additionally, this function will throw an SQL error if you add a column to the source table and forget to add it to the audit table. Generally this is not good, since you'll be losing data. If you wanted to specifically, intentionally ignore a column on the source table you can exclude it by adding AND column_name NOT IN ('column to ignore') to line 9.

The Trigger

This calls the aforementioned function after insert/update, and before delete. Note: you have to call before delete, call after delete and there will be noting to record.