A Small Bike Shop's Recommendations in One File: Similar Products, Parts That Fit and Stock Levels

Picture a bike shop with one store and a web shop selling about 3,000 products, from helmets to brake pads. The owner wants the product pages to suggest things worth adding to the basket. The web platform came with a “customers also bought” box, and it keeps suggesting lights that are out of stock and mudguards that don’t fit the customer’s bike.

Good suggestions mix two kinds of knowledge that usually live apart. Whether two products are alike is a question about meaning, which vectors are good at. Whether a part fits a bike is a plain fact from the supplier’s spreadsheet, and so is the stock count, except that it changes every time the till rings.

Alike, and on the Shelf

Every product is a record with its title, price, stock count and a vector made from its description. For “similar products”, the page asks for the six closest products that are in stock, leaving out the one being viewed:

hits, err := db.Nearest("product", productVec, 6, "stock > 0 AND key <> ?", productKey)

productVec is the product’s own stored vector, so showing the page doesn’t need an embedding model at all. The filter is SQL on the same row as the vector, which means the stock count it reads is the current one.

What Actually Fits

Similarity can’t tell whether a set of mudguards fits a frame. Two sets can look identical in a photo and need different clearances. So fit is stored as links, loaded from the supplier’s compatibility list: each part gets a fits link to every bike model it fits. Everything that fits a customer’s bike is one walk away:

fits, err := db.Walk("model:city-8-2023", hypercrux.In, "fits", 1)

The walk goes backwards along fits links, from the bike model to the parts that point at it. Customers who tell the shop which bike they ride get a “fits your bike” section built from this.

In Stock and Closest in Style

For the basket, the shop links accessories to the products they go with, such as lights and locks to helmets, or bottle cages to frames. One query brings it together:

rows, err := db.Query(`
	SELECT p.key, p.title, p.price
	FROM json_each(walk(?, 1, 'accessory_of', 'in')) w
	JOIN product p ON p.key = w.value
	WHERE p.stock > 0 AND p.vec IS NOT NULL
	ORDER BY distance(p.vec, ?)
	LIMIT 4`, cartItem, cartVec)

That’s the in-stock accessories linked to the item in the basket, closest in style first. A matte black helmet gets the matte black lights ahead of the neon ones, as long as the descriptions say so.

The Till Writes Plain SQL

The till system reports each sale to the web server, and a short script there records it:

UPDATE product SET stock = stock - 1 WHERE key = ? AND stock > 0

There’s no sync job. The next page view reads the new count from the same row. The script can be in any language that talks to SQLite, because the rules that tie links and vectors to their records are triggers stored in the file. Fields are ordinary columns, too, so whoever runs the site can index stock for the morning restock list:

CREATE INDEX product_stock ON product (stock);
SELECT key, title FROM product WHERE stock = 0;

Size and Speed

At 3,000 products, exact search is comfortable. In the recorded benchmarks on a two-core cloud machine, a search among 1,000 vectors of 384 values took about 4 milliseconds, and among 10,000 about 42. Walks are faster still: one link out among 100,000 records took 43 microseconds.

The limits are the usual ones for a single file. It lives on one machine, which here is the server the web shop already runs on, and every program that opens it has to run there too. The vectors are only as good as the product descriptions behind them. A shop with millions of products and traffic spread over many servers needs something bigger.

Shop Knowledge First

Most of what makes a suggestion good is plain shop knowledge, like what fits and what’s on the shelf. Vectors add a sense of style on top. Keeping both in one file means that knowledge can’t drift out of date behind the suggestions. The quick start shows the same moves on a smaller example.