Data Model

This document describes the key entities in Switchyard and how they relate to each other.

Schema Overview

The Switchyard database contains tables organized into focused domains:

Domain	Tables	Purpose
Product Catalog	10	Scraped products, sellable catalog, variants
Inventory	4	Physical stock, locations, warehouse groups
Orders & Fulfillment	9	Orders, totes, bags, pick lists
Sweeps & Routes	6	Retail shopping trips, driver assignments
Partner Brands	8	Consignment partners, manifests, payouts
Staff & Auth	9	Users, roles, permissions, service accounts
Equipment Monitoring	5	Sensors, alerts, temperature readings

Layered Product Architecture

Switchyard uses a layered product architecture that separates scraped data from the curated sellable catalog:

Scraped Products Layer - Raw product data from retailer scrapers (unique by UPC)
Sellable Products Layer - Curated catalog of products we sell (1:1 with scraped products)
Inventory Layer - Physical stock in the RFC warehouse
Order & Fulfillment Layer - Customer orders, bags, totes, and robot delivery

Complete Schema Architecture

Product Domain

scraped_products

The raw product catalog from scrapers. Unique by UPC/barcode.

Field	Type	Description
id	uuid	Unique identifier
name	string	Product name
barcode	string	UPC/EAN barcode (canonical identifier)
brand	string	Brand name
image_url	string	Primary product image
category_id	uuid	Reference to Category
subcategory_id	uuid	Reference to subcategory
partner_brand_id	uuid	Partner brand owner (nullable)
description	text	Product description
created_at	datetime	First scraped
updated_at	datetime	Last updated

sellable_products

The curated catalog of products we sell. 1:1 relationship with scraped_products.

Field	Type	Description
id	uuid	Unique identifier
scraped_product_id	uuid	Reference to scraped_products (UNIQUE)
name	string	Curated product name
brand	string	Brand
selling_price	decimal	Our price to customers
is_perishable	boolean	Requires expiration tracking
temperature_zone	string	Zone (ambient, chilled, frozen)
is_partner_brand	boolean	Owned by partner brand
commission_rate	decimal	Partner commission override
status	enum	draft, active, discontinued
is_active	boolean	Currently available for sale

retailer_mappings

Links scraped products to specific retailers and stores. Contains aisle/location data for sweeps.

Field	Type	Description
id	uuid	Unique identifier
product_id	uuid	Reference to scraped_products
store_name	string	Retailer (heb, walmart, target)
retailer_location_id	string	Specific store ID
store_location_text	string	Aisle location (e.g., “Aisle 27”)
store_aisle	integer	Parsed aisle number
is_active	boolean	Currently available
last_seen_at	datetime	Last successful scrape

Pricing Model

Switchyard tracks three types of prices:

Table	Source	Purpose
`retailer_pricing`	Scrapers	Our cost - what we pay retailers
`retailer_selling_prices`	Scrapers	Retailer’s price - what retailers charge customers
`sellable_products.selling_price`	Admin	Our price - what we charge customers

Inventory Domain

inventory_items

Tracks physical inventory in the RFC warehouse. Supports FEFO/FIFO picking.

Field	Type	Description
id	uuid	Unique identifier
sellable_product_id	uuid	Reference to sellable_products
location_id	uuid	Reference to inventory_locations
quantity	integer	Total quantity
reserved_quantity	integer	Reserved for orders
received_at	datetime	When received (for FIFO)
expiration_date	date	Expiration (for FEFO, nullable)
lot_number	string	Lot tracking
partner_brand_id	uuid	Consignment owner (nullable)
is_consignment	boolean	Partner-owned inventory
manifest_item_id	uuid	Receiving manifest reference
source_sweep_id	uuid	Which sweep brought this in
unit_cost	decimal	Acquisition cost

FEFO/FIFO Picking: Items are picked with expiring soonest first (FEFO), with oldest received as fallback for non-perishables (FIFO).

inventory_locations

Physical location within the RFC warehouse.

inventory_groups

Hierarchical warehouse organization: Zone → Aisle → Bay → Shelf → Slot

Partner Brand Domain

partner_brands

External brand partners who consign inventory.

Field	Type	Description
id	uuid	Unique identifier
company_name	string	Partner company name
contact_email	string	Primary contact
approval_status	enum	pending, approved, rejected, suspended
default_commission_rate_ambient	decimal	Default commission for ambient (0.15)
default_commission_rate_cold	decimal	Default commission for cold (0.20)
retailer_id	uuid	Created retailer for sourcing
retailer_location_id	uuid	Created location for sourcing
approved_at	datetime	When approved
approved_by	uuid	Admin who approved

partner_manifests

Shipments from partner brands to RFC.

Field	Type	Description
id	uuid	Unique identifier
partner_brand_id	uuid	Reference to partner_brands
taxonomy_id	string	18-digit QR code ID (TYPE 27)
manifest_number	integer	Sequential per brand
status	enum	draft, pending_brand_approval, approved, in_transit, received, partially_received, cancelled
tracking_number	string	Shipping tracking
carrier	string	Shipping carrier
expected_delivery_date	date	Expected arrival

partner_manifest_items

Line items on a manifest with expected and received quantities.

Field	Type	Description
manifest_id	uuid	Reference to partner_manifests
sellable_product_id	uuid	Product being shipped
expected_quantity	integer	Quantity expected
received_quantity	integer	Quantity received
lot_number	string	Lot tracking
expiration_date	date	Product expiration
discrepancy_notes	text	Notes on quantity differences

partner_reorder_requests

Admin-initiated requests for partner inventory.

