8000 exclude deleted users from the user status graph · coder/coder@5b8fcac · GitHub
[go: up one dir, main page]
Skip to content

Commit 5b8fcac

Browse files
SasSwartSasSwart
committed
exclude deleted users from the user status graph
1 parent 1a2fde8 commit 5b8fcac

File tree

8 files changed

+73
-6
lines changed

8 files changed

+73
-6
lines changed

‎coderd/database/dump.sql

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

‎coderd/database/foreign_key_constraint.go

Lines changed: 1 addition & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/migrations/000280_user_status_changes.down.sql

Lines changed: 2 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,11 +1,9 @@
1-
-- Drop the trigger first
21
DROP TRIGGER IF EXISTS user_status_change_trigger ON users;
32

4-
-- Drop the trigger function
53
DROP FUNCTION IF EXISTS record_user_status_change();
64

7-
-- Drop the indexes
85
DROP INDEX IF EXISTS idx_user_status_changes_changed_at;
6+
DROP INDEX IF EXISTS idx_user_deleted_deleted_at;
97

10-
-- Drop the table
118
DROP TABLE IF EXISTS user_status_changes;
9+
DROP TABLE IF EXISTS user_deleted;

‎coderd/database/migrations/000280_user_status_changes.up.sql

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -21,6 +21,16 @@ SELECT
2121
FROM users
2222
WHERE NOT deleted;
2323

24+
CREATE TABLE user_deleted (
25+
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
26+
user_id uuid NOT NULL REFERENCES users(id),
27+
deleted_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
28+
);
29+
30+
COMMENT ON TABLE user_deleted IS 'Tracks when users were deleted';
31+
32+
CREATE INDEX idx_user_deleted_deleted_at ON user_deleted(deleted_at);
33+
2434
CREATE OR REPLACE FUNCTION record_user_status_change() RETURNS trigger AS $$
2535
BEGIN
2636
IF TG_OP = 'INSERT' OR OLD.status IS DISTINCT FROM NEW.status THEN
@@ -34,6 +44,17 @@ BEGIN
3444
NEW.updated_at
3545
);
3646
END IF;
47+
48+
IF OLD.deleted = FALSE AND NEW.deleted = TRUE THEN
49+
INSERT INTO user_deleted (
50+
user_id,
51+
deleted_at
52+
) VALUES (
53+
NEW.id,
54+
NEW.updated_at
55+
);
56+
END IF;
57+
3758
RETURN NEW;
3859
END;
3960
$$ LANGUAGE plpgsql;

‎coderd/database/models.go

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

‎coderd/database/queries.sql.go

Lines changed: 7 additions & 1 deletion
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries/insights.sql

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -812,14 +812,20 @@ daily_counts AS (
812812
d.date,
813813
asc1.new_status,
814814
-- For each date and status, count users whose most recent status change
815-
-- (up to that date) matches this status
815+
-- (up to that date) matches this status AND who weren't deleted by that date
816816
COUNT(*) FILTER (
817817
WHERE asc1.changed_at = (
818818
SELECT MAX(changed_at)
819819
FROM all_status_changes asc2
820820
WHERE asc2.user_id = asc1.user_id
821821
AND asc2.changed_at <= d.date
822822
)
823+
AND NOT EXISTS (
824+
SELECT 1
825+
FROM user_deleted ud
826+
WHERE ud.user_id = asc1.user_id
827+
AND ud.deleted_at <= d.date
828+
)
823829
)::bigint AS count
824830
FROM dates d
825831
CROSS JOIN all_status_changes asc1

‎coderd/database/unique_constraint.go

Lines changed: 1 addition & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

0 commit comments

Comments
 (0)
0