# Data Model Reference

Generated from the live PostgreSQL schema. Conventions: `timestamptz` everywhere, quantities `numeric(18,3)` in base UoM, status/type columns are `varchar` validated by PHP enums, master data soft-deletes, transactional documents are cancelled rather than deleted.

Entity relationship overview:

```
clients ─┬─ products ─┬─ product_uoms / product_barcodes / kit_components
         │            └─ product_location_assignments ─ locations
         ├─ asns ─ asn_lines ─ receipts ─ receipt_lines ─ qc_inspections ─ putaway_tasks
         ├─ sales_orders ─ sales_order_lines ─ allocations ─ pick_task_lines ─ pick_tasks ─ waves
         │                                    └─ package_lines ─ packages ─ shipments
         ├─ return_authorizations ─ return_authorization_lines
         ├─ work_orders ─ work_order_components / work_order_tasks
         ├─ rate_cards ─ rate_card_lines ; billable_events ─ billing_periods
         └─ inventory_balances ◄── inventory_transactions (ledger) ; lots ; handling_units ; serial_numbers
warehouses ─ zones ─ locations ─ docks ; yard_slots ─ trailers ; cycle_counts ─ cycle_count_lines ; replenishment_tasks
```

## Tenancy & facility

### `clients`

`id:bigint`, `code:character varying`, `name:character varying`, `legal_name:character varying`, `status:character varying`, `billing_currency:character varying`, `default_allocation_strategy:character varying`, `default_min_shelf_life_days:integer`, `settings:jsonb`, `contacts:jsonb`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`, `deleted_at:timestamp with time zone`

### `users`

`id:bigint`, `name:character varying`, `email:character varying`, `email_verified_at:timestamp without time zone`, `password:character varying`, `remember_token:character varying`, `created_at:timestamp without time zone`, `updated_at:timestamp without time zone`, `user_type:character varying`, `client_id:bigint`, `is_active:boolean`, `locale:character varying`, `employee_code:character varying`, `last_login_at:timestamp with time zone`

### `user_warehouse`

`user_id:bigint`, `warehouse_id:bigint`, `is_default:boolean`

### `client_warehouse`

`client_id:bigint`, `warehouse_id:bigint`, `is_active:boolean`

### `warehouses`

`id:bigint`, `code:character varying`, `name:character varying`, `timezone:character varying`, `address:jsonb`, `is_active:boolean`, `settings:jsonb`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`, `deleted_at:timestamp with time zone`

### `zones`

`id:bigint`, `warehouse_id:bigint`, `code:character varying`, `name:character varying`, `zone_type:character varying`, `temperature_class:character varying`, `pick_sequence:integer`, `is_active:boolean`, `attributes:jsonb`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `locations`

`id:bigint`, `warehouse_id:bigint`, `zone_id:bigint`, `code:character varying`, `barcode:character varying`, `aisle:character varying`, `bay:character varying`, `level:character varying`, `position:character varying`, `location_type:character varying`, `status:character varying`, `pick_sequence:integer`, `putaway_sequence:integer`, `max_weight_kg:numeric`, `max_volume_m3:numeric`, `max_handling_units:integer`, `length_cm:numeric`, `width_cm:numeric`, `height_cm:numeric`, `allow_mixed_products:boolean`, `allow_mixed_lots:boolean`, `is_pickable:boolean`, `is_receivable:boolean`, `hazmat_allowed:boolean`, `temperature_class:character varying`, `coord_x:numeric`, `coord_y:numeric`, `attributes:jsonb`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`, `deleted_at:timestamp with time zone`

### `docks`

`id:bigint`, `warehouse_id:bigint`, `code:character varying`, `name:character varying`, `dock_type:character varying`, `staging_location_id:bigint`, `is_active:boolean`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `yard_slots`

`id:bigint`, `warehouse_id:bigint`, `code:character varying`, `slot_type:character varying`, `is_active:boolean`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

## Catalog

### `product_categories`

`id:bigint`, `client_id:bigint`, `parent_id:bigint`, `code:character varying`, `name:character varying`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `products`

`id:bigint`, `client_id:bigint`, `category_id:bigint`, `sku:character varying`, `name:character varying`, `description:text`, `status:character varying`, `base_uom:character varying`, `is_lot_tracked:boolean`, `is_serial_tracked:boolean`, `is_expiry_tracked:boolean`, `shelf_life_days:integer`, `min_shelf_life_days_outbound:integer`, `allocation_strategy:character varying`, `temperature_class:character varying`, `is_hazmat:boolean`, `hazmat_class:character varying`, `un_number:character varying`, `country_of_origin:character varying`, `hs_code:character varying`, `unit_weight_kg:numeric`, `unit_length_cm:numeric`, `unit_width_cm:numeric`, `unit_height_cm:numeric`, `unit_volume_m3:numeric`, `unit_cost:numeric`, `cost_currency:character varying`, `velocity_class:character varying`, `is_kit:boolean`, `qc_required_on_receipt:boolean`, `attributes:jsonb`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`, `deleted_at:timestamp with time zone`

