Base de données — Schéma et requêtes

English version
← Retour au sommaire

---

Configuration du pool

Fichier : src/lib/postgres.ts

const pool = new Pool({
  host:     process.env.PG_HOST,
  port:     parseInt(process.env.PG_PORT || '5432'),
  database: process.env.PG_DATABASE,
  user:     process.env.PG_USER,
  password: process.env.PG_PASSWORD,
  ssl:      process.env.PG_SSL === 'true' ? { rejectUnauthorized: false } : false,
  max:               20,    // connexions simultanées max
  idleTimeoutMillis: 30000, // délai avant de fermer une connexion inactive
  connectionTimeoutMillis: 10000, // délai d'attente pour obtenir une connexion
});

---

Schéma des tables

Table languages

CREATE TABLE languages (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name            VARCHAR(255) NOT NULL UNIQUE,
  code            VARCHAR(10) NOT NULL UNIQUE,  -- ex: 'pul', 'wol'
  access_code     VARCHAR(255) NOT NULL UNIQUE, -- code joueur
  speaker_access_code VARCHAR(255),             -- code locuteur
  speaker_name    VARCHAR(255),                 -- nom du locuteur
  is_active       BOOLEAN DEFAULT false,        -- visible publiquement
  display_order   INTEGER DEFAULT 0,            -- ordre d'affichage
  primary_color   VARCHAR(7) DEFAULT '#D97706', -- ex: '#D97706'
  icon_url        TEXT,                         -- URL de l'icône dans MinIO
  created_at      TIMESTAMPTZ DEFAULT NOW()
);

Index : code, access_code, is_active

---

Table cards

CREATE TABLE cards (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  language_id UUID REFERENCES languages(id) ON DELETE CASCADE,
  type        VARCHAR(20) NOT NULL CHECK (type IN ('word', 'phrase')),
  card_number INTEGER NOT NULL,
  theme       VARCHAR(255),                    -- ex: 'Famille', 'Nourriture'
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(language_id, type, card_number)       -- unicité par langue et type
);

---

Table items

CREATE TABLE items (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  card_id       UUID REFERENCES cards(id) ON DELETE CASCADE,
  position      INTEGER NOT NULL,              -- 1-6 pour mots, 1-4 pour phrases
  original_text TEXT NOT NULL,                 -- texte en langue africaine
  translation   TEXT NOT NULL,                 -- traduction française
  audio_url     TEXT,                          -- URL vers MinIO (null si pas d'audio)
  lot_number    INTEGER,                       -- 1 ou 2 pour les phrases (null pour mots)
  item_type     VARCHAR(20),                   -- 'question' ou 'answer' (null pour mots)
  created_at    TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(card_id, position)
);

-- Index ajouté en v3 (pour IndexedDB)
CREATE INDEX idx_items_card_id ON items(card_id);

---

Table speakers

CREATE TABLE speakers (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  language_id UUID REFERENCES languages(id) ON DELETE CASCADE,
  name        VARCHAR(255) NOT NULL,
  access_code VARCHAR(255) NOT NULL UNIQUE,
  is_active   BOOLEAN DEFAULT true,
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

---

Table admins

CREATE TABLE admins (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email         VARCHAR(255) NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,              -- format: pbkdf2$iter$salt$hash
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

---

Table statistics

CREATE TABLE statistics (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  language_id UUID REFERENCES languages(id) ON DELETE CASCADE,
  card_id     UUID REFERENCES cards(id) ON DELETE CASCADE,
  item_id     UUID REFERENCES items(id) ON DELETE CASCADE,
  event_type  VARCHAR(50) NOT NULL,         -- 'unlock_language', 'view_card', 'play_audio'
  count       INTEGER DEFAULT 1,
  event_date  DATE NOT NULL,                -- agrégé par jour
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(language_id, card_id, item_id, event_type, event_date)
);

Comportement : les événements sont agrégés par jour via un INSERT ... ON CONFLICT DO UPDATE SET count = count + 1 (upsert).

---

Table contact_messages

CREATE TABLE contact_messages (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        VARCHAR(255) NOT NULL,
  email       VARCHAR(255) NOT NULL,
  subject     VARCHAR(255) NOT NULL,
  message     TEXT NOT NULL,
  status      VARCHAR(50) DEFAULT 'unread', -- 'unread', 'read', 'replied', 'archived'
  admin_notes TEXT,                         -- notes internes admin
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

---

Table quiz_sessions

CREATE TABLE quiz_sessions (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  language_id      UUID REFERENCES languages(id) ON DELETE CASCADE,
  category_slug    VARCHAR(255) NOT NULL,   -- slug URL du thème
  category_name    VARCHAR(255),            -- nom lisible du thème
  total_questions  INTEGER NOT NULL,        -- 15, 18 ou 20
  score            INTEGER,                 -- null si session non terminée
  started_at       TIMESTAMPTZ DEFAULT NOW(),
  completed_at     TIMESTAMPTZ,             -- null si session abandonnée
  created_at       TIMESTAMPTZ DEFAULT NOW()
);

---

Relations entre tables

languages ─┬─< cards ─┬─< items ─── statistics
           │          └─────────────────────────── statistics
           ├─< speakers
           └─< quiz_sessions

admins (table indépendante)
contact_messages (table indépendante)

---

Requêtes SQL utiles

Obtenir le contenu complet d'une langue (pour le téléchargement offline)

SELECT
  c.id AS card_id, c.type, c.card_number, c.theme,
  i.id AS item_id, i.position, i.original_text, i.translation,
  i.audio_url, i.lot_number, i.item_type
FROM cards c
JOIN items i ON i.card_id = c.id
WHERE c.language_id = $1
ORDER BY c.type, c.card_number, i.position;

Progression audio d'une langue

SELECT
  COUNT(*) AS total_items,
  COUNT(i.audio_url) AS items_with_audio,
  ROUND(COUNT(i.audio_url)::numeric / COUNT(*)::numeric * 100, 1) AS completion_pct
FROM cards c
JOIN items i ON i.card_id = c.id
WHERE c.language_id = $1;

Rechercher un item par texte

SELECT c.card_number, c.type, i.position, i.original_text, i.translation
FROM items i
JOIN cards c ON i.card_id = c.id
WHERE c.language_id = $1
  AND (i.original_text ILIKE $2 OR i.translation ILIKE $2)
ORDER BY c.card_number, i.position;
-- $2 = '%terme%'

Cartes sans audio (pour le locuteur)

SELECT c.card_number, c.type, i.position, i.original_text
FROM items i
JOIN cards c ON i.card_id = c.id
WHERE c.language_id = $1
  AND i.audio_url IS NULL
  AND (c.type = 'word' OR i.position IN (1, 4))  -- mots: toutes positions, phrases: 1 et 4
ORDER BY c.type, c.card_number, i.position;

---

Migrations

Les fichiers de migration sont dans le dossier migrations/ :

Fichier	Contenu
001_initial_schema.sql	Schéma initial : languages, cards, items, statistics, speakers, contact_messages
002_create_admins.sql	Table admins
003_idb_card_id_index_note.sql	Note de documentation (IndexedDB v3) — pas de SQL à exécuter
004_quiz_results.sql	Table quiz_sessions

Pour appliquer une migration :

psql -h $PG_HOST -U $PG_USER -d $PG_DATABASE -f migrations/00X_nom.sql

---

Étapes suivantes

• 👉 API REST — référence complète
• 👉 Statistiques et tracking