Lead Lookup System Design (case study)

This case study explains how to design a high-scale lead lookup system for 200M users, where the main requirement is fast lookup by phone number, email address, or SSN while still supporting contact reassignment and full contact history.

Table of Contents

• What Problem Are We Solving?
  • Business Use Case
  • Lookup Examples
  • Important Assumption
• Requirements
  • Functional Requirements
  • Non-Functional Requirements
  • Data Shape
• Solution 1: PostgreSQL
  • PostgreSQL Data Model
  • Lookup Queries
  • Contact History
  • PostgreSQL Pros and Cons
• Solution 2: ClickHouse
  • Core Problem
  • Reassignment Example
  • Where ClickHouse Works Well
• Solution 3: PostgreSQL + ClickHouse Hybrid
  • Hybrid Architecture
  • Query Flow
  • Why This Design Works
• Solution Comparison
• Final Recommendation

What Problem Are We Solving?

The system needs to store lead data for around 200M users and resolve a user profile from a contact identifier. A typical request receives one phone number, email address, or SSN and returns the associated user profile.

At first glance, this looks like a simple lookup problem. However, the design becomes more interesting because contacts can change over time. A phone number may be reassigned from one user to another. An email may be removed and later attached to a different lead. The system also needs to preserve historical contact changes without slowing down the hot lookup path.

Business Use Case

Imagine a CRM or lead enrichment platform. An external system sends a phone number such as +12025551234, and the lookup service must return the matching lead profile.

Input:
phone = +12025551234

Output:
user_id = 123
lead_data = {...}

The same lookup should work for email and SSN.

email = john.doe@example.com  -> user_id = 123
ssn   = ***-**-1234           -> user_id = 123

Lookup Examples

The most important access patterns are direct key-based lookups:

• Find user by phone number.
• Find user by email address.
• Find user by SSN.
• Fetch the latest lead profile by user ID.
• Show contact history for a user.

This is not primarily an analytics system. The main workload is operational lookup.

Important Assumption

A typical request maps one contact identifier to exactly one active user. Phone numbers and emails can change ownership over time, but at any single moment, one active phone or email belongs to only one user.

That assumption is important because it allows us to enforce uniqueness in the lookup indexes.

Requirements

Functional Requirements

• Lookup by phone: Given a phone number, return the associated user profile.
• Lookup by email: Given an email address, return the associated user profile.
• Lookup by SSN: Given an SSN, return the associated user profile.
• Multiple contacts: A user can have multiple phone numbers and multiple emails.
• SSN relationship: A user has exactly one SSN.
• Uniqueness: Each active phone number and email belongs to only one user at a time.
• Reassignment: A contact can move from one user to another.
• History: All contact changes should be tracked separately from the hot lookup path.

Non-Functional Requirements

• Scale: 200M users.
• Read load: Around 200K lookups per day, roughly 2-3 QPS on average.
• Write load: 10-30% of users updated once every 1-2 months.
• Latency: Lookups should be fast and predictable.
• Correctness: A contact lookup should not return stale ownership after reassignment.

The read volume is not extremely high compared to many internet-scale systems. The main challenge is the size of the dataset, uniqueness requirements, correctness during reassignment, and maintaining history.

Data Shape

Most users have one phone number and one email address. A very small percentage may have two or three. This matters because it means the contact index tables will be close to the user count in size, not hundreds of times larger.

200M users

Approximate active contacts:
phones: ~200M
emails: ~200M
ssns:   ~200M

The history table may grow much larger over time, but it does not need to be part of the hot lookup path.

Solution 1: PostgreSQL

PostgreSQL is a strong fit for the hot lookup path because it supports indexes, uniqueness constraints, transactions, foreign keys, and predictable point lookups. For this workload, correctness matters more than analytical scan speed.

PostgreSQL Data Model

The main user profile data can be stored in a leads table. The profile itself can be stored as JSONB if the lead attributes are flexible.

