coder · sreya · Aug 22, 2023 · Aug 2, 2023 · Aug 14, 2023 · Aug 14, 2023
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -2385,6 +2385,14 @@ func (q *querier) UpdateTemplateVersionGitAuthProvidersByJobID(ctx context.Conte
 	return q.db.UpdateTemplateVersionGitAuthProvidersByJobID(ctx, arg)
 }
 
+func (q *querier) UpdateTemplateWorkspacesLastUsedAt(ctx context.Context, arg database.UpdateTemplateWorkspacesLastUsedAtParams) error {
+	fetch := func(ctx context.Context, arg database.UpdateTemplateWorkspacesLastUsedAtParams) (database.Template, error) {
+		return q.db.GetTemplateByID(ctx, arg.TemplateID)
+	}
+
+	return fetchAndExec(q.log, q.auth, rbac.ActionUpdate, fetch, q.db.UpdateTemplateWorkspacesLastUsedAt)(ctx, arg)
+}
+
 // UpdateUserDeletedByID
 // Deprecated: Delete this function in favor of 'SoftDeleteUserByID'. Deletes are
 // irreversible.
@@ -2663,12 +2671,12 @@ func (q *querier) UpdateWorkspaceTTL(ctx context.Context, arg database.UpdateWor
 	return update(q.log, q.auth, fetch, q.db.UpdateWorkspaceTTL)(ctx, arg)
 }
 
