fix(websocketspec,mqttspec,resolvemcp): stop casting citext columns to TEXT for LIKE/ILIKE

Same class of bug as the restheadspec/resolvespec fix: these handlers
unconditionally rendered CAST(col AS TEXT) LIKE/ILIKE for every column,
which flips a citext column to case-sensitive matching and defeats a
citext index. Thread the model through to buildFilterCondition/applyFilters
so reflection.IsCitextColumn can skip the cast for citext columns.

resolvemcp's eq/neq/gt/lt paths never cast (they never had the
restheadspec-style reflect.Kind cast heuristic), so this only touches
LIKE/ILIKE. funcspec is unaffected: it has no Go struct model to check
against (colname/value come straight from SQL function parameters).

pkg/mqttspec/handler.go

@@ -720,7 +720,13 @@ func (h *Handler) readMultiple(hookCtx *HookContext) (data interface{}, metadata
 			}
 			op := strings.ToLower(filter.Operator)
 			if op == "like" || op == "ilike" {
-				query = query.Where(fmt.Sprintf("CAST(%s AS TEXT) %s ?", filter.Column, h.getOperatorSQL(filter.Operator)), filter.Value)
+				// citext columns are already case-insensitive; casting to TEXT would
+				// switch to case-sensitive matching and defeat a citext index.
+				if reflection.IsCitextColumn(hookCtx.Model, filter.Column) {
+					query = query.Where(fmt.Sprintf("%s %s ?", filter.Column, h.getOperatorSQL(filter.Operator)), filter.Value)
+				} else {
+					query = query.Where(fmt.Sprintf("CAST(%s AS TEXT) %s ?", filter.Column, h.getOperatorSQL(filter.Operator)), filter.Value)
+				}
 			} else {
 				query = query.Where(fmt.Sprintf("%s %s ?", filter.Column, h.getOperatorSQL(filter.Operator)), filter.Value)
 			}
@@ -786,7 +792,13 @@ func (h *Handler) readMultiple(hookCtx *HookContext) (data interface{}, metadata
 			}
 			op := strings.ToLower(filter.Operator)
 			if op == "like" || op == "ilike" {
-				countQuery = countQuery.Where(fmt.Sprintf("CAST(%s AS TEXT) %s ?", filter.Column, h.getOperatorSQL(filter.Operator)), filter.Value)
+				// citext columns are already case-insensitive; casting to TEXT would
+				// switch to case-sensitive matching and defeat a citext index.
+				if reflection.IsCitextColumn(hookCtx.Model, filter.Column) {
+					countQuery = countQuery.Where(fmt.Sprintf("%s %s ?", filter.Column, h.getOperatorSQL(filter.Operator)), filter.Value)
+				} else {
+					countQuery = countQuery.Where(fmt.Sprintf("CAST(%s AS TEXT) %s ?", filter.Column, h.getOperatorSQL(filter.Operator)), filter.Value)
+				}
 			} else {
 				countQuery = countQuery.Where(fmt.Sprintf("%s %s ?", filter.Column, h.getOperatorSQL(filter.Operator)), filter.Value)
 			}

pkg/reflection/model_utils.go

@@ -9,6 +9,7 @@ import (
 	"time"
 
 	"github.com/bitechdev/ResolveSpec/pkg/modelregistry"
+	"github.com/bitechdev/ResolveSpec/pkg/spectypes"
 )
 
 type PrimaryKeyNameProvider interface {
@@ -728,19 +729,19 @@ func GetColumnTypeFromModel(model interface{}, colName string) reflect.Kind {
 			// Parse JSON tag (format: "name,omitempty")
 			parts := strings.Split(jsonTag, ",")
 			if parts[0] == sourceColName {
-				return field.Type.Kind()
+				return spectypes.UnwrapKind(field.Type)
 			}
 		}
 
 		// Check field name (case-insensitive)
 		if strings.EqualFold(field.Name, sourceColName) {
-			return field.Type.Kind()
+			return spectypes.UnwrapKind(field.Type)
 		}
 
 		// Check snake_case conversion
 		snakeCaseName := ToSnakeCase(field.Name)
 		if snakeCaseName == sourceColName {
-			return field.Type.Kind()
+			return spectypes.UnwrapKind(field.Type)
 		}
 	}
 

pkg/reflection/model_utils_test.go

@@ -3,6 +3,8 @@ package reflection
 import (
 	"reflect"
 	"testing"
+
+	"github.com/bitechdev/ResolveSpec/pkg/spectypes"
 )
 
 // Test models for GORM
@@ -1047,6 +1049,22 @@ func TestGetColumnTypeFromModel(t *testing.T) {
 	}
 }
 
+// SqlNull-wrapped columns (e.g. nullable bigint foreign keys) must report the
+// wrapped value's Kind, not reflect.Struct, so numeric eq/gt/lt filters don't
+// get an unnecessary CAST(... AS TEXT) that defeats the column's index.
+type SqlNullFKModel struct {
+	RidParent spectypes.SqlInt64 `bun:"rid_parent" json:"rid_parent"`
+}
+
+func TestGetColumnTypeFromModel_SqlNullWrapper(t *testing.T) {
+	model := SqlNullFKModel{RidParent: spectypes.NewSqlInt64(90446096)}
+
+	result := GetColumnTypeFromModel(model, "rid_parent")
+	if result != reflect.Int64 {
+		t.Errorf("GetColumnTypeFromModel(rid_parent) = %v, want %v (SqlInt64 must unwrap to its numeric Kind)", result, reflect.Int64)
+	}
+}
+
 // ============= Tests for relation functions =============
 
 // Models for relation testing

