Supabase Database

Supabase provides a hosted PostgreSQL database with real-time capabilities, Row Level Security, and seamless integration with authentication. This module is already integrated in the template.

Overview

Supabase Database offers PostgreSQL with built-in authentication integration, real-time subscriptions, and automatic API generation.

Key Features:

Hosted PostgreSQL database
Row Level Security (RLS)
Real-time subscriptions
Auto-generated REST API
Database migrations via CLI
Built-in authentication integration

Getting Started

1. Create Supabase Project

Go to supabase.com and create an account
Create a new project and wait for setup completion
Go to Settings → API to get your credentials:
- Project URL
- Anon/Public Key
- Service Role Key

2. Add Environment Variables

Add these to your .env.local file:


NEXT_PUBLIC_SUPABASE_URL=https://your-project-ref.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=your-anon-key-here
SUPABASE_SERVICE_ROLE_KEY=your-service-role-key-here

3. How It Works in the Template

The template includes pre-configured Supabase database:

Client Setup: Multiple clients for different use cases
Database Migrations: Pre-built schema with RLS policies
Type Generation: Automatic TypeScript types from schema
API Integration: Server and client-side database operations
Real-time Features: Live data updates and subscriptions

4. Template Structure


supabase/
├── migrations/        # Database schema and RLS policies
types/
├── supabase.ts       # Generated database types
lib/
├── supabase.ts       # Client-side Supabase config
├── supabase-server.ts # Server-side operations
├── supabase-service.ts # Service role for admin ops
├── supabase/profile.ts # Profile management utilities
app/api/
├── profiles/         # Profile management endpoints
├── auth/             # Authentication status

Database Schema

The template includes these pre-configured tables:

Profiles Table


