Yoyacoo Referral Program — Specification

Last updated: 2026-07-07
Status: Confirmed (overrides all prior Slack discussions) — includes (a) expiration rule fix (referrer tickets never expire; referee tickets expire 90 days from issuance), (b) upgrade-with-tickets fix (pro-rate over paid portion only; free upgrade in bonus-only period; bonus months carried over), and © deferred downgrade (downgrade is scheduled for billing_use_end_date; user keeps current plan and any active bonus until then)

---

1. Overview

1.1 Purpose

Accelerate Yoyacoo’s new user acquisition and conversion of those users to paying customers.

1.2 Scope

Grant rewards to both existing users (referrers) and new users (referees) when the latter sign up via the former’s referral link.

1.3 Out of Scope (not implemented in this version)

• Invitation code input field (link only)
• Cash rewards / agency commissions
• Ranking features
• Complex fraud detection
• Automated referral emails
• Auto-generated social share text
• Agency-only admin screen
• Yearly → Monthly plan switch (not supported in the current system)

---

2. Core Design Principles

2.1 All rewards are unified as “payment + billing cycle extension”

Every reward is applied in the same way, regardless of whether the user is a referrer/referee or on a monthly/yearly plan:

• Push the referrer’s user_plans.billing_use_end_date forward
• Sync the Stripe Subscription’s current_period_end
• No actual payment transaction occurs (Stripe Coupon is NOT used)

Approaches like “first month free” or “% discount” via Stripe are explicitly rejected.

2.2 Two fully independent counters

A. Free registration counter: +1 month for every 3 referrals. Stock cap of 1 (no new ticket is granted while one is still held). Resets to 0 after the held ticket transitions to consumed or voided. There is no expiration-driven reset — referrer tickets never expire (see §4).

B. Paid conversion counter: +1 month per paid conversion. No cap, no expiration on the counter. At exactly 5 paid conversions, an additional +7 months are granted (5 normal + 7 bonus = 12 total). The 5-person milestone bonus is one-time only; from the 6th person onward, only the +1-month standard rule continues.

Important: Mixed judgment is prohibited. The “5 people” milestone uses ONLY Counter B. A combination like “3 free signups + 2 paid conversions = 5” does NOT count.

2.3 Referee reward

A referee who signs up via a referral link and subscribes to a paid plan within 90 days of registration receives a +1-month ticket.

• The 90-day window is computed dynamically from the registration date (no hard-coded dates).

---

3. Application Timing

---

4. Expiration Rules

Expiration is split by ticket holder — referrer tickets never expire; referee tickets do.

Referee eligibility window (§2.3) is unchanged: the referee must subscribe to a paid plan within 90 days of registration to qualify for the ticket. Once issued, the referee_reward ticket has its own 90-day lifetime from granted_at.

Pre-expiration notifications: Sent 14 days and 3 days before expiration via email + in-app banner. These notifications fire only for referee_reward tickets. Referrer tickets are structurally excluded (their expires_at is NULL).

Storage convention: referral_tickets.expires_at is nullable. NULL semantically means “never expires”. All batch and notification queries must guard with expires_at IS NOT NULL before any range comparison.

---

5. Plan Change Behavior (Final)

Upgrade pricing formula (paid-portion-only):

paid_remaining    = max(0, paid_period_end − now)              // months
bonus_remaining   = max(0, billing_use_end_date − max(now, paid_period_end))   // months
amountOff         = (new_price_per_month − old_price_per_month) × paid_remaining
if amountOff ≤ 0: free upgrade — switch tiers, charge nothing

After the upgrade:

• If paid_remaining > 0: new_paid_period_end = old_paid_period_end (unchanged). new_billing_use_end_date = old_billing_use_end_date (unchanged). The period structure (paid + bonus) is preserved.
• If paid_remaining = 0 (free upgrade): new_paid_period_end = max(now, old_paid_period_end). new_billing_use_end_date = new_paid_period_end + bonus_remaining. The bonus months transfer to the new tier.

