# How to Build an Inventory System with Replit

- Tool: How to Build with Replit
- Difficulty: Intermediate
- Compatibility: Replit Free
- Last updated: April 2026

## TL;DR

Build a warehouse inventory management system in Replit in 1-2 hours. Track product stock across multiple locations, log every movement (received, shipped, transferred, adjusted), create purchase orders, and get low-stock alerts. All stock updates run in atomic PostgreSQL transactions to prevent negative inventory. Uses Express, PostgreSQL with Drizzle ORM, and Replit Auth.

## Before you start

- A Replit account (free tier is sufficient)
- Basic understanding of inventory management concepts (stock, SKUs, purchase orders)
- No external API keys required — everything runs on Replit's built-in PostgreSQL

## Step-by-step guide

### 1. Generate the schema and project with Replit Agent

The stock_levels table is the heart of the system — it stores current quantity per product-location combination. Every stock change goes through a movement record, maintaining a complete audit trail.

```
// Prompt to type into Replit Agent:
// Build an inventory management system with Express and PostgreSQL using Drizzle ORM.
// Create these tables in shared/schema.ts:
// - products: id serial pk, sku text unique not null, name text not null,
//   description text, category text, unit_cost integer (cents),
//   selling_price integer, image_url text,
//   reorder_point integer not null default 10,
//   is_active boolean default true, created_at timestamp
// - locations: id serial pk, name text unique not null, address text,
//   type text default 'warehouse' (warehouse/store/transit)
// - stock_levels: id serial pk, product_id integer references products not null,
//   location_id integer references locations not null, quantity integer not null default 0,
//   last_counted_at timestamp,
//   UNIQUE constraint on (product_id, location_id)
// - stock_movements: id serial pk, product_id integer references products not null,
//   from_location_id integer references locations (nullable for received),
//   to_location_id integer references locations (nullable for shipped),
//   quantity integer not null, type text not null (received/shipped/transferred/adjusted/returned),
//   reference_number text, notes text,
//   created_by text not null, created_at timestamp
// - purchase_orders: id serial pk, supplier_name text not null,
//   items jsonb not null, status text default 'draft' (draft/submitted/received/cancelled),
//   total_cost integer, expected_date timestamp, received_date timestamp,
//   created_by text not null, created_at timestamp
// Create a PostgreSQL view v_low_stock:
//   SELECT p.sku, p.name, p.reorder_point, sl.location_id, l.name AS location_name,
//          sl.quantity FROM stock_levels sl JOIN products p ON p.id = sl.product_id
//          JOIN locations l ON l.id = sl.location_id WHERE sl.quantity < p.reorder_point
// Set up Replit Auth. Bind server to 0.0.0.0.
```

> Pro tip: After Agent generates the schema, run the migration and open Drizzle Studio (database icon in sidebar). Create two test locations and one product, then add a stock_levels row manually to verify the unique constraint on (product_id, location_id) works.

**Expected result:** Agent creates the schema with all tables and the v_low_stock view. Drizzle Studio shows all tables. The view appears under Database → Views.

### 2. Build the atomic stock movement route

The stock movement creation route is the most critical piece of code in the system. It uses a PostgreSQL transaction to atomically decrement from-location stock and increment to-location stock. If stock goes negative, the transaction rolls back.

```
const { db } = require('../db');
const { stockMovements, stockLevels, products } = require('../../shared/schema');
const { eq, and, sql } = require('drizzle-orm');

router.post('/api/stock/movements', async (req, res) => {
  const { productId, fromLocationId, toLocationId, quantity, type, referenceNumber, notes } = req.body;

  if (!quantity || quantity <= 0) {
    return res.status(400).json({ error: 'Quantity must be a positive integer' });
  }

  const product = await db.query.products.findFirst({ where: eq(products.id, Number(productId)) });
  if (!product) return res.status(404).json({ error: 'Product not found' });

  try {
    // All operations in a single atomic transaction
    const movement = await db.transaction(async (tx) => {

      // Decrement from-location stock (if applicable)
      if (fromLocationId) {
        // WHERE quantity >= qty prevents negative stock
        const decrementResult = await tx.execute(
          sql`UPDATE stock_levels
              SET quantity = quantity - ${Number(quantity)}
              WHERE product_id = ${Number(productId)}
                AND location_id = ${Number(fromLocationId)}
                AND quantity >= ${Number(quantity)}
              RETURNING quantity`
        );

        if (decrementResult.rows.length === 0) {
          throw new Error('Insufficient stock at source location');
        }
      }

      // Increment to-location stock (if applicable)
      if (toLocationId) {
        await tx.execute(
          sql`INSERT INTO stock_levels (product_id, location_id, quantity)
              VALUES (${Number(productId)}, ${Number(toLocationId)}, ${Number(quantity)})
              ON CONFLICT (product_id, location_id)
              DO UPDATE SET quantity = stock_levels.quantity + ${Number(quantity)}`
        );
      }

      // Log the movement record
      const [move] = await tx.insert(stockMovements).values({
        productId: Number(productId),
        fromLocationId: fromLocationId ? Number(fromLocationId) : null,
        toLocationId: toLocationId ? Number(toLocationId) : null,
        quantity: Number(quantity), type, referenceNumber, notes,
        createdBy: req.user.id
      }).returning();

      return move;
    });

    res.json(movement);
  } catch (err) {
    if (err.message.includes('Insufficient stock')) {
      return res.status(409).json({ error: err.message });
    }
    res.status(500).json({ error: err.message });
  }
});
```

