SOP-LAB-01: Batch Creation & Lab-to-Release v1.1¶

Complete workflow from batch creation through quality release

Document ID: SOP-LAB-01-v1.1 Version: 1.1 Status: ✅ Production Ready Last Updated: 2026-03-21 Owner: Protocol Raw Operations Replaces: SOP-LAB-01-v1.0 Review Date: 2026-06-21

Key Changes in v1.1¶

✅ Lab SLA monitor absorbed from SOP-MON-01 legacy monitors (thresholds, runbook, pg_cron job)
✅ Working Capital monitor absorbed from SOP-MON-01 legacy monitors (thresholds, runbook, pg_cron job)
✅ MON-01 references updated to v1.1

Key Changes from SOP-01 v4.3¶

Lab Email Ingestion — Full Migration from Make.com to Native Stack: - ✅ Make.com "Lab Email Ingestion" scenario deactivated — replaced by lab-email-ingestion Edge Function - ✅ Google Pub/Sub push notifications for near-real-time processing (replaces 2-minute Make.com polling) - ✅ Fallback poll every 30 minutes via pg_cron as insurance against Pub/Sub delivery failure - ✅ Recoverable quarantine queue (lab_email_failures table) — failed emails are quarantined, not lost - ✅ DB-level idempotency via source_email_id unique constraint on lab_results - ✅ OpenAI Responses API for native PDF parsing (replaces Chat Completions with image_url workaround) - ✅ Lab email filtering — isLabEmail() function skips non-lab PDF attachments - ✅ Tiered alert severity — infrastructure failures are critical, parse failures are warnings - ✅ Gmail watch auto-renewal every 6 days via pg_cron (watch expires every 7 days)

Enterobacteriaceae Threshold Update: - ✅ <5,000 cfu/g (legal maximum per EC 2073/2005) replaces <100 cfu/g placeholder - ✅ Updated in: batch release trigger, v_released_batches_verified view, proof portal sample page, OpenAI parsing prompt - ✅ Internal benchmark TBD after 10-20 production batches establish baseline data

Database Changes: - ✅ lab_results.source_email_id column added with partial unique index (idempotency key) - ✅ lab_email_failures table created (recoverable quarantine queue) - ✅ fn_process_lab_email_v1() function created (idempotent lab result insert) - ✅ fn_check_lab_ingestion_health_v2() function created (monitoring) - ✅ batch_status_history RLS enabled (was missing)

Unchanged from SOP-01 v4.3: - ✅ All batch creation logic (Ops Portal, Edge Functions, dual ID system, QR codes, labels) - ✅ on_lab_result_insert trigger (batch release/rejection logic) - ✅ on_batch_released_allocate trigger (order allocation) - ✅ Outbox pattern for partner notifications - ✅ Proof portal integration - ✅ Co-packer email automation (separate Edge Function triggered by batch creation) - ✅ All monitoring views and KPIs - ✅ Compliance, audit trail, recall procedures

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 (native Supabase stack)
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

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)

Related SOPs: - SOP-MON-01: Monitoring & Alerting Architecture - SOP-PROOF-01: Proof Portal System - SOP-ORD-02: 3PL Order Export - SOP-INV-01: Inventory Control (FEFO allocation) - SOP-PROOF-00: Proof-of-Safety System (QR, labels, co-packer email)

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)
  ↓
Gmail → Pub/Sub → lab-email-ingestion Edge Function → OpenAI Parse → DB Insert
  ↓
on_lab_result_insert trigger → Batch RELEASED or REJECTED
  ↓
on_batch_released_allocate → Order Allocation → Export (SOP 0X) → Fulfillment
  ↓
Customer: Scan QR → Proof Portal → Verify Safety

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. If the batch doesn't exist, the lab result will be quarantined with batch_not_found error.

1.2 Batch Creation Process¶

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

Everything else is automated.

Auto-Generated Values:

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: The proof_insert_pdf_url column 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¶

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.

1.3 Dual ID System¶

Every batch has two identifiers serving different purposes.

Internal Batch Code (Sequential)

