Refactor how unprocessed builds are tracked

Previously, the allocator worked out the derived priorities for each
build. Unfortunately this is quite a complex query, and took lots of time. As
a result of this, I think the WAL could grow excessively while this long query
was running.

To try and mitigate this, add a new table to keep track of the derived
priorities for unprocessed builds. This requires some maintenance to keep up
to date, which in turn will make things like submitting builds slower, but I
think this might help keep transaction length and WAL size down overall.

11 files changed, 414 insertions, 127 deletions

diff --git a/guix-build-coordinator/build-allocator.scm b/guix-build-coordinator/build-allocator.scm
index d43cbbf..31f3865 100644
--- a/guix-build-coordinator/build-allocator.scm
+++ b/guix-build-coordinator/build-allocator.scm
@@ -491,11 +491,9 @@
     (let-values
         (((derived-build-priorities-hash
            build-ids-for-unprocessed-builds-with-built-inputs)
-          (with-time-logging
-           "fetch-build-ids-and-propagated-priorities-for-unprocessed-builds"
-           (datastore-fetch-build-ids-and-propagated-priorities-for-unprocessed-builds
-            datastore
-            builds-created-after))))
+          (datastore-fetch-build-ids-and-propagated-priorities-for-unprocessed-builds
+           datastore
+           builds-created-after)))
 
       (define systems-for-builds
         (map (lambda (build-id)
diff --git a/guix-build-coordinator/coordinator.scm b/guix-build-coordinator/coordinator.scm
index c95099d..310e8dd 100644
--- a/guix-build-coordinator/coordinator.scm
+++ b/guix-build-coordinator/coordinator.scm
@@ -880,6 +880,9 @@
                         (datastore-delete-relevant-outputs-from-unbuilt-outputs
                          datastore
                          build-id)
+                        (datastore-update-unprocessed-builds-for-build-success
+                         datastore
+                         build-id)
                         (datastore-store-output-metadata
                          datastore
                          build-id
diff --git a/guix-build-coordinator/datastore.scm b/guix-build-coordinator/datastore.scm
index 613de36..0afaea0 100644
--- a/guix-build-coordinator/datastore.scm
+++ b/guix-build-coordinator/datastore.scm
@@ -26,6 +26,7 @@
 (re-export datastore-fetch-agent-tags)
 (re-export datastore-count-build-results)
 (re-export datastore-insert-build-result)
+(re-export datastore-update-unprocessed-builds-for-build-success)
 (re-export datastore-remove-build-allocation)
 (re-export datastore-mark-build-as-processed)
 (re-export datastore-delete-relevant-outputs-from-unbuilt-outputs)
diff --git a/guix-build-coordinator/datastore/sqlite.scm b/guix-build-coordinator/datastore/sqlite.scm
index 1eeac2b..55f9648 100644
--- a/guix-build-coordinator/datastore/sqlite.scm
+++ b/guix-build-coordinator/datastore/sqlite.scm
@@ -38,6 +38,7 @@
             datastore-count-builds-for-derivation
             datastore-count-build-results
             datastore-insert-build-result
+            datastore-update-unprocessed-builds-for-build-success
             datastore-remove-build-allocation
             datastore-mark-build-as-processed
             datastore-delete-relevant-outputs-from-unbuilt-outputs
@@ -995,6 +996,21 @@ UPDATE builds SET canceled = 1 WHERE uuid = :uuid"
         #:uuid uuid)
 
        (sqlite-step statement)
+       (sqlite-reset statement))
+
+     (let ((statement (sqlite-prepare
+                       db
+                       "
+DELETE FROM unprocessed_builds_with_derived_priorities
+  WHERE build_id = (
+    SELECT id FROM builds WHERE uuid = :uuid
+)"
+                       #:cache? #t)))
+       (sqlite-bind-arguments
+        statement
+        #:uuid uuid)
+
+       (sqlite-step statement)
        (sqlite-reset statement))))
   #t)
 
@@ -1079,6 +1095,80 @@ INSERT INTO build_results (
        ")"))))
   #t)
 
+(define-method (datastore-update-unprocessed-builds-for-build-success
+                (datastore <sqlite-datastore>)
+                build-uuid)
+  (define (all-inputs-built? db build-id)
+    (let ((statement
+           (sqlite-prepare
+            db
+            "
+SELECT 1
+FROM derivation_inputs
+INNER JOIN derivation_outputs
+  ON derivation_inputs.derivation_output_id = derivation_outputs.id
+INNER JOIN unbuilt_outputs
+  ON unbuilt_outputs.output_id = derivation_outputs.output_id
+INNER JOIN builds
+  ON builds.derivation_id = derivation_inputs.derivation_id
+WHERE builds.id = :build_id"
+            #:cache? #t)))
+
+      (sqlite-bind-arguments
+       statement
+       #:build_id build-id)
+
+      (let ((result (match (sqlite-step statement)
+                      (#f #t)
+                      (#(1) #f))))
+        (sqlite-reset statement)
+
+        result)))
+
+  (call-with-worker-thread
+   (slot-ref datastore 'worker-writer-thread-channel)
+   (lambda (db)
+     (let ((builds-statement
+            (sqlite-prepare
+             db
+             "
+SELECT DISTINCT unprocessed_builds.id
+FROM builds
+INNER JOIN derivation_outputs
+  ON builds.derivation_id = derivation_outputs.derivation_id
+INNER JOIN derivation_outputs AS all_derivation_outputs
+  ON all_derivation_outputs.output_id = derivation_outputs.output_id
+INNER JOIN derivation_inputs
+  ON derivation_inputs.derivation_output_id = all_derivation_outputs.id
+INNER JOIN builds AS unprocessed_builds
+  ON unprocessed_builds.processed = 0
+ AND unprocessed_builds.derivation_id = derivation_inputs.derivation_id
+INNER JOIN unprocessed_builds_with_derived_priorities
+  ON unprocessed_builds_with_derived_priorities.build_id = unprocessed_builds.id
+ AND unprocessed_builds_with_derived_priorities.all_inputs_built = 0"
+             #:cache? #t))
+
+           (update-statement
+            (sqlite-prepare
+             db
+             "
+UPDATE unprocessed_builds_with_derived_priorities
+SET all_inputs_built = 1
+WHERE build_id = :build_id"
+             #:cache? #t)))
+
+       (sqlite-fold
+        (lambda (row result)
+          (match row
+            (#(build-id)
+             (when (all-inputs-built? db build-id)
+               (sqlite-bind-arguments update-statement #:build_id build-id)
+               (sqlite-step update-statement)
+               (sqlite-reset update-statement))))
+          #f)
+        #f
+        builds-statement)))))
+
 (define-method (datastore-remove-build-allocation
                 (datastore <sqlite-datastore>)
                 build-uuid agent-id)
@@ -1111,7 +1201,22 @@ SET processed = 1 "
        (if end-time
            (string-append ", end_time = '" end-time "'")
            "") "
-WHERE id = " (number->string (db-find-build-id db build-uuid))))))
+WHERE id = " (number->string (db-find-build-id db build-uuid))))
+
+     (let ((statement (sqlite-prepare
+                       db
+                       "
+DELETE FROM unprocessed_builds_with_derived_priorities
+  WHERE build_id = (
+    SELECT id FROM builds WHERE uuid = :uuid
+)"
+                       #:cache? #t)))
+       (sqlite-bind-arguments
+        statement
+        #:uuid build-uuid)
+
+       (sqlite-step statement)
+       (sqlite-reset statement))))
   #t)
 
 (define-method (datastore-delete-relevant-outputs-from-unbuilt-outputs
@@ -2137,103 +2242,21 @@ LIMIT 1"
 (define-method (datastore-fetch-build-ids-and-propagated-priorities-for-unprocessed-builds
                 (datastore <sqlite-datastore>)
                 created-after)
-  (define (populate-unprocessed-builds-table db)
-    (sqlite-exec
-     db
-     "
-DROP TABLE IF EXISTS temp.unprocessed_builds")
-
-    (let ((statement
-           (sqlite-prepare
-            db
-            (string-append
-             "
-CREATE TEMP TABLE unprocessed_builds AS
-SELECT id
-FROM builds
-WHERE processed = 0
-  AND canceled = 0
-  AND (
-    deferred_until IS NULL
-    OR
-    deferred_until < datetime('now')
-  )
-"
-             (if created-after
-                 (simple-format
-                  #f "AND created_at >= ~A\n"
-                  created-after)
-                 "")
-             "
-AND NOT EXISTS (
-  SELECT 1
-  FROM derivation_inputs
-  INNER JOIN derivation_outputs
-    ON derivation_inputs.derivation_output_id = derivation_outputs.id
-  INNER JOIN unbuilt_outputs
-    ON unbuilt_outputs.output_id = derivation_outputs.output_id
-  WHERE builds.derivation_id = derivation_inputs.derivation_id
-)
-EXCEPT
-  SELECT build_id FROM allocated_builds
-"))))
-
-      (sqlite-step statement)
-      (sqlite-finalize statement)
-      #t))
-
-  (define (query-unprocessed-builds-table db)
-    (let ((statement
-           (sqlite-prepare
-            db
-            "
-SELECT builds.uuid
-FROM unprocessed_builds
-INNER JOIN builds
-  ON builds.id = unprocessed_builds.id"
-            #:cache? #t)))
-
-      (let ((result (sqlite-map
-                     (match-lambda
-                       (#(uuid) uuid))
-                     statement)))
-        (sqlite-reset statement)
-
-        result)))
-
   (define (fetch-propagated-priorities-for-unprocessed-builds db)
     (let ((statement
            (sqlite-prepare
             db
             "
-WITH RECURSIVE builds_with_derived_priority(
-                 id, derivation_id, derived_priority
-               ) AS (
-  SELECT builds.id, builds.derivation_id, builds.priority
-  FROM builds
-  INNER JOIN unprocessed_builds
-    ON builds.id = unprocessed_builds.id
-UNION
-  SELECT builds.id, builds.derivation_id,
-         max(builds.priority, builds_with_derived_priority.derived_priority)
-  FROM builds_with_derived_priority
-  INNER JOIN derivation_outputs
-    ON builds_with_derived_priority.derivation_id =
-       derivation_outputs.derivation_id
-  INNER JOIN derivation_outputs AS all_derivation_outputs
-    ON all_derivation_outputs.output_id = derivation_outputs.output_id
-  INNER JOIN derivation_inputs
-    ON derivation_inputs.derivation_output_id = all_derivation_outputs.id
-  INNER JOIN builds
-    ON builds.processed = 0
-   AND builds.derivation_id = derivation_inputs.derivation_id
-)
-SELECT builds.uuid, MAX(derived_priority)
-FROM builds_with_derived_priority
-INNER JOIN builds
-  ON builds.id = builds_with_derived_priority.id
-WHERE builds.processed = 0
-GROUP BY builds.uuid"
+SELECT builds.uuid, derived_priority
+FROM unprocessed_builds_with_derived_priorities
+INNER JOIN builds ON build_id = builds.id
+WHERE all_inputs_built = 1
+  AND NOT EXISTS (
+    SELECT 1
+    FROM allocated_builds
+    WHERE unprocessed_builds_with_derived_priorities.build_id =
+          allocated_builds.build_id
+) ORDER BY derived_priority ASC"
             #:cache? #t)))
 
       (let ((result (sqlite-fold
@@ -2248,19 +2271,17 @@ GROUP BY builds.uuid"
 
         result)))
 
-  (datastore-call-with-transaction
-   datastore
+  (call-with-worker-thread
+   (slot-ref datastore 'worker-reader-thread-channel)
    (lambda (db)
-     (populate-unprocessed-builds-table db)
-
-     (let ((propagated-priorities
-            (fetch-propagated-priorities-for-unprocessed-builds db))
-           (unprocessed-build-ids
-            (query-unprocessed-builds-table db)))
+     (let* ((propagated-priorities
+             (fetch-propagated-priorities-for-unprocessed-builds db))
+            (unprocessed-build-ids
+             (hash-map->list (lambda (key _) key)
+                             propagated-priorities)))
 
        (values propagated-priorities
-               unprocessed-build-ids)))
-   #:readonly? #t))
+               unprocessed-build-ids)))))
 
 (define-method (datastore-insert-unprocessed-hook-event
                 (datastore <sqlite-datastore>)
@@ -3451,29 +3472,190 @@ INSERT INTO derivation_outputs (derivation_id, name, output_id) VALUES "
                 (datastore <sqlite-datastore>)
                 uuid derivation-name priority
                 defer-until)
-  (call-with-worker-thread
-   (slot-ref datastore 'worker-writer-thread-channel)
-   (lambda (db)
-     (let ((statement
-            (sqlite-prepare
-             db
-             "
+  (define (insert-build db)
+    (let ((statement
+           (sqlite-prepare
+            db
+            "
 INSERT INTO builds (uuid, derivation_id, priority, created_at, deferred_until)
 VALUES (:uuid, :derivation_id, :priority, datetime('now'), :deferred_until)"
-             #:cache? #t)))
+            #:cache? #t)))
 
-       (sqlite-bind-arguments
-        statement
-        #:uuid uuid
-        #:derivation_id (db-find-derivation-id db derivation-name)
-        #:priority priority
-        #:deferred_until
-        (and=> defer-until
-               (lambda (date)
-                 (date->string date "~1 ~3"))))
+      (sqlite-bind-arguments
+       statement
+       #:uuid uuid
+       #:derivation_id (db-find-derivation-id db derivation-name)
+       #:priority priority
+       #:deferred_until
+       (and=> defer-until
+              (lambda (date)
+                (date->string date "~1 ~3"))))
 
-       (sqlite-step statement)
-       (sqlite-reset statement))))
+      (sqlite-step statement)
+      (sqlite-reset statement))
+
+    (last-insert-rowid db))
+
+  (define (get-derived-priority db build-id)
+    (let ((statement
+           (sqlite-prepare
+            db
+            "
+SELECT max(dependent_unprocessed_builds_with_derived_priorities.derived_priority)
+FROM unprocessed_builds_with_derived_priorities
+INNER JOIN builds
+  ON builds.id = unprocessed_builds_with_derived_priorities.build_id
+INNER JOIN derivation_outputs
+  ON builds.derivation_id =
+     derivation_outputs.derivation_id
+INNER JOIN derivation_outputs AS all_derivation_outputs
+  ON all_derivation_outputs.output_id = derivation_outputs.output_id
+INNER JOIN derivation_inputs
+  ON derivation_inputs.derivation_output_id = all_derivation_outputs.id
+INNER JOIN builds AS dependent_builds
+  ON dependent_builds.processed = 0
+ AND dependent_builds.derivation_id = derivation_inputs.derivation_id
+INNER JOIN unprocessed_builds_with_derived_priorities AS dependent_unprocessed_builds_with_derived_priorities
+  ON dependent_builds.id = dependent_unprocessed_builds_with_derived_priorities.build_id
+WHERE unprocessed_builds_with_derived_priorities.build_id = :build_id"
+            #:cache? #t)))
+
+      (sqlite-bind-arguments
+       statement
+       #:build_id build-id)
+
+      (let ((result (match (sqlite-step statement)
+                      (#(#f) #f)
+                      (#(derived-priority) derived-priority))))
+        (sqlite-reset statement)
+
+        result)))
+
+  (define (all-inputs-built? db build-id)
+    (let ((statement
+           (sqlite-prepare
+            db
+            "
+SELECT 1
+FROM derivation_inputs
+INNER JOIN derivation_outputs
+  ON derivation_inputs.derivation_output_id = derivation_outputs.id
+INNER JOIN unbuilt_outputs
+  ON unbuilt_outputs.output_id = derivation_outputs.output_id
+INNER JOIN builds
+  ON builds.derivation_id = derivation_inputs.derivation_id
+WHERE builds.id = :build_id"
+            #:cache? #t)))
+
+      (sqlite-bind-arguments
+       statement
+       #:build_id build-id)
+
+      (let ((result (match (sqlite-step statement)
+                      (#f #t)
+                      (#(1) #f))))
+        (sqlite-reset statement)
+
+        result)))
+
+  (define (insert-unprocessed-builds-with-derived-priorities-entry
+           db
+           build-id
+           derived-priority
+           all-inputs-built?)
+    (let ((statement
+           (sqlite-prepare
+            db
+            "
+INSERT INTO unprocessed_builds_with_derived_priorities (build_id, derived_priority, all_inputs_built)
+VALUES (:build_id, :derived_priority, :all_inputs_built)"
+            #:cache? #t)))
+
+      (sqlite-bind-arguments
+       statement
+       #:build_id build-id
+       #:derived_priority derived-priority
+       #:all_inputs_built (if all-inputs-built? 1 0))
+
+      (sqlite-step statement)
+      (sqlite-reset statement)))
+
+  (define (update-unprocessed-builds-with-derived-priorities db
+                                                             build-id
+                                                             derived-priority)
+    ;; Recursively find builds for all missing outputs that this build takes
+    ;; as inputs. These builds should have a derived priority of at least the
+    ;; derived priority of this build
+    (let ((find-builds-statement
+           (sqlite-prepare
+            db
+            "
+WITH RECURSIVE relevant_builds (id) AS (
+  VALUES (:build_id)
+UNION
+  SELECT builds.id
+  FROM relevant_builds
+  INNER JOIN builds AS relevant_builds_full
+    ON relevant_builds.id = relevant_builds_full.id
+  INNER JOIN derivation_outputs
+    ON relevant_builds_full.derivation_id = derivation_outputs.derivation_id
+  INNER JOIN unbuilt_outputs
+    ON unbuilt_outputs.output_id = derivation_outputs.output_id
+  INNER JOIN derivation_outputs AS all_derivation_outputs
+    ON all_derivation_outputs.output_id = derivation_outputs.output_id
+  INNER JOIN derivation_inputs
+    ON derivation_inputs.derivation_output_id = all_derivation_outputs.id
+  INNER JOIN builds
+    ON builds.processed = 0
+   AND builds.derivation_id = derivation_inputs.derivation_id
+)
+SELECT build_id
+FROM relevant_builds
+INNER JOIN unprocessed_builds_with_derived_priorities
+  ON relevant_builds.id = unprocessed_builds_with_derived_priorities.build_id
+WHERE unprocessed_builds_with_derived_priorities.derived_priority <
+      :derived_priority"
+            #:cache? #t))
+
+          (update-derived-priority-statement
+           (sqlite-prepare
+            db
+            "
+UPDATE unprocessed_builds_with_derived_priorities
+SET derived_priority = :derived_priority
+WHERE build_id = :build_id"
+            #:cache? #t)))
+
+      (sqlite-fold
+       (lambda (row result)
+         (match row
+           (#(id)
+            (sqlite-bind-arguments update-derived-priority-statement
+                                   #:build_id id
+                                   #:derived_priority derived-priority)
+            (sqlite-step update-derived-priority-statement)
+            (sqlite-reset update-derived-priority-statement)))
+         #f)
+       #f
+       find-builds-statement)
+      (sqlite-reset find-builds-statement)))
+
+  (call-with-worker-thread
+   (slot-ref datastore 'worker-writer-thread-channel)
+   (lambda (db)
+     (let* ((build-id          (insert-build db))
+            (derived-priority  (or (get-derived-priority db build-id)
+                                   priority))
+            (all-inputs-built? (all-inputs-built? db build-id)))
+
+       (insert-unprocessed-builds-with-derived-priorities-entry db
+                                                                build-id
+                                                                derived-priority
+                                                                all-inputs-built?)
+       (unless all-inputs-built?
+         (update-unprocessed-builds-with-derived-priorities db
+                                                            build-id
+                                                            derived-priority)))))
   #t)
 
 (define (insert-agent db uuid name description)
diff --git a/sqitch/pg/deploy/create_unprocessed_builds_with_derived_priorities.sql b/sqitch/pg/deploy/create_unprocessed_builds_with_derived_priorities.sql
new file mode 100644
index 0000000..adfda14
--- /dev/null
+++ b/sqitch/pg/deploy/create_unprocessed_builds_with_derived_priorities.sql
@@ -0,0 +1,7 @@
+-- Deploy guix-build-coordinator:create_unprocessed_builds_with_derived_priorities to pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/pg/revert/create_unprocessed_builds_with_derived_priorities.sql b/sqitch/pg/revert/create_unprocessed_builds_with_derived_priorities.sql
new file mode 100644
index 0000000..e16cdf5
--- /dev/null
+++ b/sqitch/pg/revert/create_unprocessed_builds_with_derived_priorities.sql
@@ -0,0 +1,7 @@
+-- Revert guix-build-coordinator:create_unprocessed_builds_with_derived_priorities from pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/pg/verify/create_unprocessed_builds_with_derived_priorities.sql b/sqitch/pg/verify/create_unprocessed_builds_with_derived_priorities.sql
new file mode 100644
index 0000000..a8a5251
--- /dev/null
+++ b/sqitch/pg/verify/create_unprocessed_builds_with_derived_priorities.sql
@@ -0,0 +1,7 @@
+-- Verify guix-build-coordinator:create_unprocessed_builds_with_derived_priorities on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index 198b4de..68e770e 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -34,3 +34,4 @@ fix_allocated_builds_attempt_2 2021-03-30T08:27:18Z Christopher Baines <mail@cba
 fix_setup_failure_missing_inputs 2021-04-08T19:31:49Z Christopher Baines <mail@cbaines.net> # Fix setup_failure_missing_inputs
 create_outputs 2021-05-21T13:37:49Z Christopher Baines <mail@cbaines.net> # Create the outputs table
 create_systems 2021-05-21T17:22:52Z Christopher Baines <mail@cbaines.net> # Create the systems table
+create_unprocessed_builds_with_derived_priorities 2021-10-22T14:52:18Z Christopher Baines <mail@cbaines.net> # Create unprocessed_builds_with_derived_priorities
diff --git a/sqitch/sqlite/deploy/create_unprocessed_builds_with_derived_priorities.sql b/sqitch/sqlite/deploy/create_unprocessed_builds_with_derived_priorities.sql
new file mode 100644
index 0000000..67cc872
--- /dev/null
+++ b/sqitch/sqlite/deploy/create_unprocessed_builds_with_derived_priorities.sql
@@ -0,0 +1,67 @@
+-- Deploy guix-build-coordinator:create_unprocessed_builds_with_derived_priorities to sqlite
+
+BEGIN;
+
+CREATE TABLE unprocessed_builds_with_derived_priorities (
+  build_id INTEGER PRIMARY KEY REFERENCES builds (id),
+  derived_priority INTEGER NOT NULL,
+  all_inputs_built BOOLEAN NOT NULL
+);
+
+INSERT INTO unprocessed_builds_with_derived_priorities WITH RECURSIVE unprocessed_builds AS (
+  SELECT id
+  FROM builds
+  WHERE processed = 0
+    AND canceled = 0
+    AND (
+      deferred_until IS NULL
+      OR
+      deferred_until < datetime('now')
+    )
+), builds_with_derived_priority(
+                 id, derivation_id, derived_priority
+               ) AS (
+  SELECT builds.id, builds.derivation_id, builds.priority
+  FROM builds
+  INNER JOIN unprocessed_builds
+    ON builds.id = unprocessed_builds.id
+UNION
+  SELECT builds.id, builds.derivation_id,
+         max(builds.priority, builds_with_derived_priority.derived_priority)
+  FROM builds_with_derived_priority
+  INNER JOIN derivation_outputs
+    ON builds_with_derived_priority.derivation_id =
+       derivation_outputs.derivation_id
+  INNER JOIN derivation_outputs AS all_derivation_outputs
+    ON all_derivation_outputs.output_id = derivation_outputs.output_id
+  INNER JOIN derivation_inputs
+    ON derivation_inputs.derivation_output_id = all_derivation_outputs.id
+  INNER JOIN builds
+    ON builds.processed = 0
+   AND builds.derivation_id = derivation_inputs.derivation_id
+), data AS (
+  SELECT builds.id AS build_id, MAX(derived_priority)
+  FROM builds_with_derived_priority
+  INNER JOIN builds
+    ON builds.id = builds_with_derived_priority.id
+  WHERE builds.processed = 0
+  GROUP BY builds.id
+)
+SELECT data.*,
+       NOT EXISTS (
+         SELECT 1
+         FROM derivation_inputs
+         INNER JOIN derivation_outputs
+           ON derivation_inputs.derivation_output_id = derivation_outputs.id
+         INNER JOIN unbuilt_outputs
+           ON unbuilt_outputs.output_id = derivation_outputs.output_id
+         WHERE builds.derivation_id = derivation_inputs.derivation_id
+       )
+FROM data
+INNER JOIN builds ON builds.id = data.build_id;
+
+CREATE INDEX unprocessed_builds_with_derived_priorities_derived_priorities_index_for_all_inputs_built
+  ON unprocessed_builds_with_derived_priorities (derived_priority)
+  WHERE all_inputs_built = 1;
+
+COMMIT;
diff --git a/sqitch/sqlite/revert/create_unprocessed_builds_with_derived_priorities.sql b/sqitch/sqlite/revert/create_unprocessed_builds_with_derived_priorities.sql
new file mode 100644
index 0000000..51b7f92
--- /dev/null
+++ b/sqitch/sqlite/revert/create_unprocessed_builds_with_derived_priorities.sql
@@ -0,0 +1,7 @@
+-- Revert guix-build-coordinator:create_unprocessed_builds_with_derived_priorities from sqlite
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqlite/verify/create_unprocessed_builds_with_derived_priorities.sql b/sqitch/sqlite/verify/create_unprocessed_builds_with_derived_priorities.sql
new file mode 100644
index 0000000..3a17113
--- /dev/null
+++ b/sqitch/sqlite/verify/create_unprocessed_builds_with_derived_priorities.sql
@@ -0,0 +1,7 @@
+-- Verify guix-build-coordinator:create_unprocessed_builds_with_derived_priorities on sqlite
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;