SOP-ORD-02: 3PL Order Export v4.1¶

Automated export of allocated orders to the fulfilment partner via CSV email, with idempotent delivery, retry with exponential backoff, and MON-01 compliant health monitoring.

Document ID: SOP-ORD-02-v4.1 Version: 4.1 Status: ✅ Production Ready Last Updated: 2026-03-20 Owner: Protocol Raw Operations Replaces: SOP-ORD-02-v4.0 Review Date: 2026-06-20

Key Changes in v4.1¶

✅ Phase A adaptation: automatic ord-02-3pl-export-cycle pg_cron job disabled (active = false)
✅ Manual "Export Pack Day Orders" button added to Ops Portal Fulfillment tab
✅ Button calls fn_trigger_3pl_export(500) → export-3pl-dispatch Edge Function (same pipeline, manual trigger)
✅ Retry (ord-02-retry-exports) and monitoring (monitor-ord-02-export-health) jobs remain active
✅ Ops Portal version bumped to 4.0

Key Changes in v4.0¶

✅ All 3 Make.com scenarios deactivated, replaced by native Supabase automation (pg_cron → pg_net → Edge Function)
✅ Email delivery migrated from Gmail (via Make.com) to Resend API (via Edge Function export-3pl-dispatch)
✅ Retry logic upgraded from 3 attempts / 30 min polling to 5 attempts with exponential backoff (5m, 15m, 1h, 6h)
✅ Monitoring rebuilt to SOP-MON-01 architecture (pg_cron → pg_net → run-monitor → fn_check_3pl_export_health_v2 → ops-alerter → Slack)
✅ New dispatched outbox state separates "email sent" from future "3PL confirmed receipt"
✅ CSV payload SHA256 hash stored in outbox for duplicate detection and audit
✅ RLS enabled on outbox and allocations tables (service_role bypasses, anon blocked)
✅ Retry uses FOR UPDATE SKIP LOCKED for concurrent-safe processing
✅ All credentials removed from documentation
❌ Removed Make.com scenario configurations (no longer applicable)

Purpose¶

Export allocated orders to the fulfilment partner via CSV email attachment. Every exported order includes batch traceability data (batch_code per line item) for recall compliance. The system runs with zero manual intervention, automatically retrying failures and alerting on health issues.

Scope¶

All PAID orders that have been allocated to RELEASED batches and have not yet been exported.

Phase A note: During founder-led fulfilment, the export recipient is the founder (anton@protocolraw.co.uk). The TPL_EXPORT_EMAIL secret controls the recipient and is updated to the 3PL partner email when transitioning to Phase B.

Related SOPs: - SOP-ORD-01: Order Ingestion (upstream: order capture) - SOP-ORD-03: Dispatch Ingestion (downstream: shipment confirmation) - SOP-LAB-01: Batch Creation and Lab-to-Release (dependency: batch RELEASED status) - SOP-INV-01: Inventory Control and FEFO Allocation (dependency: order allocation) - SOP-DLV-01: Courier Watchdog (downstream: delivery tracking) - SOP-MON-01: Monitoring and Alerting Architecture (master pattern)

Architecture¶

pg_cron (every 15 min)
    ↓
fn_run_3pl_export_cycle_v1() (PostgreSQL — orchestrator)
    ↓
fn_trigger_3pl_export() (PostgreSQL — find eligible orders, generate CSV, create outbox entry)
    ↓ (via pg_net)
export-3pl-dispatch (Edge Function — send email via Resend, update outbox, mark orders)
    ↓
fn_mark_orders_exported_by_outbox() (PostgreSQL — set export_state = 'sent')
    ↓ (on failure)
fn_retry_failed_3pl_exports_v1() (pg_cron every 5 min — exponential backoff retry)
    ↓ (after 5 failures)
ops-alerter → #ops-urgent (critical alert)

Monitoring (parallel):

pg_cron (every 5 min)
    ↓
run-monitor (Edge Function — dispatch)
    ↓
fn_check_3pl_export_health_v2() (PostgreSQL — health check)
    ↓ (if threshold breached)
ops-alerter → #ops-alerts or #ops-urgent (Slack)

Phase A Operating Mode¶

During Phase A (founder-led fulfilment, Tuesday/Thursday pack days), the automatic 15-minute export cycle is disabled. Instead, exports are triggered manually via the Ops Portal.

Trigger: "Export Pack Day Orders" button in Ops Portal → Fulfillment tab

Flow:

Operator clicks "Export Pack Day Orders" (button shows live count of eligible orders)
Confirmation dialog: "Export {count} orders?"
Button calls fn_trigger_3pl_export(500) via Supabase RPC
If orders found: calls export-3pl-dispatch Edge Function with the returned outbox_id
CSV email sent via Resend to TPL_EXPORT_EMAIL (founder email in Phase A)
Orders marked as queued → sent

What remains active:

ord-02-retry-exports pg_cron job (every 5 min) — retries if email fails
monitor-ord-02-export-health pg_cron job (every 5 min) — alerts on stuck exports

What is disabled:

ord-02-3pl-export-cycle pg_cron job — automatic 15-minute export cycle

Phase B transition: Re-enable automatic exports:

SELECT cron.alter_job(job_id := 72, active := true);

Control Rules¶

1. Order Eligibility Criteria¶

An order is eligible for export when ALL conditions are met:

Criterion	Rule	Rationale
Order Status	`status = 'PAID'`	Payment confirmed
Export State	`export_state IS NULL`	Not previously processed
Address Complete	address1, city, postcode present	Fulfilment requires complete address
Allocation Status	ALL order items have allocations	Inventory confirmed available
Batch Status	ALL allocated batches = `RELEASED`	Quality control passed, lab results verified

These rules are enforced by the v_orders_ready_for_export view. No order can bypass these criteria.

2. CSV Export Format¶

The exported CSV contains these columns in exact order:

Column	Source	Required	Notes
order_reference	orders.shopify_order_id	Yes	Unique order identifier
customer_email	customers.email	Yes	For delivery notifications
customer_first_name	customers.first_name	Yes	Delivery recipient
customer_last_name	customers.last_name	Yes	Delivery recipient
customer_phone	customers.phone	Yes	For delivery contact
delivery_address_line1	customers.address_json->>'address1'	Yes	Primary address
delivery_address_line2	customers.address_json->>'address2'	No	Optional address line
delivery_city	customers.address_json->>'city'	Yes	City/town
delivery_postcode	customers.address_json->>'postcode'	Yes	Postal code
delivery_country	customers.address_json->>'country'	Yes	Default: GB
product_sku	products.sku	Yes	Product identifier
product_name	products.name	Yes	Human-readable name
quantity	order_items.qty	Yes	Units to fulfil
batch_code	batches.batch_code (via allocations)	Yes	For traceability and recalls
order_date	orders.ordered_at	Yes	Format: YYYY-MM-DD HH24:MI:SS
order_total_gbp	orders.total_inc_vat	Yes	Total order value

3. Outbox State Machine¶

State	Meaning	Transition
`pending`	Outbox entry created, email not yet sent	→ `dispatched` on Resend success, stays `pending` with incremented attempts on failure
`dispatched`	Email sent to fulfilment partner (Resend returned success)	Terminal success state
`sent`	Reserved for future: fulfilment partner has confirmed receipt	Not currently used
`failed`	Max retry attempts (5) exhausted	Terminal failure state, triggers critical alert

4. Order Export State Flow¶

State	Set By	Meaning
`NULL`	Default	Order eligible for export
`queued`	`fn_trigger_3pl_export()`	Outbox entry created, email pending
`sent`	`fn_mark_orders_exported_by_outbox()`	Email successfully dispatched

Database Schema¶

Tables and Columns¶

outbox (export-relevant columns added in v4.0)¶

-- Columns added to existing raw_ops.outbox table
ALTER TABLE raw_ops.outbox
  ADD COLUMN IF NOT EXISTS next_retry_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS csv_sha256 TEXT;

COMMENT ON COLUMN raw_ops.outbox.next_retry_at IS
  'Next eligible retry time, calculated with exponential backoff';
COMMENT ON COLUMN raw_ops.outbox.csv_sha256 IS
  'SHA256 hash of CSV payload for duplicate detection and audit';

outbox state constraint¶

ALTER TABLE raw_ops.outbox ADD CONSTRAINT chk_outbox_state
  CHECK (state IN ('pending', 'dispatched', 'sent', 'failed'));

Row Level Security¶

RLS is enabled on all tables in the export pipeline. No policies are defined. Service role bypasses RLS. Anon role is blocked.

Table	RLS Status
raw_ops.outbox	✅ Enabled
raw_ops.orders	✅ Enabled
raw_ops.order_items	✅ Enabled
raw_ops.allocations	✅ Enabled

Performance Indexes¶

-- Orders export eligibility (most critical)
CREATE INDEX idx_orders_export_eligibility
  ON raw_ops.orders(status, export_state, ordered_at)
  WHERE status = 'PAID' AND export_state IS NULL;