Format: PR-YYMMDD-NNN
Example: PR-251128-004

Used for internal operations, warehouse management, co-packer communication, database queries. Contains production date and is sequential. Hidden from customer packaging but shown on proof pages for transparency.

Public Batch ID (Hashed)

Format: PR-XXXXXXXX (8-character SHA-256 hash)
Example: PR-E769561D

Used for QR codes on packaging, proof portal URLs, customer-facing materials. Non-sequential, reveals no date or production frequency information. Protects competitive intelligence.

Hash Generation:

Function: raw_ops.generate_public_batch_id(batch_code TEXT)

Take internal code: "PR-251128-004"
Append secret salt: "PR-251128-004|protocol_raw_2024_batch_salt_v1"
SHA-256 hash, take first 8 chars, uppercase
Prepend "PR-": "PR-E769561D"

Database trigger trigger_set_public_batch_id auto-generates on batch creation.

1.4 Formulation Assignment¶

Every batch is automatically linked to a formulation version based on the production date.

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;

No manual intervention required. Batches automatically assign to the correct formulation version based on date ranges.

1.5 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_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: Batch exists, status = QA_HOLD, public batch ID present, formulation linked, QR code URL present, proof URL present, dates correct.

Part 2: Lab Results Processing¶

2.1 Lab Testing Timeline¶

Day	Event	Status
Day 0	Batch created (status: QA_HOLD)	Manual (Ops Portal)
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 ingestion
Day 5-8	System processes results and releases batch	Automated

2.2 Automated Email Processing¶

Architecture: Native Supabase stack (Edge Function + PostgreSQL + pg_cron)
Primary trigger: Google Pub/Sub push notification (near-real-time)
Safety net: pg_cron fallback poll every 30 minutes
Status: Production (replaces Make.com "Lab Email Ingestion" scenario, deactivated 2026-03-19)

Processing Flow¶

Gmail Inbox (new email arrives)
    ↓
Google Pub/Sub push notification
    ↓
lab-email-ingestion Edge Function
    ↓
1. Filter: isLabEmail() — check sender domain + subject keywords + PDF attachment
   → Not a lab email? Skip silently
    ↓
2. Idempotency check: source_email_id already in lab_results or lab_email_failures?
   → Already processed? Return duplicate:true
    ↓
3. Fetch email from Gmail API (OAuth2, gmail.readonly scope)
    ↓
4. Download PDF attachment from Gmail API
    ↓
5. Upload PDF to Supabase Storage (lab-reports/{emailId}.pdf)
    ↓
6. Parse PDF via OpenAI Responses API (GPT-4o, native PDF input, temperature 0)
    ↓
7. Validate parsed data (batch_code, outcome, pathogen results required)
    ↓
8. fn_process_lab_email_v1() — idempotent DB write with ON CONFLICT DO NOTHING
    ↓
9. on_lab_result_insert trigger fires automatically (see Part 2.3)

At any step, if processing fails, the email is quarantined in lab_email_failures with the appropriate error type and a Slack alert is sent.

Lab Email Filtering¶

The isLabEmail() function prevents processing random PDF attachments:

Criteria (must have PDF attachment AND match at least one of): - Sender domain matches known labs: i2analytical, eurofins, alsglobal, i2fast - Subject contains: certificate, lab, test result, analysis, protocol raw

Non-lab emails are silently skipped. Not quarantined.

OpenAI Parsing¶

API: OpenAI Responses API (/v1/responses)
Model: GPT-4o
Input: Native PDF file (base64, input_file type)

Extracted fields:

Field	Type	Parsing Rules
`batch_code`	TEXT	PR-YYMMDD-NNN format
`lab_name`	TEXT	Name of testing laboratory
`outcome`	TEXT	PASS if all three tests pass. FAIL otherwise
`salmonella_absent`	BOOLEAN	"Not detected" / "Absent" = true
`listeria_absent`	BOOLEAN	Same logic as Salmonella
`enterobacteriaceae_cfu_per_g`	NUMERIC	CFU/g count. <5,000 = pass
`reported_at`	DATE	YYYY-MM-DD

