How we sync Stripe to manage trials and subscriptions
Tags: stripe, programming, react, sql
Published: 2021-10-05 20:49:28

In this post, I'll describe one of the ways we're using [Sequin](https://sequin.io) 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.