Oct 14, 2025 · Oct 13, 2025 · Oct 14, 2025 · Oct 14, 2025
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
 return fetchWithPostFilter(q.auth, policy.ActionRead, q.db.ListProvisionerKeysByOrganizationExcludeReserved)(ctx, organizationID)
 }

 func (q *querier) ListTasks(ctx context.Context, arg database.ListTasksParams) ([]database.Task, error) {
 // TODO(Cian): replace this with a sql filter for improved performance. https://github.com/coder/internal/issues/1061
 return fetchWithPostFilter(q.auth, policy.ActionRead, q.db.ListTasks)(ctx, arg)
 }

 func (q *querier) ListUserSecrets(ctx context.Context, userID uuid.UUID) ([]database.UserSecret, error) {
 obj := rbac.ResourceUserSecret.WithOwner(userID.String())
 if err := q.authorizeContext(ctx, policy.ActionRead, obj); err != nil {
diff --git a/coderd/database/dbauthz/dbauthz_test.go b/coderd/database/dbauthz/dbauthz_test.go
 dbm.EXPECT().GetTaskByWorkspaceID(gomock.Any(), task.WorkspaceID.UUID).Return(task, nil).AnyTimes()
 check.Args(task.WorkspaceID.UUID).Asserts(task, policy.ActionRead).Returns(task)
 }))
 s.Run("ListTasks", s.Mocked(func(dbm *dbmock.MockStore, faker *gofakeit.Faker, check *expects) {
 u1 := testutil.Fake(s.T(), faker, database.User{})
 u2 := testutil.Fake(s.T(), faker, database.User{})
 org1 := testutil.Fake(s.T(), faker, database.Organization{})
 org2 := testutil.Fake(s.T(), faker, database.Organization{})
 _ = testutil.Fake(s.T(), faker, database.OrganizationMember{UserID: u1.ID, OrganizationID: org1.ID})
 _ = testutil.Fake(s.T(), faker, database.OrganizationMember{UserID: u2.ID, OrganizationID: org2.ID})
 t1 := testutil.Fake(s.T(), faker, database.Task{OwnerID: u1.ID})
 t2 := testutil.Fake(s.T(), faker, database.Task{OwnerID: u2.ID})
 dbm.EXPECT().ListTasks(gomock.Any(), gomock.Any()).Return([]database.Task{t1, t2}, nil).AnyTimes()
 check.Args(database.ListTasksParams{}).Asserts(t1, policy.ActionRead, t2, policy.ActionRead).Returns([]database.Task{t1, t2})
 }))
 }

 func (s *MethodTestSuite) TestProvisionerKeys() {
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/querier.go b/coderd/database/querier.go
diff --git a/coderd/database/querier_test.go b/coderd/database/querier_test.go
 require.Len(t, rows, 0)
 })
 }

 func TestListTasks(t *testing.T) {
 t.Parallel()

 db, ps := dbtestutil.NewDB(t)

 // Given: two organizations and two users, one of which is a member of both
 org1 := dbgen.Organization(t, db, database.Organization{})
 org2 := dbgen.Organization(t, db, database.Organization{})
 user1 := dbgen.User(t, db, database.User{})
 user2 := dbgen.User(t, db, database.User{})
 _ = dbgen.OrganizationMember(t, db, database.OrganizationMember{
 OrganizationID: org1.ID,
 UserID:         user1.ID,
 })
 _ = dbgen.OrganizationMember(t, db, database.OrganizationMember{
 OrganizationID: org2.ID,
 UserID:         user2.ID,
 })

 // Given: a template with an active version
 tv := dbgen.TemplateVersion(t, db, database.TemplateVersion{
 CreatedBy:      user1.ID,
 OrganizationID: org1.ID,
 })
 tpl := dbgen.Template(t, db, database.Template{
 CreatedBy:       user1.ID,
 OrganizationID:  org1.ID,
 ActiveVersionID: tv.ID,
 })

 // Helper function to create a task
 createTask := func(orgID, ownerID uuid.UUID) database.TaskTable {
 ws := dbgen.Workspace(t, db, database.WorkspaceTable{
 OrganizationID: orgID,
 OwnerID:        ownerID,
 TemplateID:     tpl.ID,
 })
 pj := dbgen.ProvisionerJob(t, db, ps, database.ProvisionerJob{})
 sidebarAppID := uuid.New()
 _ = dbgen.WorkspaceBuild(t, db, database.WorkspaceBuild{
 JobID:             pj.ID,
 TemplateVersionID: tv.ID,
 WorkspaceID:       ws.ID,
 })
 wr := dbgen.WorkspaceResource(t, db, database.WorkspaceResource{
 JobID: pj.ID,
 })
 agt := dbgen.WorkspaceAgent(t, db, database.WorkspaceAgent{
 ResourceID: wr.ID,
 })
 wa := dbgen.WorkspaceApp(t, db, database.WorkspaceApp{
 ID:      sidebarAppID,
 AgentID: agt.ID,
 })
 tsk := dbgen.Task(t, db, database.TaskTable{
 OrganizationID:    orgID,
 OwnerID:           ownerID,
 Prompt:            testutil.GetRandomName(t),
 TemplateVersionID: tv.ID,
 WorkspaceID:       uuid.NullUUID{UUID: ws.ID, Valid: true},
 })
 _ = dbgen.TaskWorkspaceApp(t, db, database.TaskWorkspaceApp{
 TaskID:           tsk.ID,
 WorkspaceAgentID: uuid.NullUUID{Valid: true, UUID: agt.ID},
 WorkspaceAppID:   uuid.NullUUID{Valid: true, UUID: wa.ID},
 })
 t.Logf("task_id:%s owner_id:%s org_id:%s", tsk.ID, ownerID, orgID)
 return tsk
 }

 // Given: user1 has one task, user2 has one task, user3 has two tasks (one in each org)
 task1 := createTask(org1.ID, user1.ID)
 task2 := createTask(org1.ID, user2.ID)
 task3 := createTask(org2.ID, user2.ID)

 // Then: run various filters and assert expected results
 for _, tc := range []struct {
 name      string
 filter    database.ListTasksParams
 expectIDs []uuid.UUID
 }{
 {
 name: "no filter",
 filter: database.ListTasksParams{
 OwnerID:        uuid.Nil,
 OrganizationID: uuid.Nil,
 },
 expectIDs: []uuid.UUID{task3.ID, task2.ID, task1.ID},
 },
 {
 name: "filter by user ID",
 filter: database.ListTasksParams{
 OwnerID:        user1.ID,
 OrganizationID: uuid.Nil,
 },
 expectIDs: []uuid.UUID{task1.ID},
 },
 {
 name: "filter by organization ID",
 filter: database.ListTasksParams{
 OwnerID:        uuid.Nil,
 OrganizationID: org1.ID,
 },
 expectIDs: []uuid.UUID{task2.ID, task1.ID},
 },
 {
 name: "filter by user and organization ID",
 filter: database.ListTasksParams{
 OwnerID:        user2.ID,
 OrganizationID: org2.ID,
 },
 expectIDs: []uuid.UUID{task3.ID},
 },
 {
 name: "no results",
 filter: database.ListTasksParams{
 OwnerID:        user1.ID,
 OrganizationID: org2.ID,
 },
 expectIDs: nil,
 },
 } {
 t.Run(tc.name, func(t *testing.T) {
 t.Parallel()
 ctx := testutil.Context(t, testutil.WaitShort)
 tasks, err := db.ListTasks(ctx, tc.filter)
 if assert.NoError(t, err) {
 require.Len(t, tasks, len(tc.expectIDs))
 for idx, eid := range tc.expectIDs {
 assert.Equal(t, eid.String(), tasks[idx].ID.String())
 }
 }
 })
 }
 }
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/tasks.sql b/coderd/database/queries/tasks.sql

 -- name: GetTaskByWorkspaceID :one
 SELECT*FROM tasks_with_statusWHERE workspace_id= @workspace_id::uuid;

 -- name: ListTasks :many
 SELECT*FROM tasks_with_status tws
 WHEREtws.deleted_at ISNULL
 AND CASE WHEN @owner_id::UUID!='00000000-0000-0000-0000-000000000000' THENtws.owner_id= @owner_id::UUID ELSE TRUE END
 AND CASE WHEN @organization_id::UUID!='00000000-0000-0000-0000-000000000000' THENtws.organization_id= @organization_id::UUID ELSE TRUE END
 ORDER BYtws.created_atDESC;