### `product_uoms`

`id:bigint`, `product_id:bigint`, `code:character varying`, `name:character varying`, `quantity_in_base:numeric`, `is_base:boolean`, `is_default_receiving:boolean`, `is_default_shipping:boolean`, `weight_kg:numeric`, `length_cm:numeric`, `width_cm:numeric`, `height_cm:numeric`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `product_barcodes`

`id:bigint`, `client_id:bigint`, `product_id:bigint`, `barcode:character varying`, `uom_code:character varying`, `barcode_type:character varying`, `is_primary:boolean`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `kit_components`

`id:bigint`, `kit_product_id:bigint`, `component_product_id:bigint`, `quantity_per:numeric`, `sequence:smallint`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `product_location_assignments`
Fixed pick faces with min/max/replenish quantities.

`id:bigint`, `warehouse_id:bigint`, `product_id:bigint`, `location_id:bigint`, `is_primary_pick:boolean`, `min_qty:numeric`, `max_qty:numeric`, `replenish_qty:numeric`, `is_active:boolean`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `product_velocities`

`id:bigint`, `warehouse_id:bigint`, `product_id:bigint`, `picks_per_day:numeric`, `units_per_day:numeric`, `lines_last_90d:numeric`, `velocity_class:character varying`, `computed_at:timestamp with time zone`

## Inventory (trust layer)

### `lots`

`id:bigint`, `client_id:bigint`, `product_id:bigint`, `lot_number:character varying`, `manufacture_date:date`, `expiry_date:date`, `supplier_lot:character varying`, `status:character varying`, `attributes:jsonb`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `handling_units`
Licence plates (pallet/case/tote/carton) with nesting via parent_id and current location.

`id:bigint`, `warehouse_id:bigint`, `client_id:bigint`, `lpn:character varying`, `sscc:character varying`, `hu_type:character varying`, `parent_id:bigint`, `location_id:bigint`, `status:character varying`, `gross_weight_kg:numeric`, `length_cm:numeric`, `width_cm:numeric`, `height_cm:numeric`, `attributes:jsonb`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `inventory_balances`
Derived projection of the ledger per (client, warehouse, location, product, lot?, LPN?, status). UNIQUE NULLS NOT DISTINCT. Only InventoryService writes it.

`id:bigint`, `client_id:bigint`, `warehouse_id:bigint`, `location_id:bigint`, `product_id:bigint`, `lot_id:bigint`, `handling_unit_id:bigint`, `inventory_status:character varying`, `quantity_on_hand:numeric`, `quantity_allocated:numeric`, `first_received_at:timestamp with time zone`, `last_movement_at:timestamp with time zone`, `version:bigint`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `inventory_transactions`
Append-only ledger. One row per movement with from/to sides, snapshots and reference document. Never updated or deleted.

`id:bigint`, `operation_id:uuid`, `client_id:bigint`, `warehouse_id:bigint`, `type:character varying`, `product_id:bigint`, `lot_id:bigint`, `quantity:numeric`, `entered_uom:character varying`, `entered_quantity:numeric`, `from_balance_id:bigint`, `to_balance_id:bigint`, `from_location_id:bigint`, `to_location_id:bigint`, `from_handling_unit_id:bigint`, `to_handling_unit_id:bigint`, `from_status:character varying`, `to_status:character varying`, `on_hand_after_from:numeric`, `on_hand_after_to:numeric`, `reference_type:character varying`, `reference_id:bigint`, `reason_code:character varying`, `notes:character varying`, `actor_type:character varying`, `actor_id:bigint`, `performed_at:timestamp with time zone`, `created_at:timestamp with time zone`

