How to build internal tools on Stripe with SQL
Tags: stripe, sql, tutorial, postgres
Published: 2021-07-01 20:29:34

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:

![Retool Stripe Subscription Tool](https://docs.sequin.io/assets/retool-subs/001_subscription_center_end_state.png)

While Retool does come with a [Stripe API integration](https://docs.retool.com/docs/stripe-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](https://dashboard.stripe.com/dashboard) and put your account into _TEST MODE_ by flipping the **View test data** switch.

![Activate Stripe Test Mode](https://docs.sequin.io/assets/retool-subs/002_turn_on_test_mode.png)

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

![Create recurring product](https://docs.sequin.io/assets/retool-subs/003_recurring_product.png)

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

![Create recurring product](https://docs.sequin.io/assets/retool-subs/004_create_subscription.png)

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](https://stripe.com/docs/keys). To generate a restricted API key:

**Step 1:** Navigate to the [Stripe API keys page](https://dashboard.stripe.com/test/apikeys) by selecting **Developers** in the left navigation and clicking **API keys**. Then click the **+ Create restricted key** button.

![Create restricted API key](https://docs.sequin.io/assets/retool-subs/005_create_restricted_api_key.png)

**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](https://docs.sequin.io/stripe/reference#create-a-stripe-api-key).

**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](https://app.sequin.io/signup).

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

![Sync Ind DB Created](https://docs.sequin.io/assets/retool-subs/007_sync_db_created.png)

## Retool Resource Setup

Now, add your Sequin database to Retool [like any other Postgres database](https://docs.retool.com/docs/connecting-your-database):

**Step 1:** In a new tab, log into your [Retool dashboard](https://retool.com/). 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.

![Enter DB credentials](https://docs.sequin.io/assets/retool-subs/009_enter_db_cred.png)

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

![Create new app](https://docs.sequin.io/assets/retool-subs/010_create_blank_app.png)

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

![Give your app a name](https://docs.sequin.io/assets/retool-subs/011_name_app.png)

**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://docs.sequin.io/assets/retool-subs/012_add_title.gif)

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

1. **Drag and drop** the visual components into your app.
2. **Configure** the data and interactions for the component.
3. **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**:

![Add search bar](https://docs.sequin.io/assets/retool-subs/013_add_search_bar.png)

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:

![Add search bar](https://docs.sequin.io/assets/retool-subs/014_add_table.png)

### 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**:

![Add new query](https://docs.sequin.io/assets/retool-subs/015_add_new_query.png)

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

```sql
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.

![Preview query](https://docs.sequin.io/assets/retool-subs/016_preview_query.png)

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

![Add data to table](https://docs.sequin.io/assets/retool-subs/017_add_data_to_table.png)

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

```sql
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://docs.sequin.io/assets/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:

```sql
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)`:

![Add data to table](https://docs.sequin.io/assets/retool-subs/019_edit_columns.png)

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:

![Subscription Details Section](https://docs.sequin.io/assets/retool-subs/020_subscription_details_section.png)

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:

![Scaffold the UI](https://docs.sequin.io/assets/retool-subs/021_scaffold_ui.png)

To format the text appropriately, use [Markdown](https://www.markdownguide.org/cheat-sheet/). 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}}`:

![Scaffold the UI](https://docs.sequin.io/assets/retool-subs/022_update_customer_name.png)

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

```js
### {{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](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/Conditional_Operator) to show a user-friendly value for the status of the subscription.

#### Customer Email | Customer ID

```js
{{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:

![Customer details](https://docs.sequin.io/assets/retool-subs/023_customer_details.png)

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:

```sql
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`:

![Get Customer Query](https://docs.sequin.io/assets/retool-subs/024_get_customer.png)

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

![New JS Query](https://docs.sequin.io/assets/retool-subs/025_new_js_query.png)

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

```js
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`.

![Calc Stats](https://docs.sequin.io/assets/retool-subs/026_calc_stats.png)

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:

![On Success Trigger](https://docs.sequin.io/assets/retool-subs/027_on_success_trigger.png)

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:

```sql
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`:

![Get Next Invoice](https://docs.sequin.io/assets/retool-subs/028_get_next_invoice.png)

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:

![Finish Sub Card](https://docs.sequin.io/assets/retool-subs/029_finish_sub_card.png)

---

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://docs.sequin.io/assets/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.

![Invoice List](https://docs.sequin.io/assets/retool-subs/031_invoice_list.png)

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.

![Finish Sub Card](https://docs.sequin.io/assets/retool-subs/032_set_invoice_list_to_1.png)

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

![Scaffold Invoice List](https://docs.sequin.io/assets/retool-subs/033_scaffold_invoice_list.png)

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:

```sql
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`:

![Get current invoices](https://docs.sequin.io/assets/retool-subs/034_get_current_invoices.png)

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

![Configure list item values](https://docs.sequin.io/assets/retool-subs/035_set_list_value.png)

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:

![Final list item](https://docs.sequin.io/assets/retool-subs/036_final_list_item.png)

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}}`:

![Final list settings](https://docs.sequin.io/assets/retool-subs/037_final_list_setting.png)

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

![Add view invoice button](https://docs.sequin.io/assets/retool-subs/038_add_view_invoice.png)

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

![Add new resource](https://docs.sequin.io/assets/retool-subs/039_add_api_resource.png)

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

![Select Stripe](https://docs.sequin.io/assets/retool-subs/040_select_stripe.png)

**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.

![Add the Stripe API resource to Retool](https://docs.sequin.io/assets/retool-subs/041_create_resource.png)

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:

![Configure Stripe Call](https://docs.sequin.io/assets/retool-subs/042_configure_stripe_call.png)

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

![Configure Advanced Stripe Call](https://docs.sequin.io/assets/retool-subs/043_stripe_advanced.png)

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

![Setup the delete button](https://docs.sequin.io/assets/retool-subs/044_setup_delete_button.png)

- 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://docs.sequin.io/assets/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.