RomM Database Schema for User Assets: How Screenshots, Saves, and States Are Stored

RomM stores user-generated screenshots, save files, and emulator states as assets linked to both a specific ROM and user account in a hierarchical SQL schema defined in backend/models/assets.py.

The RomM game library manager tracks user-specific files through a robust relational database architecture. Understanding the RomM database schema for user assets is essential for developers extending the platform or troubleshooting synchronization issues. This schema employs SQLAlchemy inheritance to share common file metadata across distinct asset types while maintaining strict referential integrity between users, ROMs, and their associated data.

Core Schema Hierarchy

The asset system builds upon two abstract base classes before implementing concrete tables for each asset type.

BaseAsset Abstract Class

Defined at the top of backend/models/assets.py, BaseAsset provides the foundation for all file-based records. It includes standard file metadata columns: id, file_name, file_name_no_tags, file_name_no_ext, file_extension, file_path, file_size_bytes, and missing_from_fs.

RomAsset Abstract Class

RomAsset extends BaseAsset to establish ownership relationships. It adds the critical foreign keys rom_id (referencing roms.id) and user_id (referencing users.id), both configured with ondelete="CASCADE" to ensure automatic cleanup when users or ROMs are removed.

Concrete Asset Tables

Three concrete tables inherit from RomAsset:

• Screenshot (screenshots table): Adds is_gallery and is_public columns for visibility control
• Save (saves table): Includes emulator, slot, content_hash, origin_device_id, and is_public for save file management
• State (states table): Tracks emulator and is_public for emulator state snapshots

File Metadata Synchronization

The schema automatically maintains derived filename columns through the _sync_file_name_parts validator in BaseAsset. When file_name is modified, the system updates file_name_no_tags, file_name_no_ext, and file_extension simultaneously, ensuring consistency for parsing and display logic without manual intervention.

Referential Integrity and Cascade Behavior

All user assets enforce strict relational constraints. The rom_id and user_id foreign keys in RomAsset utilize ondelete="CASCADE", meaning deleting a user or ROM record automatically removes associated assets from the database. Relationships are declared with lazy="joined" to enable eager loading of Rom and User objects without additional database queries.

Asset-Specific Features

Screenshot Visibility Controls

The Screenshot model includes boolean flags for is_gallery (distinguishing community uploads from auto-generated thumbnails) and is_public (controlling cross-user visibility). It also provides a download_path property that generates API endpoints with cache-busting timestamps based on the updated_at field.

Save File Device Tracking

The Save model supports multi-device synchronization through the origin_device_id foreign key and a one-to-many relationship to DeviceSaveSync. This enables per-device sync state tracking. Save records also include content_hash for integrity verification and slot for emulator slot management.

State Emulation Metadata

The State model tracks emulator-specific state files with the emulator column and public visibility flags. Both Save and State models include a cached screenshot property that fetches the matching screenshot for the same ROM/user pair if available, leveraging the relationship with lazy="joined" for efficient access.

Querying User Assets

The User model in backend/models/user.py exposes back-references via user.screenshots, user.saves, and user.states, enabling efficient querying of owned assets:

# Fetch all screenshots for a user with eager loading

user = db.session.query(User).filter(User.id == 42).one()
for screenshot in user.screenshots:
    print(f"{screenshot.file_name} (public={screenshot.is_public})")

To retrieve a save file with its associated screenshot:

from sqlalchemy.orm import joinedload

save = (
    db.session.query(Save)
    .filter(Save.id == 1234, Save.user_id == 42)
    .options(joinedload(Save.screenshot))
    .one()
)
print(save.file_name, save.screenshot.file_name if save.screenshot else "no screenshot")

Creating new assets follows standard SQLAlchemy patterns:

new_state = State(
    rom_id=7,
    user_id=42,
    file_name="state1.st0",
    file_path="/states/user42",
    file_size_bytes=2048,
    emulator="retroarch",
    is_public=False,
)
db.session.add(new_state)
db.session.commit()

Summary

• The RomM asset schema uses a two-tier inheritance model (BaseAsset → RomAsset) shared by screenshots, saves, and states.
• Foreign keys rom_id and user_id with cascade deletes ensure data consistency when users or ROMs are removed.
• Automatic filename parsing via _sync_file_name_parts maintains normalized file metadata columns.
• Save files support device synchronization through DeviceSaveSync relationships and content hashing via content_hash.
• Screenshot visibility is controlled via is_gallery and is_public flags, with similar privacy controls on saves and states.

Frequently Asked Questions

How does RomM handle file metadata normalization?

The BaseAsset class implements the _sync_file_name_parts validator in backend/models/assets.py, which automatically populates file_name_no_tags, file_name_no_ext, and file_extension whenever file_name changes. This ensures derived columns remain consistent without manual intervention during file operations.

What happens to user assets when a ROM is deleted?

The schema enforces referential integrity through ondelete="CASCADE" on both rom_id and user_id foreign keys in the RomAsset abstract class. When a ROM or user is deleted, all associated screenshots, saves, and states are automatically removed from the database to prevent orphaned records.

Can users make their save files private?

Yes, the Save model includes an is_public boolean column that controls visibility. When set to False, only the owning user can access the save file through the API, similar to the privacy controls implemented for screenshots (is_public) and gallery distinctions (is_gallery).

How does RomM track which device created a save file?

The Save model tracks device origin through the origin_device_id foreign key referencing the devices table, and maintains synchronization state via a one-to-many relationship with DeviceSaveSync. This allows the system to manage per-device sync status and prevent conflicts across multiple emulation devices.

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