482 lines
15 KiB
Markdown
482 lines
15 KiB
Markdown
Music Store Management Platform
|
|
|
|
Domain Design: Sales & Point of Sale
|
|
|
|
|
|
|
|
# 1. Overview
|
|
|
|
The Sales & POS domain handles all in-person and counter transactions — retail sales, deposits, repair payments, rental fees, and account charges. It integrates with Stripe Terminal for card-present payments and Stripe Elements for keyed entry.
|
|
|
|
|
|
|
|
# 2. Transaction Types
|
|
|
|
Type
|
|
|
|
Description
|
|
|
|
sale
|
|
|
|
Retail sale of product — instruments, accessories, supplies
|
|
|
|
repair_payment
|
|
|
|
Payment collected at repair ticket pickup
|
|
|
|
rental_deposit
|
|
|
|
Security deposit collected at rental start
|
|
|
|
account_payment
|
|
|
|
Payment against outstanding account balance
|
|
|
|
refund
|
|
|
|
Return of payment — full or partial
|
|
|
|
|
|
|
|
# 3. Database Schema
|
|
|
|
## 3.1 transaction
|
|
|
|
Column
|
|
|
|
Type
|
|
|
|
Notes
|
|
|
|
id
|
|
|
|
uuid PK
|
|
|
|
|
|
|
|
company_id
|
|
|
|
uuid FK
|
|
|
|
Tenant scoping
|
|
|
|
location_id
|
|
|
|
uuid FK
|
|
|
|
Physical location where transaction occurred
|
|
|
|
transaction_number
|
|
|
|
varchar
|
|
|
|
Human-readable ID
|
|
|
|
account_id
|
|
|
|
uuid FK
|
|
|
|
Nullable for anonymous cash sales
|
|
|
|
transaction_type
|
|
|
|
enum
|
|
|
|
sale|repair_payment|rental_deposit|account_payment|refund
|
|
|
|
status
|
|
|
|
enum
|
|
|
|
pending|completed|voided|refunded
|
|
|
|
subtotal
|
|
|
|
numeric(10,2)
|
|
|
|
Before tax and discounts
|
|
|
|
discount_total
|
|
|
|
numeric(10,2)
|
|
|
|
Total discounts applied
|
|
|
|
tax_total
|
|
|
|
numeric(10,2)
|
|
|
|
|
|
|
|
total
|
|
|
|
numeric(10,2)
|
|
|
|
Final amount charged
|
|
|
|
payment_method
|
|
|
|
enum
|
|
|
|
card_present|card_keyed|cash|check|account_charge
|
|
|
|
stripe_payment_intent_id
|
|
|
|
varchar
|
|
|
|
For card transactions
|
|
|
|
stripe_terminal_reader_id
|
|
|
|
varchar
|
|
|
|
Which reader was used
|
|
|
|
processed_by
|
|
|
|
uuid FK
|
|
|
|
Employee who processed
|
|
|
|
notes
|
|
|
|
text
|
|
|
|
|
|
|
|
created_at
|
|
|
|
timestamptz
|
|
|
|
|
|
|
|
|
|
|
|
## 3.2 transaction_line_item
|
|
|
|
id, transaction_id, product_id (nullable), inventory_unit_id (nullable),description, qty, unit_price, discount_amount, discount_reason,tax_rate, line_total, created_at
|
|
|
|
|
|
|
|
## 3.3 discount
|
|
|
|
id, company_id, location_id, name, discount_type (percent|fixed),discount_value, applies_to (order|line_item|category),requires_approval_above (threshold amount), is_active,valid_from, valid_until, created_at
|
|
|
|
|
|
|
|
## 3.4 discount_audit
|
|
|
|
id, transaction_id, transaction_line_item_id, discount_id,applied_by (employee_id), approved_by (employee_id, nullable),original_amount, discounted_amount, reason, created_at
|
|
|
|
|
|
|
|
# 4. Discount & Adjustment Rules
|
|
|
|
- Discounts are applied at line item or order level
|
|
|
|
- Discounts exceeding manager threshold require approval — logged in discount_audit
|
|
|
|
- Manual price overrides treated as discounts — require reason code
|
|
|
|
- Promotional codes validated against discount table before application
|
|
|
|
- All discounts appear as line items on invoice — original price shown
|
|
|
|
- Discount audit records are immutable — no deletion permitted
|
|
|
|
|
|
|
|
# 5. Payment Methods
|
|
|
|
Method
|
|
|
|
Integration
|
|
|
|
Notes
|
|
|
|
Card present
|
|
|
|
Stripe Terminal WiFi
|
|
|
|
Desktop — WisePOS E reader
|
|
|
|
Card present (BT)
|
|
|
|
Stripe Terminal BT
|
|
|
|
iOS — Bluetooth reader at conventions
|
|
|
|
Card keyed
|
|
|
|
Stripe Elements
|
|
|
|
Desktop — PCI safe, card data never touches app
|
|
|
|
Cash
|
|
|
|
Manual entry
|
|
|
|
Change calculated, drawer tracked
|
|
|
|
Check
|
|
|
|
Manual entry
|
|
|
|
Check number recorded
|
|
|
|
Account charge
|
|
|
|
Internal
|
|
|
|
Charges to account balance — billed later
|
|
|
|
|
|
|
|
# 6. Trade-In Workflow
|
|
|
|
Trade-ins are a core music retail workflow. A customer brings in a used instrument and receives credit toward a purchase. The trade-in creates both an inventory intake (new used product) and a credit applied to a transaction.
|
|
|
|
## 6.1 trade_in
|
|
|
|
Column | Type | Notes
|
|
id | uuid PK |
|
|
company_id | uuid FK | Tenant scoping
|
|
location_id | uuid FK |
|
|
account_id | uuid FK | Nullable — walk-in trade-ins allowed
|
|
appraised_by | uuid FK | Employee who appraised
|
|
instrument_description | varchar | What the customer is trading in
|
|
serial_number | varchar | Nullable
|
|
condition | enum | excellent | good | fair | poor
|
|
appraisal_value | numeric(10,2) | What store offers the customer
|
|
credit_applied | numeric(10,2) | Amount applied to transaction (may equal appraisal or less if partial)
|
|
transaction_id | uuid FK | Nullable — the sale transaction where credit was applied
|
|
product_id | uuid FK | Nullable — the product record created for the traded-in item
|
|
inventory_unit_id | uuid FK | Nullable — the inventory unit created (if serialized)
|
|
status | enum | appraised | accepted | declined | sold
|
|
notes | text | Condition details, customer negotiation notes
|
|
appraised_at | timestamptz |
|
|
created_at | timestamptz |
|
|
|
|
## 6.2 Trade-In Flow
|
|
|
|
1. Customer brings instrument — staff appraises condition and fair market value
|
|
2. Staff enters trade-in: description, serial, condition, appraisal value
|
|
3. Customer accepts or declines the offer
|
|
4. If accepted:
|
|
a. New product/inventory_unit created for the traded-in instrument (used, condition noted)
|
|
b. Trade-in credit applied to the current or future transaction
|
|
c. Transaction shows trade-in credit as a line item (negative amount)
|
|
5. Traded-in instrument enters inventory as available for resale
|
|
|
|
## 6.3 Business Rules
|
|
|
|
- Appraisal value is a negotiation — staff can adjust within min/max guidelines
|
|
- Trade-in credit can exceed purchase price — difference paid as store credit (not cash refund)
|
|
- Trade-in without immediate purchase: credit stored on account as store credit balance
|
|
- Traded-in items default to "used" condition and get a new product record (not merged with new inventory)
|
|
- Trade-in appraisal records are immutable — if value changes, create new appraisal
|
|
- Trade-in value reported separately from sales discount for accounting purposes
|
|
- Manager approval required for trade-in value above configurable threshold
|
|
|
|
|
|
|
|
# 7. Returns & Exchanges
|
|
|
|
Structured returns workflow beyond simple refund transactions. Handles RMA tracking, reason codes, restocking, and return window enforcement.
|
|
|
|
## 7.1 return_request
|
|
|
|
Column | Type | Notes
|
|
id | uuid PK |
|
|
company_id | uuid FK | Tenant scoping
|
|
location_id | uuid FK |
|
|
return_number | varchar | Human-readable RMA number (auto-generated)
|
|
original_transaction_id | uuid FK | The sale being returned against
|
|
account_id | uuid FK | Nullable — anonymous returns allowed with receipt
|
|
status | enum | pending | approved | completed | denied
|
|
return_type | enum | refund | exchange | store_credit
|
|
initiated_by | uuid FK | Employee processing return
|
|
approved_by | uuid FK | Nullable — manager if approval required
|
|
reason_code | enum | defective | wrong_item | changed_mind | duplicate | other
|
|
reason_detail | text | Free-text explanation
|
|
restocking_fee | numeric(10,2) | Default 0 — configurable per category
|
|
refund_amount | numeric(10,2) | Amount returned to customer (after restocking fee)
|
|
refund_method | enum | original_payment | cash | store_credit
|
|
refund_transaction_id | uuid FK | The refund transaction created
|
|
created_at | timestamptz |
|
|
updated_at | timestamptz |
|
|
|
|
## 7.2 return_line_item
|
|
|
|
Column | Type | Notes
|
|
id | uuid PK |
|
|
return_request_id | uuid FK |
|
|
original_line_item_id | uuid FK | Line item from original transaction
|
|
product_id | uuid FK |
|
|
inventory_unit_id | uuid FK | Nullable — for serialized items
|
|
qty_returned | integer |
|
|
unit_price | numeric(10,2) | Price at time of original sale
|
|
condition_returned | enum | new | excellent | good | fair | poor | defective
|
|
restock | boolean | Whether item goes back to available inventory
|
|
created_at | timestamptz |
|
|
|
|
## 7.3 Return Policies
|
|
|
|
return_policy — configurable per company, overridable per category:
|
|
|
|
Column | Type | Notes
|
|
id | uuid PK |
|
|
company_id | uuid FK |
|
|
category_id | uuid FK | Nullable — null = company-wide default
|
|
return_window_days | integer | Days after purchase returns are accepted (e.g. 30)
|
|
restocking_fee_percent | numeric(5,2) | Default restocking fee (e.g. 15%)
|
|
requires_receipt | boolean | Whether original receipt/transaction required
|
|
requires_approval | boolean | Whether manager approval needed
|
|
exchange_only | boolean | Some categories only allow exchange, not refund
|
|
is_active | boolean |
|
|
created_at | timestamptz |
|
|
|
|
## 7.4 Return Workflow
|
|
|
|
1. Customer requests return — staff looks up original transaction
|
|
2. System checks return window — warns if outside policy (manager can override)
|
|
3. Staff selects items being returned, enters reason code and condition
|
|
4. System calculates refund amount (original price minus restocking fee if applicable)
|
|
5. Manager approves if required by policy or amount threshold
|
|
6. Refund processed: original payment method, cash, or store credit
|
|
7. Returned items: restocked (condition updated) or marked defective (removed from available)
|
|
|
|
## 7.5 Exchange Flow
|
|
|
|
1. Return initiated with return_type = exchange
|
|
2. Customer selects replacement item(s)
|
|
3. System calculates difference — customer pays overage or receives credit for underage
|
|
4. Single transaction captures both the return credit and the new sale
|
|
5. Both the return and new sale are linked for reporting
|
|
|
|
## 7.6 Business Rules
|
|
|
|
- Return window enforced at POS — staff sees clear "within policy" / "outside policy" indicator
|
|
- Outside-policy returns require manager override with reason
|
|
- Defective items never restocked — routed to repair assessment or disposal
|
|
- Serialized items: inventory unit status reverted from "sold" to "available" on restock
|
|
- Non-serialized items: qty_on_hand incremented on restock
|
|
- Refund to original payment method preferred — Stripe refund API called for card transactions
|
|
- Store credit refunds create a credit balance on the account
|
|
- No cash refunds above configurable threshold without manager approval
|
|
- Return fraud tracking: system flags accounts with high return frequency
|
|
|
|
|
|
|
|
# 8. Tax Exemptions at POS
|
|
|
|
Tax-exempt customers (schools, churches, resellers) are common in music retail. Tax exemption is checked at transaction time and the exemption is recorded on each qualifying transaction.
|
|
|
|
## 8.1 Transaction Tax Flow
|
|
|
|
1. Transaction started — account attached (or anonymous)
|
|
2. System checks account.tax_exempt_status:
|
|
- If "approved": tax automatically set to $0.00 on all eligible lines
|
|
- If "pending" or "none": standard tax calculation applies
|
|
3. Tax exemption displayed on POS screen — staff sees "TAX EXEMPT" indicator
|
|
4. Receipt and invoice show $0.00 tax with exemption reference number
|
|
5. Tax-exempt transactions flagged in reporting for audit compliance
|
|
|
|
## 8.2 Manual Override
|
|
|
|
- Staff can manually mark a transaction as tax-exempt (e.g. walk-in school purchaser with certificate)
|
|
- Manual exemptions require: certificate number entry and manager approval
|
|
- Manual exemptions logged in tax_exemption_audit for compliance
|
|
|
|
## 8.3 Business Rules
|
|
|
|
- Tax exemption applies to the entire transaction, not individual line items
|
|
- Resale certificates have expiration dates — system warns when nearing expiry
|
|
- Expired certificates block auto-exemption until renewed — staff can override with manager approval
|
|
- Tax-exempt transaction count and value reported monthly for state compliance
|
|
- Rental transactions for schools are tax-exempt if the school account is exempt
|
|
|
|
|
|
|
|
# 9. In-Home Trials
|
|
|
|
In-home trials let a customer take an instrument home to try before committing to a purchase. Common for higher-value instruments (guitars, violins, horns) where feel, sound, and fit matter. The instrument leaves the store, so precise inventory tracking is critical.
|
|
|
|
## 9.1 instrument_trial
|
|
|
|
Column | Type | Notes
|
|
id | uuid PK |
|
|
company_id | uuid FK | Tenant scoping
|
|
location_id | uuid FK | Originating location
|
|
account_id | uuid FK | Customer taking the trial
|
|
member_id | uuid FK | Nullable — specific member
|
|
inventory_unit_id | uuid FK | The specific instrument being trialed
|
|
trial_number | varchar | Human-readable ID (auto-generated)
|
|
status | enum | active | returned | converted | overdue | cancelled
|
|
checkout_date | date | When instrument left the store
|
|
due_date | date | When instrument must be returned
|
|
returned_date | date | Nullable — when actually returned
|
|
duration_days | integer | Trial length (default configurable, e.g. 3-7 days)
|
|
deposit_amount | numeric(10,2) | Nullable — optional hold or deposit collected
|
|
deposit_transaction_id | uuid FK | Nullable — POS transaction for deposit
|
|
condition_out | enum | new | excellent | good | fair | poor — condition at checkout
|
|
condition_in | enum | Nullable — condition at return
|
|
checked_out_by | uuid FK | Employee who processed checkout
|
|
checked_in_by | uuid FK | Nullable — employee who processed return
|
|
sale_transaction_id | uuid FK | Nullable — if trial converted to sale
|
|
notes | text |
|
|
created_at | timestamptz |
|
|
updated_at | timestamptz |
|
|
|
|
## 9.2 Inventory Unit Status
|
|
|
|
**inventory_unit.status** — add value:
|
|
|
|
Value | Notes
|
|
on_trial | Instrument is out with a customer on an in-home trial. Not available for sale, rental, or display.
|
|
|
|
This status ensures the instrument is accounted for in all inventory views. Staff can see exactly which instruments are out on trial, with whom, and when they're due back.
|
|
|
|
## 9.3 Trial Workflow
|
|
|
|
1. Customer interested in an instrument — staff creates trial, selects inventory unit
|
|
2. Condition documented at checkout (condition_out)
|
|
3. Optional: deposit collected via POS (hold against card or cash deposit)
|
|
4. inventory_unit.status set to "on_trial"
|
|
5. Customer takes instrument home
|
|
6. Daily job checks for overdue trials — flags on dashboard, notifies staff
|
|
7. Customer returns instrument:
|
|
a. Condition assessed at return (condition_in)
|
|
b. inventory_unit.status reverted to "available" (or "in_repair" if damaged)
|
|
c. Deposit refunded if collected
|
|
8. **Conversion**: customer decides to buy — trial marked "converted", sale transaction created, deposit applied as payment
|
|
|
|
## 9.4 Multiple Trials
|
|
|
|
- A customer may trial multiple instruments simultaneously (e.g. comparing two violins)
|
|
- Each instrument gets its own trial record
|
|
- Staff can see all active trials per customer on the account view
|
|
- Common scenario: customer takes two, returns one, buys the other
|
|
|
|
## 9.5 Overdue Handling
|
|
|
|
- Trial due_date is firm — system escalates overdue trials:
|
|
- Day of due date: reminder notification sent to customer
|
|
- 1 day overdue: staff alerted on dashboard
|
|
- 3 days overdue: manager notified, customer contacted directly
|
|
- 7+ days overdue: escalation to store owner, deposit may be charged
|
|
- Overdue trials appear prominently on the daily operations dashboard
|
|
- If deposit was collected, store can charge the deposit after configurable overdue period
|
|
|
|
## 9.6 Business Rules
|
|
|
|
- Trial requires an account — no anonymous trials (need contact info for follow-up and liability)
|
|
- Only serialized inventory units can go on trial (need serial number tracking)
|
|
- Instrument must be in "available" status to start a trial
|
|
- Maximum concurrent trials per account configurable (default: 3)
|
|
- Maximum trial duration configurable per company (default: 7 days, max: 30 days)
|
|
- Condition documented at checkout and return — discrepancies flagged for review
|
|
- Deposit optional but recommended for high-value instruments — threshold configurable
|
|
- Trial history visible on both the account record and the inventory unit record
|
|
- Conversion rate report: trials started vs converted to sale, by instrument category and employee
|
|
- Lost instrument on trial: if not returned and unresolved, inventory_unit.status set to "lost", deposit charged, account flagged |