Documentation Index
Fetch the complete documentation index at: https://docs.switchyard.run/llms.txt
Use this file to discover all available pages before exploring further.
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.
