coder · mafredri · Aug 21, 2023 · Aug 16, 2023 · Aug 16, 2023 · Aug 17, 2023
diff --git a/coderd/apidoc/docs.go b/coderd/apidoc/docs.go
diff --git a/coderd/apidoc/swagger.json b/coderd/apidoc/swagger.json
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -1173,6 +1173,25 @@ func (q *querier) GetTailnetClientsForAgent(ctx context.Context, agentID uuid.UU
 	return q.db.GetTailnetClientsForAgent(ctx, agentID)
 }
 
+func (q *querier) GetTemplateAppInsights(ctx context.Context, arg database.GetTemplateAppInsightsParams) ([]database.GetTemplateAppInsightsRow, error) {
+	for _, templateID := range arg.TemplateIDs {
+		template, err := q.db.GetTemplateByID(ctx, templateID)
+		if err != nil {
+			return nil, err
+		}
+
+		if err := q.authorizeContext(ctx, rbac.ActionUpdate, template); err != nil {
+			return nil, err
+		}
+	}
+	if len(arg.TemplateIDs) == 0 {
+		if err := q.authorizeContext(ctx, rbac.ActionUpdate, rbac.ResourceTemplate.All()); err != nil {
+			return nil, err
+		}
+	}
+	return q.db.GetTemplateAppInsights(ctx, arg)
+}
+
 // Only used by metrics cache.
 func (q *querier) GetTemplateAverageBuildTime(ctx context.Context, arg database.GetTemplateAverageBuildTimeParams) (database.GetTemplateAverageBuildTimeRow, error) {
 	if err := q.authorizeContext(ctx, rbac.ActionRead, rbac.ResourceSystem); err != nil {

diff --git a/coderd/database/dbfake/dbfake.go b/coderd/database/dbfake/dbfake.go
@@ -549,6 +549,19 @@ func (q *FakeQuerier) getWorkspaceAgentsByResourceIDsNoLock(_ context.Context, r
 	return workspaceAgents, nil
 }
 
+func (q *FakeQuerier) getWorkspaceAppByAgentIDAndSlugNoLock(_ context.Context, arg database.GetWorkspaceAppByAgentIDAndSlugParams) (database.WorkspaceApp, error) {
+	for _, app := range q.workspaceApps {
+		if app.AgentID != arg.AgentID {
+			continue
+		}
+		if app.Slug != arg.Slug {
+			continue
+		}
+		return app, nil
+	}
+	return database.WorkspaceApp{}, sql.ErrNoRows
+}
+
 func (q *FakeQuerier) getProvisionerJobByIDNoLock(_ context.Context, id uuid.UUID) (database.ProvisionerJob, error) {
 	for _, provisionerJob := range q.provisionerJobs {
 		if provisionerJob.ID != id {
@@ -1966,6 +1979,125 @@ func (*FakeQuerier) GetTailnetClientsForAgent(context.Context, uuid.UUID) ([]dat
 	return nil, ErrUnimplemented
 }
 
+func (q *FakeQuerier) GetTemplateAppInsights(ctx context.Context, arg database.GetTemplateAppInsightsParams) ([]database.GetTemplateAppInsightsRow, error) {
+	err := validateDatabaseType(arg)
+	if err != nil {
+		return nil, err
+	}
+
+	q.mutex.RLock()
+	defer q.mutex.RUnlock()
+
+	type appKey struct {
+		AccessMethod string
+		SlugOrPort   string
+		Slug         string
+		DisplayName  string
+		Icon         string
+	}
+	type uniqueKey struct {
+		TemplateID uuid.UUID
+		UserID     uuid.UUID
+		AgentID    uuid.UUID
+		AppKey     appKey
+	}
+
+	appUsageIntervalsByUserAgentApp := make(map[uniqueKey]map[time.Time]int64)
+	for _, s := range q.workspaceAppStats {
+		// (was.session_started_at >= ts.from_ AND was.session_started_at < ts.to_)
+		// OR (was.session_ended_at > ts.from_ AND was.session_ended_at < ts.to_)
+		// OR (was.session_started_at < ts.from_ AND was.session_ended_at >= ts.to_)
+		if !(((s.SessionStartedAt.After(arg.StartTime) || s.SessionStartedAt.Equal(arg.StartTime)) && s.SessionStartedAt.Before(arg.EndTime)) ||
+			(s.SessionEndedAt.After(arg.StartTime) && s.SessionEndedAt.Before(arg.EndTime)) ||
+			(s.SessionStartedAt.Before(arg.StartTime) && (s.SessionEndedAt.After(arg.EndTime) || s.SessionEndedAt.Equal(arg.EndTime)))) {
+			continue
+		}
+
+		w, err := q.getWorkspaceByIDNoLock(ctx, s.WorkspaceID)
+		if err != nil {
+			return nil, err
+		}
+
+		app, _ := q.getWorkspaceAppByAgentIDAndSlugNoLock(ctx, database.GetWorkspaceAppByAgentIDAndSlugParams{
+			AgentID: s.AgentID,
+			Slug:    s.SlugOrPort,
+		})
+
+		key := uniqueKey{
+			TemplateID: w.TemplateID,
+			UserID:     s.UserID,
+			AgentID:    s.AgentID,
+			AppKey: appKey{
+				AccessMethod: s.AccessMethod,
+				SlugOrPort:   s.SlugOrPort,
+				Slug:         app.Slug,
+				DisplayName:  app.DisplayName,
+				Icon:         app.Icon,
+			},
+		}
+		if appUsageIntervalsByUserAgentApp[key] == nil {
+			appUsageIntervalsByUserAgentApp[key] = make(map[time.Time]int64)
+		}
+
+		t := s.SessionStartedAt.Truncate(5 * time.Minute)
+		if t.Before(arg.StartTime) {
+			t = arg.StartTime
+		}
+		for t.Before(s.SessionEndedAt) && t.Before(arg.EndTime) {
+			appUsageIntervalsByUserAgentApp[key][t] = 300 // 5 minutes.
+			t = t.Add(5 * time.Minute)
+		}
+	}
+
+	appUsageTemplateIDs := make(map[appKey]map[uuid.UUID]struct{})
+	appUsageUserIDs := make(map[appKey]map[uuid.UUID]struct{})
+	appUsage := make(map[appKey]int64)
+	for uniqueKey, usage := range appUsageIntervalsByUserAgentApp {
+		for _, seconds := range usage {
+			if appUsageTemplateIDs[uniqueKey.AppKey] == nil {
+				appUsageTemplateIDs[uniqueKey.AppKey] = make(map[uuid.UUID]struct{})
+			}
+			appUsageTemplateIDs[uniqueKey.AppKey][uniqueKey.TemplateID] = struct{}{}
+			if appUsageUserIDs[uniqueKey.AppKey] == nil {
+				appUsageUserIDs[uniqueKey.AppKey] = make(map[uuid.UUID]struct{})
+			}
+			appUsageUserIDs[uniqueKey.AppKey][uniqueKey.UserID] = struct{}{}
+			appUsage[uniqueKey.AppKey] += seconds
+		}
+	}
+
+	var rows []database.GetTemplateAppInsightsRow
+	for appKey, usage := range appUsage {
+		templateIDs := make([]uuid.UUID, 0, len(appUsageTemplateIDs[appKey]))
+		for templateID := range appUsageTemplateIDs[appKey] {
+			templateIDs = append(templateIDs, templateID)
+		}
+		slices.SortFunc(templateIDs, func(a, b uuid.UUID) int {
+			return slice.Ascending(a.String(), b.String())
+		})
+		activeUserIDs := make([]uuid.UUID, 0, len(appUsageUserIDs[appKey]))
+		for userID := range appUsageUserIDs[appKey] {
+			activeUserIDs = append(activeUserIDs, userID)
+		}
+		slices.SortFunc(activeUserIDs, func(a, b uuid.UUID) int {
+			return slice.Ascending(a.String(), b.String())
+		})
+
+		rows = append(rows, database.GetTemplateAppInsightsRow{
+			TemplateIDs:   templateIDs,
+			ActiveUserIDs: activeUserIDs,
+			AccessMethod:  appKey.AccessMethod,
+			SlugOrPort:    appKey.SlugOrPort,
+			DisplayName:   sql.NullString{String: appKey.DisplayName, Valid: appKey.DisplayName != ""},
+			Icon:          sql.NullString{String: appKey.Icon, Valid: appKey.Icon != ""},
+			IsApp:         appKey.Slug != "",
+			UsageSeconds:  usage,
+		})
+	}
+
+	return rows, nil
+}
+
 func (q *FakeQuerier) GetTemplateAverageBuildTime(ctx context.Context, arg database.GetTemplateAverageBuildTimeParams) (database.GetTemplateAverageBuildTimeRow, error) {
 	if err := validateDatabaseType(arg); err != nil {
 		return database.GetTemplateAverageBuildTimeRow{}, err
@@ -2093,12 +2225,15 @@ func (q *FakeQuerier) GetTemplateDAUs(_ context.Context, arg database.GetTemplat
 	return rs, nil
 }
 
-func (q *FakeQuerier) GetTemplateDailyInsights(_ context.Context, arg database.GetTemplateDailyInsightsParams) ([]database.GetTemplateDailyInsightsRow, error) {
+func (q *FakeQuerier) GetTemplateDailyInsights(ctx context.Context, arg database.GetTemplateDailyInsightsParams) ([]database.GetTemplateDailyInsightsRow, error) {
 	err := validateDatabaseType(arg)
 	if err != nil {
 		return nil, err
 	}
 
+	q.mutex.RLock()
+	defer q.mutex.RUnlock()
+
 	type dailyStat struct {
 		startTime, endTime time.Time
 		userSet            map[uuid.UUID]struct{}
@@ -2133,6 +2268,37 @@ func (q *FakeQuerier) GetTemplateDailyInsights(_ context.Context, arg database.G
 		}
 	}
 
+	for _, s := range q.workspaceAppStats {
+		// (was.session_started_at >= ts.from_ AND was.session_started_at < ts.to_)
+		// OR (was.session_ended_at > ts.from_ AND was.session_ended_at < ts.to_)
+		// OR (was.session_started_at < ts.from_ AND was.session_ended_at >= ts.to_)
+		if !(((s.SessionStartedAt.After(arg.StartTime) || s.SessionStartedAt.Equal(arg.StartTime)) && s.SessionStartedAt.Before(arg.EndTime)) ||
+			(s.SessionEndedAt.After(arg.StartTime) && s.SessionEndedAt.Before(arg.EndTime)) ||
+			(s.SessionStartedAt.Before(arg.StartTime) && (s.SessionEndedAt.After(arg.EndTime) || s.SessionEndedAt.Equal(arg.EndTime)))) {
+			continue
+		}
+
+		for _, ds := range dailyStats {
+			// (was.session_started_at >= ts.from_ AND was.session_started_at < ts.to_)
+			// OR (was.session_ended_at > ts.from_ AND was.session_ended_at < ts.to_)
+			// OR (was.session_started_at < ts.from_ AND was.session_ended_at >= ts.to_)
+			if !(((s.SessionStartedAt.After(arg.StartTime) || s.SessionStartedAt.Equal(arg.StartTime)) && s.SessionStartedAt.Before(arg.EndTime)) ||
+				(s.SessionEndedAt.After(arg.StartTime) && s.SessionEndedAt.Before(arg.EndTime)) ||
+				(s.SessionStartedAt.Before(arg.StartTime) && (s.SessionEndedAt.After(arg.EndTime) || s.SessionEndedAt.Equal(arg.EndTime)))) {
+				continue
+			}
+
+			w, err := q.getWorkspaceByIDNoLock(ctx, s.WorkspaceID)
+			if err != nil {
+				return nil, err
+			}
+
+			ds.userSet[s.UserID] = struct{}{}
+			ds.templateIDSet[w.TemplateID] = struct{}{}
+			break
+		}
+	}
+
 	var result []database.GetTemplateDailyInsightsRow
 	for _, ds := range dailyStats {
 		templateIDs := make([]uuid.UUID, 0, len(ds.templateIDSet))
@@ -2201,9 +2367,14 @@ func (q *FakeQuerier) GetTemplateInsights(_ context.Context, arg database.GetTem
 	slices.SortFunc(templateIDs, func(a, b uuid.UUID) int {
 		return slice.Ascending(a.String(), b.String())
 	})
+	activeUserIDs := make([]uuid.UUID, 0, len(appUsageIntervalsByUser))
+	for userID := range appUsageIntervalsByUser {
+		activeUserIDs = append(activeUserIDs, userID)
+	}
+
 	result := database.GetTemplateInsightsRow{
-		TemplateIDs: templateIDs,
-		ActiveUsers: int64(len(appUsageIntervalsByUser)),
+		TemplateIDs:   templateIDs,
+		ActiveUserIDs: activeUserIDs,
 	}
 	for _, intervals := range appUsageIntervalsByUser {
 		for _, interval := range intervals {
@@ -3075,24 +3246,15 @@ func (q *FakeQuerier) GetWorkspaceAgentsInLatestBuildByWorkspaceID(ctx context.C
 	return agents, nil
 }
 
-func (q *FakeQuerier) GetWorkspaceAppByAgentIDAndSlug(_ context.Context, arg database.GetWorkspaceAppByAgentIDAndSlugParams) (database.WorkspaceApp, error) {
+func (q *FakeQuerier) GetWorkspaceAppByAgentIDAndSlug(ctx context.Context, arg database.GetWorkspaceAppByAgentIDAndSlugParams) (database.WorkspaceApp, error) {
 	if err := validateDatabaseType(arg); err != nil {
 		return database.WorkspaceApp{}, err
 	}
 
 	q.mutex.RLock()
 	defer q.mutex.RUnlock()
 
-	for _, app := range q.workspaceApps {
-		if app.AgentID != arg.AgentID {
-			continue
-		}
-		if app.Slug != arg.Slug {
-			continue
-		}
-		return app, nil
-	}
-	return database.WorkspaceApp{}, sql.ErrNoRows
+	return q.getWorkspaceAppByAgentIDAndSlugNoLock(ctx, arg)
 }
 
 func (q *FakeQuerier) GetWorkspaceAppsByAgentID(_ context.Context, id uuid.UUID) ([]database.WorkspaceApp, error) {

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