# Warehouse Schema Gap Analysis Date: 2026-05-10 ## Evidence Reviewed - Runtime schema screenshot supplied from the database browser. - `app/Modules/Warehouse` - `routes/modules/warehouse.php` - `database/migrations` - Existing cross-module gap docs for inventory, purchases, and sales. - Inventory seed failures involving `warehouse_id` foreign keys and required inventory movement fields. ## Attached Current Schema The live `warehouse` schema shown in the database browser has 11 tables: - `warehouse.bin_locations` - `warehouse.dispatch_items` - `warehouse.dispatches` - `warehouse.pick_list_items` - `warehouse.pick_lists` - `warehouse.putaway_task_items` - `warehouse.putaway_tasks` - `warehouse.receiving_task_items` - `warehouse.receiving_tasks` - `warehouse.transfer_order_items` - `warehouse.transfer_orders` This means the database already has operational warehouse workflow tables for receiving, putaway, picking, dispatch, bin locations, and transfer orders. ## Backend Fit Assessment The backend warehouse module is currently a placeholder around transfer orders only. Current implementation: - `routes/modules/warehouse.php` registers no routes. - `app/Modules/Warehouse/Presentation/Routes/api.php` registers no routes. - `TransferOrderRepository` points at `warehouse.warehouse`, which does not match the live schema. The live table appears to be `warehouse.transfer_orders`. - Domain, DTO, mapper, validator, model, cache, publisher, and integration classes are skeletal. - No repositories/controllers exist for bin locations, receiving tasks, putaway tasks, pick lists, dispatches, or dispatch items. So the runtime schema is richer than the code. The primary gap is not just schema creation; it is schema-to-backend alignment. ## Cross-Module Pressure Inventory, Purchases, Sales, and Suppliers already use warehouse concepts: - Inventory tables store `warehouse_id` and `warehouse_name` for stock balances, movements, counts, transfers, scrap, adjustments, reorder rules, and document links. - Purchases stores warehouse snapshots on purchase requests, purchase orders, goods receipts, approval queues, discrepancy cases, and supplier terms. - Sales stores warehouse snapshots on sales orders, fulfillment events, and delivery notes. - Settings contains warehouse-relevant policy such as `require_bin_location`. The warehouse schema should be treated as the source of truth for physical warehouse execution, while commercial modules may keep snapshot names for reporting/history. ## Key Gaps ### Warehouse Master Data The live schema screenshot does not show a `warehouse.warehouses` or `warehouse.warehouse` master table. Yet other modules have `warehouse_id` foreign keys that reference a `warehouses` table in the database. Required decision: - Confirm whether the warehouse master table lives in `public.warehouses`, `inventory.warehouses`, `org.locations`, or another schema. - Standardize FK targets for `warehouse_id` columns across inventory, purchases, sales, suppliers, and warehouse workflow tables. - Keep snapshot columns like `warehouse_name` where documents need historical display values. ### Transfer Orders The live schema has: - `warehouse.transfer_orders` - `warehouse.transfer_order_items` Backend currently queries: - `warehouse.warehouse` Required backend changes: - Point the repository to `warehouse.transfer_orders`. - Add fields and mapping for transfer number, source/destination warehouses, status, approval, dispatch/receipt progress, item totals, and audit metadata. - Add item repository support for `warehouse.transfer_order_items`. - Register API routes for listing, detail, create/update, approve, dispatch, receive, and cancel actions. ### Receiving The live schema has: - `warehouse.receiving_tasks` - `warehouse.receiving_task_items` This should align with Purchases GRNs and Inventory stock movements. Required integration points: - Link receiving tasks to purchase orders/goods receipts. - Track expected, received, accepted, rejected, shortlanded, and overage quantities. - Capture receiving user, receiving date, inspection status, discrepancy status, and bin/putaway handoff. - Emit inventory stock movements only after accepted receipt or configured workflow stage. ### Putaway The live schema has: - `warehouse.putaway_tasks` - `warehouse.putaway_task_items` Required integration points: - Link putaway tasks to receiving tasks/GRNs. - Track destination bin/location, assigned user, priority, started/completed timestamps, and exceptions. - Respect `require_bin_location` settings. - Update bin-level inventory or availability once putaway completes. ### Picking The live schema has: - `warehouse.pick_lists` - `warehouse.pick_list_items` Required integration points: - Link pick lists to sales orders, transfer orders, or production/material requests. - Track reserved quantity, picked quantity, shortages, substitutions, picker assignment, and status. - Feed Sales fulfillment progress and Inventory reserved/available quantities. ### Dispatch The live schema has: - `warehouse.dispatches` - `warehouse.dispatch_items` Required integration points: - Link dispatches to sales delivery notes, transfer orders, or other outbound documents. - Track carrier, tracking reference, dispatch date, package/weight data, status, and proof of delivery. - Feed Sales delivery state and Inventory outgoing/stock movement records. ### Bin Locations The live schema has: - `warehouse.bin_locations` Required integration points: - Standard bin code/name/zone/aisle/rack/shelf structure. - Warehouse-level FK. - Active/inactive and capacity metadata. - Optional item/bin balances if the warehouse module owns bin-level stock. ## Required Backend Additions - Route registration in `routes/modules/warehouse.php`. - Resource endpoints for: - `/api/v1/warehouse/transfer-orders` - `/api/v1/warehouse/receiving-tasks` - `/api/v1/warehouse/putaway-tasks` - `/api/v1/warehouse/pick-lists` - `/api/v1/warehouse/dispatches` - `/api/v1/warehouse/bin-locations` - Repositories for every live warehouse table. - Detail loaders that include child item rows. - Status transition methods for workflow actions. - Consistent document numbering fields such as transfer/order/task/pick/dispatch numbers. - Cross-module ID and display-name snapshots for PO/SO/GRN/delivery/transfer references. ## Required Schema Verification Before writing a warehouse migration, inspect the live database definitions for: - Primary keys and unique indexes. - Required `NOT NULL` columns. - FK targets, especially all `warehouse_id` fields. - Existing status enums/check constraints. - Whether the warehouse master table is outside the `warehouse` schema. This matters because the current runtime failures show constraints that are not represented in the repo migrations. ## Migration Recommendation Do not create a blind `warehouse` schema migration until the live definitions are exported. Recommended next migration should be a schema gap closer that: - Uses `ALTER TABLE IF EXISTS ... ADD COLUMN IF NOT EXISTS` for live warehouse tables. - Fixes backend-facing aliases and nullable display/snapshot fields. - Adds missing indexes for tenant, document number, status, dates, and parent-child joins. - Avoids recreating tables already present in the live database. - Aligns inventory/purchases/sales warehouse references to the confirmed warehouse master. ## Immediate Bug `TransferOrderRepository` should not query `warehouse.warehouse`; the live table is `warehouse.transfer_orders`. Until this is fixed, warehouse transfer-order listing cannot work even if routes are registered.