Database

membership

1、In Supabase SQL Editor, run this query to add a membership table (an extension of the authenticated user to store data like user membership level, expiration date etc...):

SQL Editor

create table
  public.membership (
    id bigint generated by default as identity,
    created_at timestamp with time zone not null default now(),
    email text null,
    user_id uuid null default gen_random_uuid (),
    member text null,
    constraint Membership_pkey primary key (id),
    constraint Membership_email_key unique (email)
  ) tablespace pg_default;

alter table public.membership enable row level security;

2、Go to the new membership table and add 2 RLS policies:

Enable read access for authenticated users only
Enable insert access for authenticated users only

order

1、In Supabase SQL Editor, run this query to add a order table(stores Stripe callback information like Stripe charge_id, user_id, etc...):

SQL Editor

create table
  public.order (
    id bigint generated by default as identity,
    created_at timestamp with time zone not null default now(),
    event_id text null,
    charge_id text null,
    description text null,
    currency text null,
    amount numeric null,
    state text null,
    created timestamp without time zone null,
    current_period_start timestamp without time zone null,
    current_period_end timestamp without time zone null,
    canceled_at timestamp without time zone null,
    payway smallint null,
    pay_channel text null,
    user_id text null,
    plan_id text null,
    invoice text null,
    customer text null,
    member text null,
    constraint Order2_pkey primary key (id)
  ) tablespace pg_default;

alter table public.order enable row level security;

2、Go to the new order table and add 2 RLS policies:

Enable read access for authenticated users only
Enable insert access for authenticated users only

Add query

When a user login, insert a record in the membership table.

SQL Editor

-- inserts a row into public.membership
create OR REPLACE FUNCTION public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = public
as $$
begin
  insert into public.membership (user_id, email)
  values (new.id, new.email);
  return new;
end;
$$;

-- trigger the function every time a user is created
DROP TRIGGER IF EXISTS on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

FeaturesSEO

FeaturesGoogle Oauth