SOP-FIN-02: Cost Tracking System v1.0¶

Supplier invoice management, cost allocation to batches and shipments, and unit economics

Document ID: SOP-FIN-02-v1.0
Version: 1.0
Status: ✅ Production Ready
Last Updated: 2026-03-20
Owner: Protocol Raw Operations
Replaces: SOP-05-v3.0
Review Date: 2026-06-20

Key Changes from v3.0¶

Complete Rewrite — New System Design: - ✅ Retired Make.com "SOP 05: Invoice Ingestion & Parsing" scenario — replaced by manual Ops Portal entry for Phase A volumes - ✅ Retired handle_new_invoice_job() trigger function — over-engineered for Phase A; processing logic moved to PostgreSQL functions called by Ops Portal - ✅ Retired on_new_invoice_job trigger — no longer needed - ✅ Retired send-slack-alert Edge Function reference — standardised to ops-alerter via SOP-MON-01 - ✅ Retired all 6 v3.0 monitoring views — replaced by cost-tracking-specific analytics views

New Schema: - ✅ raw_ops.suppliers — Supplier registry with type categorisation - ✅ raw_ops.supplier_invoices — Invoice headers with payment status tracking - ✅ raw_ops.invoice_line_items — Line items with category, batch/shipment linkage - ✅ Idempotency via unique constraint on (supplier_id, invoice_number) - ✅ ops_events audit logging on all cost mutations

New Analytics Views: - ✅ analytics.v_batch_cogs — COGS per batch with category breakdown - ✅ analytics.v_shipment_cpd — Cost per delivery - ✅ analytics.v_unit_economics — Combined P&L per batch - ✅ analytics.v_supplier_spend — Monthly spend by supplier - ✅ analytics.v_cost_category_trend — Weekly spend by category

New Monitoring: - ✅ fn_check_cost_tracking_health_v2() — SOP-MON-01 pattern monitoring - ✅ Detects: missing COGS on released batches, missing CPD on dispatched shipments, stale draft invoices, overdue payments, cost anomalies

Ops Portal: - ✅ New Cost Tracking section with Suppliers, Invoices, and Dashboard views

Phase B Extension (documented, not built): - ✅ AI invoice parsing pipeline architecture specified for future implementation when co-packer/3PL invoice volume justifies automation

Purpose¶

This SOP defines the system for tracking all supplier costs against production batches and customer shipments. The system provides:

Supplier management: Registry of all vendors with type categorisation
Invoice recording: Structured entry of invoice headers and line items
Cost allocation: Linking costs to specific batches (COGS) and shipments (CPD)
Unit economics: Real-time views of cost per kg produced, cost per delivery, and gross margin per batch
Monitoring: Automated detection of missing costs, anomalies, and payment status

Scope¶

Applies to: All supplier invoices received by Protocol Raw

Phase A invoice types: - Raw materials (meat, organ, eggs, oils) — via ingredient supplier(s) - Functional preblend ingredients or batch-dose units — via blender/packer or direct sourcing - Packaging (trays, labels, film) - Courier (DPD delivery invoices) - Lab testing (UKAS lab fees per batch) - Facility costs (rent, utilities, equipment) - Other operational costs

Users: - Founder / Operations Lead (manual invoice entry via Ops Portal) - Automated systems (monitoring, analytics) - Metabase dashboards (cost tracking, unit economics)

Related SOPs: - SOP-MON-01: Monitoring & Alerting Architecture (monitoring pattern) - SOP-LAB-01: Batch Creation & Lab-to-Release (batch lifecycle) - SOP-INV-01: Inventory Control (allocation, stock levels) - SOP-DLV-01: Courier Watchdog (shipment lifecycle)

System Architecture¶

Data Flow¶

Founder receives invoice (email/paper/portal)
    ↓
Ops Portal → Cost Tracking → New Invoice
    ↓
Edge Function: save-invoice
    ↓
PostgreSQL: raw_ops.fn_save_invoice_v1()
    - Validates supplier exists
    - Creates invoice header + line items
    - Links line items to batches/shipments where specified
    - Logs ops_events for every cost mutation
    - Returns invoice ID
    ↓
Analytics views auto-update (v_batch_cogs, v_shipment_cpd, etc.)
    ↓
Metabase dashboards reflect real-time cost data

Monitoring¶

pg_cron (daily at 09:00 UTC)
    ↓
pg_net (async HTTP)
    ↓
run-monitor (Edge Function)
    ↓
fn_check_cost_tracking_health_v2() (PostgreSQL function)
    ↓
monitoring_runs (logging)
    ↓
ops-alerter (Edge Function) [if threshold breached]
    ↓
Slack (#ops-alerts)

Database Schema¶

1. Suppliers Table¶

CREATE TABLE raw_ops.suppliers (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name text NOT NULL,
    type text NOT NULL CHECK (type IN (
        'raw_materials', 'packaging', 'courier', 'lab', 
        'facility', 'preblend', 'other'
    )),
    email text,
    payment_terms_days integer DEFAULT 30,
    is_active boolean DEFAULT true,
    notes text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

-- Indexes
CREATE INDEX idx_suppliers_type ON raw_ops.suppliers(type);
CREATE INDEX idx_suppliers_active ON raw_ops.suppliers(is_active) WHERE is_active = true;

-- Comments
COMMENT ON TABLE raw_ops.suppliers IS 'Registry of all suppliers providing goods/services to Protocol Raw';
COMMENT ON COLUMN raw_ops.suppliers.type IS 'Supplier category: raw_materials, packaging, courier, lab, facility, preblend, other';
COMMENT ON COLUMN raw_ops.suppliers.payment_terms_days IS 'Standard payment terms in days from invoice date';

-- RLS
ALTER TABLE raw_ops.suppliers ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Service role full access on suppliers"
    ON raw_ops.suppliers
    FOR ALL
    USING (auth.role() = 'service_role')
    WITH CHECK (auth.role() = 'service_role');

2. Supplier Invoices Table¶

CREATE TABLE raw_ops.supplier_invoices (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    supplier_id uuid NOT NULL REFERENCES raw_ops.suppliers(id),
    invoice_number text NOT NULL,
    invoice_date date NOT NULL,
    due_date date,
    total_amount_gbp numeric(10,2) NOT NULL,
    vat_amount_gbp numeric(10,2) DEFAULT 0,
    net_amount_gbp numeric(10,2) NOT NULL,
    currency text NOT NULL DEFAULT 'GBP',
    status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'approved', 'paid')),
    payment_date date,
    invoice_pdf_url text,
    source text NOT NULL DEFAULT 'manual' CHECK (source IN ('manual', 'ai_parsed')),
    notes text,
    created_by uuid,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),

    -- Idempotency: prevent duplicate entry of the same invoice from the same supplier
    CONSTRAINT uq_supplier_invoice UNIQUE (supplier_id, invoice_number)
);

-- Indexes
CREATE INDEX idx_supplier_invoices_supplier ON raw_ops.supplier_invoices(supplier_id);
CREATE INDEX idx_supplier_invoices_status ON raw_ops.supplier_invoices(status);
CREATE INDEX idx_supplier_invoices_date ON raw_ops.supplier_invoices(invoice_date DESC);
CREATE INDEX idx_supplier_invoices_due_date ON raw_ops.supplier_invoices(due_date)
    WHERE status != 'paid';
CREATE INDEX idx_supplier_invoices_draft_stale 
    ON raw_ops.supplier_invoices(status, created_at DESC)
    WHERE status = 'draft';

-- Comments
COMMENT ON TABLE raw_ops.supplier_invoices IS 'Invoice headers from all suppliers. Phase A: manual entry. Phase B: AI-parsed ingestion.';
COMMENT ON COLUMN raw_ops.supplier_invoices.net_amount_gbp IS 'Ex-VAT amount. All unit economics use net figures.';
COMMENT ON COLUMN raw_ops.supplier_invoices.source IS 'How this invoice was entered: manual (Ops Portal) or ai_parsed (Phase B automation)';

-- RLS
ALTER TABLE raw_ops.supplier_invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Service role full access on supplier_invoices"
    ON raw_ops.supplier_invoices
    FOR ALL
    USING (auth.role() = 'service_role')
    WITH CHECK (auth.role() = 'service_role');

3. Invoice Line Items Table¶

