Temporal Data Modeling

Overview

Temporal data modeling addresses the challenge of representing data that changes over time while preserving historical context. Unlike traditional data models that store only current state, temporal models maintain a complete history of changes, enabling queries across different time periods and reconstruction of past states.

Applications requiring temporal data modeling include financial systems tracking transaction history, inventory systems monitoring stock levels over time, employee databases maintaining job history, and compliance systems requiring audit trails. The fundamental challenge lies in balancing query performance, storage efficiency, and data integrity while managing time-varying information.

Temporal models distinguish between two primary time dimensions: valid time (when data is true in the real world) and transaction time (when data is recorded in the system). A record might become valid on January 1st but not be recorded until January 15th, creating a distinction between these temporal perspectives.

# Simple temporal record structure
class EmployeeHistory
  attr_accessor :employee_id, :salary, :valid_from, :valid_to, :recorded_at
  
  def current?
    valid_to.nil? && valid_from <= Date.today
  end
  
  def valid_during?(date)
    valid_from <= date && (valid_to.nil? || valid_to > date)
  end
end

The complexity of temporal modeling increases with requirements for point-in-time queries, temporal joins between multiple changing entities, and handling of retroactive corrections. Systems must decide whether to support uni-temporal (single time dimension) or bi-temporal (both valid and transaction time) tracking based on business requirements and regulatory constraints.

Key Principles

Temporal data modeling rests on several foundational concepts that define how time-varying information is captured and queried. Understanding these principles clarifies the design decisions and trade-offs in temporal systems.

Valid Time represents when data is true in the real world, independent of when the system learns about it. An employee's salary increase might have a valid time of January 1st even if the change is entered into the system on December 15th of the previous year. Valid time typically uses ranges with start and end timestamps, where a NULL end time indicates the current state.

Transaction Time captures when data is recorded in the database. This dimension provides an audit trail of database modifications and enables point-in-time recovery. Transaction time is immutable—once recorded, historical transaction times cannot be changed. This property distinguishes transaction time from valid time, which can be adjusted retroactively.

Bi-temporal modeling combines both dimensions, creating a two-dimensional temporal space. Each fact exists at a specific point in valid time and a specific point in transaction time. This approach supports both "what did we know then?" queries (transaction time) and "what was true then?" queries (valid time), critical for compliance and audit requirements.

# Bi-temporal record structure
class BiTemporalRecord
  attr_accessor :data, :valid_from, :valid_to, :transaction_from, :transaction_to
  
  def self.query_at_time(valid_date, transaction_date)
    where('valid_from <= ? AND (valid_to IS NULL OR valid_to > ?)', valid_date, valid_date)
      .where('transaction_from <= ? AND (transaction_to IS NULL OR transaction_to > ?)', 
             transaction_date, transaction_date)
  end
  
  def self.current_view
    where(transaction_to: nil)
  end
end

Slowly Changing Dimensions (SCD) provide patterns for handling temporal changes in data warehousing. Type 1 overwrites old values, Type 2 creates new rows with temporal markers, and Type 3 maintains limited history in additional columns. Each type represents different trade-offs between history preservation and query complexity.

Point-in-time queries reconstruct the database state as it existed at a specific moment. These queries filter records based on temporal ranges, selecting only those valid at the target time. The complexity increases with bi-temporal queries that must consider both valid and transaction time dimensions.

Temporal joins connect time-varying entities by matching temporal ranges. An order joined with customer history must match the customer record valid when the order was placed, not necessarily the current customer state. These joins introduce complexity in query planning and execution.

Temporal normalization extends traditional normalization principles to temporal models. A temporally normalized design avoids redundancy across time while maintaining referential integrity. Denormalization decisions balance query performance against storage overhead and update complexity.

Implementation Approaches

Different temporal modeling strategies address varying requirements for history tracking, query patterns, and data integrity constraints. Selecting an appropriate approach depends on specific application needs and operational characteristics.

Separate History Tables maintain current records in one table and historical records in another. The current table contains only active rows with simple indexes, while the history table accumulates all changes. This approach separates operational queries (which access current data) from analytical queries (which access historical data), optimizing each workload independently.

class Employee < ApplicationRecord
  after_update :archive_changes
  
  private
  
  def archive_changes
    EmployeeHistory.create(
      employee_id: id,
      salary: salary_was,
      department: department_was,
      valid_from: updated_at_was,
      valid_to: updated_at
    )
  end