Field	Type	Description
id	uuid	Unique identifier
partner_brand_id	uuid	Reference to partner_brands
status	enum	pending, accepted, rejected, expired, cancelled
expires_at	datetime	7 days from creation
manifest_id	uuid	Created manifest on acceptance
is_auto_generated	boolean	From auto-reorder system

consignment_sales

Records sales of consigned inventory for payout calculation.

Field	Type	Description
id	uuid	Unique identifier
inventory_item_id	uuid	Sold inventory item
order_item_id	uuid	Customer order item
partner_brand_id	uuid	Partner to pay
quantity_sold	integer	Units sold
sale_price	decimal	Price per unit
commission_rate	decimal	Rate at time of sale
commission_amount	decimal	Our commission
net_to_partner	decimal	Amount due to partner
payout_status	enum	pending, included, paid
payout_period	string	Settlement period (YYYY-MM)

shopify_sync

Per-product Shopify sync configuration.

Field	Type	Description
sellable_product_id	uuid	Product to sync
partner_brand_id	uuid	Partner brand
shopify_product_id	string	Shopify product ID
sync_name	boolean	Sync product name
sync_description	boolean	Sync description
sync_images	boolean	Sync images
sync_retail_price	boolean	Sync price
last_sync_at	datetime	Last sync time
last_sync_status	enum	success, failed
locally_modified_fields	array	Fields with local edits

Staff Domain

staff

Warehouse staff with role-based access control.

Field	Type	Description
id	uuid	Unique identifier
user_id	string	Link to auth user account
email	string	Staff email
role	string	Access role (superadmin, admin, manager, marketing, warehouse)
role_id	uuid	Reference to roles table
operational_units	string[]	Deputy operational areas
is_active	boolean	Currently active

Roles determine what features staff can access:

superadmin: Full system access with all permissions
admin/manager: Full dashboard and scanner access
marketing: Partners, products (view), orders/customers (view)
warehouse: Scanner app only

Order & Fulfillment Domain

orders

Customer orders from app or admin dashboard.

Field	Type	Description
id	uuid	Unique identifier
customer_id	uuid	Reference to customers
source	enum	’app’ or ‘admin’
status	enum	pending, processing, sweep_in_progress, intake, picking, staged, delivering, delivered

order_items

Line items referencing sellable_products.

Field	Type	Description
sellable_product_id	uuid	Reference to sellable_products
fulfillment_source	enum	’inventory’ or ‘sweep’
allocated_at	datetime	When allocation was made

totes, bags, bag_items

Physical containers for robot delivery:

Order → has many Totes
Tote → has many Bags (one robot per tote)
Bag → has many Bag Items (temperature-separated)
Bag Item → fulfills an Order Item

Operations Domain

routes

Groups multiple sweeps together for a single driver trip.

sweeps

Shopping trips to retailers. Supports order sweeps and inventory sweeps.

Field	Type	Description
sweep_type	enum	’order’ or ‘inventory’
driver_id	uuid	Reference to staff
route_id	uuid	Reference to routes

sweep_economics_settings

Configuration for sweep profitability calculations.

Field	Type	Description
in_store_breakeven_items	integer	Min items for profitable in-store sweep
curbside_breakeven_items	integer	Min items for profitable curbside sweep
labor_rate_hourly	decimal	Driver hourly cost
new_sweep_marginal_cost	decimal	Cost to add another sweep

pick_lists

RFC picking assignments assigned to staff (pickers).

Write Separation

To protect product data integrity, scrapers and admin have different write permissions:

Table	Scrapers	Admin
`scraped_products`	Create new only	Full control
`retailer_mappings`	Full control	Read only
`retailer_pricing`	Full control	Read only
`sellable_products`	Never	Full control
`inventory_items`	Never	Full control

Once a product exists, scrapers only update retailer-specific tables (pricing, availability), never core product attributes.

Getting Started

Architecture

Authentication

Operations

Equipment

Data Model

Data Model

Schema Overview

Layered Product Architecture

Complete Schema Architecture

Product Domain

scraped_products

sellable_products

retailer_mappings

Pricing Model

Inventory Domain

inventory_items

inventory_locations

inventory_groups

Partner Brand Domain

partner_brands

partner_manifests

partner_manifest_items

partner_reorder_requests

consignment_sales

shopify_sync

Staff Domain

staff

Order & Fulfillment Domain

orders

order_items

totes, bags, bag_items

Operations Domain

routes

sweeps

sweep_economics_settings

pick_lists

Write Separation

​Data Model

​Schema Overview

​Layered Product Architecture

​Complete Schema Architecture

​Product Domain

​scraped_products

​sellable_products

​retailer_mappings

​Pricing Model

​Inventory Domain

​inventory_items

​inventory_locations

​inventory_groups

​Partner Brand Domain

​partner_brands

​partner_manifests

​partner_manifest_items

​partner_reorder_requests

​consignment_sales

​shopify_sync

​Staff Domain

​staff

​Order & Fulfillment Domain

​orders

​order_items

​totes, bags, bag_items

​Operations Domain

​routes

​sweeps

​sweep_economics_settings

​pick_lists

​Write Separation

Data Model

Schema Overview

Layered Product Architecture

Complete Schema Architecture

Product Domain

scraped_products

sellable_products

retailer_mappings

Pricing Model

Inventory Domain

inventory_items

inventory_locations

inventory_groups

Partner Brand Domain

partner_brands

partner_manifests

partner_manifest_items

partner_reorder_requests

consignment_sales

shopify_sync

Staff Domain

staff

Order & Fulfillment Domain

orders

order_items

totes, bags, bag_items

Operations Domain

routes

sweeps

sweep_economics_settings

pick_lists

Write Separation