Suggested replenishment

Suggested replenishment is one of the highest ROI features in retail apparel. It directly answers: 👉 What should I buy now? 👉 How much per size/color? 👉 Which store needs it? If you implement this well, FlowPOS becomes a decision system — not just a POS. Here’s what to add for Purchasing / Replenishment → Suggested replenishment. What “Suggested replenishment” means (business meaning) The system proposes purchase quantities based on: current stock sales velocity lead time min/max levels size curve (very apparel specific) seasonality (later) It produces recommended PO lines, not automatic orders (MVP). The core concept: Replenishment = math over signals Minimum signals you need: On hand stock Reserved stock Sales velocity Lead time Target stock level (min/max or days of cover) Without these, suggestions are unreliable. What you must track (data requirements) Inventory signals on_hand qty reserved qty (reservations + layaway) incoming qty (open POs) available_to_sell Demand signals sales last 7/14/30 days average daily sales sales by variant (critical apparel) Supply signals supplier lead time order frequency (weekly/monthly) MOQ (minimum order qty) pack size (very apparel) Planning signals min stock max stock target days of cover size curve rules (Phase 2) Database additions (recommended)

1. Replenishment settings per variant/location This is required. replenishment_setting business_id location_id variant_id min_qty (optional) max_qty (optional) target_days_of_cover (recommended) supplier_id (default supplier) lead_time_days moq pack_size active You don’t need all fields for MVP — but schema should allow them.
2. Derived metrics (not tables, but computed views/services) You’ll compute: avg_daily_sales days_of_stock_remaining suggested_qty These can be materialized later if needed.
3. Optional but powerful: replenishment run snapshot If you want history of suggestions: replenishment_run id location_id executed_at executed_by parameters (JSON) replenishment_run_line run_id variant_id suggested_qty inputs snapshot (stock, velocity, lead_time) MVP can skip history. Suggestion logic (simple MVP formula) Classic retail rule: target_stock = avg_daily_sales * (lead_time_days + safety_days)

suggested_qty = target_stock

• available_to_sell

• incoming_qty Then apply: MOQ rounding pack size rounding never negative Example: If you sell 2/day, lead time 10 days → need ~20 units. This alone is very valuable. Backend functionality you need Core service GET /replenishment/suggestions Inputs: location supplier (optional) timeframe for velocity strategy (min/max vs days-of-cover) Output: list of suggested variants with qty reasons (very important UX) Convert suggestions → PO POST /purchase-orders/from-suggestions This is a huge workflow win. Frontend screens (PWA) MVP screen: Replenishment suggestions Table showing: product / variant on_hand reserved incoming avg daily sales days of stock remaining suggested qty supplier Allow: select lines edit qty create PO This is a flagship screen for FlowPOS. Apparel-specific importance (huge) Replenishment must work at variant level: Not “T-shirt” But: T-shirt / Size M / Black T-shirt / Size L / Black This is where many POS fail. Later you add: size curve logic color performance collection lifecycle But MVP variant-level suggestions already differentiate you. Reports (Metabase) Stockout risk list Items below min stock Replenishment accuracy (suggested vs actual) Supplier lead time reliability Lost sales estimate Fast movers / slow movers This feeds purchasing strategy. MVP vs Phase 2 ✅ MVP simple suggestion formula replenishment settings (lead time + days cover) suggestion screen create PO from suggestions variant-level suggestions 🚀 Phase 2 (big differentiation) size curve replenishment seasonal logic allocation across stores AI demand forecasting supplier constraints optimization auto-replenishment runs omnichannel signals Biggest edge cases (important) New products (no sales history) → fallback rule (min stock) Highly seasonal items Promotions causing velocity spikes Negative stock distortions Returns affecting velocity Multi-location stock sharing Pack size rounding creating overstock These need explicit fallback logic. Architecture insight for FlowPOS (important) This feature requires your system to expose: inventory signals (clean) sales analytics (per variant) purchasing integration Meaning your earlier architecture decisions (ledger, cost history, variants) directly enable this. You’re on the right path.

---

Implementation Details

Database: replenishment_setting table

