# 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.