# ER図

## 概要

本ドキュメントは、GitLabプロジェクトの主要テーブル間のリレーションシップをER図で表現したものです。

## 全体ER図

```mermaid
erDiagram
    namespaces ||--o{ projects : "namespace_id"
    namespaces ||--o{ namespaces : "parent_id"
    namespaces ||--o{ members : "member_namespace_id"
    namespaces ||--o{ labels : "group_id"
    namespaces ||--o{ milestones : "group_id"

    users ||--o{ projects : "creator_id"
    users ||--o{ members : "user_id"
    users ||--o{ issues : "author_id"
    users ||--o{ merge_requests : "author_id"
    users ||--o{ notes : "author_id"
    users ||--o{ events : "author_id"

    projects ||--o{ issues : "project_id"
    projects ||--o{ merge_requests : "target_project_id"
    projects ||--o{ labels : "project_id"
    projects ||--o{ milestones : "project_id"
    projects ||--o{ notes : "project_id"
    projects ||--o{ events : "project_id"
    projects ||--o{ p_ci_pipelines : "project_id"
    projects ||--o{ deployments : "project_id"
    projects ||--o{ releases : "project_id"

    issues }o--|| milestones : "milestone_id"
    issues ||--o{ notes : "noteable_id"

    merge_requests }o--|| milestones : "milestone_id"
    merge_requests ||--o{ notes : "noteable_id"
    merge_requests }o--|| p_ci_pipelines : "head_pipeline_id"

    p_ci_pipelines ||--o{ p_ci_stages : "pipeline_id"
    p_ci_pipelines ||--o{ p_ci_builds : "pipeline_id"

    p_ci_stages ||--o{ p_ci_builds : "stage_id"

    p_ci_builds ||--o{ p_ci_job_artifacts : "job_id"

    deployments }o--|| p_ci_builds : "deployable_id"

    namespaces {
        bigint id PK
        varchar name
        varchar path
        bigint owner_id FK
        varchar type
        bigint parent_id FK
        bigint organization_id
    }

    projects {
        bigint id PK
        varchar name
        varchar path
        bigint creator_id FK
        bigint namespace_id FK
        bigint organization_id
    }

    users {
        bigint id PK
        varchar email
        varchar username
        varchar name
        boolean admin
        bigint organization_id
    }

    members {
        bigint id PK
        integer access_level
        bigint source_id
        varchar source_type
        bigint user_id FK
        bigint member_namespace_id FK
    }

    issues {
        bigint id PK
        varchar title
        bigint author_id FK
        bigint project_id FK
        bigint milestone_id FK
        smallint state_id
        bigint namespace_id FK
    }

    merge_requests {
        bigint id PK
        varchar title
        varchar target_branch
        varchar source_branch
        bigint author_id FK
        bigint target_project_id FK
        bigint milestone_id FK
        smallint state_id
        bigint head_pipeline_id FK
    }

    labels {
        bigint id PK
        varchar title
        varchar color
        bigint project_id FK
        bigint group_id FK
    }

    milestones {
        bigint id PK
        varchar title
        bigint project_id FK
        bigint group_id FK
        date due_date
        varchar state
    }

    notes {
        bigint id PK
        text note
        varchar noteable_type
        bigint noteable_id
        bigint author_id FK
        bigint project_id FK
    }

    events {
        bigint id PK
        bigint project_id FK
        bigint author_id FK
        smallint action
        varchar target_type
        bigint target_id
    }

    p_ci_pipelines {
        bigint id PK
        varchar ref
        varchar sha
        bigint project_id FK
        varchar status
        bigint user_id FK
        bigint partition_id
    }

    p_ci_stages {
        bigint id PK
        bigint project_id FK
        bigint pipeline_id FK
        varchar name
        integer status
        bigint partition_id
    }

    p_ci_builds {
        bigint id PK
        varchar status
        varchar name
        varchar stage
        bigint project_id FK
        bigint pipeline_id FK
        bigint partition_id
    }

    p_ci_job_artifacts {
        bigint id PK
        bigint project_id FK
        bigint job_id FK
        integer file_type
        bigint partition_id
    }

    deployments {
        bigint id PK
        bigint project_id FK
        bigint environment_id FK
        varchar ref
        varchar sha
        smallint status
        bigint deployable_id FK
    }

    releases {
        bigint id PK
        varchar tag
        bigint project_id FK
        bigint author_id FK
        varchar name
    }
```

