Supabase PostgreSQL Setup

Status: Active Last Updated: 2025-01-06 Related Docs: Database Schema, System Overview Project: Architecture & Workflow Redesign (Phase 1)

Overview

EPGOAT uses Supabase PostgreSQL for production database hosting. This document covers setup, configuration, and connection details.

Why Supabase? - PostgreSQL 17.6 with full JSONB support - Built-in GUI for data exploration (no phpMyAdmin setup needed) - REST API auto-generated from schema - Row-level security for multi-tenancy - Free tier: 500MB database (sufficient for initial launch) - Compatible with DataGrip and other SQL IDEs

Project Details

Project Information: - Name: epgoat-production - Region: us-east-1 (AWS US East) - PostgreSQL Version: 17.6 - Plan: Free tier (500MB database, 2GB bandwidth/month) - Dashboard: https://supabase.com/dashboard/project/ezuujxdmkkbxmrpwdmyo

Database Connection: - Connection Method: Session Pooler (IPv4 compatible) - Host: aws-1-us-east-1.pooler.supabase.com - Port: 5432 - Database: postgres - User: postgres.ezuujxdmkkbxmrpwdmyo

Environment Configuration

Required Environment Variables

All credentials are stored in .env.supabase (NOT committed to git):

# Supabase Project Details
SUPABASE_URL=https://ezuujxdmkkbxmrpwdmyo.supabase.co
SUPABASE_ANON_KEY=sb_publishable_[KEY]
SUPABASE_SERVICE_ROLE_KEY=sb_secret_[KEY]

# Database Connection (Session Pooler - IPv4 Compatible)
SUPABASE_DB_HOST=aws-1-us-east-1.pooler.supabase.com
SUPABASE_DB_PORT=5432
SUPABASE_DB_NAME=postgres
SUPABASE_DB_USER=postgres.ezuujxdmkkbxmrpwdmyo
SUPABASE_DB_PASSWORD=[YOUR_PASSWORD]

# Connection String
DATABASE_URL=postgresql://postgres.ezuujxdmkkbxmrpwdmyo:[PASSWORD]@aws-1-us-east-1.pooler.supabase.com:5432/postgres

Security Notes

CRITICAL: Never commit .env.supabase to git - ✅ .env.supabase is in .gitignore - ✅ Use .env.supabase.example for templates - ✅ Store actual credentials in password manager

Connection Methods

Session Pooler vs Direct Connection

We use Session Pooler because: - ✅ IPv4 compatible (direct connection requires IPv6) - ✅ Connection pooling built-in - ✅ Better for serverless/lambda functions - ✅ Works on all networks

Direct Connection (IPv6 only): - Host: db.ezuujxdmkkbxmrpwdmyo.supabase.co - Port: 5432 - ❌ Not IPv4 compatible - Only use if you have native IPv6

Python Connection

Using psycopg2 (Recommended for migrations)

import psycopg2
import os
from dotenv import load_dotenv

load_dotenv('.env.supabase')

conn = psycopg2.connect(
    host=os.getenv('SUPABASE_DB_HOST'),
    port=os.getenv('SUPABASE_DB_PORT'),
    database=os.getenv('SUPABASE_DB_NAME'),
    user=os.getenv('SUPABASE_DB_USER'),
    password=os.getenv('SUPABASE_DB_PASSWORD')
)

cursor = conn.cursor()
cursor.execute('SELECT version();')
print(cursor.fetchone())

cursor.close()
conn.close()

Using Supabase Client (Recommended for application code)

from supabase import create_client
import os
from dotenv import load_dotenv

load_dotenv('.env.supabase')

client = create_client(
    os.getenv('SUPABASE_URL'),
    os.getenv('SUPABASE_SERVICE_ROLE_KEY')
)

# Example query
result = client.table('channel_names').select('*').limit(10).execute()
print(result.data)

Available Schemas

Supabase includes 20+ built-in schemas:

Core schemas: - public - Your application tables (default) - auth - Supabase authentication system - storage - Supabase file storage - graphql - Auto-generated GraphQL API - realtime - Real-time subscriptions

EPGOAT tables go in public schema (standard practice)

