# ER図（Entity-Relationship Diagram）

## 概要

本ドキュメントは、Chatwootデータベースのエンティティ関係図をMermaid形式で記載しています。関係性の複雑さから、ドメイン別に分割して表示しています。

## 1. コアエンティティ（Account/User/Contact/Conversation/Message）

```mermaid
erDiagram
    accounts ||--o{ account_users : "has many"
    accounts ||--o{ contacts : "has many"
    accounts ||--o{ conversations : "has many"
    accounts ||--o{ messages : "has many"
    accounts ||--o{ inboxes : "has many"
    accounts ||--o{ teams : "has many"
    accounts ||--o{ labels : "has many"
    accounts ||--o{ campaigns : "has many"
    accounts ||--o{ automation_rules : "has many"
    accounts ||--o{ webhooks : "has many"
    accounts ||--o{ notifications : "has many"
    accounts ||--o{ sla_policies : "has many"
    accounts ||--o{ custom_roles : "has many"
    accounts ||--o{ companies : "has many"

    users ||--o{ account_users : "has many"
    users ||--o{ notification_settings : "has many"
    users ||--o{ notification_subscriptions : "has many"
    users ||--o{ notifications : "has many"
    users ||--o{ team_members : "has many"
    users ||--o{ inbox_members : "has many"
    users ||--o{ mentions : "has many"
    users ||--o{ custom_filters : "has many"
    users ||--o{ notes : "has many"

    account_users }o--|| accounts : "belongs to"
    account_users }o--|| users : "belongs to"
    account_users }o--o| custom_roles : "has optional"
    account_users }o--o| agent_capacity_policies : "has optional"

    contacts ||--o{ conversations : "has many"
    contacts ||--o{ contact_inboxes : "has many"
    contacts ||--o{ notes : "has many"
    contacts ||--o{ csat_survey_responses : "has many"
    contacts }o--|| accounts : "belongs to"
    contacts }o--o| companies : "belongs to optional"

    companies }o--|| accounts : "belongs to"
    companies ||--o{ contacts : "has many"

    conversations }o--|| accounts : "belongs to"
    conversations }o--|| inboxes : "belongs to"
    conversations }o--|| contacts : "belongs to"
    conversations }o--o| users : "assigned to optional"
    conversations }o--o| teams : "belongs to optional"
    conversations }o--o| campaigns : "belongs to optional"
    conversations }o--o| sla_policies : "has optional"
    conversations }o--o| agent_bots : "assigned to optional"
    conversations ||--o{ messages : "has many"
    conversations ||--o{ conversation_participants : "has many"
    conversations ||--o{ mentions : "has many"
    conversations ||--o| csat_survey_responses : "has one optional"

    messages }o--|| accounts : "belongs to"
    messages }o--|| inboxes : "belongs to"
    messages }o--|| conversations : "belongs to"
    messages ||--o{ attachments : "has many"
    messages ||--o| csat_survey_responses : "has one optional"

    accounts {
        serial id PK
        string name
        integer locale
        string domain
        string support_email
        bigint feature_flags
        integer auto_resolve_duration
        jsonb limits
        jsonb custom_attributes
        integer status
        jsonb settings
    }

    users {
        serial id PK
        string name
        string email
        string encrypted_password
        string pubsub_token
        integer availability
        jsonb ui_settings
        string type
        boolean otp_required_for_login
    }

    account_users {
        bigint id PK
        bigint account_id FK
        bigint user_id FK
        integer role
        bigint inviter_id FK
        integer availability
        bigint custom_role_id FK
    }

    contacts {
        serial id PK
        string name
        string email
        string phone_number
        integer account_id FK
        string identifier
        jsonb custom_attributes
        integer contact_type
        boolean blocked
        bigint company_id FK
    }

    companies {
        bigint id PK
        string name
        string domain
        text description
        bigint account_id FK
        integer contacts_count
    }

    conversations {
        serial id PK
        integer account_id FK
        integer inbox_id FK
        integer status
        integer assignee_id FK
        bigint contact_id FK
        integer display_id
        uuid uuid
        bigint team_id FK
        bigint campaign_id FK
        bigint sla_policy_id FK
        integer priority
    }

    messages {
        serial id PK
        text content
        integer account_id FK
        integer inbox_id FK
        integer conversation_id FK
        integer message_type
        boolean private
        integer status
        integer content_type
        string sender_type
        bigint sender_id
    }

    attachments {
        serial id PK
        integer file_type
        string external_url
        integer message_id FK
        integer account_id FK
        string extension
        jsonb meta
    }
```

