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).
{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”What is the "aha moment" and how does onboarding design target it?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”The aha moment is the specific point in the product where a new user experiences the core value for the first time — the moment they "get it." For Slack, it’s sending 2,000 messages in a team. For Dropbox, it’s uploading a file and seeing it appear on another device. For GitHub, it’s making a commit. Onboarding is designed to reach the aha moment as fast as possible: remove every step between signup and that moment that isn’t strictly necessary. Measurement: identify the aha moment by cohort analysis — what action, performed within the first N days, most strongly predicts 30-day retention? The action with the highest predictive power is the aha moment. Onboarding steps that don’t lead toward that action are candidates for removal. A 5-step onboarding that ends at the aha moment converts better than a 10-step onboarding that ends at a profile completion screen.”}},{“@type”:”Question”,”name”:”How do you prevent onboarding from blocking users who want to explore the product immediately?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Mandatory blocking onboarding (you cannot proceed until you complete step N) has high drop-off rates — users who want to explore first are forced into a linear flow. Two non-blocking alternatives: (1) Progressive onboarding — show a persistent checklist widget (collapsible, in the sidebar or dashboard) that surfaces onboarding steps as suggestions, not gates. Users can complete steps in any order or skip them entirely. Track completion in UserOnboardingStep. (2) Contextual prompts — instead of showing all steps upfront, trigger them at the relevant moment: the first time the user visits a feature, show a tooltip for that feature’s setup step. Both approaches rely on the same data model (OnboardingStep, UserOnboardingStep) — the difference is purely in UI presentation and the trigger logic. Measure: A/B test blocking vs. non-blocking to compare 7-day activation rates.”}},{“@type”:”Question”,”name”:”How do you handle onboarding for products with multiple user roles?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”An admin onboarding a workspace has different steps than a regular member joining an existing workspace. Implementation: maintain separate OnboardingFlow rows per role (flow_key: "admin_setup_2024", "member_join_2024"). Assign users to the appropriate flow at signup based on their role: an admin who creates a workspace starts the admin flow; a member who accepts an invite starts the member flow. The step completion events are role-specific: admin flow has "invite_team_member", "configure_billing", "connect_integration"; member flow has "complete_profile", "post_first_message", "react_to_a_message". Both flows share the same UserOnboarding and UserOnboardingStep tables — the flow_id differentiates them. A user can be enrolled in multiple flows simultaneously (e.g., an admin is also a member and progresses through both flows independently).”}},{“@type”:”Question”,”name”:”How do you trigger onboarding emails at the right time without spamming users?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Onboarding emails are triggered by step completion state and time since signup, not just time-based drip sequences. Three patterns: (1) Completion-triggered: when a user completes step N, trigger step N+1’s prompt email (if they haven’t started it in 24 hours). Only sent if the next step is in pending state — not sent if the user is already working on it. (2) Inactivity-triggered: if a user hasn’t made progress in 48 hours and onboarding is <50% complete, send a "continue where you left off" email with a link to the next step. Query: SELECT user_id FROM UserOnboarding WHERE completed_at IS NULL AND started_at < NOW()-INTERVAL ’48h’ AND NOT EXISTS (SELECT 1 FROM UserOnboardingStep WHERE user_id=uo.user_id AND completed_at > NOW()-INTERVAL ’48h’). (3) Abandonment recovery: users who started but didn’t complete onboarding within 7 days get a single re-engagement email (not a sequence — sequences have diminishing returns and irritate users).”}},{“@type”:”Question”,”name”:”How do you use cohort analysis to identify the highest-leverage step to improve?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Not all onboarding steps have equal impact on activation. Steps with low completion rates but high correlation with retention are the highest-leverage improvement targets. Analysis: SELECT s.step_key, COUNT(DISTINCT us.user_id) AS completed, AVG(CASE WHEN u30.retained THEN 1.0 ELSE 0.0 END) AS retention_30d FROM OnboardingStep s LEFT JOIN UserOnboardingStep us ON us.step_id=s.step_id AND us.status=’completed’ LEFT JOIN UserRetention30d u30 ON u30.user_id=us.user_id WHERE s.flow_key=’new_user_2024′ GROUP BY s.step_key ORDER BY retention_30d DESC. Steps with high retention_30d correlation but low completion_rate are broken: users who complete them retain well, but most users don’t complete them. Fix the friction (simplify the UI, add a tooltip, reduce required fields). Steps with high completion but low retention correlation are noise: users complete them but it doesn’t drive value. Consider removing them to shorten the onboarding path.”}}]}
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