Feature 08 — Suggested Replenishment

Phase: 3 Priority: 🟡 Medium Status: ⏳ Pending Depends on: Feature 07 (Collections for filtering), existing inventory, low_stock_alert, purchase_order modules

Context

Low stock alerts, reorder points (reorder_point, safety_stock, lead_time_days, max_stock_level), and purchase order creation all exist. What's missing is a service that:

Calculates sales velocity (units sold per day) per product+location
Applies a replenishment formula to determine suggested order quantities
Presents suggestions in a UI where users can review, edit quantities, and convert to PO drafts

Existing foundation:

inventory table: has reorder_point, safety_stock, lead_time_days, max_stock_level, reorder_quantity
low_stock_alert table + backend: tracks alerts when stock < threshold
apps/backend/src/purchase-orders/ — full PO module; extend to accept suggestions
apps/frontend-pwa/src/components/forms/purchase-order/PurchaseOrderPage.tsx — reference

Velocity approach: Store pre-computed velocity in inventory table (updated by nightly BullMQ job) to avoid expensive real-time JSON unnesting on every suggestion request.

Task Checklist

Database

Create migration: packages/backend/database/src/migrations/2026-03-XX-inventory-velocity.mjs
Alter inventory: add velocity_30d, velocity_90d, last_velocity_updated_at
Run pnpm run migration:local:push
Run pnpm run generate:types

Backend

PWA Frontend

Create apps/frontend-pwa/src/types/replenishment.ts
Create apps/frontend-pwa/src/services/replenishmentService.ts
Create apps/frontend-pwa/src/components/forms/replenishment/ReplenishmentPage.tsx
Register in apps/frontend-pwa/src/pages/MainPage.tsx
(Optional) Add "View Suggestions" shortcut button from Analytics page

Verification

Migration applies cleanly
Types regenerated
Backend builds
Velocity update job runs and populates velocity_30d values
GET /replenishment/suggestions?businessId=X returns suggestions
POST /replenishment/convert-to-po creates PO drafts grouped by supplier
ReplenishmentPage renders suggestion table with editable quantities
"Create PO" creates draft POs visible in PurchaseOrderPage

Database Changes

// packages/backend/database/src/migrations/2026-03-XX-inventory-velocity.mjs
import { Kysely, sql } from "kysely";

export async function up(db) {
  await db.schema.alterTable("inventory")
    .addColumn("velocity_30d", "numeric", (col) => col.defaultTo(0))
    // Units sold per day (30-day rolling average)
    .addColumn("velocity_90d", "numeric", (col) => col.defaultTo(0))
    // Units sold per day (90-day rolling average)
    .addColumn("last_velocity_updated_at", "timestamptz")
    // Timestamp of last velocity recalculation
    .execute();
}

export async function down(db) {
  await db.schema.alterTable("inventory")
    .dropColumn("last_velocity_updated_at")
    .dropColumn("velocity_90d")
    .dropColumn("velocity_30d")
    .execute();
}

Backend Implementation

Replenishment Formula

// For each inventory row where currentStock <= reorderPoint:
suggestedQty = Math.max(
  // Option 1: use configured reorder_quantity
  inventory.reorderQuantity,
  // Option 2: fill up to max stock level
  inventory.maxStockLevel - inventory.quantity,
  // Option 3: cover lead time demand + safety stock buffer
  Math.ceil(
    inventory.velocity30d * inventory.leadTimeDays
    + inventory.safetyStock
    - inventory.quantity
  )
)
// Only suggest if currentStock is at or below reorderPoint
if (inventory.quantity > inventory.reorderPoint) skip;

Service: `replenishment.service.ts`

@Injectable()
export class ReplenishmentService {
  constructor(
    @Inject(DATABASE) private readonly database: KyselyDatabase,
    private readonly purchaseOrdersService: PurchaseOrdersService,
  ) {}

