# RomM Database Schema for ROMs: Complete SQLAlchemy Model Reference

> Explore the RomM database schema for ROMs using the SQLAlchemy Rom model. Discover how games are stored with metadata, provider IDs, and relationships to other data.

- Repository: [The RomM Project/romm](https://github.com/rommapp/romm)
- Tags: api-reference
- Published: 2026-07-05

---

**RomM stores each game as a row in the `roms` table defined by the `Rom` SQLAlchemy model in [`backend/models/rom.py`](https://github.com/rommapp/romm/blob/main/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`](https://github.com/rommapp/romm/blob/main/backend/models/rom.py) with length constants imported from [`backend/models/base.py`](https://github.com/rommapp/romm/blob/main/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`](https://github.com/rommapp/romm/blob/main/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`](https://github.com/rommapp/romm/blob/main/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`](https://github.com/rommapp/romm/blob/main/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

```python
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

```python

# 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

```python
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`](https://github.com/rommapp/romm/blob/main/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.