CREATE TABLE raw_ops.invoice_line_items (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    invoice_id uuid NOT NULL REFERENCES raw_ops.supplier_invoices(id) ON DELETE CASCADE,
    description text NOT NULL,
    category text NOT NULL CHECK (category IN (
        'meat', 'organ', 'egg', 'oil', 'preblend', 'fibre',
        'packaging', 'courier', 'lab_testing', 'facility',
        'coolant', 'other'
    )),
    quantity numeric,
    unit text,
    unit_price_gbp numeric(10,4),
    line_total_gbp numeric(10,2) NOT NULL,
    vat_rate numeric(4,2) DEFAULT 0.20,
    batch_id uuid REFERENCES raw_ops.batches(id),
    shipment_id uuid REFERENCES raw_ops.shipments(id),
    notes text,
    created_at timestamptz NOT NULL DEFAULT now()
);

-- Indexes
CREATE INDEX idx_line_items_invoice ON raw_ops.invoice_line_items(invoice_id);
CREATE INDEX idx_line_items_batch ON raw_ops.invoice_line_items(batch_id) WHERE batch_id IS NOT NULL;
CREATE INDEX idx_line_items_shipment ON raw_ops.invoice_line_items(shipment_id) WHERE shipment_id IS NOT NULL;
CREATE INDEX idx_line_items_category ON raw_ops.invoice_line_items(category);

-- Comments
COMMENT ON TABLE raw_ops.invoice_line_items IS 'Individual line items from supplier invoices, linked to batches and/or shipments for cost allocation';
COMMENT ON COLUMN raw_ops.invoice_line_items.category IS 'Cost category for analytics: meat, organ, egg, oil, preblend, fibre, packaging, courier, lab_testing, facility, coolant, other';
COMMENT ON COLUMN raw_ops.invoice_line_items.batch_id IS 'Links this cost to a specific production batch (COGS). Nullable — not all costs are batch-specific.';
COMMENT ON COLUMN raw_ops.invoice_line_items.shipment_id IS 'Links this cost to a specific shipment (CPD). Nullable — not all costs are shipment-specific.';
COMMENT ON COLUMN raw_ops.invoice_line_items.line_total_gbp IS 'Net (ex-VAT) line total. All unit economics use net figures.';

-- RLS
ALTER TABLE raw_ops.invoice_line_items ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Service role full access on invoice_line_items"
    ON raw_ops.invoice_line_items
    FOR ALL
    USING (auth.role() = 'service_role')
    WITH CHECK (auth.role() = 'service_role');

Database Functions¶

1. Save Invoice (Ops Portal Entry)¶

CREATE OR REPLACE FUNCTION raw_ops.fn_save_invoice_v1(
    p_supplier_id uuid,
    p_invoice_number text,
    p_invoice_date date,
    p_due_date date DEFAULT NULL,
    p_total_amount_gbp numeric DEFAULT 0,
    p_vat_amount_gbp numeric DEFAULT 0,
    p_net_amount_gbp numeric DEFAULT 0,
    p_invoice_pdf_url text DEFAULT NULL,
    p_notes text DEFAULT NULL,
    p_created_by uuid DEFAULT NULL,
    p_line_items jsonb DEFAULT '[]'::jsonb
)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = raw_ops, public
AS $$
DECLARE
    v_invoice_id uuid;
    v_supplier_name text;
    v_line_item jsonb;
    v_line_item_id uuid;
    v_due_date date;
BEGIN
    -- Validate supplier exists and is active
    SELECT name INTO v_supplier_name
    FROM raw_ops.suppliers
    WHERE id = p_supplier_id AND is_active = true;

    IF v_supplier_name IS NULL THEN
        RETURN jsonb_build_object(
            'success', false,
            'error', 'Supplier not found or inactive'
        );
    END IF;

    -- Calculate due date from payment terms if not provided
    IF p_due_date IS NULL THEN
        SELECT p_invoice_date + payment_terms_days INTO v_due_date
        FROM raw_ops.suppliers
        WHERE id = p_supplier_id;
    ELSE
        v_due_date := p_due_date;
    END IF;

    -- Insert invoice header
    INSERT INTO raw_ops.supplier_invoices (
        supplier_id, invoice_number, invoice_date, due_date,
        total_amount_gbp, vat_amount_gbp, net_amount_gbp,
        invoice_pdf_url, notes, created_by, source
    ) VALUES (
        p_supplier_id, p_invoice_number, p_invoice_date, v_due_date,
        p_total_amount_gbp, p_vat_amount_gbp, p_net_amount_gbp,
        p_invoice_pdf_url, p_notes, p_created_by, 'manual'
    )
    RETURNING id INTO v_invoice_id;

    -- Log invoice creation
    INSERT INTO raw_ops.ops_events (entity_type, entity_id, kind, message, meta)
    VALUES ('invoice', v_invoice_id, 'INFO', 'Invoice created: ' || p_invoice_number,
        jsonb_build_object(
            'supplier', v_supplier_name,
            'invoice_number', p_invoice_number,
            'net_amount_gbp', p_net_amount_gbp,
            'source', 'manual'
        )
    );

    -- Insert line items
    FOR v_line_item IN SELECT * FROM jsonb_array_elements(p_line_items)
    LOOP
        INSERT INTO raw_ops.invoice_line_items (
            invoice_id, description, category, quantity, unit,
            unit_price_gbp, line_total_gbp, vat_rate,
            batch_id, shipment_id, notes
        ) VALUES (
            v_invoice_id,
            v_line_item->>'description',
            v_line_item->>'category',
            (v_line_item->>'quantity')::numeric,
            v_line_item->>'unit',
            (v_line_item->>'unit_price_gbp')::numeric,
            (v_line_item->>'line_total_gbp')::numeric,
            COALESCE((v_line_item->>'vat_rate')::numeric, 0.20),
            (v_line_item->>'batch_id')::uuid,
            (v_line_item->>'shipment_id')::uuid,
            v_line_item->>'notes'
        )
        RETURNING id INTO v_line_item_id;

        -- Log batch cost allocation
        IF (v_line_item->>'batch_id') IS NOT NULL THEN
            INSERT INTO raw_ops.ops_events (entity_type, entity_id, kind, message, meta)
            VALUES ('batch', (v_line_item->>'batch_id')::uuid, 'INFO',
                'COGS updated via invoice ' || p_invoice_number,
                jsonb_build_object(
                    'invoice_id', v_invoice_id,
                    'invoice_number', p_invoice_number,
                    'supplier', v_supplier_name,
                    'category', v_line_item->>'category',
                    'line_total_gbp', v_line_item->>'line_total_gbp',
                    'line_item_id', v_line_item_id
                )
            );
        END IF;

        -- Log shipment cost allocation
        IF (v_line_item->>'shipment_id') IS NOT NULL THEN
            INSERT INTO raw_ops.ops_events (entity_type, entity_id, kind, message, meta)
            VALUES ('shipment', (v_line_item->>'shipment_id')::uuid, 'INFO',
                'CPD updated via invoice ' || p_invoice_number,
                jsonb_build_object(
                    'invoice_id', v_invoice_id,
                    'invoice_number', p_invoice_number,
                    'supplier', v_supplier_name,
                    'category', v_line_item->>'category',
                    'line_total_gbp', v_line_item->>'line_total_gbp',
                    'line_item_id', v_line_item_id
                )
            );
        END IF;
    END LOOP;

    RETURN jsonb_build_object(
        'success', true,
        'invoice_id', v_invoice_id,
        'line_items_count', jsonb_array_length(p_line_items)
    );

EXCEPTION WHEN unique_violation THEN
    RETURN jsonb_build_object(
        'success', false,
        'error', 'Invoice ' || p_invoice_number || ' already exists for this supplier'
    );
WHEN OTHERS THEN
    -- Log detailed error internally, return bounded message to caller
    INSERT INTO raw_ops.ops_events (entity_type, entity_id, kind, message, meta)
    VALUES ('invoice', COALESCE(v_invoice_id, '00000000-0000-0000-0000-000000000000'::uuid), 'ERROR',
        'Invoice save failed: ' || p_invoice_number,
        jsonb_build_object('error', SQLERRM, 'supplier_id', p_supplier_id)
    );
    RETURN jsonb_build_object(
        'success', false,
        'error', 'Invoice save failed. Please check inputs and try again.'
    );
END;
$$;

COMMENT ON FUNCTION raw_ops.fn_save_invoice_v1 IS 'Creates an invoice with line items, links costs to batches/shipments, logs to ops_events. Called by save-invoice Edge Function.';

2. Update Invoice Status¶

CREATE OR REPLACE FUNCTION raw_ops.fn_update_invoice_status_v1(
    p_invoice_id uuid,
    p_new_status text,
    p_payment_date date DEFAULT NULL
)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = raw_ops, public
AS $$
DECLARE
    v_current_status text;
    v_invoice_number text;
