A catalog lookup that counted products as handled because the ID looked right

Flow

How the lookup decides what's already handled

1. 1Read review receipts⌄

   Only for the right setup and brand, from a queue that's still running.

2. 2Read repair notes⌄

   Only from repairs marked applied, for the same setup and brand.

3. 3Merge the two lists⌄

   Each table is filtered on its own first.

4. 4Cross off known IDs⌄

   Whatever is left in the catalog is new work.

Products in one brand's catalog that hadn't been dealt with yet were what a new lookup in the ordering system was meant to find. It counted a product as dealt with if any row anywhere had an ID of the right kind. About twenty minutes later I rewrote that query. I can't tell if the loose version ever ran on real data.

Two tables and one loose question

The lookup builds a list of known product IDs from two tables. One has receipts from my review queue. The other has notes left by a repair job that fills in missing product IDs.

Then it subtracts. Catalog IDs on the known list are dropped, and the rest are new work. If none are left, the report says there's nothing new.

Both reads asked only one thing: is this a product ID? They didn't check which store setup the row was for, which brand, or whether the queue was still running. They didn't check if a repair had actually been applied.

What a too-wide list does

Extra IDs on the known list can only make the "new" pile smaller. A product I'd never touched could vanish from it because some unrelated row had the same ID. If the pile gets to zero, the report looks like "done."

I know the query would let those rows in. I haven't checked how many there were. Either way, there's no error to catch. The only symptom is fewer products marked new.

The lookup's question, rewritten

Now each read has to match the right setup, brand and source. Receipts must come from a queue that's still active. Repair notes must belong to a repair marked applied. Each table is filtered on its own before the two lists are merged.

Each read now carries the job and open-queue conditions instead of only the kind of ID.

Another part of my program, built earlier the same day, already read the repair notes this way. The lookup just didn't copy it.

A stub that answers every question the same way

My tests use a stand-in database that gives back the same rows no matter what you ask. So a test that only looks at the answer can't tell a good question from a bad one. The test now saves the question the lookup sends and checks what's in it.