How to build internal tools on Stripe with SQL

We're all building internal tools on Stripe. It would be great if we could build them faster so our customers and business are happier.

So 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:

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:

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.

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 an H3 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 the get_current_invoices query. Because we pull in all the invoices in the get_current_invoices query, this value is actually an array.

So finally, the [i] is extracting just one value from that array. The variable i is used by the List View component so that you request the same index from the array for every item in the same container in the list. So for instance, the first container 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 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 Sequin 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:

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:

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.

Categories:

Sql Postgres

Tags:

Stripe Sql Tutorial Postgres

Websites To Learn Programming For Free

C# For Beginners - Lesson 6: Strings

Laravel Fluent Str - 48 Methods [🚀 10 MINUTES!]

How to Safely Render Markdown From a React Component

Your Essential Guide To Map Built-in Object In Javascript

Operating Systems: Introduction

How to cook up a powerful React async component using hooks (and no Suspense)

PostgreSQL prepared statements in PL/pgSQL

How to build internal tools on Stripe with SQL

Categories:

Tags:

Related Posts