Food Inventory Management Software Development

Engineering teams building food inventory management systems for restaurants, food distributors, or food manufacturers face a distinctive set of domain problems: lot traceability that must hold up in a recall, FIFO/FEFO stock rotation that needs to be automatic rather than manual, recipe costing that reconciles theoretical against actual consumption, and expiry management with real-time alerts rather than daily spreadsheet checks. Scrums.com builds dedicated teams for your food inventory domain: engineers who design lot management ledgers, recipe costing workflows, supplier order automation, and food safety compliance infrastructure. Every system is built for companies operating professional food operations at scale.

Ingredient Inventory and FIFO/FEFO Lot Management

Ingredient inventory is modelled as two layers: the item master and the lot record. The inventory_items table defines each ingredient: SKU, category, unit of measure, storage type (AMBIENT, CHILLED, FROZEN), minimum stock level, and reorder point. The lot_records table holds the actual stock: lot ID, item ID, supplier ID, received date, expiry date, quantity received, quantity remaining, cost per unit, and storage location. Every stock movement (receipt, consumption, waste, transfer, or adjustment) writes an append-only row to inventory_movements. On-hand quantity per lot is always derived from this ledger, never stored as a mutable field.

FIFO/FEFO picking is enforced algorithmically at the point of consumption. When a recipe production event or manual consumption fires, the pick algorithm queries lot_records ordered by expiry_date ascending (FEFO) for perishables, or by received_date ascending (FIFO) for ambient items (this preference is a per-item configuration, not a system-wide toggle). The pick writes consumption movements against the oldest eligible lots first, automatically draining them before newer stock is touched. Operators cannot accidentally pick newer stock while older stock sits unused.

Expiry alerts run as a nightly scheduled job: all lots where expiry_date < NOW() + alert_threshold_days are written to an expiry_alert_queue with severity (CRITICAL for items expiring in 24 hours, WARNING for 3 days, WATCH for 7 days). Alerts are grouped by storage location and dispatched to the relevant kitchen manager via the notification channel configured in location_alert_config.

Recipe Management and Theoretical vs Actual Cost Variance

Recipes are versioned records. A recipes table carries recipe ID, name, version number, yield quantity, yield unit, and status (DRAFT, ACTIVE, ARCHIVED). recipe_ingredients are child rows linking each ingredient item with a quantity and unit of measure. When a version is archived, historical production batches retain their reference to that version so cost variance reports remain reproducible. Theoretical cost per portion is calculated from recipe_ingredients joined to the lot cost at the time of production (not the current lot cost), ensuring historical margin analysis is not distorted by later price changes.

Actual consumption is recorded from inventory_movements referencing a production_batch_id. A variance_report view computes: theoretical cost, actual cost, variance amount, and variance percentage per batch. Persistent positive variance (actual consistently exceeding theoretical) surfaces in the kitchen manager dashboard as a cost control signal: typically indicating unmeasured prep waste, recipe drift, or portion size creep. The waste log is a separate table from inventory_movements for reporting clarity: each waste event carries a typed reason (EXPIRED, PREP_WASTE, SPILLAGE, OVERPRODUCTION, RETURNED) and also writes a corresponding movement row to keep the inventory ledger balanced.

Food inventory management systems like these are built and delivered by dedicated engineering teams through our mobile app development service.

Supplier Management and Purchase Order Workflow

Suppliers are configured with lead time in days, payment terms, preferred delivery days, and active status. A purchase_orders table drives the replenishment workflow: PO ID, supplier, status state machine (DRAFT, SUBMITTED, CONFIRMED, PARTIAL_RECEIVED, RECEIVED, CANCELLED), created by, submitted timestamp, and expected delivery date. po_lines are child rows per item with quantity ordered, unit cost, and quantity received. The PO status is derived from the line-level receipts: PARTIAL_RECEIVED when at least one line has been received but others remain outstanding; RECEIVED when all lines are fulfilled.