## 2. インボックス/チャネル関連

```mermaid
erDiagram
    inboxes ||--o{ inbox_members : "has many"
    inboxes ||--o{ contact_inboxes : "has many"
    inboxes ||--o{ conversations : "has many"
    inboxes ||--o{ messages : "has many"
    inboxes ||--o{ campaigns : "has many"
    inboxes ||--o{ webhooks : "has many"
    inboxes ||--o| agent_bot_inboxes : "has one optional"
    inboxes ||--o| inbox_assignment_policies : "has one optional"
    inboxes }o--|| accounts : "belongs to"
    inboxes }o--o| portals : "belongs to optional"

    inbox_members }o--|| inboxes : "belongs to"
    inbox_members }o--|| users : "belongs to"

    contact_inboxes }o--|| contacts : "belongs to"
    contact_inboxes }o--|| inboxes : "belongs to"

    channel_web_widgets ||--|| inboxes : "one to one"
    channel_email ||--|| inboxes : "one to one"
    channel_facebook_pages ||--|| inboxes : "one to one"
    channel_twitter_profiles ||--|| inboxes : "one to one"
    channel_telegram ||--|| inboxes : "one to one"
    channel_twilio_sms ||--|| inboxes : "one to one"
    channel_sms ||--|| inboxes : "one to one"
    channel_whatsapp ||--|| inboxes : "one to one"
    channel_api ||--|| inboxes : "one to one"
    channel_line ||--|| inboxes : "one to one"
    channel_instagram ||--|| inboxes : "one to one"
    channel_tiktok ||--|| inboxes : "one to one"
    channel_voice ||--|| inboxes : "one to one"

    inboxes {
        serial id PK
        integer channel_id
        string channel_type
        integer account_id FK
        string name
        boolean enable_auto_assignment
        boolean csat_survey_enabled
        string timezone
        bigint portal_id FK
    }

    inbox_members {
        serial id PK
        integer user_id FK
        integer inbox_id FK
    }

    contact_inboxes {
        bigint id PK
        bigint contact_id FK
        bigint inbox_id FK
        text source_id
        boolean hmac_verified
        string pubsub_token
    }

    channel_web_widgets {
        serial id PK
        string website_url
        integer account_id FK
        string website_token
        string widget_color
        boolean pre_chat_form_enabled
    }

    channel_email {
        bigint id PK
        integer account_id FK
        string email
        string forward_to_email
        boolean imap_enabled
        boolean smtp_enabled
        string provider
    }

    channel_whatsapp {
        bigint id PK
        integer account_id FK
        string phone_number
        string provider
        jsonb message_templates
    }

    channel_telegram {
        bigint id PK
        string bot_name
        integer account_id FK
        string bot_token
    }

    channel_api {
        bigint id PK
        integer account_id FK
        string webhook_url
        string identifier
        string hmac_token
    }
```

## 3. チーム/ラベル/タグ関連

