This number is already in use, and nobody has it

A depot tries to register a new distributor and the portal refuses: the phone number must be unique. The depot checks. No distributor has that number. They try a second new distributor the same week and get the same refusal on a different number that nobody has either.

In the same fortnight, a field employee reports that he cannot log in as staff. Every time he opens the app it sends him to the screen for registering as a farmer. And a regional manager reports that his account has been marked inactive, which it has not.

Three complaints, three screens, and one thing underneath all of them. The employee had been unable to use the app for three months. Two distributor registrations were blocked. And while tracing the manager’s report we found 255 accounts created in the spring of 2024 that had never been able to log in and never would.

What was actually going on

When anybody opens this app and asks for a one-time code, the system creates a blank account on that phone number before the code is even entered. No name, no role, no dealer, no depot: just a row holding the number. If the person never completes registration, the row stays. And because phone numbers must be unique, that blank row now owns the number.

That was the distributors. Both numbers were held by blank accounts left behind by somebody opening the app once, months earlier, almost certainly the distributor himself.

The employee’s case was the same mechanism with a twist. His staff account had been created in April with a leading space in the phone number. The login looks for an exact match, and a space is a character, so when he first tried the app in June the lookup found nothing and the system did what it always does: it created a blank account on the clean number. Every login since had resolved to the blank account, and because a blank account has no type, the app offered him registration rather than his employee home. Its last update was 06:03 on the morning he reported it, his latest attempt.

The manager’s account was fine; the “inactive” message was coming from the phone login path for 27 other numbers, none of them his. But the check that produces it treats “never set” the same as “switched off”, and 255 accounts from March to May 2024 had the flag never set.

What we changed

Each case was repaired in a single transaction that first asserted the state it expected to find. Order mattered, and that is the part worth remembering. Trimming the employee’s real number while the blank account existed would have violated the uniqueness rule; deleting the blank account alone would have got him a fresh blank account on his next login. So: check that nothing across all 113 tables that can point at a user pointed at the blank account, delete it, then trim the real one. Verified on a fresh connection: the number now resolves to his staff account alone, with his role and employee code.

The distributor numbers were freed the same way. Then a dealer’s number change went through the same trap in the other direction: the new number was already held by a blank account, and the number lives in two rows, only one of which is unique-constrained, so changing just the dealer record would have left him unable to log in with his own number. Both rows were updated together after the blank account was removed.

A sweep of the whole table found exactly one more untrimmed number, and it was left alone: both accounts on it are real dealer accounts, and which one keeps the number is the depot’s call.

What it did not fix

Nothing in the code has changed yet. The durable fixes are logged for the next release: trim the number on lookup and on every create, update and spreadsheet import; adopt an existing blank account when a real one is added on its number instead of refusing; and stop treating an unset flag as a lockout. Until they ship, the next person who opens the app before their account is created will hit the same wall. The 255 accounts from 2024 are still locked. Another dealer still carries a placeholder number, and the users table carries 26 and 30 duplicate uniqueness constraints on the same columns, which is untidy rather than dangerous.

The mechanism

A login path that creates a row as a side effect of asking for a code, and a uniqueness rule applied to a text column that was never trimmed. Each is harmless alone. Together they mean the first person to open the app on a number owns it, whoever they are.

Where this ends up

Sazinga Field signs dealers and staff in by phone number, and the rule that came out of this is that a number is claimed when a person registers, not when a phone asks for a code.