Database

Setup

1. In Supabase SQL Editor, run this query to add a profiles table (an extension of the authenticated user to store data like Stripe customer_id, subscription access, etc...):
   SQL Editor1-- Create the profiles table in the public schema 2CREATE TABLE public.profiles ( 3 id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE, 4 name TEXT, 5 email TEXT, 6 image TEXT, 7 customer_id TEXT, 8 price_id TEXT, 9 has_access BOOLEAN DEFAULT false, 10 created_at TIMESTAMP WITH TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC'), 11 updated_at TIMESTAMP WITH TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC') 12); 13 14-- Create a function to update the updated_at timestamp 15CREATE OR REPLACE FUNCTION update_updated_at() 16RETURNS TRIGGER AS $$ 17BEGIN 18 NEW.updated_at = (now() AT TIME ZONE 'UTC'); 19 RETURN NEW; 20END; 21$$ LANGUAGE plpgsql; 22 23-- Create a trigger to automatically update the updated_at column 24CREATE TRIGGER update_profiles_updated_at 25BEFORE UPDATE ON public.profiles 26FOR EACH ROW 27EXECUTE FUNCTION update_updated_at(); 28 29-- Create a function to automatically add a profile on signup 30CREATE OR REPLACE FUNCTION public.handle_new_user() 31RETURNS TRIGGER AS $$ 32BEGIN 33 INSERT INTO public.profiles (id, email, name, image, created_at, updated_at) 34 VALUES ( 35 NEW.id, 36 NEW.email, 37 COALESCE(NEW.raw_user_meta_data->>'full_name', NEW.raw_user_meta_data->>'name'), 38 NEW.raw_user_meta_data->>'avatar_url', 39 (now() AT TIME ZONE 'UTC'), 40 (now() AT TIME ZONE 'UTC') 41 ); 42 RETURN NEW; 43END; 44$$ LANGUAGE plpgsql SECURITY DEFINER; 45 46-- Create a trigger to call the handle_new_user function on signup 47CREATE TRIGGER on_auth_user_created 48 AFTER INSERT ON auth.users 49 FOR EACH ROW EXECUTE FUNCTION public.handle_new_user(); 50
2. (Optional, but highly recommended) Add RLS policies to the profiles table to prevent users from accessing or updating other users' data. This is a security best practice.
   SQL Editor1-- Enable Row Level Security 2ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY; 3 4-- Create a policy to allow users to read their own data 5CREATE POLICY read_own_profile_data ON public.profiles 6FOR SELECT 7USING (auth.uid() = id); 8 9-- Create a policy to allow users to update their own data 10CREATE POLICY update_own_profile_data ON public.profiles 11FOR UPDATE 12USING (auth.uid() = id); 13 14-- Create a policy to allow users to insert their own data 15CREATE POLICY insert_own_profile_data ON public.profiles 16FOR INSERT 17WITH CHECK (auth.uid() = id); 18 19-- Create a policy to allow users to delete their own data 20CREATE POLICY delete_own_profile_data ON public.profiles 21FOR DELETE 22USING (auth.uid() = id); 23
3. (Optional) If you want to collect leads with ButtonLead, create a new table called leads and add a RLS policy with insert access for anyone:
   SQL Editor1-- Create the leads table 2create table public.leads ( 3 id uuid default gen_random_uuid(), 4 email text, 5 created_at timestamp with time zone default timezone('utc'::text, now()) not null, 6 7 primary key (id) 8); 9 10-- Enable Row Level Security 11alter table public.leads enable row level security; 12 13-- Create a policy to allow anyone to insert data 14create policy insert_lead on public.leads 15for insert 16to public 17with check (true); 18