ryan/lunarfront-app
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
lunarfront-app/planning/03_Domain_Inventory.md
Ryan Moon f4e5a57846 Update planning docs to reflect current implementation state 
- Doc 02: Add member_identifier table, member_number, primary_member_id,
  account_number auto-generation, isMinor override, tax_exemption as
  separate table, member move, updated business rules
- Doc 03: Document lookup table pattern replacing pgEnums for status and
  condition, add system/custom value distinction
- Doc 22: Mark all Phase 2 items as complete, add new tables to additions
  section, update audit findings, note admin frontend exists
2026-03-28 09:49:34 -05:00

1274 lines
24 KiB
Markdown
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
Music Store Management Platform
Domain Design: Inventory
Version 1.2 | Updated: Repair Parts Inventory, Bulk Materials, Suppliers
# 1. Overview
The Inventory domain manages all physical items in the store across three distinct inventories: sale inventory (items sold at POS), rental fleet (instruments held for rental), and repair parts inventory (materials consumed in repairs). Each inventory type has different tracking requirements, pricing models, and accounting treatment.
# 2. Inventory Types
Type
Tracking
Appears at POS
Examples
Sale — serialized
Per unit (serial #)
Yes — retail price
Guitars, trumpets, keyboards
Sale — non-serialized
Quantity on hand
Yes — retail price
Strings, reeds, books, picks
Rental fleet
Per unit (serial #)
No — rental only
Student rental instruments
Repair parts — discrete
Qty on hand (integer)
No — repair use only
Valve guides, springs, pads
Repair parts — bulk
Qty on hand (decimal)
No — repair use only
Bow hair, cork sheet, solder
Dual-use
Qty on hand (integer)
Yes — also used in repairs
Strings used in setups, valve oil
Shop supplies
Qty on hand (decimal)
No — overhead cost only
Cleaning patches, lubricant, sandpaper
# 3. Sale Inventory Schema
## 3.1 product
A product is the catalog definition — what an item is, not a specific physical unit. Applies to sale and rental fleet items.
Column
Type
Notes
id
uuid PK
company_id
uuid FK
Tenant scoping
location_id
uuid FK
Physical location — inventory is tracked per-location
sku
varchar
Store SKU
upc
varchar
Manufacturer barcode
name
varchar
description
text
brand
varchar
model
varchar
category_id
uuid FK
Product category
is_serialized
boolean
True = tracked per unit
is_rental
boolean
True = rental fleet item
is_dual_use_repair
boolean
True = also usable in repairs
cost
numeric(10,2)
Supplier cost
price
numeric(10,2)
Default retail price
min_price
numeric(10,2)
Minimum allowed sell price
rental_rate_monthly
numeric(10,2)
Default monthly rental rate
qty_on_hand
integer
Non-serialized items only
qty_reorder_point
integer
Low stock alert threshold
legacy_id
varchar
AIM inventory ID
created_at
timestamptz
## 3.2 inventory_unit
Individual physical units for serialized items and rental fleet instruments.
id, product_id, company_id, location_id, serial_number, condition (varchar — references item_condition lookup), status (varchar — references inventory_unit_status lookup), purchase_date, purchase_cost, notes, legacy_id, created_at
**Note:** `condition` and `status` are stored as varchar columns referencing slug values in lookup tables (not pgEnums). This allows stores to add custom statuses and conditions. Code references system slugs for business logic; custom values are informational.
### Lookup Tables
Both `inventory_unit_status` and `item_condition` are company-scoped lookup tables with the pattern:
Column | Type | Notes
id | uuid PK |
company_id | uuid FK | Tenant scoping
name | varchar | Display name
slug | varchar | Code-level reference (unique per company)
description | text |
is_system | boolean | True = seeded by system, cannot be deleted or deactivated
sort_order | integer | Display ordering
is_active | boolean |
created_at | timestamptz |
### System Status Values (seeded per company)
Status | Description | Set By
available | In stock, ready for sale or rental | Default, return, restock
sold | Purchased by customer | Sale transaction
rented | Out on active rental contract | Rental activation
on_trial | Out with customer on in-home trial | In-home trial checkout (07_Domain_Sales_POS.md §9)
in_repair | In repair shop for service | Repair intake
layaway | Reserved for layaway customer, not available | Layaway creation (08_Domain_Payments_Billing.md §9)
lost | Unrecovered — trial, rental, or inventory discrepancy | Overdue escalation or cycle count
retired | Permanently removed from inventory | Manual retirement
### System Condition Values (seeded per company)
Condition | Description
new | Brand new, unopened or unused
excellent | Like new, minimal signs of use
good | Normal wear, fully functional
fair | Noticeable wear, functional with minor issues
poor | Heavy wear, may need repair
# 4. Repair Parts Inventory
Repair parts are a completely separate inventory from sale items. They are never sold at POS, never appear in the sales catalog, and are tracked specifically for repair cost accounting. The repair parts inventory is accessible only through the repairs module (MOD-REPAIRS).
## 4.1 Part Types
Part Type
Description
Invoice Treatment
billable
Parts charged to customer. Tracked at cost and bill rate.
Appears as line item on invoice at bill_rate
shop_supply
Consumed in repairs but not billed individually. Overhead cost.
Does not appear on invoice
dual_use
Also exists in sale inventory. Repair use decrements sale stock.
Billed at repair rate or retail
flat_rate_material
Material bundled into a flat-rate service charge. E.g. bow hair in a rehair service.
Included in flat rate line item — not itemized
## 4.2 Discrete vs Bulk Parts
Material Type
Tracked As
Examples
Discrete
Integer quantity — whole units only
Valve guides, pad sets, bridge blanks, spring sets
Bulk
Decimal quantity — fractional units allowed
Bow hair (hank), cork sheet, solder (spool), varnish (ml)
Bulk consumable
Decimal — consumed by drops/grams/ml
Lubricant, pad cement, shellac, jewelers rouge
## 4.3 repair_part
Column
Type
Notes
id
uuid PK
company_id
uuid FK
Tenant scoping
location_id
uuid FK
Physical location — repair parts stock is per-location
name
varchar
e.g. 'Bow Hair — Natural White (Standard)'
description
text
part_number
varchar
Manufacturer or supplier part number
part_type
enum
billable | shop_supply | dual_use | flat_rate_material
is_bulk
boolean
True = fractional qty tracking
unit_of_measure
varchar
each | hank | sheet | roll | spool | ml | gram | drop
qty_on_hand
numeric(10,3)
Decimal supports fractional bulk quantities
qty_reorder_point
numeric(10,3)
Low stock alert threshold
cost_per_unit
numeric(10,4)
What store pays per unit — 4 decimal places for small fractions
bill_rate_per_unit
numeric(10,2)
Nullable — what customer is charged per unit if billed per unit
billing_type
enum
per_unit | flat_rate | shop_supply
product_id
uuid FK
Nullable — links to sale inventory for dual_use parts
supplier_id
uuid FK
Primary supplier for reorders
instrument_categories
text[]
Which instrument types use this part
notes
text
is_active
boolean
created_at
timestamptz
## 4.4 repair_part_usage_template
Predefined templates define how much of a bulk material a specific job consumes. Technicians select a template rather than entering quantities manually. Particularly important for bow hair where qty varies by instrument size.
Column
Type
Notes
id
uuid PK
company_id
uuid FK
Tenant scoping
repair_part_id
uuid FK
Which part this template applies to
template_name
varchar
e.g. 'Full size violin bow rehair'
instrument_type
varchar
violin | viola | cello | bass | fractional | other
instrument_size
varchar
4/4 | 3/4 | 1/2 | 1/4 | 1/8 | full
qty_used
numeric(10,3)
Amount consumed e.g. 1.0 hank, 0.67 hank
billing_type
enum
per_unit | flat_rate | shop_supply
flat_rate_desc
varchar
Customer-facing description if flat rate
flat_rate_amount
numeric(10,2)
Nullable — flat rate bill amount
sort_order
integer
Display order in picker UI
created_at
timestamptz
### Default Bow Hair Templates (System Seeded)
Template Name
Instrument
Qty Used
Typical Flat Rate
Full size violin/viola rehair
violin/viola
1.0 hank
$45-55 (store sets)
Cello bow rehair
cello
0.67 hank
$65-80
Bass bow rehair
bass
0.75 hank
$85-100
3/4 violin rehair
violin
0.75 hank
$35-45
1/2 violin rehair
violin
0.60 hank
$30-40
1/4 violin rehair
violin
0.50 hank
$25-35
1/8 and smaller
violin
0.40 hank
$20-30
## 4.5 repair_part_usage
Records each part consumed in a repair ticket. One record per part type per ticket. Referenced by repair_ticket for invoice generation and cost accounting.
Column
Type
Notes
id
uuid PK
repair_ticket_id
uuid FK
repair_part_id
uuid FK
Nullable for custom flat-rate entries
template_id
uuid FK
Nullable — set if selected from template
qty_used
numeric(10,3)
Actual quantity consumed
unit_of_measure
varchar
Recorded at time of use
unit_cost
numeric(10,4)
Cost at time of use — historical accuracy
total_cost
numeric(10,2)
qty_used * unit_cost
billing_type
enum
per_unit | flat_rate | shop_supply
billed_amount
numeric(10,2)
Amount on invoice — 0 for shop_supply
invoice_description
varchar
Customer-facing line item description
created_at
timestamptz
## 4.6 Bulk Material Examples
Material
Unit
Type
Notes
Bow hair — natural white standard
hank
flat_rate_material
Used via templates by bow size
Bow hair — natural white premium
hank
flat_rate_material
Higher grade — higher flat rate
Bow hair — black
hank
flat_rate_material
Bass/cello preference
Cork sheet — 1mm
sheet
billable
Cut to size for neck corks etc
Cork sheet — 2mm
sheet
billable
Thicker applications
Solder — silver bearing
spool
shop_supply
Overhead — not billed
Pad leather — natural
sheet
billable
Cut to size per pad
Shellac flakes
gram
shop_supply
Overhead
Pad cement
ml
shop_supply
Overhead
Valve oil (bulk)
bottle
shop_supply
Overhead — retail valve oil is separate sale item
Cleaning patches
each
shop_supply
Discrete but overhead
Abrasive paper — 220 grit
sheet
shop_supply
Overhead
Resonators — mylar
sheet
billable
Tone holes — billed per replacement
Fingerboard ebony blank
each
billable
Discrete — billed per blank used
Jewelers rouge
gram
shop_supply
Overhead
# 5. Supplier Management
Suppliers are the vendors from whom the store purchases repair parts and sale inventory. Common music repair suppliers include RS Musical, Ferree's Tools, and Allied Supply. Supplier records enable reorder tracking and purchase order generation.
## 5.1 supplier
Column
Type
Notes
id
uuid PK
company_id
uuid FK
Tenant scoping
name
varchar
e.g. 'RS Musical', 'Ferree’s Tools'
contact_name
varchar
Primary contact
email
varchar
phone
varchar
website
varchar
account_number
varchar
Store’s account number with this supplier
payment_terms
varchar
e.g. Net 30, COD, prepaid
notes
text
is_active
boolean
created_at
timestamptz
# 6. Repair Parts Reporting
- Parts on hand — current stock levels across all repair parts
- Low stock alerts — parts at or below reorder point
- Parts usage by period — which parts consumed most frequently
- Parts cost per repair ticket — material cost breakdown
- Technician material usage — parts consumed per technician
- Shop supply expense by period — overhead cost tracking
- Gross margin per repair — revenue vs labor cost vs parts cost
- Bow hair usage analysis — hanks used per month, yield per hank by bow type
- Reorder suggestions — parts below reorder point with preferred supplier
# 7. Business Rules
- Repair parts never appear in sale inventory search or POS catalog
- Dual-use parts decrement sale inventory qty_on_hand when used in repair
- Bulk qty_on_hand uses numeric(10,3) — supports fractional units down to 0.001
- cost_per_unit uses numeric(10,4) — supports very small per-unit costs for bulk materials
- Usage templates are store-configurable — seeded defaults provided, store can modify
- Shop supply parts reduce qty_on_hand but generate no invoice line item
- Flat-rate billing combines material + labor into one customer-facing line item
- Parts cost always recorded at time of use — price changes do not affect historical records
- Negative qty_on_hand not permitted — system warns technician when stock is insufficient
# 8. Instrument Sizing
String instruments come in fractional sizes — critical for rentals, school orders, and customer matching. Size is tracked on both the product catalog and individual inventory units.
## 8.1 Size Values
Size | Instruments | Notes
4/4 (full) | Violin, viola, cello, bass, guitar | Default adult size
3/4 | Violin, cello, bass, guitar | Older students, smaller adults
1/2 | Violin, cello, bass | Intermediate students
1/4 | Violin, cello | Younger students
1/8 | Violin | Beginner young students
1/10 | Violin | Smallest common size
1/16 | Violin | Rare — very young students
15" | Viola | Measured in inches for viola
15.5" | Viola | Common intermediate
16" | Viola | Full-size standard
16.5" | Viola | Large full-size
## 8.2 Schema Changes
**product** — add column:
Column | Type | Notes
instrument_size | varchar | Nullable — only set for sized instruments. Free text to support both fractional (1/2) and inch (15.5") formats.
**inventory_unit** — add column:
Column | Type | Notes
instrument_size | varchar | Nullable — size of this specific physical unit. May differ from product default (e.g. product "Yamaha Model 5 Violin" has units in multiple sizes).
## 8.3 Business Rules
- Size is optional — only relevant for sized instruments (strings, some guitars)
- Product-level size is the default/catalog size — inventory units can override per-unit
- Size is searchable and filterable in product lists and POS lookup
- Rental matching: when a student needs a size, search filters by instrument_size
- Size changes on rental returns are common (student grew) — logged in rental history
# 9. Inventory Cycle Counts
Physical inventory reconciliation ensures system counts match actual shelf counts. Cycle counts can be full-store or targeted (by category, location area, or supplier).
## 9.1 count_session
Column | Type | Notes
id | uuid PK |
company_id | uuid FK | Tenant scoping
location_id | uuid FK | Which location is being counted
name | varchar | e.g. "Q1 2025 Full Count", "Guitar Room Spot Check"
count_type | enum | full | category | spot
status | enum | draft | in_progress | review | completed | cancelled
category_id | uuid FK | Nullable — set if count_type = category
started_by | uuid FK | Employee who initiated
started_at | timestamptz | When counting began
completed_at | timestamptz | When finalized
notes | text |
created_at | timestamptz |
## 9.2 count_entry
One row per product counted in a session. For serialized products, one row per unit.
Column | Type | Notes
id | uuid PK |
count_session_id | uuid FK |
product_id | uuid FK |
inventory_unit_id | uuid FK | Nullable — set for serialized items
expected_qty | integer | System qty at time count started
counted_qty | integer | Physical count entered by staff
variance | integer | counted_qty - expected_qty (computed)
counted_by | uuid FK | Employee who counted this item
counted_at | timestamptz | When this entry was recorded
notes | text | Explanation for variance
created_at | timestamptz |
## 9.3 count_adjustment
When a count session is completed, variances are applied as inventory adjustments. Each adjustment is an auditable record.
Column | Type | Notes
id | uuid PK |
count_session_id | uuid FK |
count_entry_id | uuid FK |
product_id | uuid FK |
inventory_unit_id | uuid FK | Nullable
previous_qty | integer | qty_on_hand before adjustment
adjusted_qty | integer | qty_on_hand after adjustment
adjustment_reason | enum | cycle_count | damaged | stolen | found | data_entry_error
approved_by | uuid FK | Manager who approved the adjustment
approved_at | timestamptz |
created_at | timestamptz |
## 9.4 Cycle Count Workflow
1. Manager creates count session — selects scope (full, category, or spot check)
2. System snapshots expected quantities for all products in scope
3. Staff count physical inventory — enter counts per product/unit
4. System calculates variances and flags discrepancies
5. Manager reviews variances — adds reason codes for each
6. Manager approves adjustments — qty_on_hand updated, adjustment records created
7. Session marked completed — immutable after completion
## 9.5 Business Rules
- Count sessions lock affected products from sale/receiving while in_progress — prevents count drift
- Variances above a configurable threshold (default: 5% or $50 value) require manager approval
- All adjustments are append-only audit records — never modified after creation
- Serialized items: count confirms unit is present and in expected status
- Completed sessions cannot be reopened — start a new session to re-count
- Spot checks do not lock inventory — used for quick verification without disrupting sales
# 10. Purchase Orders
Formal purchase order workflow extends the existing stock_receipt flow. POs track what was ordered, what was received, and flag discrepancies.
## 10.1 purchase_order
Column | Type | Notes
id | uuid PK |
company_id | uuid FK | Tenant scoping
location_id | uuid FK | Receiving location
po_number | varchar | Human-readable PO number (auto-generated)
supplier_id | uuid FK |
status | enum | draft | submitted | partial | received | cancelled
order_date | date | When PO was sent to supplier
expected_date | date | Expected delivery date
received_date | date | When fully received
subtotal | numeric(10,2) | Sum of line totals
shipping_cost | numeric(10,2) |
tax | numeric(10,2) |
total | numeric(10,2) |
notes | text | Internal notes or special instructions
created_by | uuid FK | Employee who created
created_at | timestamptz |
updated_at | timestamptz |
## 10.2 purchase_order_line
Column | Type | Notes
id | uuid PK |
purchase_order_id | uuid FK |
product_id | uuid FK |
supplier_sku | varchar | Supplier's SKU for this product
description | varchar | Line item description
qty_ordered | integer |
qty_received | integer | Updated as items arrive — default 0
unit_cost | numeric(10,2) | Agreed cost per unit
line_total | numeric(10,2) | qty_ordered * unit_cost
created_at | timestamptz |
## 10.3 Three-Way Match
When receiving against a PO, the system compares:
1. **PO line** — what was ordered (qty_ordered, unit_cost)
2. **Packing slip** — what supplier says they shipped (entered by staff at receiving)
3. **Physical count** — what actually arrived (counted by staff)
Discrepancies flagged: short shipment, over shipment, wrong item, cost mismatch.
## 10.4 PO → Stock Receipt Flow
- Receiving against a PO auto-creates stock_receipt records for each line received
- stock_receipt.purchase_order_id links receipt back to originating PO
- Partial receives update PO status to "partial" — remaining lines stay open
- Final receive updates PO status to "received"
## 10.5 Business Rules
- POs in draft status are editable — submitted POs are locked (create new revision if needed)
- Supplier auto-populated from product's preferred supplier if set
- Unit cost defaults to last stock_receipt cost for that product from that supplier
- PO approval workflow optional — configurable threshold requiring manager sign-off
- Cancelled POs retained for audit — soft cancel with reason code
- Reorder report: products below qty_reorder_point generate suggested PO lines grouped by preferred supplier
# 11. Product Bundles & Kits
Bundles group multiple products into a single sellable item at a package price. Common in music retail: instrument + case + accessories starter packs.
## 11.1 product_bundle
Column | Type | Notes
id | uuid PK |
company_id | uuid FK | Tenant scoping
product_id | uuid FK | The bundle's parent product record (is_bundle = true)
created_at | timestamptz |
## 11.2 product_bundle_item
Column | Type | Notes
id | uuid PK |
bundle_id | uuid FK | References product_bundle
component_product_id | uuid FK | The included product
qty | integer | How many of this component per bundle (default 1)
sort_order | integer | Display order in bundle breakdown
created_at | timestamptz |
## 11.3 Schema Changes
**product** — add column:
Column | Type | Notes
is_bundle | boolean | Default false. True = this product is a bundle of other products.
## 11.4 Pricing Models
Model | Description
fixed | Bundle has a set price — component prices ignored at POS
sum_discount | Bundle price = sum of component prices minus a bundle discount (percent or fixed)
Bundle price is stored on the parent product.price field. The pricing model determines how the discount is displayed on receipts (single line vs itemized with discount).
## 11.5 Inventory Behavior
- Bundle does not have its own qty_on_hand — availability derived from component stock
- Bundle is "in stock" only if ALL components are in stock at required quantities
- Selling a bundle decrements each component product's qty_on_hand (or marks serialized units as sold)
- Stock receipt never targets a bundle directly — components are received individually
- Low stock alert triggers if any component falls below its reorder point
## 11.6 Business Rules
- A bundle component cannot itself be a bundle (no nesting)
- Bundles appear in POS search like any product — clearly labeled as bundle
- Receipt shows bundle name and price, with component breakdown below
- Returning a bundle returns all components — partial bundle returns handled as individual item returns
- Bundle price must be less than or equal to sum of component prices (enforced at creation)
# 12. Barcode Label Printing
Bulk label printing for inventory receiving, repricing, and cycle count preparation.
## 12.1 Label Templates
Template | Use Case | Content
standard_price | Shelf labels | SKU, name, price, barcode (UPC or SKU)
serialized | Individual units | Serial number, SKU, name, barcode (serial)
clearance | Sale items | SKU, name, original price (struck), sale price, barcode
receiving | Incoming stock | SKU, name, price, received date, barcode
bundle | Bundle items | Bundle name, bundle price, component list, barcode
## 12.2 Print Jobs
Label printing is triggered from:
- **Stock receipt** — auto-prompt to print labels for received items
- **Price change** — bulk print updated labels for repriced items
- **Cycle count prep** — print labels for products missing barcodes
- **Manual** — select products from inventory list, choose template, print
## 12.3 Technical
- Labels rendered server-side as PDF (ZPL support planned for thermal printers)
- Standard label sizes: 1.25" x 0.875" (Dymo), 2" x 1" (thermal roll), 4" x 6" (shipping)
- Barcode formats: Code 128 (SKU-based), UPC-A (manufacturer UPC), QR code (serial number)
- Print queue supports batching — up to 500 labels per job
# 13. Backorders
Customer order queue for out-of-stock items. Tracks demand and notifies customers when stock arrives.
## 13.1 backorder
Column | Type | Notes
id | uuid PK |
company_id | uuid FK | Tenant scoping
location_id | uuid FK |
product_id | uuid FK | What was requested
account_id | uuid FK | Who wants it
member_id | uuid FK | Nullable — specific member on account
qty | integer | Quantity requested
status | enum | pending | ordered | received | fulfilled | cancelled
purchase_order_line_id | uuid FK | Nullable — linked to PO when ordered from supplier
deposit_transaction_id | uuid FK | Nullable — if deposit collected
notes | text |
requested_date | date | When customer placed backorder
fulfilled_date | date | When customer received item
created_by | uuid FK | Employee who took the order
created_at | timestamptz |
updated_at | timestamptz |
## 13.2 Workflow
1. Customer wants an out-of-stock product — staff creates backorder
2. Optional: collect deposit via POS (deposit_transaction_id recorded)
3. When creating next PO for that supplier, backorder quantities surfaced as suggested lines
4. Stock receipt against PO triggers backorder match — status updated to "received"
5. Staff notified to contact customer — notification sent via preferred channel
6. Customer picks up item — backorder marked "fulfilled", deposit applied to sale
## 13.3 Business Rules
- Multiple backorders can exist for the same product — filled in request date order (FIFO)
- Backorder quantities included in reorder report alongside reorder point calculations
- Cancelled backorders refund any deposit collected
- Backorder demand report: shows products with pending backorders — informs purchasing decisions
Reference in New Issue View Git Blame Copy Permalink