Member Recruitment
Data Entity
Description
Tracks peer-to-peer referral events where a user shares a personalized referral link and a new member registers through it. Captures referrer identity, referral codes, attribution windows, and completion status for recruitment analytics and badge evaluation.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key — unique identifier for each referral event record | PKrequiredunique |
referrer_id |
uuid |
Foreign key to users table — the authenticated user who generated and shared the referral link | required |
referral_code |
string |
Unique short code embedded in the referral URL. Generated server-side by the Referral Edge Function as a URL-safe alphanumeric token (12 characters). Used as the lookup key when a new user follows the link. | requiredunique |
referral_short_url |
string |
The fully resolved short URL displayed to and shared by the referrer. Constructed by the Referral Edge Function from the referral_code and org_slug. Stored for display in the Recruitment Screen without regeneration on every read. | unique |
organization_id |
uuid |
Foreign key to organizations — scopes the referral to the referrer's active organization context at the time the link was generated. Ensures new user sign-up is attributed to the correct association. | required |
org_slug |
string |
Denormalized URL-safe slug for the organization (e.g. 'nhf', 'blindeforbundet', 'hlf'). Embedded in the referral URL to pre-fill the registration context. Stored to avoid a join on every link resolution. | required |
new_user_id |
uuid |
Foreign key to users — populated when a new member completes registration via this referral link. NULL while the referral is pending or expired. Used for reverse lookup (which referral brought this user in). | unique |
status |
enum |
Lifecycle state of the referral. 'pending' — link generated, no sign-up yet. 'completed' — new user registered via this link within the attribution window. 'expired' — attribution window elapsed without a completed sign-up. | required |
attribution_window_days |
integer |
Number of days from created_at within which a new user sign-up is attributed to this referral. Defaults to 30. Configurable per organization to accommodate campaign durations. After this window, the record transitions to 'expired' via a scheduled Edge Function. | required |
expires_at |
datetime |
Computed expiry timestamp (created_at + attribution_window_days). Set at creation time by the Recruitment Service. Used by the Referral Edge Function to reject attribution attempts after the window closes and by the scheduled expiry job. | required |
created_at |
datetime |
UTC timestamp when the referral record was created (link was first generated). Set server-side by Supabase default. Used as the attribution window anchor and for analytics queries. | required |
completed_at |
datetime |
UTC timestamp when the new user completed registration via this referral link. NULL until status transitions to 'completed'. Used for attribution reporting and badge evaluation trigger timing. | - |
referral_click_count |
integer |
Running count of how many times the referral link has been followed (resolved by the Referral Edge Function). Provides engagement analytics even for referrals that do not convert. Incremented atomically server-side. | required |
badge_evaluated |
boolean |
Flag indicating whether the Achievement Service has already run badge evaluation for this completed referral. Prevents duplicate badge awards if the completion event is processed more than once. Only meaningful when status = 'completed'. | required |
metadata |
json |
Flexible key-value store for supplementary attribution context: UTM parameters captured at link resolution, device platform of the new user at sign-up, campaign identifiers for coordinated recruitment drives. Schema is unversioned; consumers must handle missing keys gracefully. | - |
Database Indexes
idx_member_recruitments_referrer_id
Columns: referrer_id
idx_member_recruitments_referral_code
Columns: referral_code
idx_member_recruitments_new_user_id
Columns: new_user_id
idx_member_recruitments_organization_id
Columns: organization_id
idx_member_recruitments_referrer_status
Columns: referrer_id, status
idx_member_recruitments_status_expires_at
Columns: status, expires_at
idx_member_recruitments_created_at
Columns: created_at
idx_member_recruitments_org_status
Columns: organization_id, status, created_at
Validation Rules
referral_code_format
error
Validation failed
referral_short_url_format
error
Validation failed
expires_at_future_on_create
error
Validation failed
attribution_window_bounds
error
Validation failed
status_transition_validity
error
Validation failed
new_user_id_present_on_completion
error
Validation failed
referrer_id_is_active_user
error
Validation failed
organization_id_membership_check
error
Validation failed
metadata_size_limit
warning
Validation failed
click_count_non_negative
error
Validation failed
Business Rules
referral_code_immutability
Once a referral record is created, its referral_code, referrer_id, organization_id, org_slug, and expires_at are immutable. Changing these fields after creation would break in-flight links already shared by the referrer.
self_referral_prevention
new_user_id must never equal referrer_id. A user cannot recruit themselves. Enforced at the database level via a CHECK constraint and validated in the Referral Edge Function before writing the completed record.
attribution_window_enforcement
A referral can only be marked 'completed' if the new user signs up before expires_at. The Referral Edge Function checks this timestamp at sign-up attribution time. Registrations after the window are silently ignored — the referral transitions to 'expired' instead.
one_completion_per_referral
A referral record can only transition to 'completed' once. The unique constraint on new_user_id (when not null) and the status check prevent the same referral from being attributed to multiple new users or the same new user being credited to multiple referrers.
one_referral_credit_per_new_user
Each newly registered user may only be attributed to a single referral record. If multiple pending referrals exist for the same new_user_id (e.g., link shared multiple times), only the first valid attribution within the window is accepted. Enforced by the unique constraint on new_user_id.
badge_evaluation_on_completion
When a referral transitions to 'completed', the Recruitment Service triggers the Achievement Service to evaluate badge eligibility for the referrer. The badge_evaluated flag is set to true after evaluation to prevent duplicate processing.
expired_status_transition
Pending referrals whose expires_at timestamp has passed must be transitioned to 'expired' by a scheduled backend job (Supabase Edge Function or database-level cron). Expired records are read-only — no further status transitions are permitted.
organization_scope_rls
Supabase Row Level Security enforces that a referrer can only read their own referral records. Coordinators can read all referral records within their organizational scope. Global admins have unrestricted read access. No role may read referral records from outside their organization.
completed_at_required_on_completion
When status is set to 'completed', completed_at must be provided and must fall within the attribution window (between created_at and expires_at). A completion without a timestamp is rejected.