SOP-CS-04: Refund Resolution Operations v1.2¶

Status: ✅ Phase 1 Complete | ✅ Phase 2 Complete | 🔶 Phase 3 Pending (Customer.io Templates)
Created: 2026-02-02
Updated: 2026-02-03
Owner: Protocol Raw Operations
Classification: Customer Operations

Document Purpose¶

This SOP defines the refund resolution system for Protocol Raw, enabling agents to process refunds and replacements from the Ops Portal with automated Shopify sync and customer notification.

Design Philosophy: - Native Supabase Architecture: Database trigger → pg_net → Edge Function (no Make.com polling) - Instant Processing: Refunds trigger immediately, not on schedule - Idempotent Processing: Atomic claim pattern prevents duplicate refunds - Full Audit Trail: All actions logged to ops_events - Guaranteed Terminal State: Every refund reaches processed or failed, no orphans - Scale First: Architecture supports 100k+ customers

1. System Architecture¶

1.1 Process Flow¶

Agent clicks "💰 Refund" in Ops Portal
        ↓
    JavaScript calls create_refund() RPC
        ↓
    PostgreSQL inserts into raw_ops.refunds (status='pending')
        ↓
    Database trigger fires AFTER INSERT
        ↓
    pg_net calls Edge Function (process-refund) async
        ↓
    Edge Function:
        ├── 1. Atomic claim: SET status='processing' WHERE status='pending'
        ├── 2. Shopify Admin API (create refund) [if shopify_order_id exists]
        ├── 3. Customer.io Track API (send notification) [with single retry]
        └── 4. Update refunds table (status='processed'/'failed')
        ↓
    Log to ops_events (refund_processed / refund_failed)

1.2 Why This Architecture (Not Make.com)¶

Aspect	Make.com Approach	Native Supabase Approach
Latency	1-15 min (polling)	<3 seconds (trigger)
Cost at scale	£0.01+ per operation	Included in Supabase
Reliability	External dependency	Native infrastructure
Idempotency	Manual dedup needed	Atomic claim built-in
Audit trail	Scattered logs	Unified ops_events
Follows SOP-MON-01	âŒ	✅

2. Implementation Status¶

2.1 Phase 1: UI + Database ✅ Complete¶

Component	Status	Deployed
`raw_ops.refunds` table	✅	2026-02-02
`public.get_customer_orders()` RPC	✅	2026-02-02
`public.create_refund()` RPC	✅	2026-02-02
Ops Portal: Refund Modal	✅	2026-02-02
Ops Portal: Refund Button	✅	2026-02-02
JavaScript functions	✅	2026-02-02
app.js exports	✅	2026-02-02
Version	✅	v3.7 - Refund Actions

2.2 Phase 2: Automation ✅ Complete¶

Component	Status	Deployed
Edge Function `process-refund`	✅	2026-02-03
Database trigger `trg_process_refund`	✅	2026-02-03
JWT verification disabled (--no-verify-jwt)	✅	2026-02-03
ops_events logging from Edge Function	✅	2026-02-03
Monitoring view `v_refunds_pending`	✅	2026-02-03
Monitoring view `v_refund_daily_summary`	✅	2026-02-03
Health check `fn_check_refund_health_v2()`	✅	2026-02-03
pg_cron job `monitor-refund-health` (*/5 min)	✅	2026-02-03
`run-monitor` dispatch updated	✅	2026-02-03
Supabase secrets configured	✅	Pre-existing

2.3 Phase 3: Customer Notifications 🔶 Pending¶

Component	Status	Notes
Customer.io auth fix	🔶	Track API returns 401 — needs credential review
Customer.io template `refund_processed`	🔶	Email for refund confirmation
Customer.io template `replacement_order_created`	🔶	Email for replacement orders

3. Database Schema¶

3.1 refunds Table (Actual Deployed)¶

