partition_plus_aggregates.diff

diff --git a/Gemfile b/Gemfile
index e3a71e1..34ba75c 100644
--- a/Gemfile
+++ b/Gemfile
@@ -107,3 +107,4 @@ gem "pundit"
 gem "rack-canonical-host"
 gem "ruby-readability"
 gem "rinku", require: "rails_rinku"
+gem "virtus"
diff --git a/app/controllers/articles_controller.rb b/app/controllers/articles_controller.rb
index 92e0108..f5b7d45 100644
--- a/app/controllers/articles_controller.rb
+++ b/app/controllers/articles_controller.rb
@@ -9,7 +9,17 @@ def show
 
   def click
     @article = Article.find(params[:id])
-    # TODO: Store the click in the database
+
+    ErrorReporter.rescue_and_report do
+      # TODO: Batch these in memory at some point and insert in batches.
+      Click.create({
+        article_id: @article.id,
+        user_id: current_user&.id,
+        ip: request.remote_ip,
+        created_at: Time.zone.now,
+      })
+    end
+
     redirect_to @article.url, allow_other_host: true
   end
 end
diff --git a/app/jobs/click_hour_aggregate_job.rb b/app/jobs/click_hour_aggregate_job.rb
new file mode 100644
index 0000000..5a92aa8
--- /dev/null
+++ b/app/jobs/click_hour_aggregate_job.rb
@@ -0,0 +1,11 @@
+class ClickHourAggregateJob < ApplicationJob
+  queue_as :aggregates
+
+  def perform
+    now = Time.now.utc
+    # TODO: Store last successful aggregation and aggregate from that point
+    # instead of past few hours. Then use the last aggregation time to determine
+    # whether to use live or aggregate data in the click count for articles.
+    ClickHourAggregate.rollup(from: now - 2.hours, to: now)
+  end
+end
diff --git a/app/jobs/clicks_maintenance_job.rb b/app/jobs/clicks_maintenance_job.rb
new file mode 100644
index 0000000..379b476
--- /dev/null
+++ b/app/jobs/clicks_maintenance_job.rb
@@ -0,0 +1,13 @@
+class ClicksMaintenanceJob < ApplicationJob
+  queue_as :maintenance
+
+  def perform
+    ErrorReporter.rescue_and_report do
+      Click.partition.premake(3)
+    end
+
+    ErrorReporter.rescue_and_report do
+      Click.partition.retain(14)
+    end
+  end
+end
diff --git a/app/models/article.rb b/app/models/article.rb
index 1d91d06..d3c3b81 100644
--- a/app/models/article.rb
+++ b/app/models/article.rb
@@ -1,4 +1,7 @@
 class Article < ApplicationRecord
+  # The time at which we started tracking and aggregating clicks.
+  AGGREGATION_START_TIME = Time.utc(2022, 9, 26, 11).freeze
+
   belongs_to :site
 
   scope :by_published_at, -> { order(published_at: :desc) }
@@ -6,4 +9,51 @@ class Article < ApplicationRecord
   validates :title, presence: true
   validates :url, presence: true, uniqueness: true, format: { with: URI::regexp(%w{http https}) }
   validates :published_at, presence: true
+
+  # The aggregated data for this article (click count and latest timestamp).
+  def aggregated_data
+    return @aggregated_data if defined?(@aggregated_data)
+
+    current_hour = Time.now.utc.beginning_of_hour
+    sql = SQL.new <<~SQL.squish, article_id: id, current_hour: current_hour
+      SELECT
+        sum(count) as count,
+        max(ts) as max_ts
+      FROM #{ClickHourAggregate.table_name}
+      WHERE
+        article_id = :article_id AND
+        ts < :current_hour
+    SQL
+
+    row = sql.hash_results.first || {}
+    max_ts = row["max_ts"]
+
+    @aggregated_data = {
+      count: row["count"] || 0,
+      max_ts: max_ts,
+    }
+  end
+
+  # The count of clicks that have been aggregated.
+  def aggregated_count
+    aggregated_data.fetch(:count)
+  end
+
+  # The maximum timestamp of any aggregated data.
+  def aggregated_max_ts
+    aggregated_data.fetch(:max_ts)
+  end
+
+  # Sums up counts that have not been aggregated yet.
+  def live_count
+    return @live_count if defined?(@live_count)
+
+    start = (aggregated_max_ts ? aggregated_max_ts + 1.hour : AGGREGATION_START_TIME)
+    @live_count = Click.count(article_id: id, created_at: (start..Time.now.utc))
+  end
+
+  # Sums up aggregated counts and live counts that have not been aggregated yet.
+  def click_count
+    aggregated_count + live_count
+  end
 end
