Skip to main content

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

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 :

# 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>)

{
  // 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])

{
  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)

No comments to display

Back to top