feat: add last_used search params to workspaces

coder · Emyrk · Aug 22, 2023 · Aug 21, 2023 · Aug 21, 2023 · Aug 21, 2023
commit fb88b92ca09dd406c13a04e1ae37d2eb47a8b040
diff --git a/coderd/database/dbfake/dbfake.go b/coderd/database/dbfake/dbfake.go
@@ -6064,6 +6064,18 @@ func (q *FakeQuerier) GetAuthorizedWorkspaces(ctx context.Context, arg database.
 			continue
 		}
 
+		if !arg.LastUsedBefore.IsZero() {
+			if workspace.LastUsedAt.After(arg.LastUsedBefore) {
+				continue
+			}
+		}
+
+		if !arg.LastUsedAfter.IsZero() {
+			if workspace.LastUsedAt.Before(arg.LastUsedAfter) {
+				continue
+			}
+		}
+
 		if arg.Status != "" {
 			build, err := q.getLatestWorkspaceBuildByWorkspaceIDNoLock(ctx, workspace.ID)
 			if err != nil {

diff --git a/coderd/database/dbgen/dbgen.go b/coderd/database/dbgen/dbgen.go
@@ -317,8 +317,9 @@ func ProvisionerJob(t testing.TB, db database.Store, orig database.ProvisionerJo
 		// Make sure when we acquire the job, we only get this one.
 		orig.Tags[id.String()] = "true"
 	}
+	jobID := takeFirst(orig.ID, uuid.New())
 	job, err := db.InsertProvisionerJob(genCtx, database.InsertProvisionerJobParams{
-		ID:             takeFirst(orig.ID, uuid.New()),
+		ID:             jobID,
 		CreatedAt:      takeFirst(orig.CreatedAt, database.Now()),
 		UpdatedAt:      takeFirst(orig.UpdatedAt, database.Now()),
 		OrganizationID: takeFirst(orig.OrganizationID, uuid.New()),
@@ -343,7 +344,7 @@ func ProvisionerJob(t testing.TB, db database.Store, orig database.ProvisionerJo
 
 	if !orig.CompletedAt.Time.IsZero() || orig.Error.String != "" {
 		err := db.UpdateProvisionerJobWithCompleteByID(genCtx, database.UpdateProvisionerJobWithCompleteByIDParams{
-			ID:          job.ID,
+			ID:          jobID,
 			UpdatedAt:   job.UpdatedAt,
 			CompletedAt: orig.CompletedAt,
 			Error:       orig.Error,
@@ -353,14 +354,14 @@ func ProvisionerJob(t testing.TB, db database.Store, orig database.ProvisionerJo
 	}
 	if !orig.CanceledAt.Time.IsZero() {
 		err := db.UpdateProvisionerJobWithCancelByID(genCtx, database.UpdateProvisionerJobWithCancelByIDParams{
-			ID:          job.ID,
+			ID:          jobID,
 			CanceledAt:  orig.CanceledAt,
 			CompletedAt: orig.CompletedAt,
 		})
 		require.NoError(t, err)
 	}
 
-	job, err = db.GetProvisionerJobByID(genCtx, job.ID)
+	job, err = db.GetProvisionerJobByID(genCtx, jobID)
 	require.NoError(t, err)
 
 	return job

diff --git a/coderd/database/modelqueries.go b/coderd/database/modelqueries.go
@@ -218,11 +218,13 @@ func (q *sqlQuerier) GetAuthorizedWorkspaces(ctx context.Context, arg GetWorkspa
 		arg.HasAgent,
 		arg.AgentInactiveDisconnectTimeoutSeconds,
 		arg.LockedAt,
+		arg.LastUsedBefore,
+		arg.LastUsedAfter,
 		arg.Offset,
 		arg.Limit,
 	)
 	if err != nil {
-		return nil, xerrors.Errorf("get authorized workspaces: %w", err)
+		return nil, err
 	}
 	defer rows.Close()
 	var items []GetWorkspacesRow

diff --git a/coderd/database/models.go b/coderd/database/models.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
@@ -267,6 +267,17 @@ WHERE
 		ELSE
 			locked_at IS NULL
 	END
+	-- Filter by last_used
+	AND CASE
+		  WHEN @last_used_before :: timestamp with time zone > '0001-01-01 00:00:00Z' THEN
+				  workspaces.last_used_at <= @last_used_before
+		  ELSE true
+	END
+	AND CASE
+		  WHEN @last_used_after :: timestamp with time zone > '0001-01-01 00:00:00Z' THEN
+				  workspaces.last_used_at >= @last_used_after
+		  ELSE true
+	END
 	-- Authorize Filter clause will be injected below in GetAuthorizedWorkspaces
 	-- @authorize_filter
 ORDER BY

diff --git a/coderd/searchquery/search.go b/coderd/searchquery/search.go
@@ -115,6 +115,8 @@ func Workspaces(query string, page codersdk.Pagination, agentInactiveDisconnectT
 	filter.Status = string(httpapi.ParseCustom(parser, values, "", "status", httpapi.ParseEnum[database.WorkspaceStatus]))
 	filter.HasAgent = parser.String(values, "", "has-agent")
 	filter.LockedAt = parser.Time(values, time.Time{}, "locked_at", "2006-01-02")
+	filter.LastUsedAfter = parser.Time3339Nano(values, time.Time{}, "last_used_after")
+	filter.LastUsedBefore = parser.Time3339Nano(values, time.Time{}, "last_used_before")
 
 	if _, ok := values["deleting_by"]; ok {
 		postFilter.DeletingBy = ptr.Ref(parser.Time(values, time.Time{}, "deleting_by", "2006-01-02"))

diff --git a/coderd/workspaces_test.go b/coderd/workspaces_test.go
@@ -1447,6 +1447,61 @@ func TestWorkspaceFilterManual(t *testing.T) {
 		require.Len(t, res.Workspaces, 1)
 		require.NotNil(t, res.Workspaces[0].LockedAt)
 	})
+
+	t.Run("LastUsed", func(t *testing.T) {
+		t.Parallel()
+		client, _, api := coderdtest.NewWithAPI(t, &coderdtest.Options{
+			IncludeProvisionerDaemon: true,
+		})
+		user := coderdtest.CreateFirstUser(t, client)
+		authToken := uuid.NewString()
+		version := coderdtest.CreateTemplateVersion(t, client, user.OrganizationID, &echo.Responses{
+			Parse:          echo.ParseComplete,
+			ProvisionPlan:  echo.ProvisionComplete,
+			ProvisionApply: echo.ProvisionApplyWithAgent(authToken),
+		})
+		template := coderdtest.CreateTemplate(t, client, user.OrganizationID, version.ID)
+		_ = coderdtest.AwaitTemplateVersionJob(t, client, version.ID)
+
+		// update template with inactivity ttl
+		ctx, cancel := context.WithTimeout(context.Background(), testutil.WaitLong)
+		defer cancel()
+
+		now := database.Now()
+		before := coderdtest.CreateWorkspace(t, client, user.OrganizationID, template.ID)
+		_ = coderdtest.AwaitWorkspaceBuildJob(t, client, before.LatestBuild.ID)
+
+		after := coderdtest.CreateWorkspace(t, client, user.OrganizationID, template.ID)
+		_ = coderdtest.AwaitWorkspaceBuildJob(t, client, after.LatestBuild.ID)
+
+		//nolint:gocritic -- testing
+		err := api.Database.UpdateWorkspaceLastUsedAt(dbauthz.AsSystemRestricted(ctx), database.UpdateWorkspaceLastUsedAtParams{
+			ID:         before.ID,
+			LastUsedAt: now.UTC().Add(time.Hour * -1),
+		})
+		require.NoError(t, err)
+
+		//nolint:gocritic -- testing
+		err = api.Database.UpdateWorkspaceLastUsedAt(dbauthz.AsSystemRestricted(ctx), database.UpdateWorkspaceLastUsedAtParams{
+			ID:         after.ID,
+			LastUsedAt: now.UTC().Add(time.Hour * 1),
+		})
+		require.NoError(t, err)
+
+		beforeRes, err := client.Workspaces(ctx, codersdk.WorkspaceFilter{
+			FilterQuery: fmt.Sprintf("last_used_before:%q", now.Format(time.RFC3339)),
+		})
+		require.NoError(t, err)
+		require.Len(t, beforeRes.Workspaces, 1)
+		require.Equal(t, before.ID, beforeRes.Workspaces[0].ID)
+
+		afterRes, err := client.Workspaces(ctx, codersdk.WorkspaceFilter{
+			FilterQuery: fmt.Sprintf("last_used_after:%q", now.Format(time.RFC3339)),
+		})
+		require.NoError(t, err)
+		require.Len(t, afterRes.Workspaces, 1)
+		require.Equal(t, after.ID, afterRes.Workspaces[0].ID)
+	})
 }
 
 func TestOffsetLimit(t *testing.T) {