Geo: Prevent duplicate records in project_repository_states

What does this MR do and why?

This MR implements the first step of preventing duplicate records in project_repository_states by adding model-level validation and transaction-level advisory locks. The project_repository_states table currently lacks a unique constraint on project_repository_id, which can lead to duplicate state records being created by the backfill worker (Geo::VerificationStateBackfillService). The duplicate record creation is further avoided by the changes in !223729 (merged)

It adds a uniqueness validation on project_repository and implements a transaction-level advisory lock in the prevent_concurrent_inserts method to block concurrent inserts.

References

• blocking !223027 (closed)
• related to #590033 (closed)
• addressing #590456 (closed)

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.