CREATE TABLE raw_ops.refunds (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  order_id UUID REFERENCES raw_ops.orders(id),
  customer_id UUID NOT NULL REFERENCES raw_ops.customers(id),
  support_ticket_id UUID REFERENCES raw_ops.support_tickets(id),

  -- Shopify sync
  shopify_refund_id TEXT,

  -- Refund details
  amount_gbp NUMERIC(10,2) NOT NULL DEFAULT 0,
  refund_type TEXT NOT NULL CHECK (refund_type IN ('full', 'partial', 'replacement')),
  reason TEXT NOT NULL,

  -- Processing
  processed_by TEXT,
  processed_at TIMESTAMPTZ,
  status TEXT NOT NULL DEFAULT 'pending' 
    CHECK (status IN ('pending', 'processing', 'processed', 'failed')),
  internal_note TEXT,

  -- Customer notification
  customer_notified BOOLEAN DEFAULT FALSE,

  -- Shopify sync tracking
  shopify_order_id TEXT,
  shopify_synced BOOLEAN DEFAULT FALSE,
  shopify_synced_at TIMESTAMPTZ,
  shopify_error TEXT,

  -- Notes
  notes TEXT
);

-- Indexes
CREATE INDEX idx_refunds_customer_id ON raw_ops.refunds(customer_id);
CREATE INDEX idx_refunds_order_id ON raw_ops.refunds(order_id);
CREATE INDEX idx_refunds_status ON raw_ops.refunds(status);
CREATE INDEX idx_refunds_created_at ON raw_ops.refunds(created_at DESC);

Key schema differences from v1.0 spec: - Status values: pending, processing, processed, failed (not completed) - No updated_at column - No completed_at column — uses processed_at instead - notes column separate from internal_note

3.2 RPC Functions¶

get_customer_orders¶

Returns orders for refund selection dropdown.

CREATE OR REPLACE FUNCTION public.get_customer_orders(
  p_customer_id UUID,
  p_limit INTEGER DEFAULT 20
)
RETURNS TABLE (
  order_id UUID,
  order_number TEXT,
  shopify_order_id TEXT,
  ordered_at TIMESTAMPTZ,
  status TEXT,
  total_gbp NUMERIC,
  is_subscription_renewal BOOLEAN
)

create_refund¶

Creates refund record and returns details for Edge Function.

CREATE OR REPLACE FUNCTION public.create_refund(
  p_order_id UUID DEFAULT NULL,
  p_customer_id UUID,
  p_amount_gbp NUMERIC DEFAULT 0,
  p_refund_type TEXT DEFAULT 'full',
  p_reason TEXT,
  p_processed_by TEXT DEFAULT 'ops_portal',
  p_internal_note TEXT DEFAULT NULL,
  p_support_ticket_id UUID DEFAULT NULL
)
RETURNS JSONB
-- Returns: { success, refund_id, shopify_order_id, customer_email }

4. Edge Function: process-refund¶

4.1 Purpose¶

Orchestrates the complete refund process with idempotent, guaranteed terminal state: 1. Atomic claim (prevent duplicate processing) 2. Create Shopify refund (if shopify_order_id exists) 3. Send Customer.io notification (with single retry) 4. Update refunds table with results 5. Log to ops_events

4.2 Trigger¶

Database trigger via pg_net (AFTER INSERT on raw_ops.refunds where status='pending').

4.3 Deployment¶

supabase functions deploy process-refund --no-verify-jwt

JWT verification is disabled. The idempotency layer (atomic claim with WHERE status='pending') is the real protection — external callers cannot cause duplicate refunds.

4.4 Idempotency Pattern¶

-- Atomic claim: only one instance can process a refund
UPDATE raw_ops.refunds
SET status = 'processing'
WHERE id = :refund_id AND status = 'pending'
RETURNING *;
-- If no rows returned → already claimed by another instance

4.5 Processing Logic¶

Atomic Claim: UPDATE ... SET status='processing' WHERE id=? AND status='pending' RETURNING *
If no rows returned → already processing, return success (idempotent)
Shopify Refund (if shopify_order_id exists and refund_type != 'replacement'):
Fetch order transactions via Admin API
Filter for status='success', sort by created_at DESC
Validate currency is GBP
Create refund via POST /admin/api/2024-01/orders/{id}/refunds.json
Store shopify_refund_id
Customer.io Notification:
Trigger refund_processed or replacement_order_created event
Uses Track API with Basic auth (Site ID : API Key)
Single retry on failure
Notification failure does NOT change refund status (intentional)
Terminal State:
Success: status='processed', processed_at=NOW(), shopify_synced=true
Failure: status='failed', shopify_error=<message>
Every code path reaches a terminal state — no orphaned processing records
Logging:
All events logged to raw_ops.ops_events via direct insert
Event kinds: refund_processed, refund_failed, refund_notification_failed

