CHAPTER 01

DB設計の基礎 — bunshin.aiのschemaで学ぶ

ステータス: 🟢 現役 最終更新: 2026-06-23 作成者: ジョブズ

このファイルでできること

フロントエンドが主戦場だった人が、DB設計の基礎を「目の前の実物(bunshin.ai の schema.md)」で理解できる。テーブル / 行 / 列 / 型 / PK / FK / index / 正規化 / RLS を、フロント脳の言葉に翻訳して一から積む。

使うタイミング:

  • bunshin.ai の schema.md を読んで「リレーションとは」「indexとは」で詰まった時
  • 新しいテーブルを追加する・カラムを足すか迷った時
  • 「これ配列で持つべき? 別テーブル?」で手が止まった時

このファイルと一緒に使うもの:

  • schema.md(bunshin.ai の実スキーマ・答えの方) — 本ファイルは「なぜそう設計したか」の原理
  • README.md — Supabase全体の基礎テンプレ

0. 大前提:DBはExcelの超厳格版

まず一番大事なイメージから。

DBのテーブル = Excelのシート1枚 です。

| Excel | DB | bunshin.aiの例 | |---|---|---| | シート | テーブル(table) | customers(顧客マスタ) | | 行(横1列) | 行 / レコード(row) | 顧客1社分のデータ | | 列(縦1列) | カラム(column) | company_name, status | | セルの値 | フィールドの値 | "株式会社○○" |

違いは「厳格さ」だけ。Excelは1つのセルに何でも入れられますが、DBは:

  • 列ごとに型が固定(この列は数値だけ、この列は日付だけ)
  • 空を許すか・必須かが決まっている(NOT NULL)
  • 重複を許さない列を指定できる(UNIQUE)
  • 「この列はあのシートのこの行を指す」という線を引ける(リレーション)

Excelで手作業でやっていた「この表の社員IDは、社員マスタにある番号しか使わないでね」というルールを、DBが強制してくれる。これがDBの本質です。

1. フロント脳との対応表(最重要)

あなたはJavaScript/TypeScriptのデータ構造に慣れているはず。それをそのまま橋にします。

`ts // フロントでよく書くデータ const customers = [ { id: "uuid-1", companyName: "A社", status: "active" }, { id: "uuid-2", companyName: "B社", status: "suspended" }, ]; `

これがそのままDBのテーブルです。

| フロント(JS/TS) | DB | 補足 | |---|---|---| | const customers = [...](配列) | テーブル customers | 配列全体 = テーブル | | 配列の要素 {...}(オブジェクト1個) | 行(row) | オブジェクト1個 = 1行 | | オブジェクトのキー companyName | カラム名 | ただしDBは慣習で company_name(スネークケース) | | 値の型 string number | カラムの型 TEXT INTEGER | DBは型がもっと細かい(後述) | | customer.id(一意のキー) | 主キー(PK) | 「この行はこれ」と特定する印 | | order.customerId(別データを指すID) | 外部キー(FK) | リレーションの線 | | Array.find(c => c.id === x) を速くする | index(索引) | 検索を速くする裏方 |

ここだけ持って帰れば十分: テーブル=配列、行=オブジェクト、カラム=キー。あとは「厳格なルールが付いた版」と思えばいい。

実際 schema.md を見ると、conversation_logs の物理カラム question/answer が、UIでは message/response という論理名で扱われています。これは DBの列名(スネークケース)とフロントの変数名(キャメルケース)を変換しているだけ。あなたが普段やっている data.company_name → companyName のマッピングと同じことです。

2. 型(type)— 列に入れていいものを縛る

フロントの string | number のDB版。schema.md の customers テーブルで実際に使われている型を見ます。

| schema.mdの型 | 意味 | フロントで言うと | 例 | |---|---|---|---| | TEXT | 文字列(長さ無制限) | string | company_name | | INTEGER / INT | 整数 | number(整数) | current_persona_version | | NUMERIC | 小数(誤差なし・金額向き) | number | cost_yen | | BOOLEAN | true/false | boolean | is_active | | DATE | 日付だけ | — | contract_start_date | | TIMESTAMPTZ | 日時+タイムゾーン | Date | created_at | | UUID | 一意なランダムID | string(uuid) | id | | JSONB | JSON丸ごと | object / any | citations, auto_judge_breakdown | | INET | IPアドレス専用 | string | ip_address | | TEXT[] | 文字列の配列 | string[] | tags, allowed_customer_slugs |