Tickets are NOT consumed at upgrade time — they remain held for the next billing cycle. The upgrade affects the current period only; held tickets continue to apply at the renewal webhook as before.

---

6. Data Model

6.1 Additions to the users table

ALTER TABLE users ADD COLUMN referral_code VARCHAR(20) UNIQUE NOT NULL;
ALTER TABLE users ADD COLUMN referrer_id BIGINT UNSIGNED NULL;
ALTER TABLE users ADD INDEX idx_users_referrer (referrer_id);
ALTER TABLE users ADD INDEX idx_users_referral_code (referral_code);

• referral_code: Auto-generated on user creation (e.g., base62_encode(random_bytes(8)) ≈ 14 characters).
• referrer_id: Set when signing up via a referral link. Self-referral prevention: if referrer_id == user_id, set to NULL.

6.2 referrals (new table)

Immutable log. One-to-one relationship (one referee has exactly one referrer).

UNIQUE constraint: (referee_user_id) UNIQUE — one referee has only one referrer.

6.3 referral_tickets (new table)

Unified ticket entity. All reward types share this table, distinguished by source.

source values:

• referee_reward — Referee’s +1 month (paid upgrade within 90 days of registration)
• free_signup_bonus — 3-referral bonus
• paid_conversion — Referrer’s +1 month (referee’s first paid charge)
• milestone_bonus — 7 extra months at 5 paid conversions

Indexes: (user_id, status, expires_at), (status, expires_at) (for expiry batch), (user_id, source) (for aggregations).

6.4 referral_stats (new table)

Denormalized counters for fast dashboard queries.

6.5 referral_settings (new table)

Stores the share text used by the copy button, designed to be replaceable in the future.

6.6 Addition to the user_plans table

A new column to track the boundary between the paid portion and any ticket-based bonus portion. This is what makes the upgrade-with-tickets behavior (pro-rate paid only, preserve bonus) possible.

ALTER TABLE user_plans ADD COLUMN paid_period_end TIMESTAMP NOT NULL
  COMMENT 'End of the paid portion only. bonus = billing_use_end_date − paid_period_end';

Backfill (existing user_plans rows pre-referral-program): paid_period_end = billing_use_end_date (no bonus has been applied historically; the two columns are equal).

Update rules (see §5 for full upgrade flow):

Invariant: paid_period_end ≤ billing_use_end_date always. A non-zero diff means a bonus is active.

---

7. Triggers in Detail

7.1 Signup via referral link (?ref=xxxxxx)

Trigger: Referee calls POST /api/pre-register or POST /api/register with ?ref=.

Process:

1. Look up ?ref value in users.referral_code → obtain referrer_id.
2. Verify that the referrer is not the same as the new user (self-referral prevention). If so, set referrer_id = NULL.
3. Set users.referrer_id on the new user.
4. INSERT into referrals table (UNIQUE constraint prevents duplicates).
5. Increment referral_stats.total_referred.
6. Counter A check: If total_referred % 3 == 0 AND free_signup_bonus_count == 0:
   • INSERT into referral_tickets (source=‘free_signup_bonus’, expires_at=NULL). – never expires (referrer ticket)
   • Set referral_stats.free_signup_bonus_count = 1.
   • Send notification: “3 free signups achieved! +1-month ticket earned.”

Notifications: The referee sees the offer details on the signup screen and confirmation email. The referrer is notified only when Counter A triggers a bonus — not on every signup.

7.2 Referee’s first paid charge

Trigger: Stripe webhook invoice.payment_succeeded or customer.subscription.updated that transitions user_payments.status from UNSETTLED to SETTLED for the first time.

Process:

1. Confirm the user has referrer_id IS NOT NULL AND referrals.first_paid_at IS NULL.
2. Update referrals.first_paid_at = now and first_paid_invoice_id = invoice.id.
3. Issue referee ticket: INSERT into referral_tickets (source=‘referee_reward’, expires_at=now+90d). Verify the 90-day eligibility window: referrals.registered_at + 90 days > now. Once issued, the ticket has its own 90-day lifetime from granted_at.
4. Issue referrer ticket (Counter B): INSERT into referral_tickets (source=‘paid_conversion’, expires_at=NULL). – never expires (referrer ticket)
5. Increment referral_stats.total_paid_conversions.
6. 5-person milestone check: If total_paid_conversions == 5 AND milestone_5_issued == FALSE:
   • INSERT 7 additional tickets (source=‘milestone_bonus’, expires_at=NULL). – never expires (referrer ticket)
   • Set referral_stats.milestone_5_issued = TRUE.
   • Send notification: “5 paid conversions achieved! 12 months of free tickets earned.”

Notifications: Referrer receives “+1-month ticket earned” (special message on the 5th). Referee also receives “Referral reward +1 month granted”.

7.3 Ticket expiration (batch)

Trigger: Daily cron (similar to existing DowngradePlanTrialToFree).

Process:

1. Find referral_tickets where status='available' AND expires_at IS NOT NULL AND expires_at < now. The IS NOT NULL guard is required — referrer tickets carry expires_at = NULL and must not match this predicate. In practice, only referee_reward rows can match.
2. Update them to status='expired'.
3. Counter A reset check: removed. Referrer tickets never expire, so the expiration-driven reset path no longer exists. Counter A resets are handled in §7.4 — when a free_signup_bonus ticket is consumed or voided, set free_signup_bonus_count = 0 (so the next 3 free signups start counting again).
4. Pre-expiration notifications (14-day and 3-day) are handled in a separate job:
   • 14-day: source='referee_reward' AND status='available' AND expires_at BETWEEN now+13days AND now+15days AND notify_14d_sent = FALSE.
   • 3-day: source='referee_reward' AND status='available' AND expires_at BETWEEN now+2days AND now+4days AND notify_3d_sent = FALSE.
   • The source='referee_reward' filter scopes these jobs to referee tickets only — referrer tickets have no expiration to warn about.

7.4 Ticket application

Trigger (monthly): Stripe webhook invoice.created (for the next billing cycle).
Trigger (yearly): Stripe webhook invoice.created or customer.subscription.updated (at yearly renewal).

Process:

1. Check the user’s validUserPlan.
2. If validUserPlan is NULL or plan IN (FREE, FREE_TRIAL):
   • Update all the user’s status='available' tickets to status='voided.
   • For any voided ticket where source='free_signup_bonus', set referral_stats.free_signup_bonus_count = 0 (Counter A reset on void).
   • Reason: reaching the Free plan = stock expiration.
3. Otherwise:
   • Fetch the oldest ticket where status='available' AND (expires_at IS NULL OR expires_at > now). If none, do nothing. (Referrer tickets with expires_at = NULL are always eligible.)
   • Monthly: Call Subscription::update to push current_period_end by 1 month AND update user_plans.billing_use_end_date accordingly. (No Stripe Coupon.)
   • Yearly (batch): Aggregate all available tickets → cancel + recreate the subscription with an extended billing_cycle_anchor (by N months where N is the ticket count).
   • Mark the tickets as status='consumed'.
   • For any consumed ticket where source='free_signup_bonus', set referral_stats.free_signup_bonus_count = 0 (Counter A reset on consume).

7.5 Plan-change ticket handling

Trigger: PaymentInteractor::handle (upgrade) / CancelInteractor::handle (cancellation).

Process:

• Upgrade (Lite → Standard):
  • Calculate the prorated difference using the existing calcAmountOff.
  • If amountOff > held-ticket-equivalent value (ticket months × monthly price): charge the difference normally (amountOff - ticket-equivalent value).
  • Otherwise: set amountOff = 0 (free upgrade).
  • Tickets are NOT consumed at this point (carry over to the next cycle).
