use provisioner job status for workspace searching

Emyrk · Emyrk · commit 16e17e8a5689 · 2023-10-03T14:05:43.000-05:00
diff --git a/coderd/database/migrations/000160_provisioner_job_status.up.sql b/coderd/database/migrations/000160_provisioner_job_status.up.sql
@@ -18,10 +18,14 @@ ALTER TABLE provisioner_jobs ADD COLUMN
             -- Not completed means it is in some "-ing" state
         	ELSE
 				CASE
+					-- This should never happen because all errors set
+					-- should also set a completed_at timestamp.
+					-- But if there is an error, we should always return
+					-- a failed state.
+					WHEN error != '' THEN 'failed'::provisioner_job_status
 					WHEN canceled_at IS NOT NULL THEN 'canceling'::provisioner_job_status
 				    -- Not done and not started means it is pending
 				    WHEN started_at IS NULL THEN 'pending'::provisioner_job_status
-				    -- Job is still running
 				    ELSE 'running'::provisioner_job_status
 				END
 		END
diff --git a/coderd/database/queries/workspaces.sql b/coderd/database/queries/workspaces.sql
@@ -96,7 +96,8 @@ LEFT JOIN LATERAL (
 		provisioner_jobs.updated_at,
 		provisioner_jobs.canceled_at,
 		provisioner_jobs.completed_at,
-		provisioner_jobs.error
+		provisioner_jobs.error,
+		provisioner_jobs.job_status
 	FROM
 		workspace_builds
 	LEFT JOIN
@@ -128,63 +129,43 @@ WHERE
 	AND CASE
 		WHEN @status :: text != '' THEN
 			CASE
-				WHEN @status = 'pending' THEN
-					latest_build.started_at IS NULL
+			    -- Some workspace specific status refer to the transition
+			    -- type. By default, the standard provisioner job status
+			    -- search strings are supported.
+			    -- 'running' states
 				WHEN @status = 'starting' THEN
-					latest_build.started_at IS NOT NULL AND
-					latest_build.canceled_at IS NULL AND
-					latest_build.completed_at IS NULL AND
+				    latest_build.job_status = 'running' AND
+					-- TODO: Should we drop the interval? Hung workspaces
+					-- should still be their last state?
 					latest_build.updated_at - INTERVAL '30 seconds' < NOW() AND
 					latest_build.transition = 'start'::workspace_transition
-
-				WHEN @status = 'running' THEN
-					latest_build.completed_at IS NOT NULL AND
-					latest_build.canceled_at IS NULL AND
-					latest_build.error IS NULL AND
-					latest_build.transition = 'start'::workspace_transition
-
 				WHEN @status = 'stopping' THEN
-					latest_build.started_at IS NOT NULL AND
-					latest_build.canceled_at IS NULL AND
-					latest_build.completed_at IS NULL AND
+					latest_build.job_status = 'running' AND
+					-- TODO: Should we drop the interval? Hung workspaces
+					-- should still be their last state?
 					latest_build.updated_at - INTERVAL '30 seconds' < NOW() AND
 					latest_build.transition = 'stop'::workspace_transition
+				WHEN @status = 'deleting' THEN
+					latest_build.job_status = 'running' AND
+					-- TODO: Should we drop the interval? Hung workspaces
+					-- should still be their last state?
+					latest_build.updated_at - INTERVAL '30 seconds' < NOW() AND
+					latest_build.transition = 'delete'::workspace_transition
 
+			    -- 'succeeded' states
+			    WHEN @status = 'deleted' THEN
+			    	latest_build.job_status = 'succeeded' AND
+			    	latest_build.transition = 'delete'::workspace_transition
 				WHEN @status = 'stopped' THEN
-					latest_build.completed_at IS NOT NULL AND
-					latest_build.canceled_at IS NULL AND
-					latest_build.error IS NULL AND
+					latest_build.job_status = 'succeeded' AND
 					latest_build.transition = 'stop'::workspace_transition
+				WHEN @status = 'started' THEN
+					latest_build.job_status = 'succeeded' AND
+					latest_build.transition = 'start'::workspace_transition
 
-				WHEN @status = 'failed' THEN
-					(latest_build.canceled_at IS NOT NULL AND
-						latest_build.error IS NOT NULL) OR
-					(latest_build.completed_at IS NOT NULL AND
-						latest_build.error IS NOT NULL)
-
-				WHEN @status = 'canceling' THEN
-					latest_build.canceled_at IS NOT NULL AND
-					latest_build.completed_at IS NULL
-
-				WHEN @status = 'canceled' THEN
-					latest_build.canceled_at IS NOT NULL AND
-					latest_build.completed_at IS NOT NULL
-
-				WHEN @status = 'deleted' THEN
-					latest_build.started_at IS NOT NULL AND
-					latest_build.canceled_at IS NULL AND
-					latest_build.completed_at IS NOT NULL AND
-					latest_build.updated_at - INTERVAL '30 seconds' < NOW() AND
-					latest_build.transition = 'delete'::workspace_transition AND
-					-- If the error field is not null, the status is 'failed'
-					latest_build.error IS NULL
-
-				WHEN @status = 'deleting' THEN
-					latest_build.completed_at IS NULL AND
-					latest_build.canceled_at IS NULL AND
-					latest_build.error IS NULL AND
-					latest_build.transition = 'delete'::workspace_transition
-
+				WHEN @status != '' THEN
+				    -- By default just match the job status exactly
+			    	latest_build.job_status = @status
 				ELSE
 					true
 			END
