coder · iskhakovt · Sep 11, 2022 · Sep 11, 2022 · Sep 11, 2022 · Sep 12, 2022
diff --git a/coderd/database/databasefake/databasefake.go b/coderd/database/databasefake/databasefake.go
@@ -1102,6 +1102,14 @@ func (q *fakeQuerier) GetTemplateVersionByJobID(_ context.Context, jobID uuid.UU
 	return database.TemplateVersion{}, sql.ErrNoRows
 }
 
+func (q *fakeQuerier) GetTemplatesAverageBuildTime(_ context.Context, _ database.GetTemplatesAverageBuildTimeParams) ([]database.GetTemplatesAverageBuildTimeRow, error) {
+	q.mutex.RLock()
+	defer q.mutex.RUnlock()
+
+	// TODO
+	return nil, nil
+}
+
 func (q *fakeQuerier) GetParameterSchemasByJobID(_ context.Context, jobID uuid.UUID) ([]database.ParameterSchema, error) {
 	q.mutex.RLock()
 	defer q.mutex.RUnlock()

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/templates.sql b/coderd/database/queries/templates.sql
@@ -105,3 +105,58 @@ WHERE
 	id = $1
 RETURNING
 	*;
+
+-- name: GetTemplatesAverageBuildTime :many
+-- Computes average build time for every template.
+-- Only considers last moving_average_size successful builds between start_ts and end_ts.
+-- If a template does not have at least min_completed_job_count such builds, it gets skipped.
+WITH query_with_all_job_count AS (SELECT
+	DISTINCT t.id,
+	AVG(pj.exec_time_sec)
+		OVER(
+			PARTITION BY t.id
+			ORDER BY pj.completed_at
+			ROWS BETWEEN @moving_average_size::integer PRECEDING AND CURRENT ROW)
+		AS avg_build_time_sec,
+	COUNT(*) OVER(PARTITION BY t.id) as job_count
+FROM
+	(SELECT
+		id,
+		active_version_id
+	FROM
+		templates) AS t
+INNER JOIN
+	(SELECT
+		workspace_id,
+		template_version_id,
+		job_id
+	FROM
+		workspace_builds)
+	AS
+		wb
+ON
+	t.id = wb.workspace_id AND t.active_version_id = wb.template_version_id
+INNER JOIN
+	(SELECT
+		id,
+		completed_at,
+		EXTRACT(EPOCH FROM (completed_at - started_at)) AS exec_time_sec
+	FROM
+		provisioner_jobs
+	WHERE
+		(completed_at IS NOT NULL) AND (started_at IS NOT NULL) AND
+		(completed_at >= @start_ts AND completed_at <= @end_ts) AND
+		(canceled_at IS NULL) AND
+		((error IS NULL) OR (error = '')))
+	AS
+		pj
+ON
+	wb.job_id = pj.id)
+SELECT
+	id,
+	avg_build_time_sec
+FROM
+	query_with_all_job_count
+WHERE
+	job_count >= @min_completed_job_count::integer
+;
diff --git a/coderd/metricscache/metricscache.go b/coderd/metricscache/metricscache.go
@@ -2,6 +2,8 @@ package metricscache
 
 import (
 	"context"
+	"database/sql"
+	"strconv"
 	"sync/atomic"
 	"time"
 
@@ -17,7 +19,7 @@ import (
 	"github.com/coder/retry"
 )
 
-// Cache holds the template DAU cache.
+// Cache holds the template metrics.
 // The aggregation queries responsible for these values can take up to a minute
 // on large deployments. Even in small deployments, aggregation queries can
 // take a few hundred milliseconds, which would ruin page load times and
@@ -26,8 +28,9 @@ type Cache struct {
 	database database.Store
 	log      slog.Logger
 
-	templateDAUResponses atomic.Pointer[map[uuid.UUID]codersdk.TemplateDAUsResponse]
-	templateUniqueUsers  atomic.Pointer[map[uuid.UUID]int]
+	templateDAUResponses        atomic.Pointer[map[uuid.UUID]codersdk.TemplateDAUsResponse]
+	templateUniqueUsers         atomic.Pointer[map[uuid.UUID]int]
+	templateAverageBuildTimeSec atomic.Pointer[map[uuid.UUID]float64]
 
 	done   chan struct{}
 	cancel func()
@@ -116,6 +119,30 @@ func countUniqueUsers(rows []database.GetTemplateDAUsRow) int {
 	return len(seen)
 }
 
+func (c *Cache) computeAverageBuildTime(ctx context.Context, now time.Time) (map[uuid.UUID]float64, error) {
+	records, err := c.database.GetTemplatesAverageBuildTime(
+		ctx,
+		database.GetTemplatesAverageBuildTimeParams{
+			StartTs:              sql.NullTime{Time: now.Add(-time.Hour * 24)},
+			EndTs:                sql.NullTime{Time: now},
+			MinCompletedJobCount: 1,
+			MovingAverageSize:    10,
+		})
+	if err != nil {
+		return nil, err
+	}
+
+	var ret = make(map[uuid.UUID]float64)
+	for _, record := range records {
+		val, err := strconv.ParseFloat(record.AvgBuildTimeSec, 64)
+		if err == nil {
+			return nil, err
+		}
+		ret[record.ID] = val
+	}
+	return ret, nil
+}
+
 func (c *Cache) refresh(ctx context.Context) error {
 	err := c.database.DeleteOldAgentStats(ctx)
 	if err != nil {
@@ -142,6 +169,12 @@ func (c *Cache) refresh(ctx context.Context) error {
 	c.templateDAUResponses.Store(&templateDAUs)
 	c.templateUniqueUsers.Store(&templateUniqueUsers)
 
+	templateAverageBuildTime, err := c.computeAverageBuildTime(ctx, time.Now())
+	if err != nil {
+		return err
+	}
+	c.templateAverageBuildTimeSec.Store(&templateAverageBuildTime)
+
 	return nil
 }
 
@@ -220,3 +253,18 @@ func (c *Cache) TemplateUniqueUsers(id uuid.UUID) (int, bool) {
 	}
 	return resp, true
 }
+
+func (c *Cache) TemplateAverageBuildTimeSec(id uuid.UUID) (float64, bool) {
+	m := c.templateAverageBuildTimeSec.Load()
+	if m == nil {
+		// Data loading.
+		return -1, false
+	}
+
+	resp, ok := (*m)[id]
+	if !ok {
+		// No data or not enough builds.
+		return -1, false
+	}
+	return resp, true
+}
diff --git a/coderd/templates.go b/coderd/templates.go
@@ -759,6 +759,7 @@ func (api *API) convertTemplate(
 	template database.Template, workspaceOwnerCount uint32, createdByName string,
 ) codersdk.Template {
 	activeCount, _ := api.metricsCache.TemplateUniqueUsers(template.ID)
+	averageBuildTimeSec, _ := api.metricsCache.TemplateAverageBuildTimeSec(template.ID)
 	return codersdk.Template{
 		ID:                         template.ID,
 		CreatedAt:                  template.CreatedAt,
@@ -769,6 +770,7 @@ func (api *API) convertTemplate(
 		ActiveVersionID:            template.ActiveVersionID,
 		WorkspaceOwnerCount:        workspaceOwnerCount,
 		ActiveUserCount:            activeCount,
+		AverageBuildTimeSec:        averageBuildTimeSec,
 		Description:                template.Description,
 		Icon:                       template.Icon,
 		MaxTTLMillis:               time.Duration(template.MaxTtl).Milliseconds(),

diff --git a/codersdk/templates.go b/codersdk/templates.go
@@ -24,6 +24,8 @@ type Template struct {
 	WorkspaceOwnerCount uint32          `json:"workspace_owner_count"`
 	// ActiveUserCount is set to -1 when loading.
 	ActiveUserCount            int       `json:"active_user_count"`
+	// AverageBuildTimeSec is set to -1 when there aren't enough recent builds.
+	AverageBuildTimeSec        float64   `json:"average_build_time_sec"`
 	Description                string    `json:"description"`
 	Icon                       string    `json:"icon"`
 	MaxTTLMillis               int64     `json:"max_ttl_ms"`

diff --git a/site/src/api/typesGenerated.ts b/site/src/api/typesGenerated.ts
@@ -379,6 +379,7 @@ export interface Template {
   readonly active_version_id: string
   readonly workspace_owner_count: number
   readonly active_user_count: number
+  readonly average_build_time_sec: number
   readonly description: string
   readonly icon: string
   readonly max_ttl_ms: number

diff --git a/site/src/pages/TemplatesPage/TemplatesPageView.tsx b/site/src/pages/TemplatesPage/TemplatesPageView.tsx
@@ -10,6 +10,7 @@ import KeyboardArrowRight from "@material-ui/icons/KeyboardArrowRight"
 import useTheme from "@material-ui/styles/useTheme"
 import { FC } from "react"
 import { useNavigate } from "react-router-dom"
+import dayjs from "dayjs"
 import { createDayString } from "util/createDayString"
 import { formatTemplateActiveDevelopers } from "util/templates"
 import * as TypesGen from "../../api/typesGenerated"
@@ -34,10 +35,13 @@ import {
 } from "../../components/Tooltips/HelpTooltip/HelpTooltip"
 
 export const Language = {
+  buildTime: (buildTimeSec: number): string =>
+    buildTimeSec === -1 ? "Unknown" : dayjs.duration(buildTimeSec, "seconds").humanize(),
   developerCount: (activeCount: number): string => {
     return `${formatTemplateActiveDevelopers(activeCount)} developer${activeCount !== 1 ? "s" : ""}`
   },
   nameLabel: "Name",
+  buildTimeLabel: "Build time",
   usedByLabel: "Used by",
   lastUpdatedLabel: "Last updated",
   emptyViewNoPerms:
@@ -118,10 +122,11 @@ export const TemplatesPageView: FC<React.PropsWithChildren<TemplatesPageViewProp
         <Table>
           <TableHead>
             <TableRow>
-              <TableCell width="50%">{Language.nameLabel}</TableCell>
-              <TableCell width="16%">{Language.usedByLabel}</TableCell>
-              <TableCell width="16%">{Language.lastUpdatedLabel}</TableCell>
-              <TableCell width="16%">{Language.createdByLabel}</TableCell>
+              <TableCell width="39%">{Language.nameLabel}</TableCell>
+              <TableCell width="15%">{Language.buildTimeLabel}</TableCell>
+              <TableCell width="15%">{Language.usedByLabel}</TableCell>
+              <TableCell width="15%">{Language.lastUpdatedLabel}</TableCell>
+              <TableCell width="15%">{Language.createdByLabel}</TableCell>
               <TableCell width="1%"></TableCell>
             </TableRow>
           </TableHead>
@@ -175,6 +180,12 @@ export const TemplatesPageView: FC<React.PropsWithChildren<TemplatesPageViewProp
                     />
                   </TableCellLink>
 
+                  <TableCellLink to={templatePageLink}>
+                    <span style={{ color: theme.palette.text.secondary }}>
+                      {Language.buildTime(template.average_build_time_sec)}
+                    </span>
+                  </TableCellLink>
+
                   <TableCellLink to={templatePageLink}>
                     <span style={{ color: theme.palette.text.secondary }}>
                       {Language.developerCount(template.active_user_count)}

diff --git a/site/src/testHelpers/entities.ts b/site/src/testHelpers/entities.ts
@@ -177,6 +177,7 @@ export const MockTemplate: TypesGen.Template = {
   active_version_id: MockTemplateVersion.id,
   workspace_owner_count: 2,
   active_user_count: 1,
+  average_build_time_sec: 123,
   description: "This is a test description.",
   max_ttl_ms: 24 * 60 * 60 * 1000,
   min_autostart_interval_ms: 60 * 60 * 1000,