Audit tables where the parent has a loose FK to projects but the child has a hard FK on the backfilled sharding key

Background

The 18.11 → 19.0 upgrade broke (Sev1, #605940) because BackfillPackagesHelmMetadataCacheStatesProjectId copied a dangling project_id from an orphaned parent row into a child column protected by a hard FK, raising PG::ForeignKeyViolation and aborting the finalize migration. The fix for that table was !246021 (merged) (delete orphaned parents per sub-batch, then backfill).

!247421 (closed) adds an orphaned-parent test case to the desired sharding key backfill shared examples, but after discussion (@tkuah) we are no longer confident that spec-level coverage properly catches the issue. Instead:

  • Root cause shape: the child table has a hard FK on the sharding key column (e.g. project_id → projects.id), while the parent table's sharding key column is only cleaned up via a loose foreign key (LFK). Because LFK cleanup is eventually consistent, a parent can hold a dangling project_id, and copying it into the child violates the child's hard FK.
  • Remediation direction: BG migrations should delete orphaned rows — either as a new BG migration, or in the same BG migration before finalizing (the !246021 (merged) pattern).

Task

Find all parent/child table pairs matching this shape:

  1. A BackfillDesiredShardingKeyJob-style backfill copies a sharding key (project_id, namespace_id, organization_id) from a parent table into a child table.
  2. The child column being backfilled has a hard FK to the referenced table (e.g. projects).
  3. The parent's source column has no hard FK — it is cleaned up via a loose foreign key (config/gitlab_loose_foreign_keys.yml).

Suggested approach:

  • Cross-reference queued/pending BackfillDesiredShardingKeyJob migrations with db/structure.sql FKs and config/gitlab_loose_foreign_keys.yml.
  • For each match, decide the remediation: delete orphaned parent rows in the same BG migration before finalize (preferred, per !246021 (merged)), or queue a separate cleanup BG migration.
  • Consider adding a static check / spec that flags new backfills matching this shape.

References

/cc @mkozono @tkuah