coder · SasSwart · Dec 14, 2024 · Dec 14, 2024 · Dec 14, 2024 · Dec 17, 2024
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -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 {

diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
@@ -5664,6 +5664,18 @@ func (q *FakeQuerier) GetUserNotificationPreferences(_ context.Context, userID u
 	return out, nil
 }
 
+func (q *FakeQuerier) GetUserStatusCountsByDay(_ context.Context, arg database.GetUserStatusCountsByDayParams) ([]database.GetUserStatusCountsByDayRow, error) {
+	q.mutex.RLock()
+	defer q.mutex.RUnlock()
+
+	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()

diff --git a/coderd/database/dbmetrics/querymetrics.go b/coderd/database/dbmetrics/querymetrics.go
diff --git a/coderd/database/dbmock/dbmock.go b/coderd/database/dbmock/dbmock.go
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/000279_user_status_changes.down.sql b/coderd/database/migrations/000279_user_status_changes.down.sql
@@ -0,0 +1,11 @@
+-- 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 the table
+DROP TABLE IF EXISTS user_status_changes;
diff --git a/coderd/database/migrations/000279_user_status_changes.up.sql b/coderd/database/migrations/000279_user_status_changes.up.sql
@@ -0,0 +1,44 @@
+CREATE TABLE user_status_changes (
+    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
+    user_id uuid NOT NULL REFERENCES users(id),
+    new_status user_status NOT NULL,
+    changed_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
+);
+
+COMMENT ON TABLE user_status_changes IS 'Tracks the history of user status changes';
+
+CREATE INDEX idx_user_status_changes_changed_at ON user_status_changes(changed_at);
+
+INSERT INTO user_status_changes (
+    user_id,
+    new_status,
+    changed_at
+)
+SELECT
+    id,
+    status,
+    created_at
+FROM users
+WHERE NOT deleted;
+
+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
+        INSERT INTO user_status_changes (
+            user_id,
+            new_status,
+            changed_at
+        ) VALUES (
+            NEW.id,
+            NEW.status,
+            NEW.updated_at
+        );
+    END IF;
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER user_status_change_trigger
+    AFTER INSERT OR UPDATE ON users
+    FOR EACH ROW
+    EXECUTE FUNCTION record_user_status_change();
diff --git a/coderd/database/models.go b/coderd/database/models.go
diff --git a/coderd/database/querier.go b/coderd/database/querier.go