Supabase Connection Setup Guide

Project: AI-Powered Photo Journaling iOS App Database: Supabase PostgreSQL Created: 2025-01-11 For: Backend and Frontend developers

Overview

This guide explains how to connect to the Supabase database from:

Backend (Next.js API routes)
Frontend (Flutter iOS app)

Supabase Project Information

Environment Variables Required

Create a .env.local file in your project root:

# Supabase Configuration
NEXT_PUBLIC_SUPABASE_URL=https://YOUR-PROJECT-ID.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=YOUR-ANON-KEY-HERE
SUPABASE_SERVICE_ROLE_KEY=YOUR-SERVICE-ROLE-KEY-HERE

# Database Direct Connection (optional, for migrations)
# DATABASE_URL=postgresql://postgres:[email protected]_WITH_PROJECT_ID.supabase.co:5432/postgres

How to Get Credentials

Go to Supabase Dashboard
Select your project
Navigate to Settings → API
Copy:
- Project URL → NEXT_PUBLIC_SUPABASE_URL
- anon public key → NEXT_PUBLIC_SUPABASE_ANON_KEY
- service_role key → SUPABASE_SERVICE_ROLE_KEY (keep secret!)

Backend Setup (Next.js)

Install Supabase Client

npm install @supabase/supabase-js

Create Supabase Client

Create lib/supabase/server.ts:

import { createClient } from '@supabase/supabase-js';

// Client for server-side operations (uses service role key)
export const supabaseServer = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!, // Full access
  {
    auth: {
      autoRefreshToken: false,
      persistSession: false
    }
  }
);

// Client for authenticated user operations (uses anon key + RLS)
export const supabaseClient = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
);

Using in API Routes

// pages/api/journal-entries/index.ts
import { supabaseClient } from '@/lib/supabase/server';

export default async function handler(req, res) {
  // Get authenticated user's JWT from request headers
  const token = req.headers.authorization?.replace('Bearer ', '');

  // Create client with user's session
  const supabase = createClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      global: {
        headers: {
          Authorization: `Bearer ${token}`
        }
      }
    }
  );

  // Query respects RLS policies (user can only see own entries)
  const { data, error } = await supabase
    .from('journal_entries')
    .select('*')
    .order('created_at', { ascending: false })
    .limit(20);

  if (error) {
    return res.status(500).json({ error: error.message });
  }

  return res.status(200).json({ entries: data });
}

Frontend Setup (Flutter)

Install Supabase Flutter Package

Add to pubspec.yaml:

dependencies:
  supabase_flutter: ^2.0.0

flutter pub get

Initialize Supabase

Create lib/core/supabase/supabase_config.dart:

import 'package:supabase_flutter/supabase_flutter.dart';

class SupabaseConfig {
  static const String supabaseUrl = String.fromEnvironment(
    'SUPABASE_URL',
    defaultValue: 'https://YOUR_PROJECT_ID.supabase.co',
  );

  static const String supabaseAnonKey = String.fromEnvironment(
    'SUPABASE_ANON_KEY',
    defaultValue: 'YOUR-ANON-KEY-HERE',
  );

  static Future<void> initialize() async {
    await Supabase.initialize(
      url: supabaseUrl,
      anonKey: supabaseAnonKey,
      authOptions: const FlutterAuthClientOptions(
        authFlowType: AuthFlowType.pkce,
      ),
    );
  }
}

// Global Supabase client instance
final supabase = Supabase.instance.client;

Using in Flutter App

Initialize in main.dart:

import 'package:flutter/material.dart';
import 'core/supabase/supabase_config.dart';

void main() async {
  WidgetsFlutterBinding.ensureInitialized();

  // Initialize Supabase
  await SupabaseConfig.initialize();

  runApp(MyApp());
}

Querying Data

import 'core/supabase/supabase_config.dart';

class JournalEntryRepository {
  Future<List<JournalEntry>> getEntries() async {
    try {
      // RLS policies automatically filter to current user's entries
      final response = await supabase
          .from('journal_entries')
          .select('*')
          .order('created_at', ascending: false)
          .limit(20);

      return (response as List)
          .map((json) => JournalEntry.fromJson(json))
          .toList();
    } catch (e) {
      throw Exception('Failed to fetch entries: $e');
    }
  }

  Future<void> createEntry(JournalEntry entry) async {
    try {
      await supabase.from('journal_entries').insert(entry.toJson());
    } catch (e) {
      throw Exception('Failed to create entry: $e');
    }
  }
}

Authentication

// Backend
const { data, error } = await supabaseClient.auth.signUp({
  email: '[email protected]',
  password: 'secure_password',
});

// Flutter
final response = await supabase.auth.signUp(
  email: '[email protected]',
  password: 'secure_password',
);

// Backend
const { data, error } = await supabaseClient.auth.signInWithPassword({
  email: '[email protected]',
  password: 'secure_password',
});

// Flutter
final response = await supabase.auth.signInWithPassword(
  email: '[email protected]',
  password: 'secure_password',
);

Get Current User

// Backend
const { data: { user } } = await supabaseClient.auth.getUser(token);

// Flutter
final user = supabase.auth.currentUser;

Running Migrations

