In this issue (11 sections)
In Issue 6, we built memory — the agent remembers search results. Now we tackle a subtle but critical problem: how do you order those results?
User says: “Show me the best laptop under $1000.”
Best by what? Cheapest? Best rated? Best battery? Fastest CPU? Best display?
We returned results sorted by created_at (newest first). The user expected “best value.” They left frustrated.
In this issue:
- Why “best” is ambiguous (and how to define it)
- Sortable field registry (preferred direction per field)
- JSONB sorting (the SQL expression nightmare)
- LLM-inferred sorting from keywords
Sorting isn’t just ORDER BY. It’s semantic understanding of what “best” means.
History Anchor: Statistical Machine Translation to Transformers
Ranking search results is one of the oldest problems in information retrieval. TF-IDF (1972) and BM25 ranked by keyword frequency. Learning-to-Rank (2000s) used ML models trained on click data. LLMs add a new capability: inferring sort intent from natural language (“best” becomes rating DESC, “cheapest” becomes price ASC). The sortable field registry bridges the semantic gap between what users say and what SQL can execute.
The Disasters
Disaster 1: The “Best” Ambiguity
USER: "Show me the best laptop under $1000"
WHAT IS "BEST"?
• Cheapest? ($699)
• Best rated? (4.8 stars)
• Best battery? (12 hours)
• Fastest CPU? (i7-12700H)
• Best screen? (4K OLED)
BOT RETURNED: Sorted by created_at (newest first) ❌The user asked for “best.” We had no definition of what “best” means for laptops. So we defaulted to creation date. Useless.
Why it happened: No component-specific default sort. No definition of what “best” means for this product category.
Disaster 2: The JSONB Nightmare
USER: "Show me the cheapest options"
↓
PRICE STORED IN JSONB: {"unit_price": 899, "bulk_price": 799, "currency": "USD"}
↓
QUERY: ORDER BY pricing ❌
↓
RESULT:
1. {"unit_price": 100.00} ← "1" comes first alphabetically
2. {"unit_price": 899.00} ← "8" comes next
3. {"unit_price": 9.99} ← "9" comes last!$9.99 came last instead of first because JSONB sorts as text, not numbers. Lexicographically, “1” < “8” < “9”, so $100.00 sorts before $9.99.
Why it happened: JSONB needs explicit SQL expressions with type casting. ORDER BY pricing sorts the JSON string, not the numeric value inside.
Disaster 3: The Direction Confusion
USER: "Show me MOSFETs with best Rds(on)"
↓
BOT SORTED: Highest Rds first (DESC) ❌
• 0.5Ω
• 0.1Ω
• 0.05Ω ← This is actually best!
↓
CORRECT: Lowest Rds first (ASC) - lower resistance is betterFor most metrics, higher is better (rating, battery life). For Rds(on), lower is better. The bot assumed DESC.
Why it happened: No preferred direction defined per field. The system didn’t know that for r_ds_on, ASC is “best.”
The Pattern
Sorting needs explicit definitions: what fields are sortable, what 'best' means for each field, and how to handle JSONB with SQL expressions.
The Diagnosis: Why ORDER BY Alone Fails
The Semantic Gap
┌──────────────────────────────────────────────────────────┐
│ WHAT SQL KNOWS │
├──────────────────────────────────────────────────────────┤
│ • ASC = ascending order (1, 2, 3...) │
│ • DESC = descending order (3, 2, 1...) │
│ • NULLS FIRST / NULLS LAST │
│ • How to sort typed columns (INTEGER, TEXT, NUMERIC) │
└──────────────────────────────────────────────────────────┘
┌──────────────────────────────────────────────────────────┐
│ WHAT SQL DOESN'T KNOW │
├──────────────────────────────────────────────────────────┤
│ ❌ What "best" means for each field │
│ ❌ That "cheapest" means price ASC │
│ ❌ That "best Rds" means r_ds_on ASC (lower is better) │
│ ❌ That "highest current" means i_d_max DESC │
│ ❌ How to extract JSONB values for numeric sorting │
└──────────────────────────────────────────────────────────┘The JSONB Problem
TYPED COLUMN (Easy):
ORDER BY price ASC ✓
→ Sorts numerically: 9.99, 100.00, 899.00
JSONB COLUMN (Hard):
ORDER BY pricing ❌
→ Sorts as text: "100.00", "899.00", "9.99" (lexicographic!)
CORRECT JSONB SORT:
ORDER BY (pricing->>'unit_price')::NUMERIC ASC ✓
↑ ↑
Extract value Cast to number
→ Sorts numerically: 9.99, 100.00, 899.00The Sortable Field Registry
What It Contains
For each sortable field, you define:
┌─────────────────────────────────────────────────────────┐
│ SORTABLE FIELD DEFINITION │
├─────────────────────────────────────────────────────────┤
│ │
│ 1. FIELD NAME: Logical name (what LLM extracts) │
│ 2. DB EXPRESSION: Actual SQL for ORDER BY │
│ 3. PREFERRED DIRECTION: What "best" means (ASC/DESC) │
│ 4. ALIASES: Natural language triggers │
│ 5. DESCRIPTION: Human-readable explanation │
│ │
└─────────────────────────────────────────────────────────┘The Implementation
from dataclasses import dataclass
from enum import Enum
from typing import List
class SortDirection(Enum):
ASC = "ASC"
DESC = "DESC"
@dataclass
class SortableField:
field_name: str
db_expression: str # Actual SQL for ORDER BY
preferred_direction: SortDirection # What "best" means
aliases: List[str] # Keywords that trigger this sort
description: strExample: MOSFET Sortable Fields
MOSFET_SORTABLE = {
"r_ds_on": SortableField(
field_name="r_ds_on",
db_expression="r_ds_on",
preferred_direction=SortDirection.ASC, # Lower is better
aliases=["lowest rds", "best rds", "best resistance", "best"],
description="On-resistance in milliohms - lower means less power loss"
),
"i_d_max": SortableField(
field_name="i_d_max",
db_expression="i_d_max",
preferred_direction=SortDirection.DESC, # Higher is better
aliases=["highest current", "most current", "max current"],
description="Maximum drain current in amps - higher means more power"
),
"v_ds_max": SortableField(
field_name="v_ds_max",
db_expression="v_ds_max",
preferred_direction=SortDirection.DESC, # Higher is better
aliases=["highest voltage", "max voltage"],
description="Maximum drain-source voltage - higher means more headroom"
),
"pricing": SortableField(
field_name="pricing",
db_expression="(pricing->>'unit_price')::NUMERIC", # JSONB!
preferred_direction=SortDirection.ASC, # Lower is better
aliases=["cheapest", "lowest price", "most affordable", "budget"],
description="Unit price in USD - lower means cheaper"
),
} Key Insight
Each field knows its own “best” direction. For
r_ds_on, lower is better (ASC). For i_d_max, higher is better (DESC). This is what turns ambiguous “best” into a concrete ORDER BY clause. JSONB Sorting
The Problem
JSONB fields can’t be sorted directly. You need SQL expressions.
JSONB COLUMN VALUE:
pricing = {"unit_price": 899.00, "bulk_price": 799.00, "currency": "USD"}
WRONG (sorts as text):
ORDER BY pricing
→ Alphabetical sort on JSON string
RIGHT (extracts and casts):
ORDER BY (pricing->>'unit_price')::NUMERIC ASC NULLS LASTSQL Expression Breakdown
(pricing->>'unit_price')::NUMERIC
↑ ↑ ↑ ↑
│ │ │ └─ Cast to NUMERIC type for numeric sort
│ │ └────────────── JSON key to extract
│ └───────────────── ->> operator (extract as text)
└───────────────────────── JSONB column name
NULLS LAST:
ORDER BY (pricing->>'unit_price')::NUMERIC ASC NULLS LAST
↑
Products without price go to endPerformance Pro Tip: Casting JSONB on the fly works for small tables (under 100k rows). For larger tables, use PostgreSQL Generated Columns:
-- Create a pre-calculated, indexable column
ALTER TABLE product ADD COLUMN price_sort NUMERIC
GENERATED ALWAYS AS ((pricing->>'unit_price')::NUMERIC) STORED;
CREATE INDEX idx_price_sort ON product(price_sort);
-- Now sorting is instant
ORDER BY price_sort ASC NULLS LASTThis pre-calculates and indexes the value. Don’t cast on the fly for production tables with millions of rows.
Building the ORDER BY Clause
def build_order_clause(
sortable_field: SortableField,
direction: str = None
) -> str:
"""Generate ORDER BY clause for a sortable field."""
# Use preferred direction if not specified
if direction is None:
direction = sortable_field.preferred_direction.value
return f"{sortable_field.db_expression} {direction} NULLS LAST"
# Examples:
build_order_clause(MOSFET_SORTABLE["r_ds_on"])
# → "r_ds_on ASC NULLS LAST"
build_order_clause(MOSFET_SORTABLE["pricing"])
# → "(pricing->>'unit_price')::NUMERIC ASC NULLS LAST"
build_order_clause(MOSFET_SORTABLE["i_d_max"])
# → "i_d_max DESC NULLS LAST"LLM-Inferred Sorting
Extracting Sort from Natural Language
Users don’t say “sort by r_ds_on ascending.” They say “show me the best” or “cheapest options.”
USER KEYWORDS → SORTABLE FIELD + DIRECTION
"cheapest" → pricing ASC
"lowest price" → pricing ASC
"most affordable" → pricing ASC
"best" → default field for component type
(r_ds_on ASC for MOSFET)
"lowest rds" → r_ds_on ASC
"best rds" → r_ds_on ASC
"highest current" → i_d_max DESC
"most current" → i_d_max DESC
"highest voltage" → v_ds_max DESCInjecting Sortable Fields into the Filter Prompt
The extraction prompt (from Issue 5) already gets field definitions. Add sortable fields:
def get_sortable_field_prompt(component_type: str) -> str:
"""Generate sortable field list for LLM prompt."""
fields = get_sortable_fields(component_type)
lines = ["## Sortable Fields"]
lines.append("If user mentions sorting keywords, extract order_by:")
lines.append("")
for name, f in fields.items():
dir_hint = "lower is better" if f.preferred_direction == SortDirection.ASC else "higher is better"
aliases = ", ".join(f'"{a}"' for a in f.aliases[:3])
lines.append(f"- {name}: {f.description}")
lines.append(f" Direction: {f.preferred_direction.value} ({dir_hint})")
lines.append(f" Keywords: {aliases}")
lines.append("")
default = get_default_sort_field(component_type)
lines.append(f"Default 'best' for {component_type}: {default}")
return "\n".join(lines)Example output injected into prompt:
## Sortable Fields
If user mentions sorting keywords, extract order_by:
- r_ds_on: On-resistance in milliohms
Direction: ASC (lower is better)
Keywords: "lowest rds", "best rds", "best"
- i_d_max: Maximum drain current in amps
Direction: DESC (higher is better)
Keywords: "highest current", "most current"
- pricing: Unit price in USD
Direction: ASC (lower is better)
Keywords: "cheapest", "lowest price"
Default 'best' for mosfet: r_ds_onExtended Filter Extraction
Add sorting to the filter extraction response:
# In filter prompt, request sorting:
"""
Return JSON:
{
"filters": { ... },
"order_by": "r_ds_on", // Field to sort by (optional)
"order_direction": "ASC", // ASC or DESC (optional)
"reasoning": "..."
}
"""
# After extraction, validate:
def validate_sort(extracted: dict, component_type: str) -> tuple:
"""Validate extracted sort field."""
order_by = extracted.get("order_by")
direction = extracted.get("order_direction")
if not order_by:
# Use default for component type
return get_default_sort(component_type)
# Validate field exists
sortable = get_sortable_field(component_type, order_by)
if not sortable:
# Unknown field, use default
return get_default_sort(component_type)
# Use preferred direction if not specified
if not direction:
direction = sortable.preferred_direction.value
return (sortable.db_expression, direction)Default Sort Logic
Component-Specific Defaults
When the user doesn’t specify sorting, use intelligent defaults:
┌─────────────────────────────────────────────────────────┐
│ COMPONENT-SPECIFIC DEFAULT SORTS │
├─────────────────────────────────────────────────────────┤
│ │
│ MOSFET → r_ds_on ASC (best performance) │
│ Capacitor → esr ASC (best quality) │
│ Resistor → pricing ASC (cheapest) │
│ Inductor → dc_resistance ASC (best performance) │
│ Diode → pricing ASC (cheapest) │
│ Laptop → value_score DESC (best value) │
│ Phone → rating DESC (best rated) │
│ │
└─────────────────────────────────────────────────────────┘Implementation
COMPONENT_DEFAULT_SORT = {
"mosfet": "r_ds_on",
"capacitor": "esr",
"resistor": "pricing",
"inductor": "dc_resistance",
"diode": "pricing",
"laptop": "value_score",
"phone": "rating",
}
def get_default_sort(component_type: str) -> tuple:
"""Get default sort field and direction for component type."""
field_name = COMPONENT_DEFAULT_SORT.get(component_type, "pricing")
sortable = get_sortable_field(component_type, field_name)
if not sortable:
# Fallback to pricing
return ("(pricing->>'unit_price')::NUMERIC", "ASC")
return (
sortable.db_expression,
sortable.preferred_direction.value
)The Complete Flow
End-to-End Sorting
USER: "Show me the cheapest MOSFETs with low Rds"
↓
┌─────────────────────────────────────────────────────────┐
│ STEP 1: INTENT CLASSIFICATION │
│ Intent: NEW_SEARCH │
└─────────────────────────────────────────────────────────┘
↓
┌─────────────────────────────────────────────────────────┐
│ STEP 2: FILTER EXTRACTION (with sortable fields) │
│ { │
│ "filters": {"r_ds_on": {"<": 0.05}}, │
│ "order_by": "pricing", │
│ "order_direction": "ASC", │
│ "reasoning": "'cheapest' → pricing ASC" │
│ } │
└─────────────────────────────────────────────────────────┘
↓
┌─────────────────────────────────────────────────────────┐
│ STEP 3: VALIDATE SORT │
│ • "pricing" exists in MOSFET_SORTABLE? ✓ │
│ • Get db_expression: (pricing->>'unit_price')::NUMERIC │
│ • Direction: ASC │
└─────────────────────────────────────────────────────────┘
↓
┌─────────────────────────────────────────────────────────┐
│ STEP 4: BUILD QUERY │
│ SELECT * FROM mosfet │
│ WHERE r_ds_on < 0.05 │
│ ORDER BY (pricing->>'unit_price')::NUMERIC ASC NULLS LAST│
│ LIMIT 10 │
└─────────────────────────────────────────────────────────┘
↓
RESPONSE: "Here are the cheapest MOSFETs with Rds(on) under 0.05Ω,
sorted by price:"The Proof: Before/After
| Before | After |
|---|---|
| 'Best' interpretation: 0% (defaulted to created_at) | 'Best' interpretation: 94% (correct default) |
| JSONB sorting errors: 100% (text sort) | JSONB sorting errors: 0% (proper expressions) |
| Direction errors: 35% (ASC when should be DESC) | Direction errors: 2% (preferred direction works) |
| User satisfaction: Low ('results aren't in order') | User satisfaction: High ('results make sense') |
What changed: We defined what “best” means for each field, added JSONB SQL expressions, and let the LLM extract sorting from keywords.
The Checklist: Sort Layer Readiness
| Item | Score | |
|---|---|---|
| Do you define sortable fields separately from filterable? | — | |
| Does each field have a preferred direction? | — | |
| Do you map keywords ('cheapest') to sort fields? | — | |
| Do you handle JSONB with SQL expressions? | — | |
| Do you use NULLS LAST in ORDER BY? | — | |
| Do you have component-specific default sorts? | — | |
| Do you inject sortable fields into extraction prompt? | — | |
| Do you validate that sort field exists? | — |
0 of 8
Score:
- 0-3: Sort layer not ready
- 4-6: Prototype-ready
- 7-8: Production-ready
What’s Next
Issue 8: The Product Deep-Dive Layer
Now that you can search, filter, and sort structured data, what about unstructured knowledge?
The Problem We’ll Solve:
“User asks ‘What do people say about the battery life?’ The answer is in 1,000 user reviews, not in your structured database columns.”
What You’ll Learn:
- When RAG is actually needed (decision framework)
- Hybrid architecture (database + RAG)
- Chunking strategies for reviews and documentation
Key Takeaways
- 1 The Problem: 'Best' is ambiguous, JSONB sorts as text, no preferred direction per field.
- 2 The Solution: Sortable field registry with preferred direction, JSONB SQL expressions with type casting, LLM keyword extraction ('cheapest' maps to pricing ASC), and component-specific default sorts.
- 3 Key Takeaway: Sorting needs semantic definitions -- what 'best' means for each field. ORDER BY alone isn't enough.
Until next issue,
Sentient Zero Labs