CREATE TABLE replenishment_setting (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  business_id     UUID NOT NULL,
  location_id     UUID NOT NULL,
  variant_id      UUID NOT NULL,
  product_id      UUID NOT NULL,
  supplier_id     UUID,
  target_days_of_cover  INTEGER,      -- used by days_of_cover strategy
  lead_time_days        INTEGER,      -- supplier lead time override
  min_qty               NUMERIC,      -- reorder point for min_max strategy
  max_qty               NUMERIC,      -- target max for min_max strategy
  moq                   NUMERIC,      -- minimum order quantity
  pack_size             NUMERIC,      -- round up to multiples
  is_active             BOOLEAN DEFAULT true,
  created_by      UUID NOT NULL,
  updated_by      UUID,
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (business_id, location_id, variant_id)
);

Velocity Query

Sales velocity is computed at the product level (not variant level) because sale_detail JSON items do not contain variantId — only productId. The service distributes product-level velocity evenly across variants of the same product.

-- Product-level velocity from sale_detail JSON
SELECT
  item->>'productId' AS “productId”,
  SUM((item->>'quantity')::numeric) AS “totalSold”,
  SUM((item->>'quantity')::numeric) / :windowDays AS “avgDailySales”,
  COUNT(DISTINCT DATE(sale_date)) AS “distinctDays”
FROM sale,
  jsonb_array_elements(
    COALESCE(
      CASE
        WHEN jsonb_typeof(sale_detail::jsonb) = 'array' THEN sale_detail::jsonb
        WHEN jsonb_typeof(sale_detail::jsonb) = 'object'
          AND jsonb_typeof(sale_detail::jsonb->'items') = 'array'
          THEN sale_detail::jsonb->'items'
        ELSE '[]'::jsonb
      END,
      '[]'::jsonb
    )
  ) AS item
WHERE business_id = :businessId
  AND location_id = :locationId
  AND sale_date >= CURRENT_DATE - INTERVAL '1 day' * :windowDays
  AND status <> 'cancelled'
  AND item->>'productId' IS NOT NULL
GROUP BY item->>'productId';

Why sale_detail needs a CASE expression: sale_detail can be stored as either a raw JSON array or as an object {items: [...]}. The CASE handles both formats.

Why product-level, not variant-level: The sale_detail items contain productId but no variantId. Future improvement: add variantId to sale line items for true per-variant velocity.

Why Suggestions May Return Empty

The suggestion engine skips a variant when none of these conditions produce a positive suggestedQty:

Condition	Why it's skipped
inventory.variant_id IS NULL	Only variant-scoped inventory is considered
distinctDays < 7 AND reorderPoint <= 0	Insufficient sales history and no fallback reorder point configured
avgDailySales <= 0 (with >= 7 days history)	No demand signal
min_max: availableToSell >= effectiveReorderPoint	Stock is above reorder point — no replenishment needed
days_of_cover: targetStock - availableToSell - incoming <= 0	Enough stock to cover the target period
suggestedQty <= 0 after MOQ/pack rounding	Rounding didn't produce a positive order
maxStockLevel cap makes qty <= 0	Already at or above max stock

Most common cause in dev/test: All reorder_point = 0 in inventory AND no replenishment_setting rows exist AND fewer than 7 distinct sale days → every variant hits the fallback path and gets skipped.

Fix: Seed replenishment_setting rows (see below) or set reorder_point > 0 on inventory rows.

Seed Query for Dev/Test

Insert replenishment settings for existing inventory variants to make the suggestion engine produce results:

INSERT INTO replenishment_setting (
  id, business_id, location_id, variant_id, product_id,
  target_days_of_cover, lead_time_days, min_qty, max_qty,
  is_active, created_by, updated_by, created_at, updated_at
)
SELECT
  gen_random_uuid(),
  i.business_id,
  i.location_id,
  i.variant_id,
  i.product_id,
  14,   -- target 14 days of cover
  3,    -- 3 day lead time
  5,    -- min qty (reorder point for min_max)
  50,   -- max qty
  true,
  '6c0c4f32-d74a-4a84-892f-3bead447d765',  -- your user id
  '6c0c4f32-d74a-4a84-892f-3bead447d765',
  NOW(), NOW()
FROM inventory i
WHERE i.business_id = '<YOUR_BUSINESS_ID>'
  AND i.location_id = '<YOUR_LOCATION_ID>'
  AND i.variant_id IS NOT NULL
LIMIT 10;

Strategy Resolution