end

class EmployeeHistory < ApplicationRecord
  belongs_to :employee
  
  def self.at_date(date)
    where('valid_from <= ? AND (valid_to IS NULL OR valid_to > ?)', date, date)
  end
end

Inline History with Status Flags embeds temporal tracking within the primary table using validity markers. Each row includes valid_from, valid_to, and is_current flags. Queries filter on is_current for operational needs and on temporal ranges for historical queries. This approach simplifies referential integrity at the cost of table size and index overhead.

Event Sourcing stores every state change as an immutable event. The current state is derived by replaying events from the beginning or from a snapshot. Events capture the intent behind changes (SalaryIncreased, EmployeePromoted) rather than just end states. This approach provides complete audit trails and enables temporal queries by replaying events up to any point in time.

class EmployeeAggregate
  attr_reader :id, :state, :events
  
  def initialize(id)
    @id = id
    @state = {}
    @events = []
  end
  
  def load_from_event_store
    EventStore.events_for_aggregate(id).each do |event|
      apply_event(event, from_store: true)
    end
  end
  
  def change_salary(new_salary, effective_date)
    event = SalaryChanged.new(
      employee_id: id,
      previous_salary: state[:salary],
      new_salary: new_salary,
      effective_date: effective_date,
      occurred_at: Time.current
    )
    apply_event(event)
    events << event
  end
  
  def state_at(target_date)
    temp_state = {}
    EventStore.events_for_aggregate(id)
      .select { |e| e.occurred_at <= target_date }
      .each { |e| apply_event_to_state(e, temp_state) }
    temp_state
  end
  
  private
  
  def apply_event(event, from_store: false)
    case event
    when SalaryChanged
      state[:salary] = event.new_salary
    when DepartmentTransfer
      state[:department] = event.new_department
    end
    
    EventStore.append(event) unless from_store
  end
end

Temporal Tables with Database Support use built-in temporal features provided by databases like PostgreSQL (with temporal_tables extension) or SQL Server (system-versioned temporal tables). The database automatically maintains history tables and handles temporal queries through SQL:2011 temporal syntax. This approach offloads complexity to the database layer but requires database-specific features.

Snapshot-based Versioning creates periodic snapshots of entire tables or datasets. Each snapshot represents the complete state at a specific time. This approach suits scenarios with infrequent changes or where query patterns primarily access complete snapshots rather than individual entity histories. Storage overhead can be mitigated through compression and delta encoding between snapshots.

Design Considerations

Choosing a temporal modeling strategy requires evaluating multiple factors including query patterns, storage constraints, compliance requirements, and system complexity tolerance. Different approaches excel in different contexts.

Query Pattern Analysis determines which temporal approach fits the application. Systems dominated by current-state queries benefit from separate history tables or status-flag approaches that keep current data compact. Applications requiring frequent temporal range queries or point-in-time reconstruction favor inline temporal columns with appropriate indexes. Event-sourced systems work well when business logic depends on understanding the sequence of changes rather than just end states.

Storage and Performance Trade-offs create tension between history completeness and system efficiency. Inline temporal tables increase storage requirements and slow down current-state queries unless carefully indexed. Separate history tables isolate performance impacts but complicate cross-temporal queries and referential integrity. Event sourcing provides complete audit trails at the cost of requiring event replay for state reconstruction.

Temporal Referential Integrity poses challenges when related entities change over time. An order referencing a customer must specify which customer version applies. Systems can store point-in-time snapshots of related entities, maintain temporal foreign keys that reference specific validity periods, or accept denormalization to avoid complex temporal joins.

# Temporal foreign key approach
class Order < ApplicationRecord
  # References customer state valid when order was placed
  def customer_at_order_time
    Customer.valid_at(ordered_at).find(customer_id)
  end
end

class Customer < ApplicationRecord
  scope :valid_at, ->(time) {
    where('valid_from <= ? AND (valid_to IS NULL OR valid_to > ?)', time, time)
  }
end

# Snapshot approach
class Order < ApplicationRecord
  # Denormalized customer snapshot at order time
  serialize :customer_snapshot, Hash
  
  before_create :capture_customer_snapshot
  
  private
  
  def capture_customer_snapshot
    self.customer_snapshot = {
      name: customer.name,
      address: customer.address,
      tax_id: customer.tax_id
    }
  end
