Apr 7, 2025 · Mar 12, 2025 · Mar 13, 2025 · Mar 13, 2025 · Mar 13, 2025 · Mar 13, 2025
diff --git a/coderd/database/dbauthz/dbauthz_test.go b/coderd/database/dbauthz/dbauthz_test.go
 check.Args(database.DeleteCustomRoleParams{
 Name: customRole.Name,
 }).Asserts(
 // fails immediately, missing organization id
 // fails immediately, missing organization id
 ).Errors(dbauthz.NotAuthorizedError{Err: xerrors.New("custom roles must belong to an organization")})
 }))
 s.Run("Blank/UpdateCustomRole", s.Subtest(func(db database.Store, check *expects) {
 codersdk.ResourceWorkspace: {codersdk.ActionRead},
 }), convertSDKPerm),
 }).Asserts(
 // fails immediately, missing organization id
 // fails immediately, missing organization id
 ).Errors(dbauthz.NotAuthorizedError{Err: xerrors.New("custom roles must belong to an organization")})
 }))
 s.Run("OrgPermissions/UpdateCustomRole", s.Subtest(func(db database.Store, check *expects) {
 codersdk.ResourceWorkspace: {codersdk.ActionRead},
 }), convertSDKPerm),
 }).Asserts(
 // fails immediately, missing organization id
 // fails immediately, missing organization id
 ).Errors(dbauthz.NotAuthorizedError{Err: xerrors.New("custom roles must belong to an organization")})
 }))
 s.Run("OrgPermissions/InsertCustomRole", s.Subtest(func(db database.Store, check *expects) {
 s.Run("GetProvisionerJobsCreatedAfter", s.Subtest(func(db database.Store, check *expects) {
 // TODO: add provisioner job resource type
 _ = dbgen.ProvisionerJob(s.T(), db, nil, database.ProvisionerJob{CreatedAt: time.Now().Add(-time.Hour)})
 check.Args(time.Now()).Asserts( /*rbac.ResourceSystem, policy.ActionRead*/)
 check.Args(time.Now()).Asserts( /*rbac.ResourceSystem, policy.ActionRead*/)
 }))
 s.Run("GetTemplateVersionsByIDs", s.Subtest(func(db database.Store, check *expects) {
 dbtestutil.DisableForeignKeysAndTriggers(s.T(), db)
 a := dbgen.ProvisionerJob(s.T(), db, nil, database.ProvisionerJob{})
 b := dbgen.ProvisionerJob(s.T(), db, nil, database.ProvisionerJob{})
 check.Args([]uuid.UUID{a.ID, b.ID}).
 Asserts( /*rbac.ResourceSystem, policy.ActionRead*/).
 Asserts( /*rbac.ResourceSystem, policy.ActionRead*/).
 Returns(slice.New(a, b))
 }))
 s.Run("InsertWorkspaceAgent", s.Subtest(func(db database.Store, check *expects) {
 OrganizationID:  j.OrganizationID,
 Types:           []database.ProvisionerType{j.Provisioner},
 ProvisionerTags: must(json.Marshal(j.Tags)),
 }).Asserts( /*rbac.ResourceSystem, policy.ActionUpdate*/)
 }).Asserts( /*rbac.ResourceSystem, policy.ActionUpdate*/)
 }))
 s.Run("UpdateProvisionerJobWithCompleteByID", s.Subtest(func(db database.Store, check *expects) {
 // TODO: we need to create a ProvisionerJob resource
 j := dbgen.ProvisionerJob(s.T(), db, nil, database.ProvisionerJob{})
 check.Args(database.UpdateProvisionerJobWithCompleteByIDParams{
 ID: j.ID,
 }).Asserts( /*rbac.ResourceSystem, policy.ActionUpdate*/)
 }).Asserts( /*rbac.ResourceSystem, policy.ActionUpdate*/)
 }))
 s.Run("UpdateProvisionerJobByID", s.Subtest(func(db database.Store, check *expects) {
 // TODO: we need to create a ProvisionerJob resource
 j := dbgen.ProvisionerJob(s.T(), db, nil, database.ProvisionerJob{})
 check.Args(database.UpdateProvisionerJobByIDParams{
 ID:        j.ID,
 UpdatedAt: time.Now(),
 }).Asserts( /*rbac.ResourceSystem, policy.ActionUpdate*/)
 }).Asserts( /*rbac.ResourceSystem, policy.ActionUpdate*/)
 }))
 s.Run("InsertProvisionerJob", s.Subtest(func(db database.Store, check *expects) {
 dbtestutil.DisableForeignKeysAndTriggers(s.T(), db)
 StorageMethod: database.ProvisionerStorageMethodFile,
 Type:          database.ProvisionerJobTypeWorkspaceBuild,
 Input:         json.RawMessage("{}"),
 }).Asserts( /*rbac.ResourceSystem, policy.ActionCreate*/)
 }).Asserts( /*rbac.ResourceSystem, policy.ActionCreate*/)
 }))
 s.Run("InsertProvisionerJobLogs", s.Subtest(func(db database.Store, check *expects) {
 // TODO: we need to create a ProvisionerJob resource
 j := dbgen.ProvisionerJob(s.T(), db, nil, database.ProvisionerJob{})
 check.Args(database.InsertProvisionerJobLogsParams{
 JobID: j.ID,
 }).Asserts( /*rbac.ResourceSystem, policy.ActionCreate*/)
 }).Asserts( /*rbac.ResourceSystem, policy.ActionCreate*/)
 }))
 s.Run("InsertProvisionerJobTimings", s.Subtest(func(db database.Store, check *expects) {
 // TODO: we need to create a ProvisionerJob resource
 j := dbgen.ProvisionerJob(s.T(), db, nil, database.ProvisionerJob{})
 check.Args(database.InsertProvisionerJobTimingsParams{
 JobID: j.ID,
 }).Asserts( /*rbac.ResourceSystem, policy.ActionCreate*/)
 }).Asserts( /*rbac.ResourceSystem, policy.ActionCreate*/)
 }))
 s.Run("UpsertProvisionerDaemon", s.Subtest(func(db database.Store, check *expects) {
 dbtestutil.DisableForeignKeysAndTriggers(s.T(), db)
diff --git a/coderd/database/querier.go b/coderd/database/querier.go
diff --git a/coderd/database/querier_test.go b/coderd/database/querier_test.go
 })

 tmpl := createTemplate(db)
 tmplV1 := createTmplVersion(db, tmpl, tmpl.ActiveVersionID, &tmplVersionOpts{
 DesiredInstances: 3,
 })
 tmplV1 := createTmplVersion(db, tmpl, tmpl.ActiveVersionID, nil)
 createWorkspaceBuild(db, tmpl, tmplV1, nil)
 createWorkspaceBuild(db, tmpl, tmplV1, nil)
 createWorkspaceBuild(db, tmpl, tmplV1, nil)
 createWorkspaceBuild(db, tmpl, tmplV1, nil)

 // Active Version
 tmplV2 := createTmplVersion(db, tmpl, tmpl.ActiveVersionID, &tmplVersionOpts{
 DesiredInstances: 2,
 })
 tmplV2 := createTmplVersion(db, tmpl, tmpl.ActiveVersionID, nil)
 createWorkspaceBuild(db, tmpl, tmplV2, nil)
 createWorkspaceBuild(db, tmpl, tmplV2, nil)

 createWorkspaceBuild(db, tmpl1, tmpl1V1, nil)

 tmpl2 := createTemplate(db)
 tmpl2V1 := createTmplVersion(db, tmpl2, tmpl2.ActiveVersionID, &tmplVersionOpts{
 DesiredInstances: 2,
 })
 tmpl2V1 := createTmplVersion(db, tmpl2, tmpl2.ActiveVersionID, nil)
 createWorkspaceBuild(db, tmpl2, tmpl2V1, nil)
 createWorkspaceBuild(db, tmpl2, tmpl2V1, nil)

 tmpl3 := createTemplate(db)
 tmpl3V1 := createTmplVersion(db, tmpl3, uuid.New(), nil)
 createWorkspaceBuild(db, tmpl3, tmpl3V1, nil)

 tmpl3V2 := createTmplVersion(db, tmpl3, tmpl3.ActiveVersionID, &tmplVersionOpts{
 DesiredInstances: 3,
 })
 tmpl3V2 := createTmplVersion(db, tmpl3, tmpl3.ActiveVersionID, nil)
 createWorkspaceBuild(db, tmpl3, tmpl3V2, nil)
 createWorkspaceBuild(db, tmpl3, tmpl3V2, nil)
 createWorkspaceBuild(db, tmpl3, tmpl3V2, nil)

 tmpl1 := createTemplate(db)
 tmpl1V1 := createTmplVersion(db, tmpl1, tmpl1.ActiveVersionID, &tmplVersionOpts{
 DesiredInstances:5,
 DesiredInstances:3,
 })
 createWorkspaceBuild(db, tmpl1, tmpl1V1, &workspaceBuildOpts{
 successfulJob: false,
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/prebuilds.sql b/coderd/database/queries/prebuilds.sql
 WHERE pj.job_status IN ('pending'::provisioner_job_status, 'running'::provisioner_job_status)
 GROUP BY t.id, wpb.template_version_id, wpb.transition;

 -- name: GetPresetsBackoff :many
 -- GetPresetsBackoff groups workspace builds by template version ID.
 -- For each group, the query checks up to N of the most recent jobs that occurred within the
 -- lookback period, where N equals the number of desired instances for the corresponding preset.
 -- For each group, the query checks the last N jobs, where N equals the number of desired instances for the corresponding preset.
 -- If at least one of the last N jobs has failed, we should backoff on the corresponding template version ID.
 -- Query returns a list of template version IDs for which we should backoff.
 -- Only active template versions with configured presets are considered.
 --
 -- NOTE:
 -- We only consider jobs that occurred within the lookback period; any failures that happened before this period are ignored.
 -- We also return the number of failed workspace builds, which is used downstream to determine the backoff duration.
 -- name: GetPresetsBackoff :many
 -- We back off on the template version ID if at least one of the N latest workspace builds has failed.
 -- However, we also return the number of failed workspace builds that occurred during the lookback period.
 --
 -- In other words:
 -- - To **decide whether to back off**, we look at the N most recent builds (regardless of when they happened).
 -- - To **calculate the number of failed builds**, we consider all builds within the defined lookback period.
 --
 -- The number of failed builds is used downstream to determine the backoff duration.
 WITH filtered_builds AS (
 -- Only select builds which are for prebuild creations
 SELECT wlb.*, tvp.id AS preset_id, pj.job_status, tvp.desired_instances
             INNER JOIN template_versions tv ON wlb.template_version_id = tv.id
             INNER JOIN templates t ON tv.template_id = t.id AND t.active_version_id = tv.id
 WHERE tvp.desired_instances IS NOT NULL -- Consider only presets that have a prebuild configuration.
  AND wlb.transition = 'start'::workspace_transition
  AND wlb.transition = 'start'::workspace_transition
 ),
 time_sorted_builds AS (
    -- Group builds by template version, then sort each group by created_at.
 SELECT fb.*,
    ROW_NUMBER() OVER (PARTITION BY fb.template_version_preset_id ORDER BY fb.created_at DESC) as rn
 FROM filtered_builds fb
 ),
 failed_count AS (
    -- Count failed builds per template version/preset in the given period
 SELECT preset_id, COUNT(*) AS num_failed
 FROM filtered_builds
 WHERE job_status = 'failed'::provisioner_job_status
 AND created_at >= @lookback::timestamptz
 GROUP BY preset_id
 )
 SELECT tsb.template_version_id,
   tsb.preset_id,
 COUNT(*)::int AS num_failed, -- Count failed builds per template version/preset in the given period
 COALESCE(fc.num_failed, 0)::intAS num_failed,
   MAX(tsb.created_at::timestamptz) AS last_build_at
 FROM time_sorted_builds tsb
 LEFT JOIN failed_count fc ON fc.preset_id = tsb.preset_id
 WHERE tsb.rn <= tsb.desired_instances -- Fetch the last N builds, where N is the number of desired instances; if any fail, we backoff
  AND tsb.job_status = 'failed'::provisioner_job_status
  AND created_at >= @lookback::timestamptz
 GROUP BY tsb.template_version_id, tsb.preset_id;
 GROUP BY tsb.template_version_id, tsb.preset_id, fc.num_failed;

 -- name: ClaimPrebuild :one
 UPDATE workspaces w
Original file line number	Diff line number	Diff line change
Expand Up		@@ -1692,7 +1692,7 @@ func (s *MethodTestSuite) TestUser() {
		check.Args(database.DeleteCustomRoleParams{
		Name: customRole.Name,
		}).Asserts(
		// fails immediately, missing organization id
		// fails immediately, missing organization id
		).Errors(dbauthz.NotAuthorizedError{Err: xerrors.New("custom roles must belong to an organization")})
		}))
		s.Run("Blank/UpdateCustomRole", s.Subtest(func(db database.Store, check *expects) {
Expand DownExpand Up		@@ -1723,7 +1723,7 @@ func (s *MethodTestSuite) TestUser() {
		codersdk.ResourceWorkspace: {codersdk.ActionRead},
		}), convertSDKPerm),
		}).Asserts(
		// fails immediately, missing organization id
		// fails immediately, missing organization id
		).Errors(dbauthz.NotAuthorizedError{Err: xerrors.New("custom roles must belong to an organization")})
		}))
		s.Run("OrgPermissions/UpdateCustomRole", s.Subtest(func(db database.Store, check *expects) {
Expand DownExpand Up		@@ -1776,7 +1776,7 @@ func (s *MethodTestSuite) TestUser() {
		codersdk.ResourceWorkspace: {codersdk.ActionRead},
		}), convertSDKPerm),
		}).Asserts(
		// fails immediately, missing organization id
		// fails immediately, missing organization id
		).Errors(dbauthz.NotAuthorizedError{Err: xerrors.New("custom roles must belong to an organization")})
		}))
		s.Run("OrgPermissions/InsertCustomRole", s.Subtest(func(db database.Store, check *expects) {
Expand DownExpand Up		@@ -3757,7 +3757,7 @@ func (s *MethodTestSuite) TestSystemFunctions() {
		s.Run("GetProvisionerJobsCreatedAfter", s.Subtest(func(db database.Store, check *expects) {
		// TODO: add provisioner job resource type
		_ = dbgen.ProvisionerJob(s.T(), db, nil, database.ProvisionerJob{CreatedAt: time.Now().Add(-time.Hour)})
		check.Args(time.Now()).Asserts( /rbac.ResourceSystem, policy.ActionRead/)
		check.Args(time.Now()).Asserts( /rbac.ResourceSystem, policy.ActionRead/)
		}))
		s.Run("GetTemplateVersionsByIDs", s.Subtest(func(db database.Store, check *expects) {
		dbtestutil.DisableForeignKeysAndTriggers(s.T(), db)
Expand DownExpand Up		@@ -3934,7 +3934,7 @@ func (s *MethodTestSuite) TestSystemFunctions() {
		a := dbgen.ProvisionerJob(s.T(), db, nil, database.ProvisionerJob{})
		b := dbgen.ProvisionerJob(s.T(), db, nil, database.ProvisionerJob{})
		check.Args([]uuid.UUID{a.ID, b.ID}).
		Asserts( /rbac.ResourceSystem, policy.ActionRead/).
		Asserts( /rbac.ResourceSystem, policy.ActionRead/).
		Returns(slice.New(a, b))
		}))
		s.Run("InsertWorkspaceAgent", s.Subtest(func(db database.Store, check *expects) {
Expand DownExpand Up		@@ -3979,22 +3979,22 @@ func (s *MethodTestSuite) TestSystemFunctions() {
		OrganizationID: j.OrganizationID,
		Types: []database.ProvisionerType{j.Provisioner},
		ProvisionerTags: must(json.Marshal(j.Tags)),
		}).Asserts( /rbac.ResourceSystem, policy.ActionUpdate/)
		}).Asserts( /rbac.ResourceSystem, policy.ActionUpdate/)
		}))
		s.Run("UpdateProvisionerJobWithCompleteByID", s.Subtest(func(db database.Store, check *expects) {
		// TODO: we need to create a ProvisionerJob resource
		j := dbgen.ProvisionerJob(s.T(), db, nil, database.ProvisionerJob{})
		check.Args(database.UpdateProvisionerJobWithCompleteByIDParams{
		ID: j.ID,
		}).Asserts( /rbac.ResourceSystem, policy.ActionUpdate/)
		}).Asserts( /rbac.ResourceSystem, policy.ActionUpdate/)
		}))
		s.Run("UpdateProvisionerJobByID", s.Subtest(func(db database.Store, check *expects) {
		// TODO: we need to create a ProvisionerJob resource
		j := dbgen.ProvisionerJob(s.T(), db, nil, database.ProvisionerJob{})
		check.Args(database.UpdateProvisionerJobByIDParams{
		ID: j.ID,
		UpdatedAt: time.Now(),
		}).Asserts( /rbac.ResourceSystem, policy.ActionUpdate/)
		}).Asserts( /rbac.ResourceSystem, policy.ActionUpdate/)
		}))
		s.Run("InsertProvisionerJob", s.Subtest(func(db database.Store, check *expects) {
		dbtestutil.DisableForeignKeysAndTriggers(s.T(), db)
Expand All		@@ -4005,21 +4005,21 @@ func (s *MethodTestSuite) TestSystemFunctions() {
		StorageMethod: database.ProvisionerStorageMethodFile,
		Type: database.ProvisionerJobTypeWorkspaceBuild,
		Input: json.RawMessage("{}"),
		}).Asserts( /rbac.ResourceSystem, policy.ActionCreate/)
		}).Asserts( /rbac.ResourceSystem, policy.ActionCreate/)
		}))
		s.Run("InsertProvisionerJobLogs", s.Subtest(func(db database.Store, check *expects) {
		// TODO: we need to create a ProvisionerJob resource
		j := dbgen.ProvisionerJob(s.T(), db, nil, database.ProvisionerJob{})
		check.Args(database.InsertProvisionerJobLogsParams{
		JobID: j.ID,
		}).Asserts( /rbac.ResourceSystem, policy.ActionCreate/)
		}).Asserts( /rbac.ResourceSystem, policy.ActionCreate/)
		}))
		s.Run("InsertProvisionerJobTimings", s.Subtest(func(db database.Store, check *expects) {
		// TODO: we need to create a ProvisionerJob resource
		j := dbgen.ProvisionerJob(s.T(), db, nil, database.ProvisionerJob{})
		check.Args(database.InsertProvisionerJobTimingsParams{
		JobID: j.ID,
		}).Asserts( /rbac.ResourceSystem, policy.ActionCreate/)
		}).Asserts( /rbac.ResourceSystem, policy.ActionCreate/)
		}))
		s.Run("UpsertProvisionerDaemon", s.Subtest(func(db database.Store, check *expects) {
		dbtestutil.DisableForeignKeysAndTriggers(s.T(), db)
Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -3631,9 +3631,7 @@ func TestGetPresetsBackoff(t *testing.T) {
		})

		tmpl := createTemplate(db)
		tmplV1 := createTmplVersion(db, tmpl, tmpl.ActiveVersionID, &tmplVersionOpts{
		DesiredInstances: 3,
		})
		tmplV1 := createTmplVersion(db, tmpl, tmpl.ActiveVersionID, nil)
		createWorkspaceBuild(db, tmpl, tmplV1, nil)
		createWorkspaceBuild(db, tmpl, tmplV1, nil)
		createWorkspaceBuild(db, tmpl, tmplV1, nil)
Expand DownExpand Up		@@ -3665,9 +3663,7 @@ func TestGetPresetsBackoff(t *testing.T) {
		createWorkspaceBuild(db, tmpl, tmplV1, nil)

		// Active Version
		tmplV2 := createTmplVersion(db, tmpl, tmpl.ActiveVersionID, &tmplVersionOpts{
		DesiredInstances: 2,
		})
		tmplV2 := createTmplVersion(db, tmpl, tmpl.ActiveVersionID, nil)
		createWorkspaceBuild(db, tmpl, tmplV2, nil)
		createWorkspaceBuild(db, tmpl, tmplV2, nil)

Expand DownExpand Up		@@ -3736,19 +3732,15 @@ func TestGetPresetsBackoff(t *testing.T) {
		createWorkspaceBuild(db, tmpl1, tmpl1V1, nil)

		tmpl2 := createTemplate(db)
		tmpl2V1 := createTmplVersion(db, tmpl2, tmpl2.ActiveVersionID, &tmplVersionOpts{
		DesiredInstances: 2,
		})
		tmpl2V1 := createTmplVersion(db, tmpl2, tmpl2.ActiveVersionID, nil)
		createWorkspaceBuild(db, tmpl2, tmpl2V1, nil)
		createWorkspaceBuild(db, tmpl2, tmpl2V1, nil)

		tmpl3 := createTemplate(db)
		tmpl3V1 := createTmplVersion(db, tmpl3, uuid.New(), nil)
		createWorkspaceBuild(db, tmpl3, tmpl3V1, nil)

		tmpl3V2 := createTmplVersion(db, tmpl3, tmpl3.ActiveVersionID, &tmplVersionOpts{
		DesiredInstances: 3,
		})
		tmpl3V2 := createTmplVersion(db, tmpl3, tmpl3.ActiveVersionID, nil)
		createWorkspaceBuild(db, tmpl3, tmpl3V2, nil)
		createWorkspaceBuild(db, tmpl3, tmpl3V2, nil)
		createWorkspaceBuild(db, tmpl3, tmpl3V2, nil)
Expand DownExpand Up		@@ -3950,7 +3942,7 @@ func TestGetPresetsBackoff(t *testing.T) {

		tmpl1 := createTemplate(db)
		tmpl1V1 := createTmplVersion(db, tmpl1, tmpl1.ActiveVersionID, &tmplVersionOpts{
		DesiredInstances:5,
		DesiredInstances:3,
		})
		createWorkspaceBuild(db, tmpl1, tmpl1V1, &workspaceBuildOpts{
		successfulJob: false,
Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -43,17 +43,22 @@ FROM workspace_latest_builds wlb
		WHERE pj.job_status IN ('pending'::provisioner_job_status, 'running'::provisioner_job_status)
		GROUP BY t.id, wpb.template_version_id, wpb.transition;

		-- name: GetPresetsBackoff :many
		-- GetPresetsBackoff groups workspace builds by template version ID.
		-- For each group, the query checks up to N of the most recent jobs that occurred within the
		-- lookback period, where N equals the number of desired instances for the corresponding preset.
		-- For each group, the query checks the last N jobs, where N equals the number of desired instances for the corresponding preset.
		-- If at least one of the last N jobs has failed, we should backoff on the corresponding template version ID.
		-- Query returns a list of template version IDs for which we should backoff.
		-- Only active template versions with configured presets are considered.
		--
		-- NOTE:
		-- We only consider jobs that occurred within the lookback period; any failures that happened before this period are ignored.
		-- We also return the number of failed workspace builds, which is used downstream to determine the backoff duration.
		-- name: GetPresetsBackoff :many
		-- We back off on the template version ID if at least one of the N latest workspace builds has failed.
		-- However, we also return the number of failed workspace builds that occurred during the lookback period.
		--
		-- In other words:
		-- - To decide whether to back off, we look at the N most recent builds (regardless of when they happened).
		-- - To calculate the number of failed builds, we consider all builds within the defined lookback period.
		--
		-- The number of failed builds is used downstream to determine the backoff duration.
		WITH filtered_builds AS (
		-- Only select builds which are for prebuild creations
		SELECT wlb.*, tvp.id AS preset_id, pj.job_status, tvp.desired_instances
Expand All		@@ -63,23 +68,31 @@ WITH filtered_builds AS (
		INNER JOIN template_versions tv ON wlb.template_version_id = tv.id
		INNER JOIN templates t ON tv.template_id = t.id AND t.active_version_id = tv.id
		WHERE tvp.desired_instances IS NOT NULL -- Consider only presets that have a prebuild configuration.
		AND wlb.transition = 'start'::workspace_transition
		AND wlb.transition = 'start'::workspace_transition
		),
		time_sorted_builds AS (
		-- Group builds by template version, then sort each group by created_at.
		SELECT fb.*,
		ROW_NUMBER() OVER (PARTITION BY fb.template_version_preset_id ORDER BY fb.created_at DESC) as rn
		FROM filtered_builds fb
		),
		failed_count AS (
		-- Count failed builds per template version/preset in the given period
		SELECT preset_id, COUNT(*) AS num_failed
		FROM filtered_builds
		WHERE job_status = 'failed'::provisioner_job_status
		AND created_at >= @lookback::timestamptz
		GROUP BY preset_id
		)
		SELECT tsb.template_version_id,
		tsb.preset_id,
		COUNT(*)::int AS num_failed, -- Count failed builds per template version/preset in the given period
		COALESCE(fc.num_failed, 0)::intAS num_failed,
		MAX(tsb.created_at::timestamptz) AS last_build_at
		FROM time_sorted_builds tsb
		LEFT JOIN failed_count fc ON fc.preset_id = tsb.preset_id
		WHERE tsb.rn <= tsb.desired_instances -- Fetch the last N builds, where N is the number of desired instances; if any fail, we backoff
		AND tsb.job_status = 'failed'::provisioner_job_status
		AND created_at >= @lookback::timestamptz
		GROUP BY tsb.template_version_id, tsb.preset_id;
		GROUP BY tsb.template_version_id, tsb.preset_id, fc.num_failed;

		-- name: ClaimPrebuild :one
		UPDATE workspaces w
Expand Down