home

Preserving Model History with ActiveRecord and PostgreSQL

written by Ivo van Hurne on · Comments

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 come in.

Using rules we can tell PostgreSQL to execute an alternative statement when we INSERT, UPDATE 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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE RULE insert_contacts AS
    ON INSERT TO contacts DO INSTEAD
      INSERT INTO contacts_versions (
        firstname, lastname, created_at, updated_at, contact_id)
      VALUES (new.firstname, new.lastname,
              new.created_at, new.updated_at,
              nextval('contacts_id_seq'::regclass))
      RETURNING contacts_versions.contact_id AS id,
        contacts_versions.id AS contact_version_id,
        contacts_versions.firstname,
        contacts_versions.lastname,
        contacts_versions.created_at,
        contacts_versions.updated_at,
        contacts_versions.deleted;

Inserting a record

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

1
2
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:

id firstname lastname contact_version_id
1 Jane Doe 2
2 John Doe 3

 

The contacts_versions table now looks like this:

id contact_id firstname lastname deleted
1 1 Jessica Doe false
2 1 Jane Doe false
3 2 John Doe false

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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?
            self.send("#{name}_without_versioning")
          else
            "#{self.class.reflect_on_association(name).klass}Version".constantize.find(self["#{name}_version_id"])
          end
        end

        alias_method_chain(name, :versioning)
      end
    end
  end
end

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.

1
2
3
4
5
6
require 'activerecord-be_readonly'

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

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.

comments powered by Disqus