SOP-REF-01: Referral System v3.0¶

Event-driven referral attribution, delivery-confirmed credit issuance, and idempotent credit application with reservation state machine.

Document ID: SOP-REF-01-v3.0 Version: 3.0 Status: ✅ Production Ready Last Updated: 2026-03-30 Owner: Protocol Raw Operations Replaces: SOP-REF-01-v2.1 Review Date: 2026-06-30

Key Changes in v3.0¶

Architecture Rebuild: - ✅ [CRITICAL] All Make.com scenarios (A, B, C, D) removed from runtime. Replaced with database triggers (event-driven) and pg_cron + Edge Functions (scheduled reconciliation) - ✅ [CRITICAL] Credit application redesigned as idempotent state machine with reservation model. Credit is reserved, not consumed, until Shopify confirms the refund - ✅ [CRITICAL] Five-state machine: pending_refund → refund_requested → refund_confirmed / refund_failed → dead_letter - ✅ [CRITICAL] Durable claim semantics via FOR UPDATE SKIP LOCKED prevent duplicate Shopify refunds - ✅ [CRITICAL] Direct expire_old_credits cron replaced with canonical pattern. Also fixed a latent bug: old function filtered on status = 'active' (nonexistent value) instead of 'available' - ✅ [HIGH] Referral attribution is event-driven: database trigger on order INSERT extracts referral slug and runs attribution inline - ✅ [HIGH] Credit issuance is event-driven: database trigger on shipment DELIVERED status issues credit inline - ✅ [HIGH] Credit expiry is reservation-aware: credits belonging to customers with active applications are not expired - ✅ [HIGH] 8-check monitoring via SOP-MON-01 with alerting for dead letters, confirmation shortfalls, stale claims, fraud rate - ✅ [MEDIUM] All functions follow fn_*_v1() naming convention - ✅ [MEDIUM] All mutations log to raw_ops.ops_events and domain audit tables - ✅ [MEDIUM] Structured referral_slug column replaces notes_json text scanning - ✅ [MEDIUM] Stable idempotency keys derived from application ID

Migration Status: - Make.com Scenarios A, B, D remain active as fallback during transition. Disable after real-traffic verification (30-day window recommended) - Make.com Scenario C (credit application) is superseded by the state machine. Disable alongside A, B, D

Purpose¶

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

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

Scope¶

This SOP governs: - Referral code creation and management - Referral attribution with fraud prevention - Delivery-confirmed credit issuance - Credit application to subscription renewals via Shopify refund - Credit expiry and expiry warnings - Monitoring and alerting for the referral system

Related SOPs: - SOP-ORD-01 — Shopify webhook ingestion (order creation triggers attribution) - SOP-DLV-01 — Courier monitoring (delivery triggers credit issuance) - SOP-MON-01 — Monitoring architecture (referral health check) - SOP-LC-01 — Lifecycle communications (delivery triggers also fire here)

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 with reservation model	Referee's first delivery confirmed

Credit Application: - Credits are stored in raw_ops.customer_credits - When referrer's subscription renews, credit is reserved (not consumed) and a Shopify refund is issued - Credit is consumed only after Shopify confirms the 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 via reservation + Shopify refund 3. Subscription-first: Works seamlessly with recurring orders 4. Delivery-confirmed rewards: Referrer credit only issued after referee receives first delivery (prevents churn arbitrage) 5. Money moves last: Internal credit state is never mutated before external settlement succeeds

System Architecture¶

EVENT-DRIVEN FLOWS
==================

1. REFERRAL CODE CREATION
   Customer Created → DB Trigger → generate_referral_slug() → referral_codes

2. REFERRAL ATTRIBUTION
   Shopify Webhook → orders INSERT → trg_extract_referral_slug (BEFORE)
       → trg_process_referral_attribution (AFTER)
       → fn_attribute_referral_v1() [fraud checks, referral record]
       → pg_net → process-referral-attribution Edge Function
       → Customer.io referral_signup

3. CREDIT ISSUANCE
   Courier Webhook → shipments UPDATE (DELIVERED)
       → trg_process_referral_delivery (AFTER)
       → fn_issue_referral_credit_v1() [£15 credit, referral confirmed]
       → pg_net → process-referral-credit-issuance Edge Function
       → Customer.io credit_earned

SCHEDULED FLOWS (Canonical Pattern)
====================================

