Our product search index covers a tenth of the table, because a hit on the other nine tenths would say not found

Munchable is a barcode scanner for people with digestive conditions. You point the phone at a pack, the app tells you whether it fits the conditions you picked, and the verdict is worked out on the device. That design has one obvious hole in it: it needs a pack.

Somebody writing a shopping list at the kitchen table has no barcode to point a camera at. So the app also has a search box, and the search box turned out to be a different problem from the scan, for a reason that is nothing to do with text matching.

A hit that cannot be answered is worse than no hit

The scan path does not serve every row in the catalog. A row can be withheld, and a row can exist as a stub with no ingredient data attached to it yet, which happens whenever we know a barcode exists before we know what is in the product.

If search ignored that, the failure mode would be this: you type three letters, you see the product you are holding, you tap it, and the app tells you it has nothing for that barcode. You did the work of finding it and the app then disowned it. A search that returns nothing is a dead end you understand in one second. A search that returns a result which cannot be opened is a bug that looks like your fault.

So the rule is that search reads only rows a lookup would serve. In the query that is a predicate on status and on whether the row has any ingredient tags:

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

Which is why the index is partial, and why the query repeats itself

Name search is a substring match, so this is a pg_trgm GIN index. Two things about it are not the textbook version.

First, the index is over the name and the brand concatenated, not over two columns. People type "alpro oat" and the database holds a row named "Oat drink" with a brand of "Alpro". One haystack means one match covers both words whichever column each of them lives in.

Second, the index is partial on exactly the servable predicate above:

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`)

That started as a correctness decision and paid for itself as a disk decision. Our Postgres is on a plan with a 0.5 GB quota, and the servable rows are a fraction of the table, so indexing only them is the difference between an index we can afford and one we cannot.

The catch is that Postgres will only use a partial expression index when the query's expression and its predicate both match the index definition. Not "imply", match. So lib/search.ts defines the haystack expression and the servable predicate once, as two SQL fragments, and the query is built from the same two fragments the index was declared from. A well-meaning tidy-up that rewrote one of them into an equivalent form would not break a test. It would quietly turn every search into a sequential scan over the whole catalog.

The comment in the schema says so in as many words, because the next person to read it will be me, having forgotten:

The query in lib/search.ts repeats this predicate and expression verbatim; the planner only uses a partial expression index when both match.

Every word on its own, and the three characters ILIKE cares about

The query matches each word separately rather than the phrase:

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

Duplicates are dropped so that typing a word twice does not cost a second index probe for the same answer. Each word becomes an ILIKE substring pattern, with the three characters ILIKE treats as special escaped:

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

Food packaging is full of percent signs. "100% orange juice" is a product name, and without that escape a search for "100%" asks for "100" followed by anything at all.

Ranking is trigram similarity to the whole query first, then how often the barcode is looked up, then the barcode itself as a stable tiebreak so the same query twice is the same list twice.

Three numbers that are also product decisions

export const SEARCH_MIN_CHARS = 3;
export const SEARCH_MAX_CHARS = 80;
export const SEARCH_LIMIT = 20;

Under three characters a trigram match is mostly noise, so the app does not ask and the server refuses as well, with a 400 and a sentence rather than an empty list. Eighty characters is longer than any product name, and anything past it is not a search. Twenty results is one screen, and there is no paging, because a longer list is a scroll rather than an answer.

The client debounces 300 ms and keeps a request counter, so a slow answer to an earlier keystroke cannot land on top of the answer to the current one. That bug is invisible in testing on a desk and constant on a phone with one bar of signal.

The search knows nothing about you

The lookup path in Munchable is deliberately condition-blind: the server is told a barcode and nothing about who is asking or why, and the verdict is computed on the device afterwards. Search inherits that unchanged. The query is matched against product names, it is not stored, not logged, and not keyed to the account. The rate limiter fails open, because a search is a read that reveals nothing about anybody's health and refusing it has a cost but no benefit.

Tapping a result does not shortcut anything either. The app takes the barcode off the result row and runs the ordinary lookup, so the cache, the trust status and the free-tier quota all apply exactly once, and the search route never has to know that any of them exist.

Look at it yourself

• The search endpoint is GET /api/product-search on munchable.app, and it needs an account. curl -s "https://munchable.app/api/product-search?q=oat" answers {"error":"unauthorized"} with a 401. That is the whole auth model from the outside: no account, no catalog.
• Try the search itself by signing in at munchable.app/get-started and choosing "Continue in browser", which hands your session to the web build of the app. The search screen is the magnifying glass on the hub, next to the scanner.
• The privacy notice at munchable.app/privacy is where the condition-blind claim is written down in a form you can hold us to, including the paragraph on the barcode counter that feeds the ranking: a barcode and a number for the calendar month, with no account identifier and no timestamp finer than the month.

The part I keep coming back to is that none of the interesting decisions here were about text matching. They were about which rows are allowed to be findable, and what a result promises the person who taps it.