# SQLite → PostgreSQL Migration Path

**Created:** 2026-06-12
**Current state:** Raw sqlite3 via `app/models.py`, single file `relay.db` (~320KB, 2 users)
**Trigger point:** 100+ concurrent connections or >500MB DB file

---

## Why Not Yet

Current scale doesn't warrant it:
- 2 users, 7 sites, ~4 submissions, ~15 documents
- SQLite handles ~100K writes/sec on SSD — 1000x current load
- Single-writer bottleneck not a problem yet (webhooks go through RQ queue)

Migrate when **any** of these hit:
- DB file > 500MB
- >5 concurrent gunicorn workers contending for write lock
- Need for read replicas (analytics dashboards)
- Multi-region deployment

## Architecture Change

```
BEFORE:                    AFTER:
─────────────              ─────────────
                         ┌──────────┐
app/models.py ──sqlite3──►relay.db  app/models.py ──SQLAlchemy──►┌──────────┐
                         └──────────┘                          │ PostgreSQL │
                                                               └──────────┘
```

### What Changes

| Layer | Before | After |
|-------|--------|-------|
| Connection | `sqlite3.connect(DB_PATH)` | SQLAlchemy `create_engine(DATABASE_URL)` |
| Queries | Raw SQL + `.fetchall()` | SQLAlchemy Core expressions + row objects |
| Transactions | Manual `commit()`/`rollback()` | Context manager `with engine.begin():` |
| Schema | `CREATE TABLE` in `init_db()` | Alembic migrations |
| Docker | `./data:/app/data` | `postgres:16` service + persistent volume |

### What Doesn't Change

- Business logic (tier checks, encryption, webhook formatting)
- API endpoints, blueprints, routes
- UI templates, admin panel
- Stripe integration, email service

## Implementation Plan

### Phase 1 — SQLAlchemy Abstraction Layer

1. Add `SQLAlchemy` + `alembic` to `requirements.txt`
2. Create `app/db.py`:
   ```python
   from sqlalchemy import create_engine
   from sqlalchemy.orm import sessionmaker
   import os

   DATABASE_URL = os.environ.get(
       "DATABASE_URL",
       "sqlite:///" + os.environ.get("RELAY_DB_PATH", "/app/data/relay.db")
   )
   engine = create_engine(DATABASE_URL, echo=False)
   Session = sessionmaker(bind=engine)
   ```
3. Replace `get_db()` with `Session()` context manager
4. Map raw SQL queries → SQLAlchemy Core expressions (no ORM models yet)
5. Test against SQLite first (same data, different access pattern)

### Phase 2 — PostgreSQL Schema DDL

Key type mappings:

| SQLite | PostgreSQL | Notes |
|--------|-----------|-------|
| `INTEGER PRIMARY KEY` | `SERIAL PRIMARY KEY` | Auto-increment |
| `INTEGER` (0/1 bool) | `BOOLEAN` | Convert flags |
| `TEXT` (JSON) | `JSONB` | Indexable, faster queries |
| `TIMESTAMP` | `TIMESTAMPTZ` | Timezone-aware |
| `REAL` | `DOUBLE PRECISION` | Same precision |
| `JSON` | `JSONB` | Already using JSONB-compatible format |

Tables requiring special attention:

- **`users`** — `email_hash`, `email_encrypted` (TEXT) → no change
- **`sites`** — `field_config` (TEXT/JSON) → `JSONB`
- **`submissions`** — `data` (TEXT/JSON) → `JSONB`
- **`documents`** — `data` (JSON) → `JSONB`
- **`webhook_logs`** — `last_error` (TEXT) → `TEXT` (fine)
- **`api_keys`** — `permissions` (TEXT/JSON) → `JSONB`

### Phase 3 — Migration Script

Use `pgloader` for zero-downtime migration:

