coder · deansheather · Aug 7, 2025 · Aug 6, 2025 · Aug 6, 2025 · Aug 7, 2025
diff --git a/CODEOWNERS b/CODEOWNERS
@@ -29,3 +29,8 @@ site/src/api/countriesGenerated.ts
 site/src/api/rbacresourcesGenerated.ts
 site/src/api/typesGenerated.ts
 site/CLAUDE.md
+
+# The blood and guts of the autostop algorithm, which is quite complex and
+# requires elite ball knowledge of most of the scheduling code to make changes
+# without inadvertently affecting other parts of the codebase.
+coderd/schedule/autostop.go @deansheather @DanielleMaywood
diff --git a/coderd/database/check_constraint.go b/coderd/database/check_constraint.go
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/errors.go b/coderd/database/errors.go
@@ -59,6 +59,28 @@ func IsForeignKeyViolation(err error, foreignKeyConstraints ...ForeignKeyConstra
 	return false
 }
 
+// IsCheckViolation checks if the error is due to a check violation. If one or
+// more specific check constraints are given as arguments, the error must be
+// caused by one of them. If no constraints are given, this function returns
+// true for any check violation.
+func IsCheckViolation(err error, checkConstraints ...CheckConstraint) bool {
+	var pqErr *pq.Error
+	if errors.As(err, &pqErr) {
+		if pqErr.Code.Name() == "check_violation" {
+			if len(checkConstraints) == 0 {
+				return true
+			}
+			for _, cc := range checkConstraints {
+				if pqErr.Constraint == string(cc) {
+					return true
+				}
+			}
+		}
+	}
+
+	return false
+}
+
 // IsQueryCanceledError checks if the error is due to a query being canceled.
 func IsQueryCanceledError(err error) bool {
 	var pqErr *pq.Error

diff --git a/coderd/database/migrations/000356_enforce_deadline_below_max_deadline.down.sql b/coderd/database/migrations/000356_enforce_deadline_below_max_deadline.down.sql
@@ -0,0 +1,2 @@
+ALTER TABLE workspace_builds
+    DROP CONSTRAINT workspace_builds_deadline_below_max_deadline;
diff --git a/coderd/database/migrations/000356_enforce_deadline_below_max_deadline.up.sql b/coderd/database/migrations/000356_enforce_deadline_below_max_deadline.up.sql
@@ -0,0 +1,20 @@
+-- New constraint: (deadline IS NOT zero AND deadline <= max_deadline) UNLESS max_deadline is zero.
+-- Unfortunately, "zero" here means `time.Time{}`...
+
+-- Update previous builds that would fail this new constraint. This matches the
+-- intended behaviour of the autostop algorithm.
+UPDATE
+    workspace_builds
+SET
+    deadline = max_deadline
+WHERE
+    deadline > max_deadline
+    AND max_deadline != '0001-01-01 00:00:00+00';
+
+-- Add the new constraint.
+ALTER TABLE workspace_builds
+    ADD CONSTRAINT workspace_builds_deadline_below_max_deadline
+        CHECK (
+            (deadline != '0001-01-01 00:00:00+00'::timestamptz AND deadline <= max_deadline)
+            OR max_deadline = '0001-01-01 00:00:00+00'::timestamptz
+        );
diff --git a/coderd/database/querier_test.go b/coderd/database/querier_test.go
@@ -6003,3 +6003,102 @@ func TestGetRunningPrebuiltWorkspaces(t *testing.T) {
 	require.Len(t, runningPrebuilds, 1, "expected only one running prebuilt workspace")
 	require.Equal(t, runningPrebuild.ID, runningPrebuilds[0].ID, "expected the running prebuilt workspace to be returned")
 }
+
+func TestWorkspaceBuildDeadlineConstraint(t *testing.T) {
+	t.Parallel()
+
+	ctx := testutil.Context(t, testutil.WaitLong)
+
+	db, _ := dbtestutil.NewDB(t)
+	org := dbgen.Organization(t, db, database.Organization{})
+	user := dbgen.User(t, db, database.User{})
+	template := dbgen.Template(t, db, database.Template{
+		CreatedBy:      user.ID,
+		OrganizationID: org.ID,
+	})
+	templateVersion := dbgen.TemplateVersion(t, db, database.TemplateVersion{
+		TemplateID:     uuid.NullUUID{UUID: template.ID, Valid: true},
+		OrganizationID: org.ID,
+		CreatedBy:      user.ID,
+	})
+	workspace := dbgen.Workspace(t, db, database.WorkspaceTable{
+		OwnerID:    user.ID,
+		TemplateID: template.ID,
+		Name:       "test-workspace",
+		Deleted:    false,
+	})
+	job := dbgen.ProvisionerJob(t, db, nil, database.ProvisionerJob{
+		OrganizationID: org.ID,
+		InitiatorID:    database.PrebuildsSystemUserID,
+		Provisioner:    database.ProvisionerTypeEcho,
+		Type:           database.ProvisionerJobTypeWorkspaceBuild,
+		StartedAt:      sql.NullTime{Time: time.Now().Add(-time.Minute), Valid: true},
+		CompletedAt:    sql.NullTime{Time: time.Now(), Valid: true},
+	})
+	workspaceBuild := dbgen.WorkspaceBuild(t, db, database.WorkspaceBuild{
+		WorkspaceID:       workspace.ID,
+		TemplateVersionID: templateVersion.ID,
+		JobID:             job.ID,
+		BuildNumber:       1,
+	})
+
+	cases := []struct {
+		name        string
+		deadline    time.Time
+		maxDeadline time.Time
+		expectOK    bool
+	}{
+		{
+			name:        "no deadline or max_deadline",
+			deadline:    time.Time{},
+			maxDeadline: time.Time{},
+			expectOK:    true,
+		},
+		{
+			name:        "deadline set when max_deadline is not set",
+			deadline:    time.Now().Add(time.Hour),
+			maxDeadline: time.Time{},
+			expectOK:    true,
+		},
+		{
+			name:        "deadline before max_deadline",
+			deadline:    time.Now().Add(-time.Hour),
+			maxDeadline: time.Now().Add(time.Hour),
+			expectOK:    true,
+		},
+		{
+			name:        "deadline is max_deadline",
+			deadline:    time.Now().Add(time.Hour),
+			maxDeadline: time.Now().Add(time.Hour),
+			expectOK:    true,
+		},
+
+		{
+			name:        "deadline after max_deadline",
+			deadline:    time.Now().Add(time.Hour),
+			maxDeadline: time.Now().Add(-time.Hour),
+			expectOK:    false,
+		},
+		{
+			name:        "deadline is not set when max_deadline is set",
+			deadline:    time.Time{},
+			maxDeadline: time.Now().Add(time.Hour),
+			expectOK:    false,
+		},
+	}
+
+	for _, c := range cases {
+		err := db.UpdateWorkspaceBuildDeadlineByID(ctx, database.UpdateWorkspaceBuildDeadlineByIDParams{
+			ID:          workspaceBuild.ID,
+			Deadline:    c.deadline,
+			MaxDeadline: c.maxDeadline,
+			UpdatedAt:   time.Now(),
+		})
+		if c.expectOK {
+			require.NoError(t, err)
+		} else {
+			require.Error(t, err)
+			require.True(t, database.IsCheckViolation(err, database.CheckWorkspaceBuildsDeadlineBelowMaxDeadline))
+		}
+	}
+}
diff --git a/coderd/provisionerdserver/provisionerdserver.go b/coderd/provisionerdserver/provisionerdserver.go
@@ -1866,8 +1866,9 @@ func (s *server) completeWorkspaceBuildJob(ctx context.Context, job database.Pro
 			Database:                    db,
 			TemplateScheduleStore:       templateScheduleStore,
 			UserQuietHoursScheduleStore: *s.UserQuietHoursScheduleStore.Load(),
-			Now:                         now,
-			Workspace:                   workspace.WorkspaceTable(),
+			// `now` is used below to set the build completion time.
+			WorkspaceBuildCompletedAt: now,
+			Workspace:                 workspace.WorkspaceTable(),
 			// Allowed to be the empty string.
 			WorkspaceAutostart: workspace.AutostartSchedule.String,
 		})
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,2 @@
		ALTER TABLE workspace_builds
		DROP CONSTRAINT workspace_builds_deadline_below_max_deadline;