Support mixin for models subclassed from ActiveRecord::Base providing as-per-API-standard dating support with services needing to know that dating is enabled and cooperate with this mixin's API, rather than working automatically via database triggers as per Hoodoo::ActiveRecord::Dated. The latter is close to transparent for ActiveRecord-based code, but it involves very complex database queries that can have high cost and is tied into PostgreSQL.

Depends upon and auto-includes Hoodoo::ActiveRecord::Finder.


This mixin lets you record and retrieve the historical state of any given ActiveRecord model. This is achieved by adding two date/time columns to the model and using these to track the start (inclusive) and end (exclusive and always set to precisely DATE_MAXIMUM for “this is the 'contemporary' record) date/times for which a particular row is valid.

The majority of the functionality is implemented within class methods defined in module Hoodoo::ActiveRecord::ManuallyDated::ClassMethods.


A table in the database needs to have various changes and additions to support manual dating. For these to be possible:

  • Your database table may not already have columns called uuid, effective_start or effective_end. If it does, you'll need to first migrate this to change the names and update any references in code.

  • Your database table must have a column called created_at with the creation timestamp of a record which will become the time from which it is “visible” in historically-dated read queries. There can be no NULL values in this column.

  • Your database table must have a column called updated_at with a non NULL value. If this isn't already present, migrate your data to add it, setting the initial value to the same as created_at.

For data safety it is very strongly recommended that you add in database level non-null constraints on created_at and updated_at if you don't have them already. The ActiveRecord change_column_null method can be used in migrations to do this in a database-engine-neutral fashion.

Vital caveats

Since both the 'contemporary' and historic states of the model are all recorded in one table, anyone using this mechanism must ensure that (unless they specifically want to run a query across all of the representations) the mixin's scoping methods are always used to target either current, or historic, or specifically-dated rows only.

With this mechanism in place, the id attribute of the model is still a unique primary key AND THIS IS NO LONGER THE RESOURCE UUID. The UUID moves to a non-unique uuid column. When rendering resources, YOU MUST USE THE uuid COLUMN for the resource ID. This is a potentially serious gotcha and strong test coverage is advised! If you send back the wrong field value, it'll look like a reasonable UUID but will not match any records at all through API-based interfaces, assuming Hoodoo::ActiveRecord::Finder is in use for read-based queries. The UUID will appear to refer to a non-existant resource.

  • The id column becomes a unique database primary key and of little to no interest whatsoever to a service or API callers.

  • The uuid column becomes the non-unique resource UUID which is of great interest to a service and API callers.

  • The uuid column is also the target for foreign keys with relationships between records, NOT id. The relationships can only be used when scoped by date.


Time accuracy is intentionally limited, to aid database indices and help avoid clock accuracy differences across operating systems or datbase engines. Hoodoo::ActiveRecord::ManuallyDated::SECONDS_DECIMAL_PLACES describes the accuracy applicable.

If a record is, say, both created and then deleted within the accuracy window, then a dated query attempting to read the resource state from that (within-accuracy) identical time will return an undefined result. It might find the resource before it were deleted, or might not find the resource because it considers it to be no longer current. Of course, any dated query from outside the accuracy window will work as you would expect; only rapid changes in state within the accuracy window result in ambiguity.

Typical workflow

Having included the mixin, run any required migrations (see below) and declared manual dating as active inside your ActiveRecord::Base subclass by calling Hoodoo::ActiveRecord::ManuallyDated::ClassMethods#manual_dating_enabled, you MUST include the ActiveRecord::Relation instances (scopes) inside any query chain used to read or write data.

Show and List

You might use Hoodoo::ActiveRecord::Finder::ClassMethods#list_in or Hoodoo::ActiveRecord::Finder::ClassMethods#acquire_in for list or show actions; such code changes from e.g.:

SomeModel.list_in( context )


SomeModel.manually_dated( context ).list_in( context )


As with automatic dating - see Hoodoo::ActiveRecord::Dated - you should use method Hoodoo::ActiveRecord::Creator::ClassMethods#new_in to create new resource instances, to help ensure correct initial date setup and to help isolate your code from future functionality extensions/changes. An ActiveRecord before_create filter deals with some of the “behind the scenes” maintenance but the initial acquisition of dating information from the prevailing request context only happens for you if you use Hoodoo::ActiveRecord::Creator::ClassMethods::new_in.

Update and Delete

