Architecture de la base de données¶

KanjiIQ utilise PostgreSQL 15 comme stockage de données principal, déployé au sein du cluster Kubernetes avec un stockage persistant.

Vue d'ensemble du schéma¶

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
    }

Stockage de contenu multilingue¶

KanjiIQ stocke les traductions en utilisant les colonnes JSONB de PostgreSQL plutôt que des tables de traduction séparées. Cela fournit un stockage multilingue flexible et sans schéma :

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

Pourquoi JSONB ?

Aucune migration de schéma nécessaire lors de l'ajout de nouvelles langues
Une seule requête récupère toutes les traductions d'un kanji
Les opérateurs JSONB de PostgreSQL permettent des recherches efficaces par langue
L'indexation GIN est disponible pour la recherche plein texte dans les traductions

Stockage Kubernetes¶

PostgreSQL s'exécute en tant que Deployment Kubernetes avec un PersistentVolumeClaim :

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

La base de données n'est pas exposée en dehors du cluster — seul le pod backend peut y accéder via le Service jlpt-postgres sur le port 5432.

Décisions de conception clés¶

Clés primaires UUID¶

Toutes les tables utilisent des clés primaires UUID au lieu d'entiers auto-incrémentés. Cela prend en charge :

La génération d'identifiants distribués (pas de séquence centrale)
L'exposition sûre des identifiants dans les API (non devinables)
La réplication multi-région future sans conflits d'identifiants

Suppressions en cascade¶

Les clés étrangères utilisent ON DELETE CASCADE pour maintenir l'intégrité référentielle. La suppression d'un utilisateur entraîne automatiquement la suppression de ses sessions d'étude, résultats de quiz et résultats de tests.

Préférences utilisateur en JSONB¶

Les préférences et statistiques des utilisateurs sont stockées en JSONB plutôt que dans des colonnes fixes :

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

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

Cela évite les migrations de schéma pour chaque nouvelle préférence ou statistique ajoutée à l'application.

Stratégie de sauvegarde¶

La base de données suit la règle de sauvegarde 3-2-1 :

3 copies des données (en direct + 2 sauvegardes)
2 supports différents (PVC + stockage objet)
1 copie hors site (Hetzner Object Storage)

Des exports pg_dump quotidiens sont stockés avec une rétention de 30 jours. Voir la section Déploiement pour les détails opérationnels.