coder · ammario · Oct 17, 2022 · Oct 16, 2022 · Oct 16, 2022 · Oct 16, 2022
diff --git a/coderd/database/databasefake/databasefake.go b/coderd/database/databasefake/databasefake.go
@@ -235,33 +235,50 @@ func (q *fakeQuerier) GetTemplateDAUs(_ context.Context, templateID uuid.UUID) (
 	return rs, nil
 }
 
-func (q *fakeQuerier) GetTemplateAverageBuildTime(ctx context.Context, arg database.GetTemplateAverageBuildTimeParams) (float64, error) {
-	var times []float64
+func (q *fakeQuerier) GetTemplateAverageBuildTime(ctx context.Context, arg database.GetTemplateAverageBuildTimeParams) (database.GetTemplateAverageBuildTimeRow, error) {
+	var emptyRow database.GetTemplateAverageBuildTimeRow
+	var (
+		startTimes  []float64
+		stopTimes   []float64
+		deleteTimes []float64
+	)
 	for _, wb := range q.workspaceBuilds {
-		if wb.Transition != database.WorkspaceTransitionStart {
-			continue
-		}
 		version, err := q.GetTemplateVersionByID(ctx, wb.TemplateVersionID)
 		if err != nil {
-			return -1, err
+			return emptyRow, err
 		}
 		if version.TemplateID != arg.TemplateID {
 			continue
 		}
 
 		job, err := q.GetProvisionerJobByID(ctx, wb.JobID)
 		if err != nil {
-			return -1, err
+			return emptyRow, err
 		}
 		if job.CompletedAt.Valid {
-			times = append(times, job.CompletedAt.Time.Sub(job.StartedAt.Time).Seconds())
+			took := job.CompletedAt.Time.Sub(job.StartedAt.Time).Seconds()
+			if wb.Transition == database.WorkspaceTransitionStart {
+				startTimes = append(startTimes, took)
+			} else if wb.Transition == database.WorkspaceTransitionStop {
+				stopTimes = append(stopTimes, took)
+			} else if wb.Transition == database.WorkspaceTransitionDelete {
+				deleteTimes = append(deleteTimes, took)
+			}
 		}
 	}
-	sort.Float64s(times)
-	if len(times) == 0 {
-		return -1, nil
+
+	tryMedian := func(fs []float64) float64 {
+		if len(fs) == 0 {
+			return -1
+		}
+		sort.Float64s(fs)
+		return fs[len(fs)/2]
 	}
-	return times[len(times)/2], nil
+	var row database.GetTemplateAverageBuildTimeRow
+	row.DeleteMedian = tryMedian(deleteTimes)
+	row.StopMedian = tryMedian(stopTimes)
+	row.StartMedian = tryMedian(startTimes)
+	return row, nil
 }
 
 func (q *fakeQuerier) ParameterValue(_ context.Context, id uuid.UUID) (database.ParameterValue, error) {

diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000059_file_id.up.sql b/coderd/database/migrations/000059_file_id.up.sql
@@ -4,8 +4,8 @@
 -- template to be able to push and read files used for template
 -- versions they create.
 -- Prior to this collisions on file.hash were not an issue
--- since users who could push files could also read all files. 
--- 
+-- since users who could push files could also read all files.
+--
 -- This migration also adds a 'files.id' column as the primary
 -- key. As a side effect the provisioner_jobs must now reference
 -- the files.id column since the 'hash' column is now ambiguous.
@@ -14,10 +14,13 @@ BEGIN;
 -- Drop the primary key on hash.
 ALTER TABLE files DROP CONSTRAINT files_pkey;
 
+-- This extension is required by gen_random_uuid
+CREATE EXTENSION IF NOT EXISTS pgcrypto;
+
 -- Add an 'id' column and designate it the primary key.
-ALTER TABLE files ADD COLUMN 
+ALTER TABLE files ADD COLUMN
 	id uuid NOT NULL PRIMARY KEY DEFAULT gen_random_uuid ();
-    
+
 -- Update the constraint to include the user who created it.
 ALTER TABLE files ADD UNIQUE(hash, created_by);
 

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
@@ -109,7 +109,8 @@ RETURNING
 -- name: GetTemplateAverageBuildTime :one
 WITH build_times AS (
 SELECT
-	EXTRACT(EPOCH FROM (pj.completed_at - pj.started_at))::FLOAT AS exec_time_sec
+	EXTRACT(EPOCH FROM (pj.completed_at - pj.started_at))::FLOAT AS exec_time_sec,
+	workspace_builds.transition
 FROM
 	workspace_builds
 JOIN template_versions ON
@@ -118,14 +119,18 @@ JOIN provisioner_jobs pj ON
 	workspace_builds.job_id = pj.id
 WHERE
 	template_versions.template_id = @template_id AND
-		(workspace_builds.transition = 'start') AND
 		(pj.completed_at IS NOT NULL) AND (pj.started_at IS NOT NULL) AND
 		(pj.started_at > @start_time) AND
 		(pj.canceled_at IS NULL) AND
 		((pj.error IS NULL) OR (pj.error = ''))
 ORDER BY
 	workspace_builds.created_at DESC
 )
-SELECT coalesce((PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY exec_time_sec)), -1)::FLOAT
+SELECT
+	-- Postgres offers no clear way to DRY this short of a function or other
+	-- complexities.
+	coalesce((PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY exec_time_sec) FILTER (WHERE transition = 'start')), -1)::FLOAT AS start_median,
+	coalesce((PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY exec_time_sec) FILTER (WHERE transition = 'stop')), -1)::FLOAT AS stop_median,
+	coalesce((PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY exec_time_sec) FILTER (WHERE transition = 'delete')), -1)::FLOAT AS delete_median
 FROM build_times
 ;
