Preserving Model History with ActiveRecord and PostgreSQL

· 16-06-2014

When you create an invoice in Moneybird, you select one of your contacts as the recipient. While the invoice is still a draft, you expect any changes to the contact’s address information to be reflected on the invoice as well. However, this shouldn’t happen for invoices that have been sent already, as that would damage the integrity of your administration. Additionally, we want to be able to revert a contact to an earlier state or restore a contact that was deleted by accident.

Versions with a view #

We could solve this using any of the myriad of versioning solutions available for Ruby on Rails. Instead, we chose a much simpler solution using PostgreSQL.

In this solution our contacts table is not a normal table any more, it’s a view on the contacts_versions table. All versions of a contact are stored as rows in this table and the view always shows the most recent version.

To create or update a contact, we now have to add a new row to the contacts_versions table containing the updated contact. This would be a bit annoying, not to mention incompatible with ActiveRecord. That’s where PostgreSQL rules (opent in nieuw tabblad) come in.

Using rules we can tell PostgreSQL to execute an alternative statement when we INSERTUPDATE or DELETE a row in a view. This allows us to use the view in ActiveRecord just like we would use a normal table. For example, for inserting new contacts in the contacts view we define the following rule:

CREATE RULE insert_contacts AS
INSERT INTO contacts_versions (
firstname, lastname, created_at, updated_at, contact_id)
VALUES (new.firstname, new.lastname,
new.created_at, new.updated_at,
RETURNING contacts_versions.contact_id AS id, AS contact_version_id,

Inserting a record #

Now we can insert a contact the usual way using ActiveRecord:

irb(main):001:0> Contact.create(firstname: 'John', lastname: 'Doe')
=> INSERT INTO contacts (firstname, lastname) VALUES ('John', 'Doe')

ActiveRecord will execute an INSERT statement on the contacts view. PostgreSQL knows how to handle this, because of the rule we defined. Afterwards, the contacts view contains the new contact:

The contacts_versions table now looks like this:

Pinning the version #

Once a particular invoice is sent, we save the current version ID of its contact as contact_version_id in the invoices table. We wrote a simple Concern for ActiveRecord that overrides the invoice’s association to a contact when the contact_version_id field is set.

module Concerns
module VersionedRelationConcern
extend ActiveSupport::Concern

module ClassMethods
def versioned_relation(name)
define_method "#{name}_with_versioning" do
if self["#{name}_version_id"].blank?

alias_method_chain(name, :versioning)

The final part of the system is the ContactVersion class. It’s basically the same as a normal Contact class with the table name changed to contacts_versions. Additionally, we mark it read-only to prevent accidental changes to old versions.

require 'activerecord-be_readonly'

class ContactVersion < Contact
self.table_name += '_versions'

Conclusions #

Preserving the history of your ActiveRecord models doesn’t have to be complicated. Use PostgreSQL to keep track of the different versions and access them using a simple override in ActiveRecord.