SOP 01: Batch Creation & Lab-to-Release v4.3¶
Complete workflow from batch creation through quality release
Document ID: SOP-01-v4.3
Version: 4.3
Status: ✅ Production Ready
Last Updated: 2026-02-07
Owner: Protocol Raw Operations
Replaces: SOP-01-v4.2
Review Date: 2026-05-07
Key Changes in v4.3¶
Proof Portal Language Update: - ✅ "All clear." replaces "Verified Safe" as primary status message - ✅ "Hygiene" replaces "Enterobacteriaceae" in customer-facing test cards - ✅ Consistent terminology across homepage showcase and Proof Portal - ✅ Wax seal design - New signature verification badge
Unchanged from v4.2:
- ✅ All batch creation, release, allocation logic unchanged
- ✅ Database column names unchanged (still enterobacteriaceae_cfu_per_g)
- ✅ Lab certificate processing unchanged
- ✅ QR code and pouch label generation unchanged
Key Changes in v4.2¶
Insert Card Simplification: - ⌠Removed dynamic proof insert PDF generation - No longer generated per-batch - ✅ Insert card is now universal - Static card, bulk printed, same for all boxes - ✅ Proof access simplified - Via pouch QR + delivery SMS + email - ✅ Batch creation faster - One less PDF to generate per batch
Database Changes:
- Deprecated proof_insert_pdf_url column (no longer populated)
- All other batch columns unchanged
Unchanged from v4.1: - ✅ QR code generation for pouch labels - ✅ Pouch label PDF generation for co-packer - ✅ Co-packer email automation - ✅ Lab email processing via Make.com - ✅ All batch creation, release, allocation logic
Key Changes in v4.1¶
Monitoring Migration:
- ✅ Native Supabase monitoring - All alerts now run via pg_cron + Edge Functions
- ✅ Centralised architecture - See SOP-MON-01 for monitoring system details
- ✅ Unified logging - All monitor runs logged to raw_ops.monitoring_runs
- ✅ Improved frequencies - Lab SLA now every 15 min (was 2 hours)
Unchanged from v4.0: - ✅ Lab email processing still uses Make.com - ✅ All batch creation, release, allocation logic unchanged - ✅ Proof portal and QR code generation unchanged
Key Changes in v4.0¶
Automation & Security: - ✅ Auto-generated batch codes - Edge Function creates codes, no manual entry - ✅ Dual ID system - Internal sequential codes + public hashed IDs - ✅ Automatic QR code generation - Links to proof portal - ✅ Automatic proof page creation - Customer verification system - ✅ Automatic label PDF generation - Print-ready pouch labels for co-packer (Note: v4.2 removed separate proof insert PDF) - ✅ Co-packer email automation - Confirmation with label PDF attached
Maintained from v3.0: - ✅ All existing lab automation unchanged - ✅ Automated batch release logic preserved - ✅ Auto-allocation on release still works - ✅ Formulation integration intact
Time Savings: - OLD (v3.0): ~15 minutes per batch (manual code entry, no QR, no proof page) - NEW (v4.0): ~30 seconds per batch (only enter date + kg, everything else automated) - Savings: 14.5 minutes per batch
Purpose¶
This SOP defines the complete workflow for batch management from initial production through quality release:
- Batch Creation: Recording new batches when co-packer confirms production
- Lab Testing: Automated processing of lab results from email
- Batch Release: Automated status updates based on test outcomes
- Order Allocation: Automatic allocation of pending orders to released batches
- Proof Portal: Customer-facing batch verification system (NEW in v4.0)
Zero human intervention required after entering production date and kg. System is production-ready for 100,000+ customers.
Scope¶
Applies to: All batches produced by co-packers for Protocol Raw
Users: - Operations team (batch creation via ops portal) - Automated systems (lab processing, release, allocation, proof generation) - Monitoring/alerts (see SOP-MON-01) - Customers (proof portal verification)
System Overview¶
Complete Process Flow¶
Co-packer Production
↓
Ops Portal: Enter Date + Kg (30 seconds)
↓
Edge Function: Auto-generate Batch Code + Public ID + QR + Label + Proof URL
↓
Database: Status = QA_HOLD (awaiting lab)
↓
Lab Email ' OpenAI Parse ' Batch Release
↓
Auto-Allocation ' Order Export (SOP 0X) ' Fulfillment
↓
Customer: Scan QR ' Proof Portal ' Verify Safety
NEW: Dual ID System (v4.0)¶
Why Two Batch IDs?¶
Every batch now has two identifiers serving different purposes:
1. Internal Batch Code (Sequential)¶
Format: PR-YYMMDD-NNN
Example: PR-251128-004
Properties: - Sequential and predictable - Contains production date - Easy to communicate verbally - Reveals batch timing and frequency
Usage: - Internal operations only - Warehouse management - Co-packer communication - Database queries - Ops portal display
Visibility: - Hidden from customer packaging - Shown on proof pages (for transparency)
2. Public Batch ID (Hashed)¶
Format: PR-XXXXXXXX (8-character hash)
Example: PR-E769561D
Properties: - Non-sequential SHA-256 hash - No date information - Cannot predict next batch - One-way hash (cannot reverse)
Usage: - QR codes on customer packaging - Proof portal URLs - Customer-facing materials - Search functionality
Visibility: - Printed on all customer packaging - Shown on proof pages - Public via proof portal
Competitive Intelligence Protection¶
Problem Without Hashing:
If we used sequential codes on packaging:
What Competitors Can Deduce: - Production dates embedded in codes - 3 batches made in 2 days = production frequency - Can track growth trajectory - Can predict next batch codes - Can systematically scrape proof portal
Solution With Hashing:
Same scenario with public IDs:
What Competitors See: - Two unrelated random codes - No date information - No pattern or sequence - Cannot predict next batch - Cannot calculate production frequency
What We Maintain: - ✅ Full transparency (both IDs shown on proof page) - ✅ Customer verification unchanged - ✅ Lab results fully visible - ✅ Operational simplicity maintained
Hash Generation Technical Details¶
Function: raw_ops.generate_public_batch_id(batch_code TEXT)
Algorithm:
1. Take internal code: "PR-251128-004"
2. Append secret salt: "PR-251128-004|protocol_raw_2024_batch_salt_v1"
3. SHA-256 hash: "e769561d..." (64 hex characters)
4. Take first 8 chars: "e769561d"
5. Uppercase: "E769561D"
6. Prepend "PR-": "PR-E769561D"
Database Trigger:
CREATE TRIGGER trigger_set_public_batch_id
BEFORE INSERT ON raw_ops.batches
FOR EACH ROW
WHEN (NEW.public_batch_id IS NULL)
EXECUTE FUNCTION raw_ops.set_public_batch_id();
Result: Every new batch automatically gets public ID on creation.
Part 1: Batch Creation¶
1.1 When to Create a Batch¶
Trigger: Co-packer confirms production via email/call
Example notification: "Batch produced today. 500kg. Ready for sampling."
⚠ï¸ Critical Timing
Create batches immediately when co-packer confirms production. Do not wait for invoice.
Why: Lab results email will arrive 2-5 days later and needs the batch to exist in the system.
1.2 Batch Creation Process (UPDATED v4.0)¶
Access the Operations Portal
Location: https://ops.protocolraw.co.uk
Authentication: Cloudflare Access (Google OAuth)
Required Information (Only 2 Fields!):
| Field | Description | Example | Source |
|---|---|---|---|
| Production Date | Date batch was produced | 2025-11-28 | Co-packer communication |
| Kg Produced | Total kilograms produced | 500.00 | Co-packer communication |
That's it! Everything else is automated.
Auto-Generated Values (UPDATED v4.2):
The following values are automatically created by the system:
| Field | How Generated | Example |
|---|---|---|
| Batch Code | Edge Function calculates next sequential number | PR-251128-004 |
| Public Batch ID | Database trigger generates SHA-256 hash | PR-E769561D |
| Formulation | Current live formulation based on production date | Adult Maintenance v1.0 |
| COGS per kg | From config table | £3.50 |
| Status | System default | QA_HOLD |
| QR Code | Edge Function generates PNG (for pouch labels) | batch-PR-251128-004.png |
| Pouch Label PDF | Edge Function generates print-ready pouch label artwork | batch-PR-251128-004-label.pdf |
| Proof URL | Customer verification page | https://proof.protocolraw.co.uk/batch/PR-E769561D |
| Expiry Date | Production date + 12 months | 2026-11-28 |
Note (v4.2): The
proof_insert_pdf_urlcolumn is deprecated. Insert cards are now universal (static, bulk printed) and link to the Customer Portal transition guide only. Proof verification is via pouch QR codes + delivery SMS + email.
Step-by-Step Instructions (v4.0)¶
1. Navigate to Batch Creation - Open: https://ops.protocolraw.co.uk - Authenticate via Google OAuth - Click "Batch Creation (SOP 01)" tab
2. Enter Required Information
- Production Date: YYYY-MM-DD format (e.g., 2025-11-28)
- Kg Produced: Decimal number (e.g., 500 or 487.5)
3. Click "Create Batch"
System executes in ~2 seconds: - ✅ Batch code generated - ✅ Database insert - ✅ Public batch ID hashed - ✅ QR code created and stored - ✅ Label PDF created and stored - ✅ Proof URL created - ✅ Co-packer email sent
4. Verify Success
Expected Success Message:
✅ Batch created successfully!
Internal Batch Code: PR-251128-004
Public Batch ID: PR-E769561D
QR Code: [Download Link]
Label PDF: [Download Link]
Proof URL: https://proof.protocolraw.co.uk/batch/PR-E769561D
Email sent to co-packer with label PDF.
5. Batch Appears in Recent Batches Table
| Batch Code | Public ID | Status | Produced | Kg | Created |
|---|---|---|---|---|---|
| PR-251128-004 | PR-E769561D | QA_HOLD | 2025-11-28 | 500.00 | 2025-11-28 14:32:01 |
✅ Success Indicators¶
- ✅ Green success message displays
- ✅ Both batch IDs shown (internal + public)
- ✅ QR code downloads successfully
- ✅ QR code scans and opens proof URL
- ✅ Label PDF contains both IDs and QR code
- ✅ Proof URL opens (shows "undergoing testing" message)
- ✅ Batch appears in Recent Batches table
- ✅ Co-packer receives email with label PDF
1.3 Formulation Assignment Logic (Unchanged from v3.0)¶
Every batch is automatically linked to a formulation version based on the production date.
How It Works:
Current Live Formulation Query:
SELECT id, version, name
FROM raw_ops.formulations
WHERE effective_from <= production_date
AND (effective_until IS NULL OR effective_until >= production_date)
ORDER BY effective_from DESC
LIMIT 1;
Date-Based Transitions:
Example scenario: Launching new formulation on 2026-01-01
| Production Date | Assigned Formulation | Reason |
|---|---|---|
| 2025-12-31 | Adult Maintenance v1.0 | Effective until 2025-12-31 |
| 2026-01-01 | Adult Maintenance v2.0 | Effective from 2026-01-01 |
| 2026-01-15 | Adult Maintenance v2.0 | V2.0 now current |
No manual intervention required - batches automatically assign to correct formulation version.
1.4 Verification¶
After creating a batch, verify it was recorded correctly:
SELECT
b.batch_code,
b.public_batch_id,
b.kg_produced,
b.produced_at,
b.status,
b.qr_image_url,
-- b.proof_insert_pdf_url, -- DEPRECATED v4.2
b.proof_url,
f.name as formulation_name,
f.version as formulation_version
FROM raw_ops.batches b
JOIN raw_ops.formulations f ON b.formulation_id = f.id
WHERE b.batch_code = 'PR-251128-004';
Expected Result: - ✅ Batch exists - ✅ Status = QA_HOLD - ✅ Public batch ID present (not NULL) - ✅ Formulation linked (not NULL) - ✅ QR code URL present - ✅ Pouch label PDF URL present (proof_insert_pdf_url deprecated) - ✅ Proof URL present - ✅ Dates correct
Part 2: Lab Results Processing¶
2.1 Lab Testing Timeline (Unchanged from v3.0)¶
| Day | Event | Status |
|---|---|---|
| Day 0 | Batch created (status: QA_HOLD) | Manual |
| Day 0-1 | Co-packer sends samples to lab | Co-packer action |
| Day 2-5 | Lab testing in progress | Lab action |
| Day 5-8 | Lab emails results PDF | Automated |
| Day 5-8 | System processes results & releases batch | Automated |
2.2 Automated Email Processing (Unchanged from v3.0)¶
Make.com Scenario: Lab Email Ingestion
Frequency: Every 2 minutes
Status: Production (ON)
Processing Steps:
- Gmail Watch: Monitors lab email inbox for new messages with PDF attachments
- PDF Upload: Stores PDF in Supabase Storage (lab-reports bucket)
- PDF Conversion: Converts PDF to text/image for AI processing
- OpenAI Vision: GPT-4o extracts structured data:
- Batch code
- Lab name
- Test results (Salmonella, Listeria, Enterobacteriaceae)
- Outcome (PASS/FAIL)
- Reported date
- Batch Lookup: Finds batch in database by batch_code
- Result Insert: Creates lab_results record
- Trigger Fires: Database trigger processes result
Error Handling:
| Error | Action | Alert |
|---|---|---|
| OpenAI API failure | Scenario pauses | Slack #ops-alerts immediately |
| Batch not found | Scenario logs error | Slack notification |
| PDF unreadable | Retry with fallback parser | Slack if fallback fails |
2.3 Database Trigger: Batch Release Logic (Unchanged from v3.0)¶
Trigger: on_lab_result_insert
Fires: When new lab result inserted into lab_results table
Logic Flow:
IF outcome = 'PASS':
1. Update batch.status ' 'RELEASED'
2. Set batch.released_at ' NOW()
3. Log to batch_status_history
4. Log to ops_events (info level)
5. Fire on_batch_released_allocate trigger
IF outcome = 'FAIL':
1. Update batch.status ' 'REJECTED'
2. Log to batch_status_history
3. Log to ops_events (high priority)
4. Send Slack alert to #ops-alerts
5. Require manual investigation
Pass Result Flow:
| Step | Action | Duration |
|---|---|---|
| 1 | Lab result inserted | Instant |
| 2 | Trigger updates batch status to RELEASED | <1ms |
| 3 | Allocation trigger fires | <1ms |
| 4 | Orders allocated to batch | <100ms |
| 5 | Outbox records created for partner notification | <50ms |
| 6 | Email sent to partners (next 2-min cycle) | <2 min |
| 7 | Proof portal updated (NEW v4.0) | Instant |
| 8 | Customer can verify via QR code (NEW v4.0) | Instant |
NEW: Customer Verification Flow (v4.0)¶
Proof Portal Integration¶
When batch status changes to RELEASED, the proof portal automatically updates.
Customer Experience:
Before Release (QA_HOLD): - Customer scans QR code on packaging - Proof page shows: "This batch is undergoing safety testing" - Message: "Results will be published once verified" - Timeline: "Typically 3-5 business days"
After Release (RELEASED): - Customer scans same QR code - Proof page now shows full results: - ✅ Wax seal badge with "All clear." headline - ✅ Test result cards (Salmonella, Listeria, Hygiene) - ✅ Lab information (UKAS accredited) - ✅ Batch ID and test date - ✅ Download link to original lab certificate PDF - ✅ Share buttons (Send to Vet, Share)
Database View:
-- Proof portal queries this view
SELECT * FROM raw_ops.v_released_batches_verified
WHERE public_batch_id = 'PR-E769561D';
View Criteria: - Status = RELEASED - Salmonella absent - Listeria absent - Enterobacteriaceae <100 cfu/g - Lab outcome = PASS - Lab reported_at NOT NULL
Result: Only safe, verified batches appear with full results on proof portal.
Part 3: Auto-Allocation (Unchanged from v3.0)¶
3.1 Allocation Trigger¶
Trigger: on_batch_released_allocate
Fires: When batch status changes to RELEASED
3.2 Allocation Logic¶
Function: allocate_pending_orders_to_batch(batch_id)
- Find all PAID orders without allocations
- Calculate available kg in batch
- Allocate orders using FEFO (First Expired, First Out)
- Create allocation records linking:
order_item_id'batch_idkg_allocated- Update
batch.qty_reserved - Log allocation events
- Orders now eligible for export (SOP 0X)
3.3 Allocation Rules¶
| Rule | Logic | Reason |
|---|---|---|
| FEFO Priority | Allocate from batches expiring soonest first | Minimize waste, ensure freshness |
| Full Order | All items in order must be allocated together | Ship complete orders only |
| Available Capacity | Don't over-allocate batch kg_produced | Prevent overselling inventory |
| PAID Only | Only allocate to orders with status=PAID | Payment confirmed before allocation |
3.4 Integration with SOP 0X¶
Once allocated, orders become eligible for export to 3PL:
View: v_orders_eligible_for_export
SELECT orders WHERE:
✅ status = 'PAID'
✅ export_state IS NULL
✅ ALL order_items have allocations
✅ ALL allocated batches have status = 'RELEASED'
' SOP 0X exports these orders every 15 minutes
Part 4: Partner Notification (Unchanged from v3.0)¶
4.1 Outbox Pattern¶
When batch is released, notification emails are queued using transactional outbox pattern.
Trigger: on_batch_released (Outbox)
For each partner in system: 1. Create outbox record: - recipient_email - batch_code - kg_produced - released_at - formulation details - idempotency_key (prevents duplicates) 2. State: 'pending' 3. Processed by pg_cron every 2 minutes
4.2 Email Content (UPDATED v4.0)¶
Subject: Batch [BATCH-CODE] Released - Ready for Fulfillment
Body includes: - Batch code (internal) - Public batch ID (NEW) - Formulation name and version - Quantity produced (kg) - Production date - Expiry date - Lab test results summary - Link to Proof Portal page (NEW)
4.3 Retry Logic (Unchanged from v3.0)¶
| Attempt | Delay | Action on Failure |
|---|---|---|
| 1 | Immediate | Retry after 1 second |
| 2 | +1s | Retry after 2 seconds |
| 3 | +2s | Retry after 4 seconds |
| 4 | +4s | Retry after 8 seconds |
| 5 | +8s | Mark as failed, Slack alert |
Monitoring & Alerts¶
Automated Monitoring (SOP-MON-01)¶
All operational monitoring runs natively within Supabase. See SOP-MON-01: Monitoring & Alerting Architecture for full implementation details.
| Monitor | Frequency | Triggers Alert When | Channel |
|---|---|---|---|
| Lab SLA | Every 15 min | Batch >4 days in QA_HOLD | #ops-alerts |
| Working Capital | Every 6 hours | WIP cash >£400K, Oldest batch >5 days | #ops-alerts / #ops-urgent |
| Outbox Queue | Every 2 min | Stuck emails >10, Queue depth >50 | #ops-alerts |
| Daily Snapshot | Daily 09:00 UTC | Always (info digest) | #daily-ops |
Architecture: pg_cron → Edge Function (run-monitor) → PostgreSQL functions → Slack via ops-alerter
Logging: All monitor runs logged to raw_ops.monitoring_runs table.
Real-Time Monitoring Views¶
| View | Purpose | Used By |
|---|---|---|
| v_batch_release_health | Current batch status snapshot | SOP-MON-01 monitors, Metabase |
| v_lab_processing_daily | Daily lab metrics | Daily summary, dashboards |
| v_lab_sla_tracking | Lab partner performance | Lab SLA monitor, compliance |
| v_working_capital_metrics | WIP cash tracking | Working Capital monitor |
| v_outbox_health | Email queue status | Outbox Health monitor |
| v_released_batches_verified (NEW) | Proof portal data | Customer verification |
Key Performance Indicators¶
| Metric | Target | Alert Threshold |
|---|---|---|
| Lab Turnaround Time | <72 hours | >96 hours |
| Auto-Release Success | 100% | <99% |
| Manual Intervention | 0 per day | >1 per day |
| WIP Cash Tied Up | <£400K | >£400K |
| Allocation Success | 100% | <99% |
| QR Code Generation (NEW) | 100% | <100% |
| Proof Page Accessibility (NEW) | 100% | <99.9% |
Troubleshooting¶
Common Issues (v3.0 issues still apply)¶
Issue 1: Batch Not Found During Lab Processing¶
Symptom: Make.com error "Batch [CODE] not found in database"
Cause: Lab results arrived before batch was created in system
Resolution: 1. Create batch immediately using Operations Portal 2. Retry lab email processing (Make.com manual trigger) 3. Verify batch now released
Prevention: Create batches immediately when co-packer confirms production
Issue 2: OpenAI Parsing Failure¶
Symptom: Slack alert "OpenAI failed to parse lab PDF"
Cause: PDF format not recognized, API quota exceeded, or service outage
Resolution: 1. Check OpenAI API status and quota 2. Review PDF manually - is format readable? 3. If urgent: Insert lab result manually via SQL 4. Update fallback parser patterns if new format
Issue 3: Formulation Not Loading¶
Symptom: "Error loading formulation" in Batch Creation UI
Cause: No active formulation for production date, or database permissions
Resolution:
1. Check formulations table has record with valid date range
2. Verify effective_from <= today and effective_until >= today (or NULL)
3. Check database permissions (RLS policies or service role key)
Issue 4: Batch Created Without Formulation¶
Symptom: Batch exists but formulation_id is NULL
Cause: No live formulation existed at production date
Resolution:
-- Fix specific batch
UPDATE raw_ops.batches
SET formulation_id = (
SELECT id FROM raw_ops.formulations
WHERE effective_from <= produced_at
AND (effective_until IS NULL OR effective_until >= produced_at)
ORDER BY effective_from DESC
LIMIT 1
)
WHERE id = 'batch-uuid-here';
NEW Issues (v4.0)¶
Issue 5: Public Batch ID is NULL¶
Symptom: Batch created but public_batch_id column empty
Causes: 1. Database trigger not firing 2. Hash function error 3. Trigger was disabled
Resolution:
-- Check trigger exists
SELECT * FROM information_schema.triggers
WHERE trigger_name = 'trigger_set_public_batch_id';
-- Manually generate missing IDs
UPDATE raw_ops.batches
SET public_batch_id = raw_ops.generate_public_batch_id(batch_code)
WHERE public_batch_id IS NULL;
-- Verify
SELECT batch_code, public_batch_id
FROM raw_ops.batches
WHERE public_batch_id IS NULL;
Issue 6: QR Code Not Generated¶
Symptom: Batch created but qr_image_url is NULL
Causes:
1. Supabase Storage bucket doesn't exist
2. Permissions issue on proof-assets bucket
3. Edge Function timeout
4. Network error during generation
Resolution:
1. Check Supabase Storage ' Verify proof-assets bucket exists
2. Check bucket is public: Settings ' Public bucket toggle ON
3. Check Edge Function logs for timeout/errors
4. Retry: Create batch again (will auto-increment batch number)
Issue 7: Pouch Label PDF Not Generated¶
Note (v4.2): The
proof_insert_pdf_urlcolumn is deprecated and no longer populated. Insert cards are now static/universal. This issue now refers to the pouch label PDF only.
Symptom: QR code exists but pouch label PDF not generated for co-packer
Causes: 1. Browserless API timeout 2. Template rendering error 3. QR code image missing (needed for PDF) 4. Storage upload failed
Resolution: 1. Check Edge Function logs for "PDF generation" errors 2. Verify QR code image exists first 3. Check Supabase Storage quota not exceeded 4. Manually download QR code and create label if urgent
Issue 8: Proof Page Shows 404 Not Found¶
Symptom: QR code scans but proof page doesn't load
Causes:
1. Cloudflare Worker routing issue
2. Edge Function proof-page not deployed
3. Public batch ID format incorrect
4. Batch status not RELEASED (expected if QA_HOLD)
Resolution:
1. Test direct URL: https://znfjpllsiuyezqlneqzr.supabase.co/functions/v1/proof-page/batch/{PUBLIC_ID}
2. If direct URL works ' Cloudflare routing issue
3. If direct URL fails ' Edge Function deployment issue
4. Check batch status:
SELECT batch_code, public_batch_id, status
FROM raw_ops.batches
WHERE public_batch_id = 'PR-XXXXXXXX';
Issue 9: Co-Packer Didn't Receive Email¶
Symptom: Batch created successfully but co-packer reports no email
Causes: 1. Make.com scenario not triggered 2. Webhook URL incorrect in Edge Function 3. Co-packer email address wrong 4. Email caught in spam filter
Resolution: 1. Check Make.com scenario execution history 2. Verify webhook URL matches 3. Check co-packer email address in Make.com 4. Manually forward label PDF to co-packer 5. Ask co-packer to check spam folder
Emergency Procedures¶
Urgent Batch Release (Lab System Down)¶
If lab email system fails and batch needs immediate release:
-- Step 1: Manually insert lab result
INSERT INTO raw_ops.lab_results (
batch_id, lab_name, outcome,
salmonella_absent, listeria_absent, enterobacteriaceae_cfu_per_g,
reported_at
) VALUES (
'batch-uuid',
'Lab Name',
'PASS',
true,
true,
50,
NOW()
);
-- Step 2: Trigger will auto-release batch
-- Verify:
SELECT status, released_at, public_batch_id
FROM raw_ops.batches
WHERE id = 'batch-uuid';
-- Should show: status='RELEASED', released_at=NOW()
⚠ï¸ Manual Override Protocol
All manual interventions MUST be logged to ops_events table with:
- Reason for manual action
- Authorization (who approved)
- Supporting documentation (lab PDF, email, etc.)
Database Schema Reference¶
Key Tables¶
batches (UPDATED v4.2)¶
Primary batch inventory table
| Column | Type | Description |
|---|---|---|
| id | UUID | Primary key |
| batch_code | TEXT | Unique batch identifier (internal: PR-YYMMDD-NNN) |
| public_batch_id | TEXT | Customer-facing hashed ID (PR-XXXXXXXX) |
| produced_at | DATE | Production date (required) |
| expiry_date | DATE | Best before date |
| kg_produced | NUMERIC | Total quantity produced |
| cogs_per_kg_gbp | NUMERIC | Cost of goods sold per kg |
| formulation_id | UUID | Link to formulations table |
| status | ENUM | QA_HOLD | RELEASED | REJECTED |
| released_at | TIMESTAMP | When batch was released (if PASS) |
| qr_image_url | TEXT | QR code PNG in Supabase Storage (for pouch labels) |
| ~~proof_insert_pdf_url~~ | TEXT | DEPRECATED v4.2 - No longer populated. Insert cards are now static. |
| proof_url | TEXT | Customer verification page URL |
formulations (Unchanged from v3.0)¶
Formulation version management
| Column | Type | Description |
|---|---|---|
| id | UUID | Primary key |
| version | TEXT | Version number (e.g., v1.0) |
| name | TEXT | Formulation name (e.g., Adult Maintenance) |
| effective_from | DATE | Start date for this version |
| effective_until | DATE | End date (NULL = open-ended) |
| fediaf_verified | BOOLEAN | FEDIAF compliance status |
| lab_certificate_pdf_url | TEXT | Link to nutritional analysis certificate |
lab_results (UPDATED v4.0)¶
Lab test results with full audit trail
| Column | Type | Description |
|---|---|---|
| id | UUID | Primary key |
| batch_id | UUID | Foreign key to batches |
| lab_name | TEXT | Testing laboratory name |
| outcome | ENUM | PASS | FAIL |
| salmonella_absent | BOOLEAN | Salmonella test (absent in 25g) |
| listeria_absent (NEW) | BOOLEAN | Listeria test (absent in 25g) |
| enterobacteriaceae_cfu_per_g | NUMERIC | Enterobacteriaceae count (<100 cfu/g) |
| reported_at | DATE | Date lab issued report |
| report_pdf_url | TEXT | Link to lab report PDF |
NEW Views (v4.0)¶
v_released_batches_verified¶
Customer-facing proof portal data
Purpose: Single source of truth for proof portal
Criteria: - Status = RELEASED - Salmonella absent - Listeria absent - Enterobacteriaceae <100 cfu/g - Lab outcome = PASS - Lab reported_at NOT NULL
Returns: - All batch fields (including public_batch_id) - Lab result fields - Product data - Formulation data
Usage:
-- Proof portal homepage (latest batch)
SELECT * FROM raw_ops.v_released_batches_verified
ORDER BY released_at DESC
LIMIT 1;
-- Individual batch page
SELECT * FROM raw_ops.v_released_batches_verified
WHERE public_batch_id = 'PR-E769561D';
Future Enhancements¶
Phase 2 Improvements¶
| Enhancement | Benefit | Effort | Priority |
|---|---|---|---|
| ~~QR Code Generation~~ (DONE v4.0) | ~~Auto-generate QR codes~~ | ~~Medium~~ | ~~High~~ |
| Bulk Batch Creation | Create multiple batches via CSV upload | Medium | Medium |
| Mobile-Friendly UI | Create batches from phone/tablet | Low | High |
| Predictive Alerts | Alert when batch likely to fail based on trends | High | Low |
| Multi-Product Support | Handle 8kg, 12kg, 16kg SKUs explicitly | Medium | Low |
| Production Schedule Integration | Expected vs. actual batch creation tracking | High | Medium |
Scaling Considerations¶
Current Capacity (Verified Nov 2025): - Database: Sub-10ms queries at 100K+ customers - Lab Processing: Handles 70 batches/day sustained - Allocation: Processes 50+ orders per batch release - Email Outbox: 100+ emails/batch, 3,000/month capacity - Proof Portal: 100+ concurrent users, sub-2s page loads
Scaling Triggers:
| Milestone | Action Required |
|---|---|
| 10,000 customers | Monitor metrics, confirm health |
| 50,000 customers | Add read replicas for analytics |
| 100,000 customers | Review monthly; system tested for this scale |
| 200,000+ customers | Upgrade Supabase instance, optimize indexes |
Compliance & Audit Trail¶
Regulatory Requirements (ENHANCED v4.0)¶
This SOP supports compliance with: - FEDIAF Standards: Complete formulation documentation and traceability - Food Safety: Batch-level testing with documented outcomes - Recall Capability: Full chain from customer order to batch to formulation - Audit Trail: All status changes logged with timestamps - Consumer Protection: Transparent verification via proof portal (NEW)
Traceability Chain (UPDATED v4.0)¶
Customer Order
↓ (via SOP 00 - Shopify integration)
Order in Database
↓ (via SOP 01 - Auto-allocation)
Allocation Record
↓ (links to)
Batch Record
↓ (links to)
Formulation Record
↓ (links to)
Lab Certificate & Nutritional Analysis
↓ (links to)
Lab Test Results
↓ (NEW v4.0)
Proof Portal Verification (Customer-Facing)
Recall Procedure (Unchanged from v3.0)¶
Scenario: Need to recall all products from a specific batch
-- Step 1: Identify affected batch
SELECT * FROM raw_ops.batches WHERE batch_code = 'BATCH-CODE';
-- Step 2: Find all orders allocated from this batch
SELECT DISTINCT
o.shopify_order_id,
c.email,
c.first_name,
c.last_name,
s.tracking_no,
s.shipped_at
FROM raw_ops.batches b
JOIN raw_ops.allocations a ON b.id = a.batch_id
JOIN raw_ops.order_items oi ON a.order_item_id = oi.id
JOIN raw_ops.orders o ON oi.order_id = o.id
JOIN raw_ops.customers c ON o.customer_id = c.id
LEFT JOIN raw_ops.shipments s ON o.id = s.order_id
WHERE b.batch_code = 'BATCH-CODE'
ORDER BY s.shipped_at DESC;
-- Step 3: Export customer list for recall notification
-- Step 4: Update batch status
UPDATE raw_ops.batches
SET status = 'RECALLED', notes = 'Recalled due to [REASON]'
WHERE batch_code = 'BATCH-CODE';
Audit Log Queries (Unchanged from v3.0)¶
-- Complete batch history
SELECT
bsh.status,
bsh.changed_at,
bsh.changed_by,
bsh.reason
FROM raw_ops.batch_status_history bsh
WHERE bsh.batch_id = 'batch-uuid'
ORDER BY bsh.changed_at;
-- Formulation usage report
SELECT
f.version,
f.name,
COUNT(DISTINCT b.id) as total_batches,
SUM(b.kg_produced) as total_kg,
MIN(b.produced_at) as first_batch,
MAX(b.produced_at) as last_batch
FROM raw_ops.formulations f
JOIN raw_ops.batches b ON f.id = b.formulation_id
GROUP BY f.id, f.version, f.name
ORDER BY f.effective_from DESC;
Access & Permissions¶
User Roles (UPDATED v4.0)¶
| Role | Access | Actions |
|---|---|---|
| Operations Manager | Batch Creation UI, Database (read/write) | Create batches, View all data, Manual overrides |
| Operations Staff | Batch Creation UI | Create batches only |
| System (Edge Functions) | Database (service role) | Process lab results, Send notifications, Run monitors |
| Monitoring (Readonly) | Metabase dashboards, Views | View metrics only |
| Customers (NEW) | Proof Portal (public) | Verify batch safety via QR codes |
Required Credentials¶
- Batch Creation UI: Supabase service role key (internal tool), Cloudflare Access (Google OAuth)
- Make.com: Supabase service role key (lab email processing only)
- Gmail: Lab inbox credentials (monitored by Make.com for lab results)
- OpenAI: API key with GPT-4o Vision access
- Resend: API key for partner notification emails
- Browserless: API key for PDF generation (NEW v4.0)
- Cloudflare: Worker for proof portal proxy (NEW v4.0)
- Slack: Webhook URL for Kai bot (monitoring alerts - see SOP-MON-01)
Version History¶
v4.2 (2026-01-21) - Insert Card Simplification¶
Changes:
- Removed dynamic proof insert PDF generation from batch creation workflow
- Insert cards are now universal (static, bulk printed, same for all boxes)
- Proof verification now via: pouch QR + delivery SMS + email
- Deprecated proof_insert_pdf_url column (no longer populated)
No Changes: - QR code generation for pouch labels unchanged - Pouch label PDF generation for co-packer unchanged - Co-packer email automation unchanged - Lab email processing unchanged - All batch creation, release, allocation logic unchanged
Rationale: - Simplifies batch creation workflow - Reduces per-batch generation overhead - Insert card serves onboarding purpose (portal transition guide) - Multiple proof access touchpoints provide better customer experience
v4.1 (2026-01-17) - Monitoring Migration¶
Changes:
- Migrated monitoring alerts from Make.com to native Supabase (SOP-MON-01)
- Lab SLA and Working Capital alerts now run via pg_cron + Edge Functions
- Updated credentials section to reflect Make.com scope (lab processing only)
- All monitoring logged to raw_ops.monitoring_runs table
No Changes: - Lab email processing still uses Make.com - All batch creation, release, and allocation logic unchanged
v4.0 (2025-11-28) - Dual ID System & Proof Portal¶
Major Changes: - Auto-generated batch codes (Edge Function creates codes) - Dual ID system (internal sequential + public hashed IDs) - SHA-256 hashing for competitive intelligence protection - Automatic QR code generation - Automatic proof page creation - Automatic pouch label PDF generation - Co-packer email automation with label PDF - Customer-facing proof portal integration - Complete workflow documentation
Database Changes:
- Added public_batch_id column to batches table
- Added qr_image_url, proof_insert_pdf_url, proof_url columns (Note: proof_insert_pdf_url deprecated in v4.2)
- Created generate_public_batch_id() function
- Created trigger_set_public_batch_id trigger
- Updated view v_released_batches_verified with public_batch_id
Time Savings: 14.5 minutes per batch
Documentation: - Operations Portal Documentation v2.1 - Proof Portal Documentation v1.0 - This SOP v4.0
v3.0 (2025-11-03) - Batch Creation & Formulation Integration¶
Major Changes: - Added Batch Creation UI for manual batch entry - Integrated formulation table from SOP 0R - Automatic formulation assignment based on production date - Made product_id optional (batches are formulation-based) - Enhanced traceability: order ' batch ' formulation ' lab cert
Database Changes: - Added formulation_id column to batches table - Created foreign key constraint to formulations table - Added index on batches.formulation_id - Made batches.product_id nullable - Backfilled existing batches with formulation links
Files Added: - batch-creation-ui-FINAL.html (production-ready UI) - Batch creation setup documentation
v2.0 (2025-10-31) - Production Optimization¶
- Deployed performance indexes for 100K+ scale
- Added 6 monitoring views for observability
- Created automated alert scenarios (now migrated to SOP-MON-01)
- Zero manual monitoring required
v1.0 (2025-10-15) - Initial Release¶
- Lab email parsing with OpenAI
- Automated batch release logic
- Auto-allocation on release
- Partner notification system
Approval & Sign-Off¶
| Role | Name | Signature | Date |
|---|---|---|---|
| Prepared by | Protocol Raw Operations Team | 2025-11-28 | |
| Reviewed by | |||
| Approved by |
Document Control¶
| Attribute | Detail |
|---|---|
| Document ID | SOP-01-v4.0 |
| Effective Date | 2025-11-28 |
| Review Frequency | Quarterly or upon significant system changes |
| Next Review Date | 2026-02-28 |
| Document Owner | Head of Operations |
| Storage Location | Protocol Raw Operations Portal / Project Knowledge Base |
| Distribution | Operations Team, Quality Team, Technical Team |
✅ System Status¶
Production Ready for 100,000+ Customers
This SOP describes a fully operational, scale-tested system with complete automation from batch creation through customer verification. Only manual touchpoint: entering production date and kg (30 seconds). Everything else is automated: batch codes, dual IDs, QR codes, labels, proof pages, emails, lab processing, release, and allocation.
End of SOP 01 v4.0
Last reviewed: 2025-11-28
Next review: Quarterly
System status: ✅ Production Ready
Protocol Raw - The systematic approach to raw feeding