4. CREDIT APPLICATION (every 15 min)
   pg_cron → fn_invoke_edge_function → process-referral-credits Edge Function
       → DISCOVER: fn_create_credit_application_v1 [reserve credit]
       → CLAIM: fn_claim_credit_applications_v1 [SKIP LOCKED]
       → PROCESS: Shopify Refund API [with idempotency_key]
           → Success: fn_confirm_credit_application_v1 [consume credit]
           → Failure: fn_fail_credit_application_v1 [retry or dead-letter]
       → CLEANUP: fn_release_stale_claims_v1
       → Customer.io credit_applied [only after confirm]

5. CREDIT EXPIRY WARNING (daily 9am)
   pg_cron → fn_invoke_edge_function → send-credit-expiry-warnings Edge Function
       → fn_get_expiring_credits_v1
       → Customer.io credit_expiring
       → fn_mark_expiry_warning_sent_v1

6. CREDIT EXPIRY (daily 2am)
   pg_cron → fn_invoke_edge_function → expire-referral-credits Edge Function
       → fn_expire_old_credits_v1 [reservation-aware: skips customers with active applications]

MONITORING (SOP-MON-01)
========================

7. REFERRAL HEALTH (every 15 min)
   pg_cron → fn_invoke_monitor → run-monitor → fn_check_referral_health_v2
       → monitoring_runs → ops-alerter → Slack

Credit Application State Machine¶

pending_refund ──── fn_claim ────► refund_requested
    ▲                                   │
    │                          ┌────────┴────────┐
    │                     Shopify OK        Shopify FAIL
    │                          │                 │
    │                          ▼                 ▼
    │                   refund_confirmed    refund_failed ◄── stale reset
    │                    (terminal)              │
    │                                       retry due?
    │                                        │    │
    │                                       yes   no
    │                                        │    │
    │                                fn_claim │   ▼
    └────────────────────────────────────────┘  dead_letter
                                                (terminal)

State	Meaning	Reservation Active	Terminal
`pending_refund`	Created, not yet claimed	Yes	No
`refund_requested`	Claimed by processor, Shopify call in flight	Yes	No
`refund_confirmed`	Shopify refund succeeded, credit consumed	No (zeroed)	Yes
`refund_failed`	Shopify refund failed, retry scheduled	Yes	No
`dead_letter`	Max retries exhausted, manual resolution required	No (released)	Yes

Accounting Rule: Available credit = remaining_pence minus active reservation_pence

Where active = status IN (pending_refund, refund_requested, refund_failed).

This is enforced transactionally by fn_create_credit_application_v1 using FOR UPDATE locks on customer_credits rows.

Retry Policy: Exponential backoff: 5 min, 30 min, 2 hours. Max 3 attempts. Dead-letter after exhaustion.

Idempotency: - Stable idempotency_key derived from application_id (passed to Shopify as Idempotency-Key header) - Unique partial index on order_id prevents duplicate active applications - fn_claim uses FOR UPDATE SKIP LOCKED so overlapping workers never claim the same row

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
`display_name`	TEXT	Shown on landing page
`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

referrals¶

Tracks the referrer-to-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`, `fraud_flagged`
`fraud_flags`	JSONB	Array of detected issues
`fraud_checked_at`	TIMESTAMPTZ	When fraud checks ran
`credit_id`	UUID	FK to customer_credits (reward)
`referral_processed_at`	TIMESTAMPTZ	When order was processed for referral
`created_at`	TIMESTAMPTZ	When referral attributed
`confirmed_at`	TIMESTAMPTZ	When delivery confirmed and credit issued

customer_credits¶

Credit ledger with reservation support.