## コアエンティティER図

ユーザー、ネームスペース、プロジェクト、メンバーの関係を詳細に表現します。

```mermaid
erDiagram
    namespaces ||--o{ projects : "所属"
    namespaces ||--o{ namespaces : "親子関係"
    users ||--o{ namespaces : "所有"
    users ||--o{ projects : "作成"
    users ||--o{ members : "メンバーシップ"
    namespaces ||--o{ members : "所属先"

    namespaces {
        bigint id PK "主キー"
        varchar name "名前"
        varchar path "パス"
        bigint owner_id FK "所有者ID"
        varchar type "タイプ"
        bigint parent_id FK "親ID"
        integer visibility_level "可視性"
        bigint organization_id "組織ID"
        bigint[] traversal_ids "階層パス"
    }

    users {
        bigint id PK "主キー"
        varchar email "メール"
        varchar username "ユーザー名"
        varchar name "表示名"
        boolean admin "管理者"
        varchar state "状態"
        bigint organization_id "組織ID"
    }

    projects {
        bigint id PK "主キー"
        varchar name "名前"
        varchar path "パス"
        bigint creator_id FK "作成者ID"
        bigint namespace_id FK "ネームスペースID"
        integer visibility_level "可視性"
        boolean archived "アーカイブ"
        bigint organization_id "組織ID"
    }

    members {
        bigint id PK "主キー"
        integer access_level "アクセスレベル"
        bigint source_id "ソースID"
        varchar source_type "ソースタイプ"
        bigint user_id FK "ユーザーID"
        date expires_at "有効期限"
        bigint member_namespace_id FK "メンバーNS ID"
    }
```

## イシュー・マージリクエストER図

イシューとマージリクエストを中心としたリレーションシップを表現します。

```mermaid
erDiagram
    projects ||--o{ issues : "belongs_to"
    projects ||--o{ merge_requests : "target"
    projects ||--o{ merge_requests : "source"
    projects ||--o{ labels : "has"
    projects ||--o{ milestones : "has"

    users ||--o{ issues : "creates"
    users ||--o{ merge_requests : "creates"

    milestones ||--o{ issues : "groups"
    milestones ||--o{ merge_requests : "groups"

    issues ||--o{ notes : "has_comments"
    merge_requests ||--o{ notes : "has_comments"

    issues {
        bigint id PK
        varchar title
        text description
        bigint author_id FK
        bigint project_id FK
        bigint milestone_id FK
        smallint state_id
        boolean confidential
        date due_date
        integer weight
        bigint namespace_id FK
    }

    merge_requests {
        bigint id PK
        varchar title
        text description
        varchar target_branch
        varchar source_branch
        bigint source_project_id FK
        bigint target_project_id FK
        bigint author_id FK
        bigint milestone_id FK
        smallint state_id
        varchar merge_status
        boolean draft
    }

    labels {
        bigint id PK
        varchar title
        varchar color
        bigint project_id FK
        bigint group_id FK
        varchar type
    }

    milestones {
        bigint id PK
        varchar title
        text description
        date start_date
        date due_date
        varchar state
        bigint project_id FK
        bigint group_id FK
    }

    notes {
        bigint id PK
        text note
        varchar noteable_type
        bigint noteable_id
        bigint author_id FK
        bigint project_id FK
        boolean system
        boolean confidential
    }
```

## CI/CDパイプラインER図

CI/CDに関連するテーブルのリレーションシップを表現します。