-- Order items lookup
CREATE INDEX idx_order_items_allocation_lookup
  ON raw_ops.order_items(order_id, id);

-- Allocations to batch lookup
CREATE INDEX idx_allocations_batch_lookup
  ON raw_ops.allocations(order_item_id, batch_id);

-- Batch status filter
CREATE INDEX idx_batches_status
  ON raw_ops.batches(status)
  WHERE status = 'RELEASED';

-- Export state tracking
CREATE INDEX idx_orders_export_state
  ON raw_ops.orders(export_state, ordered_at)
  WHERE export_state IS NOT NULL;

Views¶

v_orders_ready_for_export¶

CREATE VIEW raw_ops.v_orders_ready_for_export AS
SELECT
  o.id,
  o.shopify_order_id,
  o.customer_id,
  o.ordered_at,
  o.total_inc_vat,
  c.email,
  c.first_name,
  c.last_name,
  c.phone,
  c.address_json
FROM raw_ops.orders o
JOIN raw_ops.customers c ON o.customer_id = c.id
WHERE o.status = 'PAID'
  AND o.export_state IS NULL
  AND c.address_json IS NOT NULL
  AND c.address_json ->> 'address1' IS NOT NULL
  AND c.address_json ->> 'city' IS NOT NULL
  AND c.address_json ->> 'postcode' IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM raw_ops.order_items oi
    LEFT JOIN raw_ops.allocations a ON oi.id = a.order_item_id
    LEFT JOIN raw_ops.batches b ON a.batch_id = b.id
    WHERE oi.order_id = o.id
      AND (a.id IS NULL OR b.status != 'RELEASED')
  )
ORDER BY o.ordered_at;

v_export_queue_depth¶

CREATE OR REPLACE VIEW raw_ops.v_export_queue_depth AS
SELECT
  COUNT(*) as orders_waiting,
  MIN(ordered_at) as oldest_order_date,
  MAX(ordered_at) as newest_order_date,
  EXTRACT(EPOCH FROM (now() - MIN(ordered_at)))/3600 as oldest_wait_hours
FROM raw_ops.v_orders_ready_for_export;

v_export_health¶

CREATE OR REPLACE VIEW raw_ops.v_export_health AS
SELECT
  (SELECT orders_waiting FROM raw_ops.v_export_queue_depth) as current_queue,
  (SELECT oldest_wait_hours FROM raw_ops.v_export_queue_depth) as oldest_wait_hours,
  (SELECT COUNT(*) FROM raw_ops.outbox
   WHERE kind = '3pl_export'
     AND state = 'pending'
     AND created_at > now() - interval '1 hour') as pending_exports_1h,
  (SELECT COUNT(*) FROM raw_ops.outbox
   WHERE kind = '3pl_export'
     AND state = 'failed'
     AND created_at > now() - interval '24 hours') as failures_24h,
  (SELECT COUNT(*) FROM raw_ops.outbox
   WHERE kind = '3pl_export'
     AND state = 'pending'
     AND created_at < now() - interval '30 minutes') as stuck_exports,
  (SELECT COUNT(*) FROM raw_ops.orders
   WHERE export_state = 'queued'
     AND exported_at < now() - interval '2 hours') as stuck_orders;

v_export_metrics_daily¶

CREATE OR REPLACE VIEW raw_ops.v_export_metrics_daily AS
SELECT
  DATE(created_at) as export_date,
  COUNT(*) as total_exports,
  COUNT(*) FILTER (WHERE state = 'dispatched') as successful,
  COUNT(*) FILTER (WHERE state = 'failed') as failed,
  COUNT(*) FILTER (WHERE state = 'pending') as pending,
  SUM((payload_json->>'order_count')::int) as total_orders_exported,
  AVG(EXTRACT(EPOCH FROM (last_sent_at - created_at))) as avg_processing_seconds,
  MAX(EXTRACT(EPOCH FROM (last_sent_at - created_at))) as max_processing_seconds
FROM raw_ops.outbox
WHERE kind = '3pl_export'
GROUP BY DATE(created_at)
ORDER BY export_date DESC;

v_export_batches_recent¶

CREATE OR REPLACE VIEW raw_ops.v_export_batches_recent AS
SELECT
  id as outbox_id,
  payload_json->>'batch_id' as batch_id,
  (payload_json->>'order_count')::int as order_count,
  state,
  attempts,
  csv_sha256,
  created_at,
  first_sent_at,
  last_sent_at,
  CASE
    WHEN state = 'dispatched' THEN EXTRACT(EPOCH FROM (first_sent_at - created_at))
    ELSE NULL
  END as processing_seconds,
  error_message
