Skip to content

feat(coderd/database): track user status changes over time #16019

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.

Sign up for GitHub

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

Merged
merged 32 commits into from
Jan 13, 2025
Merged

feat(coderd/database): track user status changes over time #16019

Changes from 1 commit
Commits
Show all changes
32 commits
Select commit Hold shift + click to select a range
cd953a3
add user_status_changes table
SasSwart Dec 14, 2024
ec16728
add GetUserStatusCountsByDay
SasSwart Dec 14, 2024
0d97e82
rename unused variable
SasSwart Dec 14, 2024
eb6e249
Test GetUserStatusCountsByDay
SasSwart Dec 17, 2024
7b2c259
make gen
SasSwart Dec 17, 2024
89177b2
fix dbauthz tests
SasSwart Dec 17, 2024
877517f
do the plumbing to get sql, api and frontend talking to one another
SasSwart Dec 23, 2024
0b3e0e6
rename migration
SasSwart Dec 23, 2024
6462cc2
move aggregation logic for GetUserStatusChanges into the SQL
SasSwart Dec 24, 2024
ccd0cdf
use window functions for efficiency
SasSwart Dec 24, 2024
12a274f
ensure we use the same time zone as the start_time param
SasSwart Dec 24, 2024
fcfd76e
ensure we use the same time zone as the start_time param
SasSwart Dec 24, 2024
7c0cade
make gen
SasSwart Dec 24, 2024
ecffc8b
update field names and fix tests
SasSwart Dec 24, 2024
f3a2ce3
exclude deleted users from the user status graph
SasSwart Dec 27, 2024
5067a63
GetUserStatusChanges now passes all querier tests
SasSwart Jan 2, 2025
254d436
renumber migrations
SasSwart Jan 2, 2025
3e86522
add partial fixture for CI
SasSwart Jan 3, 2025
2e49e4c
fix migration numbers
SasSwart Jan 3, 2025
ff59729
rename and document sql function
SasSwart Jan 3, 2025
b1ad074
make gen
SasSwart Jan 3, 2025
de4081f
Remove unwanted comments from the generated interface
SasSwart Jan 9, 2025
4de334f
review notes
SasSwart Jan 9, 2025
8fca0c5
make gen
SasSwart Jan 9, 2025
b06179c
remove frontend changes
SasSwart Jan 9, 2025
213b288
rename GetUserStatusCountsOverTime to GetUserStatusCounts
SasSwart Jan 9, 2025
9457ac8
fix tests
SasSwart Jan 9, 2025
63128a3
Update coderd/database/queries/insights.sql
SasSwart Jan 10, 2025
89f0a11
Provide basic durability against multiple deletions
SasSwart Jan 13, 2025
89ebab2
Provide basic durability against multiple deletions
SasSwart Jan 13, 2025
012f14c
populate the user_deleted_table
SasSwart Jan 13, 2025
c2efd97
formatting
SasSwart Jan 13, 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
ensure we use the same time zone as the start_time param
  • Loading branch information
@SasSwart
SasSwart committed Jan 9, 2025
commit 12a274fd364442cec84ab6ffa55e7b905ac29b43
4 changes: 2 additions & 2 deletions coderd/database/queries/insights.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -774,11 +774,11 @@ GROUP BY utp.num, utp.template_ids, utp.name, utp.type, utp.display_name, utp.de

-- name: GetUserStatusChanges :many
WITH dates AS (
SELECT generate_series(
SELECT (generate_series(
date_trunc('day', @start_time::timestamptz),
date_trunc('day', @end_time::timestamptz),
'1 day'::interval
)::timestamptz AS date
) AT TIME ZONE (extract(timezone FROM @start_time::timestamptz)::text || ' minutes'))::timestamptz AS date
),
latest_status_before_range AS (
-- Get the last status change for each user before our date range
Expand Down