> Pro tip: The UPDATE ... WHERE quantity >= :qty returning technique is the key to race-condition-free stock management. If two shipments run simultaneously against stock of 5 and both try to ship 5 units, only one succeeds. The other gets an empty RETURNING result, and the transaction rolls back with 'Insufficient stock'.

**Expected result:** POST /api/stock/movements with a transfer from warehouse to store either succeeds with the new quantities or returns 409 if insufficient stock. Check Drizzle Studio to verify the stock_levels table updated correctly.

### 3. Build product and location CRUD routes

Products and locations are the master data that stock_levels references. Include the aggregated total stock query that sums across all locations for the product list view.

```
// Prompt to type into Replit Agent:
// Add these routes to server/routes/products.js:
//
// GET /api/products — list products with aggregated stock
//   SELECT p.*, COALESCE(SUM(sl.quantity), 0) AS total_stock,
//          COUNT(sl.id) AS location_count
//   FROM products p LEFT JOIN stock_levels sl ON sl.product_id = p.id
//   WHERE p.is_active = true GROUP BY p.id ORDER BY p.name
//   Return each product with total_stock and a stock_status:
//   'healthy' if total_stock >= reorder_point * 2
//   'low' if total_stock >= reorder_point
//   'critical' if total_stock < reorder_point
//
// GET /api/products/:id — single product with per-location stock breakdown
//   Join stock_levels with locations to show: location_name, quantity per location
//
// POST /api/products — create product
// PUT /api/products/:id — update product
// PATCH /api/products/:id/archive — set is_active=false
//
// GET /api/locations — list all locations
// POST /api/locations — create location
//
// GET /api/stock/low — low stock report
//   Query the v_low_stock view
//   Group by product, show all locations where quantity < reorder_point
//
// GET /api/reports/valuation — total inventory value
//   SELECT p.name, p.sku, p.unit_cost,
//          SUM(sl.quantity) AS total_quantity,
//          SUM(sl.quantity) * p.unit_cost AS total_value
//   FROM products p JOIN stock_levels sl ON sl.product_id = p.id
//   WHERE p.unit_cost IS NOT NULL
//   GROUP BY p.id ORDER BY total_value DESC
```

**Expected result:** GET /api/products returns products with total_stock and stock_status. A product with 5 units and reorder_point=10 shows status='critical'. GET /api/stock/low returns the same product with location details.

### 4. Add purchase order workflow

Purchase orders track incoming inventory before it arrives. When a PO is received, each line item creates a 'received' stock movement, incrementing stock at the destination location. This keeps the movement history complete.

```
// Prompt to type into Replit Agent:
// Add purchase order routes to server/routes/orders.js:
//
// POST /api/purchase-orders — create PO
//   Body: {supplier_name, items: [{productId, quantity, unitCost}],
//          expected_date, notes}
//   Calculate total_cost = SUM(item.quantity * item.unitCost)
//   INSERT into purchase_orders with status='draft'
//   Return created PO
//
// GET /api/purchase-orders — list POs with status filter
// GET /api/purchase-orders/:id — detail with items expanded (join product names)
// PATCH /api/purchase-orders/:id/submit — change status from draft to submitted
//
// PATCH /api/purchase-orders/:id/receive — mark PO as received
//   Body: {location_id} — which location received the inventory
//   For each item in po.items:
//     Call the stock movement logic (same atomic UPDATE/INSERT as above) with:
//       type='received', to_location_id=location_id, quantity=item.quantity
//   Update PO status to 'received', received_date=now()
//   Return {success: true, movementsCreated: count}
//
// PATCH /api/purchase-orders/:id/cancel — cancel draft or submitted PO
//   Only allowed if status is 'draft' or 'submitted'
//   Set status='cancelled'
```