-func (q *querier) UpdateWorkspacesDeletingAtByTemplateID(ctx context.Context, arg database.UpdateWorkspacesDeletingAtByTemplateIDParams) error {
-	fetch := func(ctx context.Context, arg database.UpdateWorkspacesDeletingAtByTemplateIDParams) (database.Template, error) {
+func (q *querier) UpdateWorkspacesLockedDeletingAtByTemplateID(ctx context.Context, arg database.UpdateWorkspacesLockedDeletingAtByTemplateIDParams) error {
+	fetch := func(ctx context.Context, arg database.UpdateWorkspacesLockedDeletingAtByTemplateIDParams) (database.Template, error) {
 		return q.db.GetTemplateByID(ctx, arg.TemplateID)
 	}
 
-	return fetchAndExec(q.log, q.auth, rbac.ActionUpdate, fetch, q.db.UpdateWorkspacesDeletingAtByTemplateID)(ctx, arg)
+	return fetchAndExec(q.log, q.auth, rbac.ActionUpdate, fetch, q.db.UpdateWorkspacesLockedDeletingAtByTemplateID)(ctx, arg)
 }
 
 func (q *querier) UpsertAppSecurityKey(ctx context.Context, data string) error {

diff --git a/coderd/database/dbfake/dbfake.go b/coderd/database/dbfake/dbfake.go
@@ -5199,6 +5199,26 @@ func (q *FakeQuerier) UpdateTemplateVersionGitAuthProvidersByJobID(_ context.Con
 	return sql.ErrNoRows
 }
 
+func (q *FakeQuerier) UpdateTemplateWorkspacesLastUsedAt(_ context.Context, arg database.UpdateTemplateWorkspacesLastUsedAtParams) error {
+	err := validateDatabaseType(arg)
+	if err != nil {
+		return err
+	}
+
+	q.mutex.Lock()
+	defer q.mutex.Unlock()
+
+	for i, ws := range q.workspaces {
+		if ws.TemplateID != arg.TemplateID {
+			continue
+		}
+		ws.LastUsedAt = arg.LastUsedAt
+		q.workspaces[i] = ws
+	}
+
+	return nil
+}
+
 func (q *FakeQuerier) UpdateUserDeletedByID(_ context.Context, params database.UpdateUserDeletedByIDParams) error {
 	if err := validateDatabaseType(params); err != nil {
 		return err
@@ -5796,7 +5816,7 @@ func (q *FakeQuerier) UpdateWorkspaceTTL(_ context.Context, arg database.UpdateW
 	return sql.ErrNoRows
 }
 
-func (q *FakeQuerier) UpdateWorkspacesDeletingAtByTemplateID(_ context.Context, arg database.UpdateWorkspacesDeletingAtByTemplateIDParams) error {
+func (q *FakeQuerier) UpdateWorkspacesLockedDeletingAtByTemplateID(_ context.Context, arg database.UpdateWorkspacesLockedDeletingAtByTemplateIDParams) error {
 	q.mutex.Lock()
 	defer q.mutex.Unlock()
 
@@ -5806,9 +5826,21 @@ func (q *FakeQuerier) UpdateWorkspacesDeletingAtByTemplateID(_ context.Context,
 	}
 
 	for i, ws := range q.workspaces {
+		if ws.TemplateID != arg.TemplateID {
+			continue
+		}
+
 		if ws.LockedAt.Time.IsZero() {
 			continue
 		}
+
+		if !arg.LockedAt.IsZero() {
+			ws.LockedAt = sql.NullTime{
+				Valid: true,
+				Time:  arg.LockedAt,
+			}
+		}
+
 		deletingAt := sql.NullTime{
 			Valid: arg.LockedTtlMs > 0,
 		}

diff --git a/coderd/database/dbmetrics/dbmetrics.go b/coderd/database/dbmetrics/dbmetrics.go
diff --git a/coderd/database/dbmock/dbmock.go b/coderd/database/dbmock/dbmock.go
diff --git a/coderd/database/querier.go b/coderd/database/querier.go
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/workspaces.sql b/coderd/database/queries/workspaces.sql
@@ -501,12 +501,23 @@ AND
 	workspaces.id = $1
 RETURNING workspaces.*;
 
--- name: UpdateWorkspacesDeletingAtByTemplateID :exec
-UPDATE
-	workspaces
+-- name: UpdateWorkspacesLockedDeletingAtByTemplateID :exec
+UPDATE workspaces
 SET
-	deleting_at = CASE WHEN @locked_ttl_ms::bigint = 0 THEN NULL ELSE locked_at + interval '1 milliseconds' * @locked_ttl_ms::bigint END
+    deleting_at = CASE
+        WHEN @locked_ttl_ms::bigint = 0 THEN NULL
+        WHEN @locked_at::timestamptz > '0001-01-01 00:00:00+00'::timestamptz THEN  (@locked_at::timestamptz) + interval '1 milliseconds' * @locked_ttl_ms::bigint
+        ELSE locked_at + interval '1 milliseconds' * @locked_ttl_ms::bigint
+    END,
+    locked_at = CASE WHEN @locked_at::timestamptz > '0001-01-01 00:00:00+00'::timestamptz THEN @locked_at::timestamptz ELSE locked_at END
 WHERE
-	template_id = @template_id
+    template_id = @template_id
 AND
-	locked_at IS NOT NULL;
+    locked_at IS NOT NULL;
+
+-- name: UpdateTemplateWorkspacesLastUsedAt :exec
+UPDATE workspaces
+SET
+	last_used_at = @last_used_at::timestamptz
+WHERE
+	template_id = @template_id;
diff --git a/coderd/schedule/template.go b/coderd/schedule/template.go
@@ -105,6 +105,18 @@ type TemplateScheduleOptions struct {
 	// LockedTTL dictates the duration after which locked workspaces will be
 	// permanently deleted.
 	LockedTTL time.Duration `json:"locked_ttl"`
+	// UpdateWorkspaceLastUsedAt updates the template's workspaces'
+	// last_used_at field. This is useful for preventing updates to the
+	// templates inactivity_ttl immediately triggering a lock action against
+	// workspaces whose last_used_at field violates the new template
+	// inactivity_ttl threshold.
+	UpdateWorkspaceLastUsedAt bool `json:"update_workspace_last_used_at"`
+	// UpdateWorkspaceLockedAt updates the template's workspaces'
+	// locked_at field. This is useful for preventing updates to the
+	// templates locked_ttl immediately triggering a delete action against
+	// workspaces whose locked_at field violates the new template locked_ttl
+	// threshold.
+	UpdateWorkspaceLockedAt bool `json:"update_workspace_locked_at"`
 }
 
 // TemplateScheduleStore provides an interface for retrieving template

diff --git a/coderd/templates.go b/coderd/templates.go
@@ -622,9 +622,11 @@ func (api *API) patchTemplateMeta(rw http.ResponseWriter, r *http.Request) {
 					DaysOfWeek: restartRequirementDaysOfWeekParsed,
 					Weeks:      req.RestartRequirement.Weeks,
 				},
-				FailureTTL:    failureTTL,
-				InactivityTTL: inactivityTTL,
-				LockedTTL:     lockedTTL,
+				FailureTTL:                failureTTL,
+				InactivityTTL:             inactivityTTL,
+				LockedTTL:                 lockedTTL,
+				UpdateWorkspaceLastUsedAt: req.UpdateWorkspaceLastUsedAt,
+				UpdateWorkspaceLockedAt:   req.UpdateWorkspaceLockedAt,
 			})
 			if err != nil {
 				return xerrors.Errorf("set template schedule options: %w", err)

diff --git a/codersdk/templates.go b/codersdk/templates.go
@@ -192,6 +192,15 @@ type UpdateTemplateMeta struct {
 	FailureTTLMillis             int64                       `json:"failure_ttl_ms,omitempty"`
 	InactivityTTLMillis          int64                       `json:"inactivity_ttl_ms,omitempty"`
 	LockedTTLMillis              int64                       `json:"locked_ttl_ms,omitempty"`
+	// UpdateWorkspaceLastUsedAt updates the last_used_at field of workspaces
+	// spawned from the template. This is useful for preventing workspaces being
+	// immediately locked when updating the inactivity_ttl field to a new, shorter
+	// value.
+	UpdateWorkspaceLastUsedAt bool `json:"update_workspace_last_used_at"`
+	// UpdateWorkspaceLockedAt updates the locked_at field of workspaces spawned
+	// from the template. This is useful for preventing locked workspaces being immediately
+	// deleted when updating the locked_ttl field to a new, shorter value.
+	UpdateWorkspaceLockedAt bool `json:"update_workspace_locked_at"`
 }
 
 type TemplateExample struct {

diff --git a/enterprise/coderd/schedule/template.go b/enterprise/coderd/schedule/template.go
@@ -113,11 +113,11 @@ func (s *EnterpriseTemplateScheduleStore) Set(ctx context.Context, db database.S
 	}
 
 	var template database.Template
-	err = db.InTx(func(db database.Store) error {
+	err = db.InTx(func(tx database.Store) error {
 		ctx, span := tracing.StartSpanWithName(ctx, "(*schedule.EnterpriseTemplateScheduleStore).Set()-InTx()")
 		defer span.End()
 
-		err := db.UpdateTemplateScheduleByID(ctx, database.UpdateTemplateScheduleByIDParams{
+		err := tx.UpdateTemplateScheduleByID(ctx, database.UpdateTemplateScheduleByIDParams{
 			ID:                           tpl.ID,
 			UpdatedAt:                    s.now(),
 			AllowUserAutostart:           opts.UserAutostartEnabled,
@@ -134,27 +134,44 @@ func (s *EnterpriseTemplateScheduleStore) Set(ctx context.Context, db database.S
 			return xerrors.Errorf("update template schedule: %w", err)
 		}
 
+		var lockedAt time.Time
+		if opts.UpdateWorkspaceLockedAt {
+			lockedAt = database.Now()
+		}
+
 		// If we updated the locked_ttl we need to update all the workspaces deleting_at
 		// to ensure workspaces are being cleaned up correctly. Similarly if we are
 		// disabling it (by passing 0), then we want to delete nullify the deleting_at
 		// fields of all the template workspaces.
-		err = db.UpdateWorkspacesDeletingAtByTemplateID(ctx, database.UpdateWorkspacesDeletingAtByTemplateIDParams{
+		err = tx.UpdateWorkspacesLockedDeletingAtByTemplateID(ctx, database.UpdateWorkspacesLockedDeletingAtByTemplateIDParams{
 			TemplateID:  tpl.ID,
 			LockedTtlMs: opts.LockedTTL.Milliseconds(),
+			LockedAt:    lockedAt,
 		})
 		if err != nil {
 			return xerrors.Errorf("update deleting_at of all workspaces for new locked_ttl %q: %w", opts.LockedTTL, err)
 		}
 
-		template, err = db.GetTemplateByID(ctx, tpl.ID)
+		if opts.UpdateWorkspaceLastUsedAt {
+			err = tx.UpdateTemplateWorkspacesLastUsedAt(ctx, database.UpdateTemplateWorkspacesLastUsedAtParams{
+				TemplateID: tpl.ID,
+				LastUsedAt: database.Now(),
+			})
+			if err != nil {
+				return xerrors.Errorf("update template workspaces last_used_at: %w", err)
+			}
+		}
+
+		// TODO: update all workspace max_deadlines to be within new bounds
+		template, err = tx.GetTemplateByID(ctx, tpl.ID)
 		if err != nil {
 			return xerrors.Errorf("get updated template schedule: %w", err)
 		}
 
 		// Recalculate max_deadline and deadline for all running workspace
 		// builds on this template.
 		if s.UseRestartRequirement.Load() {
-			err = s.updateWorkspaceBuilds(ctx, db, template)
+			err = s.updateWorkspaceBuilds(ctx, tx, template)
 			if err != nil {
 				return xerrors.Errorf("update workspace builds: %w", err)
 			}