Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit4543b21

Browse files
SasSwartmafredri
andauthored
feat(coderd/database): track user status changes over time (#16019)
RE:#15740,#15297In order to add a graph to the coder frontend to show user status overtime as an indicator of license usage, this PR adds the following:* a new `api.insightsUserStatusCountsOverTime` endpoint to the API* which calls a new `GetUserStatusCountsOverTime` query from postgres* which relies on two new tables `user_status_changes` and`user_deleted`* which are populated by a new trigger and function that tracks updatesto the users tableThe chart itself will be added in a subsequent PR---------Co-authored-by: Mathias Fredriksson <mafredri@gmail.com>
1 parent73d8dde commit4543b21

25 files changed

+1456
-3
lines changed

‎Makefile‎

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -521,6 +521,7 @@ lint/markdown: node_modules/.installed
521521
# All files generated by the database should be added here, and this can be used
522522
# as a target for jobs that need to run after the database is generated.
523523
DB_GEN_FILES :=\
524+
coderd/database/dump.sql\
524525
coderd/database/querier.go\
525526
coderd/database/unique_constraint.go\
526527
coderd/database/dbmem/dbmem.go\
@@ -540,8 +541,6 @@ GEN_FILES := \
540541
provisionersdk/proto/provisioner.pb.go\
541542
provisionerd/proto/provisionerd.pb.go\
542543
vpn/vpn.pb.go\
543-
coderd/database/dump.sql\
544-
$(DB_GEN_FILES)\
545544
site/src/api/typesGenerated.ts\
546545
coderd/rbac/object_gen.go\
547546
codersdk/rbacresources_gen.go\
@@ -559,7 +558,7 @@ GEN_FILES := \
559558
coderd/database/pubsub/psmock/psmock.go
560559

561560
# all gen targets should be added here and to gen/mark-fresh
562-
gen:$(GEN_FILES)
561+
gen:gen/db$(GEN_FILES)
563562
.PHONY: gen
564563

565564
gen/db:$(DB_GEN_FILES)

‎coderd/apidoc/docs.go‎

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

‎coderd/apidoc/swagger.json‎

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

‎coderd/coderd.go‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1281,6 +1281,7 @@ func New(options *Options) *API {
12811281
r.Use(apiKeyMiddleware)
12821282
r.Get("/daus",api.deploymentDAUs)
12831283
r.Get("/user-activity",api.insightsUserActivity)
1284+
r.Get("/user-status-counts",api.insightsUserStatusCounts)
12841285
r.Get("/user-latency",api.insightsUserLatency)
12851286
r.Get("/templates",api.insightsTemplates)
12861287
})

‎coderd/database/dbauthz/dbauthz.go‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2421,6 +2421,13 @@ func (q *querier) GetUserNotificationPreferences(ctx context.Context, userID uui
24212421
returnq.db.GetUserNotificationPreferences(ctx,userID)
24222422
}
24232423

2424+
func (q*querier)GetUserStatusCounts(ctx context.Context,arg database.GetUserStatusCountsParams) ([]database.GetUserStatusCountsRow,error) {
2425+
iferr:=q.authorizeContext(ctx,policy.ActionRead,rbac.ResourceUser);err!=nil {
2426+
returnnil,err
2427+
}
2428+
returnq.db.GetUserStatusCounts(ctx,arg)
2429+
}
2430+
24242431
func (q*querier)GetUserWorkspaceBuildParameters(ctx context.Context,params database.GetUserWorkspaceBuildParametersParams) ([]database.GetUserWorkspaceBuildParametersRow,error) {
24252432
u,err:=q.db.GetUserByID(ctx,params.OwnerID)
24262433
iferr!=nil {

‎coderd/database/dbauthz/dbauthz_test.go‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1708,6 +1708,12 @@ func (s *MethodTestSuite) TestUser() {
17081708
rbac.ResourceTemplate.InOrg(orgID),policy.ActionRead,
17091709
)
17101710
}))
1711+
s.Run("GetUserStatusCounts",s.Subtest(func(db database.Store,check*expects) {
1712+
check.Args(database.GetUserStatusCountsParams{
1713+
StartTime:time.Now().Add(-time.Hour*24*30),
1714+
EndTime:time.Now(),
1715+
}).Asserts(rbac.ResourceUser,policy.ActionRead)
1716+
}))
17111717
}
17121718

