Look into adding database triggers/constraints to prevent deletion of workspace related records

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

• Close this issue

MR: Pending

Description

Fix issue with missing workspace's agent config (#553324 - closed) presented us with a case where a workspace agent config is deleted manually from the database, but the associated workspace still exists, leading to the following errors on Sentry:

.....
RuntimeError
#workspaces_agent_config cannot be called until #agent and #agent.unversioned_latest_workspaces_agent_config are set. (RuntimeError)

We would need a way to prevent the deletion of an agent config entry if workspaces exist for that agent. This would force any cleanup logic to first delete all workspaces before proceeding to clean up the associated agent config.

We should also consider applying these deletion constraints on other workspace child records like workspace_variables, user_provided_workspace_variables, workspace_agentk_state and workspace_token, i.e do not allow deleting these records until the associated workspace is deleted.

Our current proposal is to write DB-level triggers to enforce these constraints, but we should take a look at how this is done in other areas of the codebase before committing and address any spec setup rewrites that might come as a result of adding triggers (triggers may affect factories used in specs)

We should also be aware of how workspace records relate to each other to prevent ending up in weird dependency chains due to adding constraints/triggers

Acceptance criteria

• Come up with safe DB constraints to enforce correct deletion order of workspaces and related records

Implementation plan