System Overview - How the SOPs Work Together¶

Version: 3.2 Last Updated: April 2026 Status: Production Ready Owner: Protocol Raw Operations

This document provides a high-level map of how Protocol Raw's operations system fits together so anyone can understand flows end to end and know where to go next.

At a Glance¶

Core Entities: Orders, Batches, Inventory, Shipments, Customers, Subscriptions, Support Tickets, Referrals, Health Baselines

Source of Truth: PostgreSQL on Supabase (schemas: raw_ops, analytics, public)

Infrastructure Counts (verified April 2026):

Component	Count
Database tables	130 (128 raw_ops, 1 analytics, 1 public)
Database views	149 (113 raw_ops, 33 analytics, 3 public)
Database functions	541 (286 raw_ops, 255 public)
Database triggers	59
Edge Functions (deployed)	96
pg_cron jobs	78 (77 active, 1 inactive)
Cloudflare Workers	2 (health-portal, email-ingest)
Make.com scenarios	~40 (many superseded by native Supabase)

Automation Layers: - Native Supabase: pg_cron, pg_net, Edge Functions (primary for all monitoring, real-time operations, and external API calls) - Cloudflare Workers: Proxy/routing layer (health portal, email ingestion) - Make.com: Multi-system orchestration only (Shopify-Seal address sync, Customer.io event delivery, dunning day 0/3/7/10)

Audit Trail: raw_ops.ops_events - append-only event log for all systems

Data Products: Metabase dashboards (connected to analytics schema views)

