# ER図（Entity-Relationship Diagram）

## 概要

Fat Free CRMのデータベース構造を表すER図です。主要エンティティ間の関連を示しています。

## 全体ER図

```mermaid
erDiagram
    %% ユーザー関連
    users ||--o{ accounts : "creates"
    users ||--o{ contacts : "creates"
    users ||--o{ leads : "creates"
    users ||--o{ opportunities : "creates"
    users ||--o{ campaigns : "creates"
    users ||--o{ tasks : "creates"
    users ||--o{ comments : "creates"
    users ||--o{ avatars : "uploads"
    users ||--o{ permissions : "has"
    users ||--o{ preferences : "has"
    users ||--o{ lists : "has"
    users }o--o{ groups : "belongs_to"

    %% アカウント関連
    accounts ||--o{ account_contacts : "has"
    accounts ||--o{ account_opportunities : "has"
    accounts ||--o{ tasks : "has"
    accounts ||--o{ addresses : "has"
    accounts ||--o{ emails : "has"

    %% コンタクト関連
    contacts ||--o| account_contacts : "belongs_to"
    contacts ||--o{ contact_opportunities : "has"
    contacts ||--o{ tasks : "has"
    contacts ||--o{ addresses : "has"
    contacts ||--o{ emails : "has"
    contacts |o--o| leads : "converted_from"

    %% リード関連
    leads ||--o{ tasks : "has"
    leads ||--o{ addresses : "has"
    leads ||--o{ emails : "has"
    leads }o--o| campaigns : "belongs_to"

    %% オポチュニティ関連
    opportunities ||--o| account_opportunities : "belongs_to"
    opportunities ||--o{ contact_opportunities : "has"
    opportunities ||--o{ tasks : "has"
    opportunities ||--o{ emails : "has"
    opportunities }o--o| campaigns : "belongs_to"

    %% キャンペーン関連
    campaigns ||--o{ leads : "generates"
    campaigns ||--o{ opportunities : "generates"
    campaigns ||--o{ tasks : "has"
    campaigns ||--o{ emails : "has"

    %% タグ関連
    tags ||--o{ taggings : "has"
    tags ||--o| field_groups : "associated_with"

    %% フィールド関連
    field_groups ||--o{ fields : "contains"

    %% 権限関連
    groups ||--o{ permissions : "has"

    %% バージョン管理
    versions }o--o| users : "created_by"

    %% Active Storage
    active_storage_blobs ||--o{ active_storage_attachments : "has"
    active_storage_blobs ||--o{ active_storage_variant_records : "has"

    %% =====================
    %% エンティティ定義
    %% =====================

    users {
        integer id PK
        string username UK
        string email
        string first_name
        string last_name
        string encrypted_password
        boolean admin
        datetime suspended_at
        datetime deleted_at
    }

    accounts {
        integer id PK
        integer user_id FK
        integer assigned_to FK
        string name
        string access
        string email
        integer rating
        string category
        integer contacts_count
        integer opportunities_count
        datetime deleted_at
    }

    contacts {
        integer id PK
        integer user_id FK
        integer lead_id FK
        integer assigned_to FK
        integer reports_to FK
        string first_name
        string last_name
        string access
        string email
        boolean do_not_call
        datetime deleted_at
    }

    leads {
        integer id PK
        integer user_id FK
        integer campaign_id FK
        integer assigned_to FK
        string first_name
        string last_name
        string access
        string status
        integer rating
        boolean do_not_call
        datetime deleted_at
    }

    opportunities {
        integer id PK
        integer user_id FK
        integer campaign_id FK
        integer assigned_to FK
        string name
        string access
        string stage
        integer probability
        decimal amount
        date closes_on
        datetime deleted_at
    }

    campaigns {
        integer id PK
        integer user_id FK
        integer assigned_to FK
        string name
        string access
        string status
        decimal budget
        integer leads_count
        integer opportunities_count
        date starts_on
        date ends_on
        datetime deleted_at
    }

    tasks {
        integer id PK
        integer user_id FK
        integer assigned_to FK
        integer completed_by FK
        string name
        string asset_type
        integer asset_id
        string priority
        string category
        datetime due_at
        datetime completed_at
        datetime deleted_at
    }

    addresses {
        integer id PK
        string street1
        string city
        string state
        string zipcode
        string country
        string address_type
        string addressable_type
        integer addressable_id
        datetime deleted_at
    }

    comments {
        integer id PK
        integer user_id FK
        string commentable_type
        integer commentable_id
        boolean private
        text comment
        string state
    }

    emails {
        integer id PK
        string imap_message_id
        integer user_id FK
        string mediator_type
        integer mediator_id
        string sent_from
        string sent_to
        string subject
        text body
        datetime deleted_at
    }

    avatars {
        integer id PK
        integer user_id FK
        string entity_type
        integer entity_id
        string image_file_name
        string image_content_type
    }

    tags {
        integer id PK
        string name UK
        integer taggings_count
    }

    taggings {
        integer id PK
        integer tag_id FK
        integer taggable_id
        string taggable_type
        string context
    }

    versions {
        integer id PK
        string item_type
        integer item_id
        string event
        string whodunnit FK
        text object
        text object_changes
    }

    account_contacts {
        integer id PK
        integer account_id FK
        integer contact_id FK
        datetime deleted_at
    }

    account_opportunities {
        integer id PK
        integer account_id FK
        integer opportunity_id FK
        datetime deleted_at
    }

    contact_opportunities {
        integer id PK
        integer contact_id FK
        integer opportunity_id FK
        string role
        datetime deleted_at
    }

    groups {
        integer id PK
        string name UK
    }

    groups_users {
        integer group_id FK
        integer user_id FK
    }

    permissions {
        integer id PK
        integer user_id FK
        integer group_id FK
        string asset_type
        integer asset_id
    }

    preferences {
        integer id PK
        integer user_id FK
        string name
        text value
    }

    settings {
        integer id PK
        string name UK
        text value
    }

    field_groups {
        integer id PK
        string name
        string label
        integer position
        integer tag_id FK
        string klass_name
    }

    fields {
        integer id PK
        string type
        integer field_group_id FK
        integer position
        string name
        string label
        string as
        boolean required
    }

    lists {
        integer id PK
        integer user_id FK
        string name
        text url
    }

    activities {
        integer id PK
        integer user_id FK
        string subject_type
        integer subject_id
        string action
        boolean private
    }

    sessions {
        integer id PK
        string session_id UK
        text data
    }

    research_tools {
        integer id PK
        string name
        string url_template
        boolean enabled
    }

    active_storage_blobs {
        integer id PK
        string key UK
        string filename
        string content_type
        bigint byte_size
    }

    active_storage_attachments {
        integer id PK
        string name
        string record_type
        bigint record_id
        bigint blob_id FK
    }

    active_storage_variant_records {
        integer id PK
        bigint blob_id FK
        string variation_digest
    }

    action_text_rich_texts {
        integer id PK
        string name
        text body
        string record_type
        bigint record_id
    }
```

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

