User Management System Design | Data Models, API, Security & Privacy

Core Components

A modern user management system isn't just a "users" table. It requires separation of concerns for security, scalability, and compliance.

🆔 Identity Service

Handles core authentication credentials (email/password, OAuth links). Optimized for fast lookups during login.

👤 Profile Service

Stores non-essential user data (avatar, bio, preferences). Can scale independently and use different storage (e.g., NoSQL).

🛡️ Auth Service

Issues and validates tokens (JWT/Session). Coordinates with Identity Service to verify credentials.

⚖️ Compliance / Audit

Tracks user activities (login IPs, changes) and handles GDPR/CCPA requests (Right to be Forgotten).

Data Modeling & SQL Schema

Proper normalization and separation of credentials from profile data is crucial.

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255), -- Nullable for OAuth users
  is_verified BOOLEAN DEFAULT FALSE,
  status VARCHAR(20) DEFAULT 'active', -- active, suspended, deleted
  created_at TIMESTAMP DEFAULT NOW(),
  last_login_at TIMESTAMP
);

CREATE TABLE user_profiles (
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  full_name VARCHAR(100),
  avatar_url TEXT,
  phone_number VARCHAR(20), -- Encrypt this if PII sensitive
  preferences JSONB DEFAULT '{}'
);

CREATE TABLE social_logins (
  id UUID PRIMARY KEY,
  user_id UUID REFERENCES users(id),
  provider VARCHAR(50), -- 'google', 'github'
  provider_id VARCHAR(255),
  UNIQUE(provider, provider_id)
);

REST Implementation

Clean, resource-oriented API endpoints for user lifecycle management.

Method	Endpoint	Description	Auth Required
POST	`/api/v1/auth/register`	Create new user account	❌ No
POST	`/api/v1/auth/login`	Authenticate and receive tokens	❌ No
GET	`/api/v1/users/me`	Get current user profile	✅ Yes
PUT	`/api/v1/users/me`	Update profile details	✅ Yes
POST	`/api/v1/auth/reset-password`	Request password reset email	❌ No

Python Registration Example (Flask)

from flask import Flask, request, jsonify
from argon2 import PasswordHasher
from sqlalchemy.exc import IntegrityError
import uuid

app = Flask(__name__)
ph = PasswordHasher()

@app.route('/api/v1/auth/register', methods=['POST'])
def register():
    data = request.get_json()
    email = data.get('email')
    password = data.get('password')
    full_name = data.get('full_name')

    # Security: Hash password using Argon2id (Slow hashing)
    try:
        password_hash = ph.hash(password)
    except Exception:
        return jsonify({"error": "Password processing failed"}), 500

    try:
        # Create User Transaction
        user_id = str(uuid.uuid4())
        # db.execute("INSERT INTO users (id, email, password_hash) VALUES ...", ...)
        # db.execute("INSERT INTO user_profiles (user_id, full_name) VALUES ...", ...)
        
        return jsonify({
            "id": user_id, 
            "message": "User registered successfully."
        }), 201
    except IntegrityError:
        return jsonify({"error": "Email already exists"}), 409

Security & Password Hashing

⛔ DO NOT Use: MD5, SHA1, SHA256, or plain text. Fast hashes are vulnerable to brute-force attacks.

Algorithm	Type	Recommendation
Argon2id	Memory-hard	✅ Gold Standard Winner of Password Hashing Competition.
bcrypt	CPU-hard	✅ Excellent Trusted, widely supported (Work factor > 12).
scrypt	Memory-hard	⚠️ Good Better than bcrypt, but Argon2 is preferred.
PBKDF2	CPU-hard	🆗 Acceptable NIST approved, but less resistant to FPGA/ASIC.

Salting & Peppering

Salt: Unique random string per user, stored with hash. Prevents Rainbow Table attacks. (Handled automatically by libraries like Argon2/bcrypt).
Pepper: Secret key stored separate from DB (e.g., config file or KMS). Added to password before hashing. Protects hashes if DB is dumped.

Privacy, GDPR & PII

Handling Personally Identifiable Information requires strict governance.

Encryption at Rest

Encrypt sensitive columns (phone, address, SSN) using AES-256 before writing to DB. Use database-level function or application-level encryption.

Right to be Forgotten

Design a "Soft Delete" vs "Hard Delete" strategy. Hard delete PII upon request, but maybe keep anonymized IDs for logs.

Data Minimization

Don't store what you don't need. Do you really need their birth date? If not, don't ask.

Summary

Separate Identity (Auth) from Profile (Data) tables.
Use Argon2id for password hashing; it resists GPU/ASIC cracking better than bcrypt.
Use UUIDs for user IDs instead of auto-incrementing integers (unpredictable, merge-friendly).
Encrypt PII fields at rest to comply with GDPR/CCPA.
Implement Soft Delete (`deleted_at` column) for data recovery, but support Hard Delete for compliance.