### `inventory_transaction_serials`

`transaction_id:bigint`, `serial_number_id:bigint`

### `serial_numbers`
One row per serialised unit pointing at the balance that holds it.

`id:bigint`, `client_id:bigint`, `product_id:bigint`, `serial:character varying`, `lot_id:bigint`, `balance_id:bigint`, `status:character varying`, `last_transaction_id:bigint`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `allocations`
Reservations of stock on a specific balance for a demand (sales_order_line | work_order_component). Status allocated → picked → packed → shipped.

`id:bigint`, `client_id:bigint`, `warehouse_id:bigint`, `demand_type:character varying`, `demand_id:bigint`, `balance_id:bigint`, `product_id:bigint`, `lot_id:bigint`, `location_id:bigint`, `handling_unit_id:bigint`, `quantity:numeric`, `status:character varying`, `pick_task_line_id:bigint`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `inventory_holds`

`id:bigint`, `client_id:bigint`, `warehouse_id:bigint`, `scope_type:character varying`, `scope_id:bigint`, `reason_code:character varying`, `notes:character varying`, `status:character varying`, `placed_operation_id:uuid`, `released_operation_id:uuid`, `placed_by:bigint`, `placed_at:timestamp with time zone`, `released_by:bigint`, `released_at:timestamp with time zone`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `inventory_adjustments`

`id:bigint`, `number:character varying`, `client_id:bigint`, `warehouse_id:bigint`, `status:character varying`, `reason_code:character varying`, `source:character varying`, `reference_type:character varying`, `reference_id:bigint`, `notes:text`, `requested_by:bigint`, `approved_by:bigint`, `approved_at:timestamp with time zone`, `posted_at:timestamp with time zone`, `operation_id:uuid`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `inventory_adjustment_lines`

`id:bigint`, `adjustment_id:bigint`, `product_id:bigint`, `location_id:bigint`, `lot_id:bigint`, `handling_unit_id:bigint`, `inventory_status:character varying`, `quantity_delta:numeric`, `unit_cost:numeric`, `transaction_id:bigint`, `notes:character varying`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `transfer_orders`

`id:bigint`, `number:character varying`, `client_id:bigint`, `from_warehouse_id:bigint`, `to_warehouse_id:bigint`, `status:character varying`, `outbound_order_id:bigint`, `inbound_asn_id:bigint`, `notes:text`, `created_by:bigint`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `transfer_order_lines`

`id:bigint`, `transfer_order_id:bigint`, `product_id:bigint`, `quantity:numeric`, `lot_number:character varying`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

## Inbound

### `dock_appointments`

`id:bigint`, `warehouse_id:bigint`, `dock_id:bigint`, `client_id:bigint`, `direction:character varying`, `reference_type:character varying`, `reference_id:bigint`, `carrier_name:character varying`, `vehicle_ref:character varying`, `driver_name:character varying`, `driver_phone:character varying`, `scheduled_start:timestamp with time zone`, `scheduled_end:timestamp with time zone`, `status:character varying`, `checked_in_at:timestamp with time zone`, `at_dock_at:timestamp with time zone`, `departed_at:timestamp with time zone`, `yard_slot_id:bigint`, `notes:character varying`, `created_by:bigint`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `asns`

`id:bigint`, `number:character varying`, `client_id:bigint`, `warehouse_id:bigint`, `asn_type:character varying`, `status:character varying`, `supplier_name:character varying`, `supplier_ref:character varying`, `client_reference:character varying`, `carrier_name:character varying`, `vehicle_ref:character varying`, `container_no:character varying`, `seal_no:character varying`, `expected_arrival_at:timestamp with time zone`, `dock_appointment_id:bigint`, `source:character varying`, `ai_recommendation_id:bigint`, `notes:text`, `confirmed_at:timestamp with time zone`, `arrived_at:timestamp with time zone`, `received_at:timestamp with time zone`, `cancelled_at:timestamp with time zone`, `cancel_reason:character varying`, `attributes:jsonb`, `created_by:bigint`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `asn_lines`

