# データベース設計書

## 概要

本ドキュメントは、Liam HQプロジェクトで使用されるSupabase（PostgreSQL）データベースの設計仕様書です。本システムは、データベーススキーマのレビュー・可視化・設計セッション管理を行うマルチテナント対応Webアプリケーションであり、組織ベースのデータ分離とRow Level Security（RLS）による厳格なアクセス制御を実装しています。

## テーブル一覧

| テーブル名 | 対応エンティティ | 説明 |
| --- | --- | --- |
| users | ユーザー | システム利用者の基本情報を管理 |
| organizations | 組織 | マルチテナント環境における組織単位を管理 |
| organization_members | 組織メンバー | ユーザーと組織の関連付けを管理 |
| invitations | 招待 | 組織への招待情報を管理 |
| projects | プロジェクト | 組織内のプロジェクト情報を管理 |
| github_repositories | GitHubリポジトリ | 連携するGitHubリポジトリ情報を管理 |
| project_repository_mappings | プロジェクト-リポジトリマッピング | プロジェクトとリポジトリの関連付けを管理 |
| schema_file_paths | スキーマファイルパス | プロジェクトのスキーマファイル位置を管理 |
| design_sessions | 設計セッション | スキーマ設計作業セッションを管理 |
| building_schemas | 構築中スキーマ | 設計セッション中のスキーマ状態を管理 |
| building_schema_versions | スキーマバージョン | スキーマの変更履歴を管理 |
| public_share_settings | 公開共有設定 | 設計セッションの公開共有状態を管理 |
| checkpoints | チェックポイント | LangGraphワークフローの状態保存を管理 |
| checkpoint_blobs | チェックポイントBLOB | チェックポイントのチャネル値データを管理 |
| checkpoint_writes | チェックポイント書き込み | 未コミットのチェックポイント操作を管理 |

## 各テーブル定義

### 1. users

システムを利用するユーザーの基本情報を管理するテーブルです。Supabase Authと連携し、auth.usersテーブルから自動同期されます。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| id | uuid | NOT NULL | 主キー（auth.usersのIDと同一） |
| name | text | NOT NULL | ユーザー表示名 |
| email | text | NOT NULL | メールアドレス（一意制約あり） |
| avatar_url | text | NULL | プロフィール画像URL |

**制約:**
- PRIMARY KEY: id
- UNIQUE: email

**トリガー:**
- `on_auth_user_created`: auth.usersへの新規挿入時にpublic.usersへ自動同期

---

### 2. organizations

マルチテナント環境における組織単位を管理するテーブルです。全てのデータは組織に紐づき、組織ベースでのデータ分離が行われます。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| id | uuid | NOT NULL | 主キー（自動生成） |
| name | text | NOT NULL | 組織名 |

**制約:**
- PRIMARY KEY: id

**RLS:** 組織メンバーのみがアクセス可能

---

### 3. organization_members

ユーザーと組織の関連付けを管理する中間テーブルです。RLSポリシーの基盤として機能します。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| id | uuid | NOT NULL | 主キー（自動生成） |
| user_id | uuid | NOT NULL | ユーザーID（外部キー） |
| organization_id | uuid | NOT NULL | 組織ID（外部キー） |
| joined_at | timestamp with time zone | NULL | 参加日時（デフォルト: CURRENT_TIMESTAMP） |

**制約:**
- PRIMARY KEY: id
- UNIQUE: (user_id, organization_id)
- FOREIGN KEY: user_id -> users(id) ON DELETE CASCADE
- FOREIGN KEY: organization_id -> organizations(id) ON DELETE CASCADE

**インデックス:**
- organization_member_user_id_idx (user_id)
- organization_member_organization_id_idx (organization_id)

**トリガー:**
- `prevent_delete_last_organization_member`: 組織の最後のメンバーの削除を防止

---

### 4. invitations

組織への招待情報を管理するテーブルです。トークンベースの招待システムを実装しています。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| id | uuid | NOT NULL | 主キー（自動生成） |
| email | text | NOT NULL | 招待先メールアドレス |
| invite_by_user_id | uuid | NOT NULL | 招待者ユーザーID（外部キー） |
| organization_id | uuid | NOT NULL | 招待先組織ID（外部キー） |
| invited_at | timestamp with time zone | NULL | 招待日時 |
| token | text | NOT NULL | 招待トークン |
| expired_at | timestamp with time zone | NOT NULL | 有効期限 |