BEGIN
    -- Get current status
    SELECT status, invoice_number INTO v_current_status, v_invoice_number
    FROM raw_ops.supplier_invoices
    WHERE id = p_invoice_id;

    IF v_current_status IS NULL THEN
        RETURN jsonb_build_object('success', false, 'error', 'Invoice not found');
    END IF;

    -- Validate state transition
    IF NOT (
        (v_current_status = 'draft' AND p_new_status = 'approved') OR
        (v_current_status = 'approved' AND p_new_status = 'paid') OR
        (v_current_status = 'approved' AND p_new_status = 'draft') -- Allow reverting to draft
    ) THEN
        RETURN jsonb_build_object(
            'success', false,
            'error', 'Invalid transition: ' || v_current_status || ' → ' || p_new_status
        );
    END IF;

    -- Update status
    UPDATE raw_ops.supplier_invoices
    SET 
        status = p_new_status,
        payment_date = CASE WHEN p_new_status = 'paid' THEN COALESCE(p_payment_date, CURRENT_DATE) ELSE payment_date END,
        updated_at = now()
    WHERE id = p_invoice_id;

    -- Log status change
    INSERT INTO raw_ops.ops_events (entity_type, entity_id, kind, message, meta)
    VALUES ('invoice', p_invoice_id, 'INFO',
        'Invoice ' || v_invoice_number || ' status: ' || v_current_status || ' → ' || p_new_status,
        jsonb_build_object(
            'invoice_number', v_invoice_number,
            'old_status', v_current_status,
            'new_status', p_new_status,
            'payment_date', p_payment_date
        )
    );

    RETURN jsonb_build_object('success', true, 'invoice_id', p_invoice_id, 'new_status', p_new_status);
END;
$$;

COMMENT ON FUNCTION raw_ops.fn_update_invoice_status_v1 IS 'Transitions invoice status (draft → approved → paid) with validation and ops_events logging.';

Analytics Views¶

1. Batch COGS¶

CREATE OR REPLACE VIEW analytics.v_batch_cogs AS
WITH eligible_line_items AS (
    -- Only include line items from approved or paid invoices (not drafts)
    SELECT li.*
    FROM raw_ops.invoice_line_items li
    JOIN raw_ops.supplier_invoices si ON si.id = li.invoice_id
    WHERE si.status IN ('approved', 'paid')
)
SELECT
    b.id AS batch_id,
    b.batch_code,
    b.status AS batch_status,
    b.kg_produced,
    b.produced_at,
    COALESCE(SUM(li.line_total_gbp), 0) AS total_cogs_gbp,
    CASE 
        WHEN b.kg_produced > 0 THEN ROUND(COALESCE(SUM(li.line_total_gbp), 0) / b.kg_produced, 2)
        ELSE 0 
    END AS cogs_per_kg_gbp,
    COALESCE(SUM(li.line_total_gbp) FILTER (WHERE li.category IN ('meat', 'organ')), 0) AS meat_cost_gbp,
    COALESCE(SUM(li.line_total_gbp) FILTER (WHERE li.category = 'egg'), 0) AS egg_cost_gbp,
    COALESCE(SUM(li.line_total_gbp) FILTER (WHERE li.category = 'oil'), 0) AS oil_cost_gbp,
    COALESCE(SUM(li.line_total_gbp) FILTER (WHERE li.category IN ('preblend', 'fibre')), 0) AS preblend_cost_gbp,
    COALESCE(SUM(li.line_total_gbp) FILTER (WHERE li.category = 'packaging'), 0) AS packaging_cost_gbp,
    COALESCE(SUM(li.line_total_gbp) FILTER (WHERE li.category = 'lab_testing'), 0) AS lab_cost_gbp,
    COALESCE(SUM(li.line_total_gbp) FILTER (WHERE li.category = 'facility'), 0) AS facility_cost_gbp,
    COALESCE(SUM(li.line_total_gbp) FILTER (WHERE li.category NOT IN ('meat', 'organ', 'egg', 'oil', 'preblend', 'fibre', 'packaging', 'lab_testing', 'facility')), 0) AS other_cost_gbp,
    COUNT(DISTINCT li.invoice_id) AS invoice_count,
    COUNT(li.id) AS line_item_count
FROM raw_ops.batches b
LEFT JOIN eligible_line_items li ON li.batch_id = b.id
GROUP BY b.id, b.batch_code, b.status, b.kg_produced, b.produced_at;

COMMENT ON VIEW analytics.v_batch_cogs IS 'COGS per batch with category breakdown. Only includes line items from approved/paid invoices. All figures are net (ex-VAT).';

GRANT SELECT ON analytics.v_batch_cogs TO authenticated;

2. Shipment CPD¶

CREATE OR REPLACE VIEW analytics.v_shipment_cpd AS
WITH eligible_line_items AS (
    SELECT li.*
    FROM raw_ops.invoice_line_items li
    JOIN raw_ops.supplier_invoices si ON si.id = li.invoice_id
    WHERE si.status IN ('approved', 'paid')
)
SELECT
    s.id AS shipment_id,
    s.order_id,
    s.tracking_no,
    s.status AS shipment_status,
    s.dispatched_at,
    COALESCE(SUM(li.line_total_gbp), 0) AS total_cpd_gbp,
    COALESCE(SUM(li.line_total_gbp) FILTER (WHERE li.category = 'courier'), 0) AS courier_cost_gbp,
    COALESCE(SUM(li.line_total_gbp) FILTER (WHERE li.category = 'packaging'), 0) AS packaging_cost_gbp,
    COALESCE(SUM(li.line_total_gbp) FILTER (WHERE li.category = 'coolant'), 0) AS coolant_cost_gbp,
    COALESCE(SUM(li.line_total_gbp) FILTER (WHERE li.category NOT IN ('courier', 'packaging', 'coolant')), 0) AS other_cost_gbp,
    COUNT(li.id) AS line_item_count
FROM raw_ops.shipments s
LEFT JOIN eligible_line_items li ON li.shipment_id = s.id
GROUP BY s.id, s.order_id, s.tracking_no, s.status, s.dispatched_at;

COMMENT ON VIEW analytics.v_shipment_cpd IS 'Cost per delivery with category breakdown. Only includes line items from approved/paid invoices. All figures are net (ex-VAT).';

GRANT SELECT ON analytics.v_shipment_cpd TO authenticated;

3. Unit Economics¶

CREATE OR REPLACE VIEW analytics.v_unit_economics AS
SELECT
    bc.batch_id,
    bc.batch_code,
    bc.batch_status,
    bc.kg_produced,
    bc.produced_at,
    bc.total_cogs_gbp,
    bc.cogs_per_kg_gbp,
    COALESCE(cpd.total_cpd_for_batch, 0) AS total_cpd_gbp,
    COALESCE(cpd.shipment_count, 0) AS shipments_from_batch,
    CASE 
        WHEN COALESCE(cpd.shipment_count, 0) > 0 
        THEN ROUND(COALESCE(cpd.total_cpd_for_batch, 0) / cpd.shipment_count, 2)
        ELSE 0 
    END AS avg_cpd_per_shipment_gbp,
    bc.total_cogs_gbp + COALESCE(cpd.total_cpd_for_batch, 0) AS total_cost_gbp,
    CASE 
        WHEN bc.kg_produced > 0 
        THEN ROUND((bc.total_cogs_gbp + COALESCE(cpd.total_cpd_for_batch, 0)) / bc.kg_produced, 2)
        ELSE 0 
    END AS total_cost_per_kg_gbp,
    -- Category breakdown from COGS
    bc.meat_cost_gbp,
    bc.egg_cost_gbp,
    bc.oil_cost_gbp,
    bc.preblend_cost_gbp,
    bc.packaging_cost_gbp AS batch_packaging_cost_gbp,
    bc.lab_cost_gbp,
    bc.facility_cost_gbp,
    bc.invoice_count AS cogs_invoice_count,
    bc.line_item_count AS cogs_line_item_count
FROM analytics.v_batch_cogs bc
LEFT JOIN (
    SELECT
        a.batch_id,
        SUM(sc.total_cpd_gbp) AS total_cpd_for_batch,
        COUNT(DISTINCT sc.shipment_id) AS shipment_count
    FROM raw_ops.allocations a
    JOIN analytics.v_shipment_cpd sc ON sc.order_id = a.order_id
    GROUP BY a.batch_id
) cpd ON cpd.batch_id = bc.batch_id;

COMMENT ON VIEW analytics.v_unit_economics IS 'Combined COGS + CPD per batch. Links batches to their allocated shipments for full cost picture. All figures are net (ex-VAT).';

