Joined model sync from Postgres, Airtable, and Stripe to Hubspot

UI demo video

You can watch a demo video of these operations in Polytomic’s admin console here.

Code

Before getting started make sure to set your Polytomic API key as an environment variable:

$export POLYTOMIC_API_KEY=YOUR-API-TOKEN

This example tutorial will cover three steps:

  1. Connecting to Airtable, Stripe, Postgres, and HubSpot.
  2. Joining the data from Airtable, Stripe, and Postgres using Polytomic models.
  3. Syncing the data to HubSpot.

Step 1: Connecting to Airtable, Stripe, Postgres, and HubSpot

The following request will create an Airtable connection. The configuration for each type of connection can be found at the connection configuration page.

$curl --request POST  \
>     --url https://app.polytomic.com/api/connections \
>     --header "accept: application/json"  \
>     --header "content-type: application/json"  \
>     --header "X-Polytomic-Version: 2024-02-08"   \
>     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}"  \
>     -d '{
>          "name": "Airtable Connection",
>          "type": "airtable", 
>          "configuration": {}
>	}'

Since Airtable connections uses OAuth to authenticate, we’ll need to follow the link returned in the auth_url parameter in the response

Redirection


By default, the API expects the auth_url to be opened in a new browser window. If you’d like to modify the redirect behavior, there is an optional redirect_url parameter that can be added to the request body.

The following request will create an Postgres connection. The configuration for each type of connection can be found at the connection configuration page.

The PostgreSQL server must be network-accessible from Polytomic.

$     curl --request POST \
>     --url https://app.polytomic.com/api/connections \
>     --header "accept: application/json" \
>     --header "content-type: application/json" \
>     --header "X-Polytomic-Version: 2024-02-08" \
>     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}" \
>     -d '{
>     "name": "Postgres Connection",
>     "type": "postgresql",
>     "configuration": {
>        "hostname": "localhost",
>        "port": 5432,
>        "database": "postgres",
>        "username": "user",
>        "password": "secret"
>        }
>    }'

The following request will create an Stripe connection. The configuration for each type of connection can be found at the connection configuration page.

$     curl --request POST \
>     --url https://app.polytomic.com/api/connections \
>     --header "accept: application/json" \
>     --header "content-type: application/json" \
>     --header "X-Polytomic-Version: 2024-02-08" \
>     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}" \
>     -d '{
>     "name": "Stripe Connection",
>     "type": "stripe",
>     "configuration": {
>        "api_key": "sk_test_4eC39HqLyjWDarjtT1zdp7dc"
>        }
>    }'

The following request will create an HubSpot connection. The configuration for each type of connection can be found at the connection configuration page.

$curl --request POST  \
>     --url https://app.polytomic.com/api/connections \
>     --header "accept: application/json"  \
>     --header "content-type: application/json"  \
>     --header "X-Polytomic-Version: 2024-02-08"  \
>     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}"  \
>     -d '{"name": "HubSpot Connection","type": "hubspot", "configuration": {}}'

Since HubSpot connections uses OAuth to authenticate, we’ll need to follow the link returned in the auth_url parameter in the response

Redirection


By default, the API expects the auth_url to be open in a new browser window. If you’d like to modify the redirect behavior, there is an optional redirect_url parameter that can be added to the request body.

The following request will create a User Details model using data from Postgres

$curl --request POST  \
>     --url https://app.polytomic.com/api/models \
>     --header "accept: application/json"  \
>     --header "content-type: application/json"  \
>     --header "X-Polytomic-Version: 2024-02-08"  \
>     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}"  \
>     -d '{
>           "name": "User Details",
>            "configuration": {
>               "table": "users"
>            },
>            "connection_id": "YOUR-POSTGRES-CONNECTION-ID"
>          }'

The following request will create a Customers model using data from Stripe joined with the User Details model using the email field.

$curl --request POST  \
>     --url https://app.polytomic.com/api/models \
>     --header "accept: application/json"  \
>     --header "content-type: application/json"  \
>     --header "X-Polytomic-Version: 2024-02-08"  \
>     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}"  \
>     -d '{
>           "name": "Customers",
>            "configuration": {
>               "stream_id": "customers"
>            },
>            "fields": ["balance", "description", "livemode"],
>            "relations": [
>                {
>                    "from": "email",
>                    "to": {
>                        "field": "email",
>                        "model_id": "YOUR-POSTGRES-MODEL-ID"
>                    }
>                }
>            ],
>            "connection_id": "YOUR-STRIPE-CONNECTION-ID"
>          }'

The following request will create a Stars data model using data from Airtable joined with the User Details model using the email field.

$curl --request POST  \
>     --url https://app.polytomic.com/api/models \
>     --header "accept: application/json"  \
>     --header "content-type: application/json"  \
>     --header "X-Polytomic-Version: 2024-02-08"  \
>     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}"  \
>     -d '{
>           "name": "Customers",
>            "configuration": {
>                "tableobj": {
>                      "value": "tblOuVBnPKvXjYhqP",
>                      "label": "Table 1"
>                 },
>                 "viewobj": {},
>                 "baseobj": {
>                      "value": "appDVrQJeb9w2F2IL",
>                      "label": "User sample"
>            },
>            "fields": ["fldR3dERuDqosnxFY"],
>            "relations": [
>                {
>                    "from": "fldR3dERuDqosnxFY",
>                    "to": {
>                        "field": "email",
>                        "model_id": "YOUR-POSTGRES-MODEL-ID"
>                    }
>                }
>            ],
>            "connection_id": "YOUR-AIRTABLE-CONNECTION-ID"
>          }'

Step 3: Sync the data to HubSpot

The following request will create a sync that loads the joined data into HubSpot.

$curl --request POST \
>     --url https://app.polytomic.com/api/syncs \
>     --header 'X-Polytomic-Version: 2024-02-08' \
>     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}" \
>     --header 'content-type: application/json' \
>     -d '{
>     "name": "Customer data to HubSpot Sync",
>     "mode": "updateOrCreate",
>     "fields": [
>     {
>          "source": {
>          "model_id": "YOUR-POSTGRES-MODEL-ID",
>          "field": "first_name"
>          },
>          "target": "firstname"
>     },
>     {
>          "source": {
>          "model_id": "YOUR-POSTGRES-MODEL-ID",
>          "field": "last_name"
>          },
>          "target": "lastname"
>     },
>     {
>          "source": {
>          "model_id": "YOUR-AIRTABLE-MODEL-ID",
>          "field": "fldah5pg1Q9k3PmEH"
>          },
>          "target": "kloutscoregeneral"
>     },
>     {
>          "source": {
>          "model_id": "YOUR-STRIPE-MODEL-ID",
>          "field": "description"
>          },
>          "target": "subscription_plan"
>     }
>     ],
>     "schedule": {
>          "frequency": "continuous"
>     },
>     "identity": {
>          "source": {
>               "model_id": "YOUR-POSTGRES-MODEL-ID",
>               "field": "email"
>          },
>          "target": "email",
>          "function": "Equality"
>     },
>     "target": {
>          "connection_id": "YOUR-HUBSPOT-CONNECTION-ID",
>          "object": "contacts"
>     }
>}'