`id:bigint`, `asn_id:bigint`, `line_no:smallint`, `product_id:bigint`, `uom_code:character varying`, `entered_quantity:numeric`, `quantity_expected:numeric`, `quantity_received:numeric`, `quantity_rejected:numeric`, `lot_number:character varying`, `expiry_date:date`, `manufacture_date:date`, `expected_serials:jsonb`, `status:character varying`, `notes:character varying`, `attributes:jsonb`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `receipts`

`id:bigint`, `number:character varying`, `warehouse_id:bigint`, `client_id:bigint`, `asn_id:bigint`, `dock_id:bigint`, `receiving_location_id:bigint`, `status:character varying`, `receipt_type:character varying`, `started_by:bigint`, `started_at:timestamp with time zone`, `completed_by:bigint`, `completed_at:timestamp with time zone`, `notes:text`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `receipt_lines`

`id:bigint`, `receipt_id:bigint`, `asn_line_id:bigint`, `product_id:bigint`, `uom_code:character varying`, `entered_quantity:numeric`, `quantity:numeric`, `lot_id:bigint`, `handling_unit_id:bigint`, `location_id:bigint`, `condition:character varying`, `qc_status:character varying`, `inventory_status:character varying`, `discrepancy_type:character varying`, `transaction_id:bigint`, `serials:jsonb`, `received_by:bigint`, `received_at:timestamp with time zone`, `notes:character varying`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `qc_inspections`

`id:bigint`, `receipt_line_id:bigint`, `status:character varying`, `sample_size:integer`, `quantity_passed:numeric`, `quantity_failed:numeric`, `failure_reason_code:character varying`, `checklist:jsonb`, `notes:text`, `inspected_by:bigint`, `inspected_at:timestamp with time zone`, `operation_id:uuid`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `putaway_rules`
Ordered rules (criteria → target zones → strategy) evaluated by PutawayStrategy; AI re-ranks the output.

`id:bigint`, `warehouse_id:bigint`, `client_id:bigint`, `name:character varying`, `priority:smallint`, `criteria:jsonb`, `target_zone_ids:jsonb`, `target_location_types:jsonb`, `strategy:character varying`, `is_active:boolean`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `putaway_tasks`

`id:bigint`, `number:character varying`, `warehouse_id:bigint`, `client_id:bigint`, `receipt_line_id:bigint`, `handling_unit_id:bigint`, `product_id:bigint`, `lot_id:bigint`, `inventory_status:character varying`, `quantity:numeric`, `from_location_id:bigint`, `suggested_location_id:bigint`, `suggestion_source:character varying`, `ai_recommendation_id:bigint`, `actual_location_id:bigint`, `status:character varying`, `priority:smallint`, `assigned_to:bigint`, `assigned_at:timestamp with time zone`, `started_at:timestamp with time zone`, `completed_at:timestamp with time zone`, `transaction_id:bigint`, `override_reason:character varying`, `notes:character varying`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

## Outbound

### `carriers`

`id:bigint`, `code:character varying`, `name:character varying`, `services:jsonb`, `integration_type:character varying`, `settings:jsonb`, `is_active:boolean`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `sales_orders`

`id:bigint`, `number:character varying`, `client_id:bigint`, `warehouse_id:bigint`, `order_type:character varying`, `client_reference:character varying`, `customer_reference:character varying`, `status:character varying`, `allocation_state:character varying`, `priority:smallint`, `order_date:date`, `requested_ship_at:timestamp with time zone`, `requested_delivery_at:timestamp with time zone`, `ship_to:jsonb`, `bill_to:jsonb`, `carrier_id:bigint`, `carrier_service:character varying`, `incoterm:character varying`, `is_on_hold:boolean`, `hold_reason:character varying`, `allocation_strategy:character varying`, `min_shelf_life_days:integer`, `allow_partial_shipment:boolean`, `wave_id:bigint`, `source:character varying`, `notes:text`, `packing_instructions:text`, `confirmed_at:timestamp with time zone`, `allocated_at:timestamp with time zone`, `picked_at:timestamp with time zone`, `packed_at:timestamp with time zone`, `shipped_at:timestamp with time zone`, `cancelled_at:timestamp with time zone`, `cancel_reason:character varying`, `attributes:jsonb`, `created_by:bigint`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `sales_order_lines`

