データベースアーキテクチャ¶

KanjiIQは、永続ストレージを備えたKubernetesクラスター内にデプロイされたPostgreSQL 15をプライマリデータストアとして使用しています。

スキーマ概要¶

erDiagram
    users ||--o{ study_sessions : has
    users ||--o{ quiz_results : has
    users ||--o{ test_results : has
    kanji ||--o{ quiz_results : referenced_in
    vocabulary ||--o{ quiz_results : referenced_in

    users {
        uuid id PK
        text email
        text password_hash
        jsonb preferences
        jsonb stats
        timestamp created_at
    }

    kanji {
        uuid id PK
        text character
        int jlpt_level
        jsonb meanings
        jsonb readings
        text example_sentences
    }

    vocabulary {
        uuid id PK
        text expression
        text reading
        int jlpt_level
        jsonb meanings
        text part_of_speech
    }

    locale_configs {
        uuid id PK
        text locale_code
        text[] default_languages
        text[] available_languages
    }

    regional_analytics {
        uuid id PK
        text country_code
        text request_path
        text user_agent
        text device_type
        boolean is_suspicious
        int response_status
        timestamp created_at
    }

    ip_blocklist {
        uuid id PK
        text ip_address
        text reason
        text blocked_by
        timestamp expires_at
        boolean is_active
    }

多言語コンテンツストレージ¶

KanjiIQは、個別の翻訳テーブルの代わりにPostgreSQLのJSONBカラムを使用して翻訳を保存しています。これにより、柔軟でスキーマレスな多言語ストレージが実現されます：

// kanji.meanings column
{
  "en": "mountain",
  "es": "montaña",
  "fr": "montagne",
  "ja": "やま",
  "pt": "montanha",
  "ar": "جبل",
  "zh-CN": "山"
}

なぜJSONBなのか？

新しい言語を追加する際にスキーマ移行が不要
単一のクエリで漢字のすべての翻訳を取得
PostgreSQLのJSONB演算子が言語ごとの効率的な検索を実現
翻訳全体の全文検索にGINインデックスが利用可能

Kubernetesストレージ¶

PostgreSQLはPersistentVolumeClaimを持つKubernetes Deploymentとして動作します：

# k8s/02-postgres-pvc.yaml
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: postgres-pvc
  namespace: jlpt-kanji
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 10Gi

データベースはクラスター外に公開されていません — バックエンドPodのみがjlpt-postgres Serviceを通じてポート5432でアクセスできます。

主要な設計判断¶

UUID主キー¶

すべてのテーブルは自動インクリメント整数の代わりにUUID主キーを使用しています。これにより以下をサポートします：

分散ID生成（中央シーケンス不要）
APIでの安全なID公開（推測不可能）
IDの競合なしで将来のマルチリージョンレプリケーションに対応

カスケード削除¶

外部キーは参照整合性を維持するためにON DELETE CASCADEを使用しています。ユーザーを削除すると、そのユーザーの学習セッション、クイズ結果、テスト結果が自動的に削除されます。

JSONBによるユーザー設定¶

ユーザーの設定と統計は固定カラムではなくJSONBとして保存されます：

// users.preferences
{
  "defaultLanguages": ["en", "pt", "es"],
  "studyLevels": ["N5", "N4"],
  "showAllLanguages": false
}

// users.stats
{
  "totalKanjiStudied": 245,
  "averageScore": 78.5,
  "streakDays": 12
}

これにより、アプリケーションに新しい設定や統計が追加されるたびにスキーマ移行を行う必要がなくなります。

バックアップ戦略¶

データベースは3-2-1バックアップルールに従っています：

データの3つのコピー（ライブ + 2つのバックアップ）
2種類の異なるメディア（PVC + オブジェクトストレージ）
1つのオフサイトコピー（Hetzner Object Storage）

毎日のpg_dumpエクスポートが30日間の保持期間で保存されます。運用の詳細はデプロイメントセクションを参照してください。