# Inventory Schema Gap Analysis

## Frontend Scope Reviewed

Source: `C:\Apache24\htdocs\railserp\src\pages\inventory`

Pages reviewed:

- `InventoryItems.tsx`
- `StockLevels.tsx`
- `StockCount.tsx`
- `Transfers.tsx`
- `Variance.tsx`
- `Scrap.tsx`
- `ReorderRules.tsx`
- `Categories.tsx`
- `SubCategories.tsx`
- `Brands.tsx`
- `approvals/TransferApprovals.tsx`
- `approvals/ScrapApprovals.tsx`
- `approvals/VarianceApprovals.tsx`

## Attached Current Schema

The attached Inventory schema has 8 tables:

- `inventory.items`
- `inventory.item_variants`
- `inventory.stock_balances`
- `inventory.stock_movements`
- `inventory.stock_adjustments`
- `inventory.stock_adjustment_items`
- `inventory.stock_counts`
- `inventory.stock_count_items`

## Fit Assessment

The existing schema covers the inventory core: item master, variants, balances, movements, stock counts, and stock adjustments.

The frontend, however, models several workflows as first-class screens:

- Brand/category/sub-category maintenance.
- Reorder rules with AI insights and suggested purchase order actions.
- Stock transfers between warehouses/shops with approval and fulfillment states.
- Scrap/write-off requests with evidence, approval, rejection, and cost impact.
- Variance investigation and approval from stock counts.
- Unified approval queues for transfer, scrap, and variance approvals.
- Stock-level detail drawers showing related movement documents and aging.

These workflows should not be forced into generic `stock_adjustments`; doing so would make status tracking, approval queues, audit trails, and UI-specific filtering brittle.

## Required Additions

### Existing Table Extensions

Add frontend-facing columns to existing tables where appropriate:

- `inventory.items`: item code/name aliases, brand/category references and labels, barcode, pricing, default warehouse, tracking flags, accounting mappings, stock thresholds, metadata.
- `inventory.stock_balances`: available/reserved/on-hand quantities, reorder level, cost, status, aging metadata.
- `inventory.stock_counts`: count number, warehouse labels, approval/status metadata, variance status, assigned user, item progress.
- `inventory.stock_count_items`: item labels, physical quantity, variance quantity/value, unit cost, status.
- `inventory.stock_adjustments`: adjustment number/type, workflow approval fields, source entity references.
- `inventory.stock_movements`: reference fields, direction, quantity/balance, warehouse labels, actor name.

### New Tables

- `inventory.categories`: supports both categories and sub-categories with `parent_category_id`.
- `inventory.brands`: brand master data.
- `inventory.reorder_rules`: reorder thresholds, suggested quantity, lead time, usage and stockout intelligence.
- `inventory.stock_transfers`: transfer header workflow.
- `inventory.stock_transfer_items`: transfer lines.
- `inventory.stock_transfer_events`: transfer lifecycle timeline.
- `inventory.scrap_requests`: scrap/write-off header workflow.
- `inventory.scrap_attachments`: evidence files/photos for scrap requests.
- `inventory.inventory_variance_cases`: stock count variance investigation and adjustment workflow.
- `inventory.inventory_approval_requests`: unified approval queue for transfers, scrap, variance, and counts.
- `inventory.inventory_ai_insights`: AI insight cards for reorder and variance screens.
- `inventory.inventory_document_links`: related PO/SO/transfer/adjustment documents for stock detail views.

## Migration Created

`database/migrations/20260509_000016_close_inventory_schema_gap.sql`

The migration is idempotent and uses `CREATE TABLE IF NOT EXISTS`, `ADD COLUMN IF NOT EXISTS`, and conditional indexes.