How to Build a Inventory System with Replit

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

1const { db } = require('../db');
2const { stockMovements, stockLevels, products } = require('../../shared/schema');
3const { eq, and, sql } = require('drizzle-orm');
4
5router.post('/api/stock/movements', async (req, res) => {
6  const { productId, fromLocationId, toLocationId, quantity, type, referenceNumber, notes } = req.body;
7
8  if (!quantity || quantity <= 0) {
9    return res.status(400).json({ error: 'Quantity must be a positive integer' });
10  }
11
12  const product = await db.query.products.findFirst({ where: eq(products.id, Number(productId)) });
13  if (!product) return res.status(404).json({ error: 'Product not found' });
14
15  try {
16    // All operations in a single atomic transaction
17    const movement = await db.transaction(async (tx) => {
18
19      // Decrement from-location stock (if applicable)
20      if (fromLocationId) {
21        // WHERE quantity >= qty prevents negative stock
22        const decrementResult = await tx.execute(
23          sql`UPDATE stock_levels
24              SET quantity = quantity - ${Number(quantity)}
25              WHERE product_id = ${Number(productId)}
26                AND location_id = ${Number(fromLocationId)}
27                AND quantity >= ${Number(quantity)}
28              RETURNING quantity`
29        );
30
31        if (decrementResult.rows.length === 0) {
32          throw new Error('Insufficient stock at source location');
33        }
34      }
35
36      // Increment to-location stock (if applicable)
37      if (toLocationId) {
38        await tx.execute(
39          sql`INSERT INTO stock_levels (product_id, location_id, quantity)
40              VALUES (${Number(productId)}, ${Number(toLocationId)}, ${Number(quantity)})
41              ON CONFLICT (product_id, location_id)
42              DO UPDATE SET quantity = stock_levels.quantity + ${Number(quantity)}`
43        );
44      }
45
46      // Log the movement record
47      const [move] = await tx.insert(stockMovements).values({
48        productId: Number(productId),
49        fromLocationId: fromLocationId ? Number(fromLocationId) : null,
50        toLocationId: toLocationId ? Number(toLocationId) : null,
51        quantity: Number(quantity), type, referenceNumber, notes,
52        createdBy: req.user.id
53      }).returning();
54
55      return move;
56    });
57
58    res.json(movement);
59  } catch (err) {
60    if (err.message.includes('Insufficient stock')) {
61      return res.status(409).json({ error: err.message });
62    }
63    res.status(500).json({ error: err.message });
64  }
65});

1// Prompt to type into Replit Agent:
2// Add these routes to server/routes/products.js:
3//
4// GET /api/products — list products with aggregated stock
5//   SELECT p.*, COALESCE(SUM(sl.quantity), 0) AS total_stock,
6//          COUNT(sl.id) AS location_count
7//   FROM products p LEFT JOIN stock_levels sl ON sl.product_id = p.id
8//   WHERE p.is_active = true GROUP BY p.id ORDER BY p.name
9//   Return each product with total_stock and a stock_status:
10//   'healthy' if total_stock >= reorder_point * 2
11//   'low' if total_stock >= reorder_point
12//   'critical' if total_stock < reorder_point
13//
14// GET /api/products/:id — single product with per-location stock breakdown
15//   Join stock_levels with locations to show: location_name, quantity per location
16//
17// POST /api/products — create product
18// PUT /api/products/:id — update product
19// PATCH /api/products/:id/archive — set is_active=false
20//
21// GET /api/locations — list all locations
22// POST /api/locations — create location
23//
24// GET /api/stock/low — low stock report
25//   Query the v_low_stock view
26//   Group by product, show all locations where quantity < reorder_point
27//
28// GET /api/reports/valuation — total inventory value
29//   SELECT p.name, p.sku, p.unit_cost,
30//          SUM(sl.quantity) AS total_quantity,
31//          SUM(sl.quantity) * p.unit_cost AS total_value
32//   FROM products p JOIN stock_levels sl ON sl.product_id = p.id
33//   WHERE p.unit_cost IS NOT NULL
34//   GROUP BY p.id ORDER BY total_value DESC

1// Prompt to type into Replit Agent:
2// Add purchase order routes to server/routes/orders.js:
3//
4// POST /api/purchase-orders — create PO
5//   Body: {supplier_name, items: [{productId, quantity, unitCost}],
6//          expected_date, notes}
7//   Calculate total_cost = SUM(item.quantity * item.unitCost)
8//   INSERT into purchase_orders with status='draft'
9//   Return created PO
10//
11// GET /api/purchase-orders — list POs with status filter
12// GET /api/purchase-orders/:id — detail with items expanded (join product names)
13// PATCH /api/purchase-orders/:id/submit — change status from draft to submitted
14//
15// PATCH /api/purchase-orders/:id/receive — mark PO as received
16//   Body: {location_id} — which location received the inventory
17//   For each item in po.items:
18//     Call the stock movement logic (same atomic UPDATE/INSERT as above) with:
19//       type='received', to_location_id=location_id, quantity=item.quantity
20//   Update PO status to 'received', received_date=now()
21//   Return {success: true, movementsCreated: count}
22//
23// PATCH /api/purchase-orders/:id/cancel — cancel draft or submitted PO
24//   Only allowed if status is 'draft' or 'submitted'
25//   Set status='cancelled'

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