Postgresql has a feature that allows you to listen to all changes in a table and audit them, this feature is described in the Audit Trigger page.
If you use Flyway to handle your database migrations it can be tedious to update your triggers at every change. Thankfully, flyway has callbacks, with these callbacks you can arbitrarily execute java code in various phases, a particular useful callback is the afterMigrate, this callback is executed after every migration.
To implement this callback, you need to add flyway as a dependency, with maven:
And create a class that implements FlywayCallback, for example, a simple callback is:
Finally, you need to add that callback in your configuration, for example, with maven:
For the real callback, we need to get the tables we want to audit, that can be done with reflexion and get all the @Entity classes, or you can use a query to get all the database tables, for example, this query obtains all tables:
We can remove some tables from this list, for example the public.schema_version and audit.logged_actions tables. The first is used by flyway to manage the versions and the second is used to store the proper audit records.
Finally, we need to add the trigger, since this callback is executed many times, the trigger must be added only if it doesn’t exists already. To achieve this we need to remove the trigger first, this SQL achieves this:
You need to manipulate the result of the tables query to put the info in the trigger query, if you run mvn clean flyway:migrate, you must see:
The table logged_actions looks like:
Note that some columns are removed to fit this page.
You can get the full example in this gist, this gist include the trigger that audit, with the modification recommended in here to save the audit data as JSON, the full callback, and the string manipulation to execute the CREATE TRIGGER sentence.