Writing Search Endpoints With LLMs in November 2026: Use Your Database's Full-Text Search, Escape Every User Query, and Test Ranking With Real Searches
Almost every app eventually grows a search box. Ask an LLM to "add search to the products page" and you will usually get something that works on the first try: a query with WHERE name ILIKE '%' || :q || '%', wired to a text field. It returns results in the demo. Then the table grows, the query starts scanning every row, a user types 100% and gets everything, someone else searches for "running shoes" and finds nothing because the product is called "Shoes for running", and a customer sees a draft item that was never meant to be public.
None of those problems are hard to avoid, but the model will not avoid them unless you ask. This post walks through how to get a search endpoint out of an LLM that is fast, safe with hostile input, respects permissions, and returns results people actually expect. The examples use PostgreSQL because it ships solid full-text search out of the box, but the same checklist applies to MySQL, SQLite FTS5, or a dedicated search engine.
A card catalogue is an index built ahead of time so nobody has to read every book. Your search index does the same job. Image: Card catalogue drawers by Carolina Prysyazhnyuk, CC BY-SA 2.0, via Wikimedia Commons.
Step 1: Tell the model what "search" means for your app
"Add search" is ambiguous. Before you ask for code, write down answers to these questions and paste them into the prompt:
- Which fields are searched? Title only, or title plus description plus tags? Should a title match rank above a body match?
- What kind of matching? Whole words with stemming ("run" finds "running"), prefix matching for search-as-you-type, or typo tolerance?
- Who can see what? Public items only, the current user's own records, or everything in their organization?
- How many rows? A few thousand rows behave very differently from tens of millions.
- What happens on an empty query? Return nothing, return the newest items, or return a validation error?
Also paste the real table definition and the indexes you already have. If the model has to guess column names and types, it will guess, and you will spend your review time fixing names instead of logic.
Step 2: Ask for real full-text search instead of ILIKE
A leading-wildcard ILIKE '%term%' cannot use a normal B-tree index, so it reads every row. It also matches substrings blindly ("cat" matches "education") and knows nothing about word forms. For a real search box, ask for your database's full-text features explicitly. In PostgreSQL 12 and later, a stored generated column plus a GIN index is a clean pattern:
ALTER TABLE products
ADD COLUMN search_doc tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(name, '')), 'A') ||
setweight(to_tsvector('english', coalesce(description, '')), 'B')
) STORED;
CREATE INDEX products_search_doc_idx ON products USING GIN (search_doc);
The setweight calls let a match in the name rank higher than a match in the description. The generated column keeps the search document in sync on every insert and update, so you do not need a trigger or a background job to remember to refresh it.
On a large, busy table, adding a stored generated column rewrites the table, and a plain CREATE INDEX blocks writes while it builds. Tell the model your table size and ask it to plan the migration accordingly (for example, CREATE INDEX CONCURRENTLY run outside a transaction). Review that migration as carefully as any other schema change.
Parse the user's text with a forgiving function
LLMs often reach for to_tsquery(:q). That function expects query syntax with operators like & and |, and it raises an error on ordinary input such as red shoes or a stray parenthesis. For text typed by users, ask for websearch_to_tsquery (PostgreSQL 11+) or plainto_tsquery. Both accept free text without throwing syntax errors, and websearch_to_tsquery also understands quoted phrases and a leading minus for exclusion, which is what most people already expect from a search box.
SELECT id, name, ts_rank(search_doc, query) AS rank
FROM products,
websearch_to_tsquery('english', :q) AS query
WHERE search_doc @@ query
AND status = 'published'
ORDER BY rank DESC, id
LIMIT :limit;
Note the id tiebreaker in the ORDER BY. Many rows can share the same rank, and without a unique tiebreaker the order between them is not guaranteed, so results can shuffle between requests.
Step 3: Treat the query string as hostile input
The search box is one of the few places where anyone on the internet can send you free text that goes straight into a database query. Be explicit about the rules in your prompt and check them in review:
- Parameters, never string building. The query text must be a bound parameter. If the generated code uses f-strings, template literals, or string concatenation to put
qinto SQL, reject it, even if it "escapes quotes" by hand. - Escape LIKE wildcards if you keep any LIKE. Parameters stop SQL injection, but they do not stop
%and_from acting as wildcards inside a LIKE pattern. A user searching for50%orfile_namegets surprising matches, and a pattern made of many wildcards can be slow. Escape the backslash, percent, and underscore before building the pattern, and declare the escape character. - Cap the length. Reject or truncate queries longer than a sensible limit, such as 200 characters. Nobody needs a 50 KB search string, and long inputs make parsing and matching more expensive.
- Cap the page size. Accept a
limitparameter if you need one, but clamp it to a maximum on the server. - Handle empty and whitespace-only input on purpose. Decide what it returns and make the code do exactly that, instead of letting an empty tsquery or an empty LIKE pattern decide for you.
Here is a small Python helper for the LIKE case, which is worth asking for by name:
def escape_like(term: str) -> str:
return (term.replace("\\", "\\\\")
.replace("%", "\\%")
.replace("_", "\\_"))
pattern = "%" + escape_like(q.strip()[:200]) + "%"
cur.execute(
"SELECT id, name FROM tags WHERE name ILIKE %s ESCAPE '\\' LIMIT 20",
(pattern,),
)
Step 4: Put the permission filter inside the same query
A classic bug in generated search code is filtering for permissions after the database returns results, or forgetting the filter entirely because the "list" endpoint had it and the new "search" endpoint is a separate function. Search endpoints leak data in quiet ways: even a result count or a snippet can reveal that a private record exists.
Tell the model the exact visibility rule and require it inside the WHERE clause of the search query itself, alongside the text match. For a multi-tenant app that usually means something like AND org_id = :current_org_id taken from the authenticated session, never from a request parameter. If you have soft-deleted rows, add that condition too. Then write a test that logs in as a user from a different organization, searches for a word that only appears in the first organization's data, and asserts zero results.
Search should only show what the person looking is allowed to see. Image: Magnifying glass2 by Heptagon, Public domain, via Wikimedia Commons.
Step 5: Decide how to handle typos and partial words
Full-text search matches whole words after stemming. It will not match "headphne" to "headphone", and it will not match "head" while someone is still typing "headphones" unless you ask for prefix matching. Choose what you need and say so:
- Search-as-you-type: Postgres tsquery supports prefix matching with the
:*suffix, but you have to build that query carefully from sanitized words rather than passing raw text intoto_tsquery. - Typo tolerance: the
pg_trgmextension provides trigram similarity and can use a GIN or GiST index withgin_trgm_opsorgist_trgm_ops. It works well for short fields like names and tags, less well for long bodies of text. - Language: the
'english'configuration stems English words. If your content is multilingual, tell the model, because a single English configuration will stem other languages poorly. The'simple'configuration skips stemming entirely.
If your requirements include fuzzy matching across millions of long documents, faceting, and synonyms, that is the point where a dedicated search engine starts to earn its operational cost. Ask the model to lay out that tradeoff instead of letting it silently bolt on a new service.
Step 6: Test ranking with searches real users make
Unit tests that check "searching 'shoe' returns the shoe" prove the code runs. They do not prove search is good. Build a small relevance test set instead:
- Collect 20 to 50 real queries. Pull them from your search logs if you have them, or from support tickets and what you would type yourself.
- For each query, write down the one or two results that should appear near the top.
- Seed a test database with representative data, including near-misses that should rank lower.
- Write a test that runs each query and asserts the expected items appear in the first few results.
Include awkward inputs in the same suite: an empty string, only spaces, a single character, punctuation only, a query with quotes and a minus sign, emoji, accented characters, % and _, and a string at your length limit. Every one of these should return a normal response, not a 500 error.
This test set also becomes your safety net later. When you or the model change weights, add fields, or switch configurations, you can see immediately which queries got better and which got worse.
Step 7: Check the query plan before you ship
Run EXPLAIN ANALYZE on the final search query with realistic data and a few common search terms. You want to see the GIN index being used (a bitmap index scan on your search index), not a sequential scan over the whole table. Watch for two common regressions in generated code: wrapping the indexed column in a function in the WHERE clause, which can stop the index from being used, and computing to_tsvector(...) on the fly in the query instead of using the stored column.
Also look at how the query behaves with a very common word. A term that matches half the table can make ranking expensive, because every matching row has to be scored before the top results can be picked. If that is slow, cap results, add a narrower filter, or rethink which fields you index.
A prompt you can reuse
Add a search endpoint for the products table below (PostgreSQL 16).
Schema and existing indexes: [paste CREATE TABLE and indexes]
Table size: about 2 million rows, steady writes.
Requirements:
- Search name (weighted higher) and description, English stemming.
- Use a stored generated tsvector column with a GIN index; plan the
migration so it does not block writes for long.
- Parse user input with websearch_to_tsquery. Never use to_tsquery
on raw input. Bind all values as parameters.
- Trim the query, cap it at 200 characters, return an empty list for
empty input. Clamp limit to a maximum of 50.
- Only return rows where status = 'published' and org_id equals the
org from the authenticated session. Filter inside the SQL query.
- Order by rank, then id.
- Write tests: relevance cases I will provide, hostile inputs
(empty, %, _, quotes, emoji, 200+ chars), and a cross-org test
that must return zero rows.
- Show EXPLAIN output you expect and explain any tradeoffs.
Review checklist
- Uses full-text search (or a deliberate alternative), not leading-wildcard LIKE on a big table.
- User text goes through
websearch_to_tsqueryorplainto_tsquery, as a bound parameter. - Any remaining LIKE pattern escapes
%,_, and the escape character. - Query length and page size are capped on the server.
- Permission and soft-delete filters live inside the search query.
- Ordering has a unique tiebreaker.
- The migration is safe for your table size.
- A relevance test set and a hostile-input test set both pass.
EXPLAIN ANALYZEshows the index in use.
LLMs are quick at producing search code, and with the right constraints they produce good search code. The work that stays with you is the part only you know: which results should come first, who is allowed to see them, and what your users actually type.
Comments
Post a Comment