**Expected result:** Creating a PO with two line items and receiving it creates two 'received' stock movements and increments the corresponding stock_levels rows. GET /api/products now shows updated totals for both products.

### 5. Build the React inventory dashboard and deploy

The dashboard shows the inventory health at a glance: critical products highlighted in red, low-stock warnings in yellow, and healthy products in green. Deploy on Autoscale for cost-effective business-hours usage.

```
// Prompt to type into Replit Agent:
// Build these React components:
//
// 1. InventoryDashboard at client/src/pages/InventoryDashboard.jsx:
//    - On mount: fetch GET /api/products, GET /api/stock/low
//    - Summary cards: Total Products, Total SKUs, Critical Stock Items, Locations
//    - Product grid cards: each card shows:
//      * SKU, product name
//      * Total stock quantity with status indicator dot:
//        green=healthy, yellow=low, red=critical
//      * Reorder point reference
//    - Low Stock Alerts panel: list of products below reorder point
//      with 'Create PO' quick action button
//
// 2. Stock Movement Form modal:
//    - Movement type selector: Receive / Ship / Transfer / Adjust
//    - Product search/select dropdown
//    - Quantity input
//    - For Transfer: from-location and to-location selectors
//    - For Receive: to-location selector only
//    - For Ship: from-location selector only
//    - Reference number input (PO#, invoice #, etc.)
//    - Notes textarea
//    - Submit → POST /api/stock/movements
//
// 3. Movement History page:
//    - Data table with: type badge, product name, quantity, from/to locations, reference, date
//    - Filter by product, location, type, date range
//
// Add SESSION_SECRET to Replit Secrets (lock icon)
// Ensure server binds to 0.0.0.0
// Deploy → Autoscale
```

> Pro tip: Color-code the stock status dots in the product grid: use Tailwind classes bg-green-500 (healthy), bg-yellow-500 (low = below 2x reorder point), and bg-red-500 (critical = below reorder point). This makes at-a-glance triage instant.

**Expected result:** The inventory dashboard loads with product cards and color-coded status indicators. The Low Stock Alerts panel lists products needing reorder. The movement form correctly prevents submitting transfers with insufficient stock.

## Complete code example

File: `server/routes/stock.js`

```javascript
const { Router } = require('express');
const { db } = require('../db');
const { stockMovements, stockLevels, products } = require('../../shared/schema');
const { eq, sql } = require('drizzle-orm');

const router = Router();

router.post('/api/stock/movements', async (req, res) => {
  if (!req.user) return res.status(401).json({ error: 'Auth required' });
  const { productId, fromLocationId, toLocationId, quantity, type, referenceNumber, notes } = req.body;

  if (!quantity || Number(quantity) <= 0) {
    return res.status(400).json({ error: 'Quantity must be a positive integer' });
  }

  const product = await db.query.products.findFirst({ where: eq(products.id, Number(productId)) });
  if (!product) return res.status(404).json({ error: 'Product not found' });

  try {
    const movement = await db.transaction(async (tx) => {
      if (fromLocationId) {
        const dec = await tx.execute(
          sql`UPDATE stock_levels
              SET quantity = quantity - ${Number(quantity)}
              WHERE product_id = ${Number(productId)}
                AND location_id = ${Number(fromLocationId)}
                AND quantity >= ${Number(quantity)}
              RETURNING quantity`
        );
        if (dec.rows.length === 0) throw new Error('Insufficient stock at source location');
      }

      if (toLocationId) {
        await tx.execute(
          sql`INSERT INTO stock_levels (product_id, location_id, quantity)
              VALUES (${Number(productId)}, ${Number(toLocationId)}, ${Number(quantity)})
              ON CONFLICT (product_id, location_id)
              DO UPDATE SET quantity = stock_levels.quantity + EXCLUDED.quantity`
        );
      }

      const [move] = await tx.insert(stockMovements).values({
        productId: Number(productId),
        fromLocationId: fromLocationId ? Number(fromLocationId) : null,
        toLocationId: toLocationId ? Number(toLocationId) : null,
        quantity: Number(quantity), type,
        referenceNumber: referenceNumber || null,
        notes: notes || null,
        createdBy: req.user.id
      }).returning();

      return move;
    });

    res.json(movement);
  } catch (err) {
    const status = err.message.includes('Insufficient stock') ? 409 : 500;
    res.status(status).json({ error: err.message });
  }
});
```

