Recruitment Attribution
Data Entity
Description
Records click and conversion events for member recruitment via referral codes. A click event is created when the referral URL is opened; a confirmed conversion event is created when the new member completes registration and membership is verified. Attribution data drives coordinator recruitment dashboards and badge criteria for recruiting milestones.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Immutable primary key for the attribution event record, generated server-side on insert | PKrequiredunique |
referral_code_id |
uuid |
Foreign key referencing the referral_code that was used. Never null — every attribution event originates from a specific code | required |
peer_mentor_id |
uuid |
Denormalized peer mentor user ID copied from the referral_code at insert time. Enables fast dashboard aggregation without joining referral_code on every query | required |
organization_id |
uuid |
Organization scope for Supabase RLS enforcement. Denormalized from referral_code at insert time | required |
event_type |
enum |
Discriminator for the two lifecycle stages of recruitment attribution. 'click' is recorded when the referral URL is first opened; 'conversion' is recorded when the new member completes registration and membership is verified | required |
new_member_id |
uuid |
User ID of the newly registered member. Null for click events. Required for conversion events once membership registration is verified | - |
is_confirmed |
boolean |
True once the conversion event has been verified — i.e., the new member's membership record is active in the member management system. Always false for click events | required |
device_metadata |
json |
Device and browser context captured at click time: user_agent, platform (ios/android/web), screen resolution, locale. Used for recruitment analytics. Null on server-side conversion confirmation events | - |
referral_url |
string |
The full referral URL that was opened, including query parameters. Captures the exact deep link variant for analytics and debugging | - |
created_at |
datetime |
UTC timestamp when the attribution event was recorded. Immutable after insert. For click events this is when the link was opened; for conversion events this is when verification completed | required |
confirmed_at |
datetime |
UTC timestamp when is_confirmed was set to true. Null until membership verification succeeds. For click events this is always null | - |
ip_address |
string |
Anonymized or hashed IP address of the device that opened the referral link. Stored for fraud detection and duplicate click analysis. Null for server-side conversion events | - |
Database Indexes
idx_recruitment_attribution_referral_code_id
Columns: referral_code_id
idx_recruitment_attribution_peer_mentor_id
Columns: peer_mentor_id
idx_recruitment_attribution_org_event_type
Columns: organization_id, event_type
idx_recruitment_attribution_new_member_id
Columns: new_member_id
idx_recruitment_attribution_created_at
Columns: created_at
idx_recruitment_attribution_unique_conversion_per_member
Columns: new_member_id
Validation Rules
event_type_enum_values
error
Validation failed
new_member_id_null_check_on_click
error
Validation failed
new_member_id_required_on_conversion
error
Validation failed
confirmed_at_consistency_with_is_confirmed
error
Validation failed
device_metadata_schema_validation
warning
Validation failed
referral_url_max_length
error
Validation failed
created_at_immutable
error
Validation failed
Business Rules
click_event_has_no_member_id
Click events must never carry a new_member_id. The member is unknown at link-open time. Enforcement prevents accidental pre-population of the member field during click recording
conversion_event_requires_member_id
Conversion events must supply a non-null new_member_id. A conversion without an identified member is meaningless for attribution and badge awarding
single_confirmed_conversion_per_new_member
A new member may only be attributed to exactly one referral code. The unique index on new_member_id enforces this at the database level. If a duplicate conversion arrives for the same new_member_id, the insert is rejected
confirmation_is_one_way_transition
Once is_confirmed is set to true it cannot be reverted. confirmed_at is set atomically alongside is_confirmed. This preserves the immutable audit trail required for badge milestone calculation
attribution_events_are_append_only
Rows in this table are never deleted. The entity serves as an immutable audit log for all recruitment activity. Deletion is prohibited to preserve historical badge and dashboard accuracy
referral_code_must_exist_at_insert
The referral_code_id must reference an active or previously active referral code. Even invalidated or rotated codes must remain in the referral_code table (soft-delete only) so that historical attribution events remain consistent
peer_mentor_id_denormalized_from_code
peer_mentor_id and organization_id are copied from the resolved referral_code at insert time, not supplied by the client. This prevents callers from spoofing attribution to a different peer mentor
CRUD Operations
Storage Configuration
Entity Relationships
A referral code accumulates multiple attribution events (clicks and confirmed conversions) over its active lifetime