SKU and variant

A "SKU" is a product line item (the master concept). A "variant" is a specific sized/colored version of that SKU, against which inventory is tracked. Many SKUs have only one variant; bikes and apparel typically have many.

Drafted from planning · v0.1

Why two tables

If we only had SKUs, we couldn't track sizes per item without piling fields onto the master. If we only had variants, the human "this is a Specialized Sirrus 4.0" concept would be scattered. The split: SKU is the brand/model concept; variant is the inventory unit.

Table: inventory_skus

Column	Type	Notes
id	INTEGER PK
sku_code	TEXT UNIQUE	Internal SKU code (often from vendor)
name	TEXT	"Specialized Sirrus 4.0 2024"
brand	TEXT	"Specialized"
model	TEXT	"Sirrus 4.0"
category_id	INTEGER FK → inventory_categories	"bike", "component", "apparel", etc.
description	TEXT	Long description
default_price_cents	INTEGER	Default sell price; can be overridden per variant
vendor_id	INTEGER FK → vendors	Primary supplier
tax_category	TEXT	'bike_new', 'component', 'service_labour', etc.; drives BC tax rules
image_url	TEXT	R2 URL for product photo
is_active	INTEGER (0/1)	Soft-delete flag
created_at, updated_at	TEXT

Table: inventory_variants

Column	Type	Notes
id	INTEGER PK
sku_id	INTEGER FK → inventory_skus
variant_code	TEXT UNIQUE	Per-variant SKU (e.g., includes size)
size	TEXT	"S", "M", "L", "XL", "54cm", "27.5"
color	TEXT	"Red", "Blue/Black"
qty_on_hand	INTEGER NOT NULL DEFAULT 0	Current count
qty_committed	INTEGER NOT NULL DEFAULT 0	Reserved (e.g., bike on hold)
qty_on_order	INTEGER NOT NULL DEFAULT 0	In open POs
reorder_threshold	INTEGER	When qty drops below this, low-stock alert
reorder_qty	INTEGER	Suggested order quantity
price_cents	INTEGER	Override of SKU default if needed
cost_cents	INTEGER	Cost basis (for margin calc)
barcode	TEXT	Scannable barcode
created_at, updated_at	TEXT

Related tables

• inventory_categories — Bikes, Components, Apparel, Accessories, Parts, Service Labor
• inventory_adjustments — manual stock corrections (theft, damage, count fix)
• vendors — Specialized, Trek, QBP, Shimano
• purchase_orders + purchase_order_lines — incoming stock

Behaviors

Search

GET /api/inventory/search?q=...:

• Matches SKU name, sku_code, variant_code, barcode
• Returns variant-level results (one row per variant matching)

Stock adjustment

POST /api/inventory/adjustments:

• Body: { variant_id, delta, reason, notes? }
• Updates inventory_variants.qty_on_hand atomically
• Writes inventory_adjustments row
• Audit-logged

Reorder alerts

Daily cron checks each variant where qty_on_hand + qty_on_order < reorder_threshold and writes to a daily low-stock report. Surfaces on the Today dashboard.

Tax category

The tax_category column on inventory_skus drives PST application:

• bike_new — GST only, no PST
• bike_used — GST only
• component — GST + PST normally; if bundled with bike at sale, PST exempted
• apparel — GST + PST
• accessory — GST + PST
• service_labour — GST + PST

See ADR-0020.

In-situ editing surface

On the Inventory screen, in edit mode:

• Column visibility/order editable
• Click any price to edit inline
• Click photo to upload new
• • Add product opens full add form
• Tax category dropdown appears on each row

On a product detail screen, in edit mode:

• All fields editable
• Variant table: drag handles, ×, +
• Vendor + tax category in dropdowns

Migrated from AIM

For Swicked: 9,337 products, 9,338 variants, 234 manufacturers, 473 categories, 165 subcategories, and 2,544 barcodes from AIM's pim (product image / product master) + related tables:

• pim.pim_pk → inventory_skus.sku_code
• pim.pim_desc → inventory_skus.name
• Category mapping by pim.pim_cat → inventory_categories (cat_pk=448 is BIKES, special handling)
• Variants from AIM's pimsize / pimcolor joins → inventory_variants
• qty_on_hand from AIM's real-time stock counts

See also

• Slice 3 — Inventory & Tax
• Slice 6 — Purchase Orders
• ADR-0020: BC tax rules
• Transaction entity