Skip to content

SOP 0Y: Dispatch Confirmation Processing v2.0

Status: ✅ Production Ready for 100k+ Customers
Last Updated: 2025-10-31
Owner: Protocol Raw Operations
Document ID: SOP-0Y-v2.0
Replaces: Manual dispatch tracking
Review Date: 2026-01-31

Purpose

Automated processing of dispatch confirmation CSV files from 3PL or internal fulfillment, updating order status to FULFILLED and creating shipment records with tracking information. System scales from self-fulfillment to 3PL partners to in-house warehouse operations.

Scope

All CSV files uploaded to the dispatch-files/inbox/ Storage bucket containing dispatch confirmations with valid order references and tracking numbers.

Applies To: - 3PL dispatch confirmations - Internal fulfillment (via fulfillment.html interface) - Future in-house warehouse operations - Batch dispatch processing

✅ Key Features - v2.0 (Scale-Ready)

NEW in v2.0: - Batch processing: Handle multiple orders per CSV file - Fulfillment interface: Brand-aligned HTML interface for internal use - Order lookup: Automatic fetching of order details to prevent re-keying - Duplicate prevention: Skip already processed dispatches - Error handling: Detailed logging for failed processing - Archive system: Processed files moved to archive with timestamps

Core Features: - Automated processing: Files processed within 5 minutes of upload - Multi-format support: Individual dispatches or batch files - Tracking integration: Links dispatch to shipment tracking - Order state management: Updates orders from 'sent' to 'fulfilled' - Audit trail: Complete processing logs for compliance

Control Rules

1. CSV File Format Requirements

Required CSV Structure: 

external_order_id,dispatch_time_utc,courier,service,tracking_number,sku,qty,parcel_count,notes,status
12294292865399,2025-10-31T14:30:00Z,DPD,Next Day,DPD123456789GB,STARTER-8KG,1,1,,DISPATCHED

Field Specifications:

Field Type Required Description Example
external_order_id String Yes Shopify Order ID 12294292865399
dispatch_time_utc ISO DateTime Yes When dispatched (UTC) 2025-10-31T14:30:00Z
courier String Yes Courier company DPD, Royal Mail, Evri, UPS
service String No Service level Next Day, Standard
tracking_number String Yes Tracking reference DPD123456789GB
sku String Yes Product SKU STARTER-8KG
qty Integer Yes Quantity dispatched 1
parcel_count Integer No Number of parcels 1
notes String No Additional notes
status String Yes Must be 'DISPATCHED' DISPATCHED

2. File Processing Rules

File Location: dispatch-files/inbox/ Supported Formats: .csv files only Processing Frequency: Every 5 minutes via Make.com cron File Size Limit: 10MB per file Batch Size: Unlimited orders per file

3. Order State Management

Eligible Orders: - Order must exist in system with matching shopify_order_id - Order must be in export_state = 'sent' (exported to 3PL) - Order must not already have shipment record

State Transitions: 

PAID → allocated → QUEUED → SENT → FULFILLED
                            ↑      ↑
                        SOP 0X   SOP 0Y

Technical Implementation

Database Components

1. Storage Structure

File Locations: 

dispatch-files/
├── inbox/          # New files to process
└── archive/        # Processed files with timestamps

2. Core Processing Function

Function: fn_process_dispatch_csv(file_content TEXT)

-- Processes CSV content and creates shipment records
-- Updates order status to FULFILLED
-- Returns processing summary with success/error counts
-- Skips duplicate dispatches for same order

Key Logic: 1. Parse CSV content into structured data 2. Validate required fields and data types 3. Check order exists and is in 'sent' state 4. Create shipment record with tracking info 5. Update order status to 'fulfilled' 6. Log any errors for review

3. Monitoring Views

-- Recent dispatch processing
SELECT * FROM raw_ops.v_dispatch_processing_recent;

-- Orders awaiting dispatch
SELECT COUNT(*) FROM raw_ops.orders WHERE export_state = 'sent';

-- Daily fulfillment metrics
SELECT 
  DATE(dispatched_at) as dispatch_date,
  COUNT(*) as orders_fulfilled,
  COUNT(DISTINCT courier) as couriers_used
FROM raw_ops.shipments 
WHERE dispatched_at >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY DATE(dispatched_at);

Make.com Automation

Scenario: "Process Dispatch Files" (Every 5 Minutes)

Schedule: Every 5 minutes