end

Retroactive Changes and Corrections require deciding whether to support modifying historical data. Bi-temporal models handle corrections by adding new records with updated valid times while preserving transaction time history. Uni-temporal models must choose between allowing updates (losing audit trail) or treating corrections as new events (maintaining history but complicating queries).

Temporal Granularity affects both storage and query precision. Date-level granularity suffices for many business applications but fails for high-frequency trading systems requiring microsecond precision. Coarser granularity reduces storage and simplifies queries; finer granularity increases accuracy and supports more precise temporal operations.

Handling Temporal Gaps and Overlaps requires validation logic. Most temporal models assume non-overlapping validity periods for a given entity, but business rules might permit gaps (employee between jobs) or overlaps (temporary dual role assignment). Implementing constraints and triggers prevents invalid temporal states while accommodating legitimate scenarios.

Ruby Implementation

Ruby applications implement temporal data modeling through Active Record extensions, dedicated gems, and custom temporal query patterns. Several approaches exist depending on application requirements and complexity tolerance.

Temporal Rails provides a popular gem for temporal data management in Rails applications. It extends Active Record models with temporal tracking, automatically maintaining history tables and providing temporal query methods.

# Gemfile
gem 'temporal_rails'

# Migration
class AddTemporalToEmployees < ActiveRecord::Migration[7.0]
  def change
    add_column :employees, :valid_from, :datetime, null: false, default: -> { 'CURRENT_TIMESTAMP' }
    add_column :employees, :valid_to, :datetime
    add_column :employees, :is_current, :boolean, default: true
    
    add_index :employees, [:id, :valid_from, :valid_to]
    add_index :employees, :is_current
  end
end

# Model with temporal_rails
class Employee < ApplicationRecord
  include TemporalRails::TemporalModel
  
  temporal_track :salary, :department, :title
  
  # Automatic temporal methods:
  # Employee.current -> returns only current records
  # Employee.at(date) -> returns records valid at date
  # Employee.between(start_date, end_date) -> returns records valid in range
  # employee.history -> returns all versions of this employee
end

# Usage
employee = Employee.create(name: 'Alice', salary: 80000)
employee.update(salary: 85000)
employee.update(salary: 90000)

# Query temporal history
employee.history.map(&:salary)  # => [80000, 85000, 90000]
employee.at(2.months.ago).salary  # => 80000

# Point-in-time queries
Employee.at(3.months.ago).where(department: 'Engineering')

Manual Temporal Implementation provides more control for specific requirements. This approach explicitly manages temporal columns and implements query scopes for temporal operations.

class Employee < ApplicationRecord
  scope :current, -> { where(valid_to: nil) }
  scope :valid_at, ->(date) {
    where('valid_from <= ? AND (valid_to IS NULL OR valid_to > ?)', date, date)
  }
  scope :valid_between, ->(start_date, end_date) {
    where('valid_from <= ? AND (valid_to IS NULL OR valid_to > ?)', end_date, start_date)
  }
  
  def self.close_period(employee_id, end_time)
    transaction do
      current_record = current.find_by(id: employee_id)
      current_record.update!(valid_to: end_time)
      
      new_record = current_record.dup
      new_record.valid_from = end_time
      new_record.valid_to = nil
      new_record.save!
      
      new_record
    end
  end
  
  def history
    self.class.where(id: id).order(:valid_from)
  end
  
  def snapshot_at(date)
    history.valid_at(date).first
  end
end

# Usage with explicit period management
employee = Employee.create(
  name: 'Bob',
  salary: 75000,
  valid_from: Date.new(2024, 1, 1),
  valid_to: nil
)

# Record salary change with explicit period closure
new_record = Employee.close_period(employee.id, Date.new(2024, 7, 1))
new_record.update!(salary: 80000)

# Query historical state
Employee.valid_at(Date.new(2024, 3, 15)).find_by(name: 'Bob').salary  # => 75000
Employee.valid_at(Date.new(2024, 9, 15)).find_by(name: 'Bob').salary  # => 80000

Event Store Implementation captures all changes as immutable events. Ruby implementations typically use gems like rails_event_store or implement custom event persistence.