FROM raw_ops.outbox
WHERE kind = '3pl_export'
ORDER BY created_at DESC
LIMIT 50;

Functions¶

fn_trigger_3pl_export() — Find eligible orders, generate CSV, create outbox entry¶

CREATE OR REPLACE FUNCTION raw_ops.fn_trigger_3pl_export(batch_size integer DEFAULT 50)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
DECLARE
  v_order_ids uuid[];
  v_csv_data jsonb;
  v_outbox_id uuid;
  v_order_count integer;
  v_batch_id text;
  v_csv_hash text;
BEGIN
  -- 1. Find eligible orders
  SELECT ARRAY_AGG(id)
  INTO v_order_ids
  FROM raw_ops.v_orders_ready_for_export
  LIMIT batch_size;

  IF v_order_ids IS NULL OR array_length(v_order_ids, 1) = 0 THEN
    RETURN jsonb_build_object(
      'success', true,
      'message', 'No eligible orders found',
      'order_count', 0
    );
  END IF;

  v_order_count := array_length(v_order_ids, 1);
  v_batch_id := to_char(now(), 'YYYY-MM-DD-HH24MI');

  -- 2. Generate CSV data
  SELECT jsonb_agg(row_to_json(t))
  INTO v_csv_data
  FROM raw_ops.fn_generate_3pl_export_data(v_order_ids) t;

  -- 3. Compute SHA256 hash for audit
  v_csv_hash := encode(digest(v_csv_data::text, 'sha256'), 'hex');

  -- 4. Create outbox entry with idempotency key
  INSERT INTO raw_ops.outbox (
    kind, target, event_type, payload_json,
    manifest_version, state, idempotency_key, csv_sha256
  )
  VALUES (
    '3pl_export', '3pl', 'order_export',
    jsonb_build_object(
      'batch_id', v_batch_id,
      'order_ids', v_order_ids,
      'order_count', v_order_count,
      'csv_rows', v_csv_data
    ),
    'v1', 'pending',
    '3pl_export_' || v_batch_id,
    v_csv_hash
  )
  RETURNING id INTO v_outbox_id;

  -- 5. Mark orders as QUEUED
  UPDATE raw_ops.orders
  SET export_state = 'queued', exported_at = now()
  WHERE id = ANY(v_order_ids);

  RETURN jsonb_build_object(
    'success', true,
    'outbox_id', v_outbox_id,
    'batch_id', v_batch_id,
    'order_count', v_order_count,
    'csv_data', v_csv_data,
    'order_ids', v_order_ids
  );

EXCEPTION WHEN OTHERS THEN
  RETURN jsonb_build_object('success', false, 'error', SQLERRM);
END;
$function$;

fn_mark_orders_exported_by_outbox() — Mark orders as sent after successful dispatch¶

CREATE OR REPLACE FUNCTION raw_ops.fn_mark_orders_exported_by_outbox(p_outbox_id uuid)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
DECLARE
  v_order_ids uuid[];
  v_updated_count integer;
BEGIN
  SELECT ARRAY(
    SELECT jsonb_array_elements_text(payload_json->'order_ids')::uuid
  )
  INTO v_order_ids
  FROM raw_ops.outbox
  WHERE id = p_outbox_id;

  UPDATE raw_ops.orders
  SET export_state = 'sent'
  WHERE id = ANY(v_order_ids)
    AND export_state = 'queued';

  GET DIAGNOSTICS v_updated_count = ROW_COUNT;

  RETURN jsonb_build_object(
    'success', true,
    'updated_count', v_updated_count,
    'order_ids', v_order_ids
  );

EXCEPTION WHEN OTHERS THEN
  RETURN jsonb_build_object('success', false, 'error', SQLERRM);
END;
$function$;

fn_run_3pl_export_cycle_v1() — Orchestrator (replaces Make.com Scenario 1)¶

CREATE OR REPLACE FUNCTION raw_ops.fn_run_3pl_export_cycle_v1()
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_export_result jsonb;
  v_outbox_id uuid;
