# ER図

本ドキュメントはFizzyプロジェクトのエンティティ関連図（ER図）をmermaid形式で記載しています。

## 全体ER図

テーブル数が多いため、ドメイン別に分割して記載します。

## 1. 認証・アカウント管理

```mermaid
erDiagram
    accounts ||--o{ users : "has many"
    accounts ||--o| account_cancellations : "has one"
    accounts ||--o{ account_join_codes : "has many"
    accounts ||--o{ account_imports : "has many"
    accounts ||--o{ boards : "has many"
    accounts ||--o{ tags : "has many"
    accounts ||--o{ exports : "has many"
    accounts ||--o{ entropies : "has many"

    identities ||--o{ users : "has many"
    identities ||--o{ sessions : "has many"
    identities ||--o{ magic_links : "has many"
    identities ||--o{ identity_access_tokens : "has many"
    identities ||--o{ account_imports : "initiated by"

    users ||--o| user_settings : "has one"
    users ||--o{ accesses : "has many"
    users ||--o{ push_subscriptions : "has many"
    users ||--o{ filters : "has many"
    users ||--o{ notifications : "receives"
    users ||--o{ notification_bundles : "has many"

    accounts {
        uuid id PK
        string name
        bigint external_account_id UK
        bigint cards_count
        datetime created_at
        datetime updated_at
    }

    identities {
        uuid id PK
        string email_address UK
        boolean staff
        datetime created_at
        datetime updated_at
    }

    users {
        uuid id PK
        uuid account_id FK
        uuid identity_id FK
        string name
        string role
        boolean active
        datetime verified_at
        datetime created_at
        datetime updated_at
    }

    sessions {
        uuid id PK
        uuid identity_id FK
        string ip_address
        string user_agent
        datetime created_at
        datetime updated_at
    }

    magic_links {
        uuid id PK
        uuid identity_id FK
        string code UK
        integer purpose
        datetime expires_at
        datetime created_at
        datetime updated_at
    }

    identity_access_tokens {
        uuid id PK
        uuid identity_id FK
        string token
        string permission
        text description
        datetime created_at
        datetime updated_at
    }

    account_cancellations {
        uuid id PK
        uuid account_id FK
        uuid initiated_by_id FK
        datetime created_at
        datetime updated_at
    }

    account_join_codes {
        uuid id PK
        uuid account_id FK
        string code
        bigint usage_count
        bigint usage_limit
        datetime created_at
        datetime updated_at
    }

    account_imports {
        uuid id PK
        uuid account_id FK
        uuid identity_id FK
        string status
        string failure_reason
        datetime completed_at
        datetime created_at
        datetime updated_at
    }

    user_settings {
        uuid id PK
        uuid account_id FK
        uuid user_id FK
        integer bundle_email_frequency
        string timezone_name
        datetime created_at
        datetime updated_at
    }
```

## 2. ボード・カード管理

```mermaid
erDiagram
    boards ||--o{ columns : "has many"
    boards ||--o{ cards : "has many"
    boards ||--o{ accesses : "has many"
    boards ||--o{ events : "has many"
    boards ||--o{ webhooks : "has many"
    boards ||--o| board_publications : "has one"
    boards }o--|| users : "created by"

    columns ||--o{ cards : "has many"

    cards ||--o{ comments : "has many"
    cards ||--o{ steps : "has many"
    cards ||--o{ taggings : "has many"
    cards ||--o{ assignments : "has many"
    cards ||--o| closures : "has one"
    cards ||--o| card_activity_spikes : "has one"
    cards ||--o| card_goldnesses : "has one"
    cards ||--o| card_not_nows : "has one"
    cards ||--o{ watches : "has many"
    cards ||--o{ pins : "has many"
    cards ||--o{ reactions : "has many"
    cards ||--o{ notifications : "triggers"
    cards }o--|| users : "created by"

    comments ||--o{ reactions : "has many"
    comments }o--|| users : "created by"

    tags ||--o{ taggings : "has many"

    assignments }o--|| users : "assignee"
    assignments }o--|| users : "assigner"

    boards {
        uuid id PK
        uuid account_id FK
        uuid creator_id FK
        string name
        boolean all_access
        datetime created_at
        datetime updated_at
    }

    board_publications {
        uuid id PK
        uuid account_id FK
        uuid board_id FK
        string key UK
        datetime created_at
        datetime updated_at
    }

    columns {
        uuid id PK
        uuid account_id FK
        uuid board_id FK
        string name
        string color
        integer position
        datetime created_at
        datetime updated_at
    }

    cards {
        uuid id PK
        uuid account_id FK
        uuid board_id FK
        uuid column_id FK
        uuid creator_id FK
        bigint number
        string title
        string status
        date due_on
        datetime last_active_at
        datetime created_at
        datetime updated_at
    }

    comments {
        uuid id PK
        uuid account_id FK
        uuid card_id FK
        uuid creator_id FK
        datetime created_at
        datetime updated_at
    }

    steps {
        uuid id PK
        uuid account_id FK
        uuid card_id FK
        text content
        boolean completed
        datetime created_at
        datetime updated_at
    }

    tags {
        uuid id PK
        uuid account_id FK
        string title
        datetime created_at
        datetime updated_at
    }

    taggings {
        uuid id PK
        uuid account_id FK
        uuid card_id FK
        uuid tag_id FK
        datetime created_at
        datetime updated_at
    }

    assignments {
        uuid id PK
        uuid account_id FK
        uuid card_id FK
        uuid assignee_id FK
        uuid assigner_id FK
        datetime created_at
        datetime updated_at
    }

    closures {
        uuid id PK
        uuid account_id FK
        uuid card_id FK
        uuid user_id FK
        datetime created_at
        datetime updated_at
    }

    card_activity_spikes {
        uuid id PK
        uuid account_id FK
        uuid card_id FK
        datetime created_at
        datetime updated_at
    }

    card_goldnesses {
        uuid id PK
        uuid account_id FK
        uuid card_id FK
        datetime created_at
        datetime updated_at
    }

    card_not_nows {
        uuid id PK
        uuid account_id FK
        uuid card_id FK
        uuid user_id FK
        datetime created_at
        datetime updated_at
    }
```

