SOP CS-00: Protocol Raw Customer Operations System

Version: 1.6
Status: ðŸâ€µ Master Design Document
Created: 2026-01-13
Updated: 2026-02-06
Owner: Protocol Raw Operations
Classification: Strategic Infrastructure

---

v1.5 Changes (Historical)

This version restores comprehensive design documentation that was inadvertently dropped in v1.4, while preserving all v1.4 email threading features.

v1.5 Restorations (from v1.3): - ✅ Track Current Delivery UI Specification (Section 2.1.3) - ✅ Future: Proactive Delay Notifications (Section 3.3) - ✅ Complete Resolution Path Details with flowcharts (Section 5.2) - ✅ Customer Context Panel: Address, Order History, Active Shipment sections - ✅ Query Performance Requirements (Section 8.3) - ✅ Database size in Scale Targets - ✅ Support Queue View SQL (Section 6.4) - ✅ Address Change Technical Reference (Appendix D) - ✅ order_items table in Core Tables

v1.4 Content Preserved: - ✅ Email threading: In-Reply-To and References headers for proper inbox threading - ✅ [Support] subject prefix on all outbound emails - ✅ Conversation history: ticket_messages table tracks full back-and-forth - ✅ Unified send-support-email Edge Function (used by CS-03 + Ops Portal) - ✅ email_message_id stored on tickets for reply threading - ✅ Updated SOP references (CS-03 v1.5)

v1.3 Content Preserved: - ✅ Shadow mode complete: unified workflow, draft_usage tracking, policy gate display - ✅ Complete Customer Lifecycle Map - ✅ Full Self-Service Specification - ✅ Proactive Communications Matrix - ✅ Agent Workstation Specification (UI details) - ✅ Contact Type Analysis & Resolution Paths - ✅ Complete Data Architecture - ✅ Integration Architecture - ✅ Performance & Scale Requirements - ✅ Success Metrics (all 4 sections) - ✅ Glossary - ✅ Address Change Technical Reference

---

Document Purpose

This is the master reference document for Protocol Raw's customer operations infrastructure. It defines every customer touchpoint, every self-serve capability, every proactive communication, every agent action, and the complete data architecture required to serve 100,000+ customers without major re-engineering.

Design Philosophy:

• Customer contact is a system failure
• Self-serve handles 90%+ of needs
• When contact happens, resolution in under 2 minutes
• Agent has complete context without leaving the portal
• Zero manual database queries, zero switching tabs
• Build once, scale forever

Goal: Resolve 90%+ of customer needs without human intervention while maintaining "proof, not promises" brand credibility.

---

Table of Contents

1. Customer Lifecycle Map
2. Self-Service Specification
3. Proactive Communications Matrix
4. Agent Workstation Specification
5. Contact Type Analysis & Resolution Paths
6. Data Architecture
7. Integration Architecture
8. Performance & Scale Requirements
9. Implementation Roadmap
10. Success Metrics
11. Current System State (Shadow Mode)
12. Roadmap to Autonomous Mode

---

System Architecture Overview

Core Platforms

Platform	Purpose	Status
Supabase	Customer data, tickets, decisions, audit trail	✅ Production
Make.com	Workflow orchestration, integrations	✅ Production
Customer.io	Email lifecycle, transactional emails	✅ Production
Shopify	Commerce, orders, subscriptions (via Seal)	✅ Production
Ops Portal	Internal operations interface	✅ Production
Customer Portal	Customer self-service	✅ Production
Claude Sonnet	AI classification and response drafting	✅ Production

Channel Architecture

                           ┌─────────────────────────┐
                           │     Customer Need       │
                           └───────────┬─────────────┘
                                       │
              ┌────────────────────────┼────────────────────────┐
              ▼                        ▼                        ▼
     ┌────────────────┐      ┌────────────────┐      ┌────────────────┐
     │  Self-Serve    │      │  Live Chat     │      │    Email       │
     │  (Portal)      │      │  (AI + Human)  │      │  (AI Triage)   │
     └───────┬────────┘      └───────┬────────┘      └───────┬────────┘
             │                       │                       │
             ▼                       ▼                       ▼
     ┌────────────────┐      ┌────────────────┐      ┌────────────────┐
     │  Automated     │      │  AI Resolution │      │  AI Drafts     │
     │  Resolution    │      │  OR Escalation │      │  Human Reviews │
     └────────────────┘      └────────────────┘      └────────────────┘

---

1. Customer Lifecycle Map

1.1 Lifecycle Stages

┌─────────────┐    ┌─────────────┐    ┌─────────────┐    ┌─────────────┐
│  PROSPECT   │ →  │  ONBOARDING │ →  │   ACTIVE    │ →  │   CHURNED   │
│             │    │  (Box 1-2)  │    │  (Box 3+)   │    │             │
└─────────────┘    └─────────────┘    └─────────────┘    └─────────────┘
      ↓                  ↓                  ↓                  ↓
  Calculator         Transition        Steady State        Win-back
  Live Chat          Guidance          Self-Service        Campaigns
  Website Q&A        Check-ins         Proof Portal        

1.2 Complete Touchpoint Map

Stage	Touchpoint	Channel	Owner	Self-Serve?
PROSPECT
	Visit website	Web	Marketing	✅
	Use feeding calculator	Web	Marketing	✅
	Read batch reports (proof)	Web	Marketing	✅
	Pre-purchase questions	Live Chat	AI → Human	✅ AI / ⚠ï¸Â Escalation
	View pricing/products	Web	Marketing	✅
CONVERSION
	Add to cart	Shopify	Automation	✅
	Checkout	Shopify	Automation	✅
	Abandoned cart recovery	Email (Customer.io)	Automation	✅
	Order confirmation	Email	Automation	✅
	Welcome email	Email	Automation	✅
