Referral Code
Data Entity
Description
A unique code generated per peer mentor encoding their identity and organization in a shareable URL and QR code for the membership recruitment (verving) feature. Codes can be invalidated and rotated without breaking attribution history. Used by coordinators to track which peer mentors are actively recruiting new members.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Immutable primary key for the referral code record, generated server-side on creation | PKrequiredunique |
peer_mentor_id |
uuid |
Foreign key referencing the peer mentor who owns this referral code. A single mentor may accumulate multiple records over time as codes are rotated or invalidated. | required |
organization_id |
uuid |
Foreign key referencing the organization this code is scoped to. Codes are org-scoped so the same mentor can have separate codes per organization if they hold multi-org membership. | required |
code |
string |
The opaque, URL-safe referral code string. Composed as a base62-encoded value derived from peer_mentor_id and organization_id plus a random nonce to ensure uniqueness and prevent enumeration. | requiredunique |
status |
enum |
Lifecycle status of the referral code. Only one active code may exist per (peer_mentor_id, organization_id) pair. Historical codes are kept for attribution history integrity. | required |
referral_url |
string |
The full deep-link URL embedding the code as a query parameter (e.g., https://app.example.org/join?ref=CODE). Persisted at creation time so that the URL remains stable even if the base domain changes. | requiredunique |
rotation_sequence |
integer |
Monotonically increasing counter tracking how many codes have been generated for this (peer_mentor_id, organization_id) pair. Sequence 0 is the first-ever code; each rotation increments by 1. Enables ordering of historical codes without relying on timestamps alone. | required |
created_at |
datetime |
UTC timestamp when this referral code record was created. Set server-side, immutable after creation. | required |
invalidated_at |
datetime |
UTC timestamp when the code was explicitly invalidated or superseded by a rotation. Null while status is active. Must be after created_at when set. | - |
invalidation_reason |
string |
Human-readable reason for invalidation, used for audit display. Examples: 'rotated_by_mentor', 'coordinator_reset', 'org_offboarding'. | - |
superseded_by_code_id |
uuid |
Self-referential FK to the new referral_code record that replaced this one during a rotation. Null for active codes and codes invalidated without a successor. Enables full rotation chain traversal. | - |
Database Indexes
idx_referral_code_code_unique
Columns: code
idx_referral_code_active_lookup
Columns: peer_mentor_id, organization_id, status
idx_referral_code_peer_mentor
Columns: peer_mentor_id
idx_referral_code_organization
Columns: organization_id
idx_referral_code_referral_url
Columns: referral_url
idx_referral_code_created_at
Columns: created_at
Validation Rules
code_format_url_safe
error
Validation failed
code_globally_unique
error
Validation failed
peer_mentor_id_exists
error
Validation failed
organization_id_exists
error
Validation failed
peer_mentor_belongs_to_organization
error
Validation failed
invalidated_at_after_created_at
error
Validation failed
status_transition_allowed
error
Validation failed
superseded_by_code_id_references_new_code
error
Validation failed
referral_url_contains_code
error
Validation failed
Business Rules
single_active_code_per_mentor_per_org
At most one referral code with status='active' may exist for any given (peer_mentor_id, organization_id) pair. Before creating a new active code, the service must transition any existing active code to 'rotated' and set superseded_by_code_id on the old record.
attribution_history_preserved_on_invalidation
Invalidating or rotating a code must never cascade-delete or alter existing recruitment_attribution records linked to that code. Past click and conversion events remain valid and continue to credit the peer mentor regardless of code status.
only_active_codes_accept_new_attributions
The referral attribution service must reject new click or conversion events referencing a code whose status is 'invalidated' or 'rotated'. Only active codes are eligible for new attribution recording.
rotation_sequence_monotonic
rotation_sequence for a new code must equal max(rotation_sequence) + 1 for the same (peer_mentor_id, organization_id) pair. First-ever code for a mentor-org pair receives sequence 0.
invalidated_at_set_on_status_change
When status transitions from 'active' to 'invalidated' or 'rotated', invalidated_at must be set to the current UTC timestamp and invalidation_reason must be provided. These fields are immutable once set.
no_deletion_soft_invalidation_only
Referral code records must never be hard-deleted. All deactivation is performed by setting status to 'invalidated' or 'rotated'. This preserves the attribution chain and audit trail required for coordinator dashboards and badge criteria.
code_scoped_to_org
A referral code belongs to exactly one organization. Coordinators from other organizations must not be able to read or act on codes outside their organization scope, enforced via Supabase RLS policies.
badge_evaluation_on_confirmed_conversion
When an attribution linked to this code reaches 'confirmed' conversion status, the badge criteria integration must be invoked to check whether the peer mentor has crossed a recruitment milestone threshold (e.g., 1st, 5th, 10th recruit).
CRUD Operations
Storage Configuration
Entity Relationships
A peer mentor may have multiple referral codes over time (active plus rotated/invalidated historical codes) for membership recruitment tracking
A referral code accumulates multiple attribution events (clicks and confirmed conversions) over its active lifetime