DB 設計ドキュメント

学習アプリのデータベース設計。PostgreSQL 16+ on Aurora Serverless v2 を前提とする。

目次

• 概要
• 命名規則とコンベンション
• テーブル一覧
• ER 図
• テーブル詳細
  • 共通系
  • 招待・メール系
  • dyslexia 領域系 (letter_)
• 将来の拡張: dyscalculia 領域
• インデックス戦略
• 運用上の注意点

概要

設計方針

サブジェクト分離

学習対象 (subject) ごとにテーブル群を分離する設計を採用:

• dyslexia (読み書き障害) 専用テーブル群: letter_ プレフィックス
• dyscalculia (算数障害) 専用テーブル群: 将来 number_ プレフィックスで追加予定

両者を共通テーブルにせず、専用テーブルに分けるのは:

1. データ構造が根本的に異なる (文字 vs 計算)
2. nullable カラムや CHECK 制約が複雑化するのを避ける
3. インデックス設計を最適化したい

概念の直交性

これにより、同じカラム名で違う意味を持つことを避ける。

命名規則とコンベンション

テーブル名

• 共通系: プレフィックスなし (school, student, teacher)
• dyslexia 系: letter_ プレフィックス (letter_session_log, letter_character_stat)
• dyscalculia 系 (将来): number_ プレフィックスを想定

カラム名

• 小文字 + snake_case
• 真偽値: is_*, has_*, *_at (例: is_correct, has_signed_up, consumed_at)
• タイムスタンプ: *_at (created_at, updated_at, sent_at)
• 日付: *_date (last_login_date)
• 外部キー: 参照先テーブル名 + _id (school_id, class_id)

ID 型

列挙型

PostgreSQL の ENUM 型は将来の値追加が困難なため、TEXT + CHECK 制約で表現する。例:

category TEXT NOT NULL CHECK (category IN ('screening', 'learning', 'posttest'))

TEXT を採用する理由:

• DB を直接見ても意味がわかる ('screening' vs 0)
• アプリ層と DB の値が一致するので、enum マッピングのバグが起きない
• 値の追加が容易 (CHECK 制約を更新するだけ)

ストレージ効率は若干劣るが、可読性とのトレードオフを考慮した上で TEXT に統一。 ただし、以下のような 数値的な意味を持つ カラムは SMALLINT のまま:

• grade (1〜6 の学年)
• student_no (1〜99 の出席番号)
• letter_character_stat.status (習熟度ステータス、0..N の段階値)

teacher.role のように追加が頻繁にあり値が少ないものも同様に TEXT + CHECK 制約で表現:

role TEXT NOT NULL CHECK (role IN ('tannin', 'shunin', 'school_admin'))

文字データの正規化

これらはアプリケーション層で処理し、DB には正規化済みデータが保存される前提。

テーブル一覧

共通系 (7 テーブル)

招待・メール系 (4 テーブル)

dyslexia 領域系 (7 テーブル, letter_ プレフィックス)

