Stripe Postgres Sync: Subscriptions in Your Own Database

Stripe Postgres sync means a copy of your Stripe customers, prices, subscriptions and invoices lives in tables in your own database and stays current through Stripe webhooks. Instead of calling the Stripe API on every page load, or writing and maintaining your own webhook handler, you join subscription status with your app's tables in SQL and use it in Row Level Security policies. On StitchDB this is built in from the Pro plan: add a key and a webhook endpoint, and the data appears in a stripe schema.

This guide covers the setup, the tables, gating features by subscription, the checkout flow and the edge cases that usually cause billing bugs.

#The usual way, and what goes wrong

Most subscription backends end up with the same hand-written pieces:

1. A webhook endpoint that verifies Stripe's signature.
2. A subscriptions table that the handler updates on customer.subscription.created, updated and deleted.
3. Code that handles events arriving twice or out of order.
4. A backfill script for when the webhook was down or a new column is needed.

Each one is small. Together they are where billing bugs live: a missed event leaves a cancelled customer with access, an out-of-order event downgrades a customer who just upgraded. A managed sync takes over all four.

#Set up the sync

1. Open Studio › Integrations › Stripe and add a Stripe secret key. A restricted key with read access is enough; create one in Stripe under Developers › API keys. Saving the key copies everything once.
2. In Stripe, under Developers › Webhooks, add an endpoint with the URL Studio shows, https://<project-ref>.p.stitchdb.com/stripe/v1/webhook, and select the events listed there.
3. Paste the endpoint's signing secret (whsec_…) into Studio.

Test-mode and live-mode keys both work; use the key, endpoint and signing secret from the same mode. When you roll the signing secret in Stripe, the previous one keeps working for 24 hours, so rotation does not drop events.

Studio then shows row counts per table, the last webhook received and any error from Stripe. Sync all again copies every object again, for example after the sync was off for a while.

#What lands in your database

Every table also has id, created, attrs (the Stripe object as JSON), updated_at and deleted. A few properties matter for correctness:

• Events are applied once each, and an older event never overwrites newer data. That removes the duplicate and out-of-order problems.
• Deleted customers, products and prices stay, with deleted = true, so old invoices still join.
• Amounts are in the smallest currency unit, as in Stripe: unit_amount = 1599 is $15.99.
• The tables are read-only. Anything you change in them is overwritten by the next event. Change data in Stripe, not in SQL.

Only the service_role can read the stripe schema, and the REST API does not expose it, so customers cannot query each other's billing data.

#Link Stripe customers to your users

The sync fills user_id from metadata.user_id. Set it when you create the customer, with the id of the signed-in user from StitchDB Auth. That one field is what lets SQL answer "is this user subscribed?".

Here is an Edge Function that creates a Checkout Session for the signed-in user:

import Stripe from 'npm:stripe@17'
import { createClient } from 'https://stitchdb.com/sdk/stitchdb-js.mjs'

const stripe = new Stripe(Deno.env.get('STRIPE_SECRET_KEY')!)

Deno.serve(async (req) => {
  const db = createClient(Deno.env.get('STITCHDB_URL')!, Deno.env.get('STITCHDB_ANON_KEY')!, {
    global: { headers: { Authorization: req.headers.get('Authorization') ?? '' } },
    auth: { persistSession: false },
  })
  const { data: { user } } = await db.auth.getUser()
  if (!user) return Response.json({ error: 'Sign in first' }, { status: 401 })

  const { price } = await req.json()
  const customer = await stripe.customers.create({ email: user.email, metadata: { user_id: user.id } })
  const session = await stripe.checkout.sessions.create({
    mode: 'subscription',
    customer: customer.id,
    client_reference_id: user.id,
    line_items: [{ price, quantity: 1 }],
    subscription_data: { metadata: { user_id: user.id } },
    success_url: 'https://example.com/billing?done=1',
    cancel_url: 'https://example.com/billing',
  })
  return Response.json({ url: session.url })
})

Store the secret key with stitchdb secrets set STRIPE_SECRET_KEY=... (secrets). This is a separate key from the restricted read key used by the sync, because creating customers and sessions needs write access. In production, look up an existing customer for the user before creating a new one, so a user who checks out twice does not get two customers.

