[🐛 Bug]: Service Topology re-pull results in FK Contraint violation

[syphernl]

Describe the bug

Doing a "Pull from providers" on Service Topology results in an error.
Initial pull worked, but pulling again results in getting a Foreign Key constraint violation.

To Reproduce
Steps to reproduce the behavior:

1. Configure FluxCD Provider
2. Go to Service Topology and click on Pull from providers
3. See topology
4. Click the Pull from providers again
5. See error

Expected behavior
No error.

Screenshots

Additional context

We use Postgres as backend.

{"worker_type": "uvicorn", "asctime": "2025-05-28 14:31:07,442", "message": "Error pulling topology from provider fluxcd (REDACTED)", "levelname": "ERROR", "name": "keep.api.routes.topology", "filename": "topology.py", "otelTraceID": "REDACTED", "otelSpanID": "REDACTED", "otelTraceSampled": true, "otelServiceName": "keep-api", "threadName": "AnyIO worker thread", "process": 18, "module": "topology", "exc_info": "Traceback (most recent call last):\n  File \"/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py\", line 1967, in _exec_single_context\n    self.dialect.do_execute(\n  File \"/venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py\", line 941, in do_execute\n    cursor.execute(statement, parameters)\npsycopg2.errors.ForeignKeyViolation: update or delete on table \"topologyservice\" violates foreign key constraint \"topologyserviceapplication_service_id_fkey\" on table \"topologyserviceapplication\"\nDETAIL:  Key (id)=(81) is still referenced from table \"topologyserviceapplication\".\n\n\nThe above exception was the direct cause of the following exception:\n\nTraceback (most recent call last):\n  File \"/venv/lib/python3.11/site-packages/keep/api/routes/topology.py\", line 208, in pull_topology_data\n    process_topology(\n  File \"/venv/lib/python3.11/site-packages/keep/api/tasks/process_topology_task.py\", line 59, in process_topology\n    ).delete()\n      ^^^^^^^^\n  File \"/venv/lib/python3.11/site-packages/sqlalchemy/orm/query.py\", line 3162, in delete\n    result: CursorResult[Any] = self.session.execute(\n                                ^^^^^^^^^^^^^^^^^^^^^\n  File \"/venv/lib/python3.11/site-packages/sqlmodel/orm/session.py\", line 127, in execute\n    return super().execute(\n           ^^^^^^^^^^^^^^^^\n  File \"/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py\", line 2362, in execute\n    return self._execute_internal(\n           ^^^^^^^^^^^^^^^^^^^^^^^\n  File \"/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py\", line 2247, in _execute_internal\n    result: Result[Any] = compile_state_cls.orm_execute_statement(\n                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n  File \"/venv/lib/python3.11/site-packages/sqlalchemy/orm/bulk_persistence.py\", line 2021, in orm_execute_statement\n    return super().orm_execute_statement(\n           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n  File \"/venv/lib/python3.11/site-packages/sqlalchemy/orm/context.py\", line 305, in orm_execute_statement\n    result = conn.execute(\n             ^^^^^^^^^^^^^\n  File \"/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py\", line 1418, in execute\n    return meth(\n           ^^^^^\n  File \"/venv/lib/python3.11/site-packages/sqlalchemy/sql/elements.py\", line 515, in _execute_on_connection\n    return connection._execute_clauseelement(\n           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n  File \"/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py\", line 1640, in _execute_clauseelement\n    ret = self._execute_context(\n          ^^^^^^^^^^^^^^^^^^^^^^\n  File \"/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py\", line 1846, in _execute_context\n    return self._exec_single_context(\n           ^^^^^^^^^^^^^^^^^^^^^^^^^^\n  File \"/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py\", line 1986, in _exec_single_context\n    self._handle_dbapi_exception(\n  File \"/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py\", line 2355, in _handle_dbapi_exception\n    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e\n  File \"/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py\", line 1967, in _exec_single_context\n    self.dialect.do_execute(\n  File \"/venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py\", line 941, in do_execute\n    cursor.execute(statement, parameters)\nsqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) update or delete on table \"topologyservice\" violates foreign key constraint \"topologyserviceapplication_service_id_fkey\" on table \"topologyserviceapplication\"\nDETAIL:  Key (id)=(81) is still referenced from table \"topologyserviceapplication\".\n\n[SQL: DELETE FROM topologyservice WHERE topologyservice.source_provider_id = %(source_provider_id_1)s AND topologyservice.tenant_id = %(tenant_id_1)s /*db_driver='psycopg2',db_framework='sqlalchemy%%3A2.0.36',traceparent='00-REDACTED-05d861c1ea0b853a-01'*/]\n[parameters: {'source_provider_id_1': 'REDACTED', 'tenant_id_1': 'keep'}]\n(Background on this error at: https://sqlalche.me/e/20/gkpj)", "provider_type": "fluxcd", "provider_id": "REDACTED", "tenant_id": "keep", "error": "(psycopg2.errors.ForeignKeyViolation) update or delete on table \"topologyservice\" violates foreign key constraint \"topologyserviceapplication_service_id_fkey\" on table \"topologyserviceapplication\"\nDETAIL:  Key (id)=(81) is still referenced from table \"topologyserviceapplication\".\n\n[SQL: DELETE FROM topologyservice WHERE topologyservice.source_provider_id = %(source_provider_id_1)s AND topologyservice.tenant_id = %(tenant_id_1)s /*db_driver='psycopg2',db_framework='sqlalchemy%%3A2.0.36',traceparent='00-REDACTED-05d861c1ea0b853a-01'*/]\n[parameters: {'source_provider_id_1': 'REDACTED', 'tenant_id_1': 'keep'}]\n(Background on this error at: https://sqlalche.me/e/20/gkpj)"}

[dosubot]

The Foreign Key constraint violation you’re seeing when re-pulling Service Topology is caused by an attempt to delete records from the topologyservice table while those records are still referenced in the topologyserviceapplication table. This typically happens if the code deletes services before removing all related service-application links, violating the database constraint.

From the code, the process_topology function currently deletes dependencies from TopologyServiceDependency and then deletes services from TopologyService, but it does not delete entries from TopologyServiceApplication before attempting to delete the services. Since TopologyServiceApplication has a foreign key to TopologyService, this leads to the error you’re experiencing when a service is still referenced by an application link [source].

The clean_before_import method in TopologiesService demonstrates the correct order: it deletes dependencies, then service-application links, then applications, and finally services [source]. This order ensures all references are removed before deleting the parent records.

To resolve the issue, ensure that before deleting from topologyservice, you also delete all related records from topologyserviceapplication (and any other tables with foreign keys to topologyservice). The deletion order should be:

1. Delete from TopologyServiceDependency
2. Delete from TopologyServiceApplication
3. Delete from TopologyApplication (if needed)
4. Delete from TopologyService

This will prevent the Foreign Key constraint violation when re-pulling topology data.

There is no documentation or previous discussion found that directly addresses this specific issue. The solution is to update the deletion logic in your topology processing to match the safe order shown above.

To reply, just mention my name @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}   

[shahargl]

@VladimirFilonov can you have a look?