Free Tier Limits

Current Plan: Free - Database size: 500MB (plenty for initial launch) - Bandwidth: 2GB/month - Database connections: Unlimited (via session pooler) - API requests: Unlimited - Egress: 2GB/month included

When to upgrade: - Database exceeds 500MB → Supabase Pro ($25/month for 8GB) - At current estimate: Upgrade needed at ~1,000 users

Monitoring: - Check database size: SELECT pg_database_size('postgres') / 1024 / 1024 AS size_mb; - Set up alerts in Supabase dashboard at 400MB (80% threshold)

DataGrip Setup

Supabase works perfectly with DataGrip!

Connection settings: 1. Open DataGrip → New → Data Source → PostgreSQL 2. Fill in connection details: - Host: aws-1-us-east-1.pooler.supabase.com - Port: 5432 - Database: postgres - User: postgres.ezuujxdmkkbxmrpwdmyo - Password: [from .env.supabase] 3. Test connection → Should connect successfully 4. ✅ Full SQL IDE with autocomplete, query history, schema explorer

Migration Workflow

Running Migrations

# Using psql
psql "postgresql://postgres.ezuujxdmkkbxmrpwdmyo:[PASSWORD]@aws-1-us-east-1.pooler.supabase.com:5432/postgres" \
  -f backend/epgoat/infrastructure/backend/epgoat/infrastructure/database/migrations/010_create_new_schema.sql

# Or using Python migration script
python backend/epgoat/infrastructure/database/migrate.py up

Rollback

# Rollback last migration
python backend/epgoat/infrastructure/database/migrate.py down

# Or manual rollback via psql
psql "..." -f backend/epgoat/infrastructure/backend/epgoat/infrastructure/database/migrations/010_create_new_schema.sql
# (Run DOWN section)

Troubleshooting

Connection Issues

Problem: could not translate host name to address - Cause: Using Direct Connection on IPv4-only network - Solution: Switch to Session Pooler (see Connection Methods above)

Problem: password authentication failed - Cause: Incorrect password or username - Solution: Verify credentials in Supabase dashboard → Settings → Database

Problem: timeout connecting to database - Cause: Firewall blocking port 5432 - Solution: Check firewall rules, try different network

Database Size Monitoring

-- Check total database size
SELECT pg_database_size('postgres') / 1024 / 1024 AS size_mb;

-- Check size by table
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Security Best Practices

API Keys: - ✅ Use SUPABASE_SERVICE_ROLE_KEY for backend/migration scripts - ✅ Use SUPABASE_ANON_KEY for frontend (if needed) - ❌ Never expose SERVICE_ROLE_KEY in client-side code - ❌ Never commit keys to git

Row-Level Security: - Enable RLS for user-facing tables - Use policies to restrict access by user_id - Example: ALTER TABLE epg_data ENABLE ROW LEVEL SECURITY;

Connection Security: - All connections encrypted via SSL/TLS automatically - Session pooler provides additional connection security - Use environment variables, never hardcode credentials

Upgrade Path

When database exceeds 500MB:

1. Supabase Pro ($25/month):
2. 8GB database
3. 50GB bandwidth
4. 7-day backups

5. 24/7 support

6. Supabase Team ($599/month):

7. 100GB database
8. 250GB bandwidth
9. 14-day backups
10. Priority support

Alternative: Self-Hosted PostgreSQL - Oracle Cloud Free Tier (2 VMs, always free) - DigitalOcean Managed PostgreSQL ($15/month for 1GB) - AWS RDS PostgreSQL (starts ~$20/month)

Recommendation: Stay with Supabase Pro until 10,000+ users, then evaluate self-hosting

Related Documentation

• Database Schema - Full schema documentation
• Migration Guide - How to run migrations
• Architecture Redesign - Why Supabase?
• Core Principles - Cost consciousness

Support

Supabase Resources: - Dashboard: https://supabase.com/dashboard - Docs: https://supabase.com/docs - Support: https://supabase.com/support - Status: https://status.supabase.com

EPGOAT Internal: - Questions? Check #database channel - Issues? Create ticket with "supabase" label - Credentials lost? Check password manager