Slice 3 — Inventory & Tax

The product catalog and stock tracking. Builds the SKU/variant model, vendor relationships, inventory categories, the tax-category column that drives BC PST rules, and manual stock adjustments.

Status: Schema complete, AIM data migrated (234 manufacturers, 473 categories, 165 subcategories, 9,337 products, 9,338 variants, 2,544 barcodes). The Inventory tab now shows live read-only stats (top-stocked, low-stock, variant detail). Full CRUD UI is still mockup. Inventory is accessible through other slices (Service tickets and Sales reference it).

Focus steward: #inventory-search. Registered on screen activate; refocus returns to it after every modal close, print, and idle window. See focus stewardship.

Drafted from planning · v0.1

Scope

inventory_skus, inventory_variants, inventory_categories, vendors
inventory_adjustments (manual stock corrections)
BC tax engine (src/lib/tax-bc.js)
Search across SKU + variant + barcode
Tax-category dropdown drives PST application
Low-stock detection (daily cron)

Schema

See SKU and variant entity.

Endpoints

GET /api/inventory/search?q=... — variant-level search
GET /api/inventory/top-stocked?limit=N — highest on-hand quantities (the "what's in the back" view)
GET /api/inventory/top-velocity?limit=N — 90-day units-sold ranking (the "what actually sells" view; anchor is the latest posted-transaction date until live POS data flows, then switches to current_timestamp). Powers the Sales-screen Frequent tab.
GET /api/inventory/low-stock — variants below threshold
GET /api/inventory/skus/:id — SKU detail with all variants
POST /api/inventory/skus — create
PUT /api/inventory/skus/:id — edit
DELETE /api/inventory/skus/:id — soft-delete (is_active=0); blocked if any variant has non-zero stock or open POs
POST /api/inventory/variants — create
PUT /api/inventory/variants/:id — edit (price, threshold, etc.)
DELETE /api/inventory/variants/:id — with blockers
POST /api/inventory/adjustments — manual stock fix
GET /api/inventory/low-stock — variants below threshold
GET /api/inventory/categories — list
GET /api/vendors — list

UI

Inventory screen (mockup-only today; live build pending):

Tabular SKU list with photo, name, brand, price, on-hand
Filter by category, vendor, low-stock-only
Click a SKU to open the detail
SKU detail shows variants in a sub-table

In-situ edit mode adds:

Column visibility/order editable
Click price to edit inline
Click photo to upload new
Tax category dropdown on each row
- Add product opens full form
Drag-and-drop reordering of variants within a SKU

What's built

