A small ActiveRecord example implementing event sourcing using CTE

event_sourcing_with_cte.rb

# frozen_string_literal: true

require 'bundler/inline'

gemfile(true) do
  source 'https://rubygems.org'

  gem 'rails', '~> 7.1.0'
  gem 'sqlite3'
end

require 'rails/all'
database = ':memory:'

ENV['DATABASE_URL'] = "sqlite3:#{database}"
ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: database)
ActiveRecord::Base.logger = Logger.new(STDOUT)
ActiveRecord::Schema.define do
  create_table "users", force: :cascade do |t|
    t.string "name"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  create_table "lessons", force: :cascade do |t|
    t.string "title"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  create_table "lesson_user_events", force: :cascade do |t|
    t.integer "user_id", null: false
    t.integer "lesson_id", null: false
    t.string "event", null: false
    t.datetime "created_at", null: false
    t.index [ "lesson_id" ], name: "index_lesson_user_events_on_lesson_id"
    t.index [ "user_id", "lesson_id", "event" ], name: "index_lesson_user_events_on_user_id_and_lesson_id_and_event", unique: true
    t.index [ "user_id" ], name: "index_lesson_user_events_on_user_id"
  end

  add_foreign_key "lesson_user_events", "lessons"
  add_foreign_key "lesson_user_events", "users"
end

class User < ActiveRecord::Base
  validates :name, presence: true
end

class Lesson < ActiveRecord::Base
  has_many :events, -> { order(created_at: :desc) },
            class_name: "LessonUserEvent",
            inverse_of: :lesson,
            dependent: :destroy

  validates :title, presence: true

  scope :of, ->(user) {
    with(events: LessonUserEvent.with_row_number.by(user))
      .joins("LEFT OUTER JOIN events ON events.row_number = 1 AND events.lesson_id = lessons.id")
      .select("lessons.*, COALESCE(events.event, 'todo') as status")
  }

  def started_by?(user)
    events.by(user).exists?(event: "start")
  end

  def start_by!(user)
    events.by(user).find_or_create_by!(event: "start")
  end

  def completed_by?(user)
    events.by(user).exists?(event: "complete")
  end

  def complete_by!(user)
    events.by(user).find_or_create_by!(event: "complete")
  end
end

class LessonUserEvent < ActiveRecord::Base
  belongs_to :user
  belongs_to :lesson

  validates :event, presence: true

  scope :by, ->(user) { where(user: user) }
  scope :with_row_number, -> {
    select(
      :user_id, :lesson_id, :event, :created_at, :updated_at,
      "ROW_NUMBER() OVER(PARTITION BY lesson_id ORDER BY created_at DESC) as row_number"
    )
  }
end

user = User.create!(name: "John Doe")
lessons = [
  Lesson.create!(title: "Lesson 1"),
  Lesson.create!(title: "Lesson 2"),
  Lesson.create!(title: "Lesson 3")
]
lessons[0].start_by!(user)
lessons[0].complete_by!(user)
lessons[1].start_by!(user)

puts <<~MESSAGE
# Run the following to test status based on events
# Notice that it required a single SQL query
Lesson.of(User.first).each do |lesson|
  puts lesson.attributes.values_at("title", "status").join(": ")
end
MESSAGE