ONBOARDING
	Portal access email	Email	Automation	✅
	Transition guide (Day 0)	Email	Automation	✅
	Week 1 check-in	Email	Automation	✅
	Week 2 check-in	Email	Automation	✅
	Box 1 delivery	Courier (DPD)	3PL	✅
	Delivery exception	Email	Courier Watchdog	✅
	Box 2 reminder	Email	Automation	✅
	Transition questions	Live Chat / Email	AI → Human	✅ AI / ⚠ï¸Â Escalation
ACTIVE
	Manage subscription	Customer Portal	Self-serve	✅
	View batch proof	Proof Portal	Self-serve	✅
	7-day delivery reminder	Email	Automation	✅
	48-hour lock notification	Email	Automation	✅
	Dispatch confirmation	Email	Automation	✅
	Delivery tracking	Email + DPD	Automation	✅
	Delivery exception	Email	Courier Watchdog	✅
	Feeding questions	Live Chat / Email	AI → Human	✅ AI / ⚠ï¸Â Escalation
	Quality concern	Email	Human	❌
	Product issue	Email	Human	❌
	Refund request	Email	Human	❌
	Address update	Customer Portal	Self-serve	✅
	Payment update	Shopify	Self-serve	⚠ï¸Â Requires Shopify login
	Cancel subscription	Customer Portal	Self-serve (friction)	✅
AT-RISK
	Pause subscription	Customer Portal	Self-serve	✅
	Skip delivery	Customer Portal	Self-serve	✅
	Cancel flow	Customer Portal	Self-serve + offers	✅
	Bereavement	Email	Human (compassionate)	❌
CHURNED
	Win-back campaign	Email	Automation	✅
	Reactivation offer	Email	Automation	✅

1.3 Contact Prevention Hierarchy

For each potential need, the system should attempt resolution in this order:

1. PREVENT     → Proactive communication eliminates the need
2. SELF-SERVE  → Customer Portal / Proof Portal handles it
3. AI-RESOLVE  → Live Chat or Email AI resolves without human
4. AI-ASSIST   → AI drafts response, human reviews and sends
5. HUMAN       → Full human handling required

Target Distribution at Scale (100k customers):

Level	% of Potential Contacts	Monthly Volume
PREVENT	60%	0 (prevented)
SELF-SERVE	25%	0 (self-resolved)
AI-RESOLVE	10%	~400 (auto-handled)
AI-ASSIST	4%	~160 (human review)
HUMAN	1%	~40 (full handling)

---

2. Self-Service Specification

2.1 Customer Portal (my.protocolraw.co.uk)

Current Status: ✅ Production (v2.5)

2.1.1 Current Capabilities

Feature	Status	Notes
Magic link authentication	✅ Built	7-day token expiry
View subscription status	✅ Built	Active/Paused/Cancelled
View next delivery date	✅ Built	With countdown
Skip next delivery	✅ Built	With confirmation flow
Reschedule delivery	✅ Built	Date picker, 3-day minimum
Pause subscription	✅ Built	With confirmation
Resume subscription	✅ Built	One-click
Change box size	✅ Built	8kg/12kg/16kg with confirmation
Change frequency	✅ Built	2-6 weeks with confirmation
Update feeding plan	✅ Built	Weight, life stage, activity, condition
View order history	✅ Built	Timeline with batch links
View batch proof	✅ Built	Links to Proof Portal
Cancel subscription	✅ Built	Multi-step with counter-offers
48-hour delivery lock	✅ Built	Prevents changes during preparation
Multi-dog support	✅ Built	Household feeding totals
Request portal access (self)	✅ Built	Landing page with email form
Update shipping address	✅ Built	Syncs to Shopify + Seal

2.1.2 Missing Capabilities (To Build)

Feature	Priority	Complexity	Impact
Track current delivery	ðŸâ€´ Critical	Low	Prevents 15-20% of contacts
View payment method	🟡 Medium	Medium	Reduces payment queries
Update payment method	🟡 Medium	High	Requires Shopify integration

2.1.3 Track Current Delivery Specification

Purpose: Allow customers to see their current order's delivery status without contacting support.

Location: Customer Portal → Dashboard → Below "Next Delivery" hero card

UI Specification:

┌─────────────────────────────────────────────────────────────────┐
│ 📦 Your Order is On Its Way                                 │
├─────────────────────────────────────────────────────────────────┤
│                                                             │
│  ●────────●────────●────────○                               │
│  Packed   Shipped  Out for   Delivered                      │
│                    Delivery                                 │
│                                                             │
│  Status: Out for delivery                                   │
│  Tracking: DPD123456789GB                                   │
│  Expected: Today by 6pm                                     │
│                                                             │
│  [Track on DPD →]                                           │
└─────────────────────────────────────────────────────────────────┘

Data Source: raw_ops.shipments joined with raw_ops.courier_events

Display States:

Order Status	Card Displayed?	Content
Pending (pre-dispatch)	❌	Nothing (use existing "Next Delivery" card)
Dispatched, in transit	✅	Full tracking card with progress
Delivered	✅ (24h)	"Delivered" confirmation, then hide
Exception (failed)	✅	Alert state with instructions

2.2 Proof Portal (proof.protocolraw.co.uk)

Current Status: ✅ Production

Feature	Status	Notes
Batch report page	✅ Built	Lab results, ingredients, production date
QR code generation	✅ Built	Links from packaging to batch page
Public access (no login)	✅ Built	Transparency = public
PDF download	✅ Built	Lab certificate download

2.3 Landing Page Self-Service

Feature	Status	Notes
Feeding calculator	✅ Built	RER/MER-based, all life stages
FAQ/Help centre	ðŸâ€µ Planned	Deflect common questions
Status page	ðŸâ€µ Planned	Delivery issues transparency

---

3. Proactive Communications Matrix

