エンティティ一覧

erDiagram
    TENANTS ||--o{ SESSIONS : "scopes (tenant_id)"
    TENANTS ||--o{ USERS : "scopes (tenant_id)"
    USERS ||--o{ PARTICIPANTS : "enters as minedia_observer (nullable)"
    TENANTS ||--o{ ROOMS : "scopes (tenant_id)"
    TENANTS ||--o{ WEBHOOK_ENDPOINTS : "scopes (tenant_id)"
    TENANTS ||--o{ API_KEYS : "scopes (tenant_id)"
    TENANTS |o--o{ JWKS_KEYS : "scopes (tenant_id・基盤鍵は NULL)"
    USERS ||--o{ WEBHOOK_ENDPOINTS : "created_by (nullable)"
    USERS ||--o{ API_KEYS : "created_by (nullable)"
    API_KEYS ||--o{ API_KEY_EVENTS : "has_many (audit)"
    USERS ||--o{ API_KEY_EVENTS : "actor (nullable)"
    USERS ||--o{ JWKS_KEYS : "created_by (nullable)"
    ROOMS ||--o| SESSIONS : "has_one (0..1, booking)"
    ROOMS ||--o| CONNECTION_TESTS : "has_one (0..1, kind=connection_test)"
    SESSIONS ||--o{ HANDOUTS : "has_many"
    ROOMS ||--o{ PARTICIPANTS : "has_many"
    ROOMS ||--o{ RECORDINGS : "has_many"
    ROOMS ||--o{ CHAT_CHANNELS : "has_many"
    ROOMS ||--o{ ROOM_EVENTS : "has_many"
    ROOMS ||--o{ ENTRY_HISTORIES : "has_many"
    RECORDINGS ||--o{ TRANSCRIPTIONS : "has_many (G5: core)"
    RECORDINGS ||--o| PROCESSED_RECORDINGS : "has_one (0..1)"
    WEBHOOK_ENDPOINTS ||--o{ WEBHOOK_DELIVERIES : "has_many"
    CONNECTION_TESTS ||--o{ CONNECTION_TEST_EVENTS : "has_many"
    CHAT_CHANNELS ||--o{ CHAT_MESSAGES : "has_many"
    CHAT_CHANNELS ||--o{ CHAT_POSTS : "has_many"
    PARTICIPANTS ||--o{ CHAT_MESSAGES : "authors (nullable)"
    PARTICIPANTS ||--o{ CHAT_POSTS : "authors (nullable)"
    PARTICIPANTS ||--o{ ENTRY_HISTORIES : "linked (nullable)"

    TENANTS {
        bigint id PK
        string uuid UK
        string name
    }
    USERS {
        bigint id PK
        bigint tenant_id "default 0"
        string uuid UK
        string email UK
        string encrypted_password
        string name
        string role "admin/member"
        string locale
        string time_zone
        datetime deactivated_at
    }
    SESSIONS {
        bigint id PK
        bigint tenant_id "default 0"
        string uuid
        bigint room_id FK "0..1 per room (booking)"
        string external_type "ext-ref (no FK)"
        string external_id "ext-ref (no FK)"
        datetime starts_at
        datetime ends_at
        string status "scheduled/active/cancelled/closed (booking)"
    }
    ROOMS {
        bigint id PK
        bigint tenant_id "default 0"
        string uuid UK
        string kind "interview/connection_test"
        string status "open/closed"
        string livekit_session_id "= LiveKit Room SID (RM_…); UK"
        string archive_mode
        string archive_resolution
        string transcription_language "default ja-JP"
        string transcription_service "default azure"
        string stream_resolution
        integer stream_frame_rate
        string access_token UK
        text entry_notice_text "nullable"
        string entry_name_field_label "nullable"
    }
    PARTICIPANTS {
        bigint id PK
        bigint tenant_id "default 0"
        string uuid
        bigint room_id FK
        string role "moderator/observer/panelist/minedia_observer"
        bigint user_id FK "nullable (platform actor)"
        string external_type "ext-ref (no FK)"
        string external_id "ext-ref (no FK)"
        string display_name
    }
    HANDOUTS {
        bigint id PK
        bigint tenant_id "default 0"
        string uuid UK
        bigint session_id FK
        string external_type "ext-ref (no FK)"
        string external_id "ext-ref (no FK)"
        string file
        string content_type "image/video"
        integer sort
    }
    CONNECTION_TESTS {
        bigint id PK
        bigint tenant_id "default 0"
        string uuid UK
        bigint room_id FK "unique (kind=connection_test)"
        string external_type "ext-ref (no FK)"
        string external_id "ext-ref (no FK)"
        string status "created/completed/failed"
        integer dummy_stream_count
        float baseline_metrics "bitrate/loss/fps/jitter"
        float load_metrics "avg+worst x video/audio"
        json raw_data
    }
    CONNECTION_TEST_EVENTS {
        bigint id PK
        bigint tenant_id "default 0"
        bigint connection_test_id FK
        string action_type
        string browser_name
        string platform_name
        string device_name
        json data
    }
    RECORDINGS {
        bigint id PK
        bigint tenant_id "default 0"
        string uuid
        bigint room_id FK
        string provider_archive_id
        string status
        string output_mode
        bigint size
        decimal duration_sec
        string storage_key
    }
    TRANSCRIPTIONS {
        bigint id PK
        bigint tenant_id "default 0"
        string uuid UK
        bigint recording_id FK
        string provider "eleven_labs/openai/azure/anyenv/rimo"
        string provider_transcription_id
        string status
        json result_json "行内格納 (As-Is 踏襲)"
        string language
        string kind "transcription/translation"
    }
    PROCESSED_RECORDINGS {
        bigint id PK
        bigint tenant_id "default 0"
        string uuid UK
        bigint recording_id FK "unique (1:1)"
        string source "trim/upload"
        decimal trim_start_sec "nullable"
        decimal trim_end_sec "nullable"
        string status "pending/processing/available/failed"
        string failure_reason "nullable"
        string storage_key
        bigint size
        decimal duration_sec
    }
    CHAT_CHANNELS {
        bigint id PK
        bigint tenant_id "default 0"
        string uuid
        bigint room_id FK
        string visibility "public/private/observer_only"
    }
    CHAT_MESSAGES {
        bigint id PK
        bigint tenant_id "default 0"
        string uuid
        bigint chat_channel_id FK
        bigint participant_id FK "nullable"
        integer content_type
        text text
        string sender_ref
        string sender_name
    }
    CHAT_POSTS {
        bigint id PK
        bigint tenant_id "default 0"
        string uuid
        bigint chat_channel_id FK
        bigint participant_id FK "nullable"
        string file
    }
    ROOM_EVENTS {
        bigint id PK
        bigint tenant_id "default 0"
        bigint room_id FK
        string event_type
        json payload
    }
    ENTRY_HISTORIES {
        bigint id PK
        bigint tenant_id "default 0"
        bigint room_id FK
        bigint participant_id FK "nullable"
        string name
        string role
        string ip_address
    }
    WEBHOOK_DELIVERIES {
        bigint id PK
        bigint tenant_id "default 0"
        bigint webhook_endpoint_id FK
        string event_id
        string event_type
        string status "pending/delivered/failed/exhausted"
        integer attempt_count
        datetime last_attempt_at
        integer response_code
    }
    WEBHOOK_ENDPOINTS {
        bigint id PK
        bigint tenant_id FK "scope (配信スコープ)"
        string uuid UK
        bigint user_id FK "nullable (audit)"
        string url
        string secret
        json subscribed_types
        boolean enabled
        string description
    }
    API_KEYS {
        bigint id PK
        bigint tenant_id FK "scope"
        string uuid UK
        bigint user_id FK "nullable (audit)"
        string key_id UK
        string secret_hash "平文非保持・ハッシュのみ"
        json scopes "任意・MT-ready"
        boolean active
        datetime rotated_at
    }
    API_KEY_EVENTS {
        bigint id PK
        bigint tenant_id "default 0"
        bigint api_key_id FK
        string event_type "issued/revoked/updated"
        bigint user_id FK "nullable (actor)"
        string actor_email "CF Access スナップショット"
        json payload "digest/rotated_from/reason/changes"
        datetime created_at "追記専用 (updated_at なし)"
    }
    JWKS_KEYS {
        bigint id PK
        bigint tenant_id FK "scope・基盤鍵は NULL"
        string uuid UK
        bigint user_id FK "nullable (audit)"
        string issuer "JWT iss 値 (interview-platform / tenants.uuid)"
        string kid "unique [issuer, kid]"
        string alg "EdDSA/RS256"
        json public_jwk "JWK (RFC 7517)"
        string use "sig"
        string private_key_ref "AWS Secrets Manager ARN (秘密鍵は DB 非保持)"
        string status "active/retiring/revoked"
        datetime expires_at
    }