Connecting a POS Terminal to Your Inventory System

-- inventory tables
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  sku TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  price BIGINT NOT NULL, -- in kobo
  stock_quantity INTEGER NOT NULL DEFAULT 0,
  low_stock_threshold INTEGER NOT NULL DEFAULT 10,
  category TEXT,
  barcode TEXT UNIQUE,
  active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE inventory_movements (
  id SERIAL PRIMARY KEY,
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity_change INTEGER NOT NULL, -- negative for sales, positive for restocks
  reason TEXT NOT NULL, -- 'sale', 'restock', 'refund', 'adjustment'
  reference TEXT, -- order ID or restock batch
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

inventory_movements is your audit trail. Every stock change, whether from a sale, restock, refund, or manual adjustment, gets a row. This lets you trace exactly how stock levels arrived at their current value.

stock_quantity on the products table is the current count. It is updated by triggers or application code whenever a movement is recorded. Having it directly on the product row makes lookups fast.

// inventory-deduction.js
async function deductInventoryForOrder(orderId) {
  var client = await db.pool.connect();

  try {
    await client.query('BEGIN');

    // Get order items
    var order = await client.query(
      'SELECT items FROM orders WHERE id = $1 AND status = $2',
      [orderId, 'paid']
    );

    if (order.rows.length === 0) {
      await client.query('ROLLBACK');
      return;
    }

    var items = JSON.parse(order.rows[0].items);

    for (var i = 0; i < items.length; i++) {
      var item = items[i];

      // Deduct stock
      await client.query(
        'UPDATE products SET stock_quantity = stock_quantity - $1, updated_at = NOW() WHERE id = $2',
        [item.quantity, item.product_id]
      );

      // Record movement
      await client.query(
        'INSERT INTO inventory_movements (product_id, quantity_change, reason, reference) VALUES ($1, $2, $3, $4)',
        [item.product_id, -item.quantity, 'sale', 'ORDER-' + orderId]
      );
    }

    // Mark inventory deduction as complete
    await client.query(
      'UPDATE orders SET inventory_deducted = true WHERE id = $1',
      [orderId]
    );

    await client.query('COMMIT');

    // Check for low stock after deduction
    await checkLowStock(items.map(function(i) { return i.product_id; }));

  } catch (error) {
    await client.query('ROLLBACK');
    console.error('Inventory deduction failed for order ' + orderId + ': ' + error.message);
    throw error;
  } finally {
    client.release();
  }
}

Database transaction is critical. If you deduct stock for 3 items and the 3rd fails, you need to roll back all deductions. Partial deductions create inaccurate inventory.

Idempotency. The inventory_deducted flag prevents double deduction if the webhook fires twice or if you retry the deduction after a failure.

// low-stock.js
async function checkLowStock(productIds) {
  var lowStock = await db.query(
    'SELECT id, name, stock_quantity, low_stock_threshold FROM products ' +
    'WHERE id = ANY($1) AND stock_quantity <= low_stock_threshold AND active = true',
    [productIds]
  );

  if (lowStock.rows.length > 0) {
    var message = 'Low stock alert:\n';
    lowStock.rows.forEach(function(product) {
      message += product.name + ': ' + product.stock_quantity + ' remaining (threshold: ' + product.low_stock_threshold + ')\n';
    });

    await notifyManager(message);
  }
}

// Check all products daily for anything that dropped below threshold
async function dailyStockCheck() {
  var lowStock = await db.query(
    'SELECT name, stock_quantity, low_stock_threshold FROM products ' +
    'WHERE stock_quantity <= low_stock_threshold AND active = true ORDER BY stock_quantity ASC'
  );

  if (lowStock.rows.length > 0) {
    await notifyManager('Daily stock report: ' + lowStock.rows.length + ' items below threshold.');
  }
}

// refund-restore.js
async function restoreInventoryForRefund(orderId, refundedItems) {
  var client = await db.pool.connect();

  try {
    await client.query('BEGIN');

    for (var i = 0; i < refundedItems.length; i++) {
      var item = refundedItems[i];

      await client.query(
        'UPDATE products SET stock_quantity = stock_quantity + $1, updated_at = NOW() WHERE id = $2',
        [item.quantity, item.product_id]
      );

      await client.query(
        'INSERT INTO inventory_movements (product_id, quantity_change, reason, reference) VALUES ($1, $2, $3, $4)',
        [item.product_id, item.quantity, 'refund', 'REFUND-ORDER-' + orderId]
      );
    }

    await client.query('COMMIT');
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

Not all refunds restore inventory. If the customer returns damaged goods, you might refund the money but not add the items back to sellable stock. Your refund flow should let the operator choose whether to restock.

Barcode scanners speed up both order building and inventory management. When a product is scanned, look it up by barcode and add it to the order.

// barcode-lookup.js
router.get('/api/products/barcode/:code', async function(req, res) {
  var barcode = req.params.code;

  var product = await db.query(
    'SELECT id, name, price, stock_quantity FROM products WHERE barcode = $1 AND active = true',
    [barcode]
  );

  if (product.rows.length === 0) {
    return res.status(404).json({ error: 'Product not found for barcode: ' + barcode });
  }

  var item = product.rows[0];

  if (item.stock_quantity <= 0) {
    return res.json({
      product: item,
      warning: 'This product is out of stock.',
    });
  }

  return res.json({ product: item });
});

Out-of-stock handling. When a scanned product has zero stock, show a warning to the cashier. Let them decide whether to proceed (perhaps the stock count is wrong) or skip the item.

For the full barcode scanning implementation, see the retail checkout guide.

// reconciliation.js
async function reconcileInventory(productId, physicalCount) {
  var product = await db.query(
    'SELECT stock_quantity FROM products WHERE id = $1',
    [productId]
  );

  var systemCount = product.rows[0].stock_quantity;
  var difference = physicalCount - systemCount;

  if (difference !== 0) {
    // Record the adjustment
    await db.query(
      'UPDATE products SET stock_quantity = $1, updated_at = NOW() WHERE id = $2',
      [physicalCount, productId]
    );

    await db.query(
      'INSERT INTO inventory_movements (product_id, quantity_change, reason, reference) VALUES ($1, $2, $3, $4)',
      [productId, difference, 'adjustment', 'RECON-' + new Date().toISOString().slice(0, 10)]
    );
  }

  return {
    product_id: productId,
    system_count: systemCount,
    physical_count: physicalCount,
    difference: difference,
    adjusted: difference !== 0,
  };
}

Reconcile weekly. Physical counts should happen regularly. The difference between system count and physical count reveals theft, damage, missed scans, or software bugs. Track the difference percentage over time. If it grows, investigate the cause.