PHP migration: the zero-padded ID that ate twelve days

It is the second Friday of the migration. Marleen, head of quality at a thirty-person Rotterdam aerospace-and-offshore toeleverancier, has borrowed an engineer's monitor and is staring at a query result. 2,840 rows. None of them reconcile against the legacy file. None of them. The TÜV audit lands in six weeks. The PHP 5.5 portal she is replacing went live in 2007 and has been quietly underwriting EN 9100 traceability ever since. We are on day twelve of what we scoped as a ten-day migration.

The bug, when we finally cornered it, was one line in a Prisma schema and a checksum function whose author left the company in 2014. This is the story.

A weld-inspection register, written before NodeJS existed

The lasnaad-keuringsregister — weld seam inspection register — is the spine of EN 9100 traceability at this kind of supplier. Every radiographic film shot on every weld carries an identifier. Lose the link between a film and the weld it documents, and the entire batch of finished components has to be re-inspected. Or scrapped. For an offshore riser flange, scrap means the order is gone and so is the client.

The 2007 portal stored those identifiers as Oracle VARCHAR2(14). Stripped of dashes (the dashes were a UI affordance, never stored), they look like this:

07 2024 001109
               ^^ checksum (mod-100 over the 12 leading digits)
        ^^^^ sequential within shift
   ^^^^ year
^^ lab code (07 = Spaanse Polder darkroom)

The canonical id was fourteen digits of zero-padded text. The leading zero on lab code 07 was not cosmetic. It was the lab code. 7 was a different lab, three buildings away, with a different head of inspection and a different set of trained technicians.

The checksum was a quiet modulo-100 over the first twelve digits, weighted alternately 3 and 1. The engineer who wrote it copied the idea from EAN-13 and adapted it for a fourteen-character field. He documented it nowhere except in a PHP function called filmcode_klopt(). We did not find that function until day eleven.

How Prisma's introspector quietly lied

The migration plan was straightforward on paper. Bring up Postgres 16, dual-write from the PHP application for a fortnight, build the NestJS read API behind the same login, cut traffic over a weekend. The bit we underestimated was the schema port.

We ran prisma db pull against the staging Oracle to get a starting schema. The introspector saw VARCHAR2(14) on the film identifier column and, correctly, mapped it to String. So far so good.

Then a well-meaning engineer cleaned the schema up.

// before -- what prisma db pull produced
model RadiographicFilm {
  id        String   @id @db.VarChar(14)
  weldId    String   @db.VarChar(20)
  shotAt    DateTime
  // ...
}

// after -- what landed in main, the day before the cutover
model RadiographicFilm {
  id        BigInt   @id
  weldId    String   @db.VarChar(20)
  shotAt    DateTime
  // ...
}

The reasoning in the pull request was sensible-sounding: “values are all digits, BigInt is faster on joins, saves index size.” The reviewer approved it. CI passed. The seed script ran green against staging because staging had been re-seeded from a CSV the dev team had hand-rolled — and Excel had quietly stripped the leading zeros six months earlier. If your seed data is a CSV that has ever been opened in Excel, treat it as compromised until you have audited the original source.

The day production data hit the new schema

We dumped from Oracle directly into Postgres for the dress rehearsal. The Oracle export was correct: every id was the full fourteen characters with its leading zeros intact. The Prisma client received them as strings, called BigInt(value) internally, and wrote them as NUMERIC. Postgres did exactly what you would expect. It stored the number.

00072024001109 became 72024001109. On the way back out through the API, the formatter re-padded to fourteen characters — but it padded with leading zeros at a fixed width. A lab-13 record that was originally 13072024001109 came back unchanged. A lab-07 record came back 00072024001109, which looks right, until you check the checksum.

The checksum is computed over the first twelve characters of the canonical id. For a lab-07 film, those twelve characters include the leading zero. For a record that has round-tripped through BigInt and back, the original leading zero is still there — but for any film whose middle digits happened to start with a zero, the BigInt strip plus the fixed-width re-pad slid the lab code into the wrong slot. 2,840 records out of 41,200 had this shape. The checksum failed for every one of them.

