Protocol Raw System Architecture¶

Layer 2: Component Registry¶

Version: 2.0 Status: Production Reference Last Updated: February 2026 Owner: Protocol Raw Operations Note: Section 1 auto-generated from live database introspection on 2026-02-21

1. Database Schema (raw_ops)¶

All operational data lives in the raw_ops schema in Supabase PostgreSQL.

1.1 Core Customer & Order Tables¶

customers¶

Purpose: Customer master data synced from Shopify

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`shopify_customer_id`	text	YES	Shopify Customer ID
`email`	citext	YES	Email
`first_name`	text	YES	First Name
`last_name`	text	YES	Last Name
`phone`	text	YES	Phone
`address_json`	jsonb	YES	Address JSON
`marketing_opt_in`	boolean	YES	Marketing Opt In (default: `false`)
`created_at`	timestamptz	NO	Record created (default: `now()`)
`address1`	text	YES	Address1
`address2`	text	YES	Address2
`city`	text	YES	City
`region`	text	YES	Region
`postcode`	text	YES	Postcode
`country_code`	text	YES	Country Code
`portal_first_visited_at`	timestamptz	YES	Portal First Visited At
`persona`	text	YES	Persona
`acquisition_diet`	text	YES	Acquisition Diet

Indexes: - PRIMARY KEY on (id) - customers_email_key UNIQUE on (email) - customers_shopify_customer_id_key UNIQUE on (shopify_customer_id) - idx_customers_acquisition_diet on (acquisition_diet) - idx_customers_portal_first_visited on (portal_first_visited_at)

orders¶

Purpose: Order headers from Shopify webhooks

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`shopify_order_id`	text	YES	Shopify Order ID
`customer_id`	uuid	YES	FK to customers
`subtotal_ex_vat`	numeric	NO	Subtotal Ex Vat (default: `0`)
`vat`	numeric	NO	Vat (default: `0`)
`total_inc_vat`	numeric	NO	Total Inc Vat (default: `0`)
`currency`	text	NO	Currency (default: `'GBP'`)
`status`	order_status	NO	Status (default: `'PENDING'`)
`is_subscription`	boolean	NO	Is Subscription (default: `false`)
`utm`	jsonb	YES	Utm
`ordered_at`	timestamptz	NO	Ordered At (default: `now()`)
`created_at`	timestamptz	NO	Record created (default: `now()`)
`order_number_label`	text	YES	Order Number Label
`created_at_shopify`	timestamptz	YES	Created At Shopify
`updated_at_shopify`	timestamptz	YES	Updated At Shopify
`fulfillment_status`	text	YES	Fulfillment Status
`subtotal_gbp`	numeric	YES	Subtotal GBP
`tax_gbp`	numeric	YES	Tax GBP
`discount_gbp`	numeric	YES	Discount GBP
`total_gbp`	numeric	YES	Total GBP
`ship_name`	text	YES	Ship Name
`ship_phone`	text	YES	Ship Phone
`ship_address1`	text	YES	Ship Address1
`ship_address2`	text	YES	Ship Address2
`ship_city`	text	YES	Ship City
`ship_postcode`	text	YES	Ship Postcode
`ship_country`	text	YES	Ship Country
`notes_json`	jsonb	YES	Notes JSON
`tags`	ARRAY	YES	Tags
`utm_json`	jsonb	YES	Utm JSON
`export_state`	text	YES	Export State
`exported_at`	timestamptz	YES	Exported At
`external_order_ref`	text	YES	External Order Ref
`acked_at`	timestamptz	YES	Acked At
`manifest_version`	text	YES	Manifest Version (default: `'v1'`)
`referral_slug`	text	YES	Referral Slug
`referral_id`	uuid	YES	FK to referrals
`discount_codes`	jsonb	YES	Discount Codes
`referral_processed_at`	timestamptz	YES	Referral Processed At
`credit_checked_at`	timestamptz	YES	Credit Checked At
`credit_applied_pence`	integer	YES	Credit Applied Pence (default: `0`)

Indexes: - PRIMARY KEY on (id) - idx_orders_allocation_pending on (status, created_at) WHERE (status = 'PAID'::raw_ops.order_status) - idx_orders_created_at_desc on (created_at) - idx_orders_credit_pending on (created_at) WHERE (credit_checked_at IS NULL) - idx_orders_customer_recent on (customer_id, ordered_at) - idx_orders_export_eligibility on (status, export_state, ordered_at) WHERE ((status = 'PAID'::raw_ops.order_status) AND (export_state IS NULL)) - idx_orders_export_state on (status, export_state) - idx_orders_paid_unallocated on (created_at) WHERE (status = 'PAID'::raw_ops.order_status) - idx_orders_referral_id on (referral_id) WHERE (referral_id IS NOT NULL) - orders_ordered_at_idx on (ordered_at) - orders_ordered_at_idx1 on (ordered_at) - orders_shopify_order_id_key UNIQUE on (shopify_order_id) - uq_orders_shopify_id UNIQUE on (shopify_order_id)

order_items¶

Purpose: Line items per order

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`order_id`	uuid	NO	FK to orders
`product_id`	uuid	YES	FK to products
`qty`	integer	NO	Qty
`unit_price_ex_vat`	numeric	NO	Unit Price Ex Vat
`shopify_line_item_id`	text	YES	Shopify Line Item ID
`shopify_product_id`	text	YES	Shopify Product ID
`sku`	text	YES	SKU
`product_name`	text	YES	Product Name
`quantity`	integer	YES	Quantity
`unit_price_gbp`	numeric	YES	Unit Price GBP
`fulfilled_qty`	integer	NO	Fulfilled Qty (default: `0`)
`pet_number`	integer	YES	Pet Number
`pet_name`	text	YES	Pet Name
`daily_grams`	integer	YES	Daily Grams
`life_stage`	text	YES	Life Stage

Indexes: - PRIMARY KEY on (id) - idx_order_items_allocation_lookup on (order_id, id) - idx_order_items_order_id on (order_id) - idx_order_items_order_lookup on (order_id, id, product_id) - idx_order_items_pet on (order_id, pet_number) WHERE (pet_number IS NOT NULL) - idx_order_items_product on (product_id) WHERE (product_id IS NOT NULL) - order_items_shopify_line_item_id_key UNIQUE on (shopify_line_item_id)

order_notes¶

Purpose: Internal notes attached to orders

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`created_at`	timestamptz	YES	Record created (default: `now()`)
`order_id`	uuid	NO	FK to orders
`author`	text	NO	Author
`note`	text	NO	Note
`note_type`	text	YES	Note Type (default: `'general'`)
`support_ticket_id`	uuid	YES	FK to support_tickets
`refund_id`	uuid	YES	FK to refunds

Indexes: - PRIMARY KEY on (id) - idx_order_notes_created on (created_at) - idx_order_notes_order on (order_id)

products¶

Purpose: Product catalog

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`sku`	text	NO	SKU
`name`	text	NO	Name
`kg`	numeric	NO	kg
`is_active`	boolean	NO	Is Active (default: `true`)
`created_at`	timestamptz	NO	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id) - products_sku_key UNIQUE on (sku)

formulations¶

Purpose: Recipe/formulation definitions

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`version`	text	NO	Version
`name`	text	NO	Name
`description`	text	YES	Description
`effective_from`	date	NO	Effective From
`effective_until`	date	YES	Effective Until
`shopify_page_url`	text	NO	Shopify Page URL
`lab_certificate_pdf_url`	text	YES	Lab Certificate PDF URL
`lab_name`	text	YES	Lab Name
`certificate_reference`	text	YES	Certificate Reference
`analysis_date`	date	YES	Analysis Date
`fediaf_verified`	boolean	YES	Fediaf Verified (default: `true`)
`created_at`	timestamptz	YES	Record created (default: `now()`)
`updated_at`	timestamptz	YES	Last modified (default: `now()`)

Indexes: - PRIMARY KEY on (id) - formulations_version_key UNIQUE on (version) - idx_formulations_current on (effective_until) WHERE (effective_until IS NULL) - idx_formulations_effective on (effective_from, effective_until)

feeding_plans¶

Purpose: Customer feeding plan configurations

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`customer_id`	uuid	NO	FK to customers
`dog_name`	text	YES	Dog Name
`dog_weight_kg`	numeric	NO	Dog Weight kg
`life_stage`	text	NO	Life Stage (default: `'adult'`)
`activity_level`	text	NO	Activity Level (default: `'moderate'`)
`is_neutered`	boolean	NO	Is Neutered (default: `true`)
`body_condition`	text	YES	Body Condition (default: `'ideal'`)
`daily_grams`	integer	NO	Daily Grams
`daily_kcal`	integer	NO	Daily Kcal
`box_size`	text	NO	Box Size (default: `'12kg'`)
`created_at`	timestamptz	NO	Record created (default: `now()`)
`updated_at`	timestamptz	NO	Last modified (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_feeding_plans_customer on (customer_id)

1.2 Batch & Inventory Tables¶

batches¶

Purpose: Production batches with QA status

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`batch_code`	text	NO	Batch Code
`product_id`	uuid	YES	FK to products
`produced_at`	date	NO	Produced At
`status`	batch_status	NO	Status (default: `'QA_HOLD'`)
`notes`	text	YES	Notes
`created_at`	timestamptz	NO	Record created (default: `now()`)
`kg_produced`	numeric	YES	kg Produced
`total_cost_gbp`	numeric	YES	Total Cost GBP
`cogs_per_kg_gbp`	numeric	YES	Cogs Per kg GBP
`proof_url`	text	YES	Proof URL
`production_date`	date	YES	Production Date
`expiry_date`	date	YES	Expiry Date
`storage_temp_min`	numeric	YES	Storage Temp Min
`storage_temp_max`	numeric	YES	Storage Temp Max
`packaging_type`	varchar(50)	YES	Packaging Type
`qty_on_hand`	numeric	YES	Qty On Hand (default: `0`)
`qty_reserved`	numeric	YES	Qty Reserved (default: `0`)
`qr_image_url`	text	YES	Qr Image URL
`proof_insert_pdf_url`	text	YES	Proof Insert PDF URL
`proof_published_at`	timestamptz	YES	Proof Published At
`inserts_printed_at`	timestamptz	YES	Inserts Printed At
`inserts_shipped_to_3pl_at`	timestamptz	YES	Inserts Shipped To 3pl At
`proof_insert_generated_at`	timestamptz	YES	Proof Insert Generated At
`qa_hold_started_at`	timestamptz	YES	QA Hold Started At
`released_at`	timestamptz	YES	Released At
`formulation_id`	uuid	YES	FK to formulations
`label_pdf_url`	text	YES	Label PDF URL
`qty_soft_reserved`	integer	YES	Qty Soft Reserved (default: `0`)
`last_reservation_at`	timestamptz	YES	Last Reservation At
`public_batch_id`	text	YES	Public Batch ID

Indexes: - PRIMARY KEY on (id) - batches_batch_code_key UNIQUE on (batch_code) - batches_batch_code_unique UNIQUE on (batch_code) - batches_public_batch_id_key UNIQUE on (public_batch_id) - batches_status_idx on (status) - batches_status_idx1 on (status) - idx_batches_availability on (product_id, status, qty_on_hand, qty_reserved) WHERE (status = 'RELEASED'::raw_ops.batch_status) - idx_batches_batch_code on (batch_code) - idx_batches_batch_code_released on (batch_code) WHERE (status = 'RELEASED'::raw_ops.batch_status) - idx_batches_fefo on (expiry_date, status) WHERE (status = 'RELEASED'::raw_ops.batch_status) - idx_batches_fefo_lookup on (product_id, expiry_date, produced_at) WHERE (status = 'RELEASED'::raw_ops.batch_status) - idx_batches_formulation_id on (formulation_id) - idx_batches_proof_insert_pdf_url on (proof_insert_pdf_url) - idx_batches_proof_insert_workflow on (status, proof_url, proof_insert_pdf_url) WHERE (status = 'RELEASED'::raw_ops.batch_status) - idx_batches_public_id on (public_batch_id) - idx_batches_qa_hold on (id) WHERE (status = 'QA_HOLD'::raw_ops.batch_status) - idx_batches_released_available on (status) WHERE (status = 'RELEASED'::raw_ops.batch_status) - idx_batches_status on (status) WHERE (status = 'RELEASED'::raw_ops.batch_status) - idx_batches_status_released on (status, proof_published_at) WHERE (status = 'RELEASED'::raw_ops.batch_status)

batch_status_history¶

Purpose: Audit trail of batch status changes

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`batch_id`	uuid	NO	FK to batches
`old_status`	text	YES	Old Status
`new_status`	text	NO	New Status
`triggered_by`	text	YES	Triggered By
`lab_result_id`	uuid	YES	FK to lab_results
`created_at`	timestamptz	YES	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_batch_status_history_batch_id on (batch_id) - idx_batch_status_history_created_at on (created_at)

inventory¶

Purpose: Stock levels by batch and location

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`batch_id`	uuid	NO	FK to batches
`location`	text	NO	Location
`kg_available`	numeric	NO	kg Available
`updated_at`	timestamptz	NO	Last modified (default: `now()`)

Indexes: - PRIMARY KEY on (id) - inventory_batch_id_location_key UNIQUE on (batch_id, location)

inventory_movements¶

Purpose: Complete audit trail of inventory changes

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`sku`	text	NO	SKU
`movement_type`	text	NO	Movement Type
`qty_change`	integer	NO	Qty Change
`qty_before`	integer	NO	Qty Before
`qty_after`	integer	NO	Qty After
`reference_id`	uuid	YES	Reference ID
`reference_type`	text	YES	Reference Type
`notes`	text	YES	Notes
`created_at`	timestamptz	NO	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_inventory_movements_created_at on (created_at) - idx_inventory_movements_sku on (sku)

inventory_on_hand¶

Purpose: Current on-hand inventory snapshot

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`sku`	text	NO	SKU
`qty_available`	integer	NO	Qty Available (default: `0`)
`location`	text	YES	Location (default: `'3PL-PRIMARY'`)
`updated_at`	timestamptz	NO	Last modified (default: `now()`)
`created_at`	timestamptz	NO	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id) - inventory_on_hand_sku_key UNIQUE on (sku)