# Using rails_event_store gem
class EmployeeEventHandler
  def call(event)
    case event
    when EmployeeHired
      Employee.create!(
        id: event.data[:employee_id],
        name: event.data[:name],
        hired_at: event.metadata[:timestamp]
      )
    when SalaryChanged
      employee = Employee.find(event.data[:employee_id])
      EmployeeSalaryHistory.create!(
        employee: employee,
        previous_salary: employee.salary,
        new_salary: event.data[:new_salary],
        effective_date: event.data[:effective_date],
        changed_at: event.metadata[:timestamp]
      )
      employee.update!(salary: event.data[:new_salary])
    end
  end
end

# Publishing events
event_store = RailsEventStore::Client.new
event_store.publish(
  SalaryChanged.new(data: {
    employee_id: 123,
    new_salary: 90000,
    effective_date: Date.today
  }),
  stream_name: "Employee$123"
)

# Rebuilding state from events
def rebuild_employee_state(employee_id, as_of_time)
  events = event_store
    .read
    .stream("Employee$#{employee_id}")
    .to_a
    .select { |e| e.metadata[:timestamp] <= as_of_time }
  
  state = {}
  events.each do |event|
    case event
    when EmployeeHired
      state[:name] = event.data[:name]
      state[:hired_at] = event.data[:hired_at]
    when SalaryChanged
      state[:salary] = event.data[:new_salary]
    when DepartmentTransferred
      state[:department] = event.data[:new_department]
    end
  end
  state
end

Bi-temporal Implementation tracks both valid time and transaction time for complete temporal history. This requires careful management of both dimensions and complex query logic.

class BiTemporalEmployee < ApplicationRecord
  scope :current_perspective, -> {
    where(transaction_to: nil)
  }
  
  scope :as_of_transaction_time, ->(time) {
    where('transaction_from <= ? AND (transaction_to IS NULL OR transaction_to > ?)', 
          time, time)
  }
  
  scope :valid_during, ->(time) {
    where('valid_from <= ? AND (valid_to IS NULL OR valid_to > ?)', time, time)
  }
  
  scope :at_both_times, ->(valid_time, transaction_time) {
    valid_during(valid_time).as_of_transaction_time(transaction_time)
  }
  
  def self.record_change(employee_id, new_attributes, effective_date)
    transaction do
      # Close current transaction time period
      current = current_perspective
        .where(id: employee_id)
        .valid_during(Time.current)
        .first
      
      if current
        current.update!(transaction_to: Time.current)
      end
      
      # Create new record with updated attributes
      create!(
        id: employee_id,
        **new_attributes,
        valid_from: effective_date,
        valid_to: nil,
        transaction_from: Time.current,
        transaction_to: nil
      )
    end
  end
  
  # Answer "What did we think Bob's salary was on June 1st, 
  # based on what we knew on July 15th?"
  def self.historical_perspective(name, valid_date, transaction_date)
    at_both_times(valid_date, transaction_date)
      .find_by(name: name)
  end
end

Common Patterns

Temporal data modeling employs recurring patterns that address specific temporal tracking requirements. These patterns represent tested solutions to common temporal challenges.

Slowly Changing Dimension Type 2 maintains full history by creating new rows for each change while preserving historical records. Each row includes temporal markers indicating its validity period. This pattern dominates data warehousing implementations due to its simplicity and query flexibility.

class ProductDimensionSCD2 < ApplicationRecord
  # Table: product_dimension_scd2
  # Columns: surrogate_key, natural_key, name, price, valid_from, valid_to, is_current
  
  scope :current, -> { where(is_current: true) }
  scope :at_date, ->(date) {
    where('valid_from <= ? AND (valid_to IS NULL OR valid_to > ?)', date, date)
  }
  
  def self.update_dimension(natural_key, new_attributes)
    transaction do
      # Expire current record
      current_record = current.find_by(natural_key: natural_key)
      if current_record
        current_record.update!(
          valid_to: Time.current,
          is_current: false
        )
      end
      
      # Create new current record
      create!(
        natural_key: natural_key,
        **new_attributes,
        valid_from: Time.current,
        valid_to: nil,
        is_current: true
      )
    end
  end
  
  # Temporal join for fact tables
  def self.join_to_facts_at_time(fact_table, date)
    fact_table
      .joins(<<-SQL)
        INNER JOIN product_dimension_scd2 
        ON #{fact_table.table_name}.product_natural_key = product_dimension_scd2.natural_key
        AND product_dimension_scd2.valid_from <= '#{date}'
        AND (product_dimension_scd2.valid_to IS NULL OR product_dimension_scd2.valid_to > '#{date}')
      SQL
  end
