Retool
Pardon the mess
We’ve made significant changes and improvements to the product since these docs were written. You’ll find that we’ve made the workflow in this playbook faster and easier.
We haven’t updated this particular playbook just yet, so it might not be very useful. Go to docs home to see the latest on how Sequin works.
Building a Retool app with Stripe
Let’s build a tool to manage Stripe subscriptions using Retool. The app will allow you to search through all your subscriptions, see all the associated current and upcoming invoices, charges, and products for your customer all in one, clean view. Then you can begin to take actions like exporting invoices or canceling subscriptions:
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, Sequin 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.
Stripe Setup
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).
Create test subscriptions
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.
Generate a restricted Stripe API key
Sequin requires an API key to sync all your Stripe data in real-time. As a best practice, we recommend providing Sequin 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 “sequin” will do just fine). Then provide this new key with the following permissions:
- READ access to everything
- READ & WRITE access to Webhooks
- No access to the CLI
You can get more details by reading Sequin’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 Sequin.
Sequin Setup
With your Stripe API key created, you can now setup Sequin to replicate Stripe to a Postgres database:
Step 1: Create or Login to your Sequin account.
Step 2: Connect your Stripe account to Sequin 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 Sequin database generated. Finally, click Create.
Step 4: Sequin 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 Sequin will only sync your TEST data for free, forever). You’ll be provided with credentials for you new database:
Retool Resource Setup
Now, add your Sequin 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 Sequin database. You can copy and paste these from Sequin. Then click the blue Create resource button.
Step 4: Retool will confirm that your resource was created. Click Back ro resources for now.
Retool App Setup
With Stripe successfully connected to Retool using Sequin, 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:
![Add title component](https://mintlify.s3-us-west-1.amazonaws.com/sequin/images/main/integrations/stripe/playbooks/retool-subs/012_add_title.gif)
This is the basic flow for adding new components to your app:
- Drag and drop the visual components into your app.
- Configure the data and interactions for the component.
- Adjust layout and polish the UI of the component.
You’ll follow this construction pattern as you build the rest of the app from here on out.
Searchable subscriptions
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.
Scaffold the UI
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:
Query for subscriptions
To add the underlying Stripe data to your app, you’ll simply query your Sequin 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:
select
customer.id as "cus_id",
customer.name,
customer.email,
subscription.id as "sub_id",
subscription.status,
subscription.current_period_end,
subscription.collection_method
from customer
left join subscription
on customer.id = subscription.customer_id;
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:
select
customer.id as "cus_id",
customer.name,
customer.email,
subscription.id as "sub_id",
subscription.status,
subscription.current_period_end,
subscription.collection_method
from customer
left join subscription
on customer.id = subscription.customer_id
where subscription.status is not null and ({{ !textinput1.value }} or customer.email::text ilike {{ "%" + textinput1.value + "%" }});
This WHERE
clause does two things:
- First, it checks if there is a value in the text input box. If there is nothing in the text input, then nothing happens.
- If there is text in the text input, then it uses Postgres
ilike
to search by the customer’s email.
When you click Save & Run you’ll now see that when you enter text into the text input you search your table:
![Search](https://mintlify.s3-us-west-1.amazonaws.com/sequin/images/main/integrations/stripe/playbooks/retool-subs/018_search.gif)
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:
select
customer.id as "cus_id",
customer.name,
customer.email,
subscription.id as "sub_id",
subscription.status,
subscription.current_period_end,
subscription.collection_method,
invoice.amount_due::numeric,
line_item.description
from customer
left join subscription
on customer.id = subscription.customer_id
left join invoice
on subscription.latest_invoice_id = invoice.id
left join invoice_line_item_map
on invoice.id = invoice_line_item_map.invoice_id
left join line_item
on invoice_line_item_map.line_item_id = line_item.id
where subscription.status is not null and ({{ !textinput1.value }} or customer.email::text ilike {{ "%" + textinput1.value + "%" }})
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.
Clean up the table
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.
Subscription 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.
Scaffold the UI
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
.
Query for customer and subscription data
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:
Status
### {{table1.selectedRow.data.status === "active" ? "Active" : table1.selectedRow.data.status === "canceled" ? "Canceled" : "Issue"}}
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.
Customer Email | Customer ID
{{table1.selectedRow.data.email}} | {{table1.selectedRow.data.cus_id}}
Here is a nice example of how you can easily concatenate string values in a text component.
Subscription Plan
{{table1.selectedRow.data.description}}
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 Sequin database. Then enter the following SQL statement:
SELECT
customer.id,
customer.created as "cus_created",
invoice.number,
invoice.amount_paid::int as "amount_paid",
invoice.period_start,
invoice.period_end,
invoice.created as "inv_created"
FROM customer
left join invoice
on customer.id = invoice.customer_id
where customer.id = {{table1.selectedRow.data.cus_id}}
order by invoice.number asc;
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:
let index = get_customer.data.amount_paid.length;
let toDollars = (num) => {
return (num / 100).toLocaleString("en-US", {
style: "currency",
currency: "USD",
});
};
return {
mrr: toDollars(get_customer.data.amount_paid[index - 1]),
spend: toDollars(get_customer.data.amount_paid.reduce((i, o) => i + o)),
growth: toDollars(
get_customer.data.amount_paid[index - 1] - get_customer.data.amount_paid[0]
),
};
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:
- MRR: is calculated as the amount paid on the most recent invoice (assuming all your customer only have subscription products)
- Spend: is a summation of all the revenue from the customer.
- Growth (i.e. Δ): is simply the difference in value of the most resent invoice compared to the first invoice.
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:
Since
{{moment(get_customer.data.cus_created[0]).format("MMMM DD, YYYY")}}
Here you use moment.js
to format the datetime
value returned from your get_customer
query.
MRR
{{calc_customer_stats.data.mrr}}
Spent
{{calc_customer_stats.data.spend}}
Growth (a.k.a Δ)
{{calc_customer_stats.data.growth}}
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 Sequin database.
Click to create another SQL query against your Sequin database and enter the following SQL statement:
SELECT
upcoming_subscription_invoice.next_payment_attempt as "next_invoice_date",
(upcoming_subscription_invoice.amount_due/100.00)::money as "next_invoice_amount"
from upcoming_subscription_invoice
where upcoming_subscription_invoice.subscription_id = {{table1.selectedRow.data.sub_id}};
Sequin 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
:
Next Invoice on **{{moment(get_next_invoice.data.next_invoice_date[0]).format("MMMM Do YYYY")}}** for **{{get_next_invoice.data.next_invoice_amount[0]}}**
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.
![Current state of the app](https://mintlify.s3-us-west-1.amazonaws.com/sequin/images/main/integrations/stripe/playbooks/retool-subs/030_current_state_of_app.gif)
Invoices, charges, and products
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.
Scaffold the UI
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.
Query for invoices, payments, and products
Open the bottom drawer and create a new query for your Sequin database. Enter the following SQL statement:
SELECT
invoice.id as "inv_id",
invoice.subscription_id as "sub_id",
invoice.number,
invoice.created,
invoice.status,
(invoice.amount_paid /100.00)::money as "amount",
invoice.hosted_invoice_url,
line_item.description as "line_item_description",
(price.unit_amount/100.00)::money as "unit amount",
price.recurring_interval,
product.name as "product",
charge.id as "charge_id",
charge.description as "charge_description",
(charge.amount/100.00)::money as "charge_amount",
charge.status as "charge_status",
charge.created as "charge_created"
from invoice
left join charge
on invoice.charge_id = charge.id
left join invoice_line_item_map
on invoice.id = invoice_line_item_map.invoice_id
left join line_item
on invoice_line_item_map.line_item_id = line_item.id
left join price
on line_item.price_id = price.id
left join product
on price.product_id = product.id
where invoice.subscription_id = {{table1.selectedRow.data.sub_id}}
group by invoice.id, charge.id, line_item.description, price.unit_amount, product.name, price.recurring_interval
order by invoice.number desc;
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
:
Clean up the list
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:
### 🧾 Invoice #: {{get_current_invoices.data.number[i]}}
This statement should look familiar to you with the exception of the [i]
at the end. So let’s step through this:
- The
###
is markdown notation for anH3
text format. - The double brackets then tell Retool we’ll be using JavaScript. The
get_current_invoices.data.number
pulls in the invoice number from theget_current_invoices
query. Because we pull in all the invoices in theget_current_invoices
query, this value is actually an array. - So finally, the
[i]
is extracting just one value from that array. The variablei
is used by theList View
component so that you request the same index from the array for every item in the samecontainer
in the list. So for instance, the firstcontainer
in the list will use index 0 and then the second will use index 1. So on and so forth.
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}}
:
Add interactions
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 Sequin and Retool.
View invoice
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:
- Event: Click
- Action: Go to URL
- URL:
{{get_current_invoices.data.hosted_invoice_url[i]}}
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.
Cancel subscription
So far, you’ve read all your Stripe data using Sequin. Sequin syncs are two-way, meaning you can mutate your Stripe data through your database.
For instance, to cancel a subscription, you’ll simply UPDATE
the subscription from your database, setting canceled_at = NOW()
.
Sequin will automatically apply this subscription request against the Stripe API.
Let’s step through it. To get started, let’s add a new query against our sync:
As you can see, our SQL query to cancel a subscription is simply:
UPDATE subscription
SET canceled_at = NOW()
WHERE id = {{table1.selectedRow.data.sub_id}};
We set canceled_at = NOW()
where our subscription id is equal 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.
Deleting a subscription is a big action. So click the Advanced tab and make the following changes:
- Select to Show a confirmation modal before running. This will ensure the user needs to confirm the action so they wield this power with caution.
- Then, set the Run triggered queries after to
1000
- this will ensure that your Sequin database is fully up-to-date before you refresh the data on the page to confirm the subscription has been deleted.
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:
- Edit the button’s text to read Cancel Subscription
- Create an event handler that triggers the
cancel_subscription
query - To improve the UX, disable the button if the subscription is already canceled by setting Disable when to
{{table1.selectedRow.data.status === "canceled"}}
- Finally, make the button red to let the user know this is dangerous.
Now, see your full Super Subscription Center working by searching for a subscription, evaluating it, and then deleting it:
![The finished app](https://mintlify.s3-us-west-1.amazonaws.com/sequin/images/main/integrations/stripe/playbooks/retool-subs/045_final_app.gif)
Next Steps
You now have an internal tool purpose built for your team to mange subscriptions.
Note all the things you didn’t need to build to get to this point.
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 Sequin, 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 (Sequin can put your Stripe data in your database). And of course, when you are ready, add your production API key to Sequin, change your resource in Retool, and start working with real customer subscriptions.
Was this page helpful?