Database Architecture - Obsidian Chess Studio

Overview

Obsidian Chess Studio uses SQLite as its embedded database, managed through Diesel ORM. The database stores chess games, player information, puzzles, and analysis results with optimized indexes for fast searches.

Technology Stack

Database Engine

SQLite 3 - Embedded SQL database
Diesel 2.3 - ORM and query builder
r2d2 - Connection pooling (16 connections)
Rusqlite - Low-level SQLite bindings

Optimizations

Custom indexes on frequently queried columns
Move encoding (8 bytes per move)
Position hashing for fast lookups
Connection pooling for concurrency

Database Schema

The database consists of several interconnected tables:

Core Tables

Games
Players
Events
Sites

Stores chess games with encoded moves and metadata.

CREATE TABLE Games (
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    EventID INTEGER NOT NULL,
    SiteID INTEGER NOT NULL,
    Date TEXT,
    UTCTime TEXT,
    Round TEXT,
    WhiteID INTEGER NOT NULL,
    WhiteElo INTEGER,
    BlackID INTEGER NOT NULL,
    BlackElo INTEGER,
    WhiteMaterial INTEGER NOT NULL DEFAULT 0,
    BlackMaterial INTEGER NOT NULL DEFAULT 0,
    Result TEXT,
    Termination TEXT,
    TimeControl TEXT,
    ECO TEXT,
    PlyCount INTEGER,
    FEN TEXT,
    Moves BLOB NOT NULL,  -- Encoded moves
    PawnHome INTEGER NOT NULL DEFAULT 0,
    
    FOREIGN KEY (EventID) REFERENCES Events(ID),
    FOREIGN KEY (SiteID) REFERENCES Sites(ID),
    FOREIGN KEY (WhiteID) REFERENCES Players(ID),
    FOREIGN KEY (BlackID) REFERENCES Players(ID)
);

-- Indexes for fast queries
CREATE INDEX idx_games_white ON Games(WhiteID);
CREATE INDEX idx_games_black ON Games(BlackID);
CREATE INDEX idx_games_date ON Games(Date);
CREATE INDEX idx_games_eco ON Games(ECO);
CREATE INDEX idx_games_result ON Games(Result);
CREATE INDEX idx_games_white_elo ON Games(WhiteElo);
CREATE INDEX idx_games_black_elo ON Games(BlackElo);

Key fields:

Moves - BLOB storing encoded moves (8 bytes per move)
PawnHome - Pawn structure signature for filtering
ECO - Opening classification code
WhiteMaterial/BlackMaterial - Material balance

Stores player information.

CREATE TABLE Players (
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    Name TEXT,
    Elo INTEGER
);

CREATE INDEX idx_players_name ON Players(Name);
CREATE INDEX idx_players_elo ON Players(Elo);

Usage:

Normalized player storage (no duplicates)
Fast lookups by name or rating
Foreign key references from Games table

Stores tournament and event information.

CREATE TABLE Events (
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    Name TEXT,
    EventType TEXT,
    Location TEXT,
    StartDate TEXT,
    EndDate TEXT
);

CREATE INDEX idx_events_name ON Events(Name);

Stores game locations/platforms.

CREATE TABLE Sites (
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    Name TEXT
);

CREATE INDEX idx_sites_name ON Sites(Name);

Common values:

“Lichess.org”
“Chess.com”
“FIDE World Championship”

Additional Tables

Comments
Puzzles
Analysis Cache
Variant Positions

Stores game annotations.

CREATE TABLE Comments (
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    GameID INTEGER NOT NULL,
    Ply INTEGER,  -- Move number
    Comment TEXT,
    
    FOREIGN KEY (GameID) REFERENCES Games(ID)
);

CREATE INDEX idx_comments_game ON Comments(GameID);

Stores tactical puzzles.

CREATE TABLE puzzles (
    id INTEGER PRIMARY KEY,
    fen TEXT NOT NULL,
    moves TEXT NOT NULL,  -- Solution moves
    rating INTEGER NOT NULL,
    rating_deviation INTEGER NOT NULL,
    popularity INTEGER NOT NULL,
    nb_plays INTEGER NOT NULL,
    themes TEXT,  -- Comma-separated tags
    game_url TEXT,
    opening_tags TEXT
);