`id:bigint`, `order_id:bigint`, `line_no:smallint`, `product_id:bigint`, `uom_code:character varying`, `entered_quantity:numeric`, `quantity_ordered:numeric`, `quantity_allocated:numeric`, `quantity_picked:numeric`, `quantity_packed:numeric`, `quantity_shipped:numeric`, `quantity_cancelled:numeric`, `requested_lot_number:character varying`, `status:character varying`, `unit_price:numeric`, `notes:character varying`, `attributes:jsonb`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `waves`

`id:bigint`, `number:character varying`, `warehouse_id:bigint`, `status:character varying`, `wave_type:character varying`, `pick_method:character varying`, `criteria:jsonb`, `order_count:integer`, `line_count:integer`, `created_by:bigint`, `released_by:bigint`, `released_at:timestamp with time zone`, `completed_at:timestamp with time zone`, `notes:text`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `pick_tasks`

`id:bigint`, `number:character varying`, `warehouse_id:bigint`, `wave_id:bigint`, `order_id:bigint`, `pick_type:character varying`, `zone_id:bigint`, `status:character varying`, `priority:smallint`, `assigned_to:bigint`, `assigned_at:timestamp with time zone`, `started_at:timestamp with time zone`, `completed_at:timestamp with time zone`, `pick_to_handling_unit_id:bigint`, `drop_location_id:bigint`, `line_count:integer`, `notes:character varying`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `pick_task_lines`

`id:bigint`, `pick_task_id:bigint`, `sequence:smallint`, `allocation_id:bigint`, `order_line_id:bigint`, `product_id:bigint`, `lot_id:bigint`, `from_location_id:bigint`, `from_handling_unit_id:bigint`, `quantity_to_pick:numeric`, `quantity_picked:numeric`, `quantity_short:numeric`, `status:character varying`, `to_handling_unit_id:bigint`, `short_reason_code:character varying`, `picked_by:bigint`, `picked_at:timestamp with time zone`, `transaction_id:bigint`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `shipments`

`id:bigint`, `number:character varying`, `warehouse_id:bigint`, `client_id:bigint`, `carrier_id:bigint`, `carrier_service:character varying`, `status:character varying`, `ship_to:jsonb`, `tracking_number:character varying`, `manifest_ref:character varying`, `vehicle_ref:character varying`, `seal_no:character varying`, `dock_appointment_id:bigint`, `staging_location_id:bigint`, `package_count:integer`, `total_weight_kg:numeric`, `total_volume_m3:numeric`, `manifested_at:timestamp with time zone`, `dispatched_at:timestamp with time zone`, `dispatched_by:bigint`, `proof_of_dispatch:jsonb`, `freight_reference:character varying`, `notes:text`, `operation_id:uuid`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `shipment_orders`

`shipment_id:bigint`, `order_id:bigint`

### `packages`

`id:bigint`, `number:character varying`, `warehouse_id:bigint`, `order_id:bigint`, `shipment_id:bigint`, `handling_unit_id:bigint`, `package_type:character varying`, `weight_kg:numeric`, `length_cm:numeric`, `width_cm:numeric`, `height_cm:numeric`, `tracking_number:character varying`, `label_path:character varying`, `status:character varying`, `packed_by:bigint`, `packed_at:timestamp with time zone`, `closed_at:timestamp with time zone`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `package_lines`

`id:bigint`, `package_id:bigint`, `order_line_id:bigint`, `product_id:bigint`, `lot_id:bigint`, `quantity:numeric`, `serials:jsonb`, `transaction_id:bigint`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

## Inventory control

### `cycle_count_plans`

`id:bigint`, `number:character varying`, `warehouse_id:bigint`, `client_id:bigint`, `name:character varying`, `count_type:character varying`, `scope:jsonb`, `frequency:character varying`, `locations_per_run:smallint`, `blind:boolean`, `require_recount_on_variance:boolean`, `variance_tolerance_pct:numeric`, `variance_tolerance_qty:numeric`, `status:character varying`, `next_run_at:timestamp with time zone`, `last_run_at:timestamp with time zone`, `created_by:bigint`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `cycle_counts`