GRANT SELECT ON analytics.v_unit_economics TO authenticated;

4. Supplier Spend¶

CREATE OR REPLACE VIEW analytics.v_supplier_spend AS
SELECT
    s.id AS supplier_id,
    s.name AS supplier_name,
    s.type AS supplier_type,
    date_trunc('month', si.invoice_date)::date AS month,
    COUNT(si.id) AS invoice_count,
    SUM(si.net_amount_gbp) AS net_spend_gbp,
    SUM(si.total_amount_gbp) AS gross_spend_gbp,
    SUM(si.vat_amount_gbp) AS vat_gbp
FROM raw_ops.suppliers s
JOIN raw_ops.supplier_invoices si ON si.supplier_id = s.id
WHERE si.status != 'draft'
GROUP BY s.id, s.name, s.type, date_trunc('month', si.invoice_date)
ORDER BY month DESC, net_spend_gbp DESC;

COMMENT ON VIEW analytics.v_supplier_spend IS 'Monthly spend by supplier. Excludes draft invoices. All figures include both approved and paid.';

GRANT SELECT ON analytics.v_supplier_spend TO authenticated;

5. Cost Category Trend¶

CREATE OR REPLACE VIEW analytics.v_cost_category_trend AS
SELECT
    date_trunc('week', si.invoice_date)::date AS week,
    li.category,
    COUNT(li.id) AS line_items,
    SUM(li.line_total_gbp) AS total_gbp,
    ROUND(AVG(li.line_total_gbp), 2) AS avg_line_total_gbp
FROM raw_ops.invoice_line_items li
JOIN raw_ops.supplier_invoices si ON li.invoice_id = si.id
WHERE si.status != 'draft'
GROUP BY date_trunc('week', si.invoice_date), li.category
ORDER BY week DESC, total_gbp DESC;

COMMENT ON VIEW analytics.v_cost_category_trend IS 'Weekly spend by cost category. Detects cost drift early. Excludes draft invoices.';

GRANT SELECT ON analytics.v_cost_category_trend TO authenticated;

Edge Functions¶

Authentication Model¶

All Edge Functions in this SOP have JWT verification enabled via verify_jwt = true in their respective config.toml files. Supabase's platform-level JWT enforcement rejects unauthenticated requests before the function code executes.

The save-invoice function additionally resolves the authenticated user via supabaseAuth.auth.getUser() because it needs the user ID for the created_by field. The other functions (update-invoice-status, manage-supplier, delete-draft-invoice) do not need the user ID for their logic, so platform-level JWT enforcement is sufficient.

Input Validation¶

All Edge Functions must validate required fields, enum membership, and numeric bounds before calling RPCs or performing mutations. Invalid requests should return a 400 response with a bounded error message. Implementation detail is left to Claude Code, but the following must be enforced:

save-invoice: supplier_id (uuid), invoice_number (non-empty string), invoice_date (valid date), net_amount_gbp (positive number), line_items (array with at least one element, each with description, category from allowed enum, and line_total_gbp)
update-invoice-status: invoice_id (uuid), new_status (one of: draft, approved, paid)
manage-supplier: action (one of: create, update, deactivate), name (required for create), type (from allowed enum for create/update)
delete-draft-invoice: invoice_id (uuid)

1. save-invoice¶

Purpose: Thin wrapper for Ops Portal invoice entry. Extracts authenticated user from JWT. Calls fn_save_invoice_v1().

Endpoint: POST /functions/v1/save-invoice
JWT Verification: Enabled (authenticated users only)

import { serve } from "https://deno.land/std@0.168.0/http/server.ts";
import { createClient } from "https://esm.sh/@supabase/supabase-js@2";

const ALLOWED_ORIGINS = [
  "https://ops.protocolraw.co.uk",
  "http://localhost:3000", // Local development only
];

function getCorsHeaders(req: Request) {
  const origin = req.headers.get("origin") || "";
  const allowedOrigin = ALLOWED_ORIGINS.includes(origin) ? origin : ALLOWED_ORIGINS[0];
  return {
    "Access-Control-Allow-Origin": allowedOrigin,
    "Access-Control-Allow-Headers": "authorization, x-client-info, apikey, content-type",
  };
}

serve(async (req) => {
  const corsHeaders = getCorsHeaders(req);

  if (req.method === "OPTIONS") {
    return new Response("ok", { headers: corsHeaders });
  }

  try {
    // Extract authenticated user from JWT
    const authHeader = req.headers.get("authorization") ?? "";
    const supabaseAuth = createClient(
      Deno.env.get("SUPABASE_URL") ?? "",
      Deno.env.get("SUPABASE_ANON_KEY") ?? "",
      { global: { headers: { Authorization: authHeader } } }
    );
    const { data: { user }, error: authError } = await supabaseAuth.auth.getUser();
    if (authError || !user) {
      return new Response(
        JSON.stringify({ success: false, error: "Unauthorized" }),
        { status: 401, headers: { ...corsHeaders, "Content-Type": "application/json" } }
      );
    }

    // Service role client for DB operations
    const supabaseClient = createClient(
      Deno.env.get("SUPABASE_URL") ?? "",
      Deno.env.get("SUPABASE_SERVICE_ROLE_KEY") ?? ""
    );

    const body = await req.json();

    const { data, error } = await supabaseClient.rpc("fn_save_invoice_v1", {
      p_supplier_id: body.supplier_id,
      p_invoice_number: body.invoice_number,
      p_invoice_date: body.invoice_date,
      p_due_date: body.due_date || null,
      p_total_amount_gbp: body.total_amount_gbp,
      p_vat_amount_gbp: body.vat_amount_gbp || 0,
      p_net_amount_gbp: body.net_amount_gbp,
      p_invoice_pdf_url: body.invoice_pdf_url || null,
      p_notes: body.notes || null,
      p_created_by: user.id, // Derived from JWT, not client body
      p_line_items: JSON.stringify(body.line_items || []),
    });

    if (error) throw error;

    return new Response(JSON.stringify(data), {
      headers: { ...corsHeaders, "Content-Type": "application/json" },
    });
  } catch (error) {
    return new Response(
      JSON.stringify({ success: false, error: "Invoice save failed. Check inputs and try again." }),
      {
        status: 400,
        headers: { ...corsHeaders, "Content-Type": "application/json" },
      }
    );
  }
});

2. update-invoice-status¶

Purpose: Thin wrapper for invoice status transitions. Calls fn_update_invoice_status_v1().

Endpoint: POST /functions/v1/update-invoice-status
JWT Verification: Enabled (authenticated users only)

import { serve } from "https://deno.land/std@0.168.0/http/server.ts";
import { createClient } from "https://esm.sh/@supabase/supabase-js@2";

const ALLOWED_ORIGINS = [
  "https://ops.protocolraw.co.uk",
  "http://localhost:3000",
];

function getCorsHeaders(req: Request) {
  const origin = req.headers.get("origin") || "";
  const allowedOrigin = ALLOWED_ORIGINS.includes(origin) ? origin : ALLOWED_ORIGINS[0];
  return {
    "Access-Control-Allow-Origin": allowedOrigin,
    "Access-Control-Allow-Headers": "authorization, x-client-info, apikey, content-type",
  };
}

serve(async (req) => {
  const corsHeaders = getCorsHeaders(req);

  if (req.method === "OPTIONS") {
    return new Response("ok", { headers: corsHeaders });
  }

  try {
    const supabaseClient = createClient(
      Deno.env.get("SUPABASE_URL") ?? "",
      Deno.env.get("SUPABASE_SERVICE_ROLE_KEY") ?? ""
    );

    const body = await req.json();

    const { data, error } = await supabaseClient.rpc("fn_update_invoice_status_v1", {
      p_invoice_id: body.invoice_id,
      p_new_status: body.new_status,
      p_payment_date: body.payment_date || null,
    });

    if (error) throw error;

    return new Response(JSON.stringify(data), {
      headers: { ...corsHeaders, "Content-Type": "application/json" },
    });
  } catch (error) {
    return new Response(
      JSON.stringify({ success: false, error: "Status update failed. Check inputs and try again." }),
      {
        status: 400,
        headers: { ...corsHeaders, "Content-Type": "application/json" },
      }
    );
  }
});

3. manage-supplier¶

Purpose: Thin wrapper for supplier CRUD. All supplier mutations go through this Edge Function to maintain consistent mutation ownership.

Endpoint: POST /functions/v1/manage-supplier
JWT Verification: Enabled (authenticated users only)

import { serve } from "https://deno.land/std@0.168.0/http/server.ts";
import { createClient } from "https://esm.sh/@supabase/supabase-js@2";

