fix: begin impl of reducing autostart false-positive rate

coder · DanielleMaywood · Dec 2, 2024 · Nov 18, 2024 · Nov 18, 2024 · Nov 19, 2024
commit 78d9bef493133cf735494cc5594b7847a2818d35
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -4044,6 +4044,13 @@ func (q *querier) UpdateWorkspaceLastUsedAt(ctx context.Context, arg database.Up
 	return update(q.log, q.auth, fetch, q.db.UpdateWorkspaceLastUsedAt)(ctx, arg)
 }
 
+func (q *querier) UpdateWorkspaceNextStartAt(ctx context.Context, arg database.UpdateWorkspaceNextStartAtParams) error {
+	fetch := func(ctx context.Context, arg database.UpdateWorkspaceNextStartAtParams) (database.Workspace, error) {
+		return q.db.GetWorkspaceByID(ctx, arg.ID)
+	}
+	return update(q.log, q.auth, fetch, q.db.UpdateWorkspaceNextStartAt)(ctx, arg)
+}
+
 func (q *querier) UpdateWorkspaceProxy(ctx context.Context, arg database.UpdateWorkspaceProxyParams) (database.WorkspaceProxy, error) {
 	fetch := func(ctx context.Context, arg database.UpdateWorkspaceProxyParams) (database.WorkspaceProxy, error) {
 		return q.db.GetWorkspaceProxyByID(ctx, arg.ID)

diff --git a/coderd/database/dbauthz/dbauthz_test.go b/coderd/database/dbauthz/dbauthz_test.go
@@ -1887,6 +1887,13 @@ func (s *MethodTestSuite) TestWorkspace() {
 			ID: ws.ID,
 		}).Asserts(ws, policy.ActionUpdate).Returns()
 	}))