Fallback Poll (Safety Net)¶

Every 30 minutes, pg_cron triggers the Edge Function with an empty body. The function:

Lists recent Gmail messages with PDF attachments from the last 2 hours
Checks each against lab_results.source_email_id and lab_email_failures.source_email_id
Processes any unhandled emails directly (calls processLabEmail() helper, no recursive HTTP)

Combined with the source_email_id unique constraint, duplicate processing is impossible even if both Pub/Sub and fallback poll fire for the same email.

Gmail Watch Auto-Renewal¶

Gmail Pub/Sub watch expires every 7 days. A pg_cron job runs every 6 days and calls the Edge Function with { action: "renew_watch" }, which calls the Gmail watch API to refresh the subscription.

2.3 Database Trigger: Batch Release Logic¶

Trigger: on_lab_result_insert
Fires: When new lab result inserted into lab_results table
Status: Unchanged from SOP-01 v4.3

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	Instant
8	Customer can verify via QR code	Instant

2.4 Quarantine System¶

When an email fails processing at any stage, it is quarantined in lab_email_failures and a Slack alert is sent. The severity depends on the failure type.

Error Type	Severity	Channel	Meaning
`storage_upload_failed`	critical	#ops-urgent	Infrastructure failure. Supabase Storage issue
`db_write_failed`	critical	#ops-urgent	Infrastructure failure. Database write failed
`batch_not_found`	warning	#ops-alerts	Lab result arrived before batch was created
`batch_not_in_qa_hold`	warning	#ops-alerts	Batch exists but is not in QA_HOLD
`ai_parsing_failed`	warning	#ops-alerts	OpenAI could not parse the PDF
`incomplete_parse`	warning	#ops-alerts	Missing required fields after parsing
`no_pdf_attachment`	warning	#ops-alerts	Email matched lab filter but had no PDF

The monitoring function (fn_check_lab_ingestion_health_v2) escalates any failure that sits unresolved for more than 24 hours to #ops-urgent, regardless of original severity.

Resolving quarantined emails: See Operational Procedures section.

Part 3: Customer Verification Flow¶

When batch status changes to RELEASED, the proof portal automatically updates.

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"

After Release (RELEASED): - Customer scans same QR code - Proof page 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:

SELECT * FROM raw_ops.v_released_batches_verified
WHERE public_batch_id = 'PR-E769561D';

View Criteria: Status = RELEASED, Salmonella absent, Listeria absent, Enterobacteriaceae <5,000 cfu/g, Lab outcome = PASS, Lab reported_at NOT NULL.

Part 4: Auto-Allocation¶

Allocation Trigger¶

Trigger: on_batch_released_allocate
Fires: When batch status changes to RELEASED
Status: Unchanged

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_id with kg_allocated
Update batch.qty_reserved
Log allocation events
Orders now eligible for export (SOP-ORD-02)

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

Integration with SOP 0X¶

Once allocated, orders become eligible for export via v_orders_eligible_for_export: - status = 'PAID' - export_state IS NULL - ALL order_items have allocations - ALL allocated batches have status = 'RELEASED'

SOP-ORD-02 exports these orders every 15 minutes.

Part 5: Partner Notification¶

Outbox Pattern¶

When batch is released, notification emails are queued using the transactional outbox pattern.

Trigger: on_batch_released (Outbox)

For each partner: 1. Create outbox record (recipient_email, batch_code, kg_produced, released_at, formulation details, idempotency_key) 2. State: 'pending' 3. Processed by pg_cron every 2 minutes

Email Content¶

Subject: Batch {batch_code} Released

Body includes: Batch code, public batch ID, formulation name and version, quantity produced (kg), production date, released date, lab test results summary, link to proof portal page.

Retry Logic¶

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 for full implementation details.

Monitor	Frequency	Triggers Alert When	Channel
Lab Ingestion Health	Every 15 min	Any quarantined emails pending review	#ops-alerts / #ops-urgent
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

Lab SLA Monitor¶