★ Insight ─────────────────────────────────────

  • NUMERIC を金額に使う理由: JSの number(浮動小数点)は 0.1 + 0.2 = 0.30000000000000004 になる。お金で誤差は致命的なので、DBは誤差ゼロの NUMERIC を別に用意している。schema.md の cost_yen がこれ
  • JSONB は「DBの中のJSON」: citations のように構造が変わりうるデータは、無理に列に割らずJSONで持てる。フロントで state にネストしたオブジェクトを置くのと同じ感覚。ただし多用は禁物(理由は §6 正規化で)
  • TIMESTAMPTZ のTZはタイムゾーン: 顧客が世界中にいても時刻がズレない。created_at にこれを使うのは定石

─────────────────────────────────────────────────`

3. 制約(constraint)— Excelで手作業だったチェックをDBに任せる

customers テーブルの定義から、制約の意味を1つずつ。

` id UUID PK, default gen_random_uuid() customer_code TEXT UNIQUE NOT NULL status TEXT default 'active' CHECK IN (active/suspended/terminated) `

| 制約 | 意味 | フロントで例えると | |---|---|---| | NOT NULL | 空を禁止(必須) | フォームの required | | UNIQUE | 重複禁止 | メアド重複登録を弾く | | default X | 未指定なら自動でXが入る | status = props.status ?? 'active' | | CHECK IN (...) | 決めた値しか入れない | TypeScriptの type Status = 'active' \| 'suspended' | | PK(主キー) | 一意+必須+検索の起点(§4) | key propの厳格版 |

つまり customer_code TEXT UNIQUE NOT NULL は「文字列で・必須で・重複しない顧客コード」をDBレベルで保証する。フロントのバリデーションをすり抜けてもDBが最後の砦になる、という二重の安全網です。

schema.md で一番面白い制約はこれ:

` admin_users_no_wildcard_slug CHECK (NOT ('*' = ANY(allowed_customer_slugs))) `

「allowed_customer_slugs(アクセス許可顧客リスト)に '*'(=全顧客)を入れられない」というルールを構造で焼き込んでいる。これは「ベンダーが全顧客を覗ける行を、そもそも作れなくする」というセキュリティ設計(ADR-029方針7)。ルールをコードでなくDBスキーマに刻むと、誰がどう操作しても破れない。これがDB制約の威力です。

4. 主キー(PK)— 「この行はこれ」と一意に特定する印

フロントで配列をrenderする時の key={item.id} を思い出してください。あの id がDBの主キー(Primary Key)です。

主キーは1つの行を世界で唯一に特定する列。schema.md ではほぼ全テーブルが:

` id UUID PK, default gen_random_uuid() `

  • UUID = "3c8598e2-718a-..." のようなランダムな一意文字列
  • gen_random_uuid() = 行を作るたびDBが自動採番(フロントで crypto.randomUUID() するのと同じ)
  • PK = この列が主キー

なぜ連番(1, 2, 3...)でなくUUIDか:

  • 連番だと「今2000件あるな」と件数が外から推測できる(情報漏れ)
  • 複数のDBを後で統合する時、連番だと衝突する(UUIDなら絶対衝突しない)

例外的に、admin_users テーブルは email が主キーです:

` email TEXT PK `

「メアドは1人1つで重複しない」から、メアド自体を「この行はこれ」の印に使える。主キーは必ずしもidという名前でなくていい、という良い例です。

5. 外部キー(FK)とリレーション — テーブル同士を線でつなぐ(★最重要)

ここがDB設計の心臓です。フロント脳で一番つまずく所なので丁寧にいきます。

5.1 なぜ分けるのか

フロントだとこう書きたくなります:

`ts // 会話ログ1件に、顧客情報を丸ごと埋め込む const log = { id: "log-1", question: "...", customer: { id: "c1", companyName: "A社", status: "active" }, // ← 埋め込み }; `

DBではこれをやりません。なぜか:

  • A社の会話ログが1万件あったら、companyName: "A社" が1万回コピーされる(容量の無駄)
  • A社が社名変更したら、1万件全部を書き換える羽目になる(=更新漏れ・不整合の温床)

代わりに、「指す」だけにします:

`ts const log = { id: "log-1", question: "...", customerId: "c1", // ← A社を「指す」だけ。実体は customers テーブルに1つ }; `