pkg/reflection/spectypes_helpers.go

@@ -145,8 +145,16 @@ func IsJSONColumn(model interface{}, colName string) bool {
 // tagDeclaresJSON reports whether an ORM struct tag declares a json/jsonb column
 // type, e.g. `bun:"meta,type:jsonb"` or `gorm:"column:meta;type:json"`.
 func tagDeclaresJSON(tag string) bool {
+	return columnTypeTagValue(tag) == "json" || strings.HasPrefix(columnTypeTagValue(tag), "json(") ||
+		columnTypeTagValue(tag) == "jsonb" || strings.HasPrefix(columnTypeTagValue(tag), "jsonb(")
+}
+
+// columnTypeTagValue extracts the lower-cased value of a `type:` entry from a
+// bun or gorm struct tag, e.g. `bun:"name,type:citext"` -> "citext". Returns ""
+// if the tag carries no `type:` entry.
+func columnTypeTagValue(tag string) string {
 	if tag == "" {
-		return false
+		return ""
 	}
 	for _, part := range strings.FieldsFunc(tag, func(r rune) bool {
 		return r == ',' || r == ';' || r == ' '
@@ -155,12 +163,22 @@ func tagDeclaresJSON(tag string) bool {
 		if !found {
 			continue
 		}
-		value = strings.ToLower(strings.TrimSpace(value))
-		// Match "json" and "jsonb", including parametrised forms just in case.
-		if value == "json" || value == "jsonb" ||
-			strings.HasPrefix(value, "json(") || strings.HasPrefix(value, "jsonb(") {
-			return true
-		}
+		return strings.ToLower(strings.TrimSpace(value))
 	}
-	return false
+	return ""
+}
+
+// IsCitextColumn reports whether colName carries an explicit `type:citext`
+// bun/gorm tag. citext columns must never be CAST(... AS TEXT) for comparisons:
+// that swaps in case-sensitive text semantics and defeats any citext index.
+func IsCitextColumn(model interface{}, colName string) bool {
+	f, ok := getColumnStructField(model, colName)
+	if !ok {
+		return false
+	}
+	tagVal := columnTypeTagValue(f.Tag.Get("bun"))
+	if tagVal == "" {
+		tagVal = columnTypeTagValue(f.Tag.Get("gorm"))
+	}
+	return tagVal == "citext"
 }

pkg/resolvemcp/handler.go

@@ -269,7 +269,7 @@ func (h *Handler) executeRead(ctx context.Context, schema, entity, id string, op
 	}
 
 	// Filters
-	query = h.applyFilters(query, options.Filters)
+	query = h.applyFilters(query, options.Filters, model)
 
 	// Custom operators
 	for _, customOp := range options.CustomOperators {
@@ -751,8 +751,10 @@ func (h *Handler) executeDelete(ctx context.Context, schema, entity, id string)
 	return recordToDelete, nil
 }
 
-// applyFilters applies all filters with OR grouping logic.
-func (h *Handler) applyFilters(query common.SelectQuery, filters []common.FilterOption) common.SelectQuery {
+// applyFilters applies all filters with OR grouping logic. model, when
+// non-nil, lets citext columns be recognised so LIKE/ILIKE compares them
+// natively instead of casting to TEXT (which would defeat a citext index).
+func (h *Handler) applyFilters(query common.SelectQuery, filters []common.FilterOption, model interface{}) common.SelectQuery {
 	if len(filters) == 0 {
 		return query
 	}
@@ -768,10 +770,10 @@ func (h *Handler) applyFilters(query common.SelectQuery, filters []common.Filter
 				orGroup = append(orGroup, filters[j])
 				j++
 			}
-			query = h.applyFilterGroup(query, orGroup)
+			query = h.applyFilterGroup(query, orGroup, model)
 			i = j
 		} else {
-			condition, args := h.buildFilterCondition(filters[i])
+			condition, args := h.buildFilterCondition(filters[i], model)
 			if condition != "" {
 				query = query.Where(condition, args...)
 			}
@@ -782,12 +784,12 @@ func (h *Handler) applyFilters(query common.SelectQuery, filters []common.Filter
 	return query
 }
 
-func (h *Handler) applyFilterGroup(query common.SelectQuery, filters []common.FilterOption) common.SelectQuery {
+func (h *Handler) applyFilterGroup(query common.SelectQuery, filters []common.FilterOption, model interface{}) common.SelectQuery {
 	var conditions []string
 	var args []interface{}
 
 	for _, filter := range filters {
-		condition, filterArgs := h.buildFilterCondition(filter)
+		condition, filterArgs := h.buildFilterCondition(filter, model)
 		if condition != "" {
 			conditions = append(conditions, condition)
 			args = append(args, filterArgs...)
@@ -803,7 +805,14 @@ func (h *Handler) applyFilterGroup(query common.SelectQuery, filters []common.Fi
 	return query.Where("("+strings.Join(conditions, " OR ")+")", args...)
 }
 
-func (h *Handler) buildFilterCondition(filter common.FilterOption) (condition string, args []interface{}) {
+func (h *Handler) buildFilterCondition(filter common.FilterOption, model interface{}) (condition string, args []interface{}) {
+	// citext columns are already case-insensitive; casting to TEXT would
+	// switch to case-sensitive matching and defeat a citext index.
+	likeColumn := filter.Column
+	if !reflection.IsCitextColumn(model, filter.Column) {
+		likeColumn = fmt.Sprintf("CAST(%s AS TEXT)", filter.Column)
+	}
+
 	switch filter.Operator {
 	case "eq", "=":
 		return fmt.Sprintf("%s = ?", filter.Column), []interface{}{filter.Value}
@@ -818,9 +827,9 @@ func (h *Handler) buildFilterCondition(filter common.FilterOption) (condition st
 	case "lte", "<=":
 		return fmt.Sprintf("%s <= ?", filter.Column), []interface{}{filter.Value}
 	case "like":
-		return fmt.Sprintf("CAST(%s AS TEXT) LIKE ?", filter.Column), []interface{}{filter.Value}
+		return fmt.Sprintf("%s LIKE ?", likeColumn), []interface{}{filter.Value}
 	case "ilike":
-		return fmt.Sprintf("CAST(%s AS TEXT) ILIKE ?", filter.Column), []interface{}{filter.Value}
+		return fmt.Sprintf("%s ILIKE ?", likeColumn), []interface{}{filter.Value}
 	case "in":
 		condition, args := common.BuildInCondition(filter.Column, filter.Value)
 		return condition, args

pkg/resolvespec/handler.go

@@ -1937,10 +1937,10 @@ func (h *Handler) buildFilterCondition(filter common.FilterOption, model interfa
 		condition = fmt.Sprintf("%s <= ?", filter.Column)
 		args = []interface{}{filter.Value}
 	case "like":
-		condition = fmt.Sprintf("CAST(%s AS TEXT) LIKE ?", filter.Column)
+		condition = fmt.Sprintf("%s LIKE ?", likeColumn(filter.Column, model))
 		args = []interface{}{filter.Value}
 	case "ilike":
-		condition = fmt.Sprintf("CAST(%s AS TEXT) ILIKE ?", filter.Column)
+		condition = fmt.Sprintf("%s ILIKE ?", likeColumn(filter.Column, model))
 		args = []interface{}{filter.Value}
 	case "in":
 		condition, args = common.BuildInCondition(filter.Column, filter.Value)
@@ -1973,6 +1973,18 @@ func (h *Handler) buildFilterCondition(filter common.FilterOption, model interfa
 	return condition, args
 }
 
+// likeColumn returns the column expression to use for LIKE/ILIKE. citext
+// columns are compared natively — they're already case-insensitive, and
+// CAST(... AS TEXT) would switch to case-sensitive matching and defeat a
+// citext index. Every other column is cast to TEXT so LIKE/ILIKE also works
+// against date/time/timestamp and numeric columns.
+func likeColumn(column string, model interface{}) string {
+	if reflection.IsCitextColumn(model, column) {
+		return column
+	}
+	return fmt.Sprintf("CAST(%s AS TEXT)", column)
+}
+
 func (h *Handler) applyFilter(query common.SelectQuery, filter common.FilterOption, model interface{}) common.SelectQuery {
 	// Determine which method to use based on LogicOperator
 	useOrLogic := strings.EqualFold(filter.LogicOperator, "OR")
@@ -2007,10 +2019,10 @@ func (h *Handler) applyFilter(query common.SelectQuery, filter common.FilterOpti
 		condition = fmt.Sprintf("%s <= ?", filter.Column)
 		args = []interface{}{filter.Value}
 	case "like":
-		condition = fmt.Sprintf("CAST(%s AS TEXT) LIKE ?", filter.Column)
+		condition = fmt.Sprintf("%s LIKE ?", likeColumn(filter.Column, model))
 		args = []interface{}{filter.Value}
 	case "ilike":
-		condition = fmt.Sprintf("CAST(%s AS TEXT) ILIKE ?", filter.Column)
+		condition = fmt.Sprintf("%s ILIKE ?", likeColumn(filter.Column, model))
 		args = []interface{}{filter.Value}
 	case "in":
 		condition, args = common.BuildInCondition(filter.Column, filter.Value)

pkg/restheadspec/filter_cast_test.go

@@ -0,0 +1,139 @@
+package restheadspec
+
+import (
+	"reflect"
+	"testing"
+
+	"github.com/bitechdev/ResolveSpec/pkg/common"
+	"github.com/bitechdev/ResolveSpec/pkg/spectypes"
+)
+
+// atdetailModel mirrors the real-world model that triggered this regression:
+// rid_parent is a nullable bigint foreign key, backed by spectypes.SqlInt64
+// (a SqlNull[int64] alias). An eq filter on it was being rendered as
+// CAST(atdetail.rid_parent AS TEXT) = '90446096', which can't use the index
+// on rid_parent. Name is a citext column, which must never be cast to TEXT
+// either (that would switch to case-sensitive matching and lose its index).
+type atdetailModel struct {
+	RidParent spectypes.SqlInt64 `json:"rid_parent" bun:"rid_parent"`
+	Name      string             `json:"name" bun:"name,type:citext"`
+}
+
+func TestValidateAndAdjustFilterForColumnType_SqlNullNumeric(t *testing.T) {
+	h := &Handler{}
+	model := atdetailModel{}
+
+	filter := &common.FilterOption{Column: "rid_parent", Operator: "eq", Value: "90446096"}
+	info := h.ValidateAndAdjustFilterForColumnType(filter, model)
+
+	if info.NeedsCast {
+		t.Fatalf("expected NeedsCast=false for a numeric SqlInt64 column with a numeric value, got true")
+	}
+	if !info.IsNumericType {
+		t.Fatalf("expected IsNumericType=true for a SqlInt64 column")
+	}
+	if v, ok := filter.Value.(int64); !ok || v != 90446096 {
+		t.Fatalf("expected filter.Value to be converted to int64(90446096), got %#v", filter.Value)
+	}
+}
+
+func TestApplyFilter_SqlNullNumeric_NoCastKeepsIndexUsable(t *testing.T) {
+	h := &Handler{}
+	model := atdetailModel{}
+
+	filter := common.FilterOption{Column: "rid_parent", Operator: "eq", Value: "90446096"}
+	castInfo := h.ValidateAndAdjustFilterForColumnType(&filter, model)
+
+	q := &jsonCapQuery{}
+	h.applyFilter(q, filter, "public.atdetail", castInfo.NeedsCast, "AND", model)
+
+	c := q.only(t)
+	const want = "atdetail.rid_parent = ?"
+	if c.query != want {
+		t.Fatalf("query = %q, want %q (must not CAST a numeric column to TEXT)", c.query, want)
+	}
+	if !reflect.DeepEqual(c.args, []interface{}{int64(90446096)}) {
+		t.Fatalf("args = %#v", c.args)
+	}
+}
+
+// TestFieldFilterHeader_SqlNullNumeric_EndToEnd reproduces the exact reported
+// regression: a request carrying the header
+//
+//	x-fieldfilter-rid_parent: 90446096
+//
+// against a model whose rid_parent field is a nullable bigint (spectypes.SqlInt64).
+// Before the fix, this parsed to a filter that got CAST(atdetail.rid_parent AS TEXT) = '90446096',
+// making the query unable to use the index on rid_parent. It must now parse to
+// a native "atdetail.rid_parent = ?" comparison with an int64 argument.
+func TestFieldFilterHeader_SqlNullNumeric_EndToEnd(t *testing.T) {
+	h := NewHandler(nil, nil)
+	model := atdetailModel{}
+
+	req := &MockRequest{
+		headers: map[string]string{
+			"x-fieldfilter-rid_parent": "90446096",
+		},
+		queryParams: map[string]string{},
+	}
+
+	options := h.parseOptionsFromHeaders(req, model)
+	if len(options.Filters) != 1 {
+		t.Fatalf("expected 1 filter parsed from x-fieldfilter-rid_parent, got %d: %+v", len(options.Filters), options.Filters)
+	}
+
+	filter := options.Filters[0]
+	if filter.Column != "rid_parent" || filter.Operator != "eq" {
+		t.Fatalf("unexpected parsed filter: %+v", filter)
+	}
+	if filter.Value != "90446096" {
+		t.Fatalf("expected raw header string value before type validation, got %#v", filter.Value)
+	}
+
+	// This is the exact step that decided whether to CAST: ValidateAndAdjustFilterForColumnType
+	// used to see reflect.Struct for the SqlInt64-wrapped column and cast to TEXT.
+	castInfo := h.ValidateAndAdjustFilterForColumnType(&filter, model)
+	if castInfo.NeedsCast {
+		t.Fatalf("regression: numeric SqlInt64 column x-fieldfilter-rid_parent got NeedsCast=true, " +
+			"which renders CAST(atdetail.rid_parent AS TEXT) = '90446096' and defeats the column's index")
+	}
+
+	q := &jsonCapQuery{}
+	h.applyFilter(q, filter, "public.atdetail", castInfo.NeedsCast, filter.LogicOperator, model)
+
+	c := q.only(t)
+	const want = "atdetail.rid_parent = ?"
+	if c.query != want {
+		t.Fatalf("SQL condition = %q, want %q (no CAST, so the rid_parent index can still be used)", c.query, want)
+	}
+	if !reflect.DeepEqual(c.args, []interface{}{int64(90446096)}) {
+		t.Fatalf("args = %#v, want [int64(90446096)]", c.args)
+	}
+}
+
+func TestApplyFilter_Citext_NeverCastForEqOrIlike(t *testing.T) {
+	h := &Handler{}
+	model := atdetailModel{}
+
+	t.Run("eq", func(t *testing.T) {
+		filter := common.FilterOption{Column: "name", Operator: "eq", Value: "Acme"}
+		castInfo := h.ValidateAndAdjustFilterForColumnType(&filter, model)
+		if castInfo.NeedsCast {
+			t.Fatalf("citext column must never need a CAST")
+		}
+		q := &jsonCapQuery{}
+		h.applyFilter(q, filter, "public.atdetail", castInfo.NeedsCast, "AND", model)
+		if c := q.only(t); c.query != "atdetail.name = ?" {
+			t.Fatalf("query = %q", c.query)
+		}
+	})
+
+	t.Run("ilike", func(t *testing.T) {
+		filter := common.FilterOption{Column: "name", Operator: "ilike", Value: "%acme%"}
+		q := &jsonCapQuery{}
+		h.applyFilter(q, filter, "public.atdetail", false, "AND", model)
+		if c := q.only(t); c.query != "atdetail.name ILIKE ?" {
+			t.Fatalf("query = %q, want no CAST for a citext column", c.query)
+		}
+	})
+}

pkg/restheadspec/handler.go

@@ -2325,6 +2325,13 @@ func (h *Handler) applyFilter(query common.SelectQuery, filter common.FilterOpti
 		qualifiedColumn = fmt.Sprintf("CAST(%s AS TEXT)", rawQualifiedColumn)
 	}
 
+	// citext columns already compare case-insensitively; casting to TEXT for
+	// LIKE/ILIKE would switch to case-sensitive matching and defeat a citext index.
+	likeColumn := rawQualifiedColumn
+	if !reflection.IsCitextColumn(model, filter.Column) {
+		likeColumn = fmt.Sprintf("CAST(%s AS TEXT)", rawQualifiedColumn)
+	}
+
 	switch strings.ToLower(filter.Operator) {
 	case "eq", "equals":
 		return applyWhere(fmt.Sprintf("%s = ?", qualifiedColumn), filter.Value)
@@ -2339,11 +2346,14 @@ func (h *Handler) applyFilter(query common.SelectQuery, filter common.FilterOpti
 	case "lte", "less_than_equals", "le":
 		return applyWhere(fmt.Sprintf("%s <= ?", qualifiedColumn), filter.Value)
 	case "like":
-		// Always cast to TEXT for LIKE/ILIKE to support date/time/timestamp columns
-		return applyWhere(fmt.Sprintf("CAST(%s AS TEXT) LIKE ?", rawQualifiedColumn), filter.Value)
+		// Cast to TEXT for LIKE to support date/time/timestamp columns; citext
+		// columns are compared natively (see likeColumn above).
+		return applyWhere(fmt.Sprintf("%s LIKE ?", likeColumn), filter.Value)
 	case "ilike":
-		// Always cast to TEXT for LIKE/ILIKE to support date/time/timestamp columns
-		return applyWhere(fmt.Sprintf("CAST(%s AS TEXT) ILIKE ?", rawQualifiedColumn), filter.Value)
+		// Cast to TEXT for ILIKE to support date/time/timestamp columns; citext
+		// columns are compared natively (see likeColumn above) since citext is
+		// already case-insensitive.
+		return applyWhere(fmt.Sprintf("%s ILIKE ?", likeColumn), filter.Value)
 	case "in":
 		cond, inArgs := common.BuildInCondition(qualifiedColumn, filter.Value)
 		if cond == "" {
@@ -2421,8 +2431,12 @@ func (h *Handler) applyOrFilterGroup(query common.SelectQuery, filters []*common
 
 		op := strings.ToLower(filter.Operator)
 		if op == "like" || op == "ilike" {
-			// Always cast to TEXT for LIKE/ILIKE to support date/time/timestamp columns
-			qualifiedColumn = fmt.Sprintf("CAST(%s AS TEXT)", rawQualifiedColumn)
+			// Cast to TEXT for LIKE/ILIKE to support date/time/timestamp columns.
+			// citext columns are left native: they're already case-insensitive and
+			// casting would defeat a citext index.
+			if !reflection.IsCitextColumn(model, filter.Column) {
+				qualifiedColumn = fmt.Sprintf("CAST(%s AS TEXT)", rawQualifiedColumn)
+			}
 		} else if castInfo[i].NeedsCast {
 			// Apply casting to text if needed for non-numeric columns or non-numeric values
 			qualifiedColumn = fmt.Sprintf("CAST(%s AS TEXT)", rawQualifiedColumn)

pkg/restheadspec/headers.go

@@ -1466,6 +1466,12 @@ func (h *Handler) ValidateAndAdjustFilterForColumnType(filter *common.FilterOpti
 		return ColumnCastInfo{NeedsCast: false, IsNumericType: false}
 	}
 
+	// Never cast citext columns to TEXT: CAST(col AS TEXT) swaps in case-sensitive
+	// comparison semantics and prevents PostgreSQL from using a citext index.
+	if reflection.IsCitextColumn(model, filter.Column) {
+		return ColumnCastInfo{NeedsCast: false, IsNumericType: false}
+	}
+
 	colType := reflection.GetColumnTypeFromModel(model, filter.Column)
 	if colType == reflect.Invalid {
 		// Column not found in model, no casting needed

pkg/spectypes/sql_types.go

@@ -425,9 +425,7 @@ func (t SqlTime) MarshalJSON() ([]byte, error) {
 		return []byte("null"), nil
 	}
 	s := t.Val.Format("15:04:05")
-	if s == "00:00:00" {
-		return []byte("null"), nil
-	}
+
 	return []byte(fmt.Sprintf(`"%s"`, s)), nil
 }
 
@@ -435,9 +433,7 @@ func (t *SqlTime) UnmarshalJSON(b []byte) error {
 	if err := t.SqlNull.UnmarshalJSON(b); err != nil {
 		return err
 	}
-	if t.Valid && t.Val.Format("15:04:05") == "00:00:00" {
-		t.Valid = false
-	}
+
 	return nil
 }
 

pkg/spectypes/sql_types_test.go

@@ -920,6 +920,411 @@ func TestSqlString_RoundTrip(t *testing.T) {
 	}
 }
 
+// TestSqlBool_Scan tests SqlBool Scan from various input types.
+func TestSqlBool_Scan(t *testing.T) {
+	tests := []struct {
+		name     string
+		input    interface{}
+		expected bool
+		valid    bool
+	}{
+		{"bool true", true, true, true},
+		{"bool false", false, false, true},
+		{"string true", "true", true, true},
+		{"string 1", "1", true, true},
+		{"int64 1 fallback", int64(1), true, true},
+		{"int64 0 fallback", int64(0), false, true},
+		{"nil", nil, false, false},
+	}
+
+	for _, tt := range tests {
+		t.Run(tt.name, func(t *testing.T) {
+			var b SqlBool
+			if err := b.Scan(tt.input); err != nil {
+				t.Fatalf("Scan failed: %v", err)
+			}
+			if b.Valid != tt.valid {
+				t.Errorf("expected valid=%v, got valid=%v", tt.valid, b.Valid)
+			}
+			if tt.valid && b.Val != tt.expected {
+				t.Errorf("expected %v, got %v", tt.expected, b.Val)
+			}
+		})
+	}
+}
+
+func TestSqlBool_Value(t *testing.T) {
+	b := NewSqlBool(true)
+	val, err := b.Value()
+	if err != nil {
+		t.Fatalf("Value failed: %v", err)
+	}
+	if val != true {
+		t.Errorf("expected true, got %v", val)
+	}
+
+	b2 := SqlBool{Valid: false}
+	val2, err := b2.Value()
+	if err != nil {
+		t.Fatalf("Value failed: %v", err)
+	}
+	if val2 != nil {
+		t.Errorf("expected nil, got %v", val2)
+	}
+}
+
+func TestSqlBool_JSON(t *testing.T) {
+	b := NewSqlBool(true)
+	data, err := json.Marshal(b)
+	if err != nil {
+		t.Fatalf("Marshal failed: %v", err)
+	}
+	if string(data) != "true" {
+		t.Errorf("expected true, got %s", string(data))
+	}
+
+	var b2 SqlBool
+	if err := json.Unmarshal([]byte("false"), &b2); err != nil {
+		t.Fatalf("Unmarshal failed: %v", err)
+	}
+	if !b2.Valid || b2.Val != false {
+		t.Errorf("expected valid=true val=false, got valid=%v val=%v", b2.Valid, b2.Val)
+	}
+
+	var b3 SqlBool
+	if err := json.Unmarshal([]byte("null"), &b3); err != nil {
+		t.Fatalf("Unmarshal null failed: %v", err)
+	}
+	if b3.Valid {
+		t.Error("expected invalid after unmarshaling null")
+	}
+}
+
+// TestSqlNull_FromString_EdgeCases tests FromString edge cases shared by all SqlNull instantiations.
+func TestSqlNull_FromString_EdgeCases(t *testing.T) {
+	t.Run("empty string is null", func(t *testing.T) {
+		var n SqlInt64
+		if err := n.FromString(""); err != nil {
+			t.Fatalf("FromString failed: %v", err)
+		}
+		if n.Valid {
+			t.Error("expected invalid for empty string")
+		}
+	})
+
+	t.Run("NULL case-insensitive", func(t *testing.T) {
+		var n SqlString
+		if err := n.FromString("NuLL"); err != nil {
+			t.Fatalf("FromString failed: %v", err)
+		}
+		if n.Valid {
+			t.Error("expected invalid for 'NuLL'")
+		}
+	})
+
+	t.Run("whitespace trimmed", func(t *testing.T) {
+		var n SqlInt64
+		if err := n.FromString("  42  "); err != nil {
+			t.Fatalf("FromString failed: %v", err)
+		}
+		if !n.Valid || n.Val != 42 {
+			t.Errorf("expected valid=true val=42, got valid=%v val=%v", n.Valid, n.Val)
+		}
+	})
+
+	t.Run("invalid int string stays invalid", func(t *testing.T) {
+		var n SqlInt64
+		if err := n.FromString("not-a-number"); err != nil {
+			t.Fatalf("FromString failed: %v", err)
+		}
+		if n.Valid {
+			t.Error("expected invalid for non-numeric string")
+		}
+	})
+
+	t.Run("float string truncated into int type", func(t *testing.T) {
+		var n SqlInt64
+		if err := n.FromString("3.7"); err != nil {
+			t.Fatalf("FromString failed: %v", err)
+		}
+		if !n.Valid || n.Val != 3 {
+			t.Errorf("expected valid=true val=3, got valid=%v val=%v", n.Valid, n.Val)
+		}
+	})
+
+	t.Run("invalid bool string stays invalid", func(t *testing.T) {
+		var n SqlBool
+		if err := n.FromString("maybe"); err != nil {
+			t.Fatalf("FromString failed: %v", err)
+		}
+		if n.Valid {
+			t.Error("expected invalid for non-bool string")
+		}
+	})
+}
+
+// TestSqlNull_String tests the String() stringer fallback.
+func TestSqlNull_String(t *testing.T) {
+	t.Run("invalid returns empty", func(t *testing.T) {
+		n := SqlInt64{Valid: false}
+		if n.String() != "" {
+			t.Errorf("expected empty string, got %q", n.String())
+		}
+	})
+
+	t.Run("stringer type delegates", func(t *testing.T) {
+		u := uuid.New()
+		n := NewSqlUUID(u)
+		if n.String() != u.String() {
+			t.Errorf("expected %s, got %s", u.String(), n.String())
+		}
+	})
+
+	t.Run("non-stringer falls back to fmt", func(t *testing.T) {
+		n := NewSqlInt64(42)
+		if n.String() != "42" {
+			t.Errorf("expected 42, got %s", n.String())
+		}
+	})
+}
+
+// TestNewSql_Generic tests the generic NewSql constructor.
+func TestNewSql_Generic(t *testing.T) {
+	t.Run("exact type match", func(t *testing.T) {
+		n := NewSql[int64](int64(5))
+		if !n.Valid || n.Val != 5 {
+			t.Errorf("expected valid=true val=5, got valid=%v val=%v", n.Valid, n.Val)
+		}
+	})
+
+	t.Run("nil value", func(t *testing.T) {
+		n := NewSql[int64](nil)
+		if n.Valid {
+			t.Error("expected invalid for nil")
+		}
+	})
+
+	t.Run("from another SqlNull", func(t *testing.T) {
+		src := SqlNull[int64]{Val: 9, Valid: true}
+		n := NewSql[int64](src)
+		if !n.Valid || n.Val != 9 {
+			t.Errorf("expected valid=true val=9, got valid=%v val=%v", n.Valid, n.Val)
+		}
+	})
+
+	t.Run("string conversion fallback", func(t *testing.T) {
+		n := NewSql[string](42)
+		if !n.Valid || n.Val != "42" {
+			t.Errorf("expected valid=true val=42, got valid=%v val=%q", n.Valid, n.Val)
+		}
+	})
+}
+
+// TestSqlNull_Int64_Conversions tests Int64() across differently-typed SqlNull values.
+func TestSqlNull_Int64_Conversions(t *testing.T) {
+	if v := (SqlNull[string]{Val: "42", Valid: true}).Int64(); v != 42 {
+		t.Errorf("expected 42, got %d", v)
+	}
+	if v := (SqlNull[bool]{Val: true, Valid: true}).Int64(); v != 1 {
+		t.Errorf("expected 1, got %d", v)
+	}
+	if v := (SqlNull[bool]{Val: false, Valid: true}).Int64(); v != 0 {
+		t.Errorf("expected 0, got %d", v)
+	}
+	if v := (SqlNull[float64]{Val: 3.9, Valid: true}).Int64(); v != 3 {
+		t.Errorf("expected 3, got %d", v)
+	}
+	if v := (SqlNull[int64]{Valid: false}).Int64(); v != 0 {
+		t.Errorf("expected 0 for invalid, got %d", v)
+	}
+}
+
+// TestSqlNull_Float64_Conversions tests Float64() across differently-typed SqlNull values.
+func TestSqlNull_Float64_Conversions(t *testing.T) {
+	if v := (SqlNull[string]{Val: "3.14", Valid: true}).Float64(); v != 3.14 {
+		t.Errorf("expected 3.14, got %v", v)
+	}
+	if v := (SqlNull[int64]{Val: 10, Valid: true}).Float64(); v != 10.0 {
+		t.Errorf("expected 10.0, got %v", v)
+	}
+	if v := (SqlNull[float64]{Valid: false}).Float64(); v != 0.0 {
+		t.Errorf("expected 0.0 for invalid, got %v", v)
+	}
+}
+
+// TestSqlNull_Bool_Conversions tests Bool() across differently-typed SqlNull values.
+func TestSqlNull_Bool_Conversions(t *testing.T) {
+	if v := (SqlNull[string]{Val: "YES", Valid: true}).Bool(); v != true {
+		t.Error("expected true for 'YES'")
+	}
+	if v := (SqlNull[string]{Val: "no", Valid: true}).Bool(); v != false {
+		t.Error("expected false for 'no'")
+	}
+	if v := (SqlNull[int]{Val: 1, Valid: true}).Bool(); v != true {
+		t.Error("expected true for int 1")
+	}
+	if v := (SqlNull[int]{Val: 0, Valid: true}).Bool(); v != false {
+		t.Error("expected false for int 0")
+	}
+	if v := (SqlNull[bool]{Valid: false}).Bool(); v != false {
+		t.Error("expected false for invalid")
+	}
+}
+
+// TestSqlNull_Time_NonTimeType verifies Time() returns zero value when T is not time.Time.
+func TestSqlNull_Time_NonTimeType(t *testing.T) {
+	n := SqlNull[string]{Val: "2024-01-15", Valid: true}
+	if !n.Time().IsZero() {
+		t.Error("expected zero time for non-time.Time SqlNull")
+	}
+}
+
+// TestSqlNull_UUID_NonUUIDType verifies UUID() returns uuid.Nil when T is not uuid.UUID.
+func TestSqlNull_UUID_NonUUIDType(t *testing.T) {
+	n := SqlNull[string]{Val: "not-a-uuid", Valid: true}
+	if n.UUID() != uuid.Nil {
+		t.Error("expected uuid.Nil for non-uuid.UUID SqlNull")
+	}
+}
+
+// TestSqlTime_Midnight verifies midnight times are serialized as "00:00:00", not null.
+func TestSqlTime_Midnight(t *testing.T) {
+	midnight := time.Date(2024, 1, 1, 0, 0, 0, 0, time.UTC)
+	tm := NewSqlTime(midnight)
+
+	data, err := json.Marshal(tm)
+	if err != nil {
+		t.Fatalf("Marshal failed: %v", err)
+	}
+	if string(data) != `"00:00:00"` {
+		t.Errorf("expected \"00:00:00\", got %s", string(data))
+	}
+
+	val, err := tm.Value()
+	if err != nil {
+		t.Fatalf("Value failed: %v", err)
+	}
+	if val != "00:00:00" {
+		t.Errorf("expected 00:00:00, got %v", val)
+	}
+}
+
+func TestSqlTime_Value_Invalid(t *testing.T) {
+	tm := SqlTime{}
+	val, err := tm.Value()
+	if err != nil {
+		t.Fatalf("Value failed: %v", err)
+	}
+	if val != nil {
+		t.Errorf("expected nil, got %v", val)
+	}
+}
+
+// TestSqlDate_ZeroValueString verifies String() blanks out sentinel zero dates.
+func TestSqlDate_ZeroValueString(t *testing.T) {
+	d := SqlDate{SqlNull: SqlNull[time.Time]{Val: time.Time{}, Valid: true}}
+	if d.String() != "" {
+		t.Errorf("expected empty string for zero date, got %q", d.String())
+	}
+
+	sentinel := time.Date(1800, 12, 31, 0, 0, 0, 0, time.UTC)
+	d2 := SqlDate{SqlNull: SqlNull[time.Time]{Val: sentinel, Valid: true}}
+	if d2.String() != "" {
+		t.Errorf("expected empty string for 1800-12-31 sentinel, got %q", d2.String())
+	}
+}
+
+// TestSqlTimeStamp_Value tests driver.Valuer for SqlTimeStamp, including the pre-year-2 cutoff.
+func TestSqlTimeStamp_Value(t *testing.T) {
+	t.Run("valid recent timestamp", func(t *testing.T) {
+		ts := NewSqlTimeStamp(time.Date(2024, 1, 15, 10, 30, 0, 0, time.UTC))
+		val, err := ts.Value()
+		if err != nil {
+			t.Fatalf("Value failed: %v", err)
+		}
+		if val != "2024-01-15T10:30:00Z" {
+			t.Errorf("expected 2024-01-15T10:30:00Z, got %v", val)
+		}
+	})
+
+	t.Run("year 1 is treated as null", func(t *testing.T) {
+		ts := NewSqlTimeStamp(time.Date(1, 1, 1, 0, 0, 0, 0, time.UTC))
+		val, err := ts.Value()
+		if err != nil {
+			t.Fatalf("Value failed: %v", err)
+		}
+		if val != nil {
+			t.Errorf("expected nil, got %v", val)
+		}
+	})
+
+	t.Run("invalid is null", func(t *testing.T) {
+		ts := SqlTimeStamp{}
+		val, err := ts.Value()
+		if err != nil {
+			t.Fatalf("Value failed: %v", err)
+		}
+		if val != nil {
+			t.Errorf("expected nil, got %v", val)
+		}
+	})
+}
+
+func TestSqlTimeStamp_UnmarshalJSON_YearOneInvalid(t *testing.T) {
+	var ts SqlTimeStamp
+	if err := json.Unmarshal([]byte(`"0001-01-01T00:00:00Z"`), &ts); err != nil {
+		t.Fatalf("Unmarshal failed: %v", err)
+	}
+	if ts.Valid {
+		t.Error("expected invalid for year 0001 timestamp")
+	}
+}
+
+// TestTryParseDT tests the internal multi-format date/time parser.
+func TestTryParseDT(t *testing.T) {
+	tests := []struct {
+		name  string
+		input string
+	}{
+		{"RFC3339", "2024-01-15T10:30:00Z"},
+		{"date only", "2024-01-15"},
+		{"datetime no tz", "2024-01-15T10:30:00"},
+		{"space separated", "2024-01-15 10:30:00"},
+		{"UK date slash", "15/01/2024"},
+		{"UK date dash", "15-01-2024"},
+		{"time only", "10:30:00"},
+		{"short time", "10:30"},
+	}
+
+	for _, tt := range tests {
+		t.Run(tt.name, func(t *testing.T) {
+			tm, err := tryParseDT(tt.input)
+			if err != nil {
+				t.Fatalf("tryParseDT failed for %q: %v", tt.input, err)
+			}
+			if tm.IsZero() {
+				t.Errorf("expected non-zero time for %q", tt.input)
+			}
+		})
+	}
+
+	t.Run("invalid format", func(t *testing.T) {
+		_, err := tryParseDT("not a date at all")
+		if err == nil {
+			t.Error("expected error for unparseable string")
+		}
+	})
+}
+
+// TestToJSONDT tests RFC3339 formatting helper.
+func TestToJSONDT(t *testing.T) {
+	dt := time.Date(2024, 1, 15, 10, 30, 0, 0, time.UTC)
+	expected := dt.Format(time.RFC3339)
+	if got := ToJSONDT(dt); got != expected {
+		t.Errorf("expected %s, got %s", expected, got)
+	}
+}
+
 // TestSqlByteArray_Base64_RoundTrip tests complete round-trip: Go -> JSON -> Go -> SQL -> Go
 func TestSqlByteArray_Base64_RoundTrip(t *testing.T) {
 	original := []byte{0x48, 0x65, 0x6C, 0x6C, 0x6F, 0x20, 0xFF, 0xFE} // "Hello " + binary data

pkg/spectypes/type_names.go

@@ -97,3 +97,27 @@ func IsJSONType(t reflect.Type) bool {
 	n, ok := SQLTypeName(t)
 	return ok && (n == "jsonb" || n == "json")
 }
+
+// UnwrapKind returns the reflect.Kind to use when reasoning about a column's
+// comparability (numeric vs. string vs. other) for filter building. Plain Go
+// types return their own Kind unchanged. spectypes.SqlNull[T] wrappers (and
+// types that embed one, such as SqlTimeStamp/SqlDate/SqlTime) always report
+// reflect.Struct for their own Kind even when T is an int64 or string, which
+// would otherwise make numeric/text columns look "complex" and force an
+// unnecessary CAST(... AS TEXT) that defeats native column indexes. For those
+// wrappers, UnwrapKind returns the Kind of the wrapped value T instead.
+func UnwrapKind(t reflect.Type) reflect.Kind {
+	for t != nil && t.Kind() == reflect.Pointer {
+		t = t.Elem()
+	}
+	if t == nil {
+		return reflect.Invalid
+	}
+	if t.Kind() != reflect.Struct || t.PkgPath() != pkgPath {
+		return t.Kind()
+	}
+	if f, ok := t.FieldByName("Val"); ok {
+		return f.Type.Kind()
+	}
+	return t.Kind()
+}

pkg/websocketspec/handler.go

@@ -863,6 +863,11 @@ func (h *Handler) buildFilterCondition(filter common.FilterOption, model interfa
 	op := strings.ToLower(filter.Operator)
 	if op == "like" || op == "ilike" {
 		operatorSQL := h.getOperatorSQL(filter.Operator)
+		// citext columns are already case-insensitive; casting to TEXT would
+		// switch to case-sensitive matching and defeat a citext index.
+		if reflection.IsCitextColumn(model, filter.Column) {
+			return fmt.Sprintf("%s %s ?", filter.Column, operatorSQL), []interface{}{filter.Value}
+		}
 		return fmt.Sprintf("CAST(%s AS TEXT) %s ?", filter.Column, operatorSQL), []interface{}{filter.Value}
 	}
 	operatorSQL := h.getOperatorSQL(filter.Operator)