database-schema-designer

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

Tier: 強力
Category: エンジニアリング
Domain: データアーキテクチャ／バックエンド

---

概要

要件からリレーショナルデータベースのスキーマを設計し、マイグレーション、TypeScript／Python の型、シードデータ、RLS ポリシー、インデックスを生成します。マルチテナント、論理削除、監査証跡、バージョニング、多態的な関連付けに対応します。

主な機能

• スキーマ設計 — 要件をテーブル、リレーションシップ、制約に正規化
• マイグレーション生成 — Drizzle、Prisma、TypeORM、Alembic
• 型生成 — TypeScript インターフェース、Python のデータクラス／Pydantic モデル
• RLS ポリシー — マルチテナントアプリ向けの行レベルセキュリティ
• インデックス戦略 — 複合インデックス、部分インデックス、カバリングインデックス
• シードデータ — 現実的なテストデータを生成
• ERD 生成 — スキーマから Mermaid 図を生成

---

使用する場面

• データベーステーブルが必要な新機能を設計する
• パフォーマンスや正規化の問題についてスキーマをレビューする
• 既存のスキーマにマルチテナント対応を追加する
• Prisma スキーマから TypeScript の型を生成する
• 破壊的変更を伴うスキーママイグレーションを計画する

---

スキーマ設計プロセス

ステップ 1：要件 → エンティティ

次の要件があるとします。

「ユーザーはプロジェクトを作成できます。各プロジェクトにはタスクがあります。タスクにはラベルを付けられます。タスクはユーザーに割り当てられます。完全な監査証跡が必要です。」

エンティティを抽出します。

User, Project, Task, Label, TaskLabel (junction), TaskAssignment, AuditLog

ステップ 2：リレーションシップを特定する

User 1──* Project         (owner)
Project 1──* Task
Task *──* Label            (via TaskLabel)
Task *──* User            (via TaskAssignment)
User 1──* AuditLog

ステップ 3：横断的な関心事を追加する

• マルチテナント：テナントにスコープされたすべてのテーブルに organization_id を追加する
• 論理削除：物理削除の代わりに deleted_at TIMESTAMPTZ を追加する
• 監査証跡：created_by、updated_by、created_at、updated_at を追加する
• バージョニング：楽観的ロック用に version INTEGER を追加する

---

完全なスキーマの例（タスク管理 SaaS）

→ 詳細は references/full-schema-examples.md を参照してください

行レベルセキュリティ（RLS）ポリシー

-- Enable RLS
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Create app role
CREATE ROLE app_user;

-- Users can only see tasks in their organization's projects
CREATE POLICY tasks_org_isolation ON tasks
  FOR ALL TO app_user
  USING (
    project_id IN (
      SELECT p.id FROM projects p
      JOIN organization_members om ON om.organization_id = p.organization_id
      WHERE om.user_id = current_setting('app.current_user_id')::text
    )
  );

-- Soft delete: never show deleted records
CREATE POLICY tasks_no_deleted ON tasks
  FOR SELECT TO app_user
  USING (deleted_at IS NULL);

-- Only task creator or admin can delete
CREATE POLICY tasks_delete_policy ON tasks
  FOR DELETE TO app_user
  USING (
    created_by_id = current_setting('app.current_user_id')::text
    OR EXISTS (
      SELECT 1 FROM organization_members om
      JOIN projects p ON p.organization_id = om.organization_id
      WHERE p.id = tasks.project_id
        AND om.user_id = current_setting('app.current_user_id')::text
        AND om.role IN ('owner', 'admin')
    )
  );

-- Set user context (call at start of each request)
SELECT set_config('app.current_user_id', $1, true);

---

シードデータの生成

// db/seed.ts
import { faker } from '@faker-js/faker'
import { db } from './client'
import { organizations, users, projects, tasks } from './schema'
import { createId } from '@paralleldrive/cuid2'
import { hashPassword } from '../src/lib/auth'

