# ER図

Ghost CMSのデータベース構造をEntity-Relationship図で表現しています。

## 全体概要

Ghostのデータベースは以下の主要ドメインで構成されています：

1. **コンテンツ管理** - 投稿、タグ、コレクション
2. **ユーザー/スタッフ管理** - スタッフアカウント、ロール、パーミッション
3. **メンバー管理** - 購読者、ラベル、ニュースレター購読
4. **課金/Stripe連携** - ティア、オファー、サブスクリプション
5. **メール配信** - ニュースレター、メールバッチ、受信者

## コンテンツ管理

```mermaid
erDiagram
    posts {
        string id PK
        string uuid UK
        string title
        string slug
        text mobiledoc
        text lexical
        text html
        string type
        string status
        string visibility
        datetime published_at
        string newsletter_id FK
    }

    posts_meta {
        string id PK
        string post_id FK,UK
        string og_image
        string og_title
        string meta_title
        string meta_description
    }

    tags {
        string id PK
        string name
        string slug UK
        text description
        string visibility
        string parent_id
    }

    posts_tags {
        string id PK
        string post_id FK
        string tag_id FK
        integer sort_order
    }

    posts_authors {
        string id PK
        string post_id FK
        string author_id FK
        integer sort_order
    }

    collections {
        string id PK
        string title
        string slug UK
        string type
        text filter
    }

    collections_posts {
        string id PK
        string collection_id FK
        string post_id FK
        integer sort_order
    }

    snippets {
        string id PK
        string name UK
        text mobiledoc
        text lexical
    }

    post_revisions {
        string id PK
        string post_id FK
        text lexical
        string author_id FK
        string title
        string post_status
    }

    mobiledoc_revisions {
        string id PK
        string post_id FK
        text mobiledoc
        bigint created_at_ts
    }

    posts ||--o| posts_meta : "has"
    posts ||--o{ posts_tags : "has"
    posts ||--o{ posts_authors : "has"
    posts ||--o{ post_revisions : "has"
    posts ||--o{ mobiledoc_revisions : "has"
    posts ||--o{ collections_posts : "belongs to"
    tags ||--o{ posts_tags : "has"
    tags ||--o| tags : "parent"
    collections ||--o{ collections_posts : "contains"
```

## ユーザー/スタッフ管理

```mermaid
erDiagram
    users {
        string id PK
        string name
        string slug UK
        string password
        string email UK
        string status
        string visibility
        datetime last_seen
    }

    roles {
        string id PK
        string name UK
        string description
    }

    roles_users {
        string id PK
        string role_id FK
        string user_id FK
    }

    permissions {
        string id PK
        string name UK
        string object_type
        string action_type
        string object_id
    }

    permissions_users {
        string id PK
        string user_id FK
        string permission_id FK
    }

    permissions_roles {
        string id PK
        string role_id FK
        string permission_id FK
    }

    invites {
        string id PK
        string role_id FK
        string status
        string token UK
        string email UK
        bigint expires
    }

    sessions {
        string id PK
        string session_id UK
        string user_id FK
        string session_data
    }

    users ||--o{ roles_users : "has"
    roles ||--o{ roles_users : "assigned to"
    users ||--o{ permissions_users : "has"
    permissions ||--o{ permissions_users : "granted to"
    roles ||--o{ permissions_roles : "has"
    permissions ||--o{ permissions_roles : "assigned to"
    roles ||--o{ invites : "for"
    users ||--o{ sessions : "has"
    users ||--o{ posts_authors : "authored"
```

## メンバー管理

