Search Semantics & Case-Insensitive Text Design simple search behavior while understanding its database trade-offs
Phase 15 - Filtering, Search, Sorting & Pagination Volume 04 - Professional Backend
search=payroll
v
v
Phase 15 turns an unbounded collection endpoint into a validated, queryable, stable, paginated API contract.
Search is not the same as exact filtering Filtering asks for exact structured attributes such as status=IN_PROGRESS. Search usually asks whether text resembles a user-entered phrase. TaskFlow begins with a deliberately simple case-insensitive substring search across title and description.
...(query.search ? { OR: [ { title: { contains: query.search, mode: "insensitive" } }, { description: { contains: query.search, mode: "insensitive" } }, ], } : {})
?status=DONE Exact structured filter
?priority=HIGH Exact structured filter
?search=payroll Text contains search across chosen fields
?status=DONE&search;=payroll DONE tasks AND text search
Search semantics must be documented Does search mean starts-with, contains, token matching, fuzzy matching, or full-text relevance? A production API must choose. Phase 15 chooses contains so behavior stays obvious while the architecture is learned.
PostgreSQL substring search such as ILIKE/contains is not automatically accelerated by an ordinary B-tree index. If TaskFlow search becomes large or latency-sensitive, measure it and consider a search-specific strategy such as trigram or full-text indexing. Do not pretend a normal title index solves arbitrary substring search.
Search is product behavior, not a database checkbox A payroll system might search employee code, legal name, and payroll batch number. Procurement might search PO number, supplier, and description. The fields and semantics should reflect user tasks, then database design follows.
Search Semantics and Case Insensitive Text matters because it changes a real boundary in TaskFlow. The goal is to understand the inputs, owner, observable output, and failure modes before adding more abstraction.
Restate the goal of “Search Semantics and Case Insensitive Text” as one observable behavior. Write the success signal before touching code.
Trace the boundary map: query parameters → validated filter/sort → stable query → page/cursor → response metadata. Mark the one boundary this lesson changes.
Inspect the current repository state and record the exact files, route, table, or runtime involved. Avoid creating an abstraction before you can name the existing owner.
Implement the smallest vertical slice. Add one coherent change, run the narrowest verification command, and read the output before continuing.
Connect the slice to TaskFlow’s phase artifact: Task list API with filter/search/sort/page contract plus metadata and indexes justified by measured query patterns.. Verify behavior from the outside as a user or API client would.
Create one controlled failure that is safe to reproduce. Predict the symptom first, capture evidence, locate the earliest broken boundary, then fix only that cause.
Close the reference and rebuild or explain the core change from memory. Record what you would monitor or test before trusting the same change in production.
orderBy: [{ createdAt: "desc" }, { id: "desc" }],
take: limit + 1,
where: cursor ? {
OR: [
{ createdAt: { lt: cursor.createdAt } },
{ createdAt: cursor.createdAt, id: { lt: cursor.id } },
],
} : undefinedDo not copy this as a finished answer. Predict what each boundary does, type the smallest useful slice, run it, then compare the result with your prediction.
Return to the nearest observable boundary: input, network, process log, database, or rendered UI. Compare actual evidence with the predicted path.
Change one variable at a time and reproduce the original failure after the fix; otherwise you have correlation, not diagnosis.
Without copying the final source, create a second small scenario that applies “Search Semantics and Case Insensitive Text” to a different TaskFlow resource or input. Write the acceptance criteria first, implement it, and provide the evidence that each criterion is satisfied.
Start with offset pagination, then explain where cursor pagination changes consistency/performance.
Use the smallest change that demonstrates this lesson. Stop after it works and capture evidence.Counting/filtering with different predicates so metadata lies
Predict the symptom before causing the failure. Then locate the earliest boundary where reality diverges from expectation.Why does this responsibility live here, what input does it trust, what output proves success, and what would go wrong if another layer owned it?
If your explanation is vague, repeat the boundary trace before moving on.Close the reference. Reproduce the core idea from memory or explain the exact request/data flow aloud. Then check your diff or notes for accidental shortcuts that bypass validation, authorization, error handling, or verification earned in earlier phases.