CREATE TABLE leads (
    user_id BIGSERIAL PRIMARY KEY,
    lead_data JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Phone numbers and emails are stored in separate index tables. This keeps lookups simple and enforces uniqueness at the database level.

CREATE TABLE phone_index (
    phone VARCHAR(20) PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES leads(user_id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_phone_user ON phone_index USING btree(user_id);

CREATE TABLE email_index (
    email VARCHAR(255) PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES leads(user_id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_email_user ON email_index USING btree(user_id);

Because phone and email are already primary keys, separate unique constraints are unnecessary. The primary key already guarantees uniqueness.

SSN Index

SSNs should not be stored in plain text. A safer design is to store a hash for lookup and optionally keep the last four digits for display or verification workflows.

CREATE TABLE ssn_index (
    ssn_hash BYTEA PRIMARY KEY,
    ssn_last4 CHAR(4) NOT NULL,
    user_id BIGINT UNIQUE NOT NULL REFERENCES leads(user_id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_ssn_user ON ssn_index USING btree(user_id);

In a real production system, SSN hashing should use a controlled normalization process and a secret pepper outside the database. The important design idea is simple: the lookup table should support exact match lookup without exposing raw SSNs.

Lookup Queries

A phone lookup is a direct indexed lookup followed by a join to the lead profile.

SELECT l.*
FROM leads l
JOIN phone_index p ON l.user_id = p.user_id
WHERE p.phone = '+12025551234';

An email lookup follows the same pattern.

SELECT l.*
FROM leads l
JOIN email_index e ON l.user_id = e.user_id
WHERE e.email = 'john.doe@example.com';

An SSN lookup uses the normalized hash.

SELECT l.*
FROM leads l
JOIN ssn_index s ON l.user_id = s.user_id
WHERE s.ssn_hash = decode('...', 'hex');

These are simple point lookups. PostgreSQL is very good at this pattern, especially when the indexed value is selective and the query returns one row.

Contact History

Contact history should be stored separately so it does not slow down the active lookup tables.

CREATE TABLE contact_history (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES leads(user_id) ON DELETE CASCADE,
    contact_type VARCHAR(20) NOT NULL CHECK (contact_type IN ('phone', 'email', 'ssn')),
    contact_value VARCHAR(255) NOT NULL,
    added_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    removed_at TIMESTAMPTZ
);

CREATE INDEX idx_contact_history_user
ON contact_history(user_id, added_at DESC);

CREATE INDEX idx_contact_history_contact
ON contact_history(contact_type, contact_value);

For example, if a phone number moves from User 123 to User 456, the active lookup table should contain only the current owner. The history table should preserve the previous ownership.

phone_index:
+12025551234 -> user_id 456

contact_history:
+12025551234 -> user_id 123, removed_at = 2026-06-01
+12025551234 -> user_id 456, removed_at = NULL

Contact Reassignment Example

Reassignment should happen inside a transaction to avoid temporary duplicate ownership or partial updates.

BEGIN;

UPDATE contact_history
SET removed_at = NOW()
WHERE contact_type = 'phone'
  AND contact_value = '+12025551234'
  AND removed_at IS NULL;

DELETE FROM phone_index
WHERE phone = '+12025551234';

INSERT INTO phone_index (phone, user_id)
VALUES ('+12025551234', 456);

INSERT INTO contact_history (user_id, contact_type, contact_value)
VALUES (456, 'phone', '+12025551234');

COMMIT;

This is where PostgreSQL is strong. The active lookup state and history update can be changed atomically.

PostgreSQL Pros and Cons

For this workload, PostgreSQL is the simplest correct solution for the hot lookup path.

Solution 2: ClickHouse

ClickHouse is excellent for analytical workloads, compression, fast scans, and append-heavy data. However, this lead lookup system is primarily a transactional lookup system, not an analytics-first system.

Core Problem

The main issue is that ClickHouse engines such as ReplacingMergeTree are optimized for append-only analytical patterns, not strict transactional ownership updates.

ReplacingMergeTree can eventually collapse multiple versions of the same logical row during background merges. But “eventually” is the problem. A lookup system needs to know the current owner of a phone number immediately after reassignment.

Reassignment Example

Consider this reassignment:

INSERT INTO phone_index VALUES ('+12025551234', 123, now());
INSERT INTO phone_index VALUES ('+12025551234', 456, now());

A simple query may see both rows until background merge or until the query explicitly resolves the latest version.

SELECT *
FROM phone_index
WHERE phone = '+12025551234';

That can return:

+12025551234 -> user_id 123
+12025551234 -> user_id 456

You can use FINAL or query patterns like ORDER BY updated_at DESC LIMIT 1, but that adds query overhead and still does not give the same transactional guarantees as PostgreSQL.

Why This Is Risky for Lookups

• Eventual deduplication: multiple versions can exist before merges.
• No foreign keys: ClickHouse does not enforce relational ownership rules like PostgreSQL.
• No simple transactional uniqueness: enforcing one active owner per contact becomes application responsibility.
• Query overhead: using FINAL can be expensive on large datasets.

For analytics, these tradeoffs are usually acceptable. For a correctness-sensitive lookup service, they are dangerous.

Where ClickHouse Works Well

ClickHouse is still very valuable in this system, but for a different part of the architecture.

• Contact history analytics: analyze how often contacts change.
• Lead reporting: aggregate users by source, region, status, or campaign.
• Storage efficiency: compress large historical datasets.
• Batch ingestion: handle high-volume append-only history records.

For example, ClickHouse is a great fit for this query:

SELECT
    toYYYYMM(added_at) AS month,
    contact_type,
    count() AS additions,
    uniq(user_id) AS unique_users
FROM contact_history
GROUP BY month, contact_type
ORDER BY month DESC;

This is an analytical scan and aggregation. ClickHouse is designed exactly for this kind of workload.

Solution 3: PostgreSQL + ClickHouse Hybrid

The hybrid design separates the system into two parts: PostgreSQL for correctness-sensitive lookups and ClickHouse for historical storage and analytics.

Hybrid Architecture

Application
  │
  ├── PostgreSQL
  │     ├── phone_index
  │     ├── email_index
  │     └── ssn_index
  │
  └── ClickHouse
        ├── leads_history
        └── contact_history

PostgreSQL owns the hot lookup path. ClickHouse owns analytics and large append-only history.

• PostgreSQL: active phone, email, and SSN indexes.
• PostgreSQL: uniqueness constraints and reassignment transactions.
• ClickHouse: contact history and analytical reporting.
• ClickHouse: large compressed historical datasets.

Query Flow

A typical lookup has two steps. First, PostgreSQL resolves the identifier to a user ID.

SELECT user_id
FROM phone_index
WHERE phone = '+12025551234';

Then the application fetches the latest lead profile. Depending on consistency needs, this can come from PostgreSQL or ClickHouse.

SELECT lead_data
FROM leads
WHERE user_id = 123;

If ClickHouse stores lead versions, the query may look like this:

SELECT lead_data
FROM leads
WHERE user_id = 123
ORDER BY updated_at DESC
LIMIT 1;

For strict correctness, keeping the latest lead profile in PostgreSQL is simpler. ClickHouse can store historical versions and analytics.

Why This Design Works

This design uses each database for what it does best.

Solution Comparison

Final Recommendation

For the stated workload, PostgreSQL is the best default choice for the hot lookup path. The system has 200M users, but only around 200K lookups per day. That read load is not high enough to justify using an analytics database for correctness-sensitive point lookups.

The strongest design is either PostgreSQL-only or a hybrid PostgreSQL + ClickHouse architecture. PostgreSQL should own active lookup indexes because it provides primary keys, uniqueness constraints, transactions, and predictable indexed lookups. ClickHouse should be introduced if the system needs large-scale historical analytics, reporting, compression, or fast aggregation over contact changes.

A practical final architecture would be:

• PostgreSQL: active lead profile, phone index, email index, SSN index.
• PostgreSQL: transactional contact reassignment.
• ClickHouse: append-only contact history and reporting.
• S3 or object storage: optional archive for raw imports and exports.

Use PostgreSQL when correctness and active lookup ownership matter. Use ClickHouse when historical analytics and large-scale aggregations matter. Use both when the system needs both fast transactional lookups and analytical reporting.