BEGIN
  -- 1. Trigger export (finds eligible orders, creates outbox entry)
  SELECT raw_ops.fn_trigger_3pl_export(50) INTO v_export_result;

  -- 2. If no orders, return early
  IF (v_export_result->>'order_count')::int = 0 THEN
    RETURN jsonb_build_object('success', true, 'message', 'No eligible orders', 'order_count', 0);
  END IF;

  -- 3. Get the outbox_id
  v_outbox_id := (v_export_result->>'outbox_id')::uuid;

  -- 4. Call the dispatch Edge Function via pg_net
  PERFORM net.http_post(
    url := '<SUPABASE_URL>/functions/v1/export-3pl-dispatch',
    headers := '{"Content-Type": "application/json", "apikey": "<ANON_KEY>"}'::jsonb,
    body := jsonb_build_object('outbox_id', v_outbox_id)
  );

  RETURN jsonb_build_object(
    'success', true,
    'outbox_id', v_outbox_id,
    'order_count', (v_export_result->>'order_count')::int,
    'batch_id', v_export_result->>'batch_id'
  );

EXCEPTION WHEN OTHERS THEN
  RETURN jsonb_build_object('success', false, 'error', SQLERRM);
END;
$$;

fn_retry_failed_3pl_exports_v1() — Retry with exponential backoff (replaces Make.com Scenario 2)¶

CREATE OR REPLACE FUNCTION raw_ops.fn_retry_failed_3pl_exports_v1()
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_entry RECORD;
  v_retried integer := 0;
BEGIN
  FOR v_entry IN
    SELECT id
    FROM raw_ops.outbox
    WHERE kind = '3pl_export'
      AND (state = 'failed' OR (state = 'pending' AND attempts > 0))
      AND attempts < 5
      AND (next_retry_at IS NULL OR next_retry_at <= now())
      AND created_at > now() - interval '48 hours'
    ORDER BY created_at ASC
    LIMIT 5
    FOR UPDATE SKIP LOCKED
  LOOP
    PERFORM net.http_post(
      url := '<SUPABASE_URL>/functions/v1/export-3pl-dispatch',
      headers := '{"Content-Type": "application/json", "apikey": "<ANON_KEY>"}'::jsonb,
      body := jsonb_build_object('outbox_id', v_entry.id)
    );
    v_retried := v_retried + 1;
  END LOOP;

  RETURN jsonb_build_object('retried', v_retried);
END;
$$;

Edge Function: export-3pl-dispatch¶

Location: supabase/functions/export-3pl-dispatch/index.ts

Purpose: Fetch outbox entry, build CSV, send email via Resend with CSV attachment, update outbox state, mark orders as exported. Idempotent: safe to invoke multiple times for the same outbox_id.

Required Secrets:

Secret	Purpose	Example
`RESEND_API_KEY`	Resend API authentication	(set in Supabase)
`TPL_EXPORT_EMAIL`	Fulfilment partner email address	`anton@protocolraw.co.uk` (Phase A)
`EXPORT_FROM_EMAIL`	Sender address (optional, defaults to `ops@protocolraw.co.uk`)	`ops@protocolraw.co.uk`

Behaviour:

Receives { "outbox_id": "uuid" } via POST
Fetches outbox entry from database
Idempotency check: If state is dispatched or sent, returns success with duplicate: true (no second email)
Builds CSV string from payload_json.csv_rows
Sends email via Resend API with CSV as base64-encoded attachment
On success: updates outbox to dispatched, calls fn_mark_orders_exported_by_outbox(), logs to ops_events
On failure: increments attempts, calculates next retry time (exponential backoff), stays pending or moves to failed at max attempts
At max attempts (5): fires critical alert to #ops-urgent via ops-alerter

Exponential Backoff Schedule:

Attempt	Backoff	Cumulative Wait
1	Immediate	0 min
2	5 min	5 min
3	15 min	20 min
4	1 hour	1h 20m
5	6 hours	7h 20m

Automation Schedule¶

pg_cron Jobs¶

Job Name	Schedule	Function	Status	Replaces
`ord-02-3pl-export-cycle`	`/15 * * *`	`fn_run_3pl_export_cycle_v1()`	⏸️ Disabled (Phase A)	Make.com "3PL Order Export"
`ord-02-retry-exports`	`/5 * * *`	`fn_retry_failed_3pl_exports_v1()`	✅ Active	Make.com "3PL Export - Retry Failed"
`monitor-ord-02-export-health`	`/5 * * *`	`fn_check_3pl_export_health_v2()` via `run-monitor`	✅ Active	Make.com "3PL Export - Health Monitor"

-- Export cycle: every 15 minutes
SELECT cron.schedule(
  'ord-02-3pl-export-cycle',
  '*/15 * * * *',
  $$ SELECT raw_ops.fn_run_3pl_export_cycle_v1(); $$
);

