Add loose foreign key from deployments.user_id to users

V K Adhithiya Kumarrequested to merge
gitlab-community/gitlab-org/gitlab:dev-adhithiya/624849-deployments-user-id-loose-fk into master

What does this MR do and why?

deployments.user_id has no foreign key to users and no loose foreign key entry. Users::DestroyService and Users::MigrateRecordsToGhostUserService do not touch deployments either, so when a user is deleted the column keeps pointing at the removed id.

Because no row changes, no CDC event fires and the stale id replicates to siphon_deployments in ClickHouse unchanged. The Deployments aggregation engine (added in !251585 (merged)) groups by distinct user_id, so each stale id becomes its own node whose user field resolves to null, sitting next to the separate node for rows that genuinely have no creator. A client cannot tell these apart.

deployments is the outlier among the four engines in https://gitlab.com/gitlab-org/gitlab/-/issues/611201: p_ci_pipelines.user_id has a loose foreign key with async_nullify, and merge_requests.author_id has a real FK with ON DELETE SET NULL.

Changes

  • config/gitlab_loose_foreign_keys.yml: add a deployments entry pointing at users, column user_id, on_delete: async_nullify.
  • spec/db/schema_spec.rb: remove user_id from the deployments allowlist entry, leaving only deployable_id.

Notes for review

  • No migration. The users_loose_fk_trigger deletion-tracking trigger already exists because other tables declare loose foreign keys to users.
  • Index-backed nullify. index_deployments_on_user_id_and_status_and_created_at covers (user_id, status, created_at), so the UPDATE ... WHERE user_id = ? the cleanup worker runs is indexed.
  • Table size. deployments is table_size: over_limit in db/docs/deployments.yml, so this needs a database review.
  • Ownership. deployments belongs to continuous_delivery, so groupenvironments should agree before this merges.

Out of scope

Backfilling existing stale user_id values. A loose foreign key only cleans up future deletions. The issue tracks a separate backfill once the real count on GitLab.com is known.

How to verify

bin/rspec spec/db/schema_spec.rb
bin/rspec spec/lib/gitlab/database/loose_foreign_keys_spec.rb

The schema spec no longer tolerates a missing FK on deployments.user_id, and the loose FK definitions spec validates the new YAML entry.

MR acceptance checklist

  • This MR has been reviewed against the MR acceptance checklist.
  • This contribution was prepared with the help of an AI assistant. I reviewed the changes and take responsibility for them.

Closes #624849

Merge request reports