Last updated: 2026-01-18
Swyft uses Supabase (PostgreSQL) with Row Level Security (RLS) enabled on all tables. The schema supports multi-tenant SaaS with Shopify app integration and WMS connectivity via Trackstar.
Total Tables: 19 (v2 schema)
Database Functions: 2
| Category | Tables | Purpose |
|---|---|---|
| Core | 4 | Store identity, sessions, channels |
| WMS Integration | 3 | Trackstar connections, warehouses, inventory |
| Orders | 1 | Unified order storage |
| Routing | 2 | Order routing decisions and rules |
| Monitoring | 3 | Webhooks, sync status, backfill jobs |
| Reports | 2 | Fulfillment analysis jobs and results |
| Forecasting | 2 | Demand forecasting jobs and results |
| Products (P1) | 1 | Unified product catalog (scaffolding) |
| Transfers (P1) | 1 | Inventory transfers (scaffolding) |
| Config | 1 | Feature flags |
storesPurpose: Root tenant table. Every merchant who installs the Shopify app gets a store record.
| Column | Type | Description |
|---|---|---|
id |
uuid | Primary key |
shop_domain |
text | Shopify domain (e.g., mystore.myshopify.com) |
shop_name |
text | Display name |
email |
text | Store owner email |
plan |
text | Subscription plan (free, pro, enterprise) |
is_active |
boolean | Whether store is active |
settings |
jsonb | Store-specific configuration |
created_at |
timestamptz | Record creation |
updated_at |
timestamptz | Last modification |
Relationships:
sales_channels, trackstar_connections, unified_orders, routing_decisions, forecast_jobs, fulfillment_report_jobs, order_backfill_jobs, unified_products, inventory_transfersUsed by: All services - this is the root entity
shopify_sessionsPurpose: Stores Shopify OAuth session tokens for the embedded app. Required by @shopify/shopify-app-remix.
| Column | Type | Description |
|---|---|---|
id |
text | Session ID (primary key) |
shop |
text | Shop domain |
state |
text | OAuth state parameter |
isOnline |
boolean | Online vs offline token |
scope |
text | Granted OAuth scopes |
expires |
timestamptz | Token expiration |
accessToken |
text | Shopify access token |
userId |
bigint | Shopify user ID (online tokens) |
firstName |
text | User first name |
lastName |
text | User last name |
email |
text | User email |
accountOwner |
boolean | Is account owner |
locale |
text | User locale |
collaborator |
boolean | Is collaborator |
emailVerified |
boolean | Email verification status |
Used by: apps/shopify-app - Shopify authentication
sales_channelsPurpose: Tracks order sources for a store. Each store has at least one channel (Shopify). Future: Amazon, WooCommerce, manual entry.
| Column | Type | Description |
|---|---|---|
id |
uuid | Primary key |
shop_id |
uuid | FK to stores |
channel_type |
enum | shopify, amazon, woocommerce, manual |
channel_name |
text | Display name |
trackstar_connection_id |
uuid | FK to cart connection (if via Trackstar) |
is_primary |
boolean | Primary channel flag |
sync_orders |
boolean | Sync orders from this channel |
sync_products |
boolean | Sync products from this channel |
sync_inventory |
boolean | Sync inventory to this channel |
is_active |
boolean | Channel is active |
last_order_sync_at |
timestamptz | Last order sync timestamp |
created_at |
timestamptz | Record creation |
updated_at |
timestamptz | Last modification |
Relationships:
shop_id → stores.idtrackstar_connection_id → trackstar_connections.idunified_orders, order_backfill_jobsUsed by: Order ingestion, multi-channel support
feature_flagsPurpose: Simple feature flag system for gradual rollouts and A/B testing.
| Column | Type | Description |
|---|---|---|
key |
text | Flag identifier (primary key) |
enabled |
boolean | Global enable/disable |
percentage |
integer | Percentage rollout (0-100) |
shop_ids |
uuid[] | Specific stores to enable for |
description |
text | Human-readable description |
created_at |
timestamptz | Record creation |
updated_at |
timestamptz | Last modification |
Used by: Feature rollout, beta testing
trackstar_connectionsPurpose: Stores WMS connections made via Trackstar Link. Each connection represents an authenticated WMS account (ShipBob, ShipHero, etc.).
| Column | Type | Description |
|---|---|---|
id |
uuid | Primary key |
shop_id |
uuid | FK to stores |
connection_id |
text | Trackstar's connection identifier |
integration_type |
enum | wms or cart |
integration_name |
text | WMS name (e.g., shipbob, shiphero) |
access_token |
text | Trackstar access token (encrypted) |
display_name |
text | User-friendly connection name |
metadata |
jsonb | Additional connection metadata |
is_active |
boolean | Connection is active |
connected_at |
timestamptz | When connection was established |
last_sync_at |
timestamptz | Last successful sync |
last_error |
text | Most recent error message |
error_count |
integer | Consecutive error count |
created_at |
timestamptz | Record creation |
updated_at |
timestamptz | Last modification |
Relationships:
shop_id → stores.idtrackstar_warehouses, sales_channels, connection_sync_statusUsed by: WMS connection management, scheduled sync
trackstar_warehousesPurpose: Warehouses synced from WMS via Trackstar. Each WMS connection can have multiple warehouses.
| Column | Type | Description |
|---|---|---|
id |
uuid | Primary key |
trackstar_connection_id |
uuid | FK to trackstar_connections |
trackstar_warehouse_id |
text | Trackstar's warehouse ID |
name |
text | Warehouse name |
code |
text | Warehouse code/identifier |
address1 |
text | Street address line 1 |
address2 |
text | Street address line 2 |
city |
text | City |
state |
text | State/province |
postal_code |
text | ZIP/postal code |
country |
text | Country code (ISO 3166-1) |
latitude |
numeric | Geocoded latitude |
longitude |
numeric | Geocoded longitude |
capabilities |
text[] | Warehouse capabilities array |
priority |
integer | Routing priority (lower = higher priority) |
is_active |
boolean | Warehouse is active |
last_synced_at |
timestamptz | Last sync from WMS |
created_at |
timestamptz | Record creation |
updated_at |
timestamptz | Last modification |
Relationships:
trackstar_connection_id → trackstar_connections.idtrackstar_inventory, routing_decisions, unified_ordersUsed by: Warehouse sync, order routing, fulfillment reports
trackstar_inventoryPurpose: SKU-level inventory per warehouse, synced from WMS via Trackstar webhooks and scheduled pulls.
| Column | Type | Description |
|---|---|---|
id |
uuid | Primary key |
warehouse_id |
uuid | FK to trackstar_warehouses |
sku |
text | Product SKU |
trackstar_product_id |
text | Trackstar's product ID |
available |
integer | Available to sell |
committed |
integer | Reserved for orders |
on_hand |
integer | Physical quantity |
incoming |
integer | Expected incoming inventory |
last_synced_at |
timestamptz | Last sync from WMS |
Relationships:
warehouse_id → trackstar_warehouses.idIndexes:
(warehouse_id, sku) - unique constraintUsed by: Inventory sync, order routing (availability check)
unified_ordersPurpose: Normalized order storage from all sales channels. Orders flow here from Shopify webhooks and future channel integrations.
| Column | Type | Description |
|---|---|---|
id |
uuid | Primary key |
shop_id |
uuid | FK to stores |
sales_channel_id |
uuid | FK to sales_channels |
external_order_id |
text | Order ID from source system |
external_order_number |
text | Human-readable order number |
order_date |
timestamptz | When order was placed |
customer_email |
text | Customer email |
ship_to_name |
text | Shipping recipient name |
ship_to_address1 |
text | Shipping address line 1 |
ship_to_address2 |
text | Shipping address line 2 |
ship_to_city |
text | Shipping city |
ship_to_state |
text | Shipping state/province |
ship_to_postal_code |
text | Shipping ZIP/postal code |
ship_to_country |
text | Shipping country code |
ship_to_phone |
text | Shipping phone number |
total_price |
numeric | Order total |
currency |
text | Currency code (ISO 4217) |
fulfillment_status |
text | unfulfilled, partial, fulfilled |
wms_order_id |
text | Order ID in WMS (after publish) |
routed_at |
timestamptz | When order was routed |
routed_to_warehouse_id |
uuid | FK to assigned warehouse |
routing_decision_id |
uuid | FK to routing decision |
line_items |
jsonb | Array of {sku, quantity, name, price} |
created_at |
timestamptz | Record creation |
updated_at |
timestamptz | Last modification |
Relationships:
shop_id → stores.idsales_channel_id → sales_channels.idrouted_to_warehouse_id → trackstar_warehouses.idrouting_decision_id → routing_decisions.idrouting_decisionsIndexes:
(shop_id, external_order_id) - unique constraint(shop_id, order_date) - for date range queries(fulfillment_status) - for filteringUsed by: Order ingestion, routing engine, dashboard metrics, fulfillment reports
routing_decisionsPurpose: Audit log of routing decisions. Records why each order was assigned to a specific warehouse.
| Column | Type | Description |
|---|---|---|
id |
uuid | Primary key |
shop_id |
uuid | FK to stores |
order_id |
uuid | FK to unified_orders |
selected_warehouse_id |
uuid | FK to chosen warehouse |
decision_factors |
jsonb | Scoring breakdown (see below) |
alternatives |
jsonb | Array of alternative warehouse scores |
decided_at |
timestamptz | When decision was made |
created_at |
timestamptz | Record creation |
decision_factors structure:
{
"inventoryScore": 0.9,
"proximityScore": 0.85,
"costScore": 0.7,
"capacityScore": 1.0,
"totalScore": 0.86
}
Relationships:
shop_id → stores.idorder_id → unified_orders.idselected_warehouse_id → trackstar_warehouses.idUsed by: Order routing, routing analytics, debugging
routing_rulesPurpose: Configurable routing rules per store. Allows merchants to customize routing behavior.
| Column | Type | Description |
|---|---|---|
id |
uuid | Primary key |
shop_id |
uuid | FK to stores |
name |
text | Rule name |
priority |
integer | Rule evaluation order |
conditions |
jsonb | When rule applies (see below) |
action |
jsonb | What to do when matched |
is_active |
boolean | Rule is enabled |
created_at |
timestamptz | Record creation |
updated_at |
timestamptz | Last modification |
conditions example:
{
"field": "ship_to_state",
"operator": "in",
"value": ["CA", "OR", "WA"]
}
action example:
{
"type": "route_to_warehouse",
"warehouse_id": "uuid-here"
}
Used by: Custom routing logic, rule-based fulfillment
trackstar_webhook_eventsPurpose: Idempotency tracking for Trackstar webhooks. Prevents duplicate processing of the same event.
| Column | Type | Description |
|---|---|---|
id |
uuid | Primary key |
event_id |
text | Trackstar event ID (unique) |
event_type |
text | Event type (e.g., inventory.updated) |
connection_id |
text | Trackstar connection ID |
shop_id |
uuid | FK to stores |
payload |
jsonb | Full webhook payload |
processed_at |
timestamptz | When event was processed |
created_at |
timestamptz | Record creation |
Indexes:
(event_id) - unique constraint for idempotencyUsed by: Webhook handler, sync debugging
connection_sync_statusPurpose: Tracks sync job status per connection. Used for UI sync status display and retry logic.
| Column | Type | Description |
|---|---|---|
id |
uuid | Primary key |
connection_id |
uuid | FK to trackstar_connections |
sync_type |
enum | historical, warehouses, inventory, orders |
status |
enum | pending, in_progress, completed, failed |
started_at |
timestamptz | Sync start time |
completed_at |
timestamptz | Sync completion time |
items_synced |
integer | Number of items synced |
error_message |
text | Error details if failed |
created_at |
timestamptz | Record creation |
updated_at |
timestamptz | Last modification |
Relationships:
connection_id → trackstar_connections.idUsed by: Sync status UI, retry logic, monitoring
order_backfill_jobsPurpose: Tracks historical order import jobs from Shopify. Used when a store first connects to import past orders.
| Column | Type | Description |
|---|---|---|
id |
uuid | Primary key |
shop_id |
uuid | FK to stores |
sales_channel_id |
uuid | FK to sales_channels |
status |
enum | pending, in_progress, completed, failed |
total_orders |
integer | Total orders to import |
orders_processed |
integer | Orders imported so far |
days_to_backfill |
integer | How many days of history |
cursor |
text | Pagination cursor for resume |
started_at |
timestamptz | Job start time |
completed_at |
timestamptz | Job completion time |
error_message |
text | Error details if failed |
created_at |
timestamptz | Record creation |
updated_at |
timestamptz | Last modification |
Relationships:
shop_id → stores.idsales_channel_id → sales_channels.idUsed by: Historical order import, onboarding flow
fulfillment_report_jobsPurpose: Tracks fulfillment analysis report generation jobs. Reports analyze order patterns and warehouse coverage.
| Column | Type | Description |
|---|---|---|
id |
uuid | Primary key |
shop_id |
uuid | FK to stores |
status |
enum | pending, running, completed, failed |
trigger_type |
enum | auto (scheduled) or manual |
orders_analyzed |
integer | Number of orders in analysis |
started_at |
timestamptz | Job start time |
completed_at |
timestamptz | Job completion time |
error_message |
text | Error details if failed |
created_at |
timestamptz | Record creation |
Relationships:
shop_id → stores.idfulfillment_report_resultsUsed by: Report generation, analytics dashboard
fulfillment_report_resultsPurpose: Stores computed fulfillment report data. Contains order geography analysis and warehouse recommendations.
| Column | Type | Description |
|---|---|---|
id |
uuid | Primary key |
report_job_id |
uuid | FK to fulfillment_report_jobs |
total_orders |
integer | Total orders analyzed |
total_revenue |
numeric | Sum of order values |
avg_order_value |
numeric | Average order value |
current_warehouse |
text | Current primary warehouse |
current_coverage_percent |
numeric | Current 2-day coverage % |
orders_by_region |
jsonb | Order breakdown by US region |
orders_by_state |
jsonb | Order breakdown by state |
top_states |
jsonb | Top 10 states by order volume |
warehouse_recommendations |
jsonb | Tiered warehouse recommendations |
optimal_3_node_network |
jsonb | Best 3-warehouse configuration |
all_warehouse_coverage |
jsonb | Coverage % for all warehouses |
created_at |
timestamptz | Record creation |
Relationships:
report_job_id → fulfillment_report_jobs.idUsed by: Fulfillment report UI, warehouse recommendations
forecast_jobsPurpose: Tracks demand forecasting job execution. Jobs analyze historical orders to predict future demand.
| Column | Type | Description |
|---|---|---|
id |
uuid | Primary key |
shop_id |
uuid | FK to stores |
status |
enum | pending, running, completed, failed |
method |
text | Forecasting method used |
parameters |
jsonb | Method parameters |
started_at |
timestamptz | Job start time |
completed_at |
timestamptz | Job completion time |
error_message |
text | Error details if failed |
created_at |
timestamptz | Record creation |
Relationships:
shop_id → stores.idforecast_resultsUsed by: Forecasting service, demand planning
forecast_resultsPurpose: Stores SKU-level demand forecasts with confidence intervals.
| Column | Type | Description |
|---|---|---|
id |
uuid | Primary key |
job_id |
uuid | FK to forecast_jobs |
shop_id |
uuid | FK to stores |
sku |
text | Product SKU |
predicted_units |
integer | Total predicted units |
confidence |
enum | low, medium, high, very_high |
mape |
numeric | Mean Absolute Percentage Error |
daily_forecast |
jsonb | Array of {date, units} |
regional_breakdown |
jsonb | Array of {state, percentage, units} |
created_at |
timestamptz | Record creation |
Relationships:
job_id → forecast_jobs.idshop_id → stores.idUsed by: Forecasting UI, inventory planning
These tables are created but not yet used. They support P1 features planned for post-launch.
unified_productsPurpose: (P1 - Products Sync) Unified product catalog across all sales channels. Will store canonical product data synced from Shopify and WMS.
| Column | Type | Description |
|---|---|---|
id |
uuid | Primary key |
shop_id |
uuid | FK to stores |
sku |
text | Primary SKU identifier |
title |
text | Product title |
description |
text | Product description |
vendor |
text | Vendor/brand name |
product_type |
text | Product category |
tags |
text[] | Product tags |
weight |
numeric | Weight in grams |
weight_unit |
text | Weight unit |
dimensions |
jsonb | {length, width, height, unit} |
images |
jsonb | Array of image URLs |
variants |
jsonb | Variant data |
channel_mappings |
jsonb | SKU mappings per channel |
is_active |
boolean | Product is active |
created_at |
timestamptz | Record creation |
updated_at |
timestamptz | Last modification |
Status: Schema ready, feature not implemented
inventory_transfersPurpose: (P1 - Auto-Balancing) Tracks inventory movement recommendations and executions between warehouses.
| Column | Type | Description |
|---|---|---|
id |
uuid | Primary key |
shop_id |
uuid | FK to stores |
from_warehouse_id |
uuid | Source warehouse |
to_warehouse_id |
uuid | Destination warehouse |
sku |
text | Product SKU |
quantity |
integer | Units to transfer |
status |
enum | recommended, approved, in_transit, completed, cancelled |
urgency |
enum | critical, high, medium, low |
recommendation_reason |
jsonb | Why transfer was suggested (structured data) |
velocity_data |
jsonb | Sales velocity at source/destination warehouses |
approved_at |
timestamptz | When merchant approved |
approved_by |
text | Who approved |
completed_at |
timestamptz | Transfer completion time |
external_transfer_id |
text | Trackstar/WMS inbound shipment ID |
trackstar_shipment_data |
jsonb | Full shipment data from Trackstar |
tracking_number |
text | Shipment tracking |
notes |
text | Additional notes |
created_at |
timestamptz | Record creation |
updated_at |
timestamptz | Last modification |
recommendation_reason structure:
{
"type": "low_stock",
"daysUntilStockout": 5,
"destVelocity": 10,
"sourceExcess": 250,
"message": "LA warehouse will stockout in 5 days"
}
velocity_data structure:
{
"sourceVelocity": 2,
"destVelocity": 15,
"sourceDaysOfStock": 180,
"destDaysOfStock": 5
}
Related GitHub Issues: #50 (EPIC), #51-56 (Sub-issues)
Status: Schema ready, feature not implemented
get_orders_by_day(shop_uuid, start_date, end_date)Purpose: Returns daily order counts and revenue for dashboard charts.
Returns: TABLE(day date, order_count bigint, total_revenue numeric)
Used by: Dashboard metrics
get_orders_by_state(shop_uuid, start_date, end_date)Purpose: Returns order counts grouped by shipping state for geographic analysis.
Returns: TABLE(state text, order_count bigint, total_revenue numeric)
Used by: Fulfillment reports, coverage analysis
┌─────────────────┐
│ stores │
└────────┬────────┘
│
┌────┴────┬──────────────┬─────────────────┐
│ │ │ │
▼ ▼ ▼ ▼
┌─────────┐ ┌────────────┐ ┌──────────────┐ ┌────────────────┐
│ sales_ │ │ trackstar_ │ │ unified_ │ │ fulfillment_ │
│channels │ │connections │ │ orders │ │ report_jobs │
└────┬────┘ └─────┬──────┘ └──────┬───────┘ └───────┬────────┘
│ │ │ │
│ ▼ │ ▼
│ ┌───────────────┐ │ ┌──────────────────┐
│ │ trackstar_ │ │ │ fulfillment_ │
│ │ warehouses │◄────┼─────────│ report_results │
│ └───────┬───────┘ │ └──────────────────┘
│ │ │
│ ▼ │
│ ┌───────────────┐ │
│ │ trackstar_ │ │
│ │ inventory │ │
│ └───────────────┘ │
│ │
│ ▼
│ ┌───────────────┐
└───────────────────►│ routing_ │
│ decisions │
└───────────────┘
All tables have Row Level Security (RLS) enabled with the following policy pattern:
-- Service role has full access
CREATE POLICY "Service role access" ON table_name
FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
Note: Direct database access is only via service role (backend). Frontend uses API endpoints that enforce authorization.
Key indexes for query performance:
| Table | Index | Purpose |
|---|---|---|
unified_orders |
(shop_id, order_date) |
Date range queries |
unified_orders |
(shop_id, external_order_id) |
Deduplication |
trackstar_inventory |
(warehouse_id, sku) |
Inventory lookups |
trackstar_webhook_events |
(event_id) |
Idempotency checks |
routing_decisions |
(order_id) |
Order→decision lookup |
| Migration | Date | Description |
|---|---|---|
20260116100000_initial_v2_schema.sql |
2026-01-16 | Initial v2 schema |
20260116110000_clean_schema_fix.sql |
2026-01-16 | P1 scaffolding tables |
20260116200000_add_webhook_events.sql |
2026-01-16 | Monitoring tables |
20260116210000_drop_legacy_tables.sql |
2026-01-16 | Removed 10 legacy tables |