-- Retry: every 5 minutes
SELECT cron.schedule(
  'ord-02-retry-exports',
  '*/5 * * * *',
  $$ SELECT raw_ops.fn_retry_failed_3pl_exports_v1(); $$
);

-- Health monitor: every 5 minutes (via run-monitor Edge Function)
SELECT cron.schedule(
  'monitor-ord-02-export-health',
  '*/5 * * * *',
  $$
  SELECT net.http_post(
    url := '<SUPABASE_URL>/functions/v1/run-monitor',
    headers := '{"Content-Type": "application/json", "apikey": "<ANON_KEY>"}'::jsonb,
    body := '{"check": "3pl_export_health"}'::jsonb
  );
  $$
);

Monitoring¶

Health Check: fn_check_3pl_export_health_v2()¶

Follows SOP-MON-01 pattern. Registered in the run-monitor Edge Function dispatch map as "3pl_export_health".

Metrics checked:

Metric	Source	Warning Threshold	Critical Threshold
Queue depth	`v_export_health.current_queue`	> 200	N/A
Oldest wait	`v_export_health.oldest_wait_hours`	> 2h	> 2h with stuck exports
Stuck exports	`v_export_health.stuck_exports`	> 0	> 3, or any stuck > 2h
Failures (24h)	`v_export_health.failures_24h`	> 0	> 5

Alert routing:

Condition	Channel	Severity
1-3 stuck exports, < 2h old	#ops-alerts	warning
> 3 stuck, or any stuck > 2h, or > 5 failures	#ops-urgent	critical
Queue > 200 or oldest wait > 2h	#ops-alerts	warning
Any failures in 24h	#ops-alerts	warning

Monitoring runs logged to: raw_ops.monitoring_runs with check_name = '3pl_export_health'

Verification Queries¶

-- Current health snapshot
SELECT * FROM raw_ops.v_export_health;

-- Recent monitoring runs
SELECT * FROM raw_ops.monitoring_runs
WHERE check_name = '3pl_export_health'
ORDER BY run_at DESC LIMIT 10;

-- Orders awaiting export
SELECT COUNT(*) FROM raw_ops.v_orders_ready_for_export;

-- Exported orders today
SELECT COUNT(*)
FROM raw_ops.orders
WHERE export_state = 'sent'
  AND DATE(exported_at) = CURRENT_DATE;

-- Daily metrics (last 7 days)
SELECT * FROM raw_ops.v_export_metrics_daily
WHERE export_date >= CURRENT_DATE - 7;

-- Recent export batches
SELECT * FROM raw_ops.v_export_batches_recent LIMIT 20;

-- Queue depth
SELECT * FROM raw_ops.v_export_queue_depth;

-- pg_cron job status
SELECT jobname, schedule, active
FROM cron.job
WHERE jobname LIKE 'ord-02%' OR jobname LIKE 'monitor-ord-02%'
ORDER BY jobname;

-- Recent cron executions
SELECT j.jobname, d.start_time, d.end_time, d.status, d.return_message
FROM cron.job_run_details d
JOIN cron.job j ON d.jobid = j.jobid
WHERE j.jobname LIKE 'ord-02%' OR j.jobname LIKE 'monitor-ord-02%'
ORDER BY d.start_time DESC LIMIT 20;

Operational Procedures¶

Normal Operation (Phase A — Manual Export)¶

On pack day (Tuesday/Thursday), open Ops Portal → Fulfillment tab
Check the "orders ready for export" count
Click "Export Pack Day Orders" and confirm
One CSV email arrives with all eligible orders
Use CSV as pick list for packing
After packing, upload dispatch confirmation CSV (SOP-ORD-03 processes it)
ord-02-retry-exports handles any email delivery failures automatically
monitor-ord-02-export-health alerts if anything is stuck

Normal Operation (Phase B — Automatic)¶

ord-02-3pl-export-cycle pg_cron job fires every 15 minutes
Calls fn_run_3pl_export_cycle_v1() which finds up to 50 eligible orders
If orders found: creates outbox entry (state pending), marks orders as queued, triggers export-3pl-dispatch Edge Function via pg_net
Edge Function builds CSV, sends email via Resend, updates outbox to dispatched, marks orders as sent
If no orders found: function returns cleanly with order_count: 0
ord-02-retry-exports runs every 5 minutes, picking up any failed or stuck entries with exponential backoff
monitor-ord-02-export-health runs every 5 minutes via run-monitor, alerting on threshold breaches

Manual Export (Emergency)¶

If the pg_cron system is unavailable, trigger an export manually:

-- Trigger export cycle (finds orders, creates outbox, dispatches)
SELECT raw_ops.fn_run_3pl_export_cycle_v1();

If the Edge Function is unavailable, extract CSV data directly:

-- Get raw CSV data for manual email
SELECT raw_ops.fn_trigger_3pl_export(10);
-- Copy csv_data from the result and email manually

Manual Retry¶

-- Retry a specific failed outbox entry
-- (calls Edge Function which handles idempotency)
SELECT net.http_post(
  url := '<SUPABASE_URL>/functions/v1/export-3pl-dispatch',
  headers := '{"Content-Type": "application/json", "apikey": "<ANON_KEY>"}'::jsonb,
  body := '{"outbox_id": "<OUTBOX_ID>"}'::jsonb
);

Error Handling¶

Common Issues¶

Issue	Cause	Resolution
No orders exported	Orders not allocated or batches not RELEASED	Check allocation status and batch status via `v_orders_ready_for_export`. Run SOP-INV-01 if needed
Resend delivery failed	Invalid API key, recipient rejected, rate limited	Check `error_message` in outbox. Verify `RESEND_API_KEY` and `TPL_EXPORT_EMAIL` secrets
Outbox stuck in `pending`	Edge Function failed to update state	Check Edge Function logs. Manual fix: retry via `export-3pl-dispatch` with the outbox_id
Orders stuck in `queued`	Outbox entry created but dispatch failed	Retry function will pick these up. If persistent, check Edge Function health
Order exported twice	Should not happen (idempotency key prevents duplicate outbox entries, Edge Function checks state before sending)	Verify outbox for duplicates. If needed: contact fulfilment partner, reset `export_state`
Max retries exhausted	Persistent Resend or recipient issue	Critical alert fires to #ops-urgent. Investigate root cause, fix, then reset outbox entry for retry

Rollback Procedure¶

If an order was exported incorrectly:

Contact fulfilment partner immediately to cancel (Phase A: check your own inbox)

Reset order export state:

UPDATE raw_ops.orders
SET export_state = NULL, exported_at = NULL
WHERE id = '<order_id>';

INSERT INTO raw_ops.ops_events (entity_type, entity_id, kind, message)
VALUES ('order', '<order_id>', 'MANUAL', 'Export state reset for re-export');

Verify allocation is correct
Order will be re-exported on next manual export (Phase A) or next 15-minute cycle (Phase B)

Important: Never manually edit allocation or batch data without logging to ops_events. All changes must be auditable for recall compliance.

KPIs¶

KPI	Target	Enforced By
Export success rate	100% of eligible orders exported within 15 minutes	`ord-02-3pl-export-cycle` schedule
Batch traceability	100% of exported orders have valid batch_code	Database constraints and eligibility view
Allocation compliance	0 orders exported without allocation	`v_orders_ready_for_export` view
Quality control	0 orders exported with non-RELEASED batches	`v_orders_ready_for_export` view
Queue depth	< 200 orders waiting	Monitored by `fn_check_3pl_export_health_v2`
Stuck exports	0 exports stuck > 30 minutes	Monitored by `fn_check_3pl_export_health_v2`
Failure rate	< 5 failures per 24 hours	Monitored by `fn_check_3pl_export_health_v2`

Dependencies¶

Dependency	Required For	SLA
SOP-INV-01 (FEFO Allocation)	Orders must be allocated before export	N/A (internal)
SOP-LAB-01 (Batch QA)	Batches must be RELEASED before orders can export	N/A (internal)
Supabase (PostgreSQL + Edge Functions)	Database, pg_cron, Edge Functions	99.9%
Resend API	Email delivery	99.9%
pgcrypto extension	SHA256 hash generation	N/A (database extension)
pg_net extension	Async HTTP calls from pg_cron	N/A (database extension)

System Capacity and Scaling¶

Current Capacity¶

Database: Query performance <1ms at current scale. Indexed for 100k+ customers.

Export throughput: 50 orders per cycle, every 15 minutes = 200 orders/hour sustained, 4,800 orders/day.

Resend: 100 emails/second API limit. Each export cycle sends 1 email with CSV attachment.

Scaling Triggers¶

Threshold	Action
5,000 orders/day	Increase cycle frequency to every 10 minutes
10,000 orders/day	Increase batch_size to 100 orders per cycle
20,000 orders/day	Add SFTP delivery as alternative to email
50,000+ orders/day	Direct API integration with 3PL

Known Limitations¶

