Syncing Associations using Postgres IDs

When managing HubSpot associations through Stacksync, the most robust approach is to decouple the creation of the association from the generation of HubSpot's internal IDs. By using your internal Postgres IDs as the "stable join key," you ensure that associations are created deterministically and without race conditions.

We recommend creating associations using internal Postgres IDs rather than waiting for HubSpot IDs.

1. Atomic Transactions:

   When a new relationship is created in your application (e.g., a Deal is linked to a Client), insert the Client, the Deal, and their Association into your Postgres database within a single atomic transaction.

   This ensures that the relationship is recorded immediately in your system, even before Stacksync has finished syncing the individual records to HubSpot.

1. ID Propagation:

   Once Stacksync creates the records in HubSpot and writes the hubspot_id back to your deals and clients tables, you can propagate those IDs into your associations table.

Sync options

Depending on your requirements for latency, you can choose between two implementation methods:

1. Near-real-time (Triggers):

   Use Postgres triggers to monitor updates on your deals and clients tables. As soon as both records in a relationship have been assigned a HubSpot ID, the trigger executes the sync query to update the association table.

   1. Pros: Low latency; associations are ready for HubSpot almost instantly.

   2. Cons: Higher database overhead and complexity.

1. Batch (Scheduled Job): Run the sync query below as a scheduled job (e.g., a cron job) every 1 minute. This backfills missing HubSpot IDs in the associations table in bulk.

   1. Pros: Simpler to implement and monitor.

   2. Cons: Up to a 60-second delay in association syncing.

Implementation: The Sync Query

Use the following SQL query to bridge your internal Postgres IDs with HubSpot IDs. This query identifies associations that lack HubSpot IDs and pulls them from the successfully synced deals and clients tables.

UPDATE associations_deal_client
SET
  hubspot_deal_id = d.id,
  hubspot_client_id = c.id
FROM deals d
JOIN clients c
  ON associations_deal_client.postgres_deal_id = d.postgres_id
 AND associations_deal_client.postgres_client_id = c.postgres_id
WHERE associations_deal_client.hubspot_deal_id IS NULL
   OR associations_deal_client.hubspot_client_id IS NULL;

By using postgres_id as the join key, you guarantee that the association is pinned to the correct records. Once the hubspot_deal_id and hubspot_client_id are populated in the associations_deal_client table, Stacksync can pick them up and create the corresponding association in HubSpot.

If you have any questions or run into any blockers, reach out anytime at [email protected] and the Stacksync team will be happy to help.