Parameter	Resolution order (first non-null wins)
leadTimeDays	replenishment_setting.lead_time_days → inventory.lead_time_days → 0
supplierId	replenishment_setting.supplier_id → product.supplier_id → null
reorderPoint	replenishment_setting.min_qty → inventory.reorder_point → 0
maxStock	replenishment_setting.max_qty → inventory.max_stock_level → reorderPoint * 2
targetDaysOfCover	replenishment_setting.target_days_of_cover → computed from leadTime + safetyDays

Module Architecture (Hexagonal)

replenishment/
├── replenishment.module.ts                          # NestJS module, DI wiring
├── domain/
│   ├── replenishment-repository.domain.ts           # Port: IReplenishmentRepository + REPLENISHMENT_REPOSITORY token
│   └── replenishment-suggestion.ts                  # Domain types: ReplenishmentSuggestion, SuggestionsResult, etc.
├── application/
│   ├── replenishment.service.ts                     # Use case: compute suggestions, create POs from suggestions
│   └── replenishment-settings.service.ts            # Use case: CRUD for per-variant settings
├── infrastructure/
│   └── replenishment.repository.ts                  # Adapter: Kysely implementation of IReplenishmentRepository
└── interfaces/
    ├── replenishment.controller.ts                  # HTTP adapter with Swagger docs
    ├── query/
    │   └── get-suggestions.query.ts                 # Validated query DTO with ApiProperty
    └── dtos/
        ├── create-replenishment-setting.dto.ts      # Create DTO with ApiProperty
        ├── update-replenishment-setting.dto.ts      # Partial update DTO
        └── create-purchase-orders-from-suggestions.dto.ts  # PO creation DTO

Dependency flow: Controller → Service → Repository (via REPLENISHMENT_REPOSITORY injection token) → Database. Services depend only on the IReplenishmentRepository interface (domain port), never on the concrete Kysely implementation.

API Endpoints

Method	Path	Description
GET	/replenishment/suggestions	Compute and return suggestions (query params: businessId, locationId, strategy, velocityWindow, supplierId, categoryId, minUrgencyDays, sortBy, sortOrder, page, limit)
POST	/replenishment/suggestions/create-purchase-orders	Convert selected suggestion lines into purchase orders grouped by supplier
GET	/replenishment/settings	List active replenishment settings (query params: businessId, locationId, variantId)
POST	/replenishment/settings	Create or upsert a replenishment setting
PATCH	/replenishment/settings/:id	Update a replenishment setting
DELETE	/replenishment/settings/:id	Soft-delete (sets isActive=false)

All endpoints are documented in Swagger under the Replenishment tag and require Bearer authentication. See also the Bruno API collection in api-client/flowpos/collections/replenishment/.

PWA Menu Location

Replenishment is under the Purchase Module in config/firebase/remote-config/pwaMenu.json:

• Path: /forms/ReplenishmentSuggestionsPage
• Icon: component_exchange
• Roles: super, support, owner, admin, accountant

How to Use the Replenishment Suggestions Form (PWA)

1. Select Business and Location — Use the Business and Location selectors in the top-right header. The suggestions API only runs when both are set.
2. Configure strategy and velocity window
   • Strategy: Days of Cover or Min / Max
   • Velocity Window: 7, 14, or 30 days (used to compute average daily sales)
3. Apply optional filters
   • Supplier: All or a specific supplier
   • Category: All or a specific category
   • Urgency (days): Max days of stock remaining (e.g. show only items with ≤ N days left)
4. Review the suggestions table — Columns include product/variant, on hand, reserved, incoming, avg daily sales, days remaining, suggested qty, supplier, and a plain-English reason. Low-history items show an orange "Fallback" badge; velocity spikes show a warning icon.
5. Adjust per-variant settings — Click the gear icon on a row to open the settings modal and override target days of cover, lead time, MOQ, pack size, or preferred supplier for that variant+location.
6. Select lines — Check individual rows or "select all". Edit quantities inline if needed.
7. Create Purchase Orders — Click "Create Purchase Order (N)". The system groups selected lines by supplier, creates one PO per supplier, and shows a summary dialog with supplier name, line count, and total amount.
8. Paginate — Use Previous/Next when there are many suggestions.

Why you might see no backend calls: The suggestions query runs only when token, currentBusiness.id, and currentLocation.id are all set. If the Location selector shows a placeholder (e.g. "Select location") or the business has no locations, the query never fires. Ensure a location is selected in the header and create one if none exist.