docs/database/production_database_strategy.md

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