end

Temporal Snapshot Pattern creates periodic full copies of data state. Instead of tracking individual changes, the system captures complete snapshots at defined intervals. This pattern suits scenarios where analyzing states at specific intervals matters more than tracking every individual change.

class InventorySnapshot < ApplicationRecord
  # Captures complete inventory state at regular intervals
  
  def self.create_snapshot
    snapshot_time = Time.current
    
    transaction do
      Inventory.current.find_each do |item|
        create!(
          snapshot_time: snapshot_time,
          item_id: item.id,
          quantity: item.quantity,
          location: item.location,
          status: item.status
        )
      end
    end
  end
  
  def self.compare_snapshots(time1, time2)
    snap1 = where(snapshot_time: time1).index_by(&:item_id)
    snap2 = where(snapshot_time: time2).index_by(&:item_id)
    
    changes = []
    snap2.each do |item_id, current|
      previous = snap1[item_id]
      if previous && previous.quantity != current.quantity
        changes << {
          item_id: item_id,
          quantity_change: current.quantity - previous.quantity,
          from: time1,
          to: time2
        }
      end
    end
    changes
  end
end

Temporal State Machine Pattern tracks entity lifecycle stages with temporal transitions. Each state change creates a new temporal record capturing when the entity entered and exited specific states.

class OrderState < ApplicationRecord
  belongs_to :order
  
  enum state: {
    pending: 0,
    confirmed: 1,
    processing: 2,
    shipped: 3,
    delivered: 4,
    cancelled: 5
  }
  
  scope :current, -> { where(exited_at: nil) }
  scope :in_state_at, ->(state, time) {
    where(state: state)
      .where('entered_at <= ? AND (exited_at IS NULL OR exited_at > ?)', time, time)
  }
  
  def self.transition(order_id, new_state)
    transaction do
      # Exit current state
      current = current.find_by(order_id: order_id)
      current.update!(exited_at: Time.current) if current
      
      # Enter new state
      create!(
        order_id: order_id,
        state: new_state,
        entered_at: Time.current,
        exited_at: nil
      )
    end
  end
  
  def self.time_in_state(order_id, state)
    where(order_id: order_id, state: state)
      .sum { |record| 
        exit_time = record.exited_at || Time.current
        exit_time - record.entered_at
      }
  end
end

Effective Dating Pattern separates when a change is known from when it becomes effective. This pattern handles scenarios like scheduled price changes or employment contracts with future start dates.

class PriceSchedule < ApplicationRecord
  # effective_date: when price takes effect
  # created_at: when price was scheduled
  
  scope :effective_on, ->(date) {
    where('effective_date <= ?', date)
      .order(effective_date: :desc)
      .limit(1)
  }
  
  scope :upcoming, -> {
    where('effective_date > ?', Date.current)
      .order(:effective_date)
  }
  
  def self.current_price(product_id)
    effective_on(Date.current)
      .find_by(product_id: product_id)
      &.price
  end
  
  def self.schedule_price_change(product_id, new_price, effective_date)
    create!(
      product_id: product_id,
      price: new_price,
      effective_date: effective_date
    )
  end
  
  def self.price_on_date(product_id, date)
    where(product_id: product_id)
      .where('effective_date <= ?', date)
      .order(effective_date: :desc)
      .first
      &.price
  end
end

Performance Considerations

Temporal queries introduce performance challenges through increased data volume, complex temporal predicates, and multi-dimensional temporal joins. Optimization strategies address these challenges through indexing, query design, and architectural patterns.

Temporal Indexes enable efficient temporal range queries. Composite indexes on (entity_id, valid_from, valid_to) support both current-state lookups and historical range scans. B-tree indexes handle temporal ranges effectively, but specialized temporal index structures can improve performance for complex temporal queries.