const ALLOWED_ORIGINS = [
  "https://ops.protocolraw.co.uk",
  "http://localhost:3000",
];

function getCorsHeaders(req: Request) {
  const origin = req.headers.get("origin") || "";
  const allowedOrigin = ALLOWED_ORIGINS.includes(origin) ? origin : ALLOWED_ORIGINS[0];
  return {
    "Access-Control-Allow-Origin": allowedOrigin,
    "Access-Control-Allow-Headers": "authorization, x-client-info, apikey, content-type",
  };
}

serve(async (req) => {
  const corsHeaders = getCorsHeaders(req);

  if (req.method === "OPTIONS") {
    return new Response("ok", { headers: corsHeaders });
  }

  try {
    const supabaseClient = createClient(
      Deno.env.get("SUPABASE_URL") ?? "",
      Deno.env.get("SUPABASE_SERVICE_ROLE_KEY") ?? ""
    );

    const body = await req.json();
    const action = body.action; // 'create', 'update', 'deactivate'

    let result;

    if (action === "create") {
      const { data, error } = await supabaseClient
        .from("suppliers")
        .insert({
          name: body.name,
          type: body.type,
          email: body.email || null,
          payment_terms_days: body.payment_terms_days || 30,
          notes: body.notes || null,
        })
        .select()
        .single();
      if (error) throw error;

      // Log to ops_events
      await supabaseClient.from("ops_events").insert({
        entity_type: "supplier",
        entity_id: data.id,
        kind: "INFO",
        message: `Supplier created: ${body.name}`,
        meta: { supplier_type: body.type },
      });

      result = { success: true, supplier: data };
    } else if (action === "update") {
      const updates: Record<string, unknown> = { updated_at: new Date().toISOString() };
      if (body.name !== undefined) updates.name = body.name;
      if (body.type !== undefined) updates.type = body.type;
      if (body.email !== undefined) updates.email = body.email;
      if (body.payment_terms_days !== undefined) updates.payment_terms_days = body.payment_terms_days;
      if (body.notes !== undefined) updates.notes = body.notes;
      if (body.is_active !== undefined) updates.is_active = body.is_active;

      const { data, error } = await supabaseClient
        .from("suppliers")
        .update(updates)
        .eq("id", body.supplier_id)
        .select()
        .single();
      if (error) throw error;

      await supabaseClient.from("ops_events").insert({
        entity_type: "supplier",
        entity_id: body.supplier_id,
        kind: "INFO",
        message: `Supplier updated: ${data.name}`,
        meta: { fields_changed: Object.keys(updates).filter((k) => k !== "updated_at") },
      });

      result = { success: true, supplier: data };
    } else if (action === "deactivate") {
      const { data, error } = await supabaseClient
        .from("suppliers")
        .update({ is_active: false, updated_at: new Date().toISOString() })
        .eq("id", body.supplier_id)
        .select()
        .single();
      if (error) throw error;

      await supabaseClient.from("ops_events").insert({
        entity_type: "supplier",
        entity_id: body.supplier_id,
        kind: "INFO",
        message: `Supplier deactivated: ${data.name}`,
        meta: {},
      });

      result = { success: true, supplier: data };
    } else {
      result = { success: false, error: "Invalid action. Use: create, update, deactivate" };
    }

    return new Response(JSON.stringify(result), {
      headers: { ...corsHeaders, "Content-Type": "application/json" },
    });
  } catch (error) {
    return new Response(
      JSON.stringify({ success: false, error: "Supplier operation failed. Check inputs and try again." }),
      {
        status: 400,
        headers: { ...corsHeaders, "Content-Type": "application/json" },
      }
    );
  }
});

4. delete-draft-invoice¶

Purpose: Controlled deletion of draft invoices with ops_events logging. Only draft-status invoices may be deleted.

Endpoint: POST /functions/v1/delete-draft-invoice
JWT Verification: Enabled (authenticated users only)

import { serve } from "https://deno.land/std@0.168.0/http/server.ts";
import { createClient } from "https://esm.sh/@supabase/supabase-js@2";

const ALLOWED_ORIGINS = [
  "https://ops.protocolraw.co.uk",
  "http://localhost:3000",
];

function getCorsHeaders(req: Request) {
  const origin = req.headers.get("origin") || "";
  const allowedOrigin = ALLOWED_ORIGINS.includes(origin) ? origin : ALLOWED_ORIGINS[0];
  return {
    "Access-Control-Allow-Origin": allowedOrigin,
    "Access-Control-Allow-Headers": "authorization, x-client-info, apikey, content-type",
  };
}

serve(async (req) => {
  const corsHeaders = getCorsHeaders(req);

  if (req.method === "OPTIONS") {
    return new Response("ok", { headers: corsHeaders });
  }

  try {
    const supabaseClient = createClient(
      Deno.env.get("SUPABASE_URL") ?? "",
      Deno.env.get("SUPABASE_SERVICE_ROLE_KEY") ?? ""
    );

    const body = await req.json();

    const { data, error } = await supabaseClient.rpc("fn_delete_draft_invoice_v1", {
      p_invoice_id: body.invoice_id,
    });

    if (error) throw error;

    return new Response(JSON.stringify(data), {
      headers: { ...corsHeaders, "Content-Type": "application/json" },
    });
  } catch (error) {
    return new Response(
      JSON.stringify({ success: false, error: "Invoice deletion failed. Check inputs and try again." }),
      {
        status: 400,
        headers: { ...corsHeaders, "Content-Type": "application/json" },
      }
    );
  }
});

Database function:

CREATE OR REPLACE FUNCTION raw_ops.fn_delete_draft_invoice_v1(
    p_invoice_id uuid
)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = raw_ops, public
AS $$
DECLARE
    v_status text;
    v_invoice_number text;
    v_supplier_name text;
    v_line_item_count integer;
BEGIN
    -- Get invoice details
    SELECT si.status, si.invoice_number, s.name
    INTO v_status, v_invoice_number, v_supplier_name
    FROM raw_ops.supplier_invoices si
    JOIN raw_ops.suppliers s ON s.id = si.supplier_id
    WHERE si.id = p_invoice_id;

    IF v_status IS NULL THEN
        RETURN jsonb_build_object('success', false, 'error', 'Invoice not found');
    END IF;

    IF v_status != 'draft' THEN
        RETURN jsonb_build_object(
            'success', false,
            'error', 'Only draft invoices can be deleted. Current status: ' || v_status
        );
    END IF;

    -- Count line items for audit log
    SELECT COUNT(*) INTO v_line_item_count
    FROM raw_ops.invoice_line_items
    WHERE invoice_id = p_invoice_id;

    -- Delete (line items cascade via ON DELETE CASCADE)
    DELETE FROM raw_ops.supplier_invoices WHERE id = p_invoice_id;

    -- Log deletion
    INSERT INTO raw_ops.ops_events (entity_type, entity_id, kind, message, meta)
    VALUES ('invoice', p_invoice_id, 'INFO',
        'Draft invoice deleted: ' || v_invoice_number,
        jsonb_build_object(
            'invoice_number', v_invoice_number,
            'supplier', v_supplier_name,
            'line_items_deleted', v_line_item_count
        )
    );

    RETURN jsonb_build_object(
        'success', true,
        'deleted_invoice', v_invoice_number,
        'line_items_deleted', v_line_item_count
    );
END;
$$;

COMMENT ON FUNCTION raw_ops.fn_delete_draft_invoice_v1 IS 'Deletes a draft invoice and its line items (CASCADE). Rejects deletion of approved/paid invoices. Logs to ops_events.';

Monitoring¶

Cost Tracking Health Monitor¶

CREATE OR REPLACE FUNCTION raw_ops.fn_check_cost_tracking_health_v2()
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = raw_ops, public
AS $$
DECLARE
    v_run_id uuid;
    v_start_time timestamptz;
    v_duration_ms integer;
    v_alert_needed boolean := false;
    v_alert_severity text := 'warning';
    v_alert_fields jsonb := '[]'::jsonb;
    v_batches_missing_cogs integer;
    v_shipments_missing_cpd integer;
    v_stale_drafts integer;
    v_overdue_invoices integer;
    v_anomaly_batches integer;
    v_cogs_mean numeric;
    v_cogs_stddev numeric;
