Designing a Secure Invite-Friend Referral System: Token-Based Flow & Database Schema
This article details the backend implementation of a viral 'invite friends to help' marketing campaign, covering the share-token flow, three MySQL table designs for tracking shares, visits, and successful assists, and the security rationale for using tokens over direct user IDs.
Background: Viral Marketing via Social Graphs
As mobile internet customer acquisition costs rise, referral campaigns that leverage social relationships have become a core low-cost growth lever. An "invite friends to help" activity incentivizes users to share via rewards (coupons, free orders, lotteries), using trust between friends to reach and convert new users, forming an "old brings new — new becomes old" growth loop.
Flow Analysis
The flow works across APP and mini-program:
User opens the activity page and taps "Share".
User selects a channel (typically WeChat) and sends the link to a friend.
Friend clicks the link and performs the assist action (e.g., registers). Success criteria vary; a common rule is that the invitee's registration counts as a successful assist.
Two key questions arise:
How to distinguish multiple concurrent "invite friend" activities? → Use marketing_activity_id.
How to record the inviter-invitee relationship when a user can be both an inviter in one activity and an invitee in another? → Store the relationship explicitly in the database.
Implementation Design
Step 1: Inviter Requests a Share Token
When the inviter taps "Share", the client requests a share token from the server. The server persists the current marketing_activity_id, inviter_member_id, and generates a unique share_token.
Core table:
marketing_invite_shareCREATE TABLE `neo-retail`.`marketing_invite_share` (</code><code> `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',</code><code> `marketing_activity_id` bigint(20) NOT NULL COMMENT 'Marketing activity id',</code><code> `inviter_member_id` bigint(20) NOT NULL COMMENT 'Inviter member id',</code><code> `round_id` bigint(20) DEFAULT NULL COMMENT 'Associated invite round id',</code><code> `share_token` varchar(64) NOT NULL COMMENT 'Share identifier, used as link parameter',</code><code> `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',</code><code> PRIMARY KEY (`id`),</code><code> UNIQUE KEY `uk_token` (`share_token`),</code><code> KEY `idx_act_inviter` (`marketing_activity_id`,`inviter_member_id`)</code><code>) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='Invite friend share token';The server returns the share_token to the client.
Step 2: Client Builds the Share Link
The client appends the share_token to the share URL and sends it to the invitee.
Step 3: Invitee Clicks Link — Bind Request
When the invitee opens the link, the client sends a bind request to the server with the share_token and the invitee's identifier (e.g., WeChat openid). The server looks up the token in marketing_invite_share:
Token not found → Return "invalid link" error.
Token found → Retrieve inviter_member_id, marketing_activity_id, and invitee info. Record the visit in marketing_invite_visit.
Core table:
marketing_invite_visitCREATE TABLE `neo-retail`.`marketing_invite_visit` (</code><code> `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',</code><code> `marketing_activity_id` bigint(20) NOT NULL COMMENT 'Marketing activity id',</code><code> `inviter_member_id` bigint(20) NOT NULL COMMENT 'Inviter member id from share link',</code><code> `invitee_open_id` varchar(128) NOT NULL COMMENT 'Invitee WeChat openid',</code><code> `visit_time` datetime NOT NULL COMMENT 'First link open time (insert only, upsert does not update)',</code><code> `last_visit_time` datetime DEFAULT NULL COMMENT 'Last link open time (updated on each open)',</code><code> `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',</code><code> PRIMARY KEY (`id`),</code><code> KEY `idx_act_openid` (`marketing_activity_id`,`invitee_open_id`),</code><code> UNIQUE KEY `uk_act_inviter_openid` (`marketing_activity_id`,`inviter_member_id`,`invitee_open_id`)</code><code>) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='Invite friend link visit tracking';Step 4: Record Assist Result
Based on the activity's success criteria (e.g., invitee registers and places an order), the server records whether the assist succeeded in a separate table.
Core table:
marketing_invite_assistCREATE TABLE `neo-retail`.`marketing_invite_assist` (</code><code> `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',</code><code> `marketing_activity_id` bigint(20) NOT NULL COMMENT 'Marketing activity id',</code><code> `inviter_member_id` bigint(20) NOT NULL COMMENT 'Inviter member id',</code><code> `invitee_member_id` bigint(20) NOT NULL COMMENT 'Invitee member id (registered & logged in)',</code><code> `round_id` bigint(20) DEFAULT NULL COMMENT 'Associated invite round id',</code><code> `assist_status` varchar(2) NOT NULL COMMENT 'Assist status: 1=success, 2=fail',</code><code> `fail_reason` varchar(2) DEFAULT NULL COMMENT 'Failure reason: 1=not new user, 2=already assisted this inviter, 3=activity ended, 4=inviter slots full, 5=no consumption, 6=assist limit reached',</code><code> `visit_time` datetime DEFAULT NULL COMMENT 'Invitee time (first link open)',</code><code> `assist_time` datetime DEFAULT NULL COMMENT 'Assist click time',</code><code> `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',</code><code> `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time',</code><code> PRIMARY KEY (`id`),</code><code> UNIQUE KEY `uk_act_invitee_inviter` (`marketing_activity_id`,`invitee_member_id`,`inviter_member_id`),</code><code> KEY `idx_act_inviter` (`marketing_activity_id`,`inviter_member_id`),</code><code> KEY `idx_invitee` (`invitee_member_id`),</code><code> KEY `idx_act_status` (`marketing_activity_id`,`assist_status`)</code><code>) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='Invite friend assist relationship';Summary & Rationale
Why use a share token instead of embedding the inviter ID directly in the link? Tokens are more secure — they prevent enumeration of user IDs and allow revocation or expiration without changing the inviter's identifier.
Why separate visit tracking ( marketing_invite_visit ) from assist recording ( marketing_invite_assist )? This separation keeps the concerns distinct: visit logs capture every link open (including repeats), while the assist table only records the final success/failure outcome with detailed failure reasons. Both relationships remain clear and queryable.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
