# Sales & Inventory — Full Expansion Master Plan

> **Analyzed:** 2026-05-03
> **System:** Laravel 12 ERP — Multi-branch, Finance module operational

---

## Table of Contents

1. [Current Gap Analysis](#1-current-gap-analysis)
2. [Full Sales Module Design](#2-full-sales-module-design)
3. [Full Inventory Module Design](#3-full-inventory-module-design)
4. [Finance Integration Design](#4-finance-integration-design)
5. [MVP vs Advanced Features](#5-mvp-vs-advanced-features)
6. [Complete Table Reference](#6-complete-table-reference)
7. [Data Flow Diagrams](#7-data-flow-diagrams)

---

## 1. Current Gap Analysis

### What Exists Today

| Module | What's Built | Status |
|--------|-------------|--------|
| Customer | code, name, phone, email, address, tax_number, branch | Missing credit_limit, payment_terms, AR account, balance |
| SalesOrder | order_number, date, customer, lines (item, qty, price) | Missing discounts, tax per line, totals, status machine |
| Invoice | invoice_number, date, customer, order_ref, lines, tax | Missing due_date, payments, payment_status, posting |
| Item | code, name, type (stock/service), unit | Missing price, cost, reorder, category, accounts |
| Warehouse | code, name, branch | Complete for basic use |
| StockMovement | in/out/adjustment, qty, cost, reference | Missing stock balance table, valuation, posting |

### Critical Missing Pieces

**Sales — Why It Fails in Production:**
- No payment tracking → cannot know what customer owes
- No credit limit enforcement → unlimited exposure
- No discounts per line → cannot serve real pricing structures
- Tax is header-level → wrong for mixed-tax invoices
- No returns → cannot handle rejected goods or refunds
- No due date → cannot manage aging/collections
- No journal entries → sales are invisible to accounting

**Inventory — Why It Fails in Production:**
- No stock balance table → cannot know current stock level without scanning all movements
- No purchase flow → stock can only be entered manually
- No cost tracking per item → cannot calculate COGS
- No suppliers → cannot track who you bought from
- No reorder alerts → cannot prevent stockouts
- No finance posting → inventory moves are invisible to accounting

**The Business Impact:**
A company using this system today would have:
- No idea what customers owe them
- No idea what their actual stock value is
- Finance books not reflecting any sales or purchases
- No way to prevent over-selling stock they don't have

---

## 2. Full Sales Module Design

### 2.1 Complete Sales Flow

```
QUOTATION (optional)
    │  customer accepts
    ▼
SALES ORDER
    │  create invoice(s) — can be partial
    ▼
INVOICE(S)  ────────────────────────────────────┐
    │                                            │
    │ post to finance                       RETURN / CREDIT NOTE
    ▼                                            │
JOURNAL ENTRY (AR + Revenue + Tax)         reverse journal
    │
    │ customer pays
    ▼
PAYMENT
    │ post to finance
    ▼
JOURNAL ENTRY (Cash/Bank DR, AR CR)
```

### 2.2 Customer Expansion

**New fields on `customers` table:**

| Field | Type | Purpose |
|-------|------|---------|
| credit_limit | decimal(18,2) default 0 | Maximum allowed outstanding balance. 0 = unlimited |
| payment_terms_days | int default 30 | Days until invoice is due |
| ar_account_id | FK → accounts (nullable) | Customer-specific AR account. Falls back to system default |
| opening_balance | decimal(18,2) default 0 | Balance carried from previous system |
| opening_balance_date | date nullable | Date of opening balance |
| currency_code | varchar(3) default 'USD' | Default invoice currency |

**Credit Limit Logic:**
```
current_balance = opening_balance + sum(invoices.total_amount) - sum(payments.amount)
available_credit = credit_limit - current_balance
if credit_limit > 0 AND new_invoice_total > available_credit → block or warn
```

### 2.3 Quotation

**Purpose:** Send a formal price offer to customer before they confirm. Optional in flow.

**Status machine:** `draft → sent → accepted → rejected → expired`

**Key logic:**
- Expiry date enforced — cannot accept expired quotation
- Accepted quotation can be converted to Sales Order (one-click)
- Converting copies all lines into Sales Order

### 2.4 Sales Order Expansion

**New fields on `sales_orders` table:**

| Field | Type | Purpose |
|-------|------|---------|
| quotation_id | FK nullable | Source quotation if converted |
| discount_type | enum(percent, amount) | Header-level discount type |
| discount_value | decimal(18,2) default 0 | Header discount value |
| discount_amount | decimal(18,2) default 0 | Calculated discount |
| subtotal | decimal(18,2) default 0 | Sum of lines before discount/tax |
| tax_amount | decimal(18,2) default 0 | Total tax |
| total_amount | decimal(18,2) default 0 | Final total |
| delivery_date | date nullable | Expected delivery |
| confirmed_at | timestamp nullable | When order was confirmed |
| invoiced_amount | decimal(18,2) default 0 | How much has been invoiced |
| delivered_qty_tracked | boolean default false | Whether delivery is tracked |

**New fields on `sales_order_lines` table:**

| Field | Type | Purpose |
|-------|------|---------|
| discount_percent | decimal(5,2) default 0 | Line discount % |
| discount_amount | decimal(18,2) default 0 | Calculated line discount |
| tax_rate_id | FK nullable | Applied tax rate |
| tax_amount | decimal(18,2) default 0 | Calculated line tax |
| line_subtotal | decimal(18,2) | qty × price before discount/tax |
| invoiced_quantity | decimal(18,2) default 0 | How much already invoiced |

**Status machine:** `draft → confirmed → partially_invoiced → invoiced → cancelled`

### 2.5 Invoice Expansion

**New fields on `invoices` table:**

| Field | Type | Purpose |
|-------|------|---------|
| due_date | date nullable | Payment due date (from payment_terms) |
| discount_amount | decimal(18,2) default 0 | Header discount |
| paid_amount | decimal(18,2) default 0 | Sum of payments received |
| balance_amount | decimal(18,2) default 0 | total_amount - paid_amount |
| payment_status | enum | unpaid / partial / paid / overdue |
| journal_entry_id | FK nullable | Posted journal entry |
| posted_at | timestamp nullable | When posted to finance |
| delivery_note_ref | varchar nullable | External delivery reference |

**New fields on `invoice_lines` table:**

| Field | Type | Purpose |
|-------|------|---------|
| discount_percent | decimal(5,2) default 0 | Line discount |
| discount_amount | decimal(18,2) default 0 | Calculated discount |
| tax_rate_id | FK → sales_tax_rates (nullable) | Tax applied |
| tax_amount | decimal(18,2) default 0 | Calculated tax |
| line_subtotal | decimal(18,2) | qty × price |
| warehouse_id | FK nullable | Source warehouse for stock deduction |
| cogs_amount | decimal(18,2) default 0 | COGS value (average cost × qty) |

**Status machine:** `draft → confirmed → posted → partially_paid → paid → cancelled`

**Partial Invoicing Logic:**
- Multiple invoices can reference one Sales Order
- Each invoice covers a subset of the order's lines
- `sales_orders.invoiced_amount` tracks total invoiced
- Order status becomes `partially_invoiced` or `invoiced` accordingly

### 2.6 Payments & Collections

**New table: `sales_payments`**

| Field | Type | Notes |
|-------|------|-------|
| payment_number | varchar unique | Auto-generated |
| payment_date | date | |
| customer_id | FK | |
| invoice_id | FK | One payment per invoice |
| amount | decimal(18,2) | |
| payment_method_id | FK → finance_payment_methods | Cash/bank/cheque |
| bank_reference | varchar nullable | Cheque/transfer ref |
| branch_id | FK | |
| journal_entry_id | FK nullable | Posted journal entry |
| posted_at | timestamp nullable | |
| status | enum | draft / posted / reversed |
| notes | text nullable | |
| created_by, updated_by | FK | |

**Payment Logic:**
1. Payment created in draft
2. Posting payment: creates journal entry (DR Cash/Bank, CR AR)
3. Updates `invoices.paid_amount` and `balance_amount`
4. Updates `invoices.payment_status` (partial / paid)
5. One invoice can receive multiple payments (installments)
6. Reversed payment: reverse journal + update invoice balance

### 2.7 Returns & Credit Notes

**New table: `sales_returns`**

| Field | Type | Notes |
|-------|------|-------|
| return_number | varchar unique | Auto-generated |
| return_date | date | |
| customer_id | FK | |
| original_invoice_id | FK | Invoice being returned against |
| branch_id | FK | |
| warehouse_id | FK nullable | Where returned stock goes |
| reason | text | |
| subtotal | decimal(18,2) | |
| tax_amount | decimal(18,2) | |
| total_amount | decimal(18,2) | |
| restocked | boolean default false | Whether items returned to stock |
| journal_entry_id | FK nullable | |
| posted_at | timestamp nullable | |
| status | enum | draft / posted / refunded |
| notes | json | |

**New table: `sales_return_lines`**

| Field | Type | Notes |
|-------|------|-------|
| sales_return_id | FK | |
| original_invoice_line_id | FK nullable | Source invoice line |
| item_id | FK | |
| quantity | decimal(18,2) | |
| unit_price | decimal(18,2) | |
| tax_amount | decimal(18,2) | |
| line_total | decimal(18,2) | |
| restocked | boolean | Item-level restock flag |

**Return Journal Entry (when posted):**
```
DR: Revenue Account     (original revenue reversal)
DR: Tax Payable         (tax reversal)
CR: Accounts Receivable (reduce what customer owes)

If restocked:
DR: Inventory Account   (stock comes back at original cost)
CR: COGS Account        (reverse COGS)
```

---

## 3. Full Inventory Module Design

### 3.1 Complete Inventory Flow

```
SUPPLIER
    │
    ▼
PURCHASE ORDER (PO)
    │  receive goods
    ▼
PURCHASE RECEIPT (GRN)
    │
    ├─► STOCK BALANCE UPDATE (item + warehouse quantity)
    │
    ├─► AVERAGE COST RECALCULATION
    │
    └─► JOURNAL ENTRY (DR Inventory, CR AP)

        STOCK IN HAND
            │
            ├─► SALES INVOICE LINE (stock out)
            │       └─► COGS journal entry
            │
            ├─► INTER-WAREHOUSE TRANSFER
            │
            └─► MANUAL ADJUSTMENT
```

### 3.2 Item Expansion

**New fields on `items` table:**

| Field | Type | Purpose |
|-------|------|---------|
| category_id | FK → item_categories | Grouping for reports |
| uom_id | FK → units_of_measure | Unit of measure |
| purchase_price | decimal(18,2) default 0 | Default purchase price |
| sale_price | decimal(18,2) default 0 | Default sale price |
| reorder_level | decimal(18,2) default 0 | Alert when stock drops below this |
| reorder_qty | decimal(18,2) default 0 | Suggested reorder quantity |
| cost_method | enum(average, fifo) default average | Valuation method |
| average_cost | decimal(18,6) default 0 | Running average cost |
| inventory_account_id | FK → accounts (nullable) | DR when stock received |
| cogs_account_id | FK → accounts (nullable) | DR when stock sold |
| revenue_account_id | FK → accounts (nullable) | CR when invoice posted |
| allow_negative_stock | boolean default false | Safety flag |

### 3.3 Supplier Master

**New table: `suppliers`**

| Field | Type | Notes |
|-------|------|-------|
| code | varchar unique | |
| name | json (translatable) | |
| phone, email | varchar nullable | |
| address | json nullable | |
| tax_number | varchar nullable | |
| credit_limit | decimal(18,2) default 0 | |
| payment_terms_days | int default 30 | |
| ap_account_id | FK → accounts (nullable) | Accounts Payable account |
| branch_id | FK | |
| is_active | boolean default true | |
| opening_balance | decimal(18,2) default 0 | |
| created_by, updated_by | FK | |

### 3.4 Purchase Orders

**New table: `purchase_orders`**

| Field | Type | Notes |
|-------|------|-------|
| po_number | varchar unique | Auto-generated |
| po_date | date | |
| expected_date | date nullable | Expected delivery date |
| supplier_id | FK | |
| branch_id | FK | |
| discount_amount | decimal(18,2) default 0 | |
| tax_amount | decimal(18,2) default 0 | |
| subtotal | decimal(18,2) default 0 | |
| total_amount | decimal(18,2) default 0 | |
| received_amount | decimal(18,2) default 0 | How much received |
| status | enum | draft/confirmed/partially_received/received/cancelled |
| notes | json | |
| created_by, updated_by | FK | |

**New table: `purchase_order_lines`**

| Field | Type | Notes |
|-------|------|-------|
| purchase_order_id | FK cascade | |
| item_id | FK | |
| description | json | |
| quantity | decimal(18,2) | Ordered qty |
| unit_cost | decimal(18,2) | Agreed price |
| discount_percent | decimal(5,2) default 0 | |
| discount_amount | decimal(18,2) default 0 | |
| tax_rate_id | FK nullable | |
| tax_amount | decimal(18,2) default 0 | |
| line_subtotal | decimal(18,2) | |
| line_total | decimal(18,2) | |
| received_quantity | decimal(18,2) default 0 | How much received so far |

### 3.5 Purchase Receipts (GRN — Goods Receipt Note)

**New table: `purchase_receipts`**

| Field | Type | Notes |
|-------|------|-------|
| receipt_number | varchar unique | Auto-generated |
| receipt_date | date | |
| purchase_order_id | FK nullable | May receive without PO |
| supplier_id | FK | |
| warehouse_id | FK | Where stock goes |
| branch_id | FK | |
| total_cost | decimal(18,2) default 0 | |
| journal_entry_id | FK nullable | |
| posted_at | timestamp nullable | |
| status | enum | draft / posted / reversed |
| notes | json | |
| created_by, updated_by | FK | |

**New table: `purchase_receipt_lines`**

| Field | Type | Notes |
|-------|------|-------|
| purchase_receipt_id | FK cascade | |
| purchase_order_line_id | FK nullable | Link back to PO line |
| item_id | FK | |
| quantity | decimal(18,2) | Received qty |
| unit_cost | decimal(18,2) | Actual cost (may differ from PO) |
| total_cost | decimal(18,2) | qty × unit_cost |

**On Receipt Posting:**
1. Each line creates a StockMovement (type=in) referencing the receipt
2. Stock balance updated per item per warehouse
3. Average cost recalculated per item
4. Journal entry created: DR Inventory, CR AP

### 3.6 Stock Balance Engine

**New table: `stock_balances`** (the current stock ledger)

| Field | Type | Notes |
|-------|------|-------|
| item_id | FK | UNIQUE with warehouse_id |
| warehouse_id | FK | |
| quantity | decimal(18,2) default 0 | Current quantity |
| average_cost | decimal(18,6) default 0 | Current average cost |
| total_value | decimal(18,2) default 0 | quantity × average_cost |
| last_movement_at | timestamp nullable | |

**UNIQUE constraint:** `(item_id, warehouse_id)`

**Update Logic (called on every StockMovement):**
```
Stock IN:
  new_average_cost = (old_qty × old_avg + new_qty × new_cost) / (old_qty + new_qty)
  new_quantity = old_qty + new_qty
  new_total_value = new_quantity × new_average_cost

Stock OUT (sale/transfer):
  if quantity > current AND NOT allow_negative_stock → throw exception
  new_quantity = old_qty - out_qty
  new_total_value = new_quantity × current_average_cost
  cogs_amount = out_qty × current_average_cost  ← used for journal entry

Transfer:
  Source warehouse: stock out logic
  Destination warehouse: stock in at same average_cost (no cost change)

Adjustment (positive):
  Same as stock IN logic
Adjustment (negative):
  Same as stock OUT logic
```

### 3.7 Stock Valuation — Average Cost Method

**Why Average Cost (not FIFO) for MVP:**
- Simpler to implement and maintain
- Works for most SMB scenarios
- No need to track individual cost layers
- Can upgrade to FIFO later

**Average Cost Formula:**
```
New Average Cost = (Existing Value + Received Value) / (Existing Qty + Received Qty)

Example:
  Before: 100 units @ 10.00 = 1,000
  Receive: 50 units @ 12.00 = 600
  New Average: (1,000 + 600) / (100 + 50) = 1,600 / 150 = 10.6667
```

**When stock goes out:** always at current average cost (not new delivery price).

### 3.8 Reorder Alerts

- `items.reorder_level` = minimum quantity before alert fires
- Checked after every stock-out movement
- Alert stored in `notifications` table (already exists)
- Dashboard widget: "Items Below Reorder Level"
- Query: `stock_balances.quantity <= items.reorder_level WHERE items.type = 'stock'`

---

## 4. Finance Integration Design

### 4.1 Sales Invoice → Journal Entry

**Trigger:** When invoice status changes to `posted`

**Journal Entry:**

```
Invoice: Customer A — 1,000 + 140 tax = 1,140

DR  Accounts Receivable (customer AR account)  1,140
    CR  Revenue Account (per item's revenue_account_id)  1,000
    CR  Tax Payable Account                               140
```

**With discount:**
```
Invoice: 1,200 − 200 discount + 140 tax = 1,140

DR  Accounts Receivable                        1,140
DR  Sales Discount Account                       200
    CR  Revenue Account                         1,200
    CR  Tax Payable                               140
```

**Multi-line, multi-tax invoice:** one journal entry, one line per account bucket.

### 4.2 Sales Payment → Journal Entry

**Trigger:** When payment status changes to `posted`

```
Payment: Customer A pays 500 by bank transfer

DR  Bank Account (from payment_method.account_id)  500
    CR  Accounts Receivable                          500
```

### 4.3 Sales Return → Journal Entry

```
Return: Customer returns 200 worth of goods

DR  Revenue Account          200
DR  Tax Payable               28  (tax reversal)
    CR  Accounts Receivable       228

If restocked:
DR  Inventory Account        120  (at original COGS cost)
    CR  COGS Account              120
```

### 4.4 Purchase Receipt → Journal Entry

**Trigger:** When receipt status changes to `posted`

```
Receive: 50 units of Item X at cost 12.00 each = 600

DR  Inventory Account (item.inventory_account_id)  600
    CR  Accounts Payable (supplier.ap_account_id)   600
```

### 4.5 Stock Out on Sale (COGS Entry)

**Trigger:** When invoice is posted AND item is type=stock

```
COGS: 50 units sold, average cost 10.667 = 533.33

DR  COGS Account (item.cogs_account_id)   533.33
    CR  Inventory Account                  533.33
```

**This entry is created alongside the sales revenue entry — same journal, separate lines.**

### 4.6 Account Resolution Priority

```
For AR account:
  1. customer.ar_account_id (customer-specific)
  2. system setting: default_ar_account_id
  3. Error if neither set

For Revenue account (per line):
  1. item.revenue_account_id
  2. item.category.revenue_account_id
  3. system setting: default_revenue_account_id
  4. Error if none

For COGS account (per line):
  1. item.cogs_account_id
  2. item.category.cogs_account_id
  3. system setting: default_cogs_account_id

For Inventory account (per line):
  1. item.inventory_account_id
  2. item.category.inventory_account_id
  3. system setting: default_inventory_account_id

For AP account:
  1. supplier.ap_account_id
  2. system setting: default_ap_account_id
```

---

## 5. MVP vs Advanced Features

### MUST HAVE (MVP)

**Sales:**
- [x] Customer with credit limit and payment terms
- [x] Sales Order with discounts and line-level tax
- [x] Invoice with due date and payment status
- [x] Payments (cash and bank)
- [x] Invoice → Journal Entry posting
- [x] Payment → Journal Entry posting
- [x] Customer balance tracking

**Inventory:**
- [x] Item with cost, sale price, reorder level
- [x] Supplier master
- [x] Purchase Receipt (with or without PO)
- [x] Stock Balance table (real-time quantities)
- [x] Average Cost valuation
- [x] Reorder alerts
- [x] Receipt → Journal Entry posting
- [x] COGS posting on sales

### NICE TO HAVE

**Sales:**
- [ ] Quotations (useful but not blocking)
- [ ] Returns & Credit Notes
- [ ] Sales tax configuration table (vs hardcoded rate)
- [ ] Installment payment plan
- [ ] Customer statement PDF

**Inventory:**
- [ ] Purchase Orders (PO before receipt)
- [ ] Inter-warehouse stock transfers
- [ ] Stock adjustment approval workflow
- [ ] Barcode/SKU scanning support
- [ ] Item images

### ADVANCED (Build Later)

- [ ] FIFO cost layers
- [ ] Multi-currency (beyond exchange rate)
- [ ] Item serial/lot number tracking
- [ ] Delivery notes (separate from invoicing)
- [ ] Consignment stock
- [ ] Drop shipping
- [ ] Customer price lists
- [ ] Volume discounts
- [ ] Sales commission tracking
- [ ] Supplier price lists
- [ ] Landed cost allocation

---

## 6. Complete Table Reference

### New Tables to Create

| Table | Module | Purpose |
|-------|--------|---------|
| `item_categories` | Inventory | Category grouping for items |
| `units_of_measure` | Inventory | UOM definitions |
| `sales_tax_rates` | Sales | Configurable tax rates |
| `suppliers` | Inventory | Supplier master |
| `purchase_orders` | Inventory | PO header |
| `purchase_order_lines` | Inventory | PO lines |
| `purchase_receipts` | Inventory | GRN header |
| `purchase_receipt_lines` | Inventory | GRN lines |
| `stock_balances` | Inventory | Current stock per item/warehouse |
| `sales_quotations` | Sales | Quotation header |
| `sales_quotation_lines` | Sales | Quotation lines |
| `sales_payments` | Sales | Payment records |
| `sales_returns` | Sales | Return/credit note header |
| `sales_return_lines` | Sales | Return lines |

### Tables to Alter (Add Columns)

| Table | New Columns |
|-------|------------|
| `customers` | credit_limit, payment_terms_days, ar_account_id, opening_balance, opening_balance_date, currency_code |
| `sales_orders` | quotation_id, discount_type, discount_value, discount_amount, subtotal, tax_amount, total_amount, delivery_date, confirmed_at, invoiced_amount |
| `sales_order_lines` | discount_percent, discount_amount, tax_rate_id, tax_amount, line_subtotal, invoiced_quantity |
| `invoices` | due_date, discount_amount, paid_amount, balance_amount, payment_status, journal_entry_id, posted_at |
| `invoice_lines` | discount_percent, discount_amount, tax_rate_id, tax_amount, line_subtotal, warehouse_id, cogs_amount |
| `items` | category_id, uom_id, purchase_price, sale_price, reorder_level, reorder_qty, cost_method, average_cost, inventory_account_id, cogs_account_id, revenue_account_id, allow_negative_stock |
| `stock_movements` | posting_status, journal_entry_id, average_cost_before, average_cost_after |

---

## 7. Data Flow Diagrams

### Sales Data Flow

```
Customer
  ↓ creates
Sales Order ────────────────────────────────────────────┐
  ↓ (partial invoicing)                                  │
Invoice ──── due_date (from customer.payment_terms) ←───┘
  ↓ post
Journal Entry (AR / Revenue / Tax / COGS)
  ↓ customer pays
Payment
  ↓ post
Journal Entry (Bank/Cash DR, AR CR)
  → Invoice.paid_amount += payment.amount
  → Invoice.payment_status = paid/partial
```

### Inventory Data Flow

```
Supplier
  ↓ creates
Purchase Order
  ↓ goods arrive
Purchase Receipt
  ↓ post
├─ StockMovement (type=in)
├─ StockBalance.quantity += received_qty
├─ Item.average_cost recalculated
└─ Journal Entry (Inventory DR, AP CR)

On Sale (Invoice Posted):
StockMovement (type=out)
├─ StockBalance.quantity -= sold_qty
└─ Journal Entry (COGS DR, Inventory CR)
```

### Account Resolution Flow

```
Invoice Line
  │
  ├─ Revenue → item.revenue_account_id
  │              ↓ (fallback) item.category.revenue_account_id
  │              ↓ (fallback) settings.default_revenue_account_id
  │
  ├─ COGS ──→ item.cogs_account_id
  │              ↓ (fallback) item.category.cogs_account_id
  │              ↓ (fallback) settings.default_cogs_account_id
  │
  └─ AR ────→ customer.ar_account_id
               ↓ (fallback) settings.default_ar_account_id
```