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.

Prerequisites

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

• Hubspot
• NextJS
• Prisma

Overview

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.

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.

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:

Column	Description
customer_id	The unique ID for the customer whose data you are syncing
provider_name	hubspot
supaglue_contact_id	The original ID in the remote provider (Hubspot)
supaglue_deal_id	The original ID in the remote provider (Hubspot)
contact_id	The ID your application has assigned to this contact, used as a Foreign Key to the contacts table
deal_id	The ID your application has assigned to this deal, used as a Foreign Key to the deals table
last_modified_at	The 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({});
}

info

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

2. Sync Contacts and Deals along with their associations

Algorithm

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

Example Code (Contacts)

async function processContacts() {
  const newMaxLastModifiedAtMs = await step.run('Process hubspot contacts from supaglue', async () => {
    const supaglueContacts = await supagluePrisma.supaglueHubSpotContact.findMany({
      where: {
        supaglue_provider_name: data.provider_name,
        supaglue_customer_id: data.customer_id,
        supaglue_last_modified_at: {
          gt: lastMaxModifiedAt,
        },
      },
    });

    if (!supaglueContacts.length) {
      return undefined;
    }

    let maxLastModifiedAtMs = 0;

    // TODO: don't iterate one by one
    for (const supaglueContact of supaglueContacts) {
      const lastModifiedAtMs = supaglueContact.supaglue_last_modified_at.getTime();
      if (lastModifiedAtMs > maxLastModifiedAtMs) {
        maxLastModifiedAtMs = lastModifiedAtMs;
      }

      if (supaglueContact.supaglue_is_deleted) {
        await prisma.$transaction(async (tx) => {
          // delete record
          await tx.contact.deleteMany({
            where: {
              providerName: supaglueContact.supaglue_provider_name,
              customerId: supaglueContact.supaglue_customer_id,
              supaglueId: supaglueContact.supaglue_id,
            },
          });
          // delete relationships
          await tx.dealToContact.deleteMany({
            where: {
              providerName: supaglueContact.supaglue_provider_name,
              customerId: supaglueContact.supaglue_customer_id,
              supaglueContactId: supaglueContact.supaglue_id,
            },
          });
        });
        return maxLastModifiedAtMs;
      }
      const mappedData = supaglueContact.supaglue_mapped_data as Record<string, any>;
      const data = {
        providerName: supaglueContact.supaglue_provider_name,
        customerId: supaglueContact.supaglue_customer_id,
        supaglueId: supaglueContact.supaglue_id,
        firstName: mappedData.firstname,
        lastName: mappedData.lastname,
        email: mappedData.email,
        phone: mappedData.phone,
        lastModifiedAt: supaglueContact.supaglue_last_modified_at,
      };

      // upsert relationships
      // TODO: we should have a standard way to get associations for
      // hubspot-like providers
      const rawData = supaglueContact.supaglue_raw_data as Record<string, any>;
      const relatedSupaglueDealIds = (rawData.associations?.deals as string[] | undefined) ?? [];
      const uniqueRelatedSupaglueDealIds = [...new Set(relatedSupaglueDealIds)];

      await prisma.$transaction(async (tx) => {
        // upsert record
        await tx.contact.upsert({
          create: data,
          update: data,
          where: {
            providerName_customerId_supaglueId: {
              providerName: supaglueContact.supaglue_provider_name,
              customerId: supaglueContact.supaglue_customer_id,
              supaglueId: supaglueContact.supaglue_id,
            },
          },
        });

        await tx.dealToContact.deleteMany({
          where: {
            supaglueContactId: supaglueContact.supaglue_id,
            customerId: customer_id,
            providerName: provider_name,
          },
        });

        await tx.dealToContact.createMany({
          data: uniqueRelatedSupaglueDealIds.map((supaglueDealId) => ({
            providerName: supaglueContact.supaglue_provider_name,
            customerId: supaglueContact.supaglue_customer_id,
            supaglueDealId: supaglueDealId,
            supaglueContactId: supaglueContact.supaglue_id,
            lastModifiedAt: supaglueContact.supaglue_last_modified_at,
          })),
          skipDuplicates: true,
        });
      });
    }
    return maxLastModifiedAtMs;
  });

  return newMaxLastModifiedAtMs;
}

Example Code (Deals)