Option 1: Supabase CLI

# Install Supabase CLI
npm install -g supabase

# Login
supabase login

# Link to your project
supabase link --project-ref YOUR_PROJECT_ID

# Push migrations
supabase db push

Option 2: Supabase Dashboard

Go to SQL Editor in Supabase Dashboard
Copy/paste migration SQL files
Run each migration in order (001, 002, 003, etc.)

Option 3: Programmatically (Backend)

import { supabaseServer } from '@/lib/supabase/server';
import fs from 'fs';
import path from 'path';

async function runMigrations() {
  const migrationsDir = path.join(process.cwd(), 'supabase/migrations');
  const files = fs.readdirSync(migrationsDir).sort();

  for (const file of files) {
    if (!file.endsWith('.sql')) continue;

    console.log(`Running migration: ${file}`);
    const sql = fs.readFileSync(path.join(migrationsDir, file), 'utf-8');

    const { error } = await supabaseServer.rpc('exec_sql', { sql });

    if (error) {
      console.error(`Migration ${file} failed:`, error);
      throw error;
    }
  }

  console.log('All migrations completed successfully');
}

Common Query Patterns

Get User’s Journal Entries (with Emotions)

const { data } = await supabase
  .from('journal_entries')
  .select(`
    *,
    entry_emotions (
      emotion:emotions (
        id,
        name,
        category,
        color_hex,
        icon_name
      )
    )
  `)
  .eq('user_id', userId)
  .order('created_at', { ascending: false });

Full-Text Search

const { data } = await supabase
  .from('journal_entries')
  .select('*')
  .textSearch('search_vector', 'beach & sunset', {
    type: 'websearch'
  })
  .order('created_at', { ascending: false });

Filter by Emotions

const { data } = await supabase
  .from('journal_entries')
  .select(`
    *,
    entry_emotions!inner (
      emotion:emotions!inner (
        name
      )
    )
  `)
  .in('entry_emotions.emotion.name', ['Happy', 'Grateful'])
  .order('created_at', { ascending: false });

Upload Photo to Storage

const { data, error } = await supabase.storage
  .from('journal-photos')
  .upload(`${userId}/${entryId}_original.jpg`, file, {
    contentType: 'image/jpeg',
    upsert: false
  });

// Get public URL
const { data: { publicUrl } } = supabase.storage
  .from('journal-photos')
  .getPublicUrl(`${userId}/${entryId}_original.jpg`);

Security Best Practices

1. Never Expose Service Role Key

DO NOT include service role key in frontend code
DO NOT commit service role key to version control
USE environment variables only
ROTATE keys if exposed

2. Use RLS Policies

All queries automatically respect Row Level Security policies:

Users can only access their own data
No need to manually filter by user_id in most queries
Policies are enforced at database level

3. Validate Input

Always validate user input before database operations:

// ❌ Bad: No validation
await supabase.from('journal_entries').insert(req.body);

// ✅ Good: Validate with Zod
const schema = z.object({
  journal_text: z.string().min(1).max(10000),
  photo_url: z.string().url(),
});

const validated = schema.parse(req.body);
await supabase.from('journal_entries').insert(validated);

Troubleshooting

Connection Issues

Error: Invalid API key

Fix: Verify NEXT_PUBLIC_SUPABASE_ANON_KEY is correct
Check Supabase Dashboard → Settings → API

Error: JWT expired

Fix: Refresh user’s auth token
Supabase client auto-refreshes tokens by default

RLS Policy Issues

Error: new row violates row-level security policy

Fix: Ensure user is authenticated (auth.uid() is not null)
Check that RLS policies allow the operation

Issue: Query returns no results

Cause: RLS policies filtering out data
Debug: Use service role key to bypass RLS (testing only!)

Migration Issues

Error: relation already exists

Cause: Migration already applied
Fix: Use IF NOT EXISTS in CREATE statements
Or track applied migrations in a separate table

Performance Tips

Use indexes: All common queries are indexed (see schema)
Limit results: Always use .limit() for list queries
Select specific columns: Don’t use SELECT * in production
Use connection pooling: Supabase handles this automatically
Cache frequently accessed data: Implement app-level caching

Need help? Contact @dba or @backend-developer for database questions.

Database

​Supabase Connection Setup Guide

​Overview

​Supabase Project Information

​Environment Variables Required

​How to Get Credentials

​Backend Setup (Next.js)

​Install Supabase Client

​Create Supabase Client

​Using in API Routes

​Frontend Setup (Flutter)

​Install Supabase Flutter Package

​Initialize Supabase

​Using in Flutter App

​Querying Data

​Authentication

​Sign Up

​Sign In

​Get Current User

​Running Migrations

​Option 1: Supabase CLI

​Option 2: Supabase Dashboard

​Option 3: Programmatically (Backend)

​Common Query Patterns

​Get User’s Journal Entries (with Emotions)

​Full-Text Search

​Filter by Emotions

​Upload Photo to Storage

​Security Best Practices

​1. Never Expose Service Role Key

​2. Use RLS Policies

​3. Validate Input

​Troubleshooting

​Connection Issues

​RLS Policy Issues

​Migration Issues

​Performance Tips

​Related Documentation