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

※ こちらは[ミツモアAdvent Calendar 2022](https://qiita.com/advent-calendar/2022/meetsmore)の15日目の記事です。

ミツモアでは、[MeetsOne](https://meetsone.com/)というフィールドサービス向けの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のドキュメント](https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#json)をご確認ください。

```
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 | メールアドレス | xxxx-xxxx@meetsmore.com |
| 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](https://wpdocs.osdn.jp/%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E6%A7%8B%E9%80%A0#.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://docs.evolveum.com/midpoint/projects/midscale/design/repo/repository-json-vs-eav/)

# 最後に

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

- 採用ページ: [https://meetsmore.com/company/recruit](https://meetsmore.com/company/recruit)
- 募集要項: [https://herp.careers/v1/meetsmore/3cQ_H41s6tZu](https://herp.careers/v1/meetsmore/3cQ_H41s6tZu)