async function seed() {
  console.log('Seeding database...')

  // Create org
  const [org] = await db.insert(organizations).values({
    id: createId(),
    name: "acme-corp",
    slug: 'acme',
    plan: 'growth',
  }).returning()

  // Create users
  const adminUser = await db.insert(users).values({
    id: createId(),
    email: '[email protected]',
    name: "alice-admin",
    passwordHash: await hashPassword('password123'),
  }).returning().then(r => r[0])

  // Create projects
  const projectsData = Array.from({ length: 3 }, () => ({
    id: createId(),
    organizationId: org.id,
    ownerId: adminUser.id,
    name: "fakercompanycatchphrase"
    description: faker.lorem.paragraph(),
    status: 'active' as const,
  }))

  const createdProjects = await db.insert(projects).values(projectsData).returning()

  // Create tasks for each project
  for (const project of createdProjects) {
    const tasksData = Array.from({ length: faker.number.int({ min: 5, max: 20 }) }, (_, i) => ({
      id: createId(),
      projectId: project.id,
      title: faker.hacker.phrase(),
      description: faker.lorem.sentences(2),
      status: faker.helpers.arrayElement(['todo', 'in_progress', 'done'] as const),
      priority: faker.helpers.arrayElement(['low', 'medium', 'high'] as const),
      position: i * 1000,
      createdById: adminUser.id,
      updatedById: adminUser.id,
    }))

    await db.insert(tasks).values(tasksData)
  }

  console.log(`✅ Seeded: 1 org, ${projectsData.length} projects, tasks`)
}

seed().catch(console.error).finally(() => process.exit(0))

---

ERD の生成（Mermaid）

erDiagram
    Organization ||--o{ OrganizationMember : has
    Organization ||--o{ Project : owns
    User ||--o{ OrganizationMember : joins
    User ||--o{ Task : "created by"
    Project ||--o{ Task : contains
    Task ||--o{ TaskAssignment : has
    Task ||--o{ TaskLabel : has
    Task ||--o{ Comment : has
    Task ||--o{ Attachment : has
    Label ||--o{ TaskLabel : "applied to"
    User ||--o{ TaskAssignment : assigned

    Organization {
        string id PK
        string name
        string slug
        string plan
    }

    Task {
        string id PK
        string project_id FK
        string title
        string status
        string priority
        timestamp due_date
        timestamp deleted_at
        int version
    }

Prisma から生成する場合：

npx prisma-erd-generator
# or: npx @dbml/cli prisma2dbml -i schema.prisma | npx dbml-to-mermaid

---

よくある落とし穴

• インデックスなしの論理削除 — WHERE deleted_at IS NULL にインデックスがないと、フルスキャンになります
• 複合インデックスの不足 — WHERE org_id = ? AND status = ? には複合インデックスが必要です
• 変更可能なサロゲートキー — メールアドレスやスラッグを PK にせず、UUID／CUID を使用してください
• デフォルト値のない NOT NULL — 既存テーブルに NOT NULL 列を追加するには、デフォルト値またはマイグレーション計画が必要です
• 楽観的ロックがない — 同時更新によって互いの変更が上書きされます。version 列を追加してください
• RLS がテストされていない — 必ずスーパーユーザー以外のロールで RLS をテストしてください

---

ベストプラクティス

1. すべてのテーブルにタイムスタンプを設定する — すべてのテーブルに created_at、updated_at を設定します
2. 監査対象データには論理削除を使う — DELETE の代わりに deleted_at を使用します
3. コンプライアンス対応には監査ログを使う — 規制対象のドメインでは、変更前後の JSON を記録します
4. PK には UUID または CUID を使う — 連番整数からの情報漏えいを避けます
5. 外部キーにインデックスを設定する — すべての FK 列にインデックスを設定します
6. 部分インデックスを使う — 有効なレコードのみを対象にするクエリには WHERE deleted_at IS NULL を使用します
7. アプリケーションレベルのフィルタリングより RLS を優先する — テナント分離はアプリのコードだけでなく、データベースで強制します