  async getSuggestions(params: {
    businessId: string;
    locationId?: string;
    supplierId?: string;
    categoryId?: string;
    collectionId?: string;
  }): Promise<ReplenishmentSuggestion[]> {
    const rows = await this.database
      .selectFrom("inventory as i")
      .innerJoin("product as p", "p.id", "i.product_id")
      .leftJoin("supplier as s", "s.id", "p.supplier_id")
      .leftJoin("category as cat", "cat.id", "p.category_id")
      .leftJoin("size as sz", "sz.id", "p.size_id")
      .leftJoin("color as col", "col.id", "p.color_id")
      .select([
        "i.id as inventoryId",
        "i.product_id as productId",
        "p.name as productName",
        "p.sku",
        "p.supplier_id as supplierId",
        "s.name as supplierName",
        "i.quantity as currentStock",
        "i.reorder_point as reorderPoint",
        "i.safety_stock as safetyStock",
        "i.lead_time_days as leadTimeDays",
        "i.max_stock_level as maxStockLevel",
        "i.reorder_quantity as reorderQuantity",
        "i.velocity_30d as velocity30d",
        "i.velocity_90d as velocity90d",
        "cat.name as categoryName",
        "sz.name as sizeName",
        "col.name as colorName",
        "p.cost",
      ])
      .where("i.business_id", "=", params.businessId)
      .where("i.is_active", "=", true)
      .$if(!!params.locationId, (qb) => qb.where("i.location_id", "=", params.locationId!))
      .$if(!!params.supplierId, (qb) => qb.where("p.supplier_id", "=", params.supplierId!))
      .$if(!!params.categoryId, (qb) => qb.where("p.category_id", "=", params.categoryId!))
      .where((qb) => qb.where("i.quantity", "<=", this.database.ref("i.reorder_point")))
      .orderBy("i.velocity_30d", "desc")
      .execute();

    return rows.map((row) => ({
      ...row,
      suggestedQty: this.calculateSuggestedQty(row),
    }));
  }

  private calculateSuggestedQty(row: InventoryRow): number {
    const leadTimeDemand = Math.ceil(row.velocity30d * (row.leadTimeDays ?? 7));
    const toMaxStock = (row.maxStockLevel ?? 0) - row.currentStock;
    const toCoverLeadTime = leadTimeDemand + (row.safetyStock ?? 0) - row.currentStock;

    return Math.max(
      row.reorderQuantity ?? 1,
      toMaxStock,
      toCoverLeadTime,
      1, // at least 1
    );
  }

  async convertToPurchaseOrders(
    suggestions: ConvertSuggestionDTO[],
    context: { businessId: string; locationId: string; createdBy: string; currencyId: string; exchangeRate: number }
  ): Promise<PurchaseOrder[]> {
    // Group suggestions by supplierId
    const bySupplier = suggestions.reduce((acc, s) => {
      const key = s.supplierId ?? 'no-supplier';
      if (!acc[key]) acc[key] = [];
      acc[key].push(s);
      return acc;
    }, {} as Record<string, ConvertSuggestionDTO[]>);

    const createdPOs: PurchaseOrder[] = [];

    for (const [supplierId, items] of Object.entries(bySupplier)) {
      const po = await this.purchaseOrdersService.createPurchaseOrder({
        businessId: context.businessId,
        locationId: context.locationId,
        supplierId: supplierId === 'no-supplier' ? undefined : supplierId,
        createdBy: context.createdBy,
        status: PurchaseOrderStatus.DRAFT,
        orderDate: new Date().toISOString(),
        currencyId: context.currencyId,
        exchangeRate: context.exchangeRate,
        purchaseDetail: {
          items: items.map((s) => ({
            productId: s.productId,
            quantity: s.suggestedQty,
            unitCost: s.cost,
            amount: s.suggestedQty * s.cost,
            notes: `Auto-generated from replenishment suggestion`,
          })),
        },
        paymentDetail: { items: [] },
        totalAmount: items.reduce((sum, s) => sum + s.suggestedQty * s.cost, 0),
      });
      createdPOs.push(po);
    }

    return createdPOs;
  }
}

BullMQ Velocity Job: `update-inventory-velocity.job.ts`

// apps/backend/src/queue/jobs/update-inventory-velocity.job.ts
@Processor("inventory-velocity")
export class UpdateInventoryVelocityJob {
  @Process()
  async handle() {
    // For each business, calculate velocity per product+location
    await sql`
      UPDATE inventory i
      SET
        velocity_30d = COALESCE((
          SELECT SUM((item->>'quantity')::numeric) / 30.0
          FROM sale s
          CROSS JOIN LATERAL jsonb_array_elements(s.sale_detail->'items') AS item
          WHERE s.business_id = i.business_id
            AND s.location_id = i.location_id
            AND (item->>'productId')::uuid = i.product_id
            AND s.sale_date >= NOW() - INTERVAL '30 days'
        ), 0),
        velocity_90d = COALESCE((
          SELECT SUM((item->>'quantity')::numeric) / 90.0
          FROM sale s
          CROSS JOIN LATERAL jsonb_array_elements(s.sale_detail->'items') AS item
          WHERE s.business_id = i.business_id
            AND s.location_id = i.location_id
            AND (item->>'productId')::uuid = i.product_id
            AND s.sale_date >= NOW() - INTERVAL '90 days'
        ), 0),
        last_velocity_updated_at = NOW()
    `.execute(this.database);
  }
}

