institutional-trader/README/LOCAL_DB_SETUP.md

Local PostgreSQL Database Setup

This guide will help you set up a local PostgreSQL database for development when you can't use the remote Supabase database.

Step 1: Install PostgreSQL

Windows

1. Download PostgreSQL:

   • Go to https://www.postgresql.org/download/windows/
   • Download the installer from EnterpriseDB
   • Run the installer

2. During installation:

   • Remember the password you set for the postgres user
   • Default port is 5432 (keep this)
   • Install pgAdmin if you want a GUI (optional)

3. Verify installation:

   psql --version
   

macOS

# Using Homebrew
brew install postgresql@15
brew services start postgresql@15

# Or download from: https://www.postgresql.org/download/macosx/

Linux (Ubuntu/Debian)

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo systemctl enable postgresql

Step 2: Configure Local Database

1. Create .env file in backend/ directory:

# Local PostgreSQL Configuration
LOCAL_DB_HOST=localhost
LOCAL_DB_PORT=5432
LOCAL_DB_USER=postgres
LOCAL_DB_PASSWORD=your_postgres_password
LOCAL_DB_NAME=institutional_trader

# Use local database (set this to use local instead of remote)
USE_LOCAL_DB=true

# Server Configuration
PORT=3010
NODE_ENV=development

# CORS Configuration
CORS_ORIGIN=http://localhost:5173

Important: Replace your_postgres_password with the password you set during PostgreSQL installation.

Step 3: Set Up Database and Tables

Run the setup script:

cd backend
npm run setup-local-db

This will:

• ✅ Create the database institutional_trader
• ✅ Create all necessary tables
• ✅ Create indexes for performance
• ✅ Test the connection

Step 4: Import CSV Files

Once the database is set up, import your CSV files:

npm run import-csv

This will import:

• OptionsFlow.csv → OptionsFlow_monthly table
• OptionsVolume.csv → OptionsVolume table
• OptionsOpenInterest.csv → OptionsOpenInterest table

Step 5: Update Database Connection

The system will automatically use the local database when USE_LOCAL_DB=true is set in your .env file.

Troubleshooting

Error: "Cannot connect to PostgreSQL server"

1. Check if PostgreSQL is running:

   # Windows
   # Check Services (services.msc) for "postgresql" service
   
   # macOS/Linux
   sudo systemctl status postgresql
   # or
   brew services list
   

2. Start PostgreSQL if not running:

   # Windows: Start from Services
   # macOS
   brew services start postgresql@15
   # Linux
   sudo systemctl start postgresql
   

3. Verify connection:

   psql -U postgres -h localhost
   

Error: "password authentication failed"

1. Check your .env file has the correct password
2. Try resetting the postgres password:

   # Windows: Use pgAdmin or reset via Services
   # macOS/Linux
   sudo -u postgres psql
   ALTER USER postgres PASSWORD 'newpassword';
   

Error: "database does not exist"

Run the setup script again:

npm run setup-local-db

Error: "permission denied"

On Linux/macOS, you might need to:

sudo -u postgres createdb institutional_trader

Or create a new user:

sudo -u postgres createuser -s your_username

Using pgAdmin (Optional GUI)

1. Open pgAdmin (installed with PostgreSQL)
2. Connect to local server (password: your postgres password)
3. You can view/edit data, run queries, etc.

Verify Setup

After setup, verify everything works:

# Test database connection
cd backend
npm run setup-local-db

# Import CSV files
npm run import-csv

# Start server
npm run dev

You should see:

✅ Database connection successful (PostgreSQL direct)

Switching Between Local and Remote

To switch between local and remote databases:

Use Local Database:

USE_LOCAL_DB=true
LOCAL_DB_HOST=localhost
LOCAL_DB_PORT=5432
LOCAL_DB_USER=postgres
LOCAL_DB_PASSWORD=your_password
LOCAL_DB_NAME=institutional_trader

Use Remote Database:

USE_LOCAL_DB=false
DATABASE_URL=postgresql://postgres:password@host:5432/database
# OR
SUPABASE_URL=https://your-project.supabase.co
SUPABASE_SERVICE_KEY=your_key

Next Steps

1. ✅ PostgreSQL installed and running
2. ✅ Database created and tables set up
3. ✅ CSV files imported
4. ✅ Server running with local database

You're ready to develop locally! 🎉