Original file line number	Diff line number	Diff line change
Expand Up		@@ -4485,6 +4485,11 @@ func (q *querier) ListProvisionerKeysByOrganizationExcludeReserved(ctx context.C
		return fetchWithPostFilter(q.auth, policy.ActionRead, q.db.ListProvisionerKeysByOrganizationExcludeReserved)(ctx, organizationID)
		}

		func (q *querier) ListTasks(ctx context.Context, arg database.ListTasksParams) ([]database.Task, error) {
		// TODO(Cian): replace this with a sql filter for improved performance. https://github.com/coder/internal/issues/1061
		return fetchWithPostFilter(q.auth, policy.ActionRead, q.db.ListTasks)(ctx, arg)
		}

		func (q *querier) ListUserSecrets(ctx context.Context, userID uuid.UUID) ([]database.UserSecret, error) {
		obj := rbac.ResourceUserSecret.WithOwner(userID.String())
		if err := q.authorizeContext(ctx, policy.ActionRead, obj); err != nil {
Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -2392,6 +2392,18 @@ func (s *MethodTestSuite) TestTasks() {
		dbm.EXPECT().GetTaskByWorkspaceID(gomock.Any(), task.WorkspaceID.UUID).Return(task, nil).AnyTimes()
		check.Args(task.WorkspaceID.UUID).Asserts(task, policy.ActionRead).Returns(task)
		}))
		s.Run("ListTasks", s.Mocked(func(dbm dbmock.MockStore, faker gofakeit.Faker, check *expects) {
		u1 := testutil.Fake(s.T(), faker, database.User{})
		u2 := testutil.Fake(s.T(), faker, database.User{})
		org1 := testutil.Fake(s.T(), faker, database.Organization{})
		org2 := testutil.Fake(s.T(), faker, database.Organization{})
		_ = testutil.Fake(s.T(), faker, database.OrganizationMember{UserID: u1.ID, OrganizationID: org1.ID})
		_ = testutil.Fake(s.T(), faker, database.OrganizationMember{UserID: u2.ID, OrganizationID: org2.ID})
		t1 := testutil.Fake(s.T(), faker, database.Task{OwnerID: u1.ID})
		t2 := testutil.Fake(s.T(), faker, database.Task{OwnerID: u2.ID})
		dbm.EXPECT().ListTasks(gomock.Any(), gomock.Any()).Return([]database.Task{t1, t2}, nil).AnyTimes()
		check.Args(database.ListTasksParams{}).Asserts(t1, policy.ActionRead, t2, policy.ActionRead).Returns([]database.Task{t1, t2})
		}))
		}

		func (s *MethodTestSuite) TestProvisionerKeys() {
Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -7314,3 +7314,139 @@ func TestUsageEventsTrigger(t *testing.T) {
		require.Len(t, rows, 0)
		})
		}

		func TestListTasks(t *testing.T) {
		t.Parallel()

		db, ps := dbtestutil.NewDB(t)

		// Given: two organizations and two users, one of which is a member of both
		org1 := dbgen.Organization(t, db, database.Organization{})
		org2 := dbgen.Organization(t, db, database.Organization{})
		user1 := dbgen.User(t, db, database.User{})
		user2 := dbgen.User(t, db, database.User{})
		_ = dbgen.OrganizationMember(t, db, database.OrganizationMember{
		OrganizationID: org1.ID,
		UserID: user1.ID,
		})
		_ = dbgen.OrganizationMember(t, db, database.OrganizationMember{
		OrganizationID: org2.ID,
		UserID: user2.ID,
		})

		// Given: a template with an active version
		tv := dbgen.TemplateVersion(t, db, database.TemplateVersion{
		CreatedBy: user1.ID,
		OrganizationID: org1.ID,
		})
		tpl := dbgen.Template(t, db, database.Template{
		CreatedBy: user1.ID,
		OrganizationID: org1.ID,
		ActiveVersionID: tv.ID,
		})

		// Helper function to create a task
		createTask := func(orgID, ownerID uuid.UUID) database.TaskTable {
		ws := dbgen.Workspace(t, db, database.WorkspaceTable{
		OrganizationID: orgID,
		OwnerID: ownerID,
		TemplateID: tpl.ID,
		})
		pj := dbgen.ProvisionerJob(t, db, ps, database.ProvisionerJob{})
		sidebarAppID := uuid.New()
		_ = dbgen.WorkspaceBuild(t, db, database.WorkspaceBuild{
		JobID: pj.ID,
		TemplateVersionID: tv.ID,
		WorkspaceID: ws.ID,
		})
		wr := dbgen.WorkspaceResource(t, db, database.WorkspaceResource{
		JobID: pj.ID,
		})
		agt := dbgen.WorkspaceAgent(t, db, database.WorkspaceAgent{
		ResourceID: wr.ID,
		})
		wa := dbgen.WorkspaceApp(t, db, database.WorkspaceApp{
		ID: sidebarAppID,
		AgentID: agt.ID,
		})
		tsk := dbgen.Task(t, db, database.TaskTable{
		OrganizationID: orgID,
		OwnerID: ownerID,
		Prompt: testutil.GetRandomName(t),
		TemplateVersionID: tv.ID,
		WorkspaceID: uuid.NullUUID{UUID: ws.ID, Valid: true},
		})
		_ = dbgen.TaskWorkspaceApp(t, db, database.TaskWorkspaceApp{
		TaskID: tsk.ID,
		WorkspaceAgentID: uuid.NullUUID{Valid: true, UUID: agt.ID},
		WorkspaceAppID: uuid.NullUUID{Valid: true, UUID: wa.ID},
		})
		t.Logf("task_id:%s owner_id:%s org_id:%s", tsk.ID, ownerID, orgID)
		return tsk
		}

		// Given: user1 has one task, user2 has one task, user3 has two tasks (one in each org)
		task1 := createTask(org1.ID, user1.ID)
		task2 := createTask(org1.ID, user2.ID)
		task3 := createTask(org2.ID, user2.ID)

		// Then: run various filters and assert expected results
		for _, tc := range []struct {
		name string
		filter database.ListTasksParams
		expectIDs []uuid.UUID
		}{
		{
		name: "no filter",
		filter: database.ListTasksParams{
		OwnerID: uuid.Nil,
		OrganizationID: uuid.Nil,
		},
		expectIDs: []uuid.UUID{task3.ID, task2.ID, task1.ID},
		},
		{
		name: "filter by user ID",
		filter: database.ListTasksParams{
		OwnerID: user1.ID,
		OrganizationID: uuid.Nil,
		},
		expectIDs: []uuid.UUID{task1.ID},
		},
		{
		name: "filter by organization ID",
		filter: database.ListTasksParams{
		OwnerID: uuid.Nil,
		OrganizationID: org1.ID,
		},
		expectIDs: []uuid.UUID{task2.ID, task1.ID},
		},
		{
		name: "filter by user and organization ID",
		filter: database.ListTasksParams{
		OwnerID: user2.ID,
		OrganizationID: org2.ID,
		},
		expectIDs: []uuid.UUID{task3.ID},
		},
		{
		name: "no results",
		filter: database.ListTasksParams{
		OwnerID: user1.ID,
		OrganizationID: org2.ID,
		},
		expectIDs: nil,
		},
		} {
		t.Run(tc.name, func(t *testing.T) {
		t.Parallel()
		ctx := testutil.Context(t, testutil.WaitShort)
		tasks, err := db.ListTasks(ctx, tc.filter)
		if assert.NoError(t, err) {
		require.Len(t, tasks, len(tc.expectIDs))
		for idx, eid := range tc.expectIDs {
		assert.Equal(t, eid.String(), tasks[idx].ID.String())
		}
		}
		})
		}
		}
Original file line number	Diff line number	Diff line change
Expand Up		@@ -21,3 +21,10 @@ SELECT * FROM tasks_with_status WHERE id = @id::uuid;

		-- name: GetTaskByWorkspaceID :one
		SELECT*FROM tasks_with_statusWHERE workspace_id= @workspace_id::uuid;

		-- name: ListTasks :many
		SELECT*FROM tasks_with_status tws
		WHEREtws.deleted_at ISNULL
		AND CASE WHEN @owner_id::UUID!='00000000-0000-0000-0000-000000000000' THENtws.owner_id= @owner_id::UUID ELSE TRUE END
		AND CASE WHEN @organization_id::UUID!='00000000-0000-0000-0000-000000000000' THENtws.organization_id= @organization_id::UUID ELSE TRUE END
		ORDER BYtws.created_atDESC;