`id:bigint`, `number:character varying`, `plan_id:bigint`, `warehouse_id:bigint`, `client_id:bigint`, `count_type:character varying`, `status:character varying`, `blind:boolean`, `require_recount_on_variance:boolean`, `variance_tolerance_pct:numeric`, `variance_tolerance_qty:numeric`, `assigned_to:bigint`, `scheduled_for:timestamp with time zone`, `started_at:timestamp with time zone`, `counted_at:timestamp with time zone`, `reviewed_by:bigint`, `reviewed_at:timestamp with time zone`, `posted_at:timestamp with time zone`, `adjustment_id:bigint`, `ai_recommendation_id:bigint`, `notes:text`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `cycle_count_lines`
System snapshot at count start, count 1 / count 2, final, variance; stale lines (stock moved) force a recount.

`id:bigint`, `cycle_count_id:bigint`, `location_id:bigint`, `client_id:bigint`, `product_id:bigint`, `lot_id:bigint`, `handling_unit_id:bigint`, `inventory_status:character varying`, `system_quantity:numeric`, `count_1_quantity:numeric`, `count_1_by:bigint`, `count_1_at:timestamp with time zone`, `count_2_quantity:numeric`, `count_2_by:bigint`, `count_2_at:timestamp with time zone`, `final_quantity:numeric`, `variance_quantity:numeric`, `variance_value:numeric`, `status:character varying`, `is_unexpected:boolean`, `reason_code:character varying`, `notes:character varying`, `transaction_id:bigint`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `replenishment_tasks`

`id:bigint`, `number:character varying`, `warehouse_id:bigint`, `client_id:bigint`, `product_id:bigint`, `to_location_id:bigint`, `from_location_id:bigint`, `from_handling_unit_id:bigint`, `lot_id:bigint`, `quantity_requested:numeric`, `quantity_moved:numeric`, `trigger:character varying`, `status:character varying`, `priority:smallint`, `assigned_to:bigint`, `assigned_at:timestamp with time zone`, `started_at:timestamp with time zone`, `completed_at:timestamp with time zone`, `transaction_id:bigint`, `ai_recommendation_id:bigint`, `notes:character varying`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `slotting_recommendations`

`id:bigint`, `warehouse_id:bigint`, `client_id:bigint`, `product_id:bigint`, `from_location_id:bigint`, `to_location_id:bigint`, `recommendation_type:character varying`, `reason:character varying`, `expected_benefit:jsonb`, `score:numeric`, `source:character varying`, `ai_recommendation_id:bigint`, `status:character varying`, `decided_by:bigint`, `decided_at:timestamp with time zone`, `executed_task_id:bigint`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

## Returns, work orders, labor, yard

### `return_authorizations`

`id:bigint`, `number:character varying`, `client_id:bigint`, `warehouse_id:bigint`, `original_order_id:bigint`, `client_reference:character varying`, `customer_reference:character varying`, `status:character varying`, `reason_code:character varying`, `return_from:jsonb`, `receipt_id:bigint`, `authorized_by:bigint`, `authorized_at:timestamp with time zone`, `received_at:timestamp with time zone`, `inspected_at:timestamp with time zone`, `closed_at:timestamp with time zone`, `notes:text`, `created_by:bigint`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `return_authorization_lines`

`id:bigint`, `rma_id:bigint`, `line_no:smallint`, `product_id:bigint`, `original_order_line_id:bigint`, `quantity_expected:numeric`, `quantity_received:numeric`, `lot_id:bigint`, `serials:jsonb`, `reason_code:character varying`, `condition:character varying`, `disposition:character varying`, `disposition_location_id:bigint`, `inventory_status:character varying`, `receipt_line_id:bigint`, `transaction_id:bigint`, `notes:character varying`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `work_orders`

`id:bigint`, `number:character varying`, `warehouse_id:bigint`, `client_id:bigint`, `wo_type:character varying`, `product_id:bigint`, `quantity_planned:numeric`, `quantity_completed:numeric`, `status:character varying`, `priority:smallint`, `order_id:bigint`, `work_location_id:bigint`, `output_location_id:bigint`, `output_lot_id:bigint`, `due_at:timestamp with time zone`, `assigned_to:bigint`, `released_at:timestamp with time zone`, `started_at:timestamp with time zone`, `completed_at:timestamp with time zone`, `instructions:text`, `attributes:jsonb`, `operation_id:uuid`, `created_by:bigint`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `work_order_components`

`id:bigint`, `work_order_id:bigint`, `product_id:bigint`, `quantity_per:numeric`, `quantity_required:numeric`, `quantity_allocated:numeric`, `quantity_consumed:numeric`, `lot_id:bigint`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `work_order_tasks`