```mermaid
erDiagram
    teams ||--o{ team_members : "has many"
    teams ||--o{ conversations : "has many"
    teams }o--|| accounts : "belongs to"

    team_members }o--|| teams : "belongs to"
    team_members }o--|| users : "belongs to"

    labels }o--|| accounts : "belongs to"

    tags ||--o{ taggings : "has many"
    taggings }o--|| tags : "belongs to"

    teams {
        bigint id PK
        string name
        text description
        boolean allow_auto_assign
        bigint account_id FK
    }

    team_members {
        bigint id PK
        bigint team_id FK
        bigint user_id FK
    }

    labels {
        bigint id PK
        string title
        text description
        string color
        boolean show_on_sidebar
        bigint account_id FK
    }

    tags {
        serial id PK
        string name
        integer taggings_count
    }

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

## 4. ボット/自動化/統合関連

```mermaid
erDiagram
    agent_bots ||--o{ agent_bot_inboxes : "has many"
    agent_bots }o--o| accounts : "belongs to optional"

    agent_bot_inboxes }o--|| inboxes : "belongs to"
    agent_bot_inboxes }o--|| agent_bots : "belongs to"

    automation_rules }o--|| accounts : "belongs to"

    macros }o--|| accounts : "belongs to"
    macros }o--o| users : "created by optional"

    integrations_hooks }o--o| accounts : "belongs to optional"
    integrations_hooks }o--o| inboxes : "belongs to optional"

    assignment_policies ||--o{ inbox_assignment_policies : "has many"
    assignment_policies }o--|| accounts : "belongs to"

    inbox_assignment_policies }o--|| inboxes : "belongs to"
    inbox_assignment_policies }o--|| assignment_policies : "belongs to"

    agent_capacity_policies ||--o{ inbox_capacity_limits : "has many"
    agent_capacity_policies ||--o{ account_users : "has many"
    agent_capacity_policies }o--|| accounts : "belongs to"

    inbox_capacity_limits }o--|| agent_capacity_policies : "belongs to"
    inbox_capacity_limits }o--|| inboxes : "belongs to"

    agent_bots {
        bigint id PK
        string name
        string description
        string outgoing_url
        bigint account_id FK
        integer bot_type
        jsonb bot_config
    }

    agent_bot_inboxes {
        bigint id PK
        integer inbox_id FK
        integer agent_bot_id FK
        integer status
        integer account_id FK
    }

    automation_rules {
        bigint id PK
        bigint account_id FK
        string name
        string event_name
        jsonb conditions
        jsonb actions
        boolean active
    }

    macros {
        bigint id PK
        bigint account_id FK
        string name
        integer visibility
        bigint created_by_id FK
        jsonb actions
    }

    assignment_policies {
        bigint id PK
        bigint account_id FK
        string name
        integer assignment_order
        boolean enabled
    }

    agent_capacity_policies {
        bigint id PK
        bigint account_id FK
        string name
        text description
        jsonb exclusion_rules
    }
```

## 5. SLA/レポート/CSAT関連

```mermaid
erDiagram
    sla_policies ||--o{ applied_slas : "has many"
    sla_policies ||--o{ sla_events : "has many"
    sla_policies ||--o{ conversations : "has many"
    sla_policies }o--|| accounts : "belongs to"

    applied_slas }o--|| accounts : "belongs to"
    applied_slas }o--|| sla_policies : "belongs to"
    applied_slas }o--|| conversations : "belongs to"
    applied_slas ||--o{ sla_events : "has many"

    sla_events }o--|| applied_slas : "belongs to"
    sla_events }o--|| conversations : "belongs to"
    sla_events }o--|| accounts : "belongs to"
    sla_events }o--|| sla_policies : "belongs to"
    sla_events }o--|| inboxes : "belongs to"

    csat_survey_responses }o--|| accounts : "belongs to"
    csat_survey_responses }o--|| conversations : "belongs to"
    csat_survey_responses }o--|| messages : "belongs to"
    csat_survey_responses }o--|| contacts : "belongs to"
    csat_survey_responses }o--o| users : "assigned to optional"

    reporting_events }o--o| accounts : "belongs to optional"
    reporting_events }o--o| inboxes : "belongs to optional"
    reporting_events }o--o| users : "belongs to optional"
    reporting_events }o--o| conversations : "belongs to optional"

    sla_policies {
        bigint id PK
        string name
        float first_response_time_threshold
        float next_response_time_threshold
        float resolution_time_threshold
        boolean only_during_business_hours
        bigint account_id FK
    }

    applied_slas {
        bigint id PK
        bigint account_id FK
        bigint sla_policy_id FK
        bigint conversation_id FK
        integer sla_status
    }

    sla_events {
        bigint id PK
        bigint applied_sla_id FK
        bigint conversation_id FK
        bigint account_id FK
        bigint sla_policy_id FK
        bigint inbox_id FK
        integer event_type
        jsonb meta
    }

    csat_survey_responses {
        bigint id PK
        bigint account_id FK
        bigint conversation_id FK
        bigint message_id FK
        integer rating
        text feedback_message
        bigint contact_id FK
        bigint assigned_agent_id FK
    }

    reporting_events {
        bigint id PK
        string name
        float value
        integer account_id FK
        integer inbox_id FK
        integer user_id FK
        integer conversation_id FK
    }