Function: raw_ops.fn_check_lab_sla_v2() Schedule: Every 15 minutes Registered in: run-monitor Edge Function as lab_sla

Purpose: Detects batches stuck in QA_HOLD beyond 4-day SLA.

Thresholds: - Any batch in QA_HOLD > 4 days (96 hours) triggers alert

Channel: #ops-alerts (warning)

Alert fields: - Lab Name - SLA Breaches (count) - Avg Turnaround (hours) - P95 Turnaround (hours) - Compliance %

Source: raw_ops.v_lab_sla_tracking view

pg_cron job:

SELECT cron.schedule(
  'monitor-lab-sla',
  '*/15 * * * *',
  $$ SELECT raw_ops.fn_invoke_monitor('lab_sla'); $$
);

Runbook:

Identify affected batches in ops portal
Contact laboratory for status update
Request expedited processing if possible
Update expected release dates
If SLA breach persists beyond 6 days, escalate to backup lab (see Growth Strategy backup lab criteria)

Common causes: - Laboratory backlog - Sample issues requiring retest - Communication delays

Working Capital Monitor¶

Function: raw_ops.fn_check_working_capital_v2() Schedule: Every 6 hours Registered in: run-monitor Edge Function as working_capital

Purpose: Monitors cash tied up in QA_HOLD inventory.

Thresholds:

Condition	Severity	Channel
cash_tied_up > 400,000 OR oldest_batch > 5 days	`warning`	#ops-alerts
cash_tied_up > 600,000 OR oldest_batch > 7 days	`critical`	#ops-urgent

Alert fields: - Cash Tied Up (GBP) - Batches in Hold (count) - Total kg Held - Oldest Batch (days) - Avg Hold Time (days)

Source: raw_ops.v_working_capital_metrics view

pg_cron job:

SELECT cron.schedule(
  'monitor-working-capital',
  '0 */6 * * *',
  $$ SELECT raw_ops.fn_invoke_monitor('working_capital'); $$
);

Runbook:

Review batches in QA_HOLD
Contact laboratory for bulk status update
Prioritise oldest batches for release
Assess cash flow impact
If lab SLA breach is contributing, escalate per Lab SLA runbook above

Common causes: - Multiple batches pending lab results - Lab SLA breaches accumulating - Production run timing issues

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	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	100%	<100%
Proof Page Accessibility	100%	<99.9%
Lab Email Quarantine	0 pending	>0 pending >24h

pg_cron Jobs (Lab Ingestion)¶

Job Name	Schedule	Purpose
`lab-01-gmail-watch-renew`	`0 0 /6 *` (every 6 days)	Renews Gmail Pub/Sub watch before 7-day expiry
`lab-01-fallback-poll`	`/30 * * *` (every 30 min)	Safety net. Checks Gmail for unprocessed PDF emails from last 2 hours
`monitor-lab-ingestion-health`	`/15 * * *` (every 15 min)	Checks quarantine table for pending failures

All jobs follow the SOP-MON-01 pattern: pg_cron → pg_net → Edge Function.

Google Cloud Infrastructure¶

Project¶

Project ID: protocol-raw-operations
Project Number: 798467631007

APIs Enabled¶

Gmail API
Cloud Pub/Sub API

Pub/Sub¶

Topic: projects/protocol-raw-operations/topics/lab-email-notifications
Push subscription: lab-email-push
Endpoint: https://znfjpllsiuyezqlneqzr.supabase.co/functions/v1/lab-email-ingestion
Publisher: gmail-api-push@system.gserviceaccount.com (granted roles/pubsub.publisher at project level)

Gmail Watch¶

Scope: https://www.googleapis.com/auth/gmail.readonly
Label filter: INBOX
Expiry: 7 days (auto-renewed every 6 days by pg_cron)

OAuth Credentials¶

Application type: Web application
Name: Lab Email Ingestion
Authorized redirect URI: https://developers.google.com/oauthplayground
Refresh token stored in Supabase Edge Function secrets

Operational Procedures¶

Resolving Quarantined Lab Emails¶

