Referral Tracking
Data Entity
Description
Records individual conversion events in the referral funnel — click, install, registration, first login, and activation — attributed to a specific referral link. Enables coordinator dashboards to measure recruiter performance and track new volunteer acquisition through peer-driven sharing.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key. Unique identifier for each tracking event record. | PKrequiredunique |
referral_link_id |
uuid |
Foreign key to referral_links. The referral link this conversion event is attributed to. | required |
event_type |
enum |
The stage in the referral conversion funnel this record represents. | required |
event_timestamp |
datetime |
UTC timestamp when the conversion event occurred. | required |
attributed_user_id |
uuid |
Foreign key to users. Set when the referred individual completes registration. Null for pre-registration events (clicked, installed). | - |
organization_id |
uuid |
Denormalized foreign key to organizations. Copied from the referral link's owner at insert time for efficient tenant-scoped queries without an extra join. | required |
device_type |
enum |
The platform from which the referral link was triggered. | - |
ip_address_hash |
string |
One-way SHA-256 hash of the visitor's IP address salted with a server secret. Used for deduplication of click events without storing PII. Max 64 chars. | - |
user_agent_hash |
string |
One-way SHA-256 hash of the User-Agent string for deduplication purposes. Max 64 chars. | - |
referral_token |
string |
The short token extracted from the referral URL at the time of the click event. Stored to support forensic attribution if the referral_link record is later updated. Max 128 chars. | - |
metadata |
json |
Unstructured key-value bag for event-specific context: UTM parameters, app store attribution data, deep-link path. Schema varies by event_type. | - |
created_at |
datetime |
UTC timestamp when this record was inserted. Used as the canonical event time for pipeline ordering. | required |
Database Indexes
idx_referral_tracking_link_id
Columns: referral_link_id
idx_referral_tracking_org_event
Columns: organization_id, event_type, event_timestamp
idx_referral_tracking_attributed_user
Columns: attributed_user_id
idx_referral_tracking_link_event_type
Columns: referral_link_id, event_type
idx_referral_tracking_created_at
Columns: created_at
uq_referral_tracking_user_registered
Columns: attributed_user_id, event_type
Validation Rules
referral_link_exists
error
Validation failed
event_type_valid
error
Validation failed
event_timestamp_not_future
error
Validation failed
attributed_user_required_for_post_click_events
error
Validation failed
ip_hash_format
error
Validation failed
metadata_json_valid
error
Validation failed
device_type_enum
error
Validation failed
Business Rules
one_registered_event_per_user
A user may only have one 'registered' tracking event across all referral links. The partial unique index uq_referral_tracking_user_registered enforces this at the database level for non-null attributed_user_id values.
event_funnel_ordering
Funnel events must follow the logical order: clicked → installed → registered → first_login → activated. A 'registered' event cannot be inserted for a referral_link_id that has no prior 'clicked' event for the same ip_address_hash within a 30-day window.
organization_id_denormalization
The organization_id field must be populated by copying the value from the parent referral_link.organization_id at insert time. It is never set directly by the caller and must not be updatable.
tenant_isolation
All read queries on this table must include a WHERE organization_id = :orgId clause. The Referral Tracking Service enforces this on every query before returning results to the Recruitment Dashboard.
append_only
Referral tracking records are immutable once created. No UPDATE or DELETE operations are permitted outside of GDPR erasure workflows. The Referral Tracking Service exposes no update mutation.
click_deduplication
Consecutive 'clicked' events from the same ip_address_hash and user_agent_hash for the same referral_link_id within a 1-hour window are silently dropped to prevent bot inflation of click counts.
attribution_lock
Once attributed_user_id is set on any record for a given referral link, subsequent registration events for that user_id referencing different referral links are rejected — first-touch attribution is the canonical model.