OMS Schema

24 min

Overview

This document outlines the complete data schema requirements for integrating custom OMS data into the Chord analytics pipeline. Each section represents a different data entity with its required and optional fields.

Use this specification to map your source OMS data to Chord’s standardized schema for seamless analytics integration.

Data Provision Guidelines

Field Requirements:

Required Fields: Must be provided with valid, non-null data.

Recommended: Optional, but strongly encouraged. Omitting these fields reduces accuracy in Chord's data science features, including customer matching and predictive modeling.

Optional Fields: Can be omitted or provided as null.

Data Format Standards:

Timestamps: ISO 8601 format (YYYY-MM-DDTHH:MM:SSZ)

Numeric values: Standard decimal notation without currency symbols

Boolean values: Lowercase true or false

String values: UTF-8 encoded, properly escaped for special characters

NULL values: Use NULL (not empty strings) for missing data in non-string fields

Data Delivery Requirements

File Format:

Parquet

One file per data entity (orders, customers, products, etc.)

Special Considerations

Variant Logic: If your system doesn’t have separate variant IDs, use product_id for both id and variant_id in the variants table

Location Data: If not tracking physical locations, leave as NULL

Vendor Values: Provide your company/brand name as the vendor for products and line items, or leave as NULL if not applicable

Data Transformation Requirements

Status Mappings Required

Order Fulfillment Status: Map your order_status values to one of:

fulfilled

restocked

partial

Product Status: Map your item_status values to one of:

active

archived

draft

Transaction Kind: Map your payment_type values to one of:

capture

void

refund

sale

change

authorization

Transaction Status: Map your response_code values to one of:

success

pending

failure

error

Financial Status (if available):

authorized

partially_paid

paid

refunded

pending

partially_refunded

voided

Data Transformation Guidance

Calculated Fields (if not available directly in your system):

shipping_address_name: Concatenate shipping_first_name + space + shipping_last_name

billing_address_name: Concatenate billing_first_name + space + billing_last_name

taxes_included: Derive from your tax calculation logic (whether product prices include tax)

cancelled_at: If order status indicates cancellation, provide the cancellation timestamp

Address Normalization:

Validate country codes against ISO 3166-1 alpha-2 standard

Validate state/province codes where applicable

Default Values for Optional Fields

When optional fields are not available in your system:

String fields: NULL

Numeric fields: NULL where appropriate

Boolean fields: false as default

Timestamp fields: NULL

Array fields: Empty array []

JSONB fields: NULL

Note on Fulfillment Data

If your system tracks fulfillment and shipment data, Sections 10-12 (Fulfillments, Fulfillment Order Lines, Fulfillment Orders) are optional.

1. Orders Data Schema

Purpose: Order records represent transaction information, including customer details, pricing, and addresses.

Primary Key: id

Core Order Fields

Field Name	Data Type	Description
id	STRING	*Unique order identifier (required)
location_id	STRING	Location/store identifier (optional)
customer_id	STRING	*Reference to customer record (required)
name	STRING	*Order number displayed to customer (required)
email	STRING	*Customer email address (required)
source_name	STRING	Order origination source
is_test	BOOLEAN	Test order flag
app	STRING	Application that created the order
tags	JSONB	Order tags
discount_codes	JSONB	Applied discount codes

Financial Fields

Field Name	Data Type	Description
total_price	NUMERIC	*Complete order total including all charges (required)
total_line_items_price	NUMERIC	*Sum of all line items before adjustments (required)
total_tax	NUMERIC	Total tax amount (optional, 0 if no tax)
total_shipping_price	NUMERIC	Total shipping charges (optional, 0 if free shipping)
total_discounts	NUMERIC	Total discount amount applied (optional, 0 if no discounts)
processing_method	STRING	Payment processing method
financial_status	STRING	Payment status (authorized, paid, refunded, etc.)
taxes_included	BOOLEAN	Whether taxes are included in subtotal

Order Status Fields

Field Name	Data Type	Description
fulfillment_status	STRING	Fulfillment status (fulfilled, restocked, partial)
cancel_reason	STRING	Reason for cancellation if applicable

Shipping Address Fields

Field Name	Data Type	Description
shipping_address_name	STRING	Full recipient name
shipping_address_first_name	STRING	Recipient first name
shipping_address_last_name	STRING	Recipient last name
shipping_address_company	STRING	Company name
shipping_address_phone	STRING	Contact phone number
shipping_address_address_1	STRING	Primary address line
shipping_address_address_2	STRING	Secondary address line
shipping_address_city	STRING	City name
shipping_address_province	STRING	State/Province
shipping_address_province_code	STRING	Province/state code
shipping_address_zip	STRING	Postal code
shipping_address_country	STRING	Country name
shipping_address_country_code	STRING	ISO country code
shipping_address_latitude	STRING	GPS latitude
shipping_address_longitude	STRING	GPS longitude