3.1 Lifecycle Emails (Customer.io)

Trigger	Email Name	Timing	Purpose
First order created	Welcome	Immediate	Set expectations, portal link
Order created	Order Confirmation	Immediate	Receipt, what to expect
First order	Transition Guide	+1 day	How to introduce raw food
First order	Week 1 Check-in	+7 days	Address early concerns
First order	Week 2 Check-in	+14 days	Reinforce benefits
Upcoming renewal	Delivery Reminder	-7 days	Prepare for delivery
Renewal locked	48-hour Notice	-48 hours	Last chance to modify
Order dispatched	Dispatch Confirmation	Immediate	Tracking link
Order delivered	Delivery Confirmation	Immediate	Batch proof link
Subscription paused	Pause Confirmation	Immediate	Resume instructions
Subscription resumed	Resume Confirmation	Immediate	Next delivery date
Subscription cancelled	Cancellation Confirmation	Immediate	Win-back offer
30 days since cancel	Win-back 1	+30 days	Incentive to return
60 days since cancel	Win-back 2	+60 days	Stronger incentive

3.2 Operational Notifications

Event	Channel	Timing	Audience
Delivery exception	Email	Immediate	Customer
Delivery rescheduled	Email	Immediate	Customer
Payment failed	Email	Immediate	Customer
Payment retry	Email	Retry schedule	Customer
Batch recall (if ever)	Email + SMS	Immediate	Affected customers

3.3 Future: Proactive Delay Notifications

Trigger	Action	Status
Courier delay detected (Watchdog)	Auto-email customer before they ask	ðŸâ€µ Planned
Stock shortage affecting renewal	Notify customer of delay	ðŸâ€µ Planned
Extreme weather delay	Batch notification	ðŸâ€µ Planned

---

4. Agent Workstation Specification

4.1 Support Workstation UI

Location: Ops Portal (ops.protocolraw.co.uk) → Support Workstation

┌─────────────────────────────────────────────────────────────────────────────────┐
│ Support Workstation                                        Anton (Agent)    │
├────────────────────────────────┬────────────────────────────────────────────────┤
│ QUEUE                          │  TICKET DETAIL                                 │
│                                │                                                │
│ ┌────────────────────────────┐ │  ┌──────────────────────────────────────────┐  │
│ │ ðŸâ€´ John Smith         2m   │ │  │ FROM: john@example.com                   │  │
│ │    Quality concern         │ │  │ SUBJECT: My dog is sick                  │  │
│ └────────────────────────────┘ │  │ STATUS: AI Reviewed                      │  │
│ ┌────────────────────────────┐ │  └──────────────────────────────────────────┘  │
│ │ 🟢 Sarah Jones        15m  │ │                                                │
│ │    Delivery question       │ │  CONVERSATION THREAD (v1.4)                    │
│ └────────────────────────────┘ │  ──────────────────────                         │
│ ┌────────────────────────────┐ │  ┌──────────────────────────────────────────┐  │
│ │ 🟡 Mike Wilson        1h   │ │  │ â†Â Customer (10:15)                       │  │
│ │    Subscription change     │ │  │ "Hi, my dog has been sick since..."      │  │
│ └────────────────────────────┘ │  ├──────────────────────────────────────────┤  │
│                                │  │ → Sophie (10:45)                         │  │
│                                │  │ "I'm so sorry to hear that..."           │  │
│                                │  ├──────────────────────────────────────────┤  │
│                                │  │ â†Â Customer (11:02)                       │  │
│                                │  │ "Thanks, but I still have questions..."  │  │
│                                │  └──────────────────────────────────────────┘  │
│                                │                                                │
│                                │  ┌──────────────────────────────────────────┐  │
│                                │  │ 🚨 POLICY GATE: health_vomiting           │  │
│                                │  │ AI bypassed - immediate escalation        │  │
│                                │  └──────────────────────────────────────────┘  │
│                                │                                                │
│                                │  🤖 AI TRIAGE                                   │
│                                │  ────────────                                   │
│                                │  Category: escalation                          │
│                                │  Confidence: 0%                                │
│                                │                                                │
│                                │  DRAFT RESPONSE                                │
│                                │  ──────────────                                │
│                                │  ┌──────────────────────────────────────────┐  │
│                                │  │ Subject: [Support] Re: My dog is sick    │  │
│                                │  ├──────────────────────────────────────────┤  │
│                                │  │ [Editable textarea with AI draft]        │  │
│                                │  ├──────────────────────────────────────────┤  │
│                                │  │ [Send Response]    [Resolve]             │  │
│                                │  └──────────────────────────────────────────┘  │
└────────────────────────────────┴────────────────────────────────────────────────┘

4.2 Conversation Thread (v1.4)

The Support Workstation now displays the full conversation history for each ticket:

Feature	Status	Notes
Inbound messages displayed	✅ Built	Customer emails in thread
Outbound messages displayed	✅ Built	Our replies in thread
Chronological order	✅ Built	Oldest first
Sender identification	✅ Built	Customer email or persona name
Timestamp display	✅ Built	Relative time (e.g., "2h ago")

Data Source: raw_ops.ticket_messages table

4.3 Email Response Features (v1.4)

Feature	Status	Notes
Editable subject line	✅ Built	Pre-filled with [Support] prefix
[Support] prefix auto-added	✅ Built	Distinguishes from marketing
Email threading	✅ Built	In-Reply-To + References headers
Persona sign-off	✅ Built	Sophie/Tom/Lucy auto-appended
Panel clears after send	✅ Built	Ready for next ticket

4.4 Priority Queue

Priority	Criteria	Visual
1 (High)	Policy gate OR escalation_reason set	Red badge
2 (Medium)	Confidence < 0.70	Orange badge
3 (Normal)	Standard AI classification	Green badge
4 (Awaiting)	Outbound/awaiting customer reply	Purple badge

