Supabase Setup Guide

Overview

This tutorial will guide you through setting up a complete Supabase backend for the Mindful Assistant project. You'll create a new Supabase project, configure the database schema, set up authentication, and establish secure access policies.

ℹ️

What is Supabase?

Supabase is an open-source Firebase alternative that provides a PostgreSQL database, authentication, real-time subscriptions, and APIs out of the box.

What You'll Build

PostgreSQL database with conversation storage
User authentication system
Secure data access with Row Level Security (RLS)
API endpoints for the frontend application
Real-time data synchronization

Prerequisites

Before starting, ensure you have:

A Supabase account (free tier available) Basic understanding of SQL Access to your project's configuration files A code editor for updating configuration

Create Supabase Project

Step 1

Sign Up / Sign In to Supabase

Go to https://supabase.com
Click "Start your project" or "Sign in"
Sign up with GitHub, Google, or email

Step 2

Create New Project

Click "New Project" in your dashboard
Choose your organization (or create one)
Configure your project:

Project Configuration

Project Name: mindful-assistant
Database Password: [Choose a strong password]
Region: [Select closest to your users]
Pricing Plan: Free (for development)

⚠️

Important

Save your database password securely! You'll need it for direct database connections.

Step 3

Wait for Project Creation

Project creation typically takes 1-2 minutes. You'll see a progress indicator during setup.

Database Schema Setup

Step 1

Access SQL Editor

In your Supabase dashboard, click "SQL Editor" in the sidebar
You'll see the SQL editor interface

Step 2

Create Core Tables

Run the following SQL to create the main tables for conversation storage:

conversations_table.sql

-- Create conversations table
CREATE TABLE IF NOT EXISTS conversations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    title TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    is_archived BOOLEAN DEFAULT FALSE
);

