User Onboarding System: Low-Level Design
A user onboarding system guides new users through the steps required to activate their account and experience the product’s core value. It tracks completion state per user, sends triggered emails and in-app prompts, and surfaces analytics to measure where users drop off. The critical design challenge is making onboarding state a first-class data model rather than an ad-hoc set of boolean columns scattered across the user table.
Core Data Model
CREATE TABLE OnboardingFlow (
flow_id SERIAL PRIMARY KEY,
flow_key VARCHAR(100) UNIQUE NOT NULL, -- "new_user_2024", "enterprise_trial"
version INT NOT NULL DEFAULT 1,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE OnboardingStep (
step_id SERIAL PRIMARY KEY,
flow_id INT NOT NULL REFERENCES OnboardingFlow(flow_id),
step_key VARCHAR(100) NOT NULL, -- "verify_email", "add_profile_photo", "invite_teammate"
display_name VARCHAR(200) NOT NULL,
step_order SMALLINT NOT NULL, -- display and dependency ordering
is_required BOOLEAN NOT NULL DEFAULT TRUE,
depends_on INT REFERENCES OnboardingStep(step_id), -- must complete this first
trigger_event VARCHAR(100), -- event that auto-completes this step
reward_points SMALLINT NOT NULL DEFAULT 0,
UNIQUE (flow_id, step_key)
);
CREATE TABLE UserOnboarding (
user_id BIGINT NOT NULL,
flow_id INT NOT NULL REFERENCES OnboardingFlow(flow_id),
started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
completed_at TIMESTAMPTZ,
current_step_id INT REFERENCES OnboardingStep(step_id),
PRIMARY KEY (user_id, flow_id)
);
CREATE TABLE UserOnboardingStep (
user_id BIGINT NOT NULL,
step_id INT NOT NULL REFERENCES OnboardingStep(step_id),
status VARCHAR(20) NOT NULL DEFAULT 'pending',
-- pending, in_progress, completed, skipped
started_at TIMESTAMPTZ,
completed_at TIMESTAMPTZ,
skip_reason VARCHAR(200),
PRIMARY KEY (user_id, step_id)
);
CREATE INDEX ON UserOnboarding(flow_id, completed_at) WHERE completed_at IS NULL;
CREATE INDEX ON UserOnboardingStep(step_id, status);
Onboarding State Machine
from dataclasses import dataclass
from typing import Optional, List
import datetime
@dataclass
class StepProgress:
step_key: str
display_name: str
step_order: int
is_required: bool
status: str # pending, in_progress, completed, skipped
is_available: bool # depends_on step is completed
completed_at: Optional[datetime.datetime]
def get_onboarding_status(user_id: int, flow_key: str = 'new_user_2024') -> dict:
"""Return full onboarding state for the user."""
flow = db.fetchone(
"SELECT flow_id FROM OnboardingFlow WHERE flow_key=%s AND is_active=TRUE", (flow_key,)
)
if not flow:
return {'onboarding': None}
uo = db.fetchone(
"SELECT started_at, completed_at FROM UserOnboarding WHERE user_id=%s AND flow_id=%s",
(user_id, flow['flow_id'])
)
if not uo:
_initialize_onboarding(user_id, flow['flow_id'])
uo = {'started_at': datetime.datetime.utcnow(), 'completed_at': None}
steps = db.fetchall("""
SELECT s.step_id, s.step_key, s.display_name, s.step_order,
s.is_required, s.depends_on,
COALESCE(us.status, 'pending') AS status,
us.completed_at
FROM OnboardingStep s
LEFT JOIN UserOnboardingStep us
ON us.step_id=s.step_id AND us.user_id=%s
WHERE s.flow_id=%s
ORDER BY s.step_order
""", (user_id, flow['flow_id']))
completed_ids = {s['step_id'] for s in steps if s['status'] == 'completed'}
step_list = []
for s in steps:
is_available = (s['depends_on'] is None or s['depends_on'] in completed_ids)
step_list.append(StepProgress(
step_key=s['step_key'],
display_name=s['display_name'],
step_order=s['step_order'],
is_required=s['is_required'],
status=s['status'],
is_available=is_available,
completed_at=s['completed_at'],
))
required_steps = [s for s in step_list if s.is_required]
pct_complete = int(
sum(1 for s in required_steps if s.status == 'completed') /
max(len(required_steps), 1) * 100
)
return {
'started_at': uo['started_at'],
'completed_at': uo['completed_at'],
'pct_complete': pct_complete,
'steps': step_list,
'next_step': next(
(s for s in step_list if s.is_available and s.status == 'pending' and s.is_required),
None
),
}
def _initialize_onboarding(user_id: int, flow_id: int):
db.execute("""
INSERT INTO UserOnboarding (user_id, flow_id) VALUES (%s, %s)
ON CONFLICT DO NOTHING
""", (user_id, flow_id))
Step Completion and Event-Driven Triggers
def complete_step(user_id: int, step_key: str, flow_key: str = 'new_user_2024'):
"""Mark a step completed and check if onboarding is now fully done."""
step = db.fetchone("""
SELECT s.step_id, s.depends_on, s.reward_points, s.flow_id
FROM OnboardingStep s
JOIN OnboardingFlow f USING(flow_id)
WHERE s.step_key=%s AND f.flow_key=%s
""", (step_key, flow_key))
if not step:
return
# Check dependency is met
if step['depends_on']:
dep = db.fetchone("""
SELECT status FROM UserOnboardingStep
WHERE user_id=%s AND step_id=%s
""", (user_id, step['depends_on']))
if not dep or dep['status'] != 'completed':
raise StepDependencyError(f"Prerequisite step not completed for {step_key}")
db.execute("""
INSERT INTO UserOnboardingStep (user_id, step_id, status, started_at, completed_at)
VALUES (%s, %s, 'completed', NOW(), NOW())
ON CONFLICT (user_id, step_id) DO UPDATE
SET status='completed', completed_at=NOW()
WHERE UserOnboardingStep.status != 'completed'
""", (user_id, step['step_id']))
if step['reward_points'] > 0:
_award_points(user_id, step['reward_points'], f"onboarding:{step_key}")
_check_flow_completion(user_id, step['flow_id'])
def handle_event(event_type: str, user_id: int, metadata: dict = None):
"""
Auto-complete steps triggered by product events.
Called from application event bus when user actions occur.
Example trigger mappings:
'user.email_verified' → completes step 'verify_email'
'profile.photo_uploaded' → completes step 'add_profile_photo'
'team.member_invited' → completes step 'invite_teammate'
'integration.connected' → completes step 'connect_integration'
"""
steps = db.fetchall("""
SELECT s.step_key, f.flow_key FROM OnboardingStep s
JOIN OnboardingFlow f USING(flow_id)
WHERE s.trigger_event=%s AND f.is_active=TRUE
""", (event_type,))
for step in steps:
try:
complete_step(user_id, step['step_key'], step['flow_key'])
except StepDependencyError:
pass # Dependency not yet met — will be completed when triggered again
def _check_flow_completion(user_id: int, flow_id: int):
incomplete = db.fetchone("""
SELECT COUNT(*) AS cnt FROM OnboardingStep s
LEFT JOIN UserOnboardingStep us ON us.step_id=s.step_id AND us.user_id=%s
WHERE s.flow_id=%s AND s.is_required=TRUE
AND COALESCE(us.status,'pending') != 'completed'
""", (user_id, flow_id))
if incomplete['cnt'] == 0:
db.execute("""
UPDATE UserOnboarding SET completed_at=NOW()
WHERE user_id=%s AND flow_id=%s AND completed_at IS NULL
""", (user_id, flow_id))
_send_completion_event(user_id) # trigger celebration email, unlock features
Drop-Off Analytics Query
-- Where do users abandon onboarding?
-- Shows completion funnel per step for a given flow.
SELECT
s.step_order,
s.step_key,
s.display_name,
COUNT(DISTINCT uo.user_id) AS users_reached,
COUNT(DISTINCT CASE WHEN us.status='completed' THEN us.user_id END) AS users_completed,
ROUND(100.0 * COUNT(DISTINCT CASE WHEN us.status='completed' THEN us.user_id END)
/ NULLIF(COUNT(DISTINCT uo.user_id), 0), 1) AS completion_pct,
ROUND(AVG(EXTRACT(EPOCH FROM (us.completed_at - uo.started_at))/3600), 1)
AS avg_hours_to_complete
FROM OnboardingStep s
JOIN OnboardingFlow f USING(flow_id)
JOIN UserOnboarding uo USING(flow_id)
LEFT JOIN UserOnboardingStep us ON us.step_id=s.step_id AND us.user_id=uo.user_id
WHERE f.flow_key='new_user_2024'
AND uo.started_at >= NOW() - INTERVAL '30 days'
GROUP BY s.step_order, s.step_key, s.display_name
ORDER BY s.step_order;
Key Design Decisions
- Steps as database rows, not code constants: defining steps in OnboardingStep instead of hardcoding them in application logic means new steps can be added without a code deploy, and A/B testing different step orderings (via different flow versions) is a database operation, not a feature flag.
- Event-driven auto-completion: tying step completion to product events (“user.email_verified” → complete “verify_email”) means onboarding state stays accurate without requiring the UI to explicitly call complete_step. The event bus fires regardless of whether the user is in an onboarding flow.
- Dependency graph prevents out-of-order completion: depends_on enforces sequencing without hardcoding step order in application logic. The is_available flag in the status response lets the UI disable locked steps without needing to know the dependency rules.
- ON CONFLICT DO NOTHING for idempotent completion: completing a step twice is harmless — the second INSERT hits the conflict and does nothing. This matters for event-driven triggers that may fire multiple times (user uploads photo, event fires twice — step remains completed, not reset).
User onboarding and activation system design is discussed in LinkedIn system design interview questions.
User onboarding and host activation system design is covered in Airbnb system design interview preparation.
User onboarding and KYC activation system design is discussed in Coinbase system design interview guide.
See also: Atlassian Interview Guide
See also: Shopify Interview Guide