4.5 Customer Context Panel

The Customer Profile Panel provides complete context without leaving the portal:

Profile Section:

Field	Source	Update Method
Name	raw_ops.customers	Shopify sync
Email	raw_ops.customers	Shopify sync
Phone	raw_ops.customers	Shopify sync
Customer since	raw_ops.customers.created_at	Automatic
LTV	Calculated from orders	Automatic
Order count	COUNT(raw_ops.orders)	Automatic

Subscription Section:

Field	Source	Actions Available
Status	Seal API	Pause, Resume, Cancel
Box size	Seal API	Change (8/12/16kg)
Frequency	Seal API	Change (2-6 weeks)
Next billing	Seal API	Skip
Next delivery	Calculated	View

Address Section:

Field	Source	Actions Available
Shipping address	raw_ops.customers	Edit (syncs to Shopify + Seal)
Billing address	Shopify	View only (edit in Shopify)

Order History Section:

Column	Source	Notes
Order #	raw_ops.orders.order_number	Link to Shopify
Date	raw_ops.orders.created_at
Amount	raw_ops.orders.total_price
Status	raw_ops.orders.fulfillment_status
Batch	raw_ops.order_items.batch_id	Link to Proof Portal

Active Shipment Section:

Field	Source	Notes
Tracking #	raw_ops.shipments.tracking_number	Link to DPD
Status	raw_ops.shipments.status
Last update	raw_ops.courier_events.created_at
Expected delivery	raw_ops.shipments.expected_delivery

Support History Section:

Column	Source	Notes
Date	raw_ops.support_tickets.created_at
Subject	raw_ops.support_tickets.subject
Status	raw_ops.support_tickets.status
Channel	raw_ops.support_tickets.channel	email/chat
Messages	COUNT(raw_ops.ticket_messages)	Thread depth (v1.4)

4.6 Agent Actions

Action	Method	Status
Send email response	Ops Portal → Send	✅ Built
Edit AI draft	Ops Portal → Edit draft	✅ Built
Edit subject line	Ops Portal → Subject field	✅ Built (v1.4)
Resolve ticket	Ops Portal → Resolve	✅ Built
Edit customer address	Ops Portal → Customer Profile	✅ Built
View subscription	Ops Portal → Customer Profile	✅ Built
View conversation thread	Ops Portal → Thread panel	✅ Built (v1.4)
Issue refund	Ops Portal → Refund button	ðŸâ€µ Planned
Send replacement	Ops Portal → Replacement button	ðŸâ€µ Planned

---

5. Contact Type Analysis & Resolution Paths

5.1 Expected Contact Categories

Category	Expected %	Resolution Path	Target Resolution
Delivery status	30%	Self-serve (portal tracking)	<1 min (auto)
Delivery exception	15%	Courier Watchdog → proactive email	<5 min (auto)
Subscription change	15%	Self-serve (portal)	<1 min (auto)
Feeding questions	15%	AI chat/email → Knowledge base	<5 min (AI)
Product quality	10%	Human review required	<2 hours (human)
Billing/payment	8%	Partial self-serve → human	<30 min
Other	7%	Varies	Varies

5.2 Resolution Path Details

5.2.1 "Where's my order?"

Customer: "Where's my order?"
│
├── Portal shows tracking? ──YES──→ Prevented (no contact needed)
│
├── Contact made (chat/email)
│   │
│   ├── AI looks up shipment
│   │   │
│   │   ├── Tracking available ──→ AI responds with status
│   │   │                          "Your order was dispatched yesterday 
│   │   │                           and is currently with DPD..."
│   │   │
│   │   └── No shipment yet ──→ AI responds with expected date
│   │                           "Your order is being prepared and will
│   │                            ship by [date]..."
│   │
│   └── Resolution: AI-resolve (no human needed)

5.2.2 Delivery Exception

Courier Watchdog detects exception
│
├── Auto-email sent to customer
│   "We've noticed your delivery has been delayed..."
│
├── If customer contacts anyway
│   │
│   ├── AI sees exception in system
│   └── AI responds with updated status + apology
│
└── Resolution: PREVENTED or AI-resolve

5.2.3 Subscription Changes

Customer wants to change subscription
│
├── Uses portal ──→ Self-serve (no contact)
│   - Skip delivery
│   - Pause/resume
│   - Change box size
│   - Change frequency
│   - Update address
│
├── Contacts support
│   │
│   ├── AI offers portal link
│   └── If portal not working → Human assists
│
└── Resolution: Self-serve or AI-resolve

5.2.4 Quality Concerns

Customer reports quality issue
│
├── Policy gate: quality concerns
│   │
│   └── Immediate escalation to human
│       │
│       ├── Agent reviews batch records
│       ├── Agent checks for pattern
│       ├── Agent offers refund/replacement
│       └── Agent logs for QA review
│
└── Resolution: HUMAN ONLY

---

6. Data Architecture

6.1 Core Tables

Table	Purpose	Key Fields
raw_ops.customers	Customer master	id, email, shopify_customer_id, address fields
raw_ops.orders	Order records	id, customer_id, order_number, status, total_price
raw_ops.order_items	Line items	order_id, product_id, batch_id, quantity
raw_ops.subscriptions	Subscription state	customer_id, seal_subscription_id, status
raw_ops.shipments	Delivery tracking	order_id, tracking_number, status, carrier
raw_ops.courier_events	Tracking history	shipment_id, event_type, timestamp
raw_ops.support_tickets	Support requests	id, customer_id, channel, status, email_message_id
raw_ops.ticket_messages	Conversation thread (v1.4)	ticket_id, direction, sender, body
raw_ops.ticket_notes	Internal notes	ticket_id, note_type, content, created_by
raw_ops.cs_agent_decisions	AI decision audit	ticket_id, ai_category, ai_confidence, policy_gate_triggered
raw_ops.address_changes	Address edit audit	customer_id, old_address, new_address, synced
raw_ops.batches	Batch records	id, batch_code, production_date, lab_status

