Skip to content

Commit c9fe7c5

Browse files
mafredrimafredri
committed
feat(coderd/database): add template_usage_stats table and rollup query
1 parent c92ceff commit c9fe7c5

14 files changed

+838
-9
lines changed

‎coderd/database/dbauthz/dbauthz.go

+30
Original file line numberDiff line numberDiff line change
@@ -1617,6 +1617,29 @@ func (q *querier) GetTemplateParameterInsights(ctx context.Context, arg database
16171617
return q.db.GetTemplateParameterInsights(ctx, arg)
16181618
}
16191619

1620+
func (q *querier) GetTemplateUsageStats(ctx context.Context, arg database.GetTemplateUsageStatsParams) ([]database.TemplateUsageStat, error) {
1621+
// Used by dbrollup tests, use same safe-guard as other insights endpoints.
1622+
// For auditors, check read template_insights, and fall back to update template.
1623+
if err := q.authorizeContext(ctx, rbac.ActionRead, rbac.ResourceTemplateInsights); err != nil {
1624+
for _, templateID := range arg.TemplateIDs {
1625+
template, err := q.db.GetTemplateByID(ctx, templateID)
1626+
if err != nil {
1627+
return nil, err
1628+
}
1629+
1630+
if err := q.authorizeContext(ctx, rbac.ActionUpdate, template); err != nil {
1631+
return nil, err
1632+
}
1633+
}
1634+
if len(arg.TemplateIDs) == 0 {
1635+
if err := q.authorizeContext(ctx, rbac.ActionUpdate, rbac.ResourceTemplate.All()); err != nil {
1636+
return nil, err
1637+
}
1638+
}
1639+
}
1640+
return q.db.GetTemplateUsageStats(ctx, arg)
1641+
}
1642+
16201643
func (q *querier) GetTemplateVersionByID(ctx context.Context, tvid uuid.UUID) (database.TemplateVersion, error) {
16211644
tv, err := q.db.GetTemplateVersionByID(ctx, tvid)
16221645
if err != nil {
@@ -3413,6 +3436,13 @@ func (q *querier) UpsertTailnetTunnel(ctx context.Context, arg database.UpsertTa
34133436
return q.db.UpsertTailnetTunnel(ctx, arg)
34143437
}
34153438

3439+
func (q *querier) UpsertTemplateUsageStats(ctx context.Context) error {
3440+
if err := q.authorizeContext(ctx, rbac.ActionUpdate, rbac.ResourceSystem); err != nil {
3441+
return err
3442+
}
3443+
return q.db.UpsertTemplateUsageStats(ctx)
3444+
}
3445+
34163446
func (q *querier) UpsertWorkspaceAgentPortShare(ctx context.Context, arg database.UpsertWorkspaceAgentPortShareParams) (database.WorkspaceAgentPortShare, error) {
34173447
workspace, err := q.db.GetWorkspaceByID(ctx, arg.WorkspaceID)
34183448
if err != nil {

‎coderd/database/dbmem/dbmem.go

+37
Original file line numberDiff line numberDiff line change
@@ -162,6 +162,7 @@ type data struct {
162162
templateVersionParameters []database.TemplateVersionParameter
163163
templateVersionVariables []database.TemplateVersionVariable
164164
templates []database.TemplateTable
165+
templateUsageStats []database.TemplateUsageStat
165166
workspaceAgents []database.WorkspaceAgent
166167
workspaceAgentMetadata []database.WorkspaceAgentMetadatum
167168
workspaceAgentLogs []database.WorkspaceAgentLog
@@ -3580,6 +3581,34 @@ func (q *FakeQuerier) GetTemplateParameterInsights(ctx context.Context, arg data
35803581
return rows, nil
35813582
}
35823583

3584+
func (q *FakeQuerier) GetTemplateUsageStats(_ context.Context, arg database.GetTemplateUsageStatsParams) ([]database.TemplateUsageStat, error) {
3585+
err := validateDatabaseType(arg)
3586+
if err != nil {
3587+
return nil, err
3588+
}
3589+
3590+
q.mutex.RLock()
3591+
defer q.mutex.RUnlock()
3592+
3593+
var stats []database.TemplateUsageStat
3594+
for _, stat := range q.templateUsageStats {
3595+
// Exclude all chunks that don't fall exactly within the range.
3596+
if stat.StartTime.Before(arg.StartTime) || stat.EndTime.After(arg.EndTime) {
3597+
continue
3598+
}
3599+
if len(arg.TemplateIDs) > 0 && !slices.Contains(arg.TemplateIDs, stat.TemplateID) {
3600+
continue
3601+
}
3602+
stats = append(stats, stat)
3603+
}
3604+
3605+
if len(stats) == 0 {
3606+
return nil, sql.ErrNoRows
3607+
}
3608+
3609+
return stats, nil
3610+
}
3611+
35833612
func (q *FakeQuerier) GetTemplateVersionByID(ctx context.Context, templateVersionID uuid.UUID) (database.TemplateVersion, error) {
35843613
q.mutex.RLock()
35853614
defer q.mutex.RUnlock()
@@ -7921,6 +7950,14 @@ func (*FakeQuerier) UpsertTailnetTunnel(_ context.Context, arg database.UpsertTa
79217950
return database.TailnetTunnel{}, ErrUnimplemented
79227951
}
79237952

7953+
func (q *FakeQuerier) UpsertTemplateUsageStats(_ context.Context) error {
7954+
q.mutex.Lock()
7955+
defer q.mutex.Unlock()
7956+
7957+
// TODO(mafredri): Implement.
7958+
panic("not implemented")
7959+
}
7960+
79247961
func (q *FakeQuerier) UpsertWorkspaceAgentPortShare(_ context.Context, arg database.UpsertWorkspaceAgentPortShareParams) (database.WorkspaceAgentPortShare, error) {
79257962
err := validateDatabaseType(arg)
79267963
if err != nil {

‎coderd/database/dbmetrics/dbmetrics.go

+14
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/dbmock/dbmock.go

+29
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/dump.sql

+52-1
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/migrations/000202_template_usage_stats.down.sql

+1
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
DROP TABLE template_usage_stats;

‎coderd/database/migrations/000202_template_usage_stats.up.sql

+36
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,36 @@
1+
CREATE TABLE template_usage_stats (
2+
start_time timestamptz NOT NULL,
3+
end_time timestamptz NOT NULL,
4+
template_id uuid NOT NULL,
5+
user_id uuid NOT NULL,
6+
median_latency_ms real NULL,
7+
usage_mins smallint NOT NULL,
8+
ssh_mins smallint NOT NULL,
9+
sftp_mins smallint NOT NULL,
10+
reconnecting_pty_mins smallint NOT NULL,
11+
vscode_mins smallint NOT NULL,
12+
jetbrains_mins smallint NOT NULL,
13+
app_usage_mins jsonb NULL,
14+
15+
PRIMARY KEY (start_time, template_id, user_id)
16+
);
17+
18+
COMMENT ON TABLE template_usage_stats IS 'Records aggregated usage statistics for templates/users. All usage is rounded up to the nearest minute.';
19+
COMMENT ON COLUMN template_usage_stats.start_time IS 'Start time of the usage period.';
20+
COMMENT ON COLUMN template_usage_stats.end_time IS 'End time of the usage period.';
21+
COMMENT ON COLUMN template_usage_stats.template_id IS 'ID of the template being used.';
22+
COMMENT ON COLUMN template_usage_stats.user_id IS 'ID of the user using the template.';
23+
COMMENT ON COLUMN template_usage_stats.median_latency_ms IS 'Median latency the user is experiencing, in milliseconds. Null means no value was recorded.';
24+
COMMENT ON COLUMN template_usage_stats.usage_mins IS 'Total minutes the user has been using the template.';
25+
COMMENT ON COLUMN template_usage_stats.ssh_mins IS 'Total minutes the user has been using SSH.';
26+
COMMENT ON COLUMN template_usage_stats.sftp_mins IS 'Total minutes the user has been using SFTP.';
27+
COMMENT ON COLUMN template_usage_stats.reconnecting_pty_mins IS 'Total minutes the user has been using the reconnecting PTY.';
28+
COMMENT ON COLUMN template_usage_stats.vscode_mins IS 'Total minutes the user has been using VSCode.';
29+
COMMENT ON COLUMN template_usage_stats.jetbrains_mins IS 'Total minutes the user has been using JetBrains.';
30+
COMMENT ON COLUMN template_usage_stats.app_usage_mins IS 'Object with app names as keys and total minutes used as values. Null means no app usage was recorded.';
31+
32+
CREATE UNIQUE INDEX ON template_usage_stats (start_time, template_id, user_id);
33+
CREATE INDEX ON template_usage_stats (start_time DESC);
34+
35+
COMMENT ON INDEX template_usage_stats_start_time_template_id_user_id_idx IS 'Index for primary key.';
36+
COMMENT ON INDEX template_usage_stats_start_time_idx IS 'Index for querying MAX(start_time).';

‎coderd/database/models.go

+28
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/querier.go

+13-8
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

0 commit comments

Comments
 (0)