Forum Academy Marketplace Showcase Pricing Features

Airtable Sync mirror of our Bubble app database

I’m relatively new to Bubble, but have learned a lot about the platform in the past six months as I led a project at my organization to rebuild our proprietary purpose built customer portal application.

For context, the app has been around fifteen years through three previous iterations, and it was in need of an update. Bubble presented an intruiging alternative to the former paradigm of Ruby-on-Rails code with a Postgres backend deployed on a container-based cloud platform service. So we went for it, partnered with @RapidDev to rebuild our software, and the short version of the story is that we successfully launched to our customers a couple of months ago.

Not everything was fully functional at launch though. Super important customer-facing things worked, like contract and payment workflows. But other back end features still needed work, like reporting on the data and enabling our staff to build list exports.

Lack of visibility into the app’s data quickly became staff pain points, and we had some healthy debates around whether it’s a better approach to build out reporting features in the app or work to integrate the app data over to another platform with more specialized analytic capabilities.

Airtable is a familiar tool in our org, multiple staff teams have used it for various projects, including the team who manages this app using Airtable this year to collaborate on curating the customer data which was later loaded into the app. We built a first draft data integration early on in our Bubble app development; I created an Airtable base with a few key Bubble app tables linked together, and our contractor built Backend Workflow database triggers to push data over when the corresponding things were created or modified. The key from the very beginning was to make the first column in each Airtable base the unique id field from Bubble, so that when a Bubble list of things fields ouputs those other objects’ unique ids then Airtable properly utilizes the values to link them.

image

Our initial Airtable integration was useful at first, but hindered by big problems that piled up and led to it being abandoned quickly. Specifically, my colleagues did not like that deleted records in Bubble remained in Airtable, or that not all of the data they needed was being pushed over. I did not initially grasp how much manual effort quickly began to get put in to bridge those gaps.

Honestly, a month ago I didn’t think that syncing our Bubble data to Airtable was going to pan out. But I started again anyway with a new approach, with different specific goals in mind, and then a game changer came along.

Our v2 Airtable integration began with me exporting all fields in each table as CSV files from the Bubble database interface. Then in each exported CSV, move unique id to the first column and save. Then create a fresh Airtable base, create tables in that base based on imports of each file, go through each table and change field types so that link fields reference the unique id of the record in the other corresponding tables.

My goal was initially to create a template that could be started with to refine the data sent over by the workflow triggers. I expected to be able to pull over more useful data, and more importantly I hoped to leverage Airtable automations to send record updates through to Salesforce (another capability of our old app that hadn’t yet been replaced). Plus, Airtable generates this spiffy schema document to help visualize and understand our app data model:

After getting that all set up, it occurred to me that it might work better if our approach were different - if there was just a way to periodically sync these tables from Bubble. So I took a look around, and lo and behold there in the Airtable app store was a game changer called Data Fetcher.

The Data Fetcher app enabled me to configure sync requests for each table through the Bubble Data API, set up mappings from the Bubble fields to the Airtable fields, enable an option to delete any Airtable records not found in the API result, and schedule the series to run automatically going forward. The basic configuration looks like so:

This product now provides a baseline sync of our tables every day, continuously removing deleted records and ensuring that any discrepancies between the datasets are fixed. With that in place, I then revisited the Backend Workflow database triggers, and ended up expanding them. The triggers now send almost all record additions and updates through in real time, and have been really effective operating on top of the daily sync. My colleagues now have confidence in the data they see in Airtable, and are much better able to analyze and export the data mirrored there from our app.

For my part, I got that Salesforce integration working as well. With the integration to Airtable up-and-running (and with a Salesforce ID field populated in the table), it was then a relatively simple matter to create an Airtable last updated field that looks just for changes to the fields I’m pushing to the CRM, which triggers an Airtable automation to push a Salesforce record update.

It’s also worth noting that while I didn’t build this to be a two-way integration, it can totally be leveraged as such. I’ve built mechanisms which for instance utilize a checkbox field as a flag to send a record update back to Bubble, which when checked puts the record in a view, then have Zapier trigger off of a record in that view to update the thing in Bubble.

2 Likes

Interesting!

Can you expand on what sort of specialized analytic capabilities you were looking to incorporate that bubble couldn’t accomplish?

Nothing too specialized, just the ability to work effectively with the underlying app data.

Here’s a look at the reporting interface which was built to replicate the interface from the prior version of our app:

The idea here is that an admin user should be able to create a canned report in which they specify the filters and output columns. The back end Bubble workflow for this is very complex. Just getting our staff on the same page as the developers in terms of which fields needed to be available here, then building out all the sorkflow steps needed for each field would take many hours of work. And there are additional difficulties when the field output we need to incorporate resides several table joins away from the primary object.

Bubble could likely accomplish what we need, but I frankly think it would take significant resources to build a relatively crappy report building feature from scratch like this. Airtable on the other hand excels at providing a flexible and efficient interface. Getting a stable integration to pipe the data over was the hard part, once it’s there then working with the data is super easy.