Skip to main content

Build a common schema

Suppose your application integrates with multiple third-party Providers in one category, like Salesforce, Hubspot, and Pipedrive in CRM. In that case, you likely want to read their data in a unified way. For example, for reading contacts from these CRMs, use one table in your database instead of three tables.

This tutorial will teach you how to structure your transformation code and how to build a common schema using Salesforce as an example.


This tutorial assumes you have already gone through Supaglue's Quickstart, have read our documentation on listening for webhooks, and have read our documentation on pagination.

We also will use the following technologies:

  • Next.js
  • Prisma
  • Postgres


Our example application has the concepts of users, opportunities, leads, accounts, and contacts and you want to create a table for each to store data from Salesforce, Hubspot, and Pipedrive. The schemas for these models (using Prisma syntax) look like the following:

model User {
id String @id
customerId String @map("_supaglue_customer_id")
providerName String @map("_supaglue_provider_name")
name String
email String
isActive Boolean @map("is_active")
createdAt DateTime? @map("created_at")
updatedAt DateTime? @map("updated_at")

@@unique([customerId, id, providerName])

A User is a person who can log into your customer's third-party Provider account.


Trigger your transformation code after it is synced to Supaglue-managed tables by listening for the sync.complete webhook. For more information, please see our documentation on listening for webhooks.

Transforming data

You can map data from each provider to your common schema using two approaches:

  • SQL selects, aliasing, and functions for simple transformations like renaming fields and format conversion
  • Code for more complex transformations like operating on objects and arrays

In the examples below, we rename fields using SQL aliasing and use code for more complex transformations.

Upon a sync.complete webhook event:

  1. Read all customer/provider/object records since the last high watermark
  2. For each record, transform it using the mapper
  3. Upsert the transformed record into your database table
  4. Set the new high watermark

Please refer to the Pagination tutorial for more details on pagination over newly-synced records.

More information

You can try out a working example of this tutorial by cloning the common-model-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.