Referral
Data Entity
Description
Tracks peer mentor referral invitations and recruitment conversions. Each record represents a single invite link or QR code generated by a referrer, capturing the full lifecycle from generation through click, registration, and conversion to an active peer mentor.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key — unique referral record identifier | PKrequiredunique |
referrer_user_id |
uuid |
FK to users — the peer mentor or coordinator who generated the invite | required |
organization_id |
uuid |
FK to organizations — tenant scope for the referral; recruits join this organization | required |
referee_user_id |
uuid |
FK to users — the person who registered via this referral link; null until registration completes | - |
referral_code |
string |
Unique URL-safe token embedded in the invite link and QR code (e.g. 24-char base62) | requiredunique |
status |
enum |
Lifecycle state of the referral | required |
invite_method |
enum |
Channel through which the invite was shared | required |
expires_at |
datetime |
UTC timestamp after which the referral link is no longer valid; defaults to 30 days from creation | required |
clicked_at |
datetime |
UTC timestamp of first link/QR tap; null until the deep link is opened | - |
registered_at |
datetime |
UTC timestamp when the referee completed account registration | - |
converted_at |
datetime |
UTC timestamp when the referee was activated as a peer mentor (full conversion) | - |
click_count |
integer |
Total number of times the link has been opened; supports detecting shared/forwarded links | required |
source_context |
json |
Optional freeform context captured at generation time (e.g. campaign label, app screen, UTM params) for analytics segmentation | - |
created_at |
datetime |
UTC timestamp of record creation | required |
updated_at |
datetime |
UTC timestamp of last status change or field update | required |
Database Indexes
idx_referrals_referral_code
Columns: referral_code
idx_referrals_referrer_user_id
Columns: referrer_user_id
idx_referrals_organization_id
Columns: organization_id
idx_referrals_status
Columns: status
idx_referrals_referee_user_id
Columns: referee_user_id
idx_referrals_expires_at
Columns: expires_at
idx_referrals_org_status_created
Columns: organization_id, status, created_at
Validation Rules
referral_code_format
error
Validation failed
expires_at_minimum_window
error
Validation failed
click_count_non_negative
error
Validation failed
timestamp_chronology
error
Validation failed
source_context_size_limit
error
Validation failed
referrer_must_be_active
error
Validation failed
Business Rules
single_active_referral_per_referrer
A referrer may have at most one non-expired, non-revoked referral in 'pending' or 'clicked' state per organization at any time. Generating a new link while one is active revokes the previous one to prevent confusion.
referral_code_immutable
The referral_code field must never be changed after creation; it is the stable identifier embedded in shared URLs and QR images that may already be distributed.
expired_links_rejected
Any attempt to redeem a referral whose expires_at is in the past must be rejected. The status is updated to 'expired' on first attempted redemption past the deadline.
referee_must_belong_to_same_org
When a referee completes registration, their organization_id must match the referral's organization_id. Cross-organization referrals are not permitted.
status_transitions_enforced
Status must follow the forward-only lifecycle: pending → clicked → registered → converted. Backward transitions are forbidden. Only 'expired' and 'revoked' may be set from any non-terminal state.
self_referral_prohibited
referee_user_id must never equal referrer_user_id. A peer mentor cannot claim their own referral code.
conversion_requires_registration
Status cannot advance to 'converted' unless referee_user_id is set and registered_at is not null.