Skip to content

Commit c25155d

Browse files
mtojekmtojek
committed
WIP
1 parent fc804bd commit c25155d

File tree

9 files changed

+160
-37
lines changed

9 files changed

+160
-37
lines changed

‎coderd/database/dbauthz/dbauthz.go

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -898,7 +898,7 @@ func (q *querier) customRoleCheck(ctx context.Context, role database.CustomRole)
898898
return nil
899899
}
900900

901-
func (q *querier) GetReportGeneratorLogByUserAndKind(ctx context.Context, arg database.GetReportGeneratorLogByUserAndKindParams) (database.ReportGeneratorLog, error) {
901+
func (q *querier) GetWorkspaceBuildStats(ctx context.Context, arg database.GetWorkspaceBuildStatsParams) ([]database.GetWorkspaceBuildStatsRow, error) {
902902
panic("not implemented")
903903
}
904904

@@ -2475,6 +2475,10 @@ func (q *querier) GetWorkspaceBuildParameters(ctx context.Context, workspaceBuil
24752475
return q.db.GetWorkspaceBuildParameters(ctx, workspaceBuildID)
24762476
}
24772477

2478+
func (q *querier) GetWorkspaceBuildStatsByTemplates(ctx context.Context, since time.Time) ([]database.GetWorkspaceBuildStatsByTemplatesRow, error) {
2479+
panic("not implemented")
2480+
}
2481+
24782482
func (q *querier) GetWorkspaceBuildsByWorkspaceID(ctx context.Context, arg database.GetWorkspaceBuildsByWorkspaceIDParams) ([]database.WorkspaceBuild, error) {
24792483
if _, err := q.GetWorkspaceByID(ctx, arg.WorkspaceID); err != nil {
24802484
return nil, err

‎coderd/database/dbmem/dbmem.go

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -991,10 +991,10 @@ func (q *FakeQuerier) getOrganizationByIDNoLock(id uuid.UUID) (database.Organiza
991991
return database.Organization{}, sql.ErrNoRows
992992
}
993993

994-
func (q *FakeQuerier) GetReportGeneratorLogByUserAndKind(ctx context.Context, arg database.GetReportGeneratorLogByUserAndKindParams) (database.ReportGeneratorLog, error) {
994+
func (q *FakeQuerier) GetWorkspaceBuildStats(ctx context.Context, arg database.GetWorkspaceBuildStatsParams) ([]database.GetWorkspaceBuildStatsRow, error) {
995995
err := validateDatabaseType(arg)
996996
if err != nil {
997-
return database.ReportGeneratorLog{}, err
997+
return nil, err
998998
}
999999

10001000
panic("not implemented")
@@ -5823,6 +5823,10 @@ func (q *FakeQuerier) GetWorkspaceBuildParameters(_ context.Context, workspaceBu
58235823
return params, nil
58245824
}
58255825

5826+
func (q *FakeQuerier) GetWorkspaceBuildStatsByTemplates(ctx context.Context, since time.Time) ([]database.GetWorkspaceBuildStatsByTemplatesRow, error) {
5827+
panic("not implemented")
5828+
}
5829+
58265830
func (q *FakeQuerier) GetWorkspaceBuildsByWorkspaceID(_ context.Context,
58275831
params database.GetWorkspaceBuildsByWorkspaceIDParams,
58285832
) ([]database.WorkspaceBuild, error) {

‎coderd/database/dbmetrics/dbmetrics.go

Lines changed: 10 additions & 3 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/dbmock/dbmock.go

Lines changed: 15 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/querier.go

Lines changed: 1 addition & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries.sql.go

Lines changed: 65 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries/notifications.sql

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -190,5 +190,5 @@ INSERT INTO report_generator_logs (user_id, notification_template_id, last_gener
190190
ON CONFLICT (user_id, notification_template_id) DO UPDATE set last_generated_at = $3 WHERE (user_id = $1 AND notification_template_id = $2);
191191

192192
-- name: DeleteOldReportGeneratorLogs :exec
193-
-- Delete report generator logs that have been created at least a <frequency_days> +5m ago.
194-
DELETE FROM report_generator_logs WHERE last_generated_at < (NOW() - CONCAT(@frequency_days::int, ' days')::interval - INTERVAL '5 min');
193+
-- Delete report generator logs that have been created at least a <frequency_days> +1h ago.
194+
DELETE FROM report_generator_logs WHERE last_generated_at < (NOW() - CONCAT(@frequency_days::int, ' days')::interval - INTERVAL '1 hour');

‎coderd/database/queries/workspacebuilds.sql

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -180,6 +180,31 @@ WHERE
180180
AND
181181
pj.completed_at IS NOT NULL;
182182

183+
-- name: GetWorkspaceBuildStatsByTemplates :many
184+
SELECT
185+
w.template_id,
186+
t.name AS template_name,
187+
t.display_name AS template_display_name,
188+
t.organization_id AS template_organization_id,
189+
COUNT(*) AS total_builds,
190+
COUNT(CASE WHEN pj.job_status = 'failed' THEN 1 END) AS failed_builds
191+
FROM
192+
workspace_build_with_user AS wb
193+
JOIN
194+
workspaces AS w ON
195+
wb.workspace_id = w.id
196+
JOIN
197+
provisioner_jobs AS pj ON
198+
wb.job_id = pj.id
199+
JOIN
200+
templates AS t ON
201+
w.template_id = t.id
202+
WHERE
203+
wb.created_at > @since
204+
AND pj.completed_at IS NOT NULL
205+
GROUP BY
206+
w.template_id, template_name, template_display_name, template_organization_id;
207+
183208
-- name: GetFailedWorkspaceBuildsByTemplateID :many
184209
SELECT
185210
wb.*

‎coderd/notifications/reports/generator.go

Lines changed: 31 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -12,18 +12,18 @@ import (
1212

1313
"cdr.dev/slog"
1414

15+
"github.com/coder/quartz"
1516
"github.com/google/uuid"
1617

1718
"github.com/coder/coder/v2/coderd/database"
1819
"github.com/coder/coder/v2/coderd/database/dbauthz"
1920
"github.com/coder/coder/v2/coderd/database/dbtime"
2021
"github.com/coder/coder/v2/coderd/notifications"
2122
"github.com/coder/coder/v2/codersdk"
22-
"github.com/coder/quartz"
2323
)
2424

2525
const (
26-
delay = 5 * time.Minute
26+
delay = 15 * time.Minute
2727
)
2828

2929
func NewReportGenerator(ctx context.Context, logger slog.Logger, db database.Store, enqueur notifications.Enqueuer, clk quartz.Clock) io.Closer {
@@ -99,30 +99,32 @@ func (i *reportGenerator) Close() error {
9999
func reportFailedWorkspaceBuilds(ctx context.Context, logger slog.Logger, db database.Store, enqueuer notifications.Enqueuer, clk quartz.Clock) error {
100100
const frequencyDays = 7
101101

102-
templates, err := db.GetTemplatesWithFilter(ctx, database.GetTemplatesWithFilterParams{
103-
Deleted: false,
104-
Deprecated: sql.NullBool{Bool: false, Valid: true},
105-
})
102+
statsRows, err := db.GetWorkspaceBuildStatsByTemplates(ctx, dbtime.Time(clk.Now()).UTC())
106103
if err != nil {
107-
return xerrors.Errorf("unable to fetch active templates: %w", err)
104+
return xerrors.Errorf("unable to fetch failed workspace builds: %w", err)
108105
}
109106

110-
for _, template := range templates {
111-
failedBuilds, err := db.GetFailedWorkspaceBuildsByTemplateID(ctx, database.GetFailedWorkspaceBuildsByTemplateIDParams{
112-
TemplateID: template.ID,
113-
Since: dbtime.Time(clk.Now()).UTC(),
114-
})
115-
if err != nil {
116-
logger.Error(ctx, "unable to fetch failed workspace builds", slog.F("template_id", template.ID), slog.Error(err))
117-
continue
118-
}
119-
120-
// TODO Lazy-render the report.
107+
for _, stats := range statsRows {
108+
var failedBuilds []database.WorkspaceBuild
121109
reportData := map[string]any{}
122110

123-
templateAdmins, err := findTemplateAdmins(ctx, db, template)
111+
if stats.FailedBuilds > 0 {
112+
failedBuilds, err = db.GetFailedWorkspaceBuildsByTemplateID(ctx, database.GetFailedWorkspaceBuildsByTemplateIDParams{
113+
TemplateID: stats.TemplateID,
114+
Since: dbtime.Time(clk.Now()).UTC(),
115+
})
116+
if err != nil {
117+
logger.Error(ctx, "unable to fetch failed workspace builds", slog.F("template_id", template.ID), slog.Error(err))
118+
continue
119+
}
120+
121+
// TODO Lazy-render the report.
122+
reportData = map[string]any{}
123+
}
124+
125+
templateAdmins, err := findTemplateAdmins(ctx, db, stats)
124126
if err != nil {
125-
logger.Error(ctx, "unable to find template admins", slog.F("template_id", template.ID), slog.Error(err))
127+
logger.Error(ctx, "unable to find template admins", slog.F("template_id", stats.TemplateID), slog.Error(err))
126128
continue
127129
}
128130

@@ -145,7 +147,7 @@ func reportFailedWorkspaceBuilds(ctx context.Context, logger slog.Logger, db dat
145147
LastGeneratedAt: dbtime.Time(clk.Now()).UTC(),
146148
})
147149
if err != nil {
148-
logger.Error(ctx, "unable to update report generator logs", slog.F("template_id", template.ID), slog.F("user_id", templateAdmin.ID), slog.F("failed_builds", len(failedBuilds)), slog.Error(err))
150+
logger.Error(ctx, "unable to update report generator logs", slog.F("template_id", stats.TemplateID), slog.F("user_id", templateAdmin.ID), slog.F("failed_builds", len(failedBuilds)), slog.Error(err))
149151
continue
150152
}
151153
}
@@ -158,24 +160,24 @@ func reportFailedWorkspaceBuilds(ctx context.Context, logger slog.Logger, db dat
158160
LastGeneratedAt: dbtime.Time(clk.Now()).UTC(),
159161
})
160162
if err != nil {
161-
logger.Error(ctx, "unable to update report generator logs", slog.F("template_id", template.ID), slog.F("user_id", templateAdmin.ID), slog.F("failed_builds", len(failedBuilds)), slog.Error(err))
163+
logger.Error(ctx, "unable to update report generator logs", slog.F("template_id", stats.TemplateID), slog.F("user_id", templateAdmin.ID), slog.F("failed_builds", len(failedBuilds)), slog.Error(err))
162164
continue
163165
}
164166
}
165167

166-
templateDisplayName := template.DisplayName
168+
templateDisplayName := stats.TemplateDisplayName
167169
if templateDisplayName == "" {
168-
templateDisplayName = template.Name
170+
templateDisplayName = stats.TemplateName
169171
}
170172

171173
if _, err := enqueuer.EnqueueData(ctx, templateAdmin.ID, notifications.TemplateWorkspaceBuildsFailedReport,
172174
map[string]string{
173-
"template_name": template.Name,
175+
"template_name": stats.TemplateName,
174176
"template_display_name": templateDisplayName,
175177
},
176178
reportData,
177179
"report_generator",
178-
template.ID, template.OrganizationID,
180+
stats.TemplateID, stats.TemplateOrganizationID,
179181
); err != nil {
180182
logger.Warn(ctx, "failed to send a report with failed workspace builds", slog.Error(err))
181183
}
@@ -186,7 +188,7 @@ func reportFailedWorkspaceBuilds(ctx context.Context, logger slog.Logger, db dat
186188
LastGeneratedAt: dbtime.Time(clk.Now()).UTC(),
187189
})
188190
if err != nil {
189-
logger.Error(ctx, "unable to update report generator logs", slog.F("template_id", template.ID), slog.F("user_id", templateAdmin.ID), slog.F("failed_builds", len(failedBuilds)), slog.Error(err))
191+
logger.Error(ctx, "unable to update report generator logs", slog.F("template_id", stats.TemplateID), slog.F("user_id", templateAdmin.ID), slog.F("failed_builds", len(failedBuilds)), slog.Error(err))
190192
continue
191193
}
192194
}
@@ -206,7 +208,7 @@ func buildDataForReportFailedWorkspaceBuilds() map[string]any {
206208
return reportData
207209
}
208210

209-
func findTemplateAdmins(ctx context.Context, db database.Store, template database.Template) ([]database.GetUsersRow, error) {
211+
func findTemplateAdmins(ctx context.Context, db database.Store, stats database.GetWorkspaceBuildStatsByTemplatesRow) ([]database.GetUsersRow, error) {
210212
users, err := db.GetUsers(ctx, database.GetUsersParams{
211213
RbacRole: []string{codersdk.RoleTemplateAdmin},
212214
})
@@ -229,7 +231,7 @@ func findTemplateAdmins(ctx context.Context, db database.Store, template databas
229231
}
230232

231233
for _, entry := range orgIDsByMemberIDs {
232-
if slices.Contains(entry.OrganizationIDs, template.OrganizationID) {
234+
if slices.Contains(entry.OrganizationIDs, stats.TemplateOrganizationID) {
233235
templateAdmins = append(templateAdmins, usersByIDs[entry.UserID])
234236
}
235237
}

0 commit comments

Comments
 (0)