Column	Type	Description
`id`	UUID	Primary key
`customer_id`	UUID	FK to customers
`amount_pence`	INTEGER	Original credit amount
`remaining_pence`	INTEGER	Unused balance (decremented only on confirmed refund)
`source_type`	TEXT	`referral`, `goodwill`, `promotion`, `manual`
`source_id`	UUID	FK to source record
`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

customer_credit_applications¶

Credit application state machine with reservation model.

Column	Type	Description
`id`	UUID	Primary key
`credit_id`	UUID	DEPRECATED: no longer populated (v3.0)
`customer_id`	UUID	FK to customers
`order_id`	UUID	FK to orders
`amount_pence`	INTEGER	Amount to apply
`reservation_pence`	INTEGER	Amount reserved (zeroed on confirm/dead-letter)
`status`	TEXT	State machine: see above
`attempt_count`	INTEGER	Shopify call attempts
`max_attempts`	INTEGER	Default 3
`last_attempt_at`	TIMESTAMPTZ	Last processing attempt
`next_retry_at`	TIMESTAMPTZ	When eligible for retry
`failure_code`	TEXT	Last failure reason
`failure_payload`	JSONB	Last failure details
`idempotency_key`	TEXT	Stable key for Shopify deduplication
`shopify_refund_id`	TEXT	Shopify refund reference (set on confirm)
`confirmed_at`	TIMESTAMPTZ	When refund confirmed
`dead_lettered_at`	TIMESTAMPTZ	When moved to dead letter
`created_at`	TIMESTAMPTZ	When application created
`updated_at`	TIMESTAMPTZ	Auto-maintained by trigger

Constraints: - chk_credit_application_status: valid states only - chk_credit_application_reservation_non_negative: reservation >= 0 - chk_credit_application_attempts_non_negative: attempts >= 0 - chk_credit_application_max_attempts_positive: max_attempts > 0 - chk_credit_application_attempts_within_max: attempts <= max_attempts - chk_credit_application_confirmed_fields: confirmed rows must have refund_id, confirmed_at, reservation = 0 - chk_credit_application_dead_letter_fields: dead-letter rows must have dead_lettered_at - chk_credit_application_dead_letter_release: dead-letter rows must have reservation = 0

Indexes: - idx_credit_applications_order_active (UNIQUE partial): one active application per order - idx_credit_applications_idempotency (UNIQUE): idempotency key uniqueness - idx_credit_applications_pending_retry (partial): processor query index - idx_credit_applications_customer_active (partial): active reservations per customer - idx_credit_applications_dead_letter (partial): dead-letter monitoring

customer_credit_events (Audit Log)¶

Immutable append-only log of credit system decisions.

Event Types: - credit_issued — Credit created - credit_reserved — Credit reserved for application (v3.0) - credit_applied — Credit consumed on confirmed refund - credit_expired — Credit expired unused - credit_cancelled — Credit manually cancelled - expiry_warning_sent — 7-day warning email triggered - expiry_skipped_reservation — Expiry skipped due to active reservation (v3.0) - application_retry_scheduled — Refund failed, retry scheduled (v3.0) - application_dead_letter — Application moved to dead letter (v3.0)

referral_events (Audit Log)¶

Immutable append-only log of referral system decisions.

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

Fraud Prevention¶

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

Self-Referral Check: Referee email matches referrer email (case-insensitive). Action: block.
Same Household Check: Normalized address + postcode match. Action: flag as fraud_flagged.
Velocity Check: Referrer has >5 referrals in past 7 days. Action: flag as fraud_flagged.
Duplicate Attribution Check: Referee email already exists in referrals table. Action: block (idempotent).

Database Functions¶

Event-Driven Functions¶

Function	Purpose	Called By
`generate_referral_slug()`	Creates 6-char unique slug	Trigger on customer INSERT
`create_referral_code_for_customer()`	Auto-creates code on customer INSERT	Trigger
`fn_extract_referral_slug_v1()`	Extracts referral slug from notes_json	BEFORE trigger on orders
`fn_trigger_referral_attribution_v1()`	Runs attribution on order with referral slug	AFTER trigger on orders
`fn_attribute_referral_v1()`	Wraps attribute_referral() with idempotency + ops_events	Attribution trigger
`fn_trigger_referral_credit_issuance_v1()`	Issues credit when shipment DELIVERED	AFTER trigger on shipments
`fn_issue_referral_credit_v1()`	Wraps confirm_referral_and_issue_reward() with FOR UPDATE lock + ops_events	Credit issuance trigger

Credit Application Functions¶

Function	Purpose	Called By
`fn_create_credit_application_v1()`	Creates reservation with FOR UPDATE lock on credit rows	process-referral-credits Edge Function
`fn_claim_credit_applications_v1()`	Claims applications via FOR UPDATE SKIP LOCKED	process-referral-credits Edge Function
`fn_confirm_credit_application_v1()`	Consumes credit via FEFO after Shopify confirms refund	process-referral-credits Edge Function
`fn_fail_credit_application_v1()`	Schedules retry or dead-letters after Shopify failure	process-referral-credits Edge Function
`fn_release_stale_claims_v1()`	Resets stuck refund_requested rows after 15 min	process-referral-credits Edge Function
`fn_get_available_credit_pence_v1()`	Available = remaining minus active reservations	Views, portal, application creation
`fn_get_customer_credits_v1()`	Portal credit display with auth.uid() ownership enforcement	Customer Portal

Expiry Functions¶

Function	Purpose	Called By
`fn_get_expiring_credits_v1()`	Credits expiring within 7 days, grouped by customer	send-credit-expiry-warnings Edge Function
`fn_mark_expiry_warning_sent_v1()`	Marks credits as warned	send-credit-expiry-warnings Edge Function
`fn_expire_old_credits_v1()`	Reservation-aware expiry (skips customers with active applications)	expire-referral-credits Edge Function

Portal/Landing Page Functions¶

Function	Purpose	Called By
`fn_get_customer_credits_v1()`	Credit balance net of reservations	Customer Portal
`get_customer_referral_info()`	Referral stats for portal	Customer Portal
`get_referral_by_slug()`	Display name for landing page	Landing Page

Monitoring Function¶

Function	Purpose	Called By
`fn_check_referral_health_v2()`	8 health checks with alerting	run-monitor via pg_cron

Edge Functions¶

Function	Purpose	Trigger	Pattern
`process-referral-attribution`	Sends Customer.io `referral_signup`	pg_net from attribution trigger	Event-driven
`process-referral-credit-issuance`	Sends Customer.io `credit_earned`	pg_net from delivery trigger	Event-driven
`process-referral-credits`	Discovers, claims, processes credit applications	pg_cron every 15 min	Scheduled
`send-credit-expiry-warnings`	Sends Customer.io `credit_expiring`	pg_cron daily 9am	Scheduled
`expire-referral-credits`	Runs reservation-aware expiry	pg_cron daily 2am	Scheduled

All Edge Functions use verify_jwt = true.

Database Triggers¶

Trigger	Table	Event	Function
`trg_extract_referral_slug`	orders	BEFORE INSERT/UPDATE OF notes_json	`fn_extract_referral_slug_v1`
`trg_process_referral_attribution`	orders	AFTER INSERT/UPDATE OF referral_slug	`fn_trigger_referral_attribution_v1`
`trg_process_referral_delivery`	shipments	AFTER INSERT/UPDATE OF status	`fn_trigger_referral_credit_issuance_v1`
(existing)	customers	AFTER INSERT	`create_referral_code_for_customer`
`trg_credit_applications_updated_at`	customer_credit_applications	BEFORE UPDATE	`fn_set_updated_at_v1`

pg_cron Jobs¶

Job Name	Schedule	Target	Pattern
`referral-process-credits`	`/15 * * *`	`process-referral-credits`	`fn_invoke_edge_function`
`referral-expiry-warnings`	`0 9 * * *`	`send-credit-expiry-warnings`	`fn_invoke_edge_function`
`referral-expire-credits`	`0 2 * * *`	`expire-referral-credits`	`fn_invoke_edge_function`
`monitor-referral-health`	`/15 * * *`	`referral_system_health`	`fn_invoke_monitor`

Disabled (legacy, kept for rollback): | Job Name | Status | Notes | |----------|--------|-------| | expire-old-credits (jobid 19) | Disabled | Old direct cron. Had latent bug: filtered on status = 'active' (nonexistent) instead of 'available' |

Customer.io Events¶

Event Name	Recipient	Trigger	Purpose
`referral_signup`	Referrer	Order attributed to referral code	"Your friend just signed up"
`credit_earned`	Referrer	Referee's first delivery confirmed	"You've earned £15 credit"
`credit_applied`	Customer	Credit refund confirmed by Shopify	"Your credit has been applied"
`credit_expiring`	Customer	7 days before expiry	"Your credit expires soon"

Monitoring¶

Monitor: referral_system_health¶

Function: raw_ops.fn_check_referral_health_v2() Schedule: Every 15 minutes Registered in: run-monitor Edge Function

Check	Threshold	Severity	Channel
Unprocessed referral orders > 30 min	> 0	`warning`	`ops-alerts`
Stuck pending referrals (delivered > 1h, no credit)	> 0	`warning`	`ops-alerts`
Dead-letter credit applications	> 0	`critical`	`ops-urgent`
Confirmation shortfalls (24h)	> 0	`critical`	`ops-urgent`
Stale claims (refund_requested > 15 min)	> 0	`warning`	`ops-alerts`
Failed credit applications > 30 min	> 0	`warning`	`ops-alerts`
Credit expiry job staleness (36h)	stale	`warning`	`ops-alerts`
Fraud flag rate (7d rolling, >= 10 referrals)	> 10%	`warning`	`ops-alerts`

Note: Early-phase thresholds are deliberately aggressive. Relax after real-traffic patterns are understood.

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 `fn_get_customer_credits_v1` (ownership enforced via auth.uid()), `get_customer_referral_info`

Troubleshooting¶

Referral not attributed¶

Symptoms: Customer used referral link but no referral record created

Investigation:

-- Check if order has referral slug
SELECT id, referral_slug, referral_processed_at, notes_json
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;

-- Check ops_events for attribution
SELECT * FROM raw_ops.ops_events
WHERE entity_type = 'REFERRAL' AND meta->>'order_id' = '[order_id]'
ORDER BY created_at DESC;

Common causes: 1. Referral slug not included in order notes (frontend/localStorage issue) 2. Email already referred (check referral_events for attribution_failed) 3. Trigger not firing (check trg_process_referral_attribution exists) 4. Order already processed (referral_processed_at is set)

Credit not issued after delivery¶

Symptoms: Referral stuck at pending after delivery confirmed

Investigation:

-- Check referral and shipment status
SELECT r.*, s.status AS shipment_status
FROM raw_ops.referrals r
JOIN raw_ops.shipments s ON s.order_id = r.referee_order_id
WHERE r.referee_customer_id = '[customer_id]';

-- Check ops_events for credit issuance
SELECT * FROM raw_ops.ops_events
WHERE entity_type = 'REFERRAL' AND message LIKE '%credit%'
ORDER BY created_at DESC LIMIT 10;

Common causes: 1. Delivery not yet confirmed in system (courier webhook delay) 2. Trigger not firing (check trg_process_referral_delivery exists) 3. Referral was fraud_flagged (check fraud_flags column)

Credit application stuck¶

Symptoms: Customer has credit but renewal was not refunded

Investigation:

-- Check application state
SELECT * FROM raw_ops.customer_credit_applications
WHERE customer_id = '[customer_id]'
ORDER BY created_at DESC;

-- Check for dead letters
SELECT * FROM raw_ops.customer_credit_applications
WHERE status = 'dead_letter'
ORDER BY dead_lettered_at DESC;

-- Check ops_events for failures
SELECT * FROM raw_ops.ops_events
WHERE entity_type = 'CREDIT'
  AND kind IN ('EXCEPTION', 'WARNING')
ORDER BY created_at DESC LIMIT 10;

Common causes: 1. Application in refund_failed (check failure_code and next_retry_at) 2. Application in dead_letter (manual resolution required) 3. Confirmation shortfall (ops_events EXCEPTION: credit pool insufficient) 4. No available credit after reservations (check fn_get_available_credit_pence_v1)

Manual resolution: dead-letter application¶

-- Review the dead-lettered application
SELECT * FROM raw_ops.customer_credit_applications
WHERE id = '[application_id]' AND status = 'dead_letter';

-- Option 1: Reset for retry (if root cause is fixed)
UPDATE raw_ops.customer_credit_applications
SET status = 'refund_failed',
    attempt_count = 0,
    next_retry_at = NOW(),
    dead_lettered_at = NULL
WHERE id = '[application_id]' AND status = 'dead_letter';

-- Option 2: Release reservation and cancel
UPDATE raw_ops.customer_credit_applications
SET reservation_pence = 0
WHERE id = '[application_id]' AND status = 'dead_letter';
-- Then investigate whether Shopify refund actually happened

Version History¶

Version	Date	Changes	Author
3.0	2026-03-30	Complete architecture rebuild. Make.com removed. Event-driven attribution and credit issuance. Idempotent credit application state machine with reservation model. Canonical scheduled expiry. 8-check monitoring.	Anton
2.1	2026-01-16	Documentation completeness	Anton
2.0	2026-01-16	Credit ledger integration	Anton
1.1	2026-01-15	Phase 1 complete	Anton
1.0	2026-01-15	Phase 1 backend	Anton

End of SOP-REF-01 v3.0

Last reviewed: 2026-03-30 Next review: After first 100 referrals processed, or 2026-06-30, whichever comes first System status: ✅ Production Ready (Make.com fallback active during transition)