# ER図

## 概要

本ドキュメントは、Liam HQプロジェクトのデータベースエンティティ関係（ER）図をMermaid形式で記述したものです。

## 全体ER図

```mermaid
erDiagram
    %% ユーザー・組織管理
    users ||--o{ organization_members : "belongs to"
    users ||--o{ invitations : "invites"
    users ||--o{ design_sessions : "creates"

    organizations ||--o{ organization_members : "has"
    organizations ||--o{ invitations : "receives"
    organizations ||--o{ projects : "owns"
    organizations ||--o{ github_repositories : "owns"
    organizations ||--o{ checkpoints : "owns"
    organizations ||--o{ checkpoint_blobs : "owns"
    organizations ||--o{ checkpoint_writes : "owns"

    %% プロジェクト・リポジトリ管理
    projects ||--o{ project_repository_mappings : "has"
    projects ||--o{ schema_file_paths : "has"
    projects ||--o{ design_sessions : "has"

    github_repositories ||--o{ project_repository_mappings : "linked to"

    %% 設計セッション管理
    design_sessions ||--o| building_schemas : "has"
    design_sessions ||--o| public_share_settings : "has"
    design_sessions ||--o{ design_sessions : "parent of"

    building_schemas ||--o{ building_schema_versions : "has"

    %% エンティティ定義
    users {
        uuid id PK
        text name
        text email UK
        text avatar_url
    }

    organizations {
        uuid id PK
        text name
    }

    organization_members {
        uuid id PK
        uuid user_id FK
        uuid organization_id FK
        timestamptz joined_at
    }

    invitations {
        uuid id PK
        text email
        uuid invite_by_user_id FK
        uuid organization_id FK
        timestamptz invited_at
        text token
        timestamptz expired_at
    }

    projects {
        uuid id PK
        text name
        uuid organization_id FK
        timestamptz created_at
        timestamptz updated_at
    }

    github_repositories {
        uuid id PK
        text name
        text owner
        integer github_installation_identifier
        integer github_repository_identifier
        uuid organization_id FK
        timestamptz created_at
        timestamptz updated_at
    }

    project_repository_mappings {
        uuid id PK
        uuid project_id FK
        uuid repository_id FK
        uuid organization_id FK
        timestamptz created_at
        timestamptz updated_at
    }

    schema_file_paths {
        uuid id PK
        text path
        schema_format_enum format
        uuid project_id FK
        uuid organization_id FK
        timestamptz created_at
        timestamptz updated_at
    }

    design_sessions {
        uuid id PK
        text name
        uuid project_id FK
        uuid organization_id FK
        uuid created_by_user_id FK
        uuid parent_design_session_id FK
        timestamptz created_at
    }

    building_schemas {
        uuid id PK
        uuid design_session_id FK
        uuid organization_id FK
        jsonb schema
        text git_sha
        jsonb initial_schema_snapshot
        text schema_file_path
        timestamptz created_at
    }

    building_schema_versions {
        uuid id PK
        uuid building_schema_id FK
        uuid organization_id FK
        integer number
        jsonb patch
        jsonb reverse_patch
        timestamptz created_at
    }

    public_share_settings {
        uuid design_session_id PK_FK
        timestamptz created_at
    }

    checkpoints {
        uuid id PK
        text thread_id
        text checkpoint_ns
        text checkpoint_id
        text parent_checkpoint_id
        jsonb checkpoint
        jsonb metadata
        uuid organization_id FK
        timestamptz created_at
        timestamptz updated_at
    }

    checkpoint_blobs {
        uuid id PK
        text thread_id
        text checkpoint_ns
        text channel
        text version
        text type
        text blob
        uuid organization_id FK
        timestamptz created_at
    }

    checkpoint_writes {
        uuid id PK
        text thread_id
        text checkpoint_ns
        text checkpoint_id
        text task_id
        integer idx
        text channel
        text type
        text blob
        uuid organization_id FK
        timestamptz created_at
    }
```

## ドメイン別ER図

### ユーザー・組織管理ドメイン

```mermaid
erDiagram
    users ||--o{ organization_members : "is member of"
    users ||--o{ invitations : "sends"
    organizations ||--o{ organization_members : "has members"
    organizations ||--o{ invitations : "has pending"

    users {
        uuid id PK
        text name
        text email UK
        text avatar_url
    }

    organizations {
        uuid id PK
        text name
    }

    organization_members {
        uuid id PK
        uuid user_id FK
        uuid organization_id FK
        timestamptz joined_at
    }

    invitations {
        uuid id PK
        text email
        uuid invite_by_user_id FK
        uuid organization_id FK
        timestamptz invited_at
        text token
        timestamptz expired_at
    }
```

