# Purchases Schema Gap Analysis

Date: 2026-05-09

## Frontend Scope Reviewed

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

Pages reviewed:
- `PurchaseOrders.tsx`
- `NewPurchaseOrder.tsx`
- `PendingPurchaseApproval.tsx`
- `Inbound.tsx`
- `Shortlanded.tsx`
- `Overages.tsx`
- `PurchaseReturns.tsx`
- `SupplierPerformance.tsx`

## Existing Purchases Tables

Current `purchases` schema tables:
- `purchase_requests`
- `purchase_request_items`
- `purchase_orders`
- `purchase_order_items`
- `goods_receipts`
- `goods_receipt_items`
- `purchase_returns`
- `purchase_return_items`

These cover the core procurement documents, but the frontend expects richer workflow, variance, approval, and supplier performance state.

## Key Gaps

### Purchase Orders

The UI needs supplier snapshots, warehouse labels, expected delivery dates, currency, tax/discount/shipping totals, approval state, submitted/approved/rejected actors, and receipt/variance progress.

Required additions:
- Supplier display/email and performance snapshot fields.
- Warehouse and currency snapshots.
- Discount, tax, shipping, and total amount fields.
- Approval threshold, status, timestamps, and actor fields.
- Receipt status/progress and variance counters.
- Printable/document metadata and audit-friendly notes.

### Purchase Order Items

The UI shows SKU, item name, stock/reorder snapshots, ordered quantity, received quantity, variance quantity, tax rate, and line totals.

Required additions:
- Item SKU/name/UOM snapshots.
- Current stock and reorder level snapshots.
- Ordered, received, accepted, rejected, shortlanded, and overage quantities.
- Tax, discount, and line total fields.
- Item-level variance status and metadata.

### Pending Purchase Approval

The UI has a dedicated approval queue with age, budget impact, supplier history, inventory impact, AI suggestions, approve/reject actions, and escalation state.

Required table:
- `purchase_approval_requests`

### Inbound / Goods Receipts

The GRN screen supports draft receipts, partial receipts, variance-pending receipts, item conditions, shortlanded/overage quantities, and variance approval before inventory updates.

Required additions:
- Goods receipt number/status fields.
- Supplier, warehouse, received-by snapshots.
- Inspection, putaway, and discrepancy statuses.
- Item-level expected/received/accepted/rejected/variance quantities.
- Receipt event history.

Required table:
- `goods_receipt_events`

### Shortlanded And Overages

The UI has separate incident queues for shortlanded and overage cases with supplier/item/PO search, value calculation, status, approval/return/resolution, and supplier performance context.

Required tables:
- `purchase_discrepancy_cases`
- `purchase_discrepancy_items`

### Purchase Returns

The return UI creates returns from GRNs, tracks return status, approval, in-transit/received/credited lifecycle, return reasons, credit notes, and item conditions.

Required additions:
- Linked PO/GRN snapshots.
- Approval, shipment, inspection, refund/credit-note fields.
- Item-level GRN quantity, return quantity, condition, and value.

Required table:
- `purchase_return_events`

### Supplier Performance

The performance dashboard needs per-supplier KPIs, score/grade, trend, delivery history, variance history, charts, and AI recommendations.

Required tables:
- `supplier_performance_snapshots`
- `supplier_performance_records`
- `purchase_ai_insights`

### Audit And Communication

The UI exposes print/download/send flows and several approval or status-changing actions. These need durable logs.

Required tables:
- `purchase_document_dispatches`
- `purchase_audit_trail`

## Migration Created

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

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