Retail ERP System Development

Retailers building custom ERP systems need engineering teams who understand the specific data architecture of omnichannel operations: catalogue and variant management, real-time inventory across locations, POS adapter patterns, loyalty ledger design, and the order management logic that keeps online and in-store fulfilment consistent. Scrums.com provides dedicated software engineering teams for retail ERP development, deploying production-ready systems with append-only inventory transaction ledgers, configurable pricing and promotion engines, multi-location stock management, and the analytics infrastructure that supports merchandising and buying decisions.

Product Catalogue, Variant Management, and Pricing Architecture

Products and their variants are modelled separately: the product record carries brand, category_id, and tax_class; each product_variants row carries sku, size, colour, material, weight, dimensions, and barcode. Variants are never deleted: DISCONTINUED is a status on the variant record, preserving purchasing history and barcode traceability. Prices are stored in product_prices with effective_from and effective_to dates; rows are immutable once published, and new prices write new rows: the pricing engine always reads the price effective at transaction time, so a mid-day price change does not retroactively reprice in-progress orders. Multiple price lists (BASE | MEMBER | STAFF | CLEARANCE | PROMOTIONAL) are assigned to customers via customer_price_list_assignments, enabling segment-specific pricing without modifying the base product record. Promotions store eligibility conditions as JSONB in promotion_rules, with type (PERCENTAGE_OFF | AMOUNT_OFF | BOGO | FREE_ITEM | BUNDLE | THRESHOLD_DISCOUNT), priority, max_uses, and validity window; every application writes a promotion_applications row against the order_line for audit. Tax rates are configuration: new rows for jurisdiction or rate changes, with effective dates; tax calculation always uses the rate effective at transaction date.

Inventory Management, Multi-Location Stock, and Replenishment

All stock movements write to inventory_transactions: sku, location_id, transaction_type (RECEIPT | SALE | RETURN | ADJUSTMENT | TRANSFER_OUT | TRANSFER_IN | TRANSFER_IN_TRANSIT | WRITE_OFF), quantity (positive for inbound, negative for outbound), reference_id, and transacted_at. The table is append-only; quantity_on_hand is always SUM(inventory_transactions.quantity) per sku per location, never a mutable counter. At checkout, inventory_holds use SELECT...FOR UPDATE to reserve stock: two simultaneous orders cannot both advance past the reservation without one waiting. Holds carry an expires_at timestamp; a background job releases expired holds by writing HOLD_RELEASE transactions rather than mutating the hold record. Inter-location transfers write TRANSFER_OUT and TRANSFER_IN_TRANSIT atomically at the source; during transit, stock is excluded from available_for_sale at both locations. TRANSFER_IN is written when goods are received at the destination, making the stock available there: stock is never double-counted or invisible during transit. Reorder rules in reorder_rules_config (sku, location_id, reorder_point, reorder_qty, preferred_supplier_id) trigger purchase requisition events automatically; demand forecast snapshots append a new row per sku per period: prior forecasts are never overwritten, enabling forecast accuracy analysis.

Retail ERP apps like these are built and delivered by dedicated engineering teams through our mobile app development service.

Order Management, POS Integration, and Fulfilment Architecture

Orders follow a state machine (CART / CHECKOUT_IN_PROGRESS / PAYMENT_PENDING / PAYMENT_CONFIRMED / PICKING / PACKED / DISPATCHED | COLLECTED / DELIVERED | RETURNED) with every transition appended to order_events. Order lines are immutable once PAYMENT_CONFIRMED; modifications (quantity change, line cancellation) write order_adjustment_events that reference the original order_line, preserving the full change history. Payment events (INITIATED | AUTHORISED | CAPTURED | FAILED | REFUNDED | PARTIALLY_REFUNDED) are append-only: payment records are never mutated; refunds write REFUNDED events and trigger inventory_transactions for restocking. The POS adapter normalises each vendor's transaction format into canonical order and inventory_transaction rows; raw POS payload is stored in pos_inbound_log before transformation, providing the canonical source for reconciliation. Click-and-collect orders move through ORDERED / READY_FOR_COLLECTION / COLLECTED | EXPIRED; after the configured hold period, EXPIRED status triggers an inventory_transaction that returns stock to available without mutating the order record. Returns follow their own state machine (RETURN_REQUESTED / RETURN_RECEIVED / INSPECTED / RESTOCKED | QUARANTINED | DISPOSED), and every transition writes to return_events; RESTOCKED writes an inventory_transaction, QUARANTINED holds stock outside available_for_sale pending a quality decision.

Customer Loyalty, CRM, and Retail Analytics

The loyalty programme is an append-only ledger: each loyalty_ledger row records customer_id, event_type (POINTS_EARNED | POINTS_REDEEMED | POINTS_EXPIRED | ADJUSTMENT), points, reference_id, and transacted_at. The customer's balance is always SUM(loyalty_ledger.points): there is no mutable balance field to corrupt. Points expiry writes POINTS_EXPIRED rows for qualifying balances at the configured expiry window; no existing row is mutated. Customer segments are defined in config (customer_segments with eligibility_rules as JSONB); segment membership is tracked as events (ADDED | REMOVED) rather than mutable flags, so segment membership at any historical date is always derivable. CRM stores every customer interaction (PURCHASE | RETURN | SUPPORT_TICKET | CAMPAIGN_RESPONSE | REVIEW | LOYALTY_REDEMPTION, channel, occurred_at) in an append-only customer_interactions table; a 360-degree customer view is always computed from the interaction log. Analytics materialised views include revenue_by_category_by_location, sell_through_rate_by_sku, stockout_rate_by_sku_by_location, basket_size_by_segment, promotion_lift_by_promotion_id, and return_rate_by_category. Forecast accuracy is computed by comparing forecast_snapshots against actual inventory_transactions: surfaced as MAPE and bias metrics per sku, recomputable from source at any time.