SOP-REF-01: Referral System (v2.1)¶

Status: ✅ Production Ready
Last Updated: 2026-01-16
Owner: Protocol Raw Operations
Related SOPs: SOP 00 (Shopify Bridge), SOP 01 (Order Fulfillment)

What's New in v2.0+¶

Credit Ledger Integration (v2.0): - ✅ Replaced Shopify discount codes with internal credit ledger - ✅ Referral rewards now issued as £15 credit (not REWARD15-XXXXXX codes) - ✅ Credits auto-apply at subscription renewal as Shopify refunds - ✅ Removed Shopify GraphQL discount creation from Scenario B - ✅ New Customer.io events: credit_earned, credit_applied, credit_expiring - ✅ Customer portal shows credit balance - ✅ 90-day credit expiry with 7-day warning emails

Documentation Completeness (v2.1): - ✅ Restored index documentation for all tables - ✅ Restored manual_override_referral() function documentation - ✅ Restored Make.com module details tables - ✅ Added credit liability view documentation - ✅ Restored troubleshooting common causes - ✅ Added all supporting function documentation

Why the credit ledger change: - Simpler for customers (no code to remember/enter) - Works seamlessly with subscriptions (auto-applies) - Better tracking and reporting - No Shopify discount code management overhead

Overview¶

The Protocol Raw Referral System enables existing customers to refer friends and receive rewards for successful conversions. The system is designed for 100,000+ customers with comprehensive fraud prevention and full audit trails.

Core Philosophy: Reward genuine advocacy, not arbitrage. The system rewards referrers only after the referee's first delivery is confirmed.

Reward Structure¶

Recipient	Reward	Mechanism	Trigger
Referee (new customer)	£25 off first box	Calculator discount code (`CALC25-XXXXXX`)	Uses referral link
Referrer (existing customer)	£15 credit	Credit ledger	Referee's first delivery confirmed

Credit Application: - Credits are stored in raw_ops.customer_credits - When referrer's subscription renews, credit is auto-applied as a Shopify refund - Customer sees: Charged £89, Refund £15, Net £74

Economics: - Standard organic discount: £20 (via calculator) - Referral bonus to referee: £5 extra (£25 vs £20) - Referral reward to referrer: £15 - Total incremental cost per referral: £20 - Comparison to paid CAC: £70-90

Key Design Decisions: 1. Time-bound invisibility: £25 referral discount never advertised publicly; only appears on referral landing pages 2. Automatic application: No code needed - credit applies at renewal 3. Subscription-first: Works seamlessly with recurring orders 4. Delivery-confirmed rewards: Referrer credit only issued after referee receives first delivery (prevents churn arbitrage)

System Architecture¶

┌─────────────────────────────────────────────────────────────────────────────â”
│                           REFERRAL FLOW (v2.1)                              │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                             │
│  1. REFERRAL CODE CREATION                                                  │
│     Customer Created → Trigger → Generate 6-char slug → Store               │
│                                                                             │
│  2. REFERRAL ATTRIBUTION (Make.com Scenario A - every 5 min)                │
│     Order with referral note → Parse slug → Fraud checks →                  │
│     Create referral record (pending) → Notify referrer (Customer.io)        │
│                                                                             │
│  3. CREDIT ISSUANCE (Make.com Scenario B - every 15 min)                    │
│     First delivery confirmed → Confirm referral → Issue £15 credit →        │
│     Send credit_earned email (Customer.io)                                  │
│                                                                             │
│  4. CREDIT APPLICATION (Make.com Scenario C - every 15 min)                 │
│     Subscription renews → Check credit balance → Apply as refund →          │
│     Send credit_applied email (Customer.io)                                 │
│                                                                             │
│  5. CREDIT EXPIRY (Daily 2am cron + Make.com Scenario D at 9am)             │
│     Credits older than 90 days → Mark as expired                            │
│     7 days before expiry → Send credit_expiring email                       │
│                                                                             │
└─────────────────────────────────────────────────────────────────────────────┘

Database Schema¶

All tables reside in the raw_ops schema.

Core Tables¶

referral_codes¶

One referral code per customer, auto-created on customer creation.

Column	Type	Description
`id`	UUID	Primary key
`customer_id`	UUID	FK to customers (unique)
`slug`	TEXT	6-char URL-safe code (e.g., `hgtuy5`)
`display_name`	TEXT	Shown on landing page ("Invited by James")
`is_active`	BOOLEAN	Can be paused by ops
`total_referrals`	INTEGER	Denormalized count
`successful_referrals`	INTEGER	Confirmed count
`total_rewards_earned_pence`	INTEGER	Total £ earned in pence
`created_at`	TIMESTAMPTZ	Creation timestamp