Flow: 1. Supabase Storage → List files in dispatch-files/inbox/ 2. Iterator → Loop through each CSV file 3. Supabase Storage → Download file content 4. HTTP → Call fn_process_dispatch_csv() with content 5. Router → Check if processing successful 6. Supabase Storage → Move file to archive/ folder 7. Slack → Alert if processing errors (optional)

Module Configurations:

Module 1: List Storage Files 

Bucket: dispatch-files
Folder: inbox
File Type: csv

Module 2: Download File Content 

Bucket: dispatch-files
File Path: {{1.name}}
Output: Text content

Module 3: Process CSV 

URL: https://znfjpllsiuyezqlneqzr.supabase.co/rest/v1/rpc/fn_process_dispatch_csv
Method: POST
Headers:
  - apikey: [service-role-key]
  - Authorization: Bearer [service-role-key]
  - Content-Type: application/json
  - Accept-Profile: raw_ops
Body: {"file_content": "{{2.data}}"}

Module 4: Archive File 

Action: Move file
From: dispatch-files/inbox/{{1.name}}
To: dispatch-files/archive/{{formatDate(now; "YYYY-MM-DD_HH-mm-ss")}}_{{1.name}}

Fulfillment Interface

Brand-Aligned HTML Interface

File: fulfillment.html (Protocol Raw branded) Access: Local file or hosted solution Password: protocol2025 (for basic security)

Key Features: - Order lookup: Enter Order ID → Fetch customer/product details - Smart forms: Only enter tracking number + courier selection - Batch mode: Collect multiple dispatches before submission - Real-time validation: Duplicate detection and error handling - Brand styling: Full Protocol Raw visual identity

Workflow: 1. Lookup Order: Enter Order ID → System shows customer, products, batch codes 2. Add Tracking: Select courier, enter tracking number 3. Add to Batch: Multiple orders collected in session 4. Submit Batch: Creates single CSV file in dispatch-files/inbox/ 5. Auto-Processing: SOP 0Y processes within 5 minutes

Fulfillment Workflow (Frozen Food)

Operational Process: 1. Receive SOP 0X export → CSV with orders to fulfill 2. Book courier collection → Plan capacity and timing 3. Batch pick & pack → Maintain cold chain integrity 4. Create shipping labels → Get tracking numbers from courier 5. Record dispatches → Use fulfillment.html interface 6. Submit batch → Automated processing via SOP 0Y

Error Handling & Monitoring

Common Issues

Issue Cause Resolution
Order not found Invalid order ID in CSV Check Shopify order exists, verify ID format
Order already fulfilled Duplicate dispatch record Skip duplicate, log warning
Missing tracking number Empty or invalid tracking field Reject row, require valid tracking
Wrong order state Order not in 'sent' status Ensure order exported via SOP 0X first
CSV format error Wrong headers or field count Validate CSV structure, check template

Monitoring Queries

-- Check processing health
SELECT 
  COUNT(*) as total_processed,
  COUNT(*) FILTER (WHERE success = true) as successful,
  COUNT(*) FILTER (WHERE success = false) as failed
FROM raw_ops.dispatch_processing_log 
WHERE created_at >= CURRENT_DATE;

-- Orders stuck in 'sent' state
SELECT COUNT(*) 
FROM raw_ops.orders 
WHERE export_state = 'sent' 
  AND exported_at < CURRENT_DATE - INTERVAL '7 days';

-- Recent shipments
SELECT 
  shopify_order_id,
  courier,
  tracking_number,
  dispatched_at
FROM raw_ops.shipments 
ORDER BY dispatched_at DESC 
LIMIT 20;

Manual Recovery

If CSV processing fails:

  1. Check error logs: 

    SELECT * FROM raw_ops.dispatch_processing_log 
WHERE success = false 
ORDER BY created_at DESC;

  2. Fix data issues in CSV file

  3. Re-upload to dispatch-files/inbox/
  4. Monitor processing in next 5-minute cycle

If order stuck in wrong state: 

-- Reset order for re-processing (use carefully)
UPDATE raw_ops.orders 
SET export_state = 'sent'
WHERE shopify_order_id = '[order_id]';

Integration Points

SOP 0X Integration

  • Receives orders exported as 'sent' status
  • Processes dispatches to update to 'fulfilled' status
  • Maintains sequence of order lifecycle

3PL Integration

  • Accepts standard CSV format from any 3PL
  • Flexible courier support (DPD, Royal Mail, Evri, UPS, etc.)
  • Batch processing handles high-volume 3PL files