Check #ops-alerts or #ops-urgent for quarantine notifications

Query the quarantine table:

SELECT id, source_email_id, error_type, error_message, 
       email_subject, email_from, pdf_storage_path, status
FROM raw_ops.lab_email_failures
WHERE status = 'pending_review'
ORDER BY created_at;

Investigate based on error_type (see quarantine severity table in Part 2.4)
If fixable (e.g., batch created after email arrived):
Fix the root cause

Reprocess:

curl -X POST "https://znfjpllsiuyezqlneqzr.supabase.co/functions/v1/lab-email-ingestion" \
  -H "Content-Type: application/json" \
  -d '{"emailId": "SOURCE_EMAIL_ID_HERE"}'

Mark resolved:

UPDATE raw_ops.lab_email_failures
SET status = 'resolved', resolved_at = now(), 
    resolution_note = 'Reprocessed after creating batch'
WHERE id = 'QUARANTINE_UUID';

If permanently unresolvable:

UPDATE raw_ops.lab_email_failures
SET status = 'permanent_failure', resolved_at = now(), 
    resolution_note = 'Not a valid lab email'
WHERE id = 'QUARANTINE_UUID';

Emergency Manual Lab Result Insert¶

If the Edge Function is down and a batch needs immediate release:

INSERT INTO raw_ops.lab_results (
  batch_id, lab_name, outcome,
  salmonella_absent, listeria_absent, enterobacteriaceae_cfu_per_g,
  reported_at, source_email_id
) VALUES (
  'BATCH_UUID',
  'Lab Name',
  'PASS',
  true,
  true,
  50,
  CURRENT_DATE,
  'manual-override-YYYYMMDD'
);

-- Log the manual intervention
INSERT INTO raw_ops.ops_events (entity_type, entity_id, kind, message, meta)
VALUES ('batch', 'BATCH_UUID', 'WARNING', 'Manual lab result insert - Edge Function bypass',
  '{"reason": "Edge Function unavailable", "operator": "Anton"}'::jsonb
);

The on_lab_result_insert trigger fires automatically and releases the batch.

⚠️ Manual Override Protocol: All manual interventions MUST be logged to ops_events with reason, authorization, and supporting documentation.

Monitoring Queries¶

-- Lab ingestion monitoring runs
SELECT check_name, status, result_summary, run_at
FROM raw_ops.monitoring_runs
WHERE check_name = 'lab_ingestion_health'
ORDER BY run_at DESC LIMIT 10;

-- All quarantined emails
SELECT source_email_id, error_type, error_message, status, created_at
FROM raw_ops.lab_email_failures
ORDER BY created_at DESC;

-- Recent successful lab results
SELECT lr.id, b.batch_code, lr.outcome, lr.source_email_id, lr.reported_at
FROM raw_ops.lab_results lr
JOIN raw_ops.batches b ON lr.batch_id = b.id
ORDER BY lr.reported_at DESC LIMIT 10;

-- PDF storage verification
SELECT name, created_at 
FROM storage.objects 
WHERE bucket_id = 'lab-reports' 
ORDER BY created_at DESC LIMIT 10;

-- 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;

Troubleshooting¶

Lab Ingestion Issues¶

Issue 1: Lab Email Not Processed (No Quarantine Entry)¶

Symptom: Lab email arrived but no lab result inserted and no quarantine entry

Causes: 1. Email didn't match isLabEmail() filter (unknown sender domain or no relevant subject keywords) 2. Pub/Sub notification failed and fallback poll hasn't run yet 3. Gmail watch expired (renewal cron failed)

Resolution: 1. Check Edge Function logs for any invocations 2. Manually trigger the fallback poll: curl -X POST "https://znfjpllsiuyezqlneqzr.supabase.co/functions/v1/lab-email-ingestion" -H "Content-Type: application/json" -d "{}" 3. If email is from a new lab, add their domain to the isLabEmail() filter and redeploy 4. Check Gmail watch status — manually renew if needed via OAuth Playground

Issue 2: OpenAI Parsing Failure¶

