Supabase•7mo ago

Supabase - Stripe connect user with subscription

Hello, we just set up stripe integration with supabase and damn it was so easy to do :D. Now the biggest question for me is whats the best way to connect a user to the subscription. I tought about getting the subscription state ina edge function based on the user.email = subscription.email but what if the user changes the email in the subscription but not in our application?

17 Replies

LexumiOP•7mo ago

The normal app flow would be: User buys the subscription -> trigger on the subscription table would trigger a trigger that will call a edge function to create a user account and send a magic link to the user which logs him into the app and shows a set password dialog. (here i could set the customerID to the user) The issue is that if the user already has registered before and then afterwards buys a subscription on our store then the issue from the thread would apear Oh and how do i get data from the stripe tables in my edge function (deno)? supabase.from only allows from public

Marcus•7mo ago

I tought about getting the subscription state ina edge function based on the user.email = subscription.email but what if the user changes the email in the subscription but not in our applications

You can grab the customer update event in your webhook and update your tables accordingly.

Oh and how do i get data from the stripe tables in my edge function (deno)?

~~i'm pretty sure you need the server client and the service role to access other schemas~~ probably wouldnt hurt storing the stripe customer id in your user table

garyaustin•7mo ago

supabase.schema('schema').from('table') is the way you access other schemas IF you have them exposed in the API (slightly risky if customer data if you make a mistake). Just make sure you lock the schema itself to only service_role.

LexumiOP•7mo ago

hmm how do i get the stripe table?

LexumiOP•7mo ago

yeah thats what i wanted to do in that edge function. Like i said ig this would be the best flow: user has no account in supbase: user buys product -> stripe wrapper adds a new entry in stripe.customers -> trigger gets called -> trigger performs https call to edge function on-stripe-customer-create -> creates user in auth and users table (linking the customerId from stripe.customers to the public.users.stripe_customer_id i have tried adding a FK to stripe.customers.id but i cant select it in my public.users table as FK? why?

Marcus•7mo ago

Well are you sure the table is in the stripe schema? only thing I can think if is if the table doesnt have a primary key maybe it will not show up

hmm how do i get the stripe table?

And you've updated the type defs since introducing it?

garyaustin•7mo ago

The REST API only works on one schema so it can't use a foreign key to another schema if that was your last question about select. Also I don't know what you are doingn with awayt supabase.schema("")... And is the stripe schema added to the exposed schema settings in the dashboard and set up to work with service_role (if that is what you are using. https://supabase.com/docs/guides/api/using-custom-schemas Maybe show some code.

LexumiOP•7mo ago

i have just followed the documentation here: https://supabase.com/docs/guides/database/extensions/wrappers/stripe#enable-wrappers

// Follow this setup guide to integrate the Deno language server with your editor:
// https://deno.land/manual/getting_started/setup_your_environment
// This enables autocomplete, go to definition, etc.

// Setup type definitions for built-in Supabase Runtime APIs
import { createClient } from "jsr:@supabase/supabase-js@2";
import { Database } from "../types/database.types.ts";


Deno.serve(async (req: Request) => {
  const supabase = createClient<Database>(Deno.env.get("SUPABASE_URL")!, Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!);

  const cors = {
    "Access-Control-Allow-Origin": "*",
    "Access-Control-Allow-Methods": "POST, OPTIONS, GET",
    "Access-Control-Allow-Headers": "Apikey, X-Client-Info, Content-Type, Authorization, Accept, Accept-Language, X-Authorization",
  };

  if (req.method === "OPTIONS") {
    return new Response("ok", {
      headers: { ...cors },
    });
  }

  const body = (await req.json()) as { customerId: string };
  const customers = await supabase.schema("stripe").from("customers").select("*");
  console.log(customers);

  // return data
  return new Response(JSON.stringify(customers), {
    headers: { "Content-Type": "application/json", ...cors },
  });
});

// Follow this setup guide to integrate the Deno language server with your editor:
// https://deno.land/manual/getting_started/setup_your_environment
// This enables autocomplete, go to definition, etc.

// Setup type definitions for built-in Supabase Runtime APIs
import { createClient } from "jsr:@supabase/supabase-js@2";
import { Database } from "../types/database.types.ts";


Deno.serve(async (req: Request) => {
  const supabase = createClient<Database>(Deno.env.get("SUPABASE_URL")!, Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!);

  const cors = {
    "Access-Control-Allow-Origin": "*",
    "Access-Control-Allow-Methods": "POST, OPTIONS, GET",
    "Access-Control-Allow-Headers": "Apikey, X-Client-Info, Content-Type, Authorization, Accept, Accept-Language, X-Authorization",
  };

  if (req.method === "OPTIONS") {
    return new Response("ok", {
      headers: { ...cors },
    });
  }

  const body = (await req.json()) as { customerId: string };
  const customers = await supabase.schema("stripe").from("customers").select("*");
  console.log(customers);

  // return data
  return new Response(JSON.stringify(customers), {
    headers: { "Content-Type": "application/json", ...cors },
  });
});

Calling this edge function returns:

    "error": {
        "code": "PGRST106",
        "details": null,
        "hint": null,
        "message": "The schema must be one of the following: public, graphql_public"
    },
    "data": null,
    "count": null,
    "status": 406,
    "statusText": "Not Acceptable"
}

    "error": {
        "code": "PGRST106",
        "details": null,
        "hint": null,
        "message": "The schema must be one of the following: public, graphql_public"
    },
    "data": null,
    "count": null,
    "status": 406,
    "statusText": "Not Acceptable"
}