**制約:**
- PRIMARY KEY: id
- FOREIGN KEY: invite_by_user_id -> users(id)
- FOREIGN KEY: organization_id -> organizations(id) ON DELETE CASCADE

**関連関数:**
- `invite_organization_member`: 招待の作成
- `accept_invitation`: 招待の承諾
- `get_invitation_data`: 招待情報の取得

---

### 5. projects

組織内のプロジェクト情報を管理するテーブルです。リポジトリやスキーマファイルと関連付けられます。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| id | uuid | NOT NULL | 主キー（自動生成） |
| name | text | NOT NULL | プロジェクト名 |
| organization_id | uuid | NOT NULL | 所属組織ID（外部キー） |
| created_at | timestamp(3) with time zone | NOT NULL | 作成日時 |
| updated_at | timestamp(3) with time zone | NOT NULL | 更新日時 |

**制約:**
- PRIMARY KEY: id
- FOREIGN KEY: organization_id -> organizations(id) ON DELETE CASCADE

**インデックス:**
- idx_project_organization_id (organization_id)

**RLS:** 組織メンバーのみがCRUD操作可能

---

### 6. github_repositories

連携するGitHubリポジトリの情報を管理するテーブルです。GitHub Appのインストール情報も含みます。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| id | uuid | NOT NULL | 主キー（自動生成） |
| name | text | NOT NULL | リポジトリ名 |
| owner | text | NOT NULL | リポジトリオーナー |
| github_installation_identifier | integer | NOT NULL | GitHub AppインストールID |
| github_repository_identifier | integer | NOT NULL | GitHubリポジトリID |
| organization_id | uuid | NOT NULL | 所属組織ID（外部キー） |
| created_at | timestamp(3) with time zone | NOT NULL | 作成日時 |
| updated_at | timestamp(3) with time zone | NOT NULL | 更新日時 |

**制約:**
- PRIMARY KEY: id
- UNIQUE: (github_repository_identifier, organization_id)
- FOREIGN KEY: organization_id -> organizations(id)

---

### 7. project_repository_mappings

プロジェクトとGitHubリポジトリの関連付けを管理する中間テーブルです。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| id | uuid | NOT NULL | 主キー（自動生成） |
| project_id | uuid | NOT NULL | プロジェクトID（外部キー） |
| repository_id | uuid | NOT NULL | リポジトリID（外部キー） |
| organization_id | uuid | NOT NULL | 組織ID（外部キー） |
| created_at | timestamp(3) with time zone | NOT NULL | 作成日時 |
| updated_at | timestamp(3) with time zone | NOT NULL | 更新日時 |

**制約:**
- PRIMARY KEY: id
- UNIQUE: (project_id, repository_id)
- FOREIGN KEY: project_id -> projects(id)
- FOREIGN KEY: repository_id -> github_repositories(id)
- FOREIGN KEY: organization_id -> organizations(id)

---

### 8. schema_file_paths

プロジェクトに関連付けられたスキーマファイルの位置情報を管理するテーブルです。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| id | uuid | NOT NULL | 主キー（自動生成） |
| path | text | NOT NULL | ファイルパス |
| format | schema_format_enum | NOT NULL | スキーマ形式 |
| project_id | uuid | NOT NULL | プロジェクトID（外部キー） |
| organization_id | uuid | NOT NULL | 組織ID（外部キー） |
| created_at | timestamp(3) with time zone | NOT NULL | 作成日時 |
| updated_at | timestamp(3) with time zone | NOT NULL | 更新日時 |

**制約:**
- PRIMARY KEY: id
- FOREIGN KEY: project_id -> projects(id)
- FOREIGN KEY: organization_id -> organizations(id)

**ENUM（schema_format_enum）:**
- `schemarb`: Ruby on Rails schema.rb形式
- `postgres`: PostgreSQL DDL形式
- `prisma`: Prisma schema形式
- `tbls`: tbls形式

---

### 9. design_sessions

スキーマ設計作業セッションを管理するテーブルです。チャット形式での設計支援やバージョン管理の基盤となります。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| id | uuid | NOT NULL | 主キー（自動生成） |
| name | text | NOT NULL | セッション名 |
| project_id | uuid | NULL | 関連プロジェクトID（外部キー） |
| organization_id | uuid | NOT NULL | 組織ID（外部キー） |
| created_by_user_id | uuid | NOT NULL | 作成者ユーザーID（外部キー） |
| parent_design_session_id | uuid | NULL | 親セッションID（外部キー、自己参照） |
| created_at | timestamp(3) with time zone | NOT NULL | 作成日時 |