BEGIN
    v_start_time := clock_timestamp();

    INSERT INTO raw_ops.monitoring_runs (check_name, status)
    VALUES ('cost_tracking_health', 'running')
    RETURNING id INTO v_run_id;

    -- Check 1: Released batches with no approved/paid invoice line items (missing COGS)
    -- Draft-linked line items do NOT count — they are excluded from analytics per business rules
    -- Only checks batches released in the last 14 days to avoid alerting on very old batches
    SELECT COUNT(*) INTO v_batches_missing_cogs
    FROM raw_ops.batches b
    WHERE b.status = 'RELEASED'
      AND b.produced_at >= NOW() - INTERVAL '14 days'
      AND NOT EXISTS (
          SELECT 1 FROM raw_ops.invoice_line_items li
          JOIN raw_ops.supplier_invoices si ON si.id = li.invoice_id
          WHERE li.batch_id = b.id
            AND si.status IN ('approved', 'paid')
      );

    IF v_batches_missing_cogs > 0 THEN
        v_alert_needed := true;
        v_alert_fields := v_alert_fields || jsonb_build_array(
            jsonb_build_object('label', 'Batches missing COGS', 'value', v_batches_missing_cogs::text, 'inline', true)
        );
    END IF;

    -- Check 2: Dispatched shipments with no approved/paid courier costs (missing CPD)
    -- Draft-linked line items do NOT count — they are excluded from analytics per business rules
    -- Only checks shipments dispatched in the last 14 days
    SELECT COUNT(*) INTO v_shipments_missing_cpd
    FROM raw_ops.shipments s
    WHERE s.status = 'DISPATCHED'
      AND s.dispatched_at >= NOW() - INTERVAL '14 days'
      AND NOT EXISTS (
          SELECT 1 FROM raw_ops.invoice_line_items li
          JOIN raw_ops.supplier_invoices si ON si.id = li.invoice_id
          WHERE li.shipment_id = s.id 
            AND li.category = 'courier'
            AND si.status IN ('approved', 'paid')
      );

    IF v_shipments_missing_cpd > 0 THEN
        v_alert_needed := true;
        v_alert_fields := v_alert_fields || jsonb_build_array(
            jsonb_build_object('label', 'Shipments missing CPD', 'value', v_shipments_missing_cpd::text, 'inline', true)
        );
    END IF;

    -- Check 3: Invoices stuck in draft for >7 days
    SELECT COUNT(*) INTO v_stale_drafts
    FROM raw_ops.supplier_invoices
    WHERE status = 'draft'
      AND created_at < NOW() - INTERVAL '7 days';

    IF v_stale_drafts > 0 THEN
        v_alert_needed := true;
        v_alert_fields := v_alert_fields || jsonb_build_array(
            jsonb_build_object('label', 'Stale draft invoices (>7d)', 'value', v_stale_drafts::text, 'inline', true)
        );
    END IF;

    -- Check 4: Approved invoices past due date and not paid
    SELECT COUNT(*) INTO v_overdue_invoices
    FROM raw_ops.supplier_invoices
    WHERE status = 'approved'
      AND due_date < CURRENT_DATE;

    IF v_overdue_invoices > 0 THEN
        v_alert_needed := true;
        v_alert_fields := v_alert_fields || jsonb_build_array(
            jsonb_build_object('label', 'Overdue invoices', 'value', v_overdue_invoices::text, 'inline', true)
        );
    END IF;

    -- Check 5: COGS anomaly detection (batch cost >2 std deviations from rolling mean)
    -- Only runs if we have at least 5 batches with COGS data
    SELECT AVG(total_cogs_gbp), STDDEV(total_cogs_gbp)
    INTO v_cogs_mean, v_cogs_stddev
    FROM analytics.v_batch_cogs
    WHERE total_cogs_gbp > 0;

    IF v_cogs_mean IS NOT NULL AND v_cogs_stddev IS NOT NULL AND v_cogs_stddev > 0 THEN
        SELECT COUNT(*) INTO v_anomaly_batches
        FROM analytics.v_batch_cogs
        WHERE total_cogs_gbp > 0
          AND produced_at >= NOW() - INTERVAL '30 days'
          AND ABS(total_cogs_gbp - v_cogs_mean) > (2 * v_cogs_stddev);

        IF v_anomaly_batches > 0 THEN
            v_alert_needed := true;
            v_alert_fields := v_alert_fields || jsonb_build_array(
                jsonb_build_object('label', 'Cost anomaly batches (>2σ)', 'value', v_anomaly_batches::text, 'inline', true)
            );
        END IF;
    ELSE
        v_anomaly_batches := 0;
    END IF;

    v_duration_ms := EXTRACT(EPOCH FROM (clock_timestamp() - v_start_time)) * 1000;

    UPDATE raw_ops.monitoring_runs
    SET 
        status = 'success',
        duration_ms = v_duration_ms,
        result_summary = format(
            'Missing COGS: %s | Missing CPD: %s | Stale drafts: %s | Overdue: %s | Anomalies: %s',
            v_batches_missing_cogs, v_shipments_missing_cpd, v_stale_drafts, v_overdue_invoices, v_anomaly_batches
        ),
        result_json = jsonb_build_object(
            'batches_missing_cogs', v_batches_missing_cogs,
            'shipments_missing_cpd', v_shipments_missing_cpd,
            'stale_drafts', v_stale_drafts,
            'overdue_invoices', v_overdue_invoices,
            'anomaly_batches', v_anomaly_batches,
            'cogs_mean', v_cogs_mean,
            'cogs_stddev', v_cogs_stddev
        ),
        alert_triggered = v_alert_needed
    WHERE id = v_run_id;

    RETURN jsonb_build_object(
        'success', true,
        'run_id', v_run_id,
        'check_name', 'cost_tracking_health',
        'duration_ms', v_duration_ms,
        'alert_config', CASE 
            WHEN NOT v_alert_needed THEN NULL
            ELSE jsonb_build_object(
                'channel', 'ops-alerts',
                'severity', v_alert_severity,
                'title', 'Cost Tracking Health Alert',
                'message', format(
                    'Missing COGS: %s batches | Missing CPD: %s shipments | Stale drafts: %s | Overdue: %s',
                    v_batches_missing_cogs, v_shipments_missing_cpd, v_stale_drafts, v_overdue_invoices
                ),
                'fields', v_alert_fields
            )
        END
    );

EXCEPTION WHEN OTHERS THEN
    v_duration_ms := EXTRACT(EPOCH FROM (clock_timestamp() - v_start_time)) * 1000;
    UPDATE raw_ops.monitoring_runs
    SET status = 'error', duration_ms = v_duration_ms, error_message = SQLERRM
    WHERE id = v_run_id;
    RETURN jsonb_build_object(
        'success', false, 'run_id', v_run_id,
        'check_name', 'cost_tracking_health', 'error', SQLERRM, 'duration_ms', v_duration_ms
    );
END;
$$;

COMMENT ON FUNCTION raw_ops.fn_check_cost_tracking_health_v2 IS 'SOP-MON-01 pattern monitor. Checks: missing COGS, missing CPD, stale drafts, overdue payments, cost anomalies.';

Register in run-monitor¶

Add to MONITOR_FUNCTIONS map in supabase/functions/run-monitor/index.ts:

const MONITOR_FUNCTIONS: Record<string, string> = {
  // ... existing monitors
  cost_tracking_health: "fn_check_cost_tracking_health_v2",
};

Schedule with pg_cron¶

SELECT cron.schedule(
    'monitor-cost-tracking-health',
    '0 9 * * *',  -- Daily at 09:00 UTC
    $$
    SELECT net.http_post(
        url := 'https://znfjpllsiuyezqlneqzr.supabase.co/functions/v1/run-monitor',
        headers := '{"Content-Type": "application/json", "apikey": "ANON_KEY"}'::jsonb,
        body := '{"check": "cost_tracking_health"}'::jsonb
    );
    $$
);

Ops Portal: Cost Tracking Section¶

Add "Cost Tracking" to the Ops Portal sidebar navigation with three sub-views:

Suppliers — Supplier registry (list, add, edit)
Invoices — Invoice entry and management (list, add, view detail, status transitions)
Dashboard — Embedded Metabase charts

Suppliers View¶

List view: - Table: Name, Type (badge), Email, Payment Terms, Active status - Filter by type, active status - "Add Supplier" button

Add/Edit form: - Name (required) - Type (dropdown: raw_materials, packaging, courier, lab, facility, preblend, other) - Email - Payment Terms (days, default 30) - Active toggle - Notes

API: All mutations via manage-supplier Edge Function (create, update, deactivate actions). Read-only list queries use Supabase client with service role via the Edge Function for consistency.

Invoices View¶

