institutional-trader/README/TROUBLESHOOTING.md

Troubleshooting Database Connection Issues

Error: "Invalid API key"

This error occurs when your Supabase API key is missing, incorrect, or expired.

Solution 1: Use Direct PostgreSQL Connection (RECOMMENDED)

For heavy SQL queries (which this system uses extensively), a direct PostgreSQL connection is faster and more reliable.

1. Get your database connection string from Supabase:

   • Go to Supabase Dashboard
   • Navigate to Settings > Database
   • Find Connection String section
   • Copy the URI connection string
   • It should look like: postgresql://postgres:[password]@db.[project-ref].supabase.co:5432/postgres

2. Add to your .env file:

   DATABASE_URL=postgresql://postgres:your_password@db.your-project-ref.supabase.co:5432/postgres
   

3. Restart your server

The system will automatically use the direct PostgreSQL connection when DATABASE_URL is set, which bypasses API key issues entirely.

Solution 2: Fix Supabase API Keys

If you prefer to use Supabase client:

1. Get your Supabase credentials:

   • Go to Supabase Dashboard
   • Navigate to Settings > API
   • Copy the following:
     • Project URL → SUPABASE_URL
     • service_role key (secret) → SUPABASE_SERVICE_KEY ⚠️ Use this one, not anon key

2. Create/update .env file in backend/ directory:

   # Supabase Configuration
   SUPABASE_URL=https://your-project-ref.supabase.co
   SUPABASE_SERVICE_KEY=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9... (your service_role key)
   
   # Optional: Direct PostgreSQL connection (recommended for heavy queries)
   DATABASE_URL=postgresql://postgres:password@db.project-ref.supabase.co:5432/postgres
   
   # Server Configuration
   PORT=3010
   NODE_ENV=development
   
   # CORS Configuration
   CORS_ORIGIN=http://localhost:5173
   

3. Important Notes:

   • ⚠️ Use service_role key, NOT anon key for backend operations
   • The service_role key has admin privileges and bypasses Row Level Security
   • Never expose the service_role key in frontend code
   • Keep your .env file in .gitignore (it should already be there)

4. Restart your server:

   cd backend
   npm run dev
   

Solution 3: Verify Environment Variables

Check that your environment variables are being loaded:

1. Verify .env file exists:

   ls -la backend/.env
   

2. Check if variables are loaded (add temporarily to test):

   // In backend/src/db.js, add temporarily:
   console.log('SUPABASE_URL:', process.env.SUPABASE_URL ? 'SET' : 'MISSING');
   console.log('SUPABASE_SERVICE_KEY:', process.env.SUPABASE_SERVICE_KEY ? 'SET' : 'MISSING');
   console.log('DATABASE_URL:', process.env.DATABASE_URL ? 'SET' : 'MISSING');
   

3. Make sure .env file is in the correct location:

   • Should be: backend/.env
   • NOT: .env (root directory)
   • NOT: backend/src/.env

Common Issues

Issue: "Missing Supabase environment variables"

• Cause: .env file doesn't exist or variables are misspelled
• Fix: Create .env file with correct variable names (case-sensitive)

Issue: "Invalid API key" but key looks correct

• Cause: Using anon key instead of service_role key
• Fix: Use the service_role key from Supabase Dashboard > Settings > API

Issue: Connection works but queries fail

• Cause: Missing exec_sql RPC function in Supabase
• Fix: Use DATABASE_URL for direct PostgreSQL connection (recommended)

Issue: Environment variables not loading

• Cause: .env file in wrong location or dotenv not configured
• Fix: Ensure .env is in backend/ directory and dotenv.config() is called in db.js

Recommended Setup

For best performance with this trading system, use direct PostgreSQL connection:

# Direct PostgreSQL Connection (RECOMMENDED)
DATABASE_URL=postgresql://postgres:your_password@db.your-project-ref.supabase.co:5432/postgres

# Optional: Supabase client (for simple queries)
SUPABASE_URL=https://your-project-ref.supabase.co
SUPABASE_SERVICE_KEY=your_service_role_key

This setup:

• ✅ Faster query execution
• ✅ No API key issues
• ✅ Direct SQL access
• ✅ Better for heavy analytical queries

Testing Connection

After fixing your configuration, test the connection:

cd backend
npm run dev

You should see:

✅ Database connection successful (PostgreSQL direct)

Or:

✅ Database connection successful (Supabase)

If you still see errors, check the console output for specific error messages and refer to the troubleshooting steps above.