Database Setup

Overview

JCV Fitness uses Supabase PostgreSQL for data persistence with Row Level Security (RLS) to protect user data. This guide will walk you through setting up your database schema using migrations.

Database Architecture

The database consists of the following tables:

Table	Purpose
`profiles`	User profiles (extends `auth.users`)
`subscriptions`	Active subscriptions with payment info
`wizard_data`	Wizard form responses
`user_plans`	Generated fitness plans (freemium system)
`plan_downloads`	Download tracking and security

Prerequisites

Create a Supabase project

Go to Supabase
Click New Project
Fill in project details:
- Name: jcv-fitness
- Database Password: (save this securely)
- Region: Choose closest to your users
Wait for project to initialize (~2 minutes)

Get your database credentials

Once created, go to Settings → API and copy:

Project URL → NEXT_PUBLIC_SUPABASE_URL
anon public key → NEXT_PUBLIC_SUPABASE_ANON_KEY
service_role key → SUPABASE_SERVICE_ROLE_KEY

Add these to your .env.local file (see Environment Variables).

Run Database Migrations

The database schema is defined in SQL migration files located in supabase/migrations/.

Migration 1: Initial Schema

Open SQL Editor

In your Supabase dashboard, navigate to SQL Editor
Click New query

Run the initial schema migration

Copy the contents of supabase/migrations/001_initial_schema.sql and paste into the SQL editor.This migration creates:

✅ profiles table with RLS policies
✅ subscriptions table with RLS policies
✅ wizard_data table with RLS policies
✅ plan_downloads table with RLS policies
✅ Trigger to auto-create profiles on user signup
✅ Functions for subscription management
✅ Automatic updated_at triggers

Click Run to execute the migration.

-- Example: profiles table structure
CREATE TABLE IF NOT EXISTS public.profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT NOT NULL,
  full_name TEXT,
  has_active_subscription BOOLEAN DEFAULT FALSE,
  current_plan TEXT CHECK (current_plan IS NULL OR current_plan IN ('PLAN_BASICO', 'PLAN_PRO', 'PLAN_PREMIUM')),
  subscription_end_date TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

Migration 2: User Plans (Freemium System)

Run the user plans migration

Copy the contents of supabase/migrations/002_user_plans.sql and run it in the SQL editor.This migration adds:

✅ user_plans table for storing generated fitness plans
✅ Freemium logic (5-week free plans, 1-year paid plans)
✅ Functions: can_create_plan(), get_active_plan(), create_user_plan()
✅ Expiration automation with expire_old_plans()
✅ Download tracking for paid users

