クラウドテックのテーブル構造を改善していった話

はじめに

クラウドワークスの運営する クラウドテック というサービスで開発に携わっている @hamajyotan といいます。ここでは、最近クラウドテックのデータベース構造の課題を解消した話をします。

クラウドテックは、ハイクラスなフリーランス IT・WEB エンジニアやデザイナーに特化したマッチングサポートサービスとして 2015年4月から始まったサービスです。サイト自体は Rails 4.0 あたりからスタートし、現在 Rails 6.0.3.3 で動いています。

データはアプリケーションより長寿なので改善を要する

一般に、データ(ベース) の寿命はアプリケーションの寿命より長いと言われています。例えばシステムの統合、あるいはシステムのフルスクラッチなどがあったとしてもそれとは別観点でデータベースはそのまま残すか否かは検討の余地があり、そしてそのまま既存のデータを引き続き扱うことも多いです。

そして、データに長く付き合うためにはプログラムと同様、頻度の差はあれど定期的なリファクタリングはあるべきでしょう。 プログラムと違い、 Security Fix や Rails バージョンアップの変更に追従するなどの動機はあまりありませんが、少し前にバズっていた技術的負債の解消などはデータベース構造のリファクタリングの動機となりえます。

改修前のざっくりとした構造

今回の話題の登場人物を示しています。

※ ここでの名前は、ある程度抽象したものに置き換えており実際の名前とは異なります。

• 「ユーザ (User)」はクラウドテックに登録くださっているユーザそのものを表します。
• 「クライアント (Client)」はクラウドテックに案件を提供くださっている企業を表します。
• 「案件 (Project)」は、特定のクライアントの案件を特定のユーザが実施する業務を示し、通常 3ヶ月前後の期間で実施されます。
• 「月間作業報告 (MonthlyReport)」は、ユーザが案件の業務を実施する際の作業報告書のイメージです。
  • 月ごとに分かれており、例えば 1月から3月までの3ヶ月間の案件であれば、1レコードの案件から 3レコードの月間作業報告が発生することになります。
  • 「案件ID × 年月」 の組み合わせで、一意制約がかけられています。
• 「クライアント請求 (Bill)」および「ユーザ支払 (Payment)」は、月ごとの稼働の検収を経て、クライアント/ユーザそれぞれに対する請求/支払を意味するデータです。
  • 「月間作業報告」と同様に月ごとに分かれており、例えば3ヶ月間の案件に対しそれぞれ 3レコード発生することになります。
  • 「案件ID × 年月」 の組み合わせで、一意制約がかけられています。

問題点およびその対応

上記の構造から、以下のような構造上の課題が存在していました。

1. 一つの事実が複数箇所に点在している
2. 年月を意味する情報が多数のテーブルで存在する
3. 年月のデータ型について (※)

※ 3 については上記の構造からは判断できないものなので、背景含めて後述します。

一つの事実が複数箇所に点在している -> 1事実1箇所にする

月間作業報告には、案件ID、クライアントID、ユーザID などの外部テーブルへの参照キーが存在していました。この中でクライアントID、ユーザID については、案件ID から参照できる案件にも同様の事実が存在しており冗長です。 むしろ何らかのバグで案件が参照するクライアント/ユーザと月間作業報告が参照するクライアント/ユーザが異なるIDとなってしまう余地が存在することを意味しており冗長なので、月間作業報告書のクライアントIDおよびユーザID は列を削除することにします。

幸いなことに手を入れる時点で、

何らかのバグで案件が参照するクライアント/ユーザと月間作業報告が参照するクライアント/ユーザが異なるIDとなってしまう余地

は存在しませんでした。非常にラッキーでした。もしそういったデータがバグで混入してしまっていた場合は直接プロダクションのデータをさわってどうにかする必要がありました。 (もっとも、一部テーブルに NOT NULL 制約がなく、何らかのバグで NULL となっていた箇所があり調査を要しましたが…)

Rails のアソシエーションに関する問題

月間作業報告にはもともとユーザやクライアントに対する参照が存在していたので、以下のような belongs_to が存在していました。

# app/models/monthly_report.rb

class MonthlyReport < ApplicationRecord
  belongs_to :project
  belongs_to :client
  belongs_to :user

# app/models/project.rb

class Project
  belongs_to :client
  belongs_to :user
  has_many :monthly_reports # 月の数だけある

この度 ユーザやクライアントへの参照列を除去することになるので、このままだとプログラム中に存在する 月間作業報告.ユーザ / 月間作業報告.クライアント などの記述はすべてエラーになります。 これの対応としては、クライアントやユーザに対するアクセスは、案件に委譲することで多くは回避できました。

# app/models/monthly_report.rb

class MonthlyReport
  belongs_to :project
  delegate :client, :user, to: :project

# app/models/project.rb

class Project
  belongs_to :client
  belongs_to :user
  has_many :monthly_reports # 月の数だけある

もちろん、これで完全に対応仕切ることができたわけではありません。たとえば N+1 回避のための eager_load の記述は厳密にテーブルの結合関係を記述する必要があるのでそういった箇所は地道に潰していく必要がありました。 なお、逆向きの関連は through オプションをうまく使うことである程度改修量を減らすことができます。

この結果、下記のような構造になりました。リレーションシップが減って少しスッキリです。

年月を意味する情報が多数のテーブルで存在する -> 年月を1箇所に集約する

「月間作業報告」「ユーザ支払」「クライアント請求」はそれぞれ「案件」に対する 1対多の関係となっています。

• Project has_many MonthlyReport(s)
• Project has_many Payment(s)
• Project has_many Bill(s)

この関係は「とある年月の、月間作業報告/ユーザ支払/クライアント請求 を結合して参照する」というケースに対応するには「年月が 2020年4月である」という条件をそれぞれのテーブルに対して加える必要が生じることになり非常につらいクエリになります。

Project.left_joins([:monthly_reports, :payments, :bills]).
    merge(MonthlyReport.where(month: '202004').or(MonthlyReport.where(id: nil))).
    merge(Payment.where(month: '202004').or(Payment.where(id: nil))).
    merge(Bill.where(month: '202004').or(Bill.where(id: nil)))
  )

この構造はアプリケーション上でももちろん、別途データ解析の際にも同様の記述を要しており、いろいろな使われ方をするたびに辛みが伝搬します。 そこで、案件と（月間作業報告|ユーザ支払|クライアント請求）との間に「案件×年月」を意味するテーブル（案件期間）を配置し、 3テーブルから年月列を取り除いた上で案件期間への参照を持つことで構造を簡素化することができました。

先のクエリは、以下のようなイメージに置き換わります。

ProjectMonthlyTerm.where(month: '202004').joins(:project).left_joins(:monthly_report, :payment, :bill)

最終的に、この度の登場人物のモデル達は以下のような定義を持つ感じになりました。

# 案件
class Project < ApplicationRecord
  belongs_to :user
  belongs_to :client
  has_many :project_monthly_terms

# 案件期間
class ProjectMonthlyTerm < ApplicationRecord
  belongs_to :project
  has_one :monthly_report
  has_one :payment
  has_one :bill

# 月間作業報告
class MonthlyReport < ApplicationRecord
  belongs_to :project_monthly_term
  delegate :project, to: :project_monthly_term
  delegate :user, :client, to: :project

# ユーザ支払
class Payment < ApplicationRecord
  belongs_to :project_monthly_term
  delegate :project, to: :project_monthly_term
  delegate :user, :client, to: :project

# クライアント請求
class Bill < ApplicationRecord
  belongs_to :project_monthly_term
  delegate :project, to: :project_monthly_term
  delegate :user, :client, to: :project

年月のデータ型について

データベースに「年月」を意味する列を定義するとき、おおまかには 2つの表現方法があると思います。

1. 数値型/文字列型のフィールドに保持する方式
   • 実際には 6桁の数値を保存。たとえば 2020年9月であれば、 202009 のような要領。
   • 日付型のフィールドを YYYYMM の形に書式化して保存する必要がある & 場合によってはオブジェクトに戻す際に Date.parse などで Date 型に戻す必要がある。
2. 日付型のフィールドに保持する方式
   • 2020-09-01 のように、 day のパートには常に 1を入れる。
   • 保存時は beginning_of_month で月初にする。
   • Date 型なので、そのまま next_month なりの操作がそのまま可能。