Indexes: - idx_referral_codes_slug (UNIQUE) - idx_referral_codes_customer_id (UNIQUE) - idx_referral_codes_active (partial, WHERE is_active = TRUE)

referrals¶

Tracks the referrer→referee relationship and state machine.

Column	Type	Description
`id`	UUID	Primary key
`referral_code_id`	UUID	FK to referral_codes
`referrer_customer_id`	UUID	FK to customers (referrer)
`referee_customer_id`	UUID	FK to customers (new customer)
`referee_email`	TEXT	Email at time of referral (unique)
`referee_order_id`	UUID	FK to orders (first order)
`referee_first_delivery_at`	TIMESTAMPTZ	When first delivery confirmed
`status`	TEXT	`pending` → `confirmed` or `fraud_flagged`
`fraud_flags`	JSONB	Array of detected issues
`fraud_checked_at`	TIMESTAMPTZ	When fraud checks ran
`referrer_credit_id`	UUID	FK to customer_credits
`referral_processed_at`	TIMESTAMPTZ	When order was processed for referral
`created_at`	TIMESTAMPTZ	When referral attributed
`confirmed_at`	TIMESTAMPTZ	When delivery confirmed

State Machine:

PENDING (order placed, fraud checks passed)
    ↓ First delivery confirmed
CONFIRMED (credit issued to referrer)

OR

FRAUD_FLAGGED (blocked, awaiting review)
    ↓ manual_override_referral() with reason
PENDING (cleared for processing)
    ↓ or directly to
CONFIRMED (approved despite flags)

Indexes: - idx_referrals_referral_code_id - idx_referrals_referrer_customer_id - idx_referrals_referee_customer_id - idx_referrals_referee_email (UNIQUE) - idx_referrals_status - idx_referrals_pending (partial, WHERE status = 'pending')

customer_credits (Credit Ledger)¶

Stores all customer credits including referral rewards.

Column	Type	Description
`id`	UUID	Primary key
`customer_id`	UUID	FK to customers
`amount_pence`	INTEGER	Original credit amount
`remaining_pence`	INTEGER	Unused balance
`source_type`	TEXT	`referral`, `goodwill`, `promotion`, `manual`
`source_id`	UUID	FK to source record (e.g., referral_id)
`source_description`	TEXT	Human-readable description
`status`	TEXT	`available`, `fully_applied`, `expired`, `cancelled`
`expires_at`	TIMESTAMPTZ	90 days from issuance
`expiry_warning_sent_at`	TIMESTAMPTZ	When 7-day warning sent
`created_at`	TIMESTAMPTZ	When issued
`updated_at`	TIMESTAMPTZ	Last modification

Indexes: - idx_customer_credits_customer_id - idx_customer_credits_status - idx_customer_credits_available (partial, WHERE status = 'available' AND remaining_pence > 0) - idx_customer_credits_expiring (partial, WHERE status = 'available', ordered by expires_at)

customer_credit_applications¶

Tracks each time credit is applied to an order.

Column	Type	Description
`id`	UUID	Primary key
`credit_id`	UUID	FK to customer_credits
`customer_id`	UUID	FK to customers
`order_id`	UUID	FK to orders
`amount_pence`	INTEGER	Amount applied
`shopify_refund_id`	TEXT	Shopify refund reference
`created_at`	TIMESTAMPTZ	When applied

Indexes: - idx_credit_applications_credit_id - idx_credit_applications_customer_id - idx_credit_applications_order_id

customer_credit_events (Audit Log)¶

Immutable append-only log of all credit system decisions.

Column	Type	Description
`id`	UUID	Primary key
`event_type`	TEXT	See event types below
`credit_id`	UUID	FK to customer_credits (nullable)
`customer_id`	UUID	FK to customers (nullable)
`order_id`	UUID	FK to orders (nullable)
`payload`	JSONB	Full event details
`created_at`	TIMESTAMPTZ	Event timestamp

Event Types: - credit_issued - Credit created (referral, goodwill, etc.) - credit_applied - Credit used on order - credit_expired - Credit expired unused - credit_cancelled - Credit manually cancelled - expiry_warning_sent - 7-day warning email triggered

referral_events (Audit Log)¶

Immutable append-only log of all referral system decisions.

Column	Type	Description
`id`	UUID	Primary key
`event_type`	TEXT	See event types below
`referral_id`	UUID	FK to referrals (nullable)
`referral_code_id`	UUID	FK to referral_codes (nullable)
`order_id`	UUID	FK to orders (nullable)
`customer_id`	UUID	FK to customers (nullable)
`payload`	JSONB	Full event details
`created_at`	TIMESTAMPTZ	Event timestamp

