feat: add registered users insights

coder · BrunoQuaresma · Nov 12, 2024 · Nov 12, 2024 · Nov 12, 2024 · Nov 12, 2024
commit a20be9f58e75bc5c9d69cc9d3cae67f802dde264
diff --git a/coderd/coderd.go b/coderd/coderd.go
@@ -1260,6 +1260,7 @@ func New(options *Options) *API {
 			r.Get("/user-activity", api.insightsUserActivity)
 			r.Get("/user-latency", api.insightsUserLatency)
 			r.Get("/templates", api.insightsTemplates)
+			r.Get("/total-users", api.insightsTotalUsers)
 		})
 		r.Route("/debug", func(r chi.Router) {
 			r.Use(

diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -2283,6 +2283,13 @@ func (q *querier) GetTemplatesWithFilter(ctx context.Context, arg database.GetTe
 	return q.db.GetAuthorizedTemplates(ctx, arg, prep)
 }
 
+func (q *querier) GetAccumulatedUsersInsights(ctx context.Context, arg database.GetAccumulatedUsersInsightsParams) ([]database.GetAccumulatedUsersInsightsRow, error) {
+	if err := q.authorizeContext(ctx, policy.ActionViewInsights, rbac.ResourceUser); err != nil {
+		return nil, err
+	}
+	return q.db.GetAccumulatedUsersInsights(ctx, arg)
+}
+
 func (q *querier) GetUnexpiredLicenses(ctx context.Context) ([]database.License, error) {
 	if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceSystem); err != nil {
 		return nil, err

diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
@@ -5169,6 +5169,48 @@ func (q *FakeQuerier) GetTemplatesWithFilter(ctx context.Context, arg database.G
 	return q.GetAuthorizedTemplates(ctx, arg, nil)
 }
 
+func (q *FakeQuerier) GetAccumulatedUsersInsights(_ context.Context, arg database.GetAccumulatedUsersInsightsParams) ([]database.GetAccumulatedUsersInsightsRow, error) {
+	q.mutex.RLock()
+	defer q.mutex.RUnlock()
+
+	newUsersByDate := make(map[time.Time]int64)
+	for _, user := range q.users {
+		if user.CreatedAt.Before(arg.StartTime) || user.CreatedAt.After(arg.EndTime) {
+			continue
+		}
+		date := user.CreatedAt.Truncate(24 * time.Hour)
+		newUsersByDate[date]++
+	}
+
+	accumulatedByDate := make(map[time.Time]int64)
+	var accumulatedCount int64
+	sortedDates := make([]time.Time, 0, len(newUsersByDate))
+
+	for date := range newUsersByDate {
+		sortedDates = append(sortedDates, date)
+	}
+
+	// Sort dates in ascending order
+	sort.Slice(sortedDates, func(i, j int) bool {
+		return sortedDates[i].Before(sortedDates[j])
+	})
+
+	// Calculate accumulated count
+	for _, date := range sortedDates {
+		accumulatedCount += newUsersByDate[date]
+		accumulatedByDate[date] = accumulatedCount
+	}
+
+	var rows []database.GetAccumulatedUsersInsightsRow
+	for _, date := range sortedDates {
+		rows = append(rows, database.GetAccumulatedUsersInsightsRow{
+			Date:  date,
+			Count: accumulatedByDate[date],
+		})
+	}
+	return rows, nil
+}
+
 func (q *FakeQuerier) GetUnexpiredLicenses(_ context.Context) ([]database.License, 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/querier.go b/coderd/database/querier.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
@@ -771,3 +771,18 @@ SELECT
 FROM unique_template_params utp
 JOIN workspace_build_parameters wbp ON (utp.workspace_build_ids @> ARRAY[wbp.workspace_build_id] AND utp.name = wbp.name)
 GROUP BY utp.num, utp.template_ids, utp.name, utp.type, utp.display_name, utp.description, utp.options, wbp.value;
+
+-- name: GetAccumulatedUsersInsights :many
+-- GetAccumulatedUsersInsights returns the accumulated number of users created
+-- in the given timeframe. It returns the accumulated number of users for each date
+-- within the specified timeframe, providing a running total of user sign-ups.
+SELECT
+	date_trunc('day', created_at)::date AS date,
+	COUNT(*) OVER (ORDER BY date_trunc('day', created_at)::date) AS accumulated_users
+FROM
+	users
+WHERE
+	created_at >= @start_time::timestamptz
+	AND created_at < @end_time::timestamptz
+ORDER BY
+	date_trunc('day', created_at)::date;
diff --git a/coderd/insights.go b/coderd/insights.go
@@ -470,6 +470,54 @@ func (api *API) insightsTemplates(rw http.ResponseWriter, r *http.Request) {
 	httpapi.Write(ctx, rw, http.StatusOK, resp)
 }
 
+func (api *API) insightsTotalUsers(rw http.ResponseWriter, r *http.Request) {
+	ctx := r.Context()
+
+	p := httpapi.NewQueryParamParser().
+		RequiredNotEmpty("start_time").
+		RequiredNotEmpty("end_time")
+	vals := r.URL.Query()
+	var (
+		// The QueryParamParser does not preserve timezone, so we need
+		// to parse the time ourselves.
+		startTimeString = p.String(vals, "", "start_time")
+		endTimeString   = p.String(vals, "", "end_time")
+	)
+	p.ErrorExcessParams(vals)
+	if len(p.Errors) > 0 {
+		httpapi.Write(ctx, rw, http.StatusBadRequest, codersdk.Response{
+			Message:     "Query parameters have invalid values.",
+			Validations: p.Errors,
+		})
+		return
+	}
+
+	startTime, endTime, ok := parseInsightsStartAndEndTime(ctx, rw, time.Now(), startTimeString, endTimeString)
+	if !ok {
+		return
+	}
+
+	rows, err := api.Database.GetAccumulatedUsersInsights(ctx, database.GetAccumulatedUsersInsightsParams{
+		StartTime: startTime,
+		EndTime:   endTime,
+	})
+	if err != nil {
+		httpapi.Write(ctx, rw, http.StatusInternalServerError, codersdk.Response{
+			Message: "Internal error fetching total users.",
+			Detail:  err.Error(),
+		})
+		return
+	}
+	var res codersdk.TotalUsersInsightResponse
+	for _, row := range rows {
+		res = append(res, codersdk.TotalUserByDate{
+			Date:  row.Date.Format(time.DateOnly),
+			Total: uint64(row.Count),
+		})
+	}
+	httpapi.Write(r.Context(), rw, http.StatusOK, res)
+}
+
 // convertTemplateInsightsApps builds the list of builtin apps and template apps
 // from the provided database rows, builtin apps are implicitly a part of all
 // templates.

diff --git a/coderd/insights_test.go b/coderd/insights_test.go
@@ -2351,3 +2351,63 @@ func TestGenericInsights_RBAC(t *testing.T) {
 		})
 	}
 }
+
+func TestTotalUsersInsight(t *testing.T) {
+	t.Parallel()
+
+	t.Run("Success", func(t *testing.T) {
+		t.Parallel()
+
+		client, db := coderdtest.NewWithDatabase(t, nil)
+		coderdtest.CreateFirstUser(t, client)
+		ctx, cancel := context.WithTimeout(context.Background(), testutil.WaitShort)
+		t.Cleanup(cancel)
+
+		// Given: a deployment with many users
+		dbgen.User(t, db, database.User{
+			Email:     "user1@coder.com",
+			Username:  "user1",
+			CreatedAt: time.Now().UTC().AddDate(0, 0, -2),
+		})
+		dbgen.User(t, db, database.User{
+			Email:     "user2@coder.com",
+			Username:  "user2",
+			CreatedAt: time.Now().UTC().AddDate(0, 0, -2),
+		})
+		dbgen.User(t, db, database.User{
+			Email:     "user3@coder.com",
+			Username:  "user3",
+			CreatedAt: time.Now().UTC().AddDate(0, 0, -1),
+		})
+
+		// When: requesting the total users by day
+		threeDaysAgo := time.Now().UTC().AddDate(0, 0, -2).Truncate(24 * time.Hour)
+		today := time.Now().UTC().Truncate(time.Hour).Add(time.Hour)
+		res, err := client.TotalUsersInsight(ctx, codersdk.TotalUsersInsightRequest{
+			StartTime: threeDaysAgo,
+			EndTime:   today,
+		})
+		require.NoError(t, err)
+
+		// Then: expect to have the accumulated users count growing by day
+		require.Len(t, res, 4)
+	})
+
+	t.Run("EmptyTimes", func(t *testing.T) {
+		t.Parallel()
+
+		client, _ := coderdtest.NewWithDatabase(t, nil)
+		coderdtest.CreateFirstUser(t, client)
+		ctx, cancel := context.WithTimeout(context.Background(), testutil.WaitShort)
+		t.Cleanup(cancel)
+
+		// When: requesting total users without a time range
+		_, err := client.TotalUsersInsight(ctx, codersdk.TotalUsersInsightRequest{})
+
+		// Then: expect a bad request error
+		require.Error(t, err)
+		var apiErr *codersdk.Error
+		require.ErrorAs(t, err, &apiErr)
+		require.Equal(t, http.StatusBadRequest, apiErr.StatusCode())
+	})
+}
diff --git a/coderd/rbac/object_gen.go b/coderd/rbac/object_gen.go
diff --git a/coderd/rbac/policy/policy.go b/coderd/rbac/policy/policy.go
@@ -84,6 +84,8 @@ var RBACPermissions = map[string]PermissionDefinition{
 
 			ActionReadPersonal:   actDef("read personal user data like user settings and auth links"),
 			ActionUpdatePersonal: actDef("update personal data"),
+
+			ActionViewInsights: actDef("view insights"),
 		},
 	},
 	"workspace": {