perf: cap count queries, use native UUID ops for audit/conn logs (bac…

…kport #23835) Audit and connection log pages were timing out due to expensive COUNT(*) queries over large tables. This commit adds opt-in count capping: requests can return a `count_cap` field signaling that the count was truncated at a threshold, avoiding full table scans that caused page timeouts. Text-cast UUID comparisons in regosql-generated authorization queries also contributed to the slowdown by preventing index usage for connection and audit log queries. These now emit native UUID operators. Frontend changes handle the capped state in usePaginatedQuery and PaginationWidget, optionally displaying a capped count in the pagination UI (e.g. "Showing 2,076 to 2,100 of 2,000+ logs") --- Cherry picked from 86ca61d
coder · f0ssel · Apr 9, 2026 · Apr 7, 2026 · Apr 7, 2026 · b9717d87dea14b799ac770733f5578c9044c8dc1
commit b9717d87dea14b799ac770733f5578c9044c8dc1
diff --git a/coderd/apidoc/docs.go b/coderd/apidoc/docs.go
diff --git a/coderd/apidoc/swagger.json b/coderd/apidoc/swagger.json
diff --git a/coderd/audit.go b/coderd/audit.go
@@ -26,6 +26,11 @@ import (
 	"github.com/coder/coder/v2/codersdk"
 )
 
+// Limit the count query to avoid a slow sequential scan due to joins
+// on a large table. Set to 0 to disable capping (but also see the note
+// in the SQL query).
+const auditLogCountCap = 2000
+
 // @Summary Get audit logs
 // @ID get-audit-logs
 // @Security CoderSessionToken
@@ -66,7 +71,7 @@ func (api *API) auditLogs(rw http.ResponseWriter, r *http.Request) {
 		countFilter.Username = ""
 	}
 
-	// Use the same filters to count the number of audit logs
+	countFilter.CountCap = auditLogCountCap
 	count, err := api.Database.CountAuditLogs(ctx, countFilter)
 	if dbauthz.IsNotAuthorizedError(err) {
 		httpapi.Forbidden(rw)
@@ -81,6 +86,7 @@ func (api *API) auditLogs(rw http.ResponseWriter, r *http.Request) {
 		httpapi.Write(ctx, rw, http.StatusOK, codersdk.AuditLogResponse{
 			AuditLogs: []codersdk.AuditLog{},
 			Count:     0,
+			CountCap:  auditLogCountCap,
 		})
 		return
 	}
@@ -98,6 +104,7 @@ func (api *API) auditLogs(rw http.ResponseWriter, r *http.Request) {
 	httpapi.Write(ctx, rw, http.StatusOK, codersdk.AuditLogResponse{
 		AuditLogs: api.convertAuditLogs(ctx, dblogs),
 		Count:     count,
+		CountCap:  auditLogCountCap,
 	})
 }
 

diff --git a/coderd/database/modelqueries.go b/coderd/database/modelqueries.go
@@ -608,6 +608,7 @@ func (q *sqlQuerier) CountAuthorizedAuditLogs(ctx context.Context, arg CountAudi
 		arg.DateTo,
 		arg.BuildReason,
 		arg.RequestID,
+		arg.CountCap,
 	)
 	if err != nil {
 		return 0, err
@@ -744,6 +745,7 @@ func (q *sqlQuerier) CountAuthorizedConnectionLogs(ctx context.Context, arg Coun
 		arg.WorkspaceID,
 		arg.ConnectionID,
 		arg.Status,
+		arg.CountCap,
 	)
 	if err != nil {
 		return 0, err

diff --git a/coderd/database/modelqueries_internal_test.go b/coderd/database/modelqueries_internal_test.go
@@ -145,5 +145,13 @@ func extractWhereClause(query string) string {
 	// Remove SQL comments
 	whereClause = regexp.MustCompile(`(?m)--.*$`).ReplaceAllString(whereClause, "")
 
+	// Normalize indentation so subquery wrapping doesn't cause
+	// mismatches.
+	lines := strings.Split(whereClause, "\n")
+	for i, line := range lines {
+		lines[i] = strings.TrimLeft(line, " \t")
+	}
+	whereClause = strings.Join(lines, "\n")
+
 	return strings.TrimSpace(whereClause)
 }