ER 図

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 : "1:1"

    student ||..o{ invitation : "user_id"
    teacher ||..o{ invitation : "user_id"
    teacher ||--o{ suppression_action_log : "performed by"

    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"

完全版の ER 図 (各カラムの詳細を含む) は er.mermaid を参照。

テーブル詳細

共通系

school - 学校マスタ

CREATE TABLE school (
    id          TEXT PRIMARY KEY,    -- 例 "sch_001"
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

設計判断:

• id を TEXT にしたのは人間可読 ID (運用上扱いやすい) のため。サロゲートキー (BIGSERIAL) でも良いが、通常は導入校数が限られる
• 学校マスタは管理画面 / 運用ツールで作成

school_class - クラス

CREATE TABLE school_class (
    id           BIGSERIAL PRIMARY KEY,
    school_id    TEXT NOT NULL REFERENCES school(id) ON DELETE CASCADE,
    school_year  INTEGER NOT NULL,
    grade        SMALLINT NOT NULL CHECK (grade BETWEEN 1 AND 6),
    name         TEXT NOT NULL,
    UNIQUE (school_id, school_year, grade, name)
);

設計判断:

• school_year (年度) を持つので、毎年新しいクラス行を作成する運用
• 例: 2026 年度の "1-A" と 2027 年度の "1-A" は別行
• 過去年度のクラスも残すことで、過去のテスト結果と紐付けできる

student - 生徒

CREATE TABLE student (
    id                UUID PRIMARY KEY,        -- UUID v7
    cognito_sub       UUID UNIQUE,              -- サインアップ前は NULL
    school_id         TEXT NOT NULL REFERENCES school(id),
    class_id          BIGINT REFERENCES school_class(id),
    student_no        SMALLINT,
    gakuseki_no       TEXT,
    email             TEXT NOT NULL,
    family_name       TEXT NOT NULL,
    given_name        TEXT NOT NULL,
    family_name_kana  TEXT NOT NULL,
    given_name_kana   TEXT NOT NULL,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

設計判断:

• cognito_sub は NULL 可能。学校管理者が事前一括登録した直後 (まだ生徒がサインアップしていない状態) は NULL
• email はグローバル UNIQUE (idx_student_email)。同じメールアドレスで複数学校に登録できない
• gakuseki_no は学校内 UNIQUE (部分インデックス、NULL は除外)
• ふりがなはひらがな統一で保存

外部キー:

• school_id → school.id
• class_id → school_class.id (NULL 可、卒業後等のため)

student_enrollment - 在籍履歴

CREATE TABLE student_enrollment (
    id           BIGSERIAL PRIMARY KEY,
    student_id   UUID NOT NULL REFERENCES student(id) ON DELETE CASCADE,
    class_id     BIGINT NOT NULL REFERENCES school_class(id),
    student_no   SMALLINT,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (student_id, class_id)
);

設計判断:

• 「いつどのクラスに在籍していたか」を年度ごとに記録
• 学習ログ (letter_session_log 等) は enrollment_id を参照することで、年度をまたいだ正確な集計が可能
• student.class_id (現在のクラス) と冗長だが、履歴と現在を分離するために両方持つ

teacher - 教員

CREATE TABLE teacher (
    id                UUID PRIMARY KEY,
    cognito_sub       UUID UNIQUE,
    school_id         TEXT NOT NULL REFERENCES school(id),
    class_id          BIGINT REFERENCES school_class(id),
    email             TEXT NOT NULL,
    family_name       TEXT NOT NULL,
    given_name        TEXT NOT NULL,
    family_name_kana  TEXT NOT NULL,
    given_name_kana   TEXT NOT NULL,
    role              TEXT NOT NULL DEFAULT 'tannin',
    CHECK (role IN ('tannin', 'shunin', 'school_admin')),
    CHECK (
        (role IN ('tannin', 'shunin') AND class_id IS NOT NULL)
        OR role = 'school_admin'
    )
);

設計判断:

• role は TEXT (将来 vice_principal 等を追加しやすい)
• tannin (担任) / shunin (学年主任) は class_id 必須 (担任兼務前提)
• school_admin は class_id 任意 (校長や ICT 担当)
• 認可ロジック上、shunin の権限範囲は「自分の class が属する学年」全体

ロールの権限範囲:

system_admin - システム運用者

CREATE TABLE system_admin (
    cognito_sub  UUID PRIMARY KEY,
    email        TEXT NOT NULL UNIQUE,
    name         TEXT NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

設計判断:

• サービス運用者のみ (Anthropic 内部の運用担当)
• cognito_sub を主キーにしている (school 等への所属を持たないため、独立)
• 認可上は最強権限

student_status - 生徒の状態

CREATE TABLE student_status (
    student_id                              UUID PRIMARY KEY REFERENCES student(id) ON DELETE CASCADE,
    last_login_date                         DATE,
    login_streak                            INTEGER NOT NULL DEFAULT 0,
    completed_dyslexia_learning_tutorial    BOOLEAN NOT NULL DEFAULT FALSE,
    completed_dyslexia_screening_tutorial   BOOLEAN NOT NULL DEFAULT FALSE,
    updated_at                              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

設計判断:

• student と 1:1 関係
• login_streak の更新は POST /me/login API で行う (学習セッションの finish 時には更新しない)
• 将来 dyscalculia_* のチュートリアルカラムを追加予定

招待・メール系

invitation - 招待 token

CREATE TABLE invitation (
    token        TEXT PRIMARY KEY,
    user_type    TEXT NOT NULL CHECK (user_type IN ('student', 'teacher')),
    user_id      UUID NOT NULL,
    email        TEXT NOT NULL,
    expires_at   TIMESTAMPTZ NOT NULL,
    consumed_at  TIMESTAMPTZ,
    sent_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

設計判断:

• user_type (0:student, 1:teacher) と user_id で参照先を判別
• 外部キーは持たない (student / teacher どちらかを参照する性質上、アプリ側で保証)
• 同じ email に対して active な invitation は 1 件まで (部分 UNIQUE インデックス)

CREATE UNIQUE INDEX idx_invitation_active_email
    ON invitation (LOWER(email))
    WHERE consumed_at IS NULL;

有効期限: 3 日 (72 時間)、POST /invitations/send の options.expires_in_hours で上書き可

email_send_log - メール送信ログ

CREATE TABLE email_send_log (
    id              UUID PRIMARY KEY,
    message_id      TEXT,
    email           TEXT NOT NULL,
    template_name   TEXT NOT NULL,
    related_id      TEXT,
    status          TEXT NOT NULL,
    bounce_type     TEXT,
    bounce_subtype  TEXT,
    queued_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    sent_at         TIMESTAMPTZ,
    delivered_at    TIMESTAMPTZ,
    bounced_at      TIMESTAMPTZ,
    complained_at   TIMESTAMPTZ
);

status の値:

• queued: SQS にキューイング済み
• sent: SES が受け付けた
• bounced: バウンス
• complained: 苦情
• delivered: 配信成功
• failed: SES 内部エラー等

設計判断:

• 全送信メールを記録 (デバッグ・監査用)
• message_id は SES のレスポンスから取得、後から bounce / complaint 通知と紐付ける

email_suppression - 送信停止リスト

CREATE TABLE email_suppression (
    email           TEXT PRIMARY KEY,
    reason          TEXT NOT NULL CHECK (reason IN ('hard_bounce', 'complaint', 'manual')),
    bounce_subtype  TEXT,
    diagnostic      TEXT,
    suppressed_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    notes           TEXT
);

reason:

• hard_bounce: 永続バウンス (メールアドレスが存在しない等)
• complaint: 苦情 (迷惑メール報告)
• manual: 手動追加

設計判断:

• AWS SES アカウントレベル Suppression List と並行して、自前でも管理
• 削除 API (DELETE /email-suppressions/{email}) では両方から削除する

suppression_action_log - suppression 操作の監査ログ

CREATE TABLE suppression_action_log (
    id            BIGSERIAL PRIMARY KEY,
    action        TEXT NOT NULL CHECK (action IN ('added', 'removed')),
    email         TEXT NOT NULL,
    reason        TEXT,
    performed_by  UUID REFERENCES teacher(id),
    performed_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    notes         TEXT,

    CHECK (reason IS NULL OR reason IN ('hard_bounce', 'complaint', 'manual'))
);

action:

• added: 追加
• removed: 削除

設計判断:

• performed_by は手動操作の場合に教員 ID を記録、自動 (バウンス検出) なら NULL
• 監査要件で「いつ誰が suppression を解除したか」を追跡

dyslexia 領域系 (letter_)

letter_block_definition - 学習ブロック定義

CREATE TABLE letter_block_definition (
    id            TEXT PRIMARY KEY,            -- "block_a" 等
    display_name  TEXT NOT NULL,               -- "あ行"
    group_name    TEXT NOT NULL,               -- "hiragana_basic"
    characters    TEXT[] NOT NULL,             -- ["あ", "い", "う", "え", "お"]
    sort_order    INTEGER NOT NULL DEFAULT 0
);

設計判断:

• マスタデータ (頻繁に更新されない)
• characters は配列で保存
• アプリ側でも内蔵される可能性あり (オフライン耐性のため)

letter_session_log - セッションログ

CREATE TABLE letter_session_log (
    id                  BIGSERIAL PRIMARY KEY,
    student_id          UUID NOT NULL REFERENCES student(id) ON DELETE CASCADE,
    enrollment_id       BIGINT NOT NULL REFERENCES student_enrollment(id),
    category            TEXT NOT NULL CHECK (category IN ('screening', 'learning', 'posttest')),
    date                DATE NOT NULL,
    start_time          TIMESTAMPTZ NOT NULL,
    end_time            TIMESTAMPTZ,
    duration_seconds    INTEGER,
    device_model        TEXT,
    max_volume_input    REAL,
    button_mash_count   INTEGER NOT NULL DEFAULT 0,
    errant_tap_count    INTEGER NOT NULL DEFAULT 0,
    block_ids           TEXT[] NOT NULL DEFAULT '{}'
);

category:

• screening: スクリーニング
• learning: 学習
• posttest: 事後テスト

設計判断:

• 1 セッションが 1 行
• end_time IS NULL なら未終了
• enrollment_id を持つことで、年度をまたいだ集計が正確
• block_ids は learning 専用。生徒が選んだブロックを配列で保持し、終了時に各ブロックの letter_block_stat を独立に更新する。混合練習(複数選択)は配列に複数要素、ブロック未指定は空配列。screening / posttest では空配列。

letter_answer_log - 解答ログ

CREATE TABLE letter_answer_log (
    id               BIGSERIAL PRIMARY KEY,
    student_id       UUID NOT NULL REFERENCES student(id) ON DELETE CASCADE,
    session_id       BIGINT NOT NULL REFERENCES letter_session_log(id) ON DELETE CASCADE,
    enrollment_id    BIGINT NOT NULL REFERENCES student_enrollment(id),
    category         TEXT NOT NULL CHECK (category IN ('screening', 'learning', 'posttest')),
    timestamp        TIMESTAMPTZ NOT NULL,
    question_char    TEXT NOT NULL,
    recognized_text  TEXT,
    is_correct       BOOLEAN,    -- NULL = screening バッチ判定待ち / 判定不能
    response_time    REAL,
    is_aborted       BOOLEAN NOT NULL DEFAULT FALSE
);

設計判断:

• 1 解答が 1 行 (1 セッション内に 30〜100 行発生)
• category は冗長だが、session_id を JOIN せず category 単独で絞り込めるように保存
• 永続保持 (削除なし)。年間 1000 万行レベルになる可能性あり

letter_character_stat - 文字単位習熟度

CREATE TABLE letter_character_stat (
    student_id                       UUID NOT NULL REFERENCES student(id) ON DELETE CASCADE,
    char_id                          TEXT NOT NULL,
    correct_count                    INTEGER NOT NULL DEFAULT 0,
    incorrect_count                  INTEGER NOT NULL DEFAULT 0,
    accumulated_fast_correct_count   INTEGER NOT NULL DEFAULT 0,
    mastery_count                    INTEGER NOT NULL DEFAULT 0,
    status                           SMALLINT NOT NULL DEFAULT 0,
    total_response_time              DOUBLE PRECISION NOT NULL DEFAULT 0,
    last_3_response_times            DOUBLE PRECISION[] NOT NULL DEFAULT '{}',
    last_5_results                   BOOLEAN[] NOT NULL DEFAULT '{}',
    updated_at                       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (student_id, char_id)
);

設計判断:

• (student_id, char_id) が主キーの集計テーブル
• last_* 配列で直近の傾向を保持 (UPSERT 時にアプリ側で更新)
• 更新タイミング: POST /learning-sessions/{id}/finish のみ。assessment では更新しない

letter_block_stat - ブロック単位習熟度

CREATE TABLE letter_block_stat (
    student_id    UUID NOT NULL REFERENCES student(id) ON DELETE CASCADE,
    block_id      TEXT NOT NULL REFERENCES letter_block_definition(id),
    clear_count   INTEGER NOT NULL DEFAULT 0,
    mastery_rate  NUMERIC(5, 4) NOT NULL DEFAULT 0,
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (student_id, block_id)
);

設計判断:

• mastery_rate は 0.0000 〜 1.0000 の範囲
• 学習セッションで block_id が指定された場合のみ更新

letter_daily_record - 日次集計

CREATE TABLE letter_daily_record (
    student_id           UUID NOT NULL REFERENCES student(id) ON DELETE CASCADE,
    date                 DATE NOT NULL,
    answer_count         INTEGER NOT NULL DEFAULT 0,
    total_play_duration  DOUBLE PRECISION NOT NULL DEFAULT 0,
    PRIMARY KEY (student_id, date)
);

設計判断:

• learning のみ記録。screening / posttest は含まれない
• カラムに category を持たない (learning しか入らないため)
• 将来 dyscalculia の learning は number_daily_record で別管理

letter_test_result - テスト結果

CREATE TABLE letter_test_result (
    session_id                  BIGINT PRIMARY KEY REFERENCES letter_session_log(id) ON DELETE CASCADE,
    student_id                  UUID NOT NULL,
    enrollment_id               BIGINT NOT NULL,
    test_type                   TEXT NOT NULL CHECK (test_type IN ('screening', 'posttest')),

    correct_count               INTEGER,
    total_count                 INTEGER,
    correct_rate                NUMERIC(5, 4),

    -- AI 判定 (アルゴリズムによる二値判定)
    ai_judgment                 TEXT,    -- 'normal' / 'support' (NULL=未算出)

    -- 教員判定 (教員レビュー後に確定、三値)
    teacher_judgment            TEXT,    -- 'normal' / 'watch' / 'support' (NULL=未レビュー)
    teacher_judgment_by         UUID,    -- teacher.id または system_admin.cognito_sub (FK なし、§設計判断参照)
    teacher_judgment_at         TIMESTAMPTZ,

    created_at                  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at                  TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CHECK (ai_judgment IS NULL OR ai_judgment IN ('normal', 'support')),
    CHECK (teacher_judgment IS NULL OR teacher_judgment IN ('normal', 'watch', 'support'))
);

test_type:

• screening: スクリーニング
• posttest: 事後テスト

AI 判定 (ai_judgment) (二値):

• normal: 問題なし
• support: 個別支援が必要 (AI が要確認と検出)
• NULL: AI 判定が未算出 (例: finish 直後で AI バッチ未実行)

教員判定 (teacher_judgment) (三値):

• normal: 問題なし
• watch: 経過観察
• support: 個別指導対象
• NULL: 教員レビューが未完了 (UI では "未確認" として表示)

設計判断:

• AI 判定と教員判定は 独立した 2 軸 として記録する。
  • AI が「support」と判定しても、教員レビューで「normal」とすることが可能 (誤検出のオーバーライド)
  • AI が「normal」でも、教員が観察結果から「support」「watch」を付けることが可能
• これにより、AI モデルの精度評価 (AI と教員の判定不一致率) が分析可能
• teacher_judgment がセットされる時、teacher_judgment_by と teacher_judgment_at も同時にセットされる (CHECK 制約で保証)
• teacher_judgment_by および letter_answer_log.reviewed_by は FK を張らない: レビューは原則 teacher 業務だが、system_admin も保守用途で実行可能(system_admin の主キーは cognito_sub、teacher.id と異種テーブルになるため)。アプリケーション層で integrity 担保。
• session_id を主キーにしているので、1 セッションに 1 結果 (1:1 関係)
• 部分インデックスで「AI が support と判定したが、教員未レビュー」のアラート用クエリを高速化

インデックス:

CREATE INDEX idx_letter_test_result_alert
    ON letter_test_result (enrollment_id)
    WHERE ai_judgment = 'support' AND teacher_judgment IS NULL;

クラス集計について

クラス全体のテスト結果集計 (例: 「3年1組の AI support 数」「judgment 分布」など) は、事前集計テーブルを持たず、動的クエリで算出する 方針。

理由:

• MVP の規模 (1 クラス 30 名程度、1 学校 数十クラス) では動的集計で十分高速 (数十 ms 以内)
• 集計バッチ (Lambda + EventBridge 等) を運用する負担が無い
• 整合性維持の責任が無い (test_result が更新されたら、次のクエリで自動的に最新値が返る)

代表的な集計クエリ例:

SELECT
    COUNT(*) AS total_students,
    COUNT(tr.session_id) AS submitted_count,
    COUNT(*) FILTER (WHERE tr.ai_judgment = 'support') AS ai_support_count,
    COUNT(*) FILTER (WHERE tr.teacher_judgment IS NOT NULL) AS teacher_reviewed_count,
    COUNT(*) FILTER (WHERE tr.teacher_judgment = 'support') AS teacher_support_count,
    COUNT(*) FILTER (WHERE tr.teacher_judgment = 'watch')   AS teacher_watch_count,
    COUNT(*) FILTER (WHERE tr.teacher_judgment = 'normal')  AS teacher_normal_count,
    COUNT(*) FILTER (WHERE tr.session_id IS NOT NULL AND tr.teacher_judgment IS NULL) AS teacher_unreviewed_count,
    AVG(tr.correct_rate) AS avg_correct_rate
FROM student s
LEFT JOIN letter_test_result tr ON tr.student_id = s.id
    AND tr.test_type = $2
    AND DATE(tr.created_at AT TIME ZONE 'Asia/Tokyo') BETWEEN $3 AND $4
WHERE s.class_id = $1;

将来データ規模が大きくなり集計コストが問題になれば、このタイミングで letter_class_daily_test_stat のような事前集計テーブルを追加することは可能。今は 必要になるまで作らない。

将来の拡張: dyscalculia 領域

将来 dyscalculia (算数障害) を扱う際の設計方針:

別系統テーブル群を新設

number_problem_definition    -- 計算問題定義 (例: 1桁同士の足し算)
number_session_log           -- セッション
number_answer_log            -- 解答 (question_text, recognized_number)
number_skill_stat            -- スキル単位習熟度 (足し算 / 引き算 / 掛け算)
number_problem_type_stat     -- 問題タイプ単位習熟度
number_daily_record          -- 日次集計 (dyscalculia learning)
number_test_result           -- テスト結果
number_class_daily_test_stat -- クラス集計

共通テーブルへの影響

student_status に completed_dyscalculia_*_tutorial カラムを追加。それ以外の共通テーブルは影響なし。

共通すべきもの・分離すべきものの判断基準

インデックス戦略

部分インデックス (PostgreSQL の強み)

NULL 許容のカラムや特定条件のクエリに最適化:

-- gakuseki_no が NULL でない学生のみ UNIQUE
CREATE UNIQUE INDEX idx_student_gakuseki
    ON student (school_id, gakuseki_no)
    WHERE gakuseki_no IS NOT NULL;

-- 未消費の招待のみ
CREATE UNIQUE INDEX idx_invitation_active_email
    ON invitation (LOWER(email))
    WHERE consumed_at IS NULL;

-- 教員アラート用 (AI が support と判定 & 教員未レビュー)
CREATE INDEX idx_letter_test_result_alert
    ON letter_test_result (enrollment_id)
    WHERE ai_judgment = 'support' AND teacher_judgment IS NULL;

複合インデックスの順序

時系列降順を含む複合インデックスは「前方一致 + ソート」で活躍:

-- (student_id, date DESC) で「特定生徒の最新セッション」が高速
CREATE INDEX idx_letter_session_student_date
    ON letter_session_log (student_id, date DESC);

関数インデックス (LOWER)

email は小文字正規化が前提だが、念のため LOWER でインデックス:

CREATE INDEX idx_email_send_log_email
    ON email_send_log (LOWER(email));

運用上の注意点

1. UUID v7 の生成

UUID v7 は Postgres 標準では生成できない。アプリ側で生成して INSERT する。

TypeScript なら uuidv7 パッケージ (npm install uuidv7):

import { uuidv7 } from 'uuidv7';
const id = uuidv7();  // 例 "01890a5d-ac96-7c3e-9b3a-8f0c4e5d6f7a"

2. ON DELETE CASCADE の方針

• student の削除 → 関連する letter_* テーブル全て CASCADE
• school の削除 → school_class CASCADE
• student_enrollment は削除しない (集計の整合性を守るため)

3. テキストフィールドの長さ制限

PostgreSQL では TEXT 型に長さ制限はないが、アプリ層でバリデーション:

4. データ保持期間

• letter_answer_log: 永続保持 (年間数千万行を覚悟、将来パーティショニング検討)
• letter_session_log: 永続保持
• email_send_log: 1 年保持 (それ以降は古いものから削除推奨)
• suppression_action_log: 監査要件次第 (基本永続)

5. PII (個人情報) の扱い

• student.family_name 等は PII。バックアップ・監査ログで暗号化を検討
• email も PII
• ログ出力時は emailをマスク (tan***@school.example.jp)

関連ドキュメント

• schema.sql - 全テーブルの DDL
• er.mermaid - 完全版 ER 図
• api.yaml - OpenAPI 3.1 仕様書