RomM Database Schema for ROMs: Complete SQLAlchemy Model Reference

RomM stores each game as a row in the roms table defined by the Rom SQLAlchemy model in backend/models/rom.py, featuring external provider IDs, file system metadata, JSON blobs for provider data, and relationships to platforms, user saves, and sibling variants.

RomM is a self-hosted ROM manager that organizes retro gaming collections using a structured database backend. The RomM database schema for ROMs centers on the Rom model class, which maps Python objects to relational tables using SQLAlchemy ORM. This article examines the complete schema definition, column types, indexes, and relationships as implemented in the rommapp/romm repository.

Core ROM Table Structure

The roms table serves as the central entity in the RomM database. Each row represents a single game entry with columns organized by function: identification, file system tracking, content metadata, and verification.

Primary and External Identifiers

Every ROM begins with an auto-incrementing primary key and integrates with multiple metadata providers through dedicated ID columns.

• id: Integer primary key with autoincrement
• igdb_id, moby_id, ss_id, ra_id, launchbox_id, hasheous_id, tgdb_id: Integer columns for external provider integration
• flashpoint_id: String(100) for Flashpoint identifiers
• gamelist_id, libretro_id: String(64) for provider-specific string identifiers

File System Representation

RomM tracks the physical location and characteristics of ROM files using columns defined in backend/models/rom.py with length constants imported from backend/models/base.py.

• fs_name: Original file name as stored on disk
• fs_name_no_tags: Filename with tags stripped
• fs_name_no_ext: Filename without extension
• fs_extension: File extension only
• fs_path: Full directory path to the file
• fs_size_bytes: BigInteger representing the file size in bytes

Content Metadata and Descriptions

Human-readable content and structured metadata from external providers use a mix of string and custom JSON types.

• name: String(350) containing the human-readable title
• slug: String(400) for URL-friendly identifiers
• summary: Text field for free-form game descriptions
• igdb_metadata, moby_metadata, ss_metadata: CustomJSON columns storing provider-specific blobs (genres, companies, ratings)

Media Assets and Verification

The schema tracks cover art, manuals, screenshots, and cryptographic hashes for integrity verification.

• path_cover_s, url_cover: Text columns for local and remote cover art
• path_manual, url_manual: Text columns for manual storage locations
• path_screenshots, url_screenshots: CustomJSON lists for screenshot paths
• crc_hash, md5_hash, sha1_hash, ra_hash: String(100) checksums for verification and deduplication

Categorization and Status Fields

Versioning and organizational metadata support complex ROM sets with multiple revisions and regional variants.

• revision, version: String columns for version tracking
• regions, languages, tags: CustomJSON lists for categorization
• missing_from_fs: Boolean (default False) flagging ROMs removed from the filesystem
• platform_id: Integer foreign key linking to the platforms table

Database Relationships and Cardinality

The Rom model defines multiple relationships in backend/models/rom.py that link ROMs to platforms, files, user data, and other ROMs.

Platform Association

The platform relationship establishes a many-to-one link between ROMs and their parent platform (e.g., NES, SNES). The platform_id foreign key column enforces referential integrity to the platforms table.

File and Asset Relationships

Multi-file ROMs and associated assets are modeled through dedicated relationship properties:

• files: One-to-many relationship to RomFile representing individual files belonging to the ROM (main binary, M3U, manuals)
• saves: One-to-many relationship to Save for user save files
• states: One-to-many relationship to State for emulator save states
• screenshots: One-to-many relationship to Screenshot for captured gameplay images

User Data and Progress Tracking

Per-user data is isolated through intermediate tables:

• rom_users: One-to-many relationship to RomUser tracking last_played, rating, and status
• notes: One-to-many relationship to RomNote for user-generated notes with title, content, and visibility fields

Sibling ROMs and Collections

Regional variants and organizational grouping use many-to-many patterns:

• sibling_roms: Many-to-many self-referential relationship through the sibling_roms association table for alternate versions/regional variants
• collections: Many-to-many relationship to Collection via the collections_roms association table
• metadatum: One-to-one relationship to RomMetadata for consolidated genre and franchise data

Supporting Tables in the Schema

The RomM database includes several satellite tables defined alongside the Rom model in backend/models/rom.py.

The rom_files Table

Individual files within a multi-file ROM are stored in the rom_files table with columns:

• id: Primary key
• rom_id: Foreign key to roms.id
• file_name, file_path, file_size_bytes: File system tracking
• Checksum columns for individual file verification
• category: RomFileCategory enum (e.g., main, manual, soundtrack)
• archive_members: CustomJSON for archive contents
• missing_from_fs: Boolean flag for file existence

Metadata Normalization with roms_metadata

The roms_metadata table normalizes JSON list data into a separate structure with rom_id as primary key, containing columns for genres, franchises, collections, companies, game_modes, and age_ratings.

User-Specific Tracking in rom_user

The rom_user table stores per-user ROM progress including last_played timestamps, star ratings, and completion status, linked via foreign key to the roms table.

Indexes and Query Optimizations

The schema includes strategic indexes defined in backend/models/rom.py to optimize common queries.

Unique Constraints

A unique composite index on (platform_id, fs_name) prevents duplicate filenames within the same platform, ensuring data integrity during scans.

Performance Indexes

Additional indexes accelerate metadata lookups:

• Indexes on provider ID columns (igdb_id, moby_id, etc.) for external API synchronization
• Index on name for search operations
• Computed name_sort_key column for natural-sort ordering

Computed Column Properties

Deferred column properties optimize loading performance for expensive calculations:

• name_sort_key: Pre-computed natural sort key
• has_manual_files, has_soundtrack: Boolean indicators derived from rom_files correlation
• multi_file, top_level_file_count: Properties detecting multi-file ROM structures

Working with the RomM Schema

These examples demonstrate how to interact with the ROM schema using SQLAlchemy ORM.

Querying ROMs by Provider ID

from backend.models.rom import Rom
from sqlalchemy import select

def get_by_igdb(session, igdb_id: int):
    """Fetch ROM by IGDB identifier."""
    stmt = select(Rom).where(Rom.igdb_id == igdb_id)
    return session.scalars(stmt).first()

Accessing Relationships

# Load ROM with relationships

rom = session.get(Rom, 1)

# Access platform

print(rom.platform.name)

# List all files in multi-file ROM

for rom_file in rom.files:
    print(f"{rom_file.file_name}: {rom_file.file_size_bytes} bytes")

# Check user progress

for user_data in rom.rom_users:
    print(f"Rating: {user_data.rating}, Status: {user_data.status}")

Creating a New ROM Entry

from backend.models.rom import Rom

def add_rom(session, platform, file_name: str, file_path: str):
    """Create new ROM record."""
    rom = Rom(
        platform_id=platform.id,
        fs_name=file_name,
        fs_path=file_path,
        name="Super Mario Bros.",
        summary="Classic NES platformer",
    )
    session.add(rom)
    session.commit()
    return rom

Summary

• The RomM database schema for ROMs centers on the roms table defined by the Rom class in backend/models/rom.py
• Each ROM tracks external provider IDs (IGDB, MobyGames, etc.), file system metadata, and JSON blobs for provider-specific data
• Relationships include many-to-one with Platform, one-to-many with RomFile/Save/State, and many-to-many with sibling ROMs and Collections
• Supporting tables (rom_files, roms_metadata, rom_user) normalize file data, metadata tags, and user progress
• A unique index on (platform_id, fs_name) prevents duplicates, while computed properties like name_sort_key optimize sorting performance

Frequently Asked Questions

What is the primary key for ROMs in RomM?

The primary key is the id column, defined as an auto-incrementing Integer in the roms table. This internal identifier links to all related records in supporting tables like rom_files and rom_user, while external provider IDs (IGDB, MobyGames, etc.) exist as separate indexed columns for metadata synchronization.

How does RomM handle ROM variants and regional versions?

RomM uses a many-to-many self-referential relationship called sibling_roms linked through the sibling_roms association table. This allows regional variants, translations, and revision updates to reference each other as alternate versions of the same game, with metadata stored in JSON columns for regions and languages to distinguish variants.

Where are file checksums stored in the RomM schema?

Checksums reside in the main roms table within String(100) columns: crc_hash, md5_hash, sha1_hash, and ra_hash (RetroAchievements). For multi-file ROMs, individual file checksums are stored in the rom_files table alongside file-specific metadata, allowing verification at both the ROM and individual file level.

How does RomM track which files belong to a multi-file ROM?

The files relationship connects a Rom to multiple RomFile records in the rom_files table. Each RomFile entry tracks file_name, file_path, file_size_bytes, and checksums, with a category enum distinguishing between main binaries, manuals, and soundtracks. Computed properties multi_file and top_level_file_count determine ROM complexity without loading full file lists.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}