// Register as nightly cron in QueueModule:
// new CronJob('0 2 * * *', () => velocityQueue.add('update', {}));

PWA Frontend

Types: `types/replenishment.ts`

export interface ReplenishmentSuggestion {
  inventoryId: string;
  productId: string;
  productName: string;
  sku?: string;
  supplierId?: string;
  supplierName?: string;
  categoryName?: string;
  sizeName?: string;
  colorName?: string;
  currentStock: number;
  reorderPoint: number;
  velocity30d: number;       // units/day
  velocity90d: number;
  suggestedQty: number;      // system-calculated
  adjustedQty: number;       // user-editable
  cost: number;
  selected: boolean;
}

export interface ConvertToPORequest {
  suggestions: {
    productId: string;
    supplierId?: string;
    suggestedQty: number;
    cost: number;
  }[];
  locationId: string;
  currencyId: string;
  exchangeRate: number;
}

Service: `replenishmentService.ts`

export async function getReplenishmentSuggestions(
  token: string,
  businessId: string,
  filters?: {
    locationId?: string;
    supplierId?: string;
    categoryId?: string;
    collectionId?: string;
  }
): Promise<ReplenishmentSuggestion[]>

export async function convertToPurchaseOrders(
  token: string,
  businessId: string,
  data: ConvertToPORequest,
): Promise<PurchaseOrder[]>

Page: `ReplenishmentPage.tsx` — UI Layout

┌─────────────────────────────────────────────────────────────┐
│  Replenishment Suggestions                  [Refresh]       │
│  Location [Downtown ▼]  Supplier [All ▼]  Category [All ▼] │
├─────────────────────────────────────────────────────────────┤
│  [☑ Select All]  5 items selected                           │
│                                       [Create PO from 5 ▼] │
├────┬──────────────────┬─────┬───────┬────────┬─────────────┤
│ ☑  │ Product          │ SKU │ Stock │ 30d/dy │ Suggest Qty │
├────┼──────────────────┼─────┼───────┼────────┼─────────────┤
│ ☑  │ Blue Shirt / M   │ BS-M│  2    │  3.2   │ [12      ] │
│ ☑  │ Blue Shirt / L   │ BS-L│  0    │  2.8   │ [10      ] │
│ ☐  │ Red Dress / S    │ RD-S│  5    │  1.1   │ [  6      ] │
└────┴──────────────────┴─────┴───────┴────────┴─────────────┘

[Create PO from Selected]
→ Confirmation modal showing POs grouped by supplier:
  Supplier: ABC Textiles (3 items, total $1,240)
  Supplier: XYZ Imports (2 items, total $680)
  [Confirm & Create Draft POs]

Key behaviors:

adjustedQty is user-editable (overrides suggestedQty)
Selecting items and clicking "Create PO" groups by supplierId → one PO per supplier
Created POs are DRAFT status and appear in PurchaseOrderPage
"Refresh" triggers a manual velocity recalculation (debounced, shows last updated timestamp)

Context​

Task Checklist​

Database​

Backend​

PWA Frontend​

Verification​

Database Changes​

Backend Implementation​

Replenishment Formula​

Service: replenishment.service.ts​

BullMQ Velocity Job: update-inventory-velocity.job.ts​

PWA Frontend​

Types: types/replenishment.ts​

Service: replenishmentService.ts​

Page: ReplenishmentPage.tsx — UI Layout​

Context

Task Checklist

Database

Backend

PWA Frontend

Verification

Database Changes

Backend Implementation

Replenishment Formula

Service: `replenishment.service.ts`

BullMQ Velocity Job: `update-inventory-velocity.job.ts`

PWA Frontend

Types: `types/replenishment.ts`

Service: `replenishmentService.ts`

Page: `ReplenishmentPage.tsx` — UI Layout