inventory_planning_config¶

Purpose: Inventory planning parameters

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`config_key`	text	NO	Config Key
`config_value`	numeric	NO	Config Value
`description`	text	YES	Description
`updated_at`	timestamptz	YES	Last modified (default: `now()`)
`updated_by`	text	YES	Updated By

Indexes: - PRIMARY KEY on (id) - inventory_planning_config_config_key_key UNIQUE on (config_key)

allocations¶

Purpose: Links order items to batches (FEFO)

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `uuid_generate_v4()`)
`order_item_id`	uuid	NO	FK to order_items
`batch_id`	uuid	NO	FK to batches
`qty_allocated`	numeric	NO	Qty Allocated
`created_at`	timestamptz	NO	Record created (default: `now()`)
`is_partial`	boolean	YES	Is Partial (default: `false`)
`allocation_group_id`	uuid	YES	Allocation Group ID
`remaining_qty`	integer	YES	Remaining Qty (default: `0`)

Indexes: - PRIMARY KEY on (id) - idx_allocations_batch on (batch_id) - idx_allocations_batch_lookup on (order_item_id, batch_id) - idx_allocations_batch_tracking on (batch_id, qty_allocated) - idx_allocations_group on (allocation_group_id) WHERE (allocation_group_id IS NOT NULL) - idx_allocations_item_lookup on (order_item_id) - idx_allocations_order_item on (order_item_id) - idx_allocations_order_item_id on (order_item_id)

allocation_queue¶

Purpose: Pending allocation requests

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`order_id`	uuid	NO	FK to orders
`order_item_id`	uuid	YES	FK to order_items
`priority`	integer	YES	Priority (default: `100`)
`state`	text	NO	State (default: `'pending'`)
`attempts`	integer	YES	Attempts (default: `0`)
`max_attempts`	integer	YES	Max Attempts (default: `5`)
`last_error`	text	YES	Last Error
`created_at`	timestamptz	NO	Record created (default: `now()`)
`started_at`	timestamptz	YES	Started At
`completed_at`	timestamptz	YES	Completed At

Indexes: - PRIMARY KEY on (id) - idx_allocation_queue_order on (order_id) - idx_allocation_queue_pending on (priority, created_at) WHERE (state = 'pending'::text) - idx_allocation_queue_state on (state, created_at)

allocation_alert_config¶

Purpose: Alert thresholds for allocation monitoring

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`alert_type`	text	NO	Alert Type
`threshold_value`	numeric	NO	Threshold Value
`enabled`	boolean	YES	Enabled (default: `true`)
`description`	text	YES	Description
`created_at`	timestamptz	YES	Record created (default: `now()`)
`updated_at`	timestamptz	YES	Last modified (default: `now()`)

Indexes: - PRIMARY KEY on (id) - allocation_alert_config_alert_type_key UNIQUE on (alert_type)

allocation_alert_history¶

Purpose: History of allocation alerts triggered

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`alert_type`	text	NO	Alert Type
`severity`	text	NO	Severity
`message`	text	NO	Message
`details`	jsonb	YES	Details
`resolved_at`	timestamptz	YES	Resolved At
`created_at`	timestamptz	YES	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_alert_history_created on (created_at) - idx_alert_history_unresolved on (alert_type, created_at) WHERE (resolved_at IS NULL)

lab_results¶

Purpose: Parsed laboratory test results

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`batch_id`	uuid	NO	FK to batches
`lab_name`	text	NO	Lab Name
`report_pdf_url`	text	NO	Report PDF URL
`salmonella_absent`	boolean	YES	Salmonella Absent
`enterobacteriaceae_cfu_per_g`	integer	YES	Enterobacteriaceae Cfu Per G
`outcome`	lab_outcome	NO	Outcome
`collected_at`	date	YES	Collected At
`reported_at`	timestamptz	NO	Reported At (default: `now()`)
`raw_email_metadata`	jsonb	YES	Raw Email Metadata
`confidence_score`	numeric	YES	Confidence Score
`pdf_hash`	text	YES	PDF Hash
`parsed_by`	text	YES	Parsed By (default: `'openai'`)
`listeria_absent`	boolean	YES	Listeria Absent

Indexes: - PRIMARY KEY on (id) - idx_lab_results_batch_id on (batch_id) - idx_lab_results_batch_id_reported on (batch_id, reported_at) - lab_results_batch_id_key UNIQUE on (batch_id) - lab_results_outcome_idx on (outcome) - lab_results_outcome_idx1 on (outcome) - unique_batch_result UNIQUE on (batch_id, lab_name, reported_at) - ux_lab_results_batch_id UNIQUE on (batch_id)

lab_pdf_patterns¶

Purpose: Patterns for parsing lab PDF reports

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`lab_name`	text	NO	Lab Name
`batch_code_regex`	text	NO	Batch Code Regex
`salmonella_pattern`	text	NO	Salmonella Pattern
`listeria_pattern`	text	YES	Listeria Pattern
`enterobacteriaceae_pattern`	text	NO	Enterobacteriaceae Pattern
`notes`	text	YES	Notes
`created_at`	timestamptz	YES	Record created (default: `now()`)
`updated_at`	timestamptz	YES	Last modified (default: `now()`)

Indexes: - PRIMARY KEY on (id)

1.3 Fulfillment & Dispatch Tables¶

shipments¶

Purpose: Dispatch and tracking data

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`order_id`	uuid	NO	FK to orders
`batch_id`	uuid	YES	FK to batches
`courier`	text	YES	Courier
`tracking_no`	text	YES	Tracking No
`status`	shipment_status	NO	Status (default: `'CREATED'`)
`dispatched_at`	timestamptz	YES	Dispatched At
`delivered_at`	timestamptz	YES	Delivered At
`address_json`	jsonb	YES	Address JSON
`created_at`	timestamptz	NO	Record created (default: `now()`)
`courier_cost_gbp`	numeric	YES	Courier Cost GBP
`packaging_cost_gbp`	numeric	YES	Packaging Cost GBP
`coolant_cost_gbp`	numeric	YES	Coolant Cost GBP
`service`	text	YES	Service
`dispatch_temp_recorded`	numeric	YES	Dispatch Temp Recorded
`coolant_units_used`	smallint	YES	Coolant Units Used
`courier_temp_compliance`	varchar(20)	YES	Courier Temp Compliance (default: `'PENDING'`)

Indexes: - PRIMARY KEY on (id) - idx_shipments_batch_id on (batch_id) - idx_shipments_created_at on (created_at) - idx_shipments_dispatched_at_desc on (dispatched_at) - idx_shipments_order_id on (order_id) - idx_shipments_status_active on (status) WHERE (status <> ALL (ARRAY['DELIVERED'::raw_ops.shipment_status, 'RETURNED'::raw_ops.shipment_status])) - idx_shipments_temp_audit on (courier_temp_compliance, created_at) WHERE (dispatch_temp_recorded IS NOT NULL) - idx_shipments_tracking_no on (tracking_no) - idx_shipments_tracking_no_unique UNIQUE on (tracking_no) WHERE (tracking_no IS NOT NULL) - shipments_status_dispatched_at_idx on (status, dispatched_at) - shipments_status_dispatched_at_idx1 on (status, dispatched_at)

courier_events¶

Purpose: Courier tracking event log

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`shipment_id`	uuid	NO	FK to shipments
`event_code`	text	NO	Event Code
`event_text`	text	YES	Event Text
`event_time`	timestamptz	NO	Event Time
`raw_payload`	jsonb	YES	Raw Payload
`event_type`	varchar(50)	YES	Event Type
`requires_customer_notification`	boolean	YES	Requires Customer Notification (default: `false`)
`customer_notified_at`	timestamptz	YES	Customer Notified At
`notification_channel`	varchar(20)	YES	Notification Channel
`created_at`	timestamptz	YES	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id) - courier_events_event_time_idx on (event_time) - courier_events_event_time_idx1 on (event_time) - idx_courier_events_created_at on (created_at) - idx_courier_events_notification_pending on (requires_customer_notification, customer_notified_at) WHERE (requires_customer_notification = true) - idx_courier_events_shipment_id on (shipment_id) - idx_courier_events_shipment_recent on (shipment_id, event_time) - idx_courier_events_shipment_time on (shipment_id, event_time)

courier_event_mapping¶

Purpose: Mapping of courier event codes to statuses

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`courier_name`	varchar(50)	NO	Courier Name
`event_code`	text	NO	Event Code
`event_type`	varchar(50)	NO	Event Type
`requires_customer_notification`	boolean	YES	Requires Customer Notification (default: `false`)
`notification_template_id`	varchar(100)	YES	Notification Template ID
`customer_message_short`	text	YES	Customer Message Short
`internal_priority`	varchar(20)	YES	Internal Priority
`created_at`	timestamptz	YES	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id) - courier_event_mapping_courier_name_event_code_key UNIQUE on (courier_name, event_code) - idx_courier_event_mapping_code on (courier_name, event_code) - idx_courier_mapping_lookup on (courier_name, event_code)

packing_instructions¶

Purpose: PCM requirements per order

Column	Type	Nullable	Description
`id`	bigint	NO	Primary key (default: `nextval('raw_ops.packing_instructions_id_seq'`)
`order_id`	uuid	YES	FK to orders
`shopify_order_id`	text	YES	Shopify Order ID
`box_size`	varchar(10)	NO	Box Size
`insulation_type`	varchar(50)	NO	Insulation Type (default: `'Wool'`)
`pcm_mass_kg`	numeric	NO	PCM Mass kg
`pcm_packs_count`	smallint	NO	PCM Packs Count
`ambient_forecast_max`	numeric	YES	Ambient Forecast Max
`transit_hours_expected`	smallint	YES	Transit Hours Expected
`forecast_retrieved_at`	timestamptz	YES	Forecast Retrieved At (default: `now()`)
`risk_level`	varchar(20)	NO	Risk Level
`logger_required`	boolean	YES	Logger Required (default: `false`)
`special_notes`	text	YES	Special Notes
`created_at`	timestamptz	YES	Record created (default: `now()`)
`calculated_by`	varchar(50)	YES	Calculated By (default: `'automated'`)
`calculation_version`	varchar(10)	YES	Calculation Version (default: `'1.0'`)
`batch_id`	uuid	YES	FK to batches

Indexes: - PRIMARY KEY on (id) - idx_packing_instructions_batch_id on (batch_id) - idx_packing_instructions_calc_version on (calculation_version) - idx_packing_instructions_created_at on (created_at) - idx_packing_instructions_order_id on (order_id) - idx_packing_instructions_shopify_order_id on (shopify_order_id) - unique_order_instruction UNIQUE on (order_id)

pack_quality_checks¶

Purpose: Quality check records during packing

Column	Type	Nullable	Description
`id`	bigint	NO	Primary key (default: `nextval('raw_ops.pack_quality_checks_id_seq'`)
`order_id`	uuid	YES	FK to orders
`packing_instruction_id`	bigint	YES	FK to packing_instructions
`shopify_order_id`	text	YES	Shopify Order ID
`expected_total_weight_kg`	numeric	NO	Expected Total Weight kg
`actual_weight_kg`	numeric	NO	Actual Weight kg
`weight_variance_kg`	numeric	YES	Weight Variance kg
`weight_variance_pct`	numeric	YES	Weight Variance Pct
`pack_started_at`	timestamptz	YES	Pack Started At
`pack_completed_at`	timestamptz	YES	Pack Completed At
`pack_time_seconds`	integer	YES	Pack Time Seconds
`logger_activated`	boolean	YES	Logger Activated (default: `false`)
`void_fill_complete`	boolean	YES	Void Fill Complete (default: `false`)
`visual_check_pass`	boolean	YES	Visual Check Pass (default: `false`)
`packed_by`	varchar(100)	NO	Packed By
`packed_at`	timestamptz	YES	Packed At (default: `now()`)
`photo_url`	text	YES	Photo URL
`notes`	text	YES	Notes
`created_at`	timestamptz	YES	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_pack_quality_checks_order_id on (order_id) - idx_pack_quality_checks_packed_at on (packed_at) - idx_pack_quality_checks_packed_by on (packed_by)

export_outbox¶

Purpose: Orders queued for export to fulfillment

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`batch_id`	text	NO	Batch ID
`order_ids`	ARRAY	NO	Order Ids
`csv_data`	jsonb	NO	Csv Data
`status`	text	NO	Status (default: `'pending'`)
`attempts`	integer	NO	Attempts (default: `0`)
`last_attempt_at`	timestamptz	YES	Last Attempt At
`error_message`	text	YES	Error Message
`created_at`	timestamptz	NO	Record created (default: `now()`)
`sent_at`	timestamptz	YES	Sent At
`confirmed_at`	timestamptz	YES	Confirmed At

Indexes: - PRIMARY KEY on (id) - idx_outbox_failed on (status, attempts, last_attempt_at) WHERE (status = 'failed'::text) - idx_outbox_pending on (status, created_at) WHERE (status = 'pending'::text) - idx_outbox_retry on (status, attempts) WHERE ((status = 'failed'::text) AND (attempts < 3))

