Database Design

Overview

This page is the canonical home for the aggregate domain model behind the SQLite persistence migration (epic #271). The migration replaced the JSON state files with a SQLite database whose tables back a set of Cosmic Python aggregates.

The migration and its teardown are complete. The enforcement infrastructure (the decorator, the linters, and the type-check rule that keep aggregates honest, #788), the full aggregate set (the aggregate roots, their fields, and their mutation methods — The aggregate set), the SQLite schema (the migration framework + 001_initial.sql, #780/#781), the per-aggregate Repository Protocols (#782), and the runtime Unit of Work + concrete sqlite3 repository adapters (#783) are all in place — documented below. SQLite, reached through the Unit of Work, is the sole live persistence path for relational state; the only remaining live JSON file is settings.json.

The cutover (#784) was a hard cut — SQLite started empty, the JSON state was not migrated into it, and each JSON-era state class was deleted once its last consumer moved over. Each vertical landed in turn:

• library/roms — the live sync path writes roms (the synced-ROM registry), sync_runs (the start→complete/cancel/error lifecycle that replaced the JSON last_sync/sync_stats scalars), and the kv_config fs_slug → name cache row, all through Repository Protocols + the Unit of Work.
• metadata — rom_metadata is written by the reporter's per-unit commit, the same write Unit of Work as the roms upsert (Rom row first, then metadata, so the rom_id FK is satisfied at commit and a ROM and its cached metadata land atomically); MetadataService/GameDetailService read it back from SQLite.
• playtime — PlaytimeService records sessions and reconciles the cross-device total through the rom_playtime aggregate, which spans two tables: the per-ROM scalars (rom_playtime) and the pending-session outbox (rom_playtime_sessions). Session-end folds the duration in a short write UoW and enqueues the session into the outbox; the outbox then flushes to RomM's native /api/play-sessions ingest outside any transaction (best-effort, offline-safe). Opening a game's detail page flushes the outbox, then folds three values derived from the same server session list into rom_playtime — the summed total (reconcile_total), the row count (reconcile_session_count) and the newest end time (reconcile_last_played), each a monotonic max() clamp — so a fresh device restores session_count and last_played alongside total_seconds, not the total alone (ADR-0018, #903).
• rom-removal + startup-healing — RomRemovalService.remove_rom/forget_download/uninstall_all_roms read and delete rom_installs through the Unit of Work, and StartupHealingService.report_missing_installs reads it and deletes nothing; an uninstall deletes only the on-disk files and the rom_installs row, a forget only the row, and neither touches the roms identity row, playtime, saves, or metadata (ADR-0007).
• read-consumers — GameDetailService resolves the ROM, install record, cached save state, cached metadata, and platform-name cache in one read Unit of Work (the has-saves badge reads rom_save_sync_states, the platform display name the kv_config cache, both degrading gracefully when absent); AchievementsService reads each ROM's ra_id from roms; SettingsService.apply_steam_input_setting reads the bound shortcut_app_ids from roms (skipping unbound NULL rows).

With every consumer moved over, the teardown completed: the JSON-era state class SaveSyncState (domain/save_state.py), the dead JSON stores (RegistryStoreAdapter, MetadataCacheStoreAdapter), the dead persisters, and the in-memory state dicts (shortcut_registry, metadata_cache, and the catch-all state dict) are all deleted.

What an aggregate is here

An aggregate is a cluster of domain objects treated as a single unit for data consistency, with one root entity that owns all invariants and is the only external entry point. The full definition — root, identity, transaction boundary, by-id references, mutation-via-methods — lives in the Aggregate glossary entry in GLOSSARY.md. This page uses that vocabulary; it does not re-derive the Cosmic Python theory.

Aggregate boundaries are invariant boundaries, not storage boundaries — one aggregate may be backed by several tables, and table layout is a downstream decision. The first concrete aggregate-boundary decision, adopting Platform as a full aggregate rather than a denormalized string, was recorded in ADR-0001 — now superseded by ADR-0003, which reverts Platform to a denormalized platform_slug string.

Standards shared across all aggregates

Every aggregate in this codebase follows the same rules, so the enforcement layers below can be uniform:

• Declared via the @cosmic_aggregate decorator. This is the canonical form — not a transitional flag. The decorator marks the class as an aggregate root and is the marker the field-assignment check looks for.
• Mutation only via verb-named methods on the root. No field assignment (aggregate.field = value) from outside the aggregate's own methods. Methods are named after the domain event that occurred (adopt_baseline(...), mark_installed(...), confirm_slot(...)) — per-field verbs even when slightly forced (set_autocleanup_limit(10)), consistency over expressiveness. Field access for reads is fine.
• Cross-aggregate references by id only — never by holding a Python reference to another aggregate's internals. RomInstall carries rom_id: int, not rom: Rom.
• No extra: dict[str, Any] forward-compat hedge. Schema migrations carry the model forward; aggregates do not hold an open-ended JSON dict against future change.

CP enforcement layers

Four mechanisms keep the aggregate rules from drifting. They are layered — each catches a different class of violation, and together they fail a change that mutates an aggregate's fields from outside its own methods before it merges, in every shape the field-assignment check (layer 2) sees.

Layer	Mechanism	What it catches
1	@cosmic_aggregate decorator	Declares the root; gives it __slots__ so unknown fields can't be set
2	AST field-assignment check	A write to an aggregate's field, on any receiver but bare self, in backend/
3	import-linter domain contracts	Non-stdlib / non-self imports into domain/
4	basedpyright reportPrivateUsage	Access to _-prefixed internals from production code

1. The @cosmic_aggregate decorator

backend/domain/_aggregate.py defines the single canonical way to declare an aggregate root:

from domain._aggregate import cosmic_aggregate

@cosmic_aggregate
class Rom:
    rom_id: int
    platform_slug: str
    # ...

The decorator applies @dataclass(slots=True), so the root gets __init__, __repr__, __eq__, and __slots__ for free. __slots__ matters for enforcement: a slotted dataclass rejects assignment to any attribute not declared as a field, so typos and ad-hoc field additions fail at runtime, not silently. It is also the marker the AST check (layer 2) scans for — @cosmic_aggregate is how a class opts into the mutation-via-methods rule.

Value Objects do not use this decorator. Immutable members of an aggregate (e.g. FileSyncState, BiosFileEntry) use a plain @dataclass(frozen=True, slots=True) — they are immutable by construction and have no mutation surface to police, so they need neither the marker nor the verb-method discipline. The decorator is for roots only.

2. AST field-assignment check

scripts/check_aggregate_field_assignment.py is a small custom linter, wired into CI alongside the cosmic call bans. It enforces the mutation-only-via-methods rule that a type checker cannot express directly.

How it works:

1. It walks backend/domain/, parses every file, and collects the classes decorated with @cosmic_aggregate with the field names their class bodies annotate. A field annotated list, dict or set is a container field.
2. It walks every file under backend/ but _vendor/ and native/, domain/ included, and flags, on any receiver but bare self: a store or del on <receiver>.<field>; a setattr or delattr whose field name is a string literal; and an item store, item del, attribute store or mutating call (append, update, pop, …) reached through <receiver>.<container field>. Bare self is exempt in every class, because it is how an aggregate's own methods write.

It matches by field name, not by type: it cannot know what a receiver holds, so rom.name = …, rows[0].name = … and ctx.save_state.files[k] = … are all caught whatever the variable is called, and a finding names the aggregates that declare the field. A value that only shares a field name is flagged too; the escape hatch is a trailing comment on the offending line:

saves[index].id = save_id  # pragma: no aggregate-check

What it does not see, and what stays prompt-only, is the register entry's (Code structure).

3. import-linter — domain is stdlib + self only

Two .importlinter contracts confine domain/ to the standard library and itself:

# Domain must not import services, adapters, lib, or models
[importlinter:contract:domain-independence]
type = forbidden
source_modules =
    domain
forbidden_modules =
    services
    adapters
    lib
    models

# Domain must not import vendored third-party packages
[importlinter:contract:domain-stdlib-only]
type = forbidden
source_modules =
    domain
forbidden_modules =
    _vendor

Together these say: domain = stdlib + self only. domain-independence forbids every sibling first-party layer (note lib and models are now in the forbidden list — domain depends on no other internal layer); domain-stdlib-only forbids the _vendor namespace, which is the codebase's only entry point for non-stdlib runtime code. This is the CP doctrine that the domain model has no external runtime dependencies, mechanically enforced.

A consequence of lib being forbidden: anything domain needs from "shared utilities" lives inside domain itself. ISO-8601 timestamp parsing (parse_iso / parse_iso_to_epoch) moved from lib/iso_time.py to domain/iso_time.py for exactly this reason.

4. basedpyright reportPrivateUsage = "error"

pyproject.toml sets reportPrivateUsage = "error", so accessing a _-prefixed name from outside its owning class is a hard type error, not a convention nobody enforces. This makes the underscore-prefix convention real: production code cannot reach into an aggregate's (or any class's) internals.

Tests are exempt via an execution-environment override:

[[tool.basedpyright.executionEnvironments]]
root = "tests"
extraPaths = ["backend"]
reportPrivateUsage = "none"

White-box testing — inspecting and rebinding the private state of the system under test — is a deliberate, accepted pattern here. The guardrail targets production encapsulation, not test setup.

One corollary worth stating: a method that one sub-service calls on a peer is part of that peer's public surface and carries no leading underscore. The _ prefix is reserved for genuinely class-internal helpers, which keeps reportPrivateUsage coherent with the saves-style peer-injection carve-out — peers call public methods, never private ones.

The aggregate set

Sixteen aggregate roots model the persisted domain (eight from #788, plus PlatformSyncState added by ADR-0023, CollectionSyncState added by #742, AnsweredSaveDirectory added by ADR-0041, ArtworkSlotOutcome added by #2295, ArtworkSlotChoice and ArtworkSlotHandover added by #2381, and StoredFirmware and FirmwareLink added by #2191). Each lives in its own domain/<name>.py module — the two firmware-store roots share domain/firmware_store.py — is declared with @cosmic_aggregate, and mutates only through verb-named methods. Per ADR-0003 the former SyncSettings knobs and device_name move to settings.json (#822) and device_id becomes a kv_config row (#784) — they were config and a singleton scalar, not relational state with invariants. The Carries column reflects the fields as actually implemented (? marks a nullable/optional field); where the shipped shape is intentionally leaner than the original #788 plan, the Why column says so. Cross-aggregate references are by id/slug only — the per-ROM aggregates (RomMetadata, RomSaveSyncState, Playtime) are keyed by rom_id externally rather than carrying it as a field; only RomInstall carries rom_id as a field rather than keying on it externally, and it also denormalizes platform_slug/system so the home migration and the save answer can read installs without a join.

Aggregate root	Carries	Why a separate aggregate
Rom (domain/rom.py)	rom_id (identity), platform_slug, name, fs_name, shortcut_app_id?, last_synced_at, cover_path?, cover_source?, igdb_id?, sgdb_id?, chosen_sgdb_id?, artwork_checked_at?, ra_id?, emulator_override?, emulator_override_source?, selected_disc?, shortcut_applied, last_fetch_id?, sibling_group_key?, regions, languages, revision, tags, is_main_sibling, fs_size_bytes?, has_file_on_disk, delivered_through?, gone_from_romm?, platform_fs_slug?	Created/updated atomically when a ROM is synced from RomM; shortcut_app_id is NULL when the ROM is unbound (shortcut removed / gone from RomM) but the row is retained per ADR-0007. A bound shortcut_app_id is unique across roms (the 003 partial unique index, #1036): whenever the same appId is presented for a new rom_id, SqliteRomRepository.save() unbinds any sibling row already holding it (UPDATE … SET shortcut_app_id = NULL WHERE shortcut_app_id = ? AND rom_id != ?) before the per-rom_id UPSERT, so the new row binds without ever leaving two bound rows sharing one appId (unbind-only — the sibling row survives, ADR-0007). This guard protects the server-switch / re-import case: an unchanged game's appId is kept alive across a rom_id change by the sibling-group rebind lane (Steam assigns the appId at creation — the old CRC32(exe + name) derivation is disproven), and the guard ensures the re-bound appId is never left on two rows (Steam Non-Steam Shortcuts — Recovery after a server switch / re-import). chosen_sgdb_id (migration 033) is the nullable SteamGridDB game the user chose for a ROM RomM gives no SteamGridDB id (#2381 D3, D8), recorded via Rom.choose_sgdb_game, dropped via Rom.supersede_chosen_sgdb_game once RomM's id replaces it, through the pin-only set_chosen_sgdb_id write path and excluded from the sync UPSERT, which writes only RomM's id, to sgdb_id; RomM's id wins where both exist (Game page artwork). artwork_checked_at (migration 037) is the nullable time, REAL Unix-epoch seconds, at which a visit to the game's page last started checking its automatic artwork against SteamGridDB (D21), recorded via Rom.begin_artwork_check and taken back via Rom.withdraw_artwork_check where SteamGridDB left a slot unanswered, through the pin-only set_artwork_checked_at write path, and excluded from the sync UPSERT; one per ROM, so each version keeps its own. NULL = never checked (Game page artwork, "Keeping automatic artwork current"). emulator_override is the nullable per-game core LABEL (NULL = follow the platform default), and emulator_override_source (migration 039) the kind of the emulator source it was chosen from (NULL beside a label = a pick from before the migration, read against the source the game starts through; #2195 D20), both pinned/cleared via Rom.pin_emulator_override / clear_emulator_override and excluded from the sync UPSERT so re-sync never wipes a user's pin; anchoring it on roms lets the choice survive uninstall/reinstall (ADR-0011). selected_disc is the nullable per-game disc pick — the basename of the selected disc for a multi-disc ROM (NULL = follow the default: the install's .m3u else disc 1), pinned/cleared via Rom.pin_selected_disc / clear_selected_disc and likewise excluded from the sync UPSERT and anchored on roms so the pick survives uninstall/reinstall and home migration (ADR-0014). shortcut_applied (migration 041) records whether the sync applied the ROM's Steam shortcut (#2221 D17), recorded via Rom.record_shortcut_applied and the pin-only set_shortcut_applied write path (never the sync UPSERT, so an unrelated re-save never wipes it) by the sync's commit alone, for a binding target, and cleared on every row by Force Full Sync. The delta-restricted apply reads it back: an applied item whose identity matches is unchanged and skipped, while an item not applied — a freshly created row, or one Force Full Sync cleared — re-applies the shortcut (ADR-0025, as amended). sibling_group_key (the RomM sibling group this dump belongs to — a connected component over RomM's sibling_roms edges keyed {source}:{value}:{platform} on the highest-priority metadata source the component agrees on over igdb/ss/moby/ra/hasheous/launchbox/tgdb/flashpoint, else romm:<rom_id>:<platform_id>; ADR-0022 supersedes ADR-0021 §1's coalesce-first derivation) and the version dimensions regions / languages / revision / tags / is_main_sibling are server-derived facts (ADR-0021): unlike the two pin columns above, they ride the sync UPSERT and refresh every sync, except that a row a platform's listing leaves out takes sibling_group_key through Rom.follow_group, which the chunk commit reads, changes and saves. sibling_group_key is nullable — a pre-#1295 row, or every row after migration 011 NULLs them for the one-time re-key (#1368), is backfilled on the next sync (a NULL key on any row that carries its platform stamp's fetch generation, bound or not, forces that platform's incremental skip to fall through to a full fetch). Its two legal states are a real key and NULL — never an empty string. No stored row holds one: migration 011 NULLed every pre-existing key, and extract_version_metadata (domain/shortcut_data.py) has computed or coalesced the key on every write since the column landed — computed unconditionally from the ROM dict at first (#1304), and since #1337 coalesced, replacing an empty incoming value with a computed key. VersionMetadata.__post_init__ now also rejects an empty key outright, on one of the two routes by which a new key enters the aggregate; the other is Rom.follow_group, by which a row a complete view leaves out takes its key, and it refuses an empty key itself (loading a stored row via _row_to_rom in adapters/repositories/rom.py validates nothing) — a guard on the write direction, not a repair of what is already stored. That is what lets the residency readers — the ones asking "does this row carry a key?" — all test is not None. It is not licence to rewrite the coalesces, which use the key or fall back to a computed one (domain/shortcut_data.py, services/prune/preview.py, services/prune/results.py): or is correct there, and shortcut_data's is precisely the guard that has kept an empty key out of the column. The array dimensions are JSON-array TEXT (json_valid-checked, decoded to tuples); is_main_sibling is a STRICT 0/1 INTEGER. The platform display name is not modeled as a local aggregate; platform_slug is a denormalized RomM slug and the display name is resolved live from RomM (per ADR-0003), cached for offline reads in the kv_config platform_names row. cover_path is the absolute path to the ROM's cover in the per-ROM cover cache ({runtime}/covers/{rom_id}.png) — keyed by RomM id, so every version of a sibling group keeps its own cover; the active version is additionally copied onto the Steam grid as {app_id}p.png for the shortcut tile (#1346, ADR-0021). Legacy rows may still hold a grid {app_id}p.png path and are read as-is (no migration). cover_source (migration 016) is the nullable cover-cache fingerprint (#1386): the full RomM cover source string (path_cover_large else path_cover_small, the ?ts=… cache-buster included) whose bytes the cache file holds, adopted via Rom.adopt_cover_source (stored verbatim — the sync compare is an exact opaque-string equality). last_fetch_id (migration 020) is the nullable fetch generation that last saw this row (#1504), recorded via Rom.record_fetch_generation. It rides the sync UPSERT, but like cover_source the value written is a confirmed-else-preserved merge: a platform unit's apply supplies the run id and advances every row it commits (on every chunk, so the whole unit shares one generation), while a collection unit supplies none and the existing value carries forward — a collection spans platforms, and re-marking a foreign platform's row would make that platform's removed-game discovery read it as a row its last complete fetch did not return. The platform incremental skip's backfill gate reads only the rows whose last_fetch_id equals its stamp's fetch_id, so a superseded row (a rom_id the server dropped, retained unbound per ADR-0007) never demands a backfill no fetch can deliver, without being deleted (Backend Architecture, "Incremental skip"). NULL = unknown (a pre-migration row). It rides the sync UPSERT like cover_path, but the value the commit writes is always the confirmed-else-preserved merge (the artwork layer's confirmed value for this unit, else the existing row's) — never blindly the fetch's fresh string — so a failed cover download keeps the old fingerprint and the change is retried. NULL = unknown: a pre-migration row with an existing cache file is adopted without a re-download (no upgrade thundering herd); a mismatch re-downloads the cache, republishes the grid copy, and feeds the frontend's in-session tile re-apply (see Backend Architecture — cover-cache invalidation). fs_size_bytes (migration 021) is the nullable server-reported size of the whole game in bytes (#1395), written by the sync and the version switch; no feature reads it (migration 042 reads it once, to fill the size of the installs made before it). Like the version dimensions — and unlike the pin columns — it rides the sync UPSERT and refreshes every sync, set directly from the fetched dict with no confirmed-else-preserved merge. RomM states 0 for a ROM it holds no file for (a physical game, or one whose file rows are gone). NULL = unknown (a pre-migration row, or a wholesale-skipped platform not yet re-applied). has_file_on_disk (migration 026) is RomM's own flag for whether it holds a file for the ROM — false for one whose file left its disk and for a physical game, either of which RomM answers a download of with a 404 (#2196). Server-derived like fs_size_bytes: it rides the sync UPSERT, set from the fetched dict with no merge, and the skip reconstruction carries the stored value. The version picker and the play button read it without a request; the representative chain reads the fetched value, and the download refuses on RomM's live answer rather than on this column (Steam Non-Steam Shortcuts — a bound ROM RomM holds no file for). delivered_through (migration 029) is the nullable kind of sync unit — platform or collection — whose commit last upserted the row (#2407), recorded via Rom.record_delivery. It rides the sync UPSERT with the confirmed-else-preserved merge: a commit names the unit's kind, and one that names none keeps the row's value. The end-of-run stale removal reads it for a bound row on a platform the run did not read (Backend Architecture, "The stale removal keeps a game gone from RomM"). NULL = no commit that named its unit has upserted the row since the migration. A version switch records no route, so a version it first stores reads NULL, and one a sync had delivered keeps that sync's route. gone_from_romm (migration 030) is the nullable local evidence that RomM no longer serves a version the removed-game cleanup's stamps cannot name (#2431): not_found for a version whose exact id RomM answered with a proven 404 (the sync's question for one reached through a collection, or the cleanup's proof), delisted for a version kept on a platform RomM no longer lists. The apply's end-of-run stale removal sets it through Rom.mark_gone_from_romm and clears it through clear_gone_from_romm when its exact-id question finds the ROM; the removed-game cleanup's run also sets and clears it, through the same verbs, by its own exact-id proof (Removed-game cleanup has which rows and when). It also rides the sync UPSERT with no merge, so any commit of the row, which writes the row as RomM delivered it, clears it with no verb, while every read-change-save carries it. Discovery only, never deletion authority (Removed-game cleanup). NULL = no such evidence. platform_fs_slug (migration 043) is the nullable folder RomM keeps the game's platform in (#2223 D45), read off the ROM's own payload. It rides the sync UPSERT with the confirmed-else-preserved merge, so a dict the incremental skip rebuilt from stored rows, which carries none, keeps the stored value. The game page's BIOS tab asks the firmware store by it, never by the slug. NULL = nothing read from RomM has written it since the migration.
RomInstall (domain/rom_install.py)	rom_id, file_path, rom_dir?, platform_slug, system, installed_at, fetched_size_bytes?	Exists only while a ROM is downloaded — created on download-complete, removed on uninstall. References Rom by rom_id. file_path is the launch target (always present); rom_dir is the dedicated per-ROM directory, set for every game that lands in a folder of its own, and NULL only for a game RomM holds as one file, kept as it arrived (which lives as a bare file in the shared <roms>/<system>/ dir) — single-vs-multi is read from rom_dir presence, not re-derived from the path (ADR-0008). Denormalized platform_slug / system let the home migration and the save answer read installs without joining the registry. fetched_size_bytes (migration 042) is RomM's size of the files a download of the game fetches (an adoption records the same figure) — a download leaves a game's extras on the server, so this differs from Rom.fs_size_bytes — NULL where RomM stated none; the Data Management page sums it. A per-file RomFile[] child (category: game/dlc/update/…) is the documented future model, deferred to #1125 / #2035 / #129 and additive when it lands (ADR-0008).
RomMetadata (domain/rom_metadata.py)	summary, genres, companies, first_release_date?, average_rating?, game_modes, player_count, cached_at, steam_categories	7-day staleness signal (cached_at), regenerated independently of library sync — staleness, not a schedule, prompts a refresh. Per-ROM, keyed by rom_id.
RomSaveSyncState (domain/rom_save_sync_state.py)	active_slot?, slot_confirmed, emulator, system, last_synced_core?, own_upload_ids?, slots{}, files{} (a FileSyncState value object per filename), last_sync_check_at?	Per-ROM saves aggregate. Matrix invariants hold inside: a file baseline always carries a non-empty last_sync_hash, and adopt_baseline, the only way a baseline is recorded, requires a server save id — a stored baseline with a NULL tracked_save_id is a hash-only adopt an earlier version recorded; a non-legacy active_slot always has its slots key. Per-ROM, keyed by rom_id.
AnsweredSaveDirectory (domain/answered_save_directory.py)	rom_id (identity), directory	The save directory the resolver last answered for one ROM. Compared with today's answer to notice that the directory moved, and read as the source of the move that follows — never where a sync or a probe looks (Following a moved save directory). A thin record built whole by record, upserted, and deleted where a re-record finds an answer the follow cannot act on. Why it is an aggregate of its own: the 024 paragraph under Adding a migration past v1.
ArtworkSlotOutcome (domain/artwork_slot_outcome.py)	(rom_id, slot) (identity), outcome, sgdb_id?, recorded_at, image_id?, image_sha256?	What was last found for one ROM's wide grid, logo, hero or shortcut icon: set, none or refused — and for its cover only set, where the image came from SteamGridDB, the SteamGridDB id it was found under, and when. Whether it rules the slot out is rules_out; the rule is Game page artwork. A thin record built whole by record_set / record_none / record_refused, upserted; Refresh Artwork deletes a game's none and refused records and keeps its set ones (Game page artwork).
ArtworkSlotChoice (domain/artwork_slot_choice.py)	(rom_id, slot) (identity), image_id, chosen_at	The SteamGridDB image a user chose for one of a ROM's artwork slots; no row is a slot on automatic unless an image is set in Steam (#2381 D8). Kept apart from ArtworkSlotOutcome, which Refresh Artwork, the artwork job and a version switch rewrite or delete, so none of them has to spare a choice. A thin record built whole and upserted.
ArtworkSlotHandover (domain/artwork_slot_handover.py)	(rom_id, slot) (identity), image_id, image_sha256, handed_at, previous_choice_id?	The SteamGridDB image Tender last handed to Steam for one of a ROM's wide grid, logo, hero or cover, kept until Steam's answer is reported, so a grid file holding it reads as Tender's however the apply ended, and the image the user had chosen for the slot then, which a refused chosen image gives back (#2381 D15, D20). Kept apart from ArtworkSlotOutcome, whose record is built whole per outcome. A thin record built whole by hand_over and upserted; a set or refused report for its image deletes it.
Playtime (domain/playtime.py)	total_seconds, session_count, last_session_start?, last_session_start_monotonic?, last_session_duration_sec?, last_played?, pending_sessions{} (a PendingPlaySession outbox row per session-start timestamp)	Per-ROM, owned by PlaytimeService. Spans two tables — the scalars (rom_playtime) plus the pending-session outbox (rom_playtime_sessions). Independent lifecycle from saves (session_lifecycle.py already treats them as separate concerns). Keyed by rom_id. Session duration is the awake-only span: begin_session stores a Clock.monotonic() start in last_session_start_monotonic, and record_session counts monotonic_end minus that start (the monotonic clock pauses during suspend, so sleep time is excluded, #1148), clamped to the wall span and to 0–24 h; a missing/negative/out-of-range monotonic delta falls back to the full wall span (pre-#1148 behavior, never a regression). Three scalars fold in via monotonic max() clamps that never regress — total_seconds (reconcile_total), session_count (reconcile_session_count), and last_played (reconcile_last_played, compared by parsed instant, not lexically): session-end folds the local duration, stamps last_played, AND enqueues the session into the outbox; reconcile-on-view flushes the outbox to RomM's native /api/play-sessions ingest and folds the summed cross-device server total, the session count, and the newest server end time back in, so a fresh device restores all three (#903). The outbox dequeues on a successful (or duplicate) ingest and survives offline; each row is grouped by its stored device_id for the POST, and its attempts counter drives a bounded-retry quarantine (dropped after the threshold of consecutive error verdicts) so a permanently-rejected session cannot wedge the outbox (ADR-0018).
BiosFile (domain/bios_file.py)	(platform_folder, file_name) (composite identity), file_path, downloaded_at, firmware_id?	Per downloaded BIOS file. Composite key — a bare filename is unsafe (two platforms can ship same-named BIOS). firmware_id is nullable metadata, not identity.
FirmwareCacheEntry (domain/firmware_cache.py)	id?, name, platform_folder, platform_slug, file_size_bytes, md5_hash, cached_at	Per cached firmware item from RomM. TTL-cached server inventory; the cache is replaced wholesale on refresh and the TTL check lives in the service, so the aggregate stays a thin record.
StoredFirmware (domain/firmware_store.py)	(platform_folder, file_name) (composite identity), folder, md5, size, downloaded_at, platform_slug, firmware_id?	Per file in Tender's firmware store, keyed by the folder RomM keeps the platform it was downloaded for in (fs_slug) and its name in RomM; the platform's slug stands beside the key. md5 is the checksum the download was checked against and is never empty.
FirmwareLink (domain/firmware_store.py)	place (identity), target, platform_folder, file_name, source, placed_at	Per link Tender placed into an emulator source, keyed by its absolute place. The store file is named, not referenced: a link Tender could not remove with its store file keeps its record until the link goes.
SyncRun (domain/sync_run.py)	id, started_at, status, platforms_planned, roms_planned, finished_at?, platforms_completed?, collections_completed?, error?	Models sync-as-operation — a running → completed/cancelled/interrupted/paused/errored state machine that terminates exactly once (cancelled = user intent; interrupted = external death — the frontend heartbeat timed out or the backend restarted mid-run; paused = the session-budget guard stopped a large run mid-flight so it can be resumed). Replaces scattered scalars (last_sync, sync_stats, last_synced_platforms, last_synced_collections). sync_stats.roms is not a field — it's a registry-derived count computed at read time. The history is the source of the "Last sync" display (newest completed run + newest terminal last-attempt) and feeds no skip gate, so "Force Full Sync" deliberately preserves it (#1318).
CollectionSyncState (domain/collection_sync_state.py)	(collection_id, collection_kind) (identity), updated_at, completed_at, rom_count, member_rom_ids, fetch_started_at?, listing_rom_id_checksum?	Per-collection "this collection fully synced" completion stamp (#742) for all three collection kinds — the collection sibling of PlatformSyncState. Written when a collection work unit's last apply chunk commits (atomically in the same write UoW as that chunk's roms upserts). fetch_started_at and listing_rom_id_checksum (migration 032, #2477) are what the incremental-skip gate compares RomM's answers with: when the completing apply began fetching the collection, on Tender's clock — the scoped updated_after probe's reference is that less ten minutes — and a checksum of the rom_ids the collection listing carried, which leaves out the ids a walk answered beyond them. NULL in either = a stamp written before 032, which never skips. A standard or smart collection's gate also compares updated_at; a virtual collection's updated_at is the empty string, since its gate never reads it. The full rule is in Backend Architecture, "Incremental skip". completed_at is the final chunk's commit time, read by no skip-side reader, and rom_count is the count RomM reported when the run that wrote the stamp planned its work, which the skip no longer reads. Unlike a platform, a collection has no local membership column to reconstruct from (roms.platform_slug is per-platform), so member_rom_ids stores the full member set at completion — a skipped run replays it into the run's synced_rom_ids and Steam-collection map, resolving each id through the registry (the same sibling-group fallback the run finalizer uses). A thin record built whole and upserted — no field mutation. Unlike a platform stamp it has no revoked state and is deleted outright; which flows delete it, and why, is in Backend Architecture, "Incremental skip".
PlatformSyncState (domain/platform_sync_state.py)	platform_folder (identity, the platform's fs_slug), completed_at, rom_count, fetch_id?, skip_revoked, fetch_started_at?, rom_id_checksum?	Per-platform "this platform fully synced" completion stamp (ADR-0023). Written when a platform work unit's last apply chunk commits — atomically in the same write UoW as that chunk's roms upserts. The incremental-skip gate honours it in place of the library-wide last_sync, so a platform that fully synced inside a run the user later cancelled/crashed (which leaves no completed SyncRun, so the library-wide last_sync never advances) still skips on the next run. fetch_started_at and rom_id_checksum (migration 031, #743) are what the gate compares RomM's one answer with: when the completing apply began fetching the platform, on Tender's clock — the skip's reference is that less ten minutes — and a checksum of the sorted ids that fetch returned. NULL in either = a stamp written before 031, which never skips. completed_at is the final chunk's commit time, read by no skip-side reader. rom_count is the count RomM reported when the run that wrote the stamp planned its work; the skip no longer reads it, and removed-game discovery reads a zero as an empty completed fetch. A thin record built whole and upserted — no field mutation. The contract is a stamp may authorise a skip ⟺ the platform's most recent apply attempt ran to completion, and neither a local removal nor a stale removal on a run that did not process the platform has unbound one of its rows or removed its shortcut since, so a stale stamp can never skip a half-mirrored platform (unbinding keeps the row, ADR-0007, and changes nothing RomM answers, so RomM's id set still matches the stamp after a partial re-apply or a local removal): the orchestrator deletes the stamp at a platform unit's apply start (an interrupted re-apply leaves none; the final chunk re-writes it), those two removals revoke its skip — the local destructive flows (report_removal_results remove-all / per-platform removal, and reconcile_live_shortcuts) on every platform they touch, in the same write UoW as their unbind, and the run finalizer's server-side stale removal on every platform the run did not process that it removes a row's shortcut on, in the finalize's write UoW — and "Force Full Sync" clears every stamp wholesale (the stamps are the fetcher's sole skip authority, so clearing them arms the full re-fetch; the SyncRun history is left intact, #1318). skip_revoked (migration 025) is that revocation: set at runtime only by the repository's revoke_skip (an UPDATE of the one column, a no-op without a row), never by the aggregate (025 also set it once on every stamp it found), and cleared by the next save of the platform, since a fresh stamp never carries it. A revoked stamp reads as no stamp to the skip-side readers (stamp_for_skip, and has_any for the resume offer) and as a stamp to the rest. (Which readers are which, why the stamp is kept rather than deleted, and why the stale removal leaves the skip of a platform the run processed, is in Backend Architecture, "Incremental skip".) fetch_id (migration 020) is the nullable fetch generation the completing apply marked every one of its rows with (#1504); the skip requires rows carrying it and reads its backfill gate over them only, and removed-game discovery tells the rows the fetch returned from the rest by it, so a superseded row retained per ADR-0007 holds nothing off. It is an opaque generation id (the run id — a platform is fetched at most once per run) rather than a timestamp comparison against completed_at, because the apply is chunked (ADR-0023) and each chunk commits with its own clock reading while the stamp is built at the final chunk: on any platform whose delta exceeds one chunk the earlier chunks' rows would fall before completed_at, so the skip and discovery would both read them as rows the fetch did not return. NULL = unknown (a stamp written before migration 020): such a stamp predates 031 too and never reaches the skip, and discovery finds no candidates under it.

FileSyncState (inside RomSaveSyncState) is a value object, not an aggregate: a frozen @dataclass(frozen=True, slots=True) built whole by adopt_baseline(...), with no mutation surface of its own.

Reserved naming — savestate sync. RomSaveSyncState tracks save files (SRAM — RomM /api/saves). If savestate sync (RomM /api/states) is ever built, its aggregate is already named: RomSavestateSyncState (module domain/rom_savestate_sync_state.py, table rom_savestate_sync_states). Terminology is fixed: "save" always means save file, "savestate" (one word) always means the emulator snapshot; neither is ever called plain "state" in code.

The SQLite schema

The tables that back the aggregates, designed in #780. The authoritative DDL — every column type, default, constraint, and the full decision rationale inline — is backend/db/migrations/001_initial.sql. This section is the map, not a re-derivation.

One table per aggregate

Each aggregate gets its own table — the per-ROM cluster is not a single wide roms mega-table. The epic floated a mega-table as a starting proposal; #780 owns the final layout and split it. The deciding factor was integrity, not speed (read performance is a non-issue at single-user scale): the per-ROM aggregates are all-or-nothing groups — an install is either fully present or absent, metadata is cached or not — and separate tables let "state absent" mean "no row" rather than a wide row of NULLs the schema cannot keep internally consistent. The rejected mega-table alternative is recorded in ADR-0002. One Repository per aggregate (the GLOSSARY.md rule) maps 1:1 onto these tables.

Table	Backs	Key	Row present when
roms	Rom (identity + shortcut)	rom_id	ROM is synced from RomM
rom_installs	RomInstall	rom_id	ROM is downloaded
rom_metadata	RomMetadata	rom_id	metadata has been cached
rom_playtime	Playtime (scalars)	rom_id	ROM has been played
rom_playtime_sessions	PendingPlaySession (1:N child)	(rom_id, start_time)	a session awaits native ingest
rom_save_sync_states	RomSaveSyncState (scalars)	rom_id	save tracking exists
answered_save_directories	AnsweredSaveDirectory	rom_id	an answered save directory was recorded
artwork_slot_outcomes	ArtworkSlotOutcome	(rom_id, slot)	a slot's outcome was recorded; a cover's as set or none
artwork_slot_choices	ArtworkSlotChoice	(rom_id, slot)	a user chose an image for a slot
artwork_slot_handovers	ArtworkSlotHandover	(rom_id, slot)	Tender handed Steam an image for a slot
rom_save_files	FileSyncState (1:N child)	(rom_id, filename)	a file baseline is tracked
downloaded_bios	BiosFile	(platform_folder, file_name)	a BIOS file is downloaded
firmware_cache	FirmwareCacheEntry	(platform_folder, name)	firmware inventory is cached
firmware_store	StoredFirmware	(platform_folder, file_name)	a file is in Tender's firmware store
firmware_links	FirmwareLink	place	Tender placed a link there
sync_runs	SyncRun	id	one row per sync run (history)
platform_sync_state	PlatformSyncState	platform_folder	a platform fully synced
collection_sync_state	CollectionSyncState	(collection_id, collection_kind)	a collection fully synced
kv_config	misc singleton scalars	key	per key

SyncRun carries its own invariants, so per GLOSSARY.md it gets a typed table rather than untyped kv_config rows. The full live kv_config key set is device_id (the server-issued device identity), platform_names (the JSON-encoded fs_slug → name cache, each name as Steam Non-Steam Shortcuts says), platform_ids (each listed platform's record, keyed by its folder in RomM (fs_slug) — see core-emulator-selection.md), retrodeck_home_path (+ its pending-migration _previous, and — when the home is changed again before the migration runs — a _hops JSON array of the additional pending homes, oldest→newest, so files under an intermediate home are never stranded, #1042), save_directories_recorded (present once the one-time pass that records the answered save directories of the installed ROMs has finished over a detected emulator installation with no ROM failing), shortcut_launcher_relocated (present once a reading of Steam's shortcut file found no shortcut of ours naming a launcher outside the launcher's home), ps3_folders_to_name (the JSON list of the rom_ids whose PS3 folder the one-time naming step renames, fixed at its first pass) and ps3_folders_named (present once that step left none of them to rename), ps3_folders_to_lift (the JSON list of the rom_ids whose PS3 game folder, one level inside its install's folder, the one-time lifting step puts in that folder's place, fixed at its first pass) and ps3_folders_lifted (present once that step left none of them to lift), playtime_scope_notice (present while RomM refuses the token the playtime read needs, cleared by a later read that succeeds or a fresh sign-in), update_check_last_seen (what the release checks last established), last_run_version (the version the previous start ran as), update_available_seen_version (the release the user has seen in Settings › Updates), and the three marks of an update toast already raised — update_available_toasted_version (the release told about), update_failure_toasted_at (the installer's record of a failed update) and update_stopped_toasted_at (an installer found stopped) — the truly miscellaneous singleton scalars. The platform_names cache is a single JSON blob the library sync refreshes every run so five reads need no RomM: the game-detail platform name, a platform removal's answer, whose name the frontend finds that platform's Steam collection by, the next sync's name, in its preview as in its apply, for the Steam collection of a platform RomM no longer lists, whose kept games stay in it, a BIOS row a link of Tender's into another platform's store folder serves, and the platform system (PlatformSystemService), whose name its no_platform_system refusal, the pages that show the system, and a download's queue entry and every frame and event it sends, download_failed included, carry (#2573 D1). Each refresh writes the name of every platform the run's own platform listing names, its sync on in Tender or not — RomM's name, never a custom name set in RomM (#2564 D1, D2) — and keeps the cached name of every other platform a bound game is still on, a stopped run's refresh included. The schema version is not a kv_config key — it is tracked in PRAGMA user_version by the migration runner (#781).

SyncRun is a history table, not a single "last run" row: a 1-row table would let a newly-started run (status='running', no stats yet) erase the last completed run's displayable stats. "Last successful sync" is the newest row with status='completed'; "is a sync running" is any row with status='running'.

A download whose file is missing

A rom_installs row records a download, not that its file is still on disk. A deleted file, a moved ROM folder and an unmounted drive look the same from here, and only the first is gone for good, so a missing file leaves the row in place: start-up only logs it, and the game page says the file is missing, names the path (file_missing_at) in the play button's menu and when the forget asks to be confirmed, and leaves the row to the user's Download again (replaced through the download-complete writer once the new download lands) or Forget this download (dropped through the uninstall's writer, no file touched).

Foreign keys

Most relationships are not parent-child (an install record outlives its file on disk; playtime survives shortcut removal), so foreign keys are deliberately sparse:

• Per-ROM tables → roms, ON DELETE CASCADE (rom_installs, rom_metadata, rom_playtime, rom_playtime_sessions, rom_save_sync_states, rom_save_files, answered_save_directories, artwork_slot_outcomes, artwork_slot_choices, artwork_slot_handovers). Per-ROM state is genuinely owned by the ROM, so a DELETE FROM roms WHERE … cascades it all away in one statement. Per ADR-0007 this cascade is dormant: a roms row is a permanent identity anchor keyed by RomM's stable rom_id. Auto-stale = unbind — once the panel reports the shortcut the sync removed gone from Steam (or Steam's own removal is reconciled), the row is unbound (Rom.unbind_shortcut() NULLs shortcut_app_id, the row and its per-ROM children stay) so local playtime/saves/metadata survive. Only a deliberate purge = delete — the explicit Clean Up Removed RomM Games action may delete a row after a fresh exact-ID 404, option checks, save/content guards, any enabled recovery bundle, and required Steam acknowledgement. Its final short UoW revalidates the expected group and binding before repository deletion lets the cascade reap all eight child tables. Installed-content cleanup before that UoW is filesystem-only; rom_installs and every other child remain until the parent cascade, so a failed filesystem phase leaves retryable aggregate state. A confirmed shortcut removal is reconciled to an unbound row before finalization, making a later safety refusal an explicit committed partial rather than a stale binding. The automatic sync never DELETEs a roms row. Collection completion stamps whose member_rom_ids intersect a purged id are deleted in that same UoW. The platform completion stamp is deliberately preserved; the reasoning lives in removed-game-cleanup.md.
  • Caveat — writes to a cascade parent (roms) MUST UPSERT, never INSERT OR REPLACE/REPLACE. In SQLite REPLACE resolves a PK conflict by delete-then-insert of the parent row, and that DELETE fires the ON DELETE CASCADE above — silently wiping every per-ROM child on a re-save (a normal library re-sync, where the roms row already exists). SqliteRomRepository.save() therefore uses INSERT … ON CONFLICT(rom_id) DO UPDATE SET …, which updates the parent in place and never triggers the cascade (#887). Leaf tables with no cascade children may keep INSERT OR REPLACE.
• platform_slug → no FK. Carried on roms / rom_installs / firmware_store / firmware_cache as a plain denormalized RomM platform slug, and so is the platform's folder in RomM (roms.platform_fs_slug, firmware_store / firmware_links / firmware_cache / downloaded_bios's platform_folder). There is no platforms table to reference — ADR-0003 dropped the Platform aggregate — so it is just a string. The platform's name is not stored on the row: it lives in the kv_config platform_names cache (One table per aggregate says what writes and reads it, keyed by fs_slug), and a reader with no name cached takes the slug where it names a game's platform (the game page, a removal's answer, the cores page, a game with no fs_slug recorded) and the bare fs_slug where it names a Steam collection, a preview row or the platform a BIOS link serves; which name the platform system takes is core-emulator-selection.md's.

The split moved the FK policy from the epic's "one FK only" (written for the mega-table world) to "CASCADE for the per-ROM ownership relationships the split introduced; no FK for cross-aggregate references" — same intent, applied to the new tables. See ADR-0002.

Type conventions

All tables are STRICT (SQLite ≥ 3.37; the Deck ships 3.50). STRICT allows only INTEGER / REAL / TEXT / BLOB / ANY, so:

• Booleans are INTEGER 0/1, guarded by CHECK (col IN (0, 1)).
• Event timestamps are TEXT ISO-8601 (sortable, human-readable); cache/TTL timestamps are REAL Unix-epoch seconds (cheap age math). The split is aggregate-driven — only the caches do age arithmetic.
• JSON arrays/objects are TEXT guarded by CHECK (json_valid(col)). They are display/read-model data, never queried by element, so normalization buys nothing.
• rom_save_sync_states.own_upload_ids is nullable TEXT where NULL ≠ '[]': NULL means attribution unknown/legacy, '[]' means we uploaded nothing — both meaningful.

No blanket created_at/updated_at audit columns (the aggregates already model the timestamps that matter), and no systems lookup table (system stays TEXT). The only secondary index is the partial unique index idx_roms_shortcut_app_id on roms(shortcut_app_id) WHERE shortcut_app_id IS NOT NULL (migration 003), added for correctness rather than tuning: it makes "two bound rows share one Steam appId" impossible (the #1036 collision — see the Rom aggregate row). It is partial so multiple unbound rows keep a NULL appId without colliding. Pure read-performance indexing is still deferred until profiling justifies it, per the epic.

The migration framework

The schema above is not loaded as a special case — it is migration 001, applied by the same runner that applies every future schema change. The runner lives in backend/adapters/sqlite_migrations.py (#781) — it does file + database I/O, so it is an adapter — and is invoked from bootstrap() at backend start, before any service is wired. stdlib sqlite3 only; no Alembic or other third-party migration tooling.

Versioning — PRAGMA user_version. SQLite keeps a single integer in the database header, readable and writable via PRAGMA user_version. The runner uses it as the applied-schema marker: a fresh database reports 0; after migration NNN is applied the runner stamps user_version = NNN. There is no separate schema_migrations table — user_version is the whole mechanism (the same lean approach SDH-PlayTime and Junk-Store use). This is why the schema version is deliberately not a kv_config key.

Discovery — NNN_descriptive_name.sql. Migrations are plain .sql files under backend/db/migrations/, named with a leading integer (001_initial.sql). At startup the runner scans that directory, parses the integer prefix off each filename, sorts ascending numerically (so 10 follows 2, not lexically), and applies only the files whose number is greater than the database's current user_version. Files that don't match NNN_*.sql are ignored.

Atomic per migration. Each migration runs inside its own transaction: BEGIN → the migration's DDL → PRAGMA user_version = NNN → COMMIT. The version bump rides the same transaction as the DDL, so a migration is all-or-nothing: if any statement fails, the transaction rolls back (DDL and version bump both undone) and the runner re-raises, leaving the database at the last successfully-applied version. Migration files therefore contain transaction-safe DDL only and must not carry their own BEGIN/COMMIT — the runner supplies the transaction.

Connection PRAGMAs. The runner sets journal_mode=WAL (persistent — recorded in the database file, so it carries over to runtime connections) and foreign_keys=ON (so CASCADE-bearing DDL behaves here as it will at runtime). The full per-connection PRAGMA set for runtime Unit-of-Work connections is applied by the UoW itself (see The runtime Unit of Work below): foreign_keys=ON, synchronous=NORMAL, busy_timeout=5000, temp_store=MEMORY, with isolation_level=None so the UoW drives BEGIN/COMMIT/ROLLBACK explicitly.

Database location. The database is romm-tender.db in this program's own data root — ~/.local/share/romm-tender/ by default — reached through bootstrap()'s directories.data_dir and never composed at a call site. It used to live in a directory a plugin loader derived from the delivered folder's name, so a release that renamed the folder moved the database with it; where the directories are is now resolved once from the environment and handed in, so no name can move it again. The ladder is on Backend Architecture → Where user data lives (ADR-0036).

The live path reads and writes it; DB-init is hard-failing (a migration failure aborts startup rather than degrading silently) so a corrupt or unmigratable database never serves stale reads.

The old name. Up to 0.33 the file was romm_sync.db, the name it had before the program was called Tender. On every start, before the schema runner opens anything, bootstrap() hands both paths to adapters/database_rename.py, which moves a romm_sync.db with no romm-tender.db beside it. It first opens the old file under its own name, reads from it and closes it: the last connection to close, once it has read the database, folds the WAL into the file and removes -wal and -shm — which SQLite finds by the database's own name — so only the main file is left to rename. A connection that reads nothing leaves both behind. The rename is one atomic step, so an interruption leaves one complete database under one of the two names, and it never replaces an existing name (renameat2 with RENAME_NOREPLACE), so a romm-tender.db that appeared meanwhile is never overwritten.

Two things about where it runs are deliberate. It runs before the schema runner, which would otherwise create an empty romm-tender.db beside the library. And it runs in bootstrap() rather than the entry point, so on a start it runs under the single-instance lock: a second backend refused a moment later cannot have renamed the file under the running one.

• Both names there: the backend starts on romm-tender.db, leaves romm_sync.db exactly as it is, and logs a warning naming both. Nothing is deleted.
• An old file SQLite cannot open, or one whose WAL survives the close because it is open elsewhere or cannot be written: nothing is renamed, the error is logged, and the start fails, as a database the schema runner cannot open fails it. It never starts on an empty romm-tender.db beside a library it could not move.
• Only the exact name moves; a copy made by hand such as romm_sync.db.backup-<date> stays where it is.

The step stays in every later release, as every one-time step does (invariants): with no old file it does nothing, so keeping it costs nothing, and someone going from 0.33 straight to a later version still gets it. The pre-install check builds on a copy taken under whichever name a start would open, so it renames the copy as a start would (Running an installed one).

Adding a migration past v1

Drop a new file NNN_descriptive_name.sql into backend/db/migrations/ containing the schema change (e.g. ALTER TABLE roms ADD COLUMN …; or a fresh CREATE TABLE …;) as transaction-safe DDL with no BEGIN/COMMIT. That's the whole change — on the next startup the runner sees NNN > user_version, applies it inside its own transaction, and bumps user_version to NNN. Existing databases receive only the new migrations; fresh databases receive all of them in order. No code change is needed to register the file.

Foreign keys stay on for every migration and a migration cannot turn them off — the runner refuses one that contains PRAGMA foreign_keys — so a table another table references cannot be rebuilt through the runner as it stands. Why, and what such a rebuild needs from the runner first, is in apply_migrations' docstring.

The first migration past 001 is 002_add_emulator_override.sql — a single ALTER TABLE roms ADD COLUMN emulator_override TEXT; for the per-game core override (ADR-0011), which stamps user_version = 2.

003_unique_shortcut_app_id.sql (user_version = 3) adds the partial unique index idx_roms_shortcut_app_id so one Steam appId is bound to at most one ROM (#1036). Because an existing database may already hold a duplicate-appId collision (an older version of Tender, or a server switch / re-import that re-bound a shortcut's appId onto a new rom_id without unbinding the old row), the migration first de-dups — keeping the binding on the newest MAX(rom_id) per appId and unbinding (NULL, never delete — ADR-0007) the older colliding siblings — so the index can build cleanly on upgrade.

004_add_selected_disc.sql (user_version = 4) adds the nullable selected_disc TEXT column to roms for the per-game disc picker — a single ALTER TABLE roms ADD COLUMN selected_disc TEXT; with no backfill. It is the second pin-only column on roms (after emulator_override): NULL means "follow the default disc," only pin/clear ever write it, and it is excluded from the sync UPSERT so a re-sync never resets a user's pick (ADR-0014).

005_unconfirm_legacy_slot_confirmations.sql (user_version = 5) flips slot_confirmed back to 0 for any ROM confirmed into the retired legacy slot:null mode (active_slot IS NULL AND slot_confirmed = 1) so the first-sync wizard reappears — a pure UPDATE, never deleting rows or baselines (#1276).

006_native_play_sessions.sql (user_version = 6) moves playtime off the retired RomM note onto RomM's native /api/play-sessions ingest (ADR-0018): it creates the rom_playtime_sessions outbox table (STRICT, PRIMARY KEY (rom_id, start_time), REFERENCES roms(rom_id) ON DELETE CASCADE, plus an attempts INTEGER NOT NULL DEFAULT 0 bounded-retry counter) and drops the now-readerless rom_playtime.note_id column (ALTER TABLE rom_playtime DROP COLUMN note_id; — SQLite ≥3.35, the Deck ships 3.50). Existing server notes are left in place, harmless. The outbox has three wedge-prevention paths. A 2xx per-session skipped — the server's explicit rejection of that exact (device, rom, start_time) window (e.g. a sub-second launch-death it refuses on validation) — is dropped immediately via Playtime.drop_rejected_sessions with a single info log, without touching attempts: re-POSTing the byte-identical row would draw the same verdict forever. A whole-request HTTP 422 — RomM validates the sessions array atomically, so one invalid entry rejects the entire POST (#1312) — is healed surgically: the adapter surfaces it as RommUnprocessableEntityError with the parsed detail, the pure rejected_session_indices kernel reads the failing detail[].loc[2] positions, PlaytimeService drops exactly those rows (again via drop_rejected_sessions) and resubmits the survivors, so one poison entry never blocks the batch; a multi-row 422 that names no usable index (a proxy-mangled body) re-submits each session on its own (_flush_single_session) so the per-session verdict isolates the genuine poison and a valid sibling is never dropped for another's fault, and a lone row bumps only its own attempts. New sub-second poison is kept out of the outbox at the recording seam by the pure is_ingestable_session gate (window strictly post-start at second resolution — the same rule RomM applies). Separately, each outbox row's attempts counts consecutive ingest error (or unknown acknowledged) verdicts; PlaytimeService quarantines (drops) a row once it reaches its retry threshold, so an ambiguous never-succeeding verdict also cannot wedge the outbox. Either way only that session's playtime is lost.

007_add_last_played.sql (user_version = 7) adds a nullable last_played TEXT column to rom_playtime (ADR-0018, #903). session_count already existed; this closes the remaining #903 gap so a fresh device restores the last-played timestamp — derived by reconcile as the newest server end_time — not just the cumulative total. Additive ALTER TABLE … ADD COLUMN; NULL until a session is recorded or a reconcile folds a server value in.

008_add_version_metadata.sql (user_version = 8) adds the RomM sibling-group key + version dimensions to roms (ADR-0021, #1295): a nullable sibling_group_key TEXT, three json_valid-checked JSON-array TEXT columns (regions / languages / tags, default '[]'), revision TEXT (default ''), and is_main_sibling INTEGER (default 0). Unlike the two pin columns, these are server-derived and ride the sync UPSERT — six additive ALTER TABLE roms ADD COLUMNs. The version dimensions default to empty for a pre-migration row; sibling_group_key stays NULL until the next sync backfills it (a bound ROM with a NULL key forces its platform's incremental-skip to fall through to a full fetch, so one sync backfills every row). The column holds a real key or NULL and never an empty string. That is not the new guard's doing: the later re-key (011) NULLed every pre-existing value, and extract_version_metadata's coalesce has replaced an empty one with a computed key on every write since. VersionMetadata.__post_init__ rejects an empty key on one of the two routes by which a new key enters the aggregate, and Rom.follow_group, the other, by which a row a complete view leaves out takes its key, refuses one itself. That guard on the write direction (_row_to_rom in adapters/repositories/rom.py loads a stored row verbatim and validates nothing) is what lets the residency readers ("does this row carry a key?") test is not None rather than truthiness. The coalesces that use the key else fall back to a computed one keep or, and must not be swept along with them.

009_add_last_session_start_monotonic.sql (user_version = 9) adds a nullable last_session_start_monotonic REAL column to rom_playtime (#1148). It stores the Clock.monotonic() reading captured when a session opens; the delta to the session-end reading is the awake-only span (the monotonic clock pauses during device suspend), so suspend time is excluded from the counted duration. Additive ALTER TABLE … ADD COLUMN; NULL until the next begin_session stamps it, and a NULL marker on a pre-migration row makes record_session fall back to the full wall span (the pre-#1148 behavior, never a regression).

010_add_sibling_group_key_index.sql (user_version = 10) adds a non-unique index idx_roms_sibling_group_key on roms(sibling_group_key) (ADR-0021, #1296). Group-aware sync persists every fetched sibling (not just the bound representative), so two hot paths now read roms keyed by sibling_group_key — the per-unit group collapse and the Steam-collection group fallback — which the index turns into a range scan. Non-unique on purpose: a sibling group has many rows sharing one key, and a NULL key (a not-yet-backfilled row) is its own solo group, so no partial WHERE clause is needed. Pure DDL — a single CREATE INDEX.

011_rekey_sibling_group_key.sql (user_version = 11) forces a one-time re-key after the derivation changed from coalesce-first to connected components over RomM's sibling_roms edges (ADR-0022, #1368): a single UPDATE roms SET sibling_group_key = NULL clears every existing key so the needs_backfill gate (any bound row with a NULL key) drops the incremental skip and the next sync recomputes every key under the new kernel. Pure DML, no schema change — the rows and every other field survive, and NULL is a tolerated transient on the read paths, so the window before that sync is safe (no data loss, only a re-derivation).

012_add_platform_sync_state.sql (user_version = 12) creates the platform_sync_state table backing the PlatformSyncState aggregate (ADR-0023, #1025): a STRICT table keyed by platform_slug TEXT PRIMARY KEY with a completed_at TEXT (ISO-8601, same comparison basis as sync_runs.finished_at) and a rom_count INTEGER. It is the per-platform completion stamp the incremental-skip gate reads so a platform that fully synced inside a later-cancelled run still skips. A leaf table with no cascade children — the row is upserted with INSERT OR REPLACE and the whole table is cleared by "Force Full Sync" (which leaves the sync_runs history intact, #1318). Pure DDL — a single CREATE TABLE. 044 rebuilds it keyed by platform_folder.

013_add_interrupted_sync_run_status.sql (user_version = 13) widens the sync_runs status CHECK with 'interrupted' (#1025): a run ended by an external death — the frontend stopped heartbeating (a steamwebhelper crash/reload) or the backend restarted mid-run — rather than by the user's Cancel. Previously both wrote 'cancelled', so a crash blamed the user for a failure they didn't cause. SQLite cannot ALTER a CHECK constraint, so sync_runs is rebuilt in place — create the widened twin, copy every row unchanged, drop the old table, rename (the table has no indexes and no incoming foreign keys, so the rename is the whole story). Existing rows keep their historical 'cancelled' status; only runs terminated after this migration carry the new value.

014_add_paused_sync_run_status.sql (user_version = 14) widens the sync_runs status CHECK once more with 'paused' (#1383, ADR-0024): a run stopped by the session-budget gate's own consented pause, distinct from the external-death 'interrupted'. Same in-place rebuild as 013.

015_add_applied_launch_options.sql (user_version = 15) adds the nullable applied_launch_options TEXT column to roms for the delta-restricted apply (#1383, ADR-0025) — a single ALTER TABLE roms ADD COLUMN applied_launch_options TEXT; with no backfill. It is the third pin-style column on roms (after emulator_override and selected_disc): NULL means "unknown" (never skipped, so a pre-migration row re-applies exactly like today and records its value), and only the six recorded-state writer sites ever write it via the pin-only set_applied_launch_options path — the sync UPSERT excludes it, so a re-sync never wipes the recorded value. Migration 041 replaces it with shortcut_applied.

016_add_cover_source.sql (user_version = 16) adds the nullable cover_source TEXT column to roms for cover-cache invalidation (#1386) — a single ALTER TABLE roms ADD COLUMN cover_source TEXT; with no backfill. It records the RomM cover source string whose bytes the per-ROM cover cache holds; sync compares it as an opaque string against the fresh fetch to detect server-side cover changes. NULL means "unknown": a pre-migration row with an existing cache file is adopted (the fresh fingerprint persists without a re-download, so the upgrade never mass re-downloads a library), and one with no cache file behaves as before. Unlike the pin columns it rides the sync UPSERT, but the commit always writes the confirmed-else-preserved merge — a fingerprint advances only when the cache was actually confirmed against the server.

017_add_last_sync_server_hash.sql (user_version = 17) adds the nullable last_sync_server_hash TEXT column to rom_save_files — the first schema change to a FileSyncState child (#1468) — a single ALTER TABLE rom_save_files ADD COLUMN last_sync_server_hash TEXT; with no backfill. It records the server's own content_hash for the save synced at the last baseline, so the save-sync identity check can compare two server-produced hashes (the stored one against the live server.content_hash) instead of relying on the client's local reimplementation of RomM's hashing — the provenance route, now primary. NULL means "no stored server hash": a pre-migration baseline (or a hash-only adopt an earlier version recorded) uses the parity fallback (local_hash == server.content_hash, #1457) until the next full sync stamps a value. Written only alongside last_sync_hash at the recorded-baseline writer sites (adopt_baseline), so a stored server hash always truthfully pairs with its last_sync_hash.

018_rename_rom_save_states.sql (user_version = 18) renames the save-sync scalar table rom_save_states -> rom_save_sync_states (#1478) so the table name matches the RomSaveSyncState aggregate and the "save" vs "savestate" terminology. A data-preserving ALTER TABLE … RENAME TO; the rom_save_files child is anchored on roms and is untouched.

019_add_collection_sync_state.sql (user_version = 19) creates the collection_sync_state table backing the CollectionSyncState aggregate (ADR-0023, #742): a STRICT table keyed by the composite PRIMARY KEY (collection_id, collection_kind) (a standard collection id and a smart collection id can collide, so the kind is part of the identity) with updated_at TEXT (the collection's server updated_at — the membership-stable signal), completed_at TEXT (ISO-8601, our sync time — the scoped updated_after member-content probe's reference until migration 032), rom_count INTEGER, and member_rom_ids TEXT (a json_valid-checked JSON array of the member rom ids). It is the per-collection completion stamp the incremental-skip gate reads so an unchanged standard/smart collection is not re-paginated — the collection sibling of platform_sync_state. A collection has no local membership column to reconstruct from, so member_rom_ids is stored to replay the member set on a skipped run. A leaf table with no cascade children — the row is upserted with INSERT OR REPLACE and deleted by the flows Backend Architecture, "Incremental skip", lists. Pure DDL — a single CREATE TABLE. Virtual collections are stamped too since #2356, which needed no migration (the CollectionSyncState row above says what their updated_at holds).

020_add_fetch_generation.sql (user_version = 20) adds the fetch-generation marker (#1504): roms.last_fetch_id TEXT (the fetch generation that last saw the row) and platform_sync_state.fetch_id TEXT (the generation the completion stamp was written for). It was added for the skip's row-count condition, which compared RomM's platform rom_count against only the rows carrying the stamp's generation and so left out a row for a rom_id the server had since dropped. Without it such a row inflated the count forever: RomM re-creating a ROM under a new id (re-import, file move, library rebuild) moves the shortcut to the new row and unbinds the old one, which is then retained as an identity anchor (ADR-0007), so the platform could never satisfy the condition again and full-fetched on every sync. The marker excluded the row from the count without deleting it — ADR-0007's retention is unchanged, and the rejected "auto-delete on sync-stale" alternative it records is exactly why a non-destructive marker was chosen. Two ALTER TABLE … ADD COLUMNs, nothing rewritten. NULL = unknown on both columns: a pre-migration stamp could not say what its fetch returned, so the skip counted every row for it (the pre-#1504 behavior) until an applying sync re-stamped both sides. Migration 031 replaced the count condition with the stamp's id checksum, and a stamp without a generation no longer reaches the skip at all: it predates 031 too, so it carries no checksum and its platform is fetched in full. The generation still scopes the skip's backfill gate and removed-game discovery, which finds no candidates under a stamp without one.

021_add_rom_fs_size.sql (user_version = 21) adds the nullable roms.fs_size_bytes INTEGER column — the server-reported ROM size in bytes (#1395), added so the game-detail UI could show the space a download needs before it starts. A single ALTER TABLE roms ADD COLUMN with no backfill pass: the size is a server-derived fact that rides the sync UPSERT like the version-metadata columns (migration 008), so the next applying sync writes it onto every row it touches. NULL = unknown (a pre-migration row, or a wholesale-skipped platform not yet re-applied).

022_rename_collection_kind_user_to_standard.sql (user_version = 22) is a data migration, not a schema change: UPDATE collection_sync_state SET collection_kind = 'standard' WHERE collection_kind = 'user'. Tender's internal name for RomM's ownership-carrying first collection kind was renamed user → standard (#1539), so reads and writes now key completion stamps on collection_kind = 'standard'. Existing stamps written before the rename carry 'user' and would otherwise strand — never matched, so their standard collections full-fetch once — so this rewrites them in place. A plain UPDATE is safe: the composite primary key is (collection_id, collection_kind) and no 'standard'-kind row can exist before this migration (the app only ever wrote 'user' / 'smart'), so the rewrite can never collide with an existing ('<id>', 'standard') row. On a fresh install the table is empty and this is a no-op. The settings.json enabled_collections bucket carries the name standard in every settings file this release reads (version 13 on).

023_add_rom_install_launchable.sql (user_version = 23) adds rom_installs.launchable INTEGER NOT NULL DEFAULT 1 — whether the system can act on the install's file_path at all (#1652). detect_launch_file ends in "largest file by size", so a download none of its format rules recognise still produces a file_path, and that path became the shortcut's launch command with nothing checking it; the reported case is a PS3 title shipped as .pkg + .rap, where the PKG is an installer and the game is still sealed inside it (#1582). NOT NULL DEFAULT 1: existing rows were launchable by assumption, since flipping any of them to 0 would have needed an accept-list the DDL could not consult. Rung 040 drops the column.

024_add_answered_save_directories.sql (user_version = 24) creates answered_save_directories — one row per ROM, the save directory the resolver last answered for it (ADR-0041; what reads it is Following a moved save directory). It is created empty: no value can be derived in DDL, because the answer is a live reading of the machine. It is a table of its own rather than a column on rom_save_sync_states, because it is recorded for games that were never synced and a row there means "tracked for save sync"; and it hangs off roms rather than rom_installs, because an install row is deleted on uninstall and this record has to survive one. The same migration deletes the two save-sort markers, save_sort_settings and save_sort_settings_previous, whose readers left with it.

025_add_platform_skip_revoked.sql (user_version = 25) adds platform_sync_state.skip_revoked INTEGER NOT NULL DEFAULT 0 — the flag that takes a platform stamp's skip authority away while keeping the stamp (#2094); what sets it, which readers honour it and why the stamp is kept are under PlatformSyncState in The aggregate set. The same migration then sets the flag on every existing stamp. A stamp written before it may stand over games the stale removal already unbound while the platform was turned off, and would keep skipping them once the platform is turned back on; the migration revokes them all rather than guessing which ones those are. That costs a one-time re-walk: each enabled platform full-fetches on the first sync after the upgrade, and since the preview counts a revoked stamp in restamp_platform_count, it offers Apply even where nothing else changed, until the first apply writes each platform a fresh stamp. Until then the resume offer rests on the games recorded then and collection stamps, since the platform has_any counts no revoked stamp. One ALTER TABLE … ADD COLUMN and one UPDATE, nothing deleted.

026_add_rom_has_file_on_disk.sql (user_version = 26) adds roms.has_file_on_disk INTEGER NOT NULL DEFAULT 1 — whether RomM holds a file for the ROM (#2196). Like fs_size_bytes it is a server-derived fact that rides the sync UPSERT, so an applying sync writes RomM's answer onto every row it fetched; a wholesale-skipped platform or collection is not fetched and keeps what it had. DEFAULT 1 — a pre-migration row reads as having a file until then, which refuses nothing on a guess; the download reads RomM's live answer when it starts either way. Because an unchanged library would otherwise skip every unit and leave every row at the default, the same migration takes the incremental skip away once, as 025 did: it sets skip_revoked on every platform stamp and deletes every collection stamp (they have no revoked flag, and they are re-derivable). Migrations 024 to 026 ship together, so for an install coming from a release that ends at 023, 025 has already revoked every platform stamp in the same upgrade and 026's UPDATE changes nothing; it reaches a database that was already at 025. The DELETE reaches every install, because 025 leaves collection stamps standing and a skipped collection rebuilds its members on a platform the run does not fetch from stored rows that carry the default. That costs one full fetch per enabled collection on every install, and one per enabled platform where 025 had not already asked for it. The fetch happens on the first applied sync: the preview offers Apply for it through the revoked platform stamps (restamp_platform_count) even where nothing else changed, and from migration 032 on through each collection left without a stamp (restamp_collection_count). Before 032, a library that synced collections with no platform enabled was offered no Apply for it, and its rows kept the default until an apply ran for another reason; the download's live check refuses a ROM without a file either way. One ALTER TABLE, one UPDATE and one DELETE; nothing but stamps is deleted.

027_add_artwork_slot_outcomes.sql (user_version = 27) creates artwork_slot_outcomes — one row per ROM and slot (the wide grid, the logo), what was last found for it: set with SteamGridDB's image id and the SHA-256 of the bytes set, none when SteamGridDB has no candidate, or refused when Steam refused the image, beside the ROM's SteamGridDB id at that time and recorded_at in REAL Unix-epoch seconds, the cache/TTL timestamp convention. CHECKs keep the image id and the hash on a set row and off the others, and the slot to the two that are recorded. How a row rules a slot out, and what reads and writes the rows, is Game page artwork and, for the shortcut artwork job, Shortcut artwork. Keyed by ROM rather than app id, because a re-created shortcut gets a new app id, and the rows go with their roms row (ON DELETE CASCADE). Created empty.

028_widen_artwork_slot_outcomes_to_the_icon.sql (user_version = 28) widens the table's slot CHECK with icon, so the shortcut icon is recorded by the rule the wide grid and the logo follow, by the shortcut artwork job and the game page alike. SQLite cannot alter a CHECK, so the table is rebuilt in place — the widened twin created, every row copied unchanged, the old table dropped, the twin renamed — with its other CHECKs and its cascade as they were. Nothing is recorded for an icon until it is asked for after the migration.

029_add_rom_delivered_through.sql (user_version = 29) adds roms.delivered_through TEXT, CHECKed to NULL, platform or collection (#2407). It is one ALTER TABLE … ADD COLUMN, with nothing rewritten and nothing fetched again: every existing row reads NULL until the next commit of the row writes its route, and a row the server no longer returns keeps NULL. A version switch writes no route either. What the stale removal makes of NULL is in Backend Architecture, "The stale removal keeps a game gone from RomM". The user_version ladder is the step's only guard, so it reaches an update that skips releases like every other rung.

030_add_rom_gone_from_romm.sql (user_version = 30) adds roms.gone_from_romm TEXT, CHECKed to NULL, not_found or delisted (#2431). It is one ALTER TABLE … ADD COLUMN with no backfill: every existing row reads NULL, and the evidence comes only from later answers, so a version gone from RomM that the cleanup's stamps cannot name appears in its list after the first apply whose stale removal reaches it, or the first cleanup run whose proof does. The user_version ladder is the step's only guard.

031_add_platform_skip_fetch_start_and_checksum.sql (user_version = 31) adds the nullable platform_sync_state.fetch_started_at TEXT and platform_sync_state.rom_id_checksum TEXT (#2469, #743 D1, D2) — what the platform skip compares RomM's answer with; both are under PlatformSyncState in The aggregate set. Two ALTER TABLE … ADD COLUMNs with no backfill, since neither value can be recovered from what a stamp holds: every existing stamp reads NULL in both and never skips, so each enabled platform is fetched in full on the first sync after the upgrade, and the preview counts it in restamp_platform_count, offering Apply even where nothing else changed, until that apply writes the platform a stamp with both.

032_add_collection_skip_fetch_start_and_checksum.sql (user_version = 32) adds the nullable collection_sync_state.fetch_started_at TEXT and collection_sync_state.listing_rom_id_checksum TEXT (#2477 D2, D4) — what the collection skip compares RomM's answers with; both are under CollectionSyncState in The aggregate set. Two ALTER TABLE … ADD COLUMNs with no backfill, since neither value can be recovered from what a stamp holds: every existing stamp reads NULL in both and never skips, so each enabled collection is fetched in full on the first sync after the upgrade, and the preview counts it in restamp_collection_count, offering Apply even where nothing else changed, until that apply writes the collection a stamp with both.

033_add_artwork_slot_choices.sql (user_version = 33) widens artwork_slot_outcomes' slot CHECK with hero and cover, rebuilding the table in place as 028 did, so the hero is recorded by the rule the other SteamGridDB slots follow, and a cover from SteamGridDB can be recorded (which cover outcomes are recorded is Game page artwork, "What was found is recorded"). It creates artwork_slot_choices — one row per ROM and slot with the SteamGridDB image id the user chose and chosen_at in REAL Unix-epoch seconds, STRICT, the same slot CHECK, and ON DELETE CASCADE to roms — and adds the nullable roms.chosen_sgdb_id INTEGER, the SteamGridDB game the user chose (#2381 D1, D8). Both are created empty and NULL: nothing was chosen before the migration. How each is read is Game page artwork.

034_clear_firmware_cache_filed_by_folder.sql (user_version = 34) empties firmware_cache (#2191 D1). The cached rows carried, as their platform_slug, a folder name read out of a file's path — the segment after bios/, or the path's first segment otherwise — and a firmware file's platform is now the one RomM states for it (platform_id). With the table empty, the first listing read after the upgrade asks the server, where a cache row would have answered for up to the cache's hour, and after that whenever the server could not be read. What becomes of a downloaded_bios row filed under a folder name is 045's, below.

035_add_artwork_slot_handovers.sql (user_version = 35) creates artwork_slot_handovers — one row per ROM and slot, for the wide grid, the logo, the hero and the cover (the icon is judged by its path), with the SteamGridDB image id and the SHA-256 of the bytes Tender last handed to Steam, handed_at in REAL Unix-epoch seconds and the nullable previous_choice_id, the image the user had chosen for the slot then (D20), STRICT, and ON DELETE CASCADE to roms (#2381 D15). A grid file holding those bytes is Tender's until Steam's answer is reported. Its own table rather than columns on artwork_slot_outcomes, whose rows are built whole per outcome with outcome NOT NULL, while a hand-over stands beside any outcome and beside none. Created empty: a slot without a row is judged as before. How it is written and settled is Game page artwork.

036_add_firmware_store.sql (user_version = 36) creates the firmware store's two tables and gives firmware_cache a checksum (#2191 D2, D3, D24). firmware_store holds one row per file Tender downloaded into its firmware store, firmware_links one per link it placed into an emulator source; both are created empty, since nothing was downloaded into a store before. firmware_cache gains md5_hash, so a listing restored while the server cannot be read still matches the library's files to what an emulator asks for by content, and is emptied again: a row of 034's shape carries no checksum, and the first read from the server fills it. downloaded_bios stays as it was: it keeps the downloads made into an emulator source's folder before the store, which a BIOS delete still removes.

037_add_rom_artwork_checked_at.sql (user_version = 37) adds the nullable roms.artwork_checked_at REAL, the time a visit to the game's page last started checking its automatic artwork against SteamGridDB (#2381 D21, D22): one per ROM rather than per slot, which would hold the same time five times and has no row for every slot. No backfill: each game is checked at its first page visit after the upgrade. How it is written and taken back is Game page artwork, "Keeping automatic artwork current".

038_add_last_sync_content_size.sql (user_version = 38) adds the nullable last_sync_content_size INTEGER column to rom_save_files beside last_sync_local_size (#2525 D2) — a single ALTER TABLE with no backfill in SQL, since the bytes a row was recorded from are not at hand there. It is the local file's content size at the last sync, recorded at every sync boundary, which the corrupt-local guard compares where both sides state one. NULL means none recorded: a pre-migration row is judged by raw size until a sync records the content size of a file still unchanged since its baseline (RomSaveSyncState.backfill_content_size), and a baseline recorded from the server's size alone carries none.

039_add_rom_emulator_override_source.sql (user_version = 39) adds the nullable emulator_override_source TEXT column to roms beside emulator_override (#2195 D20): the kind of the emulator source a per-game pick was chosen from, so a label that no longer exists there can be named with it. No backfill: nothing recorded which source an existing pick came from, and such a pick is read against the source the game starts through. Written only with the pick, through set_emulator_override.

040_drop_rom_install_launchable.sql (user_version = 40) drops rom_installs.launchable again (#2195 D13, D26, D28). Whether an installed game's file starts is the resolver's answer, asked with the entry Tender starts the game through, when its page, its emulator menu or a press on Steam's own Play reads it; a stored yes or no would go stale with the emulator choice and with the resolver's next release. A start asks for an installed game's command whatever that answer is (Launch-target validation).

041_replace_applied_launch_options_with_shortcut_applied.sql (user_version = 41) replaces roms.applied_launch_options with roms.shortcut_applied INTEGER NOT NULL DEFAULT 0 (#2221 D7, D17). Every bound shortcut carries its appId and a start asks the resolver for its command, so there is no command left to record; a row keeps only whether the sync applied its shortcut. The rung sets it on every row that had a command recorded, whose shortcut a sync has applied before — the command such a shortcut still carries is set to its appId by the reconcile at panel load, not by the sync — and then drops the old column. One ALTER TABLE … ADD COLUMN, one UPDATE, one ALTER TABLE … DROP COLUMN.

042_add_rom_install_fetched_size.sql (user_version = 42) adds the nullable rom_installs.fetched_size_bytes INTEGER (#2732 D1): the sum of RomM's sizes of the files a download of the game fetches (an adoption records the same figure), which the Data Management page sums. A download fetches a game's own files and leaves its manuals, soundtracks and other extras on the server, so this is no longer roms.fs_size_bytes, RomM's size of the whole game, which the sync keeps writing. The rung fills it for every existing install from roms.fs_size_bytes, because every install made before it fetched every file of its game. NULL = no size recorded.

043_key_firmware_store_by_platform_folder.sql (user_version = 43) keys the firmware store by the folder RomM keeps a platform in (#2734; #2223 D25, D45, D46, D47). firmware_store.platform_slug and firmware_links.platform_slug are renamed platform_folder and keep their values, so a record filed under a slug stays under that name and nothing on disk moves (#2219 D11: no release shipped the store); firmware_store gains platform_slug, filled from the old key. firmware_cache is rebuilt keyed (platform_folder, name) with the slug beside it, so two platforms that share a slug keep a cached file of the same name each, and starts empty; the next read from the server fills it. roms gains the nullable platform_fs_slug, which the sync writes. Every platform stamp is revoked, as 025 did (D47): a skipped platform's rows are rebuilt from stored rows, which hold no folder yet, so the first sync after the upgrade fetches each enabled platform in full and writes it. Every collection stamp is dropped, as Force Full Sync's clear does, for the same reason: a collection's skip rebuilds the members no platform unit fetched from stored rows, so a game only a collection delivers would keep no folder.

044_key_platform_sync_state_by_platform_folder.sql (user_version = 44) rebuilds platform_sync_state keyed by platform_folder, the platform's fs_slug (#2796; #2452 D1, D4). Two RomM platforms can share a slug, and their stamps overwrote each other under it; a stamp written under a slug cannot say which fs_slug it was written for, so every row is dropped and the first sync after the upgrade reads every platform in full and stamps it again. A database coming from before 043 loses nothing more: 043 has already revoked every stamp's skip. The columns are 031's, the key renamed.

045_key_downloaded_bios_by_platform_folder.sql (user_version = 45) renames downloaded_bios.platform_slug to platform_folder and keeps every value (#2797; #2452 D6): a record is read as filed under the fs_slug it holds, and the composite key follows the rename. A record whose value is no listed fs_slug — a slug, as records were kept before, or a server folder name — is moved by the next read of RomM's platform listing that keeps or uses it (services/platform_refiling.py): to the platform of the library file its firmware_id names, while that file still carries its name, else to the one listed fs_slug with that slug. Where several share the slug it stays where it is, and each of their platform pages offers it for deletion; one that nothing places, such as a folder name no file of the library names, stays where it is and no page offers it.

The runtime Unit of Work

The schema is read and written at runtime through a Unit of Work (UoW) — the atomic transaction boundary one operation works inside. The concrete UoW and the sixteen sqlite3 repository adapters that back it live in backend/adapters/repositories/ (#783). The UnitOfWork / UnitOfWorkFactory Protocols services depend on live in backend/services/protocols/uow.py; the per-aggregate Repository Protocols in backend/services/protocols/repositories.py (#782).

Synchronous sqlite3, not aiosqlite. Per ADR-0004 the runtime UoW uses stdlib sqlite3 (synchronous), reversing the epic's earlier aiosqlite plan: aiosqlite is itself thread-based with no concurrency win for this single-writer workload, it would add a vendored dependency, and it would introduce a second I/O paradigm alongside the established run_in_executor idiom and the sync #781 migration runner. No new vendored dependency.

Shape. SqliteUnitOfWork(db_path) is a synchronous context manager. __enter__ opens one connection (isolation_level=None, row_factory = sqlite3.Row), applies the per-connection PRAGMAs (foreign_keys=ON, synchronous=NORMAL, busy_timeout=5000, temp_store=MEMORY — journal_mode=WAL is already persistent from the runner), issues an explicit BEGIN IMMEDIATE, builds one repository per aggregate root, plus the kv_config one, over that shared connection, and returns itself. __exit__ commits on a clean exit, rolls back on an exception (then re-raises), and always closes the connection. One UoW therefore equals one transaction; writes across several repositories commit or roll back together.

Transaction policy — BEGIN IMMEDIATE for every UoW. The UoW starts its transaction with BEGIN IMMEDIATE, not a deferred BEGIN, so the write lock is acquired at transaction start. The reason: a read-then-write UoW (e.g. the reporter's per-rom upsert does a roms.get then an INSERT in a loop) opened with a deferred BEGIN takes a read snapshot first, then tries to upgrade read → write on the first INSERT. Under WAL, if another connection commits a write in between, that upgrade fails immediately with SQLITE_BUSY_SNAPSHOT — and busy_timeout does not retry a snapshot-upgrade failure, so the operation errors spuriously. BEGIN IMMEDIATE holds the write lock from the start, so there is no read → write upgrade and no snapshot to invalidate; concurrent writers serialize on busy_timeout=5000 instead of failing. The decision is universal — all UoWs use BEGIN IMMEDIATE, with no read-only/write distinction: across ~116 call sites in a single-user, short-DB-op workload, the universal rule is the safe one-liner with no mislabeling footgun. The accepted tradeoff is that read-only UoWs also take the write lock (and so serialize against each other) — negligible for this workload.

Thread affinity. A sqlite3 connection is single-thread by default (check_same_thread=True, left at its safe default). Services run the whole with uow_factory() as uow: block inside their synchronous run_in_executor worker (the house do_<verb> / _<verb>_io idiom), so the connection is created, used, and closed entirely on one worker thread and never escapes it.

Repositories. Each SqliteXxxRepository holds the UoW's open connection and maps rows ↔ domain aggregates: STRICT booleans round-trip through int(bool) / bool(int), JSON-array/object columns through json.dumps / json.loads. RomSaveSyncStateRepository spans two tables (rom_save_sync_states + rom_save_files) — save writes the scalar row then replaces the child file rows inside the same transaction. The adapters import only sqlite3, json, and domain.* (never services), and structurally satisfy the Repository Protocols; the UoW structurally satisfies UnitOfWork, keeping the adapters ↛ services boundary intact. The factory (functools.partial(SqliteUnitOfWork, db_path)) is wired in bootstrap() and threaded into every migrated service's *ServiceConfig as uow_factory; per ADR-0006 a service opens the UoW only around its DB reads/writes, never across the server/file I/O or the frontend ack.

Cutover status

The service cutover (#784) landed vertical by vertical and is complete. Every slice migrated:

• firmware and downloads (the launcher's old SQLite read path was since removed — the launcher no longer reads SQLite at all; it runs the command the start check hands it for one start, per ADR-0044).
• the saves vertical.
• the library/roms slice (registry → roms, sync lifecycle → sync_runs, the platform-name cache → kv_config).
• the metadata slice (rom_metadata written by the reporter's per-unit commit; MetadataService/GameDetailService read it from SQLite).
• the playtime slice (PlaytimeService records sessions into rom_playtime + the rom_playtime_sessions outbox and reconciles the cross-device total, session count, and last-played timestamp through RomM's native /api/play-sessions ingest — session-end fold+enqueue+flush, pull-only reconcile-on-view, ADR-0018 / #903).
• the rom-removal + startup-healing slice (RomRemovalService reads/deletes and StartupHealingService.report_missing_installs reads rom_installs through the UoW).
• the read-consumers slice (GameDetailService/AchievementsService/SettingsService read the synced-shortcut registry, install record, save state, and ra_id from SQLite).
• the migration slice — MigrationService reads the RetroDECK-home change-detection markers from kv_config (Bucket 2 per ADR-0003) and relocates installed-ROM file paths through uow.rom_installs.relocate; StartupHealingService.report_missing_installs reads the pending-migration home from kv_config.

With every consumer moved over, the teardown completed: the dead persisters, the RegistryStoreAdapter / MetadataCacheStoreAdapter JSON stores, domain/save_state.py (SaveSyncState), and the in-memory state dicts (shortcut_registry, metadata_cache, installed_roms, and the catch-all state dict) are all deleted.

Chapter 8+ of the Cosmic Python book (domain events + message bus) is explicitly out of scope for this epic; the triggers for revisiting that scope are recorded in .claude/rules/adapters-domain.md.

See also

• Backend Architecture — the four-layer split, the XxxServiceConfig pattern, and the boundary-enforcement layers that aggregates build on.
• GLOSSARY.md — the Aggregate, kv_config, and Rom/ROM/RomM glossary entries.
• ADR-0001 — the decision to adopt Platform as a full aggregate (superseded by ADR-0003).
• ADR-0002 — the per-ROM table-per-aggregate split and the per-ROM CASCADE foreign keys.
• ADR-0003 — the JSON/SQLite persistence boundary; drops the Platform, Device, and SyncSettings aggregates and reverts Platform to a denormalized platform_slug string.