diff --git a/app/models/click.rb b/app/models/click.rb
new file mode 100644
index 0000000..bbf01e1
--- /dev/null
+++ b/app/models/click.rb
@@ -0,0 +1,182 @@
+class Click
+  class Row
+    include Virtus.value_object
+
+    attribute :user_id, Integer
+    attribute :article_id, Integer
+    attribute :ip, String
+    attribute :created_at, DateTime
+
+    delegate :site, to: :article
+
+    def article
+      return if article_id.blank?
+      return @article if defined?(@article)
+
+      @article = Article.find_by_id(article_id)
+    end
+
+    def user
+      return if user_id.blank?
+      return @user if defined?(@user)
+
+      @user = User.find_by_id(user_id)
+    end
+  end
+
+  # Name of the parent table that the partitions inherit from.
+  def self.table_name
+    "clicks".freeze
+  end
+
+  # Build a partition instance for a given time.
+  def self.partition(time = Time.now.utc)
+    PartitionByDay.new(table_name, time)
+  end
+
+  # Public: Create one or more adapter request logs from an Array of Hashes.
+  #
+  # Note: This method assumes the partition is already created.
+  # Use partition(time).create if it does not exist.
+  # In production, jobs should automatically create new partitions.
+  #
+  # Returns Result.
+  def self.create_many(*rows)
+    Result.new {
+      insert_rows = rows.flatten.map do |row|
+        [
+          row[:user_id].presence || SQL::NULL,
+          row[:article_id],
+          row[:ip],
+          row.fetch(:created_at).utc,
+        ]
+      end
+
+      SQL.run <<~SQL.squish, rows: SQL::ROWS(insert_rows)
+        INSERT INTO #{table_name} (user_id, article_id, ip, created_at)
+        VALUES :rows
+      SQL
+
+      nil
+    }
+  end
+
+  # Public: Create an adapter request log from a Hash.
+  #
+  # Note: This method assumes the partition is already created.
+  # Use partition(time).create if it does not exist.
+  # In production, jobs should automatically create new partitions.
+  #
+  # Returns Result.
+  def self.create(attributes = {})
+    create_many([attributes])
+  end
+
+  # TODO: See how to use pagy for this pagination instead of custom.
+  #
+  # Public: Paginate adapter request logs.
+  #
+  # page - The Integer page (default: 1).
+  # per_page - The Integer per_page (default: 20).
+  # site_id - The Integer site_id to filter returned logs for.
+  # user_id - The Integer user_id to filter returned logs for.
+  #
+  # Returns PaginateResponse.
+  def self.paginate(page: 1, per_page: 20, site_id: nil, user_id: nil, article_id: nil, created_at: nil)
+    page ||= 1
+    per_page ||= 20
+    page = page.to_i
+    per_page = per_page.to_i
+
+    raise ArgumentError, "page must be >= 1 (was #{page})" unless page >= 1
+    raise ArgumentError, "per_page must be >= 1 (was #{per_page})" unless per_page >= 1
+
+    limit = per_page + 1
+    offset = (page - 1) * per_page
+
+    sql = build_sql(
+      select: "#{table_name}.*",
+      site_id: site_id,
+      user_id: user_id,
+      article_id: article_id,
+      created_at: created_at
+    )
+    sql.add "ORDER BY created_at DESC"
+    sql.add "LIMIT :limit OFFSET :offset", limit: limit, offset: offset
+
+    PaginateResponse.new({
+      page: page,
+      per_page: per_page,
+      has_next_page: sql.hash_results.slice!(per_page, 1).present?,
+      rows: sql.hash_results.map { |row| Row.new(row) },
+    })
+  end
+
+  # Private: Count the number of adapter request logs. Only use this in tests.
+  #
+  # Returns Integer number of logs.
+  def self.count(article_id: nil, site_id: nil, user_id: nil, created_at: nil)
+    build_sql(
+      select: "COUNT(*)",
+      article_id: article_id,
+      site_id: site_id,
+      user_id: user_id,
+      created_at: created_at
+    ).value
+  end
+
+  # Private: Return the last adapter request log row. Only use this in tests.
+  #
+  # Returns a Row if found else nil.
+  def self.last
+    rows = SQL.hash_results <<~SQL.squish
+      SELECT * FROM #{table_name} ORDER BY created_at DESC LIMIT 1
+    SQL
+
+    if rows.size == 1
+      Row.new(rows[0])
+    else
+      nil
+    end
+  end
+
+  # Private: Build a SQL query for clicks that can filter based on article,
+  # site and user.
+  def self.build_sql(select: "*", article_id: nil, site_id: nil, user_id: nil, created_at: nil)
+    sql = SQL.new("SELECT #{select} FROM #{table_name}")
+    sql.add "INNER JOIN articles a ON a.id = #{table_name}.article_id INNER JOIN sites s ON s.id = a.site_id" if site_id.present?
+    sql.add "INNER JOIN users u ON u.id = #{table_name}.user_id" if user_id.present?
+
+    fragments = []
+    binds = {}
+
+    if user_id.present?
+      fragments << "u.id = :user_id"
+      binds[:user_id] = user_id
+    end
+
+    if site_id.present?
+      fragments << "s.id = :site_id"
+      binds[:site_id] = site_id
+    end
+
+    if article_id.present?
+      fragments << "#{table_name}.article_id = :article_id"
+      binds[:article_id] = article_id
+    end
+
+    if created_at.present?
+      fragments << "#{table_name}.created_at >= :from AND #{table_name}.created_at <= :to"
+      binds[:from] = created_at.first
+      binds[:to] = created_at.last
+    end
+
+    if fragments.any?
+      sql.add "WHERE"
+      sql.add fragments.join(" AND "), binds
+    end
+
+    sql
+  end
+  class << self; private :build_sql; end
+end
diff --git a/app/models/click_hour_aggregate.rb b/app/models/click_hour_aggregate.rb
new file mode 100644
index 0000000..f1509a6
--- /dev/null
+++ b/app/models/click_hour_aggregate.rb
@@ -0,0 +1,32 @@
+class ClickHourAggregate < ApplicationRecord
+  def self.rollup(from:, to:)
+    raise ArgumentError, "from is required" if from.blank?
+    raise ArgumentError, "to is required" if to.blank?
+    raise ArgumentError, "from must be less than to" unless from < to
+
+    binds = {
+      from: from,
+      to: to,
+      insert_table: SQL::LITERAL(table_name),
+      select_table: SQL::LITERAL(Click.table_name),
+    }
+
+    SQL.run <<~SQL.squish, binds
+      INSERT INTO :insert_table (article_id, ts, count)
+        SELECT
+          article_id,
+          date_trunc('hour', created_at) AS ts,
+          count(*) as count
+        FROM :select_table
+        WHERE
+          article_id IS NOT NULL AND
+          created_at BETWEEN :from AND :to
+        GROUP BY 1, 2
+      ON CONFLICT (article_id, ts)
+      DO UPDATE SET
+        count = EXCLUDED.count
+    SQL
+  end
+
+  belongs_to :article
+end
diff --git a/app/models/paginate_response.rb b/app/models/paginate_response.rb
new file mode 100644
index 0000000..63ecd6f
--- /dev/null
+++ b/app/models/paginate_response.rb
@@ -0,0 +1,54 @@
+class PaginateResponse
+  include Enumerable
+  include Virtus.value_object
+
+  attribute :page, Integer
+  attribute :per_page, Integer
+  attribute :has_next_page, Boolean
+  attribute :rows, Array
+
+  def next_page
+    has_next_page? ? page + 1 : nil
+  end
+
+  def next_page?
+    has_next_page?
+  end
+
+  def prev_page
+    prev_page? ? page - 1 : nil
+  end
+
+  def prev_page?
+    page > 1
+  end
+
+  def length
+    rows.length
+  end
+  alias size length
+
+  def empty?
+    rows.empty?
+  end
+
+  def to_ary
+    rows
+  end
+
+  def [](idx)
+    rows[idx]
+  end
+
+  def last(n = nil)
+    n ? rows.last(n) : rows.last
+  end
+
+  def each(&block)
+    if block_given?
+      rows.each(&block)
+    else
+      rows.to_enum { @rows.size }
+    end
+  end
+end
diff --git a/app/models/partition_by_day.rb b/app/models/partition_by_day.rb
new file mode 100644
index 0000000..27d6c31
--- /dev/null
+++ b/app/models/partition_by_day.rb
@@ -0,0 +1,254 @@
+# https://www.postgresql.org/docs/13/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE
+class PartitionByDay
+  class Row
+    include Virtus.model
+
+    attribute :name, String
+    attribute :expression, String
+  end
+
+  def self.validate_table(table:)
+    raise ArgumentError, "table cannot be blank" if table.blank?
+    raise ArgumentError, "table must be a String" unless table.is_a?(String)
+
+    table
+  end
+
+  def self.validate_name(table:, name:)
+    validate_table(table: table)
+
+    raise ArgumentError, "name must be a String" unless name.is_a?(String)
+    unless name.starts_with?(table)
+      raise ArgumentError, "name (#{name}) must start with table (#{table})"
+    end
+    unless name =~ /_\d{4}_\d{2}_\d{2}$/
+      raise ArgumentError, "name must end with yyyy_mm_dd but does not (#{name})"
+    end
+
+    name
+  end
+
+  def self.validate_from(from:)
+    raise ArgumentError, "from must not be nil" if from.nil?
+
+    from
+  end
+
+  def self.validate_to(to:)
+    raise ArgumentError, "to must not be nil" if to.nil?
+
+    to
+  end
+
+  def self.validate_number(number:)
+    raise ArgumentError, "number must not be nil" if number.nil?
+    unless number >= 2
+      raise ArgumentError, "number should be at least 2 or whats the point"
+    end
+
+    number
+  end
+
+  # Fetch all partitions for a given table.
+  def self.all(table:)
+    validate_table(table: table)
+
+    rows = SQL.hash_results <<-SQL.squish, table: table
+      SELECT pg_class.relname AS name,
+        pg_get_expr(pg_class.relpartbound, pg_class.oid, true) AS expression
+      FROM pg_class base_tb
+        JOIN pg_inherits ON pg_inherits.inhparent = base_tb.oid
+        JOIN pg_class ON pg_class.oid = pg_inherits.inhrelid
+      WHERE base_tb.oid = :table::regclass;
+    SQL
+
+    rows.map { |row| Row.new(row) }
+  end
+
+  # Generate a partition name based on table and from time.
+  #
+  # table - The String name of the source table.
+  # from - The Time of the new partition.
+  #
+  # Returns String partition name.
+  def self.name(table:, from:)
+    validate_table(table: table)
+    validate_from(from: from)
+
+    "#{table}_%d_%02d_%02d" % [from.year, from.month, from.day]
+  end
+
+  # Create new partition for provided table.
+  #
+  # table - The String name of the source table.
+  # name - The String name of the new partition.
+  # from - The Time to start the range of the partition.
+  # to - The Time to end the range of the partition.
+  #
+  # Returns nothing.
+  # Raises if anything goes wrong.
+  def self.create(table:, name:, from:, to:)
+    validate_name(table: table, name: name)
+    validate_from(from: from)
+    validate_to(to: to)
+
+    binds = {
+      table: SQL::LITERAL(table),
+      name: SQL::LITERAL(name),
+      from: from,
+      to: to,
+    }
+
+    SQL.run <<~SQL.squish, binds
+      CREATE TABLE IF NOT EXISTS :name
+      PARTITION OF :table FOR VALUES FROM (:from) TO (:to)
+    SQL
+
+    nil
+  end
+
+  # Premake several partitions from a given time. Also tries to create a
+  # partition for the from time so sometimes you ask for 3 partitions but get 4
+  # if the partition does not exist for the provided time.
+  #
+  # table - The String name of the source table.
+  # from - The Time to start premaking partitions from.
+  # number - The Integer number of partitions to create.
+  #
+  # Returns nothing.
+  # Raises if anything goes wrong.
+  def self.premake(table:, from: Time.now.utc, number: 3)
+    validate_table(table: table)
+    validate_from(from: from)
+    validate_number(number: number)
+
+    start = from.to_date
+    stop = start + number
+
+    (start..stop).each do |date|
+      new(table, date).create
+    end
+
+    nil
+  end
+
+  # Retain a given number of partitions and detch + drop the rest.
+  #
+  # table - The String name of the source table.
+  # from - The Time to determine retention from.
+  # number - The Integer number of partitions to older than from time.
+  #
+  # Returns nothing.
+  # Raises if anything goes wrong.
+  def self.retain(table:, from: Time.now.utc, number: 14)
+    validate_table(table: table)
+    validate_from(from: from)
+    validate_number(number: number)
+
+    date = from.to_date - number
+    binds = {
+      relname_pattern: "#{table}_%",
+      max_relname: name(table: table, from: date),
+    }
+    prunable = SQL.values <<~SQL.squish, binds
+        SELECT relname
+          FROM pg_class c
+          JOIN pg_namespace n ON n.oid = c.relnamespace
+         WHERE nspname = 'public' AND
+               relname LIKE :relname_pattern AND
+               relkind = 'r' AND
+               relname <= :max_relname
+      ORDER BY relname
+    SQL
+
+    prunable.each { |name|
+      detach(table: table, name: name)
+      drop(table: table, name: name)
+    }
+
+    nil
+  end
+
+  # Drops a partition table.
+  #
+  # table - The String name of the source table.
+  # name - The String name of the partition.
+  #
+  # Returns nothing.
+  # Raises if anything goes wrong.
+  def self.drop(table:, name:)
+    validate_name(table: table, name: name)
+
+    SQL.run <<~SQL.squish, name: SQL::LITERAL(name)
+      DROP TABLE IF EXISTS :name
+    SQL
+
+    nil
+  end
+
+  # Detaches a partition from a table. Once detached you can do whatever with it
+  # and it won't show up in query results.
+  #
+  # table - The String name of the source table.
+  # name - The String name of the partition.
+  #
+  # Returns nothing.
+  # Raises if anything goes wrong.
+  def self.detach(table:, name:)
+    validate_name(table: table, name: name)
+
+    SQL.run <<~SQL.squish, table: SQL::LITERAL(table), name: SQL::LITERAL(name)
+      ALTER TABLE IF EXISTS :table DETACH PARTITION :name;
+    SQL
+
+    nil
+  end
+
+  def self.exists?(name)
+    raise ArgumentError, "name can't be blank" if name.blank?
+
+    ActiveRecord::Base.connection.table_exists?(name)
+  end
+  class << self; alias exist? exists?; end
+
+  attr_reader :from, :to, :table, :name
+
+  def initialize(table, from)
+    self.class.validate_table(table: table)
+    self.class.validate_from(from: from)
+
+    @from = from.to_time.utc.beginning_of_day
+    @to = @from + 1.day
+    @table = table
+    @name = self.class.name(table: @table, from: @from)
+  end
+
+  def create
+    self.class.create(table: @table, name: @name, from: @from, to: @to)
+  end
+
+  def premake(number)
+    self.class.premake(table: @table, from: @from, number: number)
+  end
+
+  def retain(number)
+    self.class.retain(table: @table, from: @from, number: number)
+  end
+
+  def detach
+    self.class.detach(table: @table, name: @name)
+  end
+
+  def drop
+    self.class.drop(table: @table, name: @name)
+  end
+
+  def exists?
+    self.class.exists?(@name)
+  end
+  alias :exist? :exists?
+
+  def all
+    self.class.all(table: @table)
+  end
+end
diff --git a/app/views/articles/index.html.erb b/app/views/articles/index.html.erb
index 15d7ea1..b0207c5 100644
--- a/app/views/articles/index.html.erb
+++ b/app/views/articles/index.html.erb
@@ -13,6 +13,7 @@
     <div class="small text-muted">
       <%= article.site.name %>
       <span title="<%= article.published_at %>"><%= time_ago_in_words article.published_at %> ago</span>