Event Types: - attribution_attempted - Referral slug found in order - attribution_success - Referral record created - attribution_failed - Attribution blocked (reason in payload) - attribution_fraud_flagged - Fraud detected - credit_issued - £15 credit created for referrer - code_created - Referral code generated - code_paused - Referral code deactivated - manual_override - Ops manual intervention (with reason in payload)

Fraud Prevention¶

The attribute_referral() function runs these checks before creating a referral:

1. Self-Referral Check¶

Detects: Referee email matches referrer email (case-insensitive) Action: Block attribution, log attribution_failed

2. Same Household Check¶

Detects: Referee's normalized address + postcode matches referrer's Normalization: Removes spaces, special characters, converts to uppercase Action: Flag as fraud_flagged, do not issue credit (flagged for review, not blocked)

3. Velocity Check¶

Detects: Referrer has >5 referrals in the past 7 days Action: Flag as fraud_flagged, do not issue credit

4. Duplicate Attribution Check¶

Detects: Referee email already exists in referrals table Action: Block attribution (idempotent - returns existing referral_id)

Database Functions¶

Core Functions¶

Function	Purpose	Called By
`generate_referral_slug()`	Creates 6-char unique slug	Trigger
`create_referral_code_for_customer()`	Auto-creates code on customer INSERT	Trigger
`extract_order_referral_data(order_id)`	Parses notes_json for referral slug	Make.com
`process_order_referral_enrichment(order_id)`	Main entry point - attributes referral	Make.com Scenario A
`attribute_referral(email, customer_id, order_id, slug)`	Creates referral record with fraud checks	Internal
`confirm_referral_and_issue_reward(referral_id)`	Confirms referral, issues £15 credit	Make.com Scenario B
`apply_credit_to_order(order_id)`	Applies available credit to order	Make.com Scenario C
`mark_credit_refund_complete(application_id, refund_id)`	Records Shopify refund ID	Make.com Scenario C
`get_customer_credits(customer_id)`	Returns credit balance for portal	Portal API
`get_customer_referral_info(customer_id)`	Returns referral stats for portal	Portal API
`get_referral_by_slug(slug)`	Returns display_name for landing page	Landing Page
`expire_old_credits()`	Marks expired credits	Cron (daily 2am)
`mark_expiry_warning_sent(customer_id, credit_ids)`	Records warning email sent	Make.com Scenario D
`manual_override_referral(referral_id, new_status, reason)`	Ops override for fraud review	Ops Portal

Function: confirm_referral_and_issue_reward¶

Purpose: Called by Make.com Scenario B when a referee's first delivery is confirmed.

Input: p_referral_id UUID

Process: 1. Verify referral exists and status = 'pending' 2. Create customer_credits record (£15, 90-day expiry, source_type='referral') 3. Update referral status to 'confirmed', set referrer_credit_id 4. Update referral_codes stats (successful_referrals, total_rewards_earned_pence) 5. Log credit_issued event 6. Return credit details for Customer.io

Returns:

{
  "success": true,
  "referral_id": "uuid",
  "referrer_customer_id": "uuid",
  "credit_id": "uuid",
  "amount_pence": 1500,
  "total_balance_pence": 1500,
  "total_balance_formatted": "£15",
  "expires_at": "2026-04-15T19:58:36Z"
}

Function: manual_override_referral¶

Purpose: Allows ops to override fraud flags or correct referral status after review.

Input: - p_referral_id UUID - The referral to modify - p_new_status TEXT - Target status: pending, confirmed, fraud_flagged, or cancelled - p_reason TEXT - Audit trail reason for the override

Process: 1. Validate referral exists 2. Validate new status is allowed 3. Update referral status (clears fraud_flags if moving to pending) 4. If moving to confirmed, issue credit 5. Log manual_override event with old/new status and reason

Returns:

{
  "success": true,
  "referral_id": "uuid",
  "old_status": "fraud_flagged",
  "new_status": "pending"
}

Use Cases: - False positive fraud flag (legitimate same-address referral, e.g., apartment building) - Velocity flag override for legitimate power referrer - Correcting processing errors

Function: apply_credit_to_order¶

Purpose: Called by Make.com Scenario C when a subscription renewal is detected.

Input: p_order_id UUID

Process: 1. Get customer_id from order 2. Find available credits (FEFO - First Expiring, First Out) 3. Calculate amount to apply (min of credit balance and order total) 4. Create credit_application record 5. Update credit remaining_pence (mark fully_applied if exhausted) 6. Return application details for Shopify refund

Returns:

{
  "applied": true,
  "application_id": "uuid",
  "amount_pence": 1500,
  "amount_formatted": "£15",
  "order_total_pence": 8900,
  "final_total_pence": 7400,
  "final_total_formatted": "£74"
}

