A search box with six states, and a Postgres index the query has to repeat verbatim or the planner ignores it
Munchable is a barcode scanner. Point the camera at a pack, get an answer about whether that product suits you. That works brilliantly in a shop and not at all on a sofa, which is where people actually plan their shoppin
Munchable is a barcode scanner. Point the camera at a pack, get an answer about whether that product suits you. That works brilliantly in a shop and not at all on a sofa, which is where people actually plan their shopping, so the app grew a second way in: search the catalogue by name.
It is 102 lines of query code, a 65 line route, and one index. Every one of those three has something in it I would not have guessed up front.
The contract: one screen, no paging
/** Fewer characters than this and a trigram match is mostly noise. */
export const SEARCH_MIN_CHARS = 3;
/** Long enough for any product name; anything past it is not a search. */
export const SEARCH_MAX_CHARS = 80;
/** One screen of results; the app has no paging and a longer list is a scroll, not an answer. */
export const SEARCH_LIMIT = 20;
Twenty results, take it or leave it. If your query produces more than twenty interesting rows, the right fix is a better query, not a second page. Shipping paging would have meant an offset, a stable sort across requests, and a scroll position to restore, which is a lot of machinery for the case where somebody typed "chocolate".
The three character floor is enforced on both sides: the client shows a hint and does not fetch, and the route answers 400 with the same number in the message, because a client is a suggestion and not a guarantee.
Words, not phrases
The naive version of this is name ILIKE '%alpro oat%', and it fails on the first real query. The product is called "Oat drink" and the brand is "Alpro", so the phrase exists nowhere in either column.
/**
* The words a query has to satisfy, each on its own. "alpro oat" matches a row
* named "Oat drink" by "Alpro" because the two words are tested separately
* against name and brand joined, not as one phrase. Duplicates are dropped so
* a repeated word does not cost a second index probe for the same answer.
*/
export function queryTerms(query: string): string[] {
return [...new Set(query.split(' ').filter(Boolean))];
}
Each word becomes its own ILIKE against name and brand concatenated, and they are ANDed. The result is that word order does not matter, which is how people type a product: brand first, brand last, or brand only.
And every word is escaped, which is a thing I nearly forgot because it is not an injection risk:
/**
* A word as an ILIKE substring pattern. The three characters ILIKE treats as
* special are escaped with the default backslash, so a "100%" in a query looks
* for the text "100%" rather than for "100" followed by anything.
*/
export function likePattern(term: string): string {
return `%${term.replace(/[\\%_]/g, (c) => `\\${c}`)}%`;
}
Parameter binding protects you from SQL injection. It does not protect you from a user typing a product called "100% Peanut", where the % is a wildcard inside the parameter and quietly turns a specific search into a broad one. Food names are full of %.
The index, and the rule I did not know
Matching is ILIKE, ranking is trigram similarity, and both are served by one GIN index over an expression:
index('products_search_trgm_idx')
.using(
'gin',
sql`(coalesce(${t.productName}, '') || ' ' || coalesce(${t.brands}, '')) gin_trgm_ops`,
)
.where(sql`/* exactly the rows a lookup would serve */`),
Two things about that are load bearing.
It is an index on an expression, not on a column, because the thing being searched is name and brand glued together with a space. And it is partial, covering only the rows the scan path would actually serve, which keeps it to a fraction of the table.
Here is the part that cost me an afternoon: Postgres will only use a partial expression index when the query's expression and predicate match the index definition. Not "are logically equivalent". Match. A query that computes the same concatenation a slightly different way, or filters on the same rows via a differently written WHERE, gets a sequential scan and no error message. You find out from EXPLAIN, or from your response times.
So the expression and the predicate are each written exactly once, in a sql fragment that the query imports:
/**
* The searchable text of a row and the servable predicate, written once so the
* query and the partial index cannot drift apart. Postgres only uses a partial
* expression index when the query's expression and predicate match the index
* definition, so this is load bearing, not tidiness.
*/
const haystack = sql`(coalesce(${catalogProducts.productName}, '') || ' ' || coalesce(${catalogProducts.brands}, ''))`;
A comment in the schema points at the search module and a comment in the search module points back. That is the sort of coupling you cannot express in types, so it has to be expressed in words, twice, facing each other.
Ranking is similarity, then popularity, then a tiebreak
.orderBy(
desc(sql`similarity(${haystack}, ${query})`),
sql`${catalogProducts.uniqueScansN} desc nulls last`,
catalogProducts.barcode,
)
Trigram similarity against the whole query is a good first pass and a poor last word: it cannot tell that one of two equally similar rows is a household name and the other is a regional variant that happens to share some letters. How often a product has been scanned can tell you that, so it breaks the tie.
The third key is the barcode, and it exists purely so the order is total. Two rows with equal similarity and equal popularity would otherwise come back in whatever order the plan happened to produce, which means a list that reshuffles itself between identical requests. Any sort you show to a human needs a deterministic final key.
A hit that cannot be opened is worse than no hit
The index and the query both exclude rows the lookup path would refuse to serve. The reason is a user experience one, from the module header:
// It answers only from rows the scan path would serve, so every hit is a
// product the app can actually check: a stub or a withheld row would send the
// user to the result screen for a "not found".
A search result is a promise. Tapping one and landing on "we do not have this product" is worse than the product simply not appearing, because the first teaches you the app is broken and the second teaches you to scan the pack.
The route returns almost nothing
// Returns only what a result row needs to be tapped: barcode, name, brand. The
// product itself is then fetched through POST /api/product-lookup, so the scan
// path's cache, trust status and quota all apply unchanged, and this route
// never has to know about any of them.
This was the best decision in the feature. The temptation is to have search return full products, since it has already touched the rows. Resisting it means search is a finder and the lookup stays the single place where a product is served. Everything the scan path does (caching, trust handling, the free tier's monthly allowance) applies to a product opened from search without one line of it being reimplemented, and the day any of that changes, search inherits the change for free.
On the client, tapping a result runs the same three steps a barcode scan does, in the same order:
* A tapped hit is a scan. The quota gate, the count and the history entry are
* the ones the hub applies to a barcode, in the same order, so a product
* opened from here costs and records exactly what a scan of it would.
Two ways in, one accounting.
Six states, because "empty" and "broken" are different sentences
type Phase = 'idle' | 'short' | 'loading' | 'results' | 'empty' | 'error';
The two that matter are the last two. "Nothing matched that" and "the search is not working" look identical if you collapse them into one empty list, and they ask the user to do opposite things: change the words, or try later. So the route refuses in a way the client can tell apart: a failure answers 502 with an error code and an empty results array, so a client that ignores the code still renders something sane, and a client that reads it says the right thing.
Rate limits here fail open:
try {
decision = await enforceParallel([
{ limiter: lim.searchDev, key: user.id },
{ limiter: lim.searchIp, key: getClientIdentifier(request) },
]);
} catch {
decision = { success: true };
}
Two limiters, per account and per address, checked in parallel, and if the limiter service itself is unreachable the search proceeds. A read that reveals nothing is not worth failing closed over.
The client half is a sequence number
// Which request is the latest. A slow answer to an earlier keystroke must
// not land on top of the answer to the current one.
const requestRef = useRef(0);
300 ms of debounce, then a request tagged with an incrementing id, and every handler begins by checking that its id is still the current one. No AbortController, because cancelling the network request is not the goal. The goal is that a stale response cannot win, and a response that arrives after being overtaken is simply dropped.
That one if (id !== requestRef.current) return; is the difference between a search box that feels live and one that flickers through old answers while you type.
What the server learns
Nothing, deliberately:
// Condition-blind, like the lookup: the query is matched against product
// names and never stored or keyed to the account. Limits fail open, because
// a search is a read that reveals no health data.
The query is not logged, not stored and not attached to the account. The server sees a few words and answers with up to twenty rows. Everything about why those words were typed, which is the sensitive part in an app about guts, stays on the phone.
If you want to see the public, crawlable half of "search" in this product, it is a different animal entirely: 461 static question pages indexed at munchable.app/answers, the recipe library, and the condition guides. Those are for strangers arriving from a search engine. The twenty row box described above is for somebody who already has the app open and a shopping list in the other hand.
Originally published by Dev.to WebDev. Aggregated on AIWithGhost for educational purposes — full credit and traffic to the original publisher.