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
Employees who can be pickers, drivers, or both. Replaces the old drivers table.
| Field | Type | Description |
|---|
| id | uuid | Unique identifier |
| user_id | string | Link to admin user account |
| email | string | Staff email |
| is_picker | boolean | Can pick orders |
| is_driver | boolean | Can perform sweeps |
| is_active | boolean | Currently active |
Staff members can have both picker and driver roles, but not perform both simultaneously.
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.