dispatch_import_queue¶

Purpose: Incoming dispatch data from 3PL

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`external_order_id`	text	NO	External Order ID
`dispatch_data`	jsonb	NO	Dispatch Data
`processing_status`	text	NO	Processing Status (default: `'pending'`)
`created_at`	timestamptz	NO	Record created (default: `now()`)
`processed_at`	timestamptz	YES	Processed At
`error_message`	text	YES	Error Message

Indexes: - PRIMARY KEY on (id) - idx_dispatch_queue_order on (external_order_id) - idx_dispatch_queue_status on (processing_status, created_at) WHERE (processing_status = ANY (ARRAY['pending'::text, 'failed'::text]))

processed_3pl_files¶

Purpose: Track processed 3PL import files

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`file_name`	text	NO	File Name
`file_hash`	text	NO	File Hash
`processed_at`	timestamptz	NO	Processed At (default: `now()`)
`record_count`	integer	NO	Record Count
`source`	text	NO	Source (default: `'manual'`)

Indexes: - PRIMARY KEY on (id) - idx_processed_3pl_files_hash on (file_hash) - processed_3pl_files_file_hash_key UNIQUE on (file_hash)

temperature_audits¶

Purpose: Temperature monitoring audit records

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`shipment_id`	uuid	NO	FK to shipments
`logger_core_csv_url`	text	YES	Logger Core Csv URL
`logger_corner_csv_url`	text	YES	Logger Corner Csv URL
`arrival_core_celsius`	numeric	YES	Arrival Core Celsius
`arrival_corner_celsius`	numeric	YES	Arrival Corner Celsius
`pass_class`	text	YES	Pass Class
`created_at`	timestamptz	NO	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id)

temperature_logger_data¶

Purpose: Raw temperature logger readings

Column	Type	Nullable	Description
`id`	bigint	NO	Primary key (default: `nextval('raw_ops.temperature_logger_data_id_seq'`)
`order_id`	uuid	YES	FK to orders
`shipment_id`	uuid	YES	FK to shipments
`logger_id`	text	NO	Logger ID
`logger_type`	text	YES	Logger Type (default: `'USB'`)
`readings`	jsonb	NO	Readings
`min_temp_c`	numeric	YES	Min Temp C
`max_temp_c`	numeric	YES	Max Temp C
`avg_temp_c`	numeric	YES	Avg Temp C
`time_above_5c_minutes`	integer	YES	Time Above 5c Minutes
`time_above_10c_minutes`	integer	YES	Time Above 10c Minutes
`trip_start_at`	timestamptz	YES	Trip Start At
`trip_end_at`	timestamptz	YES	Trip End At
`trip_duration_hours`	numeric	YES	Trip Duration Hours
`cold_chain_intact`	boolean	YES	Cold Chain Intact
`breach_detected`	boolean	YES	Breach Detected (default: `false`)
`breach_details`	text	YES	Breach Details
`data_uploaded_at`	timestamptz	YES	Data Uploaded At (default: `now()`)
`data_source`	text	YES	Data Source
`raw_file_url`	text	YES	Raw File URL
`created_at`	timestamptz	YES	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_temp_logger_cold_chain_intact on (cold_chain_intact) - idx_temp_logger_logger_id on (logger_id) - idx_temp_logger_order_id on (order_id) - idx_temp_logger_shipment_id on (shipment_id) - idx_temp_logger_uploaded_at on (data_uploaded_at)

high_risk_postcodes¶

Purpose: Postcodes requiring extra cold chain protection

Column	Type	Nullable	Description
`id`	bigint	NO	Primary key (default: `nextval('raw_ops.high_risk_postcodes_id_seq'`)
`postcode_prefix`	text	NO	Postcode Prefix
`region_name`	text	NO	Region Name
`typical_transit_hours`	integer	NO	Typical Transit Hours
`courier_service_level`	text	YES	Courier Service Level
`delivery_policy`	text	NO	Delivery Policy
`checkout_message`	text	NO	Checkout Message
`is_active`	boolean	YES	Is Active (default: `true`)
`notes`	text	YES	Notes
`created_at`	timestamptz	YES	Record created (default: `now()`)
`updated_at`	timestamptz	YES	Last modified (default: `now()`)

Indexes: - PRIMARY KEY on (id) - high_risk_postcodes_postcode_prefix_key UNIQUE on (postcode_prefix) - idx_high_risk_postcodes_active on (is_active) WHERE (is_active = true) - idx_high_risk_postcodes_prefix on (postcode_prefix)

allowed_delivery_areas¶

Purpose: Delivery coverage area definitions

Column	Type	Nullable	Description
`id`	bigint	NO	Primary key (default: `nextval('raw_ops.allowed_delivery_areas_id_seq'`)
`postcode_prefix`	text	NO	Postcode Prefix
`area_name`	text	NO	Area Name
`phase`	text	NO	Phase
`enabled_from_date`	date	YES	Enabled From Date
`typical_transit_hours`	integer	NO	Typical Transit Hours (default: `48`)
`is_active`	boolean	YES	Is Active (default: `true`)
`notes`	text	YES	Notes
`created_at`	timestamptz	YES	Record created (default: `now()`)
`updated_at`	timestamptz	YES	Last modified (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_allowed_delivery_areas_active on (is_active) WHERE (is_active = true) - idx_allowed_delivery_areas_prefix on (postcode_prefix)

1.4 Support & Customer Service Tables¶

support_tickets¶

Purpose: Customer support requests

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`customer_id`	uuid	YES	FK to customers
`customer_email`	text	NO	Customer Email
`source`	text	YES	Source (default: `'email'`)
`subject`	text	YES	Subject
`body`	text	YES	Body
`attachments`	jsonb	YES	Attachments
`ai_category`	text	YES	AI Category
`ai_confidence`	numeric	YES	AI Confidence
`ai_draft_response`	text	YES	AI Draft Response
`ai_recommended_action`	text	YES	AI Recommended Action
`ai_escalation_reason`	text	YES	AI Escalation Reason
`status`	text	YES	Status (default: `'pending'`)
`final_response`	text	YES	Final Response
`resolution_action`	text	YES	Resolution Action
`resolved_by`	text	YES	Resolved By
`related_batch_code`	text	YES	Related Batch Code
`related_order_id`	uuid	YES	Related Order ID
`created_at`	timestamptz	YES	Record created (default: `now()`)
`ai_processed_at`	timestamptz	YES	AI Processed At
`human_reviewed_at`	timestamptz	YES	Human Reviewed At
`resolved_at`	timestamptz	YES	Resolved At
`response_sent_at`	timestamptz	YES	Response Sent At
`approved_by`	text	YES	Approved By
`approved_at`	timestamptz	YES	Approved At
`response_channel`	text	YES	Response Channel
`edit_reason`	text	YES	Edit Reason
`original_draft`	text	YES	Original Draft
`crisp_session_id`	text	YES	Crisp Session ID
`is_pre_purchase`	boolean	YES	Is Pre Purchase (default: `true`)
`assigned_to`	text	YES	Assigned To
`response_method`	text	YES	Response Method
`human_edited`	boolean	YES	Human Edited (default: `false`)
`edit_distance`	integer	YES	Edit Distance
`actions_taken`	jsonb	YES	Actions Taken (default: `'[]'`)
`replacement_order_id`	text	YES	Replacement Order ID
`first_viewed_at`	timestamptz	YES	First Viewed At
`internal_notes`	jsonb	YES	Internal Notes (default: `'[]'`)
`outbound_reason`	text	YES	Outbound Reason
`is_outbound`	boolean	YES	Is Outbound (default: `false`)
`reply_count`	integer	YES	Reply Count (default: `0`)
`last_customer_reply_at`	timestamptz	YES	Last Customer Reply At
`resolution_type`	text	YES	Resolution Type
`resolution_category`	text	YES	Resolution Category
`resolution_summary`	text	YES	Resolution Summary
`draft_usage`	text	YES	Draft Usage
`last_response_at`	timestamptz	YES	Last Response At
`email_message_id`	text	YES	Email Message ID
`email_references`	text	YES	Email References
`csat_survey_scheduled_for`	timestamptz	YES	Csat Survey Scheduled For
`csat_survey_sent_at`	timestamptz	YES	Csat Survey Sent At
`csat_survey_token`	text	YES	Csat Survey Token

Indexes: - PRIMARY KEY on (id) - idx_support_tickets_csat_ready on (csat_survey_scheduled_for) WHERE ((csat_survey_scheduled_for IS NOT NULL) AND (csat_survey_sent_at IS NULL)) - idx_support_tickets_customer on (customer_id) - idx_support_tickets_outbound on (is_outbound, status) WHERE (is_outbound = true) - idx_support_tickets_resolution_type on (resolution_type) WHERE (resolution_type IS NOT NULL) - idx_support_tickets_status_open on (status) WHERE (status <> ALL (ARRAY['resolved'::text, 'closed'::text])) - idx_tickets_ai_category on (ai_category) - idx_tickets_batch on (related_batch_code) WHERE (related_batch_code IS NOT NULL) - idx_tickets_batch_recent on (related_batch_code, created_at) WHERE (ai_category = 'quality'::text) - idx_tickets_created on (created_at) - idx_tickets_crisp_session on (crisp_session_id) WHERE (crisp_session_id IS NOT NULL) - idx_tickets_customer on (customer_id) - idx_tickets_pending_queue on (status, created_at) WHERE (status = 'pending'::text) - idx_tickets_priority_queue on (status, ai_escalation_reason, ai_confidence, created_at) - idx_tickets_status on (status) - idx_tickets_status_created on (status, created_at)

ticket_messages¶

Purpose: Full conversation history per ticket

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`ticket_id`	uuid	NO	FK to support_tickets
`direction`	text	NO	Direction
`sender`	text	NO	Sender
`subject`	text	YES	Subject
`body`	text	NO	Body
`delivery_id`	text	YES	Delivery ID
`created_at`	timestamptz	YES	Record created (default: `now()`)
`email_message_id`	text	YES	Email Message ID

Indexes: - PRIMARY KEY on (id) - idx_ticket_messages_created_at on (created_at) - idx_ticket_messages_ticket_id on (ticket_id)

ticket_notes¶

Purpose: Internal agent notes on tickets

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`ticket_id`	uuid	NO	FK to support_tickets
`note_type`	text	NO	Note Type (default: `'internal'`)
`content`	text	NO	Content
`created_by`	text	YES	Created By
`created_at`	timestamptz	YES	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_ticket_notes_ticket_id on (ticket_id)

cs_agent_decisions¶

Purpose: AI classification and draft tracking

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`ticket_id`	uuid	YES	FK to support_tickets
`category`	text	YES	Category
`confidence_score`	numeric	YES	Confidence Score
`reasoning`	text	YES	Reasoning
`draft_response`	text	YES	Draft Response
`proposed_actions`	jsonb	YES	Proposed Actions (default: `'[]'`)
`escalation_reason`	text	YES	Escalation Reason
`processed_at`	timestamptz	YES	Processed At (default: `now()`)
`review_status`	text	YES	Review Status (default: `'pending'`)
`reviewed_at`	timestamptz	YES	Reviewed At
`reviewed_by`	text	YES	Reviewed By
`rejection_reason`	text	YES	Rejection Reason
`rejection_feedback`	text	YES	Rejection Feedback
`created_at`	timestamptz	YES	Record created (default: `now()`)
`source`	text	YES	Source (default: `'email'`)
`dedupe_key`	text	YES	Dedupe Key
`execution_duration_ms`	integer	YES	Execution Duration ms
`model_used`	text	YES	Model Used
`policy_gate_triggered`	boolean	YES	Policy Gate Triggered (default: `false`)
`policy_gate_code`	text	YES	Policy Gate Code

Indexes: - PRIMARY KEY on (id) - cs_agent_decisions_dedupe_key_unique UNIQUE on (dedupe_key) - idx_cs_agent_decisions_pending on (review_status) WHERE (review_status = 'pending'::text) - idx_cs_agent_decisions_review_status on (review_status) WHERE (review_status = 'pending'::text) - idx_cs_agent_decisions_source on (source) - idx_cs_agent_decisions_ticket on (ticket_id)

chat_sessions¶

Purpose: Live chat session metadata

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`visitor_id`	text	NO	Visitor ID
`visitor_email`	text	YES	Visitor Email
`customer_id`	uuid	YES	FK to customers
`started_at`	timestamptz	YES	Started At (default: `now()`)
`last_message_at`	timestamptz	YES	Last Message At (default: `now()`)
`ended_at`	timestamptz	YES	Ended At
`entry_page`	text	YES	Entry Page
`referrer`	text	YES	Referrer
`device_type`	text	YES	Device Type
`status`	text	YES	Status (default: `'active'`)
`escalated_at`	timestamptz	YES	Escalated At
`escalated_reason`	text	YES	Escalated Reason
`support_ticket_id`	uuid	YES	FK to support_tickets
`message_count`	integer	YES	Message Count (default: `0`)
`ai_message_count`	integer	YES	AI Message Count (default: `0`)
`created_at`	timestamptz	YES	Record created (default: `now()`)
`updated_at`	timestamptz	YES	Last modified (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_chat_sessions_status on (status) - idx_chat_sessions_visitor on (visitor_id)

chat_messages¶

Purpose: Live chat message history

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`session_id`	uuid	NO	FK to chat_sessions
`role`	text	NO	Role
`content`	text	NO	Content
`ai_confidence`	numeric	YES	AI Confidence
`ai_model`	text	YES	AI Model
`tokens_used`	integer	YES	Tokens Used
`response_time_ms`	integer	YES	Response Time ms
`escalation_trigger`	boolean	YES	Escalation Trigger (default: `false`)
`edited`	boolean	YES	Edited (default: `false`)
`created_at`	timestamptz	YES	Record created (default: `now()`)
`user_rating`	text	YES	User Rating
`user_rating_at`	timestamptz	YES	User Rating At
`user_rating_feedback`	text	YES	User Rating Feedback

Indexes: - PRIMARY KEY on (id) - idx_chat_messages_rated on (user_rating, created_at) WHERE (user_rating IS NOT NULL) - idx_chat_messages_session on (session_id, created_at)

escalation_queue¶

Purpose: Tickets requiring human escalation

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`created_at`	timestamptz	YES	Record created (default: `now()`)
`ticket_id`	uuid	NO	FK to support_tickets
`reason`	text	NO	Reason
`urgency`	text	NO	Urgency
`agent_recommendation`	text	YES	Agent Recommendation
`context_summary`	text	YES	Context Summary
`queued_at`	timestamptz	NO	Queued At
`position`	integer	YES	Position
`processed_at`	timestamptz	YES	Processed At
`holding_response_sent`	boolean	YES	Holding Response Sent (default: `false`)
`holding_response_sent_at`	timestamptz	YES	Holding Response Sent At

Indexes: - PRIMARY KEY on (id) - idx_escalation_queue_pending on (queued_at) WHERE (processed_at IS NULL) - idx_escalation_queue_urgency on (urgency, queued_at) WHERE (processed_at IS NULL)

csat_responses¶

Purpose: Customer satisfaction survey responses

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`ticket_id`	uuid	NO	FK to support_tickets
`customer_id`	uuid	NO	FK to customers
`rating`	integer	NO	Rating
`comment`	text	YES	Comment
`submitted_at`	timestamptz	YES	Submitted At (default: `now()`)
`submitted_from`	text	YES	Submitted From (default: `'email_link'`)

Indexes: - PRIMARY KEY on (id) - csat_responses_ticket_id_key UNIQUE on (ticket_id) - idx_csat_responses_rating on (rating) - idx_csat_responses_submitted_at on (submitted_at)

customer_health_scores¶

Purpose: Computed customer health metrics

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`customer_id`	uuid	NO	FK to customers
`health_score`	integer	NO	Health Score
`risk_tier`	text	NO	Risk Tier
`previous_score`	integer	YES	Previous Score
`previous_tier`	text	YES	Previous Tier
`score_delta`	integer	YES	Score Delta
`tier_changed`	boolean	YES	Tier Changed
`delivery_score`	integer	NO	Delivery Score
`engagement_score`	integer	NO	Engagement Score
`support_score`	integer	NO	Support Score
`payment_score`	integer	NO	Payment Score
`tenure_score`	integer	NO	Tenure Score
`skip_score`	integer	NO	Skip Score
`delivery_issues_count`	integer	YES	Delivery Issues Count (default: `0`)
`skipped_boxes_count`	integer	YES	Skipped Boxes Count (default: `0`)
`support_tickets_count`	integer	YES	Support Tickets Count (default: `0`)
`portal_logins_count`	integer	YES	Portal Logins Count (default: `0`)
`proof_scans_count`	integer	YES	Proof Scans Count (default: `0`)
`email_opens_count`	integer	YES	Email Opens Count (default: `0`)
`payment_failures_count`	integer	YES	Payment Failures Count (default: `0`)
`subscription_age_days`	integer	YES	Subscription Age Days
`total_orders_delivered`	integer	YES	Total Orders Delivered
`lifetime_value_pence`	integer	YES	Lifetime Value Pence
`calculated_at`	timestamptz	NO	Calculated At (default: `now()`)
`calculation_version`	text	NO	Calculation Version (default: `'1.0'`)
`created_at`	timestamptz	NO	Record created (default: `now()`)
`updated_at`	timestamptz	NO	Last modified (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_health_scores_at_risk on (customer_id, health_score) WHERE (risk_tier = ANY (ARRAY['at_risk'::text, 'critical'::text])) - idx_health_scores_calculated on (calculated_at) - idx_health_scores_customer on (customer_id) - idx_health_scores_score on (health_score) - idx_health_scores_tier on (risk_tier) - idx_health_scores_tier_changed on (customer_id) WHERE (tier_changed = true) - unique_customer_health UNIQUE on (customer_id)

customer_interventions¶

Purpose: Proactive customer intervention records

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`customer_id`	uuid	NO	FK to customers
`intervention_type`	text	NO	Intervention Type
`trigger_component`	text	NO	Trigger Component
`trigger_value`	integer	NO	Trigger Value
`created_at`	timestamptz	NO	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_customer_interventions_lookup on (customer_id, intervention_type, created_at)

customer_engagement_events¶

Purpose: Customer engagement activity tracking

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`customer_id`	uuid	NO	FK to customers
`event_type`	text	NO	Event Type
`event_source`	text	NO	Event Source (default: `'system'`)
`event_metadata`	jsonb	YES	Event Metadata (default: `'{}'`)
`created_at`	timestamptz	NO	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_engagement_created on (created_at) - idx_engagement_customer on (customer_id) - idx_engagement_customer_created on (customer_id, created_at) - idx_engagement_type on (event_type)

testimonials¶

Purpose: Customer testimonials and reviews

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`customer_id`	uuid	NO	FK to customers
`source_ticket_id`	uuid	YES	FK to support_tickets
`source_type`	text	NO	Source Type (default: `'email_reply'`)
`original_text`	text	NO	Original Text
`approved_quote`	text	YES	Approved Quote
`display_city`	text	YES	Display City
`status`	text	NO	Status (default: `'flagged'`)
`flagged_at`	timestamptz	YES	Flagged At (default: `now()`)
`flagged_by`	text	YES	Flagged By
`request_sent_at`	timestamptz	YES	Request Sent At
`request_email_delivery_id`	text	YES	Request Email Delivery ID
`approved_at`	timestamptz	YES	Approved At
`approval_method`	text	YES	Approval Method
`is_featured`	boolean	YES	Is Featured (default: `false`)
`display_order`	integer	YES	Display Order
`created_at`	timestamptz	YES	Record created (default: `now()`)
`updated_at`	timestamptz	YES	Last modified (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_testimonials_customer on (customer_id) - idx_testimonials_featured on (is_featured) WHERE ((status = 'approved'::text) AND (is_featured = true)) - idx_testimonials_status on (status)

1.5 AI & Knowledge Base Tables¶

ai_knowledge_sections¶

Purpose: Knowledge base sections for AI agent

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`section_key`	text	NO	Section Key
`version`	integer	NO	Version (default: `1`)
`title`	text	NO	Title
`content`	text	NO	Content
`is_active`	boolean	NO	Is Active (default: `false`)
`created_at`	timestamptz	NO	Record created (default: `now()`)
`created_by`	text	YES	Created By (default: `'system'`)
`notes`	text	YES	Notes
`embedding`	vector	YES	Embedding

Indexes: - PRIMARY KEY on (id) - idx_ai_knowledge_active on (section_key) WHERE (is_active = true) - idx_kb_sections_embedding on (embedding) - unique_section_version UNIQUE on (section_key, version)

ai_prompt_templates¶

Purpose: Reusable AI prompt templates

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`channel`	text	NO	Channel
`section_key`	text	NO	Section Key
`section_order`	integer	NO	Section Order
`is_included`	boolean	NO	Is Included (default: `true`)
`wrapper_prefix`	text	YES	Wrapper Prefix
`wrapper_suffix`	text	YES	Wrapper Suffix
`created_at`	timestamptz	NO	Record created (default: `now()`)
`updated_at`	timestamptz	NO	Last modified (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_ai_templates_channel on (channel, section_order) WHERE (is_included = true) - unique_channel_section UNIQUE on (channel, section_key)

ai_prompt_logs¶

Purpose: Log of AI prompt executions

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`channel`	text	NO	Channel
`assembled_at`	timestamptz	NO	Assembled At (default: `now()`)
`section_versions`	jsonb	NO	Section Versions
`prompt_hash`	text	NO	Prompt Hash
`token_estimate`	integer	YES	Token Estimate

Indexes: - PRIMARY KEY on (id) - idx_ai_logs_recent on (channel, assembled_at)

agents¶

Purpose: Registered AI agent definitions

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`name`	text	NO	Name
`email`	text	NO	Email
`role`	text	NO	Role (default: `'agent'`)
`is_active`	boolean	NO	Is Active (default: `true`)
`created_at`	timestamptz	NO	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id) - agents_email_key UNIQUE on (email) - idx_agents_active on (is_active) WHERE (is_active = true)

agent_executions¶

Purpose: AI agent execution runs

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`created_at`	timestamptz	YES	Record created (default: `now()`)
`ticket_id`	uuid	YES	FK to support_tickets
`trigger_source`	text	NO	Trigger Source
`model_used`	text	YES	Model Used (default: `'claude-sonnet-4-20250514'`)
`started_at`	timestamptz	NO	Started At
`completed_at`	timestamptz	YES	Completed At
`duration_ms`	integer	YES	Duration ms
`input_tokens`	integer	YES	Input Tokens
`output_tokens`	integer	YES	Output Tokens
`total_tokens`	integer	YES	Total Tokens
`outcome`	text	YES	Outcome
`confidence_score`	numeric	YES	Confidence Score
`tools_used`	ARRAY	YES	Tools Used
`safety_filter_triggered`	boolean	YES	Safety Filter Triggered (default: `false`)
`safety_filter_violations`	ARRAY	YES	Safety Filter Violations
`error_message`	text	YES	Error Message
`error_type`	text	YES	Error Type
`dedupe_key`	text	YES	Dedupe Key

Indexes: - PRIMARY KEY on (id) - agent_executions_dedupe_key_key UNIQUE on (dedupe_key) - idx_agent_executions_created on (created_at) - idx_agent_executions_dedupe_key on (dedupe_key) WHERE (dedupe_key IS NOT NULL) - idx_agent_executions_escalated_recent on (created_at) WHERE (outcome = 'escalated'::text) - idx_agent_executions_outcome on (outcome) - idx_agent_executions_safety on (safety_filter_triggered) WHERE (safety_filter_triggered = true) - idx_agent_executions_ticket on (ticket_id)

agent_decisions¶

Purpose: AI agent decision records

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`created_at`	timestamptz	YES	Record created (default: `now()`)
`execution_id`	uuid	NO	FK to agent_executions
`ticket_id`	uuid	YES	FK to support_tickets
`decision_type`	text	NO	Decision Type
`decision_rationale`	text	YES	Decision Rationale
`confidence_score`	numeric	YES	Confidence Score
`escalation_reason`	text	YES	Escalation Reason
`agent_recommendation`	text	YES	Agent Recommendation
`human_reviewed_at`	timestamptz	YES	Human Reviewed At
`human_reviewer`	text	YES	Human Reviewer
`human_agreed`	boolean	YES	Human Agreed
`human_feedback`	text	YES	Human Feedback

Indexes: - PRIMARY KEY on (id) - agent_decisions_execution_id_unique UNIQUE on (execution_id) - idx_agent_decisions_confidence on (confidence_score) - idx_agent_decisions_execution on (execution_id) - idx_agent_decisions_reviewed on (human_reviewed_at) WHERE (human_reviewed_at IS NOT NULL) - idx_agent_decisions_type on (decision_type)

agent_tool_calls¶

Purpose: AI agent tool call audit log

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`created_at`	timestamptz	YES	Record created (default: `now()`)
`execution_id`	uuid	NO	FK to agent_executions
`tool_name`	text	NO	Tool Name
`tool_input`	jsonb	NO	Tool Input
`tool_output`	jsonb	YES	Tool Output
`started_at`	timestamptz	NO	Started At
`completed_at`	timestamptz	YES	Completed At
`duration_ms`	integer	YES	Duration ms
`success`	boolean	YES	Success
`error_message`	text	YES	Error Message

Indexes: - PRIMARY KEY on (id) - idx_agent_tool_calls_created on (created_at) - idx_agent_tool_calls_execution on (execution_id) - idx_agent_tool_calls_tool on (tool_name)

1.6 Subscription & Billing Tables¶

subscriptions¶

Purpose: Local cache of Seal subscription state

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`customer_id`	uuid	NO	FK to customers
`shopify_subscription_id`	text	NO	Shopify Subscription ID
`seal_subscription_id`	text	YES	Seal Subscription ID
`status`	text	NO	Status (default: `'active'`)
`frequency_weeks`	integer	NO	Frequency Weeks (default: `4`)
`price_gbp`	numeric	NO	Price GBP
`next_billing_date`	date	YES	Next Billing Date
`created_at`	timestamptz	NO	Record created (default: `now()`)
`updated_at`	timestamptz	NO	Last modified (default: `now()`)
`paused_at`	timestamptz	YES	Paused At
`cancelled_at`	timestamptz	YES	Cancelled At
`box_size`	text	YES	Box Size (default: `'12kg'`)
`shipping_address`	text	YES	Shipping Address
`email`	text	YES	Email
`card_brand`	text	YES	Card Brand
`card_last_digits`	text	YES	Card Last Digits
`card_expiry_month`	integer	YES	Card Expiry Month
`card_expiry_year`	integer	YES	Card Expiry Year
`payment_status`	text	YES	Payment Status (default: `'healthy'`)
`failed_payment_count`	integer	YES	Failed Payment Count (default: `0`)
`last_failed_at`	timestamptz	YES	Last Failed At
`dunning_started_at`	timestamptz	YES	Dunning Started At
`last_synced_at`	timestamptz	YES	Last Synced At (default: `now()`)
`seal_payload`	jsonb	YES	Seal Payload
`reminder_7day_sent_at`	timestamptz	YES	Reminder 7day Sent At
`reminder_48hour_sent_at`	timestamptz	YES	Reminder 48hour Sent At
`dunning_email_day0_sent`	boolean	YES	Dunning Email Day0 Sent (default: `false`)
`dunning_email_day3_sent`	boolean	YES	Dunning Email Day3 Sent (default: `false`)
`dunning_email_day7_sent`	boolean	YES	Dunning Email Day7 Sent (default: `false`)
`dunning_email_day10_sent`	boolean	YES	Dunning Email Day10 Sent (default: `false`)
`last_error_message`	text	YES	Last Error Message
`pause_reason`	text	YES	Pause Reason
`seal_internal_id`	text	YES	Seal Internal ID
`shopify_customer_id`	bigint	YES	Shopify Customer ID
`delivery_interval`	text	YES	Delivery Interval
`billing_interval`	text	YES	Billing Interval
`variant_id`	bigint	YES	Variant ID
`currency`	text	YES	Currency (default: `'GBP'`)
`subscription_created_at`	timestamptz	YES	Subscription Created At

Indexes: - PRIMARY KEY on (id) - idx_subscriptions_card_expiry on (card_expiry_year, card_expiry_month) WHERE (status = 'active'::text) - idx_subscriptions_customer on (customer_id) - idx_subscriptions_dunning on (dunning_started_at) WHERE (dunning_started_at IS NOT NULL) - idx_subscriptions_dunning_active on (dunning_started_at, last_failed_at) WHERE ((payment_status = ANY (ARRAY['failing'::text, 'failed'::text])) AND (status = 'ACTIVE'::text)) - idx_subscriptions_payment_status on (payment_status) WHERE (payment_status <> 'healthy'::text) - idx_subscriptions_shopify on (shopify_subscription_id) - idx_subscriptions_status on (status) - subscriptions_shopify_subscription_id_key UNIQUE on (shopify_subscription_id)

subscription_events¶

Purpose: Immutable log of all subscription changes

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`subscription_id`	uuid	YES	FK to subscriptions
`seal_subscription_id`	text	YES	Seal Subscription ID
`customer_id`	uuid	YES	FK to customers
`event_type`	text	NO	Event Type
`event_source`	text	NO	Event Source
`old_status`	text	YES	Old Status
`new_status`	text	YES	New Status
`payload`	jsonb	YES	Payload
`event_at`	timestamptz	NO	Event At
`created_at`	timestamptz	YES	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_sub_events_at on (event_at) - idx_sub_events_customer on (customer_id) - idx_sub_events_sub on (subscription_id) - idx_sub_events_type on (event_type) - idx_subscription_events_created on (created_at) - idx_subscription_events_customer_id on (customer_id) - idx_subscription_events_customer_recent on (customer_id, created_at) - idx_subscription_events_seal_id on (seal_subscription_id) - idx_subscription_events_subscription_id on (subscription_id) - idx_subscription_events_type on (event_type) - idx_subscription_events_type_created on (event_type, created_at)

subscription_actions¶

Purpose: Subscription modification actions

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`customer_id`	uuid	NO	FK to customers
`subscription_id`	uuid	YES	FK to subscriptions
`action_type`	text	NO	Action Type
`action_data`	jsonb	YES	Action Data (default: `'{}'`)
`source`	text	NO	Source (default: `'portal'`)
`created_at`	timestamptz	NO	Record created (default: `now()`)
`seal_synced`	boolean	YES	Seal Synced (default: `false`)
`email_sent`	boolean	YES	Email Sent (default: `false`)

Indexes: - PRIMARY KEY on (id) - idx_subscription_actions_created on (created_at) - idx_subscription_actions_customer on (customer_id) - idx_subscription_actions_subscription on (subscription_id) - idx_subscription_actions_type on (action_type)

subscription_config¶

Purpose: Subscription configuration parameters

Column	Type	Nullable	Description
`config_key`	text	NO	Config Key
`config_value`	text	NO	Config Value
`description`	text	YES	Description
`updated_at`	timestamptz	YES	Last modified (default: `now()`)
`updated_by`	text	YES	Updated By

Indexes: - PRIMARY KEY on (config_key)

billing_attempts¶

Purpose: Payment billing attempt records

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`subscription_id`	uuid	YES	FK to subscriptions
`seal_subscription_id`	text	NO	Seal Subscription ID
`seal_billing_attempt_id`	text	NO	Seal Billing Attempt ID
`customer_id`	uuid	YES	FK to customers
`scheduled_at`	timestamptz	NO	Scheduled At
`attempted_at`	timestamptz	YES	Attempted At
`completed_at`	timestamptz	YES	Completed At
`status`	text	NO	Status (default: `'scheduled'`)
`shopify_order_id`	text	YES	Shopify Order ID
`order_id`	uuid	YES	FK to orders
`error_code`	text	YES	Error Code
`error_message`	text	YES	Error Message
`retry_count`	integer	YES	Retry Count (default: `0`)
`requires_authentication`	boolean	YES	Requires Authentication (default: `false`)
`authentication_url`	text	YES	Authentication URL
`amount_pence`	integer	YES	Amount Pence
`currency`	text	YES	Currency (default: `'GBP'`)
`is_retry`	boolean	YES	Is Retry (default: `false`)
`original_attempt_id`	uuid	YES	FK to billing_attempts
`triggered_manually`	boolean	YES	Triggered Manually (default: `false`)
`seal_payload`	jsonb	YES	Seal Payload
`created_at`	timestamptz	YES	Record created (default: `now()`)
`updated_at`	timestamptz	YES	Last modified (default: `now()`)

Indexes: - PRIMARY KEY on (id) - billing_attempts_seal_billing_attempt_id_key UNIQUE on (seal_billing_attempt_id) - idx_billing_attempts_customer_id on (customer_id) - idx_billing_attempts_customer_recent on (customer_id, attempted_at) - idx_billing_attempts_failed on (subscription_id, status) WHERE (status = 'failed'::text) - idx_billing_attempts_scheduled on (scheduled_at) - idx_billing_attempts_seal_id on (seal_billing_attempt_id) - idx_billing_attempts_seal_sub on (seal_subscription_id) - idx_billing_attempts_status on (status) - idx_billing_attempts_sub on (subscription_id) - idx_billing_attempts_subscription_id on (subscription_id)

payment_events¶

Purpose: Payment lifecycle events

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`customer_id`	uuid	NO	FK to customers
`order_id`	uuid	YES	FK to orders
`subscription_id`	uuid	YES	Subscription ID
`event_type`	text	NO	Event Type
`failure_reason`	text	YES	Failure Reason
`attempt_number`	integer	YES	Attempt Number (default: `1`)
`amount_pence`	integer	YES	Amount Pence
`shopify_event_id`	text	YES	Shopify Event ID
`event_metadata`	jsonb	YES	Event Metadata (default: `'{}'`)
`created_at`	timestamptz	NO	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_payment_events_created on (created_at) - idx_payment_events_customer on (customer_id) - idx_payment_events_customer_created on (customer_id, created_at) - idx_payment_events_type on (event_type)

seal_webhook_inbox¶

Purpose: Raw incoming Seal webhooks

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`webhook_topic`	text	NO	Webhook Topic
`seal_subscription_id`	text	YES	Seal Subscription ID
`payload`	jsonb	NO	Payload
`headers`	jsonb	YES	Headers
`hmac_signature`	text	YES	Hmac Signature
`hmac_valid`	boolean	YES	Hmac Valid
`processing_status`	text	YES	Processing Status (default: `'pending'`)
`processing_attempts`	integer	YES	Processing Attempts (default: `0`)
`last_error`	text	YES	Last Error
`error_category`	text	YES	Error Category
`processed_at`	timestamptz	YES	Processed At
`processing_duration_ms`	integer	YES	Processing Duration ms
`idempotency_key`	text	YES	Idempotency Key
`received_at`	timestamptz	YES	Received At (default: `now()`)
`created_at`	timestamptz	YES	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_seal_webhook_inbox_recent on (created_at) - idx_seal_webhook_inbox_retry_queue on (received_at) WHERE (processing_status = ANY (ARRAY['pending'::text, 'failed'::text])) - idx_seal_webhook_inbox_status on (processing_status) - idx_seal_webhook_inbox_subscription on (seal_subscription_id) - idx_seal_webhook_inbox_topic on (webhook_topic) - idx_seal_webhook_received on (received_at) - idx_seal_webhook_status on (processing_status) - idx_seal_webhook_sub_id on (seal_subscription_id) - idx_seal_webhook_topic on (webhook_topic) - seal_webhook_inbox_idempotency_key_key UNIQUE on (idempotency_key)

1.7 Customer Credits & Referrals¶

customer_credits¶

Purpose: Customer credit balances

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`customer_id`	uuid	NO	FK to customers
`amount_pence`	integer	NO	Amount Pence
`remaining_pence`	integer	NO	Remaining Pence
`source_type`	text	NO	Source Type
`source_id`	uuid	YES	Source ID
`source_description`	text	YES	Source Description
`status`	text	NO	Status (default: `'available'`)
`expires_at`	timestamptz	NO	Expires At
`expiry_warning_sent_at`	timestamptz	YES	Expiry Warning Sent At
`created_at`	timestamptz	NO	Record created (default: `now()`)
`updated_at`	timestamptz	NO	Last modified (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_credits_expiry_warning on (expires_at, expiry_warning_sent_at) WHERE ((status = 'active'::text) AND (remaining_pence > 0)) - idx_customer_credits_customer_available on (customer_id, created_at) WHERE (status = 'available'::text) - idx_customer_credits_customer_id on (customer_id) - idx_customer_credits_expires_at on (expires_at) WHERE (status = 'available'::text) - idx_customer_credits_expiry_warning on (expires_at) WHERE ((status = 'available'::text) AND (expiry_warning_sent_at IS NULL)) - idx_customer_credits_source on (source_type, source_id) - idx_customer_credits_status on (status)

customer_credit_events¶

Purpose: Credit balance change events

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`event_type`	text	NO	Event Type
`credit_id`	uuid	YES	FK to customer_credits
`customer_id`	uuid	YES	FK to customers
`order_id`	uuid	YES	FK to orders
`application_id`	uuid	YES	FK to customer_credit_applications
`payload`	jsonb	NO	Payload (default: `'{}'`)
`created_at`	timestamptz	NO	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_credit_events_credit_id on (credit_id) - idx_credit_events_customer_id on (customer_id) - idx_credit_events_order_id on (order_id) - idx_credit_events_type on (event_type)

customer_credit_applications¶

Purpose: Credit application to orders

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`credit_id`	uuid	NO	FK to customer_credits
`order_id`	uuid	NO	FK to orders
`customer_id`	uuid	NO	FK to customers
`amount_applied_pence`	integer	NO	Amount Applied Pence
`status`	text	NO	Status (default: `'applied'`)
`reversed_at`	timestamptz	YES	Reversed At
`reversed_reason`	text	YES	Reversed Reason
`applied_at`	timestamptz	NO	Applied At (default: `now()`)

Indexes: - PRIMARY KEY on (id) - customer_credit_applications_credit_id_order_id_key UNIQUE on (credit_id, order_id) - idx_credit_applications_credit_id on (credit_id) - idx_credit_applications_customer_id on (customer_id) - idx_credit_applications_order_id on (order_id)

referral_codes¶

Purpose: Referral code definitions

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`customer_id`	uuid	NO	FK to customers
`slug`	text	NO	Slug
`display_name`	text	YES	Display Name
`total_referrals`	integer	YES	Total Referrals (default: `0`)
`successful_referrals`	integer	YES	Successful Referrals (default: `0`)
`total_rewards_earned_pence`	integer	YES	Total Rewards Earned Pence (default: `0`)
`is_active`	boolean	YES	Is Active (default: `true`)
`paused_at`	timestamptz	YES	Paused At
`paused_reason`	text	YES	Paused Reason
`created_at`	timestamptz	YES	Record created (default: `now()`)
`updated_at`	timestamptz	YES	Last modified (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_referral_codes_active on (is_active) WHERE (is_active = true) - idx_referral_codes_customer_id on (customer_id) - idx_referral_codes_slug on (slug) - referral_codes_slug_key UNIQUE on (slug) - unique_customer_referral_code UNIQUE on (customer_id)

referrals¶

Purpose: Referral relationship records

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`referral_code_id`	uuid	NO	FK to referral_codes
`referrer_customer_id`	uuid	NO	FK to customers
`referee_customer_id`	uuid	YES	FK to customers
`referee_email`	text	NO	Referee Email
`referee_order_id`	uuid	YES	FK to orders
`referee_first_delivery_at`	timestamptz	YES	Referee First Delivery At
`status`	text	NO	Status (default: `'pending'`)
`fraud_flags`	jsonb	YES	Fraud Flags (default: `'[]'`)
`fraud_checked_at`	timestamptz	YES	Fraud Checked At
`referrer_reward_id`	uuid	YES	FK to referral_rewards
`created_at`	timestamptz	YES	Record created (default: `now()`)
`confirmed_at`	timestamptz	YES	Confirmed At
`rewarded_at`	timestamptz	YES	Rewarded At
`updated_at`	timestamptz	YES	Last modified (default: `now()`)
`credit_id`	uuid	YES	FK to customer_credits

Indexes: - PRIMARY KEY on (id) - idx_referrals_credit_id on (credit_id) WHERE (credit_id IS NOT NULL) - idx_referrals_pending on (status, created_at) WHERE (status = 'pending'::text) - idx_referrals_referee_customer_id on (referee_customer_id) - idx_referrals_referee_email on (referee_email) - idx_referrals_referral_code_id on (referral_code_id) - idx_referrals_referrer_customer_id on (referrer_customer_id) - idx_referrals_status on (status) - unique_referee_email UNIQUE on (referee_email)

referral_events¶

Purpose: Referral lifecycle events

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`event_type`	text	NO	Event Type
`referral_id`	uuid	YES	FK to referrals
`referral_code_id`	uuid	YES	FK to referral_codes
`reward_id`	uuid	YES	FK to referral_rewards
`order_id`	uuid	YES	FK to orders
`customer_id`	uuid	YES	FK to customers
`payload`	jsonb	NO	Payload (default: `'{}'`)
`created_at`	timestamptz	YES	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_referral_events_created on (created_at) - idx_referral_events_order_id on (order_id) WHERE (order_id IS NOT NULL) - idx_referral_events_referral_id on (referral_id) WHERE (referral_id IS NOT NULL) - idx_referral_events_type on (event_type, created_at)

referral_rewards¶

Purpose: Referral reward issuance records

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`customer_id`	uuid	NO	FK to customers
`referral_id`	uuid	NO	FK to referrals
`discount_code`	text	NO	Discount Code
`amount_pence`	integer	NO	Amount Pence (default: `1500`)
`is_used`	boolean	YES	Is Used (default: `false`)
`used_at`	timestamptz	YES	Used At
`used_order_id`	uuid	YES	FK to orders
`expires_at`	timestamptz	NO	Expires At
`is_expired`	boolean	YES	Is Expired (default: `false`)
`shopify_discount_id`	text	YES	Shopify Discount ID
`shopify_synced_at`	timestamptz	YES	Shopify Synced At
`created_at`	timestamptz	YES	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_referral_rewards_customer_id on (customer_id) - idx_referral_rewards_discount_code on (discount_code) - idx_referral_rewards_expiring on (expires_at) WHERE ((is_used = false) AND (is_expired = false)) - idx_referral_rewards_referral_id on (referral_id) - idx_referral_rewards_unused on (customer_id, is_used, is_expired) WHERE ((is_used = false) AND (is_expired = false)) - referral_rewards_discount_code_key UNIQUE on (discount_code)

refunds¶

Purpose: Refund records and processing status

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`created_at`	timestamptz	YES	Record created (default: `now()`)
`order_id`	uuid	YES	FK to orders
`customer_id`	uuid	YES	FK to customers
`support_ticket_id`	uuid	YES	FK to support_tickets
`shopify_refund_id`	text	YES	Shopify Refund ID
`amount_gbp`	numeric	NO	Amount GBP
`refund_type`	text	NO	Refund Type
`reason`	text	NO	Reason
`processed_by`	text	YES	Processed By
`processed_at`	timestamptz	YES	Processed At
`status`	text	YES	Status (default: `'pending'`)
`internal_note`	text	YES	Internal Note
`customer_notified`	boolean	YES	Customer Notified (default: `false`)
`shopify_order_id`	text	YES	Shopify Order ID
`shopify_synced`	boolean	YES	Shopify Synced (default: `false`)
`shopify_synced_at`	timestamptz	YES	Shopify Synced At
`shopify_error`	text	YES	Shopify Error
`notes`	text	YES	Notes

Indexes: - PRIMARY KEY on (id) - idx_refunds_created_at on (created_at) - idx_refunds_customer on (customer_id) - idx_refunds_customer_id on (customer_id) - idx_refunds_order on (order_id) - idx_refunds_order_id on (order_id) - idx_refunds_status on (status) WHERE (status = 'pending'::text) - idx_refunds_ticket on (support_ticket_id)

replacement_requests¶

Purpose: Product replacement request records

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`created_at`	timestamptz	YES	Record created (default: `now()`)
`ticket_id`	uuid	NO	FK to support_tickets
`customer_id`	uuid	NO	FK to customers
`original_order_id`	uuid	NO	FK to orders
`reason`	text	NO	Reason
`agent_notes`	text	YES	Agent Notes
`status`	text	YES	Status (default: `'pending'`)
`reviewed_at`	timestamptz	YES	Reviewed At
`reviewed_by`	text	YES	Reviewed By
`review_notes`	text	YES	Review Notes
`replacement_order_id`	uuid	YES	FK to orders
`fulfilled_at`	timestamptz	YES	Fulfilled At

Indexes: - PRIMARY KEY on (id) - idx_replacement_requests_customer on (customer_id) - idx_replacement_requests_pending on (created_at) WHERE (status = 'pending'::text) - idx_replacement_requests_status on (status)

1.8 Calculator & Portal Tables¶

calculator_tokens¶

Purpose: Calculator session tokens

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`token`	uuid	NO	Token (default: `gen_random_uuid()`)
`box_size`	text	NO	Box Size
`daily_grams`	integer	NO	Daily Grams
`delivery_weeks`	integer	NO	Delivery Weeks
`status`	text	NO	Status (default: `'pending'`)
`email`	text	YES	Email
`address_line_1`	text	YES	Address Line 1
`shopify_discount_code`	text	YES	Shopify Discount Code
`shopify_price_rule_id`	text	YES	Shopify Price Rule ID
`created_at`	timestamptz	NO	Record created (default: `now()`)
`validated_at`	timestamptz	YES	Validated At
`used_at`	timestamptz	YES	Used At
`expires_at`	timestamptz	NO	Expires At (default: `(now() + '30 days'`)
`user_agent`	text	YES	User Agent
`ip_address`	inet	YES	Ip Address

Indexes: - PRIMARY KEY on (id) - calculator_tokens_token_key UNIQUE on (token) - idx_calculator_tokens_status on (status, created_at) WHERE (status = ANY (ARRAY['pending'::text, 'validated'::text])) - idx_calculator_tokens_token on (token) WHERE (status <> 'used'::text)

calculator_pets¶

Purpose: Pet profiles from calculator

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`discount_id`	uuid	NO	FK to calculator_discounts
`pet_number`	integer	NO	Pet Number
`pet_name`	text	YES	Pet Name
`life_stage`	text	NO	Life Stage
`weight_kg`	numeric	NO	Weight kg
`age_range`	text	YES	Age Range
`neutered`	boolean	YES	Neutered
`activity_level`	text	NO	Activity Level
`body_condition`	text	YES	Body Condition
`calculated_rer`	numeric	NO	Calculated Rer
`calculated_mer`	numeric	NO	Calculated Mer
`daily_grams`	integer	NO	Daily Grams
`monthly_price_gbp`	integer	NO	Monthly Price GBP
`created_at`	timestamptz	YES	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_calculator_pets_discount on (discount_id) - unique_pet_per_discount UNIQUE on (discount_id, pet_number)

calculator_discounts¶

Purpose: Calculator discount definitions

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`token`	text	NO	Token
`box_size`	text	NO	Box Size
`daily_grams`	integer	NO	Daily Grams
`delivery_weeks`	integer	NO	Delivery Weeks
`regular_price`	integer	NO	Regular Price
`discounted_price`	integer	NO	Discounted Price
`used`	boolean	YES	Used (default: `false`)
`used_at`	timestamptz	YES	Used At
`email`	text	YES	Email
`address_line_1`	text	YES	Address Line 1
`discount_code`	text	YES	Discount Code
`fraud_flag`	boolean	YES	Fraud Flag (default: `false`)
`fraud_reason`	text	YES	Fraud Reason
`created_at`	timestamptz	YES	Record created (default: `now()`)
`expires_at`	timestamptz	YES	Expires At (default: `(now() + '72:00:00'`)
`postcode`	text	YES	Postcode
`address`	text	YES	Address
`pet_count`	integer	YES	Pet Count (default: `1`)
`household_total_gbp`	integer	YES	Household Total GBP
`household_daily_grams`	integer	YES	Household Daily Grams
`dog_name`	text	YES	Dog Name
`abandonment_sent_at`	timestamptz	YES	Abandonment Sent At
`calculated_mer`	integer	YES	Calculated Mer
`previous_diet`	text	YES	Previous Diet

Indexes: - PRIMARY KEY on (id) - calculator_discounts_token_key UNIQUE on (token) - idx_calculator_discounts_abandonment_queue on (created_at) WHERE ((used = false) AND (abandonment_sent_at IS NULL) AND (email IS NOT NULL) AND (discount_code IS NOT NULL)) - idx_calculator_discounts_address on (address_line_1) - idx_calculator_discounts_email on (email) - idx_calculator_discounts_expires on (expires_at) - idx_calculator_discounts_postcode on (postcode) - idx_calculator_discounts_token on (token) - idx_calculator_discounts_used on (used)

calculator_discount_usage¶

Purpose: Calculator discount usage tracking

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`email`	text	NO	Email
`address_line_1`	text	NO	Address Line 1
`token_id`	uuid	YES	FK to calculator_tokens
`used_at`	timestamptz	NO	Used At (default: `now()`)

Indexes: - PRIMARY KEY on (id) - calculator_discount_usage_email_address_line_1_key UNIQUE on (email, address_line_1) - idx_calculator_discount_usage_lookup on (email, address_line_1)

portal_sessions¶

Purpose: Customer/ops portal session tracking

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`customer_id`	uuid	NO	FK to customers
`token`	text	NO	Token
`created_at`	timestamptz	NO	Record created (default: `now()`)
`expires_at`	timestamptz	NO	Expires At (default: `(now() + '7 days'`)
`last_accessed_at`	timestamptz	YES	Last Accessed At
`ip_address`	text	YES	Ip Address
`user_agent`	text	YES	User Agent

Indexes: - PRIMARY KEY on (id) - idx_portal_sessions_customer on (customer_id) - idx_portal_sessions_expires on (expires_at) - idx_portal_sessions_token on (token) - portal_sessions_token_key UNIQUE on (token)

1.9 Email & Communications Tables¶

outbox¶

Purpose: Email/notification outbox queue

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`created_at`	timestamptz	NO	Record created (default: `now()`)
`target`	text	NO	Target
`event_type`	text	NO	Event Type
`payload_json`	jsonb	NO	Payload JSON
`state`	text	NO	State (default: `'pending'`)
`attempts`	integer	NO	Attempts (default: `0`)
`first_sent_at`	timestamptz	YES	First Sent At
`last_sent_at`	timestamptz	YES	Last Sent At
`error_message`	text	YES	Error Message
`manifest_version`	text	NO	Manifest Version (default: `'v1'`)
`hash`	text	YES	Hash
`kind`	text	YES	Kind
`updated_at`	timestamptz	NO	Last modified (default: `now()`)
`idempotency_key`	text	YES	Idempotency Key

Indexes: - PRIMARY KEY on (id) - idx_outbox_state_created on (state, created_at) WHERE (state = 'pending'::text) - ix_outbox_state_created_at on (state, created_at) - ix_outbox_target_state on (target, state) - outbox_idempotency_key_key UNIQUE on (idempotency_key)

email_events¶

Purpose: Email delivery event tracking

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`customer_id`	uuid	NO	FK to customers
`subscription_id`	uuid	NO	FK to subscriptions
`event_type`	text	NO	Event Type
`billing_date`	date	NO	Billing Date
`sent_at`	timestamptz	YES	Sent At (default: `now()`)

Indexes: - PRIMARY KEY on (id) - email_events_subscription_id_event_type_billing_date_key UNIQUE on (subscription_id, event_type, billing_date) - idx_email_events_lookup on (subscription_id, event_type, billing_date)

lifecycle_events¶

Purpose: Customer lifecycle event log

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`event_name`	text	NO	Event Name
`idempotency_key`	text	NO	Idempotency Key
`customer_id`	uuid	NO	FK to customers
`customer_email`	text	NO	Customer Email
`event_data`	jsonb	NO	Event Data (default: `'{}'`)
`status`	text	NO	Status (default: `'pending'`)
`attempts`	integer	NO	Attempts (default: `0`)
`max_attempts`	integer	NO	Max Attempts (default: `5`)
`created_at`	timestamptz	NO	Record created (default: `now()`)
`scheduled_for`	timestamptz	NO	Scheduled For (default: `now()`)
`next_retry_at`	timestamptz	YES	Next Retry At
`processed_at`	timestamptz	YES	Processed At
`customerio_delivery_id`	text	YES	Customerio Delivery ID
`last_error`	text	YES	Last Error
`error_history`	jsonb	YES	Error History (default: `'[]'`)
`source_table`	text	YES	Source Table
`source_id`	uuid	YES	Source ID
`triggered_by`	text	YES	Triggered By (default: `'system'`)

Indexes: - PRIMARY KEY on (id) - idx_lifecycle_events_customer on (customer_id, created_at) - idx_lifecycle_events_name_status on (event_name, status, created_at) - idx_lifecycle_events_pending on (scheduled_for, created_at) WHERE (status = 'pending'::text) - idx_lifecycle_events_retry on (next_retry_at) WHERE ((status = 'failed'::text) AND (attempts < max_attempts)) - idx_lifecycle_events_source on (source_table, source_id) WHERE (source_id IS NOT NULL) - lifecycle_events_idempotency_key_key UNIQUE on (idempotency_key)

1.10 Advertising & Marketing Tables¶

ad_accounts¶

Purpose: Advertising platform account connections

Column	Type	Nullable	Description
`id`	bigint	NO	Primary key (default: `nextval('raw_ops.ad_accounts_id_seq'`)
`platform`	text	NO	Platform
`account_id`	text	NO	Account ID
`account_name`	text	YES	Account Name
`is_active`	boolean	YES	Is Active (default: `true`)
`api_credentials_ref`	text	YES	API Credentials Ref
`last_sync_at`	timestamptz	YES	Last Sync At
`last_sync_status`	text	YES	Last Sync Status
`created_at`	timestamptz	YES	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id) - ad_accounts_platform_account_id_key UNIQUE on (platform, account_id)

ad_campaigns¶

Purpose: Ad campaign performance data

Column	Type	Nullable	Description
`id`	bigint	NO	Primary key (default: `nextval('raw_ops.ad_campaigns_id_seq'`)
`platform`	text	NO	Platform
`account_id`	text	NO	Account ID
`account_name`	text	YES	Account Name
`campaign_id`	text	NO	Campaign ID
`campaign_name`	text	NO	Campaign Name
`adset_id`	text	YES	Adset ID
`adset_name`	text	YES	Adset Name
`date`	date	NO	Date
`spend_local`	numeric	YES	Spend Local
`currency_code`	text	YES	Currency Code (default: `'GBP'`)
`spend_gbp`	numeric	NO	Spend GBP
`impressions`	integer	YES	Impressions (default: `0`)
`clicks`	integer	YES	Clicks (default: `0`)
`conversions`	integer	YES	Conversions (default: `0`)
`attribution_window`	text	YES	Attribution Window (default: `'7d_click_1d_view'`)
`imported_at`	timestamptz	YES	Imported At (default: `now()`)
`updated_at`	timestamptz	YES	Last modified (default: `now()`)

Indexes: - PRIMARY KEY on (id) - ad_campaigns_platform_account_id_campaign_id_date_key UNIQUE on (platform, account_id, campaign_id, date) - idx_ad_campaigns_date on (date) - idx_ad_campaigns_platform on (platform, date)

1.11 System & Ops Tables¶

webhook_inbox¶

Purpose: All incoming webhooks with processing status

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`received_at`	timestamptz	NO	Received At (default: `now()`)
`topic`	text	NO	Topic
`shopify_order_id`	bigint	YES	Shopify Order ID
`shopify_order_number`	text	YES	Shopify Order Number
`raw_headers`	jsonb	NO	Raw Headers
`raw_payload`	jsonb	NO	Raw Payload
`hmac_verified`	boolean	YES	Hmac Verified (default: `false`)
`processing_status`	text	NO	Processing Status (default: `'pending'`)
`processed_at`	timestamptz	YES	Processed At
`processing_time_ms`	integer	YES	Processing Time ms
`processing_attempts`	integer	NO	Processing Attempts (default: `0`)
`last_error`	text	YES	Last Error
`last_error_at`	timestamptz	YES	Last Error At
`error_category`	text	YES	Error Category
`created_at`	timestamptz	NO	Record created (default: `now()`)
`updated_at`	timestamptz	NO	Last modified (default: `now()`)
`processing_duration_ms`	integer	YES	Processing Duration ms

Indexes: - PRIMARY KEY on (id) - idx_inbox_pending on (received_at) WHERE (processing_status = ANY (ARRAY['pending'::text, 'failed'::text, 'retrying'::text])) - idx_inbox_shopify_order on (shopify_order_id) - idx_inbox_status on (processing_status, received_at) - idx_webhook_inbox_retry_queue on (received_at) WHERE (processing_status = ANY (ARRAY['pending'::text, 'failed'::text, 'retrying'::text])) - idx_webhook_inbox_shopify_order on (shopify_order_id) WHERE (shopify_order_id IS NOT NULL) - idx_webhook_inbox_status on (processing_status, created_at)

dead_letter_queue¶

Purpose: Failed messages for manual review

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `uuid_generate_v4()`)
`source`	text	NO	Source
`topic`	text	NO	Topic
`payload`	jsonb	NO	Payload
`error_message`	text	YES	Error Message
`retries`	integer	NO	Retries (default: `0`)
`created_at`	timestamptz	NO	Record created (default: `now()`)
`resolved`	boolean	NO	Resolved (default: `false`)
`resolved_at`	timestamptz	YES	Resolved At

Indexes: - PRIMARY KEY on (id)

ops_events¶

Purpose: Operational event log (audit trail)

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`entity_type`	text	NO	Entity Type
`entity_id`	uuid	NO	Entity ID
`kind`	text	NO	Kind
`message`	text	NO	Message
`created_at`	timestamptz	NO	Record created (default: `now()`)
`meta`	jsonb	YES	Meta
`slack_notified_at`	timestamptz	YES	Slack Notified At
`processed_at`	timestamptz	YES	Processed At
`customer_event_type`	text	YES	Customer Event Type
`event_payload`	jsonb	YES	Event Payload
`source_system`	text	YES	Source System
`source_id`	uuid	YES	Source ID
`processing_time_ms`	integer	YES	Processing Time ms

Indexes: - PRIMARY KEY on (id) - idx_ops_events_allocations on (kind, created_at) WHERE (kind = ANY (ARRAY['allocation_success'::text, 'allocation_warning'::text, 'allocation_partial'::text])) - idx_ops_events_cold_chain on (kind, created_at) WHERE (kind = ANY (ARRAY['TEMPERATURE_LOGGED'::text, 'TEMPERATURE_ALERT'::text, 'PACKAGING_ASSIGNED'::text])) - idx_ops_events_courier_unprocessed on (created_at) WHERE ((processed_at IS NULL) AND (kind = 'CUSTOMER_EVENT'::text) AND (customer_event_type = 'courier_exception'::text)) - idx_ops_events_customer_polling on (kind, processed_at, created_at) WHERE (kind = 'CUSTOMER_EVENT'::text) - idx_ops_events_kind_created on (kind, created_at) - idx_ops_events_slack_notified on (slack_notified_at) WHERE ((entity_type = 'SHIPMENT'::text) AND (kind = 'EXCEPTION'::text)) - idx_ops_events_source on (source_system, source_id)

monitoring_runs¶

Purpose: Monitor execution history

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`check_name`	text	NO	Check Name
`run_at`	timestamptz	NO	Run At (default: `now()`)
`duration_ms`	integer	YES	Duration ms
`status`	text	NO	Status (default: `'running'`)
`result_summary`	text	YES	Result Summary
`result_json`	jsonb	YES	Result JSON
`alert_triggered`	boolean	YES	Alert Triggered (default: `false`)
`error_message`	text	YES	Error Message

Indexes: - PRIMARY KEY on (id) - idx_monitoring_runs_check_run on (check_name, run_at) - idx_monitoring_runs_errors on (status, run_at) WHERE (status = 'error'::text)

alert_log¶

Purpose: Alert delivery log

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`sent_at`	timestamptz	NO	Sent At (default: `now()`)
`channel`	text	NO	Channel
`severity`	text	NO	Severity
`title`	text	NO	Title
`source_check`	text	YES	Source Check
`monitoring_run_id`	uuid	YES	FK to monitoring_runs
`payload_json`	jsonb	NO	Payload JSON
`delivery_status`	text	YES	Delivery Status (default: `'pending'`)
`delivery_response`	text	YES	Delivery Response
`retry_count`	integer	YES	Retry Count (default: `0`)
`idempotency_key`	text	YES	Idempotency Key

Indexes: - PRIMARY KEY on (id) - idx_alert_log_channel_sent on (channel, sent_at) - idx_alert_log_failed on (delivery_status, sent_at) WHERE (delivery_status = 'failed'::text) - idx_alert_log_idempotency on (idempotency_key, sent_at) WHERE (idempotency_key IS NOT NULL)

address_changes¶

Purpose: Track address updates for sync

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`created_at`	timestamptz	YES	Record created (default: `now()`)
`customer_id`	uuid	NO	FK to customers
`changed_by`	text	NO	Changed By
`agent_email`	text	YES	Agent Email
`old_address`	jsonb	NO	Old Address
`new_address`	jsonb	NO	New Address
`support_ticket_id`	uuid	YES	FK to support_tickets
`source`	text	NO	Source
`shopify_synced`	boolean	YES	Shopify Synced (default: `false`)
`shopify_synced_at`	timestamptz	YES	Shopify Synced At
`seal_synced`	boolean	YES	Seal Synced (default: `false`)
`seal_synced_at`	timestamptz	YES	Seal Synced At
`reason`	text	YES	Reason

Indexes: - PRIMARY KEY on (id) - idx_address_changes_created on (created_at) - idx_address_changes_customer on (customer_id)

config¶

Purpose: Application configuration key-value store

Column	Type	Nullable	Description
`key`	text	NO	Key
`value_numeric`	numeric	YES	Value Numeric
`value_text`	text	YES	Value Text
`description`	text	YES	Description
`updated_at`	timestamptz	YES	Last modified (default: `now()`)

Indexes: - PRIMARY KEY on (key)

system_config¶

Purpose: System-level configuration

Column	Type	Nullable	Description
`key`	text	NO	Key
`value`	text	NO	Value
`created_at`	timestamptz	YES	Record created (default: `now()`)
`updated_at`	timestamptz	YES	Last modified (default: `now()`)

Indexes: - PRIMARY KEY on (key)

retry_config¶

Purpose: Retry policy configuration

Column	Type	Nullable	Description
`error_category`	text	NO	Error Category
`max_attempts`	integer	NO	Max Attempts
`backoff_seconds`	ARRAY	NO	Backoff Seconds
`alert_on_attempt`	integer	YES	Alert On Attempt
`escalate_on_attempt`	integer	YES	Escalate On Attempt
`description`	text	YES	Description
`created_at`	timestamptz	NO	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (error_category)

cron_jobs_metadata¶

Purpose: Cron job execution metadata

Column	Type	Nullable	Description
`jobname`	text	NO	Jobname
`description`	text	YES	Description
`schedule`	text	YES	Schedule
`created_at`	timestamptz	YES	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (jobname)

exceptions_queue¶

Purpose: Operational exceptions requiring attention

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`order_id`	uuid	NO	FK to orders
`shipment_id`	uuid	YES	FK to shipments
`issue_type`	text	NO	Issue Type
`issue_description`	text	NO	Issue Description
`raw_data`	jsonb	YES	Raw Data
`resolved`	boolean	NO	Resolved (default: `false`)
`resolved_at`	timestamptz	YES	Resolved At
`resolved_by`	text	YES	Resolved By
`created_at`	timestamptz	NO	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_exceptions_queue_unresolved on (resolved, created_at) WHERE (NOT resolved)

manual_override_audit¶

Purpose: Audit trail for manual overrides

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`batch_id`	uuid	NO	FK to batches
`batch_code`	text	NO	Batch Code
`override_type`	text	NO	Override Type
`old_status`	text	YES	Old Status
`new_status`	text	YES	New Status
`requester_name`	text	NO	Requester Name
`approver_name`	text	NO	Approver Name
`justification`	text	NO	Justification
`evidence_pdf_url`	text	NO	Evidence PDF URL
`created_at`	timestamptz	YES	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_manual_override_audit_batch_id on (batch_id) - idx_manual_override_audit_created_at on (created_at)

currency_rates¶

Purpose: Currency exchange rates

Column	Type	Nullable	Description
`id`	bigint	NO	Primary key (default: `nextval('raw_ops.currency_rates_id_seq'`)
`date`	date	NO	Date
`from_currency`	text	NO	From Currency
`to_currency`	text	YES	To Currency (default: `'GBP'`)
`rate`	numeric	NO	Rate
`source`	text	YES	Source (default: `'exchangerate-api'`)
`fetched_at`	timestamptz	YES	Fetched At (default: `now()`)

Indexes: - PRIMARY KEY on (id) - currency_rates_date_from_currency_to_currency_key UNIQUE on (date, from_currency, to_currency)

partner_contacts¶

Purpose: Partner/supplier contact directory

Column	Type	Nullable	Description
`target`	text	NO	Target
`contact_name`	text	YES	Contact Name
`email`	text	NO	Email
`cc`	text	YES	Cc
`created_at`	timestamptz	NO	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (target)

invoice_parsing_jobs¶

Purpose: Invoice PDF parsing job tracking

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`status`	text	NO	Status (default: `'pending'`)
`invoice_pdf_url`	text	NO	Invoice PDF URL
`sender_email`	text	YES	Sender Email
`invoice_type`	text	YES	Invoice Type
`parsed_data`	jsonb	YES	Parsed Data
`confidence_score`	numeric	YES	Confidence Score
`last_error`	text	YES	Last Error
`created_at`	timestamptz	YES	Record created (default: `now()`)
`processed_at`	timestamptz	YES	Processed At
`processing_duration_ms`	integer	YES	Processing Duration ms

Indexes: - PRIMARY KEY on (id) - idx_invoice_jobs_created on (created_at) - idx_invoice_jobs_failed_review on (status, created_at) WHERE (status = 'failed_review'::text) - idx_invoice_jobs_performance on (processed_at, processing_duration_ms) WHERE (processing_duration_ms IS NOT NULL) - idx_invoice_jobs_status on (status) - idx_invoice_jobs_type on (invoice_type)

proof_artifacts¶

Purpose: Proof portal generated assets

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`batch_id`	uuid	NO	FK to batches
`proof_url`	text	NO	Proof URL
`qr_image_url`	text	YES	Qr Image URL
`proof_insert_pdf_url`	text	YES	Proof Insert PDF URL
`proof_json_url`	text	YES	Proof JSON URL
`checksum`	text	YES	Checksum
`generated_at`	timestamptz	NO	Generated At (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_proof_artifacts_batch on (batch_id) - proof_artifacts_batch_id_key UNIQUE on (batch_id)

proof_jobs¶

Purpose: Proof generation job tracking

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`batch_id`	uuid	NO	FK to batches
`job_key`	text	NO	Job Key
`state`	text	NO	State (default: `'queued'`)
`attempts`	integer	NO	Attempts (default: `0`)
`last_error`	text	YES	Last Error
`created_at`	timestamptz	NO	Record created (default: `now()`)
`updated_at`	timestamptz	NO	Last modified (default: `now()`)
`processing_duration_ms`	integer	YES	Processing Duration ms

Indexes: - PRIMARY KEY on (id) - idx_proof_jobs_batch_id on (batch_id, updated_at) - idx_proof_jobs_state on (state, created_at) - idx_proof_jobs_state_created on (state, created_at) WHERE (state = ANY (ARRAY['queued'::text, 'failed'::text])) - proof_jobs_job_key_key UNIQUE on (job_key) - ux_proof_jobs_jobkey UNIQUE on (job_key)

proof_page_events¶

Purpose: Proof page visitor analytics

Column	Type	Nullable	Description
`id`	uuid	NO	Primary key (default: `gen_random_uuid()`)
`event_type`	text	NO	Event Type
`batch_code`	text	NO	Batch Code
`public_batch_id`	text	YES	Public Batch ID
`referrer`	text	YES	Referrer
`user_agent`	text	YES	User Agent
`device_type`	text	YES	Device Type
`share_method`	text	YES	Share Method
`created_at`	timestamptz	NO	Record created (default: `now()`)

Indexes: - PRIMARY KEY on (id) - idx_proof_page_events_batch on (batch_code, created_at) - idx_proof_page_events_type_created on (event_type, created_at)

2. Database Views¶

2.1 Monitoring Views¶

View	Purpose	Source Table(s)
`v_webhook_health`	24-hour processing metrics	webhook_inbox
`v_retry_queue_depth`	Current retry queue status	webhook_inbox
`v_error_summary`	Error patterns by category	webhook_inbox
`v_outbox_health`	Email queue status	email_outbox
`v_lab_sla_tracking`	Lab turnaround metrics	batches, lab_results
`v_working_capital_metrics`	Cash tied in QA hold	batches, inventory
`v_daily_snapshot_health`	Snapshot function health	Multiple

2.2 Operational Views¶

View	Purpose	Source Table(s)
`v_support_queue`	Open tickets with context	support_tickets, customers, cs_agent_decisions
`v_customer_context`	Full customer view	customers, orders, subscriptions, shipments
`v_pack_queue`	Orders ready for packing	orders, packing_instructions, allocations
`v_released_batches_verified`	Batches with lab results	batches, lab_results
`v_inventory_status`	Current stock levels	inventory, batches

2.3 Alert Views¶

View	Purpose	Trigger Condition
`alert_retry_queue_backup`	Webhook queue alert	pending > 100 OR oldest > 30 min
`alert_slow_processing`	Performance alert	p95 > 5s OR max > 10s

3. Database Functions¶

3.1 Core Processing Functions¶

Function	Purpose	Called By
`process_order_webhook_txn()`	Process Shopify order	shopify-webhook-handler
`allocate_order_to_batches()`	FEFO allocation	Database trigger
`release_batch()`	Release batch from QA	SOP-01 automation
`execute_manual_inventory_adjustment()`	Manual stock adjustment	Ops Portal

3.2 Monitoring Functions (v2)¶

Function	Purpose	Schedule
`fn_check_order_ingestion_slo_v2()`	Order ingestion health	Every 2 min
`fn_check_outbox_health_v2()`	Email queue health	Every 2 min
`fn_check_stock_levels_v2()`	Low stock detection	Every 15 min
`fn_check_lab_sla_v2()`	Lab SLA tracking	Every 15 min
`fn_check_working_capital_v2()`	QA hold value	Every 6 hours
`fn_daily_snapshot_v2()`	Daily ops digest	Daily 09:00
`fn_check_support_queue_v2()`	Support queue depth	Every 15 min

3.3 Utility Functions¶

Function	Purpose
`generate_public_batch_id()`	SHA-256 hash for batch
`get_daily_snapshot()`	Daily metrics
`get_low_stock_alerts()`	Stock below threshold
`get_current_inventory_for_reconciliation()`	Inventory display
`retry_failed_webhooks()`	Webhook retry logic

4. Database Triggers¶

Trigger	Table	Event	Function	Purpose
`trg_allocate_on_order`	orders	INSERT	`allocate_order_to_batches()`	Auto-allocate new orders
`trg_allocate_pending_on_release`	batches	UPDATE (status → RELEASED)	`allocate_pending_orders()`	Allocate waiting orders
`trigger_set_public_batch_id`	batches	INSERT	`set_public_batch_id()`	Generate public batch ID
`trg_decrement_inventory_on_dispatch`	shipments	INSERT/UPDATE (status → DISPATCHED)	`fn_decrement_inventory_on_dispatch()`	Reduce stock
`trg_log_inventory_movement`	inventory	UPDATE	`log_inventory_movement()`	Audit trail

5. Edge Functions¶

5.1 Ingestion Functions¶

shopify-webhook-handler¶

Purpose: Receive and process Shopify order webhooks

Attribute	Value
Endpoint	`POST /functions/v1/shopify-webhook-handler`
Trigger	Shopify orders/create webhook
Auth	HMAC signature validation
SOP Reference	SOP-00

Processing Steps: 1. Validate Shopify HMAC signature 2. Store raw webhook in webhook_inbox (status: pending) 3. Call process_order_webhook_txn() function 4. Upsert customer data 5. Create order record 6. Create order items 7. Mark webhook as success/failed

Environment Variables: - SHOPIFY_WEBHOOK_SECRET - HMAC validation key

seal-webhook-handler¶

Purpose: Receive and process Seal subscription webhooks

Attribute	Value
Endpoint	`POST /functions/v1/seal-webhook-handler`
Trigger	Seal subscription events
Auth	HMAC signature validation
SOP Reference	SOP-SUB-00

Event Types Handled: - subscription.created - subscription.updated - subscription.cancelled - subscription.paused - subscription.resumed - billing_attempt.success - billing_attempt.failed

5.2 Processing Functions¶

create-batch¶

Purpose: Create new production batch with all assets

Attribute	Value
Endpoint	`POST /functions/v1/create-batch`
Trigger	Ops Portal
Auth	Service role
SOP Reference	SOP-01

Request Body:

{
  "produced_at": "2026-01-15",
  "kg_produced": 500
}

Processing Steps: 1. Generate sequential batch code (PR-YYMMDD-NNN) 2. Generate public batch ID (SHA-256 hash) 3. Generate QR code image 4. Generate pouch label PDF 5. Create batch record 6. Create inventory record (QA_HOLD) 7. Send email to co-packer with label PDF

cs-agent-triage¶

Purpose: AI classification and draft response

Attribute	Value
Endpoint	`POST /functions/v1/cs-agent-triage`
Trigger	Make.com (Gmail watch)
Auth	X-Internal-Secret header
SOP Reference	SOP-CS-01, SOP-CS-03

Request Body:

{
  "email_body": "...",
  "email_subject": "...",
  "email_from": "...",
  "message_id": "...",
  "dedupe_key": "..."
}

Processing Steps: 1. Check dedupe_key for duplicate 2. Run preflight policy gates (health, refund, legal) 3. Load customer context 4. Search knowledge base (pgvector) 5. Call Claude API for classification 6. Generate draft response 7. Store in cs_agent_decisions 8. Return classification result

Config:

{
  model: 'claude-sonnet-4-20250514',
  maxIterations: 10,
  maxTokens: 4096,
  timeoutMs: 30000,
  shadowMode: true,
  confidenceThreshold: 0.70
}

send-support-email¶

Purpose: Send support response with threading

Attribute	Value
Endpoint	`POST /functions/v1/send-support-email`
Trigger	Ops Portal, CS-03 automation
Auth	Service role
SOP Reference	SOP-CS-00

Processing Steps: 1. Fetch ticket email_message_id 2. Build In-Reply-To and References headers 3. Send via Customer.io Transactional API 4. Log to ticket_messages 5. Update ticket status

sync-address-change¶

Purpose: Sync address to Shopify and Seal

Attribute	Value
Endpoint	`POST /functions/v1/sync-address-change`
Trigger	Database trigger on customers
Auth	Service role
SOP Reference	SOP-CS-00

5.3 Monitoring Functions¶

run-monitor¶

Purpose: Generic monitor runner

Attribute	Value
Endpoint	`POST /functions/v1/run-monitor`
Trigger	pg_cron via pg_net
Auth	Anon key (internal)
SOP Reference	SOP-MON-01

Request Body:

{"check": "order_ingestion_slo"}

Available Checks: - order_ingestion_slo - outbox_health - stock_levels - lab_sla - daily_snapshot - working_capital - support_queue

ops-alerter¶

Purpose: Send formatted Slack alerts

Attribute	Value
Endpoint	`POST /functions/v1/ops-alerter`
Trigger	Monitoring functions
Auth	None (internal)
SOP Reference	SOP-MON-01

Request Body:

{
  "channel": "ops-alerts",
  "severity": "warning",
  "title": "Alert Title",
  "message": "Description",
  "fields": [{"label": "Field", "value": "Value", "inline": true}]
}

Channels: - ops-alerts → #ops-alerts (warning) - ops-urgent → #ops-urgent (critical) - daily-ops → #daily-ops (info)

5.4 Operational Functions¶

calculate-pack-day-instructions¶

Purpose: Calculate PCM requirements

Attribute	Value
Endpoint	`POST /functions/v1/calculate-pack-day-instructions`
Trigger	Ops Portal
Auth	Service role
SOP Reference	SOP-PACK-01

retry-failed-webhooks¶

Purpose: Retry failed webhook processing

Attribute	Value
Endpoint	`POST /functions/v1/retry-failed-webhooks`
Trigger	pg_cron (every 5 min)
Auth	Service role
SOP Reference	SOP-00

6. pg_cron Jobs¶

Job Name	Schedule	Purpose	SOP
`monitor-order-ingestion-slo`	`/2 * * *`	Order ingestion health	SOP-MON-02
`monitor-outbox-health`	`/2 * * *`	Email queue health	SOP-MON-01
`monitor-stock-levels`	`/15 * * *`	Low stock alerts	SOP-MON-01
`monitor-lab-sla`	`/15 * * *`	Lab SLA tracking	SOP-MON-01
`monitor-working-capital`	`0 /6 * *`	QA hold value	SOP-MON-01
`monitor-daily-snapshot`	`0 9 * * *`	Daily digest	SOP-03
`monitor-support-queue`	`/15 * * *`	Support queue depth	SOP-CS-03
`retry-failed-webhooks`	`/5 * * *`	Webhook retry	SOP-00
`process-email-outbox`	`* * * * *`	Send queued emails	SOP-05
`order-export`	`0 6 * * *`	Export orders	SOP-0X

7. Make.com Scenarios¶

Scenario	Trigger	Purpose	SOP
CS-01: Email Triage	Gmail inbox watch	Process inbound support emails	SOP-CS-01
Address Update Sync	Supabase webhook	Sync address to Shopify + Seal	SOP-CS-00
Lab Results Processing	Gmail (lab emails)	Parse PDF → OpenAI → update batch	SOP-01
DPD Tracking Ingestion	DPD webhook	Store tracking events	SOP-02
Courier Exception Handler	courier_events trigger	Customer notification	SOP-02
Event Bridge	Database trigger	Deliver events to Customer.io	SOP-LC-01

Make.com Webhook URLs¶

Scenario	URL
Address Update Sync	`https://hook.eu2.make.com/yxpdv3l9gvy433tem2tmxds2ssek53t2`
CS-01 Email Triage	`https://hook.eu2.make.com/[REDACTED]`
DPD Tracking	`https://hook.eu2.make.com/[REDACTED]`

8. Slack Channels¶

Channel	Purpose	Severity	Sources
`#ops-alerts`	Standard operational alerts	Warning	All monitors
`#ops-urgent`	Critical issues	Critical	Stock, working capital, escalations
`#daily-ops`	Daily digest	Info	Daily snapshot

9. Customer.io Templates¶

9.1 Transactional Templates¶

Template ID	Trigger	Purpose
`order_confirmation_first`	First order created	Welcome + order receipt
`order_confirmation_repeat`	Repeat order created	Order receipt
`order_dispatched`	Order dispatched	Tracking info
`order_delivered`	Order delivered	Delivery confirmation + proof link
`subscription_paused`	Subscription paused	Pause confirmation
`subscription_resumed`	Subscription resumed	Resume confirmation
`subscription_cancelled`	Subscription cancelled	Cancellation confirmation
`delivery_skipped`	Delivery skipped	Skip confirmation
`delivery_rescheduled`	Delivery rescheduled	New date confirmation

9.2 Lifecycle Templates¶

Template ID	Timing	Purpose
`transition_guide`	+24 hours	How to introduce raw
`transition_week_1`	+7 days	Week 1 check-in
`transition_week_2`	+14 days	Week 2 check-in
`delivery_reminder_7day`	-7 days	Upcoming delivery
`delivery_lock_48h`	-48 hours	Last chance to modify
`winback_30day`	+30 days after cancel	Win-back offer
`winback_60day`	+60 days after cancel	Stronger offer

9.3 Operational Templates¶

Template ID	Trigger	Purpose
`delivery_exception`	Courier exception	Proactive delay notice
`payment_failed_day0`	Payment fails	Update card prompt
`payment_failed_day3`	+3 days	Reminder
`payment_failed_day7`	+7 days	Urgent reminder
`card_expiring_30day`	-30 days	Card expiry warning
`card_expiring_7day`	-7 days	Urgent card update

Document	Purpose
Layer 1: System Map	Visual architecture and data flows
Layer 3: Integration Specifications	API specs for external systems
Layer 4: SOP Cross-Reference	Master SOP index

Protocol Raw — Verified safe, batch by batch.