# Migration for temporal indexes
class AddTemporalIndexes < ActiveRecord::Migration[7.0]
  def change
    # Composite index for temporal queries
    add_index :employees, [:id, :valid_from, :valid_to], 
              name: 'idx_employees_temporal'
    
    # Covering index for current records
    add_index :employees, [:id, :is_current], 
              where: 'is_current = true',
              name: 'idx_employees_current'
    
    # Index for point-in-time queries
    add_index :employees, [:valid_from, :valid_to],
              name: 'idx_employees_validity_period'
  end
end

# Query optimization using indexes
class Employee < ApplicationRecord
  # Efficient current lookup (uses partial index)
  def self.find_current(id)
    where(id: id, is_current: true).first
  end
  
  # Efficient temporal range query (uses composite index)
  def self.active_during(start_date, end_date)
    where('valid_from <= ? AND (valid_to IS NULL OR valid_to > ?)', 
          end_date, start_date)
  end
end

Partitioning Strategies improve query performance by physically segregating temporal data. Range partitioning on temporal columns isolates historical data from current data, allowing queries to skip irrelevant partitions. PostgreSQL table partitioning supports this approach.

# PostgreSQL partitioning for temporal tables
class CreatePartitionedEmployees < ActiveRecord::Migration[7.0]
  def up
    execute <<-SQL
      CREATE TABLE employees_partitioned (
        id BIGINT NOT NULL,
        name VARCHAR(255),
        salary DECIMAL(10,2),
        valid_from TIMESTAMP NOT NULL,
        valid_to TIMESTAMP,
        PRIMARY KEY (id, valid_from)
      ) PARTITION BY RANGE (valid_from);
      
      -- Current year partition
      CREATE TABLE employees_2024 PARTITION OF employees_partitioned
        FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
      
      -- Historical partitions
      CREATE TABLE employees_2023 PARTITION OF employees_partitioned
        FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
    SQL
  end
end

# Partition-aware queries automatically prune irrelevant partitions
Employee.where(valid_from: Date.new(2024, 6, 1)..Date.new(2024, 12, 31))
# Only scans employees_2024 partition

Materialized Views precompute expensive temporal queries. Complex temporal aggregations or multi-table temporal joins benefit from materialization, trading freshness for query speed.

class CreateTemporalMaterializedViews < ActiveRecord::Migration[7.0]
  def up
    execute <<-SQL
      CREATE MATERIALIZED VIEW employee_salary_history AS
      SELECT 
        id,
        name,
        salary,
        valid_from,
        valid_to,
        COALESCE(valid_to, CURRENT_TIMESTAMP) - valid_from as duration,
        LAG(salary) OVER (PARTITION BY id ORDER BY valid_from) as previous_salary
      FROM employees
      ORDER BY id, valid_from;
      
      CREATE INDEX ON employee_salary_history (id, valid_from);
    SQL
  end
  
  def down
    execute 'DROP MATERIALIZED VIEW employee_salary_history'
  end
end

# Refresh strategy
class EmployeeSalaryHistory < ApplicationRecord
  self.table_name = 'employee_salary_history'
  
  def self.refresh
    connection.execute('REFRESH MATERIALIZED VIEW CONCURRENTLY employee_salary_history')
  end
  
  # Schedule periodic refresh
  # In config/initializers/temporal_views.rb:
  # Rails.application.config.after_initialize do
  #   EmployeeSalaryHistory.refresh if Rails.env.production?
  # end
end

Query Optimization Techniques minimize temporal query overhead. Avoiding correlated subqueries in temporal joins, using window functions for temporal aggregations, and database-specific temporal features improve performance.

# Inefficient: Correlated subquery for each order
def orders_with_customer_name_slow
  Order.select('orders.*, (
    SELECT name FROM customers 
    WHERE customers.id = orders.customer_id 
    AND customers.valid_from <= orders.created_at 
    AND (customers.valid_to IS NULL OR customers.valid_to > orders.created_at)
    LIMIT 1
  ) as customer_name')
end

# Efficient: Single temporal join
def orders_with_customer_name_fast
  Order
    .joins(<<-SQL)
      INNER JOIN customers ON customers.id = orders.customer_id
      AND customers.valid_from <= orders.created_at
      AND (customers.valid_to IS NULL OR customers.valid_to > orders.created_at)
    SQL
    .select('orders.*, customers.name as customer_name')
end

