Arquitectura de la base de datos¶

KanjiIQ utiliza PostgreSQL 15 como su almacén de datos principal, desplegado dentro del clúster de Kubernetes con almacenamiento persistente.

Descripción general del esquema¶

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
    }

Almacenamiento de contenido multilingüe¶

KanjiIQ almacena las traducciones usando columnas JSONB de PostgreSQL en lugar de tablas de traducción separadas. Esto proporciona un almacenamiento multilingüe flexible y sin esquema fijo:

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

¿Por qué JSONB?

No se necesitan migraciones de esquema al añadir nuevos idiomas
Una sola consulta recupera todas las traducciones de un kanji
Los operadores JSONB de PostgreSQL permiten búsquedas eficientes por idioma
Indexación GIN disponible para búsqueda de texto completo en las traducciones

Almacenamiento en Kubernetes¶

PostgreSQL se ejecuta como un Deployment de Kubernetes con 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 datos no está expuesta fuera del clúster — solo el pod del backend puede acceder a ella a través del Service jlpt-postgres en el puerto 5432.

Decisiones de diseño clave¶

Claves primarias UUID¶

Todas las tablas usan claves primarias UUID en lugar de enteros auto-incrementales. Esto permite:

Generación distribuida de IDs (sin secuencia central)
Exposición segura de IDs en las APIs (no adivinables)
Futura replicación multi-región sin conflictos de IDs

Eliminaciones en cascada¶

Las claves foráneas usan ON DELETE CASCADE para mantener la integridad referencial. Eliminar un usuario automáticamente elimina sus sesiones de estudio, resultados de cuestionarios y resultados de exámenes.

Preferencias de usuario como JSONB¶

Las preferencias y estadísticas de usuario se almacenan como JSONB en lugar de columnas fijas:

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

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

Esto evita migraciones de esquema cada vez que se añade una nueva preferencia o estadística a la aplicación.

Estrategia de respaldo¶

La base de datos sigue la regla de respaldo 3-2-1:

3 copias de los datos (en vivo + 2 respaldos)
2 medios diferentes (PVC + almacenamiento de objetos)
1 copia fuera del sitio (Hetzner Object Storage)

Las exportaciones diarias con pg_dump se almacenan con retención de 30 días. Consulta la sección de despliegue para detalles operativos.