List view: - Table: Invoice #, Supplier Name, Date, Net Amount, Status (badge), Due Date - Filter by status (draft, approved, paid), supplier, date range - Sort by date (default: newest first) - Colour-coded status: draft (grey), approved (blue), paid (green) - Overdue indicator (red) on approved invoices past due date - "New Invoice" button

New Invoice form:

Step 1: Header - Supplier (searchable dropdown from suppliers table) - Invoice Number (text, required) - Invoice Date (date picker, required) - Net Amount (£, required) - VAT Amount (£, default auto-calculated at 20%) - Total Amount (£, auto-calculated: net + VAT) - Notes (optional text) - PDF Upload (file picker → Supabase Storage invoice-reports bucket)

Step 2: Line Items (repeating section) - Description (text, required) - Category (dropdown: meat, organ, egg, oil, preblend, fibre, packaging, courier, lab_testing, facility, coolant, other) - Quantity (number, optional) - Unit (text: kg, units, litres, tests, shipments, etc.) - Unit Price (£, optional) - Line Total (£, required — auto-calculated if quantity × unit price provided) - Batch (searchable dropdown from batches table, optional) - Shipment (searchable dropdown from shipments table, optional) - Notes (optional)

"Add Line Item" button to add more rows. Line totals must sum to invoice net amount (validation warning if mismatch).

Step 3: Review and Save - Summary of header + all line items - "Save as Draft" button → calls save-invoice Edge Function - Validation: invoice number unique per supplier, at least one line item

Invoice Detail view: - Full header info with PDF viewer/download - Line items table with batch/shipment links - Status transition buttons: - Draft → "Approve" button - Approved → "Mark Paid" button (with optional payment date picker) - Approved → "Revert to Draft" button - All transitions call update-invoice-status Edge Function

Dashboard View¶

Embedded Metabase dashboard with:

COGS per kg trend — Line chart from v_batch_cogs, last 20 batches
CPD per delivery trend — Line chart from v_shipment_cpd, last 90 days
Monthly spend by supplier — Bar chart from v_supplier_spend
Cost category breakdown — Stacked area chart from v_cost_category_trend
Unit economics summary — Table from v_unit_economics, last 10 batches

Metabase Dashboards¶

Collection: "SOP-FIN-02 — Cost Tracking"

1. COGS Tracking¶

Charts: - COGS per kg by batch (line chart, target overlay at £4.10-4.90 for Phase A) - Cost category breakdown per batch (stacked bar) - Ingredient cost trends (line chart by category over time)

Source: analytics.v_batch_cogs

2. CPD Tracking¶

Charts: - Cost per delivery trend (line chart, target overlay at £24-28 for Phase A) - Cost breakdown: courier vs packaging vs coolant (stacked bar)

Source: analytics.v_shipment_cpd

3. Unit Economics¶

Charts: - Total cost per kg trend (COGS + CPD combined) - Gross margin per batch (requires revenue data from orders) - Cost category waterfall (which categories contribute most to total cost)

Source: analytics.v_unit_economics

4. Supplier Spend¶

Charts: - Monthly spend by supplier (stacked bar) - Spend by category over time (area chart) - Payment status breakdown (pie: draft, approved, paid, overdue)

Source: analytics.v_supplier_spend, analytics.v_cost_category_trend

Business Rules¶

Invoice Lifecycle¶

Transition	Allowed	Notes
draft → approved	Yes	Invoice verified, costs count in analytics
approved → paid	Yes	Payment recorded
approved → draft	Yes	Revert if error found
draft → paid	No	Must approve first
paid → any	No	Paid invoices are final

Deletion Rules¶

Draft invoices: May be deleted (with all line items via CASCADE)
Approved/paid invoices: Cannot be deleted. If an error is found on an approved invoice, revert to draft first, then delete or correct.

Line Item Linkage¶

A line item may reference both batch_id and shipment_id simultaneously. Example: packaging costs that apply to a specific production batch and are also attributable to a specific shipment.
A line item may reference neither. Example: facility rent, which is a general overhead not tied to a specific batch or shipment.
v_batch_cogs only sums line items where batch_id is set.
v_shipment_cpd only sums line items where shipment_id is set.

Line Total Validation¶

The Ops Portal UI warns if the sum of line item totals does not match the invoice net amount.
This is a warning, not a hard constraint at the database level. Invoices sometimes have rounding discrepancies, or line items may be entered incrementally.
If the mismatch exceeds 5%, the UI should require explicit confirmation before saving.

Supplier Mutations¶

All supplier creates, updates, and deactivations are logged to ops_events via the manage-supplier Edge Function.
Suppliers are never hard-deleted. Deactivation (setting is_active = false) is the correct way to retire a supplier.

Draft Invoice Exclusion¶

Analytics views (v_batch_cogs, v_shipment_cpd, v_unit_economics, v_supplier_spend, v_cost_category_trend) exclude draft invoices.
Only approved and paid invoice line items are included in cost calculations.
The monitoring function counts draft invoices for the "stale drafts" check but does not include them in COGS/CPD calculations.

Healthy State and Alert Thresholds¶

Check	Healthy State	Alert Threshold	Severity	Channel
Batches missing COGS	All RELEASED batches (last 14 days) have at least one approved/paid invoice line item linked	Any batch missing approved/paid COGS	warning	#ops-alerts
Shipments missing CPD	All DISPATCHED shipments (last 14 days) have at least one approved/paid courier-category line item	Any shipment missing approved/paid CPD	warning	#ops-alerts
Stale draft invoices	No invoices in draft status for more than 7 days	Any stale draft	warning	#ops-alerts
Overdue invoices	No approved invoices past their due date	Any overdue invoice	warning	#ops-alerts
COGS anomaly	All batch costs within 2 standard deviations of rolling mean	Any batch outside 2σ (last 30 days)	warning	#ops-alerts

Monitor schedule: Daily at 09:00 UTC Target metrics at steady state: Zero alerts on a typical day. Alerts indicate either missing data entry or genuine cost issues.

Operational Procedures¶

Daily (2 minutes)¶

Check Slack #ops-alerts for cost tracking health alerts
If alert received: review specific issue (missing COGS, missing CPD, stale drafts, overdue payments)

Weekly (15 minutes)¶

Enter any new invoices received during the week via Ops Portal
Approve any draft invoices that have been verified
Mark paid any invoices that have been settled
Review COGS per kg trend — is it within Phase A target (£4.10-4.90)?
Review CPD per delivery — is it within Phase A target (£24-28)?

Monthly (30 minutes)¶

Review supplier spend dashboard for trends
Check for cost category drift
Reconcile invoice count against expected supplier invoices
Update supplier payment terms if renegotiated

Phase B Extension: AI Invoice Parsing (Not Built)¶

When co-packer and 3PL invoice volume justifies automation (expected Phase B, Month 7+), the following architecture layers onto the existing schema with no breaking changes.

Target Architecture¶

Gmail Pub/Sub push notification
    ↓
invoice-ingestion (Edge Function)
    - Validates sender against allowlist
    - Fetches email + PDF attachment via Gmail API
    - Checks idempotency (source_email_id unique constraint)
    - Stores PDF in Supabase Storage
    - Calls OpenAI GPT-4o Vision for structured extraction
    - Inserts row into invoice_parsing_jobs (status: pending)
    ↓
pg_cron (every 1 minute) → pg_net → invoice-processor (Edge Function)
    ↓
fn_process_pending_invoices_v1() (PostgreSQL function)
    - SELECT FOR UPDATE SKIP LOCKED on pending jobs
    - Confidence check (90% threshold)
    - Creates supplier_invoice + invoice_line_items via fn_save_invoice_v1()
    - Sets source = 'ai_parsed'
    - Bounded retry (max 3, then permanent failed_review)
    ↓
ops-alerter [if failures detected]

Additional Database Objects (Phase B)¶

raw_ops.invoice_parsing_jobs — AI parsing staging table

Column	Type	Notes
id	uuid PK
source_email_id	text UNIQUE	Gmail message ID (idempotency key)
invoice_type	text	`copacker`, `3pl`, `unknown`
invoice_pdf_url	text	Supabase Storage path
parsed_data	jsonb	Structured extraction from AI
confidence_score	numeric(4,2)	0.00-1.00
status	text	`pending` → `processing` → `done` / `failed_review`
retry_count	integer DEFAULT 0	Bounded at 3
last_error	text
processing_duration_ms	integer
created_at	timestamptz
processed_at	timestamptz

raw_ops.invoice_email_failures — Quarantine for ingestion failures

