Provide basic durability against multiple deletions

SasSwart · SasSwart · commit 89f0a11c1b03 · 2025-01-13T05:26:19.000Z
diff --git a/coderd/database/migrations/testdata/fixtures/000283_user_status_changes.up.sql b/coderd/database/migrations/testdata/fixtures/000283_user_status_changes.up.sql
@@ -10,7 +10,6 @@ INSERT INTO
 		rbac_roles,
 		login_type,
 		avatar_url,
-		deleted,
 		last_seen_at,
 		quiet_hours_schedule,
 		theme_preference,
@@ -30,7 +29,6 @@ INSERT INTO
 		'{}',
 		'password',
 		'',
-		false,
 		'2024-01-01 00:00:00',
 		'',
 		'',
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
@@ -818,9 +818,13 @@ latest_status_before_range AS (
         usc.new_status,
         usc.changed_at
     FROM user_status_changes usc
-	LEFT JOIN user_deleted ud ON usc.user_id = ud.user_id
+	LEFT JOIN LATERAL (
+		SELECT COUNT(*) > 0 AS deleted
+		FROM user_deleted ud
+		WHERE ud.user_id = usc.user_id AND ud.changed_at < usc.changed_at
+	) AS ud ON usc.user_id = ud.user_id
     WHERE usc.changed_at < @start_time::timestamptz
-	AND (ud.user_id IS NULL OR ud.deleted_at > @start_time::timestamptz)
+	AND NOT ud.deleted
     ORDER BY usc.user_id, usc.changed_at DESC
 ),
 	-- status_changes_during_range defines the status of each user during the start_time and end_time.
@@ -833,10 +837,14 @@ status_changes_during_range AS (
         usc.new_status,
         usc.changed_at
     FROM user_status_changes usc
-	LEFT JOIN user_deleted ud ON usc.user_id = ud.user_id
+	LEFT JOIN LATERAL (
+		SELECT COUNT(*) > 0 AS deleted
+		FROM user_deleted ud
+		WHERE ud.user_id = usc.user_id AND ud.changed_at < usc.changed_at
+	) AS ud ON usc.user_id = ud.user_id
     WHERE usc.changed_at >= @start_time::timestamptz
         AND usc.changed_at <= @end_time::timestamptz
-		AND (ud.user_id IS NULL OR usc.changed_at < ud.deleted_at)
+        AND NOT deleted
 ),
 	-- relevant_status_changes defines the status of each user at any point in time.
 	-- It includes the status of each user before the start_time, and the status of each user during the start_time and end_time.
