coder · dannykopping · Aug 21, 2024 · Aug 7, 2024 · Aug 13, 2024 · Aug 13, 2024
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -2793,6 +2793,14 @@ func (q *querier) InsertProvisionerJobLogs(ctx context.Context, arg database.Ins
 	return q.db.InsertProvisionerJobLogs(ctx, arg)
 }
 
+// TODO: We need to create a ProvisionerJob resource type
+func (q *querier) InsertProvisionerJobTimings(ctx context.Context, arg database.InsertProvisionerJobTimingsParams) ([]database.ProvisionerJobTiming, error) {
+	// if err := q.authorizeContext(ctx, policy.ActionCreate, rbac.ResourceSystem); err != nil {
+	// return nil, err
+	// }
+	return q.db.InsertProvisionerJobTimings(ctx, arg)
+}
+
 func (q *querier) InsertProvisionerKey(ctx context.Context, arg database.InsertProvisionerKeyParams) (database.ProvisionerKey, error) {
 	return insert(q.log, q.auth, rbac.ResourceProvisionerKeys.InOrg(arg.OrganizationID).WithID(arg.ID), q.db.InsertProvisionerKey)(ctx, arg)
 }

diff --git a/coderd/database/dbauthz/dbauthz_test.go b/coderd/database/dbauthz/dbauthz_test.go
@@ -2470,6 +2470,13 @@ func (s *MethodTestSuite) TestSystemFunctions() {
 			JobID: j.ID,
 		}).Asserts( /*rbac.ResourceSystem, policy.ActionCreate*/ )
 	}))
