TemporalTables

Easily recall what your data looked like at any point in the past! TemporalTables sets up and maintains history tables to track all temporal changes to to your data.

Installation

Add this line to your application's Gemfile:

gem 'temporal_tables'

And then execute:

$ bundle

Or install it yourself as:

$ gem install temporal_tables

Usage

Schema

In your rails migration, specify that you want a table to have its history tracked:

create_table :people, temporal: true do |t|
  ...
end

This will create a history table called "people_h" which will maintain a history of all changes to any records in people with the use of triggers on any create/update/delete operation.

Any subsequent schema changes to people will be reflected automatically in people_h.

# Nothing extra required -- people_h will automatically get an "name" column too!
add_column :people, :name, :string

To track the history of a pre-existing table, just call add_temporal_table:

add_temporal_table :people

Querying

For the below queries, we'll assume the following schema:

class Person < ActiveRecord::Base
  belongs_to :coven, optional: true
  has_many :warts

  def to_s
    parts = [name]
    parts << "from #{coven.name}" if coven
    parts.join ' '
  end
end

class Coven < ActiveRecord::Base
  has_many :members, class_name: "Person"

  def to_s
    name
  end
end

class Wart < ActiveRecord::Base
  belongs_to :person

  scope :very_hairy, -> { where(arel_table[:num_hairs].gteq(3)) }

  def to_s
    "wart on #{location} with #{pluralize num_hairs, 'hair'}"
  end
end

You can query the history tables by calling history on the class.

Person         #=> Person(id: :integer, name: :string)
Person.history #=> PersonHistory(history_id: :integer, id: :integer, name: :string, eff_from: :datetime, eff_to: :datetime)

You can easily get a history of all changes to a record.

Person.history.where(id: 1).map { |p| "#{p.eff_from}: #{p.to_s}" }
# => [
#  "1974-01-14: Emily",
#  "2003-11-03: Grunthilda from Delta Gamma Gamma"
# ]

A convenient history method is mixed into ActiveRecord classes. The above could be rewritten as:

grunthilda = Person.find(1)
grunthilda.history.map { |p| "#{p.eff_from}: #{p.to_s}" }

You can query for records as they were at any point in the past by calling at.

Person.history.at(2.years.ago).where(id: 1).first.name #=> "Grunthilda"

# alternatively:
grunthilda = Person.find(1)
grunthilda.history.at(2.years.ago).first.name #=> "Grunthilda"

Associations work too.

grunthilda = Person.history.at(20.years.ago).find_by_name("Grunthilda")
grunthilda.warts.count            #=> 2

grunthilda = Person.history.at(1.year.ago).find_by_name("Grunthilda")
grunthilda.warts.count            #=> 13

grunthilda.warts.first.class.name #=> "WartHistory"

And scopes also!

grunthilda = Person.history.at(1.year.ago).find_by_name("Grunthilda")
grunthilda.warts.count            #=> 13
grunthilda.warts.very_hairy.count #=> 7

Instance methods are inherited.

grunthilda.to_s                   #=> "Grunthilda from Delta Gamma Gamma"
grunthilda.class.name             #=> "PersonHistory"
grunthilda.class.superclass.name  #=> "Person"

Config

You can configure temporal_tables in an initializer.

Create temporal tables for all tables by default (default = false)

TemporalTables.create_by_default = true

Don't create temporal tables for these tables. (default = %w{schema_migrations sessions ar_internal_metadata})

TemporalTables.skip_temporal_table_for :table_one, :table_two

Add an updated_by column to all temporal tables to track who made any changes, which is quite useful for auditing. Defaults to a :string field. The block is called when records are saved to determine the value to place within the updated_by field. updated_by fields are only auto-created if this is configured.

TemporalTables.add_updated_by_field(:integer) { User.current_user&.id }

Development

To add a new version of rails to the specs, create corresponding Gemfiles in the gemfiles/ directory, and add references to them in .travis.yml.

To run specs, run rake matrix:install and rake matrix:spec.

Copyright

See LICENSE for more details.