[Bug]: Deleting robots lead to inconsistent DB state

[maxxgx]

Description

Deleting a robot that is part of an existing environment creates an inconsistent state. As a result, the environment (and the entire project) cannot be deleted.

Deleting a robot in this case should not be possible to prevent this issue.

Steps to Reproduce

1. Create a robot A
2. Create environment Env and add robot A
3. Delete robot A
4. Delete Env

Expected Behavior

Deleting robot A should not be allowed

Actual Behavior

Robot A is deleted and DB becomes inconsistent. Cannot delete Env or the project containing it.

Component

Backend (FastAPI server)

Environment

- OS: Ubuntu 24

Logs / Error Output

2026-03-05 11:16:06.652 | INFO     | uvicorn.protocols.http.httptools_impl:send:483 - 127.0.0.1:43638 - "DELETE /api/projects/8a0ec102-ffba-45f7-b359-b3cd5d21aab4/environments/fa8e91cf-d728-42bc-aaef-32352c4c05e5 HTTP/1.1" 500
2026-03-05 11:16:06.652 | ERROR    | uvicorn.protocols.http.httptools_impl:run_asgi:421 - Exception in ASGI application

Traceback (most recent call last):

  File "/home/max/physical-ai-studio/application/backend/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
    │    │       └ <function DefaultDialect.do_execute at 0x7980aacfac00>
    │    └ <sqlalchemy.dialects.sqlite.aiosqlite.SQLiteDialect_aiosqlite object at 0x7980aac7e930>
    └ <sqlalchemy.engine.base.Connection object at 0x79818b2c0620>
  File "/home/max/physical-ai-studio/application/backend/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 952, in do_execute
    cursor.execute(statement, parameters)
    │      │       │          └ ('fa8e91cf-d728-42bc-aaef-32352c4c05e5', '8a0ec102-ffba-45f7-b359-b3cd5d21aab4')
    │      │       └ 'DELETE FROM project_environments WHERE project_environments.id = ? AND project_environments.project_id = ? RETURNING id'
    │      └ <function AsyncAdapt_aiosqlite_cursor.execute at 0x7980aa7554e0>
    └ <sqlalchemy.dialects.sqlite.aiosqlite.AsyncAdapt_aiosqlite_cursor object at 0x79818b14bbe0>
  File "/home/max/physical-ai-studio/application/backend/.venv/lib/python3.12/site-packages/sqlalchemy/dialects/sqlite/aiosqlite.py", line 182, in execute
    self._adapt_connection._handle_exception(error)
    │    └ <member '_adapt_connection' of 'AsyncAdapt_aiosqlite_cursor' objects>
    └ <sqlalchemy.dialects.sqlite.aiosqlite.AsyncAdapt_aiosqlite_cursor object at 0x79818b14bbe0>
  File "/home/max/physical-ai-studio/application/backend/.venv/lib/python3.12/site-packages/sqlalchemy/dialects/sqlite/aiosqlite.py", line 342, in _handle_exception
    raise error
          └ IntegrityError('FOREIGN KEY constraint failed')
  File "/home/max/physical-ai-studio/application/backend/.venv/lib/python3.12/site-packages/sqlalchemy/dialects/sqlite/aiosqlite.py", line 164, in execute
    self.await_(_cursor.execute(operation, parameters))
    │    │      │       │       │          └ ('fa8e91cf-d728-42bc-aaef-32352c4c05e5', '8a0ec102-ffba-45f7-b359-b3cd5d21aab4')
    │    │      │       │       └ 'DELETE FROM project_environments WHERE project_environments.id = ? AND project_environments.project_id = ? RETURNING id'
    │    │      │       └ <function Cursor.execute at 0x7980aa764900>
    │    │      └ <aiosqlite.cursor.Cursor object at 0x797f061ae510>
    │    └ <member 'await_' of 'AsyncAdapt_aiosqlite_cursor' objects>
    └ <sqlalchemy.dialects.sqlite.aiosqlite.AsyncAdapt_aiosqlite_cursor object at 0x79818b14bbe0>
  File "/home/max/physical-ai-studio/application/backend/.venv/lib/python3.12/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 132, in await_only
    return current.parent.switch(awaitable)  # type: ignore[no-any-return,attr-defined] # noqa: E501
           │       │             └ <coroutine object Cursor.execute at 0x797f061e5380>
           │       └ <attribute 'parent' of 'greenlet.greenlet' objects>
           └ <_AsyncIoGreenlet object at 0x79818b287a80 (otid=0x7980acb25440) dead>
  File "/home/max/physical-ai-studio/application/backend/.venv/lib/python3.12/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 196, in greenlet_spawn
    value = await result
                  └ <coroutine object Cursor.execute at 0x797f061e5380>
  File "/home/max/physical-ai-studio/application/backend/.venv/lib/python3.12/site-packages/aiosqlite/cursor.py", line 40, in execute
    await self._execute(self._cursor.execute, sql, parameters)
          │    │        │    │       │        │    └ ('fa8e91cf-d728-42bc-aaef-32352c4c05e5', '8a0ec102-ffba-45f7-b359-b3cd5d21aab4')
          │    │        │    │       │        └ 'DELETE FROM project_environments WHERE project_environments.id = ? AND project_environments.project_id = ? RETURNING id'
          │    │        │    │       └ <method 'execute' of 'sqlite3.Cursor' objects>
          │    │        │    └ <sqlite3.Cursor object at 0x79818b2a76c0>
          │    │        └ <aiosqlite.cursor.Cursor object at 0x797f061ae510>
          │    └ <function Cursor._execute at 0x7980aa764860>
          └ <aiosqlite.cursor.Cursor object at 0x797f061ae510>
  File "/home/max/physical-ai-studio/application/backend/.venv/lib/python3.12/site-packages/aiosqlite/cursor.py", line 32, in _execute
    return await self._conn._execute(fn, *args, **kwargs)
                 │    │     │        │    │       └ {}
                 │    │     │        │    └ ('DELETE FROM project_environments WHERE project_environments.id = ? AND project_environments.project_id = ? RETURNING id', (...
                 │    │     │        └ <built-in method execute of sqlite3.Cursor object at 0x79818b2a76c0>
                 │    │     └ <function Connection._execute at 0x7980aa765bc0>
                 │    └ <aiosqlite.core.Connection object at 0x79818b2c00b0>
                 └ <aiosqlite.cursor.Cursor object at 0x797f061ae510>
  File "/home/max/physical-ai-studio/application/backend/.venv/lib/python3.12/site-packages/aiosqlite/core.py", line 160, in _execute
    return await future
                 └ <Future finished exception=IntegrityError('FOREIGN KEY constraint failed')>
  File "/home/max/physical-ai-studio/application/backend/.venv/lib/python3.12/site-packages/aiosqlite/core.py", line 63, in _connection_worker_thread
    result = function()
             └ <function Connection.stop.<locals>.close_and_stop at 0x797f0617ff60>

sqlite3.IntegrityError: FOREIGN KEY constraint failed

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) FOREIGN KEY constraint failed
[SQL: DELETE FROM project_environments WHERE project_environments.id = ? AND project_environments.project_id = ? RETURNING id]
[parameters: ('fa8e91cf-d728-42bc-aaef-32352c4c05e5', '8a0ec102-ffba-45f7-b359-b3cd5d21aab4')]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

Additional Context

No response

[MarkRedeman]

For a proposed solution I'm thinking that we can make the backend return a 409 conflict error when we detect that there is one or more environments that the robot belongs to.
Perhaps the error message can then also include the ids / names of the environment.

Then in the UI we will show a error modal with an explanation which environments need to be deleted first.

Another option could be to use soft deletes, but then we'd also need to maintain that on the environments side.

[maxxgx]

For a proposed solution I'm thinking that we can make the backend return a 409 conflict error when we detect that there is one or more environments that the robot belongs to. Perhaps the error message can then also include the ids / names of the environment.

Then in the UI we will show a error modal with an explanation which environments need to be deleted first.

Another option could be to use soft deletes, but then we'd also need to maintain that on the environments side.

Yeah I think 409 + a clear error message is the best solution