Schema migrations applied (slice-3 migration 003_inventory_tax.sql) ✓
9,337 SKUs + 9,338 variants + 234 manufacturers + 473 categories + 165 subcategories + 2,544 barcodes migrated from AIM ✓
/api/inventory/* endpoints: stats, search, top-stocked, top-velocity, low-stock, variant detail ✓
Live read-only Inventory tab (stats + low-stock surfacing) ✓
Tax engine (src/lib/tax-bc.js) — used by service tickets and (eventually) sales ✓

What's not yet built

Full CRUD UI — read-side Inventory tab is live; create/edit/delete screens are still the static mockup
Barcode scanning — schema has barcodes table; reader integration not wired
Reorder PO generation — handed off to slice 6
Vendor-API integrations — QBP, Shimano stock checks; deferred
Daily low-stock cron summary — endpoint surfaces low stock on demand; the scheduled write-summary cron is not yet wired (no Worker cron handlers exist yet)

Stock behavior + movements (live 2026-05-15/16)

Two related schema additions land the stock-side bookkeeping that real POST /api/sales needs:

`products.stock_behavior` (migration 028, with revert + backfill in 029)

A three-way enum on every product, replacing the old is_stocked boolean (which broke down for rentals):

Value	Sale path	Used for
`tracked`	Writes an `inventory_movements` `sale` row + decrements `inventory.quantity_on_hand`	Real goods — the default
`untracked`	No-op for inventory; line lands in `transaction_lines`; revenue recorded	Services / labor / shop-defined open lines / fees
`rental`	`/api/sales` REJECTS rental lines with "use the rental flow" error	Rental fleet bikes (Slice 7)

Enforced at the API layer (apiInventoryProductUpdate) because SQLite ALTER TABLE ADD COLUMN can't carry a CHECK constraint.

Backfill rules (in migrate_aim.py + a follow-up scan):

product_type='service' → 'untracked' (689 rows in Swicked)
variants in active rental_fleet → 'rental' (0 today; ready for fleet seeding)
everything else → 'tracked' (DEFAULT)

The 689 service-tagged-but-clearly-goods rows surface as migration_issues entries for the owner to bulk-reclassify before launch — see migration from AIM.

`inventory_movements` (migration 027)

Every change to inventory.quantity_on_hand writes one row here. The inventory table stores the current absolute value; this table stores the deltas with reasons so an owner can answer "where did 4 units go?".

`movement_type`	Direction	Triggered by
`receive`	+	PO receipt, supplier delivery, trade-in intake, manual entry from Inventory edit modal
`sale`	–	`/api/sales` after the transaction lands (only for `stock_behavior='tracked'` lines)
`return`	+	Customer return
`adjust`	±	Physical-count reconciliation; requires `reason`
`write_off`	–	Destroyed / lost / damaged; requires `reason`
`transfer`	paired	Between locations (out + in rows)

new_quantity_on_hand stores the post-row absolute value, so a query like "show the last 30 days for variant X" renders a running balance without re-deriving from history.

New endpoint: POST /api/inventory/movements for manual adjustments + write-offs (sale + receive + return are driven by their own endpoints which call the helper internally).

Inventory page redesign — mirrors Sales (2026-05-19)

The Inventory page was rebuilt to mirror the Sales screen 1:1. Where Sales has a Cart + page-actions (+ Customer · Park · Estimate · Charge), Inventory has a Bucket + matching bucket-actions (Generate PO · Receive · Clear). Same mental model — operator collects items, then commits — different commit target: a sale becomes a transactions row, a bucket becomes one or more purchase_orders rows in an atomic per-supplier split.

What the redesign adds:

Bucket panel on the right, backed by inventory_order_queue rows
PO ledger below the Bucket; clicking a row opens the PO detail modal; clicking-PO-to-populate copies its lines into the Bucket
Bucket supplier groups as colored accordions (collapsed by default), with Edit + View pills on the header
+ New SKU opens the Product Card in create mode (the same unified helmOpenVariantEdit modal that opens for an existing variant — consistent in-situ pattern)
Inventory search uses the FTS5 engine (Phase 1, search engine)
Per-line supplier dropdown in the Bucket pulls from each variant's linked suppliers

Full PO workflow detail (Generate PO, Receive mode, cost basis on receive, customer earmark) lives in slice 6.

`inventory_order_queue` (migration 034, live 2026-05-16; extended by 038/045/046)

Staff click Add to Order Queue from the product-card editor (opened from a Sales-cart-line click) to flag a variant for reorder. Each queue row records:

Column	Notes
`variant_id`	FK → `product_variants`
`supplier_id`	FK → `suppliers`; picked from the variant's linked suppliers
`quantity`	How many to order; defaults to 1
`status`	`pending` → `ordered` → `received`, or `cancelled`
`added_by_staff_id`	Who flagged it
`note`	Optional free text
`external_po_ref`	Set when status moves to `ordered`

Lifecycle:

pending  → ordered    (a PO is placed; external_po_ref captures it)
         → cancelled  (owner removed before ordering)
ordered  → received   (goods came in; closes the loop)

Endpoints:

POST /api/inventory/order-queue — flag a variant
GET /api/inventory/order-queue?status=pending — list, filterable
DELETE /api/inventory/order-queue/:id — cancel a pending entry

The intended workflow shipped 2026-05-19: the Bucket panel groups by supplier (colored accordions) and Generate PO does the atomic per-supplier split. See slice 6 → Bucket pattern.

Migrations on top of 034:

038 — accept bulk items: adds bulk_slug + bulk_label + supplier_name_free, with a CHECK enforcing variant XOR bulk
045 — converted_to_po_id + customer_id (Sales Deposit special-order earmark)
046 — source_po_line_id closes the receive loop back to the PO line + rolls up PO status

Bulk items (migrations 038 + 042, live 2026-05-19)

Bulk items (cable housing, ferrules, bar tape, brake cables) live as cart-tile pills, not as product_variants rows. Two tables back them:

bulk_items (migration 042) — catalog of small parts. Before this table, the Bulk-items dropdown tiles were hardcoded in public/index.html; now a + Add Bulk Item button can persist new rows that the dropdown picks up on next load. Existing hardcoded tiles continue to work — the UI merges hardcoded entries with DB rows at render time.
bulk_item_splits (migration 038) — the deconstruct config: how many smaller sellable units 1 received bulk produces, plus the child unit's display name and price (e.g. 1 reel of cable housing → 25 cuts of housing). The stock conversion ships in a later slice; this table captures the rule so the breakdown is visible + auditable now.

Bulk items get keyed in carts and buckets by bulk:<slug> so all existing cart-line + bucket handling carries through unchanged.

Cost basis — weighted average on receive (2026-05-19)

When a PO line is received, inventory_movements records a receive row. The unit cost on that row contributes to a weighted-average cost basis for the variant — not last-cost, not most-expensive, weighted by quantity. Operators can override the cost at receive time if the invoice line differs from the PO; the override is logged.

The inventory.cost_cents column carries the current weighted average; receive operations update it atomically alongside the on-hand bump.

Bulk items in the Order Queue (migration 038, live 2026-05-19)

Bulk items (cable housing sold by the foot, ferrules from a bag of 100, etc.) live as cart-tile pills today, not as product_variants rows — they get added to the cart from a pill grid and decrement against a per-bulk counter. To let them flow through the same Order Queue surface as inventory items without adding a synthetic product_variants row, migration 038:

Relaxed inventory_order_queue.variant_id to nullable
Added bulk_slug, bulk_label, supplier_name_free columns
Added a CHECK so every row points to exactly one of variant_id OR bulk_slug (never both, never neither)

Plus a new bulk_item_splits table that records the deconstruct config: when one bulk receive arrives (1 spool of cable housing), how many smaller sellable units it produces (e.g., 200 feet), what the child unit is called for display, and the child unit price. The actual stock conversion ships in a later slice; this table captures the rule so the breakdown is visible + auditable now.

Tax engine

src/lib/tax-bc.js exports taxForLine({ tax_category, bundled_with_bike, is_used }) returning {gst_rate, pst_rate}. Used by:

Service-ticket line addition (immediate tax recalc on parent)
Sales line addition (cart re-totaling)
Receipt generation (final-confirmed tax)

See ADR-0020: BC tax rules in code.

Acceptance criteria for "slice 3 done"

The Inventory screen replaces the current mockup
Operators can edit SKUs, variants, prices in-situ
Low-stock report has a "Create PO" button (handing to slice 6)
Barcode scan opens the variant directly

Scope​

Schema​

Endpoints​

UI​

What's built​

What's not yet built​

Stock behavior + movements (live 2026-05-15/16)​

products.stock_behavior (migration 028, with revert + backfill in 029)​

inventory_movements (migration 027)​

Inventory page redesign — mirrors Sales (2026-05-19)​

inventory_order_queue (migration 034, live 2026-05-16; extended by 038/045/046)​

Bulk items (migrations 038 + 042, live 2026-05-19)​

Cost basis — weighted average on receive (2026-05-19)​

Bulk items in the Order Queue (migration 038, live 2026-05-19)​

Tax engine​

Acceptance criteria for "slice 3 done"​

See also​