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:

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

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