**制約:**
- PRIMARY KEY: id
- FOREIGN KEY: project_id -> projects(id)
- FOREIGN KEY: organization_id -> organizations(id)
- FOREIGN KEY: created_by_user_id -> users(id)
- FOREIGN KEY: parent_design_session_id -> design_sessions(id)

**トリガー:**
- `set_design_sessions_organization_id_trigger`: プロジェクトから組織IDを自動設定

---

### 10. building_schemas

設計セッション中のスキーマ状態を管理するテーブルです。JSONBでスキーマ情報を保持します。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| id | uuid | NOT NULL | 主キー（自動生成） |
| design_session_id | uuid | NOT NULL | 設計セッションID（外部キー） |
| organization_id | uuid | NOT NULL | 組織ID（外部キー） |
| schema | jsonb | NOT NULL | 現在のスキーマ状態 |
| git_sha | text | NULL | 関連Gitコミットハッシュ |
| initial_schema_snapshot | jsonb | NULL | 初期スキーマスナップショット |
| schema_file_path | text | NULL | スキーマファイルパス |
| created_at | timestamp(3) with time zone | NOT NULL | 作成日時 |

**制約:**
- PRIMARY KEY: id
- UNIQUE: design_session_id（1:1関係）
- FOREIGN KEY: design_session_id -> design_sessions(id) ON DELETE CASCADE
- FOREIGN KEY: organization_id -> organizations(id)

**関連関数:**
- `update_building_schema`: スキーマ更新用ストアドプロシージャ

---

### 11. building_schema_versions

スキーマの変更履歴（バージョン）を管理するテーブルです。JSON Patchでの差分管理を実装しています。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| id | uuid | NOT NULL | 主キー（自動生成） |
| building_schema_id | uuid | NOT NULL | スキーマID（外部キー） |
| organization_id | uuid | NOT NULL | 組織ID（外部キー） |
| number | integer | NOT NULL | バージョン番号 |
| patch | jsonb | NULL | 適用パッチ（JSON Patch形式） |
| reverse_patch | jsonb | NULL | 逆パッチ（ロールバック用） |
| created_at | timestamp with time zone | NOT NULL | 作成日時 |

**制約:**
- PRIMARY KEY: id
- FOREIGN KEY: building_schema_id -> building_schemas(id) ON DELETE CASCADE
- FOREIGN KEY: organization_id -> organizations(id)

**インデックス:**
- building_schema_versions_building_schema_id_idx (building_schema_id)
- building_schema_versions_number_idx (number)

---

### 12. public_share_settings

設計セッションの公開共有状態を管理するテーブルです。匿名ユーザーへの読み取り許可を制御します。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| design_session_id | uuid | NOT NULL | 設計セッションID（主キー、外部キー） |
| created_at | timestamp with time zone | NOT NULL | 公開設定日時 |

**制約:**
- PRIMARY KEY: design_session_id
- FOREIGN KEY: design_session_id -> design_sessions(id) ON DELETE CASCADE

**インデックス:**
- idx_public_share_settings_created_at (created_at)

**RLS:** 匿名ユーザーに対して関連テーブルの読み取り専用アクセスを許可

---

### 13. checkpoints

LangGraphワークフローのチェックポイントメタデータを管理するテーブルです。AIエージェントの状態永続化に使用されます。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| id | uuid | NOT NULL | 主キー（自動生成） |
| thread_id | text | NOT NULL | ワークフロースレッドID |
| checkpoint_ns | text | NOT NULL | チェックポイント名前空間 |
| checkpoint_id | text | NOT NULL | チェックポイントID |
| parent_checkpoint_id | text | NULL | 親チェックポイントID |
| checkpoint | jsonb | NOT NULL | シリアライズされたチェックポイントデータ |
| metadata | jsonb | NOT NULL | カスタムメタデータ |
| organization_id | uuid | NOT NULL | 組織ID（外部キー） |
| created_at | timestamp with time zone | NULL | 作成日時 |
| updated_at | timestamp with time zone | NULL | 更新日時 |

