Schema · Entity relationship diagram
ShipBob tables and relationships
Orders, shipments and line items, products and variants, inventory (total and by fulfillment center), receiving orders, returns, invoices and billing transactions.
ConnectPaste an API key
Default schema
shipbobSource API docsdeveloper.shipbob.com
DestinationsAmazon Redshift, Google BigQuery, Snowflake, PostgreSQL, Amazon S3
- Column names follow ShipBob's own API field names. The order table is
fulfillment_orders. - Columns ending in
_jsonhold nested API objects as JSON text.
Primary keyForeign keyReferencesJoins on (not a key)
Drag to pan, scroll or pinch to zoom, click a table to see what it joins to.
Table reference
14 tables · click a table for its columnsfulfillment_orders28 columns · Orders & shipmentsshow in diagram
Primary key: id
| Column | Type | Key | Description |
|---|---|---|---|
| idPrimary key. | bigint | PK | Primary key. |
| order_number | varchar(256) | ||
| reference_id | varchar(512) | ||
| status | varchar(32) | ||
| type | varchar(32) | ||
| created_date | timestamp | ||
| purchase_date | timestamp | ||
| channel_idReferences channels.id. | bigint | FK | References channels.id. |
| channel_name | varchar(256) | ||
| shipping_method | varchar(256) | ||
| gift_message | varchar(4096) | ||
| total_price | decimal(18,4) | ||
| recipient_name | varchar(512) | ||
| recipient_email | varchar(512) | ||
| recipient_phone | varchar(128) | ||
| address1 | varchar(512) | ||
| address2 | varchar(512) | ||
| company_name | varchar(512) | ||
| city | varchar(256) | ||
| state | varchar(128) | ||
| zip_code | varchar(64) | ||
| country | varchar(64) | ||
| shipping_carrier_type | varchar(32) | ||
| shipping_payment_term | varchar(32) | ||
| retailer_program_data | varchar(65535) | ||
| tags | varchar(65535) | ||
| shipment_count | integer | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
order_products12 columns · Orders & shipmentsshow in diagram
Primary key: _jsdata_key
| Column | Type | Key | Description |
|---|---|---|---|
| _jsdata_keyPrimary key. | varchar(32) | PK | Primary key. |
| order_idReferences fulfillment_orders.id. | bigint | FK | References fulfillment_orders.id. |
| product_idReferences products.id. | bigint | FK | References products.id. |
| reference_id | varchar(512) | ||
| sku | varchar(256) | ||
| quantity | integer | ||
| quantity_unit_of_measure_code | varchar(16) | ||
| unit_price | decimal(18,4) | ||
| gtin | varchar(64) | ||
| upc | varchar(64) | ||
| external_line_id | bigint | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
shipments36 columns · Orders & shipmentsshow in diagram
Primary key: id
| Column | Type | Key | Description |
|---|---|---|---|
| idPrimary key. | bigint | PK | Primary key. |
| order_idReferences fulfillment_orders.id. | bigint | FK | References fulfillment_orders.id. |
| reference_id | varchar(512) | ||
| status | varchar(32) | ||
| status_details | varchar(65535) | ||
| created_date | timestamp | ||
| last_update_at | timestamp | ||
| last_tracking_update_at | timestamp | ||
| actual_fulfillment_date | timestamp | ||
| estimated_fulfillment_date | timestamp | ||
| estimated_fulfillment_date_status | varchar(64) | ||
| delivery_date | timestamp | ||
| location_idReferences fulfillment_centers.id. | bigint | FK | References fulfillment_centers.id. |
| location_name | varchar(256) | ||
| ship_option | varchar(256) | ||
| carrier | varchar(128) | ||
| carrier_service | varchar(256) | ||
| tracking_number | varchar(256) | ||
| tracking_url | varchar(1024) | ||
| shipping_date | timestamp | ||
| bol | varchar(128) | ||
| pro_number | varchar(128) | ||
| scac | varchar(16) | ||
| invoice_amount | decimal(18,4) | ||
| invoice_currency_code | varchar(8) | ||
| insurance_value | decimal(18,4) | ||
| total_weight_oz | decimal(18,4) | ||
| length_in | decimal(18,4) | ||
| width_in | decimal(18,4) | ||
| depth_in | decimal(18,4) | ||
| package_material_type | varchar(32) | ||
| require_signature | boolean | ||
| is_tracking_uploaded | boolean | ||
| gift_message | varchar(4096) | ||
| parent_cartons | varchar(65535) | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
shipment_products16 columns · Orders & shipmentsshow in diagram
Primary key: _jsdata_key
| Column | Type | Key | Description |
|---|---|---|---|
| _jsdata_keyPrimary key. | varchar(32) | PK | Primary key. |
| shipment_idReferences shipments.id. | bigint | FK | References shipments.id. |
| order_id | bigint | ||
| product_idReferences products.id. | bigint | FK | References products.id. |
| reference_id | varchar(512) | ||
| sku | varchar(256) | ||
| name | varchar(1024) | ||
| inventory_idReferences inventory_levels.inventory_id. | bigint | FK | References inventory_levels.inventory_id. |
| inventory_name | varchar(1024) | ||
| quantity | integer | ||
| quantity_committed | integer | ||
| lot | varchar(256) | ||
| expiration_date | timestamp | ||
| is_dangerous_goods | boolean | ||
| serial_numbers | varchar(65535) | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
channels5 columns · Orders & shipmentsshow in diagram
Primary key: id
| Column | Type | Key | Description |
|---|---|---|---|
| idPrimary key. | bigint | PK | Primary key. |
| name | varchar(256) | ||
| application_name | varchar(256) | ||
| scopes | varchar(4096) | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
products11 columns · Products & inventoryshow in diagram
Primary key: id
| Column | Type | Key | Description |
|---|---|---|---|
| idPrimary key. | bigint | PK | Primary key. |
| name | varchar(1024) | ||
| type | varchar(64) | ||
| user_id | bigint | ||
| created_on | timestamp | ||
| updated_on | timestamp | ||
| taxonomy_id | bigint | ||
| taxonomy_name | varchar(512) | ||
| taxonomy_path | varchar(1024) | ||
| variant_count | integer | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
product_variants21 columns · Products & inventoryshow in diagram
Primary key: id
| Column | Type | Key | Description |
|---|---|---|---|
| idPrimary key. | bigint | PK | Primary key. |
| product_idReferences products.id. | bigint | FK | References products.id. |
| sku | varchar(256) | ||
| name | varchar(1024) | ||
| status | varchar(32) | ||
| upc | varchar(64) | ||
| gtin | varchar(64) | ||
| inventory_idReferences inventory_levels.inventory_id. | bigint | FK | References inventory_levels.inventory_id. |
| on_hand_qty | integer | ||
| is_digital | boolean | ||
| packaging_material_type | varchar(32) | ||
| created_on | timestamp | ||
| updated_on | timestamp | ||
| barcodes | varchar(4096) | ||
| dimension | varchar(4096) | ||
| weight | varchar(4096) | ||
| lot_information | varchar(4096) | ||
| bundle_definition | varchar(65535) | ||
| fulfillment_settings | varchar(65535) | ||
| customs | varchar(65535) | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
inventory_levels15 columns · Products & inventoryshow in diagram
Current stock snapshot (all fulfillment centers combined), refreshed every run.
Primary key: inventory_id
| Column | Type | Key | Description |
|---|---|---|---|
| inventory_idPrimary key. | bigint | PK | Primary key. |
| name | varchar(1024) | ||
| sku | varchar(256) | ||
| total_on_hand_quantity | integer | ||
| total_committed_quantity | integer | ||
| total_fulfillable_quantity | integer | ||
| total_sellable_quantity | integer | ||
| total_awaiting_quantity | integer | ||
| total_backordered_quantity | integer | ||
| total_exception_quantity | integer | ||
| total_internal_transfer_quantity | integer | ||
| total_in_fulfillment_quantity | integer | ||
| total_damaged_quantity | integer | ||
| total_quarantine_quantity | integer | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
inventory_levels_by_location16 columns · Products & inventoryshow in diagram
Current stock snapshot per fulfillment center. _jsdata_key = md5(inventory_id|location_id).
Primary key: _jsdata_key
| Column | Type | Key | Description |
|---|---|---|---|
| _jsdata_keyPrimary key. | varchar(32) | PK | Primary key. |
| inventory_idReferences inventory_levels.inventory_id. | bigint | FK | References inventory_levels.inventory_id. |
| sku | varchar(256) | ||
| name | varchar(1024) | ||
| location_idReferences fulfillment_centers.id. | bigint | FK | References fulfillment_centers.id. |
| location_name | varchar(256) | ||
| on_hand_quantity | integer | ||
| committed_quantity | integer | ||
| fulfillable_quantity | integer | ||
| awaiting_quantity | integer | ||
| exception_quantity | integer | ||
| internal_transfer_quantity | integer | ||
| in_fulfillment_quantity | integer | ||
| damaged_quantity | integer | ||
| quarantine_quantity | integer | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
fulfillment_centers19 columns · Products & inventoryshow in diagram
Primary key: id
| Column | Type | Key | Description |
|---|---|---|---|
| idPrimary key. | bigint | PK | Primary key. |
| name | varchar(256) | ||
| abbreviation | varchar(32) | ||
| timezone | varchar(64) | ||
| is_active | boolean | ||
| is_receiving_enabled | boolean | ||
| is_shipping_enabled | boolean | ||
| access_granted | boolean | ||
| region_id | bigint | ||
| region_name | varchar(128) | ||
| address1 | varchar(512) | ||
| address2 | varchar(512) | ||
| city | varchar(256) | ||
| state | varchar(128) | ||
| country | varchar(64) | ||
| zip_code | varchar(64) | ||
| attributes | varchar(4096) | ||
| services | varchar(65535) | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
receiving_orders18 columns · Inbound & returnsshow in diagram
Warehouse receiving orders (WROs / inbound shipments).
Primary key: id
| Column | Type | Key | Description |
|---|---|---|---|
| idPrimary key. | bigint | PK | Primary key. |
| purchase_order_number | varchar(512) | ||
| status | varchar(32) | ||
| package_type | varchar(32) | ||
| box_packaging_type | varchar(32) | ||
| expected_arrival_date | timestamp | ||
| insert_date | timestamp | ||
| last_updated_date | timestamp | ||
| external_sync_timestamp | timestamp | ||
| fulfillment_center_idReferences fulfillment_centers.id. | bigint | FK | References fulfillment_centers.id. |
| fulfillment_center_name | varchar(256) | ||
| box_labels_uri | varchar(2048) | ||
| total_expected_quantity | integer | ||
| total_received_quantity | integer | ||
| total_stowed_quantity | integer | ||
| inventory_quantities | varchar(65535) | ||
| status_history | varchar(65535) | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
returns26 columns · Inbound & returnsshow in diagram
Primary key: id
| Column | Type | Key | Description |
|---|---|---|---|
| idPrimary key. | bigint | PK | Primary key. |
| reference_id | varchar(512) | ||
| store_order_id | varchar(256) | ||
| status | varchar(32) | ||
| return_type | varchar(32) | ||
| insert_date | timestamp | ||
| awaiting_arrival_date | timestamp | ||
| arrived_date | timestamp | ||
| processing_date | timestamp | ||
| completed_date | timestamp | ||
| cancelled_date | timestamp | ||
| tracking_number | varchar(256) | ||
| shipment_tracking_number | varchar(256) | ||
| original_shipment_idReferences shipments.id. | bigint | FK | References shipments.id. |
| customer_name | varchar(512) | ||
| channel_idReferences channels.id. | bigint | FK | References channels.id. |
| channel_name | varchar(256) | ||
| fulfillment_center_idReferences fulfillment_centers.id. | bigint | FK | References fulfillment_centers.id. |
| fulfillment_center_name | varchar(256) | ||
| invoice_amount | decimal(18,4) | ||
| invoice_currency_code | varchar(8) | ||
| total_quantity | integer | ||
| inventory | varchar(65535) | ||
| transactions | varchar(65535) | ||
| status_history | varchar(65535) | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
invoices7 columns · Billingshow in diagram
Primary key: invoice_id
| Column | Type | Key | Description |
|---|---|---|---|
| invoice_idPrimary key. | bigint | PK | Primary key. |
| invoice_date | date | ||
| invoice_type | varchar(64) | ||
| amount | decimal(18,4) | ||
| currency_code | varchar(8) | ||
| running_balance | decimal(18,4) | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
transactions16 columns · Billingshow in diagram
Billing line items (one per charge), pulled per invoice.
Primary key: transaction_id
| Column | Type | Key | Description |
|---|---|---|---|
| transaction_idPrimary key. | varchar(128) | PK | Primary key. |
| invoice_idReferences invoices.invoice_id. | bigint | FK | References invoices.invoice_id. |
| invoice_date | date | ||
| invoice_type | varchar(64) | ||
| charge_date | timestamp | ||
| transaction_type | varchar(32) | ||
| transaction_fee | varchar(256) | ||
| amount | decimal(18,4) | ||
| currency_code | varchar(8) | ||
| fulfillment_center | varchar(256) | ||
| reference_id | varchar(256) | ||
| reference_type | varchar(64) | ||
| invoiced_status | boolean | ||
| taxes | varchar(65535) | ||
| additional_details | varchar(65535) | ||
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |