Querying PaperTrail Object Changes in JSON

PaperTrail may be a fit for your rails app if you've ever wanted help answering the question, "what series of events conspired to put my database in this state?" It acts as a time capsule, storing each change made to instances of your models that you have PaperTrail turned on for.

Let's create a user. Unfortunately, we can't make up our mind about which email to use, so we change that a few times.

> user = User.create(email: "[e-mail]", first_name: "Kevin", last_name: "Murphy")
> user.update(email: "[e-mail]")
> user.update(email: "[e-mail]")

We have PaperTrail set to track changes made to a user's email. Later on, we may need to do some archaeology to figure out why this user's email address isn't [e-mail]. Let's investigate how PaperTrail can help with that.

Using PaperTrail's API

PaperTrail provides a few options to navigate changes made over the history of our object. We're going to use where_object_changes to figure out what happened with [e-mail]. where_object_changes will find any time the provided attributes changed to or from the values provided.

Let's first find out if this user's email ever was [e-mail].

> user.versions.where_object_changes(email: "[e-mail]").count
=> 2

Now we know that at some point in time, this user's email was stored as [e-mail]. We can hone in on the changes, and specifically only look at the email address changes.

> user.versions
    .where_object_changes(email: "[e-mail]")
    .pluck(:object_changes)
    .map { |c| c.slice("email") }
=> [{"email"=>["[e-mail]", "[e-mail]"]},
   {"email"=>["[e-mail]", "[e-mail]"]}]

Any PaperTrail Version has an object_changes attribute. The value of that attribute is a hash. The keys of that hash are the attributes that changed, and the value is a tuple (as an array) that shows first the value that attribute changed from, and the value it changed to.

Here we're finding all versions that have to do with the email address [e-mail] and only showing the changes to the email address.

This first version shows that the email address changed from [e-mail] to [e-mail]. Later on, the second version shows the email address changing from [e-mail] to [e-mail].

Enhanced JSON Querying

If PaperTrail versions are stored as JSON or JSONB in Postgres, we have some more detailed queries we can write by digging into Postgres' JSON functions.

Changed

Sometimes we may not have an attribute value that we want to search for. We may need to first know when an attribute changed to anything at all. Let's look for any time that this user's email attribute changed.

> user.versions.where("object_changes -> 'email' IS NOT NULL").pluck(:object_changes).map { |c| c.slice("email") }
=> [{"email"=>["", "[e-mail]"]},
 {"email"=>["[e-mail]", "[e-mail]"]},
 {"email"=>["[e-mail]", "[e-mail]"]}]

The first version is logged when you initially create the row in your database, which is why it changes from the empty string to [e-mail]. From there, we see the additional updates to email, as all versions have changed the
email address.

Changing From

While the prior query gives us less specificity than PaperTrail provides with where_object_changes, we may also benefit from more specificity. For example, we may want to know when the user's email changed from [e-mail], and not care about when it changed to [e-mail].

We can use where_object_changes, but to find out which changes are only changing the value from [e-mail], we'll need to scan the results ourselves or in memory, rather than relying on the database.

However, we can construct a query ourselves to tell us only when an attribute changed from a value.

> user.versions.where("object_changes ->>'email' ILIKE '[\"[e-mail]\",%'").pluck(:object_changes).map { |c| c.slice("email") }
=> [{"email"=>["[e-mail]", "[e-mail]"]}]

Remember that the object changes of an attribute are stored as a tuple in an array. The first value in the array is the value the attribute changed from, and the second value is the value the attribute changed to.

This query looks within the object_changes attribute, and checks to see if the value of the email field within that attribute has the email address we're looking for as the first element in the tuple. The opening bracket before the email address in escaped quotes is the start of the array/tuple.

Changing To

We can also construct a query to look for versions on the other side of the equation. We can find all instances where the attribute changed to a particular value.

> user.versions.where("object_changes ->>'email' ILIKE '[%,\"[e-mail]\"]'").pluck(:object_changes).map { |c| c.slice("email") }
=> [{"email"=>["[e-mail]", "[e-mail]"]}]

When looking for what the value changed to, we want to look in our tuple to find the email address in question as the last element, which will be preceded by a comma and have an ending bracket after it.

Trailing Off

Thanks to storing our PaperTrail versions in a JSON format, we were able to sneak in an applied lesson on Postgres' powerful JSON functions and operators while also reviewing the structure of data that's used by PaperTrail to track any versions of our database records.

This post originally published on The Gnar Company blog.

Learn more about how The Gnar builds Ruby on Rails applications.