Resend attachment limit: ~25MB per email (supports approximately 5,000 orders per CSV).

Batch ID format: Timestamp-based (YYYY-MM-DD-HH24MI). At high volume with multiple exports per minute, consider UUID-based manifest IDs.

Deactivated Make.com Scenarios¶

The following Make.com scenarios were replaced in v4.0. They are deactivated but retained for reference.

Scenario	Schedule	Replaced By
"3PL Order Export - v3.0 (Scale Ready)"	Every 15 min	`ord-02-3pl-export-cycle` pg_cron job
"3PL Export - Retry Failed"	Every 30 min	`ord-02-retry-exports` pg_cron job
"3PL Export - Health Monitor"	Every 5 min	`monitor-ord-02-export-health` pg_cron job

Do not delete these scenarios. They serve as reference for the original implementation and as a fallback if emergency rollback is needed.

Complete Object Inventory¶

Object	Type	Status
`raw_ops.v_orders_ready_for_export`	View	Unchanged from v3.0
`raw_ops.v_export_queue_depth`	View	Unchanged from v3.0
`raw_ops.v_export_health`	View	Unchanged from v3.0
`raw_ops.v_export_metrics_daily`	View	Updated: `dispatched` replaces `sent` in success filter
`raw_ops.v_export_batches_recent`	View	Updated: includes `csv_sha256`, uses `dispatched` state
`raw_ops.fn_generate_3pl_export_data()`	Function	Unchanged from v3.0
`raw_ops.fn_trigger_3pl_export()`	Function	Updated: SHA256 hash computed and stored
`raw_ops.fn_mark_orders_exported_by_outbox()`	Function	Unchanged from v3.0
`raw_ops.fn_run_3pl_export_cycle_v1()`	Function	New in v4.0 — orchestrator
`raw_ops.fn_retry_failed_3pl_exports_v1()`	Function	New in v4.0 — exponential backoff retry
`raw_ops.fn_check_3pl_export_health_v2()`	Function	New in v4.0 — MON-01 compliant monitor
`export-3pl-dispatch`	Edge Function	New in v4.0 — Resend email delivery
`ord-02-3pl-export-cycle`	pg_cron job	New in v4.0 — every 15 min
`ord-02-retry-exports`	pg_cron job	New in v4.0 — every 5 min
`monitor-ord-02-export-health`	pg_cron job	New in v4.0 — every 5 min
`outbox.next_retry_at`	Column	New in v4.0
`outbox.csv_sha256`	Column	New in v4.0
`chk_outbox_state`	Constraint	Updated in v4.0 — includes `dispatched`
Ops Portal: Pack Day Export button	UI (Fulfillment tab)	New in v4.1 — manual export trigger

Future Enhancements¶

Enhancement	Trigger	Description
3PL confirmation loop	Phase B (3PL onboarded)	Add `confirmed` state when 3PL acknowledges receipt. Currently `dispatched` is terminal success
SFTP/API delivery	20k+ orders/day	Replace email+CSV with SFTP upload or direct API integration
UUID manifest IDs	High export frequency	Replace timestamp-based batch_id with UUID + explicit manifest table
Metabase dashboard	Post-launch	Export volume trend, failure rate, queue depth, average dispatch latency
Allocation warning alerts	Phase A enhancement	Make.com scenario to alert on `allocation_warning` events (15-min check, Slack #ops-urgent)

Version History¶

Version	Date	Changes	Author
4.1	2026-03-20	Phase A adaptation: disabled automatic export cycle, added manual "Export Pack Day Orders" button to Ops Portal Fulfillment tab. Retry and monitoring remain active.	Protocol Raw Ops
4.0	2026-03-19	Migrated from Make.com to native Supabase (pg_cron + Edge Functions). Email delivery via Resend. Exponential backoff retry (5 attempts). MON-01 compliant monitoring. `dispatched` outbox state. CSV SHA256 hashing. RLS on outbox and allocations. Renamed from SOP-ORD-02 to SOP-ORD-02	Protocol Raw Ops
3.0	2025-10-30	Added outbox pattern for idempotency. Automated retry (Make.com, max 3 attempts). Health monitoring (Make.com). Gmail error handler. 5 monitoring views. Scale-ready implementation	Protocol Raw Ops
2.0	2025-10-23	Added batch_code to CSV export. Allocation enforcement. Batch status check (RELEASED only)	Protocol Raw Ops
1.3	2025-10-15	Initial version	Protocol Raw Ops

End of SOP-ORD-02 v4.1

Last reviewed: 2026-03-20 Next review: 2026-06-20