Promotions that started two hours late and barcodes that belonged to nobody.

What they sent us

A fashion brand selling in four countries, four currencies, seven languages. The e-commerce manager had built the storefront admin in v0 on Next.js, with Postgres behind it. At Discovery it held 3,782 product models across three SKU levels (model, model-colour, model-colour-size) in 62 tables. The dump was 106 MB. A supplier XML feed arrived over FTPS every hour, and a sync pushed the catalogue to the four storefronts every five minutes.

Two lines decided how data got in. The first was the merge in the importer: every field was written as incoming || stored. That is fine for a description. For a barcode it means whatever the supplier puts in that column wins, and the supplier had started putting an internal counter there. The second was the column type. Every date-time was a plain timestamp with no zone; the admin wrote wall-clock strings and the storefront read them back as UTC. On a summer day that is two hours.

The schema was applied with drizzle-kit push straight against the live database. When the schema file drifts, push asks "was table X renamed from Y?" and the wrong answer drops a production table. There was no test suite. There was no dry-run mode on any script. Every fix was a query typed into a console.

Before we changed a line we counted the damage: 193 colour-size variants whose barcode was a supplier counter, not a GTIN. 546 units on sale under sizes that existed twice, once as "ONE SIZE" and once as "ONESIZE". 620 rows in one market's language that were byte-for-byte copies of the source text, with zero characters from the target alphabet. Nobody had filed a bug for any of it, because none of it throws.

What would have happened

Forty-eight hours

Hour 0 to 6: count before you touch. We restored the dump locally and ran three queries against it. Barcodes that did not match a thirteen-digit shape or did not carry a GS1 company prefix: 193. Sizes that collapsed to the same key after removing spaces and case: 546 units across duplicates. Translation rows equal to their source row: 620. We also found the SKU problem nobody had mentioned. The feed's internal id uses underscores, so a subset of size SKUs held _ where the rest held -, and the storefront treated them as different products.

Hour 6 to 18: a boundary. The importer wrote whatever arrived. We put a zod schema at the boundary and made it lenient where a bad value is harmless and strict where a bad value is an identifier. A missing name does not block a row. A barcode with the wrong shape does.

const Variant = z.object({
  sku:     z.string().trim().regex(/^[A-Z0-9]+-[A-Z0-9]+-[A-Z0-9]+$/),
  barcode: z.string().regex(/^\d{13}$/).optional(),  // strict: GTIN-13 or absent
  size:    z.string().trim().transform(normSize),      // "ONE SIZE" and "ONESIZE" → one key
  name:    z.string().catch(''),                      // lenient: never blocks a row
  price:   z.coerce.number().nonnegative().nullish(),
}).passthrough();

const r = Variant.safeParse(raw);
if (!r.success) { skipped.push({ raw, error: r.error.message }); continue; }

Invalid rows are skipped with a warning and counted in a summary at the end of the run, so a bad feed file produces a number, not a silent write. The barcode rule sits behind the schema and is the line that would have saved the warehouse a week. An identifier is never merged. It is accepted or refused.

// identifiers are not merged. They are accepted or refused.
function acceptBarcode(incoming?: string, stored?: string) {
  if (!incoming) return stored;                        // feed silent: keep ours
  if (!/^\d{13}$/.test(incoming)) return stored;       // wrong shape: keep ours
  if (!hasGs1Prefix(incoming) || !checkDigitOk(incoming)) {
    report('barcode.rejected', { incoming, stored });   // a counter, not a GTIN
    return stored;
  }
  if (stored && stored !== incoming) report('barcode.changed', { incoming, stored });
  return incoming;
}

The GS1 prefix is the part that separates a real barcode from a supplier's counter: real GTINs start with a registered company prefix, counters start with whatever the supplier's database was up to. Add the check digit and the counter cannot get through by accident. A changed barcode on an existing variant is allowed, but it is reported, because it is the one field the warehouse keys on.

Hour 18 to 36: one clock, then tests. We replaced every date-time column with timestamptz in an additive, numbered SQL migration, and routed every time in the app through one helper with a fixed zone. The admin form produces wall-clock text. The helper turns it into an instant. Nothing else in the codebase is allowed to construct a date from a string.

import { fromZonedTime, toZonedTime } from 'date-fns-tz';
const ZONE = process.env.SHOP_TZ ?? 'Europe/Berlin';   // one zone, set once, never guessed

// wall-clock from the admin form → instant for the database
export const fromShop = (wall: string) => fromZonedTime(wall, ZONE);
// instant from the database → wall-clock for the screen
export const toShop   = (at: Date)     => toZonedTime(at, ZONE);

// the only allowed way to build a promotion window
export const promoWindow = (start: string, end: string) =>
  ({ startsAt: fromShop(start), endsAt: fromShop(end) });

Then the tests. The rule for this suite was simple: every fixture is a real bad input from the incident log, not a made-up one. The size normaliser is tested against the non-breaking spaces that Excel exports and the typographic dashes that a copy-paste from a PDF produces. The retry classifier is tested against the literal HTML page a reverse proxy returns on a 502, because that page once arrived where JSON was expected. The barcode rule is tested against the exact counter values that overwrote the 193. By hour 36 the suite ran 717 cases across 40 files, and a change that reintroduced any of the incidents would fail before it reached a branch.

Hour 36 to 48: repair, in batches, with a dry run. The 193 barcodes, the 546 phantom units, the 620 copied rows and the underscore SKUs all needed fixing in place. Every fix script we wrote runs dry by default, prints the rows it would change, and only writes when passed --apply. It works in batches of 200 and prints an ETA, because a script that says nothing for eleven minutes gets killed by a nervous operator.

const DRY = !process.argv.includes('--apply');           // dry run unless told otherwise
const rows = await db.select().from(variants)
  .where(sql`size <> upper(replace(size, ' ', ''))`);
console.log(`${rows.length} rows to merge${DRY ? ' (dry run)' : ''}`);
const t0 = Date.now();
for (const [i, batch] of chunk(rows, 200).entries()) {
  if (!DRY) await mergeBatch(batch);   // on 23505: drop the bad row and its country rows
  const done = Math.min((i + 1) * 200, rows.length);
  const eta = ((Date.now() - t0) / done) * (rows.length - done);
  console.log(`${done}/${rows.length}  eta ${Math.round(eta / 1000)}s`);
}

The SKU cleanup showed why the dry run matters. Rewriting _ to - in place collided with correct rows that already existed, error 23505 on the unique constraint. The dry run listed the collisions before anything was written; the real run deleted the bad row and its country rows on collision instead of stopping halfway. The translation fix got its own rule set: brand, model and fit names stay in Latin script, because we checked ten local retailers in that market and not one transliterates them. Rows with zero characters from the target alphabet are refused before they reach the storefront.

Two smaller things closed the window. The VAT table now returns null for a country it does not know, instead of falling through to the first rate; a null fails loudly at the price, a wrong rate does not. And the schema is now four hand-written, forward-only SQL files, numbered 0001 to 0004, with a README that logs the date each one was applied. Nobody answers a "was this renamed?" prompt against production again.

Every one of those bugs was a single line. Every one of them cost a week.

What it looks like now

The feed still arrives every hour and the sync still runs every five minutes. What changed is that nothing reaches the catalogue without passing a schema, no identifier is ever merged, and every time in the system is an instant in one named zone. Readiness went from 2.0 to 3.8. The suite that guards it has 717 cases, and the ones that matter most are named after the week they would have cost.

Do this tonight

The rule

Every one of those bugs was a single line. Every one of them cost a week.— E-commerce manager