Future Warehouse Integration

  • Same CSV interface for in-house fulfillment
  • Barcode scanner support via fulfillment.html
  • Multi-station workflow using shared interface
  • No system changes required for warehouse transition

KPIs and Monitoring

Processing Metrics

  • Target: 100% of dispatch files processed within 5 minutes
  • Current: Real-time processing via Make.com automation

Order Fulfillment Rate

  • Target: >95% of 'sent' orders fulfilled within 48 hours
  • Monitoring: Daily alerts for orders stuck >7 days

Data Quality

  • Target: <1% processing errors per batch
  • Tracking: Error logs and weekly review process

Courier Performance

  • Tracking: Dispatch time by courier
  • Reporting: Weekly courier performance review

System Capacity & Scaling

Current Capacity (Verified Oct 2025)

File Processing: - Volume: 1000+ orders per CSV file - Frequency: Every 5 minutes (12x per hour) - Daily capacity: 17,000+ orders/day

Database Performance: - Processing time: <5 seconds per 50-order file - Storage: Unlimited CSV archive retention - Scaling: Auto-scales with Supabase

Scaling Evolution

Phase 1: Self-Fulfillment (Months 1-6) - Manual dispatch via fulfillment.html - 5-50 orders per batch - Twice-weekly dispatch cycles

Phase 2: 3PL Partner (Months 6-18) - Automated CSV uploads from 3PL - 100-500 orders per file - Daily or real-time processing

Phase 3: In-House Warehouse (18+ months) - Multiple fulfillment stations - Barcode scanner integration - 1000+ orders per day capacity

No system changes required between phases - same CSV interface scales across all fulfillment models.

Dependencies

  • SOP 0X: Orders must be exported before dispatch processing
  • Make.com availability: File processing depends on automation (99.9% SLA)
  • Supabase Storage: File upload/download functionality
  • Courier systems: Tracking number generation and validation

Brand Alignment

Fulfillment Interface Styling

  • Typography: Montserrat Bold headlines, Inter Regular body
  • Colors: Protocol Raw palette (Terracotta, Nandor, Old Lace)
  • Messaging: "The systematic approach to dispatch recording"
  • Footer: "Verified safe, batch by batch"

Operational Consistency

  • No manual re-keying: System fetches existing order data
  • Batch traceability: Links dispatches to batch codes
  • Evidence-based: Complete audit trail for all dispatches
  • Systematic process: Consistent workflow across fulfillment models

Change Log

Version Date Changes Author
2.0 2025-10-31 • Added fulfillment.html interface
• Added order lookup functionality
• Added batch processing capability
• Added Protocol Raw brand styling
• Added frozen food workflow guidance
• Added error handling and monitoring
• Updated for 3PL → in-house transition
• Complete production deployment		 Protocol Raw Ops
1.0 2025-10-25 • Initial CSV processing implementation
• Basic file upload/archive system
• Core dispatch confirmation logic		 Protocol Raw Ops

Operational Procedures

Daily Operations

No manual intervention required - system runs automatically

Optional monitoring: 1. Check Slack alerts for any processing errors 2. Review daily fulfillment metrics 3. Monitor orders stuck in 'sent' status

Weekly Review

  1. Review error logs for any systematic issues
  2. Check courier performance across different providers
  3. Validate processing metrics against targets
  4. Archive old CSV files if storage management needed

Troubleshooting

File not processing: 1. Check file is in correct dispatch-files/inbox/ location 2. Verify CSV format matches template exactly 3. Check Make.com scenario is running (every 5 minutes) 4. Review error logs for specific issues

Order not updating: 1. Verify order exists with correct Shopify ID 2. Check order is in 'sent' status (exported via SOP 0X) 3. Confirm no existing shipment record 4. Validate CSV row has all required fields

Security & Compliance

Data Protection

  • Service role authentication for all database operations
  • Encrypted storage for all CSV files
  • Audit logs for all processing activities
  • No PII exposure in error messages

Batch Traceability

  • Complete chain from order → allocation → batch → dispatch
  • Recall capability via batch code tracking
  • Compliance logs for regulatory requirements

Access Control

  • Password-protected fulfillment interface
  • Role-based database permissions
  • Encrypted API communications

Approval

Prepared by: Protocol Raw Operations Team
Reviewed by: Pending
Approved by: Pending
Effective Date: 2025-10-31
Status: ✅ Production Ready for 100,000+ Customers

End of SOP 0Y v2.0

Last reviewed: 2025-10-31
Next review: 2026-01-31
System status: ✅ Production Ready - Multi-Modal Fulfillment Support - Zero Manual Intervention Required