A search hit we cannot answer is worse than no hit, so our index covers a tenth of the table

Munchable reads a packaged food's label and tells you whether it fits your gut condition. The normal way in is the camera: point it at a barcode, get a verdict. You can see the conditions it reasons about at munchable.app/conditions, and a public slice of the underlying ingredient data at munchable.app/answers.

The camera assumes you are holding the pack. Plenty of the time you are not. You are writing a shopping list on the sofa, or deciding on Sunday what the week looks like. So the app grew a name search, which you can try by signing in at app.munchable.app and opening Search from the hub.

Search sounds like the easy feature in a product like this. It turned out to have one hard constraint, and that constraint wrote both the query and the index.

The constraint

A tap on a search result has to lead somewhere.

Our catalogue has rows in states that the scan path deliberately refuses to serve. Some are withheld after being reported. Some are stubs: a barcode we have seen, with no ingredients attached yet, waiting for somebody to photograph the label. Those rows are useful internally and they are completely useless as search results, because tapping one takes the reader to a result screen that says "we do not have this product yet".

That is a worse outcome than the product simply not appearing. A user who searches "oat drink" and sees four results expects four answers. Giving them three answers and one dead end teaches them not to trust the list.

So the rule is: search only answers from rows the scan path would serve. Written down, it is two conditions.

const servable = sql`${catalogProducts.status} <> 'withheld' and cardinality(${catalogProducts.ingredientsTags}) > 0`;

The predicate has to exist exactly once

Here is the part that is easy to get wrong, and that Postgres will punish you for quietly rather than loudly.

The searchable text is not one column. A user types "alpro oat", and "Alpro" is the brand while "oat" is in the name, so the thing being matched is the two columns concatenated:

const haystack = sql`(coalesce(${catalogProducts.productName}, '') || ' ' || coalesce(${catalogProducts.brands}, ''))`;

That means the index has to be an expression index over the same concatenation, and because of the constraint above it should also be partial, covering only the servable rows. On a free tier database with a disk quota, that is not a nicety: it keeps the trigram index sized to roughly a tenth of the table instead of all of it.

index('products_search_trgm_idx')
  .using(
    'gin',
    sql`(coalesce(${t.productName}, '') || ' ' || coalesce(${t.brands}, '')) gin_trgm_ops`,
  )
  .where(sql`${t.status} <> 'withheld' and cardinality(${t.ingredientsTags}) > 0`),

The planner will only use a partial expression index when the query's expression and the query's WHERE both match the index definition. Not "means the same thing". Match. If the query says status != 'withheld' and the index says status <> 'withheld', you are fine, those parse identically. If the query checks array_length(ingredients_tags, 1) > 0 and the index checks cardinality(...) > 0, you have just built an index nobody will ever use, and your search will sequential scan a million rows while looking completely correct in tests with fifty rows in them.

So the two fragments live next to each other in the search module, with a comment saying why, and the schema file points back at it:

 * The searchable text of a row and the servable predicate, written once so the
 * query and the partial index in lib/db/schema.ts 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.

"Load bearing, not tidiness" is a phrase I have started using in comments for exactly this class of duplication. A future me who tidies one of those two strings into a nicer form has broken production performance without breaking a single test.

Words, not phrases

The matching itself is per word, deliberately.

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

Every word has to match the haystack on its own, as a substring, which is what makes "alpro oat" find a row named "Oat drink" by "Alpro". A phrase match would find nothing there, because no single stored string contains "alpro oat". The Set is not decoration: someone typing "oat oat" should not pay for a second index probe that returns the same rows.

One small detail that is easy to skip. The words become ILIKE patterns, and ILIKE has three characters with opinions:

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

Products have names like "100% pure orange juice". Without that escape, a search for "100%" asks for "100" followed by anything, which is a different and much larger question.

Ranking is then trigram similarity to the whole query, then how often the product actually gets scanned, then barcode:

.orderBy(
  desc(sql`similarity(${haystack}, ${query})`),
  sql`${catalogProducts.uniqueScansN} desc nulls last`,
  catalogProducts.barcode,
)

The middle line is the one that makes the list feel right. Letters alone cannot tell a household brand from a regional variant with a similar name, and popularity can. The barcode at the end is not a ranking signal at all, it is there so two equally good rows always come back in the same order, because a list that reshuffles between identical queries looks broken.

The client side of a live search

Three details, all about not lying to the reader while they type.

A minimum of three characters, matching the server's own floor. Below that a trigram match is mostly noise, and a list of noise is worse than a prompt to keep typing.

A 300 ms debounce, long enough to let a word finish and short enough to feel live.

And a request generation counter, which is the bug everyone writes once:

// 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);

Without it, typing "oat" fires requests for "oat" while "oa" is still in flight, and if the shorter query is slower to come back you render results for a query the user has already moved past. The screen has six named phases (idle, short, loading, results, empty, error) rather than a couple of booleans, because "empty" and "error" want different words on screen and a boolean pair will eventually let you render both.

The bit I am most pleased with

A tap on a search result is not a special path. It calls the same product lookup the camera calls.

That means the trust status, the Redis cache, the free-tier scan quota and the result screen all apply with no new code and no second set of rules to keep in sync. Search is a way of producing a barcode, and after that the app does not know or care where the barcode came from.

It also keeps the privacy story simple. The query is matched against product names and nothing else, it is not stored, not logged, and not keyed to the account. Like the barcode lookup, the search route is condition blind: it never learns why you asked.

If you want to see the shape of the data behind all of this without signing in, the answers index is generated from the same ingredient knowledge the scanner uses, and each condition page such as low FODMAP for IBS explains what the engine looks for.