-- Example: user_plans table structure
CREATE TABLE IF NOT EXISTS public.user_plans (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  plan_data JSONB NOT NULL,
  plan_type TEXT NOT NULL DEFAULT 'free' CHECK (plan_type IN ('free', 'paid')),
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  expires_at TIMESTAMPTZ NOT NULL,
  is_active BOOLEAN DEFAULT TRUE,
  download_count INTEGER DEFAULT 0,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

Row Level Security (RLS)

All tables have RLS enabled to ensure users can only access their own data.

Profiles Table Policies

-- Users can view own profile
CREATE POLICY "Users can view own profile"
  ON public.profiles FOR SELECT
  USING (auth.uid() = id);

-- Users can update own profile
CREATE POLICY "Users can update own profile"
  ON public.profiles FOR UPDATE
  USING (auth.uid() = id);

Subscriptions Table Policies

-- Users can view own subscriptions
CREATE POLICY "Users can view own subscriptions"
  ON public.subscriptions FOR SELECT
  USING (auth.uid() = user_id);

-- Users can insert own subscriptions
CREATE POLICY "Users can insert own subscriptions"
  ON public.subscriptions FOR INSERT
  WITH CHECK (auth.uid() = user_id);

User Plans Table Policies

-- Users can view own plans
CREATE POLICY "Users can view own plans"
  ON public.user_plans FOR SELECT
  USING (auth.uid() = user_id);

-- Users can create own plans
CREATE POLICY "Users can insert own plans"
  ON public.user_plans FOR INSERT
  WITH CHECK (auth.uid() = user_id);

RLS policies are enforced at the database level, providing security even if your application code has bugs.

Database Functions

The migrations include several PostgreSQL functions for common operations:

Subscription Functions

has_active_subscription(user_uuid)

Purpose: Check if a user has an active subscriptionReturns: BOOLEANExample:

SELECT public.has_active_subscription('user-uuid-here');

get_active_subscription(user_uuid)

Purpose: Get details of user’s active subscriptionReturns: Table with id, plan_type, end_date, days_remainingExample:

SELECT * FROM public.get_active_subscription('user-uuid-here');

expire_old_subscriptions()

Purpose: Mark expired subscriptions as expired (run by cron)Returns: INTEGER (number of affected rows)Example:

SELECT public.expire_old_subscriptions();

Plan Functions

can_create_plan(user_uuid)

Purpose: Check if user can create a new planReturns: Table with can_create (boolean) and reason (text)Example:

SELECT * FROM public.can_create_plan('user-uuid-here');

Possible reasons:

already_has_plan: User has an active plan
free_used: User already used their free plan
NULL: User can create a plan

get_active_plan(user_uuid)

Purpose: Get user’s active plan with metadataReturns: Table with plan details, expiration, and download countExample:

SELECT * FROM public.get_active_plan('user-uuid-here');

create_user_plan(user_uuid, plan_data, plan_type)

Purpose: Create a new plan for a userParameters:

user_uuid: User ID
plan_data: JSONB containing wizard state
plan_type: ‘free’ or ‘paid’

Returns: UUID of created planExample:

SELECT public.create_user_plan(
  'user-uuid',
  '{"level": "beginner", "goal": "lose_weight"}'::jsonb,
  'free'
);

Triggers

When a user signs up via Supabase Auth, a profile is automatically created:

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id, email, full_name)
  VALUES (
    NEW.id,
    NEW.email,
    COALESCE(NEW.raw_user_meta_data->>'full_name', NEW.raw_user_meta_data->>'name')
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Auto-update `updated_at`

All tables automatically update their updated_at timestamp on modification.

Cron Jobs (Optional)

Set up automated tasks to expire old subscriptions and plans:

Navigate to Database Functions

In Supabase dashboard: Database → Functions

Create a cron job

Go to Database → Cron Jobs (requires Supabase Pro plan)Add a daily job to expire subscriptions:

SELECT cron.schedule(
  'expire-subscriptions',
  '0 0 * * *', -- Daily at midnight
  $$SELECT public.expire_old_subscriptions()$$
);

If you don’t have Supabase Pro, you can call these functions from your application code or use a service like GitHub Actions to run them periodically.

Verify Setup

Check that your tables were created successfully:

View tables

In Supabase dashboard: Table EditorYou should see:

✅ profiles
✅ subscriptions
✅ wizard_data
✅ user_plans
✅ plan_downloads

Test RLS policies

Try querying as an authenticated user:

-- This should only return data for the authenticated user
SELECT * FROM profiles WHERE auth.uid() = id;

Create a test user

Go to Authentication → Users → Add userAfter creating, check that a profile was auto-created in the profiles table.

Authentication Setup

Configure authentication providers

Go to Authentication → ProvidersEnable Email provider with:

Magic Link enabled
Email confirmation required (recommended)

Set site URL

Go to Authentication → URL ConfigurationAdd:

Site URL: http://localhost:3000 (development)
Redirect URLs:
- http://localhost:3000/auth/callback
- https://jcv24fitness.com/auth/callback (production)

Plan Types

The system supports three subscription plans:

Plan Type	Duration	Price	Features
`PLAN_BASICO`	1 month	Basic pricing	Basic features
`PLAN_PRO`	2 months	Mid-tier	Enhanced features
`PLAN_PREMIUM`	3 months	Premium	All features

Freemium System

Free plans: 5-week access, one per user
Paid plans: 1-year access, unlimited regeneration
Downloads: Only available for paid subscribers

Troubleshooting

Migration fails with syntax error

Ensure you’re copying the entire SQL file content
Check for any missing semicolons
Run migrations in order (001, then 002)

RLS policies blocking queries

Verify you’re authenticated when testing
Use SUPABASE_SERVICE_ROLE_KEY for admin access (server-side only)
Check policy definitions match your use case

Verify the trigger is installed: SELECT * FROM pg_trigger WHERE tgname = 'on_auth_user_created';
Check Supabase logs for trigger errors
Ensure profile table has all required columns

Setup

Contributing

Overview

Database Architecture

Prerequisites

Run Database Migrations

Migration 1: Initial Schema

Migration 2: User Plans (Freemium System)

Row Level Security (RLS)

Profiles Table Policies

Subscriptions Table Policies

User Plans Table Policies

Database Functions

Subscription Functions

Plan Functions

Triggers

Auto-update `updated_at`

Cron Jobs (Optional)

Verify Setup

Authentication Setup

Plan Types

Freemium System

Troubleshooting

Migration fails with syntax error

RLS policies blocking queries

Next Steps

Cloudflare Deployment

API Reference

Build docs developers (and LLMs) love

Setup

Contributing

​Overview

​Database Architecture

​Prerequisites

​Run Database Migrations

​Migration 1: Initial Schema

​Migration 2: User Plans (Freemium System)

​Row Level Security (RLS)

​Profiles Table Policies

​Subscriptions Table Policies

​User Plans Table Policies

​Database Functions

​Subscription Functions

​Plan Functions

​Triggers

​Auto-create Profile on Signup

​Auto-update updated_at

​Cron Jobs (Optional)

​Verify Setup

​Authentication Setup

​Plan Types

​Freemium System

​Troubleshooting

​Migration fails with syntax error

​RLS policies blocking queries

​Profile not auto-created on signup

​Next Steps

Cloudflare Deployment

API Reference

Build docs developers (and LLMs) love

Overview

Database Architecture

Prerequisites

Run Database Migrations

Migration 1: Initial Schema

Migration 2: User Plans (Freemium System)

Row Level Security (RLS)

Profiles Table Policies

Subscriptions Table Policies

User Plans Table Policies

Database Functions

Subscription Functions

Plan Functions

Triggers

Auto-create Profile on Signup

Auto-update `updated_at`

Cron Jobs (Optional)

Verify Setup

Authentication Setup

Plan Types

Freemium System

Troubleshooting

Migration fails with syntax error

RLS policies blocking queries

Profile not auto-created on signup

Next Steps