Rails transaction start hook

Question

I want move my company's database from app-level auditing to database-level auditing. I will be using the 91audit trigger detailed here: https://github.com/2ndQuadrant/audit-trigger/blob/master/audit.sql

The problem with DB level auditing is that it doesn't actually record the current user, it records the postgresql user.

The downside of App level auditing is that I HAVE to run every transaction through the web app in order for a change to be audited (does not work well if I have multiple apps on the same database)

I found a really interesting solution here: http://schinckel.net/2014/06/01/in-database-audit-trail/

The solution relies on updating/inserting a row on a temporary table at the start of every transaction.

NOW

In order for this to work for me, I want to add a hook to rails to update this temp table on any save, update, destroy, or delete call.

My question is:

Does a top-level transaction-start hook exist for Rails 5.2+; preferably something i can set inside and initializer or in ApplicationRecord?

My first thought would be to add an initializer, but I have no idea what I'd hook into

My backup plan, is to override ApplicationRecord.transaction

class ApplicationRecord < ActiveRecord::Base
  def self.transaction(*args)
    super(*args) do
      if Current.user
        ActiveRecord::Base.connection.execute(%Q(
          CREATE TEMP TABLE IF NOT EXISTS
          "_app_user" (user_id integer, ip_address inet);

          UPDATE
            _app_user
          SET
            user_id=#{Current.user.id},
            ip_address='#{Current.ip_address}';

          INSERT INTO
            _app_user (user_id, ip_address)
          SELECT
            #{Current.user.id}, '#{Current.ip_address}'
          WHERE NOT EXISTS (SELECT * FROM _app_user);
        ))
      end

      yield
    end
  end
end

Answer 1

Overriding ActiveRecord.transaction seems to work just fine

class ApplicationRecord < ActiveRecord::Base
  def self.transaction(*args)
    super(*args) do
      if Current.user
        ip = Current.ip_address ? "'#{Current.ip_address}'" : 'NULL'
        ActiveRecord::Base.connection.execute <<-SQL
          CREATE TEMP TABLE IF NOT EXISTS
            "_app_user" (user_id integer, user_type text, ip_address inet);

          UPDATE
            _app_user
          SET
            user_id=#{Current.user.id},
            user_type='#{Current.user.class.to_s}',
            ip_address=#{ip};

          INSERT INTO
            _app_user (user_id, user_type, ip_address)
          SELECT
            #{Current.user.id},
            '#{Current.user.class.to_s}',
            #{ip}
          WHERE NOT EXISTS (SELECT * FROM _app_user);
        SQL
      end

      yield
    end
  end
end