From the app:

const { data, error } = await stitchdb.functions.invoke('create-checkout', {
  body: { price: 'price_123' },
})
if (data?.url) window.location.href = data.url

You do not need a webhook function for this. After payment, Stripe sends the subscription events to the sync endpoint, and stripe.subscriptions has a row with user_id and status = 'active' a moment later.

#Gate features with Row Level Security

This is where having the data in Postgres pays off. The helper stripe.has_active_subscription(user_id, price) is true when the user has a subscription that is active or trialing:

-- Only subscribers can read premium posts
create policy "subscribers read premium posts" on public.posts
  for select to authenticated
  using (not premium or stripe.has_active_subscription(auth.uid()));

-- A specific price (or one of the subscription's items)
select stripe.has_active_subscription(auth.uid(), 'price_123');

With that policy, the REST API, GraphQL, Realtime and every SDK enforce the paywall. A free user who calls the API directly with a modified client still gets no premium rows. Compare that with a flag in the frontend, which only hides a button.

The app also needs to know whether to show the upgrade screen. Wrap the helper in a function the client can call:

create function public.is_subscriber() returns boolean
language sql stable as $$
  select stripe.has_active_subscription(auth.uid())
$$;

const { data: subscribed } = await stitchdb.rpc('is_subscriber')

The function runs as the caller, so it can only ever answer for the signed-in user.

#Tiers, seats and plans

Use one price per tier and check by price in policies, for example stripe.has_active_subscription(auth.uid(), 'price_team') for team features. Give prices a lookup_key in Stripe so your code refers to team_monthly rather than an id that differs between test and live mode.

For seat-based plans, stripe.subscription_items.quantity holds the seat count. Compare it with the members of the user's organization in a trigger or function before adding a member.

#Let customers manage billing

Plan changes, card updates and cancellation are best left to the Stripe Customer Portal, created from another small Edge Function with stripe.billingPortal.sessions.create({ customer, return_url }). Whatever the customer changes there arrives through the same webhooks, and your policies follow.

Two statuses need a decision:

• cancel_at_period_end = true: the customer cancelled but has paid until current_period_end. The helper still returns true, which is usually what you want; show a "your plan ends on" note.
• past_due and unpaid: payment failed. The helper returns false for these, so access stops. If you want a grace period, write your own check against stripe.subscriptions in server code that runs with the service role.

#Reporting with SQL

Because it is all Postgres, revenue questions become queries. Run them in the SQL editor or from a server with the service role:

-- Active subscriptions by price
select p.lookup_key, count(*) as subscriptions
from stripe.subscriptions s
join stripe.prices p on p.id = s.price
where s.status in ('active', 'trialing')
group by p.lookup_key
order by subscriptions desc;

Join stripe.customers.user_id with your own tables to see which features paying users actually use.

#Plans and limits

Stripe sync is included from the Pro plan at no extra charge, and it runs on your project's own server. The Free plan does not include it. Pro is $15.99 a month as of November 2026; see pricing. Setup details are in the Stripe sync docs.

If you sell inside iOS and Android apps, the stores require their own billing for digital goods. StitchDB has a matching feature for App Store and Google Play purchases with the same "check it in a policy" model; see verify in-app purchases server-side.

#FAQ

#Do I still need my own Stripe webhook handler?

Not to keep subscription data in your database. You may still want one for side effects such as a welcome e-mail; write it as an Edge Function with verify_jwt = false and Stripe signature checks, or react to changes from a database trigger.

#Can I write to the stripe tables?

No. They are read-only copies. Changes are overwritten by the next event, so make changes in Stripe.

#What happens if webhooks were down?

Stripe retries failed deliveries. If the sync was off for longer, use Sync all again in Studio to copy every object again.

#Can the client read stripe tables directly?

No. Only the service_role can read the stripe schema, and the REST API does not expose it. Use stripe.has_active_subscription() in policies and functions.

Bill with Stripe and keep the data in Postgres: start on the Pro plan, or read Row Level Security first.