# Sales Schema Gap Analysis

Date: 2026-05-09

## Frontend Scope Reviewed

Frontend path: `C:\Apache24\htdocs\railserp\src\pages\sales`

Pages reviewed:
- `SalesOrders.tsx`
- `NewSalesOrder.tsx`
- `PriceQuotations.tsx`
- `SalesReturns.tsx`
- `OrderTracking.tsx`
- `PendingApproval.tsx`
- `SalesForecast.tsx`
- `CustomerCredit.tsx`

## Existing Sales Tables

Current `sales` schema tables:
- `price_lists`
- `quotations`
- `quotation_items`
- `sales_orders`
- `sales_order_items`
- `sales_returns`
- `sales_return_items`

These cover the basic commercial documents, but the frontend expects richer operational state and analytics.

## Key Gaps

### Sales Orders

The UI needs customer display fields, sales rep names, payment terms, warehouse labels, shipping amount, approval state, credit snapshot, and fulfillment/tracking fields. The current `sales_orders` table only has the core customer/order/amount/status fields.

Required additions:
- Customer email/name snapshot fields.
- Warehouse/branch display snapshots.
- Sales rep snapshot.
- Payment terms, due date, delivery date.
- Shipping amount.
- Approval status and timestamps.
- Credit status/limit/outstanding snapshots.
- Fulfillment status/progress, carrier, tracking reference, dispatch and delivery dates.
- Invoice/delivery-note conversion references.

### Line Items

The UI shows SKU, product name, available stock, tax rate, discount percent, weight, delivered/returned quantities. Current line item tables have description, quantity, unit price, discount amount, tax amount, line total.

Required additions:
- Item SKU/name/UOM snapshots.
- Discount percent and tax rate.
- Available stock snapshot.
- Reserved, fulfilled, delivered, returned quantities.
- Weight metadata.

### Quotations

The UI supports quote status lifecycle, send/print/email actions, quote-to-order conversion, quote sales rep, customer email, notes, and line item product snapshots.

Required additions:
- Customer display/email.
- Sales rep display.
- Sent/accepted/rejected/expired timestamps and rejection reason.
- Converted sales order reference.
- Quotation item SKU/name/discount/tax snapshots.

### Pending Approval

The UI has a dedicated approval queue with age, threshold, credit exposure, AI suggestions, approval/rejection actions, and audit trail. Existing tables only have `approved_at`.

Required table:
- `sales_approval_requests`

### Order Tracking

The UI displays fulfillment lifecycle timeline, tracking refs, carriers, total weight, estimated delivery, and event history.

Required tables:
- `sales_order_fulfillment_events`
- `sales_delivery_notes`
- `sales_delivery_note_items`

### Sales Returns

The UI includes approval status, inspection status, refund status, credit note impact, restocking fee, and item-level inspection/refund quantities.

Required additions:
- Return approval/refund/inspection fields.
- Return item inspection/refund/restock fields.
- Optional return events/audit trail.

Required table:
- `sales_return_events`

### Customer Credit

The UI monitors credit utilization, overdue invoices, payment behavior, credit history, reminders, and credit reviews. Some customer credit fields exist in `crm.customers`, but the Sales module needs sales-facing snapshots and workflow history.

Required tables:
- `sales_customer_credit_profiles`
- `sales_customer_credit_events`
- `sales_customer_invoice_links`
- `sales_customer_payment_behavior`
- `sales_payment_reminders`

### Sales Forecast

The UI has historical-vs-forecast charts, forecast accuracy, revenue by category/channel, and AI insights.

Required tables:
- `sales_forecasts`
- `sales_forecast_lines`
- `sales_ai_insights`

### Audit/Communication

The UI exposes print, email, convert-to-invoice, convert-to-delivery, and AI prompts. These need durable logs for backend implementation.

Required tables:
- `sales_document_dispatches`
- `sales_audit_trail`

## Migration Created

Migration:
- `database/migrations/20260509_000014_close_sales_schema_gap.sql`

The migration is additive and tenant-scoped. It preserves existing tables while extending them for the frontend workflows.