```

## 6. ヘルプセンター/ポータル関連

```mermaid
erDiagram
    portals ||--o{ articles : "has many"
    portals ||--o{ categories : "has many"
    portals ||--o{ inboxes : "has many"
    portals }o--|| accounts : "belongs to"
    portals }o--o| channel_web_widgets : "belongs to optional"

    portals_members }o--|| portals : "belongs to"
    portals_members }o--|| users : "belongs to"

    articles }o--|| accounts : "belongs to"
    articles }o--|| portals : "belongs to"
    articles }o--o| categories : "belongs to optional"
    articles }o--o| folders : "belongs to optional"
    articles }o--o| users : "authored by optional"
    articles }o--o| articles : "associated with optional"
    articles ||--o{ article_embeddings : "has many"

    categories }o--|| accounts : "belongs to"
    categories }o--|| portals : "belongs to"
    categories }o--o| categories : "parent optional"
    categories }o--o| categories : "associated optional"
    categories ||--o{ articles : "has many"
    categories ||--o{ folders : "has many"
    categories ||--o{ related_categories : "has many"

    folders }o--|| accounts : "belongs to"
    folders }o--|| categories : "belongs to"
    folders ||--o{ articles : "has many"

    related_categories }o--|| categories : "belongs to"

    portals {
        bigint id PK
        integer account_id FK
        string name
        string slug
        string custom_domain
        string color
        jsonb config
        boolean archived
        bigint channel_web_widget_id FK
    }

    articles {
        bigint id PK
        integer account_id FK
        integer portal_id FK
        integer category_id FK
        integer folder_id FK
        string title
        text content
        integer status
        integer views
        bigint author_id FK
        string slug
        string locale
    }

    article_embeddings {
        bigint id PK
        bigint article_id FK
        text term
        vector embedding
    }

    categories {
        bigint id PK
        integer account_id FK
        integer portal_id FK
        string name
        text description
        integer position
        string slug
        string locale
        bigint parent_category_id FK
    }

    folders {
        bigint id PK
        integer account_id FK
        integer category_id FK
        string name
    }
```

## 7. Captain（AI機能）関連

```mermaid
erDiagram
    captain_assistants ||--o{ captain_assistant_responses : "has many"
    captain_assistants ||--o{ captain_documents : "has many"
    captain_assistants ||--o{ captain_inboxes : "has many"
    captain_assistants ||--o{ captain_scenarios : "has many"
    captain_assistants }o--|| accounts : "belongs to"

    captain_assistant_responses }o--|| captain_assistants : "belongs to"
    captain_assistant_responses }o--|| accounts : "belongs to"

    captain_documents }o--|| captain_assistants : "belongs to"
    captain_documents }o--|| accounts : "belongs to"

    captain_inboxes }o--|| captain_assistants : "belongs to"
    captain_inboxes }o--|| inboxes : "belongs to"

    captain_scenarios }o--|| captain_assistants : "belongs to"
    captain_scenarios }o--|| accounts : "belongs to"

    captain_custom_tools }o--|| accounts : "belongs to"

    copilot_threads ||--o{ copilot_messages : "has many"
    copilot_threads }o--|| users : "belongs to"
    copilot_threads }o--|| accounts : "belongs to"
    copilot_threads }o--o| captain_assistants : "belongs to optional"

    copilot_messages }o--|| copilot_threads : "belongs to"
    copilot_messages }o--|| accounts : "belongs to"

    captain_assistants {
        bigint id PK
        string name
        bigint account_id FK
        string description
        jsonb config
        jsonb response_guidelines
        jsonb guardrails
    }

    captain_assistant_responses {
        bigint id PK
        string question
        text answer
        vector embedding
        bigint assistant_id FK
        bigint account_id FK
        integer status
    }

    captain_documents {
        bigint id PK
        string name
        string external_link
        text content
        bigint assistant_id FK
        bigint account_id FK
        integer status
        jsonb metadata
    }

    captain_inboxes {
        bigint id PK
        bigint captain_assistant_id FK
        bigint inbox_id FK
    }

    captain_scenarios {
        bigint id PK
        string title
        text description
        text instruction
        jsonb tools
        boolean enabled
        bigint assistant_id FK
        bigint account_id FK
    }

    captain_custom_tools {
        bigint id PK
        bigint account_id FK
        string slug
        string title
        string http_method
        text endpoint_url
        string auth_type
        jsonb param_schema
        boolean enabled
    }

    copilot_threads {
        bigint id PK
        string title
        bigint user_id FK
        bigint account_id FK
        integer assistant_id FK
    }

    copilot_messages {
        bigint id PK
        bigint copilot_thread_id FK
        bigint account_id FK
        jsonb message
        integer message_type
    }
