coder · DanielleMaywood · Apr 10, 2025 · Apr 9, 2025 · Apr 9, 2025 · Apr 10, 2025
diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
@@ -3291,6 +3291,7 @@ func (q *FakeQuerier) GetFailedWorkspaceBuildsByTemplateID(ctx context.Context,
 		}
 
 		workspaceBuildStats = append(workspaceBuildStats, database.GetFailedWorkspaceBuildsByTemplateIDRow{
+			WorkspaceID:            w.ID,
 			WorkspaceName:          w.Name,
 			WorkspaceOwnerUsername: workspaceOwner.Username,
 			TemplateVersionName:    templateVersion.Name,

diff --git a/coderd/database/migrations/000316_group_build_failure_notifications.down.sql b/coderd/database/migrations/000316_group_build_failure_notifications.down.sql
@@ -0,0 +1,21 @@
+UPDATE notification_templates
+SET
+	name = 'Report: Workspace Builds Failed For Template',
+	title_template = E'Workspace builds failed for template "{{.Labels.template_display_name}}"',
+    body_template = E'Template **{{.Labels.template_display_name}}** has failed to build {{.Data.failed_builds}}/{{.Data.total_builds}} times over the last {{.Data.report_frequency}}.
+
+**Report:**
+{{range $version := .Data.template_versions}}
+**{{$version.template_version_name}}** failed {{$version.failed_count}} time{{if gt $version.failed_count 1.0}}s{{end}}:
+{{range $build := $version.failed_builds}}
+* [{{$build.workspace_owner_username}} / {{$build.workspace_name}} / #{{$build.build_number}}]({{base_url}}/@{{$build.workspace_owner_username}}/{{$build.workspace_name}}/builds/{{$build.build_number}})
+{{- end}}
+{{end}}
+We recommend reviewing these issues to ensure future builds are successful.',
+        actions = '[
+        {
+            "label": "View workspaces",
+            "url": "{{ base_url }}/workspaces?filter=template%3A{{.Labels.template_name}}"
+        }
+    ]'::jsonb
+WHERE id = '34a20db2-e9cc-4a93-b0e4-8569699d7a00';
diff --git a/coderd/database/migrations/000316_group_build_failure_notifications.up.sql b/coderd/database/migrations/000316_group_build_failure_notifications.up.sql
@@ -0,0 +1,29 @@
+UPDATE notification_templates
+SET
+	name = 'Report: Workspace Builds Failed',
+	title_template = 'Failed workspace builds report',
+	body_template =
+E'The following templates have had build failures over the last {{.Data.report_frequency}}:
+{{range $template := .Data.templates}}
+- **{{$template.display_name}}** failed to build {{$template.failed_builds}}/{{$template.total_builds}} times
+{{end}}
+
+**Report:**
+{{range $template := .Data.templates}}
+**{{$template.display_name}}**
+{{range $version := $template.versions}}
+- **{{$version.template_version_name}}** failed {{$version.failed_count}} time{{if gt $version.failed_count 1.0}}s{{end}}:
+{{range $build := $version.failed_builds}}
+   - [{{$build.workspace_owner_username}} / {{$build.workspace_name}} / #{{$build.build_number}}]({{base_url}}/@{{$build.workspace_owner_username}}/{{$build.workspace_name}}/builds/{{$build.build_number}})
+{{end}}
+{{end}}
+{{end}}
+
+We recommend reviewing these issues to ensure future builds are successful.',
+	actions = '[
+        {
+            "label": "View workspaces",
+            "url": "{{ base_url }}/workspaces?filter={{$first := true}}{{range $template := .Data.templates}}{{range $version := $template.versions}}{{range $build := $version.failed_builds}}{{if not $first}}+{{else}}{{$first = false}}{{end}}id%3A{{$build.workspace_id}}{{end}}{{end}}{{end}}"
+        }
+    ]'::jsonb
+WHERE id = '34a20db2-e9cc-4a93-b0e4-8569699d7a00';
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/workspacebuilds.sql b/coderd/database/queries/workspacebuilds.sql
@@ -213,6 +213,7 @@ SELECT
 	tv.name AS template_version_name,
 	u.username AS workspace_owner_username,
 	w.name AS workspace_name,
+	w.id AS workspace_id,
 	wb.build_number AS workspace_build_number
 FROM
 	workspace_build_with_user AS wb

diff --git a/coderd/notifications/notifications_test.go b/coderd/notifications/notifications_test.go
@@ -978,45 +978,102 @@ func TestNotificationTemplates_Golden(t *testing.T) {
 				UserName:     "Bobby",
 				UserEmail:    "bobby@coder.com",
 				UserUsername: "bobby",
-				Labels: map[string]string{
-					"template_name":         "bobby-first-template",
-					"template_display_name": "Bobby First Template",
-				},
+				Labels:       map[string]string{},
 				// We need to use floats as `json.Unmarshal` unmarshal numbers in `map[string]any` to floats.
 				Data: map[string]any{
-					"failed_builds":    4.0,
-					"total_builds":     55.0,
 					"report_frequency": "week",
-					"template_versions": []map[string]any{
+					"templates": []map[string]any{
 						{
-							"template_version_name": "bobby-template-version-1",
-							"failed_count":          3.0,
-							"failed_builds": []map[string]any{
+							"name":          "bobby-first-template",
+							"display_name":  "Bobby First Template",
+							"failed_builds": 4.0,
+							"total_builds":  55.0,
+							"versions": []map[string]any{
 								{
-									"workspace_owner_username": "mtojek",
-									"workspace_name":           "workspace-1",
-									"build_number":             1234.0,
+									"template_version_name": "bobby-template-version-1",
+									"failed_count":          3.0,
+									"failed_builds": []map[string]any{
+										{
+											"workspace_owner_username": "mtojek",
+											"workspace_name":           "workspace-1",
+											"workspace_id":             "24f5bd8f-1566-4374-9734-c3efa0454dc7",
+											"build_number":             1234.0,
+										},
+										{
+											"workspace_owner_username": "johndoe",
+											"workspace_name":           "my-workspace-3",
+											"workspace_id":             "372a194b-dcde-43f1-b7cf-8a2f3d3114a0",
+											"build_number":             5678.0,
+										},
+										{
+											"workspace_owner_username": "jack",
+											"workspace_name":           "workwork",
+											"workspace_id":             "1386d294-19c1-4351-89e2-6cae1afb9bfe",
+											"build_number":             774.0,
+										},
+									},
 								},
 								{
-									"workspace_owner_username": "johndoe",
-									"workspace_name":           "my-workspace-3",
-									"build_number":             5678.0,
-								},
-								{
-									"workspace_owner_username": "jack",
-									"workspace_name":           "workwork",
-									"build_number":             774.0,
+									"template_version_name": "bobby-template-version-2",
+									"failed_count":          1.0,
+									"failed_builds": []map[string]any{
+										{
+											"workspace_owner_username": "ben",
+											"workspace_name":           "cool-workspace",
+											"workspace_id":             "86fd99b1-1b6e-4b7e-b58e-0aee6e35c159",
+											"build_number":             8888.0,
+										},
+									},
 								},
 							},
 						},
 						{
-							"template_version_name": "bobby-template-version-2",
-							"failed_count":          1.0,
-							"failed_builds": []map[string]any{
+							"name":          "bobby-second-template",
+							"display_name":  "Bobby Second Template",
+							"failed_builds": 5.0,
+							"total_builds":  50.0,
+							"versions": []map[string]any{
+								{
+									"template_version_name": "bobby-template-version-1",
+									"failed_count":          3.0,
+									"failed_builds": []map[string]any{
+										{
+											"workspace_owner_username": "daniellemaywood",
+											"workspace_name":           "workspace-9",
+											"workspace_id":             "cd469690-b6eb-4123-b759-980be7a7b278",
+											"build_number":             9234.0,
+										},
+										{
+											"workspace_owner_username": "johndoe",
+											"workspace_name":           "my-workspace-7",
+											"workspace_id":             "c447d472-0800-4529-a836-788754d5e27d",
+											"build_number":             8678.0,
+										},
+										{
+											"workspace_owner_username": "jack",
+											"workspace_name":           "workworkwork",
+											"workspace_id":             "919db6df-48f0-4dc1-b357-9036a2c40f86",
+											"build_number":             374.0,
+										},
+									},
+								},
 								{
-									"workspace_owner_username": "ben",
-									"workspace_name":           "cool-workspace",
-									"build_number":             8888.0,
+									"template_version_name": "bobby-template-version-2",
+									"failed_count":          2.0,
+									"failed_builds": []map[string]any{
+										{
+											"workspace_owner_username": "ben",
+											"workspace_name":           "more-cool-workspace",
+											"workspace_id":             "c8fb0652-9290-4bf2-a711-71b910243ac2",
+											"build_number":             8878.0,
+										},
+										{
+											"workspace_owner_username": "ben",
+											"workspace_name":           "less-cool-workspace",
+											"workspace_id":             "703d718d-2234-4990-9a02-5b1df6cf462a",
+											"build_number":             8848.0,
+										},
+									},
 								},
 							},
 						},