Supabase Configuration for StartupBolt

Overview

In this guide, we'll walk you through the process of configuring Supabase for StartupBolt. You'll learn how to create tables to manage customer data, set up essential RPC functions, and configure triggers to handle payments and subscriptions.

To get started, navigate to Supabase > SQL Editor > New Query or Create a new snippet, paste the provided SQL queries, and execute them. Run each query separately by creating a new snippet.

Choosing a Name for Your Customers Table

By default, the Customers Table is named customers. However, depending on how you plan to use StartupBolt, you might want to customize this name. Below are two approaches you can take:

Option 1: Use a Fresh Supabase Project for Each SaaS Product

Supabase offers a free tier, but it has limitations suitable mainly for development purposes.
For production, you will need to use a paid plan, which costs $25/month for the first project and $10/month for each additional project (micro instance).
This approach allows you to use the default customers table name for each project.
If you choose this option, you can skip the rest of the steps below and proceed to the next section.

Option 2: Use a Single Supabase Project for Multiple SaaS Products

By using the same Supabase project, you can manage multiple SaaS products under a single project.
This saves costs, as you only pay for one Supabase project regardless of the number of products.
For this setup, you need to rename the Customers Table to something unique for each product.

Choose the Name for Your Customers Table

Below is an interactive form to customize your Customers Table name:

Now, let’s proceed to create the Customers Table.

Creating the Customers Table

First, let's create the customers table to store customer information.

CREATE TABLE customers (
  id UUID NOT NULL REFERENCES auth.users ON DELETE CASCADE,
  customer TEXT,
  name TEXT,
  price_id TEXT,
  credits INTEGER NOT NULL DEFAULT 0,
  email TEXT,
  provider TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC'),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC'),
  PRIMARY KEY (id)
);

Enabling RLS on the Customers Table

To enhance security and ensure users can only access their own data, enable Row-Level Security (RLS) on the customers table:

-- Enable RLS on the customers table
ALTER TABLE public.customers ENABLE ROW LEVEL SECURITY;

Creating RLS Policies for the Customers Table

Next, we'll create RLS policies to manage data access. These policies will ensure that authenticated users can only interact with their own data.

-- Policy for inserting own data
CREATE POLICY insert_own_customer ON public.customers
FOR INSERT WITH CHECK (auth.uid() = id);
 
-- Policy for reading own data
CREATE POLICY read_own_customer ON public.customers
FOR SELECT USING (auth.uid() = id);
 
-- Policy for updating own data
CREATE POLICY update_own_customer ON public.customers
FOR UPDATE USING (auth.uid() = id);
 
-- Policy for deleting own data
CREATE POLICY delete_own_customer ON public.customers
FOR DELETE USING (auth.uid() = id);

Tracking Last Update Timestamps

We'll create a function to automatically update the updated_at timestamp whenever a record is modified.

First, create the function:

-- First, create the function that will update the timestamp
CREATE OR REPLACE FUNCTION public.update_customer_timestamp()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
AS $$
BEGIN
    NEW.updated_at = now() AT TIME ZONE 'UTC';
    RETURN NEW;
END;
$$;

Then, set up the trigger to call this function before every update on the customers table:

-- Trigger to update the timestamp
CREATE TRIGGER update_customer_timestamp_trigger
BEFORE UPDATE ON public.customers
FOR EACH ROW
EXECUTE FUNCTION update_customer_timestamp();

Saving Processed Events

To ensure idempotency and prevent double-processing of webhook events, we'll create a processed_events table to record processed events.

CREATE TABLE processed_events (
  id TEXT,
  processed_at TIMESTAMP WITH TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC'),
  PRIMARY KEY (id)
);
 
ALTER TABLE processed_events ENABLE ROW LEVEL SECURITY;

Note: RLS policies are not needed for this table as it will only be accessed by server-side service roles.

Cleaning Up Processed Events (Optional)

To prevent the processed_events table from growing too large, we'll set up a cron job to delete records older than 30 days. The cron job will run daily at 3:00 AM UTC.

First, ensure the pg_cron extension is installed:

-- First, ensure pg_cron extension is installed
CREATE EXTENSION IF NOT EXISTS pg_cron;

Next, create a function to delete old records:

-- Create a function to delete old records
CREATE OR REPLACE FUNCTION public.delete_old_processed_events()
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
AS $$
BEGIN
    DELETE FROM public.processed_events
    WHERE processed_at < ((now() AT TIME ZONE 'UTC') - INTERVAL '30 days');
END;
$$;

Finally, schedule the cron job:

-- Schedule the job to run daily at 3:00 AM UTC
SELECT cron.schedule('0 3 * * *', 'SELECT delete_old_processed_events()');

Function to Increment Credits

Credits are incremented when a user makes a payment via Stripe or LemonSqueezy webhook. Create function to update the credits:

CREATE OR REPLACE FUNCTION public.increment_credits_customers(user_id uuid, increment_value int)
RETURNS void 
LANGUAGE plpgsql 
SECURITY DEFINER
SET search_path = ''
AS $$
BEGIN
    UPDATE public.customers
    SET credits = COALESCE(credits, 0) + increment_value
    WHERE id = user_id;
END;
$$;

Function to Decrement Credits

Credits are decremented when a user consumes them, typically in an AI/credit-based app. For example, if a user has 100 credits and they make a request that consumes 10 credits, the credits will be decremented by 10.

Create the function to decrement the credits:

CREATE OR REPLACE FUNCTION public.decrement_credits_customers(user_id uuid, decrement_value int)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
AS $$
BEGIN
    -- Ensure credits are decremented only if there are enough credits
    UPDATE public.customers
    SET credits = credits - decrement_value
    WHERE id = user_id
      AND credits >= decrement_value;
 
    -- If no rows are affected, raise an exception (e.g., insufficient credits)
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Insufficient credits for user %', user_id;
    END IF;
END;
$$;

Function to Get User ID from Email

This function allows you to retrieve a user ID based on their email, which is required for managing non-authenticated payments.

CREATE OR REPLACE FUNCTION public.get_user_id_email(p_email TEXT)
RETURNS JSON
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
AS $$
DECLARE
    user_data JSON;
BEGIN
    SELECT json_build_object(
        'id', id,
        'email', email
    ) INTO user_data
    FROM auth.users
    WHERE email = p_email;
    
    IF user_data IS NULL THEN
        RETURN json_build_object('error', 'User not found');
    END IF;
    
    RETURN user_data;
END;
$$;

Customers Table in `settings.js`

The settings.js file, located at /settings.js, is the central configuration file for your StartupBolt application. This file contains various settings that control different aspects of your app, allowing for easy customization and management of your project's core parameters. It is important to update these configuration values as per your product or service.

If you are using the default customers table because you chose Option 1 from the Choosing a Name for Your Customers Table, you can skip this step.
If you chose Option 2 and are using a custom table name, you need to update the settings.js file to reflect the correct table name.

const CUSTOMERS_TABLE_NAME = "customers"; // This is the default name. If you chose Option 2, update this to the correct table name.

For example, if your table name is customers_myawesomesaas, update the settings.js file as follows:

const CUSTOMERS_TABLE_NAME = "customers_myawesomesaas";

Final Remarks

This concludes the Supabase configuration for StartupBolt. By completing these steps, including setting up the necessary tables, functions, and triggers, your application will be fully equipped to manage customer data and handle payments efficiently. For any further customization or support, please reach out to the StartupBolt team.

Supabase Setup Google OAuth Setup