Referral Tracking
Data Entity
Description
Records individual referral events when invited users click a referral link or QR code and complete registration, linking outcomes back to the originating referral invite for recruitment analytics.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key | PKrequiredunique |
referral_invite_id |
uuid |
FK to referral_invites — the invite that triggered this tracking event | required |
invited_user_id |
uuid |
FK to users — the user who was recruited via this referral. NULL until the invited person completes registration. | - |
status |
enum |
Current state of this referral tracking record | required |
clicked_at |
datetime |
Timestamp when the invite link or QR code was first clicked | required |
registered_at |
datetime |
Timestamp when the invited user completed account registration. NULL if not yet registered. | - |
activated_at |
datetime |
Timestamp when the invited user was activated as a peer mentor (role assigned and onboarded). NULL if not yet activated. | - |
device_type |
enum |
Device category used when the referral link was clicked | - |
channel |
enum |
How the referral was shared — via link or QR code scan | - |
organization_id |
uuid |
FK to organizations — denormalized from the referral invite for efficient org-scoped queries | required |
created_at |
datetime |
Record creation timestamp | required |
updated_at |
datetime |
Last modification timestamp | required |
Database Indexes
idx_referral_tracking_invite_id
Columns: referral_invite_id
idx_referral_tracking_invited_user_id
Columns: invited_user_id
idx_referral_tracking_organization_id
Columns: organization_id
idx_referral_tracking_status
Columns: status
idx_referral_tracking_clicked_at
Columns: clicked_at
idx_referral_tracking_org_status
Columns: organization_id, status
Validation Rules
referral_invite_id_exists
error
Validation failed
valid_status_value
error
Validation failed
clicked_at_not_future
error
Validation failed
timestamps_chronological
error
Validation failed
invited_user_belongs_to_same_org
error
Validation failed
Business Rules
single_tracking_per_click_session
Each unique click on a referral link creates at most one tracking record. Duplicate clicks from the same session (same invite, same device within a short window) must not create duplicate records.
status_progression_only_forward
Status must progress forward: clicked → registered → activated. Backward transitions are not permitted except to cancelled or expired.
invited_user_required_on_registered
When status transitions to 'registered', invited_user_id must be populated and registered_at must be set.
activated_at_requires_registered
activated_at may only be set after registered_at is already set. A user cannot be activated without first registering.
org_scoped_visibility
Referral tracking records are only visible to coordinators and admins of the organization that owns the referral invite. Global admins require support access grant.
module_toggle_guard
Referral tracking is only accessible when the referral-program module is enabled for the organization.