Sourcing Architecture

Switchyard uses a multi-channel sourcing model that combines warehouse inventory, retail sweeps, and partner brand consignment to fulfill customer orders.

Sourcing Channels

Channel	Description	Lead Time	Typical Use
RFC Inventory	Pre-stocked items in our fulfillment center	Minutes	High-velocity items, perishables
Retail Sweeps	Shopping trips to partner retailers	1-4 hours	Long-tail catalog, price arbitrage
Partner Consignment	Inventory owned by partner brands	Pre-stocked	Emerging brands, specialty products

RFC Inventory

Overview

The RFC (Rapid Fulfillment Center) is our warehouse where we pre-stock high-velocity and perishable items for immediate fulfillment.

Inventory Structure

inventory_groups (hierarchical)
├── Zone (A=Ambient, C=Chilled, F=Frozen)
│   ├── Aisle
│   │   ├── Bay
│   │   │   ├── Shelf
│   │   │   │   └── Slot → inventory_locations → inventory_items

Picking Strategy

FEFO/FIFO (First Expiring First Out / First In First Out):

Items with earliest expiration date picked first
For non-perishables, oldest received date picked first
Reserved quantities tracked to prevent over-allocation

Key Tables

Table	Purpose
`inventory_items`	Physical stock with quantities, expiration, lot numbers
`inventory_locations`	Where items are stored
`inventory_groups`	Hierarchical warehouse organization
`pick_lists`	Picking assignments for staff
`pick_list_items`	Individual items to pick

Retail Sweeps

Overview

Sweeps are shopping trips to partner retailers (HEB, Target, Walmart, Central Market, Whole Foods, Costco, Trader Joe’s) to source items not stocked in the RFC.

Sweep Economics

The system optimizes sweeps based on profitability:

Setting	Description
`in_store_breakeven_items`	Minimum items for profitable in-store sweep
`curbside_breakeven_items`	Minimum items for profitable curbside sweep
`labor_rate_hourly`	Cost of driver time
`new_sweep_marginal_cost`	Cost to add another sweep

Sweep Optimization

The Sweep Optimizer consolidates under-threshold sweeps:

Identifies sweeps below breakeven threshold
Finds alternative sweeps at same location with capacity
Moves items to maximize efficiency
Cancels empty sweeps

Sweep Flow

Key Tables

Table	Purpose
`routes`	Groups multiple sweeps for a driver trip
`sweeps`	Individual shopping trip to a retailer
`sweep_items`	Items on the sweep manifest
`sweep_order_allocations`	Links sweep items to customer orders
`sweep_economics_settings`	Profitability thresholds
`retailer_mappings`	Aisle locations at each retailer
`retailer_pricing`	Our acquisition cost at each retailer

Partner Brand Consignment

Overview

Partner brands ship their own products to our RFC for consignment sale. We handle fulfillment and take a commission on sales.

Partner Lifecycle

Commission Model

Temperature Zone	Default Commission
Ambient	15%
Chilled/Frozen	20%

Commission can be overridden at the product level via sellable_products.commission_rate.

Manifest Flow

Partner manifests use TYPE 27 taxonomy IDs for QR code scanning:

Admin creates reorder request with items and quantities
Partner has 7 days to accept/reject
On acceptance, manifest auto-created with QR code
Partner ships to RFC with tracking info
Staff scans manifest QR at receiving
Items scanned into consignment inventory

Consignment Sales Tracking

When consignment inventory is sold:

record_consignment_sale() function triggered
Captures commission rate at time of sale
Records commission_amount and net_to_partner
Assigns to payout_period (YYYY-MM format)
Partner views via portal, admin settles monthly

Shopify Integration

Partners can sync products from their Shopify store:

Feature	Description
Import	Initial product import from Shopify catalog
Field Sync	Configurable per-field sync (name, price, images, etc.)
Daily Cron	Automatic sync at 6am CST
Conflict Handling	Local edits pause sync for that field
Audit Logs	All sync changes logged

Key Tables

Table	Purpose
`partner_brands`	Partner company profiles and commission rates
`partner_manifests`	Shipments from partners
`partner_manifest_items`	Line items with expected/received quantities
`partner_reorder_requests`	Admin-initiated inventory requests
`partner_reorder_request_items`	Request line items
`consignment_sales`	Sales records for payout calculation
`shopify_sync`	Per-product sync configuration
`shopify_sync_logs`	Sync execution audit trail

Partner-Specific Fields

On scraped_products:

partner_brand_id - Links product to partner

On sellable_products:

is_partner_brand - Flag for partner products
commission_rate - Product-level commission override

