How You Can Store Your Audit Records In The Second Database Using Rails

Content posted here with the permission of the author Ganesh Sagare, who is currently employed at Josh Software. Original post available here.

In most of the Rails applications, we track the important data for auditing. Most of the time, database table in which these audit records are stored, is under same database of our application.

Keeping this table in same database is helpful until it doesn’t grows tremendously. We use this table most of the time for analysis & sometimes for recovering the data. As this size increases, it will start showing impacts in space consumption, like, increase in database size and backup size and also time taken for database backup.

There are multiple reasons for size of table to increase like

tracking lots of columns from different tables
tracking more actions happening on data

So to optimize our database & backup storage usage and to increase speed in backup process, we just thought what if we store this history/audit records in another database and we found its very easy to do so.

First lets see advantages of this.

Avoid rapidly growing database size.
Reduced database backup size.
Speed up in backup process.
Data isolation.

Now lets see how to store audit records to second database.

1. Update your Gemfile

We used gem audited to keep track of our data. So you can add below entry to your Gemfile.

gem “audited”, “~> 4.7”

2. Create configuration for second database.

We can configure our application to connect to second database using YAML file similar to our database.yml code.

# config/audited.yml

development:
encoding: utf8
adapter: postgresql
database: audit_development
port: 5432

production:
encoding: utf8
adapter: postgresql
database: audit_production
port: 5432

The purpose of this configuration file is to have a nice clean place to store our database connection configuration options.

Note: Assuming database has already been created, and running on default postgres port i.e 5432.

3. Connect to the second database.

Using ActiveRecord::Base.establish_connection method, we can connect to second database. Using our YAML configurations let’s create connection to our second database.

Also let Audited::Audit table (i.e table which stores audit records) to read & write data to second database.

# config/initializers/audited.rb

AUDIT_DB = YAML.load_file(
File.join(Rails.root, "config", "audited.yml")
)[Rails.env.to_s]

# Configure Audited to read/write to second database
Audited::Audit.class_eval do
establish_connection AUDIT_DB
end

4. Create “audits” table in second database

Audited gem uses audits table to store model related changes. You can generate migration for audits table using below command,

rails generate audited:install

For more information refer to gem documentation.

# db/migrate/20180629113852_install_audited.rb

class InstallAudited < ActiveRecord::Migration[5.2]
def self.up
create_table :audits, :force => true do |t|
t.column :auditable_id, :integer
t.column :auditable_type, :string
t.column :associated_id, :integer
t.column :associated_type, :string
t.column :user_id, :integer
t.column :user_type, :string
t.column :username, :string
t.column :action, :string
t.column :audited_changes, :text
t.column :version, :integer, :default => 0
t.column :comment, :string
t.column :remote_address, :string
t.column :request_uuid, :string
t.column :created_at, :datetime
end

add_index :audits, [:auditable_type, :auditable_id], 
:name => 'auditable_index'
add_index :audits, [:associated_type, :associated_id], 
:name => 'associated_index'
add_index :audits, [:user_id, :user_type], :name => 'user_index'
add_index :audits, :request_uuid
add_index :audits, :created_at
end

def self.down
drop_table :audits
end
end

Wait, we don’t simply want to run this migration, because this will update schema in our Rails application, instead we want this migration to be executed on our second database.

Hence we need to update generated migration so it should connect with second database as below. (Note changes highlighted.)

# db/migrate/20180629113852_install_audited.rb

class InstallAudited < ActiveRecord::Migration[5.2]
def self.up
 Audited::Audit.connection.create_table :audits, 
:force => true do |t|
t.column :auditable_id, :integer
t.column :auditable_type, :string
t.column :associated_id, :integer
t.column :associated_type, :string
t.column :user_id, :integer
t.column :user_type, :string
t.column :username, :string
t.column :action, :string
t.column :audited_changes, :text
t.column :version, :integer, :default => 0
t.column :comment, :string
t.column :remote_address, :string
t.column :request_uuid, :string
t.column :created_at, :datetime
end

 Audited::Audit.connection.add_index :audits,
[:auditable_type, :auditable_id], :name => 'auditable_index'
 Audited::Audit.connection.add_index :audits,
[:associated_type, :associated_id],:name => 'associated_index'
 Audited::Audit.connection.add_index :audits,
[:user_id, :user_type], :name => 'user_index'
 Audited::Audit.connection.add_index :audits, :request_uuid
 Audited::Audit.connection.add_index :audits, :created_at
end

def self.down
 Audited::Audit.connection.drop_table :audits
end
end

And then execute migration in order to create our table in second database.