tradesenseAi / docs /database /production_database_strategy.md
Niranjan-Murugarasu024
feat: TradeSense AI Chairman Edition production release ready for Hugging Face Spaces
0125724
|
Raw History Blame Contribute Delete
1.39 kB
# 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).