SOP 0R: Proof-of-Safety System (v4.2)¶
Status: ✅ Production Ready for 100,000+ Customers
Last Updated: 2026-01-21
Owner: Protocol Raw Operations
Version: 4.2 - Static Insert Card Edition
Replaces: SOP-0R v4.1
Key Changes in v4.2¶
Insert Card Now Fully Static:
- ⌠Removed dynamic proof insert PDF generation - proof_insert_pdf_url deprecated
- ✅ Insert cards are universal - Same static card for all boxes, bulk printed
- ✅ Insert card links ONLY to portal - Transition guide QR (portal.protocolraw.co.uk?view=transition)
- ✅ Proof verification via multiple channels - Pouch QR + delivery SMS + email
What's Unchanged:
- ✅ QR code generation for pouch labels (qr_image_url)
- ✅ Proof URL generation (proof_url)
- ✅ Pouch label PDF for co-packer
- ✅ All batch creation, release, lab processing workflows
Customer Proof Access (Post-Change): | Method | Priority | |--------|----------| | Pouch QR code scan | Primary | | Delivery SMS link | Secondary | | Order confirmation email | Secondary | | Customer portal | Tertiary | | Manual website lookup | Fallback |
Key Changes in v4.1¶
Insert Card Clarification: - Clarified that insert card QR links to Customer Portal transition guide (not proof page) - Proof verification QR remains on product pouches only - Insert card is universal (no batch code) â€â€ same card works for all boxes - Card serves dual purpose: onboarding (front) + proof education (back)
Unchanged from v4.0: - All batch creation, proof generation, and label workflows unchanged - QR codes on pouches still generated at production time (not release time) - Dual ID system (internal sequential + public hashed) preserved - Co-packer email automation intact
Purpose¶
To provide customers with verifiable, transparent proof that each batch of Protocol Raw has passed independent safety testing before release. This system automatically generates public-facing proof pages with QR codes that customers can scan to view lab results and batch information.
Key Principle: Transparency isn't a claimâ€â€it's a feature customers can use. Every batch has a unique, publicly accessible proof page that cannot be edited after release.
Key Innovation (v4.0): QR codes and labels generated at production time, not release time. This enables pre-printed QR codes on packaging while maintaining proof page privacy until lab clearance.
System Overview¶
The Proof-of-Safety system consists of four main components:
- Pre-Production Layer: Batch creation UI and label generation for co-packers
- Database Layer: Supabase PostgreSQL storing batch, lab result, product, and formulation data
- Generation Layer: Automated proof job system creating proof pages and QR codes
- Delivery Layer: Public-facing proof pages served via custom domain with Cloudflare CDN
Database Schema¶
Core Tables¶
| Table | Purpose | Key Fields |
|---|---|---|
raw_ops.batches |
Master batch records | batch_code, status, formulation_id, produced_at, proof_url, qr_image_url, label_pdf_url |
raw_ops.lab_results |
Independent lab test results | batch_id, salmonella_absent, listeria_absent, enterobacteriaceae_cfu_per_g, reported_at |
raw_ops.formulations |
Formulation version tracking | id, version, name, effective_from, effective_until, lab_certificate_pdf_url |
raw_ops.proof_jobs |
Proof generation queue | batch_id, job_key, state, attempts, last_error, processing_duration_ms |
Proof Jobs Table Structure¶
CREATE TABLE raw_ops.proof_jobs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
batch_id UUID NOT NULL REFERENCES raw_ops.batches(id),
job_key TEXT NOT NULL,
state TEXT NOT NULL DEFAULT 'queued',
attempts INTEGER DEFAULT 0,
last_error TEXT,
processing_duration_ms INTEGER,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now(),
CONSTRAINT valid_state CHECK (state IN ('queued', 'processing', 'done', 'failed'))
);
Formulations Table Structure¶
CREATE TABLE raw_ops.formulations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
version TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
description TEXT,
effective_from DATE NOT NULL,
effective_until DATE,
shopify_page_url TEXT NOT NULL,
lab_certificate_pdf_url TEXT,
lab_name TEXT,
certificate_reference TEXT,
analysis_date DATE,
fediaf_verified BOOLEAN DEFAULT true,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
Key Indexes¶
-- Batch code lookup (used during production and customer scans)
CREATE INDEX idx_batches_batch_code ON raw_ops.batches(batch_code);
-- Status filtering for public access
CREATE INDEX idx_batches_status ON raw_ops.batches(status);
-- Lab result lookups
CREATE INDEX idx_lab_results_batch_id ON raw_ops.lab_results(batch_id);
-- Proof job processing
CREATE INDEX idx_proof_jobs_state ON raw_ops.proof_jobs(state)
WHERE state IN ('queued', 'processing');
QR Code System¶
ℹï¸Â IMPORTANT: There are TWO types of QR codes in the Protocol Raw system with different purposes:
| QR Type | Location | Links To |
|---|---|---|
| Proof Verification QR | Product pouch labels | proof.protocolraw.co.uk/batch/{code} |
| Transition Guide QR | In-box insert card | portal.protocolraw.co.uk?view=transition |
Proof Verification QR (On Pouches)¶
These QR codes are batch-specific and generated automatically when a batch is created, before the product is manufactured.
- Content: Proof page URL (
https://proof.protocolraw.co.uk/batch/{batch_code}) - Storage:
proof-assets/qr-codes/{batch_code}.png - Generation: Automatic via API on batch creation
- Size on label: 25mm × 25mm (300 DPI)
Generation Process:
1. Generate QR via API: https://api.qrserver.com/v1/create-qr-code/?size=300x300&format=png&data={url}
2. Download image data
3. Upload to Supabase Storage
4. Store public URL in batches.qr_image_url
Transition Guide QR (On Insert Card)¶
This QR code is universal (same for all boxes) and links to the Customer Portal transition guide.
- Content: Portal URL (
https://portal.protocolraw.co.uk?view=transition) - Purpose: Onboarding â€â€ personalised 10-day transition feeding plan
- Generation: Static (not batch-specific) â€â€ same QR on all cards
- Size on card: Minimum 25mm × 25mm for reliable scanning
Physical Insert Cards (UPDATED v4.2)¶
⚠ï¸ CRITICAL (v4.2): Insert cards are NO LONGER generated per-batch. The
proof_insert_pdf_urlcolumn is deprecated. Insert cards are now universal static cards, bulk printed, with identical content for every box.âš ï¸Â CLARIFICATION: The insert card QR links to the Customer Portal transition guide, NOT the proof page. Proof verification happens via QR codes on the pouches themselves.
Card Purpose¶
- Onboarding new customers with transition guidance
- Explaining the proof verification system
- Driving portal engagement for personalised feeding plans
Card Design: Front Side (Transition)¶
Headline: "Your First 10 Days"
Content:
- Visual transition structure (Day 1-2: 25%, Day 3-4: 50%, etc.)
- QR code → portal.protocolraw.co.uk?view=transition
- CTA: "Scan for your personalised feeding plan"
- Safe handling reminder
Card Design: Back Side (Proof Education)¶
Headline: "Verified Safe, Batch by Batch"
Content: - Explains that every pouch has a QR code linking to lab results - Points customer to scan pouch QR (no separate proof QR on card) - CTA: "Scan any pouch to verify" - Brief explanation of independent lab testing
Card Specifications¶
| Attribute | Specification |
|---|---|
| Size | TBD by designer (A7 or business card format recommended) |
| QR Codes | ONE QR only (front side) → Portal transition guide |
| Batch Code | NONE â€â€ card is universal (same card for all boxes) |
| Print Run | Bulk print — Order in batches of 1,000+ (NOT generated per-batch) |
| Delivery | Stocked at packing location, one card per box |
| Brand Guidelines | Visual Identity Guide v2.2 (Forest Green, Cream, Espresso) |
Key Design Principle¶
The insert card educates and onboards. The pouch QR verifies. These are separate concerns with separate touchpoints.
Workflow¶
1. Batch Creation (Pre-Production)¶
Trigger: Operations team creates batch via ops.protocolraw.co.uk/batches/create
User Interface: Simple form with Production date + KG produced
Product/Formulation: Hardcoded to "Protocol Raw Complete / All Life Stages v1.0"
Inputs:
- production_date: Date batch will be manufactured
- kg_produced: Total kilograms (e.g., 500kg = 1,000 × 500g pouches)
Automatic Actions:
1. Generate unique batch code using format PR-YYMMDD-NNN
2. Generate public batch ID (hashed for competitive protection)
3. Calculate expiry date (production + 1 year)
4. Insert batch record with status QA_HOLD
INSERT INTO raw_ops.batches (
batch_code, produced_at, expiry_date, kg_produced,
product_id, formulation_id, status
) VALUES (
'[auto-generated]',
'[production-date]',
'[production-date + 1 year]',
[kg],
'[product-id]',
'[formulation-id]',
'QA_HOLD'
);
2. Automatic Proof Job Creation (Immediate)¶
Trigger: Database trigger fires on batch INSERT
CREATE TRIGGER trigger_create_proof_job_on_insert
AFTER INSERT ON raw_ops.batches
FOR EACH ROW
EXECUTE FUNCTION raw_ops.create_proof_job_on_batch_insert();
Function Logic:
CREATE OR REPLACE FUNCTION raw_ops.create_proof_job_on_batch_insert()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO raw_ops.proof_jobs (batch_id, job_key, state)
VALUES (NEW.id, 'proof:batch:' || NEW.id::text, 'queued');
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Result: Proof job created with state='queued'
3. Proof Generation Process (Within 1 minute)¶
Processor: Background worker
Steps:
1. Fetch batch data from database
2. Generate proof URL: https://proof.protocolraw.co.uk/batch/{batch_code}
3. Generate QR code via API (this QR goes on pouch labels)
4. Upload to Supabase Storage: proof-assets/qr-codes/{batch_code}.png
5. Update batch record with proof_url and qr_image_url
6. Mark proof job complete
UPDATE raw_ops.proof_jobs
SET state = 'done',
processing_duration_ms = [duration]
WHERE batch_id = '[batch-id]';
Key Difference from v3.0: QR codes now generated at production time (batch creation), not release time. Proof page URL exists but remains private until batch released.
4. Label Generation & Co-packer Delivery (Within 1 minute)¶
Trigger: After proof generation completes
Edge Function: generate-label-pdf
Label Specifications: - Size: 100mm × 150mm (standard for 500g pouches) - Format: PDF, 300 DPI, print-ready - Template: HTML/CSS rendered via Browserless API - Includes: Proof verification QR code (25mm × 25mm)
Label Content: 1. Brand Header: "PROTOCOL RAW" (Montserrat Bold, 14pt), tagline, proof QR code (25mm × 25mm, top right) 2. Product Information: "PROTOCOL RAW COMPLETE", description, net weight 3. Batch Traceability: Batch code (prominent), production date, best before date 4. Ingredients List: Full list in descending order by weight 5. Footer: "Made in the UK", company details, "Verified safe, batch by batch"
Storage Path: https://[project].supabase.co/storage/v1/object/public/proof-assets/batch-labels/{batch_code}.pdf
5. Co-packer Email Notification (Immediate)¶
Trigger: After label PDF generated
Edge Function: send-copacker-email
Email Details:
- From: production@protocolraw.co.uk
- To: Co-packer email (from COPACKER_EMAIL secret)
- Subject: Protocol Raw Batch {batch_code} - Label Artwork
Body:
New Batch Order: PR-251126-001
Please produce the following batch:
Batch Code: PR-251126-001
Production Date: 26 November 2025
Quantity: 500kg (1,000 × 500g pouches)
Product: Protocol Raw Complete
Formulation: All Life Stages v1.0
Instructions:
1. Download and print the attached label artwork
2. Apply labels to all pouches during packing
3. Collect samples for lab testing as per usual process
4. Arrange delivery to our HOLD location once complete
[Download Label Artwork Button]
--
Protocol Raw Ltd
Verified safe, batch by batch
protocolraw.co.uk
Delivery: Via Resend API with retry logic
6. Production & Lab Testing (Days 0-3)¶
Co-packer Actions: 1. Receives email with label artwork 2. Manufactures batch 3. Prints and applies labels with proof QR codes during packing 4. Collects samples for lab testing 5. Ships samples to UKAS-accredited lab 6. Delivers finished product to Protocol Raw HOLD location
Protocol Raw Actions: 1. Receives product at warehouse 2. Physical QA check (count, label verification) 3. Moves to HOLD location (separate from released stock) 4. Waits for lab results
7. Lab Result Processing (Automated - SOP 01)¶
Trigger: Lab result email received
Database Trigger (from SOP 01):
CREATE TRIGGER on_lab_result_insert
AFTER INSERT ON raw_ops.lab_results
FOR EACH ROW
EXECUTE FUNCTION raw_ops.process_lab_result();
Actions:
- If outcome = 'PASS': Update batch status → 'RELEASED'
- If outcome = 'FAIL': Update batch status → 'REJECTED'
- Log to batch_status_history
- Log to ops_events
8. Public Proof Page Delivery (Immediate on Release)¶
Trigger: Batch status changes to RELEASED
What Changes: - RLS policy now allows public access to this batch - Same proof URL that was generated at production - No regeneration needed â€â€ URL was ready all along
Customer Access Methods¶
1. Pouch QR Code Scan (Primary Proof Verification)¶
- Customer scans QR on any pouch label
- Mobile browser opens:
https://proof.protocolraw.co.uk/batch/{batch_code} - Proof page loads with full lab results
2. Insert Card QR Scan (Onboarding)¶
- Customer scans QR on insert card
- Mobile browser opens:
https://portal.protocolraw.co.uk?view=transition - Portal shows personalised 10-day transition feeding plan
3. Direct Link¶
- Included in delivery SMS (SOP 0X)
- Included in order confirmation email
- Accessible via customer portal
4. Manual Batch Lookup¶
- Website form: Enter batch code from pouch label
- Redirects to proof page
Timeline Summary¶
| Phase | What Happens |
|---|---|
| Day 0 (Production) | Proof QR code generated, printed on pouch labels |
| Days 0-3 | Product manufactured, samples at lab, batch in QA_HOLD |
| Day 3 | Lab results arrive, SOP 01 releases batch |
| Day 3+ | Pouch QR codes now link to live proof page with results |
Technical Architecture¶
Edge Functions (Supabase)¶
| Function | Purpose |
|---|---|
create-batch |
Creates batch record and orchestrates proof/label generation |
generate-label-pdf |
Generates print-ready pouch label artwork with proof QR |
send-copacker-email |
Sends label artwork to co-packer via Resend API |
proof-page |
Serves public proof pages with batch data |
Database Triggers¶
trigger_create_proof_job_on_insert
- Fires: After INSERT on raw_ops.batches
- Action: Creates proof job with state='queued'
- Purpose: Initiates proof generation immediately after batch creation
on_lab_result_insert (SOP 01)
- Fires: After INSERT on raw_ops.lab_results
- Actions: Update batch status based on outcome, log to batch_status_history, log to ops_events
Batch Code Generation¶
Format: PR-YYMMDD-NNN
Example:
- First batch on 2025-11-26: PR-251126-001
- Second batch same day: PR-251126-002
- First batch on 2025-11-27: PR-251127-001
Concurrency: Unique constraint on batch_code prevents collisions
External Services¶
| Service | Purpose |
|---|---|
| QR Generation API | https://api.qrserver.com (for pouch proof QRs) |
| PDF Rendering | Browserless API |
| Email Delivery | Resend API |
| CDN | Cloudflare (configured for proof domain) |
Storage Buckets¶
proof-assets/qr-codes/: Proof QR code images (for pouch labels)proof-assets/batch-labels/: Pouch label PDFsproof-assets/lab-reports/: Lab certificate PDFs (SOP 01)
Note: Insert cards with transition QR are managed separately (bulk print, static content). They are not generated per-batch and do not require storage in proof-assets.
Proof Page Content¶
Data Displayed (When RELEASED)¶
Batch Information: - Batch code (large heading) - Production date - Best before date - Product name - Formulation version
Lab Test Results:
- Salmonella Test:
- Status: "NOT DETECTED" (in 25g sample)
-
Visual indicator: Green checkmark
-
Listeria Test:
- Status: "NOT DETECTED" (in 25g sample)
-
Visual indicator: Green checkmark
-
Enterobacteriaceae Test:
- Status: "< 100 cfu/g" (actual value shown)
- Visual indicator: Green checkmark if < 100
Lab Information: - Lab name (UKAS-accredited) - Report date - Certificate reference (if available) - Link to full report PDF
Formulation Information: - FEDIAF compliance badge - Link to formulation page - Nutritional analysis certificate
Performance & Scale Readiness¶
Capacity at 100K Customers¶
| Metric | Volume | Performance | Status |
|---|---|---|---|
| Batches created/day | 70 | <2 sec each | ✅ |
| Proof jobs processed/day | 70 | <1 min each | ✅ |
| QR codes generated/day | 70 | <5 sec each | ✅ |
| Label PDFs generated/day | 70 | <3 sec each | ✅ |
| Co-packer emails sent/day | 70 | <2 sec each | ✅ |
| Public proof page views/day | 1,000+ | <500ms load | ✅ |
| Database queries (proof page) | - | <10ms | ✅ |
Storage Requirements (Annual)¶
| Asset Type | Size per Item | Annual Volume | Total |
|---|---|---|---|
| QR codes | 10 KB | 25,550 | 255 MB |
| Label PDFs | 500 KB | 25,550 | 12.7 GB |
| Lab reports | 2 MB | 25,550 | 51 GB |
| Total | ~64 GB/year |
Cost: Supabase Storage = $0.021/GB/month = ~$1.30/month at scale
Optimization Features¶
- Cloudflare CDN: Proof pages cached globally for <200ms load times
- Database Indexes: Batch lookups <10ms
- RLS Policies: Security enforcement at database level (no app logic needed)
- Static Asset CDN: QR codes and PDFs served from edge locations
Operations Manual¶
Daily Tasks (100% Automated)¶
✅ No manual daily tasks â€â€ System runs autonomously
Monitoring via Automated Alerts Only: - Batch creation failures → Slack alert - Proof generation failures → Slack alert - Label PDF failures → Slack alert - Email delivery failures → Slack alert
Weekly Tasks¶
Review Performance Metrics:
-- Batches created this week
SELECT
DATE(created_at) as date,
COUNT(*) as batches_created
FROM raw_ops.batches
WHERE created_at > now() - interval '7 days'
GROUP BY DATE(created_at);
-- Proof job success rate
SELECT
state,
COUNT(*) as count,
ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) as percentage
FROM raw_ops.proof_jobs
WHERE created_at > now() - interval '7 days'
GROUP BY state;
-- Average processing times
SELECT
AVG(processing_duration_ms) as avg_ms,
MAX(processing_duration_ms) as max_ms
FROM raw_ops.proof_jobs
WHERE state = 'done'
AND created_at > now() - interval '7 days';
Monthly Tasks¶
Verify System Health: - Check proof page accessibility (scan 5 random QR codes) - Verify label PDFs print correctly - Confirm co-packer receiving emails - Review storage costs (should be ~$1-2/month) - Check SSL certificate expiry (Cloudflare auto-renews)
Creating a New Batch (Manual Process)¶
When: Co-packer confirms production date
Steps:
1. Navigate to ops.protocolraw.co.uk/batches/create
2. Enter production date (e.g., 2025-12-01)
3. Enter KG produced (e.g., 500)
4. Click "Create Batch & Generate Labels"
5. Wait for confirmation (~5 seconds)
6. Verify:
- Batch code generated (e.g., PR-251201-001)
- QR code URL accessible
- Label PDF downloadable
- Email sent confirmation
Frequency at Scale: ~70 batches/day = ~3 batches/hour during production hours
Time Required: ~30 seconds per batch
Troubleshooting Guide¶
Issue: Batch Creation Fails¶
Symptoms: Error message in UI, no batch code generated
Diagnosis: 1. Check edge function logs in Supabase 2. Common causes: Invalid date format, database connection failure, missing product/formulation data
Solution:
-- Verify products exist
SELECT * FROM raw_ops.products;
-- Verify active formulation exists
SELECT * FROM raw_ops.formulations
WHERE effective_until IS NULL;
-- Manually retry via SQL if needed
SELECT raw_ops.generate_batch_code('2025-12-01'::DATE);
Issue: Proof Job Stuck in "Queued"¶
Symptoms: Batch created but no QR code generated
Diagnosis:
SELECT * FROM raw_ops.proof_jobs
WHERE state = 'queued'
AND created_at < now() - interval '5 minutes';
Solution:
-- Reset job to queued
UPDATE raw_ops.proof_jobs
SET state = 'queued', attempts = 0, last_error = NULL
WHERE batch_id = '[batch-id]';
-- Or create new job manually
INSERT INTO raw_ops.proof_jobs (batch_id, job_key, state)
VALUES ('[batch-id]', 'proof:batch:[batch-id]', 'queued');
Issue: Label PDF Not Generated¶
Symptoms: Batch created, QR exists, but no label PDF
Diagnosis: Check edge function logs for generate-label-pdf errors
Common Causes: - Browserless API timeout - Invalid QR image URL - Storage permissions issue
Solution:
1. Manually call generate-label-pdf edge function
2. Verify Browserless API key is set
3. Check Supabase Storage bucket permissions
Issue: Co-packer Didn't Receive Email¶
Symptoms: Batch created successfully but co-packer says no email
Diagnosis:
1. Check Resend dashboard for delivery status
2. Verify COPACKER_EMAIL secret is correct
3. Check spam folder
Solution:
-- Get label PDF URL to send manually
SELECT batch_code, label_pdf_url
FROM raw_ops.batches
WHERE batch_code = 'PR-251201-001';
Then forward link manually or manually trigger send-copacker-email function.
Issue: Proof Page Returns "Batch Not Found"¶
Symptoms: Customer scans QR but sees error message
Diagnosis:
-- Check batch status
SELECT batch_code, status, proof_url
FROM raw_ops.batches
WHERE batch_code = 'PR-251201-001';
Causes & Solutions: 1. Status = QA_HOLD: Batch not yet released by lab â€â€ expected behavior, wait for lab results 2. Status = REJECTED: Batch failed lab testing â€â€ expected behavior, batch should not be in customer hands 3. RLS Policy Not Active: Verify RLS enabled
-- Verify RLS enabled
SELECT * FROM pg_policies
WHERE tablename = 'batches'
AND policyname = 'Allow public read on released batches';
Issue: Pouch QR Code Not Scanning¶
Symptoms: QR code on label won't scan
Causes: 1. Print Quality: QR too small or low resolution (minimum 25mm × 25mm at 300 DPI) 2. Damaged Label: QR code scratched or wet 3. Scanner Issue: Camera focus or lighting
Solution: 1. Verify QR image accessible: Copy URL from database and test in browser 2. Print test label at different sizes 3. Use manual batch lookup as fallback
Metrics & KPIs¶
Reliability Targets¶
| Metric | Target | Measurement |
|---|---|---|
| Batch creation success rate | >99.9% | Monitor edge function errors |
| Proof job completion rate | 100% | Query proof_jobs state |
| QR code scannability | >99% | Customer support feedback |
| Label PDF generation success | >99.5% | Monitor Browserless API |
| Email delivery success | >99.5% | Resend dashboard |
| Proof page uptime | >99.9% | Cloudflare analytics |
Performance Targets¶
| Metric | Target | Current |
|---|---|---|
| Batch creation time | <5 sec | ~3 sec |
| Proof generation time | <1 min | ~30 sec |
| QR code generation | <10 sec | ~5 sec |
| Label PDF generation | <5 sec | ~3 sec |
| Email send time | <3 sec | ~2 sec |
| Proof page load time | <1 sec | ~500ms |
Monthly Reporting¶
Automated Metrics Collection:
-- Monthly batch creation summary
SELECT
DATE_TRUNC('month', created_at) as month,
COUNT(*) as total_batches,
COUNT(*) FILTER (WHERE status = 'RELEASED') as released,
COUNT(*) FILTER (WHERE status = 'REJECTED') as rejected,
COUNT(*) FILTER (WHERE status = 'QA_HOLD') as pending
FROM raw_ops.batches
WHERE created_at > now() - interval '3 months'
GROUP BY DATE_TRUNC('month', created_at);
-- Proof job performance
SELECT
DATE_TRUNC('month', created_at) as month,
COUNT(*) as total_jobs,
COUNT(*) FILTER (WHERE state = 'done') as completed,
COUNT(*) FILTER (WHERE state = 'failed') as failed,
AVG(processing_duration_ms) as avg_duration_ms
FROM raw_ops.proof_jobs
WHERE created_at > now() - interval '3 months'
GROUP BY DATE_TRUNC('month', created_at);
Future Enhancements (Phase 2)¶
Potential Features (Not Currently Scoped):
-
Customer Notifications: Email when "your batch" is released, push notifications via app, SMS with proof link
-
Proof Analytics: Track QR scan rates, heatmap of scan locations, time-to-scan metrics
-
Multi-Language Support: Proof pages in multiple languages based on customer locale
-
Historical Batch Lookup: Public database of all released batches, search by date range, product
-
API Access: Partners can query batch status programmatically, webhook notifications on batch release
Compliance & Audit Trail¶
Regulatory Requirements Met¶
- ✅ Traceability: Every pouch traceable to specific batch via pouch QR code
- ✅ Transparency: Independent lab results publicly accessible
- ✅ Immutability: Batch data cannot be edited after creation
- ✅ Audit Trail: All status changes logged in
batch_status_history - ✅ Recall Capability: Can identify all customers who received specific batch
Audit Trail Query¶
-- Complete history for a batch
SELECT
bsh.created_at,
bsh.old_status,
bsh.new_status,
bsh.triggered_by,
bsh.notes
FROM raw_ops.batch_status_history bsh
JOIN raw_ops.batches b ON b.id = bsh.batch_id
WHERE b.batch_code = 'PR-251201-001'
ORDER BY bsh.created_at;
Data Retention¶
| Data Type | Retention Period | Reason |
|---|---|---|
| Batches | Indefinitely | Permanent record |
| Lab Results | Indefinitely | Regulatory requirement |
| Proof QR Codes | Indefinitely | Customer access |
| Pouch Label PDFs | 2 years | Operational reference |
| Proof Jobs | 90 days | Operational monitoring |
System Links & Resources¶
- Batch Creation UI:
https://ops.protocolraw.co.uk/batches/create - Public Proof Pages:
https://proof.protocolraw.co.uk/batch/{batch_code} - Customer Portal:
https://portal.protocolraw.co.uk - Supabase Dashboard:
https://supabase.com/dashboard/project/[project-id]
Edge Functions:
- create-batch: Orchestrates batch creation workflow
- generate-label-pdf: Creates print-ready label artwork
- send-copacker-email: Sends labels to co-packer
- proof-page: Serves public proof pages
External Services:
- QR Generation: https://api.qrserver.com
- PDF Rendering: Browserless API (configured in secrets)
- Email Delivery: Resend API (configured in secrets)
- CDN: Cloudflare (configured for proof domain)
Contact & Escalation¶
Operations Owner: Protocol Raw Operations Team
Technical Owner: Protocol Raw Technical Team
Escalation Path: 1. Check automated monitoring alerts (Slack #ops-alerts) 2. Review edge function logs (Supabase Dashboard) 3. Query database for diagnostic data 4. Check external service status (Resend, Browserless, Cloudflare) 5. If unresolved: Escalate to technical lead
Critical Issues: - Batch creation failure: Immediate fix required (blocks production) - Proof generation failure: High priority (QR codes won't work) - Label PDF failure: High priority (co-packer cannot produce) - Email delivery failure: Medium priority (manual send as backup) - Proof page unavailable: High priority (customer-facing)
Version History¶
| Version | Date | Changes |
|---|---|---|
| v4.2 | 2026-01-21 | Static Insert Card Edition: Removed dynamic proof insert PDF generation. proof_insert_pdf_url deprecated. Insert cards now bulk printed (universal). Proof access via pouch QR + delivery SMS + email. |
| v4.1 | 2026-01-21 | Insert card clarification: QR links to portal transition guide (not proof page). Card is universal (no batch code). Proof QR remains on pouches only. Added two-QR-type documentation. |
| v4.0 | 2025-11-10 | Pre-production integration: QR codes at production time, label PDF generation, co-packer email automation, batch code auto-generation |
| v3.0 | 2025-11-01 | Scale-ready: RLS for public access, formulation version tracking, FEDIAF certification integration, performance indexes |
| v2.0 | 2025-10-15 | Automation: Automatic proof generation on batch release, QR code generation and storage |
| v1.0 | 2025-10-01 | Initial build: Database schema, manual proof page creation |
Approval¶
| Role | Name | Date |
|---|---|---|
| Prepared by | Protocol Raw Operations Team | 2026-01-21 |
| Reviewed by | Technical Lead | |
| Approved by | Founder |
Effective Date: 2026-01-21
Status: ✅ Production Ready for 100,000+ Customers
End of SOP 0R v4.2
Last reviewed: 2026-01-21
Next review: Monthly via automated monitoring
System status: ✅ Production Ready - Static Insert Card Implementation Complete