4.6 Environment Variables¶

Variable	Secret Name	Purpose
SUPABASE_URL	Auto-injected	Supabase project URL
SUPABASE_SERVICE_ROLE_KEY	Auto-injected	Service role for database updates
Shopify store domain	`SHOPIFY_STORE`	e.g., protocolraw.myshopify.com
Shopify admin token	`SHOPIFY_ACCESS_TOKEN`	Admin API access token
Customer.io site ID	`CUSTOMERIO_SITE_ID`	Customer.io site ID
Customer.io API key	`CUSTOMERIO_API_KEY`	Customer.io API key

4.7 Error Handling¶

Error Type	Behaviour
Missing env vars	Mark `failed` immediately with descriptive error
Shopify API error	Mark `failed`, store error in `shopify_error`
Customer.io error	Log to ops_events, keep status as `processed`
Duplicate processing	Return success (idempotent, no-op)
Unhandled exception	Catch-all marks `failed` with error message

5. Database Trigger¶

5.1 Trigger Function¶

CREATE OR REPLACE FUNCTION raw_ops.fn_trigger_process_refund()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_shopify_order_id TEXT;
  v_customer_email TEXT;
  v_customer_name TEXT;
BEGIN
  -- Only process pending refunds
  IF NEW.status != 'pending' THEN
    RETURN NEW;
  END IF;

  -- Look up shopify_order_id if order_id provided
  IF NEW.order_id IS NOT NULL THEN
    SELECT shopify_order_id INTO v_shopify_order_id
    FROM raw_ops.orders
    WHERE id = NEW.order_id;
  END IF;

  -- Look up customer details
  SELECT email, first_name INTO v_customer_email, v_customer_name
  FROM raw_ops.customers
  WHERE id = NEW.customer_id;

  -- Log trigger fired
  INSERT INTO raw_ops.ops_events (entity_type, entity_id, kind, message)
  VALUES ('REFUND', NEW.id, 'refund_trigger_fired',
    'Trigger fired - calling process-refund Edge Function');

  -- Call Edge Function via pg_net (async, non-blocking)
  PERFORM net.http_post(
    url := 'https://znfjpllsiuyezqlneqzr.supabase.co/functions/v1/process-refund',
    headers := '{"Content-Type": "application/json"}'::jsonb,
    body := jsonb_build_object(
      'refund_id', NEW.id,
      'customer_id', NEW.customer_id,
      'customer_email', COALESCE(v_customer_email, ''),
      'customer_name', COALESCE(v_customer_name, ''),
      'order_id', NEW.order_id,
      'shopify_order_id', COALESCE(v_shopify_order_id, NEW.shopify_order_id),
      'refund_type', NEW.refund_type,
      'amount_gbp', NEW.amount_gbp,
      'reason', NEW.reason
    )
  );

  RETURN NEW;
END;
$$;

5.2 Trigger Definition¶

CREATE TRIGGER trg_process_refund
  AFTER INSERT ON raw_ops.refunds
  FOR EACH ROW
  EXECUTE FUNCTION raw_ops.fn_trigger_process_refund();

Note: No authentication header in pg_net call. Idempotency layer is the protection.

6. Customer.io Email Templates¶

6.1 refund_processed¶

Status: 🔶 Pending (Customer.io auth needs fixing)

Trigger Event: refund_processed

Subject: Your refund has been processed

Data: - amount_formatted: £89.00 - refund_type: full/partial - reason: Product quality issue - customer_name: John Smith

6.2 replacement_order_created¶

Status: 🔶 Pending

Trigger Event: replacement_order_created

Subject: Your replacement order is on its way

Data: - refund_type: replacement - reason: Delivery problem - customer_name: John Smith

7. Monitoring¶

7.1 Monitoring View: v_refunds_pending¶

Operational attention queue showing refunds needing action, priority-sorted.

