> ## 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
> Key entities and their relationships in Switchyard
# 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:
1. **Scraped Products Layer** - Raw product data from retailer scrapers (unique by UPC)
2. **Sellable Products Layer** - Curated catalog of products we sell (1:1 with scraped products)
3. **Inventory Layer** - Physical stock in the RFC warehouse
4. **Order & Fulfillment Layer** - Customer orders, bags, totes, and robot delivery
## Complete Schema Architecture
```mermaid theme={null}
erDiagram
%% === PRODUCT CATALOG ===
scraped_products ||--o{ retailer_mappings : "available at"
scraped_products ||--o{ retailer_pricing : "priced at"
scraped_products ||--o| sellable_products : "curated as 1-to-1"
scraped_products }o--o| partner_brands : "owned by"
sellable_products ||--o{ inventory_items : "stocked as"
sellable_products }o--o{ variant_group_members : "grouped in"
sellable_products }|--|| categories : "categorized in"
variant_groups ||--|{ variant_group_members : contains
inventory_items }|--|| inventory_locations : "stored at"
inventory_items }o--o| partner_brands : "consigned by"
inventory_locations }|--|| inventory_groups : "part of"
%% === ORDERS & FULFILLMENT ===
orders ||--|{ order_items : contains
orders ||--|{ totes : "assembled into"
order_items }|--|| sellable_products : references
totes ||--|{ bags : contains
totes ||--o| robots : "delivered by 1-to-1"
bags ||--|{ bag_items : contains
bag_items }|--|| order_items : fulfills
bags }|--|| inventory_locations : "staged at"
%% === SWEEPS & PICKING ===
routes ||--|{ sweeps : contains
sweeps ||--|{ sweep_items : contains
sweep_items }|--|| sellable_products : references
sweep_items ||--o{ sweep_order_allocations : "allocated to orders"
pick_lists ||--|{ pick_list_items : contains
pick_list_items }|--|| sellable_products : references
pick_list_items }|--|| inventory_items : "picked from"
pick_list_items }|--|| bag_items : "placed into"
%% === PARTNER BRANDS ===
partner_brands ||--o{ partner_manifests : "receives shipments"
partner_brands ||--o{ partner_reorder_requests : "receives requests"
partner_brands ||--o{ consignment_sales : "earns from"
partner_manifests ||--|{ partner_manifest_items : contains
partner_reorder_requests ||--|{ partner_reorder_request_items : contains
%% === STAFF ===
users ||--o| staff : "linked to"
staff ||--o{ sweeps : "drives"
staff ||--o{ pick_lists : "picks"
```
## 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 |
```mermaid theme={null}
flowchart LR
subgraph Scrapers
RP[retailer_pricing
Our acquisition cost]
RSP[retailer_selling_prices
What retailers charge customers]
end
subgraph Admin
SP[sellable_products.selling_price
Our customer price]
end
subgraph Margin
CALC[Margin = Our Price - Our Cost]
end
RP --> CALC
SP --> CALC
```
## 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.