Referral Trackings
Data Entity
Description
Records conversion funnel events for referral links, tracking each step from link click through app install, account creation, and first login. Enables attribution of new member sign-ups to the referring peer mentor or coordinator.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Unique identifier for each referral tracking event | PKrequiredunique |
referral_link_id |
uuid |
Foreign key to the referral link that generated this tracking event | required |
event_type |
enum |
The conversion funnel stage this event represents | required |
referred_user_id |
uuid |
Foreign key to the user account created through this referral, populated once account_created event fires | - |
organization_id |
uuid |
Organization context inherited from the referral link for tenant-scoped reporting | required |
ip_address_hash |
string |
SHA-256 hash of the client IP address for deduplication without storing PII | - |
user_agent |
text |
Browser or app user agent string for device and platform analytics | - |
platform |
enum |
Platform where the referral event originated | - |
utm_source |
string |
UTM source parameter from the referral link for channel attribution | - |
utm_medium |
string |
UTM medium parameter from the referral link | - |
utm_campaign |
string |
UTM campaign parameter from the referral link | - |
metadata |
json |
Additional context data for the tracking event (e.g., share method, deep link parameters) | - |
created_at |
datetime |
Timestamp when this tracking event was recorded | required |
Database Indexes
idx_referral_trackings_link_id
Columns: referral_link_id
idx_referral_trackings_org_created
Columns: organization_id, created_at
idx_referral_trackings_event_type
Columns: event_type, created_at
idx_referral_trackings_referred_user
Columns: referred_user_id
idx_referral_trackings_dedup
Columns: referral_link_id, event_type, ip_address_hash
Validation Rules
valid_referral_link_id
error
Validation failed
valid_event_type_enum
error
Validation failed
valid_referred_user_if_present
error
Validation failed
valid_platform_enum
error
Validation failed
ip_hash_format
error
Validation failed
valid_organization_id
error
Validation failed
metadata_json_valid
error
Validation failed
utm_parameters_sanitized
error
Validation failed
Business Rules
referral_link_must_exist
Every tracking event must reference a valid, non-expired referral link
funnel_event_ordering
Events should follow logical funnel progression: link_clicked → app_installed → account_created → first_login → onboarding_completed. Warn if events arrive out of order.
deduplicate_click_events
Multiple link_clicked events from the same IP hash and referral link within a 24-hour window are deduplicated to prevent inflated metrics
referred_user_required_on_account_created
The referred_user_id field must be populated when event_type is account_created, first_login, or onboarding_completed
organization_scope_isolation
All tracking queries and aggregations must be scoped to the requesting user's organization for multi-tenant isolation
append_only_tracking
Tracking events are append-only for audit integrity. Events cannot be deleted or modified after creation.
single_account_per_referral
A referral link should produce at most one account_created event per unique referred_user_id to prevent double-counting conversions