Symptom: Email quarantined with ai_parsing_failed

Causes: PDF format not recognized, OpenAI API quota exceeded, service outage, PDF too large

Resolution: 1. Check OpenAI API status and quota 2. Download the PDF from Supabase Storage (lab-reports/{emailId}.pdf) and review manually 3. If urgent: Insert lab result manually via SQL (see Emergency procedures) 4. If new PDF format: Update the OpenAI system prompt and redeploy

Issue 3: Batch Not Found¶

Symptom: Email quarantined with batch_not_found

Cause: Lab result arrived before batch was created in system

Resolution: 1. Create the batch via Ops Portal immediately 2. Reprocess: curl -X POST ... -d '{"emailId": "EMAIL_ID"}' 3. Verify batch released

Prevention: Create batches immediately when co-packer confirms production.

Issue 4: Batch Not in QA_HOLD¶

Symptom: Email quarantined with batch_not_in_qa_hold

Cause: Batch exists but has already been released, rejected, or is in another status

Resolution: Check batch status. If already released, this may be a duplicate lab email. Mark quarantine entry as resolved.

Batch Creation Issues¶

Issue 5: Formulation Not Loading¶

Symptom: "Error loading formulation" in Batch Creation UI

Cause: No active formulation for production date

Resolution: 1. Check formulations table: effective_from <= today and effective_until >= today (or NULL) 2. Check database permissions

Issue 6: Public Batch ID is NULL¶

Symptom: Batch created but public_batch_id column empty

Resolution:

SELECT * FROM information_schema.triggers 
WHERE trigger_name = 'trigger_set_public_batch_id';

UPDATE raw_ops.batches 
SET public_batch_id = raw_ops.generate_public_batch_id(batch_code) 
WHERE public_batch_id IS NULL;

Issue 7: QR Code Not Generated¶

Symptom: Batch created but qr_image_url is NULL

Resolution: 1. Check Supabase Storage — verify proof-assets bucket exists and is public 2. Check Edge Function logs for timeout/errors 3. Retry by creating batch again (will auto-increment batch number)

Issue 8: Proof Page Shows 404¶

Symptom: QR code scans but proof page doesn't load

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 (QA_HOLD is expected behaviour, not an error)

Issue 9: Co-Packer Didn't Receive Email¶

Symptom: Batch created but co-packer reports no email

Resolution: 1. Check Edge Function logs for process-proof-jobs (handles co-packer email as part of proof job pipeline) 2. Check raw_ops.proof_jobs for the batch — look at email_sent flag and last_error 3. Verify COPACKER_EMAIL and RESEND_API_KEY secrets are correct 4. Check spam folder 5. Manually forward label PDF if urgent

Database Schema Reference¶

batches¶

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
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	FK to formulations table
product_id	UUID	FK to products table (nullable)
status	ENUM	QA_HOLD, RELEASED, REJECTED
released_at	TIMESTAMPTZ	When batch was released
qr_image_url	TEXT	QR code PNG in Supabase Storage
proof_url	TEXT	Customer verification page URL

formulations¶

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
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)¶

Column	Type	Description
id	UUID	Primary key
batch_id	UUID	FK to batches
lab_name	TEXT	Testing laboratory name
outcome	ENUM	PASS or FAIL
salmonella_absent	BOOLEAN	Salmonella test (absent in 25g)
listeria_absent	BOOLEAN	Listeria test (absent in 25g)
enterobacteriaceae_cfu_per_g	NUMERIC	Enterobacteriaceae count (<5,000 cfu/g)
reported_at	DATE	Date lab issued report
report_pdf_url	TEXT	Link to lab report PDF in Supabase Storage
source_email_id	TEXT	Gmail message ID. Unique constraint prevents duplicate processing

lab_email_failures (NEW)¶