Column	Type	Notes
id	uuid PK
source_email_id	text	Gmail message ID
error_type	text	`unknown_sender`, `no_pdf`, `api_error`, `parse_error`
error_message	text
email_subject	text
email_from	text
pdf_storage_path	text
status	text	`pending_review` → `resolved` / `dismissed`
resolved_at	timestamptz
resolution_note	text
created_at	timestamptz

Google Cloud Infrastructure (Phase B)¶

Pub/Sub topic: projects/protocol-raw-operations/topics/invoice-email-notifications
Push subscription endpoint: https://znfjpllsiuyezqlneqzr.supabase.co/functions/v1/invoice-ingestion
Gmail watch on invoices@protocolraw.co.uk inbox
Auto-renewal pg_cron job every 6 days (same pattern as SOP-LAB-01)
Fallback poll every 30 minutes

Sender Allowlist (Phase B)¶

const ALLOWED_SENDER_DOMAINS = [
  "copacker-domain.co.uk",  // Co-packer email domain
  "3pl-domain.co.uk",       // 3PL email domain
];

Emails from unrecognised domains are quarantined in invoice_email_failures, not processed.

Go/No-Go Criteria for Phase B Build¶

Co-packer contract signed and invoicing started
3PL contract signed and invoicing started
Invoice volume exceeds 50/month (manual entry taking >2 hours/week)
Consistent invoice format established with both vendors

Objects Summary¶

Phase A (Build Now)¶

Object	Type	Purpose
`raw_ops.suppliers`	Table	Supplier registry
`raw_ops.supplier_invoices`	Table	Invoice headers
`raw_ops.invoice_line_items`	Table	Line items with batch/shipment linkage
`raw_ops.fn_save_invoice_v1()`	Function	Invoice creation with ops_events logging
`raw_ops.fn_update_invoice_status_v1()`	Function	Status transitions with validation
`raw_ops.fn_delete_draft_invoice_v1()`	Function	Controlled draft invoice deletion
`raw_ops.fn_check_cost_tracking_health_v2()`	Function	SOP-MON-01 monitoring
`analytics.v_batch_cogs`	View	COGS per batch
`analytics.v_shipment_cpd`	View	Cost per delivery
`analytics.v_unit_economics`	View	Combined P&L per batch
`analytics.v_supplier_spend`	View	Monthly spend by supplier
`analytics.v_cost_category_trend`	View	Weekly spend by category
`save-invoice`	Edge Function	Ops Portal → fn_save_invoice_v1
`update-invoice-status`	Edge Function	Ops Portal → fn_update_invoice_status_v1
`delete-draft-invoice`	Edge Function	Ops Portal → fn_delete_draft_invoice_v1
`manage-supplier`	Edge Function	Supplier CRUD with ops_events logging
`run-monitor` update	Edge Function update	Add cost_tracking_health to dispatch map
`monitor-cost-tracking-health`	pg_cron job	Daily at 09:00 UTC
Ops Portal: Cost Tracking	UI section	Suppliers, Invoices, Dashboard
Metabase: SOP-FIN-02 collection	Dashboards	COGS, CPD, Unit Economics, Supplier Spend

Retired from v3.0¶

Object	Reason
Make.com "SOP 05: Invoice Ingestion & Parsing"	Deactivate scenario
`raw_ops.invoice_parsing_jobs`	Phase B object (not needed now)
`handle_new_invoice_job()` trigger function	DROP FUNCTION
`on_new_invoice_job` trigger	DROP TRIGGER
`send-slack-alert` Edge Function reference	Use ops-alerter
`v_invoice_parsing_health`	Replaced by new analytics views
`v_failed_invoices`	Replaced by monitoring function
`v_invoice_performance_7d`	Phase B concern
`v_recent_invoices`	Replaced by Ops Portal invoice list
`v_invoice_stats_by_type`	Phase B concern
`v_invoice_volume_12w`	Replaced by v_cost_category_trend

Troubleshooting¶

Invoice already exists error¶

The unique constraint (supplier_id, invoice_number) prevents duplicates. If you need to re-enter an invoice:

-- Check existing invoice
SELECT id, status, created_at
FROM raw_ops.supplier_invoices
WHERE supplier_id = '[supplier_uuid]'
  AND invoice_number = '[invoice_number]';

If the invoice is in draft status, delete it via the Ops Portal (which calls fn_delete_draft_invoice_v1) and re-enter. If it has been approved or paid, revert to draft first via update-invoice-status, then delete.

If a batch or shipment doesn't appear in the Ops Portal dropdown:

-- Check batch exists and status
SELECT id, batch_code, status FROM raw_ops.batches WHERE batch_code = '[code]';

-- Check shipment exists
SELECT id, tracking_no, status FROM raw_ops.shipments WHERE tracking_no = '[tracking]';

Batches must exist in the database before costs can be linked. If a batch hasn't been created yet (pre-production), create the batch first via the Batch Creation workflow (SOP-LAB-01), then enter the invoice.

Cost anomaly alert¶

If the monitoring function flags a cost anomaly:

-- Check which batches triggered the anomaly
SELECT batch_code, total_cogs_gbp, cogs_per_kg_gbp, kg_produced
FROM analytics.v_batch_cogs
WHERE produced_at >= NOW() - INTERVAL '30 days'
ORDER BY cogs_per_kg_gbp DESC;

Common causes: supplier price increase, batch size variation, incorrect line item entry. Verify the invoice data before investigating further.

Version History¶

Version	Date	Changes	Author
1.0	2026-03-20	New SOP replacing SOP-05. New schema (suppliers, supplier_invoices, invoice_line_items). Manual Ops Portal entry for Phase A. Retired Make.com scenario. New analytics views with correct draft exclusion. SOP-MON-01 monitoring. Phase B AI parsing documented as extension.	Protocol Raw Operations

Legacy SOP-05 history (retired):

Version	Date	Changes
SOP-05 v3.0	2025-11-02	Scale optimisation. Performance indexes, 6 monitoring views, processing duration tracking.
SOP-05 v2.0	2025-10-28	Initial production. Make.com automation, AI parsing, confidence scoring.
SOP-05 v1.0	2025-10-20	Initial build. Make.com setup, database schema.

SOP-MON-01: Monitoring & Alerting Architecture (monitoring pattern)
SOP-LAB-01: Batch Creation & Lab-to-Release (batch lifecycle, Phase B migration pattern)
SOP-INV-01: Inventory Control (allocation, stock levels)
SOP-DLV-01: Courier Watchdog (shipment lifecycle)
Ops Portal Documentation v3.8: Portal architecture and UI patterns
Business Plan v2.7: Unit economics and pricing models
Financial Model v2.1: Cost projections and margin analysis

End of SOP-FIN-02 v1.0

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

SOP-FIN-02: Cost Tracking System v1.0¶

Key Changes from v3.0¶

Purpose¶

Scope¶

System Architecture¶

Data Flow¶

Monitoring¶

Database Schema¶

1. Suppliers Table¶

2. Supplier Invoices Table¶

3. Invoice Line Items Table¶

Database Functions¶

1. Save Invoice (Ops Portal Entry)¶

2. Update Invoice Status¶

Analytics Views¶

1. Batch COGS¶

2. Shipment CPD¶

3. Unit Economics¶

4. Supplier Spend¶

5. Cost Category Trend¶

Edge Functions¶

Authentication Model¶

Input Validation¶

1. save-invoice¶

2. update-invoice-status¶

3. manage-supplier¶

4. delete-draft-invoice¶

Monitoring¶

Cost Tracking Health Monitor¶

Register in run-monitor¶

Schedule with pg_cron¶

Ops Portal: Cost Tracking Section¶

Navigation¶

Suppliers View¶

Invoices View¶

Dashboard View¶

Metabase Dashboards¶

1. COGS Tracking¶

2. CPD Tracking¶

3. Unit Economics¶

4. Supplier Spend¶

Business Rules¶

Invoice Lifecycle¶

Deletion Rules¶

Line Item Linkage¶

Line Total Validation¶

Supplier Mutations¶

Draft Invoice Exclusion¶

Healthy State and Alert Thresholds¶

Operational Procedures¶

Daily (2 minutes)¶

Weekly (15 minutes)¶

Monthly (30 minutes)¶

Phase B Extension: AI Invoice Parsing (Not Built)¶

Target Architecture¶

Additional Database Objects (Phase B)¶

Google Cloud Infrastructure (Phase B)¶

Sender Allowlist (Phase B)¶

Go/No-Go Criteria for Phase B Build¶

Objects Summary¶

Phase A (Build Now)¶

Retired from v3.0¶

Troubleshooting¶

Invoice already exists error¶

Missing batch/shipment in dropdown¶

Cost anomaly alert¶

Version History¶

Related Documentation¶