`id:bigint`, `work_order_id:bigint`, `sequence:smallint`, `task_type:character varying`, `description:character varying`, `charge_code:character varying`, `billable_units:numeric`, `status:character varying`, `completed_by:bigint`, `completed_at:timestamp with time zone`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `labor_standards`

`id:bigint`, `warehouse_id:bigint`, `activity_type:character varying`, `unit_type:character varying`, `standard_seconds_per_unit:numeric`, `effective_from:date`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `labor_activities`

`id:bigint`, `warehouse_id:bigint`, `user_id:bigint`, `client_id:bigint`, `activity_type:character varying`, `reference_type:character varying`, `reference_id:bigint`, `started_at:timestamp with time zone`, `ended_at:timestamp with time zone`, `duration_seconds:integer`, `units:numeric`, `unit_type:character varying`, `standard_seconds:numeric`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `shift_sessions`

`id:bigint`, `warehouse_id:bigint`, `user_id:bigint`, `clock_in_at:timestamp with time zone`, `clock_out_at:timestamp with time zone`, `device_id:character varying`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `trailers`

`id:bigint`, `warehouse_id:bigint`, `trailer_ref:character varying`, `carrier_name:character varying`, `trailer_type:character varying`, `status:character varying`, `yard_slot_id:bigint`, `dock_id:bigint`, `dock_appointment_id:bigint`, `seal_no:character varying`, `arrived_at:timestamp with time zone`, `departed_at:timestamp with time zone`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `yard_moves`

`id:bigint`, `trailer_id:bigint`, `from_type:character varying`, `from_id:bigint`, `to_type:character varying`, `to_id:bigint`, `moved_by:bigint`, `moved_at:timestamp with time zone`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

## Billing

### `rate_cards`

`id:bigint`, `client_id:bigint`, `name:character varying`, `currency:character varying`, `effective_from:date`, `effective_to:date`, `status:character varying`, `minimum_monthly_charge:numeric`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `rate_card_lines`

`id:bigint`, `rate_card_id:bigint`, `charge_code:character varying`, `description:character varying`, `unit:character varying`, `rate:numeric`, `minimum_charge:numeric`, `tiers:jsonb`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `billing_periods`

`id:bigint`, `client_id:bigint`, `period_start:date`, `period_end:date`, `status:character varying`, `total_amount:numeric`, `currency:character varying`, `totals_by_charge_code:jsonb`, `closed_by:bigint`, `closed_at:timestamp with time zone`, `exported_at:timestamp with time zone`, `export_reference:character varying`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `billable_events`
Activity-based charges priced from the rate card in force; idempotent on (source_event_id, charge_code).

`id:bigint`, `client_id:bigint`, `warehouse_id:bigint`, `charge_code:character varying`, `quantity:numeric`, `unit:character varying`, `rate_card_line_id:bigint`, `unit_rate:numeric`, `amount:numeric`, `currency:character varying`, `reference_type:character varying`, `reference_id:bigint`, `source_event_id:uuid`, `event_date:date`, `billing_period_id:bigint`, `status:character varying`, `description:character varying`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `storage_snapshots`
Daily per client/warehouse occupancy feeding storage charges.

`id:bigint`, `client_id:bigint`, `warehouse_id:bigint`, `snapshot_date:date`, `pallet_count:integer`, `handling_unit_count:integer`, `locations_occupied:integer`, `units_on_hand:numeric`, `volume_m3:numeric`, `weight_kg:numeric`, `created_at:timestamp with time zone`

## Platform

### `reason_codes`

`id:bigint`, `category:character varying`, `code:character varying`, `description:character varying`, `requires_approval:boolean`, `is_active:boolean`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `audit_events`
Immutable audit trail: actor, action, before/after, reason, request id.

`id:bigint`, `actor_type:character varying`, `actor_id:bigint`, `actor_label:character varying`, `client_id:bigint`, `warehouse_id:bigint`, `action:character varying`, `auditable_type:character varying`, `auditable_id:bigint`, `before:jsonb`, `after:jsonb`, `reason:character varying`, `request_id:uuid`, `ip:character varying`, `created_at:timestamp with time zone`

### `outbox_events`
Transactional outbox; published by wms:outbox:publish to internal consumers and webhooks.