CREATE INDEX idx_puzzles_rating ON puzzles(rating);
CREATE INDEX idx_puzzles_themes ON puzzles(themes);

Caches position analysis results.

CREATE TABLE analysis_cache (
    fen TEXT PRIMARY KEY,
    depth INTEGER NOT NULL,
    score INTEGER,
    best_move TEXT,
    pv TEXT,  -- Principal variation
    timestamp INTEGER NOT NULL
);

CREATE INDEX idx_analysis_timestamp ON analysis_cache(timestamp);

Purpose:

Avoid re-analyzing same positions
Cache engine evaluations
Timestamp for cache invalidation

Stores repertoire variant positions.

CREATE TABLE variant_positions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    variant_id INTEGER NOT NULL,
    fen TEXT NOT NULL,
    move_san TEXT NOT NULL,
    depth INTEGER NOT NULL,
    parent_id INTEGER,
    
    FOREIGN KEY (variant_id) REFERENCES variants(id),
    FOREIGN KEY (parent_id) REFERENCES variant_positions(id)
);

CREATE INDEX idx_variant_positions_variant ON variant_positions(variant_id);
CREATE INDEX idx_variant_positions_fen ON variant_positions(fen);

Move Encoding

Moves are stored as binary blobs to save space and improve performance.

Encoding Format

// Each move is encoded as 8 bytes (u64)
// Bits 0-5:   from_square (0-63)
// Bits 6-11:  to_square (0-63)
// Bits 12-15: promotion piece (0=none, 1=N, 2=B, 3=R, 4=Q)
// Bits 16-31: reserved for flags

pub fn encode_move(m: &Move) -> u64 {
    let mut encoded = 0u64;
    
    // From square (6 bits)
    encoded |= m.from().index() as u64;
    
    // To square (6 bits)
    encoded |= (m.to().index() as u64) << 6;
    
    // Promotion (4 bits)
    if let Some(promotion) = m.promotion() {
        encoded |= (piece_to_code(promotion) as u64) << 12;
    }
    
    encoded
}

pub fn decode_move(encoded: u64) -> Move {
    let from = Square::new((encoded & 0x3F) as u8);
    let to = Square::new(((encoded >> 6) & 0x3F) as u8);
    let promotion = if (encoded >> 12) & 0xF != 0 {
        Some(code_to_piece(((encoded >> 12) & 0xF) as u8))
    } else {
        None
    };
    
    Move::new(from, to, promotion)
}

Benefits:

Space efficient: 8 bytes per move vs 4-6 bytes for SAN string
Fast parsing: Bit manipulation faster than string parsing
Portable: Binary format independent of notation style

Example Storage

// Game: 1. e4 e5 2. Nf3 Nc6 3. Bb5
let moves = vec![
    encode_move("e2e4"),  // 0x0000_0000_0000_040C
    encode_move("e7e5"),  // 0x0000_0000_0000_1C34
    encode_move("g1f3"),  // 0x0000_0000_0000_1506
    encode_move("b8c6"),  // 0x0000_0000_0000_1101
    encode_move("f1b5"),  // 0x0000_0000_0000_0D05
];

// Store as BLOB
let blob: Vec<u8> = moves.iter()
    .flat_map(|m| m.to_le_bytes())
    .collect();

// Insert into database
diesel::insert_into(games::table)
    .values((
        games::white_id.eq(white_id),
        games::black_id.eq(black_id),
        games::moves.eq(&blob),
    ))
    .execute(conn)?;

Position Search

Exact Position Search

Searches for games matching an exact FEN position.

pub fn search_exact_position(
    fen: &str,
    conn: &mut SqliteConnection,
) -> Result<Vec<Game>, Error> {
    // Parse FEN to position
    let position = Fen::from_ascii(fen.as_bytes())
        .map_err(|e| Error::InvalidFen(e.to_string()))?;
    
    // Calculate position hash
    let hash = zobrist_hash(&position);
    
    // Search in cache first
    if let Some(cached) = POSITION_CACHE.get(&hash) {
        return Ok(cached.clone());
    }
    
    // Query database
    let games = games::table
        .filter(games::fen.eq(fen))
        .load::<Game>(conn)?;
    
    // Cache result
    POSITION_CACHE.insert(hash, games.clone());
    
    Ok(games)
}

