Fix IntegrityError when updating favourites after ROM rescan by Copilot · Pull Request #3166 · rommapp/romm

Copilot · 2026-03-23T22:14:03Z

After a rescan that replaces ROMs (new files → new DB IDs), the favourites collection still held stale IDs. Toggling favourites triggered a foreign key violation when attempting to insert CollectionRom rows referencing non-existent rom_id values.

Changes

backend/handler/database/collections_handler.py — In update_collection, validate rom_ids against the roms table before inserting into collections_roms. Stale/deleted IDs are silently dropped.

# Before: blind insert — crashes if any rom_id no longer exists
session.execute(insert(CollectionRom), [{"collection_id": id, "rom_id": rom_id} for rom_id in set(rom_ids)])

# After: filter to only existing IDs first
valid_rom_ids = set(session.scalars(select(Rom.id).where(Rom.id.in_(rom_ids))).all())
if valid_rom_ids:
    session.execute(insert(CollectionRom), [{"collection_id": id, "rom_id": rom_id} for rom_id in valid_rom_ids])

Original prompt

This section details on the original issue you should resolve

<issue_title>[Bug] Failed to update favourites</issue_title>
<issue_description>RomM version
Currently 4.7.0, it was installed new at 4.7.0 so there have been no upgrades.

Describe the bug
Clicking add to favourites or remove from favourites pops up with an error "Failed to update favourites".

To Reproduce

Click favourites icon (star) on a rom.
See error.

Expected behavior
The favourites list to be updated.

Server (please complete the following information):

OS: ubuntu 25.10 with container running in podman 5.4.2

Client (please complete the following information):

Device: PC
OS: CachyOS
Browser: Firefox
Version: 148.0.2 (64-bit)

Additional context

It may have been triggered by a rescan replacing a few bad roms with ones with identical names. I did a full scan on the platform after replacing the files.

Container logs reveal this:

INFO:     [RomM][httptools_impl][2026-03-23 18:29:14] 10.89.6.4:0 - "PUT /api/collections/1?is_public=false&remove_cover=false HTTP/1.0" 500