Stripe | Supabase Docs

garyaustin•7mo ago

Your error means you did not set the schema in the API settings as the link mentions.

LexumiOP•7mo ago

Yeah, im localy and i do not have a settings page. also we selfhost supabase for our dev/prod. is the setting in some config file?

garyaustin•7mo ago

Yes it would be in the settings file you set auth settings and other things in.

LexumiOP•7mo ago

think i found it for local dev:

config.toml
# Schemas to expose in your API. Tables, views and stored procedures in this schema will get API
# endpoints. `public` is always included.
schemas = ["public", "graphql_public"]

config.toml
# Schemas to expose in your API. Tables, views and stored procedures in this schema will get API
# endpoints. `public` is always included.
schemas = ["public", "graphql_public"]

great, now i get the stripe schema in suapbase.schema trying to execute a select returns this error now:

{
    "error": {
        "code": "42501",
        "details": null,
        "hint": null,
        "message": "permission denied for table wrappers_fdw_stats"
    },
    "data": null,
    "count": null,
    "status": 403,
    "statusText": "Forbidden"
}

{
    "error": {
        "code": "42501",
        "details": null,
        "hint": null,
        "message": "permission denied for table wrappers_fdw_stats"
    },
    "data": null,
    "count": null,
    "status": 403,
    "statusText": "Forbidden"
}

i have already executed:

GRANT USAGE ON SCHEMA stripe TO service_role;
GRANT ALL ON ALL TABLES IN SCHEMA stripe TO service_role;
GRANT ALL ON ALL ROUTINES IN SCHEMA stripe TO  service_role;
GRANT ALL ON ALL SEQUENCES IN SCHEMA stripe TO  service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA stripe GRANT ALL ON TABLES TO service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA stripe GRANT ALL ON ROUTINES TO  service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA stripe GRANT ALL ON SEQUENCES TO  service_role;

GRANT USAGE ON SCHEMA stripe TO service_role;
GRANT ALL ON ALL TABLES IN SCHEMA stripe TO service_role;
GRANT ALL ON ALL ROUTINES IN SCHEMA stripe TO  service_role;
GRANT ALL ON ALL SEQUENCES IN SCHEMA stripe TO  service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA stripe GRANT ALL ON TABLES TO service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA stripe GRANT ALL ON ROUTINES TO  service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA stripe GRANT ALL ON SEQUENCES TO  service_role;

garyaustin•7mo ago

Probably this: https://github.com/supabase/wrappers/issues/203 But I've not used wrappers.

GitHub

permission denied for table wrappers_fdw_stats error when `wrappe...

Bug report I confirm this is a bug with Supabase, not with my own application. I confirm I have searched the Docs, GitHub Discussions, and Discord. Describe the bug permission denied for table wrap...

LexumiOP•7mo ago

yeppp i was in that ticket: https://github.com/supabase/wrappers/issues/131 It worked with yours the one you found :)

GitHub

Updating to 0.1.16 from 0.1.14 broke my wrapper due to access issue...

Bug report I confirm this is a bug with Supabase, not with my own application. -> Well I can't be 100% sure... I confirm I have searched the Docs, GitHub Discussions, and Discord. Describe t...

LexumiOP•7mo ago

Cool thank you so much :D hmm meh i just realized you cannot create triggers on foreign tables. Do you have an idea on how we can execute a function when a new customer is added? ig only way would be a webhook that stripe can send data to

garyaustin•7mo ago

Seems like an edge function called by Stripe would be one way... https://supabase.com/docs/guides/functions/examples/stripe-webhooks

LexumiOP•7mo ago

yep okay, kinda sad thought i wouldnt have to do that xD

Gaming

Programming

Supabase - Stripe connect user with subscription

Did you find this page helpful?