SOP CS-05: CSAT Survey System (v1.0)¶

Status: Production Ready
Last Updated: 2026-02-06
Owner: Protocol Raw Operations
Platform: Supabase (pg_cron + Edge Functions) + Customer.io

Overview¶

Automated customer satisfaction survey system that sends feedback requests after support ticket resolution, collects one-click ratings, and stores responses for analytics.

Flow: Ticket Resolved → Trigger Schedules Survey → pg_cron Sends Email → Customer Clicks Rating → Response Recorded

Key Features: - 2-hour delay after resolution (time for customer to verify issue is fixed) - Operating hours enforcement (08:00-20:00 UK, 7 days a week) - One-click ratings (no forms to fill) - Signed tokens prevent tampering - 14-day expiry on rating links - Duplicate submission prevention

Architecture¶

System Flow Diagram¶

┌─────────────────────────────────────────────────────────────────────┐
│                         CSAT SURVEY FLOW                            │
├─────────────────────────────────────────────────────────────────────┤
│                                                                     │
│  SCHEDULING (Automatic)                                             │
│  ─────────────────────                                              │
│  1. Ticket status changes to 'resolved'                             │
│  2. Database trigger fires (trg_schedule_csat_survey)               │
│  3. Sets csat_survey_scheduled_for = resolved_at + 2 hours          │
│                                                                     │
│  SENDING (Every 15 minutes)                                         │
│  ────────────────────────                                           │
│  1. pg_cron triggers csat-survey-sender Edge Function               │
│  2. Function checks operating hours (08:00-20:00 UK)                │
│  3. Queries tickets where scheduled_for <= NOW() AND sent_at IS NULL│
│  4. For each: generates token → sends via Customer.io → marks sent  │
│                                                                     │
│  SUBMISSION (Customer Action)                                       │
│  ─────────────────────────────                                      │
│  1. Customer clicks rating button (1-5) in email                    │
│  2. csat-submit Edge Function validates token                       │
│  3. Records rating to csat_responses table                          │
│  4. Displays branded thank you page                                 │
│                                                                     │
└─────────────────────────────────────────────────────────────────────┘

Components¶

Component	Type	Purpose
`trg_schedule_csat_survey`	Database Trigger	Schedules survey on ticket resolution
`fn_get_csat_surveys_ready_to_send`	PostgreSQL Function	Finds due surveys (respects operating hours)
`fn_generate_csat_token`	PostgreSQL Function	Creates signed token for secure submission
`fn_submit_csat_response`	PostgreSQL Function	Validates token and records rating
`fn_mark_csat_survey_sent`	PostgreSQL Function	Marks survey as sent
`csat-survey-sender`	Edge Function	Sends emails via Customer.io
`csat-submit`	Edge Function	Receives rating clicks, shows thank you page
`csat_survey` (ID: 8)	Customer.io Template	Survey email with rating buttons
`csat-survey-sender`	pg_cron Job	Runs every 15 minutes

Database Schema¶

Columns on `support_tickets`¶

Column	Type	Purpose
`csat_survey_scheduled_for`	TIMESTAMPTZ	When survey should be sent (set by trigger)
`csat_survey_sent_at`	TIMESTAMPTZ	When survey was actually sent
`csat_survey_token`	TEXT	Signed token for this ticket's survey

Table: `csat_responses`¶

CREATE TABLE raw_ops.csat_responses (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  ticket_id UUID NOT NULL REFERENCES raw_ops.support_tickets(id) ON DELETE CASCADE,
  customer_id UUID NOT NULL REFERENCES raw_ops.customers(id),
  rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
  comment TEXT,
  submitted_at TIMESTAMPTZ DEFAULT NOW(),
  submitted_from TEXT DEFAULT 'email_link',
  UNIQUE(ticket_id)  -- One response per ticket
);

Indexes¶

-- Find surveys ready to send
CREATE INDEX idx_support_tickets_csat_ready 
  ON raw_ops.support_tickets(csat_survey_scheduled_for)
  WHERE csat_survey_scheduled_for IS NOT NULL 
    AND csat_survey_sent_at IS NULL;

-- Analytics queries
CREATE INDEX idx_csat_responses_submitted_at ON raw_ops.csat_responses(submitted_at);
CREATE INDEX idx_csat_responses_rating ON raw_ops.csat_responses(rating);

Trigger Logic¶

On Ticket Resolution¶

