Replace gpg_signatures unique index for Cells compatibility

Hunter Stewartrequested to merge
562042-cells-gpg-signatures-index-migration into master

What does this MR do and why?

This is MR 1 of 2 for #562042 (closed) (Cells: Handle unique index gpg_signatures.index_gpg_signatures_on_commit_sha).

Work item: #598319 (closed)

Extends the single-column unique index on commit_sha to include project_id (the Cells sharding key), supporting the Cells data partitioning model where each Cell has its own database and global uniqueness on commit_sha alone cannot be enforced.

Index changes

Before After
index_gpg_signatures_on_commit_sha UNIQUE (commit_sha) index_gpg_signatures_on_commit_sha_and_project_id UNIQUE (commit_sha, project_id)
index_gpg_signatures_on_project_id (project_id) (unchanged)

Why this approach?

The existing commit_sha unique index is extended to (commit_sha, project_id) to scope uniqueness per-project for Cells. Since commit_sha remains the leading column, all existing query patterns that filter by commit_sha alone (e.g., CommitSignature.by_commit_sha, CommitSignature#unsigned_commit_shas, lazy_signature in BaseSignedCommit) continue to use this index efficiently.

The standalone index_gpg_signatures_on_project_id is kept as-is — it serves FK cascade deletes on project_id and doesn't need modification.

Two-release shipping strategy

Splitting migration and code changes across two releases ensures the post-deploy migration has executed on all deployment types — including self-managed instances following zero-downtime upgrades — before application code that depends on the new schema is deployed. This prevents uniqueness constraint mismatches between what the code expects and what the database enforces. See the SSH uniqueness incident and database index guidelines for background.

Following the SSH signatures precedent (migrations in 18.8, code in 18.9):

  1. This MR (MR 1, 19.0): Post-deploy migration only — safe to run before code changes deploy. All existing code paths remain fully functional with the new index structure.
  2. MR 2 (19.3): Application code changes (safe_create! override, lazy_signature override, specs) — ships in the next after a required stop to ensure the post deployment migration has executed before the code that depends on it deploys.

References

Migration output

db:migrate (up)

main: == 20260422182859 ReplaceIndexGpgSignaturesOnCommitSha: migrating =============
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0455s
main: -- index_exists?(:gpg_signatures, [:commit_sha, :project_id], {:unique=>true, :name=>"index_gpg_signatures_on_commit_sha_and_project_id", :algorithm=>:concurrently})
main:    -> 0.0038s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- add_index(:gpg_signatures, [:commit_sha, :project_id], {:unique=>true, :name=>"index_gpg_signatures_on_commit_sha_and_project_id", :algorithm=>:concurrently})
main:    -> 0.0029s
main: -- execute("RESET statement_timeout")
main:    -> 0.0007s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0005s
main: -- index_name_exists?(:gpg_signatures, "index_gpg_signatures_on_commit_sha")
main:    -> 0.0009s
main: -- remove_index(:gpg_signatures, {:algorithm=>:concurrently, :name=>"index_gpg_signatures_on_commit_sha"})
main:    -> 0.0025s
main: == 20260422182859 ReplaceIndexGpgSignaturesOnCommitSha: migrated (0.0989s) ====

db:rollback (down)

main: == 20260422182859 ReplaceIndexGpgSignaturesOnCommitSha: reverting =============
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0557s
main: -- index_exists?(:gpg_signatures, :commit_sha, {:unique=>true, :name=>"index_gpg_signatures_on_commit_sha", :algorithm=>:concurrently})
main:    -> 0.0038s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0005s
main: -- add_index(:gpg_signatures, :commit_sha, {:unique=>true, :name=>"index_gpg_signatures_on_commit_sha", :algorithm=>:concurrently})
main:    -> 0.0032s
main: -- execute("RESET statement_timeout")
main:    -> 0.0046s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0006s
main: -- index_name_exists?(:gpg_signatures, "index_gpg_signatures_on_commit_sha_and_project_id")
main:    -> 0.0009s
main: -- remove_index(:gpg_signatures, {:algorithm=>:concurrently, :name=>"index_gpg_signatures_on_commit_sha_and_project_id"})
main:    -> 0.0034s
main: == 20260422182859 ReplaceIndexGpgSignaturesOnCommitSha: reverted (0.1178s) ====
Edited by Hunter Stewart

Merge request reports