Remove project_id FKs from two Geo verification state tables

What does this MR do and why?

Removes the hard project_id foreign keys from packages_nuget_symbol_states (fk_3e841eeb5d) and packages_package_file_states (fk_e568054097).

Both columns are sharding keys copied from a parent row — first by a BackfillDesiredShardingKeyJob, then on every insert and update by the sharding-key sync triggers. A hard FK on a copied value can only be as safe as the parent's own reference to projects, and in both cases the parent is permitted to hold the project_id of a deleted project. This is the failure mode behind the professional_teal_sparrow Sev1, which was hardened for one table in !246021 (merged). Removing the FK fixes the class rather than the instance: because the sync triggers re-copy parent values, the hazard would otherwise outlive the finalize migration.

packages_nuget_symbol_states

packages_nuget_symbols has neither a foreign key nor a loose foreign key to projects. Deleting a project hard-cascades packages_packages away and fk_rails_5df972da14 nulls the symbol's package_id, but the symbol row survives holding a dangling project_id until Packages::Nuget::CleanupStaleSymbolsWorker destroys it. Finalize migration 20260428232406, shipping in 19.0, copies that value into the hard-FK'd column and aborts the upgrade. Note this needs no LFK cleanup backlog to trigger — orphans appear as soon as a project with nuget symbols is deleted.

packages_package_file_states

packages_package_files.project_id is denormalized with no foreign key of its own, and sits in allowed_to_be_missing_foreign_key because the row references object storage and so can be neither cascade-deleted nor nulled. The child must not constrain the copied value more strictly than the parent does.

To be precise about the justification here: unlike the nuget case, I could not construct an orphan path for this table. projects → packages_packages → packages_package_files is hard ON DELETE CASCADE end to end, so a package file cannot outlive its project. This change rests on the child-stronger-than-parent invariant rather than on a demonstrable finalize failure.

Row cleanup is unaffected

Both state tables are cleaned up by loose foreign keys on their parents (async_delete on packages_nuget_symbol_id and package_file_id), and both parents carry *_loose_fk_trigger delete-tracking triggers, so a state row is removed when its parent goes — including when the parent is removed by a cascade. This matches ci_secure_file_states and ci_job_artifact_states, which likewise have no direct projects FK. The IS NOT NULL check constraints are unchanged.

One behaviour change worth noting: today the projects cascade deletes a nuget state row immediately on project deletion, while the symbol it verifies lives on. After this change the state row tracks the symbol's lifetime instead, which is the correct pairing for a Geo verification registry but does mean the row lingers until CleanupStaleSymbolsWorker runs.

Regular, not post-deploy migrations

Deliberate. Regular migrations run before post-deploy migrations in an upgrade, so these drop the FKs before the finalize migrations execute even though their timestamps are later. A post-deploy migration would sort after the finalize and the upgrade would still fail. The 19.0–19.2 backports depend on this ordering.

Allowlists

The two guard specs are two-sided — an ignored column must not have a hard FK, and a column without a FK must be ignored — so these entries must land in the same MR as the migrations:

• 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

Migration output

Up:

== 20260729234000 RemoveProjectIdForeignKeyFromPackagesNugetSymbolStates: migrated
== 20260729234100 RemoveProjectIdForeignKeyFromPackagesPackageFileStates: migrated

Down (re-adds and validates both constraints):

-- execute("ALTER TABLE packages_package_file_states VALIDATE CONSTRAINT fk_e568054097;")
== 20260729234100 RemoveProjectIdForeignKeyFromPackagesPackageFileStates: reverted
-- execute("ALTER TABLE packages_nuget_symbol_states VALIDATE CONSTRAINT fk_3e841eeb5d;")
== 20260729234000 RemoveProjectIdForeignKeyFromPackagesNugetSymbolStates: reverted

spec/lib/gitlab/organizations/sharding_key_spec.rb passes (15 examples, 0 failures).

Backports

Needs backports to 19.0, 19.1 and 19.2, since the finalize migrations ship in 19.0. Will open those once this merges.

Related to #606437 (guideline) and #606453 (fix the automation).

Closes #606941