Views¶

v_orders_pending_referral_processing¶

Used by Scenario A to find orders needing referral enrichment. Uses durable referral_processed_at flag instead of time window to ensure no orders are missed.

SELECT id AS order_id, customer_id, notes_json, discount_codes
FROM raw_ops.orders
WHERE referral_processed_at IS NULL
  AND notes_json::text LIKE '%referral%'
ORDER BY created_at ASC
LIMIT 50;

Note: process_order_referral_enrichment() sets referral_processed_at = NOW() after processing, ensuring each order is only processed once regardless of Make.com downtime.

v_referrals_pending_delivery_confirmation¶

Used by Scenario B to find referrals ready for credit issuance.

SELECT 
    r.id AS referral_id,
    r.referee_customer_id,
    r.referrer_customer_id,
    r.referee_order_id,
    s.delivered_at,
    c.email AS referrer_email,
    c.first_name AS referrer_first_name
FROM raw_ops.referrals r
JOIN raw_ops.shipments s ON s.order_id = r.referee_order_id
JOIN raw_ops.customers c ON c.id = r.referrer_customer_id
WHERE r.status = 'pending'
  AND s.status = 'DELIVERED'
  AND r.referrer_credit_id IS NULL
LIMIT 50;

v_orders_pending_credit_application¶

Used by Scenario C to find subscription renewals eligible for credit application.

SELECT 
    o.id AS order_id,
    o.customer_id,
    o.total_pence,
    o.shopify_order_id,
    cc.total_available_pence
FROM raw_ops.orders o
JOIN (
    SELECT customer_id, SUM(remaining_pence) AS total_available_pence
    FROM raw_ops.customer_credits
    WHERE status = 'available' AND remaining_pence > 0
    GROUP BY customer_id
) cc ON cc.customer_id = o.customer_id
WHERE o.credit_applied_at IS NULL
  AND o.is_subscription_renewal = TRUE
  AND o.financial_status = 'paid'
  AND cc.total_available_pence > 0
ORDER BY o.created_at ASC
LIMIT 50;

v_credits_expiring_soon¶

Used by Scenario D to find customers with credits expiring within 7 days.

SELECT 
    c.id AS customer_id,
    c.email,
    c.first_name,
    SUM(cc.remaining_pence) AS total_expiring_pence,
    CONCAT('£', (SUM(cc.remaining_pence) / 100)::INTEGER) AS amount_formatted,
    MIN(cc.expires_at) AS earliest_expiry,
    TO_CHAR(MIN(cc.expires_at), 'DD Month YYYY') AS expires_at_formatted,
    ARRAY_AGG(cc.id) AS credit_ids
FROM raw_ops.customer_credits cc
JOIN raw_ops.customers c ON c.id = cc.customer_id
WHERE 
    cc.status = 'available'
    AND cc.remaining_pence > 0
    AND cc.expires_at > NOW()
    AND cc.expires_at <= NOW() + INTERVAL '7 days'
    AND cc.expiry_warning_sent_at IS NULL
GROUP BY c.id, c.email, c.first_name;

v_credit_liability¶

Tracks outstanding credit liability for financial reporting.

SELECT 
    DATE_TRUNC('month', created_at) AS month,
    SUM(CASE WHEN status = 'available' THEN remaining_pence ELSE 0 END) AS outstanding_pence,
    SUM(CASE WHEN status = 'fully_applied' THEN amount_pence ELSE 0 END) AS redeemed_pence,
    SUM(CASE WHEN status = 'expired' THEN amount_pence ELSE 0 END) AS expired_pence,
    COUNT(*) FILTER (WHERE status = 'available') AS outstanding_count,
    COUNT(*) FILTER (WHERE status = 'fully_applied') AS redeemed_count,
    COUNT(*) FILTER (WHERE status = 'expired') AS expired_count
FROM raw_ops.customer_credits
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month DESC;

v_referral_metrics¶

Daily aggregation for Metabase dashboards.

SELECT 
    DATE_TRUNC('day', created_at) AS date,
    COUNT(*) AS total_referrals,
    COUNT(*) FILTER (WHERE status = 'pending') AS pending,
    COUNT(*) FILTER (WHERE status = 'confirmed') AS confirmed,
    COUNT(*) FILTER (WHERE status = 'fraud_flagged') AS fraud_flagged
FROM raw_ops.referrals
GROUP BY DATE_TRUNC('day', created_at)
ORDER BY date DESC;

v_referral_leaderboard¶

Top referrers by successful_referrals.

SELECT 
    rc.id AS referral_code_id,
    rc.slug,
    rc.display_name,
    c.email,
    c.first_name,
    rc.successful_referrals,
    rc.total_rewards_earned_pence,
    CONCAT('£', (rc.total_rewards_earned_pence / 100)::INTEGER) AS total_earned_formatted
