Geo: Deduplicate `ProjectRepository` on `project_repository_id` to ensure unique foreign keys

Problem to solve

The SSF template shows how to create the state table with:

t.references :cool_widget,
        null: false,
        index: { unique: true },
        foreign_key: { on_delete: :cascade }

This works well, but if the foreign key is added differently (for example, if it's a loose foreign key), it can be easy to miss the importance of the unique constraint.

When defining foreign keys (loose or not), it's critical to keep the unique index because the backfill workers rely on it:

• Geo::VerificationStateBackfillService
• Calls Geo::VerifiableModel#bulk_insert_verification_state_records
• Uses Rails' insert_all which only prevents duplicate records only if there's a unique constraint

So without the unique constraint, duplicate state records can be created by the backfill worker, leading to potential bugs when counting records or retrieving record states.

Proposal

• Add a test case in the shared examples a verifiable model for verification state to ensure duplicates are handled gracefully (see existing tests for inspiration)
• Investigate current state models to ensure uniqueness of the foreign key and add a constraint if not present

Implementation plan

We can follow the deduplication doc:

• Release 18.10:
  • Step 1: Make sure there can't be duplicates:
    • add model-level validation & transaction level lock
    • override create_verification_details_for in ProjectRepositoryto ensureverification_state_table_class.insert_all` do not add duplicates.
    • add test cases to ensure this can't happen. It should be added to the shared examples a verifiable model for verification state to ensure duplicates are handled gracefully (see existing tests for inspiration)
• Release 18.11:
  • Step 2: Add a post-deployment migration to fix the existing records.
  • Step 3: Replace the existing index with a UNIQUE index on project_repository_id.
    • The override from create_verification_details_for in the ProjectRepository model can be removed
    • Generic tests from step 1 used instead of specific ones
• Release 19.0:
  • Step 4: Remove the transaction lock