```

## 8. 通知/Webhook/その他関連

```mermaid
erDiagram
    notifications }o--|| accounts : "belongs to"
    notifications }o--|| users : "belongs to"

    notification_settings }o--o| accounts : "belongs to optional"
    notification_settings }o--o| users : "belongs to optional"

    notification_subscriptions }o--|| users : "belongs to"

    webhooks }o--o| accounts : "belongs to optional"
    webhooks }o--o| inboxes : "belongs to optional"

    access_tokens }o--o| users : "owned by polymorphic"

    audits }o--o| users : "by optional"

    conversation_participants }o--|| accounts : "belongs to"
    conversation_participants }o--|| users : "belongs to"
    conversation_participants }o--|| conversations : "belongs to"

    mentions }o--|| accounts : "belongs to"
    mentions }o--|| users : "belongs to"
    mentions }o--|| conversations : "belongs to"

    notes }o--|| accounts : "belongs to"
    notes }o--|| contacts : "belongs to"
    notes }o--o| users : "by optional"

    working_hours }o--o| inboxes : "belongs to optional"
    working_hours }o--o| accounts : "belongs to optional"

    leaves }o--|| accounts : "belongs to"
    leaves }o--|| users : "belongs to"
    leaves }o--o| users : "approved by optional"

    custom_attribute_definitions }o--o| accounts : "belongs to optional"

    custom_filters }o--|| accounts : "belongs to"
    custom_filters }o--|| users : "belongs to"

    data_imports }o--|| accounts : "belongs to"

    notifications {
        bigint id PK
        bigint account_id FK
        bigint user_id FK
        integer notification_type
        string primary_actor_type
        bigint primary_actor_id
        datetime read_at
        datetime snoozed_until
    }

    notification_settings {
        bigint id PK
        integer account_id FK
        integer user_id FK
        integer email_flags
        integer push_flags
    }

    webhooks {
        bigint id PK
        integer account_id FK
        integer inbox_id FK
        text url
        integer webhook_type
        jsonb subscriptions
        string name
    }

    access_tokens {
        bigint id PK
        string owner_type
        bigint owner_id
        string token
    }

    working_hours {
        bigint id PK
        bigint inbox_id FK
        bigint account_id FK
        integer day_of_week
        boolean closed_all_day
        integer open_hour
        integer close_hour
    }

    leaves {
        bigint id PK
        bigint account_id FK
        bigint user_id FK
        date start_date
        date end_date
        integer leave_type
        integer status
        bigint approved_by_id FK
    }
```
