Function buildFilterConditions

internal/database/postgres.go:89–145

buildFilterConditions constructs WHERE clause conditions from a ServerFilter nolint:unparam // argIndex is always 1 currently but kept for API flexibility

(filter *ServerFilter, argIndex int)

//
//nolint:unparam // argIndex is always 1 currently but kept for API flexibility
func buildFilterConditions(filter *ServerFilter, argIndex int) ([]string, []any, int) {
	var conditions []string
	var args []any

	if filter == nil {
		return conditions, args, argIndex
	}

	if filter.Name != nil {
		conditions = append(conditions, fmt.Sprintf("server_name = $%d", argIndex))
		args = append(args, *filter.Name)
		argIndex++
	}
	if filter.RemoteURL != nil {
		// Use a JSONB containment predicate so the planner can use the GIN index
		// idx_servers_json_remotes on (value -> 'remotes'). The previously-used
		// EXISTS / jsonb_array_elements / ->> form is logically equivalent but
		// the planner can't translate the per-row array unfolding into a GIN
		// search — it falls back to scanning every row. validateNoDuplicateRemoteURLs
		// in the publish path ran this filter and was measured at ~10s on prod's
		// 21K-row table on 2026-04-28; the containment form is GIN-indexable and
		// completes in low single-digit ms.
		conditions = append(conditions, fmt.Sprintf("value -> 'remotes' @> jsonb_build_array(jsonb_build_object('url', $%d::text))", argIndex))
		args = append(args, *filter.RemoteURL)
		argIndex++
	}
	if filter.UpdatedSince != nil {
		conditions = append(conditions, fmt.Sprintf("updated_at > $%d", argIndex))
		args = append(args, *filter.UpdatedSince)
		argIndex++
	}
	if filter.SubstringName != nil {
		// Escape LIKE metacharacters so that user input cannot expand into
		// wildcard matches (e.g. `?search=_` matching every single-char name,
		// `?search=%` matching everything). Order matters: backslashes must be
		// escaped first so subsequent escape backslashes are not double-escaped.
		escaped := strings.NewReplacer(`\`, `\\`, `%`, `\%`, `_`, `\_`).Replace(*filter.SubstringName)
		conditions = append(conditions, fmt.Sprintf("server_name ILIKE $%d ESCAPE '\\'", argIndex))
		args = append(args, "%"+escaped+"%")
		argIndex++
	}
	if filter.Version != nil {
		conditions = append(conditions, fmt.Sprintf("version = $%d", argIndex))
		args = append(args, *filter.Version)
		argIndex++
	}
	if filter.IsLatest != nil {
		conditions = append(conditions, fmt.Sprintf("is_latest = $%d", argIndex))
		args = append(args, *filter.IsLatest)
		argIndex++
	}
	if filter.IncludeDeleted == nil || !*filter.IncludeDeleted {
		conditions = append(conditions, "status != 'deleted'")
	}

	return conditions, args, argIndex
}