-- When status changes TO 'resolved'
NEW.csat_survey_scheduled_for := COALESCE(NEW.resolved_at, NOW()) + INTERVAL '2 hours';

On Ticket Re-open¶

-- When status changes FROM 'resolved' to something else (and survey not yet sent)
NEW.csat_survey_scheduled_for := NULL;

Operating Hours¶

Parameter	Value
Days	Monday - Sunday (7 days)
Hours	08:00 - 20:00 UK time
Overnight handling	Rescheduled to 08:00-08:30 next day

Timing Examples¶

Resolved At	Scheduled For	Sent At
14:00 Mon	16:00 Mon	~16:00-16:15 Mon
18:05 Mon	20:05 Mon	~08:00-08:30 Tue (outside hours)
23:00 Sat	01:00 Sun	~08:00-08:30 Sun (outside hours)
05:00 Sun	07:00 Sun	~08:00-08:30 Sun (outside hours)

Token Security¶

Token Format¶

{ticket_id}.{expires_epoch}.{signature_16chars}

Example:
a1b2c3d4-e5f6-7890-abcd-ef1234567890.1771586762.e340ff71c5a10f43

Security Features¶

Feature	Implementation
Ticket identification	UUID in token
Expiry	14 days from generation
Tampering prevention	HMAC-SHA256 signature
Replay prevention	One response per ticket (UNIQUE constraint)

Validation Errors¶

Error Code	User Message
`invalid_token_format`	"This feedback link doesn't appear to be valid."
`token_expired`	"This feedback link has expired."
`invalid_signature`	"This feedback link doesn't appear to be valid."
`ticket_not_found`	"We couldn't find the support request this feedback relates to."
`already_submitted`	"We've already received your feedback for this request."

Email Template¶

Customer.io Configuration¶

Setting	Value
Template ID	8
Transactional Message ID	`csat_survey`
From	`Protocol Raw <support@protocolraw.co.uk>`
Reply-To	`support@protocolraw.co.uk`
Subject	"How did we do?"
Preview Text	"One click to let us know."

Template Variables¶

Variable	Description
`{{message_data.first_name}}`	Customer's first name
`{{message_data.ticket_subject}}`	Original ticket subject
`{{message_data.resolved_by}}`	Persona who resolved (Sophie/Tom/Lucy)
`{{message_data.url_1}}` - `{{message_data.url_5}}`	One-click rating URLs

Rating Button Colours¶

Rating	Colour	Hex
1	Red	#DC6B6B
2	Orange	#E8A86B
3	Yellow	#E8D86B
4	Light Green	#A8C686
5	Green	#6BAF6B

Edge Functions¶

csat-survey-sender¶

Trigger: pg_cron every 15 minutes
JWT Verification: Enabled (uses anon key)

Process: 1. Call fn_get_csat_surveys_ready_to_send(50) 2. If outside operating hours, function returns empty set 3. For each candidate: - Generate token via fn_generate_csat_token - Build rating URLs - Send via Customer.io Transactional API (template ID: 8) - Mark sent via fn_mark_csat_survey_sent 4. Return summary (sent count, failed count, details)

Response Example:

{
  "success": true,
  "sent": 3,
  "failed": 0,
  "results": [
    { "ticket_id": "...", "success": true, "delivery_id": "..." }
  ]
}

csat-submit¶

Trigger: Customer clicks rating link
JWT Verification: Disabled (public access)
URL Pattern: /functions/v1/csat-submit?token=...&rating=5

Process: 1. Parse token and rating from query params 2. Call fn_submit_csat_response 3. Render HTML thank you page

Thank You Messages:

Rating	Message
1	"We're sorry we didn't meet your expectations. We'll work to do better."
2	"Thank you for letting us know. We'll use this to improve."
3	"Thank you for your feedback."
4	"Great to hear! Thank you for your feedback."
5	"Wonderful! We're glad we could help."

pg_cron Job¶

SELECT cron.schedule(
  'csat-survey-sender',
  '*/15 * * * *',
  $$
  SELECT net.http_post(
    url := 'https://znfjpllsiuyezqlneqzr.supabase.co/functions/v1/csat-survey-sender',
    headers := '{"Content-Type": "application/json", "apikey": "YOUR_ANON_KEY"}'::jsonb,
    body := '{}'::jsonb
  );
  $$
);

Job Management¶

-- View job status
SELECT jobid, jobname, schedule, active
FROM cron.job 
WHERE jobname = 'csat-survey-sender';