async function processDeals() {
  const newMaxLastModifiedAtMs = await step.run('Process hubspot deals from supaglue', async () => {
    const supaglueDeals = await supagluePrisma.supaglueHubSpotDeal.findMany({
      where: {
        supaglue_provider_name: provider_name,
        supaglue_customer_id: customer_id,
        supaglue_last_modified_at: {
          gt: lastMaxModifiedAt,
        },
      },
    });

    if (!supaglueDeals.length) {
      return undefined;
    }

    let maxLastModifiedAtMs = 0;

    // TODO: don't iterate one by one
    for (const supaglueDeal of supaglueDeals) {
      const lastModifiedAtMs = supaglueDeal.supaglue_last_modified_at.getTime();
      if (lastModifiedAtMs > maxLastModifiedAtMs) {
        maxLastModifiedAtMs = lastModifiedAtMs;
      }

      if (supaglueDeal.supaglue_is_deleted) {
        await prisma.$transaction(async (tx) => {
          // delete record
          await tx.deal.deleteMany({
            where: {
              providerName: supaglueDeal.supaglue_provider_name,
              customerId: supaglueDeal.supaglue_customer_id,
              supaglueId: supaglueDeal.supaglue_id,
            },
          });
          // delete relationships
          await tx.dealToContact.deleteMany({
            where: {
              providerName: supaglueDeal.supaglue_provider_name,
              customerId: supaglueDeal.supaglue_customer_id,
              supaglueDealId: supaglueDeal.supaglue_id,
            },
          });
        });
      } else {
        const mappedData = supaglueDeal.supaglue_mapped_data as Record<string, any>;
        const data = {
          providerName: supaglueDeal.supaglue_provider_name,
          customerId: supaglueDeal.supaglue_customer_id,
          supaglueId: supaglueDeal.supaglue_id,
          name: mappedData.dealname,
          amount: mappedData.amount ? parseInt(mappedData.amount) : null,
          lastModifiedAt: supaglueDeal.supaglue_last_modified_at,
        };

        // upsert relationships
        // TODO: we should have a standard way to get associations for
        // hubspot-like providers
        const rawData = supaglueDeal.supaglue_raw_data as Record<string, any>;
        const relatedSupaglueContactIds = (rawData.associations?.contacts as string[] | undefined) ?? [];
        const uniqueRelatedSupaglueContactIds = [...new Set(relatedSupaglueContactIds)];

        await prisma.$transaction(async (tx) => {
          // upsert record
          await tx.deal.upsert({
            create: data,
            update: data,
            where: {
              providerName_customerId_supaglueId: {
                providerName: supaglueDeal.supaglue_provider_name,
                customerId: supaglueDeal.supaglue_customer_id,
                supaglueId: supaglueDeal.supaglue_id,
              },
            },
          });
          await tx.dealToContact.deleteMany({
            where: {
              supaglueDealId: supaglueDeal.supaglue_id,
              customerId: customer_id,
              providerName: provider_name,
            },
          });

          await tx.dealToContact.createMany({
            data: uniqueRelatedSupaglueContactIds.map((supaglueContactId) => ({
              providerName: supaglueDeal.supaglue_provider_name,
              customerId: supaglueDeal.supaglue_customer_id,
              supaglueDealId: supaglueDeal.supaglue_id,
              supaglueContactId,
              lastModifiedAt: supaglueDeal.supaglue_last_modified_at,
            })),
            skipDuplicates: true,
          });
        });
      }
    }

    return maxLastModifiedAtMs;
  });

  return newMaxLastModifiedAtMs;
}

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.

note

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

Algorithm

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.

Example Code

async function reconcileAssociations() {
  const relationships = await prisma.dealToContact.findMany({
    where: {
      AND: [
        {
          providerName: data.provider_name,
          customerId: data.customer_id,
        },
        {
          OR: [
            {
              dealId: null,
            },
            {
              contactId: null,
            },
          ],
        },
      ],
    },
  });

  if (!relationships.length) {
    return;
  }

  const uniqueSupaglueDealIds = [...new Set(relationships.map((r) => r.supaglueDealId))];
  const uniqueSupaglueContactIds = [...new Set(relationships.map((r) => r.supaglueContactId))];

  const deals = await prisma.deal.findMany({
    where: {
      providerName: data.provider_name,
      customerId: data.customer_id,
      supaglueId: {
        in: uniqueSupaglueDealIds,
      },
    },
    select: {
      id: true,
      supaglueId: true,
    },
  });
  const contacts = await prisma.contact.findMany({
    where: {
      providerName: data.provider_name,
      customerId: data.customer_id,
      supaglueId: {
        in: uniqueSupaglueContactIds,
      },
    },
    select: {
      id: true,
      supaglueId: true,
    },
  });

  const dealIdBySupaglueId = new Map<string, string>();
  for (const deal of deals) {
    dealIdBySupaglueId.set(deal.supaglueId, deal.id);
  }
  const contactIdBySupaglueId = new Map<string, string>();
  for (const contact of contacts) {
    contactIdBySupaglueId.set(contact.supaglueId, contact.id);
  }

  for (const relationship of relationships) {
    const dealId = dealIdBySupaglueId.get(relationship.supaglueDealId);
    const contactId = contactIdBySupaglueId.get(relationship.supaglueContactId);
    const res = await prisma.dealToContact.update({
      where: {
        providerName_customerId_supaglueDealId_supaglueContactId: {
          providerName: provider_name,
          customerId: customer_id,
          supaglueDealId: relationship.supaglueDealId,
          supaglueContactId: relationship.supaglueContactId,
        },
      },
      data: {
        dealId: dealId ?? undefined,
        contactId: contactId ?? undefined,
      },
    });
  }
}

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.