**制約:**
- PRIMARY KEY: id
- UNIQUE: (thread_id, checkpoint_ns, checkpoint_id, organization_id)
- FOREIGN KEY: organization_id -> organizations(id)

**インデックス:**
- idx_checkpoints_organization_id (organization_id)
- idx_checkpoints_thread_id (thread_id, checkpoint_ns)
- idx_checkpoints_parent (parent_checkpoint_id) WHERE parent_checkpoint_id IS NOT NULL
- idx_checkpoints_created_at (created_at DESC)

**トリガー:**
- `update_checkpoints_updated_at_trigger`: 更新時にupdated_atを自動更新

---

### 14. checkpoint_blobs

チェックポイントのチャネル値（状態データ）を管理するテーブルです。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| id | uuid | NOT NULL | 主キー（自動生成） |
| thread_id | text | NOT NULL | ワークフロースレッドID |
| checkpoint_ns | text | NOT NULL | チェックポイント名前空間 |
| channel | text | NOT NULL | チャネル名 |
| version | text | NOT NULL | バージョン番号 |
| type | text | NOT NULL | シリアライズ形式 |
| blob | text | NULL | バイナリデータ（Base64等） |
| organization_id | uuid | NOT NULL | 組織ID（外部キー） |
| created_at | timestamp with time zone | NULL | 作成日時 |

**制約:**
- PRIMARY KEY: id
- UNIQUE: (thread_id, checkpoint_ns, channel, version, organization_id)
- FOREIGN KEY: organization_id -> organizations(id)

**インデックス:**
- idx_checkpoint_blobs_organization_id (organization_id)
- idx_checkpoint_blobs_thread_id (thread_id, checkpoint_ns)

---

### 15. checkpoint_writes

未コミットのチェックポイント書き込み操作を管理するテーブルです。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| id | uuid | NOT NULL | 主キー（自動生成） |
| thread_id | text | NOT NULL | ワークフロースレッドID |
| checkpoint_ns | text | NOT NULL | チェックポイント名前空間 |
| checkpoint_id | text | NOT NULL | チェックポイントID |
| task_id | text | NOT NULL | タスクID |
| idx | integer | NOT NULL | 書き込み順序インデックス |
| channel | text | NOT NULL | 対象チャネル |
| type | text | NULL | シリアライズ形式 |
| blob | text | NOT NULL | 書き込みデータ |
| organization_id | uuid | NOT NULL | 組織ID（外部キー） |
| created_at | timestamp with time zone | NULL | 作成日時 |

**制約:**
- PRIMARY KEY: id
- UNIQUE: (thread_id, checkpoint_ns, checkpoint_id, task_id, idx, organization_id)
- FOREIGN KEY: organization_id -> organizations(id)

**インデックス:**
- idx_checkpoint_writes_organization_id (organization_id)
- idx_checkpoint_writes_checkpoint (thread_id, checkpoint_ns, checkpoint_id)
- idx_checkpoint_writes_task (task_id)

## 列挙型（ENUM）定義

| ENUM名 | 値 | 説明 |
| --- | --- | --- |
| schema_format_enum | schemarb, postgres, prisma, tbls | スキーマファイルの形式 |
| assistant_role_enum | db, pm, qa | AIアシスタントの役割 |
| workflow_run_status | pending, success, error | ワークフロー実行状態 |

## 備考

### セキュリティ設計

1. **Row Level Security (RLS)**: 全テーブルでRLSが有効化され、organization_membersテーブルを基準としたアクセス制御が実装されています。
2. **マルチテナント分離**: 全データは`organization_id`によって分離され、クロステナントアクセスは防止されています。
3. **サービスロール**: バックグラウンドジョブ用にservice_roleポリシーが設定されています。

### トリガーと自動化

1. ユーザー同期: auth.usersからpublic.usersへの自動同期
2. 組織ID自動設定: 関連テーブルから組織IDを自動で継承
3. 更新日時自動更新: checkpointsテーブルの更新時にupdated_atを自動更新

### データ整合性

1. 外部キー制約による参照整合性の確保
2. カスケード削除による関連データの自動削除
3. 一意制約によるデータ重複の防止

### パフォーマンス最適化

1. 頻繁にクエリされるカラムへのインデックス設定
2. 複合インデックスによるクエリ高速化
3. JSONBカラムによる柔軟なスキーマ設計