The downstream query that matters is the EN 9100 traceability report. It joins films to welds to batches to delivery notes by id, and the auditor walks a sample backwards through that chain. A film whose id no longer matches the weld record is, for audit purposes, an orphaned weld with no radiographic evidence. 2,840 of those is not a defect. It is the company losing its certification.

Twelve days of looking in the wrong place

Days one through six we were convinced the bug was in the Oracle export. It was not. Days seven through nine we suspected the dual-write bridge was double-encoding. It was not. Day ten an engineer noticed that every failing record's id started with 0. Day eleven we found filmcode_klopt() in /legacy/inc/laskeuring.inc.php, thirty-eight lines including the comment block, and understood that we had silently re-encoded a structured identifier as a number.

The fix took four hours.

model RadiographicFilm {
  id        String   @id @db.Char(14)
  weldId    String   @db.VarChar(20)
  shotAt    DateTime
  // ...
  @@index([weldId])
}

@db.Char(14) rather than @db.VarChar(14) because Postgres CHAR rejects writes that do not match the declared width, and we wanted any silent truncation to throw rather than pad. The Postgres character-types documentation spells out the difference clearly enough that we should have read it on day one.

The checksum verifier got ported to TypeScript, byte for byte, with a unit test against twenty-five known-good ids from the 2007 production export:

// libs/laskeuring/src/checksum.ts
export function filmCodeIsValid(id: string): boolean {
  if (id.length !== 14 || !/^\d{14}$/.test(id)) return false;
  const body = id.slice(0, 12);
  const expected = id.slice(12);
  const sum = body
    .split('')
    .reduce(
      (acc, ch, i) => acc + Number(ch) * (i % 2 === 0 ? 3 : 1),
      0,
    );
  const computed = String((100 - (sum % 100)) % 100).padStart(2, '0');
  return computed === expected;
}

A reconciliation script walked the 2,840 broken rows, re-derived the original id from surrounding context (lab code lives on the weld record, the year lives on the work order, the sequential-within-shift was preserved on the dump), recomputed the checksum, and wrote a corrected row alongside an audit trail. Every corrected row got a row in migration_correction_log with the old id, the new id, the reason, and a SHA of the source row from the Oracle dump. The TÜV auditor asked for that table in the next audit. He got it.

What we changed in the playbook

Three things, in order of how much pain they would have saved us.

Audit the values, not just the column types. Before any schema port, run a five-minute query that buckets the column by length, by leading character, by character class. A column that is VARCHAR2(14) and whose values are entirely digits is not a number column. It is a string column whose values happen to be digits, and ninety percent of the time the leading characters carry meaning.

Read the legacy code before you trust the legacy schema. The schema told us the column was a fourteen-character string. The PHP told us it was a structured identifier with an embedded checksum. The PHP was right. grep -ri 'checksum\|klopt\|valid' legacy/ on day one would have saved us nine days and one very long conversation with the head of quality.

Never use BigInt for a domain identifier, even when the values look numeric. Domain identifiers are strings. Always. The index-size argument does not survive contact with a real audit. The Prisma native-type reference makes @db.Char(n) the obvious choice for fixed-width opaque identifiers. We just talked ourselves out of it because the values looked like numbers and we were three days from cutover.

What we shipped, in the end

Cutover happened three weeks later than planned. The NestJS API now serves the traceability portal for inspectors on the shop floor, the quality team's audit prep, and a tablet app the welders use to scan film barcodes into the right weld out in the yard. The reconciliation log stays in production indefinitely. Every record migrated has a SHA trail back to its 2007-Oracle origin. When the TÜV auditor came back the next year he sampled fourteen records and walked every one of them backwards without finding a break.

When we built the certification portal migration for this client, the lesson we kept was small and specific: the legacy code is the schema's real documentation, and you read it before you touch Prisma. We carry that into every legacy migration we take on now — it costs an afternoon of reading and saves the kind of week that ends with a head of quality staring at 2,840 broken rows.

If you are about to start one of these ports, run one query before anything else: SELECT LENGTH(id), SUBSTR(id, 1, 1), COUNT(*) FROM your_legacy_table GROUP BY 1, 2 ORDER BY 1, 2;. If the leading character is not uniformly distributed, the leading character carries meaning. Treat the column as a string.