```mermaid
erDiagram
    members {
        string id PK
        string uuid UK
        string transient_id UK
        string email UK
        string status
        string name
        boolean email_disabled
        integer email_open_rate
        datetime last_seen_at
    }

    labels {
        string id PK
        string name UK
        string slug UK
    }

    members_labels {
        string id PK
        string member_id FK
        string label_id FK
        integer sort_order
    }

    newsletters {
        string id PK
        string uuid UK
        string name UK
        string slug UK
        string status
        string visibility
    }

    members_newsletters {
        string id PK
        string member_id FK
        string newsletter_id FK
    }

    members_created_events {
        string id PK
        string member_id FK
        string attribution_type
        string attribution_url
        string referrer_source
        string source
        datetime created_at
    }

    members_login_events {
        string id PK
        string member_id FK
        datetime created_at
    }

    members_status_events {
        string id PK
        string member_id FK
        string from_status
        string to_status
        datetime created_at
    }

    members_email_change_events {
        string id PK
        string member_id FK
        string to_email
        string from_email
        datetime created_at
    }

    members_subscribe_events {
        string id PK
        string member_id FK
        string newsletter_id FK
        boolean subscribed
        string source
        datetime created_at
    }

    members ||--o{ members_labels : "has"
    labels ||--o{ members_labels : "applied to"
    members ||--o{ members_newsletters : "subscribes"
    newsletters ||--o{ members_newsletters : "subscribed by"
    members ||--o{ members_created_events : "has"
    members ||--o{ members_login_events : "has"
    members ||--o{ members_status_events : "has"
    members ||--o{ members_email_change_events : "has"
    members ||--o{ members_subscribe_events : "has"
    newsletters ||--o{ members_subscribe_events : "for"
```

## 課金/Stripe連携

```mermaid
erDiagram
    products {
        string id PK
        string name
        string slug UK
        boolean active
        string visibility
        string type
        integer trial_days
        integer monthly_price
        integer yearly_price
    }

    offers {
        string id PK
        string name UK
        string code UK
        string product_id FK
        string stripe_coupon_id UK
        string interval
        string discount_type
        integer discount_amount
        string duration
    }

    benefits {
        string id PK
        string name
        string slug UK
    }

    products_benefits {
        string id PK
        string product_id FK
        string benefit_id FK
        integer sort_order
    }

    members_products {
        string id PK
        string member_id FK
        string product_id FK
        integer sort_order
        datetime expiry_at
    }

    posts_products {
        string id PK
        string post_id FK
        string product_id FK
        integer sort_order
    }

    subscriptions {
        string id PK
        string type
        string status
        string member_id FK
        string tier_id FK
        string cadence
        string currency
        integer amount
        string payment_provider
        string offer_id FK
        datetime expires_at
    }

    members_stripe_customers {
        string id PK
        string member_id FK
        string customer_id UK
        string name
        string email
    }

    members_stripe_customers_subscriptions {
        string id PK
        string customer_id FK
        string ghost_subscription_id FK
        string subscription_id UK
        string stripe_price_id FK
        string status
        boolean cancel_at_period_end
        datetime current_period_end
        integer mrr
        string offer_id FK
    }

    stripe_products {
        string id PK
        string product_id FK
        string stripe_product_id UK
    }

    stripe_prices {
        string id PK
        string stripe_price_id UK
        string stripe_product_id FK
        boolean active
        string currency
        integer amount
        string type
        string interval
    }

    offer_redemptions {
        string id PK
        string offer_id FK
        string member_id FK
        string subscription_id FK
        datetime created_at
    }

    members_payment_events {
        string id PK
        string member_id FK
        integer amount
        string currency
        string source
        datetime created_at
    }

    members_cancel_events {
        string id PK
        string member_id FK
        string from_plan
        datetime created_at
    }

    members_paid_subscription_events {
        string id PK
        string member_id FK
        string subscription_id
        string from_plan
        string to_plan
        integer mrr_delta
        datetime created_at
    }

    members_product_events {
        string id PK
        string member_id FK
        string product_id FK
        string action
        datetime created_at
    }

    members_subscription_created_events {
        string id PK
        string member_id FK
        string subscription_id FK
        string attribution_type
        string referrer_source
        datetime created_at
    }

    donation_payment_events {
        string id PK
        string member_id FK
        string email
        integer amount
        string currency
        string attribution_type
        datetime created_at
    }

    products ||--o{ offers : "has"
    products ||--o{ products_benefits : "has"
    benefits ||--o{ products_benefits : "belongs to"
    members ||--o{ members_products : "has"
    products ||--o{ members_products : "subscribed by"
    posts ||--o{ posts_products : "restricted by"
    products ||--o{ posts_products : "restricts"
    members ||--o{ subscriptions : "has"
    products ||--o{ subscriptions : "tier for"
    offers ||--o{ subscriptions : "applied to"
    members ||--o{ members_stripe_customers : "has"
    members_stripe_customers ||--o{ members_stripe_customers_subscriptions : "has"
    subscriptions ||--o{ members_stripe_customers_subscriptions : "linked to"
    offers ||--o{ members_stripe_customers_subscriptions : "applied to"
    products ||--o{ stripe_products : "mapped to"
    stripe_products ||--o{ stripe_prices : "has"
    offers ||--o{ offer_redemptions : "redeemed as"
    members ||--o{ offer_redemptions : "redeemed by"
    members ||--o{ members_payment_events : "has"
    members ||--o{ members_cancel_events : "has"
    members ||--o{ members_paid_subscription_events : "has"
    members ||--o{ members_product_events : "has"
    members ||--o{ members_subscription_created_events : "has"
    members ||--o{ donation_payment_events : "made"
```

