# PostgreSQL Migration Plan
**Author:** Hermes Agent (security review follow-up)
**Date:** 2026-07-07
**Status:** Proposed — not yet implemented
---
## 1. Why Migrate
### Current State
The application uses raw `sqlite3.connect()` for all database operations. The DB path is controlled by `RELAY_DB_PATH` (default: `/app/data/relay.db`) in `app/db.py`.
### Limitations of SQLite for Production
| Issue | Impact |
|-------|--------|
| **Write contention** | SQLite serializes all writes. With multiple gunicorn workers + RQ workers writing concurrently, `database is locked` errors increase with traffic. WAL mode helps reads but not write serialization. |
| **No connection pooling** | Each `get_db()` call opens a new file connection. No pooling, no max-connection limits. |
| **Scaling** | SQLite doesn't handle >100 concurrent readers well. A busy SaaS with multiple users submitting forms simultaneously will hit contention. |
| **Replication/HA** | SQLite can't replicate. Disaster recovery means copying a single file. |
| **Query performance** | No query plan caching, limited index types, no materialized views. Complex analytics queries (campaign reports, geo stats) are slower. |
### Target State
PostgreSQL 15+ with SQLAlchemy connection pooling (engine-only, no ORM) for:
- Concurrent write support (multiple gunicorn workers + RQ workers)
- Connection pooling (reduces connection overhead)
- Horizontal read scaling (read replicas for analytics)
- Point-in-time recovery (WAL archiving)
- Better query performance for analytics dashboards
---
## 2. Architecture: Connection Pooling Without ORM
### Key Constraint
The codebase uses **raw SQL via `sqlite3`** — there is no ORM layer. All queries are inline `conn.execute("...")` calls with parameterized arguments. We want to minimize refactoring while gaining PostgreSQL compatibility.
### Approach: SQLAlchemy Engine as Connection Pool
Use `sqlalchemy.create_engine()` **only for connection pooling** — not as an ORM. The engine provides:
- Connection pool (`QueuePool` for PostgreSQL, `StaticPool` for SQLite dev)
- Connection lifecycle management
- Cross-dialect compatibility
```python
# app/db.py (proposed)
import os
from sqlalchemy import create_engine, text
DATABASE_URL = os.environ.get(
"DATABASE_URL",
os.environ.get("RELAY_DB_PATH", "sqlite:////app/data/relay.db"),
)
# SQLite URL normalization for SQLAlchemy
if DATABASE_URL and not DATABASE_URL.startswith(("sqlite://", "postgresql://", "postgres://")):
DATABASE_URL = f"sqlite:///{DATABASE_URL}"
engine = create_engine(
DATABASE_URL,
pool_size=10, # PostgreSQL: pool size
max_overflow=20, # PostgreSQL: overflow above pool_size
pool_pre_ping=True, # Detect stale connections
pool_recycle=1800, # Recycle connections after 30 min
echo=False, # Set True for SQL logging in dev
)
def get_db():
"""Return a raw DB-API 2.0 connection from the pool.
Returns a sqlite3-style connection object with row_factory support.
For PostgreSQL, returns psycopg2 connection (row_factory not needed —
use cursor.description for column names).
"""
conn = engine.connect()
# For SQLite, set row_factory on the underlying connection
if "sqlite" in DATABASE_URL:
conn.connection.row_factory = type(conn.connection.row_factory) # preserve existing
return conn
```
### Pool Configuration by Environment
| Environment | Pool Class | pool_size | max_overflow | Notes |
|-------------|-----------|-----------|-------------|-------|
| **Dev (SQLite)** | `StaticPool` | N/A | N/A | Single in-memory or file DB |
| **Staging (PostgreSQL)** | `QueuePool` | 5 | 10 | Low traffic |
| **Production (PostgreSQL)** | `QueuePool` | 10 | 20 | ~5 gunicorn workers + RQ |
| **Analytics-heavy** | `QueuePool` | 20 | 50 | Dashboard queries |
### Migration Impact on Query Code
The `get_db()` function currently returns a `sqlite3.Connection` with `.execute()` and `row_factory`. After migration:
1. **SQLite path** (dev/test): No change — still returns `sqlite3.Connection`
2. **PostgreSQL path**: Returns SQLAlchemy `Connection` which wraps `psycopg2`
The `Connection.execute()` method in SQLAlchemy 2.0 accepts raw SQL strings directly:
```python
# Current (sqlite3):
conn = get_db()
conn.row_factory = sqlite3.Row
result = conn.execute("SELECT * FROM users WHERE id = ?", (user_id,))
row = result.fetchone()
print(row["email"]) # dict-like access via sqlite3.Row
# After migration (SQLAlchemy + PostgreSQL):
conn = get_db() # Returns SQLAlchemy Connection
result = conn.execute(text("SELECT * FROM users WHERE id = %s"), (user_id,))
row = result.fetchone()
print(row._mapping["email"]) # SQLAlchemy Row
# Or: print(row[0].email) for named tuples
```
**Key change needed**: All `result.fetchone()` accesses change from `row["column"]` to `row._mapping["column"]` or use `NamedTuple` result format.
To minimize this change, wrap the SQLAlchemy connection in a thin adapter:
```python
# app/db.py (proposed adapter)
class DBConnection:
"""Thin wrapper that makes SQLAlchemy connections behave like sqlite3."""
def __init__(self, sqlalchemy_conn):
self._conn = sqlalchemy_conn
def execute(self, sql, params=None):
result = self._conn.execute(text(sql), params or ())
return DBResult(result)
def commit(self):
self._conn.commit()
def rollback(self):
self._conn.rollback()
def close(self):
self._conn.close()
class DBResult:
"""Makes SQLAlchemy result rows behave like sqlite3.Row."""
def __init__(self, sqlalchemy_result):
self._result = sqlalchemy_result
def fetchone(self):
row = self._result.fetchone()
return None if row is None else DBRow(row._mapping)
def fetchall(self):
rows = self._result.fetchall()
return [DBRow(r._mapping) for r in rows]
def __iter__(self):
for row in self._result:
yield DBRow(row._mapping)
class DBRow(dict):
"""Dict-like row with key access, matching sqlite3.Row behavior."""
def __init__(self, mapping):
super().__init__(mapping)
# Also set attributes for .column_name access
for key in mapping:
setattr(self, str(key), mapping[key])
```
---
## 3. Migration Strategy
### Phase 1: Data Export from SQLite (Offline)
**Approach**: Export SQLite data to PostgreSQL-compatible SQL dump.
```bash
# Step 1: Export SQLite to CSV (or use sqlite3 .dump)
python scripts/export_sqlite.py --output data/export/
# Step 2: Transform CSV to PostgreSQL INSERT statements
python scripts/sqlite_to_pg.py --input data/export/ --output data/pg_import.sql
# Step 3: Import into PostgreSQL
psql -h pg-host -U agentforms -d agentforms_production < data/pg_import.sql
```
**Export script** (`scripts/export_sqlite.py`):
- Iterates all tables via `sqlite_master`
- Exports each table to a CSV file with headers
- Handles BLOB columns (encrypted data) as hex-encoded strings
- Preserves table creation statements for reference
**Transformation script** (`scripts/sqlite_to_pg.py`):
- Reads CSV exports
- Generates PostgreSQL `INSERT INTO ... VALUES (...)` statements
- Converts SQLite types to PostgreSQL types:
- `TEXT` → `TEXT`
- `INTEGER` → `INTEGER` or `BIGINT` for IDs
- `REAL` → `DOUBLE PRECISION`
- `BLOB` → `BYTEA`
- Adds `ON CONFLICT` handling for tables with primary keys
### Phase 2: Dual-Write Testing (Optional, Low Risk)
If zero-downtime migration is required:
1. Deploy code that writes to both SQLite and PostgreSQL simultaneously
2. Run for 1-2 days with production traffic
3. Compare data between SQLite and PostgreSQL
4. Cut over to PostgreSQL-only
This is **not required** for the initial migration — a single offline migration during a maintenance window is sufficient for the current scale.
### Phase 3: Cutover
1. Schedule maintenance window (30 min estimated)
2. Stop gunicorn workers + RQ workers
3. Run SQLite → PostgreSQL export/import
4. Verify row counts match
5. Update `DATABASE_URL` env var to point to PostgreSQL
6. Start workers
7. Monitor error logs for 1 hour
---
## 4. Configuration Approach
### Environment Variables
```bash
# Development (default — SQLite):
DATABASE_URL=sqlite:////app/data/relay.db
# Or use existing RELAY_DB_PATH:
RELAY_DB_PATH=/app/data/relay.db
# Production (PostgreSQL):
DATABASE_URL=postgresql://agentforms:secretpassword@pg-host:5432/agentforms_production
# Connection pool tuning (optional):
DB_POOL_SIZE=10
DB_MAX_OVERFLOW=20
DB_POOL_RECYCLE=1800
DB_POOL_PRE_PING=true
```
### Config Precedence
1. `DATABASE_URL` env var (highest priority — supports both sqlite:// and postgresql://)
2. `RELAY_DB_PATH` env var (backward compatibility — treated as `sqlite:///<path>`)
3. Default: `sqlite:////app/data/relay.db`
### Docker Compose Example
```yaml
# docker-compose.prod.yml (excerpt)
services:
app:
environment:
- DATABASE_URL=postgresql://agentforms:${DB_PASSWORD}@db:5432/agentforms_production
- DB_POOL_SIZE=10
- DB_MAX_OVERFLOW=20
db:
image: postgres:16-alpine
environment:
POSTGRES_DB: agentforms_production
POSTGRES_USER: agentforms
POSTGRES_PASSWORD: ${DB_PASSWORD}
volumes:
- pg_data:/var/lib/postgresql/data
healthcheck:
test: ["CMD-SHELL", "pg_isready -U agentforms"]
interval: 10s
timeout: 5s
retries: 5
volumes:
pg_data:
```
---
## 5. Type Mapping: SQLite → PostgreSQL
| SQLite Type | PostgreSQL Type | Notes |
|------------|----------------|-------|
| `INTEGER PRIMARY KEY` | `SERIAL PRIMARY KEY` or `BIGSERIAL` | Auto-increment |
| `TEXT` | `TEXT` | Direct mapping |
| `INTEGER` | `INTEGER` or `BIGINT` | Use BIGINT for user IDs, site IDs |
| `REAL` | `DOUBLE PRECISION` | Float precision |
| `BLOB` | `BYTEA` | For encrypted data columns |
| `BOOLEAN` (stored as INT) | `BOOLEAN` | Migrate 0/1 to true/false |
| `DATETIME` (TEXT) | `TIMESTAMPTZ` | Store as timezone-aware |
| `JSON` (TEXT) | `JSONB` | Native JSON support |
**Migration notes**:
- All SQLite tables use `TEXT` for most columns (SQLite is dynamically typed). PostgreSQL migration should use appropriate types for better storage efficiency.
- Encrypted columns (BLOB data) map to `BYTEA` — no data transformation needed.
- UUID columns stored as TEXT remain TEXT in PostgreSQL.
---
## 6. Schema Changes During Migration
### Required Adjustments
1. **Auto-increment IDs**: SQLite `AUTOINCREMENT` → PostgreSQL `SERIAL`/`BIGSERIAL`
2. **Default values**: SQLite `DEFAULT 0` → PostgreSQL `DEFAULT 0` (compatible)
3. **Index naming**: SQLite allows duplicate index names; PostgreSQL does not — ensure unique index names
4. **`LIKE` operator**: PostgreSQL `LIKE` is case-sensitive by default; use `ILIKE` for case-insensitive (matches SQLite behavior)
5. **`LIMIT/OFFSET`**: Syntax identical between SQLite and PostgreSQL
6. **`RETURNING` clause**: PostgreSQL supports `RETURNING` after INSERT/UPDATE (more powerful than SQLite)
7. **Foreign keys**: SQLite has deferred FK enforcement; PostgreSQL enforces FKs by default — ensure data integrity before migration
### Migration Script Outline
```python
# scripts/migrate_schema.py
"""Convert SQLite schema dump to PostgreSQL-compatible DDL."""
TYPE_MAP = {
"INTEGER": "BIGINT",
"TEXT": "TEXT",
"REAL": "DOUBLE PRECISION",
"BLOB": "BYTEA",
"NUMERIC": "NUMERIC",
}
def sqlite_autoincrement_to_pg(sql):
"""Convert SQLite AUTOINCREMENT to PostgreSQL SERIAL."""
# INTEGER PRIMARY KEY AUTOINCREMENT → BIGSERIAL PRIMARY KEY
sql = re.sub(
r"INTEGER\s+PRIMARY\s+KEY\s+AUTOINCREMENT",
"BIGSERIAL PRIMARY KEY",
sql,
flags=re.IGNORECASE,
)
# INTEGER PRIMARY KEY → BIGSERIAL PRIMARY KEY
sql = re.sub(
r"(\w+)\s+INTEGER\s+PRIMARY\s+KEY\b",
r"\1 BIGSERIAL PRIMARY KEY",
sql,
flags=re.IGNORECASE,
)
return sql
```
---
## 7. Risk Assessment
### High Risk
| Risk | Likelihood | Mitigation |
|------|-----------|------------|
| **Data loss during export/import** | Low | Take SQLite backup before migration. Verify row counts match. |
| **Application downtime > 30 min** | Low | Migration is ~5 min for current DB size. 30 min buffer included. |
| **SQL syntax incompatibility** | Medium | Test all query paths against PostgreSQL before cutover. |
### Medium Risk
| Risk | Likelihood | Mitigation |
|------|-----------|------------|
| **Query performance regression** | Medium | Benchmark analytics queries (geo stats, campaign reports) on PostgreSQL. |
| **Encrypted data corruption** | Low | BYTEA storage preserves binary data. Test encryption/decryption after migration. |
| **Connection pool exhaustion** | Low | Start with conservative pool settings (5/10). Monitor and tune. |
### Low Risk
| Risk | Likelihood | Mitigation |
|------|-----------|------------|
| **Migration script fails mid-way** | Low | Run on copy of production DB first. |
| **PostgreSQL not available** | Low | Use managed PostgreSQL (Supabase, AWS RDS, Hetzner CPX) for simplicity. |
### Rollback Plan
If issues arise after cutover:
1. Point `DATABASE_URL` back to SQLite (backup file preserved)
2. Restart application
3. Investigate issue on staging environment
4. Re-attempt migration with fixes
---
## 8. Estimated Effort
| Task | Estimated Time | Notes |
|------|---------------|-------|
| **DB adapter layer** (app/db.py) | 4 hours | Thin wrapper for SQLAlchemy → sqlite3-compatible interface |
| **Type mapping** | 1 hour | Document all column types, create mapping |
| **Export/import scripts** | 4 hours | scripts/export_sqlite.py + scripts/sqlite_to_pg.py |
| **Schema migration script** | 2 hours | scripts/migrate_schema.py |
| **Testing on staging** | 4 hours | Run full test suite + smoke tests against PostgreSQL |
| **Docker Compose + config** | 2 hours | docker-compose.prod.yml + env var docs |
| **Production cutover** | 1 hour | Maintenance window execution |
| **Monitoring + tuning** | 2 hours | Post-cutover monitoring, pool tuning |
| **Total** | **~20 hours** | ~2.5 working days |
---
## 9. Recommended Timeline
1. **Week 1**: Implement DB adapter layer, export/import scripts, Docker Compose config
2. **Week 2**: Test on staging with copy of production data, fix any query incompatibilities
3. **Week 3**: Production cutover during low-traffic window (e.g., Tuesday 3-4 AM UTC)
---
## 10. Alternative Approaches Considered
### Option A: Full ORM Migration (SQLAlchemy)
**Rejected** — Would require rewriting all ~200+ database query functions across 10+ model modules. Estimated 60+ hours. No functional benefit for current codebase.
### Option B: Keep SQLite, use connection pooling
**Rejected** — SQLite doesn't support connection pooling (single file lock). The `sqlite3` module doesn't have a pool implementation that works for concurrent writers.
### Option C: Read-only PostgreSQL replica
**Not needed** — Current scale doesn't justify read replicas. Simple primary PostgreSQL instance is sufficient.
### Option D: Managed Database (Supabase/RDS)
**Recommended** — Use managed PostgreSQL (Supabase free tier or Hetzner CPX) to avoid database administration overhead. Matches the existing Docker-based infrastructure.
---
## Appendix A: Current Database Tables
Based on migration scripts in `app/migrations.py`:
- `users` (core auth)
- `sites` (form sites)
- `submissions` (form submissions)
- `site_versions` (site history)
- `monthly_usage` (tier limits)
- `api_keys` (API access)
- `webhook_destinations` + `webhook_logs` (webhook delivery)
- `campaigns`, `campaign_recipients`, `campaign_ab_variants`, `campaign_reminders` (email campaigns)
- `email_settings`, `custom_templates`, `email_send_log` (email config)
- `document_templates`, `documents`, `invoice_schedules` (document generation)
- `teams`, `team_members`, `invites` (team collaboration)
- `form_actions`, `form_impressions`, `form_sessions`, `form_versions`, `form_variants` (form analytics)
- `site_analytics` (analytics aggregation)
- `templates` (form templates)
- `waitlist` (beta waitlist)
- `referrals` (referral tracking)
- `agents`, `agent_deployments` (AI agent builder)
- `chains`, `chain_runs` (AI chains)
- `ai_prompt_history` (AI builder history)
---
## Appendix B: PostgreSQL vs SQLite Query Compatibility
| Pattern | SQLite | PostgreSQL | Compatible? |
|---------|--------|-----------|-------------|
| `?` parameterized | ✅ | ❌ Use `%s` | Need adapter |
| Named params `:name` | ✅ | ✅ | Compatible |
| `LIMIT` | ✅ | ✅ | Compatible |
| `LIKE '%search%'` | Case-insensitive | Case-sensitive | Use `ILIKE` |
| `COUNT(*)` | ✅ | ✅ | Compatible |
| `INSERT OR REPLACE` | ✅ | ❌ | Use `INSERT ... ON CONFLICT` |
| `INSERT OR IGNORE` | ✅ | ❌ | Use `INSERT ... ON CONFLICT DO NOTHING` |
| `RETURNING` | Partial (3.35+) | Full | Compatible |
| `UPSERT` | Via `INSERT OR REPLACE` | Via `ON CONFLICT` | Need adapter |
| `GLOB` | ✅ | ❌ | Use `~` regex |
| `JSON_EXTRACT` | ✅ | ✅ (`->>`) | Minor syntax diff |