Validate bigint FK for deployments phase two

What does this MR do and why?

Phase two of the deployments bigint conversion covers the project_id and user_id columns. This MR does one thing: it synchronously validates the foreign key on the new bigint project_id column.

fk_b9a3851b82_tmp (deployments.project_id_convert_to_bigint → projects.id) was added NOT VALID in !217495 (merged) and prepared for async validation in 19.0. On GitLab.com that async validation has already completed, so this migration is a no-op there. It exists so self-managed and local environments reach the same state before the swap.

user_id intentionally has no foreign key, so there is nothing to validate for it.

This mirrors FkValidationForDeployments (db/post_migrate/20260319230445), the equivalent phase-one migration, which shipped in 18.11 via !221371 (merged).

Current production state

deployments on production, confirming everything upstream of this MR is in place — phase one is swapped, all seven phase-two bigint indexes exist, and fk_b9a3851b82_tmp is already validated (psql prints no NOT VALID suffix):

              Column              |  Type   | Nullable | Default
----------------------------------+---------+----------+---------
 id                               | bigint  | not null | nextval(...)
 id_convert_to_bigint             | integer | not null | 0
 project_id                       | integer | not null |
 project_id_convert_to_bigint     | bigint  | not null | 0
 environment_id                   | bigint  | not null | 0
 environment_id_convert_to_bigint | integer | not null |
 user_id                          | integer |          |
 user_id_convert_to_bigint        | bigint  |          |
Foreign-key constraints:
    "fk_b9a3851b82" FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
    "fk_b9a3851b82_tmp" FOREIGN KEY (project_id_convert_to_bigint) REFERENCES projects(id) ON DELETE CASCADE

Migration

validate_foreign_key :deployments, :project_id_convert_to_bigint, name: :fk_b9a3851b82_tmp

Guarded on column_exists?('deployments', 'project_id_convert_to_bigint'), so it self-skips where the conversion column is absent. down is a deliberate no-op, matching phase one.

db/structure.sql is unchanged. Schemas built from structure.sql have no _convert_to_bigint columns, so there is nothing for this migration to alter locally — the same reason the phase-one commit touched only migration files and checksums.

Migration output

No-op locally, as the conversion column does not exist in a schema loaded from db/structure.sql.

Not in this MR

Deliberately split so each step is independently reviewable and revertable:

1. The column swap — swaps project_id and user_id with their bigint counterparts, along with the seven project_id indexes, the self-managed-only user_id index, and fk_b9a3851b82. Simpler than phase one: the primary key is already bigint, so there is no DROP CONSTRAINT ... CASCADE and no inbound foreign key renaming.
2. Dropping the tmp indexes and FK.

Once both land, #551605 (drop the trigger and old integer columns) unblocks for the following milestone.

Note for reviewers

deployments is a member of the prd_main_siphon_publication_1 logical replication publication. This MR only validates a constraint and changes no column names, so it carries no risk there — flagging it because the follow-up swap MR renames project_id, and logical decoding resolves columns by name. Phase one performed the same rename on this same published table without incident, but it is worth a deliberate look rather than an inference when that MR arrives.

References

• Related to #551602
• Epic: &20032
• Phase one swap: !221371 (merged)
• Adds the FK being validated here: !217495 (merged)

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.