Add consistency check for audit logs SQL queries

coder · kacpersaw · Jul 1, 2025 · Jun 25, 2025 · Jun 26, 2025 · Jun 26, 2025
commit ee0e49cad78ab006065bdd5adb67b7240dd2e6fd
diff --git a/coderd/database/modelqueries_internal_test.go b/coderd/database/modelqueries_internal_test.go
@@ -1,9 +1,12 @@
 package database
 
 import (
+	"regexp"
+	"strings"
 	"testing"
 	"time"
 
+	"github.com/google/go-cmp/cmp"
 	"github.com/stretchr/testify/require"
 
 	"github.com/coder/coder/v2/testutil"
@@ -54,3 +57,41 @@ 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.")
 }
+
+// TestAuditLogsQueryConsistency ensures that GetAuditLogsOffset and CountAuditLogs
+// have identical WHERE clauses to prevent filtering inconsistencies.
+// This test is a guard rail to prevent developer oversight mistakes.
+func TestAuditLogsQueryConsistency(t *testing.T) {
+	t.Parallel()
+
+	getWhereClause := extractWhereClause(getAuditLogsOffset)
+	require.NotEmpty(t, getWhereClause, "failed to extract WHERE clause from GetAuditLogsOffset")
+
+	countWhereClause := extractWhereClause(countAuditLogs)
+	require.NotEmpty(t, countWhereClause, "failed to extract WHERE clause from CountAuditLogs")
+
+	// Compare the WHERE clauses
+	if diff := cmp.Diff(getWhereClause, countWhereClause); diff != "" {
+		t.Errorf("GetAuditLogsOffset and CountAuditLogs WHERE clauses must be identical to ensure consistent filtering.\nDiff:\n%s", diff)
+	}
+}
+
+// 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/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/auditlogs.sql b/coderd/database/queries/auditlogs.sql
@@ -223,7 +223,7 @@ END
     -- Filter by build_reason
 AND CASE
 	    WHEN @build_reason::text != '' THEN
-            COALESCE(wb_build.reason::text, wb_workspace.reason::text)  = @build_reason
+            COALESCE(wb_build.reason::text, wb_workspace.reason::text) = @build_reason
         ELSE true
 END
 	-- Filter request_id