## Common mistakes

- **Updating stock_levels in two separate statements instead of a transaction** — If a transfer decrements the from-location but then the server crashes before incrementing the to-location, stock disappears permanently. Two concurrent transfers can read the same quantity and both succeed, creating phantom stock. Fix: Use db.transaction() as shown. Drizzle's transaction block commits atomically — either both the decrement and increment succeed, or neither does.
- **Allowing quantity to go negative on the from-location** — If stock_levels.quantity is 5 and you try to ship 10 units, a naive UPDATE sets quantity to -5. Negative stock is physically impossible and corrupts all downstream reports. Fix: The UPDATE includes WHERE quantity >= :qty. If the update affects zero rows (stock is insufficient), throw an error and roll back the transaction before the movement record is inserted.
- **Storing stock levels as a sum query on movements instead of a dedicated stock_levels table** — With thousands of movements per product, SELECT SUM(quantity) FROM stock_movements WHERE product_id = :id becomes slow and forces a table scan on every product card load. Fix: Maintain stock_levels as a current-state table. Stock movements update it atomically. The current quantity is always a fast indexed lookup.

## Best practices

- Every stock update must go through the stock_movements route — never UPDATE stock_levels directly from the frontend. The movement log is the audit trail.
- Use PostgreSQL transactions (db.transaction()) for all stock level changes. Never update from-location and to-location in separate SQL statements.
- The v_low_stock view is a real-time low-stock query — no need to cache or schedule it. Query it on every dashboard load.
- Use SKU (not product ID) as the human-visible identifier for products. SKUs are stable across migrations and easy to reference in purchase orders and spreadsheet exports.
- Use Drizzle Studio (database icon in sidebar) to inspect stock_levels during testing. You can see exact quantities per product-location combination and verify movements worked correctly.
- Deploy on Autoscale — inventory management happens during business hours. Scale-to-zero overnight costs nothing on the free tier.

## Frequently asked questions

### What's the difference between this and inventory-tracking-platform?

This inventory-system tracks aggregate product stock quantities across locations — how many units of Product A are at Warehouse 1. The inventory-tracking-platform tracks individual uniquely-identified assets — which specific laptop (serial: ABC123) is assigned to which employee. Use this system for consumable products; use the tracking platform for durable assets.

### How do I handle the opening stock balance when I first set up the system?

Create 'adjusted' stock movements for each product-location combination. Adjusted movements have no from-location (null) and set the initial quantity via the to-location increment. This way the opening balances appear in the movement history like any other stock change.

### Can I track stock for e-commerce and decrement on each sale?

Yes. When a sale is confirmed (e.g., Stripe webhook fires checkout.session.completed), call POST /api/stock/movements with type='shipped', from_location_id pointing to your main warehouse, and quantity from the order. Integrate this into your checkout webhook handler.

### What happens if a physical stock count doesn't match the system?

Create an 'adjusted' movement with the variance quantity. If the system shows 50 units but you count 48, create a movement: type='adjusted', from_location_id=warehouse, quantity=2, notes='Cycle count adjustment 2026-05-01'. This records the discrepancy without deleting history.

### Do I need a paid Replit plan?

No. The free plan includes PostgreSQL, Autoscale deployment, and Replit Auth. The free PostgreSQL tier (10GB) is sufficient for thousands of products and years of movement history.

### How do I generate a stock report for a specific date in the past?

Add a created_at filter to the movements table. To reconstruct stock levels at a past date, query: SELECT SUM(CASE WHEN to_location_id = :loc THEN quantity ELSE -quantity END) AS quantity FROM stock_movements WHERE product_id = :pid AND (from_location_id = :loc OR to_location_id = :loc) AND created_at <= :date. This is the movement-log-as-state approach.

### Can RapidDev help build a custom inventory system for my business?

Yes. RapidDev has built 600+ apps and can add features like barcode scanning, supplier portal, automated PO generation from low-stock alerts, and integration with e-commerce platforms. Book a free consultation at rapidevelopers.com.

---

Source: https://www.rapidevelopers.com/how-to-build-replit/inventory-system
© RapidDev — https://www.rapidevelopers.com/how-to-build-replit/inventory-system