exclude deleted users from the user status graph

coder · SasSwart · Jan 13, 2025 · Dec 14, 2024 · Dec 14, 2024 · Dec 14, 2024
commit f3a2ce34d5fd209decc70792ae3f10d09a65eb53
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/foreign_key_constraint.go b/coderd/database/foreign_key_constraint.go
diff --git a/coderd/database/migrations/000280_user_status_changes.down.sql b/coderd/database/migrations/000280_user_status_changes.down.sql
@@ -1,11 +1,9 @@
--- Drop the trigger first
 DROP TRIGGER IF EXISTS user_status_change_trigger ON users;
 
--- Drop the trigger function
 DROP FUNCTION IF EXISTS record_user_status_change();
 
--- Drop the indexes
 DROP INDEX IF EXISTS idx_user_status_changes_changed_at;
+DROP INDEX IF EXISTS idx_user_deleted_deleted_at;
 
--- Drop the table
 DROP TABLE IF EXISTS user_status_changes;
+DROP TABLE IF EXISTS user_deleted;
diff --git a/coderd/database/migrations/000280_user_status_changes.up.sql b/coderd/database/migrations/000280_user_status_changes.up.sql
@@ -21,6 +21,16 @@ SELECT
 FROM users
 WHERE NOT deleted;
 
+CREATE TABLE user_deleted (
+    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
+    user_id uuid NOT NULL REFERENCES users(id),
+    deleted_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
+);
+
+COMMENT ON TABLE user_deleted IS 'Tracks when users were deleted';
+
+CREATE INDEX idx_user_deleted_deleted_at ON user_deleted(deleted_at);
+
 CREATE OR REPLACE FUNCTION record_user_status_change() RETURNS trigger AS $$
 BEGIN
     IF TG_OP = 'INSERT' OR OLD.status IS DISTINCT FROM NEW.status THEN
@@ -34,6 +44,17 @@ BEGIN
             NEW.updated_at
         );
     END IF;
+
+    IF OLD.deleted = FALSE AND NEW.deleted = TRUE THEN
+        INSERT INTO user_deleted (
+            user_id,
+            deleted_at
+        ) VALUES (
+            NEW.id,
+            NEW.updated_at
+        );
+    END IF;
+
     RETURN NEW;
 END;
 $$ LANGUAGE plpgsql;

diff --git a/coderd/database/models.go b/coderd/database/models.go
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/insights.sql b/coderd/database/queries/insights.sql
@@ -812,14 +812,20 @@ daily_counts AS (
         d.date,
         asc1.new_status,
         -- For each date and status, count users whose most recent status change
-        -- (up to that date) matches this status
+        -- (up to that date) matches this status AND who weren't deleted by that date
         COUNT(*) FILTER (
             WHERE asc1.changed_at = (
                 SELECT MAX(changed_at)
                 FROM all_status_changes asc2
                 WHERE asc2.user_id = asc1.user_id
                 AND asc2.changed_at <= d.date
             )
+            AND NOT EXISTS (
+                SELECT 1
+                FROM user_deleted ud
+                WHERE ud.user_id = asc1.user_id
+                AND ud.deleted_at <= d.date
+            )
         )::bigint AS count
     FROM dates d
     CROSS JOIN all_status_changes asc1

diff --git a/coderd/database/unique_constraint.go b/coderd/database/unique_constraint.go