Filtering & Sorting

Every Filter Is a Query-Plan Promise

A filter parameter isn’t syntax — it’s a commitment that some index can serve it:

 GET /trips?status=completed&city=london&created_after=2026-01-01

 the database must find these rows WITHOUT scanning everything,
 which means indexes aligned to actual filter combinations:
   INDEX (city, status, created_at)   ← designed from real query traffic

 unwhitelisted/arbitrary filters = full scans = one user's query
 degrades everyone sharing the database

Whitelist, Don’t Parse

 SAFE design:
   allowed_filters = { status, city, created_after, driver_id }
   unknown filter → 400 with allowed list in error body

 UNSAFE design:
   pass request params straight into query builders
   → arbitrary column filtering, injection surface, plan chaos

 whitelist is also PRODUCT design: exposing only filters
 you've built indexes for keeps API honest about cost

Composite Index Alignment

Multi-filter queries need column order discipline — equality columns first, range last:

 query: WHERE city=? AND status=? AND created_at > ?

 ideal index: (city, status, created_at)
              equality, equality, range ✓ fully served by index scan

 wrong order: (created_at, city, status)
              range first → remaining filters applied row-by-row ✗

 rule: EQ EQ ... RANGE last. every supported combination
 is an index decision made consciously.

Sorting Is Index-Bound Too

 ORDER BY fare DESC only avoids a sort step when an index matches:
   (filters..., fare) — or a separate (fare) index + merge

 dynamic client-chosen sorting ("sort by ANY column") forces either
 per-column indexes (expensive write amplification) or filesort at
 query time (expensive reads)

 production pattern: expose 2–3 curated sort options,
 each backed by a real index; reject the rest

Cursor Interactions

Filters and cursors compose but constrain:

 cursor embeds its position in ONE sort order;
 changing filter values mid-walk invalidates the bookmark
 → new filter set = new pagination walk (document it)

 filtered+paginated endpoints therefore want:
 stable filter semantics within a session + matching composite
 index including the cursor's sort key

Counting and Facets

 COUNT(*) with filters = another scan per call

 alternatives:
 - cached/approximate counts (fine for UI "about 2,300 results")
 - count only first page depth ("more than 100")
 - separate faceted-search backend for heavy discovery UX

Interview Framing

“Support filtering by anything” requests are traps testing pushback ability. Scored response: accept curated filters mapped to named indexes, refuse arbitrary ones with the load rationale, and note sort-option budget separately. Saying yes to everything is the junior answer; designing the query surface like an index budget is the senior one.