# ER図

本ドキュメントは、Giselle AIワークフローシステムのデータベースER図をMermaid形式で記載したものです。

## 全体ER図

```mermaid
erDiagram
    %% コアエンティティ
    teams {
        serial db_id PK
        text id UK
        text name
        text avatar_url
        timestamp created_at
        timestamp updated_at
        text plan
        text active_subscription_id UK
        text active_customer_id
    }

    users {
        serial db_id PK
        text id UK
        text email UK
        text display_name
        text avatar_url
    }

    supabase_user_mappings {
        integer user_db_id FK,UK
        text supabase_user_id UK
    }

    team_memberships {
        serial id PK
        integer user_db_id FK
        integer team_db_id FK
        text role
    }

    %% ワークスペース関連
    workspaces {
        serial db_id PK
        text id UK
        text name
        integer team_db_id FK
        timestamp created_at
        timestamp updated_at
        integer creator_db_id FK
        jsonb metadata
    }

    agents {
        serial db_id PK
        text id UK
        integer team_db_id FK
        text name
        text graph_url
        text workspace_id
        timestamp created_at
        timestamp updated_at
        integer creator_db_id FK
        jsonb metadata
    }

    apps {
        serial db_id PK
        text id UK
        text app_entry_node_id
        text end_node_id
        integer team_db_id FK
        integer workspace_db_id FK
        timestamp created_at
        timestamp updated_at
    }

    tasks {
        serial db_id PK
        text id UK
        integer team_db_id FK
        integer app_db_id FK
        timestamp created_at
        timestamp updated_at
    }

    %% リレーション - コア
    users ||--o{ supabase_user_mappings : "has"
    users ||--o{ team_memberships : "has"
    teams ||--o{ team_memberships : "has"
    teams ||--o{ workspaces : "has"
    teams ||--o{ agents : "has"
    teams ||--o{ apps : "has"
    teams ||--o{ tasks : "has"
    users ||--o{ workspaces : "creates"
    users ||--o{ agents : "creates"
    workspaces ||--o| apps : "has"
    apps ||--o{ tasks : "has"
```

## 認証・招待関連

```mermaid
erDiagram
    users {
        serial db_id PK
        text id UK
        text email UK
        text display_name
        text avatar_url
    }

    oauth_credentials {
        serial id PK
        integer user_id FK
        text provider
        text provider_account_id
        text access_token
        text refresh_token
        timestamp expires_at
        text token_type
        text scope
        timestamp created_at
        timestamp updated_at
    }

    invitations {
        text token UK
        integer team_db_id FK
        text email
        text role
        integer inviter_user_db_id FK
        timestamp expired_at
        timestamp created_at
        timestamp revoked_at
    }

    teams {
        serial db_id PK
        text id UK
        text name
    }

    users ||--o{ oauth_credentials : "has"
    teams ||--o{ invitations : "has"
    users ||--o{ invitations : "invites"
```

## APIキー・レート制限

```mermaid
erDiagram
    teams {
        serial db_id PK
        text id UK
        text name
    }

    users {
        serial db_id PK
        text id UK
    }

    api_keys {
        serial db_id PK
        text id UK
        integer team_db_id FK
        text label
        integer created_by_user_db_id FK
        text redacted_value
        text kdf_type
        text kdf_salt
        integer kdf_n
        integer kdf_r
        integer kdf_p
        integer kdf_key_len
        text secret_hash
        timestamp created_at
        timestamp last_used_at
        timestamp revoked_at
    }

    api_rate_limit_counters {
        integer team_db_id PK,FK
        text route_key PK
        timestamp window_start PK
        integer count
        timestamp updated_at
    }

    teams ||--o{ api_keys : "has"
    users ||--o{ api_keys : "creates"
    teams ||--o{ api_rate_limit_counters : "has"
```

## GitHubリポジトリ・ベクトル検索

