ER Diagram

テーブル間のリレーション図 (Mermaid)。

erDiagram
    %% ======================================
    %% 共通系
    %% ======================================
    school ||--o{ school_class : "has"
    school ||--o{ student : "enrolls"
    school ||--o{ teacher : "employs"
    school_class ||--o{ student : "contains"
    school_class ||--o{ student_enrollment : "tracks"
    school_class ||--o{ teacher : "assigned_to"
    student ||--o{ student_enrollment : "history"
    student ||--|| student_status : "has_status"
    teacher ||--o{ suppression_action_log : "performed_by"

    %% ======================================
    %% 招待・メール系
    %% ======================================
    student ||..o{ invitation : "user_id_when_student"
    teacher ||..o{ invitation : "user_id_when_teacher"

    %% ======================================
    %% dyslexia 領域系 (letter_)
    %% ======================================
    student ||--o{ letter_session_log : "owns"
    student ||--o{ letter_answer_log : "answered"
    student ||--o{ letter_character_stat : "has"
    student ||--o{ letter_block_stat : "has"
    student ||--o{ letter_daily_record : "has"
    student ||--o{ letter_test_result : "took"

    student_enrollment ||--o{ letter_session_log : "during"
    student_enrollment ||--o{ letter_answer_log : "during"
    student_enrollment ||--o{ letter_test_result : "during"

    letter_session_log ||--o{ letter_answer_log : "contains"
    letter_session_log ||--o| letter_test_result : "produces"

    letter_block_definition ||--o{ letter_block_stat : "tracked"

    teacher ||..o{ letter_test_result : "teacher_judgment_by"

    %% ======================================
    %% 属性定義
    %% ======================================
    school {
        string id PK
        string name
        timestamptz created_at
    }
    school_class {
        bigint id PK
        string school_id FK
        int school_year
        smallint grade
        string name
    }
    student {
        uuid id PK
        uuid cognito_sub UK
        string school_id FK
        bigint class_id FK
        smallint student_no
        string gakuseki_no
        string email UK
        string family_name
        string given_name
        string family_name_kana
        string given_name_kana
    }
    student_enrollment {
        bigint id PK
        uuid student_id FK
        bigint class_id FK
        smallint student_no
    }
    teacher {
        uuid id PK
        uuid cognito_sub UK
        string school_id FK
        bigint class_id FK
        string email UK
        string family_name
        string given_name
        string role
    }
    system_admin {
        uuid cognito_sub PK
        string email UK
        string name
    }
    student_status {
        uuid student_id PK
        date last_login_date
        int login_streak
        bool completed_dyslexia_learning_tutorial
        bool completed_dyslexia_screening_tutorial
    }

    invitation {
        string token PK
        string user_type
        uuid user_id
        string email
        timestamptz expires_at
        timestamptz consumed_at
        timestamptz sent_at
    }
    email_send_log {
        uuid id PK
        string message_id
        string email
        string template_name
        string related_id
        string status
        timestamptz queued_at
    }
    email_suppression {
        string email PK
        string reason
        string bounce_subtype
        timestamptz suppressed_at
    }
    suppression_action_log {
        bigint id PK
        string action
        string email
        uuid performed_by FK
        timestamptz performed_at
    }

    letter_block_definition {
        string id PK
        string display_name
        string group_name
        string characters
    }
    letter_session_log {
        bigint id PK
        uuid student_id FK
        bigint enrollment_id FK
        string category
        date date
        timestamptz start_time
        timestamptz end_time
        int duration_seconds
        text_array block_ids
    }
    letter_answer_log {
        bigint id PK
        uuid student_id FK
        bigint session_id FK
        bigint enrollment_id FK
        string category
        timestamptz timestamp
        string question_char
        string recognized_text
        bool is_correct
        real response_time
    }
    letter_character_stat {
        uuid student_id PK
        string char_id PK
        int correct_count
        int incorrect_count
        smallint status
    }
    letter_block_stat {
        uuid student_id PK
        string block_id PK
        int clear_count
        numeric mastery_rate
    }
    letter_daily_record {
        uuid student_id PK
        date date PK
        int answer_count
        double total_play_duration
    }
    letter_test_result {
        bigint session_id PK
        uuid student_id FK
        bigint enrollment_id FK
        string test_type
        int correct_count
        int total_count
        string ai_judgment
        string teacher_judgment
        uuid teacher_judgment_by FK
    }