FROM raw_ops.referral_codes rc
JOIN raw_ops.customers c ON c.id = rc.customer_id
WHERE rc.successful_referrals > 0
ORDER BY rc.successful_referrals DESC
LIMIT 50;

Make.com Scenarios¶

Scenario A: SOP-REF-01 - Referral Order Enrichment¶

Schedule: Every 5 minutes

Flow:

[1] HTTP GET - v_orders_pending_referral_processing
        ↓
[2] Iterator - Loop through orders
        ↓
[3] HTTP POST - process_order_referral_enrichment(order_id)
        ↓
[Filter: attribution.success = true AND attribution.status = pending]
        ↓
[4] HTTP POST - Customer.io event (referral_signup)

Module Details:

Module	Type	URL/Action
1	HTTP GET	`https://[supabase]/rest/v1/v_orders_pending_referral_processing`
2	Iterator	`{{1.data}}`
3	HTTP POST	`https://[supabase]/rest/v1/rpc/process_order_referral_enrichment`
4	HTTP POST	`https://track.customer.io/api/v1/customers/{{3.data.attribution.referrer_customer_id}}/events`

Customer.io Event Body (Module 4):

{
  "name": "referral_signup",
  "data": {
    "referee_first_name": "A friend",
    "referral_id": "{{3.data.attribution.referral_id}}"
  }
}

Scenario B: SOP-REF-02 - Delivery Confirmation Credit Issuance¶

Schedule: Every 15 minutes

Flow:

[1] HTTP GET - v_referrals_pending_delivery_confirmation
        ↓
[2] Iterator - Loop through pending referrals
        ↓
[3] HTTP POST - confirm_referral_and_issue_reward(referral_id)
        ↓
[Filter: success = true]
        ↓
[4] HTTP POST - Customer.io event (credit_earned)

Module Details:

Module	Type	URL/Action
1	HTTP GET	`https://[supabase]/rest/v1/v_referrals_pending_delivery_confirmation`
2	Iterator	`{{1.data}}`
3	HTTP POST	`https://[supabase]/rest/v1/rpc/confirm_referral_and_issue_reward`
4	HTTP POST	`https://track.customer.io/api/v1/customers/{{3.data.referrer_customer_id}}/events`

Note: No Shopify discount code creation needed. Credit is stored in ledger.

Customer.io Event Body (Module 4):

{
  "name": "credit_earned",
  "data": {
    "amount_formatted": "£15",
    "total_balance_formatted": "{{3.data.total_balance_formatted}}",
    "expires_at": "{{3.data.expires_at}}"
  }
}

Scenario C: SOP-REF-03 - Credit Application (Order Renewal)¶

Schedule: Every 15 minutes

Flow:

[1] HTTP GET - v_orders_pending_credit_application
        ↓
[2] Iterator - Loop through orders
        ↓
[3] HTTP POST - apply_credit_to_order(order_id)
        ↓
[Filter: applied = true]
        ↓
[4] HTTP POST - Shopify refund API
        ↓
[5] HTTP POST - mark_credit_refund_complete(application_id, refund_id)
        ↓
[6] HTTP POST - Customer.io event (credit_applied)

Module Details:

Module	Type	URL/Action
1	HTTP GET	`https://[supabase]/rest/v1/v_orders_pending_credit_application`
2	Iterator	`{{1.data}}`
3	HTTP POST	`https://[supabase]/rest/v1/rpc/apply_credit_to_order`
4	HTTP POST	`https://[store].myshopify.com/admin/api/2024-10/orders/{{2.shopify_order_id}}/refunds.json`
5	HTTP POST	`https://[supabase]/rest/v1/rpc/mark_credit_refund_complete`
6	HTTP POST	`https://track.customer.io/api/v1/customers/{{2.customer_id}}/events`

Shopify Refund Body (Module 4):

{
  "refund": {
    "note": "Credit applied: {{3.data.amount_formatted}}",
    "notify": false,
    "transactions": [
      {
        "parent_id": "{{original_transaction_id}}",
        "amount": "{{3.data.amount_pence / 100}}",
        "kind": "refund",
        "gateway": "manual"
      }
    ]
  }
}

Customer.io Event Body (Module 6):

{
  "name": "credit_applied",
  "data": {
    "order_total_formatted": "£89",
    "amount_formatted": "{{3.data.amount_formatted}}",
    "final_total_formatted": "{{3.data.final_total_formatted}}"
  }
}

Scenario D: SOP-REF-04 - Credit Expiry Warning¶

Schedule: Daily at 9am

Flow:

[1] HTTP GET - v_credits_expiring_soon
        ↓
[2] Iterator - Loop through customers with expiring credits
        ↓
[3] HTTP POST - Customer.io event (credit_expiring)
        ↓
[4] HTTP POST - mark_expiry_warning_sent(customer_id, credit_ids)

Module Details:

Module	Type	URL/Action
1	HTTP GET	`https://[supabase]/rest/v1/v_credits_expiring_soon`
2	Iterator	`{{1.data}}`
3	HTTP POST	`https://track.customer.io/api/v1/customers/{{2.customer_id}}/events`
4	HTTP POST	`https://[supabase]/rest/v1/rpc/mark_expiry_warning_sent`

Customer.io Event Body (Module 3):

{
  "name": "credit_expiring",
  "data": {
    "amount_formatted": "{{2.amount_formatted}}",
    "expires_at_formatted": "{{2.expires_at_formatted}}"
  }
}

Customer.io Events¶

Event Name	Recipient	Trigger	Purpose
`referral_signup`	Referrer	Referee places order	"Your friend just signed up!"
`credit_earned`	Referrer	Referee's first delivery confirmed	"You've earned £15 credit"
`credit_applied`	Customer	Credit used at renewal	"Your credit has been applied"
`credit_expiring`	Customer	7 days before expiry	"Your credit expires soon"

Event: referral_signup¶

Attributes: - referee_first_name: "A friend" (privacy) - referral_id: UUID for tracking

Template Notes: - Don't confirm reward yet (delivery not confirmed) - Set expectation: "We'll add credit to your account once their first box is delivered"

Event: credit_earned¶

Attributes: - amount_formatted: "£15" - total_balance_formatted: "£15" (may be higher if multiple credits) - expires_at: ISO timestamp

Template Notes: - Emphasize auto-application: "This will automatically apply to your next renewal" - Show total balance if >£15 - Link to customer portal for full credit/referral stats

Event: credit_applied¶

Attributes: - order_total_formatted: "£89" - amount_formatted: "£15" - final_total_formatted: "£74"

Template Notes: - Show breakdown clearly - Reference the refund they'll see on their statement

Event: credit_expiring¶

Attributes: - amount_formatted: "£15" - expires_at_formatted: "15 April 2026"

Template Notes: - Urgency without panic - Note auto-application: "Don't worry - if your subscription renews before then, we'll apply it automatically"

Email Templates¶

See Protocol_Raw_Email_Design_System_v1_0.md for template specifications.

Order Data Flow¶

How referral data enters the system¶

Shopify Order Note Attribute:

{
  "name": "referral",
  "value": "hgtuy5"
}

This is added by the frontend when a customer arrives via a referral link. The calculator/checkout stores the slug in localStorage and includes it as an order note.

Stored in orders table: - notes_json: Contains the full note attributes array - referral_slug: Populated after processing - referral_id: FK to referrals table after attribution - referral_processed_at: When referral enrichment completed

Customer Portal Integration¶

Credit Balance Display¶

The customer portal shows: - Current available balance (Forest Green, prominent) - "Expiring soon" warning banner (if credits expire within 7 days) - "Applied automatically to your next renewal" info text

API Function: get_customer_credits(customer_id)

Returns:

{
  "balance_pence": 1500,
  "balance_formatted": "£15",
  "has_credits": true,
  "credits": [
    {
      "id": "uuid",
      "amount_pence": 1500,
      "remaining_pence": 1500,
      "source_type": "referral",
      "source_description": "Referral reward",
      "expires_at": "2026-04-15T00:00:00Z",
      "expires_at_formatted": "15 April 2026"
    }
  ],
  "expiring_soon": [],
  "has_expiring_soon": false
}

Referral Stats Display¶

API Function: get_customer_referral_info(customer_id)

Returns:

{
  "referral_code": {
    "slug": "hgtuy5",
    "display_name": "James",
    "referral_link": "https://protocolraw.com/r/hgtuy5"
  },
  "stats": {
    "total_referrals": 5,
    "successful_referrals": 3,
    "pending_referrals": 2,
    "total_earned_pence": 4500,
    "total_earned_formatted": "£45"
  },
  "recent_referrals": [
    {
      "status": "confirmed",
      "created_at": "2026-01-10T14:30:00Z",
      "confirmed_at": "2026-01-15T09:00:00Z"
    }
  ]
}

Cron Jobs¶

expire-old-credits¶

Schedule: Daily at 2:00 AM UTC

Expression: 0 2 * * *

Function: raw_ops.expire_old_credits()

Action: Marks credits as expired where expires_at <= NOW() and status = 'available'

Permissions¶