Column	Type	Description
id	UUID	Primary key
source_email_id	TEXT	Gmail message ID
email_subject	TEXT	Original email subject
email_from	TEXT	Sender address
email_received_at	TIMESTAMPTZ	When email was received
pdf_storage_path	TEXT	Path in Supabase Storage (if uploaded)
error_type	TEXT	Category of failure
error_message	TEXT	Detailed error description
raw_payload	JSONB	OpenAI parsed data (if available)
retry_count	INTEGER	Number of reprocessing attempts
status	TEXT	pending_review, reprocessing, resolved, permanent_failure
resolved_at	TIMESTAMPTZ	When the issue was resolved
resolution_note	TEXT	How it was resolved

RLS Posture¶

Table	RLS Enabled	Policies
batches	Yes	Public read on released batches (proof portal)
lab_results	Yes	None. Service role bypasses. Anon returns zero rows
lab_email_failures	Yes	None. Service role bypasses. Anon returns zero rows
batch_status_history	Yes	None. Service role bypasses. Anon returns zero rows
formulations	Yes	Service role access only

Enterobacteriaceae Threshold¶

The pass/fail threshold for Enterobacteriaceae is <5,000 cfu/g (legal maximum per EC 2073/2005).

This was updated from the <100 cfu/g placeholder during the SOP-LAB-01 build. We do not yet have production data to set a tighter internal benchmark. Once 10-20 batches have been processed, review the actual distribution and consider setting an internal threshold for early warning alerts.

Locations where the threshold is enforced: - on_lab_result_insert trigger function (batch release logic) - v_released_batches_verified view (proof portal data filter) - serveSamplePage() in the proof-page Edge Function (sample batch display) - OpenAI system prompt in lab-email-ingestion (parsing instructions)

Edge Functions¶

Function	Purpose	Trigger
`create-batch`	Thin wrapper — calls `fn_create_batch_v1()`, returns result	Ops Portal UI
`process-proof-jobs`	Queue worker — QR, label PDF, co-packer email (see SOP-PROOF-00)	pg_cron (30s)
`lab-email-ingestion`	Processes lab email → DB insert	Pub/Sub push, pg_cron, manual
`proof-page`	Serves public proof pages	Customer QR scan
`run-monitor`	Central monitor dispatch	pg_cron
`ops-alerter`	Slack notifications	run-monitor

Required Secrets (Lab Ingestion)¶

Secret	Purpose
`OPENAI_API_KEY`	PDF parsing via GPT-4o
`GMAIL_REFRESH_TOKEN`	Gmail API access (OAuth2)
`GMAIL_CLIENT_ID`	Gmail OAuth2 client
`GMAIL_CLIENT_SECRET`	Gmail OAuth2 client
`GMAIL_PUBSUB_TOPIC`	`projects/protocol-raw-operations/topics/lab-email-notifications`

Other Required Secrets¶

Secret	Purpose
`RESEND_API_KEY`	Co-packer email delivery
`BROWSERLESS_API_KEY`	Label PDF generation
`COPACKER_EMAIL`	Co-packer email address
`SLACK_OPS_ALERTS_WEBHOOK`	Slack alerts
`SLACK_OPS_URGENT_WEBHOOK`	Slack urgent alerts

Compliance & Audit Trail¶

Regulatory Requirements¶

This SOP supports compliance with: - FEDIAF Standards: Complete formulation documentation and traceability - Food Safety (EC 2073/2005): Batch-level testing with documented outcomes - Recall Capability: Full chain from customer order → batch → formulation - Audit Trail: All status changes logged with timestamps - Consumer Protection: Transparent verification via proof portal

Traceability Chain¶

Customer Order
  ↓ (via Shopify integration)
Order in Database
  ↓ (via auto-allocation)
Allocation Record
  ↓ (links to)
Batch Record
  ↓ (links to)
Formulation Record
  ↓ (links to)
Lab Certificate & Nutritional Analysis
  ↓ (links to)
Lab Test Results
  ↓
Proof Portal Verification (Customer-Facing)

Recall Procedure¶

-- 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: Update batch status
UPDATE raw_ops.batches
SET status = 'RECALLED', notes = 'Recalled due to [REASON]'
WHERE batch_code = 'BATCH-CODE';

