Query Airtable or Stripe with all the power of SQL.

Handling Twilio Events Part 1: PG Notify


[Sequin](https://sequin.io/sources/twilio) gives developers a _secondary source of truth_ for their data. Instead of making GET requests to fetch data or keep your local copy of data in sync, you can rely on Sequin to handle that for you in real-time.

We recently added support for Twilio. With our Twilio sync, it takes just a couple clicks to have all your messages, phone calls, and call recordings synced to a `twilio` schema in your database as they are sent and received.

While this second source of truth is powerful, Twilio is the most event-oriented platform we've integrated with. Often, developers want to handle inbound text messages or phone calls _right away_, like by having their app respond to a text. With Sequin, new text messages will be synced straight to your database. This gives you a reliable source to query from. But in contrast to a webhook, by default your own code will not be invoked when you receive a new text.

We're exploring different ways to attach events to our sync process. The simplest way is using **PG Notify**. In this post, we'll explore how this might work.

To do so, we'll build an SMS service where a group of people can manage a list of tasks over text message:

1. Texting in a string of text adds it to the global list.
2. Texting the number `1` or `ls` will send back the current list. Each text entry is numbered, eg:

```plain
1. clean rackets
2. order more pickle balls
3. make court reservations for saturday
```

3. Texting `del [num]` will delete the entry listed at `[num]`

For simplicity, we're going to have just one global list in this app. But a real-world implementation would allow users to invite certain members (phone numbers) to participate in their list.

This post showcases both the Postgres code for triggering notifications as well as Elixir code to handle those notifications. If you don't know Elixir, context is given around code blocks to help you get the gist.

> We won't cover every line of code or command needed to get a functioning version of this app. However, if you're following along and want help, feel free to [reach out](founders@sequin.io) if you get stuck!

## Setup the sync

We're using [Twilio](https://www.twilio.com/) to send and receive text messages, which offers a free trial. After signing up, spin up a sync to your database with [Sequin](https://app.sequin.io/signup) by supplying your API key:

<div style={{textAlign: "center"}}>
    <img src="./twilio-pgnotify/setting-up-twilio.png" alt="setting up Twilio on Sequin" style={{maxWidth: "400px"}} />
</div>

Sequin's Twilio sync polls for changes to Twilio every couple seconds, meaning your database is always up-to-date.

Instead of using Twilio's webhooks to subscribe to new texts, we'll use PG Notify. However, Twilio needs to call _some_ endpoint when new texts come in, otherwise it won't handle them. It's a bit of a hack, but on the page where you configure the webhooks for your Twilio Phone Number, you can just have Twilio make a GET request to our homepage:

```
GET https://sequin.io
```

<div style={{textAlign: "center"}}>
    <img src="./twilio-pgnotify/number-http-get.png" alt="set Twilio to make a GET request to sequin.io when receiving a text message" style={{maxWidth: "700px"}} />
</div>

## PG Notify

> The code in this section is largely adapted from the wonderful article [How to use LISTEN and NOTIFY PostgreSQL commands in Elixir](https://blog.lelonek.me/listen-and-notify-postgresql-commands-in-elixir-187c49597851) by [Kamil Lelonek](https://medium.com/@KamilLelonek).

Sequin will sync changes in Twilio's API right to your database. In our case, text messages will be synced to the `message` table inside the schema `twilio`.

But our requirements for this app are event-oriented. We want to respond to text messages right away, as they come in.

We could have a worker that polls the `message` table for changes. Perhaps every tick, it polls the `message` table with an incrementing `inserted_at` cursor to see if there's anything new to handle. But for fun, and for illustrative purposes, we'll use PG Notify in this project.

Each Postgres notification includes a _name_ and a _payload_. The _name_ is the event name. You can send a notification using the `pg_notify()` function like this:

```sql
select pg_notify('new_message', '{"body": "clean rackets", "from": "+12135550000"}');
```

For our application, we want to publish new notifications based on inserts into the `twilio.message` table. When Sequin sees a new message at Twilio's `/Messages.json` endpoint, it will insert that message into our database. We can use a _Postgres trigger_ that executes a function that creates a notification whenever this happens.

We'll ignore _updates_ to messages for now. In Twilio, the only time we'll see updates for messages is when the delivery `status` has changed (from eg `sent` to `delivered`). (In a more robust production application, we might want to monitor these events for delivery failures.)

#### Notify function

Here's what the function that creates the notification will look like:

```sql
create or replace function notify_new_message()
  returns trigger
  as $$
begin
  perform
    pg_notify('new_message', json_build_object('operation', tg_op, 'message', row_to_json(new))::text);
  return new;
end;
$$
language plpgsql;
```

Everything around the `BEGIN/END` block is standard syntax to declare a Postgres function. Our function is called `notify_new_message()`. The name of the notification is `new_message`. In `json_build_object()`, we define a JSON object with two properties: `operation` and `message`. `operation` is set to the special variable `TG_OP`, which in our case will always be `'INSERT'`. Then `message` will contain the full message row that was just inserted (the special variable `NEW` inside a trigger function body).

Notifications are cast to `text`. But here's what they will look like as parsed JSON:

```js
{
  "operation": "INSERT",
  "record": {
    "account_id": "ACc96f9",
    "api_version": "2010-04-01",
    "body": "pickup jersey from cleaners ",
    "date_created": "2021-09-27T15:29:34",
    "date_sent": "2021-09-27T15:29:35",
    "date_updated": "2021-09-27T15:29:35",
    "deleted": false,
    "direction": "inbound",
    "error_code": null,
    "error_message": null,
    "from": "+12135551111",
    "id": "SMbdac6",
    "messaging_service_id": null,
    "num_media": 0,
    "num_segments": 1,
    "price": null,
    "price_unit": "USD",
    "status": "received",
    "to": "+12135550000",
    "uri": "/2010-04-01/Accounts/ACc96f9d47739a/Messages/SMbdac623.json"
  }
}
```

#### The after insert trigger

Now, we just need to invoke our notify function after messages are created in the database:

```sql
create trigger new_message
after insert
on twilio.message
for each row
execute procedure notify_new_message()
```

Our notify function is written and it's connected to an `after insert` trigger on `message`. Now it's time to subscribe to this notification from Elixir.

## The Elixir subscription

If you want the complete starting point for a brand new app that uses PG Notify, I recommend you check out [this article](https://blog.lelonek.me/listen-and-notify-postgresql-commands-in-elixir-187c49597851).

Touching on the highlights: First, you need to ensure that your app subscribes to the `new_message` event on boot. One way to do this is to create a new GenServer that invokes `Postgrex.Notifications.listen/2` inside its `init`.

Below is an Elixir GenServer declaration. A _GenServer_ in Elixir is a process that can send and receive messages. In our case, our GenServer will subscribe to notifications from Postgres. We'll first see how to subscribe to these notifications, then we'll see how to handle them.

If you're not familiar with Elixir, gloss over the methods `child_spec/1` and `start_link/1` – these are standard for a GenServer:

```elixir
defmodule SmsBot.Listener do
  use GenServer

  def child_spec(opts) do
    %{
      id: __MODULE__,
      start: {__MODULE__, :start_link, [opts]}
    }
  end

  def start_link(opts \\ []),
    do: GenServer.start_link(__MODULE__, opts)

  def init(opts) do
    with {:ok, _pid, _ref} <- setup_listener("new_message") do
      {:ok, opts}
    else
      error -> {:stop, error}
    end
  end

  defp setup_listener(event_name) do
    with {:ok, pid} <- Postgrex.Notifications.start_link(SmsBot.Repo.config()),
         {:ok, ref} <- Postgrex.Notifications.listen(pid, event_name) do
      {:ok, pid, ref}
    end
  end
```

The important part is inside `init/1`, which is invoked when this process is booted. That calls `setup_listener/1`, which tells Postgres (via the `Postgrex` library), that our process would like to receive notifications for all `"new_message"` events.

To get the connection details for `Postgrex.Notifications.start_link/1`, we're using the `config/0` function on an Ecto repo (common in Phoenix apps).

Now, just add this new GenServer to your child spec in your `Application`:

```elixir
defp children do
  [
    SmsBot.Listener
  ]
end
```

If we were to boot the app, we'd now have a GenServer listening for notifications. But we still need to handle those notifications.

Postgrex.Notifications will send notifications to our GenServer as regular process messages. That means we can handle them with `handle_info/2`:

```elixir
defmodule SmsBox.Listener do

  @impl true
  def handle_info({:notification, _pid, _ref, "new_message", payload}, _state) do
    with {:ok, data} <- Jason.decode(payload, keys: :atoms) do
      data
      |> inspect()
      |> Logger.info()

      {:noreply, :event_handled}
    else
      error -> {:stop, error, []}
    end
  end
end
```

The event name is `"new_message"` and the payload is the stringified JSON object from our `notify_new_message()` function.

If we booted our server and sent an SMS in, we'd see an insert notification like the one above printed to the console.

With notifications flowing from inserts through Elixir, we're almost ready to write our app's handler code. We just need to add one more construct to our data model.

## Creating `list_item`

The `message` table will contain an append-only list of all messages flowing through Twilio. For managing lists in our app, we'll want to group messages in a separate table. In the short run, this will allow users to remove messages from the list. In the long run, this is the construct we'd use to isolate lists between groups of phone numbers. We'd add a `list` table then group `list_items` under that table.

Here's the declaration for the `list_item` table:

```sql
create table list_item (
  id serial not null,
  message_id text references message (id)
);
```

## Handling inbound SMS messages

We'll use this handler to route the incoming SMS notification to one of three functions, depending on the command. We'll rewrite the body of `handle_info/2` and extract the logic out to a new function `handle_message/1`:

```elixir
defmodule SmsBot.Listener do

  @impl true
  def handle_info({:notification, _pid, _ref, "new_message", payload}, _state) do
    with {:ok, data} <- Jason.decode(payload, keys: :atoms) do
      handle_message(data[:message])

      {:noreply, :event_handled}
    else
      error -> {:stop, error, []}
    end
  end

  defp handle_message(%{ direction: "inbound" }) do
    message = data[:message]
	  message_body =
		  data[:message][:body]
				|> String.trim_leading()
				|> String.trim_trailing()

	  case message_body do
	    body when body in ["1", "ls"] ->
	      hande_list(message)

		  "del " <> num ->
			num = String.to_integer(num)
			  handle_delete(message, num)

		  _body ->
			  handle_add(message)
	  end
  end

  defp handle_message(_outbound_message) do
    :ok
  end
end
```

Inside `handle_message/1`, we first normalize the inbound message by trimming trailing and leading whitespace. Then we use a `case` statement to direct the message to the appropriate sub-handler:

- If the message was a `1` or `ls`, then we know the user wants to list all list items so far.
- If the message matches `del [num]`, then they want to delete a list item.
- Otherwise, we'll create a new list item for the message.

> Note: A production app would want to do way more inbound text normalization.

Note that we only want to handle messages that have `direction="inbound"`. Twilio will also record all of our _outbound_ messages (replies to the users), which will in turn trigger PG Notifications. We just ignore those.

Here's what each of those handler functions might look like. Note that this assumes we've setup an Ecto repo for list items, called `ListItem`.

First, we define a helper function that lists all list items in the database, with a corresponding index. These indexes will be used when displaying the list, to both number the list and to allow the user to delete items:

```elixir
defp list_messages_with_indexes do
  ListItem
  |> SmsBot.Repo.all()
  |> SmsBot.Repo.preload(:message)
  |> Enum.map(fn item -> item.message.body end)
  |> Enum.with_index(1)
end
```

The result of calling this function will be a list that looks like this:

```elixir
[
  {1, "clean rackets"},
  {2, "order more pickle balls"},
  {3, "make court reservations for saturday"}
]
```

We'll use this helper function in a couple places in our handler functions:

```elixir
defmodule SmsBot.Listener do

  # ...

  defp handle_list(message) do
    messages =
      list_messages_with_indexes()
      |> Enum.map(fn {idx, msg} -> "#{idx}. #{msg}" end)
      |> Enum.join("\n")

    outgoing_body = "Here's the list:\n#{messages}"

    send_sms(message.from, outgoing_body)
  end

  defp handle_delete(message, del_idx) do
		to_delete =
		  ListItem
      |> SmsBot.Repo.all()
      |> Enum.with_index(1)
		  |> Enum.find(fn {idx, msg} -> idx == del_idx end)

	  if to_delete do
		  with {:ok, _li} <- SmsBot.Repo.delete(to_delete) do
		    send_sms(message.from, "Item deleted")
		  end
	  else
			send_sms(message.from, "Error: No entry for #{del_idx}")
	  end
  end

  defp handle_add(message) do
    changeset = Ecto.Changeset.cast(%ListItem{}, %{ message_id: message.id })
    with {:ok, _li} <- SmsBot.Repo.create(changeset) do
	    send_sms(message.from, "Item added")
	    handle_list(message)
    else
      err ->
        Logger.error(err)
        send_sms(message.from, "Something went wrong.")
    end
  end
end
```

Digging in to how each of these functions work is beyond the scope of this tutorial. But touching on the highlights:

- We assume the implementation of `send_sms/2`, which makes an API call to send a message via Twilio. This expects a phone number as the first argument and a message body as the second. Note that all messages are sent to `message.from`, or the sender of the message we are handling.
- Our ordering/indexing of list items is simple. We just generate the indexes on the fly, relying on insertion order to keep the list stable.
- The `message` table is a read-only table that reflects our Twilio data, kept in-sync by Sequin. By creating the `list_item` table, we are maintaining our domain-specific data (list items) elsewhere. That gives us the freedom to e.g. delete list items, while preserving the full record of messages sent and received by Twilio.

## Wrapping up

PG Notify is a great way to trigger events around your sync process. It's well-supported among programming languages, quick to setup, and doesn't require any other tools outside of Postgres (like eg Kafka).

Using Sequin as the backbone of our Twilio integration offers us a foundation that is both easy to build on and scalable. Our app – like many apps built with Twilio – relies on a historical record of all text messages received. Retrieving that text message history via Twilio every time we needed it would be inefficient. Having the data readily available in a database alleviates the need for polling or handling webhooks.

However, using PG Notify here has some big limitations:

- If your app is down (no listeners), _events are not buffered anywhere_. After broadcast, events disappear immediately. This means even if you're down for just a few seconds, you could miss text messages that you need to respond to.
- There are no retries. If your notification handler in your code has a bug, or hits a temporary error (Twilio is unable to process your outgoing text), the event will be lost.

All this means that PG Notify is best used in production _in collaboration with_ some type of database polling process. For example, in this application, we could store a cursor somewhere, `last_message_processed`. As we process messages, we increment the cursor. If our app restarts, we use the cursor to "catch up" on any messages we may have missed before subscribing to the notify stream for new ones.

At Sequin, we're brainstorming other ways to trigger events around your sync. If there's anything you'd love to see here, let us know! Otherwise, stay tuned.


How We Use Our Stripe Sync


In this post, I'll describe one of the ways we're using Sequin internally as part of our core app. I'll show how we're integrating directly with _Stripe_ data and how we're using that with some simple React components that implement the trial banner in our user-facing console.

### The back-end

At Sequin, we recently added a banner to our console to warn users about expiring trial periods.

In order to track the latest trial status for subscriptions in Stripe, we could either use the Stripe API whenever one of our users logged into the console. Or we could use Sequin to keep track of the data.

We used our own Stripe sync to power these banners for three main reasons:

1. No additional dependencies - _we already need the database for serving the app_
2. Speed - _no unnecessary lag for the user when using the synced data_
3. It was easy!

**The key to using your Stripe sync effectively is to have a shared ID between your tables and Stripe's resources**. For our integration, whenever a new user signs up for Sequin, we automatically create a new `StripeCustomer` via the Stripe API. We store the ID for the `StripeCustomer` with our user's record:

```python
org = db.Org()
user = db.User(email=email, org=org)
org.stripe_id = create_stripe_customer(org=org)
db.save(org)
db.save(user)
```

> Note: you _could_ use the user's email address for this join. But storing the Stripe Customer ID will make this join a lot more resilient. The link won't break if the user changes their email address or adds other members of their team to their Sequin account.

Then, whenever a user creates a sync for the first time on a given platform, we create a new `StripeSubscription`. In the `subscription.metadata`,  we include the `platform` (e.g. __Shopify__, __Stripe__ or __Airtable__).

Now we can put the data managed by our Stripe sync to work! We're using Sequin to sync Stripe data into a separate `stripe` schema in our main production database. We now have direct access to Subscription and Trial data from Stripe which we can easily join back onto tables in our own internal schema:

```sql
select
    subs.metadata ->> 'platform' as platform,
    users.id                     as user_id,
    users.stripe_id              as customer_id,
    subs.status                  as subscription_status,
    -- get the time until the trial ends in days.
    date_part(
        'epoch', now() - subs.trial_ends
    )/(60*60*24)                 as days_left_in_trial,
    subs.trial_ends              as trial_end_date
from orgs
left join stripe.subscription as subs
    on orgs.stripe_id = subs.customer_id
where orgs.id = :org_id;
```

### The front-end

Now, whenever a customer loads their console, we can quickly check the subscription status for each platform and display any relevant notices about active trials.

Here, we're using ReactJS components to conditionally render some basic banners inside the `<header>` tag of our page. The _Header_ component will render a separate _TrialNotice_ banner for each subscription where `status=trialing`. The banner will either display the end date of the trial or, if there's 3 (or fewer) days left in the trial, it'll show the number of days remaining.

```javascript
export default function TrialNotice({
    subscription,
}: React.PropsWithChildren<{ subscription: Subscription }>) {
  if (subscription.days_left_in_trial > 3.0) {
    return (
      <div class="trial-banner">
      Your trial for ${subscription.platform}
      ends on ${subscription.trial_end_date}
      </div>
    )
  } else {
    let days_left = subscription.days_left_in_trial;
    return (
      <div class="trial-ending-banner">
      Your trial for ${subscription.platform}
      ends in ${days_left} days!
      </div>
    )
  }
  return null;
}

export default function Header(subscriptions) {
  return (
  <header>
  ...
  {subscriptions.map((sub) => {
    sub.status == 'trialing' ? <TrialNotice subscription={sub} /> : null
  })}
  ...
  </header>
  )
}
```

As you can see, the key is establishing the link between your internal data and your Stripe data. In our case, we use the Stripe Customer ID. Another idea is to store an ID from your internal data (like your user's ID) in Stripe, in say the customer.metadata field. With the link in place and Stripe data syncing to your database, you can easily build all kinds of features around a subscription's status.


SQL queries for calculating Stripe MRR


Monthly Recurring Revenue (MRR) is our primary metric at Sequin. This keeps us all focused on what matters.

I went on quite the journey to calculate MRR for our real-time dashboard. Several times it appeared my query was on the money, only to find later in the day it would diverge from Stripe's calculation. How was this happening?

I've since honed in on an MRR query that is accurate and aligns to Stripe's own calculation of MRR:

```sql
with active_subscription_items as (
	select *
	from stripe.subscription_item si
	where si.created = (
	    select MAX(si2.created)
	    from stripe.subscription_item si2
	    where si.subscription_id = si2.subscription_id
	)
)
select
	sum((stripe.price.unit_amount/100.00) * active_subscription_items.quantity)::money
from stripe.subscription
left join active_subscription_items
	on active_subscription_items.subscription_id = stripe.subscription.id
left join stripe.price
	on active_subscription_items.price_id = stripe.price.id
where stripe.subscription.status != 'canceled';
```

I'll explain how this query works to accurately calculate MRR so you can build upon it as you need.

## Calculating MRR with Stripe

Monthly Recurring Revenue (MRR) is the amount of revenue you can expect to receive on a monthly basis. It's a very popular metric for subscription businesses.

Stripe makes setting up a subscription business easy. And in your Stripe dashboard, you'll actually see an MRR metric from day one. Then, you'll look to slice and dice this metric or pull it into your own dashboard. This is where things get tricky.

### Get your data

The first step is to pull your Stripe data into a model that is easy to query. There are a couple options to consider.

Stripe has a great product called [Stripe Sigma](https://stripe.com/sigma) built in. Stripe Sigma allows you to build custom queries using SQL and comes with some very helpful report templates (except no template for MRR!). Note that Stripe Sigma data will always have a 24-hour lag. Additionally, you can't access the underlying database Stripe Sigma runs on, so funneling your metrics to a dashboard is not possible.

We wanted real-time data, in our Postgres Database, with no scheduling overhead. So ultimately, we added [Stripe support](/stripe-on-sync-inc) to our product, [Sequin](https://sequin.io). It replicates your stripe data to your Postgres database in real-time. It not only solves our analytics needs, but also is at the core of our integration with Stripe.

> Note: While we're using [Sequin's Stripe Schema](https://docs.sequin.io/stripe/schema) from here on out, with minor edits these queries should still work with Stripe Sigma and the data sets generated by other services.

### The naive approach

Now, to the MRR query.

Stripe explains in their documentation that, "You can calculate Monthly Recurring Revenue (MRR) by summing the monthly-normalized amounts of all active subscriptions at that time.[1](https://support.stripe.com/questions/calculating-monthly-recurring-revenue-mrr-in-billing)"

Seems simple enough. I'll just pull down all my active subscriptions and the amount of revenue from the most recently paid invoice. Here was my initial query:

```sql
select
	sum(stripe.invoice.amount_paid/100.00)::money
from stripe.subscription
left join stripe.invoice
	on stripe.subscription.latest_invoice_id = stripe.invoice.id
where stripe.subscription.status != 'canceled'
```

This query finds the latest invoice for any active subscription and then sums up the `amount_paid` on that invoice. Nothing fancy.

But not so fast.

If an invoice includes any non-subscription items or represents just an incremental portion of the subscription for the customer, the data will be messy. In the first instance, I might unintentionally count one-off onboarding or servicing fees as recurring. In the second instance, I'll under-value the full recurring value of a subscription by only incorporating a fraction of the full subscription in my MRR. Neither are accurate.

### The Stripe approach

To fix this issue, I realized I needed to go one step beyond the invoice and consider all the current _subscription items_ associated to the most recent period of the subscription. This ensures that I'll capture all the recurring revenue tied to subscriptions:

```sql
with active_subscription_items as (
	select *
	from stripe.subscription_item si
	where si.created = (
	    select MAX(si2.created)
	    from stripe.subscription_item si2
	    where si.subscription_id = si2.subscription_id
	)
)
select
	sum((stripe.price.unit_amount/100.00) * active_subscription_items.quantity)::money
from stripe.subscription
left join active_subscription_items
	on active_subscription_items.subscription_id = stripe.subscription.id
left join stripe.price
	on active_subscription_items.price_id = stripe.price.id
where stripe.subscription.status != 'canceled';
```

This query returns an accurate MRR that matches Stripes MRR to the cent.

The challenge this query overcomes is finding just the most recent `subscription_items` associated to any given subscription. Hence the `with active_subscription_items as` variable. Then, I filter those subscription items down to just the ones that are associated to active subscriptions and calculate the recurring revenue by multiplying the `price` of the subscription item to the `quantity` the customer has subscribed to.

By using `subscription_items` I'm calculating MRR based on the most up to date, projected revenue on any subscription. It is a "forward-looking" metric. That is, as soon as my customer's subscription is updated to reflect a new item (say they added a couple more seats to their plan), my MRR will update even if the customer won't be billed for that MRR until the next billing cycle.

This is the MRR metric we put on our dashboard. It's the metric we talk about every day in standup and consider when we're prioritizing initiatives. As Paul Graham [advises](http://www.paulgraham.com/growth.html), we find that orienting our company around MRR growth makes it easier to align as a team and focus on what really creates growth.

### Backward looking MRR

We now have an accurate MRR metric to orient our team as we move forward. But often we'll want to pull reports that look backward. How should we calculate MRR in this instance?

When looking back, it's instructive to adjust the MRR calculation to look at what revenue actually came in through paid invoices as opposed to the listed price of the subscription.

So when assessing last month's MRR, I'll use the following query:

```sql
select
	sum(stripe.line_item.amount/100.00)::money
from stripe.subscription
left join stripe.line_item
	on stripe.subscription.id = stripe.line_item.subscription_id
left join stripe.invoice_line_item_map
	on stripe.invoice_line_item_map.line_item_id = stripe.line_item.id
left join stripe.invoice
	on stripe.invoice_line_item_map.invoice_id = stripe.invoice.id
where
	stripe.subscription.status != 'canceled' and
	stripe.line_item.type = 'subscription' and
	stripe.line_item.period_start = stripe.subscription.current_period_start and
	stripe.line_item.period_end = stripe.subscription.current_period_end and
	stripe.invoice.paid;
```

As opposed to tallying `subscription items`, this query calculates MRR by summing up the receivables from subscription `line items` on paid invoices. In short, this is the money we actually made from subscriptions.

## Next steps

From here, you'll likely need to tailor these queries to fit the nuances of your business. You might need to handle different currencies or consider discounts, taxes, or trials. Luckily, these queries give you a strong foundation to work from.


Managing Stripe as a Source of Truth


If you're using Stripe, there's a good chance your app's functionality depends on data stored in Stripe. Some data requests are easy. A user's _billing details_ page is often just one API call to Stripe's `v1/payment_methods?customer={customerId}` endpoint (and a little formatting) away.

But some data requests are hard. Some require a combination of data stored in Stripe along with data in your own app to know what to show the user.

For example, showing a re-order button on a site's _order history_ page should have full details of both past purchases (from Stripe) and current stock levels from an inventory management system.

What are the best approaches for implementing these kind of complex data integrations with Stripe?

## Practical example: WebFlicks Movie Search

A practical example will help us evaluate the pros and cons of different approaches to integrating Stripe data back into your app.

Let's say you've built a movie streaming service, WebFlicks, where your users pay for subscriptions to access different packages of shows and movies. $3/month for animated movies, $2/month for action movies etc.

Now, you want your Movie Search feature to intelligently mark movies as `available` for each user. So as a user searches for content, the results consider a user's purchase and subscription history by replacing a `Buy Now` button with a `Watch Now` button.

The requirements might look something like:

1. A movie should be `available` if the user has an active subscription including that movie
2. A movie should be `available` if the user has purchased it
3. A movie's `available` flag should be immediately updated when:
   1. a user starts a new subscription
   2. a user's subscription is cancelled and expired
   3. a movie is added to and removed from a genre

Now, let's say your search is based on a table called `movie_search`:

| movie_id | subscription_groups   | title         | keywords                             |
| :------- | :-------------------- | :------------ | :----------------------------------- |
| 1        | `action`, `adventure` | Indiana Jones | 'harrison ford action archaeologist' |
| 2        | `action`, `sci-fi`    | Star Wars     | 'harrison ford space opera'          |

You'll use Postgres' `tsvector` and `tsquery` for a simple _Full Text Search_ interface. For example, the following sql clause will match any rows where the `keywords` field contains both `harrison` and `ford`:

```sql
to_tsvector(movie.keywords) @@ to_tsquery('harrison & ford')
```

A solution for running a user search, including a boolean flag (`included_in_user_subscription`) might look something like this:

```python
def get_movie_results(customer_id, query):
    # somehow fetch the users's active subscriptions
    user_subscribed_groups = get_subscription_groups_for_customer(customer_id)
    query = """
      select
        movie_search.movie_id,
        movie_search.title,
        -- We'll use postgres' '&&' (overlap) operator to check if the user's subscriptions
        -- include any of the subscription_groups for the movie.
        -- The users's 'subscription_groups' are parameterized here as '$1'
        (movie.subscription_groups && $1) as included_in_user_subscription
      from movie_search
      where to_tsvector(movie.keywords) @@ to_tsquery($2)
    """
    return db.execute(query, [subscription_groups, query])
```

To serve these search results, we need to know the details of any subscriptions currently active for the user so that we can include those details as part of the search query. To do this, we need access to the full subscription state and subscription items to serve any request on WebFlick's search page. What is the best way to do this?

### Common Approaches

There are several ways to handle this kind of problem, each with their own downsides and edge cases.

### Method 1: Hammering The Stripe API

The most obvious approach is to simply query Stripe via their API whenever you need to access the data. Maybe with a short-lived cache.

One strategy for this may be to do something like the following:

1. List the customer's subscriptions and subscription items via Stripe's `/v1/subscriptions?customer=<customerId>` endpoint. The response includes `subscriptionItems` as a list (which may require separate pagination calls) which each contain a reference to a `product`.
2. Using the `productId`s from the `subscriptionItems` you could now use `/v1/products?id=<productId1>,<productId2>,<productId3>` (again, requiring pagination if you have a lot of productIds) to get that metadata [1], which you can then merge into the results from your movie search system to mark movies which are included in active subscriptions as available.

> [1] Depending on your Stripe integration, a Stripe ProductId could map to a number of different objects in your system such as a specific `Movie`, a `Genre`, a `Movie Studio` or something like `New Releases`. There are multiple ways you may be storing the relationship between Stripe's concept of a product and your own, but a common pattern is to store this on the `product.metadata` field.

An implementation of this method might look something like the following. We'll use Stripe's Python API as an example, including its `auto_paging_iter` helper method that handles pagination. Note: this method may use as few as 2 api calls, but any pagination required will still count towards Stripe's API rate limits of 100 requests per second.

```python
def get_customer_subscription_groups(customer_id: str):
    subscribed_product_ids = set()
    subscriptions = stripe.Subscriptions.list(customer=customer_id)
    for subscription in subscriptions.auto_paging_iter():
        for subscription_item in subscription.items.auto_paging_iter():
            product_id = subscription_item.price.product
            subscribed_product_ids.add(product_id)

    subscription_groups = set()
    products = stripe.Products.list(ids=list(subscribed_product_ids))
    # for each product, extract the 'subscription_group' and add it to our set
    for product in products.auto_paging_iter():
        metadata = product.metadata
        subscription_group = metadata.get('subscription_group')
        subscription_groups.add(subscription_group)
    return list(subscription_groups)
```

Pros:

- Stripe is the ultimate source of truth for subscription/order data
- It's simple, and the Stripe API has great docs
- It's usually fast to onboard a new feature

Cons:

- The Stripe API doesn't have a lot of filter options, so you'll probably need to paginate
- The Stripe API rate limit of 100/s would allow maybe 20/25 search page requests per second due to multiple calls
  - If you exceed these limits you could start interfering with orders and payments!
- Caching the results from the Stripe API for more than a few minutes could lead to a bad user experience if someone sees a movie which they know they should have access to but don't.

### Method 2: Maintain Your Own Copy Of The Data

Having order and subscription data in your own database gives you a lot more options! A (relatively) simple SQL query can merge your realtime subscription data directly with your inventory data efficiently and flexibly. This query makes use of `stripe.subscription`, `stripe.subscription_items` and `stripe.product` tables which contain the same data as you'd get from the Stripe API. The approach is similar to **Including Subscriptions In The Search Query**, outlined above, but with the added benefit of the user's subscription database being pulled directly from the same database as part of the query.

```sql
select
    movie_search.movie_id,
    exists(
        select 1
        from stripe.subscription s
        join stripe.subscription_item si
            on s.id = si.subscription_id
        join stripe.product p
            on si.product_id = p.id
        where s.customer_id = '<Stripe_customer_id>'
        and p.metadata -> 'group' = ANY(movie.subscription_groups)
    ) as included_in_subscription
from movie_search
where to_tsvector(move.info) @@ to_tsquery('harrison & ford')
```

This allows a **fast** and **scalable** interface for search without having to touch the Stripe API. _If you can keep these tables up to date in realtime_ then you have a convenient and scalable (e.g. via database replication) solution without any caching and hard rate limits.

So, what's the best way to build and maintain this database?

#### Option 1. Update State With Stripe's Webhooks

Stripe's webhooks mechanism allows you to register an HTTP endpoint in your own API, which Stripe will use to alert you of any changes to the data in your Stripe account. For example, a new subscription for product `prod_0001` would trigger an HTTP POST request to your API with contents like:

```json
{
  "object": "event",
  "type": "customer.subscription.created",
  ...
  "data": {
    "object": {
      "object": "subscription",
      "id": "sub_0027",
      "customer": "cus_0012",
      ...
      "items": [
        ...
        "data": [
          {
            "object": "subscription_item",
            ...
            "price": {
              "product": "prod_0001"
            }
          }
        ]
      ]
    }
  }
}
```

We recently wrote a [pretty thorough summary of the issues with webhooks](/events-not-webhooks) so I'll just summarize here:

- Requires high uptime to reliably receive events
- Hard to recover from bugs in webhook handlers
- No handling of events that occurred before the webhook was integrated. You'll need to setup a backfill operation.
- Even with Stripes toolings, its hard to work with in a development environment (where you likely don't have a secure public facing API to receive webhooks)
- Having to manage verification of the webhook's source

#### Option 2. Update State After All Stripe API Operations

All actions on the Stripe API return the new (and modified) Stripe objects in the response. You can use this response data to update your own database.

```python
class Subscripton(Model):
    id = fields.text()
    # ... more fields

    def create_subscription(customer_id: str, price: str):
        subscription = stripe.Subscription.create(
            customer=customer_id",
            items=[
                {"price": price"},
            ],
        )
        # tranform the customer object to fit your model
        # and insert-and-update
        self.create_from_stripe_object(subscription)
        return customer
```

Here, `stripe.Subscription.create` will return an object similar to the WebHook JSON payload shown above, including nested objects for `subscription`, `subscription_item` and `price` (amongst others). You could use this response in a function, something like `Subscription.create_from_stripe_object` to traverse these nested objects and insert/update the corresponding records in your own database.

This approach allows for some nice abstractions, but thorough implementation **would need to manage over 2,000 fields**! Yes, you could just implement tables and fields as-and-when you need them, but this brings back a problem we had with the webhook approach - you'd need a whole separate system to backfill data whenever your requirements change!

One final drawback: even the most thoroughly planned integration between your app and Stripe is likely to hit some unexpected feature limitations. Don't have an in-app way to handle changing the shipping address for an order? Someone's probably going to have to bypass your app entirely to log into the Stripe console to fix a few manually. Even worse, you may expect cancelled subscriptions to come via your users logging in clicking `unsubscribe` but sometimes payments are refused and bank accounts get closed - and you'll need to handle that! **You'll still need webhooks** or else these changes will never make it back to your app database!

#### Option 3. Existing Services

Several services exist for syncing Stripe data to a query-able database, namely Stripe's own _Sigma_ and FiveTran's _Stripe Connector_. These services are great and solve some difficult problems, but _they're not realtime_. They're aimed primarily at Data Warehousing and Analytics use cases and are updated snapshots.

A intermittent update frequency is a deal-breaker. Remember that we need to quickly reflect new purchases and subscriptions.

#### Option 4. Here Comes Sequin

So it turns out that maintaining a copy of the full Stripe dataset in realtime is complicated. Worse, it's messy.

You need a hybrid approach. The accuracy and realtime benefits of directly using the Stripe API in addition to the scalability and flexibility of having the data in your application database can only be achieved by historical back-filling of data from the Stripe API in combination with real-time event syncing.

This is why we built Sequin.


Give me /events, not webhooks


<div style={{margin: "20px 0px 20px 0px", fontWeight: 400, color: "rgb(100, 116, 139)", padding: "15px", borderRadius: "5px", backgroundColor: "rgb(246, 248, 250, 0.5)" }}>
We're <a href="http://sequin.io/">Sequin</a> and we help developers skip API integrations. We sync data from third-party APIs like Airtable and Stripe right to your Postgres database in real-time. It's like having row-level access to your data in <code>stripe_prod</code>. Setup takes <a href="https://app.sequin.io/signup" target="_blank">just two minutes</a>.
</div>

Webhooks are a supplement to many APIs. With a webhook system in place, System B can register to receive notifications about certain changes to System A. When a change occurs, System A _pushes_ the change to System B, usually in the form of making an HTTP POST request.

Webhooks are intended to eliminate or reduce the need to constantly poll for data. But in my experience, webhooks come with a few challenges.

In general, **you can't rely on webhooks alone to keep two systems consistent**. Every integration I've ever worked on has realized this fact by eventually augmenting webhooks with polling. This is due to a few problem areas.

First, **there are risks when you go down**. Yes, senders typically retry undelivered webhooks with some exponential back-off. But the guarantees are often loose or unclear. And the last thing your system probably needs after recovering from a disaster is a deluge of backed-up webhooks to handle.

Second, **webhooks are ephemeral**. They are too easy to mishandle or lose. If you realize after deploying a code change that you fat-fingered a JSON field and are inserting `null`s into your database, there is no way to play the webhooks back. Or, you might handle part of the webhook processing pipeline out-of-band with the webhook request – like the database insert. But then you risk that failing and you losing the webhook.

To mitigate both of these issues, many developers end up buffering webhooks onto a message bus system like Kafka, which feels like a cumbersome compromise.

Consider the architecture for a sophisticated webhook pipeline between two parties:

<div style={{textAlign: "center"}}>
    <img src="./events-not-webhooks/producer-consumer-pipeline.png" alt="arch of producer pushing events to a consumer" style={{maxWidth: "250px"}} />
</div>

We have two message buses, one on the sending end and one on the receiving end. The complexity is apparent and the stages where things can go wrong are many. For example: On the receiving end, even if your system is tight you're still subject to sender deliverability failure. If the sender's queue starts to experience back-pressure, webhook events will be delayed, and it may be very difficult for you to know that this slippage is occurring.

Adding to the complexity, the security layer between the two is usually some HTTP request signing protocol, like HMAC. This is robust and alleviates managing a secret. But it's also far less familiar to your average developer and therefore more prone to headache and error. (HTTP request signing and verification is one of those tasks I feel one does _just infrequently enough_ to never fully commit to memory.)

So, not only do webhooks leave you open to eventual _inconsistency_, they're also a lot more work for everybody.

What else can we use to keep two systems in sync, then?

### The `/events` endpoint

For inspiration on keeping two data sets in harmony, we need look no further than databases. Consider Postgres' replication slots: you create a replication slot for each follower database, and the followers subscribe to that replication slot for updates.

The two key components are:

- The primary database keeps a log of everything that's changed recently and
- The primary database keeps a cursor that tracks the position in the changelog for each follower database

If the follower goes down, when it comes back it can page through the history at its leisure. There is no queue, nor workers on each end trying to pass events along as a bucket brigade.

APIs can follow from this model as well. Take Stripe. They have an `/events` endpoint that contains all creates, updates, and deletes to a Stripe account over the last 30 days. Each event object contains the full payload of the entity that was acted upon. Here's an example of an event for a `subscription` object:

```
{
  "id": "evt_1J7rE6DXGuvRIWUJM7m6q5ds",
  "object": "event",
  "created": 1625012666,
  "data": {
    "object": {
      "id": "sub_JgFEscIjO0YEHN",
      "object": "subscription",
      "canceled_at": 1625012666,
      "customer": "cus_Jff7uEN4dVIeMQ",
      "items": {
        "object": "list",
        "data": [
          // ...
        ],
        "url": "/v1/subscription_items?subscription=sub_JgFEscIjO0YEHN"
      },
      "start_date": 1623826800,
      "status": "canceled",
    }
  },
  "type": "customer.subscription.deleted"
},

```

Some important qualities:

- Each event has a `type` which tells us what this event is. In this case, we see that a customer's subscription has been deleted. Because the full subscription payload is included, we can update our database to reflect fields like `canceled_at` and its new `status` of `canceled`.
- Every embedded object contains an `object` field, so we can easily extract and parse them.
- The event object liberally embeds child objects, giving us a full view of everything that changed without needing to poll the API.

So, instead of listening to webhooks to keep things up-to-date, we can poll `/events`. We just need to keep a cursor locally, which we use in our requests to indicate to Stripe which events we've already seen.

The advantages:

- If we go down, we don't have to worry about missing webhooks. And when we come back up, we can catch up at our own pace.
- If we deploy a bug that mishandles events, no sweat. We can deploy a fix and rewind the cursor for `/events`, which will play them back.
- No need for a message bus on our end.
- We don't have to worry about Stripe's webhook senders delaying delivery. Speed is in our hands. The only thing between us and the freshest data is any caching at the API layer that Stripe is doing (which appears to be none.)
- We're using a simple token-based authentication scheme.
- The way we pull and process events looks the same as how we process any other endpoint. We can reuse a lot of the same API request/processing code.

On the producer side, to support `/events` you need to add the same ceremony around monitoring creates/updates/deletes as you'd use for webhooks. Except, instead of needing to build a delivery pipeline, you just have to insert records into an append-only database table.

On the consumer side, you'll need to setup some polling infrastructure. This is more legwork than, say, a rudimentary webhook handling endpoint that processes everything in-band. But, I bet a decent polling system is no more difficult to build than a robust webhook handling system with, for instance, a message bus. And you get a much better consistency guarantee.

### Making `/events` even better

There is one glaring inefficiency to the `/events` endpoint: to keep things as real-time as possible, you have to poll very frequently. [We poll the Stripe `/events` endpoint every 500ms per account](https://blog.sequin.io/stripe-sync-process), and have considered halving that.

These requests are light, as for all but the most active Stripe accounts the responses will often be empty. But as programmers, we can't help but reach for a way to make this more efficient.

One idea for Stripe and other API platforms: support long-polling!

In the lost art of long-polling, the client makes a standard HTTP request. If there is nothing new for the server to deliver to the client, the server _holds the request open_ until there is new information to deliver.

In our integration with Stripe, it would be neat if we could request `/events` with a parameter indicating we wanted to long-poll. Given the cursor we send, if there were new events Stripe would return those immediately. But if there wasn't, Stripe could hold the request open until new events were created. When the request completes, we simply re-open it and repeat the cycle. This would not only mean we could get events as fast as possible, but would also reduce overall network traffic.

The advantage of long-polling over websockets is code reuse and simplicity. Most integrations involve some form of polling anyway, whether you are backfilling data or replaying mishandled events. The ability to switch from eg backfilling to listening for new events in real-time with a single parameter tweak is a huge win.

### Which should I use?

For API consumers, if you're lucky enough to have the choice between polling `/events` or using webhooks, the question of which to use boils down to your consistency needs. Webhooks can be faster for getting started, especially if you only care about a few API objects. And for some workflows, it's OK if webhooks get dropped, like if you're posting a "new subscriber" announcement to a Slack channel.

But as an integration grows in importance and as the need to ensure nothing gets dropped emerges, we think polling `/events` is hard to beat.

For API producers, supporting `/events` is not only a great gift to your API consumers. `/events` can easily be a stepping stone on the way to providing webhooks. Your `events` table can serve as a "queue" for outbound work for your webhook senders. In fact, `events` can unlock much-needed webhook features, like allowing your webhook consumers to replay or reset the position of their webhook subscription.


Syncing Stripe to Postgres


At Sequin, we replicate APIs to Postgres databases in real-time. We want to provide our customers with the experience of having direct, row-level access to their data from third-party platforms, like Stripe and Airtable.

Stripe's API is great, so we knew our Stripe support would have to be very fast and reliable on day one. In order to replace API reads, our sync needs to create a database that is truly **a second source of truth**. This means:

1. **We need to backfill all data**: On initial sync, we load in all historical data for a given Stripe account into the target Postgres database.
2. **Support virtual objects**: Stripe has a few "virtual" objects, like "upcoming invoices." These objects are in constant flux until they are created (eg an upcoming invoice becomes an invoice.) You have to fetch these one-off as there's no place to paginate them. They don't even have primary keys.
3. **We provide a /wait endpoint**: As you'll see, customers can call the `/wait` endpoint on Sequin after writing changes to Stripe. This endpoint will return a `200` when we've confirmed the database is completely up-to-date. This means they can read from their database after writing to Stripe and know it's consistent.

### Two primary sync strategies

Our Stripe sync orbits around Stripe's `events` endpoint. This endpoint serves the same purpose as a replication slot on a database. It contains a list of all create/update/delete events that have happened for a given account on Stripe.

Each event contains a full payload of the affected record. We can use this event stream to effectively playback all changes to a Stripe account.

However, as you might expect, the endpoint does not contain an unbounded list of all events in a Stripe account ever. It contains data from the last 30 days.

So, this means that when our customers start up a new replica Postgres database, we need to _backfill_ it with historical Stripe data first. Backfilling just means paginating each and every endpoint back to the beginning of the Stripe account.

What we ended up with was two distinct sync processes: Our _backfill_ process and our _events polling_ process.

We run the backfill process first to build up the initial database. Then we run the events polling process continuously over the lifetime of the database to keep it in sync.

### Sync process: The backfill

During the backfill, we need to paginate through the full history of each endpoint on Stripe.

Given the breadth of the Stripe API, there are a few of challenges the backfill poses:

- We need to make requests to dozens of API endpoints.
- Then, for each endpoint, we have to convert the JSON response into a structure that's ready to insert into Postgres.
- Further, each response can contain several layers of nested children. Those children can be _lists_ of children which are, in turn, paginateable.

This was a great excuse to use Elixir's [Broadway](https://github.com/dashbitco/broadway). A Broadway pipeline consists of one _producer_ and one or more _workers_. The producer is in charge of producing jobs. The workers consume and work those jobs, each working in parallel. Broadway gives us a few things out of the box:

- A worker queue with back-pressure.
- We can dynamically scale the number of workers in the pipeline based on the amount of work to do.
- We can easily rate limit the volume of work we process per unit of time. We tuned this to stay well below Stripe's API quota limit.
- A "message" construct with acknowledge/fail behaviors. This made things like retry logic trivial.

In our case, the queue of work the producer maintains is a list of _pages_ to be processed. A page is the combination of an endpoint and the current cursor for that endpoint. Here's a small example:

```
queue = [
  {"/v1/customers", "cur9sjkxi1x"},
  {"/v1/invoices", "cur0pskoxiq1"},
  # ...
]

```

To configure throughput, we just instantiate Broadway with a few parameters:

```
options = [
  producer: [
    module: BackfillProducer,
    rate_limiting: [
      allowed_messages: 50,
      interval: :timer.seconds(1)
    ]
  ],
  processors: [
    default: [
      concurrency: 50,
      max_demand: 1
    ]
  ]
]

```

That `rate_limiting` setting is all we need to ensure we process no more than 50 pages per second. This leaves a comfy 50 requests per second left over in a customer's Stripe quota.

Under `processors`, we specify that we want up to 50 concurrent workers and that each may request one unit of work per time (in our case, a page).

So, to kick off the sync, the backfill producer's queue is seeded with all Stripe endpoints (and `nil` cursors). Our workers checkout a page to work and fetch it. Each page contains up to 100 objects. Each of those objects can contain a list of paginateable children. As such, the worker's first job is to populate all objects in the page completely.

Once we have a list of "filled out" objects, we parse and insert them. We use a large JSON object which maps object types and their fields to tables and columns in Postgres. We benefit greatly from the fact that every Stripe object contains an `object` field which identifies what the entity is.

### Sync process: new events

After the backfill completes, it's time to switch to processing events for the indefinite lifetime of the sync. But we need a smooth hand-off between the two, otherwise we risk missing a change.

To facilitate the hand-off, before the backfill begins we make a request to `/events` to grab the most recent cursor. After the backfill completes, we first catch up on all /events that occurred while we were backfilling. After those are processed, the database is up-to-date. And it's time to poll `/events` indefinitely.

We poll the `/events` endpoint every 500ms to check to see if there’s anything new to process, continuously. This is how we can promise "sub-second" lag.

We log sync completions to a Postgres table. We use the "polymorphic embed" pattern, where each log entry contains a JSON `payload` that can take one of several shapes. For example, our "Stripe backfill complete" log looks like this:

```
{
  "kind": "stripe_backfill_complete",
  "row_count": 1830,
  "last_event_before_backfill": "evt_1J286oDXGuvRIWUJKfUqKpsJ"
}
```

When we boot a Stripe sync process, it checks the sync logs table for the most recent completed sync for this database. Our sync manager then knows what kind of sync process we need to boot and the initial state of that process.

### What about webhooks?

When one hears about a "real-time" or "evented" API integration, the first API primitive that leaps to mind is "webhooks."

But webhooks come with a few challenges:

1. **You can't go down**: Senders typically retry undelivered webhooks with some exponential back-off. But the guarantees are often loose or unclear. And the last thing your system probably needs after recovering from a disaster is a deluge of backed-up webhooks to handle.
2. **You're counting on sender deliverability**: When polling, the only real barrier between you and the latest data is a possible caching layer. With webhooks, senders will typically have some sort of queue or "outbox" that their workers work through. Queues like this are subject to back-pressure. This opens you up to your sync slowing down if your sender's queue backs up.
3. **A redundant system**: Webhooks are not something we can rely on exclusively for a syncing operation like this, so they'll always need to be complemented by a polling system. We have to poll to backfill the database after it initializes. And we may have to poll after recovering from downtime or after fixing a bug in our webhook handling logic.

In general, I have this suspicion that a system that relies purely on webhooks to stay in sync is bound to fail. All it takes is for one webhook to get dropped, on either the receiving end or sending end. With no other backup mechanisms in place, you risk a record in your database being out of sync indefinitely.

Luckily, it turns out that with an `/events` endpoint to poll, webhooks are not necessary. The trick is to just poll it frequently enough to get as close to real-time as possible! What's great is that you can use the same sync system to get a change made milliseconds ago or to catch up on all changes that happened during unexpected downtime.

### The "wait" endpoint

Our databases are read-only. Customers make writes to a platform's API, so that those writes can go through a platform's validation stack. Then, those changes flow down to their database. This is the [_one-way data flow_ we advocate](https://acco.io/read-from-dbs).

To make our Stripe database a true second source of truth, we need a final pillar. We need to enable "read after writes," or the guarantee that if you make a write to Stripe's API, that write will be reflected in your database in a subsequent read. While our Stripe sync is _fast_, the architecture at present leaves open a race condition: You can make a write to Stripe then query your database before the change has propagated.

The simplest way to overcome this race condition is to sleep for one second before any subsequent reads. This should work almost all the time. But we wanted to provide something even more robust.

Customers can instead call our "wait" endpoint:

```
GET <https://api.sequin.io/api/stripe/wait/:id>
```

This endpoint will hold open until we've confirmed that your Stripe database is up-to-date. When it is, the request returns with a `200`. You can now make a subsequent read to your database with confidence.

### Coming up

With backfills, support for virtual objects like "upcoming invoices," and sub-second sync times, we provide a true Postgres replica with all your Stripe data.

We still have a lot of work to do to make the developer experience around this database great. There are 82 tables to wrap one's head around (!!) and robust ORM support is a must for many developers. Now that the foundations of our sync are in place, stay tuned for updates to the overall experience.


Our Airtable sync process, layer by layer


At Sequin, we replicate APIs to Postgres databases in real-time. We want to provide our customers with the experience of having direct, row-level access to their data from third-party platforms, like Stripe and Airtable.

After the concept of Sequin crystallized, we knew we had to get something to market quickly. But it turns out that most APIs are not designed to service real-time replication. The first API we supported – Airtable – is no exception.

Airtable's API presents some unique challenges, which I'll touch on first. Then I'll describe how we built a minimum-viable sync process that was fast and reliable enough to get us our first customers. From there, you'll see how we iteratively built and improved our sync process layer-by-layer.

### Challenges

With Airtable's API:

- There's no way to figure out what's changed
- The schema can change at any time
- Throughput is low

Breaking these challenges down:

**No way to figure out what's changed**

There's no way around it: we have to sweep every table, all the time.

For Airtable, it's difficult to see what's changed in a base. First, you have to make requests _against each individual table_. Given that Airtable's API is limited to 5 requests/second, if you have more than a few tables our dream of sub-second lag time becomes difficult to attain.

Second, deletes are very common in Airtable. And yet, there's no way to easily tell from the API what's been deleted – you have to check every row.

The last curveball is Airtable's inconsistent treatment of the `last_updated` field for records. For example, changes to computed fields do not affect this timestamp. So, we can't poll for changes to them.

**The schema can change at any time**

Airtable has a flexible schema. Users can add, drop, rename, and change the type of columns at any time. This means the shape of the data we receive from the API is constantly changing.

**Throughput is low**

Airtable's responses will contain a maximum of 100 records. And we can only make 5 requests per second. This, of course, means we can only process a maximum of 500 records per second.

### Iteratively building up our sync

We launched with the minimum viable version of our sync process. Once we had something that worked, we began to layer on sync optimizations that made the sync faster and more efficient.

**Lowest-common denominator: the rebuild sync**

Because the Airtable schema can change at any time and we have to sweep the full Airtable API on each sync run, the lowest-common denominator was what we call "the rebuild sync." In the rebuild sync, we perform every single operation necessary to instantiate a Postgres database and bring it to parity with the data in Airtable.

To support this process, our database is split into two schemas:

- `public`
- `public_swap`

Our customers read from the `public` schema. The `public_swap` or "swap schema" is where the sync takes place during a rebuild.

At the top of each sync cycle, we initialize all the tables in `public_swap` as defined by the Airtable base's current schema. We then pull all the records for each table from Airtable and insert them into the tables in the swap schema.

At the end of the sync cycle, we open up a database transaction. Inside that transaction, we drop every table in `public` and "promote" every table in `public_swap` to `public`.

From our customers' point of view, it looks like their `public` schema suddenly receives all updates from Airtable, all at once, at discrete intervals.

We decided it made sense to start with doing this rebuild every sync cycle for a couple of reasons:

- We can use the same process for syncing for the first time or the millionth time.
- Using migrations would be tricky. When the schema changes, we'd have to map out all the paths that were taken to get from schema A → schema B. We avoid this entirely by just rebuilding all the tables from scratch.

**Sync to `public`, only rebuild on schema changes**

As you might imagine, there were a few immediate problems with running a full rebuild on every sync cycle:

1. We were rebuilding the tables in the swap schema and "promoting" them to public on each sync run. Promoting is an expensive operation. We had to drop all the tables in `public`. This meant we were constantly marking Postgres tuples for deletion, keeping the vacuuming functionality very busy.
2. It increased the max lag time of a given row. This is because a row first had to get synced to the swap schema, and then wait for the rest of the rows to get synced to the swap schema _before_ being promoted to the public schema.

So, for our database this meant high write IOPs and high network utilization. For our customers, it meant a suboptimal max lag time.

The next level of our sync was to sync directly to the `public` schema. To pull this off, we needed to incorporate a few changes.

First, we needed a way to _upsert_ records from Airtable right to the `public` schema. Here's an example of what the upsert statement looked like:

```sql
insert into public.products (id,created_time,name,size,color)
      values $1, $2, $3, $4, $5
      on conflict (id) do update set
      id=excluded.id, created_time=excluded.created_time, name=excluded.name, size=excluded.size, color=excluded.color
      where (created_time, name, size, color) is distinct from (excluded.created_time, excluded.name, excluded.size, excluded.color)
```

There are a few key components of this upsert statement:

1. The `on conflict (id)` is what switches Postgres from performing an insert to performing an update. When updating, the `set` clause is used instead. The `set` clause here is just a re-iteration of the mapping that the `insert` clause makes between columns and their values.
2. The `where ... is distinct from` is a key clause. Without it, the upsert would perform a write operation for every single row in the upsert clause, regardless if there were any changes. This trades a write-heavy characteristic for a read-heavy characteristic, which is more efficient for Postgres. Furthermore, for customers using replication slots, this means we'll only generate a WAL entry when something has actually been created/updated.

Note that we're putting the database in charge of determining what's changed. We're constantly sending rows to Postgres, and Postgres is diffing those rows with what it has stored. If there are changes, then it performs a write.

This upsert logic lets us write directly to `public`. But, we still need to trigger a full rebuild if the base's schema is modified in some way (eg a table is added or a column is changed).

So, at the beginning of each sync we need to check to see if the Airtable schema has changed since our last sync. We pull the schema, hash it, then compare that hash with the hash of the last build. If the hashes are the same, we sync right to `public`. If the hashes are different, we kick off a full rebuild.

This sync process was a big step up from the naive first implementation. For our customers, this reduced the lag time of a given row significantly. For Postgres, we traded high write IOPs for high read IOPs, which meant our database could handle greater load. It also effectively eliminated the fraction of time a table is in a write lock, removing all kinds of weird, intermittent performance hiccups.

**In-memory fingerprinting**

Layer two of our sync process bought us considerably more room on our Postgres database. But there was one last major piece of low-hanging fruit.

While more optimal than the constant rebuild, the upsert statement above still places a heavy burden on the Postgres database. Every sync cycle, Postgres is given the responsibility of diffing each batch of rows we pull from Airtable with what it has stored. Read IOPs and network were both still high.

We knew it would be very beneficial to hoist this diffing work up to our workers.

Traditionally, this is the moment I'd reach for Redis. But sharing memory between worker instances is precisely where Elixir/OTP shines. We can keep a map in-memory of `%{ "record_id" => "record_hash" }`, where `record_hash` is an MD5 hash of all the key/value pairs of a given record. When we request 100 records from Airtable, we can compare their latest hashes with what we have in memory. Then, we only perform an upsert of the records that have new hashes. After Postgres confirms the write succeeded, we write the latest hashes to our in-memory cache.

The algorithm to employ this in-memory hash is pretty straightforward:

1. Hash the incoming records we just pulled from Airtable
2. Diff the incoming hashes with the hashes we have in-memory
3. Upsert all records that have changed into Postgres
4. Update the in-memory hash

By keeping the upsert from our second layer intact, we're resilient to failures in this pipeline. If eg the update step in 4 fails, no big deal, those records will just be re-upserted on the next sync, meaning just a little more work for Postgres. If we're unable to upsert to Postgres (eg Postgres is down), we don't update our in-memory hash, so try again to insert on the next go around.

This particular improvement had a massive impact. Our read IOPs and network utilization each dropped by over 90%:

<div style={{textAlign: "center"}}>
    <img src="./airtable-sync-process/db-perf-improvement.png" alt="graph of db performance improvements" style={{maxWidth: "550px"}} />
</div>

There was an uptick in load on our workers, as they were now doing all the diffing. But this was more than offset by the reduction in time they spent waiting for Postgres.

**Support read-after-writes with a proxy**

At this point, our sync is efficient and we're able to approach the theoretical replication limit given the limitations of the Airtable API.

However, we felt ourselves missing an answer for _writes_. We believe in a one-way data flow: reads are best made in SQL, but for writes you'll want to make an API request so you can properly handle eg validation errors. But, even if the replication lag is only a few seconds, any code that performs a _read-after-write_ will not work.

For example, we have some customers that are powering their React web applications with an Airtable back-end. Let's say they have a workflow to let users update their email address. The user clicks an "Edit profile" button, and is prompted for their new email address. After updating, they're sent back to their profile page:

<div style={{textAlign: "center"}}>
    <img src="./airtable-sync-process/edit-profile-flow.png" alt="flow of user profile edit flow" style={{maxWidth: "150px"}} />
</div>

Here's the problem: when they save their new email address, that triggers an API PATCH request to Airtable on behalf of that user. But there's a race condition: when the user is redirected back to his or her profile page, the React app re-fetches their user record from Postgres. There's no guarantee, though, that the updated user record (with the new email) has propagated via Sequin to their Postgres database. So the React app grabs and displays the stale email, confusing the user.

So we built a proxy that users can write through to Airtable. Functionally, it's a reverse proxy. You just prepend the proxy's hostname to whatever Airtable API request you want to make, eg:

```
PATCH https://proxy.sequin.io/api.airtable.com/v0/appF0qbTS1QiA025N/Users
```

The proxy attempts to write the change to Airtable first. If the Airtable write succeeds, the change is written to the database _and then_ the requestor receives a 200. This means that the change that was just written is guaranteed to be present in any subsequent database read.

### Layer by layer

Our wisest move was to build the initial sync to serve the lowest common denominator, even if it was slow and inefficient. As long as we were reliable, delivered on the replication time we estimated in our console, and were resilient to eg schema changes, our customers were happy.

Reflecting back on our discussions before launch, a lot of our original assumptions about what our sync would need were wrong. Luckily, we time-boxed the initial build and got our MVP sync (the rebuild sync) to market as fast as possible. From there, servicing customers and learning about their needs helped inform the shape of each subsequent layer and its priority. Notice how we slowly layered on performance optimizations:

- **Layer 1**
  - The rebuild sync: High write IOPs, high network, and high vacuuming.
- **Layer 2**
  - Sync to `public`: We first reduced vacuuming by syncing directly to `public` and keeping tables between syncs.
  - Then we added `where ... is distinct from`. This traded high write IOPs for high read IOPs and got rid of our vacuuming problem. We still had high network.
- **Layer 3**
  - Fingerprinting: By hoisting the diffing logic up to our workers, we solved both read IOPs and network on our database.


Stripe on Sequin: Replicate Stripe to Postgres in real-time


We're excited to announce we've added support for Stripe to [Sequin](https://sequin.io/)! Now you can get a replica Postgres database with all your Stripe data, synced in real-time. It’s like having row-level access to your data in `stripe_prod`.

Below, I'll discuss the general philosophy behind our company and touch on some of the details of Stripe's API that made working with it really great:

### Reading data

API reads are way more difficult than database reads. Every API has its own pagination rules, rate limits, and auth/refresh schemes. APIs can go down or return an unknown set of errors.

Your code has to handle all of this.

Little things add up: Perhaps the API doesn't support the query you want to make, so you have to make a few subsequent calls. Later, you get complaints from customers about performance, so you cache a subset of the data. But now you have to worry about cache _invalidation_, so before you know it, you're on the third answer of a StackOverflow post trying to get HMAC verification for your new webhook handler working.

Ultimately, API providers are just building HTTP query abstractions over their database. But each of these interfaces are unique, and you have to learn them. And HTTP query param implementations can never match SQL in power. In fact, they rarely come close.

So why does every platform offer an HTTP API vs direct access to their databases? There are two primary reasons:

1. **Abstraction**: A provider can "hide" underlying data model changes from API consumers.
2. **Access control**: They need to make sure you only see data that belongs to you!

When dreaming up Sequin, we realized that you can accomplish both of these goals _with a database_. In fact, the database is an amazing platform for data interchange: Postgres is incredibly well-supported, the database enforces a strong schema that is easily introspected, and access controls are baked-in.

Once data is in a database, it feels "native." With SQL, you can query it just about any way you like. You can plug the data right into a variety of tools, from ORMs to internal tool builders. Databases are highly available and high throughput, so you can get rid of retry or pagination logic. And you can do things you'd never dream of doing with an HTTP integration, like backups, snapshots, or auditing.

### Supporting Stripe

Airtable was the first platform we built support for. Stripe's API couldn't be more different from Airtable's. Here are some of the big features of Stripe's platform that made a real-time replica database possible:

**Support for "events" or webhooks**

The `/events` endpoint contains all creates/updates/deletes of your Stripe data. This is a critical endpoint for our implementation, and we wish more API providers had something like this. Thanks to this endpoint, we didn't even need to build out webhook handling. Our syncing infrastructure can just repeatedly poll this endpoint (>1x/second) to keep our data up-to-date.

`/events` serves the same purpose as a replication slot in a master → replica setup. Combine `/events` with our resilient sync process that's always running, and you get a Postgres database with sub-second lag.

This experience is a big contrast to Airtable's. For Airtable, it's actually pretty difficult to see what's changed in a base. First, you have to make requests _against each individual table_. Given that Airtable's API is limited to 5 requests/second (more below), if you have more than a few tables our dream of sub-second lag time becomes difficult to attain.

Second, deletes are very common in Airtable. And yet, there's no way to easily tell from the API what's been deleted – you have to check every row.

The last curveball is Airtable's inconsistent treatment of the `last_updated` field for records. For example, changes to computed fields do not affect this timestamp. So, we can't poll for changes to them.

Because of the challenges presented by deletes and computed fields, our sync has to perform a full scan of the Airtable API. This means our lag time is O(N), where N is the size of a given Airtable base.

With Stripe, our lag time is effectively O(1).

**Significantly higher API quota**

Whereas with Airtable we're limited to 5 requests/second, Stripe grants customers 100 requests/second. This means when you launch a new Stripe database, we can leave half the quota open to you and still backfill 300,000 records **per minute**.

This helps us deliver a "time to first row" experience that's competitive with querying the API directly. It also means we don't have to worry about 429s/"crowding out" a customer's request quota.

**A static schema with an OpenAPI spec**

As a flexible datastore/spreadsheet, Airtable's schema can change at any time! This presented some unique design challenges that ended up being blessings in disguise – these challenges helped us build a data-driven, resilient sync for Stripe.

With Airtable, knowledge about a base is contained in a handy `/metadata` endpoint. This endpoint tells us which tables are in the base and the names and types of the columns for each table.

With Stripe, we drive the sync process off of a single JSON file, itself derived from Stripe's [OpenAPI spec](https://github.com/stripe/openapi). As you can imagine, this spec saved us a tremendous amount of time.

### Reads vs writes

Core to our hypothesis is not only that database reads are 10x easier and faster than API reads, but that _reads are 10x harder than writes_. As stated previously, reads require piecing together bespoke building blocks to formulate your question as an HTTP query. So if your query is "give me all the customers with unpaid invoices over $100", you need to figure out how to express that query in terms of Stripe's API particulars. As reads get more complex, you'll often need to reach for other tricks like caching.

But writes have a low upper-limit to their complexity: "Send along a payload that has this shape. Here are the required fields."

This is why we champion the mantra "[read from databases, write to APIs](https://acco.io/read-from-dbs)." For reads, we let you skip the API and query your data from SaaS platforms in plain SQL. For writes, a remote procedure call makes sense: you usually want to synchronously verify that the object you're trying to create is valid. You need to pass it through a software validation stack, as often database constraints are not expressive enough.

So, just write to the API, and let those changes flow down to your Sequin database.

To make this architecture great for Airtable was a challenge, as our lag time is O(N). So, you risked the experience where you'd make a write to Airtable's API, yet had to wait several seconds for those changes to show up in your database. So [we built a proxy](https://docs.sequin.io/airtable/reference#writes) that customers write to Airtable through. When changes are written to Airtable through our proxy, we ensure those changes are written to Airtable _and_ your Sequin database simultaneously.

Because our Stripe sync is so fast, we don't offer a proxy for it just yet. We're planning on instead releasing a synchronization endpoint for customers that want to do read-after-writes with guarantees, eg:

```
await stripe.markInvoicePaid(invoice);
await fetch("https://api.sequin.io/sync/#{resourceId}", { headers: syncIncHeaders });
let customersPendingInvoices = Customer.joins("invoice").where("invoice.status = 'pending'").order("invoice.created_at desc")
```

In this semi-pseudocode, on line 1 we make an API request to Stripe to mark an invoice as paid. On line 2, we call the hypothetical Sequin synchronization endpoint. All it does is hold the HTTP request open until your Stripe database is up-to-date. So that way, on line 3, we know we can reload data from the database and be confident the change we made on line 1 will be present.

Assuming your volume of Stripe events is less than ~200-300/s, we'd expect the synchronization endpoint to only hold open for 1-2 seconds.

If this is something you need for your use case, let us know!

### More to come

We think a lot about "time to first row" or how long it takes a developer to setup and retrieve the first row of their data from an API platform. If they're querying an API, this means the time it takes for them to get an API token and run their first successful curl request. If they're using Sequin, this is the time it takes for their database to be provisioned and for them to connect with their favorite client and run their first SQL query.

We want to make our databases not only faster and easier to use for complex, mature integrations – but also faster and easier to use _from minute 1_. A goal approximated by this graph:

<img src="./intro-stripe/dev-complexity-graph.png" alt="Graph of query complexity vs dev time required" style={{maxWidth: "550px"}} />

We think **great ORM support** will be integral to this. With ORM support, developers can query their data across SaaS platforms without ever leaving the comfort of a library they already know.

For Stripe, we also think a lot about how we can help developers **quickly explore and grok the data model**. Stripe databases currently have 82 tables -- that's a lot to wrap one's head around! In the near-term, we'll be releasing sample queries and navigable entity relationship diagrams to help developers get started. But in the long-term, we want to build an amazing in-browser database explorer that will let developers quickly see the schema of example rows from the most important tables and how those rows relate to one another.

### Give it a whirl

It's free to get started with Sequin, no credit card required. And Stripe test accounts are free forever. Give us a whirl and spin up a database of your own! We're here to help if you need anything.


Benchmarking Airtable API performance


Since we launched in December, all of our API request traffic to Airtable has been GET requests. Specifically, we poll their API by requesting pages of 100 records at a time.

The performance of these GET requests is good, averaging just below 600ms. The slowest 95th percentile is about 1100ms:

<div style={{textAlign: "center"}}>
    <img src="./airtable-api/api-get-request_time-avg.png" alt="graph of get requests" style={{maxWidth: "550px"}} />
    <img src="./airtable-api/api-get-request_time-95th.png" alt="graph of get requests" style={{maxWidth: "550px"}} />
</div>

> _We're in AWS' us-west-2 (Oregon) and Airtable is in us-east-1 (N. Virginia). We expect that to add about 75ms of round-trip latency to each request._

At the beginning of this month, [we released our proxy](https://docs.sequin.io/reference#writes). Our customers make write requests through our proxy, like this:

```bash
curl -v -X POST https://proxy.sequin.io/api.airtable.com/v0/appi7vQMBr0oPikls/Main \
  -H "Authorization: Bearer keyshhhhhhh" \
  -H "Content-Type: application/json" \
  --data '{ "records": /* payload */ }
```

Those writes are committed to both Airtable and their [Sequin](https://sequin.io) database simultaneously.

As a result, we're now starting to service Airtable API traffic that isn't just GET requests, but PATCH/POST/DELETE requests as well.

Along with these new request types, we've noted API performance statistics that seemed unusual. For example, we're working with some bases that consistently take up to 10 seconds to service all PATCH/POST/DELETE requests.

I wanted to get to the bottom of what caused writes to slow down so significantly. So I wrote up some quick benchmarks. Each benchmark you see below is the result of running a given type of request against a base at least 100 times in succession.

### Testing different base sizes

The first hypothesis was the most obvious: Perhaps as a base gets larger (ie holds more records), writes get slower.

To test this, I used bases of three sizes that we already had in our account (1k, 10k, and 49k records). These bases did not have the same structure, ie they had different fields (columns). I ran my benchmarks against each base. Here's the average duration at each base size:

<div className="full-width">
    <img src="./airtable-api/duration-by-base-size.png" alt="benchmarks against base sizes" style={{maxWidth: "900px"}} />
</div>

The result was a bit surprising: The average duration of write requests against the 10K base was by far the largest. All write requests to the 10k base took about the same amount of time to service. And there was no meaningful difference between the 49K base and the 1K base.

Given how similar the performance between the 1K base and 49K base is, this benchmark illustrates that the correlation between base _size_ and the service time for writes is weak. This instead suggests that something about the _fields_ in a base play a role.

Something is happening with the fields in the 10K base that is not present in the 1K and 49K bases.

### Computed fields

It turns out that the 10k base had _a lot_ of Formula and Lookup field types. These fields are _computed_. You supply a little function that Airtable runs to derive the cell value, based on other records in the base.

My next hypothesis was that these computed fields were responsible for the slowdown on writes. I began adding arbitrary formula fields to the base, but that didn't seem to make an impact. I suspected that the [Pareto Principle](https://en.wikipedia.org/wiki/Pareto_principle) was at work and that one or two computed fields were responsible for a majority of the latency.

So I stripped out all the computed fields in the 10k base and confirmed the slowdown for writes disappeared. Then I added back computed fields, one-by-one, until I got my first jump:

<div className="full-width">
    <img src="./airtable-api/10k-only.png" alt="benchmarking 10k with different fields" style={{maxWidth: "900px"}} />
</div>

The yellow bar is the full 10k base. The orange bar is the base with all its computed fields stripped out. The turquoise bar is what I got after I added back one of the computed fields.

This base was based off a [de-duplication base from the community](https://airtable.com/shrMnI6K6OIKvLNDN/tbl25vkJL5apr0Kqm/viwBFyJ1CNk3T1lgU?blocks=bipCb8vTGNvvgtnJc). It has some fairly complex computed fields. Here's the computed field that tipped the scales:

<div className="full-width">
    <img src="./airtable-api/dedupe-field.png" alt="benchmarking 10k with different fields" style={{maxWidth: "300px"}} />
</div>

The full snippet below:

```
IF(
    MatchKey,
    IF(
        (LEN(
            values&''
            )-LEN(
                SUBSTITUTE(
                    values&'',
                    MatchKey,''
                    )
                )
            )>LEN(
                MatchKey
                ),
        1
        )
    )
```

You can see that the operation it's performing is non-trivial. But what might be more impactful than the formula itself is the fact that it's operating on _another_ lookup field, and _that_ field is operating on _another_ formula field. So, to calculate a given cell value, there's a small function stack that has to be executed!

Check out the standard deviation of these results:

<div className="full-width">
    <img src="./airtable-api/std-10k.png" alt="benchmarks against base sizes" style={{maxWidth: "900px"}} />
</div>

This relatively small standard deviation tells us the results are pretty consistent request-to-request. This suggests that these computed fields don't add an _erratic_ service time. They add a _consistent_ overhead to each and every request.

### Takeaway

We can reason about what is happening here: When you create or update a record in Airtable, Airtable constructs all of its computed fields. Whether these computed fields are stored or cached anywhere is unclear. _But_, regardless, Airtable still needs to generate these fields because they return them to you in their API response:

```js
200 OK

{
    "records": [
        {
            "id": "recgm4yapfePwBTmq",
            "fields": {
                "ID": "00001",
                "Job Title": "Building superintendent",
                "First": "Deborah",
                "Last": "Harris",
                "Company": "Gottschalks",
                "Link2DeDupe": [
                    "reck9Y37hBxarHHIw"
                ],
               // ...
            }
        }
        // ...
    ]
}
```

This means your creates/updates/deletes will only work as fast as it takes to calculate all your computed fields. (The fact that deletes take the same amount of time as creates and updates suggests that the computed fields for a base are indeed being re-calculated whenever the base is mutated.)

Now that we've isolated computed fields as the main culprit, I was still curious if there was any correlation with base size. So, I doubled the size of the 10k base with the problematic lookup field and ran the benchmarks again:

<div className="full-width">
    <img src="./airtable-api/10k-2x.png" alt="benchmarks against base sizes" style={{maxWidth: "900px"}} />
</div>

Turquoise is the base with 10k records which contains that one heavy computed field. Purple is the turquoise base, but doubled in size. Doubling the size of the base almost tripled the service time for all write requests!

So, it feels safe to draw the following conclusion:

**A sufficiently complex computed field can easily increase the write time for your base dramatically. This impact is proportional to the size of your base.**

This means that if your base is highly transactional (expecting a lot of reads/writes through the API), you should be mindful about how computed fields fit into your schema.

As an alternative to complex, inter-connected computed fields, you can lean on automations or scripts to populate derivative fields. Or, you can move such logic out of your Airtable base entirely.

### Other notes: Outlier requests

It's worth noting that in my results, I pruned out some outlier requests. About every ~200 requests or so, I'd have one API request that would take nearly 30 seconds to complete. It is not clear to me why these occurred or what would have happened if halfway through waiting I'd canceled the request and simply tried to make a new one. (Worth future investigation.)


Prototyping apps on Airtable: Best practices


The first iteration of most software is a _prototype_. The purpose of a prototype is to validate a hypothesis or learn more about a market. Because a prototype is likely to undergo radical changes or total abandonment in face of the information it uncovers, the faster you can generate a prototype, the better.

To learn the most, great teams don't just mock up UIs. They use modern tools to rapidly transform ideas into working software. These [high-fidelity prototypes](https://svpg.com/high-fidelity-prototypes/) let a team quickly de-risk the project.

Airtable is a killer tool for quickly standing up high-fidelity prototypes.

Airtable can be to your prototype's data model what Figma is to your prototype's interface. It allows you to quickly create, iterate, and change the data that powers your app in minutes. Helping you learn more about your ultimate use case and product. From the bits to the pixels.

But as with all powerful tools, there is an 80/20 rule that applies. A couple tricks can make building prototypes on Airtable much easier.

## Two limitations to keep in mind

There are a couple Airtable limitations you should be aware of from the start. For many prototypes this shouldn't be an issue (and if these items are an issue - I would ask, "have you scoped your prototype?").

### Record limits

Airtable maxes out at around 100,000 records per base on the enterprise plan. We've seen companies go beyond 100,000 records in a base, but the front-end of Airtable begins to noticeably slow.

Different [Airtable plans](https://support.airtable.com/hc/en-us/articles/115010928147-Airtable-plans) have different data limits (records, automations, etc). So keep these in mind when you're prototyping:

![Airtable data limits](./prototype/001_pricing.png)

If prototyping your application is going to require hundreds of thousands of records, Airtable isn't for you.

### API rate limits

The Airtable REST API is [limited to 5 requests per second per base](https://support.airtable.com/hc/en-us/articles/203313985-Public-REST-API).

So if multiple clients are making request to the same base, you might see some 429s.

This limit can be particularly onerous for reads. We'll talk about this more later on - but tools like [Sequin](https://sequin.io) abstract the API quota and make prototyping on your Airtable data far simpler.

## Best practices for building Airtable prototypes

You probably get the picture by now, Airtable is endlessly adaptable - it's what makes prototyping on Airtable so powerful. But endless configuration comes with all the intellectual yack shaving and bike-shedding that can slow projects way down. So we would propose a couple conventions to help you build quickly.

### Create a developer environment

To build quickly, you need to be able to break things. To give you the this freedom with an Airtable prototype you should create a **production** and **dev** base in your workspace.

Airtable makes this simple. First, set up your production base. Then duplicate the base to create a development environment with the same structure. Now, you can change the schema and make improvements knowing you have a fallback.

![Create a dev environment](./prototype/002_duplicate.png)

Once your dev environment is stable and reflects the new changes, you can "deploy" by simply making the dev environment the new "production." Or, add the updates to the production base.

If you don't need to perform _writes_ in the development environment, then you should consider "syncing" the data (one way) from your production base to the development base using [Airtable's sync](https://support.airtable.com/hc/en-us/articles/360052521254) functionality. Any updates to the data in the table shared from the production base will then be reflected in the development base.  Keep in mind a synced view is _read only_ - so this can work for some use cases but not all.

### Schema design

As much as possible, treat Airtable as a traditional, relational database. You want your schema to help you maintain data integrity and understand the relationships between your records.

Airtable actively fights traditional schema design in two ways:

1. It is so easy to add fields and views that you can quickly begin to duplicate data across many tables with `lookups`
2. You can begin to mix business logic with raw data using calculated fields (i.e. `formulas`, `rollups`, and `counts`).

In the big scheme of things, lookups and calculated fields are helpful. These capabilities make Airtable easy for everyday users to work with their data in Airtable. <a name="schema-design"></a>

However, for the developer, these field types can create a mess. So to make things easier, implement a simple naming convention: **add some sort of delineation (like an underscore) in front of any Airtable column that is a lookup or calculated field.**

You'll find that this will make working with your data in your prototype easier without sacrificing the underlying power of Airtable. When you are pulling data into your prototype you will be able to quickly see which fields are derived or linked.

This naming convention will also come in handy should your prototype evolve into a production app that is too big for Airtable to handle. A simple `_` in front of each field that is calculated will help you see and properly deal with any Airtable dependencies.

> Pro tip: use the Airtable [base schema app](https://support.airtable.com/hc/en-us/articles/360044409793-Base-schema-app) to spin up an ERD for your base to better visualize these risks.

### Users, personas, and permissions

It's a timeless question: "who is my user?" Luckily, an Airtable prototype can help you come to an answer.

With Airtable, you can not only manage your prototype's users and permissions - but you can also easily enrich your user data with persona and demographic tags to help you segment and introspect your users.

To manage users and permissions in Airtable, you'll create a `users` table just as you would in a relational database. Here is how:

1. Create a table where you will store user information. The primary column should be a unique identifier for the user - like their email.
2. In other fields in the user table, record helpful user data like referral source, persona, job role, and more.
3. Use [linked records](https://support.airtable.com/hc/en-us/articles/206452848-How-to-link-records-in-Airtable) to then associate users to their data in other tables.
4. Store hashed passwords and login information in the `users` table as well.

> This [post](https://docs.stacker.app/data-connection/prepare-your-data) from [Stacker](https://docs.stacker.app/data-connection/prepare-your-data) lays out the structure well.

The beauty of Airtable is that managing users is not only possible, but you can make it as manual or automated as you like. For instance, if security isn't a big deal for your early prototype (i.e. you aren't working with real users or any PII) you can manually create users and store simple passwords. Or, you can go the extra mile and create entire login flows that store hashed passwords in Airtable. It's up to you.

### Seed your database

Oftentimes an early prototype can run on mock data to help a team quickly understand the shape of their data model. Airtable makes working with mock data easy.

Of course, you can just type in data with all the edit tools you are familiar with from spreadsheets.

Or, you can also import existing data from [CSV](https://support.airtable.com/hc/en-us/articles/115013249187-CSV-import-app) and [XML](https://support.airtable.com/hc/en-us/articles/115013739848-XML-import-app) documents.

There is also a [helpful script](https://airtable.com/marketplace/scrRkRnEjgerYYihJ/randomize-values) in the marketplace for filling a base with dummy values.

> Tip: To generate dummy data, check out [Mockaroo](https://www.mockaroo.com/). Just export the mock data as a CSV and import it into Airtable.

### Isolate any business logic

As much as possible, put all the logic and automations that manipulates the raw data in your Airtable base in one place so you can manage it.

For many prototypes, you'll likely house the business logic in the front-end application you are building. So try to keep all derived calculations and validation in the front-end as well.

For other prototypes, you might manage this business logic in Airtable. If you take this approach, instead of using calculated fields that spread logic across each table of your base, use [Airtable automations](/the-complete-developers-guide-to-airtable) to detect when a row is created or changed - and then use an [Airtable script](/the-complete-developers-guide-to-airtable) to calculate and insert derived fields. This can make managing the business logic much simpler because you can see it all in one place as plaintext.

In instances where you need to use calculated fields, use the [naming convention suggested previously](#schema-design) to help you keep things straight.

Additionally, try to stick to one automation service. If you use Zapier, don't split automations between Zapier AND Airtable automations. Try to use just one.

In fact, to help you and your team keep everything in order, document all integrations as a sort of `README` using [Airtable's Description app](https://support.airtable.com/hc/en-us/articles/115013405088-Description-app). In the description block list all the services that are pushing data into your Airtable base (i.e. forms, webhook integrations, or automations) and what fields they touch.

### Simplify reads

The [Airtable REST API](https://airtable.com/api) is not great for prototyping. After working with many customers, we've learned that our product, [Sequin](https://sequin.io/), works great for prototyping on Airtable because you can access all your Airtable data using SQL. Here is why:

Even with [Airtable.js](https://github.com/airtable/airtable.js/) to help manage retries, reading data through the API is an uphill climb. Between the rate limits, pagination, and Airtable's unique `filterBy` parameter you'll find many little quirks. The API is especially slow and onerous when you need to make nested calls to source data from several tables to create a complete data view. And, as you leverage Airtable's ease of use to make updates to your base, your API calls can break as columns and tables change.

Sequin allows you to skip the API by replicating Airtable to a Postgres database. You can then use SQL to quickly `SELECT`, `JOIN`, and `GROUP BY` to get the data you need. For example, you can become agnostic to column changes when sourcing data into your app by simply activating the **Sync all tables** setting in Sequin and then use `SELECT *` in your query. As new columns and tables, appear, they'll load into your app and you can work with them directly.

This reduces the amount of code you need to write, speeds up your data request, and simplifies maintenance as you iteratively improve your prototype.

You can also connect your Sequin Postgres database to all sorts of ORMs like [Prisma](https://www.prisma.io/) or generate a GraphQL API using [Hasura](https://hasura.io/). The entire Postgres ecosystem is at your disposal to help you build.

## Conclusion

Prototypes built on Airtable are uniquely powerful. By giving you a flexible datastore that anyone on your team can use, Airtable can help you quickly build a data model that powers working software. When built with these six, simple conventions, you'll be able to not only build functional software on Airtable - but you'll also be able to quickly iterate, operate, and maintain that prototype as you grow. Get building.

The Complete Developer's Guide to Airtable Part 2: Webhooks


Airtable continues to launch new tools for developers to build in and on Airtable. Which means it is time for a new installment of [The Complete Developer's Guide to Airtable](/the-complete-developers-guide-to-airtable).

In this *part 2* of the Complete Developer's Guide to Airtable, we'll cover how to use Airtable's [incoming webhooks for automations](https://community.airtable.com/t/incoming-webhooks-for-automations/38411/9).

## What are webhooks

Webhooks are messages an application can send when a specific event occurs. Messages are most often HTTP requests. They are a common method for integrating applications and services.

In the absence of webhooks, to know if an event has occurred in another application, you need to constantly _poll_ the other application for an update. Think of a small child incessantly asking "are we there yet?" on a road trip.

Webhooks, by contrast, are a more elegant solution. When an event happens in another application - for example a new contact is created in Salesforce - an HTTP request will be sent to your app describing the event and its details. Then, your app needs an always-on endpoint for receiving these webhook requests and taking the proper action (easier said than done).

With Airtable's new support for incoming webhooks, you get an always-on webhook endpoint that is ready to receive events from internal or third party services. You can then send webhooks to Airtable to trigger automations that take the inputs of the webhook to manipulate your airtable base or trigger other actions like emails and more.

With webhooks, you no longer need to set up a scheduled automation to poll a service for updates every couple minutes. This makes integrating Airtable into your stack much nicer.

## Who get's Airtable webhooks

Webhooks are included on all Airtable plans as a part of the *Automations* feature:

![Airtable Webhook Plans](./webhooks/001_who_gets.png)

Keep in mind, that webhooks can happen frequently. For example, if you trigger a webhook every time a new contact is created in Salesforce, this might happen several times each day. So you can burn through the 100 automations allotted on the free tier in a number of days.

Beyond the total number of Webhooks, there are a couple other limitations developers should consider with Airtable webhooks:

* Airtable [caps webhook payloads to 100kb](https://community.airtable.com/t/incoming-webhooks-for-automations/38411/12?u=sync_inc_test_user_2) right now.
* Airtable only works with `JSON`. No `XML` or `urlencoded` webhooks for now.

> Note: Thank you to [Bill French](https://community.airtable.com/u/Bill.French), [kuovonne](https://community.airtable.com/u/kuovonne) and [ScottWorld](https://community.airtable.com/u/ScottWorld) for investigating these limits in the Airtable community.

## Building a webhook automation

To give you a practical example of how Airtable automations work with webhooks, let's build one using a [Stripe webhook](https://stripe.com/docs/webhooks).

### The scenario

Let's say your accounting team wants to use Airtable to help manage customer subscription renewals that are processed through Stripe. The team wants to be alerted before any subscription renews so they can verify the details of the subscription.

Luckily, you can have Stripe send an `invoice-upcoming` webhook several days ahead of a subscription renewing. You can create a new record in an Airtable base with the details of the webhook so the team can create a workflow to manage this process.

### A three step process

As you'll see in [Airtable's official webhook help docs](https://support.airtable.com/hc/en-us/articles/1500003044161#h_01EZFJ2CNX8M1RFABS6VEBHJZN), creating an automation that is triggered by a webhook is a three step process:

1. Create the webhook and test it.
2. Upon a successful test of the webhook, add an automation action.
3. Turn your new automation on.

### Create and test your webhook

Starting with a blank Airtable base, click the **Automations** button and select the **Create a custom automation** option:

![Add Airtable Automation](./webhooks/002_add_custom_auto.png)

Name your new automation something along the lines of **Upcoming Stripe Invoice**. Then click the blue **+ Choose a trigger** button and select **When webhook received**:

![Name automation and start](./webhooks/003_name_and_start.png)

Airtable will now generate the URL where you want Stripe to post the webhook. Copy this URL:

![Get webhook url](./webhooks/004_webhook_url.png)

Now that Airtable is ready to receive a webhook, head over to [Stripe](https://dashboard.stripe.com/test/webhooks) to configure a new outbound webhook.

> Note: for this tutorial we're using Stripe, but all sorts of platforms - from [Salesforce](https://salesforce-webhook-creator.herokuapp.com/), to [Jira](https://developer.atlassian.com/server/jira/platform/webhooks/), to [Shopify](https://shopify.dev/docs/admin-api/rest/reference/events/webhook) - use webhooks. Just check their developer docs.

After you login to your Stripe account, flip the switch to **View test data** (so you can test your webhooks). Then click the **Developers** option, select **Webhooks** and click to **+ Add endpoint**:

![Create stripe endpoint](./webhooks/005_create_stripe_endpoint.png)

In the **Add a webhook endpoint** modal that appears, paste in the URL you copied from Airtable. Then add a description and under the **Events to send** section select **invoice.upcoming**. Finally, click **Add endpoint**:

![Configure stripe endpoint](./webhooks/006_configure_endpoint.png)

Stripe will create the new endpoint for you. As mentioned earlier, you first need to send a _test_ webhook to Airtable in order to build out your automation. To do so click the **Send test webhook...** button:

![Configure stripe endpoint](./webhooks/007_test_webhook.png)

In the modal that appears, select the **invoice.upcoming** event and then click the **Send test webhook** button:

![Configure stripe endpoint](./webhooks/008_test_webhook_2.png)

Stripe will now send a test webhook to Airtable. You'll see the payload that was sent, and (if everything worked correctly) a `"success": true` in the response:

![Configure stripe endpoint](./webhooks/009_test_webhook_3.png)

Now that you have sent the test webhook from Stripe to Airtable, head back to Airtable to complete the test of the webhook.

To do so, click the **Run test** button in your automation. Airtable will verify the webhook and then present you with the data payload:

![Add resource](./webhooks/010_run_test.gif)

### Build your automation

Now that Airtable has validated the webhook trigger, you can build the rest of your automation by adding actions.

You could build a custom script that takes in the payload from the webhook as inputs. But for this use case, we'll just use the no-code "Create record" action.

But before we add the action, set up the Airtable base to properly store the contents of the webhook. I find it's helpful to do this after you've tested the webhook so that you can see the structure of the data you're working with.

For instance, you can see that the Stripe webhook will deliver the data with currency formatted as cents and time formatted as a Unix timestamp:

![Webhook data types](./webhooks/011_formats.png)

As a result, it's best we store these values as integers in the Airtable base and then use formulas to make them more friendly to users. Taken together, this is one way you could configure your Airtable base to take in the data from this webhook:

![Configure Airtable base](./webhooks/012_table_setup.png)

Here, you plan to record the `event_id` from the webhook as the primary key in the table. Then you store data from the webhook in additional columns. Last, you store the status of each upcoming invoice so your accounting team can track their workflow.

With your table set up, you can now easily configure the action step of your automation to create a new record. Click the blue **+ Add action** button and select **Create Record**:

![Create action](./webhooks/013_add_action.png)

Now map the values in the webhook payload to the fields in your table. It just takes some clicking:

![Add resource](./webhooks/014_map_fields.gif)

Once you've mapped all the fields, test your automation by clicking the **Run test** button:

![Add resource](./webhooks/015_test.gif)

Voila - your automation works!

### Turn your automation on

To finish your webhook automation, there are two more steps.

First, you'll recall that the webhook endpoint you created in Stripe is using test data. Now that you've confirmed everything is working, go back to Stripe and flip the switch so you are now working with _real_ Stripe data. Then create a new webhook endpoint that points to your Airtable webhook trigger.

> Note: Stripe is a bit unique in that it offers "test" webhooks. Most platforms require you to work with real data from the start.

Second, turn your Airtable automation on:

![Turn Airtable automation on](./webhooks/016_turn_on.png)

You've completed all the steps: You created a webhook trigger in Airtable, configured Stripe to send events to that webhook, tested the integration end to end, added an action to create a new record, and turned everything on.

Now, whenever a new upcoming invoice event fires, your accounting team will see it appear in Airtable automatically.

If ever there is an error, Airtable will alert you with an email. So far, we've been running our operations at [Sequin](https://sequin.io/) on an Airtable base populated with Stripe webhooks for a month with no errors on either end.

## Conclusion

For developers, the addition of inbound webhooks to trigger automations is a nice upgrade to the Airtable tool stack. Yes, you could do this with low-code tools like Zapier - but now having this raw capability in Airtable alongside Airtable scripts makes the platform even more programmable.

The Complete Developer's Guide to Airtable

What is RDS Proxy? Exploring with benchmarks


I was surprised when a customer wrote in to explain that he noticed - for simple SQL queries - the Airtable API was about as performant as querying his [Sequin](https://sequin.io/?utm_source=blog&utm_medium=post&utm_campaign=rds)-provisioned Airtable database. We'd expect a database query to be at least an order of magnitude faster than an API query.

So I had to investigate: What was the explanation for this?

But these outlier queries still bothered me. What was the explanation for them?

Airtable's API is pretty consistent and responsive, with a mean and median request time of 350ms:

<img src="./rds-proxy/airtable-api.png" style={{maxHeight: "500px", margin: "auto"}} alt="airtable api performance" />

This is about what you'd expect from a standard list request against a third-party API with 100 items returned per request.

So how could a database query get up to 350ms? A little digging, and we discovered that the customer was querying his database via a Lambda function. This meant opening a connection to his Sequin db before each query. A solid lead.

I wanted to see how much overhead the connection times were adding. So I wrote some simple benchmark functions in Go.

The timing for the benchmarking is handled by this simple function:

```go
func timeTrack(start time.Time, name string) {
	elapsed := time.Since(start)
	log.Printf("%s took %s", name, elapsed)
}
```

You can log how long a function execution took in Go using this function in combination with `defer` like so:

```go
func benchmarkQuery(count int) {
	defer timeTrack(time.Now(), "benchmarkQuery")
	for i := 0; i < count; i++ {
		funcUnderTest()
	}
}
```

Above, the arguments to `timeTrack()` are evaluated immediately, meaning the first argument is set to `time.Now()` - the time `benchmarkQuery()` was invoked.

But `defer` waits to _invoke_ `timeTrack()` until right before the function `benchmarkQuery()` returns. Which, in this case, will be after the for loop executing `funcUnderTest()` `count` times has completed. Neat trick.

With a timing function in place, I wrote out some benchmarks. We want to benchmark two different kinds of behavior:

- A function that opens a connection _on every loop_ and then makes a request
- A function that opens a connection _then_ loops and makes requests

This should help us understand how much time is spent establishing the connection and how much time is spent executing the query.

Here's what the function that opens a connection every loop looks like:

```go
func benchmarkOpen(count int) {
  defer timeTrack(time.Now(), "benchmarkOpen")
	for i := 0; i < count; i++ {
		db := openDb()
		defer db.Close()
		rows, err := db.Query("SELECT id from purchase_orders limit(100);")
    rows.Close()
		if err != nil {
			log.Printf("Got error: %v", err)
		}
	}
}
```

And the one that opens a connection then makes a bunch of SQL queries on that connection:

```go
func benchmarkQuery(count int) {
  defer timeTrack(time.Now(), "benchmarkQuery")
	db := openDb()
	defer db.Close()

	for i := 0; i < count; i++ {
		rows, err := db.Query("SELECT id from purchase_orders limit(100);")
    rows.Close()
		if err != nil {
			log.Printf("Got error: %v", err)
		}
	}
}
```

(Note: It's important to run `rows.Close()`, otherwise the connection is not immediately released - which means Go's `database/sql` will default to just opening a new connection for the next request, defeating this test.)

Using these two functions, we're prepared to find out:

1. What kind of impact does re-creating a db connection on each run have on the wall time of a 1-off execution?
2. Does using RDS proxy have an effect on that wall time?
3. What are the basic load characteristics of a db.r5.large getting hammered with new connection requests? How does RDS Proxy help it perform?

This inquiry is all motivated by the nature of Lambda functions, which the customer uses. And indeed Amazon recently released [RDS proxy](https://aws.amazon.com/rds/proxy/) to address this precise architecture:

> Many applications, including those built on modern serverless architectures, can have a large number of open connections to the database server, and may open and close database connections at a high rate, exhausting database memory and compute resources. Amazon RDS Proxy allows applications to pool and share connections established with the database, improving database efficiency and application scalability.

## RDS Proxy gotchas

Before we get into the benchmarks, I want to touch on the gotchas that sucked up a lot of my time. (I guess after all these years I haven't learned my lesson: The AWS UX is just not going to help you use their products. You have to read the user guide from end-to-end.)

Here are the two that tripped me up big time:

**1. RDS Proxy only supports specific database engine versions** – and the latest RDS Postgres is not one of them.

The first time I went to setup a new RDS proxy, I didn't see any available databases in the dropdown:

![No listed databases](./rds-proxy/empty-dropdown.png)

After much trial and error, I ended up spinning up a new Aurora db. At last, I saw _something_ populate the dropdown.

It wasn't until much later I learned that Postgres 12 - the engine I use everywhere - just isn't supported yet.

(What would have been nice: Showing me all my RDS databases in that drop-down, but just _greying out the ones that are not supported by Proxy_.)

**2. You can only connect to your RDS Proxy from inside _the same VPC_**

This one was a time sink. There are few things I dread more than trying to connect to something on AWS and getting a timeout. There are about a half-dozen layers (security groups, IGWs, subnets) that all have to be lined up _just so_ to get a connection online. The bummer is that there's no easy way to debug _where_ a given connection has failed.

So, the first wrench was when I discovered - after much trial and error - that I couldn't connect to my RDS Proxy from my laptop.

(How about a small bone, right next to "Proxy endpoint," that lets me know the limitations associated with this endpoint?)

## Now that we're up and connected

**RDS vs RDS Proxy**

With my RDS Proxy 101 hard-won, we're ready to run some benchmarks.

I copied the benchmark binary up to an EC2 server co-located with the RDS database and RDS proxy.

Per above:

- `benchmarkOpen` opens a connection and then runs a query on each loop (sequential)
- `benchmarkQuery` opens a connection first, then each loop is a query on that connection (also sequential)

Here's what I got running this against the RDS database directly, 1000 times per, with a 10s pause between:

```bash
# RDS direct, same datacenter
$ ./main
2020/12/25 00:08:14 Starting benchmarks...
2020/12/25 00:08:19 benchmarkOpen took 5.846447324s
2020/12/25 00:08:30 benchmarkQuery took 504.32703ms
```

So:

- `benchmarkOpen` - ~5.85ms per loop
- `benchmarkQuery` - ~0.500ms per loop

Fair to assume the connection handshake takes 5ms, which isn't bad.

This benchmark runs too fast to register on any RDS graphs. But if I loop the benchmark to sustain it over a couple minutes, I can see a healthy connection spike for the first test:

![graphs for direct queries](./rds-proxy/db-direct-graphs.png)

I recognize that in `benchmarkOpen`, instead of deferring `db.Close()` I could be nicer to my database and close it immediately. But I kind of like the DB slow boil.

Let's move on to the RDS Proxy. Remember, RDS Proxy has to be in the same VPC as the entity calling it as well as the database. So all three are co-located:

```bash
# RDS Proxy, same datacenter
$ ./main
2020/12/25 00:15:21 Starting benchmarks...
2020/12/25 00:15:28 benchmarkOpen took 7.094809555s
2020/12/25 00:15:39 benchmarkQuery took 1.239892867s
```

Comparing to the first benchmark: It appears the Proxy adds an overhead to each request of 0.7ms (1.2ms - 0.5ms). As we'd expect, connection openings take a bit longer, about 1.15ms longer.

And, sure enough, I can't get the graph to budge. It sustains about 100 connections, with no real CPU spike, with the benchmark sustained over several minutes:

![graphs using proxy](./rds-proxy/rds-with-proxy.png)

We'll see soon how all this performance translates to Lambda, but the motivation for RDS Proxy is clear: **It's less about the client, more about the database**. RDS Proxy is just a layer of indirection for our database connections. It doesn't speed up establishing new connections - in fact, we pay a small tax on connection opening. It just saves our database from the thundering herd.

**Cross-region**

Because we're here, I'm curious: What happens if we run this benchmark cross-region, from US-East-1 to US-West-2?

```bash
# RDS - different datacenter
$ ./main
2020/12/25 00:11:12 Starting benchmarks...
2020/12/25 00:15:53 benchmarkOpen took 4m41.607514892s
2020/12/25 00:17:15 benchmarkQuery took 1m11.767803425s
```

Different story:

- `benchmarkOpen` - ~281.6ms per loop
- `benchmarkQuery` - ~71.77ms per loop

Connections take about 200ms, presumably because they involve a lot of back and forth that require coast-to-coast travel.

The difference is over a full order of magnitude. At this point, opening a database connection and making a query starts to have performance characteristics similar to querying Airtable's API.

Indeed, the customer that opened this inquiry operates out of US-East-1. Bingo.

Opening connections is certainly a factor here. But RDS Proxy won't help, because as we've learned it's not intended to reduce client-side connection opening costs. And even if it did, we couldn't use it cross-region!

So co-locating the database in the customer's data center is the move, and will give their team performance an API can only dream of.

### Benchmarking Lambda

Lambda got us into this mess, so let's do an end-to-end benchmark with it. Our direct-to-RDS test will route like this:

```bash
API Gateway -> Lambda -> RDS
```

And our RDS Proxy test will look like this:

```bash
API Gateway -> Lambda -> RDS Proxy -> RDS
```

Each lambda function call will open a new database connection and issue a single query. I just adapted the function above to make one open/query as opposed to doing so inside a `for` loop.

I'll run a benchmark locally on my computer that will call the API Gateway endpoint. Because I'm running locally, I'm able to save a little work by using Go's native benchmarking facilities:

```go
func BenchmarkRequest(b *testing.B) {
	for i := 0; i < b.N; i++ {
		resp, err := http.Post("https://[REDACTED]/run", "application/json", nil)
		if err != nil {
			panic(err)
		}
		defer resp.Body.Close()
		if resp.StatusCode != 200 {
			fmt.Println("Received non-200 response. Continuing")
		}
	}
}
```

The first test routes to RDS directly:

```bash
$ go test -bench=. -benchtime 1000x
goos: darwin
goarch: amd64
pkg: github.com/acco/rds-proxy-bench
BenchmarkRequest-8   	    1000	 128684974 ns/op
PASS
ok  	github.com/acco/rds-proxy-bench	130.351s
```

So, 128ms per request.

Now using RDS Proxy:

```bash
$ go test -bench=. -benchtime 1000x
goos: darwin
goarch: amd64
pkg: github.com/acco/rds-proxy-bench
BenchmarkRequest-8   	    1000	 118576056 ns/op
PASS
ok  	github.com/acco/rds-proxy-bench	120.529s
```

118ms per request.

We're not impressed until we look at the graphs:

![graphs using proxy](./rds-proxy/proxy-lambda.png)

The first big spike is the first benchmark, hitting RDS directly. The second non-spike is the second benchmark, routed through the proxy.

The end-to-end Lambda benchmark takes it all home: The purpose of RDS Proxy is foremost to tame connections (and related load) on the database. Any happy gains on the _client_ side will be a result of a relieved database.


How to go further with Airtable using Postgres Arrays


While Airtable looks and feels like a database, you can't query it like a database.

That's why we built [Sequin](https://sequin.io/).

In this guide, you'll first replicate Airtable to Postgres using [Sequin](https://sequin.io/). Then, you'll unlock your Airtable data with some killer PostgreSQL array methods.

## Airtable and Postgres Arrays

Airtable's power lies in its flexibility. Thanks to Airtable's friendly UI, I can add new tables, new columns, or change the format of a column at will. It's great.

For example, let's say I have a Products table with a column that contains the product's image. But what happens later when I decide to keep more than one image per product? I just drag another image into the row! No need to migrate the column or my previous images.

To reflect this flexibility, when Airtable data is pulled into Postgres via Sequin, that data is often represented as [Postgres Arrays](https://www.postgresql.org/docs/9.1/arrays.html). So when a new image pops into the Products table, your Postgres table can support it immediately, without migrations or changes to existing queries. (The new image is simply another value in the array.)

Many field types in Airtable can contain multiple values: attachments, multiple select, lookups, and collaborators. With Sequin, these field types will all end up in Postgres as arrays. These might not be as familiar to you, but arrays are first-class in Postgres.

In a couple minutes you'll get the hang of Postgres Arrays and never look at Airtable the same way.

## Wait...a growing small business needs your help?

To make this guide more interesting, imagine you are helping a small business owner grow their business.

They've been using Airtable to catalog products, purchase orders, and sales orders. The business owner wants their Airtable base to remain simple and easy to use - but she needs more insight to grow.

To play along with this story, take a look at the [small business's inventory tracker base](https://airtable.com/shrV2tLjq2FbrRtO2) and add it to your workspace by clicking **Copy base** in the top right corner:

![Copy base](./postgres-arrays/copy_base.png)

> I’m going to assume that if you’ve made it this far, you already know Airtable is the bees knees and know how to set it up.

## Connect Airtable to Postgres

Now, let’s get your Airtable base into Postgres using [Sequin](https://sequin.io/):

1. Go to https://app.sequin.io/signup and create an account.
2. Connect your base to Sequin using the tutorial or check out the [Quickstart guide](https://docs.sequin.io/get-started).
3. In a couple seconds, you’ll be provisioned a Postgres database and Sequin will begin filling it with all your Airtable data.
4. Now [connect to your new Postgres database](https://docs.sequin.io/reference#tools-and-applications) with any standard SQL client. Tools like [TablePlus](https://tableplus.com/) and [PopSQL](https://popsql.com/) are great options (I’ll be using TablePlus).

## How Airtable looks in Postgres

Take a look at your Postgres schema.

First, you’ll see that all your Airtable table names have been converted to snake_case:

![Schema](./postgres-arrays/schema.png)

Second, you’ll notice that any field type in Airtable that could hold multiple values (like `lookup`, `multiple select` , and `attachment`) have been stored in columns of type `text[]`. `text[]` is an array of text fields:

![arrays](./postgres-arrays/product_array.png)

Arrays in Postgres are delimited with curly braces `{}`, instead of the common square bracket `[]` you are probably familiar with. (It will keep you on your toes.)

## Airtable and the PostgreSQL `ARRAY`

Before you start solving real problems for the small business you are assisting, let's just get comfortable with Postgres arrays.

For example, in your Inventory Tracker base, you can see that the Warehouse Locations table contains a `lookup` field where you can select which products are associated to a given warehouse location:

![product inventory](./postgres-arrays/warehouse_product_inventory.png)

You can select multiple products for each location. So in your synced Postgres database, the `warehouse_locations` table shows the `product_inventory` column as an array type containing multiple `record_id`s representing each selected product.

![tableplus](./postgres-arrays/tableplus.png)

Let's play with the `product_inventory` arrays.

Selecting the `product_inventory` column from the `warehouse_locations` table is simple:

```sql
SELECT product_inventory FROM warehouse_locations;
```

This will return all the `product_inventory` as a column full of arrays:

<img src="./postgres-arrays/product_inventory_arrays.png" alt="product inventory" style={{width: "400px"}} />

This is okay, but let’s say you want to select the first product listed on every warehouse shelf.

To do so, you need to access the values in the array. Postgres arrays are base-1 indexed (another fun way to keep you on your toes) - so to access the first value in the array we use `[1]` with square brackets. Like so:

```sql
SELECT product_inventory[1] FROM warehouse_locations;
```

Now you'll see the `record_id` for the first product on each shelf.

<img src="./postgres-arrays/product_inventory.png" alt="locations" style={{width: "300px"}} />

Again, when using Postgres with Airtable, any field that can contain multiple values will be an array. So this simple approach of extracting the first value of the array comes in handy.

As a practical example, in the Product Inventory table in your Airtable base, you can see that the Product Name column is a multi-select field. This is probably better represented as a text field - but then you don't get the pretty colors 😌:

![product name](./postgres-arrays/product_name.png)

To query for just the text of the product name, you'll use the array syntax again:

```SQL
SELECT product_name[1] FROM product_inventory;
```

This will return a clean list of product names:

<img src="./postgres-arrays/product_name_output.png" alt="product names" style={{width: "300px"}} />

## `unnest()`

With a grasp of how Postgres arrays work, let's begin to use SQL to extract some insights.

For example, let’s say you want to see which product colors are most appealing to customers. So you want to pull the list of colors for products that sell well. Let's say "sell well" for this small business means more than 5 sales.

To make get this data, you need to get a list of `colors` from the `product_inventory` table where the units sold are greater than 5:

```sql
SELECT colors FROM product_inventory WHERE units_sold[1]::integer > 5;
```

> Tip: The double colon next to `WHERE units_sold` is short-hand for `CAST`. Casting allows you to change the data type of a field - in this case from `text` to `integer` - so that you can evaluate the `> 5` statement. You'll use `CAST` often with your Airtable data as Sequin works to support more data types beyond `text`, `text[]`, and `bool`;

The results from this query are a little convoluted because all the colors return in arrays. Some of the colors are duplicated across rows so you can't really tell what the most popular colors are:

<img src="./postgres-arrays/color_arrays.png" alt="color arrays" style={{width: "400px"}} />

It would be nice to see each unique color in a clear list.

`unnest()` allows you to _un-nest_ each item of an array onto its own row:

```sql
SELECT
    DISTINCT UNNEST(colors) AS "Color",
    count(colors) AS "Frequency"
FROM product_inventory
WHERE units_sold[1]::integer > 5
GROUP BY "Color"
ORDER BY "Frequency" DESC;
```

Here, you use `DISTINCT` in conjunction with `unnest()` to get a clean list of the most popular colors. You are then using `AS` to give the column an easy to understand name - in this case "Color".

Then you use `count(colors)` (again, using `AS` to alias the column to the name "Frequency") to calculate how frequently each unique color is appearing. `count(colors)` works with `GROUP BY` so that `count(colors)` is tabulated against each unique Color.

Lastly, you use `ORDER BY` to sort the table so that the most popular color is at the top.

It looks like "Desert Brown" is a common color amongst the most popular products:

<img src="./postgres-arrays/colors.png" alt="colors" style={{width: "300px"}} />

## `ANY`

Building on your new insights around the popularity of "Desert Brown", you now want to understand which products are available in this desirable color.

We can use `ANY` to quickly check if a product's color array contains our desired color - in this case "Desert Brown"

```sql
SELECT product_name[1] FROM product_inventory WHERE 'Desert Brown' = ANY(colors);
```

Okay, some ideas are beginning to come together. Let’s unpack (dare I say unnest) this query.

First, because `product_name` is represented in your database as an array (you'll recall that product name is a `multiple select` in our Airtable base) we use `product_name[1]` to select the first item in the array. In this case, the name of the product.

Then, the `WHERE` clause is checking that the product colors array (`colors`) contains a string matching `Desert Brown`.

This produces a list of all the products with a "Desert Brown" color option:

<img src="./postgres-arrays/product_color.png" alt="Desert Brown Products" style={{width: "300px"}} />

## Airtable and PostgreSQL `JOIN`

Now, you're ready for some heavy lifting. Warehouse optimization.

It would be nice to see how much inventory is on each shelf in the warehouse. This might help the business owners use their inventory space more efficiently.

The first tool you might reach for is an Airtable lookup. But it won't render exactly what you want:

<img src="./postgres-arrays/airtable_mess.png" alt="Airtable mess" style={{width: "300px"}} />

In Airtable, you'd need to resort to writing complex functions. But now that your data is in Postgres, you can just use SQL to express what you want in a few lines.

To do so, you can use `ANY` with a `JOIN` to see how much inventory is on each shelf:

```sql
SELECT
    warehouse_locations.name AS "Shelf",
    SUM((product_inventory.units_ordered[1]::INTEGER) - (product_inventory.units_sold[1]::INTEGER)) AS "Inventory"
FROM warehouse_locations
JOIN product_inventory
    ON product_inventory.id = ANY (warehouse_locations.product_inventory)
GROUP BY warehouse_locations.name
ORDER BY warehouse_locations.name ASC;
```

You've already worked with most of the elements in this query, so let's highlight how this is all working together.

First, you use `CAST` to turn `units_ordered` and `units_sold` into `INTEGERS` so you can calculate the inventory of each product. You use `AS` to give each returned column a nice name.

Then, you use `ANY` to `JOIN` the available product inventory to each shelf.

Lastly, as with the prior query, you use `GROUP BY` and `ORDER BY` to organize and sort the output.

<img src="./postgres-arrays/shelf_inventory.png" alt="shelf inventory" style={{width: "300px"}} />

## Airtable and PostgreSQL `UNION`

There's one more SQL operator I find myself reaching for all the time: `UNION`. (It's not strictly related to Postgres arrays, so let's call this a bonus.)

`UNION` is a particularly helpful tool when working with Airtable because it can help you pull together similar data from across different tables.

As an example, perhaps you want to see all the store's product images in one place to prepare for a new marketing campaign. To do so, you'll need to pull product images from both the Product Inventory table as well as from the Purchase Orders table.

In SQL this is no big deal:

```sql
SELECT product_inventory.images[1] FROM product_inventory
UNION
SELECT purchase_orders.image[1] FROM purchase_orders;
```

Now, in one list you have access to all the product images appearing across the system. To do this natively in Airtable you would have needed to create a unique `Images` table with lookups...and restructure the entire base.

<img src="./postgres-arrays/product_images.png" alt="SQL union" style={{width: "300px"}} />

## Best of both worlds

When you combine Airtable with a Postgres database, you get all the amazing capabilities of Airtable plus the flexibility and reliability of PostgreSQL.

Now you can query, integrate and build on your data with the ease of Airtable and the power of SQL.


Posts