Add support for incrementally tracking blocked builds

This will hopefully provide a less expensive way of finding out if a scheduled
build is probably blocked by other builds failing or being canceled.

By working this out when the build events are recieved, it should be more
feasible to include information about whether builds are likely blocked or not
in various places (e.g. revision comparisons).

4 files changed, 34 insertions, 0 deletions

diff --git a/sqitch/deploy/blocked_builds.sql b/sqitch/deploy/blocked_builds.sql
new file mode 100644
index 0000000..d3fa429
--- /dev/null
+++ b/sqitch/deploy/blocked_builds.sql
@@ -0,0 +1,19 @@
+-- Deploy guix-data-service:blocked_builds to pg
+
+BEGIN;
+
+CREATE TABLE blocked_builds (
+  build_server_id integer NOT NULL REFERENCES build_servers (id),
+  blocked_derivation_output_details_set_id integer NOT NULL REFERENCES derivation_output_details_sets (id),
+  blocking_derivation_output_details_set_id integer NOT NULL REFERENCES derivation_output_details_sets (id),
+  PRIMARY KEY (
+    build_server_id,
+    blocked_derivation_output_details_set_id,
+    blocking_derivation_output_details_set_id
+  )
+) PARTITION BY LIST (build_server_id);
+
+CREATE INDEX blocked_builds_blocked_derivation_output_details_set_id
+  ON blocked_builds (build_server_id, blocked_derivation_output_details_set_id);
+
+COMMIT;
diff --git a/sqitch/revert/blocked_builds.sql b/sqitch/revert/blocked_builds.sql
new file mode 100644
index 0000000..1adf12e
--- /dev/null
+++ b/sqitch/revert/blocked_builds.sql
@@ -0,0 +1,7 @@
+-- Revert guix-data-service:blocked_builds from pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index a33137b..5af890d 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -89,3 +89,4 @@ package_range_index 2022-06-17T10:39:31Z Chris <chris@felis> # Add index on pack
 fix_git_commits_duplicates 2022-06-17T10:39:50Z Chris <chris@felis> # Fix git_commits duplicates
 git_repositories_query_substitutes 2022-09-09T11:35:16Z Chris <chris@felis> # Add git_repositories.query_substitutes
 package_derivations_id_package_id_idx 2022-09-14T09:24:30Z Chris <chris@felis> # Add index on package_derivations id and package_id
+blocked_builds 2022-11-07T11:27:28Z Chris <chris@felis> # Add blocked_builds
diff --git a/sqitch/verify/blocked_builds.sql b/sqitch/verify/blocked_builds.sql
new file mode 100644
index 0000000..504d07a
--- /dev/null
+++ b/sqitch/verify/blocked_builds.sql
@@ -0,0 +1,7 @@
+-- Verify guix-data-service:blocked_builds on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;