6.2 Key Views

View	Purpose	Source Tables
v_support_queue	Unified Support Workstation queue	support_tickets, cs_agent_decisions, customers
v_customer_context	Customer 360° view	customers, subscriptions, orders
v_customer_active_shipment	Current delivery status	shipments, courier_events, orders
v_support_resolved_today	Daily resolution metrics	support_tickets

6.3 Key Fields (v1.4 Additions)

Table	Field	Purpose
support_tickets	email_message_id	Original email Message-ID for threading
support_tickets	email_references	Thread chain for multi-reply threads
support_tickets	draft_usage	How AI draft was used (sent_as_is, minor_edits, major_rewrite, replaced)
cs_agent_decisions	policy_gate_triggered	Whether code gate fired
cs_agent_decisions	policy_gate_code	Machine code for analytics
ticket_messages	direction	inbound (customer) or outbound (us)
ticket_messages	sender	Customer email or persona name
ticket_messages	delivery_id	Customer.io delivery ID for outbound

6.4 Ticket Messages Table (v1.4)

CREATE TABLE raw_ops.ticket_messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  ticket_id UUID NOT NULL REFERENCES raw_ops.support_tickets(id),
  direction TEXT NOT NULL CHECK (direction IN ('inbound', 'outbound')),
  sender TEXT NOT NULL,
  subject TEXT,
  body TEXT NOT NULL,
  email_message_id TEXT,
  delivery_id TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_ticket_messages_ticket_id ON raw_ops.ticket_messages(ticket_id);

6.5 Support Queue View

-- v_support_queue (simplified)
CREATE OR REPLACE VIEW raw_ops.v_support_queue AS
SELECT 
  t.id,
  t.subject,
  t.status,
  t.channel,
  t.created_at,
  t.draft_usage,
  c.email as customer_email,
  c.first_name || ' ' || c.last_name as customer_name,
  d.ai_category,
  d.ai_confidence,
  d.ai_draft,
  d.ai_action,
  d.escalation_reason,
  d.policy_gate_triggered,
  d.policy_gate_code,
  CASE 
    WHEN d.policy_gate_triggered OR d.escalation_reason IS NOT NULL THEN 1
    WHEN d.ai_confidence < 0.70 THEN 2
    WHEN t.status = 'awaiting_customer' THEN 4
    ELSE 3
  END as priority
FROM raw_ops.support_tickets t
LEFT JOIN raw_ops.customers c ON t.customer_id = c.id
LEFT JOIN raw_ops.cs_agent_decisions d ON t.id = d.ticket_id
WHERE t.status NOT IN ('resolved', 'closed')
ORDER BY priority, t.created_at;

6.6 Index Strategy

Table	Index	Purpose
support_tickets	idx_support_tickets_status	Queue filtering
support_tickets	idx_support_tickets_customer_id	Customer lookup
ticket_messages	idx_ticket_messages_ticket_id	Thread loading
customers	idx_customers_email	Email lookup
customers	idx_customers_shopify_id	Webhook matching
orders	idx_orders_customer_recent	Order history (customer_id, created_at DESC)
shipments	idx_shipments_order_id	Shipment lookup
shipments	idx_shipments_tracking	Tracking lookup
courier_events	idx_courier_events_shipment_recent	Event history
address_changes	idx_address_changes_unsynced	Sync monitoring

---

7. Integration Architecture

7.1 External Systems

System	Integration Method	Data Flow
Shopify	Webhooks → Make.com → Supabase	Orders, customers, refunds
Seal Subscriptions	Webhooks → Make.com → Supabase	Subscription events
DPD (via 3PL)	Webhooks → Make.com → Supabase	Tracking events
Customer.io	Edge Function → Transactional API	Support emails (v1.4)
Gmail	Make.com watch → Supabase	Inbound emails + Message-ID
Anthropic Claude	Edge Function → Claude API	AI classification

7.2 Make.com Scenarios

Scenario	Trigger	Purpose
CS-01: Email Triage	Gmail inbox	Process inbound emails, capture Message-ID
Address Update Sync	Supabase webhook	Sync address to Shopify + Seal
Courier Watchdog	Scheduled (15 min)	Check for delivery exceptions
Order Ingestion	Shopify webhook	Process new orders

7.3 Edge Functions

Function	Trigger	Purpose
cs-agent-triage	Make.com HTTP	AI classification + draft
send-support-email	Ops Portal / CS-03	Send emails with threading (v1.4)
sync-address-change	Database webhook	Address sync orchestration

7.4 Email Threading Flow (v1.4)

┌─────────────────────────────────────────────────────────────────────────────────┐
│                           EMAIL THREADING FLOW                              │
├─────────────────────────────────────────────────────────────────────────────────┤
│                                                                             │
│  INBOUND (Customer → Us)                                                    │
│  ──────────────────────                                                     │
│  1. Gmail receives email with Message-ID header                             │
│  2. Make.com captures Message-ID from Gmail module                          │
│  3. cs-agent-triage stores email_message_id on ticket                       │
│  4. Email body stored in ticket_messages (direction: inbound)               │
│                                                                             │
│  OUTBOUND (Us → Customer)                                                   │
│  ────────────────────────                                                   │
│  1. Agent clicks "Send Response" in Ops Portal                              │
│  2. send-support-email Edge Function called                                 │
│  3. Function fetches email_message_id from ticket                           │
│  4. Builds headers: In-Reply-To + References                                │
│  5. Sends via Customer.io Transactional API with headers                    │
│  6. Response stored in ticket_messages (direction: outbound)                │
│  7. Customer sees threaded conversation in their inbox                      │
│                                                                             │
└─────────────────────────────────────────────────────────────────────────────────┘