Billing Address Fields

Field Name	Data Type	Description
billing_address_name	STRING	Full billing name
billing_address_first_name	STRING	Billing first name
billing_address_last_name	STRING	Billing last name
billing_address_company	STRING	Company name
billing_address_phone	STRING	Billing phone number
billing_address_address_1	STRING	Primary billing address
billing_address_address_2	STRING	Secondary billing address
billing_address_city	STRING	Billing city
billing_address_province	STRING	Billing state/province
billing_address_province_code	STRING	Province/state code
billing_address_zip	STRING	Billing postal code
billing_address_country	STRING	Billing country
billing_address_country_code	STRING	Billing ISO country code
billing_address_latitude	STRING	GPS latitude
billing_address_longitude	STRING	GPS longitude

Order Timestamps

Field Name	Data Type	Description
created_at	TIMESTAMP	*Order creation timestamp (required)
processed_at	TIMESTAMP	Order processing timestamp (optional)
updated_at	TIMESTAMP	*Last modification timestamp (required)
closed_at	TIMESTAMP	Order completion timestamp
cancelled_at	TIMESTAMP	Cancellation timestamp (if cancelled)

2. Customers Data Schema

Purpose: Customer records contain account and contact information.

Primary Key: id

Field Name	Data Type	Description
id	STRING	*Unique customer identifier (required)
email	STRING	*Customer email address (required)
first_name	STRING	Customer first name (Recommended - improves accuracy of Chord AI identity matching and predictive modeling)
last_name	STRING	Customer last name (Recommended - improves accuracy of Chord AI identity matching and predictive modeling)
phone	STRING	Customer phone number
type	STRING	Customer account type
has_verified_email	BOOLEAN	Email verification status
created_at	TIMESTAMP	Account creation date
updated_at	TIMESTAMP	Last account update

3. Products Data Schema

Purpose: Product catalog information for all sellable items.

Primary Key: id

Field Name	Data Type	Description
id	STRING	*Unique product identifier (required)
title	STRING	*Product display name (required)
handle	STRING	URL handle/slug
product_type	STRING	Product category
vendor	STRING	Product vendor name
published_scope	STRING	Publication scope
status	STRING	Product status (active/archived/draft)
created_at	TIMESTAMP	Product creation date
updated_at	TIMESTAMP	Last product update

4. Product Variants Data Schema

Purpose: Product variant details including pricing and inventory attributes.

Primary Key: id

Note: If your system doesn’t use variants, create one variant per product with the same ID.

Field Name	Data Type	Description
id	STRING	*Variant identifier (required; use Product ID if no separate variant IDs)
product_id	STRING	*Parent product reference (required)
title	STRING	*Variant display name (required)
sku	STRING	Stock keeping unit (optional but recommended)
barcode	STRING	Product barcode
price	NUMERIC	Retail price
grams	NUMERIC	Weight in grams
option_1	STRING	First variant option
option_2	STRING	Second variant option
option_3	STRING	Third variant option
is_taxable	BOOLEAN	Taxability flag
created_at	TIMESTAMP	Variant creation date
updated_at	TIMESTAMP	Last variant update

5. Order Line Items Data Schema

Purpose: Individual items within each order.

Primary Key: id

Field Name	Data Type	Description
id	STRING	*Unique line item identifier (required)
order_id	STRING	*Parent order reference (required)
product_id	STRING	*Product reference (required)
variant_id	STRING	*Product variant reference (required)
subscription_id	STRING	*Parent subscription reference
title	STRING	*Line item product name (required)
variant_title	STRING	Line item variant name
name	STRING	Full line item name with quantity
sku	STRING	Product SKU
vendor	STRING	Product vendor
grams	NUMERIC	Total weight in grams
price	NUMERIC	*Unit price (required)
quantity	INTEGER	*Quantity ordered (required)
is_taxable	BOOLEAN	Item taxability
variant_inventory_management	STRING	Inventory management method
properties	ARRAY	Custom properties
promo_total	NUMERIC	Line-level discount amount

6. Locations Data Schema (If Available)

Purpose: Location records for stores, warehouses, or other fulfillment locations.

Primary Key: id

Note: This entity is optional. If you don’t track physical locations, you can omit this table.

