Database Schema

The Dice Chess Lab backend uses SQLite + SQLAlchemy 2.0. This page reflects the current schema defined in backend-api/src/dicechess_trainer/models/.

All class-based models inherit from Base, which contributes:

• id (integer autoincrement primary key), unless explicitly overridden
• created_at (DateTime(timezone=True), server default)
• updated_at (DateTime(timezone=True), auto-updated on row updates)

Entity-Relationship Diagram

We use a repository pattern and keep nested API payloads in JSON columns to avoid over-normalizing external Dice Chess data.

erDiagram
    User {
        int id PK
        string email "unique"
        string name "nullable"
        string picture_url "nullable"
        string role
        boolean is_approved
        boolean is_active
        datetime last_login_at "nullable"
        datetime created_at
        datetime updated_at
    }

    Game {
        int id PK
        string game_id "unique"
        string white_player_id "nullable"
        string black_player_id "nullable"
        string white_player_username "nullable"
        string black_player_username "nullable"
        string tournament_id "nullable"
        int bet "nullable"
        int time_limit "nullable"
        int time_bonus "nullable"
        float white_player_rating "nullable"
        float black_player_rating "nullable"
        int result "nullable"
        boolean allow_doubling "nullable"
        datetime start_time "nullable"
        json metadata_json "nullable"
        json moves_json "nullable"
        datetime created_at
        datetime updated_at
    }

    Player {
        int id PK
        string username "unique"
        int dicechess_id "unique, nullable"
        float rating "nullable"
        float x2_rating "nullable"
        int total_games
        json raw_data
        datetime created_at
        datetime updated_at
    }

    Tournament {
        int id PK
        string tournament_id "unique"
        string name "nullable"
        string status "nullable"
        datetime start_time "nullable"
        datetime end_time "nullable"
        json raw_data
        datetime created_at
        datetime updated_at
    }

    UserArchivedGame {
        int id PK
        int user_id FK
        string game_id FK
        datetime created_at
        datetime updated_at
    }

    UserFavoriteGame {
        int user_id FK
        string game_id FK
    }

    BookmarkedPosition {
        string id PK "uuid"
        int user_id FK
        string game_id FK
        int ply
        string fen
        string dice_roll
        string played_moves
        string notes "nullable"
        datetime created_at
        datetime updated_at
    }

    TrainingLog {
        int id PK
        int user_id FK
        datetime timestamp
        string game_id FK
        int move_number
        string color_played
        string dice_roll
        int time_spent_ms
        string fen_before
        string user_guess
        string actual_move
        int actual_moves_count
        int guessed_moves_count
        boolean is_perfect
        string fen_after_guess "nullable"
        string fen_after_actual
        datetime created_at
        datetime updated_at
    }

    TrainingPuzzle {
        string id PK "uuid"
        int user_id FK
        string game_id FK "nullable"
        int ply
        string normalized_initial_fen
        string dice
        string solution_moves
        string normalized_final_fen
        int success_count
        int failure_count
        datetime last_attempted_at "nullable"
        string notes "nullable"
        boolean is_active
        datetime created_at
        datetime updated_at
    }

    PuzzleAttempt {
        int id PK
        string puzzle_id FK
        int user_id FK
        boolean is_correct
        int time_spent_ms
        datetime created_at
        datetime updated_at
    }

    User ||--o{ UserArchivedGame : archives
    User ||--o{ UserFavoriteGame : favorites
    User ||--o{ BookmarkedPosition : bookmarks
    User ||--o{ TrainingLog : records
    User ||--o{ TrainingPuzzle : creates
    User ||--o{ PuzzleAttempt : attempts
    Game ||--o{ UserArchivedGame : is_archived_by
    Game ||--o{ UserFavoriteGame : is_favorited_by
    Game ||--o{ BookmarkedPosition : belongs_to
    Game ||--o{ TrainingLog : contains
    Game ||--o{ TrainingPuzzle : sources
    TrainingPuzzle ||--o{ PuzzleAttempt : has_attempts
    Tournament ||..o{ Game : "logical by tournament_id"
    Player ||..o{ Game : "logical by username/id fields"

Design Philosophies

• Raw JSON retention: games.metadata_json, games.moves_json, and similar fields keep external payloads intact while preserving selected indexed columns for filtering.
• Constraint-driven integrity:
  • bookmarked_positions: unique (user_id, game_id, ply)
  • user_archived_games: unique (user_id, game_id)
  • training_puzzles: unique (user_id, normalized_initial_fen, dice)
  • user_favorite_games: composite PK (user_id, game_id)
• Training analytics foundation: training_logs stores structured trainer outcomes; is_perfect is based on board-part FEN equality semantics.
• Practice scaling strategy: training_puzzles keeps denormalized progress counters (success_count, failure_count, last_attempted_at) for cheap candidate selection on SQLite.

Relationship Notes

• games.white_player_id, games.black_player_id, and games.tournament_id are stored as indexed scalar fields, not strict SQL foreign keys.
• Game.players in ORM is a view-only helper relationship using username/id matching rules; it is not backed by one explicit FK constraint.

See also: Frontend Trainer Mode for trainer scoring semantics (is_perfect), and SQLite Performance for connection-level PRAGMA settings and operational guidance.