Add migrations for background verification schema

This commit adds the necessary schema changes for the metadata
background verification. Each replica receives two new columns:

1. 'verified_at' which contains the timestamp of the last successful
   verification of the replica. This effectively allows for identifying
   replicas that are in need of reverification.

2. 'verification_leased_until' which contains a timestamp until which
   a worker has acquired a lease to reverify the repository. This prevents
   multiple workers from picking the same repository for reverification at
   the same time.

'verification_queue' index is added to index replicas which have not been
acquired by any worker. This allows for efficientl querying replicas that
are in need of reverification later.

Changelog: other

1 files changed, 10 insertions, 1 deletions

diff --git a/_support/praefect-schema.sql b/_support/praefect-schema.sql
index b69a2f0c6..78316e80c 100644
--- a/_support/praefect-schema.sql
+++ b/_support/praefect-schema.sql
@@ -266,7 +266,9 @@ CREATE TABLE public.storage_repositories (
     relative_path text NOT NULL,
     storage text NOT NULL,
     generation bigint NOT NULL,
-    repository_id bigint NOT NULL
+    repository_id bigint NOT NULL,
+    verified_at timestamp with time zone,
+    verification_leased_until timestamp with time zone
 );
 
 
@@ -556,6 +558,13 @@ CREATE UNIQUE INDEX storage_repositories_new_pkey ON public.storage_repositories
 
 
 --
+-- Name: verification_queue; Type: INDEX; Schema: public; Owner: -
+--
+
+CREATE INDEX verification_queue ON public.storage_repositories USING btree (verified_at NULLS FIRST) WHERE (verification_leased_until IS NULL);
+
+
+--
 -- Name: virtual_target_on_replication_queue_idx; Type: INDEX; Schema: public; Owner: -
 --