```bash
# 1. Dump SQLite to SQL
sqlite3 /app/data/relay.db ".dump" > /tmp/relay_dump.sql

# 2. Load into PostgreSQL
PGPASSWORD=$PGPASSWORD psql -h postgres -U agentforms -d agentforms < /tmp/relay_dump.sql

# 3. Fix types post-load
PGPASSWORD=$PGPASSWORD psql -h postgres -U agentforms -d agentforms -c "
  ALTER TABLE users ALTER COLUMN email_verified TYPE BOOLEAN USING email_verified::boolean;
  ALTER TABLE users ALTER COLUMN is_admin TYPE BOOLEAN USING is_admin::boolean;
  ALTER TABLE sites ALTER COLUMN webhook_enabled TYPE BOOLEAN USING webhook_enabled::boolean;
  -- ... repeat for all INTEGER(0/1) columns
  ALTER TABLE sites ALTER COLUMN field_config TYPE JSONB USING field_config::jsonb;
  ALTER TABLE submissions ALTER COLUMN data TYPE JSONB USING data::jsonb;
"
```

Alternative: write Python migration script with `sqlite3` → `psycopg2`:

```python
import sqlite3
import psycopg2

src = sqlite3.connect('/app/data/relay.db')
dst = psycopg2.connect('postgresql://agentforms:pass@postgres:5432/agentforms')

# Copy table by table, converting types as needed
for table in ['users', 'sites', 'submissions', ...]:
    src_cur = src.execute(f'SELECT * FROM {table}')
    cols = [desc[0] for desc in src_cur.description]
    rows = src_cur.fetchall()
    dst_cur = dst.cursor()
    for row in rows:
        placeholders = ', '.join(['%s'] * len(cols))
        col_names = ', '.join(cols)
        dst_cur.execute(f"INSERT INTO {table} ({col_names}) VALUES ({placeholders})", row)
    dst.commit()
```

### Phase 4 — Docker Compose Update

```yaml
# Add to docker-compose.yml
services:
  postgres:
    image: postgres:16-alpine
    container_name: agentforms-postgres
    environment:
      POSTGRES_DB: agentforms
      POSTGRES_USER: agentforms
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-change-me}
    volumes:
      - pg-data:/var/lib/postgresql/data
    ports:
      - "127.0.0.1:5432:5432"  # Local-only for security
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U agentforms"]
      interval: 10s
      timeout: 5s
      retries: 5

volumes:
  pg-data:
```

Add to relay service:
```yaml
  relay:
    depends_on:
      redis:
        condition: service_healthy
      postgres:
        condition: service_healthy
    environment:
      - DATABASE_URL=postgresql://agentforms:${POSTGRES_PASSWORD}@postgres:5432/agentforms
```

### Phase 5 — Rollback Plan

**Keep SQLite as fallback:**
1. Before migration: `cp /app/data/relay.db /app/data/relay.db.pg-backup`
2. App reads `DATABASE_URL` env var — fall back to SQLite if PG unavailable
3. Rollback command: `docker compose restart relay` (with `DATABASE_URL` pointing back to SQLite)

```python
# In app/db.py — graceful fallback
try:
    engine = create_engine(os.environ.get("DATABASE_URL", "sqlite:///..."))
    engine.connect()  # Test connection
except Exception as e:
    log.warning("db", f"Primary DB unavailable ({e}), falling back to SQLite")
    engine = create_engine("sqlite:///" + DB_PATH)
```

## Timeline

| Phase | Effort | Risk |
|-------|--------|------|
| 1. SQLAlchemy layer | 2-3 hours | Low — test against SQLite first |
| 2. Schema DDL | 1 hour | Low — documented mappings |
| 3. Data migration | 1 hour | Medium — verify row counts + checksums |
| 4. Docker compose | 30 min | Low — additive, doesn't break existing |
| 5. Rollback plan | 30 min | Low — env var toggle |

**Total: 5-6 hours**

## Verification Checklist

- [ ] All existing routes return same results against PostgreSQL
- [ ] Webhook delivery still works (enqueue → worker → deliver)
- [ ] Rate limiting counters persist across restarts (Redis — no change)
- [ ] Database backups work (pg_dump instead of cp)
- [ ] Account deletion cascade works (FK constraints)
- [ ] Concurrent submissions don't deadlock
- [ ] Rollback to SQLite works within 2 minutes

## When to Actually Do It

Not now. Current load:
- 2 users, 7 sites, 4 submissions total
- 320KB DB file
- Zero concurrent write contention

**Revisit at:** 50+ active sites or 10K+ monthly submissions.