> ## Documentation Index
> Fetch the complete documentation index at: https://docs.hiveku.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Workflow Recipe: Low-Stock Inventory Alerts

> Get notified when product inventory drops below your threshold

Running out of stock means lost sales and disappointed customers. Over-ordering means cash tied up in shelves. A simple alert workflow keeps you on the right side of both.

<Info>
  Before you start: you'll need a `products` or `inventory` table with a stock quantity column, plus a notification channel like Slack, email, or SMS. See [Connect Slack](/how-tos/connect-slack) or [Send Emails](/how-tos/send-emails).
</Info>

## The Flow at a Glance

<CardGroup cols={3}>
  <Card title="Check" icon="magnifying-glass">
    Scan inventory for items below threshold
  </Card>

  <Card title="Alert" icon="bell">
    Notify the purchasing team once per item
  </Card>

  <Card title="Reset" icon="rotate">
    Clear alert when stock is replenished
  </Card>
</CardGroup>

## Step 1: Create the Workflow

<Steps>
  <Step title="Open Workflows">
    In your project, go to **Workflows > New Workflow**. Name it `Low-Stock Alerts`.
  </Step>

  <Step title="Add a Schedule trigger">
    Click **Add Trigger > Schedule**. Set to **every hour**.

    <Tip>
      If you also want instant alerts the moment a sale pushes stock below threshold, add a second trigger on your `orders` table (fires after each purchase). The two triggers complement each other.
    </Tip>
  </Step>
</Steps>

## Step 2: Query for Low-Stock Items

Add a **Database Query** action:

```sql theme={null}
SELECT id, name, sku, stock_quantity, reorder_threshold
FROM products
WHERE stock_quantity <= reorder_threshold
  AND stock_quantity > 0
  AND low_stock_alerted_at IS NULL
```

The `low_stock_alerted_at IS NULL` check prevents spam — once you've alerted on an item, you don't re-alert until stock is replenished and cleared.

<Warning>
  Make sure every product row has a `reorder_threshold` value set. Rows with NULL thresholds silently fail the comparison and never trigger alerts.
</Warning>

## Step 3: Send Slack Notifications

For each low-stock product, add a **Send Slack Message** action:

```
:warning: Low stock alert — {{row.name}} ({{row.sku}})

Current: {{row.stock_quantity}}
Threshold: {{row.reorder_threshold}}

<@U123456> — time to reorder
<https://app.hiveku.com/inventory/{{row.id}}|Update inventory>
```

Tag the person responsible for purchasing with `<@USER_ID>`. Link back to your inventory admin so they can mark a reorder in progress.

## Step 4: Mark as Alerted

After the Slack message sends, update the database:

```sql theme={null}
UPDATE products
SET low_stock_alerted_at = now()
WHERE id = {{row.id}}
```

This is what prevents duplicate alerts. Without it, every hourly tick would re-alert the same low-stock items.

## Step 5: Handle Out-of-Stock Separately

Items that hit zero need different treatment — they're no longer "low," they're gone. Add a second query:

```sql theme={null}
SELECT id, name, sku
FROM products
WHERE stock_quantity = 0
  AND out_of_stock_alerted_at IS NULL
```

Send a more urgent message — maybe via SMS using [Twilio](/how-tos/connect-twilio) — and flag the product as out-of-stock in your UI automatically.

## Step 6: Reset When Restocked

Create a small workflow (or a database trigger) that fires when stock is replenished:

<Steps>
  <Step title="Trigger on inventory update">
    Database trigger on `products` UPDATE when `stock_quantity` goes up.
  </Step>

  <Step title="Check if now above threshold">
    ```sql theme={null}
    IF NEW.stock_quantity > NEW.reorder_threshold THEN
      NEW.low_stock_alerted_at := NULL;
      NEW.out_of_stock_alerted_at := NULL;
    END IF;
    ```
  </Step>
</Steps>

Now when stock drops again, the alert fires fresh.

## Advanced: Velocity-Based Alerts

Fixed thresholds are blunt. A better approach: alert based on days of inventory remaining, using recent sales velocity.