### プロジェクト・リポジトリ管理ドメイン

```mermaid
erDiagram
    organizations ||--o{ projects : "owns"
    organizations ||--o{ github_repositories : "owns"
    projects ||--o{ project_repository_mappings : "connected via"
    github_repositories ||--o{ project_repository_mappings : "connected via"
    projects ||--o{ schema_file_paths : "has"

    projects {
        uuid id PK
        text name
        uuid organization_id FK
        timestamptz created_at
        timestamptz updated_at
    }

    github_repositories {
        uuid id PK
        text name
        text owner
        integer github_installation_identifier
        integer github_repository_identifier
        uuid organization_id FK
        timestamptz created_at
        timestamptz updated_at
    }

    project_repository_mappings {
        uuid id PK
        uuid project_id FK
        uuid repository_id FK
        uuid organization_id FK
        timestamptz created_at
        timestamptz updated_at
    }

    schema_file_paths {
        uuid id PK
        text path
        schema_format_enum format
        uuid project_id FK
        uuid organization_id FK
        timestamptz created_at
        timestamptz updated_at
    }
```

### 設計セッション・スキーマ管理ドメイン

```mermaid
erDiagram
    users ||--o{ design_sessions : "creates"
    projects ||--o{ design_sessions : "contains"
    design_sessions ||--o{ design_sessions : "branches from"
    design_sessions ||--o| building_schemas : "has"
    design_sessions ||--o| public_share_settings : "shared via"
    building_schemas ||--o{ building_schema_versions : "versioned by"

    design_sessions {
        uuid id PK
        text name
        uuid project_id FK
        uuid organization_id FK
        uuid created_by_user_id FK
        uuid parent_design_session_id FK
        timestamptz created_at
    }

    building_schemas {
        uuid id PK
        uuid design_session_id FK
        uuid organization_id FK
        jsonb schema
        text git_sha
        jsonb initial_schema_snapshot
        text schema_file_path
        timestamptz created_at
    }

    building_schema_versions {
        uuid id PK
        uuid building_schema_id FK
        uuid organization_id FK
        integer number
        jsonb patch
        jsonb reverse_patch
        timestamptz created_at
    }

    public_share_settings {
        uuid design_session_id PK_FK
        timestamptz created_at
    }
```

### チェックポイント（LangGraph）ドメイン

```mermaid
erDiagram
    organizations ||--o{ checkpoints : "owns"
    organizations ||--o{ checkpoint_blobs : "owns"
    organizations ||--o{ checkpoint_writes : "owns"

    checkpoints {
        uuid id PK
        text thread_id
        text checkpoint_ns
        text checkpoint_id
        text parent_checkpoint_id
        jsonb checkpoint
        jsonb metadata
        uuid organization_id FK
        timestamptz created_at
        timestamptz updated_at
    }

    checkpoint_blobs {
        uuid id PK
        text thread_id
        text checkpoint_ns
        text channel
        text version
        text type
        text blob
        uuid organization_id FK
        timestamptz created_at
    }

    checkpoint_writes {
        uuid id PK
        text thread_id
        text checkpoint_ns
        text checkpoint_id
        text task_id
        integer idx
        text channel
        text type
        text blob
        uuid organization_id FK
        timestamptz created_at
    }
```

## 関係性の説明

### 主要な関係パターン

| 関係 | カーディナリティ | 説明 |
| --- | --- | --- |
| users - organization_members | 1:N | ユーザーは複数組織に所属可能 |
| organizations - organization_members | 1:N | 組織は複数メンバーを持つ |
| organizations - projects | 1:N | 組織は複数プロジェクトを所有 |
| projects - design_sessions | 1:N | プロジェクトは複数の設計セッションを持つ |
| design_sessions - building_schemas | 1:1 | 設計セッションは1つのスキーマ状態を持つ |
| building_schemas - building_schema_versions | 1:N | スキーマは複数のバージョン履歴を持つ |
| design_sessions - design_sessions | 1:N | セッションは親セッションからブランチ可能（自己参照） |
| design_sessions - public_share_settings | 1:0..1 | セッションは任意で公開共有設定を持つ |

### マルチテナント設計

全てのビジネスデータテーブルは `organization_id` を持ち、組織単位でのデータ分離を実現しています。Row Level Security（RLS）により、ユーザーは所属組織のデータのみにアクセスできます。
