When 00412 Becomes 412: A Roundtrip Witness for Customer Reference Codes

A customer reference such as 00412 is a label, not a quantity. Once any step treats it as a number, the leading zeros can vanish and two different references can collapse into one. This article proposes a design and a witness table you can use to check each hop. All of it is a conditional recommendation, not a description of any existing product.

Primary artifact: the roundtrip witness

A witness is a small fixed set of identifiers that you push through every hop and compare character by character at the end. Build it from these hypothetical samples:

W1: 00412 (leading zeros, short)
W2: 412 (the same digits without zeros, a different reference)
W3: 0000731 (several leading zeros)
W4: 7310 (trailing zero)

W1 and W2 matter most together. If both come back as 412, the pipeline has merged two customers.

For each hop, record the exact string going in, the exact string coming out, and a verdict of identical or not identical. Compare the text, never a parsed value.

Decision branches

Choose the branch by where the identifier first becomes digits.

Branch A: you control the entry point. Declare the field as text in the form, the API schema and the storage column. Do not apply numeric validation. If you validate at all, validate the shape (allowed characters, and a length range you have agreed with the people who issue references), and reject rather than correct.

Branch B: an upstream source already sends a number, for example a JSON number or a spreadsheet cell stored as numeric. The zeros are gone before you see them. Do not guess a padding width. Only pad if the issuer has confirmed in writing a fixed width for that reference type, and record that decision next to the field. Otherwise mark the record as unverified identity and ask the source to resend it as text.

Branch C: you produce an export that people open in a spreadsheet. The file may be correct yet reopen with zeros stripped. Treat this as a display hazard of the consumer. Propose an export option that marks the column as text where the format allows it, and keep the plain stored value as the authority. Never use the re-opened spreadsheet as a source for re-import without running the witness.

Worked hypothetical

A small training business issues references to customers. Four witness values run through five hops: form, API, database, CSV export, re-import of that CSV.

Suppose the observation shows W1 to W4 identical after hops one to three, and after the CSV export, but after a manual save in a spreadsheet and re-import, W1 returns as 412 and W3 returns as 731. W2 and W4 are unchanged. The count is 2 not identical out of 4, and W1 now equals W2, so a duplicate would appear. Branch C applies: the fix is outside the database. The re-import step should refuse any file whose witness rows do not match, and the 2 affected rows are held, not merged.

Stop conditions

Stop the import if two different input strings map to one stored value.
Stop if any witness row is not identical and the cause is unknown.
Stop padding logic if no issuer-confirmed width exists.
Do not repeat a failed external write automatically; a person decides after reading the difference.

Proposed acceptance tests (not yet performed)

Submit W1 to W4 through the form and check each is stored and displayed unchanged.
Fetch them through the API and compare the raw response text.
Export, then compare the file text before any spreadsheet is opened.
Re-import a file where W1 is shown as 412 and expect the file to be rejected or held.
Search for 00412 and for 412 and expect different single results.
Check that sorting treats values as text, so the order is stated and predictable.

A passing run gives evidence for these cases only. Add new witness values whenever a new reference format appears.