+      &bull; <%= pluralize article.click_count, "view" %>
     </div>
   </div>
 <%- end -%>
diff --git a/config/application.rb b/config/application.rb
index 70686ae..f53c3c9 100644
--- a/config/application.rb
+++ b/config/application.rb
@@ -24,6 +24,9 @@ class Application < Rails::Application
 
     config.active_job.queue_adapter = :good_job
 
+    # Because we use partitioned tables and ruby/rails schema doesn't support that.
+    config.active_record.schema_format = :sql
+
     config.generators.assets = false
     config.generators.helper = false
     config.generators.jbuilder = false
@@ -35,10 +38,15 @@ class Application < Rails::Application
 
     config.good_job.enable_cron = true
     config.good_job.cron = {
-      frequent_task: {
-        cron: "@hourly",
+      refresh_sites: {
+        cron: "1 * * * *",
         class: "RefreshSitesJob",
       },
+
+      click_hour_aggregates: {
+        cron: "5 0-23 * * *",
+        class: "ClickHourAggregateJob",
+      },
     }
   end
 end
diff --git a/db/migrate/20220926122307_create_partitioned_clicks.rb b/db/migrate/20220926122307_create_partitioned_clicks.rb
new file mode 100644
index 0000000..145df80
--- /dev/null
+++ b/db/migrate/20220926122307_create_partitioned_clicks.rb
@@ -0,0 +1,20 @@
+class CreatePartitionedClicks < ActiveRecord::Migration[7.0]
+  def up
+    execute <<~SQL
+      CREATE TABLE clicks (
+        user_id integer,
+        article_id integer NOT NULL,
+        ip inet NOT NULL,
+        created_at timestamp without time zone NOT NULL
+      ) PARTITION BY RANGE (created_at);
+    SQL
+
+    add_index :clicks, :user_id, where: "user_id IS NOT NULL"
+    add_index :clicks, :article_id
+    add_index :clicks, :created_at, order: {created_at: :desc}
+  end
+
+  def down
+    drop_table :clicks
+  end
+end
diff --git a/db/migrate/20220926134853_create_click_hour_aggregates.rb b/db/migrate/20220926134853_create_click_hour_aggregates.rb
new file mode 100644
index 0000000..1ae96f8
--- /dev/null
+++ b/db/migrate/20220926134853_create_click_hour_aggregates.rb
@@ -0,0 +1,10 @@
+class CreateClickHourAggregates < ActiveRecord::Migration[7.0]
+  def change
+    create_table :click_hour_aggregates do |t|
+      t.references :article, null: false
+      t.integer :count, null: false
+      t.datetime :ts, null: false
+      t.index [:article_id, :ts], unique: true
+    end
+  end
+end
diff --git a/test/controllers/articles_controller_test.rb b/test/controllers/articles_controller_test.rb
index a17acf9..8fe769b 100644
--- a/test/controllers/articles_controller_test.rb
+++ b/test/controllers/articles_controller_test.rb
@@ -19,8 +19,24 @@ class ArticlesControllerTest < ActionDispatch::IntegrationTest
   end
 
   test "should get click" do
+    freeze_time
+    Click.partition(Time.zone.now).create
     article = articles(:rare_unusual_patek_calatrava)
-    get click_article_path(article)
+    assert_difference 'Click.count(article_id: article.id)' do
+      get click_article_path(article)
+    end
+    assert_redirected_to article.url
+  end
+
+  test "should get click with signed in user" do
+    freeze_time
+    Click.partition(Time.zone.now).create
+    user = users(:john)
+    article = articles(:rare_unusual_patek_calatrava)
+    sign_in user
+    assert_difference 'Click.count(article_id: article.id, user_id: user.id)' do
+      get click_article_path(article)
+    end
     assert_redirected_to article.url
   end
 end
diff --git a/test/fixtures/click_hour_aggregates.yml b/test/fixtures/click_hour_aggregates.yml
new file mode 100644
index 0000000..1f0df1d
--- /dev/null
+++ b/test/fixtures/click_hour_aggregates.yml
@@ -0,0 +1,11 @@
+# Read about fixtures at https://api.rubyonrails.org/classes/ActiveRecord/FixtureSet.html
+
+# This model initially had no columns defined. If you add columns to the
+# model remove the "{}" from the fixture names and add the columns immediately
+# below each fixture, per the syntax in the comments below
+#
+# one: {}
+# column: value
+#
+# two: {}
+# column: value
diff --git a/test/jobs/click_hour_aggregate_job_test.rb b/test/jobs/click_hour_aggregate_job_test.rb
new file mode 100644
index 0000000..5558cc6
--- /dev/null
+++ b/test/jobs/click_hour_aggregate_job_test.rb
@@ -0,0 +1,7 @@
+require "test_helper"
+
+class ClickHourAggregateJobTest < ActiveJob::TestCase
+  # test "the truth" do
+  #   assert true
+  # end
+end
diff --git a/test/models/article_test.rb b/test/models/article_test.rb
index 9c9ba95..75ffd3f 100644
--- a/test/models/article_test.rb
+++ b/test/models/article_test.rb
@@ -1,7 +1,42 @@
 require "test_helper"
 
 class ArticleTest < ActiveSupport::TestCase
-  # test "the truth" do
-  #   assert true
-  # end
+  test "click_count for article with clicks" do
+    freeze_time
+
+    article = articles(:rare_unusual_patek_calatrava)
+    current_hour = Time.zone.now.beginning_of_hour.utc
+    two_hours_ago = current_hour - 2.hours
+    three_hours_ago = current_hour - 3.hours
+    live_time = current_hour == Time.zone.now ? current_hour : current_hour + 1.second
+
+    ClickHourAggregate.create!(article: article, count: 2, ts: current_hour)
+    ClickHourAggregate.create!(article: article, count: 2, ts: two_hours_ago)
+    ClickHourAggregate.create!(article: article, count: 6, ts: three_hours_ago)
+
+    [
+      current_hour,
+      two_hours_ago,
+      three_hours_ago,
+      live_time,
+    ].each do |time|
+      Click.partition(time).create
+    end
+
+    Click.create_many([
+      {article_id: article.id, created_at: two_hours_ago, ip: "127.0.0.1"}, # shouldn't count
+      {article_id: article.id, created_at: two_hours_ago, ip: "127.0.0.1"}, # shouldn't count
+      {article_id: article.id, created_at: three_hours_ago, ip: "127.0.0.1"}, # shouldn't count
+      {article_id: article.id, created_at: live_time, ip: "127.0.0.1"},
+      {article_id: article.id, created_at: live_time, ip: "127.0.0.1"},
+      {article_id: article.id, created_at: live_time, ip: "127.0.0.1"},
+    ]).value!
+
+    assert_equal 11, article.click_count
+  end
+
+  test "click_count for article without clicks" do
+    article = articles(:rare_unusual_patek_calatrava)
+    assert_equal 0, article.click_count
+  end
 end
diff --git a/test/models/click_hour_aggregate_test.rb b/test/models/click_hour_aggregate_test.rb
new file mode 100644
index 0000000..ac3c9b6
--- /dev/null
+++ b/test/models/click_hour_aggregate_test.rb
@@ -0,0 +1,51 @@
+require "test_helper"
+
+class ClickHourAggregateTest < ActiveSupport::TestCase
+  setup do
+    freeze_time
+    Click.partition.create
+    @end_of_day = Time.now.utc.end_of_day
+  end
+
+  test "rollup" do
+    Click.create_many([
+      {
+        article_id: 1,
+        ip: "127.0.0.1",
+        created_at: @end_of_day,
+      },
+      {
+        article_id: 1,
+        ip: "127.0.0.1",
+        created_at: @end_of_day - 1.hour,
+      },
+      {
+        article_id: 2,
+        ip: "127.0.0.1",
+        created_at: @end_of_day - 1.hour,
+      },
+      {
+        article_id: 3,
+        ip: "127.0.0.1",
+        created_at: @end_of_day - 2.hours,
+      },
+    ])
+
+    assert_difference 'ClickHourAggregate.count', 4 do
+      ClickHourAggregate.rollup(from: @end_of_day - 2.days, to: @end_of_day)
+    end
+
+    hours = ClickHourAggregate.where(article_id: 1)
+    assert_equal 2, hours.size
+    assert_equal 1, hours[0].count
+    assert_equal 1, hours[1].count
+
+    hours = ClickHourAggregate.where(article_id: 2)
+    assert_equal 1, hours.size
+    assert_equal 1, hours[0].count
+
+    hours = ClickHourAggregate.where(article_id: 3)
+    assert_equal 1, hours.size
+    assert_equal 1, hours[0].count
+  end
+end
diff --git a/test/models/click_test.rb b/test/models/click_test.rb
new file mode 100644
index 0000000..496e51b
--- /dev/null
+++ b/test/models/click_test.rb
@@ -0,0 +1,95 @@
+require "test_helper"
+
+class ClickTest < ActiveSupport::TestCase
+  setup do
+    freeze_time
+    @attributes = valid_attributes
+    Click.partition(@attributes[:created_at]).create
+  end
+
+  test ".create" do
+    Click.create(@attributes)
+
+    log = Click.last
+    refute_nil log
+    assert_equal users(:john).id, log["user_id"]
+    assert_equal articles(:rare_unusual_patek_calatrava).id, log["article_id"]
+    assert_equal "127.0.0.1", log["ip"]
+    assert_equal Time.zone.now, log["created_at"]
+  end
+
+  test ".create without user_id" do
+    Click.create(@attributes.except(:user_id))
+
+    log = Click.last
+    refute_nil log
+    assert_nil log["user_id"]
+    assert_equal articles(:rare_unusual_patek_calatrava).id, log["article_id"]
+    assert_equal "127.0.0.1", log["ip"]
+    assert_equal Time.zone.now, log["created_at"]
+  end
+
+  test ".create_many with args" do
+    Click.create_many(@attributes, @attributes, @attributes)
+    assert_equal 3, Click.count
+  end
+
+  test ".create_many with array" do
+    Click.create_many([@attributes, @attributes, @attributes])
+    assert_equal 3, Click.count
+  end
+
+  test ".create_many fails when partition doesn't exist" do
+    Click.partition(@attributes[:created_at]).drop
+    result = Click.create_many(@attributes)
+    refute_predicate result, :ok?
+    assert_instance_of ActiveRecord::StatementInvalid, result.error
+    assert_instance_of PG::CheckViolation, result.error.cause
+  end
+
+  test ".count" do
+    Click.create(@attributes)
+    assert_equal 1, Click.count
+    assert_equal 1, Click.count(
+      article_id: articles(:rare_unusual_patek_calatrava).id,
+      user_id: users(:john).id
+    )
+    assert_equal 1, Click.count(site_id: articles(:rare_unusual_patek_calatrava).site_id)
+    assert_equal 1, Click.count(created_at: Time.zone.now.beginning_of_day..Time.zone.now.end_of_day)
+  end
+
+  test ".paginate" do
+    Click.create(@attributes)
+    assert_equal 1, Click.paginate.size
+    assert_equal 1, Click.paginate(
+      article_id: articles(:rare_unusual_patek_calatrava).id,
+      user_id: users(:john).id
+    ).size
+    assert_equal 0, Click.paginate(
+      article_id: articles(:rare_unusual_patek_calatrava).id,
+      user_id: users(:kris).id
+    ).size
+    assert_equal 0, Click.paginate(article_id: 0).size
+    assert_equal 1, Click.paginate(site_id: articles(:rare_unusual_patek_calatrava).site_id).size
+    assert_equal 0, Click.paginate(page: 2).size
+
+    Click.create(@attributes)
+    assert_equal 1, Click.paginate(
+      article_id: articles(:rare_unusual_patek_calatrava).id,
+      user_id: users(:john).id,
+      per_page: 1,
+      page: 2
+    ).size
+  end
+
+  private
+
+  def valid_attributes(attributes = {})
+    {
+      user_id: users(:john).id,
+      article_id: articles(:rare_unusual_patek_calatrava).id,
+      ip: "127.0.0.1",
+      created_at: Time.zone.now,
+    }.merge(attributes)
+  end
+end
diff --git a/test/models/partition_by_day_test.rb b/test/models/partition_by_day_test.rb
new file mode 100644
index 0000000..0b59cf4
--- /dev/null
+++ b/test/models/partition_by_day_test.rb
@@ -0,0 +1,150 @@
+require 'test_helper'
+
+class PartitionByDayTest < ActiveSupport::TestCase
+  def table_name
+    "partitioned_fake_table"
+  end
+
+  setup do
+    SQL.run <<~SQL.squish
+      CREATE TABLE #{table_name} (
+        created_at timestamp without time zone NOT NULL
+      ) PARTITION BY RANGE (created_at);
+    SQL
+  end
+
+  test ".all and #all" do
+    time = Time.utc(2021, 10, 12, 12, 3, 3)
+    partition = PartitionByDay.new(table_name, time)
+    partition.create
+
+    partitions = PartitionByDay.all(table: table_name).map(&:name)
+    assert_includes partitions, partition.name
+    assert_includes partition.all.map(&:name), partition.name
+  end
+
+  test "#name" do
+    {
+      Time.utc(2021, 9, 9, 12, 3, 3) => "#{table_name}_2021_09_09",
+      Time.utc(2021, 10, 9, 12, 3, 3) => "#{table_name}_2021_10_09",
+      Time.utc(2021, 10, 12, 12, 3, 3) => "#{table_name}_2021_10_12",
+    }.each do |time, name|
+      assert_equal name, PartitionByDay.new(table_name, time).name
+      assert_equal name, PartitionByDay.new(table_name, time).name
+    end
+  end
+
+  test "#from" do
+    {
+      Time.utc(2021, 9, 9, 12, 3, 3) => Time.utc(2021, 9, 9, 0, 0, 0),
+      Time.utc(2021, 10, 12, 12, 3, 3) => Time.utc(2021, 10, 12, 0, 0, 0),
+    }.each do |time, name|
+      assert_equal name, PartitionByDay.new(table_name, time).from
+      assert_equal name, PartitionByDay.new(table_name, time).from
+    end
+  end
+
+  test "#to" do
+    {
+      Time.utc(2021, 9, 9, 12, 3, 3) => Time.utc(2021, 9, 10, 0, 0, 0),
+      Time.utc(2021, 10, 12, 12, 3, 3) => Time.utc(2021, 10, 13, 0, 0, 0),
+    }.each do |time, name|
+      assert_equal name, PartitionByDay.new(table_name, time).to
+      assert_equal name, PartitionByDay.new(table_name, time).to
+    end
+  end
+
+  test "#create" do
+    time = Time.utc(2021, 10, 12, 12, 3, 3)
+    partition = PartitionByDay.new(table_name, time)
+    refute_includes ActiveRecord::Base.connection.tables, partition.name
+    partition.create
+    assert_includes ActiveRecord::Base.connection.tables, partition.name
+
+    # verify the partition is all created right
+    partitions = PartitionByDay.all(table: table_name)
+    assert_equal 1, partitions.size
+    assert_equal "#{table_name}_2021_10_12", partitions[0].name
+    assert_equal "FOR VALUES FROM ('2021-10-12 00:00:00') TO ('2021-10-13 00:00:00')",
+      partitions[0].expression
+  end
+
+  test "#exist?" do
+    time = Time.utc(2021, 10, 12, 12, 3, 3)
+    partition = PartitionByDay.new(table_name, time)
+    refute_predicate partition, :exist?
+    partition.create
+    assert_predicate partition, :exist?
+  end
+
+  test "#exists?" do
+    time = Time.utc(2021, 10, 12, 12, 3, 3)
+    partition = PartitionByDay.new(table_name, time)
+    refute_predicate partition, :exists?
+    partition.create
+    assert_predicate partition, :exists?
+  end
+
+  test "#detach" do
+    time = Time.utc(2021, 10, 12, 12, 3, 3)
+    partition = PartitionByDay.new(table_name, time)
+    partition.create
+    partition.detach
+    partitions = PartitionByDay.all(table: table_name).map(&:name)
+    refute_includes partitions, partition.name
+  end
+
+  test "#drop" do
+    time = Time.utc(2021, 10, 12, 12, 3, 3)
+    partition = PartitionByDay.new(table_name, time)
+    partition.create
+    partition.drop
+    partitions = PartitionByDay.all(table: table_name).map(&:name)
+    refute_includes partitions, partition.name
+  end
+
+  test "#premake" do
+    time = Time.utc(2021, 9, 9)
+    partition = PartitionByDay.new(table_name, time)
+    partition.create
+
+    ActiveRecord::Base.uncached do
+      partition.premake(3)
+      assert_equal 4, PartitionByDay.all(table: table_name).size
+
+      partition.premake(5)
+      assert_equal 6, PartitionByDay.all(table: table_name).size
+    end
+  end
+
+  test "#retain" do
+    hi = Time.utc(2021, 9, 30)
+    low = hi - 20.days
+
+    (low.to_date..hi.to_date).each do |date|
+      PartitionByDay.new(table_name, date.to_time).create
+    end
+
+    ActiveRecord::Base.uncached do
+      assert_equal 21, PartitionByDay.all(table: table_name).size
+      PartitionByDay.new(table_name, hi).retain(14)
+      assert_equal 14, PartitionByDay.all(table: table_name).size
+    end
+  end
+
+  private
+
+  def valid_attributes(attributes = {})
+    {
+      status: 200,
+      method: "GET",
+      path: "/adapter/features",
+      ip: "127.0.0.1",
+      token_id: tokens(:john_api).id,
+      created_at: Time.zone.now,
+      headers: {
+        "content_type" => "application/json",
+      },
+    }.merge(attributes)
+  end
+end
diff --git a/test/test_helper.rb b/test/test_helper.rb
index 4fe687a..c03a9c4 100644
--- a/test/test_helper.rb
+++ b/test/test_helper.rb
@@ -23,3 +23,7 @@ class ActiveSupport::TestCase
 class ActionDispatch::IntegrationTest
   include Devise::Test::IntegrationHelpers
 end
+
+Click.partition.all.map(&:name).each do |name|
+  PartitionByDay.drop(table: Click.table_name, name: name)
+end