The same shipping event, twice, in two languages: a dedup rule we measured and did not ship

If you store carrier scan events, you will eventually meet a timeline where every row appears twice: once in English, once in another script, with the identical timestamp. Some postal operators publish each event in more than one language, and if you persist what the carrier published, you persist both.

The obvious fix is a dedup rule. This is the story of why we measured one, found it destroys real data, and shipped nothing.

Why the naive key is wrong in both directions

Most event tables end up keyed on something like:

UNIQUE KEY uk_event (tracking_number, event_date, description)

That key is wrong twice over.

It under-dedups. Two rows for one event differ in description (different language), so both survive. The customer counts rows and concludes the parcel went through sorting twice as often as it did.

And it over-dedups the moment you "fix" it. The intuitive repair is to drop description from the key and treat (tracking_number, event_date) as the identity of an event. Do not do this. On air-cargo timelines, two genuinely different events legitimately share a timestamp:

2026-03-11 14:32  Booked on Flight EK-7281D
2026-03-11 14:32  Manifested on Flight EK-7281D

Booked and manifested are different states. Collapse them and you have deleted a state transition that someone will later need.

It gets worse. We found pairs that shared a timestamp and differed only in the flight number, EK-7007D and EK-7013D. A timestamp-only key merges two different flights into one row.

What we actually measured

We took every stored pair that shared a tracking number and a timestamp and asked a narrow question: how many pairs are provably the same event rendered twice, under a rule a machine can apply?

The honest answer was none. Every candidate rule either left duplicates behind or deleted something real:

• Same timestamp merges booked/manifested and distinct flights.
• Same timestamp + same location still merges booked/manifested, which share a facility.
• Same timestamp + different script is closer, but a bilingual carrier can also publish two different events a minute apart, and script is a property of the carrier's publishing habit, not of the event.
• String similarity after translation requires trusting a translation of operational wording. In Chinese, 妥投失败 (delivery failed) and 已妥投 (delivered) share the same two-character root and mean opposite things.

So the safe set was empty, and we wrote that down instead of shipping a rule. The reopen condition is recorded with it: do this when the source supplies a stable per-event id, and not before. Guessing event identity from prose is not a rule, it is a coin flip with your customers' history.

What to do instead

Dedup at read time, not write time. Keep both rows. When rendering, group by (event_date, location) and, if two rows in a group are in different scripts, show one and keep the other available. Nothing is deleted, and the customer sees one event.

Detect script with Unicode properties, not \w. This bites people constantly:

// WRONG - \w is ASCII-only, so a CJK string becomes empty
const letters = s.replace(/[^\w]/g, '')        // "已妥投" -> ""

// RIGHT
const hasCJK  = /\p{Script=Han}/u.test(s)
const hasCyr  = /\p{Script=Cyrillic}/u.test(s)
const letters = s.normalize('NFKC').replace(/[^\p{L}\p{N}]/gu, '')

Any heuristic built on \w silently classifies every non-Latin status as "empty" and then does something confident with that.

Treat "carrier" as a role, not a company. The second thing a bilingual timeline teaches you is that the entity on the label is often not the entity that delivers. A freight forwarder issues a job reference, booked / received / dispatched, and its record ends when the cargo reaches the destination country. Three milestones is a complete forwarder record. If your UI calls that "incomplete tracking", your UI is wrong about the domain. Model the issuer separately from the final-mile carrier, and expect the delivery date to live on a different number.

The general lesson

Normalisation code gets written in the direction of "make the data tidier". Tidier is not the goal; lossless and legible is. Before shipping any merge, collapse or translation rule, run it over your whole corpus and count three things: rows changed, rows emptied, and rows that lost meaning. If the third number is not zero, you do not have a rule yet.

---

I work on 24hTrack, a free package tracker for 3,200+ carriers - paste any tracking number, the carrier is detected automatically, no sign-up. The behaviour above is why we show the scans a carrier published rather than a cleaned-up summary of them. The customer-facing version of the same material is in our international parcel journey guide (CC BY).