社名は customers テーブルに1箇所だけ置く。会話ログは customerId で参照する。社名変更は1箇所直せば全部に反映される。これが正規化(§6)の出発点です。

5.2 FKの実物

schema.md の conversation_logs を見ると:

` customer_id UUID FK customers(id) `

これが外部キー(Foreign Key)。読み方は「この customer_id は、customers テーブルの id を指す」。

DBはこのFKがあると、こう守ってくれます:

  • 存在しない顧客を指す会話ログは作れない(customers にない customer_id はエラー)
  • これが「参照整合性(referential integrity)」=あなたが確認したかった「整合性」の正体

5.3 リレーションの3種類

| 種類 | 意味 | bunshin.aiの例 | |---|---|---| | 1対多 (1:N) | 1顧客 → 多数の会話ログ | customers 1件 ⇄ conversation_logs N件 | | 多対多 (N:M) | 中間テーブルで橋渡し | (今のschemaには明示的には無い) | | 1対1 (1:1) | 補助情報を別テーブルに | customers ⇄ Phase2の staff_info |

圧倒的に多いのが 1対多。「親1つに子が複数ぶら下がる」形です。schema.md のFKはほぼ全部これ:

` conversation_logs.customer_id → customers.id (顧客1 : 会話ログ多) masking_dict.customer_id → customers.id (顧客1 : 辞書多) knowledge_meta.customer_id → customers.id (顧客1 : ナレッジ多) team_join_requests.invitation_id → team_invitations.id (招待1 : 申請多) `

5.4 ON DELETE — 親が消えたら子をどうする

schema.md にこんな指定があります:

` problem_knowledge_id UUID FK knowledge_meta(id) ON DELETE SET NULL `

「会話ログが指しているナレッジ(原因ナレッジ)が削除されたら、problem_knowledge_id を NULL にする」。ナレッジを消したからといって会話ログまで道連れに消すのは乱暴なので、「指し先を空にするだけ」にしている。ON DELETE には主にこの選択肢:

| 指定 | 親削除時の挙動 | 使いどころ | |---|---|---| | CASCADE | 子も一緒に消す | 顧客を消したら会話ログも全消し | | SET NULL | 子の参照を空にする | 上の例(参照は切るが子は残す) | | RESTRICT(既定) | 子がいる限り親を消せない | 誤削除防止 |

6. 正規化と「あえて崩す」判断 — 配列 vs 別テーブル

あなたが詰まりがちな「これ配列で持つ?別テーブル?」に答えます。

6.1 原則:繰り返すものは別テーブル(正規化)

§5.1でやった「社名を1箇所に置く」が正規化。原則は 「同じ情報を2箇所に書かない」。

6.2 でもschema.mdは配列やJSONも使っている

` tags TEXT[] -- 文字列の配列 citations JSONB -- JSON丸ごと allowed_customer_slugs TEXT[] `

正規化の原則からすると tags は別テーブルにすべき…ですが、あえて崩している。判断基準はこれ:

| 配列/JSONでOK | 別テーブルにすべき | |---|---| | その値だけを単独で検索しない | その値で頻繁に検索・集計する | | 他テーブルから参照されない | 他から参照される(FKを張りたい) | | 構造が変わりうる(citations) | 構造が固まっている | | 量が小さい(タグ数個) | 量が多い・無限に増える |

tags(タグ数個・このナレッジ固有・他から参照されない)→ 配列でOK。conversation_logs(顧客ごとに無限に増える・検索する・FKで参照)→ 別テーブルが正解。

★ Insight ─────────────────────────────────────

  • 正規化は「教科書の理想」、現実は「適度に崩す」: 全部きっちり正規化すると、1画面出すのに10テーブルJOINになって遅い・書くのも辛い。「検索する? 参照される? 増える?」の3問で判断するのが実務
  • schema.md の judgment_class が良い例: 会話ログのカラムとして直接持っている(別テーブルにせず)。light/medium/heavy のような固定の分類値は、別テーブルにするほどでもないので列で持つ

