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, paste the provided SQL queries, and execute them.

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(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 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;
$$;

This concludes the Supabase configuration for StartupBolt. With these tables, functions, and triggers in place, your application will be equipped to manage customer data and handle payments.

Supabase Setup Google OAuth Setup