database-schema-designer

データベーススキーマ設計

データのルールをデータベースで適用し、アプリで実際に使うクエリを高速化するスキーマを設計します。

ワークフロー

1. 要件を収集します。 会話またはコードベースから情報を取得し、設計を妨げる項目についてのみ質問します。

   • エンジンとバージョン（デフォルト：PostgreSQL 18。存在する場合は docker-compose.yml、ORM の設定、または Supabase プロジェクトを確認します）
   • エンティティとその関係を平易な言葉で説明したもの
   • 最も頻繁に実行される、または最も重要なクエリ 5～10 件（フィルターと並び順を含む）
   • データ量と増加率（テーブルごとの年間行数）、読み取りと書き込みの割合
   • テナント構成（単一アプリ、組織ごとのマルチテナント）、保持期間、監査、コンプライアンス要件（PII、削除リクエスト）
   • 使用中の ORM またはマイグレーションツール（Prisma、Drizzle、Rails、Django、Alembic、Supabase migrations、プレーン SQL）

2. エンティティとリレーションシップをモデル化します。 エンティティ、属性、カーディナリティ（1:1、1:N、N:M）を列挙します。デフォルトで 3NF に正規化し、名前を挙げたクエリに必要な場合にのみ非正規化し、そのクエリを明記します。

3. references/postgres.md（MySQL、SQLite、MongoDB の場合は references/other-engines.md）を使用して、キーと型を選択します。Postgres のデフォルト：主キーには bigint generated always as identity または uuid DEFAULT uuidv7()、varchar(n) より text、すべてのタイムスタンプに timestamptz、金額には numeric を使用します。

4. ルールを制約として定義します。 値が必須の箇所には NOT NULL、自然キーには UNIQUE、範囲や許可される値には CHECK を設定し、外部キーごとに意図した ON DELETE の動作を選択します。アプリコードだけに存在するデータルールは、次のスクリプトや管理者による修正で破られてしまいます。

5. ステップ 1 のクエリからインデックスを導き出します。 チェックリストをもとに決めるのではありません。各クエリについて、それに対応するインデックスを明記します。結合またはカスケードに使用するすべての外部キー列にインデックスを設定します（Postgres は自動で設定しません）。UNIQUE または主キーですでに提供されているインデックスは追加しません。

6. references/schema-templates.md と references/output-format.md のレイアウトを使用して、納品物（DDL、ERD、インデックスの根拠、ロールバック付きマイグレーション）を作成します。ユーザーがマイグレーションツールを使用している場合は、それに合わせます。

7. 検証します。 データベース（ローカルの Postgres、Docker の postgres:18、Supabase ブランチ）を利用できる場合は、DDL を実行し、数行を挿入して、主要なクエリに EXPLAIN を実行し、計画したインデックスが使用されることを確認します。利用できない場合は、ステップ 1 の各クエリをスキーマに照らして手作業で確認します。その後、以下のリストをチェックします。

提出前のチェック項目

• ステップ 1 のすべてのクエリについて、明記されたインデックスで対応できるか、想定されるデータ量では全表スキャンで問題ない理由が説明されています。
• すべての外部キーに明示的な ON DELETE の動作と、参照先列のインデックスがあります。
• タイムゾーンなしのタイムスタンプ、金額に対する浮動小数点数、慣習による varchar(255) はありません。
• updated_at が実際に更新されます。DEFAULT now() は挿入時にしか設定されないため、トリガーを追加するか、ORM で設定します。
• 大文字小文字を区別しない一意性（メールアドレス、ユーザー名など）は、大文字小文字を区別しない方法で適用されています。
• マルチテナントのテーブルには tenant_id（または org_id）があり、一意制約と先頭のインデックス列に含まれています。また、データベースにクライアントからアクセスできる場合は行レベルセキュリティが設定されています（Supabase では常に有効にします）。
• 既存の大規模テーブルを変更するマイグレーションでは、長時間のロックを避けています（references/postgres.md のマイグレーションの安全性を参照）。
• ロールバックスクリプトは実際に変更を取り消せるものであり、破壊的なロールバック（データのある列の削除）は明記されています。

作成例

リクエスト：「マルチテナントのプロジェクトトラッカー：組織、複数の組織に所属できるユーザー、組織ごとのプロジェクト、プロジェクトごとのタスク。メイン画面には、プロジェクトの未完了タスクを期限順に表示します。」

• エンティティ：orgs、users、org_members（ユーザーと組織の多対多関係で、role を持つ）、projects（組織に対する多対一）、tasks（プロジェクトに対する多対一で、担当者は任意）。
• 主なクエリ：特定のプロジェクトの未完了タスクを期限順に取得するため、部分インデックス ON tasks (project_id, due_at) WHERE status <> 'done' を作成します。
• ルールを制約として定義：org_members に UNIQUE (org_id, user_id) を設定し、CHECK (role IN ('owner','admin','member')) を適用します。status も CHECK で制限し、組織内でプロジェクト名が一意になるよう UNIQUE (org_id, name) を設定します。
• 削除：組織を削除すると、プロジェクトとタスクも連鎖削除されます。ユーザーを削除する場合、タスクは削除せず、tasks.assignee_id を null に設定します。
• テナント分離：tasks に org_id を持たせることで、projects を経由して結合せずに行レベルセキュリティでメンバーシップを確認できます。また、複合外部キー (project_id, org_id) -> projects (id, org_id) により、両者の整合性を保ちます。

この例の DDL 全体は、references/schema-templates.md にあります。

失敗しやすいパターン

• クエリを把握する前にテーブルを設計し、「念のため」すべての列にインデックスを作成すること。インデックスを作成するたびに、すべての書き込みが遅くなります。
• 変更される可能性のある値に Postgres の ENUM 型を使うこと。値の追加は簡単ですが、削除や名前の変更は困難です。CHECK 制約やルックアップテーブルのほうが変更に対応しやすくなります。
• デフォルトであらゆる場所にソフトデリート（deleted_at）を追加すること。すべてのクエリや一意制約に影響します。復元や監査の明確な必要性がある場所で使い、部分一意インデックスと組み合わせてください。
• データベースで制約を適用できないポリモーフィック外部キー（commentable_type と commentable_id）を使うこと。代わりに、片方だけが設定されることを保証する CHECK を付けた、別々の nullable 外部キーを使うか、テーブルを分けてください。
• 構造化され、クエリで使うフィールドを jsonb に格納すること。JSON は、まばらにしか存在しない属性や、本当に変動する属性向けです。絞り込み、結合、制約に使うフィールドには列を用意してください。