Communications: - Slack alerts (#ops-alerts, #ops-urgent, #daily-ops) - Customer emails via Customer.io (transactional API + Track API + campaigns) - Support emails via cs-agent-triage + support-send-response pipeline

Portals: - Customer Portal: my.protocolraw.co.uk (Cloudflare Pages, React 19) - Ops Portal: ops.protocolraw.co.uk - Proof Portal: proof.protocolraw.co.uk (Edge Function: proof-page) - Health Portal: health.protocolraw.co.uk (Cloudflare Worker -> health-page Edge Function)

System Architecture¶

Core Components¶

Layer	Technology	Purpose
Database	Supabase PostgreSQL	Source of truth, business logic, triggers
Scheduled Jobs	pg_cron (83 jobs)	Time-based operations (monitoring, exports, processing queues)
Async HTTP	pg_net	Non-blocking calls from database to Edge Functions
API Layer	Supabase Edge Functions (96)	External integrations, webhooks, monitoring, portal backends
Proxy/Routing	Cloudflare Workers (2)	Domain routing for health portal, email ingestion
Frontend	Cloudflare Pages	Customer Portal (React 19), Docs site (MkDocs)
Orchestration	Make.com	Multi-system coordination (Shopify, Seal, Customer.io)
Customer Comms	Customer.io	Lifecycle emails, transactional messages, campaigns
Monitoring	SOP-MON-01 Architecture	Centralised alerting via `ops-alerter` Edge Function
Analytics	Metabase	Dashboards and reporting via analytics schema

Canonical Monitoring Pattern (SOP-MON-01)¶

All automated monitoring follows this architecture:

pg_cron (scheduled jobs)
    |
pg_net (async HTTP)
    |
run-monitor (Edge Function)
    |
fn_check_*_v2() (PostgreSQL functions)
    |
monitoring_runs (logging table)
    |
ops-alerter (Edge Function) [if threshold breached]
    |
Slack (#ops-alerts / #ops-urgent / #daily-ops)

Key Principle: Native Supabase for all monitoring. Make.com only for multi-system orchestration.

Independent canary: fn_canary_system_health_v1() runs every 10 min via its own pg_cron job, completely independent of fn_invoke_edge_function. Detects if the monitoring infrastructure itself is down.

Data Flow by Lifecycle Stage¶

Order Lifecycle¶

Shopify Order
    |
Edge Function (shopify-webhook)
    |
raw_ops.webhook_inbox -> raw_ops.customers + raw_ops.orders + raw_ops.order_items
    |
Allocation (SOP-INV-01) - assigns order to RELEASED batches via FEFO
    |
3PL Export (SOP-ORD-02) - CSV to fulfilment partner
    |
Dispatch Ingestion (SOP-ORD-03) - tracking numbers recorded from 3PL files
    |
Courier Monitoring (SOP-DLV-01) - delivery status, exceptions
    |
Customer Comms (SOP-LC-01) - lifecycle emails via Customer.io

Batch Lifecycle¶

Ops Portal: Create Batch (date + kg)
    |
Edge Function (create-batch)
    |
Database: batch_code, public_batch_id, QR code, proof_url generated
    |
Status: QA_HOLD (awaiting lab results)
    |
Co-packer produces batch, prints labels with proof QR
    |
Lab Email -> lab-email-ingestion Edge Function -> OpenAI Vision parse
    |
raw_ops.lab_results INSERT
    |
Database Triggers: PASS -> status = RELEASED, FAIL -> status = REJECTED
    |
Auto-allocation trigger fires (SOP-INV-01)
    |
Proof page now publicly accessible via QR code (proof-page Edge Function)
    |
Proof jobs: QR PDFs, label PDFs, co-packer emails (process-proof-jobs)

Subscription Lifecycle¶

Shopify Order (first)
    |
Customer created + subscription synced from Seal webhook
    |
Referral attribution (if referral slug present)
    |
Lifecycle events pipeline: order_created_first -> Customer.io campaign
    |
Delivery -> first_box_delivered event -> Founder check-in (day 10)
    |
Ongoing: 7-day reminder -> 48-hour lock -> dispatch -> delivery
    |
Subscription actions (portal or ops): skip, pause, resume, cancel, change_box, change_frequency
    |
Cancel -> cancel flow modal (8 categories, 15 codes) -> retention intervention -> portal-subscription-action: cancel -> Seal webhook -> fn_confirm_cancellation_from_seal_v1 -> cancellation_cancelled event + GBP10 win-back credit -> Win-back campaign (30d, 60d)
    |
Dunning (payment failure): Day 0/3/7/10 via Make.com scenarios
    |
Card expiry warnings: 30-day and 7-day via card-expiry-processor pg_cron

Customer Support Lifecycle¶

Customer email -> Cloudflare Worker (email-ingest) -> cs-agent-triage Edge Function
    |
AI classification + draft response -> support_tickets + ticket_messages
    |
Autonomous resolution (if confidence high) OR human review queue
    |
Response sent via support-send-response Edge Function
    |
CSAT survey sent after resolution (cast-survey-send, csat-submit)
    |
Live chat: chat Edge Function (streaming SSE) -> chat-rating

Edge Functions by Category (96 deployed)¶

Orders and Webhooks (4)¶

Function	Purpose
shopify-webhook	Primary order ingestion from Shopify
shopify-webhook-retry	Retry failed webhook processing
seal-webhook-handler	Subscription events from Seal + cancellation confirmation (v2.0)
widget-events	Website widget event tracking

Fulfilment and 3PL (6)¶

Function	Purpose
export-3pl-orders	Export orders to 3PL
export-3pl-dispatch	Export dispatch data
import-3pl-dispatch	Import dispatch confirmations from 3PL
poll-dispatch-files	Poll 3PL for dispatch files
fulfillment-interface	Fulfilment UI backend
trigger-courier-webhook	Trigger courier events

Operations and Batch (4)¶

Function	Purpose
create-batch	Batch creation (calls fn_create_batch_v1)
calculate-pack-day-instructions	PCM calculations
send-copacker-email	Send emails to co-packer
hyper-api	Consolidated API endpoint

Calculator and Abandonment (8)¶

Function	Purpose
calculator-generate-token	Create discount token
calculator-generate-token-multipet	Multi-pet token
calculator-get-session	Fetch session data for product page
calculator-validate-token	Validate token for checkout
calculator-apply-discount	Apply discount at checkout
calculator-check-customer	Check existing customer
send-abandonment-events	Calculator abandonment recovery
waitlist-signup	Waitlist lead capture

Customer Support (8)¶

Function	Purpose
cs-agent-triage	Email classification and AI draft
autonomous-support-agent	Auto-resolve tickets
support-send-response	Send support emails
send-support-email	Send support email replies
chat	Live chat AI with streaming
chat-rating	Chat response ratings
chat-transcript	Chat transcript export
embedding-generator	KB embeddings

Portal (6)¶

Function	Purpose
portal-session	Authenticate and load dashboard
portal-logout	Revoke session
portal-subscription-action	Execute subscription actions via Seal API
portal-update-address	Update address, sync to Shopify + Seal
request-portal-access	Generate magic-link token
send-portal-access-emails	Proactive portal magic links

Proof System (5)¶

Function	Purpose
proof-page	Render proof page
proof_job_worker	Queue worker for proof jobs
process-proof-jobs	Proof job processing
generate-proof-insert-pdf	Generate proof insert PDFs
generate-label-pdf	Generate label PDFs

Email and Lifecycle (7)¶

Function	Purpose
process-outbox	Email outbox queue processor
assemble-email-prompt	Assemble email prompt for AI-generated emails
send-delivery-emails	7-day and 48-hour reminders (includes credit data)
send-lifecycle-email	Single transactional email
send-lifecycle-events	Lifecycle event queue processor + Customer.io identify
send-credit-expiry-warnings	Credit expiry warning emails
lab-report-proxy	Proxy lab PDFs

Payment and Subscriptions (4)¶

Function	Purpose
get-payment-update-url	Seal payment edit URL
process-refund	Process refunds
process-dunning	Dunning payment retry processing
reconcile-subscriptions	Subscription reconciliation

Referrals (4)¶

Function	Purpose
process-referral-credits	Process referral credits
process-referral-credit-issuance	Process referral credit issuance
process-referral-attribution	Attribute referral orders
expire-referral-credits	Expire old referral credits

Cancellation (1)¶

Function	Purpose
cancellation-tracking	Cancellation flow v2.0: GET codes, POST record reason, POST log-step, POST update-outcome. 4 routes, session-authenticated.

Cancellation Database Functions (6)¶

Function	Purpose
fn_get_cancellation_categories_v1	Return active categories from reference table
fn_get_cancellation_reason_codes_v1	Return active codes with intervention mappings
fn_log_cancel_flow_step_v1	Log cancel funnel step to subscription_actions
fn_record_cancellation_reason_v1	Record structured reason (monotonic upsert)
fn_update_cancellation_outcome_v1	Advance outcome state machine (validated transitions)
fn_confirm_cancellation_from_seal_v1	Confirm cancellation from Seal webhook timestamp

Cancellation Analytics Views (3)¶

View	Purpose
v_cancellation_analysis	Cancellation reasons with subscription context, outcome flags, SKU signal
v_sku2_signal	Monthly variety + exclusion % of voluntary cancels (WATCH 10%, INVESTIGATE 15%)
v_cancel_intervention_effectiveness	Intervention acceptance and save rates by category

Health Data (8)¶

Function	Purpose
health-baseline-submit	Submit health baseline scores
health-baseline-render	Render health baseline page
health-checkpoint-render	Render health checkpoint page
health-checkpoint-submit	Submit health checkpoint scores
health-page	Health portal page renderer
schedule-health-checkpoints	Schedule follow-up health checks
send-health-checkpoints	Send health checkpoint emails
expire-health-tokens	Expire old health tokens

Monitoring and Alerting (4)¶

Function	Purpose
run-monitor	Central monitor dispatch
ops-alerter	Slack notifications
send-slack-alert	Legacy Slack sender
health-sop01	SOP-LAB-01 health check

Finance (3)¶

Function	Purpose
fin-fx-sync	Currency rate sync
fin-ad-spend-provider-sync	Ad spend sync (Meta, Google)
save-invoice	Save supplier invoice

Finance - Ops Portal (2)¶

Function	Purpose
update-invoice-status	Update invoice status
delete-draft-invoice	Delete draft invoices

Suppliers (1)¶

Function	Purpose
manage-supplier	Supplier CRUD

Milkround Delivery (10)¶

Function	Purpose
milkround-get-today	Get today's delivery round
milkround-eligible-orders	Find orders eligible for milkround
milkround-create-round	Create a delivery round
milkround-cancel-round	Cancel a delivery round
milkround-start-round	Start a delivery round
milkround-record-delivery	Record successful delivery
milkround-record-failure	Record delivery failure
milkround-upload-photo	Upload delivery photo
milkround-photo-cleanup	Clean up old delivery photos
milkround-optimise-route	Optimise delivery route

Market Intelligence (3)¶

Function	Purpose
mi-scraper	Scrape competitor data (Reddit, Trustpilot)
mi-analyser	Classify and analyse scraped content
mi-digest	Generate weekly intelligence digest

Feedback and CSAT (3)¶

Function	Purpose
cast-submit	Submit CAST survey
cast-survey-send	Send CAST survey
csat-submit	Submit CSAT rating

Sync and Integration (2)¶

Function	Purpose
synch-address-update	Sync addresses to Shopify/Seal
allocation-alerts	Inventory allocation alerts

Lab Ingestion (1)¶

Function	Purpose
lab-email-ingestion	Parse lab result emails (replaced Make.com scenario)

Courier Notifications (1)¶

Function	Purpose
send-courier-notification	Send courier exception emails (replaced Make.com scenario)

Content and Media (1)¶

Function	Purpose
dog-photo-upload	Multipart upload endpoint for customer dog photos. Validates MIME (image/jpeg, image/png, image/webp, image/heic) and size (10 MB), writes to the private `dog-photos` storage bucket, calls `submit_dog_photo` RPC, cleans up orphans on failure. Deployed with `--no-verify-jwt`. See SOP-PHOTO-01.

Testing and Utility (1)¶

Function	Purpose
test-shopify-api	Test Shopify connection

Storage Buckets¶

Bucket	Visibility	Size limit	Allowed MIME	Purpose
`dog-photos`	Private	10 MB	image/jpeg, image/png, image/webp, image/heic	Customer-submitted dog photos for website, social, and ad use. Path convention `uploads/{customer_id}/{uuid}.{ext}`. Read by the Ops Portal via `createSignedUrls(paths, 3600)`. See SOP-PHOTO-01.

Notable Schema Additions (v3.2)¶

raw_ops.dog_photos - Customer-submitted dog photos for website/social/ad use with channel-scoped consent and moderation workflow. See SOP-PHOTO-01.
public.v_dog_wall_display - Public-facing view of approved and featured dog photos with website consent, joined to customer tenure and order count. Anon-readable.
raw_ops.customer_health_checkpoints.photo_storage_path - Optional storage path for a photo submitted alongside a health checkpoint (see SOP-HDI-01).

pg_cron Jobs (83 total, 81 active)¶

Queue Processors (every minute)¶

Job	Schedule	Purpose
process-allocation-queue	`* * * * *`	Process allocation queue
process-lifecycle-events	`* * * * *`	Process lifecycle events
process-outbox-every-minute	`* * * * *`	Process email outbox (fast)
process-refund-retries	`* * * * *`	Process refund retries

High-Frequency (every 2-5 min)¶

Job	Schedule	Purpose
process-outbox-every-2-min	`/2 * * *`	Secondary outbox processing
monitor-order-ingestion-slo	`/2 * * *`	Order ingestion SLO check
monitor-outbox-health	`/2 * * *`	Email queue health
check-allocation-alerts	`/5 * * *`	Inventory allocation issues
trigger-allocation-alerts	`/5 * * *`	Trigger allocation alert checks
poll-dispatch-files-every-5-min	`/5 * * *`	Poll 3PL for dispatch files
retry-lifecycle-events	`/5 * * *`	Retry failed lifecycle events
dlv-01-retry-notifications	`/5 * * *`	Retry courier notifications
ord-01-webhook-retry	`/5 * * *`	Retry failed Shopify webhooks
ord-02-retry-exports	`/5 * * *`	Retry failed 3PL exports
rate-limits-cleanup	`/5 * * *`	Clean up expired rate limits
monitor-monitoring-heartbeat	`/5 * * *`	Monitor heartbeat
monitor-ord-01-webhook-health	`/5 * * *`	Webhook health
monitor-ord-02-export-health	`/5 * * *`	Export health
monitor-proof-portal-health	`/5 * * *`	Proof portal health
monitor-proof-system-health	`/5 * * *`	Proof system health
monitor-refund-health	`/5 * * *`	Refund processing health
monitor-subscription-health	`/5 * * *`	Subscription system health

Medium-Frequency (every 10-15 min)¶

Job	Schedule	Purpose
canary-system-health	`/10 * * *`	Independent system health (canary)
csat-survey-sender	`/15 * * *`	Send CSAT surveys
dunning-processor	`/15 * * *`	Process dunning actions
referral-process-credits	`/15 * * *`	Process referral credits
monitor-abandonment-health	`/15 * * *`	Abandonment health
monitor-allocation-health	`/15 * * *`	Allocation system health
monitor-cs-01-support-health	`/15 * * *`	Support health
monitor-dlv-01-watchdog-health	`/15 * * *`	Courier watchdog health
monitor-lab-ingestion-health	`/15 * * *`	Lab ingestion health
monitor-lab-sla	`/15 * * *`	Lab turnaround SLA
monitor-lifecycle-events	`/15 * * *`	Lifecycle event processing
monitor-ord-03-dispatch-health	`/15 * * *`	Dispatch health
monitor-referral-health	`/15 * * *`	Referral system health
monitor-stock-levels	`/15 * * *`	Low stock alerts

Sub-Minute¶

Job	Schedule	Purpose
process-proof-jobs	`30 seconds`	Process proof job queue

Half-Hourly¶

Job	Schedule	Purpose
lab-01-fallback-poll	`/30 * * *`	Fallback lab email poll
mi-classify	`/30 * * *`	Classify MI content

Hourly¶

Job	Schedule	Purpose
send-abandonment-events-hourly	`0 * * * *`	Calculator abandonment emails

Multi-Hour¶

Job	Schedule	Purpose
monitor-inventory-planning	`0 /6 * *`	Inventory planning health
monitor-working-capital	`0 /6 * *`	Working capital monitoring
lab-01-gmail-watch-renew	`0 0 /6 *`	Renew Gmail watch for lab emails

Daily¶

Job	Schedule	Purpose
refresh-lab-performance	`0 1 * * *`	Refresh lab performance metrics
expire-old-credits	`0 2 * * *`	Expire old credits (INACTIVE)
referral-expire-credits	`0 2 * * *`	Expire referral credits
expire-referral-rewards	`0 3 * * *`	Expire unused referral rewards
health-expire-tokens	`0 3 * * *`	Expire health tokens
reconcile-subscriptions-daily	`0 3 * * *`	Reconcile subscriptions with Seal
mi-scrape-reddit	`0 3 * * *`	Scrape Reddit
milkround-photo-cleanup	`0 3 * * *`	Clean up milkround photos
mi-scrape-trustpilot	`30 3 * * *`	Scrape Trustpilot
daily-health-score-calculation	`0 5 * * *`	Calculate customer health scores
daily-intervention-detection	`30 5 * * *`	Detect at-risk customers
health-schedule-checkpoints	`0 6 * * *`	Schedule health checkpoints
health-send-checkpoints	`15 6 * * *`	Send health checkpoint emails
monitor-health-data-completeness	`30 6 * * *`	Health data completeness
fallback-first-delivery-events	`0 7 * * *`	Fallback for missed delivery events
fin-fx-sync	`55 7 * * *`	Currency rate sync
fin-ad-spend-meta	`0 8 * * *`	Meta ad spend sync
fin-ad-spend-google	`5 8 * * *`	Google ad spend sync
monitor-ad-spend-freshness	`30 8 * * *`	Ad spend freshness
card-expiry-processor	`0 9 * * *`	Card expiry warnings
send-delivery-emails	`0 9 * * *`	7-day and 48-hour reminders
monitor-daily-snapshot	`0 9 * * *`	Daily digest to #daily-ops
monitor-cost-tracking-health	`0 9 * * *`	Cost tracking health
monitor-ord-01-daily-digest	`0 9 * * *`	Order daily digest
referral-expiry-warnings	`0 9 * * *`	Credit expiry warnings
monitor-monitor-coverage	`15 9 * * *`	Monitor coverage check
monitor-subscription-daily	`5 9 * * *`	Daily subscription metrics
send-portal-access-emails	`0 10 * * *`	Proactive portal magic links

Weekly¶

Job	Schedule	Purpose
cleanup-monitoring-runs	`0 3 * * 0`	Clean old monitoring runs
mi-weekly-digest	`0 9 * * 0`	Weekly MI digest

Monthly¶

Job	Schedule	Purpose
mi-cleanup	`0 2 1 * *`	Clean old MI data
mi-monthly-copy-bank	`0 9 1 * *`	Monthly copy bank generation

Inactive Jobs (1)¶

Job	Reason
expire-old-credits	Replaced by referral-expire-credits

Database Triggers (60)¶

Order Processing¶

Trigger	Table	Event	Purpose
trg_order_confirmation	orders	AFTER INSERT	Send order confirmation email
trg_order_lifecycle_event	orders	AFTER INSERT/UPDATE	Create lifecycle events
trg_extract_referral_slug	orders	BEFORE INSERT/UPDATE	Extract referral attribution
trg_process_referral_attribution	orders	AFTER INSERT/UPDATE	Process referral attribution
trigger_allocate_on_paid	orders	AFTER UPDATE	Auto-allocate on payment
trigger-packing-instruction	orders	AFTER UPDATE	Generate packing instructions

Batch Processing¶

Trigger	Table	Event	Purpose
trigger_set_public_batch_id	batches	BEFORE INSERT	Generate public batch ID
trg_calculate_batch_cogs	batches	BEFORE INSERT/UPDATE	Calculate batch COGS
trg_create_outbox_on_release	batches	AFTER UPDATE	Create outbox entry on release
trigger_batch_released_allocate	batches	AFTER UPDATE	Auto-allocate on batch release
trigger_enqueue_proof_job	batches	AFTER INSERT	Enqueue proof generation job

Lab Results¶

Trigger	Table	Event	Purpose
trg_apply_lab_outcome	lab_results	AFTER INSERT	Apply lab outcome to batch
trg_auto_release_batch	lab_results	AFTER INSERT	Auto-release on PASS
trg_release_batch_on_pass	lab_results	AFTER INSERT/UPDATE	Release batch on pass

Shipments¶

Trigger	Table	Event	Purpose
trg_shipment_email	shipments	AFTER INSERT/UPDATE	Send dispatch/delivery emails + lifecycle events (first_box_dispatched, first_box_delivered)
trg_auto_decrement_inventory	shipments	AFTER INSERT/UPDATE	Decrement inventory on shipment
trg_emit_out_for_delivery	shipments	AFTER INSERT/UPDATE	Emit out-for-delivery event
trg_log_shipment_exception	shipments	AFTER INSERT/UPDATE	Log shipment exceptions
trg_process_referral_delivery	shipments	AFTER INSERT/UPDATE	Process referral on delivery confirmation

Subscription Actions¶

Trigger	Table	Event	Purpose
trg_subscription_action_email	subscription_actions	AFTER INSERT	Send action confirmation email + cancellation lifecycle event

Customer¶

Trigger	Table	Event	Purpose
trg_assign_persona	customers	BEFORE INSERT	Assign deterministic persona
trg_create_referral_code	customers	AFTER INSERT	Generate referral code

Support¶

Trigger	Table	Event	Purpose
trigger_link_support_ticket_customer	support_tickets	BEFORE INSERT	Link ticket to customer
trigger_ticket_created	support_tickets	AFTER INSERT	Process new ticket
trg_schedule_csat_survey	support_tickets	BEFORE UPDATE	Schedule CSAT on resolution

Other¶

Trigger	Table	Event	Purpose
trg_cancellation_event	cancellation_reasons	AFTER INSERT/UPDATE	Track cancellation events
trigger_notify_courier_exception	ops_events	AFTER INSERT	Notify on courier exceptions
trg_kb_auto_embed	ai_knowledge_sections	AFTER INSERT/UPDATE	Auto-embed KB sections
trg_set_token_count	ai_knowledge_sections	BEFORE INSERT/UPDATE	Set token count
proof_job_processor	proof_jobs	AFTER INSERT	Process proof jobs

Cloudflare Workers (2)¶

Worker	Domain	Purpose
health-portal	health.protocolraw.co.uk	Proxy to health-page Edge Function
email-ingest	(configured in Cloudflare)	Route inbound support emails to cs-agent-triage

How the SOPs Interlock¶

Allocation and Readiness¶

SOP	Function
SOP-INV-01 v1.2	FEFO allocation - orders only reserved against RELEASED, unexpired batches
SOP-LAB-01 v1.1	Lab-to-Release - flips batches QA_HOLD -> RELEASED, triggers allocation cascade

Proof and Verification¶

SOP	Function
SOP-PROOF-00 v1.0	Proof-of-Safety system - generates proof pages, QR codes, label PDFs at batch creation
SOP-PROOF-01 v1.1	Proof Portal - public-facing proof page rendering and lab report proxy
SOP-LAB-01	Updates proof page visibility when batch released

Fulfilment Loop¶

SOP	Function
SOP-ORD-01 v5.1	Shopify webhook ingestion, order processing
SOP-ORD-02 v4.1	3PL order export with batch traceability
SOP-ORD-03 v3.0	Dispatch file ingestion, shipment creation
SOP-DLV-01 v4.0	Courier watchdog, delivery exceptions, customer notifications
SOP-MR-01 v1.0	Milkround local delivery system

Cold Chain and Packaging¶

SOP	Function
SOP-PACK-01 v2.1	Intelligent packing operations (insulation, gel packs by ambient temperature)

Finance¶

SOP	Function
SOP-FIN-01 v1.2	Ad spend sync (Meta, Google) for CAC calculations - now via Edge Functions
SOP-FIN-02 v1.0	Cost tracking system - supplier invoices, COGS via Ops Portal

Customer Operations¶

SOP	Function
SOP-CS-00 v1.7	Master customer operations design (all touchpoints)
SOP-CS-01 v2.3	AI customer service triage
SOP-CS-02 v2.2	Live chat system
SOP-CS-03 v1.6	Email support workflow with threading (shadow validation)
SOP-CS-04 v1.3	Refund resolution operations
SOP-CS-05 v1.0	CSAT survey system
SOP-LC-01 v1.4	Lifecycle communications (all email copy and triggers)

Subscription Management¶

SOP	Function
SOP-SUB-00 v4.1	Subscription state management, dunning, reconciliation
SOP-REF-01 v3.0	Referral system with credit allocation and delivery confirmation
SOP-CHS-01 v1.0	Customer health scoring
SOP-HDI-01 v1.2	Health data infrastructure (baselines, checkpoints, portal)

Email Architecture¶

SOP	Function
SOP-EMAIL-01 v1.1	Email sender configuration and architecture
SOP-LC-01 v1.4	All email copy, triggers, and technical implementation

Knowledge and Content¶

SOP	Function
SOP-AI-KB-01 v3.2	AI knowledge base for chat and support
SOP-JOURNAL-01 v1.0	Journal content management
SOP-TEST-01 v1.0	Testimonials system
SOP-PHOTO-01 v1.0	Dog photo collection, moderation, and display (channel-scoped consent, `raw_ops.dog_photos`, `public.v_dog_wall_display`, `dog-photo-upload` Edge Function, private `dog-photos` storage bucket)

Market Intelligence¶

SOP	Function
SOP-MI-01 v1.1	Market intelligence (Reddit, Trustpilot scraping, classification, digests)

Monitoring¶

SOP	Function
SOP-MON-01 v1.4	Monitoring and alerting architecture

Pre-Launch¶

SOP	Function
SOP-WL-01 v1.0	Pre-launch waitlist system

Complete SOP Index¶

Core Operations¶

SOP ID	Name	Version	Status
SOP-ORD-01	Shopify Order Ingestion	v5.1	Production
SOP-ORD-02	3PL Order Export	v4.1	Production
SOP-ORD-03	Dispatch Ingestion	v3.0	Production
SOP-LAB-01	Batch Creation, Lab to Release	v1.1	Production
SOP-PROOF-00	Proof of Safety System	v1.0	Production
SOP-PROOF-01	Proof Portal System	v1.1	Production
SOP 03	Daily Snapshot Ping	v3.1	Production

Delivery and Fulfilment¶

SOP ID	Name	Version	Status
SOP-DLV-01	Courier Watchdog	v4.0	Production
SOP-PACK-01	Intelligent Packing Operations	v2.1	Production
SOP-MR-01	Milkround Delivery System	v1.0	Production

Inventory¶

SOP ID	Name	Version	Status
SOP-INV-01	Inventory Control (FEFO)	v1.2	Production
SOP-INV-02	Demand Forecasting and Inventory Planning	v1.0	Production

Customer Operations¶

SOP ID	Name	Version	Status
SOP-CS-00	Customer Operations System (Master)	v1.7	Design Doc
SOP-CS-01	AI Customer Service Triage	v2.3	Production
SOP-CS-02	Live Chat System	v2.2	Production
SOP-CS-03	Email Support Workflow	v1.6	Shadow Validation
SOP-CS-04	Refund Resolution Operations	v1.3	Production
SOP-CS-05	CSAT Survey System	v1.0	Production

Subscriptions and Lifecycle¶

SOP ID	Name	Version	Status
SOP-SUB-00	Subscription State Management	v4.1	Production
SOP-REF-01	Referral System	v3.0	Production
SOP-CHS-01	Customer Health Scoring	v1.0	Production
SOP-HDI-01	Health Data Infrastructure	v1.2	Production
SOP-LC-01	Lifecycle Communications	v1.4	Production
SOP-EMAIL-01	Email Architecture	v1.1	Production

Finance¶

SOP ID	Name	Version	Status
SOP-FIN-01	Ad Spend Sync	v1.2	Production
SOP-FIN-02	Cost Tracking System	v1.0	Production

Monitoring¶

SOP ID	Name	Version	Status
SOP-MON-01	Monitoring and Alerting Architecture	v1.4	Production
SOP-MON-02	Order Ingestion SLO Monitor	v2.0	RETIRED (absorbed into SOP-ORD-01)
SOP-MON-03	Allocation Health Monitor	v1.0	RETIRED (absorbed into SOP-INV-01)

Knowledge and Content¶

SOP ID	Name	Version	Status
SOP-AI-KB-01	AI Knowledge Base	v3.2	Production
SOP-JOURNAL-01	Journal Content Management	v1.0	Production
SOP-TEST-01	Testimonials System	v1.0	Production
SOP-PHOTO-01	Dog Photo System	v1.0	Production Ready

Market Intelligence¶

SOP ID	Name	Version	Status
SOP-MI-01	Market Intelligence System	v1.1	Production

Pre-Launch¶

SOP ID	Name	Version	Status
SOP-WL-01	Pre-Launch Waitlist	v1.0	Production

Superseded¶

SOP ID	Name	Replaced By
SOP 0R v4.2	Proof-of-Safety (legacy)	SOP-PROOF-00 + SOP-PROOF-01
SOP 05 v3.0	AI Invoice Parsing	SOP-FIN-02 (Ops Portal manual entry)

Monitoring, Alerting, and Audit¶

Audit Log¶

Table: raw_ops.ops_events - canonical append-only event stream for all SOPs

Fields: - entity_type: order, batch, shipment, customer, SUBSCRIPTION, ADDRESS, etc. - entity_id: UUID of affected record - kind: INFO, EXCEPTION, ERROR - message: Human-readable description - meta: JSONB with additional context

Monitoring Runs¶

Table: raw_ops.monitoring_runs - execution history for all automated monitors

Retention: 30 days, pruned daily at 02:00 UTC by pg_cron job prune-monitoring-runs (jobid 145). See SOP-MON-01 for details.

Database Storage (2026-04-15)¶

Total DB size is ~199 MB (down from 1,415 MB after remediation on 2026-04-15).

High-churn system tables and their retention:

Table	Retention	Mechanism
`cron.job_run_details`	30 days	pg_cron `prune-cron-job-details` (jobid 146), daily 02:05 UTC
`raw_ops.monitoring_runs`	30 days	pg_cron `prune-monitoring-runs` (jobid 145), daily 02:00 UTC
`net._http_response`	Manual	No automated job. Requires periodic `VACUUM FULL` — live row count stays near zero but heap bloats from pg_net MVCC churn. Run monthly or when size exceeds ~100 MB.

Active Monitors (34 pg_cron jobs)¶

Monitor	Schedule	Channel
monitor-order-ingestion-slo	*/2 min	#ops-alerts / #ops-urgent
monitor-outbox-health	*/2 min	#ops-alerts / #ops-urgent
monitor-monitoring-heartbeat	*/5 min	#ops-urgent
monitor-ord-01-webhook-health	*/5 min	#ops-alerts
monitor-ord-02-export-health	*/5 min	#ops-alerts
monitor-proof-portal-health	*/5 min	#ops-alerts
monitor-proof-system-health	*/5 min	#ops-alerts
monitor-refund-health	*/5 min	#ops-alerts
monitor-subscription-health	*/5 min	#ops-alerts
canary-system-health	*/10 min	#ops-urgent
monitor-abandonment-health	*/15 min	#ops-alerts
monitor-allocation-health	*/15 min	#ops-alerts
monitor-cs-01-support-health	*/15 min	#ops-alerts
monitor-dlv-01-watchdog-health	*/15 min	#ops-alerts
monitor-lab-ingestion-health	*/15 min	#ops-alerts
monitor-lab-sla	*/15 min	#ops-alerts
monitor-lifecycle-events	*/15 min	#ops-alerts
monitor-ord-03-dispatch-health	*/15 min	#ops-alerts
monitor-referral-health	*/15 min	#ops-alerts
monitor-stock-levels	*/15 min	#ops-urgent
monitor-inventory-planning	*/6 hr	#ops-alerts
monitor-working-capital	*/6 hr	#ops-alerts
monitor-ad-spend-freshness	Daily 08:30	#ops-alerts
monitor-daily-snapshot	Daily 09:00	#daily-ops
monitor-cost-tracking-health	Daily 09:00	#ops-alerts
monitor-ord-01-daily-digest	Daily 09:00	#daily-ops
monitor-monitor-coverage	Daily 09:15	#ops-alerts
monitor-subscription-daily	Daily 09:05	#daily-ops
monitor-health-data-completeness	Daily 06:30	#ops-alerts

Slack Channels¶

Channel	Purpose
#ops-alerts	Standard operational alerts (warning severity)
#ops-urgent	Critical issues requiring immediate action
#daily-ops	Daily digest and info notifications

Customer.io Integration¶

Transactional Message IDs¶

ID	Email	Trigger
3	Portal Access Magic Link	request-portal-access
6	Order Confirmation (First)	trg_order_confirmation
7	Order Confirmation (Repeat)	trg_order_confirmation
9	Skip Confirmation	trg_subscription_action_email
10	Reschedule Confirmation	trg_subscription_action_email
11	Pause Confirmation	trg_subscription_action_email
12	Resume Confirmation	trg_subscription_action_email
13	Cancellation Confirmation	trg_subscription_action_email
14	Box Size Change	trg_subscription_action_email
15	Frequency Change	trg_subscription_action_email
16	Address Change	trg_subscription_action_email
17	Dispatch Confirmation	trg_shipment_email
18	Delivery Confirmation	trg_shipment_email

Customer.io Campaigns (built, inactive pending launch)¶

Campaign	Trigger Event
Transition Guide	order_created_first (+1 day)
Onboarding Check-ins	first_box_delivered (+10 days founder email)
Win-Back Sequence	subscription_cancelled (+30d, +60d)

Track API Events (via send-delivery-emails)¶

Event	Trigger
delivery_reminder_7d	7 days before billing
delivery_cutoff_48h	48 hours before billing

Key Technical Decisions¶

Make.com Usage (Restricted)¶

Make.com is only used for multi-system orchestration: - Shopify-Seal address sync - Customer.io event delivery (via Event Bridge scenario) - Dunning day 0/3/7/10 payment retry (SOP-SUB-00) - Referral order enrichment and delivery reward issuance (SOP-REF-01) - Credit expiry warnings

Make.com is not used for: - Lab email ingestion (replaced by lab-email-ingestion Edge Function, March 2026) - Courier notifications (replaced by send-courier-notification Edge Function) - Ad spend sync (replaced by fin-ad-spend-provider-sync Edge Function) - Invoice parsing (replaced by Ops Portal manual entry) - Dispatch file polling (replaced by poll-dispatch-files Edge Function) - Monitoring and alerting (all via SOP-MON-01 architecture)

Batch Status Values¶

Status	Meaning
QA_HOLD	Awaiting lab results (cannot be allocated)
RELEASED	Lab passed, available for allocation
REJECTED	Lab failed, quarantined
DEPLETED	All kg allocated

Order Export States¶

State	Meaning
NULL	Not yet exported
queued	In outbox, pending delivery
sent	Successfully exported to 3PL
fulfilled	Dispatch confirmed

Where to Go for Specifics¶

Topic	Document
Monitoring patterns	SOP-MON-01 v1.4
Database schema	raw_ops schema (128 tables, 171 views)
Customer touchpoints	SOP-CS-00 v1.7
Email copy and triggers	SOP-LC-01 v1.4
Email design system	Email Design System v1.3
Proof system	SOP-PROOF-00, SOP-PROOF-01
Batch lifecycle	SOP-LAB-01 v1.1
Fulfilment flow	SOP-ORD-01/02/03
Courier monitoring	SOP-DLV-01 v4.0
Customer Portal	Customer Portal Documentation v3.0
Health data	SOP-HDI-01 v1.2
Referral system	SOP-REF-01 v3.0
Subscription management	SOP-SUB-00 v4.1
Edge Function registry	This document (Edge Functions by Category section)
Visual identity	Visual Identity Guide v2.4
Brand voice	Brand Voice and Copy Guidelines v1.4

Change Management¶

Schema changes: PR'd as migration scripts with version and backfill plan

New SOPs must: - Write to ops_events for audit trail - Include error handlers with retries - Follow SOP-MON-01 pattern for any scheduled operations

New external integrations must: - Define webhook signature verification - Include replay/retry policy - Use Edge Functions (not Make.com) for API calls

Edge Function deployment: Via supabase functions deploy (see CLAUDE.md for details)

Glossary¶

Term	Definition
FEFO	First Expired, First Out - inventory allocation strategy
H&R	Hold and Release - block shipments until lab PASS
ops_events	Append-only audit table for system events
Shadow Mode	Live runs with notifications to internal channels only
Outbox Pattern	Idempotent async delivery with retry logic
pg_cron	PostgreSQL job scheduler extension
pg_net	PostgreSQL HTTP client extension for async calls
Canary	Independent health check that monitors the monitoring infrastructure
Lifecycle Event	Queued event in lifecycle_events table, processed by send-lifecycle-events
Poultry Pack	Internal clearance unit for poultry ingredients (SOP-SOURCE-01)
Milkround	Local delivery round managed via Ops Portal

Version History¶

Version	Date	Changes
3.2	15 April 2026	Dog Photos system (SOP-PHOTO-01 v1.0): added `raw_ops.dog_photos` table, `public.v_dog_wall_display` view, `customer_health_checkpoints.photo_storage_path` column, `dog-photo-upload` Edge Function (Content and Media category), private `dog-photos` storage bucket, SOP-PHOTO-01 indexed under Knowledge and Content.
3.1	April 2026	Cancellation flow v2.0: cancellation-tracking Edge Function rewritten (4 routes), seal-webhook-handler patched with fn_confirm_cancellation_from_seal_v1, 6 cancellation database functions, 3 analytics views (v_cancellation_analysis, v_sku2_signal, v_cancel_intervention_effectiveness), subscription lifecycle flow updated.
3.0	April 2026	Full infrastructure audit and rewrite. Verified counts: 128 tables, 171 views, 487 functions, 96 Edge Functions, 83 pg_cron jobs, 60 triggers, 2 Cloudflare Workers. Added complete Edge Function registry by category. Added complete pg_cron job listing. Added trigger inventory. Added Customer.io integration section. Added Cloudflare Workers section. Updated SOP index with current versions (37 SOPs). Added subscription lifecycle and customer support lifecycle flows. Updated Make.com usage to reflect migrations to native Supabase (lab ingestion, courier notifications, ad spend, invoice parsing, dispatch polling). Added portals section. Added canary monitor. Removed obsolete SOP references (SOP 0X, SOP 0Y, SOP 02, SOP 04, SOP 05 renamed to current IDs).
2.0	January 2026	Complete rewrite reflecting actual architecture: SOP-MON-01 pattern, correct SOP versions, complete SOP index including customer operations, Make.com usage restrictions, batch status terminology (RELEASED not CLEARED)
1.0	October 2025	Initial systems overview

End of Systems Overview v3.2