SOP-TEST-01: Testimonials System v1.0¶

Status: Production Ready (Phase 1)
Last Updated: 2026-02-07
Owner: Protocol Raw Operations

Overview¶

Collect, approve, and display customer testimonials with live tenure data. Direct testimonials with full attribution, no third-party review platforms.

Philosophy: Protocol Raw's differentiator is verifiable proof. Third-party review platforms suggest the brand needs external validation because internal evidence isn't sufficient. Testimonials display alongside batch test results - the context does the credibility work.

Workflow¶

1. Customer sends positive feedback (Week 2 check-in reply, support email)
       ↓
2. Support agent clicks "â Flag Testimonial" in ticket panel
       ↓
3. Testimonial appears in Ops Portal → Testimonials tab (status: flagged)
       ↓
4. Review and edit proposed quote
       ↓
5. Send permission request email (status: requested)
       ↓
6. Customer replies "yes" → Approve (status: approved)
       ↓
7. Testimonial displays on website with live data

Database Schema¶

Table: `raw_ops.testimonials`¶

CREATE TABLE raw_ops.testimonials (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id UUID NOT NULL REFERENCES raw_ops.customers(id),

  -- Source tracking
  source_ticket_id UUID REFERENCES raw_ops.support_tickets(id),
  source_type TEXT NOT NULL DEFAULT 'email_reply' 
    CHECK (source_type IN ('email_reply', 'chat', 'manual')),
  original_text TEXT NOT NULL,

  -- Approved display version
  approved_quote TEXT,
  display_city TEXT,

  -- Approval workflow
  status TEXT NOT NULL DEFAULT 'flagged' 
    CHECK (status IN ('flagged', 'requested', 'approved', 'declined', 'withdrawn')),
  flagged_at TIMESTAMPTZ DEFAULT NOW(),
  flagged_by TEXT,

  -- Permission request
  request_sent_at TIMESTAMPTZ,
  request_email_delivery_id TEXT,

  -- Approval
  approved_at TIMESTAMPTZ,
  approval_method TEXT CHECK (approval_method IN ('email_reply', 'portal')),

  -- Display control
  is_featured BOOLEAN DEFAULT FALSE,
  display_order INTEGER,

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_testimonials_status ON raw_ops.testimonials(status);
CREATE INDEX idx_testimonials_customer ON raw_ops.testimonials(customer_id);
CREATE INDEX idx_testimonials_featured ON raw_ops.testimonials(is_featured) 
  WHERE status = 'approved' AND is_featured = TRUE;

View: `public.v_testimonials_display`¶

Returns approved testimonials with live customer data:

CREATE OR REPLACE VIEW public.v_testimonials_display AS
SELECT 
  t.id,
  t.approved_quote,
  c.first_name,
  t.display_city,
  TO_CHAR(c.created_at, 'Month YYYY') AS customer_since,
  COUNT(o.id) AS order_count,
  t.is_featured,
  t.display_order
FROM raw_ops.testimonials t
JOIN raw_ops.customers c ON c.id = t.customer_id
LEFT JOIN raw_ops.orders o ON o.customer_id = c.id 
  AND o.export_state = 'EXPORTED'
WHERE t.status = 'approved'
GROUP BY t.id, t.approved_quote, c.first_name, t.display_city, 
         c.created_at, t.is_featured, t.display_order
ORDER BY t.is_featured DESC, t.display_order ASC, t.approved_at DESC;

GRANT SELECT ON public.v_testimonials_display TO anon;

Key feature: order_count updates automatically as customers continue ordering. A testimonial quoted at "8 deliveries" will show "15 deliveries" after 7 more orders without manual maintenance.

RPC Functions¶

`public.flag_testimonial`¶

Called from Support Workstation when flagging positive feedback.

flag_testimonial(
  p_customer_id UUID,
  p_original_text TEXT,
  p_source_ticket_id UUID DEFAULT NULL,
  p_source_type TEXT DEFAULT 'email_reply',
  p_flagged_by TEXT DEFAULT 'system'
) RETURNS UUID

`public.get_testimonials_queue`¶

Returns flagged and requested testimonials for ops portal.

get_testimonials_queue() RETURNS TABLE (
  id UUID,
  customer_name TEXT,
  customer_email TEXT,
  customer_city TEXT,
  customer_since TEXT,
  order_count BIGINT,
  original_text TEXT,
  approved_quote TEXT,
  status TEXT,
  flagged_at TIMESTAMPTZ,
  flagged_by TEXT,
  request_sent_at TIMESTAMPTZ
)

`public.update_testimonial`¶

Updates testimonial status, quote, or display settings.

update_testimonial(
  p_testimonial_id UUID,
  p_status TEXT DEFAULT NULL,
  p_approved_quote TEXT DEFAULT NULL,
  p_display_city TEXT DEFAULT NULL,
  p_is_featured BOOLEAN DEFAULT NULL
) RETURNS BOOLEAN

Ops Portal Integration¶

Support Workstation¶

Location: Ticket response panel, action buttons row

Button: "â Flag Testimonial"

Behaviour: - Extracts first inbound message from conversation thread - Calls flag_testimonial() with customer_id and message text - Shows confirmation alert

Testimonials Tab¶

Location: Ops Portal → â Testimonials

Features: - Queue of flagged/requested testimonials - Customer context (name, city, tenure, order count) - Original message display - Editable proposed quote field - "Send Request" button (triggers permission email) - "Approve" button (manual approval) - "Decline" button (removes from pipeline) - Badge showing pending count

Permission Request Email¶

Template ID: testimonial_request (Customer.io)

Subject: Thank you

Body:

Hi {{customer.first_name}},

This really made me smile - thank you for taking the time to write.

If you're happy for us to share it on our site, here's how it would appear:

---

"{{proposed_quote}}"

{{customer.first_name}}, {{customer.city}} · Customer since {{customer_since}} · {{order_count}} deliveries

---

Reply "yes" if that works, or let me know if you'd prefer different wording.

{{persona}}
Protocol Raw

Status: Template not yet created in Customer.io

Website Display¶

Endpoint: Query public.v_testimonials_display directly or via Edge Function

Response format:

[
  {
    "approved_quote": "First raw food I felt comfortable feeding with kids in the house.",
    "first_name": "Sarah",
    "display_city": "Bristol",
    "customer_since": "March 2024",
    "order_count": 12,
    "is_featured": true
  }
]

Display format:

"First raw food I felt comfortable feeding with kids in the house."

Sarah, Bristol · Customer since March 2024 · 12 deliveries

Google Reviews (Passive)¶

Separate from the testimonials system:

Create Google Business Profile for Protocol Raw
Verify as service-area business
Do not actively promote or request reviews
Let reviews accrue organically from customers who seek it out
No widgets, no emphasis, no review request campaigns

Outstanding Items¶

Item	Status	Priority
Make.com scenario for permission email	Not built	Low (manual until volume)
Customer.io `testimonial_request` template	Not built	Low
Website display component	Not built	Low
Inbound reply parsing for auto-approval	Not built	Future

File Locations¶

Component	Location
Database table	`raw_ops.testimonials`
Display view	`public.v_testimonials_display`
RPC functions	`public.flag_testimonial`, `public.get_testimonials_queue`, `public.update_testimonial`
Ops Portal module	`js/modules/testimonials.js`
Support integration	`js/modules/support.js` → `flagAsTestimonial()`
HTML tab	`index.html` → `#testimonialsContent`

Version History¶

Version	Date	Changes
1.0	2026-02-07	Initial implementation - database, ops portal module, support integration

Document Owner: Protocol Raw Operations
Next Review: When first testimonial is collected