coder · ethanndickson · Jul 15, 2025 · Jun 27, 2025
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -1353,15 +1353,26 @@ func (q *querier) CountAuditLogs(ctx context.Context, arg database.CountAuditLog
 	if err == nil {
 		return q.db.CountAuditLogs(ctx, arg)
 	}
-
 	prep, err := prepareSQLFilter(ctx, q.auth, policy.ActionRead, rbac.ResourceAuditLog.Type)
 	if err != nil {
 		return 0, xerrors.Errorf("(dev error) prepare sql filter: %w", err)
 	}
-
 	return q.db.CountAuthorizedAuditLogs(ctx, arg, prep)
 }
 
+func (q *querier) CountConnectionLogs(ctx context.Context, arg database.CountConnectionLogsParams) (int64, error) {
+	// Just like the actual query, shortcut if the user is an owner.
+	err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceConnectionLog)
+	if err == nil {
+		return q.db.CountConnectionLogs(ctx, arg)
+	}
+	prep, err := prepareSQLFilter(ctx, q.auth, policy.ActionRead, rbac.ResourceConnectionLog.Type)
+	if err != nil {
+		return 0, xerrors.Errorf("(dev error) prepare sql filter: %w", err)
+	}
+	return q.db.CountAuthorizedConnectionLogs(ctx, arg, prep)
+}
+
 func (q *querier) CountInProgressPrebuilds(ctx context.Context) ([]database.CountInProgressPrebuildsRow, error) {
 	if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceWorkspace.All()); err != nil {
 		return nil, err
@@ -5392,3 +5403,7 @@ func (q *querier) CountAuthorizedAuditLogs(ctx context.Context, arg database.Cou
 func (q *querier) GetAuthorizedConnectionLogsOffset(ctx context.Context, arg database.GetConnectionLogsOffsetParams, _ rbac.PreparedAuthorized) ([]database.GetConnectionLogsOffsetRow, error) {
 	return q.GetConnectionLogsOffset(ctx, arg)
 }
+
+func (q *querier) CountAuthorizedConnectionLogs(ctx context.Context, arg database.CountConnectionLogsParams, _ rbac.PreparedAuthorized) (int64, error) {
+	return q.CountConnectionLogs(ctx, arg)
+}
diff --git a/coderd/database/dbauthz/dbauthz_test.go b/coderd/database/dbauthz/dbauthz_test.go
@@ -406,6 +406,42 @@ func (s *MethodTestSuite) TestConnectionLogs() {
 			LimitOpt: 10,
 		}, emptyPreparedAuthorized{}).Asserts(rbac.ResourceConnectionLog, policy.ActionRead)
 	}))
+	s.Run("CountConnectionLogs", s.Subtest(func(db database.Store, check *expects) {
+		ws := createWorkspace(s.T(), db)
+		_ = dbgen.ConnectionLog(s.T(), db, database.UpsertConnectionLogParams{
+			Type:             database.ConnectionTypeSsh,
+			WorkspaceID:      ws.ID,
+			OrganizationID:   ws.OrganizationID,
+			WorkspaceOwnerID: ws.OwnerID,
+		})
+		_ = dbgen.ConnectionLog(s.T(), db, database.UpsertConnectionLogParams{
+			Type:             database.ConnectionTypeSsh,
+			WorkspaceID:      ws.ID,
+			OrganizationID:   ws.OrganizationID,
+			WorkspaceOwnerID: ws.OwnerID,
+		})
+		check.Args(database.CountConnectionLogsParams{}).Asserts(
+			rbac.ResourceConnectionLog, policy.ActionRead,
+		).WithNotAuthorized("nil")
+	}))
+	s.Run("CountAuthorizedConnectionLogs", s.Subtest(func(db database.Store, check *expects) {
+		ws := createWorkspace(s.T(), db)
+		_ = dbgen.ConnectionLog(s.T(), db, database.UpsertConnectionLogParams{
+			Type:             database.ConnectionTypeSsh,
+			WorkspaceID:      ws.ID,
+			OrganizationID:   ws.OrganizationID,
+			WorkspaceOwnerID: ws.OwnerID,
+		})
+		_ = dbgen.ConnectionLog(s.T(), db, database.UpsertConnectionLogParams{
+			Type:             database.ConnectionTypeSsh,
+			WorkspaceID:      ws.ID,
+			OrganizationID:   ws.OrganizationID,
+			WorkspaceOwnerID: ws.OwnerID,
+		})
+		check.Args(database.CountConnectionLogsParams{}, emptyPreparedAuthorized{}).Asserts(
+			rbac.ResourceConnectionLog, policy.ActionRead,
+		)
+	}))
 }
 
 func (s *MethodTestSuite) TestFile() {

diff --git a/coderd/database/dbauthz/setup_test.go b/coderd/database/dbauthz/setup_test.go
@@ -318,7 +318,7 @@ func hasEmptyResponse(values []reflect.Value) bool {
 			}
 		}
 
