A search box is a second door into the same scanner, and Postgres made us write one expression twice

Munchable answers one question: does this food fit the gut condition you live with. Until recently there was exactly one way to ask it, which was to hold a phone camera up to a barcode. That works in a shop aisle and nowhere else. Somebody planning a weekly shop at the kitchen table has no pack in their hand, and the app had nothing to say to them.

So the app grew a second door: type the name instead. The whole thing is about a hundred lines of SQL written in TypeScript, and nearly every decision in it is about what search is not allowed to do.

You can try it on the web build of the app at app.munchable.app. Sign in, open Search, and type a brand you have in the cupboard. The product pages the same catalogue backs are public if you would rather look before signing in: munchable.app/answers is a few hundred static pages, each one answering a single ingredient question by running the same checker the app runs.

Search returns identities, not answers

The tempting version of this feature runs the rules engine over every hit and puts a verdict chip next to each row. We do not do that, and refusing it is the most load bearing decision in the file.

The endpoint returns three fields per hit: barcode, product name, brand. Tapping a row sends the barcode through the same product lookup the camera uses. That means the scan path's cache, its trust rules for community edited rows and its per account quota all apply unchanged, and the search route never has to know that any of them exist. It also means there is exactly one screen in the app that is allowed to say "good fit", "caution" or "avoid", so a search result can never disagree with a scan of the same pack.

The second reason is privacy. A search that returned verdicts would have to read your conditions, which turns a product query into a health record. As written, the route is condition blind in the same way the barcode lookup is. The query string is matched against product names and brands, and nothing else; it is not stored, not logged and not keyed to the account.

A hit we cannot answer is worse than no hit

Not every row in the catalogue is a product the app can check. Some are stubs with a name and no ingredient list. Some are withheld. If search matched those, a hit would look like a promise and then land the user on a result screen that says it cannot assess anything, which is a worse experience than an empty result list.

So the query carries a predicate that keeps it to rows the scan path would actually serve:

status <> 'withheld' and cardinality(ingredients_tags) > 0

Those two conditions are roughly a tenth of the table. I wrote about why the index deliberately covers only that tenth when the index went in; this post is the query that sits on top of it.

Every word on its own, never the phrase

"alpro oat" is a reasonable thing to type and a terrible thing to match as a phrase. The row is named "Oat drink" and the brand column says "Alpro", so no single column contains the string the user typed.

The query splits on whitespace and tests each word separately against name and brand joined together:

export function queryTerms(query: string): string[] {
  return [...new Set(query.split(' ').filter(Boolean))];
}

The Set is not tidiness. A duplicated word would add a second identical predicate, which costs another index probe for an answer the planner already has.

Each word then becomes an ILIKE pattern, with the three characters ILIKE treats as special escaped:

export function likePattern(term: string): string {
  return `%${term.replace(/[\\%_]/g, (c) => `\\${c}`)}%`;
}

Without that, a query for "100%" looks for "100" followed by anything at all, and a query for "7_up" matches "7up", "7-up" and "7xup". Product names are full of punctuation that SQL has opinions about.

Ordering is three terms, and the third one is stability

similarity(name || ' ' || brands, query) desc
unique_scans_n desc nulls last
barcode

Trigram similarity against the whole query first, so a row whose name reads like what you typed beats one that merely contains the letters. Then how often the product has been scanned, which is the cheapest available proxy for "this is the household name and the other one is a regional variant". Then barcode, purely so that two rows that tie do not swap places between two identical requests.

Postgres makes you write the expression twice

This is the part I would have got wrong without a comment to stop me. The index is a partial GIN trigram index over an expression:

index('products_search_trgm_idx')
  .using('gin', sql`(coalesce(product_name, '') || ' ' || coalesce(brands, '')) gin_trgm_ops`)
  .where(sql`status <> 'withheld' and cardinality(ingredients_tags) > 0`)

The planner only uses a partial expression index when the query's expression and predicate match the index definition. Not "mean the same thing": match. Swap the coalesce order, filter on status = 'imported' instead of <> 'withheld', add a redundant and true, and you still get correct results, from a sequential scan over the whole table.

So both the haystack and the predicate are defined once in the query file as sql fragments and reused, and the schema file carries a comment saying the two definitions are load bearing rather than duplicated by accident. Two copies that must agree, in two files that cannot import each other, is the kind of thing that deserves a paragraph of prose rather than a tidy refactor.

Six numbers, none of them a setting

Three characters minimum, because a shorter trigram match is mostly noise. Eighty characters maximum, because anything longer is not a search. Twenty results, which is one screen on a phone; the app has no paging, since a longer list is a scroll rather than an answer. Sixty requests a minute per account and three hundred per IP address, counted in parallel so a shared household address does not lock out one person because another is typing. And the limiter fails open: if the rate limit store is unreachable, the search runs. A read that reveals no health data and returns twenty rows is not worth refusing because Redis had a bad second.

What it cost

One route, one query file, one partial index, one test file. No new table, no search service, no embedding model, no synonym dictionary. The catalogue already had names in it; the only real work was deciding that search must hand off to the scanner rather than pretend to be one.