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.

Configuration

To implement this callback, you need to add flyway as a dependency, with maven:

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
    <version>3.2.1</version>
</dependency>

And create a class that implements FlywayCallback, for example, a simple callback is:

package test;

import org.flywaydb.core.api.callback.FlywayCallback;

public class AuditCallback implements FlywayCallback {

    // Other methods

    @Override
    public void afterMigrate(Connection connection) {

        System.out.println("afterMigrate");
    }

}

Finally, you need to add that callback in your configuration, for example, with maven:

<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>3.2.1</version>
    <configuration>
        <url>jdbc:postgresql://localhost:5432/database</url>
        <!-- Place your callbacks here, in the prefered execution order -->
        <callbacks>
            <callback>test.AuditCallback</callback>
        </callbacks>
    </configuration>
    <dependencies>
        <!-- This is necessary to create the connection-->
        <dependency>
            <groupId>postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>9.1-901-1.jdbc4</version>
        </dependency>
    </dependencies>
</plugin>

Actual callback

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:

SELECT table_schema || '.' || table_name AS table
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

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:

DROP TRIGGER IF EXISTS table_audit ON schema.table;
CREATE TRIGGER table_audit
AFTER INSERT OR UPDATE OR DELETE ON schema.table
FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func();

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:

[INFO] Scanning for projects...
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] Building project 1.0-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] --- maven-resources-plugin:2.6:resources (default-resources) @ project ---
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] Copying 9 resources
[INFO]
[INFO] --- maven-compiler-plugin:3.1:compile (default-compile) @ project ---
[INFO] Nothing to compile - all classes are up to date
[INFO]
[INFO] --- flyway-maven-plugin:3.2.1:migrate (default-cli) @ project ---
[INFO] Flyway 3.2.1 by Boxfuse
[INFO] Database: jdbc:postgresql://localhost:5432/newproject (PostgreSQL 9.3)
[INFO] Validated 4 migrations (execution time 00:00.014s)
[INFO] Current version of schema "public": 1.3
[INFO] Schema "public" is up to date. No migration necessary.

Updating audit info

[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 2.776 s
[INFO] Finished at: 2015-10-19T11:22:42-03:00
[INFO] Final Memory: 35M/382M
[INFO] ------------------------------------------------------------------------

The table logged_actions looks like:

| schema_name   | table_name    | user_name   | action_tstamp                   | action   | original_data   | new_data                                                                                                     |
| ------------- | ------------- | ----------- | ------------------------------- | -------- | --------------- | ------------------------------------------------------------------------------------------------------------ |
| public        | rol           | postgres    | 2015-10-19 17:37:21.859658-03   | I        |                 | {"id":1,"nombre":"rol 1","descripcion":"el esl rol numero 1"}                                                |
| public        | usuario       | postgres    | 2015-10-19 17:42:49.63219-03    | I        |                 | {"id":1,"nombre":"avolpe"}                                                                                   |
| public        | usuario_rol   | postgres    | 2015-10-19 17:42:49.63219-03    | I        |                 | {"id_usuario":1,"id_rol":1}                                                                                  |

Note that some columns are removed to fit this page.

Final code

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.