Setup and Configuration

Database Setup

Create a new Supabase project

First of all, you need to create a new Supabase project.

Once created, you need to add the following environment variables to the .env file:

SUPABASE_URL=https://your-project-id.supabase.co
SUPABASE_ANON_KEY=your-anon-key

Subscriptions Table

The subscriptions table manages user subscription data for Stripe integration. It has Row Level Security (RLS) enabled. Here we are going to keep track of the user's subscription data, like their stripe customer id, their subscription status, their plan, etc.

If you don't want to offer subscriptions just now, there is also the option of recording a lifetime subscription, which is a one-time payment for unlimited access to the app.

Table Creation SQL

To create the table, just copy the content of the setup_subscriptions_table.sql file and run it in the Supabase SQL Editor of your project.

Column Details

Column	Type	Default	Nullable	Description
`id`	bigint	ALWAYS	NO	Primary key, auto-incrementing (GENERATED ALWAYS)
`created_at`	timestamptz	now()	YES	Record creation timestamp
`user_id`	uuid	-	YES	Foreign key to auth.users(id)
`email`	text	-	YES	User email address (duplicated for admin convenience)
`stripe_id`	text	-	YES	Stripe customer/subscription ID
`stripe_status`	text	-	YES	Stripe subscription status
`stripe_price_id`	text	-	YES	Stripe price ID for the plan
`trial_ends_at`	timestamptz	-	YES	Trial period end date
`ends_at`	timestamptz	-	YES	Subscription end date
`start_date`	timestamptz	-	YES	Subscription start date
`last_event_date`	timestamptz	-	YES	Last Stripe webhook event date
`plan`	text	-	YES	Plan name/identifier
`lifetime_purchase`	boolean	false	NO	Whether this is a lifetime purchase vs subscription

These policies ensure users can only read their own subscription data. This is very important as we don't want users to be able to modify their own subscription data.

Migration Notes

Auth Schema: Ensure the auth.users table exists before creating the foreign key constraint
Extensions: The table uses uuid type, ensure the uuid-ossp extension is enabled if needed
RLS: The policies assume Supabase's auth.uid() function is available
Performance: Multiple indexes optimize common query patterns (user_id, stripe_status, lifetime_purchase)
Data Types: All timestamp columns use timestamp with time zone for proper timezone handling
Security: Comprehensive CRUD policies restrict access to authenticated users and their own data only
Email Field: The email field is intentionally duplicated for admin convenience - ensure it's populated when creating subscriptions