The Excel Round Trip That Kept Warehouse Data Safe

Route optimization was the visible trick. Data consistency was the part that could ruin the warehouse.

The same stock tables fed every pick route. If an upload changed A4.12 into A4.2, accepted a negative quantity, or died after half the rows, the router would keep producing confident nonsense. The problem was not exporting a spreadsheet. The problem was letting people correct inventory in Excel without letting a bad file become production truth.

The warehouse team already used Excel for inventory snapshots, printed audit sheets, and corrections after walking the floor. Building a separate data-entry tool would have made the product feel more complete to a software engineer and less useful to the people doing the work. So the system used Excel both ways: click a button to get the current warehouse, edit the file, upload it back. The hard part was making that round trip safe against a live ERP database.

Export: Boring by Design

Exporting is a read. The REST API called one stored procedure, received the current warehouse state, serialized it into a four-column workbook, and returned the file to the browser.

async function exportWarehouseSnapshot(
  repo: WarehouseRepository
): Promise<Buffer> {
  const slots = await repo.getSnapshot();

  return generateExcel(slots, {
    columns: [
      { header: "Position", key: "position" },
      { header: "Item ID", key: "itemId" },
      { header: "Quantity", key: "quantity" },
      { header: "Description", key: "description" },
    ],
    sheetName: "Warehouse Snapshot",
  });
}

The four columns were deliberately boring: position, item ID, quantity, description. The same shape came back during import. Operators did not need to learn a second schema for uploads.

The export path worked early and stayed stable. That is usually a sign that the problem is small. The import path had a much larger blast radius.

What Can Go Wrong on Import

If an operator uploads a spreadsheet with A4.2 when the intended position was A4.12, a route can send them to the wrong shelf. If a negative quantity slips through, inventory becomes nonsense. If the upload fails halfway through, the system can end up showing a warehouse that is neither the old truth nor the new one. These writes go to tables that the route optimizer trusts, so failures have outsized consequences.

I cared about three failure modes:

• Partial writes, where the first 146 rows land and row 147 crashes.
• Bad rows, where a typo or invalid quantity corrupts a real slot.
• Memory pressure, where a large spreadsheet sits inside the API process on a shared 4 GB server.

The import design follows from those risks: stream rows out of the API process, validate before production writes, stage data separately, and apply the final change in one transaction.

Streaming Rows Instead of Buffering Files

The browser uploaded the spreadsheet to the REST API. The API parsed rows and streamed them to a separate warehouse-updater service over gRPC. That service validated rows as they arrived and staged valid data away from production.

message WarehouseRow {
  string position = 1;    // e.g. "A4.2"
  string item_id = 2;     // e.g. "ESP32S3"
  int32 quantity = 3;     // non-negative integer
  string description = 4;
}

message ParseError {
  enum Code {
    INVALID_POSITION_FORMAT = 0;
    NON_NUMERIC_QUANTITY = 1;
    NEGATIVE_QUANTITY = 2;
    EMPTY_ITEM_ID = 3;
  }
  Code code = 1;
  int32 row = 2;
  string detail = 3;
}

message SemanticError {
  enum Code {
    ITEM_NOT_FOUND = 0;
    POSITION_NOT_FOUND = 1;
  }
  Code code = 1;
  int32 row = 2;
  string detail = 3;
}

service WarehouseUpdater {
  rpc UpdateWarehouse (stream WarehouseRow) returns (UpdateResult);
}

gRPC was useful here for a narrow reason: client-side streaming is part of the protocol. The API could send rows one at a time over one connection, and the updater could return a structured result after the stream ended. REST can approximate this with chunked uploads, but protobuf gave me typed rows and typed errors without inventing a framing protocol.

The process boundary mattered too. If the updater crashed, the route API kept serving scans. The worst import failure should be a failed import, not a dead picking station.

Errors Operators Can Fix

I split validation errors into parse errors and semantic errors.

Parse errors meant the row was structurally invalid: malformed position, empty item ID, non-numeric quantity, negative quantity. Semantic errors meant the row was structurally valid but did not match the ERP-backed world: item not found, position not found.

That distinction seems small until someone has to correct 40 rows. A parse error says “fix your spreadsheet.” A semantic error says “this item or position does not exist in the system.” The frontend could map each error code to an Italian message without parsing exception strings.

The service validated the whole file before applying anything. Stopping at the first bad row would have forced operators into a repair loop: upload, fix row 14, upload, fix row 23, upload again. Returning every error at once was more useful, and it kept production untouched until the file was coherent.

The Atomic Swap

Validated rows went into a staging table. The route optimizer, search endpoints, and export path never read from staging. Staging was a quarantine area.

Only after validation passed did a stored procedure apply the change:

BEGIN TRANSACTION

  DELETE FROM InventorySlots
  WHERE position_id IN (
    SELECT position_id FROM StagingInventory
  );

  INSERT INTO InventorySlots (position_id, item_id, quantity)
  SELECT position_id, item_id, quantity
  FROM StagingInventory
  WHERE quantity > 0;

  DELETE FROM StagingInventory;

COMMIT TRANSACTION

This is a swap, not an upsert. The distinction matters because zero had meaning. If a row set a quantity to 0, the system interpreted it as “remove this item from this position.” A naive upsert would update and insert, but it would not naturally delete slots that the spreadsheet intentionally cleared.

The transaction boundary gives the operator a simple mental model. Before commit, everyone sees the old warehouse. After commit, everyone sees the new warehouse. If anything fails, the old warehouse remains. There is no in-between state to explain on Monday morning.

Stored Procedures as the Boundary

The warehouse system read and wrote the same Microsoft SQL Server database used by the ERP. That is usually the sort of coupling architecture advice tells you to avoid. Here it was the smaller risk.

A separate database would have needed synchronization for item masters, order lines, positions, and stock levels. The warehouse router needed fresh ERP data within seconds of a scan. Eventual consistency would have added an entire class of stale-data bugs to a project maintained by one engineer.

Stored procedures became the contract. Application code called procedures with stable names and result shapes. The SQL underneath could change with the ERP schema, while the route optimizer and import pipeline kept consuming typed records.

This design would not generalize to a multi-tenant warehouse product. It fit one company, one ERP, one engineer, and one physical warehouse. That is the recurring theme of the project: the best architecture was the one sized to the actual operating context.

The route optimizer made the system interesting. The Excel round trip made it usable by the people who had to keep the data true.