7 Import and Migration Tooling as a Product

Every customer you close arrives carrying years of data in spreadsheets exported from NuORDER, JOOR, QuickBooks, or a hand-tended Excel file named MASTER_INVENTORY_FINAL_v3 (2).xlsx. NuORDER (now NuORDER by Lightspeed) and JOOR are business-to-business wholesale platforms: the websites apparel brands and retail buyers use to write seasonal orders. QuickBooks is small-business accounting software. Between them they hold most of the data your customer wants to move. The importer is the first feature they ever touch, and its quality decides whether the deal closes and whether onboarding takes two days or two months. This chapter builds a production import pipeline (streaming parse, jsonb staging, mapping templates, validation with repair loops, dry-run diffs, idempotent commits, and ledger-backed rollback) and treats every stage as a customer-facing part of the product. Every one of those terms is defined from the ground up as we reach it, so if the list reads like noise right now, that is expected.

What you need to know first

Almost every bug in this chapter starts with someone assuming a file is friendlier than it is.

Files, bytes, and text encodings

A file is a long sequence of bytes. That is all it is. A byte is a number from 0 to 255, and a one-megabyte file is roughly a million of them in a row. Your hard drive stores numbers and hands them back in order. It has no concept of a spreadsheet. Everything above that ("this is a photo," "this is a customer list") is agreement between the program that wrote the bytes and the program that reads them. When writer and reader disagree, you get corrupted data.

A text encoding is the agreement that maps bytes to letters. If a file holds the byte 65, is that "A"? Only if both sides agree on an encoding that says so. The dominant agreement today is UTF-8, which stores English characters as one byte each and everything else (accents, emoji, Chinese) as two to four bytes. In UTF-8, "é" is the two-byte pair C3 A9. An older family, latin-1 and its Microsoft cousin windows-1252, uses exactly one byte per character. There, "é" is the single byte E9.

Now the failure. Save "Café" as UTF-8 and the bytes are 43 61 66 C3 A9. Hand those to a program assuming windows-1252 and it decodes each byte on its own (C3 is "Ã", A9 is "©"), so it prints "Café". The file was never damaged. The reader applied the wrong agreement.

Garbled text produced this way has a name you will see in bug reports: mojibake. Run the mismatch the other way and you get the replacement character "�", which is destructive, because the original byte is thrown away. And nothing inside a plain text file records which encoding was used. You are told, or you guess.

What CSV and XLSX files really are

A CSV, or comma-separated values, is plain text, and less structured than you think. No cells, no colors, no formulas, no column types: just one line per row, commas between fields, and a first line that is usually the headers. A three-column inventory file is, byte for byte, style,color,qty, a newline, 1234,Navy,12, a newline.

So the obvious way to read it, splitting each line on commas, works right up until a customer named Gap, Inc. arrives. The line 4471,Gap, Inc.,Navy,12 splits into five pieces instead of four: every later field shifts left, and the quantity lands in the color column.

The real format has a rule for this. A field containing a comma is wrapped in double quotes, as in 4471,"Gap, Inc.",Navy,12. A literal quote inside a field is written twice, and a quoted field may even contain a newline. Honoring all that means walking character by character while tracking whether you are inside quotes, which is why you use a real CSV library and never a string split.

An XLSX file is a zip archive in disguise. Rename one to .zip, unzip it, and you get a folder. XLSX is a zip archive of XML documents, XML being a text format of tagged elements nested inside each other, like HTML. One document holds the cells, another (sharedStrings.xml) holds each distinct string once so cells can refer to it by number, another holds formatting.

The consequence: a cell showing "11/1/2023" actually stores the number 45231 plus a style id meaning "display as a date." The screen shows you a rendering of that number. The number is the data.

Parsing, streaming, and the staging dock

Parsing is turning raw text into structured pieces your program can address. Input: the flat characters 1234,Navy,12. Output: something your code can reach into by name, { style: "1234", color: "Navy", qty: "12" }. A parser walks the input tracking where it is (inside a field, inside quotes, at a row boundary) and emits finished pieces as it goes.

Streaming means processing a file in small pieces instead of all at once. It is the difference between drinking from a firehose and filling a cup, emptying it, and filling it again. The naive approach reads the whole file into memory and parses it into an array of rows. A 500,000-row file that is 80 MB on disk expands to many times that once every cell becomes its own string object with its own bookkeeping overhead. If your server is allowed 1 GB, the process dies.