You MUST NOT update or delete records using conventional ActiveRecord methods if you want to use manual dating to record state changes. Instead, use Hoodoo::ActiveRecord::ManuallyDated::ClassMethods#manually_dated_update_in or Hoodoo::ActiveRecord::ManuallyDated::ClassMethods#manually_dated_destruction_in. For example to update a model based on the context.request.body data without changes to the item in context.request.ident, handling “not found” or valiation error cases with the assumption that the Hoodoo::ActiveRecord::ErrorMapping mixin is in use, do this:

result = SomeModel.manually_dated_destruction_in( context )

if result.nil?
  context.response.not_found( context.request.ident )
elsif result.adds_errors_to?( context.response.errors ) == false
  rendered_data = render_model( result )
  context.response.set_data( rendered_data )

See the documentation for the update/destroy methods mentioned above for information on overriding the identifier used to find the target record and the attribute data used for updates.


When rendering, you MUST remember to set the resource's id field from the model's uuid field:

    :uuid         => model.uuid, # <-- ".uuid" - IMPORTANT!
    :created_at   => model.created_at


Generally, use of ActiveRecord associations is minimal in most services because there is an implied database-level coupling of resources and a temptation to use cross-table ActiveRecord mechanisms for things like relational UUID integrity checks, rather than inter-resource calls. Doing so couples resources together at the database rather than keeping them isolated purely by API, which is often a really bad idea. It is, however, sometimes necessary for best possible performance, or sometimes one complex resource may be represented by several models with relationships between them.

In such cases, remember to set foreign keys for relational declarations to a manually dated table via the uuid column - e.g. go from this:

member.account_id =

…to this:

member.account_id = account.uuid

…with the relational declarations in Member changing from:

belongs_to :account


belongs_to :account, :primary_key => :uuid

Required migrations

You must write an ActiveRecord migration for any table that wishes to use manual dating. The template below can handle multiple tables in one pass and can be rolled back safely IF no historic records have been added. Rollback becomes impossible once historic entries appear.

require 'hoodoo/active'

class ConvertToManualDating < ActiveRecord::Migration

  # This example migration can handle multiple tables at once - e.g. pass an
  # array of ":accounts, :members" if you were adding manual dating support to
  # tables supporting an Account and Member ActiveRecord model.
  TABLES_TO_CONVERT = [ :table_name, :another_table_name, ... ]

  # This will come in handy later.
  SQL_DATE_MAXIMUM = ActiveRecord::Base.connection.quoted_date( Hoodoo::ActiveRecord::ManuallyDated::DATE_MAXIMUM )

  def up

    # If you have any uniqueness constraints on this table, you'll need to
    # remove them and re-add them with date-based scope. The main table will
    # contain duplicated entries once historical versions of a row appear.
    #   remove_index :table_name, <index fields(s) or name: 'index name'>
    # For example, suppose you had declared this index somewhere:
    #   add_index :accounts, :account_number, :unique => true
    # Remove it with:
    #   remove_index :accounts, :account_number

    TABLES_TO_CONVERT.each do | table |

      add_column table, :effective_start, :datetime, :null  => true # (initially, but see below)
      add_column table, :effective_end,   :datetime, :null  => true # (initially, but see below)
      add_column table, :uuid,            :string,   :limit => 32

      add_index table, [        :effective_start, :effective_end ], :name => "index_#{ table }_start_end"
      add_index table, [ :uuid, :effective_start, :effective_end ], :name => "index_#{ table }_uuid_start_end"

      # We can't allow duplicate UUIDs. Here's how to correctly scope based on
      # any 'contemporary' record, given its known fixed 'effective_end'.
      ActiveRecord::Migration.add_index table,
                                        :unique => true,
                                        :name   => "index_#{ table }_uuid_end_unique",
                                        :where  => "(effective_end = '#{ SQL_DATE_MAXIMUM }')"

      # If there's any data in the table already, it can't have any historic
      # entries. So, we want to set the UUID to the 'id' field's old value,
      # but we can also leave the 'id' field as-is. New rows for historical
      # entries will acquire a new value of 'id' via Hoodoo.
      execute "UPDATE #{ table } SET uuid = id"

      # This won't follow the date/time rounding described by manual dating
      # but it's good enough for an initial migration.
      execute "UPDATE #{ table } SET effective_start = created_at"

      # Mark these records as contemporary/current.
      execute "UPDATE #{ table } SET effective_end = '#{ ActiveRecord::Base.connection.quoted_date( Hoodoo::ActiveRecord::ManuallyDated::DATE_MAXIMUM ) }'"

      # We couldn't add the UUID column with a not-null constraint until the
      # above SQL had run to update any existing records with a value. Now we
      # should put this back in, for rigour. Likewise for the start/end times.
      change_column_null table, :uuid,            false
      change_column_null table, :effective_start, false
      change_column_null table, :effective_end,   false


    # Now add back any indices dropped earlier, but add them back as a
    # conditional index as shown earlier for the "uuid" column. For example,
    # suppose you had declared this index somewhere:
    #   add_index :accounts, :account_number, :unique => true
    # You need to have done "remove_index :accounts, :account_number" earlier;
    # then now add the new equivalent. You may well find you have to give it a
    # custom name to avoid hitting index name length limits in your database:
    # ActiveRecord::Migration.add_index :accounts,
    #                                   :account_number,
    #                                   :unique => true,
    #                                   :name   => "index_#{ table }_account_number_end_unique",
    #                                   :where  => "(effective_end = '#{ SQL_DATE_MAXIMUM }')"
    # You might want to perform more detailed analysis on your index
    # requirements once manual dating is enabled, but the above is a good rule
    # of thumb.


  # This would fail if any historic entries now existed in the database,
  # because primary key 'id' values would get set to non-unique 'uuid'
  # values. This is intentional and required to avoid corruption; you
  # cannot roll back once history entries accumulate.
  def down

    # Remove any indices added manually at the end of "up", for example:
    #   remove_index :accounts, :name => 'index_accounts_an_es_ee'
    #   remove_index :accounts, :name => 'index_accounts_an_ee'

    TABLES_TO_CONVERT.each do | table |

      remove_index table, :name => "index_#{ table }_id_end"
      remove_index table, :name => "index_#{ table }_id_start_end"
      remove_index table, :name => "index_#{ table }_start_end"

      execute "UPDATE #{ table } SET id = uuid"

      remove_column table, :uuid
      remove_column table, :effective_end
      remove_column table, :effective_start


    # Add back any indexes you removed at the very start of "up", e.g.:
    #   add_index :accounts, :account_number, :unique => true