Access & Permissions¶

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	Proof Portal (public)	Verify batch safety via QR codes

Future Enhancements¶

Enhancement	Priority	Status
Bulk Batch Creation via CSV	Medium	Not started
Mobile-Friendly Batch Creation UI	High	Not started
Internal Enterobacteriaceae benchmark	Medium	After 10-20 batches
Pub/Sub JWT verification	Low	Phase B hardening
Lab domain config table	Low	When second lab onboarded
Predictive lab failure alerts	Low	After sufficient data

Scaling Considerations¶

Current Capacity: - 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

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

Outstanding Items¶

Item	Priority	Status
Pub/Sub endpoint is `--no-verify-jwt`	Low	Acceptable for Phase A. Harden in Phase B
`isLabEmail()` filter uses hardcoded lab domains	Low	Move to config table when second lab onboarded
Internal Enterobacteriaceae benchmark	Medium	After 10-20 production batches

Cutover Record (Lab Ingestion Migration)¶

Cutover date: 2026-03-19
Cutover operator: Anton

Test	Result
End-to-end processing (mock lab PDF)	✅ Pass. Batch PR-260315-001 auto-released from QA_HOLD to RELEASED
Idempotency (duplicate email)	✅ Pass. Second call returned `duplicate: true`, no second insert
Quarantine (malformed PDF)	✅ Pass. Landed in `lab_email_failures` with `pending_review` status
Slack alert on quarantine	✅ Pass. Alert received
Monitoring runs	✅ Pass. `lab_ingestion_health` running every 15 minutes
Make.com scenario deactivated	✅ Done. No Make.com scenarios active for lab ingestion

All Objects (Lab Ingestion Build)¶

Object	Type	Purpose
`raw_ops.lab_email_failures`	Table	Recoverable quarantine queue
`raw_ops.lab_results.source_email_id`	Column + unique index	Idempotency key
`raw_ops.fn_process_lab_email_v1()`	Function	Idempotent lab result insert
`raw_ops.fn_check_lab_ingestion_health_v2()`	Function	Monitoring for quarantined emails
`lab-email-ingestion`	Edge Function	Replaces Make.com lab email scenario
`run-monitor` dispatch entry	Edge Function update	Added `lab_ingestion_health` to dispatch map
`lab-01-gmail-watch-renew`	pg_cron job	Renews Gmail Pub/Sub watch every 6 days
`lab-01-fallback-poll`	pg_cron job	Safety net poll every 30 minutes
`monitor-lab-ingestion-health`	pg_cron job	Checks quarantine table every 15 minutes

Version History¶

Version	Date	Changes	Author
SOP-LAB-01 v1.1	2026-03-21	Lab SLA and Working Capital monitors absorbed from SOP-MON-01 legacy monitors. MON-01 references updated to v1.1.	Anton / Claude
SOP-LAB-01 v1.0	2026-03-19	Full rewrite. Replaces SOP-01 v4.3. Lab email ingestion migrated from Make.com to native Supabase stack. Enterobacteriaceae threshold updated to <5,000 cfu/g. DB-level idempotency added. Quarantine system added. Google Pub/Sub integration.	Protocol Raw Operations
SOP-01 v4.3	2026-02-07	Proof portal language update ("All clear." headline, "Hygiene" test card label)
SOP-01 v4.2	2026-01-21	Insert card simplification. Deprecated proof_insert_pdf_url
SOP-01 v4.1	2026-01-17	Monitoring migration to native Supabase (SOP-MON-01)
SOP-01 v4.0	2025-11-28	Dual ID system, QR codes, proof portal, label PDFs, co-packer email automation
SOP-01 v3.0	2025-11-03	Batch Creation UI, formulation integration
SOP-01 v2.0	2025-10-31	Performance indexes, monitoring views
SOP-01 v1.0	2025-10-15	Initial release. Lab email parsing, batch release, auto-allocation

End of SOP-LAB-01 v1.1

Last reviewed: 2026-03-21 Next review: 2026-06-21
System status: ✅ Production Ready

Protocol Raw — Verified safe, batch by batch