• Downgrade (Standard → Lite) — DEFERRED to billing_use_end_date:
  • The tier switch is scheduled, not immediate. The user keeps the current plan (with all its features and any active bonus) until the period ends.
  • At billing_use_end_date, the subscription renews at the new (lower) tier. paid_period_end and billing_use_end_date are reset by the renewal webhook based on the new tier’s price.
  • No refund; no immediate feature loss.
  • Held tickets are NOT consumed during the deferred period — they remain status='available'. They will be applied at the renewal, now on the new (lower) tier.
  • Already-consumed bonus is applied at the CURRENT (higher) tier — the user enjoys the higher tier for the entire paid + bonus period, since they already paid for it. The bonus is “used up” before the switch.
  • The user may cancel the scheduled downgrade before billing_use_end_date (revert to staying on the current plan).
• Cancellation (→ Free):
  • Set the old subscription’s cancel_at_period_end = true.
  • When user_plans.billing_use_end_date is reached, userDowngradePlan runs and voids all tickets.

---

8. API Endpoints

8.1 User-facing (yoyacoo_be/user)

8.2 Admin-facing (yoyacoo_be/admin)

8.3 Webhook additions

• PaymentWebhookController::handleInvoiceCreated (new): ticket consumption trigger
• PaymentWebhookController::handleCustomerSubscriptionUpdated (existing, modified): detect first paid charge

---

9. UI Surfaces

9.1 User-facing (yoyacoo_fe/supplier)

9.2 Admin-facing (yoyacoo_fe/admin)

• pages/referrals/index.tsx — list view
• pages/referrals/[userId].tsx — detail view
• pages/referrals/settings.tsx — share text settings

9.3 Registration screen (yoyacoo_fe/customer + yoyacoo_fe/supplier)

• Read ?ref=xxxxxx from URL on mount
• Display a read-only invitation code field (per Furuha’s judgment)
• Include ref in the POST /api/pre-register and POST /api/register payloads

9.4 Copy button

• One click copies pre-canned text + link to the clipboard
• Text is fetched from referral_settings.share_text
• The {referral_link} placeholder is replaced with the actual link

---

10. Notifications

10.1 Email

Pre-expiration notifications are scoped to referee_reward tickets only. Referrer tickets (free_signup_bonus, paid_conversion, milestone_bonus) have expires_at = NULL and never trigger pre-expiration emails.

10.2 In-app banner

• Show ExpiringTicketBanner on the dashboard / Referral page
• Use referral_tickets.notify_14d_sent and notify_3d_sent flags to prevent duplicate sends

---

11. Edge Cases and Idempotency

11.1 Idempotency

• Webhook retries: all main operations use UNIQUE constraints + select-then-act pattern
• referrals(referee_user_id) UNIQUE prevents duplicate signup processing
• referral_tickets duplicate prevention uses job ID or UNIQUE(source, referee_user_id, type) constraint

11.2 Self-referral prevention

• users.referrer_id != users.id is checked at signup
• If detected, referrer_id = NULL and no referral is recorded

11.3 Duplicate-referee prevention

• One referee has only one referrer (referrals.referee_user_id UNIQUE)
• A second ?ref= from a different referrer is silently ignored

11.4 Sub-accounts

• Referral link issuance is restricted to the master account (child accounts cannot generate links)
• Parent and child accounts are treated as separate users (they can refer each other)

11.5 Cancellation → re-subscription

• After cancellation (downgrade to Free), all held tickets are voided. Re-subscribing is treated as a fresh start.
• Past referral history (Counters A/B) is preserved (the cumulative count for the 5-person milestone does not reset).

11.6 Yearly ticket batch-application failure

• If any ticket in the batch fails, roll back the entire batch — leave the affected tickets as status='available'
• Schedule a retry job (e.g., 1 hour later)

---

12. Alignment with the Existing System

12.1 What we reuse