Rounding resolution, in terms of number of decimal places to which seconds are rounded. Excessive accuracy makes for difficult, large indices in the database and may fall foul of system / database clock accuracy mismatches.

Time.parse( '9999-12-31T23:59:59.0Z' ).round( SECONDS_DECIMAL_PLACES )

In order for indices to work properly on effective_end dates, NULL values cannot be permitted as SQL NULL is magic and means “has no value”, so such a value in a column prohibits indexing.

We might have used a NULL value in the 'end' date to mean “this is the contemporary/current record”, but since we can't do that, we need the rather nasty alternative of an agreed constant that defines a “large date” which represents “maximum possible end-of-time”.

SQL does not define a maximum date, but most implementations do. PostgreSQL has a very high maximum year, while SQLite, MS SQL Server and MySQL (following a cursory Google search for documentation) say that the end of year 9999 is as high as it goes.

To use this DATE_MAXIMUM constant in raw SQL, be sure to format the Time instance through your ActiveRecord database adapter thus:

ActiveRecord::Base.connection.quoted_date( Hoodoo::ActiveRecord::ManuallyDated::DATE_MAXIMUM )
# => returns "9999-12-31 23:59:59.000000" for PostgreSQL 9.4.
Class Public methods
included( model )

Instantiates this module when it is included.


class SomeModel < ActiveRecord::Base
  include Hoodoo::ActiveRecord::ManuallyDated
  # ...

Depends upon and auto-includes Hoodoo::ActiveRecord::UUID and Hoodoo::ActiveRecord::Finder.


The ActiveRecord::Base descendant that is including this module.

# File lib/hoodoo/active/active_record/manually_dated.rb, line 386
def self.included( model )
      :instance_predicate => false,
      :instance_accessor  => false

  unless model == Hoodoo::ActiveRecord::Base
    model.send( :include, Hoodoo::ActiveRecord::UUID   )
    model.send( :include, Hoodoo::ActiveRecord::Finder )

    instantiate( model )

  super( model )
instantiate( model )

When instantiated in an ActiveRecord::Base subclass, all of the Hoodoo::ActiveRecord::ManullyDated::ClassMethods methods are defined as class methods on the including class.


The ActiveRecord::Base descendant that is including this module.

# File lib/hoodoo/active/active_record/manually_dated.rb, line 412
def self.instantiate( model )
  model.extend( ClassMethods )