Goods receipt creates a goods_receipt record referencing the PO and writes a lot_record for each received line: received date, expiry date (scanned from the label or manually entered), cost per unit (from the PO line or corrected on receipt if the supplier invoices differently), and storage location. A three-way match then compares GRN quantity against PO quantity against supplier invoice quantity. Discrepancies flag the PO line for review before the invoice is approved for payment: no payment is released on an unmatched line.

Auto-reorder logic runs as a scheduled job after each inventory movement: for any item where the on-hand balance from the on_hand_by_lot view falls below the configured reorder_point, a draft PO line is created for the preferred supplier at the standard order quantity. Draft POs require planner review and explicit submission (the system never places orders autonomously), but the drafts eliminate the manual effort of identifying what needs to be ordered.

Lot Traceability, Food Safety Compliance, and Integrations

Lot traceability is bidirectional. A forward trace query (given a lot ID, find all production batches and sales events that consumed it) runs from inventory_movements through production_batches to sales records. A backward trace query (given a production batch or sales event, find all input lots) runs the same join in reverse. Both are materialised as indexed views for low-latency recall execution. When a supplier issues a contamination notice, operators enter the affected lot ID and receive the full forward trace within seconds, including which orders were fulfilled from that stock.

Temperature compliance is monitored via a temperature_log table: storage location ID, timestamp, temperature in Celsius, and recorded by (SENSOR for IoT probes, MANUAL for staff checks). The table is append-only. Alert events are written when a reading falls outside the safe range configured per storage type (CHILLED: 1 to 5 degrees Celsius, FROZEN: below -18 degrees Celsius). HACCP checkpoints are scheduled in a haccp_checkpoints table with frequency, last completed timestamp, and next due timestamp. Overdue checkpoints surface in the compliance dashboard without requiring manual tracking.

POS integration receives sale events as webhooks from Square, Toast, or Lightspeed. Each sale event triggers FEFO lot picks and writes inventory_movements automatically; no end-of-day stock reconciliation is required for portioned items. Accounting integration publishes COGS entries from CONSUMPTION movements to Xero or QuickBooks via outbound webhook, with the PO line reference included for cost matching. An integration_log records every outbound message with delivery status and retry count.

Frequently Asked Questions

How does FEFO picking prevent expired stock from being used?

When a consumption event fires, the pick algorithm queries lot_records ordered by expiry_date ascending and writes movements against the earliest-expiring eligible lots first. This is enforced in application logic; operators cannot select a newer lot while an older one remains available. The configuration is per item: perishables use FEFO, ambient items use FIFO, and the preference is stored in the item master rather than hardcoded.

What does lot traceability look like in a real recall scenario?

An operator enters the affected supplier lot ID. The forward trace query traverses inventory_movements through production batches and sales records to produce a list of every order that contained stock from that lot, including customer details if required. The query runs against indexed materialised views and returns results in seconds. The backward trace from any affected order retrieves all input lots for that production batch, supporting multi-ingredient investigation.

How does theoretical vs actual cost variance help kitchen managers?

For each production batch, a variance_report view computes theoretical cost (from recipe ingredients at the production-date lot cost) against actual cost (from recorded inventory movements for that batch). Persistent positive variance (actual consistently exceeding theoretical) is a signal that surfaces in the dashboard. Common causes are unmeasured prep waste, portion size creep, or recipe drift. The report directs investigation rather than requiring managers to manually compare recipe cards to stock usage.

How does POS integration keep inventory accurate without manual stock takes?

Sale events from the POS (Square, Toast, or Lightspeed) arrive as webhooks. Each event triggers the FEFO lot pick algorithm and writes consumption movements to inventory_movements in real time. On-hand balances are always current (derived from the movement ledger), so there is no gap between sales and stock deductions that would require a daily reconciliation run for portioned items.

How does auto-reorder work without placing orders autonomously?

Auto-reorder logic runs as a scheduled job after each inventory movement: for any item where the on-hand balance from the on_hand_by_lot view falls below the configured reorder_point, a draft PO line is created for the preferred supplier at the standard order quantity. Draft POs require planner review and explicit submission (the system never places orders autonomously), but the drafts eliminate the manual effort of identifying what needs to be ordered.