Our search endpoint returns three fields, and its WHERE clause is copied out of an index definition on purpose

Munchable's main input is a camera pointed at a barcode. That is useless to somebody writing a shopping list on the sofa, so there is also a search screen: type a name, tap a result, get the same answer you would have got in the aisle.

The interesting thing about building it was how little the new code was allowed to know. This post is the route, the query, and the screen, in that order, with the three decisions I would defend and the two I would not.

An endpoint that knows nothing

The route is 65 lines including comments, and it returns this per hit:

export interface SearchHit {
  barcode: string;
  productName: string;
  brands?: string;
}

Three fields. Enough to draw a row and nothing else. Tapping that row does not use any product data from the search response: it fires the existing POST /api/product-lookup with the barcode, exactly as the scanner does.

That is the whole architectural decision, and the route's comment is the justification:

// 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.

Three things that are easy to get wrong were avoided by not being touched. The cache that fronts a lookup keeps fronting it. Whether a row is currently trusted is decided in one place. The free tier's scan allowance is counted in one place. A search endpoint that returned full products would have had to re-implement all three and then drift from them.

The cost is an extra round trip on every tap, visible to the user as a spinner on the row they pressed. I will take that trade every time. One endpoint that answers "which products are called this" and one that answers "tell me about this product" is a boundary that stays correct while features pile up on either side of it.

Fail open, and why that is not a careless default

The route rate limits on two keys at once, user and IP, and then does this:

try {
  decision = await enforceParallel([...]);
} catch {
  decision = { success: true };
}

A broken limiter lets everybody through. The reason is in the route header: "Limits fail open, because a search is a read that reveals no health data."

Fail-open is a decision about what the limiter is protecting, not a shortcut. This endpoint is condition-blind: the query is matched against product names, never stored, never logged, never keyed to the account. The worst case of an unlimited search is database load. On a path that touched a user's health profile, the same catch would be indefensible, and in this codebase those paths fail closed. Writing the policy in the comment next to the catch is what stops the pattern being copied to a route where it is wrong.

Short queries are rejected before any of this, with a message rather than an empty list:

export const SEARCH_MIN_CHARS = 3;  // fewer and a trigram match is mostly noise
export const SEARCH_MAX_CHARS = 80; // anything past it is not a search
export const SEARCH_LIMIT = 20;     // the app has no paging; a longer list is a scroll, not an answer

The query that has to agree with its index

Here is the part worth the price of admission. The searchable text and the "is this row servable" predicate are each written once, as SQL fragments:

const haystack = sql`(coalesce(${catalogProducts.productName}, '') || ' ' || coalesce(${catalogProducts.brands}, ''))`;
const servable = sql`${catalogProducts.status} <> 'withheld' and cardinality(${catalogProducts.ingredientsTags}) > 0`;

And the index, in the schema file, is a partial GIN trigram index over the same expression with the same predicate:

index('products_search_trgm_idx').using(
  'gin',
  sql`(coalesce(${t.productName}, '') || ' ' || coalesce(${t.brands}, '')) gin_trgm_ops`,
)

The schema comment spells out why the duplication is deliberate:

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

This is the Postgres fact that makes or breaks a feature like this. A partial index is only a candidate when the planner can prove the query's WHERE implies the index predicate, and an expression index is only a candidate when the query contains the same expression. Get either half slightly wrong, say cardinality(...) > 0 in one place and array_length(..., 1) > 0 in the other, and nothing errors. You get a sequential scan, locally on a small table it is fine, and you find out when the table is large and real.

Two more reasons the index is partial rather than total. A hit that the lookup would then answer "not found" for is worse than no hit, so the index literally cannot produce one. And the partial index covers roughly the servable tenth of the table, which matters when the database is on a plan with a half-gigabyte disk quota: an index you do not build is the cheapest storage saving there is.

The predicate itself is not invented here either. "Servable" is defined as a pure function in the contribution code, and the search predicate is its SQL twin, named in the comment so the next person knows there is a pair to keep in step. That is still two expressions of one rule, which is a real risk, and the mitigation is a comment rather than a test. I am not pretending that is adequate.

Per word, not per phrase

Matching is per word, each tested against the name and brand joined:

/**
 * 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.
 */
export function queryTerms(query: string): string[] {
  return [...new Set(query.split(' ').filter(Boolean))];
}