Role	Tables	Functions
`service_role`	ALL on all referral/credit tables	EXECUTE on all functions
`anon`	None	EXECUTE on `get_referral_by_slug` only
`authenticated`	SELECT on own referral_codes, customer_credits	EXECUTE on `get_customer_referral_info`, `get_customer_credits`

Monitoring¶

Key Metrics to Track¶

Metric	Target	Alert Threshold
Referral conversion rate	>50%	<30%
Fraud flag rate	<5%	>10%
Credit redemption rate	>80%	<50%
Average time to credit use	<30 days	>60 days
Pending referrals backlog	<10	>50
Credit liability	Track	>£10,000

Metabase Dashboards¶

Referral Performance: - Daily referrals (total, pending, confirmed, fraud) - Conversion funnel visualization - Top referrers leaderboard - Credit liability tracking (v_credit_liability)

Credit Health: - Outstanding credit balance over time - Credit redemption rate - Expiry rate (% of credits expiring unused) - Average time from issuance to use

Troubleshooting¶

Referral not attributed¶

Symptoms: Customer used referral link but no referral record created

Investigation:

-- Check if order has referral note
SELECT id, notes_json, referral_processed_at FROM raw_ops.orders 
WHERE customer_id = '[customer_id]' ORDER BY created_at DESC LIMIT 1;

-- Check referral events for failures
SELECT * FROM raw_ops.referral_events 
WHERE order_id = '[order_id]' ORDER BY created_at DESC;

Common causes: 1. Referral note not included in order (frontend issue - check localStorage) 2. Email already referred (check referral_events for attribution_failed) 3. Make.com Scenario A not running 4. Order already processed (referral_processed_at is set)

Credit not issued¶

Symptoms: Referral status stuck at pending

Investigation:

-- Check referral status
SELECT * FROM raw_ops.referrals 
WHERE referee_customer_id = '[customer_id]';

-- Check if delivery was confirmed
SELECT * FROM raw_ops.shipments 
WHERE order_id = '[order_id]' AND status = 'DELIVERED';

-- Check if referral appears in pending view
SELECT * FROM raw_ops.v_referrals_pending_delivery_confirmation
WHERE referral_id = '[referral_id]';

Common causes: 1. Delivery not yet confirmed in system (courier webhook delay) 2. Make.com Scenario B not running 3. Referral was fraud_flagged (check fraud_flags column)

Credit not appearing in portal¶

Symptoms: Customer has credit but portal shows £0

Investigation:

-- Check credit exists and is available
SELECT * FROM raw_ops.customer_credits 
WHERE customer_id = '[customer_id]' 
AND status = 'available' 
AND remaining_pence > 0;

-- Test the portal function
SELECT raw_ops.get_customer_credits('[customer_id]');

Common causes: 1. Credit status is not 'available' (check for 'expired' or 'fully_applied') 2. RLS policy blocking access (check authenticated role) 3. Customer ID mismatch

Credit not applied to renewal¶

Symptoms: Customer has credit but wasn't charged less on renewal

Investigation:

-- Check order is flagged as subscription renewal
SELECT id, is_subscription_renewal, credit_applied_at, financial_status
FROM raw_ops.orders 
WHERE customer_id = '[customer_id]' 
ORDER BY created_at DESC LIMIT 1;

-- Check credit application records
SELECT * FROM raw_ops.customer_credit_applications
WHERE customer_id = '[customer_id]';

-- Check if order appears in pending view
SELECT * FROM raw_ops.v_orders_pending_credit_application
WHERE customer_id = '[customer_id]';

Common causes: 1. Order not marked as subscription renewal 2. Make.com Scenario C not running 3. Credit already fully applied to previous orders 4. Shopify refund API failure (check Make.com logs)

Duplicate referral code¶

Symptoms: Slug generation fails repeatedly

Investigation:

-- Check for slug collisions
SELECT slug, COUNT(*) FROM raw_ops.referral_codes 
GROUP BY slug HAVING COUNT(*) > 1;

Resolution: Function has 100-attempt retry; if still failing, check character set or increase slug length.

Migration Notes (v1.x → v2.0)¶

Removed Components¶

referral_rewards table (replaced by customer_credits)
Shopify GraphQL discount code creation (Module 4 in old Scenario B)
REWARD15-XXXXXX code format
referral_reward_ready Customer.io event (replaced by credit_earned)
expire_old_referral_rewards() function (replaced by expire_old_credits())
mark_referral_reward_used() function (no longer needed)
rewarded status (simplified state machine)

Added Components¶

customer_credits table
customer_credit_applications table
customer_credit_events table
get_customer_credits() function
apply_credit_to_order() function
mark_credit_refund_complete() function
expire_old_credits() function
Credit application Make.com scenario (Scenario C)
Credit expiry warning Make.com scenario (Scenario D)
Portal credits display
credit_earned, credit_applied, credit_expiring Customer.io events

