CurrenciCombo/docs/DATABASE_OPTIONS.md

# Database Options: Local vs Azure

## Overview

The system supports both local development databases and cloud-hosted Azure databases. Choose based on your needs:

- **Local**: Faster development, no costs, easier debugging
- **Azure**: Production-ready, scalable, managed service

---

## Option 1: Local PostgreSQL (Recommended for Development)

### Prerequisites
- Docker Desktop installed, OR
- PostgreSQL installed locally

### Setup with Docker (Easiest)

1. **Start PostgreSQL Container**
```powershell
docker run --name combo-postgres `
  -e POSTGRES_PASSWORD=postgres `
  -e POSTGRES_DB=comboflow `
  -p 5432:5432 `
  -d postgres:15
```

2. **Update orchestrator/.env**
```env
DATABASE_URL=postgresql://postgres:postgres@localhost:5432/comboflow
RUN_MIGRATIONS=true
```

3. **Run Migrations**
```powershell
cd orchestrator
npm run migrate
```

### Setup with Local PostgreSQL

1. **Install PostgreSQL**
   - Download from https://www.postgresql.org/download/
   - Install and start service

2. **Create Database**
```sql
CREATE DATABASE comboflow;
CREATE USER comboflow_user WITH PASSWORD 'your_password';
GRANT ALL PRIVILEGES ON DATABASE comboflow TO comboflow_user;
```

3. **Update orchestrator/.env**
```env
DATABASE_URL=postgresql://comboflow_user:your_password@localhost:5432/comboflow
RUN_MIGRATIONS=true
```

### Verify Connection
```powershell
# Test connection
cd orchestrator
npm run migrate

# Check health endpoint
Invoke-WebRequest -Uri "http://localhost:8080/health" -UseBasicParsing
```

---

## Option 2: Azure Database for PostgreSQL

### Prerequisites
- Azure account with subscription
- Azure CLI installed (`az` command)

### Setup Steps

1. **Create Resource Group**
```powershell
az group create --name comboflow-rg --location eastus
```

2. **Create PostgreSQL Flexible Server**
```powershell
az postgres flexible-server create `
  --resource-group comboflow-rg `
  --name comboflow-db `
  --location eastus `
  --admin-user comboflow_admin `
  --admin-password "YourSecurePassword123!" `
  --sku-name Standard_B1ms `
  --tier Burstable `
  --version 15 `
  --storage-size 32
```

3. **Configure Firewall (Allow Azure Services)**
```powershell
az postgres flexible-server firewall-rule create `
  --resource-group comboflow-rg `
  --name comboflow-db `
  --rule-name AllowAzureServices `
  --start-ip-address 0.0.0.0 `
  --end-ip-address 0.0.0.0
```

4. **Get Connection String**
```powershell
az postgres flexible-server show `
  --resource-group comboflow-rg `
  --name comboflow-db `
  --query "fullyQualifiedDomainName" `
  --output tsv
```

5. **Update orchestrator/.env**
```env
DATABASE_URL=postgresql://comboflow_admin:YourSecurePassword123!@comboflow-db.postgres.database.azure.com:5432/comboflow?sslmode=require
RUN_MIGRATIONS=true
```

### Azure App Service Integration

If deploying to Azure App Service:

1. **Add Connection String in App Service**
   - Go to Azure Portal → App Service → Configuration
   - Add `DATABASE_URL` as Connection String
   - Use format: `postgresql://user:pass@host:5432/db?sslmode=require`

2. **Enable Managed Identity (Recommended)**
```powershell
# Assign managed identity to App Service
az webapp identity assign `
  --resource-group comboflow-rg `
  --name comboflow-app

# Grant database access to managed identity
az postgres flexible-server ad-admin create `
  --resource-group comboflow-rg `
  --server-name comboflow-db `
  --display-name comboflow-app `
  --object-id <managed-identity-object-id>
```

---

## Option 3: Azure SQL Database (Alternative)

If you prefer SQL Server instead of PostgreSQL:

1. **Create SQL Database**
```powershell
az sql server create `
  --resource-group comboflow-rg `
  --name comboflow-sql-server `
  --location eastus `
  --admin-user comboflow_admin `
  --admin-password "YourSecurePassword123!"

az sql db create `
  --resource-group comboflow-rg `
  --server comboflow-sql-server `
  --name comboflow `
  --service-objective Basic
```

2. **Update Connection String**
```env
DATABASE_URL=mssql://comboflow_admin:YourSecurePassword123!@comboflow-sql-server.database.windows.net:1433/comboflow?encrypt=true
```

**Note**: Requires updating database schema and migrations for SQL Server syntax.

---

## Comparison

| Feature | Local PostgreSQL | Azure PostgreSQL | Azure SQL |
|---------|-----------------|------------------|-----------|
| **Cost** | Free | ~$15-50/month | ~$5-30/month |
| **Setup Time** | 5 minutes | 15 minutes | 15 minutes |
| **Scalability** | Limited | High | High |
| **Backup** | Manual | Automatic | Automatic |
| **High Availability** | No | Yes | Yes |
| **SSL/TLS** | Optional | Required | Required |
| **Best For** | Development | Production | Production (MS ecosystem) |

---

## Recommendation

### For Development
✅ **Use Local PostgreSQL with Docker**
- Fastest setup
- No costs
- Easy to reset/clear data
- Works offline

### For Production
✅ **Use Azure Database for PostgreSQL**
- Managed service (no maintenance)
- Automatic backups
- High availability
- Scalable
- Integrated with Azure services

---

## Migration Path

1. **Start Local**: Develop with local PostgreSQL
2. **Test Azure**: Create Azure database for staging
3. **Migrate Data**: Export from local, import to Azure
4. **Deploy**: Update production connection strings

### Data Migration Script
```powershell
# Export from local
pg_dump -h localhost -U postgres comboflow > backup.sql

# Import to Azure
psql -h comboflow-db.postgres.database.azure.com -U comboflow_admin -d comboflow -f backup.sql
```

---

**Last Updated**: 2025-01-15