backend/docs/database_setup_guide.md

# 🗄️ Supabase Database Setup Guide

## 🚨 Current Issue: Network Connectivity

The connection test is failing with `[Errno 11003] getaddrinfo failed`, which indicates a network resolution issue.

## 🔧 Troubleshooting Steps

### 1. Verify Database URL Format
Current URL: `postgresql+asyncpg://postgres:@Jk=54##0@@6@1@db.txtxywmflduurfhfhnlu.supabase.co:5432/postgres`

**Issues identified:**
- Password contains special characters that need URL encoding
- Missing password in the connection string

### 2. Fix Database URL

The correct format should be:
```
postgresql+asyncpg://postgres:PASSWORD@db.txtxywmflduurfhfhnlu.supabase.co:5432/postgres
```

Where PASSWORD should be URL-encoded if it contains special characters.

### 3. Alternative: Use SQLite for Testing

Since we have SQLite fallback configured, let's test with SQLite first:

```bash
# Update .env to use SQLite temporarily
DATABASE_URL="sqlite+aiosqlite:///./aegislm_test.db"
```

## 📋 Database Schema Overview

Once connected, the following tables will be created:

### Core Tables
- ✅ `users` - User accounts and authentication
- ✅ `experiments` - AI evaluation experiments
- ✅ `datasets` - Dataset management
- ✅ `audit_trails` - Audit logging

### Business Tables
- ✅ `subscriptions` - User subscriptions
- ✅ `invoices` - Billing invoices
- ✅ `payment_methods` - Payment methods

### Analytics Tables
- ✅ `benchmarks` - Performance benchmarks
- ✅ `analytics` - Usage analytics
- ✅ `notifications` - User notifications
- ✅ `system_logs` - System logging

## 🚀 Next Steps

### Option 1: Fix Supabase Connection
1. Get the correct Supabase password
2. URL-encode special characters
3. Test connection again

### Option 2: Use SQLite for Development
1. Temporarily switch to SQLite
2. Complete database schema testing
3. Switch back to Supabase for production

### Option 3: Manual Setup
1. Use Supabase dashboard to create tables
2. Import SQL schema manually
3. Test with sample data

## 📄 SQL Schema (Manual Setup)

If needed, here's the basic SQL for manual table creation:

```sql
-- Users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    uuid UUID DEFAULT gen_random_uuid() UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Experiments table
CREATE TABLE experiments (
    id SERIAL PRIMARY KEY,
    uuid UUID DEFAULT gen_random_uuid() UNIQUE NOT NULL,
    run_id UUID UNIQUE NOT NULL,
    experiment_name VARCHAR(255) NOT NULL,
    status VARCHAR(50) DEFAULT 'pending',
    created_by INTEGER REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Add more tables as needed...
```

## 🎯 Recommendation

**For now, let's use SQLite** to continue with the "Making It Work" phase, then switch to Supabase once the connection is resolved.

This approach allows us to:
- ✅ Continue testing all components
- ✅ Validate database schema
- ✅ Test authentication system
- ✅ Complete Phase 4 testing
- ✅ Switch to Supabase later without code changes