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

2 files changed, 33 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: -
 --
 
diff --git a/internal/praefect/datastore/migrations/20220303105110_background_verification_columns.go b/internal/praefect/datastore/migrations/20220303105110_background_verification_columns.go
new file mode 100644
index 000000000..578a86948
--- /dev/null
+++ b/internal/praefect/datastore/migrations/20220303105110_background_verification_columns.go
@@ -0,0 +1,23 @@
+package migrations
+
+import migrate "github.com/rubenv/sql-migrate"
+
+func init() {
+	m := &migrate.Migration{
+		Id: "20220303105110_background_verification_columns",
+		Up: []string{
+			"ALTER TABLE storage_repositories ADD COLUMN verified_at TIMESTAMPTZ",
+			"ALTER TABLE storage_repositories ADD COLUMN verification_leased_until TIMESTAMPTZ",
+			`CREATE INDEX verification_queue ON storage_repositories ( verified_at NULLS FIRST )
+			        WHERE verification_leased_until IS NULL
+			`,
+		},
+		Down: []string{
+			"DROP INDEX verification_queue",
+			"ALTER TABLE storage_repositories DROP COLUMN verification_leased_until",
+			"ALTER TABLE storage_repositories DROP COLUMN verified_at",
+		},
+	}
+
+	allMigrations = append(allMigrations, m)
+}