# TradeSense AI — Production Database Strategy (Milestone 17)

**Document Version**: `1.7.0-DB-STRATEGY`  
**Classification**: Enterprise Database Architecture & Operations Strategy  

---

## 1. Architectural Model & Dual-Engine Abstraction

TradeSense AI uses a clean repository abstraction layer to support dual persistence engines:
1. **Production Engine**: **PostgreSQL 15+** (Multi-node connection pooling via PgBouncer, statement timeouts, WAL replication, point-in-time recovery).
2. **Local / CI Engine**: **Embedded SQLite in Write-Ahead Logging (WAL) mode** (`PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;`).

---

## 2. Migration & Versioning Strategy

- **Deterministic Ordering**: Forward-only migrations managed via `backend/storage/migrations.py`.
- **Zero-Downtime Safe DDL**: All column additions provide `DEFAULT` values; all table additions execute with `IF NOT EXISTS`.
- **Automated Startup Application**: Applied automatically on application launch before binding HTTP traffic.

---

## 3. High-Availability, Backup & Recovery Strategy

- **RPO Target**: < 1 minute (Continuous WAL streaming / replication).
- **RTO Target**: < 5 minutes (Automated read-replica promotion and snapshot mount).
- **Retention Schedule**: Hourly differential backups (retained 7 days), daily full snapshots (retained 90 days), annual immutable compliance archives (retained 7 years).