The only free text a stranger can write into our catalogue is a product name, and the isNull in the WHERE is the whole concurrency story

Munchable is a barcode scanner for people with digestive conditions. When you scan something it has no ingredient data for, you can photograph the label to add it, which is the arrangement described on the licenses page and in section 8 of the terms. The photograph is read once and discarded.

Since September the capture flow ends on a naming step, so a new contribution arrives with a name already attached. Rows added before that have no name, and to every later scanner they read as "Product 5021991938719". So the result screen offers those rows a one-line "Name it", and that is the subject of this post: one small POST route that is unlike every other write in the product.

Why a name is the awkward field

Everything else a contribution carries is machine-read from a label and has gates behind it before anybody else sees it. A typed name has none of that. It is a person's words, going into a row that strangers will read.

So the route is fill-if-empty only, and the comment says what that costs:

// Fill-if-empty ONLY. A name Munchable already holds is never overwritten by an
// open write: the ingredients text has to survive similarity and consensus
// before it is trusted, and a name had no such gate, so one POST could rename
// a well-known product for everyone. Correcting a wrong name is a support job.
//
// The name is the one free-text field a scanner ever sees ... Display only;
// the rules never read it.

Two decisions worth separating there.

A write with no quality gate gets the weakest possible power: it may create, never replace. The cost is that a typo is permanent until a human intervenes, and that cost was accepted explicitly rather than solved with an edit feature that would reopen the hole.

And the field is display only. Nothing keys off this string, no matching reads it, no logic parses it. That is what makes a simple scrub sufficient. The moment a free-text field starts feeding a lookup, a scrub is the wrong tool and you need a normaliser with tests.

Check-then-write is a race, so the check is in the write

The obvious implementation reads the row, sees a null name, and updates it. Two scanners naming the same product in the same second both read null and the second one wins, silently.

So the predicate goes into the statement:

// The `isNull` in the WHERE makes fill-if-empty atomic: two scanners naming
// the same product at once cannot overwrite each other.
const updated = await db
  .update(catalogProducts)
  .set({ productName, ...(brands ? { brands } : {}), updatedAt: new Date() })
  .where(and(nameable, isNull(catalogProducts.productName)))
  .returning({ productName: catalogProducts.productName, brands: catalogProducts.brands });
if (updated.length === 0) {
  return NextResponse.json({ error: 'already_named' }, { status: 409, headers: rlHeaders });
}

There is still a read before it, but it only exists to answer the client better: a barcode that is not nameable gets a 404, one that is already named gets a 409 with the existing name so the screen can show what it is called instead. The read is a courtesy. The isNull is the correctness.

Zero rows back is not an error anybody needs to see as an error. Somebody else named the product first, which is a perfectly good outcome for a shared catalogue, and the 409 says so.

When two tables became one, a boundary became a WHERE clause

This is the part I would want somebody else to read:

Community rows only, and never a withheld one. That scope used to be structural. Captures lived in a table of their own, which held nothing else, so "name a community product" could not reach any other kind of row by construction. The catalogue is one table now, and without those two predicates a signed-in account could write an unvetted name onto any unnamed row in it, permanently, because this route is fill-if-empty and the app offers no correction.

Merging the tables was a good change for other reasons. But a safety property that used to be enforced by the shape of the schema turned into two conditions in a query that somebody has to remember to write, in every route that touches the table:

const nameable = and(
  eq(catalogProducts.barcode, barcode),
  eq(catalogProducts.source, 'community'),
  ne(catalogProducts.status, 'withheld'),
);

Note the single named constant. It is used in the read, in the update, and nowhere is it spelled out twice, because the dangerous version of this code is the one where the select and the update have slightly different ideas about what is nameable. The result screen gates the button on the same flag, so the UI and the server agree about which rows can be named rather than the UI merely hiding something the API would allow.

If you take one thing from this post: when you merge two tables, grep your comments for the words "cannot" and "only". Each one is a claim that was true about the old schema, and each is now a predicate or a bug.

The scrub, and why rejection is not fatal

The name goes through the same cleaner the capture flow uses:

/**
 * Every later scanner of the barcode sees it, so it is kept to a plain label:
 * control characters stripped, whitespace collapsed, links and addresses
 * refused outright, markup refused, hard length-capped. A rejected name is
 * dropped, never fatal: the ingredients are the contribution.
 */
export function cleanDisplayName(value: unknown, max: number): string | null {
  if (typeof value !== 'string') return null;
  const cleaned = tidy(value);
  if (cleaned.length < 2 || cleaned.length > max) return null;
  if (URL_RE.test(cleaned) || EMAIL_RE.test(cleaned)) return null;
  if (/[<>]/.test(cleaned)) return null;
  return cleaned;
}

120 characters for a name, 80 for a brand, two characters minimum. The URL test is deliberately crude and checks for a scheme, a www., or a handful of common TLDs, because the thing being prevented is somebody using a shared label as an advertising slot, not every conceivable URI.

The last line of that comment is the one that matters in the capture flow. If a contributor's typed name fails the scrub, their contribution still lands; the name is simply dropped. A request that refused the whole capture because of a bad name would throw away the part that was machine-read and useful in order to defend a field that is cosmetic.

In this route, where the name is the entire payload, a rejected name is a 400, because there is nothing else to keep.

The boring parts that are not optional

The write is rate limited in the same three buckets as product reports: per user per minute, per user per day, and per IP. All three are checked before anything is read. If the limiter itself is unreachable the route answers 503 rather than proceeding unlimited, because this is a write other people read.

A successful write busts the cached lookup for that barcode, so the next scanner gets the name rather than a cached "Product 5021991938719" for however long the TTL had left.

And the whole route is one POST, about 100 lines with its comments, doing nothing clever. That is roughly the right ratio for an endpoint whose only job is to let a stranger fill in a blank exactly once.

The user-facing half of all this is two paragraphs: section 8 of the terms and the licenses page. Both are worth reading next to the code, because every predicate above is there to make one of those sentences true.