feat(coderd/database): rewrite GetTemplateInsights to use `template…

…_usage_stats`
coder · mafredri · Mar 18, 2024 · Mar 19, 2024 · Mar 18, 2024 · Mar 20, 2024
commit 9037b25def98ea4cd7984a8aca1d0e90d4e40a55
diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
@@ -3290,8 +3290,8 @@ func (q *FakeQuerier) GetTemplateInsights(_ context.Context, arg database.GetTem
 	}
 
 	result := database.GetTemplateInsightsRow{
-		TemplateIDs:   templateIDs,
-		ActiveUserIDs: activeUserIDs,
+		TemplateIDs: templateIDs,
+		ActiveUsers: int64(len(activeUserIDs)),
 	}
 	for _, intervals := range appUsageIntervalsByUser {
 		for _, interval := range intervals {

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/insights.sql b/coderd/database/queries/insights.sql
@@ -99,40 +99,67 @@ GROUP BY users.id, username, avatar_url
 ORDER BY user_id ASC;
 
 -- name: GetTemplateInsights :one
--- GetTemplateInsights has a granularity of 5 minutes where if a session/app was
--- in use during a minute, we will add 5 minutes to the total usage for that
--- session/app (per user).
-WITH agent_stats_by_interval_and_user AS (
-	SELECT
-		date_trunc('minute', was.created_at),
-		was.user_id,
-		array_agg(was.template_id) AS template_ids,
-		CASE WHEN SUM(was.session_count_vscode) > 0 THEN 60 ELSE 0 END AS usage_vscode_seconds,
-		CASE WHEN SUM(was.session_count_jetbrains) > 0 THEN 60 ELSE 0 END AS usage_jetbrains_seconds,
-		CASE WHEN SUM(was.session_count_reconnecting_pty) > 0 THEN 60 ELSE 0 END AS usage_reconnecting_pty_seconds,
-		CASE WHEN SUM(was.session_count_ssh) > 0 THEN 60 ELSE 0 END AS usage_ssh_seconds
-	FROM workspace_agent_stats was
-	WHERE
-		was.created_at >= @start_time::timestamptz
-		AND was.created_at < @end_time::timestamptz
-		AND was.connection_count > 0
-		AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN was.template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
-	GROUP BY date_trunc('minute', was.created_at), was.user_id
-), template_ids AS (
-	SELECT array_agg(DISTINCT template_id) AS ids
-	FROM agent_stats_by_interval_and_user, unnest(template_ids) template_id
-	WHERE template_id IS NOT NULL
-)
+-- GetTemplateInsights returns the aggregate user-produced usage of all
+-- workspaces in a given timeframe. The template IDs, active users, and
+-- usage_seconds all reflect any usage in the template, including apps.
+--
+-- When combining data from multiple templates, we must make a guess at
+-- how the user behaved for the 30 minute interval. In this case we make
+-- the assumption that if the user used two workspaces for 15 minutes,
+-- they did so sequentially, thus we sum the usage up to a maximum of
+-- 30 minutes with LEAST(SUM(n), 30).
+WITH
+	insights AS (
+		SELECT
+			user_id,
+			-- See motivation in GetTemplateInsights for LEAST(SUM(n), 30).
+			LEAST(SUM(usage_mins), 30) AS usage_mins,
+			LEAST(SUM(ssh_mins), 30) AS ssh_mins,
+			LEAST(SUM(sftp_mins), 30) AS sftp_mins,
+			LEAST(SUM(reconnecting_pty_mins), 30) AS reconnecting_pty_mins,
+			LEAST(SUM(vscode_mins), 30) AS vscode_mins,
+			LEAST(SUM(jetbrains_mins), 30) AS jetbrains_mins
+		FROM
+			template_usage_stats
+		WHERE
+			start_time >= @start_time::timestamptz
+			AND end_time <= @end_time::timestamptz
+			AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
+		GROUP BY
+			start_time, user_id
+	),
+	templates AS (
+		SELECT
+			array_agg(DISTINCT template_id) AS template_ids,
+			array_agg(DISTINCT template_id) FILTER (WHERE ssh_mins > 0) AS ssh_template_ids,
+			array_agg(DISTINCT template_id) FILTER (WHERE sftp_mins > 0) AS sftp_template_ids,
+			array_agg(DISTINCT template_id) FILTER (WHERE reconnecting_pty_mins > 0) AS reconnecting_pty_template_ids,
+			array_agg(DISTINCT template_id) FILTER (WHERE vscode_mins > 0) AS vscode_template_ids,
+			array_agg(DISTINCT template_id) FILTER (WHERE jetbrains_mins > 0) AS jetbrains_template_ids
+		FROM
+			template_usage_stats
+		WHERE
+			start_time >= @start_time::timestamptz
+			AND end_time <= @end_time::timestamptz
+			AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
+	)
 
 SELECT
-	COALESCE((SELECT ids FROM template_ids), '{}')::uuid[] AS template_ids,
-	-- Return IDs so we can combine this with GetTemplateAppInsights.
-	COALESCE(array_agg(DISTINCT user_id), '{}')::uuid[] AS active_user_ids,
-	COALESCE(SUM(usage_vscode_seconds), 0)::bigint AS usage_vscode_seconds,
-	COALESCE(SUM(usage_jetbrains_seconds), 0)::bigint AS usage_jetbrains_seconds,
-	COALESCE(SUM(usage_reconnecting_pty_seconds), 0)::bigint AS usage_reconnecting_pty_seconds,
-	COALESCE(SUM(usage_ssh_seconds), 0)::bigint AS usage_ssh_seconds
-FROM agent_stats_by_interval_and_user;
+	COALESCE((SELECT template_ids FROM templates), '{}')::uuid[] AS template_ids, -- Includes app usage.
+	COALESCE((SELECT ssh_template_ids FROM templates), '{}')::uuid[] AS ssh_template_ids,
+	COALESCE((SELECT sftp_template_ids FROM templates), '{}')::uuid[] AS sftp_template_ids,
+	COALESCE((SELECT reconnecting_pty_template_ids FROM templates), '{}')::uuid[] AS reconnecting_pty_template_ids,
+	COALESCE((SELECT vscode_template_ids FROM templates), '{}')::uuid[] AS vscode_template_ids,
+	COALESCE((SELECT jetbrains_template_ids FROM templates), '{}')::uuid[] AS jetbrains_template_ids,
+	COALESCE(COUNT(DISTINCT user_id), 0)::bigint AS active_users, -- Includes app usage.
+	COALESCE(SUM(usage_mins) * 60, 0)::bigint AS usage_total_seconds, -- Includes app usage.
+	COALESCE(SUM(ssh_mins) * 60, 0)::bigint AS usage_ssh_seconds,
+	COALESCE(SUM(sftp_mins) * 60, 0)::bigint AS usage_sftp_seconds,
+	COALESCE(SUM(reconnecting_pty_mins) * 60, 0)::bigint AS usage_reconnecting_pty_seconds,
+	COALESCE(SUM(vscode_mins) * 60, 0)::bigint AS usage_vscode_seconds,
+	COALESCE(SUM(jetbrains_mins) * 60, 0)::bigint AS usage_jetbrains_seconds
+FROM
+	insights;
 
 -- name: GetTemplateInsightsByTemplate :many
 WITH agent_stats_by_interval_and_user AS (

diff --git a/coderd/insights.go b/coderd/insights.go
@@ -395,8 +395,8 @@ func (api *API) insightsTemplates(rw http.ResponseWriter, r *http.Request) {
 		resp.Report = &codersdk.TemplateInsightsReport{
 			StartTime:       startTime,
 			EndTime:         endTime,
-			TemplateIDs:     convertTemplateInsightsTemplateIDs(usage, appUsage),
-			ActiveUsers:     convertTemplateInsightsActiveUsers(usage, appUsage),
+			TemplateIDs:     usage.TemplateIDs,
+			ActiveUsers:     usage.ActiveUsers,
 			AppsUsage:       convertTemplateInsightsApps(usage, appUsage),
 			ParametersUsage: parametersUsage,
 		}
@@ -416,55 +416,22 @@ func (api *API) insightsTemplates(rw http.ResponseWriter, r *http.Request) {
 	httpapi.Write(ctx, rw, http.StatusOK, resp)
 }
 
-func convertTemplateInsightsTemplateIDs(usage database.GetTemplateInsightsRow, appUsage []database.GetTemplateAppInsightsRow) []uuid.UUID {
-	templateIDSet := make(map[uuid.UUID]struct{})
-	for _, id := range usage.TemplateIDs {
-		templateIDSet[id] = struct{}{}
-	}
-	for _, app := range appUsage {
-		for _, id := range app.TemplateIDs {
-			templateIDSet[id] = struct{}{}
-		}
-	}
-	templateIDs := make([]uuid.UUID, 0, len(templateIDSet))
-	for id := range templateIDSet {
-		templateIDs = append(templateIDs, id)
-	}
-	slices.SortFunc(templateIDs, func(a, b uuid.UUID) int {
-		return slice.Ascending(a.String(), b.String())
-	})
-	return templateIDs
-}
-
-func convertTemplateInsightsActiveUsers(usage database.GetTemplateInsightsRow, appUsage []database.GetTemplateAppInsightsRow) int64 {
-	activeUserIDSet := make(map[uuid.UUID]struct{})
-	for _, id := range usage.ActiveUserIDs {
-		activeUserIDSet[id] = struct{}{}
-	}
-	for _, app := range appUsage {
-		for _, id := range app.ActiveUserIDs {
-			activeUserIDSet[id] = struct{}{}
-		}
-	}
-	return int64(len(activeUserIDSet))
-}
-
 // convertTemplateInsightsApps builds the list of builtin apps and template apps
 // from the provided database rows, builtin apps are implicitly a part of all
 // templates.
 func convertTemplateInsightsApps(usage database.GetTemplateInsightsRow, appUsage []database.GetTemplateAppInsightsRow) []codersdk.TemplateAppUsage {
 	// Builtin apps.
 	apps := []codersdk.TemplateAppUsage{
 		{
-			TemplateIDs: usage.TemplateIDs,
+			TemplateIDs: usage.VscodeTemplateIds,
 			Type:        codersdk.TemplateAppsTypeBuiltin,
 			DisplayName: codersdk.TemplateBuiltinAppDisplayNameVSCode,
 			Slug:        "vscode",
 			Icon:        "/icon/code.svg",
 			Seconds:     usage.UsageVscodeSeconds,
 		},
 		{
-			TemplateIDs: usage.TemplateIDs,
+			TemplateIDs: usage.JetbrainsTemplateIds,
 			Type:        codersdk.TemplateAppsTypeBuiltin,
 			DisplayName: codersdk.TemplateBuiltinAppDisplayNameJetBrains,
 			Slug:        "jetbrains",
@@ -478,15 +445,15 @@ func convertTemplateInsightsApps(usage database.GetTemplateInsightsRow, appUsage
 		// condition finding the corresponding app entry in appUsage is:
 		// !app.IsApp && app.AccessMethod == "terminal" && app.SlugOrPort == ""
 		{
-			TemplateIDs: usage.TemplateIDs,
+			TemplateIDs: usage.ReconnectingPtyTemplateIds,
 			Type:        codersdk.TemplateAppsTypeBuiltin,
 			DisplayName: codersdk.TemplateBuiltinAppDisplayNameWebTerminal,
 			Slug:        "reconnecting-pty",
 			Icon:        "/icon/terminal.svg",
 			Seconds:     usage.UsageReconnectingPtySeconds,
 		},
 		{
-			TemplateIDs: usage.TemplateIDs,
+			TemplateIDs: usage.SshTemplateIds,
 			Type:        codersdk.TemplateAppsTypeBuiltin,
 			DisplayName: codersdk.TemplateBuiltinAppDisplayNameSSH,
 			Slug:        "ssh",

diff --git a/...a/insights/template/multiple_users_and_workspaces_three_weeks_second_template.json.golden b/...a/insights/template/multiple_users_and_workspaces_three_weeks_second_template.json.golden
@@ -18,9 +18,7 @@
         "seconds": 3600
       },
       {
-        "template_ids": [
-          "00000000-0000-0000-0000-000000000002"
-        ],
+        "template_ids": [],
         "type": "builtin",
         "display_name": "JetBrains",
         "slug": "jetbrains",

diff --git a/...emplate/multiple_users_and_workspaces_three_weeks_second_template_only_report.json.golden b/...emplate/multiple_users_and_workspaces_three_weeks_second_template_only_report.json.golden
@@ -18,9 +18,7 @@
         "seconds": 3600
       },
       {
-        "template_ids": [
-          "00000000-0000-0000-0000-000000000002"
-        ],
+        "template_ids": [],
         "type": "builtin",
         "display_name": "JetBrains",
         "slug": "jetbrains",