# Window function for temporal aggregations
def salary_changes_with_percentage
  connection.select_all(<<-SQL)
    SELECT 
      id,
      valid_from,
      salary,
      LAG(salary) OVER (PARTITION BY id ORDER BY valid_from) as previous_salary,
      CASE 
        WHEN LAG(salary) OVER (PARTITION BY id ORDER BY valid_from) IS NOT NULL
        THEN ((salary - LAG(salary) OVER (PARTITION BY id ORDER BY valid_from)) 
              / LAG(salary) OVER (PARTITION BY id ORDER BY valid_from) * 100)
        ELSE NULL
      END as percentage_change
    FROM employees
    ORDER BY id, valid_from
  SQL
end

Archival Strategies balance performance with historical data retention requirements. Moving ancient historical data to separate archive tables or external storage reduces active table size while preserving queryable history.

Practical Examples

Real-world temporal scenarios demonstrate how different temporal patterns solve specific business problems. These examples show complete implementations including edge cases and integration concerns.

Employee Management System tracks employment history, salary changes, and organizational structure over time. The system must answer questions like "Who reported to Manager X on date Y?" and "What was Employee Z's salary progression?"

class Employee < ApplicationRecord
  scope :employed_on, ->(date) {
    where('hire_date <= ? AND (termination_date IS NULL OR termination_date > ?)', 
          date, date)
  }
  
  scope :in_department_on, ->(department, date) {
    joins(:department_histories)
      .where(department_histories: { department: department })
      .where('department_histories.valid_from <= ? AND 
              (department_histories.valid_to IS NULL OR department_histories.valid_to > ?)',
             date, date)
  }
end

class DepartmentHistory < ApplicationRecord
  belongs_to :employee
  
  def self.organizational_structure_at(date)
    where('valid_from <= ? AND (valid_to IS NULL OR valid_to > ?)', date, date)
      .includes(:employee)
      .group_by(&:department)
      .transform_values { |histories| histories.map(&:employee) }
  end
  
  def self.transfer_employee(employee, new_dept, effective_date)
    transaction do
      # Close current assignment
      current = where(employee: employee, valid_to: nil).first
      current.update!(valid_to: effective_date) if current
      
      # Create new assignment
      create!(
        employee: employee,
        department: new_dept,
        valid_from: effective_date,
        valid_to: nil
      )
    end
  end
end

class SalaryHistory < ApplicationRecord
  belongs_to :employee
  
  def self.compensation_on(employee_id, date)
    where(employee_id: employee_id)
      .where('effective_date <= ?', date)
      .order(effective_date: :desc)
      .first
  end
  
  def self.salary_progression(employee_id)
    where(employee_id: employee_id)
      .order(:effective_date)
      .select(:effective_date, :salary, :currency)
  end
  
  def self.average_raise_percentage(employee_id)
    changes = salary_progression(employee_id).each_cons(2).map do |prev, curr|
      ((curr.salary - prev.salary).to_f / prev.salary * 100).round(2)
    end
    changes.sum / changes.size if changes.any?
  end
end

# Usage example
employee = Employee.create(name: 'Alice', hire_date: Date.new(2020, 1, 15))
SalaryHistory.create(employee: employee, salary: 80000, effective_date: Date.new(2020, 1, 15))
SalaryHistory.create(employee: employee, salary: 85000, effective_date: Date.new(2021, 1, 15))
DepartmentHistory.transfer_employee(employee, 'Engineering', Date.new(2020, 1, 15))

# Temporal queries
employee.salary_history.compensation_on(Date.new(2020, 6, 1)).salary  # => 80000
Employee.in_department_on('Engineering', Date.new(2020, 6, 1))  # => [#<Employee id: 1, name: "Alice">]

Product Pricing with Effective Dating manages scheduled price changes, promotional pricing, and historical price tracking for e-commerce systems. The system must handle overlapping promotions and maintain audit trails.

class ProductPrice < ApplicationRecord
  belongs_to :product
  
  enum price_type: { regular: 0, promotional: 1, clearance: 2 }
  
  scope :effective_on, ->(date) {
    where('effective_from <= ? AND (effective_to IS NULL OR effective_to > ?)', date, date)
  }
  
  def self.current_price(product_id)
    where(product_id: product_id)
      .effective_on(Date.current)
      .order(price_type: :desc)  # Promotional takes precedence
      .first
  end
  
  def self.schedule_promotion(product_id, promo_price, start_date, end_date)
    create!(
      product_id: product_id,
      amount: promo_price,
      price_type: :promotional,
      effective_from: start_date,
      effective_to: end_date
    )
  end
  
  def self.price_history(product_id, start_date, end_date)
    where(product_id: product_id)
      .where('effective_from <= ? AND (effective_to IS NULL OR effective_to > ?)',
             end_date, start_date)
      .order(:effective_from)
  end