```mermaid
erDiagram
    teams {
        serial db_id PK
        text id UK
    }

    github_repository_index {
        serial db_id PK
        text id UK
        text owner
        text repo
        integer team_db_id FK
        integer installation_id
        text last_ingested_commit_sha
        text status
        text error_code
        timestamp retry_after
        timestamp created_at
        timestamp updated_at
    }

    github_repository_embedding_profiles {
        integer repository_index_db_id PK,FK
        integer embedding_profile_id PK
        timestamp created_at
    }

    github_repository_content_status {
        serial db_id PK
        integer repository_index_db_id FK
        integer embedding_profile_id
        text content_type
        boolean enabled
        text status
        timestamp last_synced_at
        jsonb metadata
        text error_code
        timestamp retry_after
        timestamp updated_at
    }

    github_repository_embeddings {
        serial db_id PK
        integer repository_index_db_id FK
        integer embedding_profile_id
        integer embedding_dimensions
        text file_sha
        text path
        vector embedding
        text chunk_content
        integer chunk_index
        timestamp created_at
    }

    github_repository_pull_request_embeddings {
        serial db_id PK
        integer repository_index_db_id FK
        integer embedding_profile_id
        integer embedding_dimensions
        integer pr_number
        timestamp merged_at
        text content_type
        text content_id
        text document_key
        vector embedding
        text chunk_content
        integer chunk_index
        timestamp created_at
    }

    github_repository_issue_embeddings {
        serial db_id PK
        integer repository_index_db_id FK
        integer embedding_profile_id
        integer embedding_dimensions
        integer issue_number
        text issue_state
        text issue_state_reason
        timestamp issue_updated_at
        timestamp issue_closed_at
        text content_type
        text content_id
        text document_key
        timestamp content_created_at
        timestamp content_edited_at
        text metadata_version
        vector embedding
        text chunk_content
        integer chunk_index
        timestamp created_at
    }

    teams ||--o{ github_repository_index : "has"
    github_repository_index ||--o{ github_repository_embedding_profiles : "has"
    github_repository_index ||--o{ github_repository_content_status : "has"
    github_repository_index ||--o{ github_repository_embeddings : "has"
    github_repository_index ||--o{ github_repository_pull_request_embeddings : "has"
    github_repository_index ||--o{ github_repository_issue_embeddings : "has"
```

## ドキュメントベクトルストア

```mermaid
erDiagram
    teams {
        serial db_id PK
        text id UK
    }

    document_vector_stores {
        serial db_id PK
        text id UK
        integer team_db_id FK
        text name
        timestamp created_at
        timestamp updated_at
    }

    document_vector_store_sources {
        serial db_id PK
        text id UK
        integer document_vector_store_db_id FK
        text storage_bucket
        text storage_key
        text file_name
        integer file_size_bytes
        text file_checksum
        text upload_status
        text upload_error_code
        text ingest_status
        text ingest_error_code
        jsonb metadata
        timestamp created_at
        timestamp updated_at
        timestamp ingested_at
    }

    document_embedding_profiles {
        integer document_vector_store_db_id PK,FK
        integer embedding_profile_id PK
        timestamp created_at
    }

    document_embeddings {
        serial db_id PK
        integer document_vector_store_db_id FK
        integer document_vector_store_source_db_id FK
        integer embedding_profile_id
        integer embedding_dimensions
        text document_key
        integer chunk_index
        text chunk_content
        vector embedding
        timestamp created_at
        timestamp updated_at
    }

    teams ||--o{ document_vector_stores : "has"
    document_vector_stores ||--o{ document_vector_store_sources : "has"
    document_vector_stores ||--o{ document_embedding_profiles : "has"
    document_vector_stores ||--o{ document_embeddings : "has"
    document_vector_store_sources ||--o{ document_embeddings : "has"
```

## フロートリガー・アクト

