FIFO Inventory Deduction and LIFO Stock Return

Problem

Inventory is rarely one number. A product can exist across many batches, each with its own quantity, cost, and creation time.

For outbound operations, FIFO keeps deduction predictable: older stock is consumed first. For rollback or return, LIFO is often easier to reason about because the newest deduction is reversed first.

Data model

The core tables are usually:

product
inventory_batch
inventory_movement
inventory_movement_line

The movement line matters because it preserves exactly which batch was affected by each operation. Without that audit trail, a later return has to guess.

Concurrency boundary

FIFO selection and quantity updates should happen inside a single transaction. If two orders select the same batch concurrently, the database must force one request to wait or fail cleanly.

Lesson

Inventory correctness comes from recording the path, not only the final quantity.