OMS Schema

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 optional fields can be omitted or provided as null where data is not available 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 last name string customer last name 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