Referral
Data Entity
Description
Tracks peer mentor referral activity — unique referral codes and links generated by peer mentors and coordinators to recruit new members, with lifecycle status from link generation through invited user registration and activation.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key — unique referral record identifier | PKrequiredunique |
referrer_user_id |
uuid |
FK → users. The peer mentor or coordinator who generated this referral | required |
organization_id |
uuid |
FK → organizations. Tenant scoping — the organization the referral belongs to | required |
referral_code |
string |
Short unique alphanumeric code embedded in the referral link (e.g. 'ABC123'). Used for deep-link routing and tracking | requiredunique |
referral_link |
string |
Full deep-link URL containing the referral code (e.g. 'https://app.meander.no/join?ref=ABC123'). Generated server-side | requiredunique |
channel |
enum |
How the referral was shared — determines QR vs link vs in-app share flow | required |
status |
enum |
Lifecycle state of the referral | required |
referred_user_id |
uuid |
FK → users. Populated once the invited person creates an account. NULL until registration occurs | unique |
referred_email |
string |
Email of the invited person captured at link generation or click time, before they register. Used for pre-registration tracking | - |
clicked_at |
datetime |
Timestamp when the referral link was first clicked. NULL if never clicked | - |
registered_at |
datetime |
Timestamp when the referred person completed account registration via this referral | - |
activated_at |
datetime |
Timestamp when the referred user was approved/activated as a peer mentor or member — the final conversion milestone | - |
expires_at |
datetime |
Expiry datetime for this referral link. After this point the link is invalid for new registrations | required |
click_count |
integer |
Number of times the referral link has been clicked (distinct or total). Used in dashboard metrics | required |
notes |
text |
Optional free-text note added by the referrer (e.g. 'Sent to Ola after Thursday meeting') | - |
created_at |
datetime |
Record creation timestamp | required |
updated_at |
datetime |
Last modification timestamp — updated on every status transition | 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_referred_user_id
Columns: referred_user_id
idx_referrals_status
Columns: status
idx_referrals_referrer_org_status
Columns: referrer_user_id, organization_id, status
idx_referrals_expires_at
Columns: expires_at
Validation Rules
referral_code_format
error
Validation failed
referral_link_format
error
Validation failed
expires_at_future
error
Validation failed
referred_email_format
error
Validation failed
referrer_must_be_active_user
error
Validation failed
click_count_non_negative
error
Validation failed
Business Rules
no_self_referral
A user cannot be both the referrer and the referred_user_id. Prevents gaming of recruitment metrics
unique_referred_user
A user can only be successfully referred once. If a user attempts to register via multiple referral links, only the first valid claim is accepted
expired_link_rejection
Referral links with expires_at < NOW() cannot transition to 'registered' or 'active' status. Clicks are still recorded but no conversion is credited
status_forward_only
Status transitions must follow the lifecycle order: pending → clicked → registered → active. No backward transitions except to 'cancelled' or 'expired'
organisation_scoped_referral
A referral always belongs to the referrer's active organization at time of creation. Referred users are onboarded into the same organization
auto_expire_pending_referrals
Referrals with status 'pending' or 'clicked' whose expires_at has passed are automatically transitioned to 'expired' by a scheduled job