---

8. Performance & Scale Requirements

8.1 Response Time Targets

Operation	Target	Current
Portal page load	<500ms	✅ Achieved
Support queue load	<200ms	✅ Achieved
Customer context load	<100ms	✅ Achieved
Conversation thread load	<100ms	✅ Achieved (v1.4)
AI classification	<3 seconds	✅ Achieved
Email send	<1 second	✅ Achieved

8.2 Scale Targets

Metric	Current	Phase A (500)	Phase B (10k)	Phase C (100k)
Daily tickets	0	~5	~50	~500
Concurrent agents	1	1	2-3	5-10
Portal DAU	0	~50	~1,000	~10,000
Database size	<100MB	<500MB	<5GB	<50GB

8.3 Query Performance Requirements

Query	Target	Index Required
Customer lookup by email	<10ms	✅ idx_customers_email
Customer full context	<50ms	✅ Composite view
Active shipments for customer	<10ms	✅ idx_shipments_order_id
Courier events for shipment	<10ms	✅ idx_courier_events_shipment_recent
Support queue (open tickets)	<20ms	✅ idx_support_tickets_status
Order history (last 10)	<20ms	✅ idx_orders_customer_recent
Address changes (unsynced)	<10ms	✅ idx_address_changes_unsynced
Ticket thread messages	<10ms	✅ idx_ticket_messages_ticket_id

---

9. Implementation Roadmap

9.1 Phase A: Foundation (Current)

Step	Component	SOP	Status
1	AI Email Triage	CS-01 v2.1	✅ Complete
2	Support Workstation	CS-01 v2.1	✅ Complete
3	Address Editing	CS-00 v1.1	✅ Complete
4	Live Chat	CS-02 v1.4	✅ Complete
5	Shipment Context Panel	CS-01 v2.1	✅ Complete
6	Unified Review Workflow	CS-01 v2.1	✅ Complete
7	Draft Usage Tracking	CS-01 v2.1	✅ Complete
8	Policy Gate Display	CS-01 v2.1	✅ Complete
9	Email Threading	CS-03 v1.5	✅ Complete (v1.4)
10	Conversation History	CS-00 v1.4	✅ Complete (v1.4)

9.2 Phase B: Autonomous Operations (Not Yet Built)

Step	Component	Status	Dependency
11	Autonomous Mode Switch	ðŸâ€µ Planned	Shadow mode validation
12	Queue Monitoring Alerts	ðŸâ€µ Planned	None
13	AI Accuracy Dashboard	ðŸâ€µ Planned	draft_usage data
14	~~Auto-refund/replacement~~	✅ Complete	See SOP CS-04 v1.2

9.3 Phase C: Scale (Post-Seed)

Step	Component	Status
15	Multi-Agent Queue Management	ðŸâ€µ Planned
16	Proactive Delay Notifications	ðŸâ€µ Planned
17	AI Chat Auto-Resolution	ðŸâ€µ Planned

---

10. Success Metrics

10.1 Contact Prevention

Metric	Current	Target	Measurement
Self-serve rate	~70%	90%	Portal actions / (Portal + Contacts)
Contacts per 1k customers	Unknown	<10/month	Total contacts / Active customers

10.2 Resolution Efficiency

Metric	Current	Target	Measurement
First response time	Unknown	<15 min	Ticket created → First response
Resolution time	Unknown	<2 hours	Ticket created → Resolved
One-touch resolution	Unknown	>80%	Resolved without customer reply
AI draft acceptance	Unknown	>70%	AI draft used vs. rewritten

10.3 Customer Satisfaction

Metric	Current	Target	Measurement
CSAT (post-resolution)	Unknown	>4.5/5	Post-resolution survey
NPS (quarterly)	Unknown	>50	Quarterly survey

10.4 Current Tracking (v1.4)

Metric	Source	Status
Tickets by category	ai_category	✅ Available
Policy gate triggers	policy_gate_code	✅ Available
Confidence scores	ai_confidence	✅ Available
Draft acceptance	draft_usage	✅ Available
Resolution time	created_at → resolved_at	✅ Available
Thread depth	COUNT(ticket_messages)	✅ Available (v1.4)
Threading enabled	email_message_id IS NOT NULL	✅ Available (v1.4)

---

11. Current System State (Shadow Mode)

11.1 What's Working

Email Processing: 1. ✅ Emails to hello@protocolraw.co.uk trigger Make.com scenario 2. ✅ Gmail Message-ID captured for threading (v1.4) 3. ✅ Customer lookup enriches context (subscription, orders, history) 4. ✅ Policy gates catch health/safety/legal instantly (zero AI cost) 5. ✅ Claude Sonnet classifies and drafts responses 6. ✅ Tickets created with full AI analysis 7. ✅ Slack notification for escalations 8. ✅ Support Workstation shows unified queue

Human Review (Current State): 1. ✅ Operator opens Support Workstation 2. ✅ Sees ticket with: message, context, AI draft, confidence, policy gate info 3. ✅ Views full conversation thread (v1.4) 4. ✅ Edits draft and subject line if needed 5. ✅ Sends response with threading headers (v1.4) 6. ✅ Response logged to ticket_messages (v1.4) 7. ✅ System logs draft_usage for accuracy analytics

Live Chat: 1. ✅ Chat widget on website 2. ✅ AI handles initial conversation 3. ✅ Escalates to Support Workstation when needed 4. ✅ Full transcript available in Ops Portal

Customer Self-Service: 1. ✅ Portal access (subscription management) 2. ✅ Skip delivery 3. ✅ Pause/resume subscription 4. ✅ Change box size 5. ✅ Update delivery address 6. ✅ View order history 7. ✅ Access batch proof pages