-- Disable temporarily
UPDATE cron.job SET active = false WHERE jobname = 'csat-survey-sender';

-- Re-enable
UPDATE cron.job SET active = true WHERE jobname = 'csat-survey-sender';

-- View recent runs
SELECT * FROM cron.job_run_details 
WHERE jobid = (SELECT jobid FROM cron.job WHERE jobname = 'csat-survey-sender')
ORDER BY start_time DESC LIMIT 10;

Analytics Queries¶

Overall CSAT Score¶

SELECT 
  COUNT(*) as total_responses,
  ROUND(AVG(rating), 2) as avg_rating,
  ROUND(AVG(rating) * 20, 1) as csat_percentage
FROM raw_ops.csat_responses
WHERE submitted_at > NOW() - INTERVAL '30 days';

Rating Distribution¶

SELECT 
  rating,
  COUNT(*) as count,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) as percentage
FROM raw_ops.csat_responses
WHERE submitted_at > NOW() - INTERVAL '30 days'
GROUP BY rating
ORDER BY rating;

CSAT by Ticket Category¶

SELECT 
  t.ai_category,
  COUNT(c.id) as responses,
  ROUND(AVG(c.rating), 2) as avg_rating
FROM raw_ops.csat_responses c
JOIN raw_ops.support_tickets t ON c.ticket_id = t.id
WHERE c.submitted_at > NOW() - INTERVAL '30 days'
GROUP BY t.ai_category
ORDER BY avg_rating DESC;

Response Rate¶

SELECT
  COUNT(*) FILTER (WHERE csat_survey_sent_at IS NOT NULL) as surveys_sent,
  COUNT(c.id) as responses_received,
  ROUND(100.0 * COUNT(c.id) / NULLIF(COUNT(*) FILTER (WHERE csat_survey_sent_at IS NOT NULL), 0), 1) as response_rate_pct
FROM raw_ops.support_tickets t
LEFT JOIN raw_ops.csat_responses c ON t.id = c.ticket_id
WHERE t.resolved_at > NOW() - INTERVAL '30 days';

Low Ratings for Follow-up¶

SELECT 
  c.ticket_id,
  c.rating,
  c.comment,
  c.submitted_at,
  cu.email,
  cu.first_name,
  t.subject
FROM raw_ops.csat_responses c
JOIN raw_ops.support_tickets t ON c.ticket_id = t.id
JOIN raw_ops.customers cu ON c.customer_id = cu.id
WHERE c.rating <= 2
  AND c.submitted_at > NOW() - INTERVAL '7 days'
ORDER BY c.submitted_at DESC;

Troubleshooting¶

Surveys Not Sending¶

Check cron job is active:

SELECT active FROM cron.job WHERE jobname = 'csat-survey-sender';

Check operating hours:

SELECT EXTRACT(HOUR FROM NOW() AT TIME ZONE 'Europe/London') as uk_hour;
-- Must be between 8 and 19 inclusive

Check for eligible tickets:

SELECT * FROM public.fn_get_csat_surveys_ready_to_send(10);

Check Edge Function logs in Supabase Dashboard → Edge Functions → csat-survey-sender → Logs

Customer Reports Link Not Working¶

Token expired (>14 days) - cannot be fixed, survey window closed
Already submitted - check csat_responses table
Edge Function down - check Supabase status

Emails Going to Spam/Promotions¶

Common for HTML emails with colourful buttons
Consider A/B testing plainer design
Gmail categorisation improves with user interaction over time

Success Metrics¶

Metric	Target	Measurement
CSAT Score	>4.5/5	`AVG(rating)`
Response Rate	>20%	Responses / Surveys Sent
Survey Delivery	>99%	Sent / Scheduled
Low Rating Follow-up	100%	Manual process for ratings 1-2

Future Enhancements¶

Enhancement	Status	Notes
Slack alert on low ratings (1-2)	🔵 Planned	Immediate visibility for follow-up
Metabase CSAT dashboard	🔵 Planned	Visual tracking over time
Comment collection	🔵 Planned	Optional text field after rating
CSAT by agent persona	🔵 Planned	Does Sophie/Tom/Lucy affect ratings?

Version History¶

Version	Date	Changes
1.0	2026-02-06	Initial release

SOP CS-00: Customer Operations System (master reference)
SOP CS-01: AI Customer Service Triage
SOP CS-02: Live Chat System
SOP CS-03: AI Customer Service Agent
SOP-MON-01: Monitoring & Alerting Architecture