-- User profiles (automatically created from auth)
CREATE TABLE public.profiles (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE UNIQUE,
  name TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

Subscription Tables (for payment templates)


-- Subscription management
CREATE TABLE public.subscriptions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  lemon_squeezy_id TEXT UNIQUE NOT NULL,
  status TEXT NOT NULL, -- active, cancelled, expired
  plan_type TEXT NOT NULL,
  current_period_end TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
 
-- Payment tracking
CREATE TABLE public.payments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  lemon_squeezy_id TEXT UNIQUE NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  currency TEXT NOT NULL,
  status TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

Automatic Profile Creation

The template includes database triggers:


-- Auto-create profile when user signs up
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (user_id)
  VALUES (new.id);
  RETURN new;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
 
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

Row Level Security

The template includes comprehensive RLS policies:

Profile Security


-- Users can only access their own profiles
CREATE POLICY "Users can read own profile" 
  ON public.profiles FOR SELECT 
  USING (auth.uid() = user_id);
 
CREATE POLICY "Users can update own profile" 
  ON public.profiles FOR UPDATE 
  USING (auth.uid() = user_id);

Subscription Security


-- Users can only see their own subscriptions
CREATE POLICY "Users can read own subscriptions" 
  ON public.subscriptions FOR SELECT 
  USING (auth.uid() = user_id);
 
-- Service role can manage all subscriptions (for webhooks)
CREATE POLICY "Service role can manage subscriptions" 
  ON public.subscriptions FOR ALL 
  USING (auth.role() = 'service_role');

Database Operations

The template provides utilities for common operations:

Profile Management


// Get user profile (implemented in template)
import { getUserProfile } from '@/lib/supabase/profile'
 
const profile = await getUserProfile()
 
// Update user profile
import { updateUserProfile } from '@/lib/supabase/profile'
 
await updateUserProfile({ name: 'New Name' })

Client-Side Queries


// In client components
import { createClient } from '@/lib/supabase'
 
const supabase = createClient()
 
// Get user's profile
const { data: profile } = await supabase
  .from('profiles')
  .select('*')
  .single()
 
// Update profile
const { error } = await supabase
  .from('profiles')
  .update({ name: 'New Name' })
  .eq('user_id', user.id)

Server-Side Operations


// In API routes or server components
import { createServerClient } from '@/lib/supabase-server'
 
const supabase = createServerClient()
 
// Get authenticated user's data
const { data: { user } } = await supabase.auth.getUser()
const { data: profile } = await supabase
  .from('profiles')
  .select('*')
  .eq('user_id', user.id)
  .single()

Real-time Subscriptions

Listen to database changes in real-time:


'use client'
import { useEffect, useState } from 'react'
import { createClient } from '@/lib/supabase'
 
function ProfileUpdates() {
  const [profiles, setProfiles] = useState([])
  const supabase = createClient()
 
  useEffect(() => {
    // Subscribe to profile changes
    const channel = supabase
      .channel('profile-changes')
      .on('postgres_changes', {
        event: '*',
        schema: 'public',
        table: 'profiles'
      }, (payload) => {
        // Handle real-time updates
        if (payload.eventType === 'INSERT') {
          setProfiles(prev => [...prev, payload.new])
        } else if (payload.eventType === 'UPDATE') {
          setProfiles(prev => 
            prev.map(p => p.id === payload.new.id ? payload.new : p)
          )
        }
      })
      .subscribe()
 
    return () => supabase.removeChannel(channel)
  }, [supabase])
}

API Routes Integration

The template includes database-connected APIs:

Profile API (`/api/profiles`)

GET - Fetch user profile data
POST - Update user profile information
Protected with authentication middleware

Server-Side Example


// app/api/profiles/route.ts (implemented in template)
import { createServerClient } from '@/lib/supabase-server'
 
export async function GET() {
  const supabase = createServerClient()
  
  const { data: { user } } = await supabase.auth.getUser()
  if (!user) {
    return Response.json({ error: 'Unauthorized' }, { status: 401 })
  }
 
  const { data: profile } = await supabase
    .from('profiles')
    .select('*')
    .eq('user_id', user.id)
    .single()
    
  return Response.json(profile)
}

Type Safety

The template generates TypeScript types:


# Generate types from your database schema
supabase gen types typescript --project-ref your-ref > types/supabase.ts

Using Generated Types


import { Database } from '@/types/supabase'
 
type Profile = Database['public']['Tables']['profiles']['Row']
type ProfileInsert = Database['public']['Tables']['profiles']['Insert']
type ProfileUpdate = Database['public']['Tables']['profiles']['Update']
 
// Type-safe database operations
const createProfile = async (profile: ProfileInsert): Promise<Profile> => {
  const { data, error } = await supabase
    .from('profiles')
    .insert(profile)
    .select()
    .single()
  
  if (error) throw error
  return data
}

Database Migrations

Using Supabase CLI


# Initialize Supabase locally
supabase init
 
# Link to your project
supabase link --project-ref your-project-ref
 
# Apply migrations
supabase db push

Creating New Migrations


# Create a new migration
supabase migration new add_user_preferences
 
# Edit the migration file in supabase/migrations/
# Then push to apply
supabase db push

Example Migration


-- supabase/migrations/add_user_preferences.sql
CREATE TABLE public.user_preferences (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  theme TEXT DEFAULT 'light',
  timezone TEXT DEFAULT 'UTC',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
 
-- Enable RLS
ALTER TABLE public.user_preferences ENABLE ROW LEVEL SECURITY;
 
-- Create policies
CREATE POLICY "Users can manage own preferences" 
  ON public.user_preferences 
  FOR ALL 
  USING (auth.uid() = user_id);

Webhook Integration

For payment templates, webhooks update the database:


// Service role client for webhook operations
import { createServiceClient } from '@/lib/supabase-service'
 
// In webhook handler (e.g., LemonSqueezy)
const supabase = createServiceClient() // Uses service role key
 
// Create subscription record
await supabase.from('subscriptions').insert({
  user_id: userId,
  lemon_squeezy_id: subscriptionId,
  status: 'active',
  plan_type: 'pro',
  current_period_end: new Date(renewsAt)
})

Environment Configuration

Development

The template works with Supabase’s hosted database out of the box.

Production

Same environment variables, just ensure:

Project is not paused (free tier)
RLS policies are properly configured
Service role key is securely stored

Advanced Features

Database Functions

Create custom SQL functions:


-- Create a custom function
CREATE OR REPLACE FUNCTION get_user_subscription_status(user_uuid UUID)
RETURNS TEXT AS $$
BEGIN
  RETURN (
    SELECT status
    FROM subscriptions 
    WHERE user_id = user_uuid 
    ORDER BY created_at DESC 
    LIMIT 1
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Call from your app:


const { data } = await supabase.rpc('get_user_subscription_status', {
  user_uuid: user.id
})

Full-Text Search


-- Add search functionality
ALTER TABLE profiles ADD COLUMN search_vector tsvector;
 
CREATE INDEX profiles_search_idx ON profiles 
USING gin(search_vector);
 
-- Update search vector automatically
CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS TRIGGER AS $$
BEGIN
  NEW.search_vector := to_tsvector('english', COALESCE(NEW.name, ''));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER update_profiles_search_vector
  BEFORE INSERT OR UPDATE ON profiles
  FOR EACH ROW EXECUTE FUNCTION update_search_vector();

Troubleshooting

RLS blocking queries:

Check policies match your authentication setup
Use service role key for admin operations
Verify auth.uid() returns expected user ID

Real-time not working:

Check table and schema names in subscription
Verify RLS allows reads on the table
Ensure WebSocket connections are allowed

Migration issues:

Check SQL syntax in migration files
Verify foreign key references exist
Ensure proper permissions for operations

Type generation failing:

Verify project reference is correct
Check network connectivity to Supabase
Ensure CLI is logged in: supabase login

Performance issues:

Add database indexes for frequently queried columns
Use select() to limit returned columns
Consider pagination for large datasets
Monitor queries in Supabase dashboard