11.2 What's NOT Working Yet

Autonomous Responses: - ❌ All emails require human review (shadow mode) - ❌ No auto-send even for high-confidence classifications - ❌ No queue monitoring alerts

Actions: - ❌ No automated refunds via Ops Portal - ❌ No automated replacement orders (button exists but not wired)

Analytics: - ❌ No Metabase dashboard for AI accuracy - ❌ No alerting on queue depth or response time

---

12. Roadmap to Autonomous Mode

12.1 Prerequisites

Before enabling autonomous email responses:

Requirement	Status	Notes
Shadow mode running	✅ Complete	Currently processing emails
Email threading working	✅ Complete	v1.4
Conversation history tracking	✅ Complete	v1.4
Draft acceptance >70%	⏳ Measuring	Need 2-4 weeks data
Email delivery verified	✅ Tested	Customer.io working
Queue alerts in place	❌ Not built	Slack alert on queue>10
Confidence threshold defined	❌ Not decided	Recommend 90%

12.2 Autonomous Mode Design

When ready, the system will:

1. Auto-send if:
2. No policy gate triggered
3. Confidence ≥ 90%

4. Category not in mandatory_review list

5. Require review if:

6. Policy gate triggered
7. Confidence < 90%

8. Category = 'quality', 'refund', 'complaint'

9. Track separately:

10. response_method = 'autonomous' vs 'workstation'
11. Compare accuracy between modes

---

Appendix A: Related Documentation

Document	Version	Purpose
SOP CS-01	v2.1	AI Customer Service Triage
SOP CS-02	v1.4	Live Chat System
SOP CS-03	v1.5	Autonomous Support Agent
SOP AI-KB-01	v1.2	AI Knowledge Base management
SOP 02	v3.0	Courier Watchdog
Ops Portal Documentation	v3.5	Support Workstation reference
Customer Portal Documentation	v2.5	Self-service reference

---

Appendix B: Glossary

Term	Definition
Contact	Any customer-initiated communication (email, chat, phone)
Self-serve	Customer resolves need without human assistance
AI-resolve	AI handles contact without human review
AI-assist	AI drafts response, human reviews before sending
Ticket	Support request record in system
Thread	Full conversation history for a ticket (v1.4)
Shipment	Physical delivery record with tracking
Exception	Courier delivery issue requiring notification
Address sync	Propagation of address change to Shopify + Seal
Policy gate	Code-based rule that bypasses AI for specific conditions
Shadow mode	AI drafts responses but human reviews all before sending
Autonomous mode	AI sends responses directly for high-confidence classifications
Email threading	Using In-Reply-To/References headers to group emails (v1.4)

---

Appendix C: Operational Queries

Find Unresolved Tickets by Age

SELECT 
  t.id,
  t.subject,
  c.email,
  d.ai_category,
  d.ai_confidence,
  EXTRACT(EPOCH FROM (NOW() - t.created_at))/3600 as hours_open
FROM raw_ops.support_tickets t
JOIN raw_ops.customers c ON t.customer_id = c.id
LEFT JOIN raw_ops.cs_agent_decisions d ON t.id = d.ticket_id
WHERE t.status NOT IN ('resolved', 'closed')
ORDER BY t.created_at;

Draft Usage Analytics

SELECT 
  draft_usage,
  COUNT(*) as count,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER(), 1) as percentage
FROM raw_ops.support_tickets
WHERE draft_usage IS NOT NULL
  AND created_at > NOW() - INTERVAL '7 days'
GROUP BY draft_usage
ORDER BY count DESC;

Conversation Thread Depth

SELECT 
  t.id,
  t.subject,
  COUNT(tm.id) as message_count,
  MIN(tm.created_at) as first_message,
  MAX(tm.created_at) as last_message
FROM raw_ops.support_tickets t
JOIN raw_ops.ticket_messages tm ON t.id = tm.ticket_id
GROUP BY t.id, t.subject
ORDER BY message_count DESC
LIMIT 20;

---

Appendix D: Address Change Technical Reference

Edge Function

Name: sync-address-change
Trigger: Database webhook on INSERT to raw_ops.address_changes
Webhook URL: https://hook.eu2.make.com/yxpdv3l9gvy433tem2tmxds2ssek53t2

Payload:

{
  "change_id": "uuid",
  "customer_id": "uuid",
  "shopify_customer_id": "string",
  "seal_subscription_id": "string",
  "new_address": {
    "address1": "string",
    "address2": "string | null",
    "city": "string",
    "region": "string | null",
    "postcode": "string",
    "country_code": "string"
  },
  "source": "ops_portal | customer_portal"
}

Sync Status Monitoring

-- Find unsynced address changes
SELECT 
  ac.id,
  ac.customer_id,
  c.email,
  ac.source,
  ac.shopify_synced,
  ac.seal_synced,
  ac.created_at
FROM raw_ops.address_changes ac
JOIN raw_ops.customers c ON c.id = ac.customer_id
WHERE ac.shopify_synced = FALSE OR ac.seal_synced = FALSE
ORDER BY ac.created_at DESC;

Manual Sync Recovery

If sync fails, manually trigger via Make.com webhook:

curl -X POST https://hook.eu2.make.com/yxpdv3l9gvy433tem2tmxds2ssek53t2 \
  -H "Content-Type: application/json" \
  -d '{
    "change_id": "<uuid>",
    "customer_id": "<uuid>",
    "shopify_customer_id": "<shopify_id>",
    "seal_subscription_id": "<seal_id>",
    "new_address": {
      "address1": "123 New Street",
      "city": "London",
      "postcode": "SW1A 1AA",
      "country_code": "GB"
    }
  }'

---

Appendix E: Version History

