## Why? - The `professional_teal_sparrow` Sev1 (incident review: https://gitlab.com/gitlab-com/gl-infra/gitlab-dedicated/team/-/work_items/13154) was caused by a sharding-key backfill copying a `project_id` from an orphaned parent row into a child column protected by a hard FK. The specific migration was hardened in https://gitlab.com/gitlab-org/gitlab/-/merge_requests/246021, but the question remained whether other tables have the same pattern. - We audited all parent-derived sharding-key backfills (db/docs `desired_sharding_key`/`sharding_key` entries, `BackfillDesiredShardingKeyJob` subclasses, the 68 `finalize_hk_backfill_*` post-deploy migrations, `db/structure.sql` FKs, and `config/gitlab_loose_foreign_keys.yml`). Two tables match the incident pattern, and both have finalize migrations shipping in **19.0**, so instances upgrading to 19.0 (including Dedicated tenants) are exposed the same way: ### 1. `packages_nuget_symbol_states.project_id` - Child has hard FK `fk_3e841eeb5d -> projects(id)` (VALID, CASCADE). - Parent `packages_nuget_symbols.project_id` has **no FK and no LFK**. Orphan path: project deleted -\> `packages_packages` rows are LFK-cleaned (async, backlog-prone) -\> `fk_rails_5df972da14` (`ON DELETE SET NULL`) nulls `package_id` -\> the symbol row survives with a dangling `project_id` until `Packages::Nuget::CleanupStaleSymbolsWorker` eventually destroys it. - `BackfillPackagesNugetSymbolStatesProjectId` is a bare `BackfillDesiredShardingKeyJob` subclass with no orphan handling. One-time orphan cleanups (`20260316115959`/`20260316120000`) ran at queue time (18.11), but orphans created afterwards will fail the finalize migration `20260428232406` exactly like the incident. ### 2. `packages_package_file_states.project_id` - Child has hard FK to `projects` (VALID, CASCADE). - Parent `packages_package_files.project_id` is denormalized with no FK of its own; its only protection is transitive (`package_id -> packages_packages` hard CASCADE). During an LFK cleanup backlog, deleted-project `packages_packages` rows still exist, so their package files exist too, with dangling denormalized `project_id` values. - `BackfillPackagesPackageFileStatesProjectId` has no orphan handling; finalize migration `20260420232456` also ships in 19.0. ## Proposal Remove the hard FKs from the two child columns instead of hardening each backfill: - Loose foreign key cleanup deletes rows async by design, so orphaned parents are the _normal_ case, not an edge case ([documented caveat](https://docs.gitlab.com/development/database/loose_foreign_keys/#caveats-of-loose-foreign-keys)). A hard FK on a column whose value chain includes an LFK-backed parent can always be violated; this is the same reasoning as https://gitlab.com/gitlab-org/gitlab/-/work_items/606437 (guideline) and https://gitlab.com/gitlab-org/gitlab/-/work_items/606453 (fix the automation). - No spec logic change is needed. The existing allowlists already have direct precedents for Geo `*_states` tables with LFK-backed parents (`ci_secure_file_states.project_id`, `ci_job_artifact_states.project_id`, and the parent `packages_nuget_symbols.project_id` itself): - `allowed_to_be_missing_foreign_key` in `spec/lib/gitlab/organizations/sharding_key_spec.rb` - `ignored_fk_columns_map` in `spec/db/schema_spec.rb` - Per table: one `remove_foreign_key` regular migration + the two allowlist entries (they must land in the same MR -- a guard spec fails if an ignored column still has a hard FK). The existing `IS NOT NULL` check constraints stay. Row cleanup continues to work via the parent-chain cascade. The main alternative is the orphan-tolerant backfill approach from !246021 (delete orphaned parents in `perform`), which is \~55 lines of careful CTE SQL + specs per table, and leaves the contradictory FK in place -- the sharding-key sync triggers re-copy parent values on insert/update, so the hazard survives past finalize (e.g. Geo verification-state recreation). Removing the FK fixes the class, not the instance. Since the finalize migrations ship in 19.0, **this needs backports to 19.0/19.1/19.2** like https://gitlab.com/gitlab-org/gitlab/-/work_items/605940 did. ## Notes from the audit - `p_ci_pipeline_artifact_states.project_id` has the same LFK-backed parent (`ci_pipeline_artifacts`) and a 19.0 finalize migration, but **no hard FK** on the child, so the backfill silently writes dangling values instead of failing. No action needed now, but a hard FK should not be added later. - `merge_request_diff_files.project_id` FK is `NOT VALID`; safe today only because the parent is hard-FK'd. - \~25 `*_upload_states` tables were false positives (they inherit hard FKs from the partitioned `uploads` table). Found while answering https://gitlab.com/gitlab-com/gl-infra/gitlab-dedicated/team/-/work_items/13154#note_3608886931.