`id:uuid`, `event_name:character varying`, `aggregate_type:character varying`, `aggregate_id:bigint`, `client_id:bigint`, `warehouse_id:bigint`, `payload:jsonb`, `occurred_at:timestamp with time zone`, `status:character varying`, `attempts:smallint`, `published_at:timestamp with time zone`, `last_error:text`, `created_at:timestamp with time zone`

### `webhook_subscriptions`

`id:bigint`, `client_id:bigint`, `name:character varying`, `url:character varying`, `secret:text`, `events:jsonb`, `is_active:boolean`, `last_success_at:timestamp with time zone`, `last_failure_at:timestamp with time zone`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `webhook_deliveries`

`id:bigint`, `subscription_id:bigint`, `outbox_event_id:uuid`, `status:character varying`, `attempts:smallint`, `next_attempt_at:timestamp with time zone`, `response_code:smallint`, `response_body:text`, `last_error:text`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `idempotency_keys`
Stored responses for Idempotency-Key replays (24 h).

`id:bigint`, `user_id:bigint`, `key:character varying`, `request_hash:character varying`, `response_code:smallint`, `response_body:jsonb`, `in_progress:boolean`, `created_at:timestamp with time zone`, `expires_at:timestamp with time zone`

### `wms_exceptions`
Operational exceptions needing a human (short pick, discrepancy, variance, QC fail, drift, AI failure…).

`id:bigint`, `warehouse_id:bigint`, `client_id:bigint`, `exception_type:character varying`, `severity:character varying`, `status:character varying`, `reference_type:character varying`, `reference_id:bigint`, `summary:character varying`, `details:jsonb`, `ai_recommendation_id:bigint`, `assigned_to:bigint`, `resolved_by:bigint`, `resolved_at:timestamp with time zone`, `resolution_notes:text`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `attachments`

`id:bigint`, `attachable_type:character varying`, `attachable_id:bigint`, `disk:character varying`, `path:character varying`, `filename:character varying`, `mime:character varying`, `size_bytes:bigint`, `purpose:character varying`, `uploaded_by:bigint`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

## AI

### `ai_policies`

`id:bigint`, `client_id:bigint`, `warehouse_id:bigint`, `use_case:character varying`, `mode:character varying`, `auto_threshold:numeric`, `daily_cost_cap_usd:numeric`, `settings:jsonb`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `ai_invocations`
Every model call: prompt version, tokens, cost, latency, status; bodies purged after retention.

`id:bigint`, `use_case:character varying`, `prompt_version:character varying`, `provider:character varying`, `model:character varying`, `client_id:bigint`, `warehouse_id:bigint`, `input_hash:character varying`, `input_tokens:integer`, `output_tokens:integer`, `cost_usd:numeric`, `latency_ms:integer`, `status:character varying`, `error:text`, `request_body:jsonb`, `response_body:jsonb`, `actor_type:character varying`, `actor_id:bigint`, `created_at:timestamp with time zone`

### `ai_recommendations`
Typed AI proposals with confidence; applied through domain services by RecommendationService.

`id:bigint`, `use_case:character varying`, `invocation_id:bigint`, `client_id:bigint`, `warehouse_id:bigint`, `subject_type:character varying`, `subject_id:bigint`, `subject_version:character varying`, `proposed_action:jsonb`, `confidence:numeric`, `rationale:text`, `status:character varying`, `decided_by:bigint`, `decided_at:timestamp with time zone`, `decision_note:character varying`, `applied_result:jsonb`, `expires_at:timestamp with time zone`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

### `ai_usage_daily`

`id:bigint`, `client_id:bigint`, `use_case:character varying`, `usage_date:date`, `calls:integer`, `input_tokens:bigint`, `output_tokens:bigint`, `cost_usd:numeric`, `created_at:timestamp with time zone`, `updated_at:timestamp with time zone`

## Reporting views

`v_inventory_summary`, `v_stock_by_lot`, `v_expiring_stock`, `v_location_utilization`, `v_order_cycle_times`, `v_receiving_accuracy`, `v_pick_productivity`, `v_billable_summary`, `v_open_exceptions` — see `database/migrations/2026_01_01_001000_create_reporting_views.php`. Every view exposes `client_id` and `warehouse_id` for uniform scoping; they are the only objects the AI natural-language query can read.