Building a Mobile-First Inventory Tracker with Node.js and SQLite using KIRO

Ever needed a simple, effective way to track inventory on the go? I recently built a comprehensive inventory management system that's perfect for small businesses, and I want to share the journey with you. This project combines the simplicity of vanilla JavaScript with the power of Node.js and SQLite to create a mobile-first solution that actually works.

The Problem

Most inventory systems are either too complex for small businesses or lack mobile optimization. I wanted something that:

• Works seamlessly on mobile devices
• Doesn't require complex setup or expensive databases
• Provides real-time stock tracking with movement history
• Can handle bulk imports for existing inventory

Tech Stack Choices

After evaluating various options, I settled on a stack that prioritizes simplicity and effectiveness:

Backend

• Node.js + Express: Fast setup, great ecosystem
• SQLite3: Zero-config database that just works
• JWT + bcryptjs: Simple authentication (ready for future expansion)
• Multer: File upload handling for product images

Frontend

• Vanilla JavaScript: No framework overhead, maximum performance
• Tailwind CSS: Rapid UI development with mobile-first approach
• Font Awesome: Consistent iconography

Database Design

The heart of any inventory system is its data structure. I designed five core tables:

-- Products with SKU-based tracking
CREATE TABLE products (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  sku TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  description TEXT,
  image_url TEXT,
  category_id INTEGER,
  supplier_id INTEGER,
  min_stock INTEGER DEFAULT 0,
  max_stock INTEGER DEFAULT 1000,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Real-time inventory levels
CREATE TABLE inventory (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL DEFAULT 0,
  location TEXT DEFAULT 'Main Warehouse',
  last_updated DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Complete audit trail
CREATE TABLE stock_movements (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  product_id INTEGER NOT NULL,
  movement_type TEXT NOT NULL,
  quantity INTEGER NOT NULL,
  reference TEXT,
  notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

The key insight here is separating current inventory from movement history. This allows for fast queries on current stock while maintaining a complete audit trail.

Mobile-First UI Design

The interface needed to work perfectly on phones since that's where most inventory management happens. Here's how I approached it:

Card-Based Product Display

function renderProducts() {
  const container = document.getElementById('productsGrid');

  container.innerHTML = products.map(product => {
    const stockStatus = getStockStatus(product.stock_quantity, product.min_stock);

    return `
      <div class="bg-white rounded-lg shadow-sm border border-gray-200 overflow-hidden hover:shadow-md transition-shadow duration-200">
        <div class="w-full">
          ${product.image_url ? 
            `<img src="${product.image_url}" alt="${product.name}" class="w-full aspect-square object-cover">` :
            '<div class="w-full aspect-square bg-gray-100 flex items-center justify-center border-2 border-dashed border-gray-300"><i class="fas fa-image text-gray-400 text-2xl"></i></div>'
          }
        </div>
        <div class="p-3">
          <h3 class="text-sm font-medium text-gray-900 truncate">${product.name}</h3>
          <p class="text-xs text-gray-500 bg-gray-100 px-2 py-0.5 rounded mt-1 inline-block">${product.sku}</p>

          <div class="bg-gray-50 rounded p-2 mb-3 text-center">
            <div class="text-xs text-gray-500">Current Stock</div>
            <div class="text-lg font-semibold text-gray-900">${product.stock_quantity || 0}</div>
          </div>

          <button onclick="quickStockAdjust(${product.id}, '${product.name}')" class="w-full inline-flex items-center justify-center px-3 py-1.5 border border-transparent text-sm leading-4 font-medium rounded-md text-white bg-blue-600 hover:bg-blue-700">
            <i class="fas fa-edit mr-2"></i>
            Adjust Stock
          </button>
        </div>
      </div>
    `;
  }).join('');
}

Touch-Friendly Navigation

The navigation uses large touch targets and clear visual hierarchy:

<nav class="bg-white border-b border-gray-200">
  <div class="flex space-x-8 overflow-x-auto">
    <button onclick="showSection('dashboard')" class="nav-item flex flex-col items-center py-4 px-1">
      <i class="fas fa-tachometer-alt text-lg mb-1"></i>
      <span class="text-sm font-medium">Dashboard</span>
    </button>
    <!-- More nav items -->
  </div>
</nav>

Real-Time Stock Management

The core functionality revolves around stock movements. Every adjustment creates both a movement record and updates current inventory:

app.post('/api/stock/adjust', (req, res) => {
  const { product_id, quantity, movement_type, reference, notes } = req.body;

  db.serialize(() => {
    db.run('BEGIN TRANSACTION');

    // Record the movement
    db.run(`INSERT INTO stock_movements (product_id, movement_type, quantity, reference, notes) 
            VALUES (?, ?, ?, ?, ?)`, 
           [product_id, movement_type, quantity, reference, notes]);

    // Update inventory
    const adjustment = movement_type === 'IN' ? quantity : -quantity;
    db.run(`INSERT OR REPLACE INTO inventory (product_id, quantity, last_updated) 
            VALUES (?, COALESCE((SELECT quantity FROM inventory WHERE product_id = ?), 0) + ?, CURRENT_TIMESTAMP)`,
           [product_id, product_id, adjustment], function(err) {
      if (err) {
        db.run('ROLLBACK');
        res.status(400).json({ error: err.message });
      } else {
        db.run('COMMIT');
        res.json({ message: 'Stock adjusted successfully' });
      }
    });
  });
});

This transactional approach ensures data consistency - either both the movement and inventory update succeed, or neither does.

Dashboard Analytics

The dashboard provides key metrics at a glance:

app.get('/api/dashboard/stats', (req, res) => {
  const stats = {};

  db.serialize(() => {
    db.get('SELECT COUNT(*) as total FROM products', [], (err, row) => {
      stats.totalProducts = row ? row.total : 0;
    });

    db.get('SELECT COUNT(*) as low FROM products p JOIN inventory i ON p.id = i.product_id WHERE i.quantity <= p.min_stock', [], (err, row) => {
      stats.lowStockItems = row ? row.low : 0;
    });

    db.get('SELECT SUM(i.quantity) as totalItems FROM inventory i', [], (err, row) => {
      stats.totalItems = row ? (row.totalItems || 0) : 0;
      res.json(stats);
    });
  });
});

Bulk Import Functionality

For businesses with existing inventory, I added bulk import capabilities:

app.post('/api/products/bulk', (req, res) => {
  const { products } = req.body;

  let successCount = 0;
  let errorCount = 0;
  const errors = [];

  db.serialize(() => {
    db.run('BEGIN TRANSACTION');

    products.forEach((product, index) => {
      const { sku, name, description, category_id, supplier_id, initial_stock } = product;

      db.run(sql, [sku, name, description, category_id, supplier_id], function(err) {
        if (err) {
          errorCount++;
          errors.push({ index, sku, error: err.message });
        } else {
          successCount++;
          // Initialize inventory
          const stockQuantity = initial_stock || 0;
          db.run('INSERT INTO inventory (product_id, quantity) VALUES (?, ?)', [this.lastID, stockQuantity]);

          if (stockQuantity > 0) {
            db.run('INSERT INTO stock_movements (product_id, movement_type, quantity, reference, notes) VALUES (?, ?, ?, ?, ?)',
                   [this.lastID, 'IN', stockQuantity, 'BULK_IMPORT', 'Bulk import initial stock']);
          }
        }

        // Commit or rollback based on results
        if (index === products.length - 1) {
          if (errorCount === 0) {
            db.run('COMMIT');
            res.json({ message: `Successfully imported ${successCount} products` });
          } else {
            db.run('ROLLBACK');
            res.status(400).json({ message: `Import completed with errors`, errors });
          }
        }
      });
    });
  });
});

Performance Optimizations

Several techniques keep the app fast:

1. Efficient Queries: Join tables only when necessary
2. Client-Side Filtering: Reduce server requests for search/filter
3. Lazy Loading: Load data sections only when accessed
4. Image Optimization: Fallback handling for broken image URLs

Deployment Considerations

The beauty of this stack is its simplicity:

# Install dependencies
npm install

# Start in development
npm run dev

# Production deployment
npm start

The SQLite database auto-initializes with sample data in development, making it perfect for demos and testing.

Lessons Learned

1. Mobile-first is crucial: Most inventory management happens on phones
2. SQLite is underrated: Perfect for small to medium applications
3. Vanilla JS can be powerful: Sometimes frameworks add unnecessary complexity
4. Transaction integrity matters: Stock movements must be atomic operations
5. User experience trumps features: A simple, fast interface beats feature bloat

What's Next?

Future enhancements could include:

• Barcode scanning integration
• Multi-location inventory tracking
• Advanced reporting and analytics
• Integration with accounting systems
• Offline-first capabilities with sync

Try It Yourself

The complete source code is available, and you can have it running locally in minutes. The combination of Node.js, SQLite, and vanilla JavaScript creates a surprisingly powerful and maintainable inventory system.

Whether you're managing a small retail store, warehouse, or just want to track your personal inventory, this approach provides a solid foundation that can grow with your needs.

---

What inventory management challenges have you faced? I'd love to hear about your experiences and any features you think would be valuable additions to this system.