Add a loose foreign key from deployments.user_id to users

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

Problem

deployments.user_id in PostgreSQL has no foreign key to users. The table's only foreign keys are environment_id (references environments(id), ON DELETE CASCADE) and project_id (references projects(id), ON DELETE CASCADE).

There is no entry for deployments in config/gitlab_loose_foreign_keys.yml. The User model has no has_many :deployments association. Users::DestroyService does not touch deployments, and Users::MigrateRecordsToGhostUserService has no deployments step (it does have a migrate_pipelines step covering Ci::Pipeline and Ci::Build).

deployments.user_id is explicitly allowlisted as exempt from the missing-foreign-key check in spec/db/schema_spec.rb, in the entry deployments: %w[deployable_id user_id].

Net effect: when a user is deleted, the deployments row is never updated, so user_id keeps pointing at the removed user's id. Because no row change occurs, no CDC event fires, and the stale id replicates through to siphon_deployments in ClickHouse unchanged.

Why it matters now

!251585 (merged) added a user dimension (UserCore) and a userId filter to the Deployments ClickHouse aggregation engine, for the DAP Impact dashboard.

The GraphQL response resolves the dimension through a batch loader in app/graphql/types/analytics/aggregation/engine_response_dimensions_type.rb, which returns nil for an id with no matching User record. ClickHouse groups by distinct user_id, so each stale id becomes its own node whose user field is null, sitting alongside the separate node for rows that genuinely have no creator. A client cannot tell these apart, because the raw id is not exposed as a response field, only the resolved association.

User ids are never reused, so a stale id can only resolve to null. It can never resolve to the wrong person.

Applying the userId filter avoids the problem, because stale ids are not in the requested set. The unfiltered group-by-user view is where it shows.

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 on_delete: async_nullify, and is additionally reassigned to the Ghost user by migrate_pipelines. merge_requests.author_id has a real foreign key with ON DELETE SET NULL. deployments.user_id has neither.

Proposal

  1. Add a loose foreign key entry for deployments to config/gitlab_loose_foreign_keys.yml, pointing at users, column user_id, on_delete: async_nullify.
  2. Remove user_id from the deployments entry in the spec/db/schema_spec.rb allowlist, leaving only deployable_id.
  3. No migration is required. The users_loose_fk_trigger deletion-tracking trigger already exists, because other tables already declare loose foreign keys to users.
  4. The nullify is index-backed: index_deployments_on_user_id_and_status_and_created_at covers (user_id, status, created_at). Note that deployments is marked table_size: over_limit in db/docs/deployments.yml, so this should get a database review.
  5. The deployments table is owned by the continuous_delivery feature category. This change needs groupenvironments agreement before it ships, in addition to the database review.

Out of scope

  • Backfilling existing stale user_id values. A loose foreign key only cleans up future deletions. Existing stale ids would need a separate backfill, which should be scoped once we know the real count on GitLab.com.
  • Any change to how the aggregation framework renders unresolvable association ids. Merging the null-resolving nodes after the fact would mean recombining their metrics, and rate and quantile cannot be correctly recombined from already-grouped results.

References

Edited by 🤖 GitLab Bot 🤖