> ## Documentation Index
> Fetch the complete documentation index at: https://docs.agentsoflearning.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Setup guide

# 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:

```bash theme={null}
# 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:REPLACE_WITH_PASSWORD@db.REPLACE_WITH_PROJECT_ID.supabase.co:5432/postgres
```

### How to Get Credentials

1. Go to [Supabase Dashboard](https://app.supabase.com/)
2. Select your project
3. Navigate to **Settings** → **API**
4. 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

```bash theme={null}
npm install @supabase/supabase-js
```

### Create Supabase Client

Create `lib/supabase/server.ts`:

```typescript theme={null}
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

```typescript theme={null}
// 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`:

```yaml theme={null}
dependencies:
  supabase_flutter: ^2.0.0
```

```bash theme={null}
flutter pub get
```

### Initialize Supabase

Create `lib/core/supabase/supabase_config.dart`:

```dart theme={null}
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`:

```dart theme={null}
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

```dart theme={null}
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

### Sign Up

```typescript theme={null}
// Backend
const { data, error } = await supabaseClient.auth.signUp({
  email: 'user@example.com',
  password: 'secure_password',
});
```

```dart theme={null}
// Flutter
final response = await supabase.auth.signUp(
  email: 'user@example.com',
  password: 'secure_password',
);
```

### Sign In

```typescript theme={null}
// Backend
const { data, error } = await supabaseClient.auth.signInWithPassword({
  email: 'user@example.com',
  password: 'secure_password',
});
```

```dart theme={null}
// Flutter
final response = await supabase.auth.signInWithPassword(
  email: 'user@example.com',
  password: 'secure_password',
);
```

### Get Current User

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

```dart theme={null}
// Flutter
final user = supabase.auth.currentUser;
```

***

## Running Migrations

### Option 1: Supabase CLI

```bash theme={null}
# 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

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

### Option 3: Programmatically (Backend)

```typescript theme={null}
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)

```typescript theme={null}
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

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

### Filter by Emotions

```typescript theme={null}
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

```typescript theme={null}
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:

```typescript theme={null}
// ❌ 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

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

***

## Related Documentation

* [Database Schema](../schemas/schema-v1.md)
* [PRD](../../product/prds/mvp-q1-2025-prd.md)
* [Supabase Docs](https://supabase.com/docs)

***

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