Brand plus product is how people actually type, and the two halves live in different columns, so one phrase match would miss nearly every query of that shape. The Set drops duplicates, so typing a word twice does not cost a second index probe for the same answer.

Each word 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 escape, searching "100%" looks for "100" followed by anything, and an underscore in a query matches any character. Easy to forget, because the parameterised query is already safe from injection and this is a separate class of bug: not a security hole, just wrong results.

Ranking is three keys:

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

Trigram similarity to the whole query first, so the closest name wins. Then how often the product gets scanned, so a household name outranks a regional variant that happens to share the letters. Then the barcode, which is there purely to make the order total: two rows that tie on both real keys must not swap places between identical requests.

One filter happens in JavaScript afterwards, and the comment explains the only case it catches:

// A servable row with no name cannot be shown as a search result; the
// haystack would have matched on the brand alone.
if (!row.productName) continue;

The screen: six phases and one ref

The client is a debounced search with a stale-response guard. The guard is the part people leave out:

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

Every search increments it and captures the value; both the success and the failure handler bail if the value has moved on. Without it, typing "oat m" and getting a slow answer for "oat" renders the wrong list under the right query, and it only ever reproduces on a bad connection.

The screen has an explicit phase union rather than a pile of booleans:

type Phase = 'idle' | 'short' | 'loading' | 'results' | 'empty' | 'error';

Six states, six pieces of copy, no combination that renders two of them. 'short' is its own phase because "keep typing, at least 3 letters" is a different message from "nothing by that name yet", and conflating them is how a search feels broken when it is working.

The empty state says what to do next, which for this product is a real offer: "Try fewer words or the brand. When you have the pack, scan it and Munchable will read the label and add it for everyone."

And the screen says what a tap costs, before the tap, only when it costs something:

Opening a product checks it against your conditions just like a scan
{isPremium ? '' : ', and uses one of your free scans'}

The race we decided to be honest about

A row is servable when the search runs and gone when the tap lands. Rare, and the handling is a sentence rather than a retry:

// Servable when the search ran, not any more: withdrawn between the
// two requests. Rare, and the honest answer is that it is gone.
showAlert(
  'Not available right now',
  'This product was taken down for checking. Scan the pack when you have it and we will read it fresh.',
);

Two things I like about this, having written a lot of worse versions of it. It does not pretend to be a network error, which is what a generic catch would have done. And it offers the path that still works, because the camera does not depend on the row existing.

The honest ordering note: the tap runs the quota check first, then the lookup, then counts the scan and records history, in the same order the scanner uses, so a product opened from search costs and records exactly what scanning it would. Those three steps living in one order in two places is another duplication held together by a comment. It is on the list.

What I would change

• Two matchers, one feature. ILIKE decides whether a row matches, trigram similarity decides how well. They can disagree: a row can pass the substring test and score poorly, or score well and be excluded. One day that becomes a bug report about a product that "does not come up" when it is there at rank 21.
• SEARCH_LIMIT = 20 with no paging means popular names with many variants are a guess about which twenty. Fine now, not fine at ten times the catalogue.
• Three duplications held by comments rather than tests: the index predicate against the query, the SQL predicate against its TypeScript twin, and the scan sequence against the scanner's.

Go and look at something

The search screen is in the phone app, but the same catalogue reasoning is public on the web: the answers index, a single answer like is soy sauce gluten free, and the recipes, which are the other half of planning at home and are the only pages on the site that are useful before you install anything.

Previously, on the endpoint this one deliberately leans on: the only free text a stranger can write into our catalogue is a product name.

Comments

[Dave Kurian]

The boundary between search and lookup is clear: returning only the row fields keeps trust, cache, and quota decisions in one path. For the fail-open limiter, I'd add a test that simulates the limiter outage and verifies a burst of broad searches stays within a DB query-time or concurrency budget; the article calls out database load as the remaining risk. Have you measured that worst case?

[Odinaka Robert Nnamani]

The duplicated predicate held together by a comment is the detail that would worry me most here. Comments do not fail a build, so the day someone edits the query's haystack expression without updating the index predicate, the planner silently drops to a sequential scan and nobody notices until latency moves. The test that would actually catch drift is an EXPLAIN-based assertion in CI that the query uses products_search_trgm_idx. Did you consider asserting the plan in tests, or is the comment a deliberate trade against test brittleness?