end

class Order < ApplicationRecord
  has_many :order_items
  
  # Snapshot price at order time
  def finalize_prices
    order_items.each do |item|
      price = ProductPrice.current_price(item.product_id)
      item.update!(
        unit_price: price.amount,
        price_type: price.price_type
      )
    end
  end
end

# Usage
product = Product.create(name: 'Widget')
ProductPrice.create(product: product, amount: 99.99, 
                   price_type: :regular, effective_from: Date.new(2024, 1, 1))
ProductPrice.schedule_promotion(product.id, 79.99, 
                                Date.new(2024, 6, 1), Date.new(2024, 6, 30))

# Price lookups
ProductPrice.current_price(product.id).amount  # => depends on current date
ProductPrice.effective_on(Date.new(2024, 6, 15))
  .find_by(product_id: product.id).amount  # => 79.99 (promotional)

Insurance Policy Management requires bi-temporal tracking to handle policy changes, retroactive corrections, and regulatory requirements for maintaining both what was recorded when and what was actually true when.

class PolicyVersion < ApplicationRecord
  belongs_to :policy
  
  scope :current_knowledge, -> { where(recorded_to: nil) }
  scope :known_at, ->(time) {
    where('recorded_from <= ? AND (recorded_to IS NULL OR recorded_to > ?)', time, time)
  }
  scope :valid_at, ->(time) {
    where('valid_from <= ? AND (valid_to IS NULL OR valid_to > ?)', time, time)
  }
  
  def self.record_policy_change(policy_id, changes, valid_from)
    transaction do
      # Close current recorded version
      current = current_knowledge
        .where(policy_id: policy_id)
        .valid_at(Time.current)
        .first
      
      current.update!(recorded_to: Time.current) if current
      
      # Create new version
      create!(
        policy_id: policy_id,
        **changes,
        valid_from: valid_from,
        valid_to: nil,
        recorded_from: Time.current,
        recorded_to: nil
      )
    end
  end
  
  def self.retroactive_correction(policy_id, corrections, valid_from, valid_to)
    transaction do
      # Close current recorded version
      current = current_knowledge
        .where(policy_id: policy_id)
        .where('valid_from <= ? AND (valid_to IS NULL OR valid_to > ?)', 
               valid_from, valid_from)
        .first
      
      if current
        current.update!(recorded_to: Time.current)
      end
      
      # Insert corrected version
      create!(
        policy_id: policy_id,
        **corrections,
        valid_from: valid_from,
        valid_to: valid_to,
        recorded_from: Time.current,
        recorded_to: nil,
        correction: true
      )
    end
  end
  
  # Answer: "What did we think the coverage amount was on June 1st,
  # based on what we knew on July 15th?"
  def self.historical_perspective(policy_id, valid_date, recorded_date)
    where(policy_id: policy_id)
      .valid_at(valid_date)
      .known_at(recorded_date)
      .first
  end
end

# Usage
policy = Policy.create(policy_number: 'POL-001')
PolicyVersion.record_policy_change(policy.id, 
  { coverage_amount: 100000, premium: 1200 }, 
  Date.new(2024, 1, 1))

# Retroactive correction: coverage was actually 150000
PolicyVersion.retroactive_correction(policy.id,
  { coverage_amount: 150000, premium: 1200 },
  Date.new(2024, 1, 1),
  Date.new(2024, 6, 1))

# Queries
PolicyVersion.historical_perspective(policy.id, 
  Date.new(2024, 3, 1), 
  Date.new(2024, 5, 1))  # => coverage_amount: 100000 (before correction)

PolicyVersion.historical_perspective(policy.id,
  Date.new(2024, 3, 1),
  Date.new(2024, 7, 1))  # => coverage_amount: 150000 (after correction)

Reference

Temporal Modeling Comparison

Temporal Query Patterns

Ruby Gem Comparison

Index Strategy Guidelines

Common Temporal Validations

Performance Optimization Checklist