Skip to main content

Association Bridge Tables

This tutorial will show how to populate bridge tables between your database tables when syncing records from Supaglue-managed tables. We'll use Hubspot Contacts and Deals as an example.


This tutorial assumes you have gone through Supaglue's Quickstart and will use the following technologies:

  • Hubspot
  • NextJS
  • Prisma


In your application, you may have chosen to normalize your database tables, and created bridge tables to represent many-to-many relationships between them. For example, you may have a contact table and a deal table, and you may want to associate a contact with multiple deals and vice versa using a contacts_deals bridge table.

bridge table diagrambridge table diagram

Some third-party providers, like Hubspot, do not expose their data in a many-to-many way. For example, Hubspot's Contacts and Deals are separate objects and return (one-to-many) associations as member fields on the objects themselves.

hubspot associationshubspot associations

This tutorial will review how to sync associations from third-party providers like Hubspot into your database.

Write objects and associations

We will go over how to do the following:

  1. Listen for Supaglue's sync.complete webhook event to trigger a workflow
  2. Write Supaglue's data into three tables:
    • contacts table containing transformed Hubspot Contacts data
    • deals table containing transformed Hubspot Deals data
    • contacts_deals table, which acts as a bridge table for the many-to-many relationship
  3. (Optional) Reconcile the bridge table entries to write foreign keys between contacts_deals and contacts/deals tables

The bridge table will contain the following columns:

customer_idThe unique ID for the customer whose data you are syncing
supaglue_contact_idThe original ID in the remote provider (Hubspot)
supaglue_deal_idThe original ID in the remote provider (Hubspot)
contact_idThe ID your application has assigned to this contact, used as a Foreign Key to the contacts table
deal_idThe ID your application has assigned to this deal, used as a Foreign Key to the deals table
last_modified_atThe timestamp this row was last modified
  • Non-null constraint on [supaglue_contact_id, supaglue_deal_id]
  • Unique constraint on [customer_id, provider_name, supaglue_contact_id, supaglue_deal_id]
  • Unique constraint on [contact_id, deal_id]

1. Listen for Supaglue's webhook event

Supaglue emits different kinds of notification webhooks. For writing objects and bridge tables, we want to process the sync.complete event, which Supaglue emits after a full or incremental sync completes.

export async function POST(request: NextRequest) {
const data = await request.json();

if (data.webhook_event_type !== 'sync.complete') {
return NextResponse.json({});

if (data.result !== 'SUCCESS') {
return NextResponse.json({});

// call your webhook handler here

return NextResponse.json({});

Please refer to the consuming webhooks tutorial for more details on how to do it.

2. Sync Contacts and Deals along with their associations

Upon a sync.complete webhook event:

  1. Read all object records since the last high-water mark
  2. For each record:
    • Upsert the object record into your database table
    • Upsert each object record's association entries into your bridge table, e.g. contacts to deals relationships
  3. Set the new high watermark

3. (Optional) Reconcile the bridge table entries

In some cases, you may want only to have an entry in the bridge table if and only if both sides of the relationship exist (i.e. if you want a Foreign Key from the bridge table to either side of the object tables). Because the Contact and Deal syncs happen asynchronously, there may be cases where a Contact with relationships to a Deal has not synced.

To deal with this, there may need to be a reconciliation step where you first write the bridge table entries (as you did in the previous step), and then validate those entries if and only if both sides of the relationship exist in the primary object tables you have written to.

As a refresher, in our example, the bridge table contacts_leads consists of:

  • {supaglue_contact_id, supaglue_deal_id}: These are the non-nullable columns we wrote in the previous step.
  • {contact_id, deal_id}: These are foreign key columns referencing the primary keys of the contact and deal tables.

The two tuples above may or may not occupy the same ID namespace, depending on how you data model your application.

Immediately after the previous step (i.e. syncing both objects and the initial bridge table entries):

  1. Find all entries in the bridge table for which contact_id or deal_id is NULL. Grab the corresponding supaglue_contact_id and/or supaglue_deal_id.
  2. See if the contact and/or deal record corresponding to the supaglue_contact_id / supaglue_deal_id exists.
  3. If so, write the contact_id / deal_id for those objects into the bridge table.

More information

You can try out a working example of this tutorial by cloning the associations-bridge-table-example repository and following the instructions in the README.

You'll want to customize the code from this tutorial to fit your specific application data model, use case, performance, and reliability requirements.