Skip to main content

Low stock alert and restocking

This workflow periodically monitors the stock level of a SKU, alerts the buyer below the threshold and triggers the supplier order.

Loading the diagram…

The need​

The stock level of a SKU must be monitored periodically to detect stockouts before they happen. Below the threshold, the buyer must be alerted and a supplier order must be placed automatically. As the workflow retriggers at each cycle as long as the stock stays low, the supplier order must not be duplicated.

What the workflow does​

  1. Stock monitoring — the Polling trigger wakes the workflow at regular intervals (inventory.level).
  2. Read the stock — the PostgreSQL node reads the stock quantity for the SKU {{ data.sku }}.
  3. Stock below threshold? — the Condition node compares the read stock to the threshold.
  4. Alert the buyer — if the stock is insufficient, an email warns the buyer.
  5. Order from the supplier — the HTTP(S) Request node creates the supplier order with the restocking quantity.
  6. Handle read failure — if the PostgreSQL node errors, the On Error node takes over, then Alert the technical team sends an email to the internal teams.

What you need​

Construction​

Copy the workflow definition and paste it into the editor (the Trigger node is recreated automatically, it is not in the snippet):

[
  {
    "id": "db-107",
    "name": "Read the stock",
    "config": {
      "type": "DATABASE_POSTGRESQL",
      "query": "SELECT sku, stock FROM inventory WHERE sku = $1",
      "binds": [
        "$.data.sku"
      ],
      "outputKey": "stockResult"
    },
    "parents": [
      "trigger"
    ],
    "position": {
      "x": 0,
      "y": 200
    },
    "next": "cond-107"
  },
  {
    "id": "cond-107",
    "name": "Stock below threshold?",
    "config": {
      "type": "CONDITION",
      "conditionCode": "return Number(context.postgresqlResult.rows[0].stock) <= Number(data.threshold);",
      "conditions": [
        {
          "value": true,
          "next": "email-107"
        },
        {
          "value": false,
          "next": null
        }
      ]
    },
    "parents": [
      "db-107"
    ],
    "position": {
      "x": 0,
      "y": 400
    }
  },
  {
    "id": "email-107",
    "name": "Alert the buyer",
    "config": {
      "type": "SEND_EMAIL",
      "to": [
        "achat@retail-center.fr"
      ],
      "subject": "Low stock: {{ data.sku }}",
      "content": "There are {{ context.postgresqlResult.rows[0].stock }} units left for reference {{ data.sku }}.",
      "sendAsHtml": true
    },
    "parents": [
      "cond-107"
    ],
    "position": {
      "x": 0,
      "y": 600
    },
    "next": "http-107"
  },
  {
    "id": "http-107",
    "name": "Order from the supplier",
    "config": {
      "type": "HTTP_REQUEST",
      "method": "POST",
      "url": "https://api.fournisseurs.fr/purchase-orders",
      "bodyMode": "RAW",
      "body": "{\"sku\": \"{{ data.sku }}\", \"quantity\": \"{{ data.reorderQty }}\"}"
    },
    "parents": [
      "email-107"
    ],
    "position": {
      "x": 0,
      "y": 800
    }
  },
  {
    "id": "error-107",
    "name": "Handle read failure",
    "config": {
      "type": "ON_ERROR",
      "enabledEnvironments": []
    },
    "parents": [],
    "position": {
      "x": -800,
      "y": 0
    },
    "next": "error-email-107"
  },
  {
    "id": "error-email-107",
    "name": "Alert the technical team",
    "config": {
      "type": "SEND_EMAIL",
      "to": [
        "ops@retail-center.fr"
      ],
      "subject": "Stock read failure: {{ data.sku }}",
      "content": "Reading the stock of reference {{ data.sku }} failed.",
      "sendAsHtml": true
    },
    "parents": [
      "error-107"
    ],
    "position": {
      "x": -800,
      "y": 200
    }
  }
]

Check that it works​

The PostgreSQL node must return the stock row read: the condition reads {{ context.postgresqlResult.rows[0].stock }}; adapt the index if the query can return several rows. Deploy the workflow, start an execution and check the traces to verify the alert to the buyer and the supplier order.

Variations​

  • Polling frequency: choose an interval adapted to the product velocity — monitoring every 5 minutes is only useful for fast-moving SKUs.
  • Duplicate order: as long as the supplier order has not been placed, the stock stays low and the workflow retriggers at each cycle — bound the behavior (e.g. an "order in progress" status) to avoid duplicates.

See the reference

See also: Examples and integrations