## メール配信

```mermaid
erDiagram
    newsletters {
        string id PK
        string uuid UK
        string name UK
        string slug UK
        string sender_name
        string sender_email
        string status
        boolean feedback_enabled
    }

    emails {
        string id PK
        string post_id FK,UK
        string uuid
        string status
        text recipient_filter
        integer email_count
        integer delivered_count
        integer opened_count
        integer failed_count
        string subject
        text html
        string newsletter_id FK
        datetime submitted_at
    }

    email_batches {
        string id PK
        string email_id FK
        string provider_id
        string status
        text member_segment
        integer error_status_code
    }

    email_recipients {
        string id PK
        string email_id FK
        string member_id FK
        string batch_id FK
        datetime processed_at
        datetime delivered_at
        datetime opened_at
        datetime failed_at
        string member_email
    }

    email_recipient_failures {
        string id PK
        string email_id FK
        string member_id
        string email_recipient_id FK
        integer code
        string message
        string severity
        datetime failed_at
    }

    suppressions {
        string id PK
        string email UK
        string email_id FK
        string reason
        datetime created_at
    }

    email_spam_complaint_events {
        string id PK
        string member_id FK
        string email_id FK
        string email_address
        datetime created_at
    }

    tokens {
        string id PK
        string token
        string uuid UK
        string data
        integer used_count
    }

    automated_emails {
        string id PK
        string status
        string name UK
        string slug UK
        string subject
        text lexical
        string sender_name
        string sender_email
    }

    automated_email_recipients {
        string id PK
        string automated_email_id FK
        string member_id FK
        string member_email
    }

    newsletters ||--o{ emails : "sends"
    posts ||--o| emails : "sent as"
    emails ||--o{ email_batches : "has"
    emails ||--o{ email_recipients : "has"
    email_batches ||--o{ email_recipients : "contains"
    email_recipients ||--o{ email_recipient_failures : "has"
    emails ||--o{ email_recipient_failures : "has"
    emails ||--o{ suppressions : "caused"
    members ||--o{ email_spam_complaint_events : "reported"
    emails ||--o{ email_spam_complaint_events : "received"
    automated_emails ||--o{ automated_email_recipients : "sent to"
```

## コメントとエンゲージメント

```mermaid
erDiagram
    comments {
        string id PK
        string post_id FK
        string member_id FK
        string parent_id FK
        string in_reply_to_id FK
        string status
        text html
        datetime edited_at
    }

    comment_likes {
        string id PK
        string comment_id FK
        string member_id FK
        datetime created_at
    }

    comment_reports {
        string id PK
        string comment_id FK
        string member_id FK
        datetime created_at
    }

    members_feedback {
        string id PK
        integer score
        string member_id FK
        string post_id FK
        datetime created_at
    }

    members_click_events {
        string id PK
        string member_id FK
        string redirect_id FK
        datetime created_at
    }

    redirects {
        string id PK
        string from
        string to
        string post_id FK
    }

    posts ||--o{ comments : "has"
    members ||--o{ comments : "wrote"
    comments ||--o| comments : "parent"
    comments ||--o| comments : "reply to"
    comments ||--o{ comment_likes : "has"
    members ||--o{ comment_likes : "liked"
    comments ||--o{ comment_reports : "has"
    members ||--o{ comment_reports : "reported"
    posts ||--o{ members_feedback : "received"
    members ||--o{ members_feedback : "gave"
    redirects ||--o{ members_click_events : "tracked"
    members ||--o{ members_click_events : "clicked"
    posts ||--o{ redirects : "has"
```