17131719
func (s*MethodTestSuite)TestWorkspace() {

‎coderd/database/dbmem/dbmem.go‎

Lines changed: 56 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -88,6 +88,7 @@ func New() database.Store {
8888
customRoles:make([]database.CustomRole,0),
8989
locks:map[int64]struct{}{},
9090
runtimeConfig:map[string]string{},
91+
userStatusChanges:make([]database.UserStatusChange,0),
9192
},
9293
}
9394
// Always start with a default org. Matching migration 198.
@@ -256,6 +257,7 @@ type data struct {
256257
lastLicenseIDint32
257258
defaultProxyDisplayNamestring
258259
defaultProxyIconURLstring
260+
userStatusChanges []database.UserStatusChange
259261
}
260262

261263
functryPercentile(fs []float64,pfloat64)float64 {
@@ -5669,6 +5671,42 @@ func (q *FakeQuerier) GetUserNotificationPreferences(_ context.Context, userID u
56695671
returnout,nil
56705672
}
56715673

5674+
func (q*FakeQuerier)GetUserStatusCounts(_ context.Context,arg database.GetUserStatusCountsParams) ([]database.GetUserStatusCountsRow,error) {
5675+
q.mutex.RLock()
5676+
deferq.mutex.RUnlock()
5677+
5678+
err:=validateDatabaseType(arg)
5679+
iferr!=nil {
5680+
returnnil,err
5681+
}
5682+
5683+
result:=make([]database.GetUserStatusCountsRow,0)
5684+
for_,change:=rangeq.userStatusChanges {
5685+
ifchange.ChangedAt.Before(arg.StartTime)||change.ChangedAt.After(arg.EndTime) {
5686+
continue
5687+
}
5688+
date:=time.Date(change.ChangedAt.Year(),change.ChangedAt.Month(),change.ChangedAt.Day(),0,0,0,0,time.UTC)
5689+
if!slices.ContainsFunc(result,func(r database.GetUserStatusCountsRow)bool {
5690+
returnr.Status==change.NewStatus&&r.Date.Equal(date)
5691+
}) {
5692+
result=append(result, database.GetUserStatusCountsRow{
5693+
Status:change.NewStatus,
5694+
Date:date,
5695+
Count:1,
5696+
})
5697+
}else {
5698+
fori,r:=rangeresult {
5699+
ifr.Status==change.NewStatus&&r.Date.Equal(date) {
5700+
result[i].Count++
5701+
break
5702+
}
5703+
}
5704+
}
5705+
}
5706+
5707+
returnresult,nil
5708+
}
5709+
56725710
func (q*FakeQuerier)GetUserWorkspaceBuildParameters(_ context.Context,params database.GetUserWorkspaceBuildParametersParams) ([]database.GetUserWorkspaceBuildParametersRow,error) {
56735711
q.mutex.RLock()
56745712
deferq.mutex.RUnlock()
@@ -8021,6 +8059,12 @@ func (q *FakeQuerier) InsertUser(_ context.Context, arg database.InsertUserParam
80218059
sort.Slice(q.users,func(i,jint)bool {
80228060
returnq.users[i].CreatedAt.Before(q.users[j].CreatedAt)
80238061
})
8062+
8063+
q.userStatusChanges=append(q.userStatusChanges, database.UserStatusChange{
8064+
UserID:user.ID,
8065+
NewStatus:user.Status,
8066+
ChangedAt:user.UpdatedAt,
8067+
})
80248068
returnuser,nil
80258069
}
80268070

@@ -9062,12 +9106,18 @@ func (q *FakeQuerier) UpdateInactiveUsersToDormant(_ context.Context, params dat
90629106
Username:user.Username,
90639107
LastSeenAt:user.LastSeenAt,
90649108
})
9109+
q.userStatusChanges=append(q.userStatusChanges, database.UserStatusChange{
9110+
UserID:user.ID,
9111+
NewStatus:database.UserStatusDormant,
9112+
ChangedAt:params.UpdatedAt,
9113+
})
90659114
}
90669115
}
90679116

90689117
iflen(updated)==0 {
90699118
returnnil,sql.ErrNoRows
90709119
}
9120+
90719121
returnupdated,nil
90729122
}
90739123

@@ -9868,6 +9918,12 @@ func (q *FakeQuerier) UpdateUserStatus(_ context.Context, arg database.UpdateUse
98689918
user.Status=arg.Status
98699919
user.UpdatedAt=arg.UpdatedAt
98709920
q.users[index]=user
9921+
9922+
q.userStatusChanges=append(q.userStatusChanges, database.UserStatusChange{
9923+
UserID:user.ID,
9924+
NewStatus:user.Status,
9925+
ChangedAt:user.UpdatedAt,
9926+
})
98719927
returnuser,nil
98729928
}
98739929
return database.User{},sql.ErrNoRows

‎coderd/database/dbmetrics/querymetrics.go‎

Lines changed: 7 additions & 0 deletions
Some generated files are not rendered by default. Learn more aboutcustomizing 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 aboutcustomizing how changed files appear on GitHub.

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp