Geo: add migrations to delete project_repository_states duplicate records and prevent further duplicates

What does this MR do and why?

This MR implements Step 2 of the deduplication plan tracked in #590458 (closed).

The project_repository_states table can contain duplicate rows for the same project_repository_id due to a missing uniqueness constraint. This MR adds a post-deployment migration (DeduplicateProjectRepositoryStates) that cleans up any such existing duplicates and a unique index to ensure no new duplicates will be created.

The deletion can be done in a post-deployment migration because the table is seldom used yet, so will be small. On GitLab.com, it's empty (because Geo is not enabled).

References

• Addresses #590458 (closed)
• Subtask of #590033 (closed)

Database considerations

Destructive action

The migration 20260316153056_deduplicate_project_repository_states_spec.rb does remove duplicate records in the project_repository_states table. If something goes wrong and too many/all records are deleted, this will not cause disruption to users:

1. This table is not yet used. It will be used when Project Replication V2 is added and enabled (not before 19.0 at the very earliest). There might already be records in there through the default Geo SSF behaviour, but those records would not be used for actual Geo replication.
2. Geo has mechanisms to periodically populate missing records in this table through the Geo::VerificationStateBackfillService, called every minute from the VerificationCronWorker.

Query plans

I inserted 49,000 unique project_repository_states rows and 500 duplicate rows in order to have data to run the queries on. I do not expect any self managed instance to have that many states populated already, given that the table is not in active use.

SELECT action

SELECT "project_repository_states"."project_repository_id"
 FROM "project_repository_states"
 WHERE "project_repository_states"."project_repository_id" IN (
   SELECT "project_repository_states"."project_repository_id"
   FROM "project_repository_states"
   WHERE "project_repository_states"."id" BETWEEN 1 AND 100000000000 )
 GROUP BY "project_repository_states"."project_repository_id"
 HAVING COUNT(*) > 1;

⬆️ I used a very high count for the batch instead of the default of 1000 in order to return records.

Query plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/49311/commands/148422

DELETE action

WITH duplicated_records AS MATERIALIZED (
SELECT id, ROW_NUMBER() OVER (PARTITION BY project_repository_id ORDER BY id DESC) AS row_number
FROM project_repository_states
WHERE project_repository_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10))
DELETE FROM project_repository_states
WHERE id IN (SELECT id FROM duplicated_records WHERE row_number > 1);

⬆️ I used 10 records arbitrarily. Theoretically there should not even be any duplicates as the method that could create some in the first place is not in use with this model yet.

Query plan: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/49311/commands/148423

How to set up and validate locally

1. Check out the branch
2. Roll back to before the migrations (if you've already run them)

bundle exec rails db:migrate:down:main VERSION=20260317141445
bundle exec rails db:migrate:down:main VERSION=20260316153056

3. Open the Rails console and create duplicate records:

project = Project.first
repo = project.project_repository
state = repo.project_repository_state # if it doesn't exist, it's OK, we'll create some
state_data = { project_repository_id: repo.id, project_id: project.id }

# Now create and duplicate this existing state
Geo::ProjectRepositoryState.insert_all([state_data, state_data]) # insert 2 additional records

# Verify there's more than one record for the repo:
Geo::ProjectRepositoryState.where(project_repository_id: repo.id)

4. Now run the migrations:

bundle exec rails db:migrate VERSION=20260316153056
bundle exec rails db:migrate VERSION=20260317141445

5. Verify in the console:

# No duplicates remain
Geo::ProjectRepositoryState.where(project_repository_id: repo.id) # only returns 1 record

6. Inserting a duplicate record does not work anymore:

# This'll return @rows=[] meaning no row was inserted
Geo::ProjectRepositoryState.insert(project_repository_id: repo.id, project_id: repo.project_id, verification_state: 0)

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.