ERROR:    [RomM][httptools_impl][2026-03-23 18:29:14] Exception in ASGI application
Traceback (most recent call last):
  File "/src/.venv/lib/python3.13/site-packages/uvicorn/protocols/http/httptools_impl.py", line 409, in run_asgi
    result = await app(  # type: ignore[func-returns-value]
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        self.scope, self.receive, self.send
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/src/.venv/lib/python3.13/site-packages/uvicorn/middleware/proxy_headers.py", line 60, in __call__
    return await self.app(scope, receive, send)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.13/site-packages/fastapi/applications.py", line 1134, in __call__
    await super().__call__(scope, receive, send)
  File "/src/.venv/lib/python3.13/site-packages/sentry_sdk/integrations/starlette.py", line 409, in _sentry_patched_asgi_app
    return await middleware(scope, receive, send)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.13/site-packages/sentry_sdk/integrations/asgi.py", line 174, in _run_asgi3
    return await self._run_app(scope, receive, send, asgi_version=3)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.13/site-packages/sentry_sdk/integrations/asgi.py", line 276, in _run_app
    raise exc from None
  File "/src/.venv/lib/python3.13/site-packages/sentry_sdk/integrations/asgi.py", line 271, in _run_app
    return await self.app(
           ^^^^^^^^^^^^^^^
        scope, receive, _sentry_wrapped_send
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/src/.venv/lib/python3.13/site-packages/starlette/applications.py", line 113, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/src/.venv/lib/python3.13/site-packages/sentry_sdk/integrations/starlette.py", line 200, in _create_span_call
    return await old_call(app, scope, new_receive, new_send, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.13/site-packages/starlette/middleware/errors.py", line 186, in __call__
    raise exc
  File "/src/.venv/lib/python3.13/site-packages/starlette/middleware/errors.py", line 164, in __call__
    await self.app(scope, receive, _send)
  File "/src/.venv/lib/python3.13/site-packages/opentelemetry/instrumentation/asgi/__init__.py", line 768, in __call__
    await self.app(scope, otel_receive, otel_send)
  File "/src/.venv/lib/python3.13/site-packages/sentry_sdk/integrations/starlette.py", line 200, in _create_span_call
    return await old_call(app, scope, new_receive, new_send, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.13/site-packages/starlette/middleware/errors.py", line 186, in __call__
    raise exc
  File "/src/.venv/lib/python3.13/site-packages/starlette/middleware/errors.py", line 164, in __call__
    await self.app(scope, receive, _send)
  File "/src/.venv/lib/python3.13/site-packages/sentry_sdk/integrations/starlette.py", line 200, in _create_span_call
    return await old_call(app, scope, new_receive, new_send, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.13/site-packages/starlette/middleware/base.py", line 191, in __call__
    with recv_stream, send_stream, collapse_excgroups():
                                   ~~~~~~~~~~~~~~~~~~^^
  File "/usr/local/lib/python3.13/contextlib.py", line 162, in __exit__
    self.gen.throw(value)
    ~~~~~~~~~~~~~~^^^^^^^
  File "/src/.venv/lib/python3.13/site-packages/starlette/_utils.py", line 85, in collapse_excgroups
    raise exc
  File "/src/.venv/lib/python3.13/site-packages/starlette/middleware/base.py", line 193, in __call__
    response = await self.dispatch_...

</details>



<!-- START COPILOT CODING AGENT SUFFIX -->

- Fixes rommapp/romm#3164

<!-- START COPILOT CODING AGENT TIPS -->
---

⚡ Quickly spin up Copilot coding agent tasks from anywhere on your macOS or Windows machine with [Raycast](https://gh.io/cca-raycast-docs).

Co-authored-by: gantoine <[email protected]> Agent-Logs-Url: https://github.com/rommapp/romm/sessions/8a6fd3fe-77ab-4f7b-a0e9-1c55e7096e88

Copilot

Pull request overview

Fixes a backend IntegrityError when updating the favourites (collections) after a ROM rescan replaces ROM rows (new DB IDs), leaving collections with stale rom_id references.

Changes:

Filter rom_ids to only those that still exist in the roms table before inserting into collections_roms, preventing foreign key violations.
Skip insert entirely when no valid ROM IDs remain.

💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.

backend/handler/database/collections_handler.py

github-actions · 2026-03-24T14:56:55Z

Test Results (mariadb)

998 tests 997 ✅ 2m 37s ⏱️
1 suites 1 💤
1 files 0 ❌

Results for commit a3ebe16.

♻️ This comment has been updated with latest results.

github-actions · 2026-03-24T14:56:56Z

☂️ Python Coverage

current status: ✅

Overall Coverage

Lines	Covered	Coverage	Threshold	Status
14274	9650	68%	0%	🟢

New Files

No new covered files...

Modified Files

File	Coverage	Status
backend/handler/database/collections_handler.py	44%	🟢
TOTAL	44%	🟢

updated for commit: a3ebe16 by action🐍

github-actions · 2026-03-24T14:57:00Z

Test Results (postgresql)

998 tests 997 ✅ 2m 34s ⏱️
1 suites 1 💤
1 files 0 ❌

Results for commit a3ebe16.

♻️ This comment has been updated with latest results.

Initial plan

e8571c2

Copilot AI assigned Copilot and gantoine Mar 23, 2026

Copilot started work on behalf of gantoine March 23, 2026 22:14 View session

Fix IntegrityError when updating favourites with stale ROM IDs

a595459

Co-authored-by: gantoine <[email protected]> Agent-Logs-Url: https://github.com/rommapp/romm/sessions/8a6fd3fe-77ab-4f7b-a0e9-1c55e7096e88

Copilot AI changed the title ~~[WIP] Fix bug causing failure to update favourites~~ Fix IntegrityError when updating favourites after ROM rescan Mar 23, 2026

Copilot AI requested a review from gantoine March 23, 2026 22:19

Copilot finished work on behalf of gantoine March 23, 2026 22:19

gantoine approved these changes Mar 24, 2026

View reviewed changes

gantoine marked this pull request as ready for review March 24, 2026 14:53

Copilot AI review requested due to automatic review settings March 24, 2026 14:53

Copilot started reviewing on behalf of gantoine March 24, 2026 14:53 View session

Copilot AI reviewed Mar 24, 2026

View reviewed changes

backend/handler/database/collections_handler.py Outdated Show resolved Hide resolved

run fmt

a3ebe16

gantoine merged commit b376620 into master Mar 24, 2026
10 checks passed

gantoine deleted the copilot/fix-failed-update-favourites branch March 24, 2026 19:54

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Fix IntegrityError when updating favourites after ROM rescan#3166

Fix IntegrityError when updating favourites after ROM rescan#3166
gantoine merged 3 commits intomasterfrom
copilot/fix-failed-update-favourites

Copilot AI commented Mar 23, 2026 •

edited

Loading

Uh oh!

Copilot AI left a comment

Uh oh!

Uh oh!

github-actions bot commented Mar 24, 2026 •

edited

Loading

Uh oh!

github-actions bot commented Mar 24, 2026 •

edited

Loading

Uh oh!

github-actions bot commented Mar 24, 2026 •

edited

Loading

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Uh oh!

Conversation

Copilot AI commented Mar 23, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Changes

Uh oh!

Copilot AI left a comment

Choose a reason for hiding this comment

Pull request overview

Uh oh!

Uh oh!

github-actions bot commented Mar 24, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Test Results (mariadb)

Uh oh!

github-actions bot commented Mar 24, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

☂️ Python Coverage

Overall Coverage

New Files

Modified Files

Uh oh!

github-actions bot commented Mar 24, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Test Results (postgresql)

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Copilot AI commented Mar 23, 2026 •

edited

Loading

github-actions bot commented Mar 24, 2026 •

edited

Loading

github-actions bot commented Mar 24, 2026 •

edited

Loading

github-actions bot commented Mar 24, 2026 •

edited

Loading