## 3. ユーザーアクション・通知

```mermaid
erDiagram
    accesses }o--|| boards : "belongs to"
    accesses }o--|| users : "belongs to"

    watches }o--|| cards : "belongs to"
    watches }o--|| users : "belongs to"

    pins }o--|| cards : "belongs to"
    pins }o--|| users : "belongs to"

    mentions }o--|| users : "mentioner"
    mentions }o--|| users : "mentionee"

    reactions }o--|| users : "reacter"

    notifications }o--|| users : "recipient"
    notifications }o--|| users : "creator"
    notifications }o--|| cards : "belongs to"

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

    accesses {
        uuid id PK
        uuid account_id FK
        uuid board_id FK
        uuid user_id FK
        string involvement
        datetime accessed_at
        datetime created_at
        datetime updated_at
    }

    watches {
        uuid id PK
        uuid account_id FK
        uuid card_id FK
        uuid user_id FK
        boolean watching
        datetime created_at
        datetime updated_at
    }

    pins {
        uuid id PK
        uuid account_id FK
        uuid card_id FK
        uuid user_id FK
        datetime created_at
        datetime updated_at
    }

    mentions {
        uuid id PK
        uuid account_id FK
        uuid mentioner_id FK
        uuid mentionee_id FK
        string source_type
        uuid source_id
        datetime created_at
        datetime updated_at
    }

    reactions {
        uuid id PK
        uuid account_id FK
        string reactable_type
        uuid reactable_id
        uuid reacter_id FK
        string content
        datetime created_at
        datetime updated_at
    }

    notifications {
        uuid id PK
        uuid account_id FK
        uuid user_id FK
        uuid creator_id FK
        uuid card_id FK
        string source_type
        uuid source_id
        datetime read_at
        integer unread_count
        datetime created_at
        datetime updated_at
    }

    notification_bundles {
        uuid id PK
        uuid account_id FK
        uuid user_id FK
        integer status
        datetime starts_at
        datetime ends_at
        datetime created_at
        datetime updated_at
    }
```

## 4. イベント・Webhook

```mermaid
erDiagram
    events }o--|| boards : "belongs to"
    events }o--|| users : "created by"
    events ||--o{ webhook_deliveries : "has many"

    webhooks }o--|| boards : "belongs to"
    webhooks ||--o{ webhook_deliveries : "has many"
    webhooks ||--o| webhook_delinquency_trackers : "has one"

    events {
        uuid id PK
        uuid account_id FK
        uuid board_id FK
        uuid creator_id FK
        string eventable_type
        uuid eventable_id
        string action
        json particulars
        datetime created_at
        datetime updated_at
    }

    webhooks {
        uuid id PK
        uuid account_id FK
        uuid board_id FK
        string name
        text url
        string signing_secret
        text subscribed_actions
        boolean active
        datetime created_at
        datetime updated_at
    }

    webhook_deliveries {
        uuid id PK
        uuid account_id FK
        uuid webhook_id FK
        uuid event_id FK
        string state
        text request
        text response
        datetime created_at
        datetime updated_at
    }

    webhook_delinquency_trackers {
        uuid id PK
        uuid account_id FK
        uuid webhook_id FK
        integer consecutive_failures_count
        datetime first_failure_at
        datetime created_at
        datetime updated_at
    }
```

## 5. フィルター