-- Create messages table
CREATE TABLE IF NOT EXISTS messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    conversation_id UUID REFERENCES conversations(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    role TEXT NOT NULL CHECK (role IN ('user', 'assistant')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    metadata JSONB DEFAULT '{}'::jsonb
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_conversations_user_id ON conversations(user_id);
CREATE INDEX IF NOT EXISTS idx_conversations_created_at ON conversations(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_messages_conversation_id ON messages(conversation_id);
CREATE INDEX IF NOT EXISTS idx_messages_created_at ON messages(created_at DESC);

Step 3

Create Analysis Tables

Add tables for pain point detection and analysis:

analysis_tables.sql

-- Create pain points table
CREATE TABLE IF NOT EXISTS pain_points (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    conversation_id UUID REFERENCES conversations(id) ON DELETE CASCADE,
    pain_point TEXT NOT NULL,
    category TEXT,
    severity_score FLOAT,
    confidence_score FLOAT,
    first_detected_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_mentioned_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    occurrence_count INTEGER DEFAULT 1
);

-- Create user profiles for personalization
CREATE TABLE IF NOT EXISTS user_profiles (
    id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    display_name TEXT,
    preferences JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for analysis tables
CREATE INDEX IF NOT EXISTS idx_pain_points_user_id ON pain_points(user_id);
CREATE INDEX IF NOT EXISTS idx_pain_points_conversation_id ON pain_points(conversation_id);
CREATE INDEX IF NOT EXISTS idx_pain_points_category ON pain_points(category);

Step 4

Run the SQL Commands

Copy the first SQL block and paste it into the SQL editor
Click "Run" to execute the commands
Repeat for the second SQL block
Verify tables were created in the "Table Editor" section

Authentication Setup

Step 1

Configure Auth Settings

Go to "Authentication" > "Settings" in your Supabase dashboard
Configure the following settings:

Setting	Value	Description
Site URL	`http://localhost:3000`	Your development URL
Redirect URLs	`http://localhost:3000/**`	Allowed redirect patterns
Enable email confirmations	Enabled	Require email verification
Enable signup	Enabled	Allow new user registration

Step 2

Set Up Email Templates

Go to "Authentication" > "Email Templates"
Customize the confirmation and recovery email templates
Update the action URLs to match your domain

Step 3

Configure Auth Providers (Optional)

Enable additional auth providers if needed:

Google OAuth
GitHub OAuth
Discord OAuth

Row Level Security (RLS)

Step 1

Enable RLS on Tables

Row Level Security ensures users can only access their own data:

enable_rls.sql

-- Enable RLS on all tables
ALTER TABLE conversations ENABLE ROW LEVEL SECURITY;
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE pain_points ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;

Step 2

Create Security Policies

Define who can access what data:

rls_policies.sql

-- Conversations policies
CREATE POLICY "Users can view their own conversations" ON conversations
    FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can create their own conversations" ON conversations
    FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update their own conversations" ON conversations
    FOR UPDATE USING (auth.uid() = user_id);

CREATE POLICY "Users can delete their own conversations" ON conversations
    FOR DELETE USING (auth.uid() = user_id);

-- Messages policies
CREATE POLICY "Users can view messages in their conversations" ON messages
    FOR SELECT USING (
        conversation_id IN (
            SELECT id FROM conversations WHERE user_id = auth.uid()
        )
    );

CREATE POLICY "Users can create messages in their conversations" ON messages
    FOR INSERT WITH CHECK (
        conversation_id IN (
            SELECT id FROM conversations WHERE user_id = auth.uid()
        )
    );

-- Pain points policies
CREATE POLICY "Users can view their own pain points" ON pain_points
    FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can create their own pain points" ON pain_points
    FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update their own pain points" ON pain_points
    FOR UPDATE USING (auth.uid() = user_id);

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

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

CREATE POLICY "Users can create their own profile" ON user_profiles
    FOR INSERT WITH CHECK (auth.uid() = id);

Step 3

Test RLS Policies

Verify your policies work correctly by testing with different user contexts.

API Keys & Configuration

Step 1

Get Your API Keys

Go to "Settings" > "API" in your Supabase dashboard
Copy the following values:

API Configuration

URL: https://your-project-ref.supabase.co
Anon Key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...
Service Role Key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

⚠️

Security Note

Never expose your Service Role Key in client-side code. Use it only in server environments.

Step 2

Update Your Project Configuration

Add the keys to your project's configuration file:

config.yml

# Supabase Configuration
supabase:
  url: "https://your-project-ref.supabase.co"
  anon_key: "your-anon-key-here"
  service_role_key: "your-service-role-key-here"  # Server-side only!

# Database Connection (optional for direct access)
database:
  host: "db.your-project-ref.supabase.co"
  port: 5432
  database: "postgres"
  user: "postgres"
  password: "your-database-password"

Step 3

Environment Variables

For security, use environment variables in production:

.env

SUPABASE_URL=https://your-project-ref.supabase.co
SUPABASE_ANON_KEY=your-anon-key-here
SUPABASE_SERVICE_ROLE_KEY=your-service-role-key-here
DATABASE_URL=postgresql://postgres:your-password@db.your-project-ref.supabase.co:5432/postgres

Test Your Connection

Step 1

Install Supabase Client

Install the Supabase Python client in your project:

Terminal

pip install supabase

Step 2

Test Connection Script

Create a simple test to verify your setup:

test_supabase.py

import os
from supabase import create_client, Client

# Initialize Supabase client
url = "https://your-project-ref.supabase.co"
key = "your-anon-key-here"
supabase: Client = create_client(url, key)

def test_connection():
    try:
        # Test basic connection
        response = supabase.table('conversations').select("*").limit(1).execute()
        print("✅ Connection successful!")
        print(f"Response: {response}")

        # Test authentication
        auth_response = supabase.auth.get_session()
        print("✅ Authentication service accessible!")

    except Exception as e:
        print(f"❌ Connection failed: {e}")

if __name__ == "__main__":
    test_connection()

Step 3

Run the Test

Execute the test script to verify everything works:

Terminal

python test_supabase.py

✅

Success!

If you see "Connection successful!" your Supabase setup is complete and ready to use.

Troubleshooting

Connection Issues

Problem: "Failed to connect to Supabase"

Solutions:

Verify your Supabase URL and API keys are correct
Check if your project is paused (free tier limitation)
Ensure your network allows connections to Supabase

RLS Policy Errors

Problem: "Row level security policy violation"

Solutions:

Verify you're authenticated before making requests
Check that RLS policies allow your operation
Test policies in the SQL editor with different user contexts

Authentication Issues

Problem: Authentication not working

Solutions:

Check your Site URL in Authentication settings
Verify redirect URLs are configured correctly
Ensure email confirmations are properly set up
Check browser console for JavaScript errors

Performance Issues

Problem: Slow query performance

Solutions:

Add indexes to frequently queried columns
Use LIMIT clauses for large datasets
Optimize your RLS policies
Consider upgrading from the free tier

🆘

Need More Help?

Check out these resources:

Overview

What is Supabase?

What You'll Build

Prerequisites

Create Supabase Project

Sign Up / Sign In to Supabase

Create New Project

Important

Wait for Project Creation

Database Schema Setup

Access SQL Editor

Create Core Tables

Create Analysis Tables

Run the SQL Commands

Authentication Setup

Configure Auth Settings

Set Up Email Templates

Configure Auth Providers (Optional)

Row Level Security (RLS)

Enable RLS on Tables

Create Security Policies

Test RLS Policies

API Keys & Configuration

Get Your API Keys

Security Note

Update Your Project Configuration

Environment Variables

Test Your Connection

Install Supabase Client

Test Connection Script

Run the Test

Success!

Troubleshooting

Connection Issues

RLS Policy Errors

Authentication Issues

Performance Issues

Need More Help?

🎉 Congratulations!

Next Steps