─────────────────────────────────────────────────`

7. index(索引)— 検索を速くする裏方

フロントで array.find(x => x.id === target) は、配列が10万件あると全部なめて遅いですよね。DBも同じで、何もしないと全行スキャンします。

index = 本の巻末の索引。「この列で検索するなら、あらかじめ並べ替えた一覧を裏で持っておく」仕組み。

schema.md の例:

` idx_conversation_logs_customer_created (customer_id, created_at DESC) `

「customer_id で絞って created_at の新しい順に並べる」検索を速くするindex。これはまさに /conversations 画面の「特定顧客の会話を新しい順に表示」に対応している。よく使う検索パターンに合わせてindexを張るのが定石です。

特に賢いのがこれ(部分index):

` idx_conversation_logs_unreviewed_low_score (auto_judge_score, created_at DESC) WHERE review_status='unreviewed' AND auto_judge_score<70 `

WHERE 付き = 「未レビューかつ低スコアの行だけ」を索引する。レビュー画面の「自信なしタブ」専用の高速化。全部を索引せず必要な部分だけ作るので、軽くて速い。

注意:indexはタダではない。検索は速くなるが、書き込み(INSERT/UPDATE)は少し遅くなる(索引も更新するから)。だから「全列にindex」ではなく「よく検索する列だけ」が原則です。

8. RLS(行レベルセキュリティ)— Supabase最重要

普通のDBは「テーブル単位」でアクセス制御しますが、SupabaseのRLS = 行単位で「誰がどの行を見れるか」を縛れる。

schema.md の方針:

` RLS は全8テーブルで有効・ポリシーゼロ = service_role以外全拒否 `

  • RLS有効 + ポリシーなし = 全部拒否(鍵をかけて鍵穴も塞いだ状態)
  • service_role(サーバ側の特権キー)だけが通れる
  • つまり「ブラウザから直接DBは触れない。必ずサーバ(Next.js API)を経由する」設計

bunshin.aiは「1顧客=1Supabaseプロジェクト」で物理分離しているので、RLSは「念のための二重ロック」。append-only(追記専用)の監査ログでは、RLSで UPDATE/DELETE のポリシーを作らないことで「書けるけど消せない・直せない」を実現しています(team_audit_logs, owner_access_logs)。

★ Insight ─────────────────────────────────────

  • 「ポリシーゼロ=全拒否」は直感に反するが安全側: 設定し忘れたテーブルが「誰でも読める」より「誰も読めない」方が事故にならない。デフォルト拒否(deny by default)はセキュリティの黄金則
  • append-onlyの実現方法が秀逸: INSERTは許す・UPDATE/DELETEのポリシーは作らない → 物理的に改ざん不能。「消せない仕組み」をアプリのコードでなくDBの設定で担保している(コードはバグるがDB制約は破れない)

─────────────────────────────────────────────────`

9. まとめ:新しいテーブルを設計する時の手順

bunshin.aiに新テーブルを足す時、この順で考えれば迷いません:

  • 何を1行にする?(1行=何の単位か。顧客1社? 会話1件? メンバー1人?)
  • どの列がいる?(各列の型を決める。文字列?数値?日付?JSON?)
  • 主キーは?(普通は id UUID PK default gen_random_uuid())
  • 他のテーブルを指す?(指すなら FK を張る = リレーション)
  • 繰り返す情報を埋め込んでない?(埋め込んでたら別テーブルに分けて参照に。§6の3問で判断)
  • 必須/重複禁止/値の縛りは?(NOT NULL / UNIQUE / CHECK)
  • どう検索する?(よく検索する列に index)
  • 誰が見れる?(RLS有効 + 必要なポリシーだけ)

この8問が、そのまま schema.md の各テーブル定義になっています。逆に言えば、schema.md を読む時もこの8問の答えを探しながら読めば、構造が頭に入ります。

関連ドキュメント

  • README.md — Supabase全体の基礎(RLS/Auth/Edge Functions)
  • ../claude-api/README.md — Claude API基礎(マスキング・採点に関連)
  • bunshin.ai 実スキーマ schema.md(ai-conpany-dev側) — 本ファイルで学んだ概念の「答え合わせ」に使う

改訂履歴

  • 2026-06-23: ジョブズ — 新規作成。フロント主戦場の人向けに、bunshin.aiのschema.mdを題材としてDB設計基礎(型/制約/PK/FK/リレーション/正規化/index/RLS)を初心者前提で執筆。

[TASK] 進行中: DB学習ページをlearning/に作成 直前: §0〜3(型・制約)を執筆・schema実物で解説 次: §4〜7(PK/FK/リレーション/index/正規化/RLS)を執筆