Inventory System for Bike Shops

The problem

• Every model ships in a matrix of sizes and colors, so "one Trek Marlin in stock" is meaningless. You need to know it is a Medium in matte blue, sitting in bin R3, before you promise it to a customer.
• Complete bikes and e-bikes carry frame serial numbers, but they live in a shoebox or a spreadsheet, so warranty claims and theft recovery fall apart the moment someone walks in with a serial and a story.
• The service department installs chains, cables, and tubes all day, and those parts almost never get deducted, so the shelf count silently drifts away from reality across the whole season.
• Peak season swings hard, but without per-variant reorder points and real sell-through data, pre-season buying is a guess, and you either sit on dead stock or run out of the size everyone wants in April.

A day in the system

1. Morning: a shipment from your distributor arrives. You scan each carton and receive it against the open purchase order, so stock lands in the right variants with the correct cost and bin location.
2. Each complete bike in that shipment gets its frame serial scanned into a serialized unit and marked in_stock, tying that specific physical frame to its variant.
3. A walk-in wants a Medium gravel bike in green. You check the variant matrix, see one in stock at bin R3, and reserve it so nobody else sells it out from under them.
4. At checkout the sale deducts that variant, flips the serialized unit to sold, attaches it to the customer with a warranty-expiry date, and prompts you to register the frame serial for theft recovery.
5. A regular drops off their bike for a tune-up. You open a work order against their unit serial, set a promised-ready date, and it lands in the service queue.
6. The mechanic adds labor plus a new chain, two cables, and a tube. Each part deducts from stock as it is added to the job, so the count stays true whether a part sells over the counter or gets installed in back.
7. End of day the reorder dashboard flags every variant below its reorder point, groups them by vendor, and drafts POs, including special-order lines for customers waiting on a size you do not stock.
8. You cycle-count one bin, reconcile the physical count against the system, and any discrepancy logs against that variant so shrinkage becomes visible instead of a year-end surprise.

Where AI trips up

• Treating a bike model as one SKU. A model is not stock, the size-and-color variant is. If the schema does not split model from variant, and let each variant carry its own count, cost, barcode, and bin, you will oversell a size you do not have and the whole count becomes fiction.
• Modeling serialized bikes and bulk parts the same way. A frame is a unique unit with one serial and a lifecycle (received to sold to warranty); a box of tubes is a fungible quantity. Force one model on both and you either invent fake serials for tubes or lose the serial on a $5,000 e-bike. You need a quantity layer and a serial layer.
• Letting service jobs bypass inventory. Every part a mechanic installs has to deduct from the same stock the sales floor sells, or the count drifts all season. The naive build tracks labor and forgets that a work order is also an inventory transaction.
• Breaking the serial-to-customer-to-warranty chain. Warranty claims and theft recovery both hinge on frame serial plus purchase date plus owner. If the serial is a free-text note instead of a linked record, you cannot answer "is this our bike?" when a stolen frame comes in, or process a warranty two years later.
• Collapsing unit states. Reserved, sold, and out-for-warranty are three different statuses. Merge them and a bike that is physically gone still shows as sellable, or a reserved special order gets sold to a walk-in. Model unit status explicitly and never let stock go negative.

FAQ

How do I handle one bike model that comes in five sizes and three colors?

Model it as a two-layer catalog. The model (Trek Marlin 7, 2026) is one product record, and each size-and-color combination is its own variant with its own barcode, cost, price, count, and bin. Stock always lives on the variant, never the model, so "three in stock" is always a specific size and color and you can never accidentally sell a Large you do not have.

Do I really need to track frame serial numbers?

For complete bikes and e-bikes, yes. The frame serial is what ties a specific unit to its buyer, its warranty window, and any theft claim. Model those bikes as serialized units, one row per physical frame with a status, and keep tubes, cables, and bolts as plain quantity. Registering the serial with a public registry such as Bike Index at the point of sale also helps recover it if it is ever stolen.

How do repair jobs keep from throwing off my counts?

Make the work order an inventory transaction. When a mechanic adds a chain or a tube to a job, that part deducts from the same stock the sales floor sells from. Labor is billed separately, but every physical part that leaves the shelf, whether sold over the counter or installed in back, comes out of one count.

Can it handle a special order for a customer who wants something I do not stock?

Yes. A special order is a purchase-order line tied to a customer. You order the specific variant from the vendor, and when it arrives it is received against the PO and flagged as spoken-for rather than free stock, so it does not get sold to a walk-in before your customer comes to pick it up.

How does this help with pre-season buying and seasonality?

Because every variant carries a reorder point and the system tracks real sell-through, the reorder dashboard shows what is moving and what is below threshold heading into spring. You place vendor POs from that view. The point is not to automate the buying decision, it is to make the shelf-versus-demand picture obvious so you are not guessing before your busiest months.