coder · ethanndickson · Jun 27, 2025
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -1323,6 +1323,21 @@ func (q *querier) CleanTailnetTunnels(ctx context.Context) error {
 	return q.db.CleanTailnetTunnels(ctx)
 }
 
+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
@@ -5301,3 +5316,7 @@ func (q *querier) GetAuthorizedAuditLogsOffset(ctx context.Context, arg database
 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
@@ -391,6 +391,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
@@ -271,7 +271,7 @@ func (s *MethodTestSuite) NotAuthorizedErrorTest(ctx context.Context, az *coderd
 
 		// This is unfortunate, but if we are using `Filter` the error returned will be nil. So filter out
 		// any case where the error is nil and the response is an empty slice.
-		if err != nil || !hasEmptySliceResponse(resp) {
+		if err != nil || !hasEmptyResponse(resp) {
 			// Expect the default error
 			if testCase.notAuthorizedExpect == "" {
 				s.ErrorContainsf(err, "unauthorized", "error string should have a good message")
@@ -297,7 +297,7 @@ func (s *MethodTestSuite) NotAuthorizedErrorTest(ctx context.Context, az *coderd
 
 		// This is unfortunate, but if we are using `Filter` the error returned will be nil. So filter out
 		// any case where the error is nil and the response is an empty slice.
-		if err != nil || !hasEmptySliceResponse(resp) {
+		if err != nil || !hasEmptyResponse(resp) {
 			if testCase.cancelledCtxExpect == "" {
 				s.Errorf(err, "method should an error with cancellation")
 				s.ErrorIsf(err, context.Canceled, "error should match context.Canceled")
@@ -308,13 +308,20 @@ func (s *MethodTestSuite) NotAuthorizedErrorTest(ctx context.Context, az *coderd
 	})
 }
 
-func hasEmptySliceResponse(values []reflect.Value) bool {
+func hasEmptyResponse(values []reflect.Value) bool {
 	for _, r := range values {
 		if r.Kind() == reflect.Slice || r.Kind() == reflect.Array {
 			if r.Len() == 0 {
 				return true
 			}
 		}
+
+		// Special case for int64, as it's the return type for count queries.
+		if r.Kind() == reflect.Int64 {
+			if r.Int() == 0 {
+				return true
+			}
+		}
 	}
 	return false
 }

diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
@@ -1780,6 +1780,10 @@ func (*FakeQuerier) CleanTailnetTunnels(context.Context) error {
 	return ErrUnimplemented
 }
 
+func (q *FakeQuerier) CountConnectionLogs(ctx context.Context, arg database.CountConnectionLogsParams) (int64, error) {
+	return q.CountAuthorizedConnectionLogs(ctx, arg, nil)
+}
+
 func (q *FakeQuerier) CountInProgressPrebuilds(ctx context.Context) ([]database.CountInProgressPrebuildsRow, error) {
 	return nil, ErrUnimplemented
 }
@@ -14167,3 +14171,97 @@ func (q *FakeQuerier) GetAuthorizedConnectionLogsOffset(ctx context.Context, arg
 
 	return logs, nil
 }
+
+func (q *FakeQuerier) CountAuthorizedConnectionLogs(ctx context.Context, arg database.CountConnectionLogsParams, prepared rbac.PreparedAuthorized) (int64, error) {
+	if err := validateDatabaseType(arg); err != nil {
+		return 0, err
+	}
+
+	// Call this to match the same function calls as the SQL implementation.
+	// It functionally does nothing for filtering.
+	if prepared != nil {
+		_, err := prepared.CompileToSQL(ctx, regosql.ConvertConfig{
+			VariableConverter: regosql.ConnectionLogConverter(),
+		})
+		if err != nil {
+			return 0, err
+		}
+	}
+
+	q.mutex.RLock()
+	defer q.mutex.RUnlock()
+
+	var count int64
+
+	for _, clog := range q.connectionLogs {
+		if arg.OrganizationID != uuid.Nil && clog.OrganizationID != arg.OrganizationID {
+			continue
+		}
+		if arg.WorkspaceOwner != "" {
+			workspaceOwner, err := q.getUserByIDNoLock(clog.WorkspaceOwnerID)
+			if err == nil && !strings.EqualFold(arg.WorkspaceOwner, workspaceOwner.Username) {
+				continue
+			}
+		}
+		if arg.Type != "" && string(clog.Type) != arg.Type {
+			continue
+		}
+		if arg.UserID != uuid.Nil && (!clog.UserID.Valid || clog.UserID.UUID != arg.UserID) {
+			continue
+		}
+		if arg.Username != "" {
+			if !clog.UserID.Valid {
+				continue
+			}
+			user, err := q.getUserByIDNoLock(clog.UserID.UUID)
+			if err != nil || user.Username != arg.Username {
+				continue
+			}
+		}
+		if arg.Email != "" {
+			if !clog.UserID.Valid {
+				continue
+			}
+			user, err := q.getUserByIDNoLock(clog.UserID.UUID)
+			if err != nil || user.Email != arg.Email {
+				continue
+			}
+		}
+		if !arg.StartedAfter.IsZero() && clog.Time.Before(arg.StartedAfter) {
+			continue
+		}
+		if !arg.StartedBefore.IsZero() && clog.Time.After(arg.StartedBefore) {
+			continue
+		}
+		if !arg.ClosedAfter.IsZero() && (!clog.CloseTime.Valid || clog.CloseTime.Time.Before(arg.ClosedAfter)) {
+			continue
+		}
+		if !arg.ClosedBefore.IsZero() && (!clog.CloseTime.Valid || clog.CloseTime.Time.After(arg.ClosedBefore)) {
+			continue
+		}
+		if arg.WorkspaceID != uuid.Nil && clog.WorkspaceID != arg.WorkspaceID {
+			continue
+		}
+		if arg.ConnectionID != uuid.Nil && (!clog.ConnectionID.Valid || clog.ConnectionID.UUID != arg.ConnectionID) {
+			continue
+		}
+		if arg.Status != "" {
+			if clog.Type == database.ConnectionTypeWorkspaceApp ||
+				clog.Type == database.ConnectionTypePortForwarding {
+				continue
+			}
+			isConnected := !clog.CloseTime.Valid
+			if (arg.Status == "connected" && !isConnected) || (arg.Status == "disconnected" && isConnected) {
+				continue
+			}
+		}
+
+		if prepared != nil && prepared.Authorize(ctx, clog.RBACObject()) != nil {
+			continue
+		}
+
+		count++
+	}
+
+	return count, nil
+}
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
@@ -566,6 +566,7 @@ func (q *sqlQuerier) GetAuthorizedAuditLogsOffset(ctx context.Context, arg GetAu
 
 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) {
@@ -653,6 +654,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.Type,
+		arg.UserID,
+		arg.Username,
+		arg.Email,
+		arg.StartedAfter,
+		arg.StartedBefore,
+		arg.ClosedAfter,
+		arg.ClosedBefore,
+		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
@@ -1,6 +1,8 @@
 package database
 
 import (
+	"regexp"
+	"strings"
 	"testing"
 	"time"
 
@@ -54,3 +56,35 @@ func TestWorkspaceTableConvert(t *testing.T) {
 		"'workspace.WorkspaceTable()' is not missing at least 1 field when converting to 'WorkspaceTable'. "+
 			"To resolve this, go to the 'func (w Workspace) WorkspaceTable()' and ensure all fields are converted.")
 }
+
+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
+	wherePattern := regexp.MustCompile(`(?is)WHERE\s+(.*)`)
+	whereMatches := wherePattern.FindStringSubmatch(query)
+	if len(whereMatches) < 2 {
+		return ""
+	}
+
+	whereClause := whereMatches[1]
+
+	// Remove ORDER BY, LIMIT, OFFSET clauses from the end
+	whereClause = regexp.MustCompile(`(?is)\s+(ORDER BY|LIMIT|OFFSET).*$`).ReplaceAllString(whereClause, "")
+
+	// Remove SQL comments
+	whereClause = regexp.MustCompile(`(?m)--.*$`).ReplaceAllString(whereClause, "")
+
+	return strings.TrimSpace(whereClause)
+}
diff --git a/coderd/database/querier.go b/coderd/database/querier.go