Skip to content

feat(site): display user status counts over time as an indicator of license usage #15893

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Closed
wants to merge 25 commits into from
Closed
Show file tree
Hide file tree
Changes from 1 commit
Commits
Show all changes
25 commits
Select commit Hold shift + click to select a range
69e4a09
add user_status_changes table
SasSwart Dec 14, 2024
0913355
add GetUserStatusCountsByDay
SasSwart Dec 14, 2024
d952af0
rename unused variable
SasSwart Dec 14, 2024
34ac634
Test GetUserStatusCountsByDay
SasSwart Dec 17, 2024
d5d3021
make gen
SasSwart Dec 17, 2024
c6b50af
fix dbauthz tests
SasSwart Dec 17, 2024
b2fb346
do the plumbing to get sql, api and frontend talking to one another
SasSwart Dec 23, 2024
ad42c16
rename migration
SasSwart Dec 23, 2024
ed86820
move aggregation logic for GetUserStatusChanges into the SQL
SasSwart Dec 24, 2024
0f17038
use window functions for efficiency
SasSwart Dec 24, 2024
1b3976d
ensure we use the same time zone as the start_time param
SasSwart Dec 24, 2024
e2d0d15
ensure we use the same time zone as the start_time param
SasSwart Dec 24, 2024
7fdd9a5
Merge remote-tracking branch 'origin/main' into jjs/dau-history
SasSwart Dec 24, 2024
aed4132
make gen
SasSwart Dec 24, 2024
9c65856
update field names and fix tests
SasSwart Dec 24, 2024
d6c5a4f
exclude deleted users from the user status graph
SasSwart Dec 27, 2024
d1c5baf
Merge remote-tracking branch 'origin/main' into jjs/dau-history
SasSwart Dec 30, 2024
fa66382
GetUserStatusChanges now passes all querier tests
SasSwart Jan 2, 2025
a8c125c
renumber migrations
SasSwart Jan 2, 2025
726bcba
add partial fixture for CI
SasSwart Jan 3, 2025
8bcbe03
fix migration numbers
SasSwart Jan 3, 2025
494f165
rename and document sql function
SasSwart Jan 3, 2025
dbebf0b
revert backend changes for now. so that we can review and merge the d…
SasSwart Jan 3, 2025
e85bc1f
Merge remote-tracking branch 'origin/main' into jjs/dau-history-backend
SasSwart Jan 3, 2025
e738a0e
make gen
SasSwart Jan 3, 2025
File filter

Filter by extension

Filter by extension

Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
Prev Previous commit
Next Next commit
add GetUserStatusCountsByDay
  • Loading branch information
SasSwart committed Dec 14, 2024
commit 0913355b8b9c8ee3d9eb525dc926683946b00d06
7 changes: 7 additions & 0 deletions coderd/database/dbauthz/dbauthz.go
Original file line number Diff line number Diff line change
Expand Up @@ -2413,6 +2413,13 @@ func (q *querier) GetUserNotificationPreferences(ctx context.Context, userID uui
return q.db.GetUserNotificationPreferences(ctx, userID)
}

func (q *querier) GetUserStatusCountsByDay(ctx context.Context, arg database.GetUserStatusCountsByDayParams) ([]database.GetUserStatusCountsByDayRow, error) {
if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceSystem); err != nil {
return nil, err
}
return q.db.GetUserStatusCountsByDay(ctx, arg)
}

func (q *querier) GetUserWorkspaceBuildParameters(ctx context.Context, params database.GetUserWorkspaceBuildParametersParams) ([]database.GetUserWorkspaceBuildParametersRow, error) {
u, err := q.db.GetUserByID(ctx, params.OwnerID)
if err != nil {
Expand Down
9 changes: 9 additions & 0 deletions coderd/database/dbmem/dbmem.go
Original file line number Diff line number Diff line change
Expand Up @@ -5664,6 +5664,15 @@ func (q *FakeQuerier) GetUserNotificationPreferences(_ context.Context, userID u
return out, nil
}

func (q *FakeQuerier) GetUserStatusCountsByDay(ctx context.Context, arg database.GetUserStatusCountsByDayParams) ([]database.GetUserStatusCountsByDayRow, error) {
err := validateDatabaseType(arg)
if err != nil {
return nil, err
}

panic("not implemented")
}

func (q *FakeQuerier) GetUserWorkspaceBuildParameters(_ context.Context, params database.GetUserWorkspaceBuildParametersParams) ([]database.GetUserWorkspaceBuildParametersRow, error) {
q.mutex.RLock()
defer q.mutex.RUnlock()
Expand Down
7 changes: 7 additions & 0 deletions coderd/database/dbmetrics/querymetrics.go

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

15 changes: 15 additions & 0 deletions coderd/database/dbmock/dbmock.go

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

1 change: 1 addition & 0 deletions coderd/database/querier.go

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

103 changes: 103 additions & 0 deletions coderd/database/queries.sql.go

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

68 changes: 68 additions & 0 deletions coderd/database/queries/insights.sql
Original file line number Diff line number Diff line change
Expand Up @@ -771,3 +771,71 @@ SELECT
FROM unique_template_params utp
JOIN workspace_build_parameters wbp ON (utp.workspace_build_ids @> ARRAY[wbp.workspace_build_id] AND utp.name = wbp.name)
GROUP BY utp.num, utp.template_ids, utp.name, utp.type, utp.display_name, utp.description, utp.options, wbp.value;

-- name: GetUserStatusCountsByDay :many
WITH dates AS (
-- Generate a series of dates between start and end
SELECT
day::date
FROM
generate_series(
date_trunc('day', @start_time::timestamptz),
date_trunc('day', @end_time::timestamptz),
'1 day'::interval
) AS day
),
initial_statuses AS (
-- Get the status of each user right before the start date
SELECT DISTINCT ON (user_id)
user_id,
new_status as status
FROM
user_status_changes
WHERE
changed_at < @start_time::timestamptz
ORDER BY
user_id,
changed_at DESC
),
relevant_changes AS (
-- Get only the status changes within our date range
SELECT
date_trunc('day', changed_at)::date AS day,
user_id,
new_status as status
FROM
user_status_changes
WHERE
changed_at >= @start_time::timestamptz
AND changed_at <= @end_time::timestamptz
),
daily_status AS (
-- Combine initial statuses with changes
SELECT
d.day,
COALESCE(rc.status, i.status) as status,
COALESCE(rc.user_id, i.user_id) as user_id
FROM
dates d
CROSS JOIN
initial_statuses i
LEFT JOIN
relevant_changes rc
ON
rc.day = d.day
AND rc.user_id = i.user_id
)
SELECT
day,
status,
COUNT(*) AS count
FROM
daily_status
WHERE
status IS NOT NULL
GROUP BY
day,
status
ORDER BY
day ASC,
status ASC;