Partial Position Search

Searches for positions with similar piece configurations.

pub fn search_partial_position(
    filters: PositionFilters,
    conn: &mut SqliteConnection,
) -> Result<Vec<Game>, Error> {
    let mut query = games::table.into_boxed();
    
    // Filter by material
    if let Some(white_material) = filters.white_material {
        query = query.filter(games::white_material.eq(white_material));
    }
    
    if let Some(black_material) = filters.black_material {
        query = query.filter(games::black_material.eq(black_material));
    }
    
    // Filter by pawn structure
    if let Some(pawn_signature) = filters.pawn_signature {
        query = query.filter(games::pawn_home.eq(pawn_signature));
    }
    
    query.load::<Game>(conn)
        .map_err(Error::from)
}

Indexing Strategy

Primary Indexes

Indexes on frequently queried columns:

-- Player searches
CREATE INDEX idx_games_white ON Games(WhiteID);
CREATE INDEX idx_games_black ON Games(BlackID);
CREATE INDEX idx_players_name ON Players(Name);

-- Rating searches
CREATE INDEX idx_games_white_elo ON Games(WhiteElo);
CREATE INDEX idx_games_black_elo ON Games(BlackElo);

-- Opening searches
CREATE INDEX idx_games_eco ON Games(ECO);

-- Date range queries
CREATE INDEX idx_games_date ON Games(Date);

-- Result filtering
CREATE INDEX idx_games_result ON Games(Result);

Composite Indexes

For common multi-column queries:

-- Player + rating
CREATE INDEX idx_games_white_elo_result 
    ON Games(WhiteID, WhiteElo, Result);

CREATE INDEX idx_games_black_elo_result 
    ON Games(BlackID, BlackElo, Result);

-- Date + player
CREATE INDEX idx_games_date_white 
    ON Games(Date, WhiteID);

Performance Impact

Query Performance with Indexes:

Player games search: < 10ms (indexed vs 500ms+ unindexed)
ECO code filtering: < 5ms (indexed vs 200ms+ unindexed)
Rating range queries: < 15ms (indexed vs 1000ms+ unindexed)

For databases with 1M+ games, indexes are essential.

Query Optimization

Bulk Inserts

pub fn insert_games_bulk(
    games: &[NewGame],
    conn: &mut SqliteConnection,
) -> Result<usize, Error> {
    // Use transaction for speed
    conn.transaction(|conn| {
        // Batch size of 1000
        for chunk in games.chunks(1000) {
            diesel::insert_into(games::table)
                .values(chunk)
                .execute(conn)?;
        }
        Ok(games.len())
    })
}

Performance:

With transaction: ~10,000 games/second
Without transaction: ~100 games/second

Prepared Statements

Diesel uses prepared statements automatically:

// This query is prepared once and reused
let query = games::table
    .filter(games::white_id.eq(player_id))
    .filter(games::white_elo.ge(min_elo));

// Execute multiple times efficiently
for player_id in player_ids {
    let games = query.load::<Game>(conn)?;
    // Process games
}

Connection Pooling

// Create pool with 16 connections
let pool = Pool::builder()
    .max_size(16)
    .build(manager)?;

// Multiple threads can query simultaneously
let results: Vec<_> = player_ids
    .par_iter()  // Rayon parallel iterator
    .map(|id| {
        let mut conn = pool.get()?;
        get_player_games(id, &mut conn)
    })
    .collect();

Database Migrations

Migrations are managed by Diesel:

migrations/
├── 2024-01-01-000000_create_games/
│   ├── up.sql
│   └── down.sql
├── 2024-01-02-000000_add_indexes/
│   ├── up.sql
│   └── down.sql
└── 2024-01-03-000000_add_puzzles/
    ├── up.sql
    └── down.sql

Running migrations:

use diesel_migrations::{embed_migrations, EmbeddedMigrations, MigrationHarness};

const MIGRATIONS: EmbeddedMigrations = embed_migrations!("migrations");

pub fn run_migrations(conn: &mut SqliteConnection) -> Result<(), Error> {
    conn.run_pending_migrations(MIGRATIONS)
        .map_err(|e| Error::Database(e.to_string()))?;
    Ok(())
}

Statistics Queries

Player Statistics