主要なCRMエンティティ（Account、Contact、Lead、Opportunity、Campaign）の関連を詳細に示します。

```mermaid
erDiagram
    users ||--o{ accounts : "creates/assigned"
    users ||--o{ contacts : "creates/assigned"
    users ||--o{ leads : "creates/assigned"
    users ||--o{ opportunities : "creates/assigned"
    users ||--o{ campaigns : "creates/assigned"

    campaigns ||--o{ leads : "generates"
    campaigns ||--o{ opportunities : "generates"

    leads ||--o| contacts : "converts_to"

    accounts ||--o{ account_contacts : "has"
    account_contacts }o--|| contacts : "links"

    accounts ||--o{ account_opportunities : "has"
    account_opportunities }o--|| opportunities : "links"

    contacts ||--o{ contact_opportunities : "has"
    contact_opportunities }o--|| opportunities : "links"

    users {
        integer id PK
        string username
        string email
        string first_name
        string last_name
        boolean admin
    }

    campaigns {
        integer id PK
        integer user_id FK "Creator"
        integer assigned_to FK "Assignee"
        string name
        string status
        decimal budget
        decimal target_revenue
        integer leads_count
        integer opportunities_count
    }

    leads {
        integer id PK
        integer user_id FK "Creator"
        integer campaign_id FK
        integer assigned_to FK "Assignee"
        string first_name
        string last_name
        string company
        string status
        integer rating
    }

    accounts {
        integer id PK
        integer user_id FK "Creator"
        integer assigned_to FK "Assignee"
        string name
        integer rating
        string category
        integer contacts_count
        integer opportunities_count
    }

    contacts {
        integer id PK
        integer user_id FK "Creator"
        integer lead_id FK "Source Lead"
        integer assigned_to FK "Assignee"
        string first_name
        string last_name
        string title
        string department
    }

    opportunities {
        integer id PK
        integer user_id FK "Creator"
        integer campaign_id FK
        integer assigned_to FK "Assignee"
        string name
        string stage
        integer probability
        decimal amount
        date closes_on
    }

    account_contacts {
        integer id PK
        integer account_id FK
        integer contact_id FK
    }

    account_opportunities {
        integer id PK
        integer account_id FK
        integer opportunity_id FK
    }

    contact_opportunities {
        integer id PK
        integer contact_id FK
        integer opportunity_id FK
        string role
    }
```

## ポリモーフィック関連ER図

ポリモーフィック関連を持つエンティティの構造を示します。