+	s.Run("UpdateWorkspaceNextStartAt", s.Subtest(func(db database.Store, check *expects) {
+		ws := dbgen.Workspace(s.T(), db, database.WorkspaceTable{})
+		check.Args(database.UpdateWorkspaceNextStartAtParams{
+			ID:          ws.ID,
+			NextStartAt: sql.NullTime{Valid: true, Time: dbtime.Now()},
+		}).Asserts(ws, policy.ActionUpdate)
+	}))
 	s.Run("BatchUpdateWorkspaceLastUsedAt", s.Subtest(func(db database.Store, check *expects) {
 		ws1 := dbgen.Workspace(s.T(), db, database.WorkspaceTable{})
 		ws2 := dbgen.Workspace(s.T(), db, database.WorkspaceTable{})

diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
@@ -9950,6 +9950,29 @@ func (q *FakeQuerier) UpdateWorkspaceLastUsedAt(_ context.Context, arg database.
 	return sql.ErrNoRows
 }
 
+func (q *FakeQuerier) UpdateWorkspaceNextStartAt(ctx context.Context, arg database.UpdateWorkspaceNextStartAtParams) error {
+	err := validateDatabaseType(arg)
+	if err != nil {
+		return err
+	}
+
+	q.mutex.Lock()
+	defer q.mutex.Unlock()
+
+	for index, workspace := range q.workspaces {
+		if workspace.ID != arg.ID {
+			continue
+		}
+
+		workspace.NextStartAt = arg.NextStartAt
+		q.workspaces[index] = workspace
+
+		return nil
+	}
+
+	return sql.ErrNoRows
+}
+
 func (q *FakeQuerier) UpdateWorkspaceProxy(_ context.Context, arg database.UpdateWorkspaceProxyParams) (database.WorkspaceProxy, error) {
 	q.mutex.Lock()
 	defer q.mutex.Unlock()

diff --git a/coderd/database/dbmetrics/querymetrics.go b/coderd/database/dbmetrics/querymetrics.go
diff --git a/coderd/database/dbmock/dbmock.go b/coderd/database/dbmock/dbmock.go
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000276_create_cron_type_for_autostart_schedule.down.sql b/coderd/database/migrations/000276_create_cron_type_for_autostart_schedule.down.sql
@@ -0,0 +1,37 @@
+DROP VIEW workspaces_expanded;
+
+ALTER TABLE ONLY workspaces DROP COLUMN IF EXISTS next_start_at;
+
+CREATE VIEW
+	workspaces_expanded
+AS
+SELECT
+	workspaces.*,
+	-- Owner
+	visible_users.avatar_url AS owner_avatar_url,
+	visible_users.username AS owner_username,
+	-- Organization
+	organizations.name AS organization_name,
+	organizations.display_name AS organization_display_name,
+	organizations.icon AS organization_icon,
+	organizations.description AS organization_description,
+    -- Template
+	templates.name AS template_name,
+	templates.display_name AS template_display_name,
+	templates.icon AS template_icon,
+	templates.description AS template_description
+FROM
+	workspaces
+	INNER JOIN
+		visible_users
+	ON
+		workspaces.owner_id = visible_users.id
+	INNER JOIN
+		organizations
+	ON workspaces.organization_id = organizations.id
+	INNER JOIN
+		templates
+	ON workspaces.template_id = templates.id
+;
+
+COMMENT ON VIEW workspaces_expanded IS 'Joins in the display name information such as username, avatar, and organization name.';
diff --git a/coderd/database/migrations/000276_create_cron_type_for_autostart_schedule.up.sql b/coderd/database/migrations/000276_create_cron_type_for_autostart_schedule.up.sql
@@ -0,0 +1,38 @@
+ALTER TABLE ONLY workspaces ADD COLUMN IF NOT EXISTS next_start_at TIMESTAMPTZ DEFAULT NULL;
+
+-- Recreate view
+DROP VIEW workspaces_expanded;
+
+CREATE VIEW
+	workspaces_expanded
+AS
+SELECT
+	workspaces.*,
+	-- Owner
+	visible_users.avatar_url AS owner_avatar_url,
+	visible_users.username AS owner_username,
+	-- Organization
+	organizations.name AS organization_name,
+	organizations.display_name AS organization_display_name,
+	organizations.icon AS organization_icon,
+	organizations.description AS organization_description,
+    -- Template
+	templates.name AS template_name,
+	templates.display_name AS template_display_name,
+	templates.icon AS template_icon,
+	templates.description AS template_description
+FROM
+	workspaces
+	INNER JOIN
+		visible_users
+	ON
+		workspaces.owner_id = visible_users.id
+	INNER JOIN
+		organizations
+	ON workspaces.organization_id = organizations.id
+	INNER JOIN
+		templates
+	ON workspaces.template_id = templates.id
+;
+
+COMMENT ON VIEW workspaces_expanded IS 'Joins in the display name information such as username, avatar, and organization name.';
diff --git a/coderd/database/modelmethods.go b/coderd/database/modelmethods.go
@@ -214,6 +214,7 @@ func (w Workspace) WorkspaceTable() WorkspaceTable {
 		DeletingAt:        w.DeletingAt,
 		AutomaticUpdates:  w.AutomaticUpdates,
 		Favorite:          w.Favorite,
+		NextStartAt:       w.NextStartAt,
 	}
 }
 
@@ -438,6 +439,7 @@ func ConvertWorkspaceRows(rows []GetWorkspacesRow) []Workspace {
 			TemplateDisplayName:     r.TemplateDisplayName,
 			TemplateIcon:            r.TemplateIcon,
 			TemplateDescription:     r.TemplateDescription,
+			NextStartAt:             r.NextStartAt,
 		}
 	}
 

diff --git a/coderd/database/modelqueries.go b/coderd/database/modelqueries.go
@@ -290,6 +290,7 @@ func (q *sqlQuerier) GetAuthorizedWorkspaces(ctx context.Context, arg GetWorkspa
 			&i.DeletingAt,
 			&i.AutomaticUpdates,
 			&i.Favorite,
+			&i.NextStartAt,
 			&i.OwnerAvatarUrl,
 			&i.OwnerUsername,
 			&i.OrganizationName,

diff --git a/coderd/database/models.go b/coderd/database/models.go
diff --git a/coderd/database/querier.go b/coderd/database/querier.go