How a Phone Number Field Can Crash a 2B‑User Database – Architect’s Design Guide

Incident Overview

At 02:43 am the user‑center alarmed with P99 login latency jumping from 38 ms to 4.8 s, registration error rate > 17 %, MySQL master CPU saturated, and downstream risk‑control and messaging services timing out. The root cause was a

BIGINT UNSIGNED

phone

column that was handled inconsistently across services (numeric, string, stripped ‘+’, etc.), leading to mixed VARCHAR and numeric expressions and uncontrolled implicit type conversion.

Domain Modeling: Phone Number Is an Identifier, Not a Number

From a DDD perspective the phone number is a business identifier that requires exact match, uniqueness, standardization, and immutability. It never participates in arithmetic (sum, average, range statistics). Therefore it should be modeled as a value object:

public final class PhoneNumber {
    private static final Pattern E164_PATTERN = Pattern.compile("^\\+[1-9]\\d{1,14}$");
    private final String e164;
    private PhoneNumber(String e164) { this.e164 = e164; }
    public static PhoneNumber of(String raw, PhoneNumberNormalizer normalizer) {
        String normalized = normalizer.normalize(raw);
        if (!E164_PATTERN.matcher(normalized).matches()) {
            throw new IllegalArgumentException("invalid phone number: " + raw);
        }
        return new PhoneNumber(normalized);
    }
    public String value() { return e164; }
    // equals, hashCode, toString omitted for brevity
}

The normalizer can be implemented with Google libphonenumber:

public class LibPhoneNumberNormalizer implements PhoneNumberNormalizer {
    private final PhoneNumberUtil util = PhoneNumberUtil.getInstance();
    private final String defaultRegion;
    public LibPhoneNumberNormalizer(String defaultRegion) { this.defaultRegion = defaultRegion; }
    @Override
    public String normalize(String rawPhone) {
        try {
            Phonenumber.PhoneNumber pn = util.parse(rawPhone, defaultRegion);
            if (!util.isValidNumber(pn)) {
                throw new IllegalArgumentException("phone number is invalid");
            }
            return util.format(pn, PhoneNumberUtil.PhoneNumberFormat.E164);
        } catch (NumberParseException ex) {
            throw new IllegalArgumentException("phone number parse failed", ex);
        }
    }
}

Database Principles

Store the phone as a normalized VARCHAR(20) (E.164 max 15 digits + ‘+’).

Use utf8mb4_bin collation for byte‑wise comparison.

Define a unique index on the normalized column; never rely on implicit casts.

Avoid using the phone as a primary key – primary keys must be stable, short, and immutable.

Example DDL for the core user table:

CREATE TABLE user_account (
    id BIGINT NOT NULL COMMENT 'distributed primary key',
    phone VARCHAR(20) NOT NULL COMMENT 'E.164, e.g. +8613800138000',
    phone_hash BINARY(32) NOT NULL COMMENT 'hashed for sharding/search',
    nickname VARCHAR(64) DEFAULT NULL,
    status TINYINT NOT NULL DEFAULT 1 COMMENT '1‑enabled 2‑frozen 3‑deleted',
    register_source VARCHAR(32) NOT NULL DEFAULT 'app',
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),
    UNIQUE KEY uk_phone (phone),
    KEY idx_created_at (created_at),
    KEY idx_status_created_at (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Sharding Strategy

Route by the hash of the normalized phone so that the hottest login/registration queries hit a single shard:

shard = hash(phone) % 256
db_index = shard / 8
table_index = shard % 8

ShardingSphere configuration (simplified):

rules:
  - !SHARDING
    tables:
      user_account:
        actualDataNodes: ds_${0..31}.user_account_${0..7}
        databaseStrategy:
          standard:
            shardingColumn: phone
            shardingAlgorithmName: user_phone_db_hash
        tableStrategy:
          standard:
            shardingColumn: phone
            shardingAlgorithmName: user_phone_table_hash
        keyGenerateStrategy:
          column: id
          keyGeneratorName: snowflake
    shardingAlgorithms:
      user_phone_db_hash:
        type: HASH_MOD
        props:
          sharding-count: 32
      user_phone_table_hash:
        type: HASH_MOD
        props:
          sharding-count: 8
    keyGenerators:
      snowflake:
        type: SNOWFLAKE

Production‑Grade Registration Flow

API gateway performs rate‑limit and basic param validation.

Domain layer normalizes the raw phone to E.164 using PhoneNumber.

Captcha, device fingerprint, and risk checks are applied.

Idempotency key ( requestId) is stored in Redis for 5 min to prevent duplicate submissions.

Cache is consulted for a quick existence check; final uniqueness is enforced by the DB unique index.

Insert the UserAccount record inside a @Transactional method; catch DuplicateKeyException to surface “phone already registered”.

After commit, publish a USER_ACCOUNT_CREATED Kafka event.

Asynchronously refresh cache, user profile, and search index.

Key code excerpt:

@Transactional
public long register(RegisterCommand cmd) {
    PhoneNumber phone = PhoneNumber.of(cmd.rawPhone(), normalizer);
    verifyCaptcha(phone.value(), cmd.captchaCode());
    String idemKey = "idem:register:" + cmd.requestId();
    Boolean locked = redis.opsForValue().setIfAbsent(idemKey, "1", Duration.ofMinutes(5));
    if (Boolean.FALSE.equals(locked)) {
        throw new IllegalStateException("duplicate request");
    }
    if (getCachedUserId(phone.value()) != null) {
        throw new IllegalArgumentException("phone already registered");
    }
    UserAccount account = new UserAccount(idGenerator.nextId(), phone.value(), cmd.nickname(), cmd.passwordHash());
    try {
        userRepository.insert(account);
    } catch (DuplicateKeyException ex) {
        throw new IllegalArgumentException("phone already registered", ex);
    }
    cachePhoneMapping(account.id(), account.phone());
    kafkaTemplate.send("user-account-created", phone.value(), new UserCreatedEvent(UUID.randomUUID().toString(), account.id(), account.phone()));
    return account.id();
}

Caching Model

Cache‑aside for phone → userId and userId → phone.

Store captcha codes under captcha:scene:phone.

All keys must use the normalized E.164 value; mixing raw, ‘+’, or stripped forms leads to cache fragmentation.

Use Bloom filter for obvious‑non‑existence checks and fine‑grained Redis locks for concurrent bind operations.

Migration Path from BIGINT to VARCHAR

Add a nullable phone_v2 VARCHAR(20) column with a unique index.

Enable double‑write: old services keep writing phone, new services write both phone and phone_v2 (normalized).

Run a batch job that reads legacy rows, normalizes the old value, and updates phone_v2.

public void migrateBatch(List<LegacyUserRow> rows) {
    for (LegacyUserRow r : rows) {
        String normalized = normalizer.normalize(r.getLegacyPhone());
        repository.updatePhoneV2(r.getId(), normalized);
    }
}

Gradually shift reads to phone_v2 (gray rollout 1 % → 100 %).

After full migration, drop the old phone column and rename phone_v2 to phone.

Consistency checks include row count, unique value count, random sampling, and full‑hash verification per shard.

Monitoring & Alerting

QPS and error‑rate of user.register.

DuplicateKeyException spike.

Phone normalization failure rate.

Cache hit‑rate for phone:uid keys.

DB query latency (p95/p99) for phone‑based lookups.

Scan‑row count anomalies on any query involving phone.

Shard‑level QPS imbalance.

Log statements must mask the raw number; use a helper like:

public final class PhoneMasker {
    public static String mask(String phone) {
        if (phone == null || phone.length() < 7) return "***";
        int prefix = Math.min(3, phone.length());
        int suffix = Math.min(4, phone.length() - prefix);
        return phone.substring(0, prefix) + "****" + phone.substring(phone.length() - suffix);
    }
}

Checklist for Architects

Model phone as a value object with E.164 standardization.

Store as VARCHAR(20) with utf8mb4_bin collation and a unique index.

Separate primary key (snowflake ID) from business key (phone).

Design sharding on the phone hash for single‑shard routing.

Implement cache‑aside, Bloom filter, and fine‑grained locks.

Provide double‑write and gray‑scale migration path for legacy BIGINT fields.

Set up end‑to‑end monitoring, alerts, and log masking.