How to build a Super Subscription Center in Retool using Stripe and Sync Inc.

While Retool does come with a Stripe API integration, configuring a Retool app to search and retrieve data through the Stripe API is tedious. You'll still need to handle pagination, caching, and running multiple, sequential API calls.

Luckily, Sync Inc replicates all your Stripe data to a Postgres database so you can use Retool's first class support for SQL to work with your Stripe data. In this tutorial you'll learn how this all works together to make building your Stripe subscription tools easy.

You'll need a Stripe account that contains active subscriptions to build this Retool app. If you don't have any active subscriptions in your LIVE Stripe account, you can easily add some fake ones in your TEST account (in fact - building a staging version of this app using your Stripe account in TEST mode is highly recommended since you're working with sensitive data).

To get going, add a couple test subscriptions to your Stripe account:

Step 1: Login to your Stripe dashboard and put your account into TEST MODE by flipping the View test data switch.

Step 2: Create a recurring product by going to the product page, clicking + Add Product, and filling out the form to create a new product. Double check that the product is configured to be Recurring:

Step 3: Finally, create a new customer with a subscription to the product you just created. To do so, click the Actions button on the customer page and select Create Subscription:

Repeat the process by creating a couple more customers with recurring subscriptions.

Sync Inc requires an API key to sync all your Stripe data in real-time. As a best practice, we recommend providing Sync Inc with a restricted API key. To generate a restricted API key:

Step 1: Navigate to the Stripe API keys page by selecting Developers in the left navigation and clicking API keys. Then click the + Create restricted key button.

Step 2: Give your key a name (something like "sync inc" will do just fine). Then provide this new key with the following permissions:

You can get more details by reading Sync Inc's reference for Stripe.

Step 3: Finally, click the Create Key button and keep this new restricted API key handy as you move on to set up Sync Inc.

With your Stripe API key created, you can now setup Sync Inc to replicate Stripe to a Postgres database:

Step 1: Create or Login to your Sync Inc account.

Step 2: Connect your Stripe account to Sync Inc by going through the tutorial or clicking the Add database button and selecting Stripe.

Step 3: You'll be prompted to enter your Stripe API key. Then, in the destination section, select to have a New Sync Inc database generated. Finally, click Create.

Step 4: Sync Inc will immediately provision you a Postgres database and begin syncing all your Stripe data to it (if you're using a TEST API key, then Sync Inc will only sync your TEST data for free, forever). You'll be provided with credentials for you new database:

Now, add your Sync Inc database to Retool like any other Postgres database:

Step 1: In a new tab, log into your Retool dashboard. In the top menu bar click Resources and then the blue Create New button.

Step 2: Select Postgres from the list of resource types.

Step 3: Enter the name for your resource (i.e. "Stripe") and then enter the Host, Port, Database name, Database username, and Password for your Sync Inc database. You can copy and paste these from Sync Inc. Then click the blue Create resource button.

Step 4: Retool will confirm that your resource was created. Click Back ro resources for now.

With Stripe successfully connected to Retool using Sync Inc, we are ready to build an app that shows all your subscriptions, invoices, and charges in one clean view.

First, get the app set up in Retool.

Step 1: On the Retool app page, click the blue Create new button and select Create a blank app:

Step 2: Give your app a name. Something like Super Subscription Center will work just fine and then click Create app:

Step 3: You'll now see a blank Retool app in edit mode. To start building the app, drag and drop a text field into the header. Then, in the inspector drawer on the right, enter # Super Subscription Center as the value to give your app a name:

This is the basic flow for adding new components to your app:

You'll follow this construction pattern as you build the rest of the app from here on out.

With all the foundations in place, you are ready to start building the core functionality of your app - starting with a searchable table that shows all the current subscriptions.

Drag and drop the components that will make up this section of the app onto the canvas:

First, drag a Container component onto the canvas. Resize it to cover about half the width of the app.

Then drag and drop a text input field and place it at the top of the container. This will be your search bar. In the inspector on the right, edit the component's Label to be Email and then to make it look nice select search as the Left icon:

Drag and drop a table component under your newly created search bar and position it to fill up the container. At the end, your app will look something like this:

To add the underlying Stripe data to your app, you'll simply query your Sync Inc database using SQL. To step into this paradigm, let's add a simple set of data with search. Then, we'll refine the query to pull in the exact data you need.

Step 1: Open up the bottom panel and create new query by clicking + New and selecting Resource query:

Step 2: Select the Stripe Postgres database you created earlier as the resource, then enter the SQL statement below:

When you click the Preview button you'll see that this query pulls in key details about your customers as well as the customer's associated subscriptions via a JOIN with the subscription table.

Step 3: This query looks good for now, so click the Save & Run button and then name the query get_subscriptions by clicking on the query in the list on the left.

Step 4: To pull the data from get_subscriptions into the table in your app, open the right inspector, select your table in the canvas, and then in the data field enter {{get_subscriptions.data}}. The double brackets (i.e. {{}}) indicate that you are using JavaScript in Retool. Then, the get_subscriptions.data is retrieving the data from your query.

You'll immediately see the data from your query populate your table:

Step 5: You're now querying data from Stripe (using SQL!) and populating that data to your table in the UI of your app. Now, add search. To do so, add the following WHERE clause to your get_subscriptions query:

This WHERE clause does two things:

When you click Save & Run you'll now see that when you enter text into the text input you search your table:

Step 6: In addition to customer and subscription data, you also want to see some invoicing data. Specifically, you need see the value of the subscription and what products are included. To do so, update your get_subscriptions query to add the following fields:

This will pull in the invoice.amount_due and line_item.description details you need by joining to the invoice and line_item table.

You've now queried for all your data and set up search using SQL.

As a last step, adjust the UI so it shows the data effectively.

Step 1: Select the table and open up the right inspector.

Step 2: Simplify the table by removing data that is only helpful to the app - but not your user. In this case, you can drop the cud_id and sub_id columns as well as the subscription end column by clicking the eye icon.

Step 3: Rename and format each of the columns in your table by select each column in the inspector, formatting the name of the column, and aligning the data type. For instance, for the amount column you can give the column a friendly name like Amount and then for the data type select USD (cents):

You now have a searchable table that returns data to help you evaluate subscriptions. Now, you'll bring in the details.

After you select a subscription in the table you just created, you'll want to see the details of the subscription on the right side of your app. Let's start with the customer and subscription details card in the top right:

For this component, you'll repeat the same construction pattern by first scaffolding the UI, connecting the data, and then cleaning up the interface.

This component is simply a container of text fields that present data about the customer and their subscription in more detail. To lay out the UI, drag and drop a container in the top right portion of the app and add the following placeholder text fields:

To format the text appropriately, use Markdown. For instance, for Customer Name and Status you can format the text as H3 by entering the value as ### Customer Name.

Now, replace the placeholder text with real data from Stripe.

You can populate the first several fields in the subscription card with data already available in the table to the left. All you need to do is pull those values into the text components.

Starting with the Customer Name text component, select the component and in the inspector on the right enter the value as ### {{table1.selectedRow.data.name}}:

This tiny JavaScript statement pulls the name value from whatever row is selected in table1. For your end user, this means the text box will immediately show the name of any customer they select in the table.

You can repeat this same data access pattern for the next several fields:

For status, you'll again pull the value from the selected row in table and utilize a ternary operator to show a user-friendly value for the status of the subscription.

Here is a nice example of how you can easily concatenate string values in a text component.

For the subscription plan you can simply return the description for the selected row in table1 as normal.

You've now populated as much of the data in the subscription card as you can from the existing data in Table1. For the rest of the data in this card, you'll need to write new queries.

Turn your attention to the Since:, Spent:, MRR:, and Δ: fields:

For these fields, you want to pull in specific details about the customer so you can easily see how valuable the customer is. To get this data, you'll need to write a new query (get_customer) as well as a helper function (calc_customer_stats).

Starting with the get_customer query, open the bottom drawer and click to create a new query against your Sync Inc database. Then enter the following SQL statement:

This query pulls in some more information about the customer and then performs a JOIN with the invoice table to pull in all their billing history. The WHERE clause at the end filters the data for just the one customer selected in Table1. Last, the ORDER BY clause allows us to sort the results to make working with the data easier in the helper functions.

Click the Save & Run button and then name the query get_customer:

You now have the raw data required to calculate the rest of the fields. While you could use some additional SQL to calculate the specific values for each field, you'll use a JavaScript helper function here.

To build your helper function, click to create a new query and select JavaScript Query:

For this helper function, you want to iterate through the array of data returned from your get_customer query to calculate some metrics:

This helper function does two things. First, it formats numbers into currency strings using the toDollars() function.

Next, it calculates the metrics you need as follows:

Click Save and then name the query calc_customer_stats.

You want this helper function to run anytime the get_customer query is run. So as a last step, open the get_customer query and have the calc_customer_stats query trigger on success:

With your metrics calculated you can now add these values to the subscription card:

Here you use moment.js to format the datetime value returned from your get_customer query.

There is just one more field to add to your subscription card: details around when the customer will receive their next invoice.

This last field requires one additional query that pulls in the upcoming_invoice details for the customer. Luckily, this data lives in your Sync Inc database.

Click to create another SQL query against your Sync Inc database and enter the following SQL statement:

Sync Inc maintains tables that show the temporary state of upcoming objects you would otherwise need to use the Stripe API for. In this case, you're pulling in the time period and amount of the next invoice associated to the subscription.

Click the Save & Run button and name this query get_next_invoice:

Now, pull this data into your app's interface by updating the last remaining value in your subscription card with the data retrieved in get_next_invoice:

To make this value stand out, format the text to be green by using the style options in the inspector:

The subscription card is now complete. When a user selects a subscription in the table, the details of the customer including key metrics and upcoming invoice details are immediately shown. All with SQL.

Now, you'll round out the app by showing the customer's prior invoices, charges, and products.

You'll be able to pull in all this data in one additional SQL query and then display it in your app using Retool's List View component.

Keeping with the process, first you'll scaffold the UI components.

The List View component allows you to show a list of items. It can dynamically show more or fewer items depending on how the underlying data changes.

Drag and drop the List View component onto your app and then add a Container component to the top of the list. As soon as you drop the Container into the List View component you'll see it's duplicated three times. This quickly gives you a sense of how the List View component works by showing a new UI component for each item in an array of data.

You'll make the List View component dynamic later, but for now you're just scaffolding the front-end. So to make things easier select the List View component and in the inspector adjust the Number of rows to one for the time being.

Now, add a couple more UI components to the Container you created:

The only flourish here (in addition to the emojis 👏) is the styling on the container with the payment information. You can do the same by selecting the container and editing the style as you did previously with the green text.

Open the bottom drawer and create a new query for your Sync Inc database. Enter the following SQL statement:

This SQL query performs a SELECT across several tables that you JOIN together in order to pull in invoices, line_items, prices, products, and charges. Then, you use the WHERE statement to filter the data down to just the one subscription you have selected in Table1.

Click to Save & Run the query and name it get_current_invoices:

You'll now link the data from your get_current_invoices query to your UI components.

Select the Invoice # placeholder and replace the value with:

This statement should look familiar to you with the exception of the [i] at the end. So let's step through this:

After you enter the value, you should see your text component update correctly:

To finish the job, you now need to match the value from the get_current_invoices query to the remaining values in the UI:

UI Text Component Value Invoice Date and Time {{moment(get_current_invoices.data.created[i]).format("MMM DD, YYYY - hh:mm A")}} Invoice Status #### {{get_current_invoices.data.status[i] === "paid" ? "🟢 Paid" : "⚠️ Issue"}} Invoice Amount {{get_current_invoices.data.amount[i]}} Invoice Plan {{get_current_invoices.data.line_item_description[i]}} Payment ##### Payment → {{get_current_invoices.data.charge_amount[i]}} Payment Status ##### {{get_current_invoices.data.charge_status[i] === "succeeded" ? "✅ Success" : "⚠️ Issue"}} Payment Date and Time {{moment(get_current_invoices.data.charge_created[i]).format("MMM DD, YYYY - hh:mm A")}} Payment Description {{get_current_invoices.data.charge_description[i]}}

You'll now have a clean representation of your customer's invoices:

Finally, you want the number of list items displayed in your list to change depending on the number of invoices associated to a subscription. To do so, select the List View component and change the Number of rows in the inspector to {{get_current_invoices.data.inv_id.length}}:

Your Super Subscription Center is now pulling in all the data you need to find a customer's subscription and evaluate it. Now, add two interactions to your app to start exploring how to mutate your Stripe data using Sync Inc and Retool.

To get a hang of interactions, let's start with a simple button that allows a user to see an invoice.

First, drag and drop a button into the container of one of your invoice items.

Then, in the inspector change the text of the button to read View Invoice.

Finally, to make the button trigger an event click the + New link in the Event Handlers section of the inspector. Configure the event as follows:

Since you are already pulling in the URL for the invoice in the get_current_invoices query, you just need to associate this URL to the button.

With the event configured, click the View Invoice button you just created and you'll see the invoice load in a new tab.

So far, you've read all your Stripe data using Sync Inc. Sync Inc is a read-only database, so to mutate your Stripe data, you'll use the Stripe API.

For instance, to cancel a subscription, you'll simply make a DELETE request against the Stripe API.

Any mutation you make will then propagate to your Sync Inc database in about 1 second.

Let's step through it. To get started, add a new Stripe API resource to Retool:

Step 1: Open up the bottom drawer and create a new resource query. In the Resource dropdown, select Create new resource:

Step 2: You'll be taken to Retool's resource page. Select to create a new Stripe resource.

Step 3: Give the new resource a name (something like Stripe API) and enter your API key. Here, you'll want to generate a new restricted API key for Retool that includes WRITE permissions as well.

Step 4: Click Create resource and then navigate back to your app.

Back in your Super Subscription Center app, open the bottom drawer, and click to create a new resource.

Select the Stripe API resource you just created and then select the DELETE /v1/subscriptions/{subscription_exposed_id} as the operation.

You want to delete the subscription that the user has selected, so in the PATH section, set the subscription_exposed_id to {{table1.selectedRow.data.sub_id}}.

After the user deletes a subscription, you also want to update the subscription's status in the app to close the feedback loop and let the user know the subscription has indeed been canceled. To do so, set the get_subscriptions query to trigger when your Stripe API call succeeds.

All together, your Stripe API query to delete subscriptions will look like this:

Deleting a subscription is a big action. So click the Advanced tab and make the following changes:

With your advanced settings in place, click to Save the query and name it cancel_subscription.

Now, drag a button into the subscription card and configure it to trigger the cancel_subscription query:

Now, see your full Super Subscription Center working by searching for a subscription, evaluating it, and then deleting it:

You now have an internal tool purpose built for your team to manage subscriptions!

With Retool, you didn't need to create a React application, worry about deployments, authentication, or even fuss with HTML, CSS, and boilerplate JavaScript. And with Sync Inc, you were able to pull in all your Stripe data in just a couple SQL queries. No need to created nested API call, deal with pagination, or fuss with client side search logic.

From here, you can continue to customize your app. Bring in data from your production database and join it to Stripe seamlessly (Sync Inc can put your Stripe data in your database). And of course, when you are ready, add your production API key to Sync Inc, change your resource in Retool, and start working with real customer subscriptions.