CREATE OR REPLACE VIEW raw_ops.v_refunds_pending AS
SELECT 
  r.id,
  r.created_at,
  r.customer_id,
  c.email AS customer_email,
  c.first_name AS customer_name,
  r.order_id,
  r.shopify_order_id,
  r.refund_type,
  r.amount_gbp,
  r.reason,
  r.status,
  r.shopify_synced,
  r.shopify_error,
  r.customer_notified,
  r.processed_by,
  r.processed_at,
  ROUND(EXTRACT(EPOCH FROM (NOW() - r.created_at)) / 60, 1) AS minutes_since_created
FROM raw_ops.refunds r
LEFT JOIN raw_ops.customers c ON c.id = r.customer_id
WHERE r.status IN ('pending', 'processing', 'failed')
   OR (r.status = 'processed' AND r.customer_notified = FALSE)
ORDER BY 
  CASE r.status
    WHEN 'failed' THEN 1
    WHEN 'processing' THEN 2
    WHEN 'pending' THEN 3
    ELSE 4
  END,
  r.created_at ASC;

7.2 Daily Summary View: v_refund_daily_summary¶

Metabase-ready view for refund trends dashboard.

CREATE OR REPLACE VIEW raw_ops.v_refund_daily_summary AS
SELECT 
  DATE(created_at) AS refund_date,
  COUNT(*) AS total_refunds,
  SUM(amount_gbp) AS total_amount_gbp,
  COUNT(*) FILTER (WHERE status = 'processed') AS processed,
  COUNT(*) FILTER (WHERE status = 'failed') AS failed,
  COUNT(*) FILTER (WHERE status = 'pending') AS pending,
  COUNT(*) FILTER (WHERE refund_type = 'full') AS full_refunds,
  COUNT(*) FILTER (WHERE refund_type = 'partial') AS partial_refunds,
  COUNT(*) FILTER (WHERE refund_type = 'replacement') AS replacements,
  COUNT(*) FILTER (WHERE customer_notified = FALSE AND status = 'processed') AS unnotified
FROM raw_ops.refunds
WHERE created_at > NOW() - INTERVAL '90 days'
GROUP BY DATE(created_at)
ORDER BY refund_date DESC;

7.3 Health Check Function: fn_check_refund_health_v2()¶

SOP-MON-01 compliant health check with monitoring_runs logging and alert_config return for ops-alerter.

Schedule: Every 5 minutes (*/5 * * * *)
pg_cron Job: monitor-refund-health
Channel: #ops-urgent (critical) / #ops-alerts (warning)

Thresholds:

Condition	Severity	Channel
Stuck pending > 5 min OR stuck processing > 10 min	critical	#ops-urgent
Failed > 3 in 24h	critical	#ops-urgent
Any failed in 24h OR unnotified customers	warning	#ops-alerts

Alert Fields: - â³ Stuck Pending (count) - 🔄 Stuck Processing (count) - âŒ Failed 24h (count) - 📧 Unnotified (count)

7.4 Manual Health Check Queries¶

-- Quick health check
SELECT * FROM raw_ops.fn_check_refund_health_v2();

-- Stuck refunds
SELECT * FROM raw_ops.v_refunds_pending
WHERE status IN ('pending', 'processing') AND minutes_since_created > 5;

-- Failed refunds in last 24h
SELECT * FROM raw_ops.refunds
WHERE status = 'failed' AND created_at > NOW() - INTERVAL '24 hours';

-- Refund events audit trail
SELECT * FROM raw_ops.ops_events
WHERE kind IN ('refund_trigger_fired', 'refund_processed', 'refund_failed', 'refund_notification_failed')
ORDER BY created_at DESC LIMIT 20;

-- Monitor execution history
SELECT check_name, run_at, status, duration_ms, alert_triggered, result_summary
FROM raw_ops.monitoring_runs
WHERE check_name = 'refund_health'
ORDER BY run_at DESC LIMIT 10;

7.5 Manual Recovery¶

-- Re-trigger a failed refund (reset to pending, trigger will re-fire)
UPDATE raw_ops.refunds
SET status = 'pending', shopify_error = NULL
WHERE id = '<refund_id>';

8. Ops Portal Integration¶