Version	Date	Author	Changes
1.0	2026-01-13	Protocol Raw	Initial master design document
1.1	2026-01-13	Protocol Raw	Address editing complete (Step 3)
1.2	2026-01-19	Protocol Raw	Shadow mode complete: unified workflow, draft_usage tracking, policy gate display, removed Agent Review tab
1.3	2026-01-19	Protocol Raw	Merged v1.1 + v1.2: restored all comprehensive design documentation while incorporating v1.2 status updates
1.4	2026-01-20	Protocol Raw	Email threading, [Support] prefix, conversation history, unified send-support-email Edge Function
1.5	2026-01-20	Protocol Raw	Restored v1.3 content dropped in v1.4: Track Delivery spec, Proactive Delay Notifications, Resolution Paths, Customer Context sections, Query Performance, Address Change Reference

---

Document Owner: Protocol Raw Operations
Last Reviewed: 2026-01-20
Next Review: 2026-02-20
Version: 1.6
Status: ðŸâ€µ Master Design Document

---

End of SOP CS-00 v1.5

---

What's New in v1.6

Phase A Foundation Complete ✅

All Phase A components are now implemented and production-ready:

Step	Component	Status	Reference
1	AI Email Triage	✅ Complete	SOP CS-01 v2.1
2	Support Workstation Base	✅ Complete	Ops Portal v3.6
3	Address Editing	✅ Complete	v1.1
4	Live Chat	✅ Complete	SOP CS-02 v1.8
5	Shipment Context Panel	✅ Complete	v1.6 - See Appendix F
6	Refund/Replacement Actions	✅ Complete	SOP CS-04 v1.2

v1.6 New Features:

• ✅ Shipment Context Panel — Real-time shipment tracking in Support Workstation
• get_customer_active_shipment RPC function
• get_customer_shipments RPC function
• v_customer_active_shipment view
• Courier tracking URLs (DPD, Royal Mail, Evri)

• Exception highlighting and event history

• ✅ Refund System Complete — Full refund processing from Ops Portal

• References SOP CS-04 v1.2 for complete documentation
• Native Supabase architecture (trigger → Edge Function)
• Automatic Shopify sync
• Monitoring and health checks

---

Appendix F: Shipment Context Technical Reference (v1.6)

Database View

Name: raw_ops.v_customer_active_shipment
Purpose: Returns active shipments with latest courier event for customer context display

RPC Functions

get_customer_active_shipment(p_customer_id UUID)

Returns the active shipment for a customer with full event history as JSONB.

Column	Type	Description
shipment_id	UUID	Shipment primary key
order_id	UUID	Related order
shopify_order_id	TEXT	Shopify order reference
tracking_no	TEXT	Courier tracking number
courier	TEXT	DPD, Royal Mail, Evri
service	TEXT	Service level
shipment_status	TEXT	Current status
dispatched_at	TIMESTAMPTZ	When dispatched
delivered_at	TIMESTAMPTZ	When delivered (null if active)
latest_event_*	TEXT/TIMESTAMPTZ	Most recent courier event
courier_event_history	JSONB	Array of all events

get_customer_shipments(p_customer_id UUID)

Returns last 10 shipments for a customer with event history.

Courier Tracking URLs

Courier	URL Pattern
DPD	https://www.dpd.co.uk/tracking/quicktrack?search={tracking_no}
Royal Mail	https://www.royalmail.com/track-your-item#/tracking-results/{tracking_no}
Evri	https://www.evri.com/track/parcel/{tracking_no}

Ops Portal Integration

Functions in support.js: - loadShipmentContext(customerId) - Fetch and render shipment panel - renderShipmentPanel(shipment) - Build HTML for shipment display - getTrackingUrl(courier, trackingNo) - Generate courier-specific tracking URL

UI Features: - Auto-loads when customer has active shipment - Copy tracking number button - Track on courier website button - Exception highlighting (amber background) - Expandable event history

---

Appendix G: Refund System Reference (v1.6)

Full documentation: SOP CS-04 v1.2

Quick Reference

Component	Location	Status
Database table	raw_ops.refunds	✅ Production
Create RPC	public.create_refund	✅ Production
Orders RPC	public.get_customer_orders	✅ Production
Edge Function	process-refund	✅ Production
Database trigger	trg_process_refund	✅ Production
Monitoring	fn_check_refund_health_v2()	✅ Production
Customer notification	Customer.io templates	🔶 Pending auth fix

Refund Types

Type	Shopify Action	Use Case
full	Create full refund	Complete order refund
partial	Create partial refund	Partial compensation
replacement	None (logged only)	Re-ship order
goodwill	None (logged only)	Customer retention credit

Processing Flow

Agent clicks "💰 Refund" in Ops Portal
    ↓
create_refund() RPC → INSERT into raw_ops.refunds (status='pending')
    ↓
Database trigger → pg_net → Edge Function (process-refund)
    ↓
Edge Function: Atomic claim → Shopify API → Update status
    ↓
ops_events logging (refund_processed / refund_failed)

---

Appendix H: Version History (Updated v1.6)

Version	Date	Changes
1.0	2026-01-13	Initial master design document
1.1	2026-01-13	Address editing complete (Step 3)
1.2	2026-01-19	Shadow mode complete
1.3	2026-01-19	Merged v1.1 + v1.2
1.4	2026-01-20	Email threading, conversation history
1.5	2026-01-20	Restored content dropped in v1.4
1.6	2026-02-06	Phase A complete: Shipment Context Panel database (get_customer_active_shipment, get_customer_shipments RPCs), Refund System complete (SOP CS-04 v1.2)

---

Document Owner: Protocol Raw Operations
Last Reviewed: 2026-02-06
Next Review: 2026-03-06
Version: 1.6
Status: 🔵 Master Design Document — Phase A Complete

---

End of SOP CS-00 v1.6