## その他のテーブル

```mermaid
erDiagram
    settings {
        string id PK
        string group
        string key UK
        text value
        string type
        string flags
    }

    integrations {
        string id PK
        string type
        string name
        string slug UK
        string description
    }

    webhooks {
        string id PK
        string event
        string target_url
        string name
        string secret
        string api_version
        string integration_id FK
        datetime last_triggered_at
    }

    api_keys {
        string id PK
        string type
        string secret UK
        string role_id FK
        string integration_id FK
        string user_id FK
        datetime last_seen_at
    }

    brute {
        string key PK
        bigint firstRequest
        bigint lastRequest
        bigint lifetime
        integer count
    }

    actions {
        string id PK
        string resource_id
        string resource_type
        string actor_id
        string actor_type
        string event
        text context
        datetime created_at
    }

    jobs {
        string id PK
        string name UK
        string status
        datetime started_at
        datetime finished_at
        string metadata
    }

    custom_theme_settings {
        string id PK
        string theme
        string key
        string type
        text value
    }

    mentions {
        string id PK
        string source
        string source_title
        string target
        string resource_id
        string resource_type
        boolean deleted
        boolean verified
    }

    milestones {
        string id PK
        string type
        integer value
        string currency
        datetime email_sent_at
    }

    recommendations {
        string id PK
        string url
        string title
        string excerpt
        boolean one_click_subscribe
    }

    recommendation_click_events {
        string id PK
        string recommendation_id FK
        string member_id FK
        datetime created_at
    }

    recommendation_subscribe_events {
        string id PK
        string recommendation_id FK
        string member_id FK
        datetime created_at
    }

    outbox {
        string id PK
        string event_type
        string status
        text payload
        integer retry_count
        datetime last_retry_at
    }

    integrations ||--o{ webhooks : "has"
    integrations ||--o{ api_keys : "has"
    roles ||--o{ api_keys : "for"
    users ||--o{ api_keys : "owns"
    recommendations ||--o{ recommendation_click_events : "has"
    members ||--o{ recommendation_click_events : "clicked"
    recommendations ||--o{ recommendation_subscribe_events : "has"
    members ||--o{ recommendation_subscribe_events : "subscribed via"
```

## 主要なリレーションシップの説明

### 投稿 (posts) を中心としたリレーション
- `posts` は `posts_meta` と1対1の関係 (SEO/OGP情報)
- `posts` は `users` と多対多の関係 (`posts_authors` 経由、複数著者対応)
- `posts` は `tags` と多対多の関係 (`posts_tags` 経由)
- `posts` は `products` と多対多の関係 (`posts_products` 経由、アクセス制御)
- `posts` は `collections` と多対多の関係 (`collections_posts` 経由)
- `posts` は `newsletters` と多対1の関係 (メール配信用)

### メンバー (members) を中心としたリレーション
- `members` は `labels` と多対多の関係 (`members_labels` 経由)
- `members` は `newsletters` と多対多の関係 (`members_newsletters` 経由)
- `members` は `products` と多対多の関係 (`members_products` 経由、購読ティア)
- `members` は `members_stripe_customers` と1対多の関係 (Stripe連携)
- `members` は各種イベントテーブルと1対多の関係 (履歴追跡)

### 課金関連のリレーション
- `products` (ティア) は `offers` と1対多の関係
- `products` は `benefits` と多対多の関係 (`products_benefits` 経由)
- `subscriptions` は `members`, `products`, `offers` と関連
- `members_stripe_customers_subscriptions` は Stripe サブスクリプションの詳細を保持

### メール配信のリレーション
- `newsletters` は `emails` と1対多の関係
- `emails` は `email_batches` と1対多の関係 (バッチ送信)
- `email_batches` は `email_recipients` と1対多の関係
- `email_recipients` は `email_recipient_failures` と1対多の関係 (エラー追跡)
