Design referral tracking from invite to conversion

What's really being asked

The interviewer wants to see if you can model a stateful business process across multiple tables rather than treating a referral as a single boolean flag. The core challenge is separating the referral code, the referral relationship, and the reward payout into distinct entities with proper constraints, while designing APIs that handle the full lifecycle from link generation to conversion triggering.

The full answer

First, data models: a users table; a referral_codes table with columns code, user_id as referrer, created_at, and a unique constraint on code to prevent collisions; a referrals table with referrer_id, invitee_id, code_used, status as enum like pending or converted, converted_at, and created_at; and a rewards table with referral_id, amount, recipient_id, and an idempotency_key to ensure exactly-once payout. Second, API endpoints: POST generateCode which creates a referral_codes row or returns an existing one for the authenticated user; POST applyCode called during signup with body code and new_user_id to write a pending row into referrals; POST markConverted triggered by the first transaction event to flip status to converted and enqueue a reward job; and GET referrals to list conversions with filters. Third, concurrency controls: use database unique constraints on referral_codes.code to handle race conditions during generation, and use the rewards idempotency_key or a composite unique constraint on referral_id plus recipient_id to prevent double payout. Fourth, status machine clarity: distinguish between signed up and converted because the business rule says rewards only flow after the first transaction, not at registration.

The mistakes people make

Storing a referral_count integer on the users table instead of normalizing referrals; this loses audit history and creates update race conditions. Collapsing the referral event and reward into one table, which makes it impossible to retry payouts independently if a downstream payment provider fails. Failing to enforce unique constraints on generated codes, leading to silent overwrites or collisions under load. Designing a single markReferral endpoint that both creates the user and pays out immediately, which breaks when the first transaction requirement is introduced later.

What usually comes next

How would you prevent self-referral or fraud rings? How would you scale code generation if you need millions of codes per hour? How do you handle reward payout failures or reconcile partial transactions? Would you use an async queue for reward distribution, and how do you guarantee at-least-once delivery without duplicate payouts?

A concrete example

A user with email alice at example dot com calls POST generateCode and receives code A1B2C. The system inserts into referral_codes with user_id pointing to Alice. Bob signs up using code A1B2C; the signup service calls POST applyCode which writes a referrals row with status pending. When Bob completes his first transaction, the transaction service emits an event that triggers POST markConverted for that referral row, flipping status to converted and inserting a rewards row with amount 201 for Alice and amount 51 for Bob, each with a unique idempotency key derived from the transaction ID.