c/bmaas/bmdb: implement backoff and history
This augments the existing Work mechanism with a Fail outcome/method
which allows insertion of a machine & process backoff until a deadline
expires.
We also add a history/audit table which contains information about the
work history of a machine - when some work started, finished, failed or
got cancelled.
Change-Id: If890a412977c1d3c7ff3baa69987fb74932818a0
Reviewed-on: https://review.monogon.dev/c/monogon/+/1086
Tested-by: Jenkins CI
Reviewed-by: Leopold Schabel <leo@monogon.tech>
diff --git a/cloud/bmaas/bmdb/model/migrations/1672749980_backoff.up.sql b/cloud/bmaas/bmdb/model/migrations/1672749980_backoff.up.sql
new file mode 100644
index 0000000..24f90e5
--- /dev/null
+++ b/cloud/bmaas/bmdb/model/migrations/1672749980_backoff.up.sql
@@ -0,0 +1,53 @@
+CREATE TYPE work_history_event AS ENUM (
+ 'Started',
+ 'Finished',
+ 'Failed',
+ 'Canceled'
+);
+
+-- Audit trail of work history for a given machine.
+CREATE TABLE work_history(
+ -- The machine subject to this audit entry. As we want to allow keeping
+ -- information about deleted machines, this is not a foreign key.
+ machine_id UUID NOT NULL,
+
+ -- TODO(q3k): session history?
+
+ -- Process acting on this machine which caused an audit entry to be created.
+ process process NOT NULL,
+ -- Process lifecycle event (started, finished, etc) that caused this audit
+ -- entry to be created.
+ event work_history_event NOT NULL,
+ -- Time at which this entry was created.
+ timestamp TIMESTAMPTZ NOT NULL,
+
+ -- Failure cause, only set when event == Failed.
+ failed_cause STRING
+);
+
+CREATE INDEX ON work_history (machine_id);
+
+-- Backoff entries are created by failed work items, and effectively act as
+-- a Lockout-tagout entry for a given machine and a given process.
+--
+-- Currently, there is no way to fully backoff an entire machine, just
+-- individual processes from a given machine.
+--
+-- Backoff entries are only valid as long as 'until' is before now(), after that
+-- they are ignored by workflow queries. Insertion queries act as upserts,
+-- and thus the backoff entries do not need to be garbage collected, as they do
+-- not grow unbounded (maximumum one entry per process/machine).
+CREATE TABLE work_backoff(
+ -- The machine affected by this backoff.
+ machine_id UUID NOT NULL REFERENCES machines(machine_id) ON DELETE CASCADE,
+ -- The process that this machine should not be subjected to.
+ process process NOT NULL,
+ -- Until when the backoff is enforced.
+ until TIMESTAMPTZ NOT NULL,
+
+ -- Error reported by process/work when this backoff was inserted.
+ -- Human-readable.
+ cause STRING NOT NULL,
+
+ UNIQUE(machine_id, process)
+);
\ No newline at end of file