Shopify as ERP Authentication and Database Structure Technical Scope
The purpose of this document is to describe how we will incorporate Shopify as ERP system in Hemi.
Please note there are some specifics around the pagination and the explanation is available here - https://shopify.dev/api/usage/pagination-rest
Authentication & Authorization
The authentication flow is first the application need to be installed in Shopify using this link structure: https://{shop_url}/admin/oauth/authorize?client_id={WAP app API key}
&scope=read_products,write_products,read_product_listings,read_inventory,write_inventory,read_content,write_content,read_orders,write_orders,read_draft_orders,write_draft_orders,read_fulfillments,write_fulfillments,read_shipping,write_shipping
&redirect_uri={WAP ap URL}
&state=code
REDIRECT URL actual parameter is redirect_uri with an "i" in the end.
Please note by default we would like to include all “read_products,write_products,read_product_listings,read_inventory,write_inventory,read_content,write_content,read_orders,write_orders,read_draft_orders,write_draft_orders,read_fulfillments,write_fulfillments,read_shipping,write_shipping“ available scopes but if we specify any in Shopify Connector we use them.
The link will redirect to the Shopify store where the client need to install the app and after successful installation will be automatically redirected to http://www.wearepentagon.com . In the generated link after the redirect you will get the field Code which is required for the integration.
With the CODE we need to generate the access token using:
The client ID and the client secret are our APP ids and are the same for every Shopify however the code is the unique value generated for the specific client which is used to generate the tokens.
Response:
{
"access_token": "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",
"scope": "write_products,read_product_listings,write_inventory,write_content,write_orders,write_draft_orders,write_fulfillments,write_shipping"
}Database
Here you can see the whole picture of the involved tables and the connections between them. Please note that the link is accessible only for accounts within Hemi Connect Ltd.
Additional tables will need to be created, but please note the structure and names are suggestions.
Integration Credentials - here we store our application ids. Please note this table is not visible in the UI and the details are the same for all instances.
| Field Name | Type | Required | Comment | Default Value |
|---|---|---|---|---|
| Integration ID | varchar | Yes | Id combining all keys for the integration | |
| Secret Key | varchar | Yes | The actual key | |
| Secret Name | varchar | Yes | The name of the key e.g. (api_key, api_secret_key) |
Shopify Connector - here we will be storing all the Shopify details required from the connection to work and this is our starting point
| Field Name | Type | Required | Default Value | |
|---|---|---|---|---|
| Name | varchar | Yes | Field for internal use to differentiate the Shopify connectors. | |
| Active | checkbox | No | Flag indicating if the Shopify connector is active and based on this we can switch off and on the connector | false |
| Seller | dropdown | No | Dropdown with all our sellers. Will be used when we want to split the orders by seller (Phase 2) | |
| One Shopify connector can have only one seller. | ||||
| Integration ID | dropdown | Yes | The ids of the Integration Credentials | |
| Shop Name | varchar | Yes | The name of the Shopify shop used for the API requests | |
| Access Token | varchar | No | Access Token populated automatically once we authorize | |
| Code | varchar | Yes | Code required for the authentication | |
| API Access Scopes | varchar | Yes | Required for the authentication | read_products,write_products,read_product_listings,read_inventory,write_inventory,read_content,write_content,read_orders,write_orders,read_draft_orders,write_draft_orders,read_fulfillments,write_fulfillments,read_shipping,write_shipping |
| Create Orders | checkbox | No | Flag indicating if we would like to export/create new orders on Shopify | false |
| Inventory Behavior | dropdown | No | Dropdown with values - “bypass”, “decrement_ignoring_policy”, “decrement_obeying_policy” | bypass |
| Send Order Confirmation | checkbox | No | Flag indicating if we would like to send order confirmation notification from Shopify to the customer. | |
| If set to true we include when we are creating the order in the body parameters: send_receipt=true | false |
|||
| Send Shipping Confirmation | checkbox | No | Flag indicating if we would like to send shipping confirmation notification from Shopify to the customer. | |
| If set to true we include when we are creating the order in the body parameters: send_fulfillment_receipt=true | false |
|||
| Get Stock | checkbox | No | Flag indicating if we would like to get stock from Shopify and populate it in our location quantities | false |
| Get Products | checkbox | No | Flag indicating if we would like to get products from Shopify | false |
| Get Prices | checkbox | No | Flag indicating if we would like to get prices from Shopify | false |
| Get Refunds | checkbox | No | Flag indicating if we would like to get refunds from Shopify | false |
| Get Shipments | checkbox | No | Flag indicating if we would like to get shipping details from Shopify | false |
Shopify Connector Locations - slave table of Location table where we basically map our locations with Shopify location IDs so we will know how to update the stock in Hemi. Please note we may have more than one location mapped.
| Field Name | Type | Required | Comment |
|---|---|---|---|
| External ID | integer | Yes | The Id of the Shopify location from where we will be getting stock levels |
| Connector ID | dropdown | Yes | A dropdown list with all Shopify Connectors |
Account Shopify Connector - slave table of Account where there will be a dropdown list with all Shopify Connectors. Here we will be
Comment
displaying the Shopify Connector > Name
| Field Name | Type | Required | Comment |
|---|---|---|---|
| Connector Name | dropdown | Yes | A dropdown list with all Shopify connectors. We can have more than one record in this table but can select only once a specific Shopify Connector. Here we will need to display the Shopify Connector > Name as dropdown options. |
| Get Prices Update Original Price | checkbox | No | By default this checkbox will be set as “No“ which mean we are downloading the prices and store them in Item Account > Price and Item Account > RRP. |
If this parameter is set as “Yes“ we update Item Account > Original Price. If this parameter is set as “Yes“ we update Item Account > Original RRP. Please note this parameter works per Account not per Shopify Connector. | | Create Order Tax Excluded | checkbox | No | By default this checkbox will be set as “No“. If set on “Yes“ we will have taxes_included = 'true' | | Create Order Including Bundles | checkbox | No | By default this checkbox will be set as “No“. If set on “Yes“ we will have to export all bundle components and their bundle SKUs to Shopify. Please note this is for Phase 2 only |
Therefore we will be able to decide if we would like to use Shopify as a ERP system if we select the Shopify Connector or as a standard Marketplace.
Item Shopify - slave table of Item where we will store the product ids
| Field Name | Type | Required | Comment |
|---|---|---|---|
| Variant ID | integer | No | |
| Product ID | integer | No | |
| Inventory Item ID | integer | No | |
| Product Details Received | checkbox | No | Indicates if we have received any ids from Shopify. |
| Get Product Details Error | varchar | No | Return error if the SKU does not exist on Shopify, or there is an issue with get products request |
| Connector ID | integer | No | The id of the connector from which we have get the details |
Order Payment Shopify This table is require to be able to cover both flows when we are creating refunds in Shopify or getting them from Shopify.
| Field Name | Type | Required | Comment |
|---|---|---|---|
| Refund ID | integer | No |
On every request we can specify the limit. If we do not include the limit parameter by default we will receive 50 records per page. Thus for each request we include “limit=250” per page.
Also we are able to specify which fields we would like to receive in the response as well and we will use this for the specific calls.