On inventory_items:

partner_brand_id - Tracks consignment ownership
is_consignment - Flags consignment inventory
manifest_item_id - Links to receiving manifest

Allocation Engine

Decision Flow

When an order arrives, the allocation engine analyzes each line item:

Sourcing Priority

RFC Inventory - Check available stock (FEFO/FIFO)
Best Retailer - Compare prices across retailers
Bulk vs Individual - Consider bulk SKU breakdowns
Route Optimization - Prefer existing routes when possible

Key Functions

Function	Location	Purpose
`allocateOrder()`	`order-allocation.ts`	Main allocation logic
`runSweepOptimization()`	`sweep-optimizer.ts`	Post-allocation optimization
`findBestSweepForProduct()`	Meilisearch	Sweep selection by price/availability

Database Views

Prebuilt views for common queries:

View	Purpose
`partner_inventory_summary`	Active consignment inventory by partner/product
`partner_sales_by_period`	Sales aggregation with payout tracking
`partner_pending_payouts`	Current amounts due to partners
`partner_manifests_summary`	Manifest status with receive percentages
`partner_reorder_requests_summary`	Request status with expiry countdown
`partner_products_view`	Products with partner brand context

Background Jobs

Scraper Scheduling

Retailer scrapers run on pg_cron schedules:

Updates retailer_pricing with current costs
Updates retailer_mappings with availability
Wakes scraper workers via pg_net HTTP calls

Shopify Sync

Daily sync at 6am CST via pg_cron:

Triggers trigger_shopify_sync() function
Calls internal API endpoint
Updates products based on sync configuration

Reorder Request Expiry

Requests expire after 7 days:

expire_old_reorder_requests() function
Marks status as expired
Partner notified of expiry

API Endpoints

Admin APIs

Endpoint	Purpose
`POST /admin/orders`	Create orders
`POST /admin/sweeps/optimize`	Run sweep optimization
`GET /admin/partners/brands`	List partner brands
`POST /admin/partners/brands/{id}/approve`	Approve partner
`GET /admin/partners/manifests`	List manifests
`POST /admin/partners/reorders`	Create reorder request

Partner Portal APIs

Endpoint	Purpose
`GET /partner/products`	View submitted products
`GET /partner/manifests`	View shipments
`POST /partner/reorder-requests/{id}/accept`	Accept reorder
`GET /partner/sales`	View sales and payouts
`POST /partner/shopify-import`	Import from Shopify

Scanner APIs

Endpoint	Purpose
`GET /scanner/receiving/manifests`	List pending manifests
`POST /scanner/receiving/manifest/{id}/items/{itemId}/receive`	Record received item
`GET /scanner/sweeps/{id}/items`	Get sweep shopping list

Integration Points

System	Integration
Goods Mobile App	Order placement via API
Admin Dashboard	Order management, partner admin
Partner Portal	Self-service partner management
Zebra Scanners	Picking, receiving, sweep operations
Shopify	Product import and sync
Meilisearch	Product search, sweep selection
pg_cron	Scheduled scraping and syncing

​Sourcing Architecture

​Sourcing Channels

​RFC Inventory

​Overview

​Inventory Structure

​Picking Strategy

​Key Tables

​Retail Sweeps

​Overview

​Sweep Economics

​Sweep Optimization

​Sweep Flow

​Key Tables

​Partner Brand Consignment

​Overview

​Partner Lifecycle

​Commission Model

​Manifest Flow

​Consignment Sales Tracking

​Shopify Integration

​Key Tables

​Partner-Specific Fields

​Allocation Engine

​Decision Flow

​Sourcing Priority

​Key Functions

​Database Views

​Background Jobs

​Scraper Scheduling

​Shopify Sync

​Reorder Request Expiry

​API Endpoints

​Admin APIs

​Partner Portal APIs

​Scanner APIs

​Integration Points

Sourcing Architecture

Sourcing Channels

RFC Inventory

Overview

Inventory Structure

Picking Strategy

Key Tables

Retail Sweeps

Overview

Sweep Economics

Sweep Optimization

Sweep Flow

Key Tables

Partner Brand Consignment

Overview

Partner Lifecycle

Commission Model

Manifest Flow

Consignment Sales Tracking

Shopify Integration

Key Tables

Partner-Specific Fields

Allocation Engine

Decision Flow

Sourcing Priority

Key Functions

Database Views

Background Jobs

Scraper Scheduling

Shopify Sync

Reorder Request Expiry

API Endpoints

Admin APIs

Partner Portal APIs

Scanner APIs

Integration Points