---
title: OMS Schema
slug: oms-schema
createdAt: 2025-10-20T20:27:01.496Z
---
# 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
1. **Variant Logic**: If your system doesn’t have separate variant IDs, use product\_id for both id and variant\_id in the variants table
2. **Location Data**: If not tracking physical locations, leave as NULL
3. **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):
1. **shipping\_address\_name**: Concatenate shipping\_first\_name + space + shipping\_last\_name
2. **billing\_address\_name**: Concatenate billing\_first\_name + space + billing\_last\_name
3. **taxes\_included**: Derive from your tax calculation logic (whether product prices include tax)
4. **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 |