もともと、「月間作業報告」「ユーザ支払」「クライアント請求」の年月フィールドには前者、つまり数値型で 6桁の数値を保持ようにしていましたが、今回の登場人物以外のテーブルで年月を意味する値として後者の方式を採るテーブルが増えており混在する状況になっていました。 今回は年月フィールドのテーブル移行を機に、後者の日付型で保持する方式に統一することにしました。

  def change
    create_table :project_monthly_terms do |t|
      t.references :project, type: :integer, null: false, foreign_key: true
      t.date :target_month, null: false  # date 型
      t.timestamps
    end
  end

attribute API

ただ、上記で定義すると既存のクエリ条件が正しくなくなってしまう箇所が存在します。

ProjectMonthlyTerm.where(target_month: Date.new(2020,9)).to_sql
#=> "SELECT `project_monthly_terms`.* FROM `project_monthly_terms` WHERE `project_monthly_terms`.`target_month` = '2020-09-01'"

ProjectMonthlyTerm.where(target_month: 202009).to_sql
#=> "SELECT `project_monthly_terms`.* FROM `project_monthly_terms` WHERE `project_monthly_terms`.`target_month` = 202009"

( データマイグレーション中だけでも ) Date 型のフィールドに対して、 202009 のような形式でもクエリしたくなります。 他では、ここでの target_month をパラメータに渡したときの挙動が違う ( もちろん Integer/Date で to_param の挙動が異なるので ) という事象もあったりしました。 そこで Rails の attribute API を使って target_month 列に対して新たに TargetMonth 型をマッピングすることにしました。

ActiveRecord::Attributes::ClassMethods

# app/types/target_month.rb

require 'delegate'

class TargetMonth < DelegateClass(Date)
  # TODO: Integer 型からの移行のために残しているが最終的には消す方針が良いかも
  def to_s(format = nil)
    if format.blank?
      strftime('%Y%m')
    else
      super(format)
    end
  end

  def to_param
    to_s
  end
end

# app/types/target_month_type.rb

class TargetMonthType < ActiveRecord::Type::Date
  def type
    :target_month
  end

  # ActiveRecord のクエリ条件の渡し方を柔軟にします。
  # 後方互換を維持するためであり、
  # 最終的には Integer/String でのクエリの仕方は消すのがハイコンテクストでなく良いです。
  #
  #   # 過去、 target_month が数値型であったときの名残り
  #   > ProjectMonthlyTerm.where(target_month: 201901).to_sql
  #   => "SELECT `project_monthly_terms`.* FROM `project_monthly_terms` WHERE `project_monthly_terms`.`target_month` = '2019-12-01'"
  #   > ProjectMonthlyTerm.where(target_month: '201807').to_sql
  #   => "SELECT `project_monthly_terms`.* FROM `project_monthly_terms` WHERE `project_monthly_terms`.`target_month` = '2018-07-01'"
  #
  #   # 通常の Date 型によるクエリ
  #   > ProjectMonthlyTerm.where(target_month: Date.new(2020, 2, 1).to_sql
  #   => "SELECT `project_monthly_terms`.* FROM `project_monthly_terms` WHERE `project_monthly_terms`.`target_month` = '2020-02-01'"
  #
  #   # 日は 1日に丸めます
  #   > ProjectMonthlyTerm.where(target_month: Date.new(2020, 2, 15).to_sql
  #   => "SELECT `project_monthly_terms`.* FROM `project_monthly_terms` WHERE `project_monthly_terms`.`target_month` = '2020-02-01'"
  #
  #   # TargetMonth 型によるクエリ
  #   > ProjectMonthlyTerm.where(target_month: TargetMonth.new(Date.new(2020, 3, 1)).to_sql
  #   => "SELECT `project_monthly_terms`.* FROM `project_monthly_terms` WHERE `project_monthly_terms`.`target_month` = '2020-03-01'"
  #
  def serialize(value)
    case value
    when TargetMonth then value.to_date.change(day: 1)
    when Date then value.change(day: 1)
    when Integer, String # TODO: 後方互換のためのフォローであり最終的には消すのが望ましい
      # YYYYMM 形式からのキャストを可能にします
      if value.to_s =~ /\A([[:digit:]]{4})([[:digit:]]{2})\z/
        begin
          Date.new(Regexp.last_match(1).to_i, Regexp.last_match(2).to_i)
        rescue StandardError
          nil
        end
      end
    end
  end

  private

  # TargetMonth 型フィールドへの代入方法を柔軟にします。
  # serialize と同様に後方互換を維持するためであり、
  # 最終的には Integer/String でのクエリの仕方は消すのがハイコンテクストでなく良いです。
  #
  #   term = ProjectMonthlyTerm.first
  #   term.target_month  #=> the TargetMonth instance.
  #
  #   # Date 型による代入。日は 1日に丸められ、 2019年12月 を意味します。
  #   term.target_month = Date.new(2019, 12, 31)
  #
  #   # TargetMonth 型による代入。日は 1日に丸められ、 2019年12月 を意味します。
  #   term.target_month = TargetMonth.new(Date.new(2019, 12, 31))
  #
  #   # YYYYMM 書式の数値あるいは文字列による代入。後方互換のためにあります。
  #   term.target_month = 201908    #=> 2019年08月
  #   term.target_month = '201912'  #=> 2019年12月
  #
  #   # YYYYMM 書式の代入の場合、正しくない書式を代入すると nil として扱います。
  #   term.target_month = 1908      #=> nil
  #   term.target_month = 'hoge'    #=> nil
  #
  def cast_value(value)
    case value
    when TargetMonth then value.dup
    when Integer, String # TODO: 後方互換のためのフォローであり最終的には消すのが望ましい
      # YYYYMM 形式からのキャストを可能にします
      if value.to_s =~ /\A([[:digit:]]{4})([[:digit:]]{2})\z/
        d = begin
              Date.new(Regexp.last_match(1).to_i, Regexp.last_match(2).to_i)
            rescue StandardError
              nil
            end
        TargetMonth.new(d) if d
      end
    else super.then { |ret| TargetMonth.new(ret.change(day: 1)) if ret }
    end
  end