```mermaid
erDiagram
    projects ||--o{ p_ci_pipelines : "has"
    users ||--o{ p_ci_pipelines : "triggers"
    merge_requests ||--o{ p_ci_pipelines : "triggers"

    p_ci_pipelines ||--o{ p_ci_stages : "contains"
    p_ci_pipelines ||--o{ p_ci_builds : "contains"
    p_ci_pipelines ||--o{ p_ci_pipeline_variables : "has"

    p_ci_stages ||--o{ p_ci_builds : "groups"

    p_ci_builds ||--o{ p_ci_job_artifacts : "produces"
    p_ci_builds ||--o{ deployments : "deploys"

    projects ||--o{ deployments : "has"

    p_ci_pipelines {
        bigint id PK
        varchar ref
        varchar sha
        varchar status
        bigint project_id FK
        bigint user_id FK
        integer source
        integer duration
        bigint merge_request_id FK
        bigint partition_id "Partition Key"
    }

    p_ci_stages {
        bigint id PK
        varchar name
        integer status
        integer position
        bigint project_id FK
        bigint pipeline_id FK
        bigint partition_id "Partition Key"
    }

    p_ci_builds {
        bigint id PK
        varchar name
        varchar status
        varchar stage
        float coverage
        varchar ref
        bigint project_id FK
        bigint pipeline_id FK
        bigint user_id FK
        bigint partition_id "Partition Key"
    }

    p_ci_job_artifacts {
        bigint id PK
        integer file_type
        bigint size
        bigint project_id FK
        bigint job_id FK
        bigint partition_id "Partition Key"
    }

    p_ci_pipeline_variables {
        bigint id PK
        varchar key
        text value
        boolean protected
        bigint pipeline_id FK
        bigint partition_id "Partition Key"
    }

    deployments {
        bigint id PK
        integer iid
        varchar ref
        varchar sha
        smallint status
        bigint project_id FK
        bigint environment_id FK
        bigint deployable_id FK
    }
```

## リリース・デプロイメントER図

リリースとデプロイメントに関連するテーブルのリレーションシップを表現します。

```mermaid
erDiagram
    projects ||--o{ releases : "has"
    users ||--o{ releases : "creates"
    releases ||--o{ evidences : "has"

    projects ||--o{ deployments : "has"
    projects ||--o{ environments : "has"
    environments ||--o{ deployments : "receives"

    releases {
        bigint id PK
        varchar tag
        varchar name
        text description
        varchar sha
        timestamp released_at
        bigint project_id FK
        bigint author_id FK
    }

    evidences {
        bigint id PK
        jsonb summary
        bytea summary_sha
        bigint release_id FK
        bigint project_id FK
    }

    deployments {
        bigint id PK
        integer iid
        varchar ref
        boolean tag
        varchar sha
        smallint status
        timestamp finished_at
        bigint project_id FK
        bigint environment_id FK
        bigint user_id FK
        bigint deployable_id FK
    }

    environments {
        bigint id PK
        varchar name
        varchar state
        varchar external_url
        bigint project_id FK
    }
```

## 備考

### ER図の表記について

- `||--o{`: 1対多のリレーションシップ
- `}o--||`: 多対1のリレーションシップ
- `PK`: Primary Key（主キー）
- `FK`: Foreign Key（外部キー）

### パーティションテーブルについて

`p_`プレフィックスが付いたテーブル（例：`p_ci_pipelines`、`p_ci_builds`等）は、PostgreSQLのテーブルパーティショニング機能を使用しています。これらのテーブルは`partition_id`をパーティションキーとして使用し、データを複数の物理テーブルに分散させています。

### 省略されたリレーションシップ

GitLabは約987テーブルを持つ大規模なデータベースのため、本図では主要なリレーションシップのみを表現しています。実際のデータベースには、以下のような追加のリレーションシップが存在します：

- ラベルとイシュー/MRの多対多関係（`label_links`テーブル経由）
- アサイニーとイシュー/MRの多対多関係（`issue_assignees`、`merge_request_assignees`テーブル経由）
- 承認者とMRの関係（`approvals`、`approval_merge_request_rules`等）
- セキュリティ関連テーブル（`vulnerability_*`系）
- パッケージレジストリ関連テーブル（`packages_*`系）