Data Migration¶

Existing unused referral_rewards can be migrated to customer_credits if needed:

INSERT INTO raw_ops.customer_credits (
    customer_id, amount_pence, remaining_pence, source_type, 
    source_id, source_description, expires_at, status
)
SELECT 
    customer_id, amount_pence, amount_pence, 'referral',
    referral_id, 'Migrated referral reward', expires_at, 'available'
FROM raw_ops.referral_rewards
WHERE is_used = FALSE AND expires_at > NOW();

Version History¶

v2.1 (2026-01-16) - Documentation Completeness¶

Restored manual_override_referral() function documentation
Restored index documentation for all tables
Added Module Details tables for all Make.com scenarios
Added v_credit_liability view documentation
Added v_referral_metrics and v_referral_leaderboard view documentation
Restored troubleshooting common causes
Added customer_credit_events audit log documentation
Added referral_processed_at column documentation
Added all supporting functions to functions table

v2.0 (2026-01-16) - Credit Ledger Integration¶

Replaced Shopify discount codes with internal credit ledger
Added customer_credits and customer_credit_applications tables
Added Scenario C (Credit Application) and Scenario D (Expiry Warning)
Added credit_earned, credit_applied, credit_expiring Customer.io events
Simplified state machine (removed rewarded status)
Updated portal integration for credit balance display

v1.1 (2026-01-15) - Phase 1 Complete¶

Make.com Scenario B implemented and tested
Shopify GraphQL discount creation (subscription-compatible)
Customer.io referral_reward_ready event working
Full end-to-end flow validated
Added manual_override_referral() function for fraud review
Replaced 24h processing window with durable referral_processed_at column

v1.0 (2026-01-15) - Phase 1 Backend¶

Database schema implemented
Core functions deployed
Make.com Scenario A built and tested
Customer.io referral_signup integration working
Fraud prevention active
Audit logging enabled

SOP 00: Shopify → Supabase Bridge (order webhook flow)
SOP 01: Order Fulfillment & Batch Allocation
Protocol_Raw_Email_Design_System_v1_0.md: Email templates
Protocol_Raw_Customer_Portal_Documentation_v2_3.md: Portal features

End of SOP-REF-01 v2.1

Last reviewed: 2026-01-16
Next review: After first 100 referrals processed
System status: ✅ Production Ready

SOP-REF-01: Referral System (v2.1)¶

What's New in v2.0+¶

Overview¶

Reward Structure¶

System Architecture¶

Database Schema¶

Core Tables¶

referral_codes¶

referrals¶

customer_credits (Credit Ledger)¶

customer_credit_applications¶

customer_credit_events (Audit Log)¶

referral_events (Audit Log)¶

Fraud Prevention¶

1. Self-Referral Check¶

2. Same Household Check¶

3. Velocity Check¶

4. Duplicate Attribution Check¶

Database Functions¶

Core Functions¶

Function: confirm_referral_and_issue_reward¶

Function: manual_override_referral¶

Function: apply_credit_to_order¶

Views¶

v_orders_pending_referral_processing¶

v_referrals_pending_delivery_confirmation¶

v_orders_pending_credit_application¶

v_credits_expiring_soon¶

v_credit_liability¶

v_referral_metrics¶

v_referral_leaderboard¶

Make.com Scenarios¶

Scenario A: SOP-REF-01 - Referral Order Enrichment¶

Scenario B: SOP-REF-02 - Delivery Confirmation Credit Issuance¶

Scenario C: SOP-REF-03 - Credit Application (Order Renewal)¶

Scenario D: SOP-REF-04 - Credit Expiry Warning¶

Customer.io Events¶

Event: referral_signup¶

Event: credit_earned¶

Event: credit_applied¶

Event: credit_expiring¶

Email Templates¶

Order Data Flow¶

How referral data enters the system¶

Customer Portal Integration¶

Credit Balance Display¶

Referral Stats Display¶

Cron Jobs¶

expire-old-credits¶

Permissions¶

Monitoring¶

Key Metrics to Track¶

Metabase Dashboards¶

Troubleshooting¶

Referral not attributed¶

Credit not issued¶

Credit not appearing in portal¶

Credit not applied to renewal¶

Duplicate referral code¶

Migration Notes (v1.x → v2.0)¶

Removed Components¶

Added Components¶

Data Migration¶

Version History¶

v2.1 (2026-01-16) - Documentation Completeness¶

v2.0 (2026-01-16) - Credit Ledger Integration¶

v1.1 (2026-01-15) - Phase 1 Complete¶

v1.0 (2026-01-15) - Phase 1 Backend¶

Related Documentation¶