Streaming reads a fixed-size chunk at a time (Node's documented default for a file read stream is 64 KB), parses the complete rows in that chunk, passes them downstream, and then forgets them so the memory is reclaimed. Peak memory is about the same for 500 rows as for 5 million, and the discipline that buys it is absolute: never build a list of all the rows.

A staging table is a loading dock. It is an ordinary database table whose only job is holding an uploaded file's contents exactly as they arrived, before anything has been checked. Nothing goes straight onto the warehouse shelves. Everything comes off the truck at the dock, where you count it, inspect it, and reject what is damaged. Only then do the good pallets go inside.

Staging is disposable: if the customer sends a corrected file, you throw the dock contents away and unload again. And because it holds untouched raw values, you can re-inspect it as often as you like without re-reading the original file. The rest of this chapter builds the dock, the inspection, and the doors.

7.1 The importer is a sales feature

There is an entire commercial category (Flatfile, OneSchema, Dromo) that sells nothing but "CSV import as a product," and what those vendors treat as the bare minimum tells you what to aim at. Their baseline feature set barely varies from product to product:

• An embeddable mapping UI (user interface: the screens a person clicks through) with fuzzy header auto-matching, where "fuzzy" means matched by resemblance rather than exact equality, so their column "Whse" finds your field warehouse_code.
• Mapping memory, so a returning user's second upload maps itself.
• A spreadsheet-like review grid with inline cell editing, find-and-replace, and one-click autofixes.
• Validation hooks that call back into your API (application programming interface: the set of URLs your server exposes for other programs to call).
• Exports of the error rows, annotated with what is wrong.
• And performance that stays interactive on very large files: OneSchema's product page claims it will "validate and transform files of up to 4GB in under 1 second" and is "performant for files up to 20M rows."

Read those as marketing copy rather than measurement, because they are the vendor's own wording as of July 2026, with no published benchmark or test conditions behind them, and no independent party has reproduced them. What they do tell you is the bar the category advertises, which is the bar a prospect will hold you to.

None of that list is hard to build. Every item on it exists to respect the person doing the import, who in apparel wholesale is often an operations manager or an office administrator with no programming background at all.

Why you build the importer instead of buying one

The reason to build rather than buy in an ERP (enterprise resource planning system: the thing you are building) is that your importer must be domain-aware in ways a generic tool cannot be:

• It has to know that a "style" fans out into SKUs (stock-keeping units: the individual sellable variants) by color and size.
• It has to know that inventory quantities must land in the ledger from Chapter 1, the append-only list of stock movements, rather than in a column somebody overwrites.
• It has to know that "Nordstrom #402" names a door, meaning one physical store location belonging to a retail account you already have, so the row should attach to that account instead of opening a fresh customer record.

But the UX (user experience: how the screens feel to use) bar those commercial tools set is your bar. If a prospect has seen Flatfile inside a competitor's product and your importer is "email us the file and we'll run a script," you lose.

7.2 The pipeline shape: staging-first

Every serious import pipeline has the same skeleton, and every stage exists to make a later stage safe:

upload → streaming parse → staging tables (raw, untyped) → column mapping → validation → dry-run diff → idempotent commit → rollback.

The last three names are probably unfamiliar. Each gets built, and defined, in its own section below. For now read them as "show the customer what would happen," "make the change safely twice," and "undo it afterwards."

The load-bearing decision is that nothing before commit touches a live table. Parsed rows land in staging as jsonb, with every cell kept as a text string. A jsonb column is a column type in Postgres (short for PostgreSQL, the database this book uses) that stores a whole JSON value inside one cell.

Here that value is always an object, a bag of named values, like {"Style #": "1234", "Qty": "12"}. Postgres parses it on the way in and stores it in a binary form, so you can query individual keys out of it later and even index them. That is what lets one table hold files with wildly different column sets: the table has a fixed shape, and the varying part lives inside the jsonb.

Staging is disposable. If mapping was wrong, remap. If validation logic changes, revalidate. If the customer uploads a corrected file, replace the batch. You can only offer that freedom because staging holds the raw truth of the file, uncontaminated by business rules or by type coercion. Type coercion is a parser "helpfully" turning text into numbers and dates, which §7.3 shows is the most destructive thing an importer can do.

create table import_batches (
  id            uuid primary key default gen_random_uuid(),
  tenant_id     uuid not null references tenants(id),
  source_system text not null,  -- 'nuorder' | 'joor' | 'quickbooks'
  entity        text not null,  -- 'customers' | 'styles' | 'inventory'
  filename      text not null,
  file_sha256   text not null,
  status        text not null default 'parsing',
    -- parsing → mapped → validated → previewed → committing
    --         → committed → rolled_back
  mapping_template_id uuid references mapping_templates(id),
  row_count     int,
  created_by    uuid not null,
  created_at    timestamptz not null default now(),
  -- re-uploading the very same bytes reuses this batch
  unique (tenant_id, entity, file_sha256)
);

create table import_rows (
  batch_id uuid not null
    references import_batches(id) on delete cascade,
  row_num  int   not null,  -- 1-based position in the source file
  raw      jsonb not null,  -- every cell as TEXT, source headers
  mapped   jsonb,           -- your field names after mapping; text
  errors   jsonb not null default '[]',
    -- [{ "field": "quantity", "code": "not_integer",
    --    "severity": "error", "message": "...", "fix": "..." }]
  action    text,           -- create | update | skip | conflict
  target_id uuid,           -- live entity id when action='update'
  committed_at timestamptz, -- stamped per row ⇒ batch is resumable
  primary key (batch_id, row_num)
);

Reading the batch and row tables

Read those two tables as "the upload" and "the rows in the upload." import_batches gets one row per file a customer sends you. Its id is the batch id: one identifier that every row, every error, and later every committed record carries, so you can always ask "what did this upload do?" and get a complete answer.

Two column types show up repeatedly: uuid is a 128-bit random identifier written as a long hyphenated string, used because two servers can each mint one without coordinating and never collide. timestamptz is a date and time that remembers its time zone.

source_system and entity record where the file came from and what is in it. file_sha256 is a fingerprint of the file's bytes: the same bytes through the SHA-256 hash function always give the same 64-character string, and changing one byte gives a completely different one. status walks the pipeline stages in order, so the UI knows what screen to show.

import_rows gets one row per line of the file. raw holds that line exactly as parsed, keyed by the customer's own headers. mapped holds the same values re-keyed to your field names once mapping has run, still text and still unvalidated. errors accumulates the problems found, each tagged with a severity (§7.5). action and target_id record what the dry run (the rehearsal in §7.5 that works out what would happen without writing anything) decided to do with this row. committed_at is stamped when the row actually lands in a live table.

File hash, row number, and the resumable commit stamp

Three details here do a lot of work. The file_sha256 uniqueness constraint means double-clicking "upload" creates one batch instead of two. Idempotency starts at the front door. (Recall from Chapter 5: an operation is idempotent when doing it twice leaves the world in the same state as doing it once. An idempotent import is one you can run, crash, and re-run without duplicating anything.)

row_num preserves file order, so every error message can say "row 1,847 in your file," which is the coordinate system the customer actually has open in Excel.

And committed_at lives on the row rather than the batch. That is what makes a crashed commit resumable: on restart you simply skip every row that already has a timestamp, and the run picks up where it stopped.

7.3 Streaming parse and encoding hell

CSV in Node: memory is the feature

A 500k-row CSV is maybe 80 MB on disk. Read naively with fs.readFileSync (Node's "load the whole file into memory right now" call) plus a parse-to-array call, it commonly balloons to several times that in the V8 heap. (V8 is the JavaScript engine Node runs on. The heap is the pool of memory it allocates objects from.) Every single cell becomes its own string object with its own bookkeeping overhead, so several hundred megabytes from an 80 MB file is routine, and the exact multiple depends on how many small cells your data has.

That matters because the memory available to your process is capped. On a serverless platform, where your code runs as a short-lived function on someone else's machine instead of on a server you keep running, the cap is explicit. An AWS Lambda function can be configured anywhere from 128 MB to 10,240 MB of memory, in 1 MB steps, as of July 2026. Teams commonly run theirs at 512 MB or 1 GB to keep costs sane.

Cross whatever cap you set and you get an out-of-memory crash on the exact customer whose data volume made them buy an ERP in the first place. That situation, where a process approaches the ceiling of what it is allowed to allocate, is memory pressure. The process either slows to a crawl doing garbage collection (the runtime's automatic reclaiming of objects nothing refers to any more) or gets killed outright.

Backpressure: how a slow consumer slows the file read

So the parse must be a stream from byte one, and a stream with backpressure. Backpressure is how a slow consumer tells a fast producer to wait. If Postgres ingests rows more slowly than the disk reads them, either the pipeline pauses the file read or the difference piles up in memory until you crash. Backpressure is the pause.

Node implements it for you, but only if you connect the pieces with tools that propagate it, which is why the code below uses pipeline(), Node's built-in helper for wiring streams together, rather than a chain of manual .on("data") handlers.

Two terms from the table below, defined up front. A BOM is a byte order mark: an invisible marker, the three bytes EF BB BF, that some programs write at the start of a UTF-8 file to announce "this is UTF-8." Nobody typed it, and if you do not strip it, it glues itself to the front of your first header.

And dedup, short for deduplication, means detecting repeated values and either removing them or making them distinguishable. Here that means two columns both named "Qty."

A good split: PapaParse in the browser for the instant-feedback preview, csv-parse on the server for the authoritative staging load, ExcelJS for xlsx.

Loading staging with COPY, not INSERT

For getting rows into staging, use Postgres COPY rather than row-by-row inserts. An INSERT is a full SQL statement: the server parses the text, plans it, and runs it, once per statement. COPY is a bulk-loading command: you issue it once, then push a firehose of raw delimited lines at the open connection, and Postgres reads them without re-parsing SQL per row.

The PostgreSQL manual is unambiguous about which to reach for: COPY "is optimized for loading large numbers of rows; it is less flexible than INSERT, but incurs significantly less overhead for large data loads," and loading many rows with it "is almost always faster than using INSERT, even if PREPARE is used and multiple insertions are batched into a single transaction."

The same page spells out the ordering for a fresh table: "create the table, bulk load the table's data using COPY, then create any indexes needed for the table." The STDIN in COPY ... FROM STDIN is the piece that makes this work from Node. The COPY reference page defines it plainly. It "specifies that input comes from the client application," and "when STDIN or STDOUT is specified, data is transmitted via the connection between the client and the server."

So your program is the source of the data, not a file path the database server has to be able to see. The pg-copy-streams package (version 7.0.0, published May 2025 and still the current release as of July 2026) wraps that as a writable Node stream and documents using it with node:stream/promises, so the whole path from disk to staging is one backpressured pipeline.

One last term. Cell coercion is when a parser sees the text "00123" and helpfully converts it to the number 123, or sees "TRUE" and hands you a boolean. Most parsers offer it and most people turn it on. Do not: coercion is a one-way, information-destroying guess made before you know what the column means.

import { createReadStream } from "node:fs";
import { Transform } from "node:stream";
import { pipeline } from "node:stream/promises";
import { parse } from "csv-parse";
import { from as copyFrom } from "pg-copy-streams";
import type { PoolClient } from "pg";
import iconv from "iconv-lite";

export async function stageCsv(
  client: PoolClient,
  batchId: string,
  path: string,
  encoding: "utf8" | "win1252",
): Promise<number> {
  let rowNum = 0;

  const parser = parse({
    // strip U+FEFF so column 1 isn't "Style #"
    bom: true,
    columns: (headers: string[]) =>
      dedupe(headers.map((h) => h.trim())),
    // NuORDER exports pad trailing commas inconsistently
    relax_column_count: true,
    skip_empty_lines: true,
    // Deliberately NO `cast` option: every cell stays a string.
  });

  const toCopyRow = new Transform({
    objectMode: true,
    transform(record: Record<string, string>, _enc, cb) {
      rowNum += 1;
      const cell = escCopy(JSON.stringify(record));
      cb(null, `${batchId}\t${rowNum}\t${cell}\n`);
    },
  });

  const copy = client.query(
    copyFrom(
      "COPY import_rows (batch_id, row_num, raw) FROM STDIN",
    ),
  );

  // pipeline() propagates backpressure: if COPY falls behind,
  // the file read pauses instead of buffering in memory.
  await pipeline(
    createReadStream(path),
    iconv.decodeStream(encoding),  // decode BEFORE parsing
    parser,
    toCopyRow,
    copy,
  );
  return rowNum;
}

// COPY text format: escape backslash, tab, newline, and CR.
const escCopy = (s: string) =>
  s.replace(/\\/g, "\\\\").replace(/\t/g, "\\t")
   .replace(/\n/g, "\\n").replace(/\r/g, "\\r");

// "Qty, Qty" happens in real JOOR exports.
// Keep the first, rename the rest, lose nothing.
const dedupe = (headers: string[]) =>
  headers.map((h, i) =>
    headers.indexOf(h) === i ? h : `${h}__${i}`);

Walking the staging pipeline stage by stage

Read the pipeline(...) call in the middle of the function first, because it is the spine and everything above it is just the parts being bolted together. Data flows top to bottom through five stages, and the whole point is that a row exists in memory for microseconds and is then thrown away:

1. Stage one, createReadStream(path), opens the file and emits raw byte chunks (64 KB at a time by Node's documented default) rather than the whole file.
2. Stage two, iconv.decodeStream(encoding), applies the encoding agreement, turning those bytes into JavaScript text as UTF-8 or windows-1252 ("win1252" is one of the names the iconv-lite library accepts for that encoding). Note the comment: decode before parsing. Repairing mojibake afterward is guessing at damage you already caused.
3. Stage three is the CSV parser, which honors quoting rules and emits one plain JavaScript object per row: keys are the headers, values are strings.
4. Stage four, the Transform (a stream that takes something in and passes something else out), takes each object, bumps the counter, turns it into a JSON string, escapes it for the COPY wire format, and emits one tab-delimited line.
5. Stage five is the open COPY connection, which writes that line into import_rows.

Now the memory story. At any instant, the only rows alive are the handful sitting in the small buffers between adjacent stages, and Node's default is 16 objects per object-mode stream. When the Transform calls cb(null, line), it drops its last reference to that record, and the garbage collector reclaims it on the next sweep. Nothing accumulates, which is why heap usage on a 5-million-row file looks the same as heap usage on a 5,000-row file: a flat line rather than a rising ramp.

And if Postgres falls behind, the COPY stream's buffer fills, it stops accepting writes, pipeline() propagates that backward through the transform and the parser, and the disk read pauses until the database catches up. That is backpressure doing its job. The cup empties before you refill it.

What each parser option defuses

Each option in that block answers a defect that turns up in real customer files:

• bom: true strips that invisible marker so your first header is Style # and not Style #.
• The columns callback trims header whitespace and runs the names through dedupe, which leaves the first occurrence alone and renames later ones to Qty__7 (the number is the column's position, counting from zero), so a file with two "Qty" columns does not silently lose one.
• relax_column_count: true tolerates short and long rows instead of aborting the import on row 9,000.
• skip_empty_lines ignores the blank rows Excel leaves at the bottom.
• And the missing cast option is the most important line in the block precisely because it is not there: every value reaches staging as the exact text the file contained.

The helper at the bottom is small but load-bearing. escCopy exists because COPY's text format treats tab, newline, carriage return, and backslash as structural characters, so a customer name containing a tab would otherwise be read as a column break and corrupt the row. Escaping them keeps the payload opaque.

On ordinary hardware you should expect this path to stage a 500k-row file in tens of seconds with flat memory, but treat that as a starting expectation and measure it on your own data rather than quoting it. Below roughly 50k rows, batched multi-row INSERTs are fine and simpler to instrument. Above that, COPY is the difference between "the progress bar moves" and "the tab looks frozen."

XLSX: parse the workbook, never its CSV shadow

Your first instinct will be to tell customers "please save as CSV." Resist it. Excel's CSV export bakes in display formatting, and display formatting is lossy. The specific disaster runs like this.

Scientific notation is a compact way of writing very large or very small numbers: 8.79002E+11 means 8.79002 × 10¹¹, or 879,002,000,000. Microsoft documents that Excel "converts large numbers to scientific notation, like 1.23E+15, in order to allow formulas and math operations to work on them." A number in the default General format flips to that notation once it is too long to print in the column, and how many significant digits survive on screen depends on how wide the column happens to be.

Whatever is on screen is what the CSV export writes. So a 12-digit UPC (the universal product code, the barcode number, 12 digits in its UPC-A form, with the 13-digit EAN-13 as its international superset) sitting in a numeric cell can export to CSV as 8.79002E+11. A UPC of 879002123456 comes back as 879002000000: the last six digits are gone, and the exported file does not contain them anywhere.

The xlsx file itself still holds the full-precision number, because the display format and the stored value are separate things. Parsing the workbook directly recovers exactly the data the CSV export destroys.

Excel's 15-significant-digit ceiling

A related limit bites earlier than people expect, and no parser can undo it. Microsoft documents that "Excel has a maximum precision of 15 significant digits, which means that for any number containing 16 or more digits, such as a credit card number, any numbers past the 15th digit are rounded down to zero."

A 12-digit UPC is safely inside that limit. A long EDI reference (EDI stands for electronic data interchange, the standardized message format large retailers use to send purchase orders and shipping notices) or a 16-digit account number typed into a numeric cell is damaged the moment it is typed, inside the workbook, before any export happens. The only fix is to store such values as text in the first place, which Microsoft's own guidance says as well: "for number codes that are 16 digits or larger, you must use a text format."

You also need to know about the Excel date serial before reading the code. Excel stores a date as a count of days since a fixed starting point, and the cell's format tells it to render that count as a date. Serial 45231 is November 1, 2023. The fractional part, if any, is the time of day, so 0.5 means noon:

import ExcelJS from "exceljs";

// Excel's 1900 date system: serial 1 = 1900-01-01, and serial 60
// is the nonexistent 1900-02-29 — a compatibility choice copied
// from Lotus 1-2-3 (the spreadsheet that dominated the 1980s)
// that Microsoft documents and will not correct. Using
// 1899-12-30 as day zero cancels that phantom day, so every
// serial from 1900-03-01 onward converts correctly.
const EXCEL_EPOCH_MS = Date.UTC(1899, 11, 30);
const MS_PER_DAY = 86_400_000;

export function serialToISODate(serial: number): string {
  const ms = EXCEL_EPOCH_MS + Math.round(serial * MS_PER_DAY);
  return new Date(ms).toISOString().slice(0, 10);
}

export async function* xlsxRows(path: string) {
  const reader = new ExcelJS.stream.xlsx.WorkbookReader(path, {
    sharedStrings: "cache",
    styles: "cache",
    worksheets: "emit",
  });
  let headers: string[] = [];
  for await (const sheet of reader) {
    for await (const row of sheet) {
      // ExcelJS numbers columns from 1, so slot 0 is empty
      const cells = (row.values as ExcelJS.CellValue[]).slice(1);
      if (row.number === 1) {
        headers = cells.map(String);
        continue;
      }
      const record: Record<string, string> = {};
      cells.forEach((v, i) => {
        record[headers[i] ?? `col_${i + 1}`] = cellToText(v);
      });
      yield record;
    }
    break;  // first sheet only — offer the rest as a picker
  }
}

function cellToText(v: ExcelJS.CellValue): string {
  if (v == null) return "";
  // a date-formatted cell arrives as a real Date object
  if (v instanceof Date) return v.toISOString().slice(0, 10);
  // full precision: 879002123456, never 8.79002E+11
  if (typeof v === "number") return String(v);
  if (typeof v === "object") {
    if ("result" in v) return cellToText(v.result);   // formula
    if ("richText" in v)
      return v.richText.map((r) => r.text).join("");
    if ("text" in v) return String(v.text);         // hyperlink
  }
  return String(v);
}

Excel's phantom leap day and the date epoch

Three pieces here. First, date conversion. EXCEL_EPOCH_MS pins day zero at December 30, 1899 rather than the January 1, 1900 you would expect, and the comment explains why: Excel believes 1900 was a leap year, so it has a February 29, 1900 that never existed. Microsoft's own support article confirms the reason. Lotus 1-2-3 made that assumption first: "when Lotus 1-2-3 was first released, the program assumed that the year 1900 was a leap year, even though it actually was not a leap year."

Excel copied it so the two programs would agree on serial numbers, and Microsoft says correcting it now is not worth the damage, because "almost all dates in current Microsoft Excel worksheets and other documents would be decreased by one day." Shifting the epoch back one day cancels the phantom day out for every real date from March 1, 1900 onward. serialToISODate multiplies the serial by 86,400,000 (milliseconds in a day), adds it to the epoch, and slices off the first ten characters to get 2023-11-01.

Streaming rows and forcing every cell to text

Second, xlsxRows, which is a generator function (note the function* and the yield). A generator produces values on demand: the caller asks for the next row, the function runs until it hits yield, hands the row back, and pauses there until asked again. That is streaming expressed as a language feature: the caller can loop over millions of rows while only ever holding one.

Inside, WorkbookReader unzips the xlsx and walks its XML without loading the whole workbook. The "cache" and "emit" settings are ExcelJS's documented options for deciding which parts it keeps and which it hands you as events. The slice(1) exists because ExcelJS numbers columns from 1 while JavaScript arrays start at 0, so position 0 is always empty.

Row 1 becomes the headers. Every later row is zipped against them into an object, with a fallback name like col_4 if a data row runs wider than the header row. The break stops after the first worksheet, deliberately, because silently importing sheet 3 is worse than asking.

Third, cellToText applies the anti-coercion rule to Excel's much messier cell values. A cell can hand you back null, a real Date object (when the cell was date-formatted), a plain number, a formula object carrying its last computed result, a rich-text object holding an array of styled runs, or a hyperlink object with display text. Every branch funnels down to a string. The number branch is the one that matters commercially: String(879002123456) gives you all twelve digits, exactly the value Excel's own CSV export would have thrown away.

ExcelJS's streaming WorkbookReader is the free-license path to bounded memory. SheetJS Community Edition takes the other approach: its XLSX.stream.* helpers produce output (CSV, JSON, HTML) incrementally, while reading a workbook builds the entire thing in memory first, which caps how many concurrent imports one server can run. Keep SheetJS around anyway, because in Node it is the practical reader for the legacy binary .xls files that older desktop accounting and warehouse programs still produce, which ExcelJS does not read at all.

The encoding gauntlet

Five defects account for nearly all "your importer corrupted my data" tickets, and every one must be handled before validation ever sees a value:

UTF-8 BOM. Excel prepends EF BB BF to "CSV UTF-8" exports. Unstripped, your first header is "Customer", with an invisible character welded to the front, which silently fails every mapping lookup, because "Customer" !== "Customer". csv-parse's bom: true handles it. Handle it yourself for any other entry path.

Latin-1 / windows-1252. QuickBooks Desktop and older ERPs export windows-1252. Decoded as UTF-8, Décolleté becomes D�collet�. Accented characters run right through an apparel catalog, so this defect turns up on the very first import rather than the hundredth.

Sniff before decoding: honor a BOM if present, else attempt a strict UTF-8 decode of the first 64 KB and fall back to windows-1252 on failure. ("Strict" means the decoder throws an error on any byte sequence that is not legal UTF-8, instead of quietly substituting "�". That thrown error is your signal.) Trim the last three bytes of the sample first, so you do not fail merely because your 64 KB window sliced a multi-byte character in half.

Excel date serials. A numeric cell holding 45231 may be November 1, 2023. Convert with the 1899-12-30 epoch shown above. The fractional part is time of day. Never "detect dates" by guessing on number ranges alone, because a quantity of 45231 is a perfectly plausible number. Use the cell's date format where the reader exposes it, and stage the original serial too, so a wrong guess is recoverable.

Leading zeros. ZIP 07305 typed into a numeric Excel cell was stored as 7305 at data-entry time, and the zero is unrecoverable from the file, because it was never in the file. This is why parse must not coerce, and why validation, rather than parsing, owns the repair: flag it, propose padStart(5, "0") as a one-click fix, let the human confirm.

Scientific-notation SKUs. The CSV-shadow problem above. If the customer only has the mangled CSV, the honest answer is a blocking error that says exactly this: "These UPCs were damaged by Excel before export. Re-export as .xlsx or re-pull from NuORDER." A precise diagnosis of an unfixable file builds more trust than silently importing garbage.

7.4 Column mapping and saved templates

The mapping screen answers one question, "your column, our field," and its job is to answer it before the user has to. Two mechanisms get you there.

First, a header fingerprint. Take the header list of each successfully committed batch, normalize it (lowercase, trimmed), sort it, and hash it into a single short string. Files from the same source system produce the same fingerprint every time. When the next upload's fingerprint matches a saved template, the mapping applies itself and the user sees a filled-in screen with a "looks right?" confirmation. This is the "mapping memory" that commercial importers treat as their headline feature, because the second import is the one where the customer stops dreading the tool.

Second, per-header fuzzy suggestion against an alias dictionary, which you grow from every mapping any customer confirms. After a few onboardings, "Whse", "Loc", and "DC" (distribution center: the warehouse a retailer ships into) all suggest warehouse_code on their own.

create table mapping_templates (
  id uuid primary key default gen_random_uuid(),
  -- NULL tenant_id = a global template you ship to everyone
  tenant_id     uuid,
  source_system text not null,
  entity        text not null,
  name          text not null,
  -- sha256 of the sorted, lowercased, trimmed header list
  header_fingerprint text not null,
  mappings jsonb not null,
    -- { "Style #": { "field": "style_number" },
    --   "Qty":     { "field": "quantity" },
    --   "Ship To Zip": { "field": "postal_code",
    --                    "transform": "zip_pad5" } }
  created_at timestamptz not null default now()
);

-- Per-header suggestion via pg_trgm over an alias dictionary.
-- Aliases accumulate from every mapping a customer confirms.
-- $1 = the incoming header, $2 = the entity being imported.
select fa.canonical_field,
       max(similarity(fa.alias, lower(trim($1)))) as score
from field_aliases fa
where fa.entity = $2
  and fa.alias % lower(trim($1))   -- trigram index makes this fast
group by fa.canonical_field
order by score desc
limit 3;

What the template table stores

The table stores a reusable answer to the mapping question. tenant_id being nullable is the interesting part: a row with a real tenant id is one customer's saved mapping, and a row with NULL is a template you ship to everybody, so a brand-new prospect uploading a standard NuORDER export gets a pre-filled screen on their very first try.

header_fingerprint is the hash described above, and it is what you look up by. mappings is a jsonb object whose keys are the customer's header names and whose values say which of your fields they feed, plus, optionally, a named transform to apply along the way.

Suggesting a field for each unmatched header

The query below it handles the case where no template matches and you must guess header by header. First, a piece of SQL notation you will meet everywhere from here on: $1 and $2 are placeholders. You send the query text once with those markers in it and hand the actual values across separately, so the database never confuses a customer's data with SQL commands.

$1 here is the incoming header (say " Whse "), trimmed and lowercased. field_aliases is your accumulated dictionary of "people have called this field these things." The % operator asks "are these two strings similar enough?" and similarity() turns that resemblance into a number between 0 and 1. The trigram index that makes both fast works by indexing every three-character slice of every alias.

group by fa.canonical_field with max(...) means that if warehouse_code has ten aliases and three resemble the input, the field appears once, scored by its best alias. Sorting by that score and taking three fills the dropdown, best first. The user picks one, their confirmation becomes a new alias, and the next customer's guess gets better.

Ship global templates for the big source systems. NuORDER, JOOR, and QuickBooks exports each use a stable, repeatable column layout, so one template per system covers most files you will ever see from it. A prospect who uploads a NuORDER style export and watches it map itself with zero clicks has just watched your best demo. Store transforms in the template alongside field names (unit conversions, zip_pad5, "split Color/Size on slash") so a template captures everything needed to make that source system's files land clean, repeatably.

7.5 Validation and the dry-run diff

Validation reads mapped, writes errors, and touches nothing else. It is a pure inspection pass over the loading dock.

The design decision that matters is severity tiers. A severity tier is a label attached to each problem saying how badly it blocks you. Two tiers are enough. An error stops the row from committing at all. A warning lets the row through but stays visible in the review grid so a human can decide.

Get this boundary wrong in either direction and you lose the customer. Block on a missing email address and they cannot import their customer list at all. Only warn on a malformed UPC and you have quietly corrupted the SKU catalog that ATS (available-to-sell: the quantity you can still promise a buyer), orders, and invoicing all key off. The rule of thumb: anything that feeds an identifier, a join, or the inventory ledger blocks. Anything cosmetic warns.

type Severity = "error" | "warning";

interface RowError {
  field: string;
  code: string;
  severity: Severity;
  message: string;  // written for the ops manager
  fix?: string;     // machine-applicable one-click repair
}

type Rule = (m: Record<string, string>) => RowError | null;

const err = (
  field: string, code: string, message: string,
): RowError =>
  ({ field, code, severity: "error", message });

const warn = (
  field: string, code: string, message: string, fix?: string,
): RowError =>
  ({ field, code, severity: "warning", message, fix });

export const inventoryRules: Rule[] = [
  (m) => m.style_number?.trim()
    ? null
    : err("style_number", "required",
        "Style number is missing, so this row can't be "
        + "matched to a style."),

  (m) => {
    // Excel writes thousands separators: "1,250"
    const q = (m.quantity ?? "").trim().replace(/,/g, "");
    if (!/^-?\d+$/.test(q))
      return err("quantity", "not_integer",
        `"${m.quantity}" isn't a whole number.`);
    if (Number(q) < 0)
      return err("quantity", "negative_qty",
        "Negative on-hand. If this is a returns file, "
        + "import it as adjustments instead.");
    return null;
  },

  (m) => {
    const zip = (m.postal_code ?? "").trim();
    return /^\d{3,4}$/.test(zip)
      ? warn("postal_code", "zip_lost_zeros",
          `"${zip}" looks like a ZIP that lost a leading zero.`,
          zip.padStart(5, "0"))
      : null;
  },
];

How the validation rules are shaped

The shape here is deliberately boring, because boring code is the kind a hurried colleague extends correctly at 6pm. A Rule is a function that takes one mapped row and returns either a RowError or null meaning "fine." Validation is then a loop: run every rule against every row and collect what comes back. Adding a check means adding a function to an array, with no framework and no configuration language.

err and warn are small helpers that stamp the severity, so the tier is visible at a glance in each rule rather than buried in an options object. Each error carries which field is at fault (so the UI can highlight that cell), a stable machine-readable code (so you can count how often not_integer fires across all customers), a message written for a human, and optionally a fix, the corrected value, ready to apply with one click.

Walk the three rules:

1. The first is presence: a missing or whitespace-only style_number is an error, because without it the row cannot be attached to anything.
2. The second handles quantity, and note the order of operations: it strips commas first, because Excel exports "1,250" with the thousands separator baked in, and rejecting that as "not a number" would be your bug rather than the customer's. It then tests the cleaned string against a pattern meaning "an optional minus sign followed by one or more digits," and rejects negatives separately with a message telling the user what to do instead.
3. The third rule acts on a suspicion it cannot prove: a three- or four-digit postal code. Almost certainly it is a ZIP whose leading zero Excel dropped, because the ZIP codes starting with 0 cover New England, New Jersey, and Puerto Rico, so 07305 (Jersey City, New Jersey) becomes 7305. But you cannot prove that from the file, so the rule warns instead of blocking and attaches zip.padStart(5, "0") as the proposed repair. The human sees "07305?" and clicks yes.

The dry run and its diff

Once every row carries its verdict, the dry run begins. A dry run is a rehearsal: you execute the decision logic completely, but write nothing to live tables. You record only what would happen. The output is a diff, short for difference: a summary of how the file's contents differ from what is already in the database.

Each clean row gets stamped with an action:

• create (nothing like this exists yet),
• update (it exists and this file changes it),
• skip (it exists and is already identical), or
• conflict (something is ambiguous and a human must decide).

Conflicts include the case dry runs exist to catch: two rows in the same file claiming the same natural key. A natural key is the identity a record already has in the real world: an account number, a style number, a UPC.

Contrast a surrogate key, which is the meaningless unique id (typically a uuid) your database invents for its own bookkeeping. Surrogate keys are great for joins and useless for matching an incoming file, because the customer's spreadsheet has never heard of them. Natural keys are how you recognize that row 12 and row 4,001 are the same customer twice.

The property that makes the rehearsal trustworthy is that the dry run runs the same resolution code as the commit, so its summary is a prediction the commit will honor exactly. That summary ("312 new customers, 1,204 updates, 40 unchanged, 3 conflicts") is the moment the customer decides whether they trust you with their data.

with clean as (
  select row_num,
         mapped->>'account_number' as account_number,
         mapped->>'customer_name'  as customer_name,
         count(*) over (
           partition by mapped->>'account_number'
         ) as dupes_in_file
  from import_rows
  where batch_id = $1
    and not exists (
      select 1 from jsonb_array_elements(errors) e
      where e->>'severity' = 'error')
),
verdict as (
  select cl.row_num, c.id as target_id,
         case
           when cl.dupes_in_file > 1 then 'conflict'
           when c.id is null         then 'create'
           when c.name is distinct from cl.customer_name
             then 'update'
           else 'skip'
         end as action
  from clean cl
  left join customers c
    on c.tenant_id = $2
   and c.account_number = cl.account_number
)
update import_rows r
set action = v.action, target_id = v.target_id
from verdict v
where r.batch_id = $1 and r.row_num = v.row_num;

Reading the dry-run query

This is one statement built from two named intermediate results, the with ... as (...) blocks, which you can read as temporary views that exist only for the duration of this query.

The clean block selects the rows eligible to commit. mapped->>'account_number' is jsonb syntax: reach into the object and pull out that key as text. The not exists filter is the severity tier being enforced: it expands the row's errors array into one row per error and excludes this row if any of them is severity error. Warnings pass through untouched. The count(*) over (partition by ...) is a window function: for each row it counts how many rows in the batch share the same account number. A value greater than 1 means the file itself contains a duplicate.

The verdict block does the matching. The left join looks for an existing customer in this tenant with the same account number, the natural key. "Left" means rows with no match are kept, with c.id coming back NULL, which is precisely the signal for "create."

The case ladder then decides in priority order: duplicated inside the file → conflict; no live match → create; live match whose name differs → update; otherwise → skip. It uses is distinct from rather than <> deliberately, because in SQL any comparison involving NULL yields NULL instead of true or false. is distinct from treats NULL as an ordinary value and gives you a real answer.

Finally, the update writes each verdict back onto its staging row. Live tables are untouched. What you now have is a complete, row-by-row rehearsal sitting in staging, ready to be summarized on screen and, if approved, executed.

7.6 Fuzzy matching: four spellings of Nordstrom

Exact natural keys settle the diff when they exist. Customer names never cooperate. The same retailer arrives as Nordstrom, Nordstrom Inc., NORDSTROM #402, and Nordstorm, and if each becomes its own customer record, the account's order history, credit terms, and AR (accounts receivable: the money customers owe you) aging shatter across four ghosts.

The fix is a two-layer pipeline: deterministic normalization first, probabilistic trigram similarity second, and a human decision for everything in between. Normalization means rewriting a value into a canonical form so that things which ought to be equal actually are equal as strings. It erases the variance that carries no information: casing, punctuation, legal suffixes (Inc, LLC, Ltd), and door numbers (#402 is a location attribute, so it does not belong in the identity). That alone collapses three of the four spellings into the single string nordstrom.

Trigrams and the similarity score

The typo, Nordstorm, survives normalization, because it is genuinely a different string. Catching it requires fuzzy matching: comparing strings by degree of resemblance instead of by equality. Postgres ships an extension for that, pg_trgm. Learn how it scores strings before you trust its answers.

A trigram is any three consecutive characters. The docs spell out the padding rule: "Each word is considered to have two spaces prefixed and one space suffixed when determining the set of trigrams contained in the string," so the start and end of a word count too. The extension then computes a similarity score by counting the trigrams two strings share and dividing by the number of distinct trigrams across both of them together. The docs describe the result as running from "zero (indicating that the two strings are completely dissimilar) to one (indicating that the two strings are identical)."

Concretely: similarity('nordstrom','nordstorm') is 6/14, about 0.43. Each word yields ten trigrams once padded. They share their opening run — the two padded leading trigrams plus nor, ord, rds, dst — but the transposed "ro"/"or" destroys every trigram after it, so six of the fourteen distinct trigrams are common. That 0.43 sits comfortably above the extension's default match threshold of 0.3, while genuinely different retailers score near zero (nordstrom against saks fifth avenue shares no trigrams at all, scoring 0).

A GIN index (generalized inverted index: a structure that maps each individual trigram back to the rows containing it) with gin_trgm_ops lets Postgres visit only the rows sharing trigrams with your search string, instead of reading every row in the customer table.

The pg_trgm docs support two index types here: GIN, and GiST (generalized search tree: a second, more flexible index framework Postgres ships). GIN is the usual pick for plain similarity lookups. GiST is the one to reach for if you want the database itself to return results already ordered by how close they are, which the docs note "can be implemented quite efficiently by GiST indexes, but not by GIN indexes."

create extension if not exists pg_trgm;

-- 1. lowercase        2. strip door numbers like "#402"
-- 3. strip legal suffixes   4. punctuation → space
-- 5. squeeze repeated spaces and trim the ends
create or replace function norm_company(name text) returns text
language sql immutable parallel safe as $$
  select trim(regexp_replace(
    regexp_replace(
      regexp_replace(
        regexp_replace(lower(name), '#\s*\d+', ' ', 'g'),
        '\m(inc(orporated)?|llc|ltd|limited'
          || '|corp(oration)?|co(mpany)?)\M\.?', ' ', 'g'),
      '[^a-z0-9]+', ' ', 'g'),
    '\s+', ' ', 'g'))
$$;

alter table customers
  add column name_norm text
  generated always as (norm_company(name)) stored;

create index customers_name_norm_trgm
  on customers using gin (name_norm gin_trgm_ops);

-- Candidate lookup for one incoming row.
-- $1 = tenant id, $2 = the raw name from the spreadsheet.
select c.id, c.name,
       similarity(c.name_norm, norm_company($2)) as score
from customers c
where c.tenant_id = $1
  -- indexed; % uses pg_trgm.similarity_threshold
  and c.name_norm % norm_company($2)
order by score desc
limit 5;

Reading the normalization function

Read norm_company from the inside out, because the replacements are nested. Each one uses a regular expression: a compact pattern language for describing text to find.

1. Innermost: lower(name) lowercases everything, so NORDSTROM and Nordstrom stop differing.
2. Then '#\s*\d+' deletes a hash followed by digits, killing the door number #402.
3. Then the legal-suffix pattern removes standalone words like inc or llc. The \m and \M are word-boundary markers, which is what stops it from mangling a company genuinely named "Incognito." (That pattern is written as two quoted pieces joined by ||, SQL's string-concatenation operator, purely so the line fits on the page. Postgres sees one pattern.)
4. Then '[^a-z0-9]+' replaces every run of non-alphanumeric characters with a single space, disposing of commas, periods, and ampersands.
5. Finally the outer replacement collapses runs of spaces into one and trim removes the edges.

All four spellings except the typo now normalize to exactly nordstrom.

The function is declared immutable, meaning "same input always gives the same output, with no side effects." That promise is what allows the next statement: name_norm is a generated stored column, so Postgres computes the normalized name on every insert and update and keeps it on disk. You never maintain it, and it can never drift out of sync with name. The GIN index over that column keeps trigram lookups fast against millions of customers.

The matcher query and the similarity threshold

The final query is the matcher, run once per incoming row. $2 is the raw spreadsheet name, normalized with the same function so both sides are comparable. The % operator is the fuzzy equals. The docs define it as returning true "if its arguments have a similarity that is greater than the current similarity threshold set by pg_trgm.similarity_threshold," and with the GIN index present Postgres uses the index rather than scanning.

The similarity(...) call re-computes the exact score so you can display and rank on it. c.tenant_id = $1 keeps everything inside one tenant, meaning one customer's slice of the database. It is a hard requirement from Chapter 4, and a spectacular data leak if you forget it. Sorted descending and capped at five, this hands a human the best candidates.

The threshold is the dial that decides how much of this is automatic. Too low, and every incoming name drags in unrelated candidates until the review queue is noise. Too high, and Nordstorm, at 0.43, is never compared to Nordstrom at all, so you silently create a fifth ghost account. The documented default of 0.3 catches our typo. Whether it also catches things you did not want is an empirical question about your own data.

Banding scores into automatic and human decisions

Then band the scores into decisions:

• Exact match on name_norm (or on a hard key like account number): auto-link, no ceremony.
• Score above roughly 0.9: auto-link but record it, so it is auditable.
• The middle band — roughly 0.4 to 0.9, tuned against your own corpus — goes to a match-review queue: a screen showing the incoming name beside each candidate with its score, recent order history, and city (the strongest disambiguator humans have), with three buttons: Same customer, New customer, Decide later. Persist every decision keyed by (batch_id, row_num, candidate_id), and feed confirmed pairs back into the alias dictionary so the same vendor's next file resolves automatically.
• Below the band: create a new record without asking.

Never auto-merge in the ambiguous band. A wrong merge silently mixes two retailers' AR balances, and unpicking that weeks later, after orders and payments have been applied to the merged record, is among the worst incidents an ERP can have.

7.7 Repair loops: designing for the non-technical fixer

Validation's output opens a loop (fix, revalidate, shrink the error list, commit) performed by someone who lives in Excel. Three mechanics cover the space. One of them needs a name first: round-tripping means sending data out of your system in some format, letting someone edit it elsewhere, and taking it back in: a round trip out and home. It is convenient, and it is where data goes to get quietly damaged, because the outside tool applies its own rules on the way through.

Ship all three, and get the round-trip right

Ship all three. They cover different situations rather than overlapping. The grid handles the typo-and-confirm cases. The round-trip handles "I need to look these 40 style numbers up in our old system." Partial accept keeps the onboarding alive while both happen.

Two implementation notes on the round-trip, because it is where quality shows. Export errors in the customer's original column layout — headers they recognize, their column order — with your annotations in clearly-prefixed extra columns. And export as .xlsx with identifier columns explicitly typed as text, so Excel cannot re-strip the zeros the customer just restored.

That second point is the round-tripping hazard in miniature: you hand out a correct ZIP code, Excel decides it is a number, and it comes back broken through no fault of the user. OneSchema advertises exactly this feature. Its site says "users can download annotated Excel files with their errors highlighted," so customers coming from that world will expect it.

7.8 Idempotent commit, resume, and rollback

The commit's contract: running it once, running it twice, or crashing at row 40,000 and re-running must all converge to the same final state.

Everything hangs on natural keys, the identity a row has in the customer's world, independent of your surrogate uuids:

• For customers it is the source system's account number (or, failing that, the normalized name).
• For styles, (tenant_id, style_number).
• For SKUs, (tenant_id, style_number, color_code, size_code) or the UPC.

Declare them as unique constraints, and upserts become mechanically safe. An upsert is a single statement that inserts a row if it is new and updates (or ignores) it if a row with that key already exists: "update or insert."

In Postgres you write it as insert ... on conflict (key) do update or do nothing, and the database resolves the race atomically, meaning the whole check-and-write happens as one indivisible step, so two concurrent imports cannot both decide the row is missing:

-- One resumable chunk (~5k rows per transaction). Crash
-- mid-batch and re-run: committed_at filters out finished
-- work, and ON CONFLICT absorbs any row that landed before
-- the crash but after our last committed_at stamp.
-- $1 = batch id, $2 = tenant id.
begin;

insert into customers
  (tenant_id, account_number, name, city, import_batch_id)
select $2,
       mapped->>'account_number',
       mapped->>'customer_name',
       mapped->>'city',
       $1
from import_rows
where batch_id = $1
  and action = 'create'
  and committed_at is null
order by row_num
limit 5000
on conflict (tenant_id, account_number) do nothing;

update import_rows set committed_at = now()
where (batch_id, row_num) in (
  select batch_id, row_num from import_rows
  where batch_id = $1
    and action = 'create'
    and committed_at is null
  order by row_num
  limit 5000);

commit;

Chunked, resumable commit

This is one chunk of work, and you run it in a loop until no rows remain. The begin/commit pair wraps both statements in a single transaction (Chapter 1): either both take effect or neither does, so you can never stamp rows as committed without having actually inserted them.

The insert ... select reads up to 5,000 not-yet-committed staging rows whose dry-run verdict was create, pulls the values out of the mapped jsonb, and inserts them into customers. Every inserted customer carries import_batch_id (that stamp is what makes rollback possible later), and order by row_num keeps the work deterministic, so a re-run processes the same rows in the same order.

The two safety mechanisms work at different layers. committed_at is null is the resume filter: rows finished by an earlier attempt are not selected again. But a crash can land after the insert and before the stamp, and for that narrow window on conflict (tenant_id, account_number) do nothing discards the re-inserted row instead of raising a duplicate-key error. Belt and braces. The chunk size of 5,000 keeps each transaction short, which matters: a transaction holding locks for ten minutes blocks other work, and a crash inside it discards everything it had done.

The second statement stamps exactly the rows the insert just handled, using the same filter and ordering. Loop until a pass processes zero rows. The commit is crash-resumable at every boundary.

Why inventory is the dangerous case

Customers and styles are the easy case, because upserting them twice is naturally harmless: the second run finds the row and does nothing. Inventory is the dangerous case. If commit adds quantities to a stock column, a double-run doubles on-hand, ATS inflates, sales reps oversell thousands of nonexistent units, and you spend a week issuing apology credits. Chapter 1's ledger design is what saves you here: quantities are never mutated, only appended as movements. So idempotency reduces to giving each movement a deterministic identity derived from the batch row, and letting a unique index turn replays into no-ops:

create unique index inv_moves_import_once
  on inventory_movements (import_batch_id, batch_row_num)
  where import_batch_id is not null;

-- $1 = batch id, $2 = tenant id. import_rows has no tenant
-- column of its own; it inherits one from its batch.
insert into inventory_movements
  (tenant_id, sku_id, warehouse_id, qty_delta, reason,
   import_batch_id, batch_row_num)
select $2, s.id, w.id,
       (r.mapped->>'quantity')::int,
       'import_opening_balance',
       r.batch_id, r.row_num
from import_rows r
join skus s
  on s.tenant_id = $2
 and s.sku_code  = r.mapped->>'sku_code'
join warehouses w
  on w.tenant_id = $2
 and w.code      = r.mapped->>'warehouse_code'
where r.batch_id = $1
  and r.action = 'create'
  and r.committed_at is null
on conflict (import_batch_id, batch_row_num)
  -- must repeat the partial index's predicate
  where import_batch_id is not null
  do nothing;

One movement per batch row, forever

The index is the whole trick. It declares that the pair (batch id, row number) may appear at most once in the movements table, so "row 1,847 of batch X" can produce exactly one inventory movement, forever, no matter how many times the commit runs. The movement's identity comes from where it came from, not from when it was created. The where import_batch_id is not null clause makes it a partial index: it covers only import rows, so the millions of ordinary movements from daily shipments, receipts, and adjustments do not bloat it.

The insert does the real work. For each eligible staging row it joins to skus on the SKU code and to warehouses on the warehouse code, both scoped to the tenant id passed in as $2, translating the customer's text codes into your internal ids. The tenant comes in as a parameter because import_rows deliberately has no tenant column of its own. It inherits one from the batch it belongs to, so there is exactly one place that fact is recorded.

Rows whose codes do not exist are dropped by the join, which is why validation must already have checked those references, or an unknown warehouse code becomes silent data loss with no error anywhere.

(r.mapped->>'quantity')::int pulls the quantity out of the jsonb as text and casts it to an integer. This is the first and only place the value stops being text, and it is safe precisely because validation proved it parses. reason = 'import_opening_balance' records in the ledger where the movement came from.

The on conflict clause turns a re-run into a no-op, and it must repeat the where predicate so Postgres knows which partial index you mean. Run this statement five times and the ledger still contains one movement per row. That single index is the difference between a safe retry and a warehouse full of imaginary stock.

Rollback as a compensating transaction

The same architecture is what makes rollback honest. Rollback here means undoing an import that has already been committed. That is a different operation from the transaction-level ROLLBACK in Chapter 1, which only throws away work that was never committed in the first place. This one is a business-level undo of data that is already live. You cannot simply DELETE an import after the fact: the business kept moving, and orders may already allocate against imported stock.

The mechanism is a compensating transaction, the accounting answer to "how do you undo something you are not allowed to erase." Instead of deleting the original entry, you append a second, equal-and-opposite entry that cancels it out. The net effect on the balance is zero, and both the mistake and the correction remain visible in the history. That is exactly how a ledger is supposed to behave.

So rollback becomes a first-class operation: append compensating movements that negate the batch's movements (idempotently, via a reverses_movement_id guard), archive created entities that nothing references yet, and flag for human review the ones that are already referenced. The batch flips to rolled_back, and the audit trail shows both the mistake and its correction, which is exactly what an auditor, and a nervous new customer, want to see.

begin;

insert into inventory_movements
  (tenant_id, sku_id, warehouse_id, qty_delta, reason,
   reverses_movement_id)
select m.tenant_id, m.sku_id, m.warehouse_id,
       -m.qty_delta, 'import_rollback', m.id
from inventory_movements m
where m.import_batch_id = $1
  -- skip anything a previous rollback already reversed
  and not exists (
    select 1 from inventory_movements r
    where r.reverses_movement_id = m.id);

update customers c set archived_at = now()
where c.import_batch_id = $1
  and not exists (
    select 1 from orders o where o.customer_id = c.id);

update import_batches set status = 'rolled_back'
where id = $1;

commit;

Reading the rollback statements

Three statements, one transaction. The first is the compensating entry, and the arithmetic is the whole idea: select every movement belonging to this batch, insert a mirror image with -m.qty_delta. A movement of +40 units gets a partner of −40. Nothing is deleted. The ledger simply nets to zero for that batch. Each reversal records reverses_movement_id = m.id, pointing back at exactly the movement it cancels, and the not exists subquery uses that pointer to skip movements already reversed. That is what makes rollback itself idempotent: run it three times and each movement still has exactly one reversal.

The second statement handles the entities the import created. It archives (sets a timestamp rather than deleting) every customer stamped with this batch id, but only those no order references. A customer who has placed an order since the import is deliberately left alone, because deleting them would break the order. In production you would flag those survivors for human review rather than passing over them in silence.

The third statement flips the batch's status, so the UI can show "rolled back" and refuse to commit it again. Because all three run inside one transaction, a partially-applied rollback, with reversals posted but status never updated, is impossible.

Field notes & further reading

• PostgreSQL docs: pg_trgm — the trigram padding rule, the % operator, the 0.3 default threshold, and the GIN/GiST choice
• PostgreSQL docs: Populating a Database — why COPY beats INSERT, and index/constraint ordering for bulk loads
• PostgreSQL docs: COPY — what FROM STDIN means, and the backslash escapes the text format requires for tab, newline, carriage return and backslash
• node-pg-copy-streams — COPY FROM STDIN as a Node writable stream, with a documented node:stream/promises pipeline example
• csv-parse documentation — streaming API, bom, column callbacks, and relaxed parsing options
• Node.js docs: Stream — buffering, highWaterMark (65536 bytes, or 16 for object-mode streams), and how pipeline() propagates backpressure
• Node.js docs: File system — fs.createReadStream() and its 64 KiB default highWaterMark
• AWS Lambda quotas — the 128 MB to 10,240 MB memory range, in 1-MB increments
• ExcelJS — the streaming WorkbookReader and its cache/emit/ignore options
• Microsoft: Excel incorrectly assumes 1900 is a leap year — the origin of the 1899-12-30 epoch
• Microsoft: Keeping leading zeros and large numbers — Excel's 15-significant-digit precision limit, dropped leading zeros, and why long codes must be entered as text
• OneSchema's embeddable importer — a product benchmark for mapping memory, autofixes, and annotated error exports