How to Build Agricultural Inventory Management Systems with Node.js and PostgreSQL

Agricultural operations depend on precise inventory tracking. When supply chain disruptions occur—like the recent Del Monte bankruptcy affecting California peach farmers—having a reliable inventory management system becomes critical. This guide walks you through building a production-ready agricultural inventory system using Node.js and PostgreSQL.

Why Developers Need Agricultural Tech Solutions

Farmers managing thousands of crop units need real-time visibility into inventory levels, harvest schedules, and market conditions. A typical operation might track:

• Crop quantities by variety and ripeness stage
• Storage facility capacity and utilization
• Harvest schedules and labor allocation
• Buyer orders and fulfillment status

Building this system requires handling concurrent updates, complex queries, and reliable data persistence—exactly where Node.js and PostgreSQL excel.

Architecture Overview

Your system will consist of:

1. API Layer: Express.js handling REST endpoints
2. Data Layer: PostgreSQL with transactional support
3. Real-time Updates: WebSocket connections for live inventory changes
4. Reporting: Aggregated queries for harvest forecasting

Database Schema Design

Start with a normalized schema that reflects agricultural workflows:

CREATE TABLE crops (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  variety VARCHAR(255),
  planted_date DATE,
  expected_harvest DATE,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE inventory (
  id SERIAL PRIMARY KEY,
  crop_id INTEGER REFERENCES crops(id) ON DELETE CASCADE,
  quantity_units INTEGER NOT NULL,
  unit_type VARCHAR(50), -- 'crates', 'tons', 'bushels'
  ripeness_stage VARCHAR(100), -- 'green', 'mature', 'ripe'
  storage_facility VARCHAR(255),
  last_updated TIMESTAMP DEFAULT NOW(),
  UNIQUE(crop_id, ripeness_stage, storage_facility)
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  crop_id INTEGER REFERENCES crops(id),
  buyer_id INTEGER,
  quantity_requested INTEGER,
  quantity_fulfilled INTEGER DEFAULT 0,
  order_date DATE,
  fulfillment_deadline DATE,
  status VARCHAR(50), -- 'pending', 'partial', 'complete', 'cancelled'
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_inventory_crop ON inventory(crop_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_deadline ON orders(fulfillment_deadline);

The UNIQUE constraint on inventory prevents duplicate stock entries, while the indexes optimize common queries for status checks and deadline tracking.

Express.js API Implementation

Set up endpoints for core inventory operations:

const express = require('express');
const { Pool } = require('pg');
const app = express();

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

app.use(express.json());

// Get inventory for a specific crop
app.get('/api/crops/:cropId/inventory', async (req, res) => {
  try {
    const { cropId } = req.params;
    const result = await pool.query(
      `SELECT 
        i.id, i.quantity_units, i.unit_type, i.ripeness_stage, 
        i.storage_facility, i.last_updated,
        c.name, c.variety
       FROM inventory i
       JOIN crops c ON i.crop_id = c.id
       WHERE i.crop_id = $1
       ORDER BY i.ripeness_stage, i.storage_facility`,
      [cropId]
    );
    res.json(result.rows);
  } catch (error) {
    console.error('Inventory query error:', error);
    res.status(500).json({ error: 'Failed to fetch inventory' });
  }
});

// Update inventory with transaction support
app.post('/api/inventory/:inventoryId/adjust', async (req, res) => {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    
    const { inventoryId } = req.params;
    const { quantity_delta, reason } = req.body;
    
    // Get current inventory
    const current = await client.query(
      'SELECT quantity_units FROM inventory WHERE id = $1 FOR UPDATE',
      [inventoryId]
    );
    
    if (current.rows.length === 0) {
      throw new Error('Inventory record not found');
    }
    
    const newQuantity = current.rows[0].quantity_units + quantity_delta;
    
    if (newQuantity < 0) {
      throw new Error('Insufficient inventory for this adjustment');
    }
    
    // Update inventory
    await client.query(
      `UPDATE inventory 
       SET quantity_units = $1, last_updated = NOW() 
       WHERE id = $2`,
      [newQuantity, inventoryId]
    );
    
    // Log the transaction
    await client.query(
      `INSERT INTO inventory_audit (inventory_id, quantity_delta, reason, created_at)
       VALUES ($1, $2, $3, NOW())`,
      [inventoryId, quantity_delta, reason]
    );
    
    await client.query('COMMIT');
    res.json({ success: true, new_quantity: newQuantity });
  } catch (error) {
    await client.query('ROLLBACK');
    console.error('Transaction error:', error);
    res.status(400).json({ error: error.message });
  } finally {
    client.release();
  }
});

// Get fulfillment status across all orders
app.get('/api/fulfillment-status', async (req, res) => {
  try {
    const result = await pool.query(
      `SELECT 
        c.name,
        COUNT(o.id) as total_orders,
        SUM(CASE WHEN o.status = 'complete' THEN 1 ELSE 0 END) as fulfilled_orders,
        SUM(o.quantity_requested) as total_requested,
        SUM(o.quantity_fulfilled) as total_fulfilled,
        SUM(i.quantity_units) as available_stock
       FROM orders o
       JOIN crops c ON o.crop_id = c.id
       LEFT JOIN inventory i ON c.id = i.crop_id
       WHERE o.status IN ('pending', 'partial')
       GROUP BY c.id, c.name
       ORDER BY c.name`
    );
    res.json(result.rows);
  } catch (error) {
    res.status(500).json({ error: 'Fulfillment status query failed' });
  }
});

app.listen(3000, () => {
  console.log('Agricultural inventory API running on port 3000');
});

Key Implementation Patterns

| Pattern | Use Case | Implementation | |---------|----------|----------------| | FOR UPDATE | Prevent concurrent adjustments | Use row-level locking in transactions | | Audit Trail | Track all inventory changes | Separate audit table with timestamps | | Aggregation Queries | Real-time dashboards | Pre-calculate with indexes on status/date | | Connection Pooling | Handle farm site queries | pg Pool with max 20 connections |

Handling Disruptions: The Supply Chain Angle

When unexpected events occur (bankruptcy, weather, regulatory action), your system should:

1. Flag affected inventory with a status update
2. Recalculate fulfillment based on available stock
3. Notify stakeholders via API webhooks
4. Maintain audit trail of all decisions

Add a column to track such events:

ALTER TABLE crops ADD COLUMN disruption_status VARCHAR(100);
ALTER TABLE crops ADD COLUMN disruption_notes TEXT;

Then query affected crops:

SELECT c.*, 
  SUM(i.quantity_units) as remaining_stock
FROM crops c
LEFT JOIN inventory i ON c.id = i.crop_id
WHERE c.disruption_status IS NOT NULL
GROUP BY c.id;

Performance Optimization

• Index ripeness_stage for rapid sorting during harvest
• Partition orders table by fulfillment_deadline for quarterly queries
• Use MATERIALIZED VIEWS for complex fulfillment reports
• Enable pg_stat_statements to identify slow queries

Testing Your System

Write tests for transaction integrity:

test('concurrent inventory adjustments', async () => {
  // Simulate 10 simultaneous harvest updates
  const promises = Array(10).fill(null).map(() => 
    request(app)
      .post('/api/inventory/1/adjust')
      .send({ quantity_delta: 100, reason: 'harvest' })
  );
  
  const results = await Promise.all(promises);
  const finalQuantity = await getInventoryQuantity(1);
  
  expect(finalQuantity).toBe(1000); // 10 * 100
});

Conclusion

Building agricultural inventory systems teaches you practical database design, transaction handling, and real-world API development. Start with the schema provided, incrementally add features, and deploy on a platform that supports PostgreSQL connections with proper pooling.