Designing Practical Encrypted Storage for Sensitive User Data

Why encryption storage is needed?

When preparing to launch a social app, the team was rejected by the app store because the platform required a "public security filing number" and mandated that personal data such as passwords, phone numbers, and ID numbers be transmitted and stored encrypted; plain‑text storage would lead to rejection or even business suspension.

App must encrypt transmission and storage of personal information, especially "password, phone number, ID number and other sensitive fields"; plain‑text storage will be directly rejected and may be stopped or penalized.

Initially the password was hashed, but phone numbers, IDs, and emails were stored in plain text because the developers never considered encryption necessary, assuming the database was not exposed and that front‑end masking was sufficient.

Later these assumptions turned into real blockers, forcing a week‑long redesign to meet compliance.

How we encrypted phone numbers

We first extracted the phone field into a separate table user_phone and researched various schemes. Complex mapping tables or partial encryption were rejected as over‑engineered.

We identified the real usage scenarios for phone numbers:

Customer service needs to look up a user by phone.

Backend risk control queries by phone.

Auditing and reporting use phone as primary key.

Business logic sometimes filters by phone suffix (e.g., "last four digits are 5678").

We do not adopt a "high‑end" universal encryption framework, nor sacrifice usability for security.

Our practical solution: controlled field splitting + plaintext index + fuzzy masking strategy . Encrypt fields that must be secret, keep a small amount of plaintext for common queries, and store an HMAC index for exact matches.

CREATE TABLE `user_phone` (
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT 'User ID, primary key, one‑to‑one with user table',
  `phone_cipher` VARBINARY(128) NOT NULL COMMENT 'Encrypted phone ciphertext',
  `phone_iv` VARBINARY(12) NOT NULL COMMENT 'Random IV for AES‑GCM',
  `phone_tag` VARBINARY(16) NOT NULL COMMENT 'AES‑GCM authentication tag',
  `phone_idx` BINARY(32) NOT NULL COMMENT 'HMAC index for equality queries (irreversible)',
  `phone_prefix` CHAR(3) NOT NULL COMMENT 'Phone prefix for controlled prefix queries',
  `phone_last4` CHAR(4) NOT NULL COMMENT 'Phone last four digits for suffix queries',
  `key_version` SMALLINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Key version used for encryption',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `uk_phone_idx` (`phone_idx`),
  KEY `idx_phone_prefix_last4` (`phone_prefix`,`phone_last4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='User phone table, encrypted storage + query support';

Key fields explained: phone_cipher, phone_iv, phone_tag: AES‑GCM encrypted phone number and its integrity data, used only when the original phone needs to be displayed or exported. phone_idx: HMAC of the plain phone, enabling exact‑match queries without revealing the phone. phone_prefix and phone_last4: Plaintext fragments retained for common prefix or suffix searches (e.g., "138%" or "%5678"). key_version: Records which encryption key version was used, facilitating key rotation without re‑encrypting all rows at once.

Name field encryption design

Name usage is limited to backend display and exact‑match filtering, so we store name_cipher, name_iv, name_tag for decryption and name_idx (HMAC) for equality queries. No prefix or suffix fields are kept because Chinese names have a tiny space and fuzzy search would expose many users.

ID card field encryption design

ID numbers are highly sensitive. We encrypt them into pid_cipher, pid_iv, pid_tag and store an HMAC index pid_idx for exact lookups. Additionally we derive structured fields from the ID: pid_birth (birthdate), pid_gender (M/F), and pid_area_code (region code) to support analytics without decrypting the full number.

Typical queries use the derived fields or the HMAC index, e.g.:

SELECT * FROM user_identity WHERE pid_idx = :hashedPid;

SELECT * FROM user_identity WHERE pid_birth BETWEEN '1990-01-01' AND '1990-12-31';

SELECT * FROM user_identity WHERE pid_gender = 'F';

SELECT * FROM user_identity WHERE pid_area_code = '310101';

Email field encryption design

Email is encrypted similarly ( email_cipher, email_iv, email_tag) with an HMAC index email_idx. To support domain‑based filtering we store email_domain in plain text.

SELECT * FROM user_email WHERE email_idx = :hashedEmail;

SELECT * FROM user_email WHERE email_domain = 'qq.com';

Masked fields for display

To avoid costly joins and decryption when rendering lists, we add masked versions of each sensitive field directly in the main user_info table (e.g., phone_masked = "138****5678"). These fields are generated together with the encrypted data and are used only for UI display, never for query logic.

Operational lessons

Key management must be externalized (KMS) rather than hard‑coded; avoid exposing decryption functions widely; migrate legacy plain‑text data with batch scripts; provide a separate de‑identified copy for BI; restrict export of raw data to approved workflows with audit logging.

Encryption is the baseline; not abusing it is the real protection.

Overall, the team chose a solution that is simple, secure, and aligned with their concrete business scenarios rather than a generic, overly complex framework.