PostgeSQL+PrismaでJSON型を使用したカスタムフィールドの実装

※ こちらはミツモアAdvent Calendar 2022の15日目の記事です。

ミツモアでは、MeetsOneというフィールドサービス向けのSaaSを提供しています。
フィールドサービスというのは、ハウスクリーニングや引越しなど、実際に現場を訪問し作業を行うお仕事です。
SaaSの特性上、全ての利用者の要望を満たした機能を提供することは難しく、業界に特化したMeetsOneでもそれは同様です。ただ、MeetsOneではデータベースに保存する値をある程度カスタマイズすることが可能です。
本日は、MeetsOneの提供する機能「カスタムフィールド」をTODOリストを題材に紹介します。

※タイトルにPostgreSQLと書きましたが、MySQLなどのRDBでもJsonをサポートしているので、Prismaがサポートしていれば同じ実装で動くはずです。

カスタムフィールドの仕組み

TODOリストの最低限必要な項目としては、作業する内容（文字列）を保存する項目だけがあればよさそうですが、利用者によっては、「優先度」が欲しい、「締め切り」が欲しい、などの要望もありそうです。
ただ、このような要望を全てテーブルに追加していくとメンテナンス面にも影響が出るのに加えて、利用する側からしても自分にとって不要な項目が表示、または入力する必要が出てUX上でも問題があります。

model Todo {
  id       String   @id @default(cuid())
  title    String
  priority Int      // ← 要望で追加
  deadline DateTime // ← 要望で追加
  // たくさん続く↓
  // ...
  // ...
  // ...
  // ...
}

そこで本当に必要な項目のみを残して、そのほかは利用者ごとに設定してもらう customFields を追加します。
※ここでは簡略化のため、id, title のみ残していますが、実際にはもっと増えるはずです。

型は Json です。PostgreSQL 9.2 からサポートされています。もちろんPrismaからも利用することができます。PostgreSQLの場合、内部的には jsonb が使用されます。その他のデータベースについては、Prismaのドキュメントをご確認ください。

model Todo {
  id           String   @id @default(cuid())
  title        String
  customFields Json // ← 追加
}

これでそれぞれ必要なデータを保存することはできるようになりました。JSONで保存できるので、
「優先度」が必要であれば、{ "priority": 10 }
「締め切り」が必要であれば、{ "deadline": "2022-12-15T19:00:00.000Z" }
のようにそれぞれできそうです。

ただまだ問題があります。
実際に利用者にブラウザ（もしくはアプリ）で入力してもらうことでそれぞれ保存されますが、優先度は 数値、締め切りは 日付、のようなメタ情報がないため、適切なUIを表示することも、バリデーションをかけることもできません。
この項目のメタ情報を事前に保存しておくテーブルとして CustomField を作成します。

enum CustomFieldType {
  Number
  Date
}

model CustomField {
  id          String   @id @default(cuid())
  type        CustomFieldType
  name        String
  displayName String

  @@unique([name])
  @@unique([displayName])
}

データのイメージは以下のようになります。

id	type	name	displayName
1	Number	priority	優先度
2	Date	deadline	締め切り

このカスタムフィールドの情報と、実際のデータを組み合わせることで画面への表示と、バリデーションを行うことができるようになりました。

MeetsOneの実装

基本的な仕組みは上記の通りですが、ここからはMeetsOneで現時点（2022/12/15）で実装されている機能について簡単に紹介していきます。
CustomFieldType として、NumberやDateを先ほど紹介しましたが、その他に以下のようなものがあります。

CustomFieldTypeの種類

CustomFieldType	意味	表示例
Text	テキスト	シンプルなテキストデータです。
DateTime	日付/時間	2022/12/15（木）17:00
Time	時間	15:00
Tel	電話番号	03-xxxx-xxxx
Email	メールアドレス	[e-mail]
Checkbox	真偽値	✅
Select	セレクトボックス	大（大中小のうち大を選択）
MultiSelect	セレクトボックス（複数選択）	大,中（大中小のうち大中を選択）

Select, MultiSelect はこの記事では紹介していませんが、CustomFieldOption テーブルを作成して選択肢を保存しています。
今後も新しいタイプがいくつか追加されていく予定です。

その他の入力制限

Number入力時に入力できる範囲を指定したい、項目を必須入力にしたいなども考えられます。
これらについては、検討の結果、必要なものはあとからあまり増えないだろうということで、CustomField に直接定義しています。Dateなどは、min, maxが関係ないため null になります。

model CustomField {
  // ...
  isRequired Boolean
  min        Int?
  max        Int?
  isArchive  Boolean @default(false)
}

各項目のグルーピング

カスタムフィールドの項目数が多くなってきたときに、項目をグルーピングする機能もあります。
仕組みは単純で、CustomField に親テーブルを持たせます。
CustomField と一緒に親テーブルをSelectしておくことで、UI上でグルーピングされた表示ができるようになります。

// MeetsOneではグルーピングされたカスタムフィールドをセクションと呼びます
model CustomFieldSection {
  id   String   @id @default(cuid())
  name String
}

model CustomField {
  // ...
  customFieldSectionId String?
  customFieldSection   CustomFieldSection? @relation(fields: [customFieldSectionId], references: [id])
  // ...
}

データ削除

CustomField のデータを削除してしまうと、Jsonが持つデータのメタ情報が失われてしまうため、Jsonのデータが残っていたとしても表示できなくなってしまいます。
そのため、削除する際には注意が必要で、MeetsOneでは削除対象の名前を入力するワンクッションを挟んでいます。

実際のJsonから対象のデータを削除するタイミングはサービスの性質によって検討が必要です。
データが数万件のように大きくなる可能性がある場合、CustomField の削除と同時にJsonのデータを削除しにいくと、長い時間テーブルにロックがかかってしまうこともありえます。
CustomField は一旦論理削除しておき、夜間バッチなどで物理削除を行うなどの対応も必要になります。

EAVを使用したカスタムフィールド

今回ご紹介した方法はJSONを使用した方法でしたが、別案としてEAV（Entity Attribute Value）を使用する方法があります。EAVを使用した代表的なプロダクトとしてWordPressが挙げられ、同プロダクトの wp_postmeta テーブルは以下のような構成になっています。

フィールド	種別	Null	キー	初期値	備考
meta_id	bigint(20) unsigned		PRI		auto_increment
post_id	bigint(20) unsigned		IND	0
meta_key	varchar(255)	YES	IND	NULL
meta_value	longtext	YES		NULL

参考：https://wpdocs.osdn.jp/データベース構造#.E3.83.86.E3.83.BC.E3.83.96.E3.83.AB:_wp_postmeta

JSON vs EAV

パフォーマンスについては独自に計測したわけではありませんが、以下ドキュメントによると大体の項目において同じくらいかもしくはJSONの方がパフォーマンスが良いようです。

EAVについてはSQLアンチパターンの１つともされているので、導入する場合には注意が必要です。

参考：https://docs.evolveum.com/midpoint/projects/midscale/design/repo/repository-json-vs-eav/

最後に

ミツモアでは様々な職種のエンジニアを積極的に採用しています！ ご興味がある方はぜひ気軽に面談しましょう！

• 採用ページ: https://meetsmore.com/company/recruit
• 募集要項: https://herp.careers/v1/meetsmore/3cQ_H41s6tZu