# Modèle de données

# Modèle de données

> _Dernière mise à jour : 2026-05-10_

## SQLite (Drizzle ORM)

Schéma : `src/schema.ts`. Migrations : `migrations/*.sql` + meta JSON.

### Diagramme entité-relation

```mermaid
erDiagram
    users ||--o{ games : "addedBy"
    users ||--o{ questions : "userId"
    games ||--o{ games : "parentGameId (extensions)"
    games ||--o{ questions : "gameId"

    users {
        text id PK
        text username UNIQUE
        text passwordHash "argon2"
        text email
        text role "admin/user/pending"
        bool canAddGames
        text createdAt
    }
    games {
        text id PK
        text name
        text parentGameId FK "nullable"
        bool isExtension
        text contentType "base/extension/advanced_rules/faq"
        text rulesLanguage "fr/en"
        text sourceFile "/app/pdfs/<slug>-<ts>.pdf"
        int chunksCount
        text ingestStatus "idle/running/done/error/scheduled"
        text ingestScheduledAt
        text addedBy FK
        text createdAt
        int bggId "nullable"
        text imageUrl
        text bggType
        text bggMechanics "JSON array"
        text bggCategories "JSON array"
        text hasCardDatabase "magic-cards/lorcana-cards/etc."
    }
    questions {
        text id PK
        text userId FK
        text gameId FK
        text question
        text answer "nullable, injecté post-génération"
        real bestScore
        text createdAt
        text vote "up/down/null"
        text feedbackComment
        text diagnostics "JSON blob"
    }
```

### Migrations livrées

| Fichier | Apport |
|---|---|
| `0000_new_forge.sql` | Initial : users, games, questions |
| `0001_vellum_bgg.sql` | Colonnes BGG (bggId, imageUrl, mechanics, categories) |
| `0002_noisy_the_initiative.sql` | `ingestScheduledAt` |
| `0003_pale_rhodey.sql` | `contentType`, `isExtension`, `parentGameId`, `rulesLanguage` |
| `0004_sharp_the_captain.sql` | `hasCardDatabase` |
| `0005_pretty_lady_bullseye.sql` | `bggType` |
| `0006_goofy_terror.sql` | `ingestStatus` enum complet |
| `0007_clever_spectrum.sql` | Schéma conflicts (extension detection) |
| `0008_panoramic_rocket_raccoon.sql` | Contextual cache support |
| `0009_daffy_tana_nile.sql` | Feedback (vote, feedbackComment) |
| `0010_daily_sentinels.sql` | Diagnostics RAG (JSON blob) |

Workflow migration :
```bash
# 1. Modifier src/schema.ts
# 2. Générer la migration
npm run db:generate
# 3. Appliquer
npm run db:migrate
```

## Qdrant (Vector DB)

### Collections

- **`rules_<slug>`** : une par jeu (chunks de règles + forums BGG)
- **`magic-cards`** : cartes MTG normalisées
- **`lorcana-cards`** : cartes Lorcana
- **`flesh-and-blood-cards`** : cartes FAB
- **`riftbound-cards`** : cartes Riftbound
- **`terraforming-mars-cards`** : cartes TM
- **`ark-nova-cards`** : cartes Ark Nova

### Vecteurs

- **Dense** : 1024 dims (TEI bge-m3)
- **Sparse** : BM25 natif Qdrant

### Payload des chunks règles (`rules_<slug>`)

```typescript
{
  // Identité
  chunk_id: string,
  source_file: string,         // chemin PDF
  source_kind: 'pdf' | 'forum',
  
  // Hiérarchie
  hierarchy_path: string[],     // ["Chap 3", "Section 2"]
  hierarchy_level: number,
  section_title: string,
  page_start: number,
  page_end: number,
  
  // Sémantique
  is_extension: boolean,
  is_advanced_rules: boolean,
  is_forum_chunk: boolean,
  game_name: string,
  game_id: string,
  
  // Conflit (extensions seulement)
  conflict_type: 'replaces' | 'modifies' | 'extends' | null,
  conflict_base_chunk_id: string | null,
  conflict_base_page: number | null,
  conflict_summary: string | null,
  
  // Texte
  text: string,                 // chunk brut
  contextual_text: string,      // 1-2 phrases LLM préfixées
}
```

### Payload des chunks cartes (par TCG, variantes)

Champs communs : `id`, `name`, `name_en`, `set_label`, `rarity`, `type`, `image_url`, `text` (effet/ability).

Champs MTG : `card_mtg_color_identity`, `card_mtg_legal_formats`, `card_mtg_layout`, `mana_cost`, `cmc`, faces (double-face).

Champs FAB : `card_legal_heroes`, `pitch`, `talents`, `class`, `intelligence`, `defense`.

Champs Riftbound : `card_domains`, `energy`, `card_type` (Unit/Champion Unit/Spell/Gear/Battlefield/Legend/Rune), `might`.

Champs Lorcana : `ink`, `lore`, `willpower`, `strength`, `card_type`.

Champs TM : `cost`, `victory_points`, `tags[]`, `requirements`.

Champs Ark Nova : `category` (animal/sponsor), `latin_name`, `size`, `conservation_point`.

### Payload des chunks méta (`[META]`)

```typescript
{
  meta_type: 'tier' | 'tournament_deck' | 'forum_thread',
  meta_format: string,          // ex: 'standard', 'classic-constructed', 'spiritforged'
  meta_set: string,             // ex: 'BLB' (17Lands)
  meta_source: string,          // '17lands' / 'mtggoldfish' / 'mobalytics' / 'fabtcg'
  meta_archetype: string,
  meta_placement: number,       // 1-N pour les top 8s
  meta_date: string,            // ISO
  ...
}
```

## Filesystem

```
/app/data/                                 # Volume persistant
├── database.db + .db-shm + .db-wal        # SQLite (WAL mode)
├── card-images-cache/                     # Cache sharp-resize
├── magic-cards/                           # Bulk Scryfall + cards.json FR
├── lorcana-cards/                         # LorcanaJSON FR + EN
├── terraforming-mars-cards/
├── ark-nova-cards/
├── ocr-v1-<slug>.json                     # Cache OCR par jeu
├── contexts-v2-<slug>.json                # Cache Contextual Retrieval B
├── conflicts-v1-<slug>.json               # Cache détection conflits
└── logs/server.log                        # Logger (rotation 50Mo / 30j)

/app/pdfs/                                 # Volume persistant
├── <slug>-<timestamp>.pdf                 # PDF uploadé
└── images/<slug>/page-XX.png              # PNG rendus 300 DPI

/app/ssh/                                  # Volume RO
└── id_ed25519                             # Clé SSH oracle (dédiée)
```