## namespaces is referenced as a foreign key from other 5 other tables in a different database The intention of this issue is to determine any risks when changing all of the below foreign keys referencing namespaces to become [Loose foreign keys](https://docs.gitlab.com/ee/development/database/loose_foreign_keys.html). Loose foreign keys are cleaned up asynchronously which means when we perform a `DELETE FROM namespaces` we will queue a cleanup task that will be executed in ~5 mins to cleanup all of the rows that reference this record. This can mean there are sometimes strange UX bugs where they may see a link to something that no longer exists and clicking it may give an error or it may mean there is a background job that runs and errors due to an invalid foreign key or it may mean a 500 when rendering a certain page containing any of the below tables. Some more details about risks and mitigations are being documented in https://gitlab.com/gitlab-org/gitlab/-/merge_requests/76626 . The ~"group::sharding" will be responsible for actually doing the work to convert the foreign keys to loose foreign keys but will likely need help from impacted teams to asses the risk and potentially determine or implement appropriate mitigation for any risks. Sometimes mitigation may mean refactoring code slightly to avoid errors or explicitly handling certain riskier cases by checking if there is a pending deletion for a record. It is also possible that we can optimize the 5 minute latency for cleanup in certain cases determined to be urgent. 1. [x] `ci_group_variables.group_id` -> `namespaces.id` - `ON DELETE CASCADE` 1. [ ] Best team to review (@fabiopitino): ~"group::pipeline execution" ~"devops::verify" 1. [ ] MR: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/77322 https://gitlab.com/gitlab-org/gitlab/-/merge_requests/77494 1. [x] No way for user to access once parent is deleted. Please explain: Group variables are accessed [through the `group` object](https://gitlab.com/gitlab-org/gitlab/-/blob/92bcf428b688156ffbe00cb48598840d97e25885/app/models/group.rb#L897). If it's deleted then we don't access this records directly. 1. [ ] Possible to access once parent deleted but low user impact. Please explain: `<DETAIL>` 1. [ ] Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: `<DETAIL>` 1. [ ] Possible user impact to be evaluated or mitigated. Please explain: `<DETAIL>` 1. [x] `ci_daily_build_group_report_results.group_id` -> `namespaces.id` - `ON DELETE CASCADE` 1. [x] Best team to review (check off when reviewed): ~"group::testing" ~"devops::verify" 1. [ ] MR: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/77636 https://gitlab.com/gitlab-org/gitlab/-/merge_requests/77721 1. [x] No way for user to access once parent is deleted. Please explain: @morefice: > We use `Ci::DailyBuildGroupReportResult.group_id` to build [coverage analytic at the group level](https://docs.gitlab.com/ee/user/group/repositories_analytics/). Impossible to access the analytic page (`groups/group_id/-/analytics/repository_analytics`) without a `group_id`. 1. [ ] Possible to access once parent deleted but low user impact. Please explain: `<DETAIL>` 1. [ ] Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: `<DETAIL>` 1. [ ] Possible user impact to be evaluated or mitigated. Please explain: `<DETAIL>` 1. [x] `ci_pending_builds.namespace_id` -> `namespaces.id` - `ON DELETE CASCADE` 1. [x] Best team to review (@fabiopitino): ~"group::pipeline execution" ~"devops::verify" 1. [ ] MR: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/77636 https://gitlab.com/gitlab-org/gitlab/-/merge_requests/77724 1. [x] No way for user to access once parent is deleted. Please explain: `Ci::PendingBuilds` is generally accessed regardless of the `namespace_id`. For group-level runners we don't use `namespace_id` column directly but we use `traversal_ids` instead. One other point of access of pending builds [via namespace is here](https://gitlab.com/gitlab-org/gitlab/-/blob/92bcf428b688156ffbe00cb48598840d97e25885/app/services/ci/update_pending_build_service.rb#L20) but this won't show orphan records since the `namespace` access point will be deleted. 1. [ ] Possible to access once parent deleted but low user impact. Please explain: `<DETAIL>` 1. [ ] Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: `<DETAIL>` 1. [ ] Possible user impact to be evaluated or mitigated. Please explain: `<DETAIL>` 1. [x] `ci_runner_namespaces.namespace_id` -> `namespaces.id` - `ON DELETE CASCADE` 1. [x] Best team to review (check off when reviewed): ~"group::runner" ~"devops::verify" 1. [ ] MR: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/77636 https://gitlab.com/gitlab-org/gitlab/-/merge_requests/77727 1. [ ] No way for user to access once parent is deleted. Please explain: `<DETAIL>` 1. [x] Possible to access once parent deleted but low user impact. Please explain: > Accessing the parent group will become possible in the near future due to an upcoming `group` GraphQL field that will be introduced in `CiRunnerType` which will allow you to query it for the associated group, but we can try to exclude the unreachable group from the results. Another minor situation that comes to mind is that [`User.ci_owned_runners`](https://gitlab.com/gitlab-org/gitlab/blob/a4cb0770a3e5e64838fc5c8bde2c4488fba2782b/app/models/user.rb#L1606-1614) might return runners for groups that have been deleted (exposed through REST API and used in `RunnerPolicy` for CRUD permissions). 1. [ ] Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: `<DETAIL>` 1. [ ] Possible user impact to be evaluated or mitigated. Please explain: `<DETAIL>` 1. [x] `ci_minutes_additional_packs.namespace_id` -> `namespaces.id` - `ON DELETE CASCADE` 1. [x] Best team to review (check off when reviewed): ~"group::utilization" ~"devops::fulfillment" 1. [ ] MR: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/77322 https://gitlab.com/gitlab-org/gitlab/-/merge_requests/77550 1. [x] No way for user to access once parent is deleted. Please explain: `The records are currently unused. Only two interfaces are via API which is only used by CustomersDot` 1. [ ] Possible to access once parent deleted but low user impact. Please explain: `<DETAIL>` 1. [ ] Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: `<DETAIL>` 1. [ ] Possible user impact to be evaluated or mitigated. Please explain: `<DETAIL>`