```mermaid
erDiagram
    filters }o--|| users : "created by"
    filters ||--o{ assignees_filters : "has many"
    filters ||--o{ assigners_filters : "has many"
    filters ||--o{ boards_filters : "has many"
    filters ||--o{ closers_filters : "has many"
    filters ||--o{ creators_filters : "has many"
    filters ||--o{ filters_tags : "has many"

    assignees_filters }o--|| users : "assignee"
    assigners_filters }o--|| users : "assigner"
    boards_filters }o--|| boards : "board"
    closers_filters }o--|| users : "closer"
    creators_filters }o--|| users : "creator"
    filters_tags }o--|| tags : "tag"

    filters {
        uuid id PK
        uuid account_id FK
        uuid creator_id FK
        string params_digest
        json fields
        datetime created_at
        datetime updated_at
    }

    assignees_filters {
        uuid assignee_id FK
        uuid filter_id FK
    }

    assigners_filters {
        uuid assigner_id FK
        uuid filter_id FK
    }

    boards_filters {
        uuid board_id FK
        uuid filter_id FK
    }

    closers_filters {
        uuid closer_id FK
        uuid filter_id FK
    }

    creators_filters {
        uuid creator_id FK
        uuid filter_id FK
    }

    filters_tags {
        uuid filter_id FK
        uuid tag_id FK
    }
```

## 6. 検索・ストレージ

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

    storage_entries }o--|| accounts : "belongs to"
    storage_entries }o--o| boards : "optionally belongs to"
    storage_entries }o--o| users : "optionally belongs to"
    storage_entries }o--o| active_storage_blobs : "optionally belongs to"

    storage_totals ||--o{ storage_entries : "tracks"

    active_storage_blobs ||--o{ active_storage_attachments : "has many"
    active_storage_blobs ||--o{ active_storage_variant_records : "has many"

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

    search_queries {
        uuid id PK
        uuid account_id FK
        uuid user_id FK
        string terms
        datetime created_at
        datetime updated_at
    }

    search_records {
        uuid id PK
        uuid account_id FK
        string account_key
        uuid board_id FK
        uuid card_id FK
        string searchable_type
        uuid searchable_id
        string title
        text content
        datetime created_at
    }

    storage_entries {
        uuid id PK
        uuid account_id FK
        uuid board_id FK
        uuid user_id FK
        uuid blob_id FK
        string recordable_type
        uuid recordable_id
        string operation
        bigint delta
        string request_id
        datetime created_at
    }

    storage_totals {
        uuid id PK
        string owner_type
        uuid owner_id
        bigint bytes_stored
        uuid last_entry_id FK
        datetime created_at
        datetime updated_at
    }

    active_storage_blobs {
        uuid id PK
        uuid account_id FK
        string key UK
        string filename
        string content_type
        text metadata
        bigint byte_size
        string checksum
        string service_name
        datetime created_at
    }

    active_storage_attachments {
        uuid id PK
        uuid account_id FK
        string name
        string record_type
        uuid record_id
        uuid blob_id FK
        datetime created_at
    }

    active_storage_variant_records {
        uuid id PK
        uuid account_id FK
        uuid blob_id FK
        string variation_digest
    }

    action_text_rich_texts {
        uuid id PK
        uuid account_id FK
        string name
        longtext body
        string record_type
        uuid record_id
        datetime created_at
        datetime updated_at
    }
```

## 7. エントロピー（自動延期設定）

```mermaid
erDiagram
    entropies }o--|| accounts : "can belong to"
    entropies }o--|| boards : "can belong to"

    entropies {
        uuid id PK
        uuid account_id FK
        string container_type
        uuid container_id
        bigint auto_postpone_period
        datetime created_at
        datetime updated_at
    }
```

## 補足

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

以下のテーブルはポリモーフィック関連を使用しています：

- **events**: `eventable_type` / `eventable_id` - Card, Comment等を参照
- **mentions**: `source_type` / `source_id` - Card, Comment等を参照
- **notifications**: `source_type` / `source_id` - Event等を参照
- **reactions**: `reactable_type` / `reactable_id` - Card, Comment を参照
- **storage_entries**: `recordable_type` / `recordable_id` - 各種モデルを参照
- **storage_totals**: `owner_type` / `owner_id` - Account, Board を参照
- **entropies**: `container_type` / `container_id` - Account, Board を参照
- **action_text_rich_texts**: `record_type` / `record_id` - Card, Comment, Board等を参照
- **active_storage_attachments**: `record_type` / `record_id` - User, Card等を参照
- **search_records_N**: `searchable_type` / `searchable_id` - Card, Comment等を参照

### シャーディング

`search_records` テーブルはパフォーマンス向上のため16シャード（`search_records_0`〜`search_records_15`）に分割されています。