+	s.Run("InsertProvisionerJobTimings", s.Subtest(func(db database.Store, check *expects) {
+		// TODO: we need to create a ProvisionerJob resource
+		j := dbgen.ProvisionerJob(s.T(), db, nil, database.ProvisionerJob{})
+		check.Args(database.InsertProvisionerJobTimingsParams{
+			JobID: j.ID,
+		}).Asserts( /*rbac.ResourceSystem, policy.ActionCreate*/ )
+	}))
 	s.Run("UpsertProvisionerDaemon", s.Subtest(func(db database.Store, check *expects) {
 		org := dbgen.Organization(s.T(), db, database.Organization{})
 		pd := rbac.ResourceProvisionerDaemon.InOrg(org.ID)

diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
@@ -6652,6 +6652,15 @@ func (q *FakeQuerier) InsertProvisionerJobLogs(_ context.Context, arg database.I
 	return logs, nil
 }
 
+func (*FakeQuerier) InsertProvisionerJobTimings(_ context.Context, arg database.InsertProvisionerJobTimingsParams) ([]database.ProvisionerJobTiming, error) {
+	err := validateDatabaseType(arg)
+	if err != nil {
+		return nil, err
+	}
+
+	return nil, nil
+}
+
 func (q *FakeQuerier) InsertProvisionerKey(_ context.Context, arg database.InsertProvisionerKeyParams) (database.ProvisionerKey, error) {
 	err := validateDatabaseType(arg)
 	if err != nil {

diff --git a/coderd/database/dbmetrics/dbmetrics.go b/coderd/database/dbmetrics/dbmetrics.go
diff --git a/coderd/database/dbmock/dbmock.go b/coderd/database/dbmock/dbmock.go
diff --git a/coderd/database/dbtime/dbtime.go b/coderd/database/dbtime/dbtime.go
@@ -9,6 +9,10 @@ func Now() time.Time {
 
 // Time returns a time compatible with Postgres. Postgres only stores dates with
 // microsecond precision.
+// FIXME(dannyk): refactor all calls to Time() to expect the input time to be modified to UTC; there are currently a
+//
+//	few calls whose behavior would change subtly.
+//	See https://github.com/coder/coder/pull/14274#discussion_r1718427461
 func Time(t time.Time) time.Time {
 	return t.Round(time.Microsecond)
 }
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/foreign_key_constraint.go b/coderd/database/foreign_key_constraint.go
diff --git a/coderd/database/migrations/000245_provisioner_job_timings.down.sql b/coderd/database/migrations/000245_provisioner_job_timings.down.sql
@@ -0,0 +1,5 @@
+DROP VIEW IF EXISTS provisioner_job_stats;
+
+DROP TYPE IF EXISTS provisioner_job_timing_stage CASCADE;
+
+DROP TABLE IF EXISTS provisioner_job_timings;
diff --git a/coderd/database/migrations/000245_provisioner_job_timings.up.sql b/coderd/database/migrations/000245_provisioner_job_timings.up.sql
@@ -0,0 +1,45 @@
+CREATE TYPE provisioner_job_timing_stage AS ENUM (
+    'init',
+    'plan',
+    'graph',
+    'apply'
+    );
+
+CREATE TABLE provisioner_job_timings
+(
+    job_id     uuid                         NOT NULL REFERENCES provisioner_jobs (id) ON DELETE CASCADE,
+    started_at timestamp with time zone     not null,
+    ended_at   timestamp with time zone     not null,
+    stage      provisioner_job_timing_stage not null,
+    source     text                         not null,
+    action     text                         not null,
+    resource   text                         not null
+);
+
+CREATE VIEW provisioner_job_stats AS
+SELECT pj.id                                                                                              AS job_id,
+       pj.job_status,
+       wb.workspace_id,
+       pj.worker_id,
+       pj.error,
+       pj.error_code,
+       pj.updated_at,
+       GREATEST(EXTRACT(EPOCH FROM (pj.started_at - pj.created_at)), 0)                                   AS queued_secs,
+       GREATEST(EXTRACT(EPOCH FROM (pj.completed_at - pj.started_at)), 0)                                 AS completion_secs,
+       GREATEST(EXTRACT(EPOCH FROM (pj.canceled_at - pj.started_at)), 0)                                  AS canceled_secs,
+       GREATEST(EXTRACT(EPOCH FROM (
+           MAX(CASE WHEN pjt.stage = 'init'::provisioner_job_timing_stage THEN pjt.ended_at END) -
+           MIN(CASE WHEN pjt.stage = 'init'::provisioner_job_timing_stage THEN pjt.started_at END))), 0)  AS init_secs,
+       GREATEST(EXTRACT(EPOCH FROM (
+           MAX(CASE WHEN pjt.stage = 'plan'::provisioner_job_timing_stage THEN pjt.ended_at END) -
+           MIN(CASE WHEN pjt.stage = 'plan'::provisioner_job_timing_stage THEN pjt.started_at END))), 0)  AS plan_secs,
+       GREATEST(EXTRACT(EPOCH FROM (
+           MAX(CASE WHEN pjt.stage = 'graph'::provisioner_job_timing_stage THEN pjt.ended_at END) -
+           MIN(CASE WHEN pjt.stage = 'graph'::provisioner_job_timing_stage THEN pjt.started_at END))), 0) AS graph_secs,
+       GREATEST(EXTRACT(EPOCH FROM (
+           MAX(CASE WHEN pjt.stage = 'apply'::provisioner_job_timing_stage THEN pjt.ended_at END) -
+           MIN(CASE WHEN pjt.stage = 'apply'::provisioner_job_timing_stage THEN pjt.started_at END))), 0) AS apply_secs
+FROM provisioner_jobs pj
+         JOIN workspace_builds wb ON wb.job_id = pj.id
+         LEFT JOIN provisioner_job_timings pjt ON pjt.job_id = pj.id
+GROUP BY pj.id, wb.workspace_id;
diff --git a/coderd/database/migrations/testdata/fixtures/000245_provisioner_job_timings.up.sql b/coderd/database/migrations/testdata/fixtures/000245_provisioner_job_timings.up.sql
@@ -0,0 +1,13 @@
+INSERT INTO provisioner_job_timings (job_id, started_at, ended_at, stage, source, action, resource)
+VALUES
+	-- Job 1 - init stage
+	('424a58cb-61d6-4627-9907-613c396c4a38', NOW() - INTERVAL '1 hour 55 minutes', NOW() - INTERVAL '1 hour 50 minutes', 'init', 'source1', 'action1', 'resource1'),
+
+	-- Job 1 - plan stage
+	('424a58cb-61d6-4627-9907-613c396c4a38', NOW() - INTERVAL '1 hour 50 minutes', NOW() - INTERVAL '1 hour 40 minutes', 'plan', 'source2', 'action2', 'resource2'),
+
+	-- Job 1 - graph stage
+	('424a58cb-61d6-4627-9907-613c396c4a38', NOW() - INTERVAL '1 hour 40 minutes', NOW() - INTERVAL '1 hour 30 minutes', 'graph', 'source3', 'action3', 'resource3'),
+
+	-- Job 1 - apply stage
+	('424a58cb-61d6-4627-9907-613c396c4a38', NOW() - INTERVAL '1 hour 30 minutes', NOW() - INTERVAL '1 hour 20 minutes', 'apply', 'source4', 'action4', 'resource4');
diff --git a/coderd/database/models.go b/coderd/database/models.go
diff --git a/coderd/database/querier.go b/coderd/database/querier.go