• StripePaymentSubscription trait’s createSubscription, cancelSubscription
• Subscription util’s newOrUpdateUserSubscription
• userDowngradePlan trait (extended with ticket-voiding logic)
• Cron pattern from DowngradePlanTrialToFree for the daily batch
• Existing SendMail infrastructure (only template additions)
• Existing reservation-level Coupon patterns as a reference for table/service design

12.2 Existing components that need modification

• PaymentInteractor::handle: add ticket-equivalent cap logic to the upgrade pricing
• CancelInteractor::handle: add ticket-voiding hook on cancellation
• PaymentWebhookController::handleCustomerSubscriptionUpdated: add first-paid-charge detection
• userDowngradePlan: add ticket-voiding hook
• User model: add referral_code, referrer_id, and related relations
• UserPlan model: add relations as needed

12.3 Where new code lives (3-app split)

• Common models (yoyacoo_be/common): Referral, ReferralTicket, ReferralStat, ReferralSetting
• User app (yoyacoo_be/user): link generation, signup hook, user-facing API
• Admin app (yoyacoo_be/admin): webhook handlers, admin API
• Common webhook (yoyacoo_be/common): expiry batches, notification batches

---

13. Acceptance Criteria

• A new user can sign up with ?ref=xxxxxx
• The referrer’s counters (A and B) update correctly
• 3 free signups trigger a +1-month ticket
• A referee’s first paid charge triggers a +1-month ticket for the referrer
• 5 paid conversions trigger the +7-month bonus
• Monthly users see their next billing date extended correctly
• Yearly users see all held tickets applied at the renewal boundary
• Email + banner notifications are sent 14 days and 3 days before expiration
• Tickets transition to expired after 90 days
• Tickets transition to voided when the user reaches the Free plan
• Upgrade pricing is capped by the held-ticket-equivalent value
• The copy button puts the share text + link on the clipboard
• Admins can view all referral data via the admin screen
• Webhook retries do not cause duplicate processing
• Self-referral is prevented
• Referrer tickets (free_signup_bonus, paid_conversion, milestone_bonus) have expires_at = NULL and never transition to expired
• Referee tickets (referee_reward) expire exactly 90 days after granted_at
• Pre-expiration notifications (14d / 3d) fire only for referee_reward tickets
• Counter A free_signup_bonus_count resets to 0 only on consumed or voided (not on expired)
• user_plans.paid_period_end column exists; invariant paid_period_end ≤ billing_use_end_date holds
• Backfill populates paid_period_end = billing_use_end_date for existing rows
• Monthly ticket consumption extends only billing_use_end_date; paid_period_end unchanged
• Yearly batch consumption extends paid_period_end by exactly 12 months and adds consumed ticket months to billing_use_end_date on top
• Upgrade pro-rates over max(0, paid_period_end − now); returns amountOff = 0 when this is 0
• When amountOff = 0 upgrade (bonus-only period), the new plan’s paid_period_end = max(now, old_paid_period_end) and billing_use_end_date preserves bonus_remaining months
• When amountOff > 0 upgrade (mid-paid-period), the new plan’s paid_period_end and billing_use_end_date are unchanged from the pre-upgrade values
• Held (unconsumed) tickets are not affected by an upgrade; they continue to apply at the next billing cycle
• Downgrade is scheduled (not immediate) — the user keeps the current plan until billing_use_end_date, then switches to the new tier on the renewal
• During the deferred-downgrade period, paid_period_end and billing_use_end_date are unchanged
• At billing_use_end_date, the scheduled downgrade fires: the subscription renews at the new (lower) tier, with paid_period_end and billing_use_end_date reset based on the new tier’s price
• Already-consumed bonus time during the deferred-downgrade period is enjoyed at the CURRENT (higher) tier, not the new one
• Held tickets remain status='available' during the deferred-downgrade period and are consumed on the new tier at the renewal
• A user can cancel a scheduled downgrade before billing_use_end_date (revert to staying on the current plan)