```mermaid
erDiagram
    teams {
        serial db_id PK
        text id UK
    }

    users {
        serial db_id PK
        text id UK
    }

    flow_triggers {
        serial db_id PK
        integer team_db_id FK
        boolean staged
        text workspace_id
        text flow_trigger_id UK
        timestamp created_at
        timestamp updated_at
    }

    acts {
        serial db_id PK
        integer team_db_id FK
        integer director_db_id FK
        text sdk_workspace_id
        text sdk_flow_trigger_id
        text sdk_act_id
        timestamp created_at
        timestamp updated_at
    }

    teams ||--o{ flow_triggers : "has"
    teams ||--o{ acts : "has"
    users ||--o{ acts : "directs"
```

## エージェントアクティビティ・使用量

```mermaid
erDiagram
    teams {
        serial db_id PK
        text id UK
    }

    agents {
        serial db_id PK
        text id UK
    }

    agent_activities {
        serial db_id PK
        integer agent_db_id FK
        timestamp started_at
        timestamp ended_at
        numeric total_duration_ms
        integer usage_report_db_id FK
    }

    agent_time_usage_reports {
        serial db_id PK
        integer team_db_id FK
        numeric accumulated_duration_ms
        integer minutes_increment
        text stripe_meter_event_id
        timestamp created_at
    }

    user_seat_usage_reports {
        serial db_id PK
        integer team_db_id FK
        integer_array user_db_id_list
        text stripe_meter_event_id
        integer value
        timestamp created_at
    }

    agent_time_restrictions {
        integer team_db_id PK,FK
        timestamp created_at
    }

    agents ||--o{ agent_activities : "has"
    agent_time_usage_reports ||--o{ agent_activities : "reports"
    teams ||--o{ agent_time_usage_reports : "has"
    teams ||--o{ user_seat_usage_reports : "has"
    teams ||--o| agent_time_restrictions : "has"
```

## Stripe課金

```mermaid
erDiagram
    teams {
        serial db_id PK
        text id UK
        text plan
        text active_subscription_id
        text active_customer_id
    }

    stripe_billing_cadence_histories {
        serial db_id PK
        integer team_db_id FK
        timestamp created_at
        text id
        text customer_id
        text billing_profile_id
        text payer_type
        text billing_cycle_type
        integer billing_cycle_interval_count
        integer billing_cycle_day_of_month
        integer billing_cycle_month_of_year
        integer billing_cycle_time_hour
        integer billing_cycle_time_minute
        integer billing_cycle_time_second
        timestamp next_billing_date
        text status
        text bill_settings_id
        timestamp created
        jsonb metadata
        text lookup_key
    }

    stripe_pricing_plan_subscription_histories {
        serial db_id PK
        integer team_db_id FK
        integer billing_cadence_db_id FK
        timestamp created_at
        text id
        text billing_cadence_id
        text pricing_plan_id
        text pricing_plan_version_id
        text servicing_status
        text collection_status
        timestamp activated_at
        timestamp canceled_at
        timestamp paused_at
        timestamp will_cancel_at
        timestamp collection_current_at
        timestamp collection_past_due_at
        timestamp collection_paused_at
        timestamp collection_unpaid_at
        timestamp collection_awaiting_customer_action_at
        timestamp created
        jsonb metadata
    }

    teams ||--o{ stripe_billing_cadence_histories : "has"
    teams ||--o{ stripe_pricing_plan_subscription_histories : "has"
    stripe_billing_cadence_histories ||--o{ stripe_pricing_plan_subscription_histories : "has"
```

## データストア

```mermaid
erDiagram
    teams {
        serial db_id PK
        text id UK
    }

    data_stores {
        serial db_id PK
        text id UK
        integer team_db_id FK
        text name
        timestamp created_at
        timestamp updated_at
    }

    teams ||--o{ data_stores : "has"
```

## GitHub連携設定（非推奨）

```mermaid
erDiagram
    agents {
        serial db_id PK
        text id UK
    }

    github_integration_settings {
        serial db_id PK
        text id UK
        integer agent_db_id FK
        text repository_full_name
        text call_sign
        text event
        text flow_id
        jsonb event_node_mappings
        text next_action
    }

    agents ||--o{ github_integration_settings : "has"
```