-		// Special case for int64, as it's the return type for count query.
+		// Special case for int64, as it's the return type for count queries.
 		if r.Kind() == reflect.Int64 {
 			if r.Int() == 0 {
 				return true

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/modelqueries.go b/coderd/database/modelqueries.go
@@ -614,6 +614,7 @@ func (q *sqlQuerier) CountAuthorizedAuditLogs(ctx context.Context, arg CountAudi
 
 type connectionLogQuerier interface {
 	GetAuthorizedConnectionLogsOffset(ctx context.Context, arg GetConnectionLogsOffsetParams, prepared rbac.PreparedAuthorized) ([]GetConnectionLogsOffsetRow, error)
+	CountAuthorizedConnectionLogs(ctx context.Context, arg CountConnectionLogsParams, prepared rbac.PreparedAuthorized) (int64, error)
 }
 
 func (q *sqlQuerier) GetAuthorizedConnectionLogsOffset(ctx context.Context, arg GetConnectionLogsOffsetParams, prepared rbac.PreparedAuthorized) ([]GetConnectionLogsOffsetRow, error) {
@@ -700,6 +701,53 @@ func (q *sqlQuerier) GetAuthorizedConnectionLogsOffset(ctx context.Context, arg
 	return items, nil
 }
 
+func (q *sqlQuerier) CountAuthorizedConnectionLogs(ctx context.Context, arg CountConnectionLogsParams, prepared rbac.PreparedAuthorized) (int64, error) {
+	authorizedFilter, err := prepared.CompileToSQL(ctx, regosql.ConvertConfig{
+		VariableConverter: regosql.ConnectionLogConverter(),
+	})
+	if err != nil {
+		return 0, xerrors.Errorf("compile authorized filter: %w", err)
+	}
+	filtered, err := insertAuthorizedFilter(countConnectionLogs, fmt.Sprintf(" AND %s", authorizedFilter))
+	if err != nil {
+		return 0, xerrors.Errorf("insert authorized filter: %w", err)
+	}
+
+	query := fmt.Sprintf("-- name: CountAuthorizedConnectionLogs :one\n%s", filtered)
+	rows, err := q.db.QueryContext(ctx, query,
+		arg.OrganizationID,
+		arg.WorkspaceOwner,
+		arg.WorkspaceOwnerID,
+		arg.WorkspaceOwnerEmail,
+		arg.Type,
+		arg.UserID,
+		arg.Username,
+		arg.UserEmail,
+		arg.ConnectedAfter,
+		arg.ConnectedBefore,
+		arg.WorkspaceID,
+		arg.ConnectionID,
+		arg.Status,
+	)
+	if err != nil {
+		return 0, err
+	}
+	defer rows.Close()
+	var count int64
+	for rows.Next() {
+		if err := rows.Scan(&count); err != nil {
+			return 0, err
+		}
+	}
+	if err := rows.Close(); err != nil {
+		return 0, err
+	}
+	if err := rows.Err(); err != nil {
+		return 0, err
+	}
+	return count, nil
+}
+
 func insertAuthorizedFilter(query string, replaceWith string) (string, error) {
 	if !strings.Contains(query, authorizedQueryPlaceholder) {
 		return "", xerrors.Errorf("query does not contain authorized replace string, this is not an authorized query")

diff --git a/coderd/database/modelqueries_internal_test.go b/coderd/database/modelqueries_internal_test.go
@@ -76,6 +76,19 @@ func TestAuditLogsQueryConsistency(t *testing.T) {
 	}
 }
 
+// Same as TestAuditLogsQueryConsistency, but for connection logs.
+func TestConnectionLogsQueryConsistency(t *testing.T) {
+	t.Parallel()
+
+	getWhereClause := extractWhereClause(getConnectionLogsOffset)
+	require.NotEmpty(t, getWhereClause, "getConnectionLogsOffset query should have a WHERE clause")
+
+	countWhereClause := extractWhereClause(countConnectionLogs)
+	require.NotEmpty(t, countWhereClause, "countConnectionLogs query should have a WHERE clause")
+
+	require.Equal(t, getWhereClause, countWhereClause, "getConnectionLogsOffset and countConnectionLogs queries should have the same WHERE clause")
+}
+
 // extractWhereClause extracts the WHERE clause from a SQL query string
 func extractWhereClause(query string) string {
 	// Find WHERE and get everything after it

diff --git a/coderd/database/querier.go b/coderd/database/querier.go
diff --git a/coderd/database/querier_test.go b/coderd/database/querier_test.go
@@ -2168,6 +2168,10 @@ func TestGetAuthorizedConnectionLogsOffset(t *testing.T) {
 		require.NoError(t, err)
 		// Then: No logs returned
 		require.Len(t, logs, 0, "no logs should be returned")
+		// And: The count matches the number of logs returned
+		count, err := authDb.CountConnectionLogs(memberCtx, database.CountConnectionLogsParams{})
+		require.NoError(t, err)
+		require.EqualValues(t, len(logs), count)
 	})
 
 	t.Run("SiteWideAuditor", func(t *testing.T) {
@@ -2186,6 +2190,10 @@ func TestGetAuthorizedConnectionLogsOffset(t *testing.T) {
 		require.NoError(t, err)
 		// Then: All logs are returned
 		require.ElementsMatch(t, connectionOnlyIDs(allLogs), connectionOnlyIDs(logs))
+		// And: The count matches the number of logs returned
+		count, err := authDb.CountConnectionLogs(siteAuditorCtx, database.CountConnectionLogsParams{})
+		require.NoError(t, err)
+		require.EqualValues(t, len(logs), count)
 	})
 
 	t.Run("SingleOrgAuditor", func(t *testing.T) {
@@ -2205,6 +2213,10 @@ func TestGetAuthorizedConnectionLogsOffset(t *testing.T) {
 		require.NoError(t, err)
 		// Then: Only the logs for the organization are returned
 		require.ElementsMatch(t, orgConnectionLogs[orgID], connectionOnlyIDs(logs))
+		// And: The count matches the number of logs returned
+		count, err := authDb.CountConnectionLogs(orgAuditCtx, database.CountConnectionLogsParams{})
+		require.NoError(t, err)
+		require.EqualValues(t, len(logs), count)
 	})
 
 	t.Run("TwoOrgAuditors", func(t *testing.T) {
@@ -2225,6 +2237,10 @@ func TestGetAuthorizedConnectionLogsOffset(t *testing.T) {
 		require.NoError(t, err)
 		// Then: All logs for both organizations are returned
 		require.ElementsMatch(t, append(orgConnectionLogs[first], orgConnectionLogs[second]...), connectionOnlyIDs(logs))
+		// And: The count matches the number of logs returned
+		count, err := authDb.CountConnectionLogs(multiOrgAuditCtx, database.CountConnectionLogsParams{})
+		require.NoError(t, err)
+		require.EqualValues(t, len(logs), count)
 	})
 
 	t.Run("ErroneousOrg", func(t *testing.T) {
@@ -2243,9 +2259,71 @@ func TestGetAuthorizedConnectionLogsOffset(t *testing.T) {
 		require.NoError(t, err)
 		// Then: No logs are returned
 		require.Len(t, logs, 0, "no logs should be returned")
+		// And: The count matches the number of logs returned
+		count, err := authDb.CountConnectionLogs(userCtx, database.CountConnectionLogsParams{})
+		require.NoError(t, err)
+		require.EqualValues(t, len(logs), count)
 	})
 }
 
+func TestCountConnectionLogs(t *testing.T) {
+	t.Parallel()
+	ctx := testutil.Context(t, testutil.WaitLong)
+
+	db, _ := dbtestutil.NewDB(t)
+
+	orgA := dbfake.Organization(t, db).Do()
+	userA := dbgen.User(t, db, database.User{})
+	tplA := dbgen.Template(t, db, database.Template{OrganizationID: orgA.Org.ID, CreatedBy: userA.ID})
+	wsA := dbgen.Workspace(t, db, database.WorkspaceTable{OwnerID: userA.ID, OrganizationID: orgA.Org.ID, TemplateID: tplA.ID})
+
+	orgB := dbfake.Organization(t, db).Do()
+	userB := dbgen.User(t, db, database.User{})
+	tplB := dbgen.Template(t, db, database.Template{OrganizationID: orgB.Org.ID, CreatedBy: userB.ID})
+	wsB := dbgen.Workspace(t, db, database.WorkspaceTable{OwnerID: userB.ID, OrganizationID: orgB.Org.ID, TemplateID: tplB.ID})
+
+	// Create logs for two different orgs.
+	for i := 0; i < 20; i++ {
+		dbgen.ConnectionLog(t, db, database.UpsertConnectionLogParams{
+			OrganizationID:   wsA.OrganizationID,
+			WorkspaceOwnerID: wsA.OwnerID,
+			WorkspaceID:      wsA.ID,
+			Type:             database.ConnectionTypeSsh,
+		})
+	}
+	for i := 0; i < 10; i++ {
+		dbgen.ConnectionLog(t, db, database.UpsertConnectionLogParams{
+			OrganizationID:   wsB.OrganizationID,
+			WorkspaceOwnerID: wsB.OwnerID,
+			WorkspaceID:      wsB.ID,
+			Type:             database.ConnectionTypeSsh,
+		})
+	}
+
+	// Count with a filter for orgA.
+	countParams := database.CountConnectionLogsParams{
+		OrganizationID: orgA.Org.ID,
+	}
+	totalCount, err := db.CountConnectionLogs(ctx, countParams)
+	require.NoError(t, err)
+	require.Equal(t, int64(20), totalCount)
+
+	// Get a paginated result for the same filter.
+	getParams := database.GetConnectionLogsOffsetParams{
+		OrganizationID: orgA.Org.ID,
+		LimitOpt:       5,
+		OffsetOpt:      10,
+	}
+	logs, err := db.GetConnectionLogsOffset(ctx, getParams)
+	require.NoError(t, err)
+	require.Len(t, logs, 5)
+
+	// The count with the filter should remain the same, independent of pagination.
+	countAfterGet, err := db.CountConnectionLogs(ctx, countParams)
+	require.NoError(t, err)
+	require.Equal(t, int64(20), countAfterGet)
+}
+
 func TestConnectionLogsOffsetFilters(t *testing.T) {
 	t.Parallel()
 	ctx := testutil.Context(t, testutil.WaitLong)
@@ -2484,7 +2562,24 @@ func TestConnectionLogsOffsetFilters(t *testing.T) {
 			t.Parallel()
 			logs, err := db.GetConnectionLogsOffset(ctx, tc.params)
 			require.NoError(t, err)
+			count, err := db.CountConnectionLogs(ctx, database.CountConnectionLogsParams{
+				OrganizationID:      tc.params.OrganizationID,
+				WorkspaceOwner:      tc.params.WorkspaceOwner,
+				Type:                tc.params.Type,
+				UserID:              tc.params.UserID,
+				Username:            tc.params.Username,
+				UserEmail:           tc.params.UserEmail,
+				ConnectedAfter:      tc.params.ConnectedAfter,
+				ConnectedBefore:     tc.params.ConnectedBefore,
+				WorkspaceID:         tc.params.WorkspaceID,
+				ConnectionID:        tc.params.ConnectionID,
+				Status:              tc.params.Status,
+				WorkspaceOwnerID:    tc.params.WorkspaceOwnerID,
+				WorkspaceOwnerEmail: tc.params.WorkspaceOwnerEmail,
+			})
+			require.NoError(t, err)
 			require.ElementsMatch(t, tc.expectedLogIDs, connectionOnlyIDs(logs))
+			require.Equal(t, len(tc.expectedLogIDs), int(count), "CountConnectionLogs should match the number of returned logs (no offset or limit)")
 		})
 	}
 }