diff --git a/coderd/metricscache/metricscache.go b/coderd/metricscache/metricscache.go
@@ -29,7 +29,7 @@ type Cache struct {
 
 	templateDAUResponses     atomic.Pointer[map[uuid.UUID]codersdk.TemplateDAUsResponse]
 	templateUniqueUsers      atomic.Pointer[map[uuid.UUID]int]
-	templateAverageBuildTime atomic.Pointer[map[uuid.UUID]time.Duration]
+	templateAverageBuildTime atomic.Pointer[map[uuid.UUID]database.GetTemplateAverageBuildTimeRow]
 
 	done   chan struct{}
 	cancel func()
@@ -130,9 +130,9 @@ func (c *Cache) refresh(ctx context.Context) error {
 	}
 
 	var (
-		templateDAUs                = make(map[uuid.UUID]codersdk.TemplateDAUsResponse, len(templates))
-		templateUniqueUsers         = make(map[uuid.UUID]int)
-		templateAverageBuildTimeSec = make(map[uuid.UUID]time.Duration)
+		templateDAUs              = make(map[uuid.UUID]codersdk.TemplateDAUsResponse, len(templates))
+		templateUniqueUsers       = make(map[uuid.UUID]int)
+		templateAverageBuildTimes = make(map[uuid.UUID]database.GetTemplateAverageBuildTimeRow)
 	)
 	for _, template := range templates {
 		rows, err := c.database.GetTemplateDAUs(ctx, template.ID)
@@ -141,6 +141,7 @@ func (c *Cache) refresh(ctx context.Context) error {
 		}
 		templateDAUs[template.ID] = convertDAUResponse(rows)
 		templateUniqueUsers[template.ID] = countUniqueUsers(rows)
+
 		templateAvgBuildTime, err := c.database.GetTemplateAverageBuildTime(ctx, database.GetTemplateAverageBuildTimeParams{
 			TemplateID: uuid.NullUUID{
 				UUID:  template.ID,
@@ -151,14 +152,15 @@ func (c *Cache) refresh(ctx context.Context) error {
 				Valid: true,
 			},
 		})
+
 		if err != nil {
 			return err
 		}
-		templateAverageBuildTimeSec[template.ID] = time.Duration(float64(time.Second) * templateAvgBuildTime)
+		templateAverageBuildTimes[template.ID] = templateAvgBuildTime
 	}
 	c.templateDAUResponses.Store(&templateDAUs)
 	c.templateUniqueUsers.Store(&templateUniqueUsers)
-	c.templateAverageBuildTime.Store(&templateAverageBuildTimeSec)
+	c.templateAverageBuildTime.Store(&templateAverageBuildTimes)
 
 	return nil
 }
@@ -239,17 +241,32 @@ func (c *Cache) TemplateUniqueUsers(id uuid.UUID) (int, bool) {
 	return resp, true
 }
 
-func (c *Cache) TemplateAverageBuildTime(id uuid.UUID) (time.Duration, bool) {
+func (c *Cache) TemplateBuildTimeStats(id uuid.UUID) codersdk.TemplateBuildTimeStats {
+	var unknown codersdk.TemplateBuildTimeStats
+
 	m := c.templateAverageBuildTime.Load()
 	if m == nil {
 		// Data loading.
-		return -1, false
+		return unknown
 	}
 
 	resp, ok := (*m)[id]
-	if !ok || resp <= 0 {
+	if !ok {
 		// No data or not enough builds.
-		return -1, false
+		return unknown
+	}
+
+	convertMedian := func(m float64) *int64 {
+		if m <= 0 {
+			return nil
+		}
+		i := int64(m * 1000)
+		return &i
+	}
+
+	return codersdk.TemplateBuildTimeStats{
+		StartMillis:  convertMedian(resp.StartMedian),
+		StopMillis:   convertMedian(resp.StopMedian),
+		DeleteMillis: convertMedian(resp.DeleteMedian),
 	}
-	return resp, true
 }