end

# config/initializers/active_record.rb

ActiveSupport.on_load(:active_record) do
  [ActiveModel::Type, ActiveRecord::Type].each do |type_class|
    type_class.register(:target_month, ::TargetMonthType)
  end
end

# app/models/project_monthly_term.rb

class ProjectMonthlyTerm < ApplicationRecord
  attribute :target_month, :target_month  # target_month 列を TargetMonth にマッピング

上記により、 ProjectMonthlyTerm#target_month に対し

• 202009 などの形式で代入できる
• ProjectMonthlyTerm.where(target_month: 202009) といったクエリも意図通りに動く

ということが実現できました。

# 全部同じクエリに

ProjectMonthlyTerm.where(target_month: 202009).count
=> "SELECT `project_monthly_terms`.* FROM `project_monthly_terms` WHERE `project_monthly_terms`.`target_month` = '2020-09-01'"

ProjectMonthlyTerm.where(target_month: Date.new(2020, 9, 1)).count
=> "SELECT `project_monthly_terms`.* FROM `project_monthly_terms` WHERE `project_monthly_terms`.`target_month` = '2020-09-01'"

ProjectMonthlyTerm.where(target_month: Date.new(2020, 9, 15)).to_sql
=> "SELECT `project_monthly_terms`.* FROM `project_monthly_terms` WHERE `project_monthly_terms`.`target_month` = '2020-09-01'"

# YYYYMM で代入
term = ProjectMonthlyTerm.new
term.target_month = 202009

# 日付(TargetMonth) 扱いになっている
term.target_month  #=> Tue, 01 Sep 2020
term.target_month.to_s  #=> "202009"
term.target_month.to_param  #=> "202009"

まとめ

クラウドテックのデータベース構造について、改善をしていった話でした。 たとえば別々の値を取りうることを想定していたが、時間が経ちドメインへの理解が進むことで冗長（一事実複数箇所）であることがわかる、ということはあると思います。そういったときにドメインの実状に近づける改善は非常に意義があると思います。 また、少々ニッチですが、今回の作業において Rails 5 からの attribute API が型の違いを吸収するのに力になってくれてよかったです。