<Steps>
  <Step title="Calculate velocity">
    ```sql theme={null}
    SELECT product_id, COUNT(*) / 30.0 as daily_velocity
    FROM order_items
    WHERE created_at >= now() - interval '30 days'
    GROUP BY product_id
    ```
  </Step>

  <Step title="Compute days remaining">
    `days_remaining = stock_quantity / daily_velocity`
  </Step>

  <Step title="Alert at 14 days, 7 days, 0 days">
    Lead time-aware reorders — the team gets warned before the situation is critical.
  </Step>
</Steps>

## Connecting to E-Commerce Platforms

If you're using Shopify, WooCommerce, or BigCommerce for storefront, pull inventory via their webhooks rather than maintaining a separate table:

* Shopify: subscribe to `inventory_levels/update`
* WooCommerce: Stock change webhook
* BigCommerce: `store/product/inventory/updated`

Mirror the inventory into your Hiveku DB so the alert workflow has a consistent source of truth.

## Multi-Channel Alerts

Different urgency, different channel:

* **Non-urgent low stock:** Slack message to #ops
* **Out of stock:** Slack + email to purchasing manager
* **Bestseller out of stock:** SMS to manager (see [Connect Twilio](/how-tos/connect-twilio))

## Out-of-Stock Page Handling

When a product hits zero, your product page should respond:

* Hide or gray-out the "Add to Cart" button
* Show an "Out of stock" badge
* Offer a "Notify me when available" signup form
* When stock returns, auto-email the waitlist

<Tip>
  The back-in-stock waitlist is one of the highest-converting email lists you'll ever build. Users explicitly asked to know. Send them the moment you have inventory.
</Tip>

## Verify It Worked

<Steps>
  <Step title="Manually lower a product's stock">
    Pick a test product, set `stock_quantity` to 1 below its `reorder_threshold`.
  </Step>

  <Step title="Trigger the workflow">
    Either wait for the hourly run, or hit **Run Now** from the workflow page.
  </Step>

  <Step title="Check Slack">
    You should see the alert message in the configured channel.
  </Step>

  <Step title="Test the reset">
    Update stock back above threshold. The `low_stock_alerted_at` should clear automatically. Lower it again — alert should re-fire.
  </Step>
</Steps>

## Troubleshooting

<AccordionGroup>
  <Accordion title="Too many alerts flooding Slack">
    Raise your `reorder_threshold` values, or switch to a daily digest format — one message listing all low-stock items rather than one per product. Batch the query results into a single Slack message.
  </Accordion>

  <Accordion title="Alerts aren't firing">
    Most common cause: product rows have NULL `reorder_threshold`. Run `SELECT COUNT(*) FROM products WHERE reorder_threshold IS NULL` and backfill. Second most common: the workflow is disabled — check the toggle in the top right.
  </Accordion>

  <Accordion title="Alerts fire but nothing is actually low">
    The `low_stock_alerted_at` flag isn't being cleared on restock. Add the reset logic described in Step 6, or manually clear stale flags: `UPDATE products SET low_stock_alerted_at = NULL WHERE stock_quantity > reorder_threshold`.
  </Accordion>

  <Accordion title="Wrong person tagged in Slack">
    Slack user IDs differ from usernames. Open the user's Slack profile, click the "..." menu, copy the member ID (starts with `U`). Use that in the mention: `<@U123456>`.
  </Accordion>

  <Accordion title="Out-of-stock page still shows product as available">
    Your product page fetches stock at render time but caches. Either disable caching on product pages, or add a stock check at the API layer that runs fresh on every page load.
  </Accordion>
</AccordionGroup>

## What's Next?

<CardGroup cols={2}>
  <Card title="Build Product Pages" icon="store" href="/how-tos/add-product-pages">
    Set up the storefront side of your e-commerce
  </Card>

  <Card title="Send SMS with Twilio" icon="message" href="/how-tos/connect-twilio">
    Wire up SMS for urgent alerts
  </Card>
</CardGroup>