A row somebody photographed may never claim our top badge, and a CHECK constraint is what says so

Munchable reads a food label and tells you whether the product fits the gut conditions you manage. Everything it can answer for lives in one Postgres table, one row per barcode, and those rows arrive from two places that do not deserve the same amount of trust:

• a bulk import, where the ingredient list came pre-structured and flagged as complete;
• a photograph somebody took of a pack in a shop, read by a model, mapped to our taxonomy.

The engine stamps a confidence badge on every result, and the top one is not available to the second kind of row. Not discouraged. Not unlikely. Not available. This post is about the four places that enforce one sentence, and why none of them is a comment.

What the top badge actually requires

The badge comes out of the engine, from the normalized product and nothing else:

export function assessConfidence(product: NormalizedProduct): Confidence {
  const hasIngredients =
    product.ingredientsTags.length > 0 || (product.ingredients?.length ?? 0) > 0;
  if (!hasIngredients) return 'unknown';

  const unknownN = product.unknownTags.length;
  const completed = product.statesTags?.includes('en:ingredients-completed') ?? false;
  const realUnmapped = Math.max(0, (product.unknownIngredientsN ?? 0) - product.droppedNoiseN);
  if (completed && unknownN === 0 && realUnmapped === 0) return 'verified';
  return 'database';
}

Three tiers. unknown is no ingredient data at all, which fails closed and can never read green. database is usable data. verified is the top of the scale, and it needs one specific string in states_tags: somebody upstream asserted that this ingredient list is the whole ingredient list.

A photograph of a pack has not earned that assertion. The crop might have missed a line. The label might continue around the corner of the box. So the rule is that a captured row never carries the tag, which caps it at database forever, and no amount of agreement promotes it past that.

The enforcement used to be a schema, then it was a decision

Originally the two families of rows lived in two tables, and the community one had no states_tags column at all. That is the nicest kind of invariant: nothing enforces it, because there is nowhere to write the violation.

Merging them into one table bought real things. A capture upgrades the row it replaces in place, so the barcode keeps its identity and its scan counts, and the read path stopped being "check here, then check there". What it cost was exactly that structural guarantee. One table means the column exists on every row, and the trust ceiling turned from a shape into a decision somebody has to keep making.

So it became a constraint:

CHECK (source <> 'community' OR states_tags IS NULL)

Declaring it in SQL only would have been one command away from gone

The migration that merged the tables has that CHECK in it. That is not where it lives, though. It is also declared in the Drizzle model next to the columns:

check('products_source_check', sql`source in ('seed', 'community')`),
// The trust ceiling: only a bulk-imported row may claim
// en:ingredients-completed, which is what awards the top confidence
// badge. A user capture carrying it would be a false green.
check(
  'products_community_no_states_tags',
  sql`source <> 'community' or states_tags is null`,
),

The comment above them in the file is blunt about why:

They MUST be declared here, not only in migration 0011: drizzle-kit push makes the live database match this model, so a constraint that exists only in hand-written SQL is one db:push away from being silently dropped.

That is the sort of thing you learn once. A schema tool whose job is to reconcile the database with your model will happily reconcile away anything your model does not know about. If the model is the source of truth, then a constraint that is not in the model is not a constraint; it is a historical event.

Two more places, because the constraint is the last line, not the first

A CHECK rejects the write. It does not tell you which code was about to make it, and a 500 in the contribute path is a user who photographed a label for nothing. So the write side is explicit:

statesTags: null,

That is in the contribute route, in the object it upserts, on purpose rather than by omission. A field that is absent because nobody thought about it looks identical to a field that is absent deliberately, right up until somebody adds a spread.

And the read side used to be the real risk. Shaping a row for the client was two exported functions over the same row type, one for each provenance, which meant the compiler could not stop a captured row being shaped as an imported one. That shape emits dataSource: 'seed' plus states_tags, which is the breach itself, assembled at serve time out of a row that is perfectly legal in the database.

Now there is one function:

const community = row.source === 'community';
return {
  // ...
  ...(community
    ? {}
    : { ingredients: shapeTree(row.ingredients), statesTags: row.statesTags ?? undefined }),
  dataSource: community ? 'user' : 'seed',
};

Provenance decides three things in there and nothing else: the provenance line the result screen shows, whether the structured tree and states_tags ride along, and where the freshness date comes from. Everything else is written once instead of twice, because everything else genuinely does not care.

The scripts are scoped, not careful

The founder-run maintenance scripts are the fourth place. They do not avoid captures by being written thoughtfully, they avoid them by having source = 'seed' in the predicate, on both halves of anything with a subquery:

delete from catalog.products where source = 'seed' and barcode in (
  select barcode from catalog.products
  where source = 'seed'
  order by ...
  limit $2)

The comment next to it says what the second one is for: catalog.products holds imported rows and contributed rows in one table, and a contributed row is somebody's work. The same scoping is on the tag compaction pass and the hygiene pass. There is no code path in the repository that can delete, rewrite or re-flag a row a user photographed.

The import guard that used to sit beside these, an ON CONFLICT ... WHERE products.source = 'seed' so a re-import could never overwrite a capture, is gone. Not relaxed: the import scripts were deleted once the catalogue was loaded, so there is no writer left for it to guard. Worth saying plainly, because a retired guard and a removed guard look the same in a diff.

Why four places for one sentence

Because this failure is silent and the output is a colour.

Getting the trust ceiling wrong does not throw. It does not show up in a test that asserts on an error. It promotes user-captured products to full confidence, and the user sees a slightly more confident badge next to a verdict they were already going to read. Nothing is reported, by anyone, ever. The category's worst outcome is a confident green on food somebody is avoiding, and this is one of the shorter paths to it.

Compare that with the failure mode of over-enforcing. A legitimate imported row gets capped at the middle badge, somebody notices a badge that reads lower than it should, and the fix is a line of SQL. The two mistakes are not the same size, so they do not get the same number of guards.

The rest of the trust loop is deliberately much softer than this: there is no moderation queue, one report demotes a row immediately, and one human saying "looks right" counts the same as one matching machine read. Those are tuning decisions and we change them. The ceiling is not a tuning decision, which is why it is the only part of the loop written four times.

What the badge no longer does is the subject of the ratio we deleted, which was making a seventh of all scans say "can't assess" over label boilerplate.

Go and look at a badge

Every public answer page runs the same engine the app runs, over the same catalogue:

• Is almond ok with gastroparesis?
• Is apple low FODMAP?
• The full set: munchable.app/answers

Then open app.munchable.app in a browser, pick a condition, and search for something ordinary like "hummus". The provenance line under the verdict tells you which kind of row answered you and how fresh it is. Scan or search for something obscure enough to miss, photograph the label, and watch the badge the capture lands on. It is the middle one. It stays the middle one.