```mermaid
erDiagram
    addresses }o--|| accounts : "addressable"
    addresses }o--|| contacts : "addressable"
    addresses }o--|| leads : "addressable"

    comments }o--|| accounts : "commentable"
    comments }o--|| contacts : "commentable"
    comments }o--|| leads : "commentable"
    comments }o--|| opportunities : "commentable"
    comments }o--|| campaigns : "commentable"
    comments }o--|| tasks : "commentable"

    emails }o--|| accounts : "mediator"
    emails }o--|| contacts : "mediator"
    emails }o--|| leads : "mediator"
    emails }o--|| opportunities : "mediator"
    emails }o--|| campaigns : "mediator"

    tasks }o--|| accounts : "asset"
    tasks }o--|| contacts : "asset"
    tasks }o--|| leads : "asset"
    tasks }o--|| opportunities : "asset"
    tasks }o--|| campaigns : "asset"

    avatars }o--|| users : "entity"
    avatars }o--|| contacts : "entity"

    permissions }o--|| accounts : "asset"
    permissions }o--|| contacts : "asset"
    permissions }o--|| leads : "asset"
    permissions }o--|| opportunities : "asset"
    permissions }o--|| campaigns : "asset"

    taggings }o--|| accounts : "taggable"
    taggings }o--|| contacts : "taggable"
    taggings }o--|| leads : "taggable"
    taggings }o--|| opportunities : "taggable"
    taggings }o--|| campaigns : "taggable"

    versions }o--|| accounts : "item"
    versions }o--|| contacts : "item"
    versions }o--|| leads : "item"
    versions }o--|| opportunities : "item"
    versions }o--|| campaigns : "item"
    versions }o--|| tasks : "item"
    versions }o--|| comments : "item"
    versions }o--|| emails : "item"

    addresses {
        integer id PK
        string addressable_type
        integer addressable_id
        string address_type
        string street1
        string city
        string state
        string zipcode
        string country
    }

    comments {
        integer id PK
        string commentable_type
        integer commentable_id
        integer user_id FK
        text comment
        boolean private
    }

    emails {
        integer id PK
        string mediator_type
        integer mediator_id
        integer user_id FK
        string sent_from
        string sent_to
        string subject
    }

    tasks {
        integer id PK
        string asset_type
        integer asset_id
        integer user_id FK
        integer assigned_to FK
        string name
        datetime due_at
    }

    avatars {
        integer id PK
        string entity_type
        integer entity_id
        integer user_id FK
        string image_file_name
    }

    permissions {
        integer id PK
        string asset_type
        integer asset_id
        integer user_id FK
        integer group_id FK
    }

    taggings {
        integer id PK
        string taggable_type
        integer taggable_id
        integer tag_id FK
        string context
    }

    versions {
        integer id PK
        string item_type
        integer item_id
        string event
        string whodunnit
        text object
    }

    accounts {
        integer id PK
        string name
    }

    contacts {
        integer id PK
        string first_name
        string last_name
    }

    leads {
        integer id PK
        string first_name
        string last_name
    }

    opportunities {
        integer id PK
        string name
    }

    campaigns {
        integer id PK
        string name
    }

    users {
        integer id PK
        string username
    }

    tags {
        integer id PK
        string name
    }
```

## ユーザー・権限管理ER図

ユーザー、グループ、権限の関連を示します。

```mermaid
erDiagram
    users }o--o{ groups : "belongs_to"
    users ||--o{ permissions : "has"
    groups ||--o{ permissions : "has"
    users ||--o{ preferences : "has"

    users {
        integer id PK
        string username UK
        string email
        string first_name
        string last_name
        boolean admin
        datetime suspended_at
    }

    groups {
        integer id PK
        string name UK
    }

    groups_users {
        integer group_id FK
        integer user_id FK
    }

    permissions {
        integer id PK
        integer user_id FK
        integer group_id FK
        string asset_type
        integer asset_id
    }

    preferences {
        integer id PK
        integer user_id FK
        string name
        text value
    }
```

## カスタムフィールドER図

カスタムフィールド管理の構造を示します。

```mermaid
erDiagram
    field_groups ||--o{ fields : "contains"
    field_groups }o--o| tags : "associated_with"

    field_groups {
        integer id PK
        string name
        string label
        integer position
        string hint
        integer tag_id FK
        string klass_name
    }

    fields {
        integer id PK
        string type "STI: CoreField, CustomField"
        integer field_group_id FK
        integer position
        string name
        string label
        string hint
        string placeholder
        string as
        text collection
        boolean disabled
        boolean required
        integer maxlength
        integer minlength
        integer pair_id FK
        text settings
    }

    tags {
        integer id PK
        string name UK
        integer taggings_count
    }
```

## 凡例

- **PK**: Primary Key（主キー）
- **FK**: Foreign Key（外部キー）
- **UK**: Unique Key（一意キー）
- **||--o{**: 1対多の関連
- **}o--o{**: 多対多の関連
- **||--o|**: 1対0または1の関連
- **}o--||**: 多対1の関連