pub fn get_player_stats(
    player_id: i32,
    conn: &mut SqliteConnection,
) -> Result<PlayerStats, Error> {
    // Total games as white
    let white_games = games::table
        .filter(games::white_id.eq(player_id))
        .count()
        .get_result::<i64>(conn)?;
    
    // Total games as black
    let black_games = games::table
        .filter(games::black_id.eq(player_id))
        .count()
        .get_result::<i64>(conn)?;
    
    // Win rate as white
    let white_wins = games::table
        .filter(games::white_id.eq(player_id))
        .filter(games::result.eq("1-0"))
        .count()
        .get_result::<i64>(conn)?;
    
    let white_win_rate = white_wins as f64 / white_games as f64;
    
    // Opening statistics
    let opening_stats = games::table
        .filter(
            games::white_id.eq(player_id)
                .or(games::black_id.eq(player_id))
        )
        .group_by(games::eco)
        .select((games::eco, diesel::dsl::count_star()))
        .load::<(Option<String>, i64)>(conn)?;
    
    Ok(PlayerStats {
        total_games: (white_games + black_games) as u32,
        white_games: white_games as u32,
        black_games: black_games as u32,
        white_win_rate,
        opening_stats,
    })
}

Opening Performance

pub fn get_opening_performance(
    eco: &str,
    min_elo: i32,
    conn: &mut SqliteConnection,
) -> Result<OpeningStats, Error> {
    let games = games::table
        .filter(games::eco.like(format!("{}%", eco)))
        .filter(
            games::white_elo.ge(min_elo)
                .and(games::black_elo.ge(min_elo))
        )
        .load::<Game>(conn)?;
    
    let total = games.len();
    let white_wins = games.iter().filter(|g| g.result == Some("1-0".into())).count();
    let draws = games.iter().filter(|g| g.result == Some("1/2-1/2".into())).count();
    let black_wins = games.iter().filter(|g| g.result == Some("0-1".into())).count();
    
    Ok(OpeningStats {
        eco: eco.to_string(),
        total_games: total as u32,
        white_win_rate: white_wins as f64 / total as f64,
        draw_rate: draws as f64 / total as f64,
        black_win_rate: black_wins as f64 / total as f64,
    })
}

Backup and Maintenance

Database Backup

use std::fs;

pub fn backup_database(db_path: &Path, backup_path: &Path) -> Result<(), Error> {
    // SQLite supports online backup
    fs::copy(db_path, backup_path)
        .map_err(Error::from)?;
    Ok(())
}

Vacuum (Defragment)

pub fn vacuum_database(conn: &mut SqliteConnection) -> Result<(), Error> {
    conn.execute("VACUUM")?;
    Ok(())
}

Integrity Check

pub fn check_integrity(conn: &mut SqliteConnection) -> Result<bool, Error> {
    let result: String = conn
        .query_row("PRAGMA integrity_check", [], |row| row.get(0))?;
    
    Ok(result == "ok")
}

Performance Tips

Use Transactions

conn.transaction(|conn| {
    // Multiple operations
    insert_games(games, conn)?;
    update_stats(conn)?;
    Ok(())
})

10-100x faster for bulk operations.

Batch Queries

// Use IN clause
games::table
    .filter(games::id.eq_any(&game_ids))
    .load(conn)

Faster than individual queries.

Limit Results

games::table
    .limit(100)
    .offset(page * 100)
    .load(conn)

Paginate large result sets.

Use Indexes

Always create indexes on:

Foreign keys
Filter columns
Join columns
ORDER BY columns

Next Steps

Backend Architecture

Learn about the Rust backend

Frontend Architecture

Explore the React frontend

Getting Started

Contributing

Architecture

​Overview

​Technology Stack

Database Engine

Optimizations

​Database Schema

​Core Tables

​Additional Tables

​Move Encoding

​Encoding Format

​Example Storage

​Position Search

​Exact Position Search

​Partial Position Search

​Indexing Strategy

​Primary Indexes

​Composite Indexes

​Performance Impact

​Query Optimization

​Bulk Inserts

​Prepared Statements

​Connection Pooling

​Database Migrations

​Statistics Queries

​Player Statistics

​Opening Performance

​Backup and Maintenance

​Database Backup

​Vacuum (Defragment)

​Integrity Check

​Performance Tips