Nine thousand contacts nobody could call, and one import that kept making more

An owner mentioned, almost in passing, that the phone numbers in their database looked wrong — some seemed too short, some too long. They wanted to know whether it mattered.

It is the sort of remark that usually turns out to be a handful of rows. We checked it against production, because a suspicion is not a finding until somebody counts.

What the count said

Of 753,994 live contact records, 9,300 held an unusable phone number — 1.22 per cent. 9,001 of them contained characters that were not digits at all.

That is nine thousand people the business believed it could reach and could not. Not a data-quality abstraction: a field rep pulling up a contact, dialling, and getting nothing, having travelled to do it.

Worse, it was still happening. One import route was producing bad rows at 3.48 per cent against roughly 0.15 per cent for every other source — about twenty-three times the failure rate — and was still running at 1.85 per cent this year. The pile was growing.

Nothing was checking, at any layer

The reason was not a subtle bug. It was that four separate places could have validated the number and none of them did.

The spreadsheet import copied the cell straight through. The data model declared the column as plain text. The table had no constraint. And the application’s own check — the one everyone assumed was doing the job — read:

if (contact.length < 10)

A lower bound only. Which is precisely why the database contained 7,995 eleven-digit numbers and 965 twelve-digit ones. Every one of them was tested, passed, and saved. The check was not missing. It was half-written, and half a check reads as a whole one in a code review.

The junk even named its own cause. 493 rows carried the apostrophe Excel adds when a user forces a cell to text. Ten rows held 9.88E+09 — a phone number that Excel had helpfully converted to scientific notation, destroying the digits permanently before the file was ever uploaded.

What we changed

One normaliser, deliberately written twice — once in the server, once in the mobile app, because those two cannot share code — with a parity test that runs 6,015 real production values through both and asserts they agree on every one. The asymmetry matters: if the app accepts what the server rejects, a rep is stranded in a field with a contact they cannot save. If the server accepts what the app rejects, good data is thrown away.

The import was rebuilt to behave like a tool rather than a trapdoor. It reads every row instead of stopping at the first bad one. It no longer swallows failures into a log nobody reads. It matches columns by name rather than position, so a reordered spreadsheet cannot silently load phone numbers into the village column.

And rejected rows now come back as a spreadsheet with the original columns plus an Error column explaining each one. The importer ignores that column, so the uploader fixes the numbers in place and re-uploads the same file — no reconciling two versions by hand. The phone column is written as text on the way out, because writing it as a number is how ten digits became 9.88E+09 in the first place.

What it recovered, and what it did not

After cleaning, 99.76 per cent of the records are valid. The remaining 1,844 genuinely have to be collected again — the information is gone, not malformed.

The detail worth sitting with: 1,410 of those are ten digits long with a leading digit no Indian mobile number can start with. Correct length, real digits, impossible number. The length check accepted every one of them, every time, for years.

The part that transfers

“Required” is not “valid”, and a validation rule that tests one end of a range is usually worse than none, because it creates the confidence that stops anyone looking again.

If a field matters commercially — you dial it, post to it, invoice against it — the rule belongs in the database as a constraint, not in a function somebody can route around with a bulk upload. Every one of these 9,300 rows entered through a door the application never guarded.

And when an owner says the data looks wrong, count it that week. This one was one per cent, which is small enough to argue about and large enough to have wasted a great many journeys.