Field Name	Data Type	Description
id	STRING	Unique location identifier
name	STRING	Location name
is_active	BOOLEAN	Active status
created_at	TIMESTAMP	Location creation date
updated_at	TIMESTAMP	Last location update

7. Payment Transactions Data Schema

Purpose: Financial transactions associated with orders.

Primary Key: id

Field Name	Data Type	Description
id	STRING	*Transaction identifier (required)
order_id	STRING	*Parent order reference (required)
refund_id	STRING	Associated refund ID if applicable
amount	NUMERIC	*Transaction amount (required)
kind	STRING	*Transaction type (required; see status mappings)
status	STRING	*Transaction status (required; see status mappings)
is_test	BOOLEAN	Test transaction flag
created_at	TIMESTAMP	Transaction timestamp

8. Order Line Refunds Data Schema

Purpose: Refunded line items from orders.

Primary Key: id

Note: Only provide if your system tracks refunds.

Field Name	Data Type	Description
id	STRING	Refund line identifier
refund_id	STRING	Parent refund reference
order_line_id	STRING	Original line item reference
quantity	INTEGER	Quantity refunded
subtotal	NUMERIC	Refund amount

9. Return Line Items Data Schema

Purpose: Returned items from orders.

Primary Key: id

Note: Only provide if your system tracks returns separately from refunds.

Field Name	Data Type	Description
id	STRING	Return line identifier
return_id	STRING	Parent return reference
order_line_id	STRING	Original line item reference
quantity	INTEGER	Total quantity returned
refundable_quantity	INTEGER	Quantity eligible for refund
refunded_quantity	INTEGER	Quantity already refunded
return_reason	STRING	Return reason category
return_reason_note	STRING	Detailed return explanation

10. Fulfillments Data Schema (If Available)

Fulfillment records for order shipments.

Field Name	Data Type	Description
id	STRING	Unique fulfillment identifier
order_id	STRING	Parent order reference
status	STRING	Fulfillment status
tracking_company	STRING	Shipping carrier name
shipment_status	STRING	Current shipment status
tracking_numbers	ARRAY	Array of tracking numbers
tracking_urls	ARRAY	Array of tracking URLs
created_at	TIMESTAMP	Fulfillment creation date
updated_at	TIMESTAMP	Last fulfillment update

11. Fulfillment Order Lines Data Schema (If Available)

Links between fulfillments and order line items.

Field Name	Data Type	Description
id	STRING	Unique fulfillment line identifier
fulfillment_id	STRING	Parent fulfillment reference
order_line_id	STRING	Order line item reference

12. Fulfillment Orders Data Schema (If Available)

Fulfillment order groupings for location-based fulfillment.

Field Name	Data Type	Description
id	STRING	Fulfillment order identifier
order_id	STRING	Parent order reference
status	STRING	Fulfillment order status
request_status	STRING	Fulfillment request status
fulfill_at	TIMESTAMP	Scheduled fulfillment date
created_at	TIMESTAMP	Creation timestamp
updated_at	TIMESTAMP	Last update timestamp

13. Subscription History Data Schema

Purpose: Recurring subscription or auto-ship orders.

Primary Key: id

Note: Only provide if your system supports recurring subscriptions or auto-ship functionality.

Field Name	Data Type	Description
id	STRING	Unique ID per subscription (e.g., subscription_id + _ + line_item_no if tracking per product)
customer_id	STRING	Reference to customer record
address_id	STRING	Reference to customer shipping address (if applicable)
oms_product_id	STRING	Product identifier
oms_variant_id	STRING	Variant identifier (use product_id if no variants)
created_at	TIMESTAMP	When subscription was created
price	NUMERIC	Price per delivery
quantity	INTEGER	Units delivered each cycle
status	STRING	Status (active, paused, cancelled, etc.)
subscription_interval_frequency	NUMERIC	How often deliveries occur (e.g., 1, 2, 3)
order_interval_unit	STRING	Time unit (day, week, month, year)
order_interval_frequency	NUMERIC	Same as subscription_interval_frequency
order_day_of_month	NUMERIC	Specific day for monthly subscriptions (1-31)
order_day_of_week	NUMERIC	Specific day for weekly subscriptions (0-6)
expire_after_specific_number_of_periods	NUMERIC	Number of deliveries before auto-cancellation (if applicable)
updated_at	TIMESTAMP	When subscription was last updated
next_run_scheduled_at	TIMESTAMP	Scheduled date for next order
cancelled_at	TIMESTAMP	When subscription was cancelled (if applicable)
cancellation_reason	STRING	Reason for cancellation
cancellation_reason_comments	STRING	Additional cancellation details