How we sync Stripe to manage trials and subscriptions

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:

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:

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.

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.