Located in Customer Profile panel. Opened via "💰 Refund" button.

Fields: - Refund Type: Full / Partial / Replacement - Order: Dropdown of customer's orders - Amount: Auto-filled for full refunds - Reason: Dropdown (Product quality, Delivery problem, Customer changed mind, Duplicate order, Dog not eating, Other) - Internal Notes: Free text

8.2 JavaScript Functions¶

Function	Purpose
`openRefundModal()`	Open modal, load customer orders
`closeRefundModal()`	Close modal
`onRefundTypeChange()`	Show/hide amount field
`onRefundOrderChange()`	Auto-fill amount for full refunds
`processRefund()`	Call create_refund RPC

9. Deployment History¶

Phase 1 (2026-02-02)¶

[x] Create raw_ops.refunds table with indexes
[x] Create public.get_customer_orders() RPC
[x] Create public.create_refund() RPC
[x] Build Ops Portal refund modal and button
[x] Export JavaScript functions to window.opsPortal.support

Phase 2 (2026-02-03)¶

[x] Create Edge Function process-refund
[x] Deploy with --no-verify-jwt
[x] Update status constraint: ['pending', 'processing', 'processed', 'failed']
[x] Create and deploy trigger trg_process_refund
[x] Fix ops_events logging (direct insert, not RPC)
[x] Fix column references (processed_at not completed_at, no updated_at)
[x] Fix env var names (SHOPIFY_STORE, SHOPIFY_ACCESS_TOKEN, CUSTOMERIO_SITE_ID)
[x] Create monitoring view v_refunds_pending
[x] Create summary view v_refund_daily_summary
[x] Create health check fn_check_refund_health_v2()
[x] Add refund_health to run-monitor dispatch map
[x] Schedule pg_cron job monitor-refund-health (*/5 min)
[x] End-to-end test: trigger → claim → process → terminal state ✅
[x] Verify monitoring runs logging ✅

Phase 3 (Pending)¶

[ ] Fix Customer.io Track API authentication (401 error)
[ ] Create Customer.io email template: refund_processed
[ ] Create Customer.io email template: replacement_order_created
[ ] Test with real customer notification
[ ] Update SOP status to fully Production Ready

10. Debugging Reference¶

Known Issues Resolved During Deployment¶

Issue	Root Cause	Fix
401 from Edge Function	Supabase default JWT verification	`--no-verify-jwt` flag
Status constraint violation	Constraint only allowed `pending/processed/failed`	Added `processing` to constraint
`updated_at` column error	Column doesn't exist on refunds table	Removed all `updated_at` references
`completed_at` column error	Column doesn't exist, uses `processed_at`	Changed to `processed_at`
Env var mismatch	Code expected different names than configured	Updated to `SHOPIFY_STORE`, `SHOPIFY_ACCESS_TOKEN`, `CUSTOMERIO_SITE_ID`
ops_events not logging	Supabase client `rpc()` doesn't throw on error	Changed to direct `.from().insert()` with error check
Status stuck at `processing`	`completed_at` field update silently failed	Fixed column name to `processed_at`
Customer.io 401	Track API auth issue	🔶 Pending fix

pg_net Response Debugging¶

-- Check recent pg_net responses for process-refund calls
SELECT id, status_code, content, error_msg, timed_out, created
FROM net._http_response
ORDER BY created DESC
LIMIT 5;

Document	Purpose
SOP-CS-00	Customer Operations System overview
SOP-CS-04 v1.1	Refund decision-making framework and policies
SOP-MON-01	Monitoring & Alerting Architecture (trigger pattern)
Protocol_Raw_Operations_Portal_Documentation_v3_6	Ops Portal technical docs
Protocol_Raw_Email_Design_System_v1_1	Email template styling

Version History¶

Version	Date	Changes
1.0	2026-02-02	Initial version — Phase 1 complete, Phase 2 architecture defined
1.2	2026-02-03	Phase 2 deployed — Edge Function, trigger, monitoring, pg_cron alerting. Schema corrections documented.

Document Owner: Protocol Raw Operations
Classification: Customer Operations
Review Cycle: Quarterly
Next Review: 2026-05-03

Protocol Raw — Verified safe, batch by batch.