# Migration Plan: SQLite → PostgreSQL + Docker on Synology DS1513+

## Architecture Decisions

| Decision | Choice | Rationale |
|----------|--------|-----------|
| Schema | Keep table-per-coin-timeframe (652 tables) | Minimal code changes, PostgreSQL handles it well |
| Table names | Sanitize `:` → `_` (e.g., `xyz_BRENTOIL_1m`) | PostgreSQL compatibility |
| Secrets | Docker env_file + bind-mount | Secure, rotate-friendly, Synology-compatible |
| Gap detection | New `gap_detector.py` | Fills data gaps when system is down |
| Backup | Daily `pg_dump` to shared folder | Accessible via File Station, Hyper Backup compatible |
| Host integration | Expose PostgreSQL port 5432 | Host scripts connect to `localhost:5432` |
| Migration | Two-phase (offline + cutover) | Minimizes downtime |
| Legacy tables | Skip `market_cap`, `candles`, `daily` | Not used by current code |

## Container Layout

```
┌─────────────────────────────────────────────────────┐
│              Docker Compose                         │
├─────────────────────────────────────────────────────┤
│  ┌──────────────┐  ┌──────────────────────────────┐  │
│  │ PostgreSQL   │  │ Data Collector (supervisord)│  │
│  │ postgres:15- │  │ python:3.11-slim             │  │
│  │ alpine       │  │                              │  │
│  │              │  │ • live_candle_fetcher (cont)│  │
│  │ shared_buff  │  │ • resampler_loop (cont)      │  │
│  │ =128MB       │  │ • indicators_fetcher (cont) │  │
│  │              │  │ • cron_scheduler (cont)      │  │
│  │ Vol:pg_data  │  │   - data_fetcher (daily)     │  │
│  │ Port:5432    │  │   - fetch_history (daily)    │  │
│  │ exposed      │  │   - gap_detector (hourly)    │  │
│  └──────────────┘  │   - backup_runner (daily)    │  │
│                    └──────────────────────────────┘  │
└─────────────────────────────────────────────────────┘

Host Machine: indicators.py, base_strategy.py, main_app.py
  → connect to localhost:5432
```

## PostgreSQL Configuration (4GB RAM)

```ini
shared_buffers = 128MB
effective_cache_size = 512MB
work_mem = 8MB
maintenance_work_mem = 64MB
max_connections = 10
max_worker_processes = 2
checkpoint_completion_target = 0.9
wal_buffers = 4MB
```

## Data Migration (Two-Phase)

**Phase 1 (offline)**: Stop current system → run `migrate_sqlite_to_pg.py` → 2-3 hours for 1.8GB

**Phase 2 (cutover)**: Start Docker containers → update host scripts to connect to `localhost:5432`

## Files to Create/Modify

### New Files
1. `db.py` — PostgreSQL abstraction layer
2. `scripts/resampler_loop.py` — Runs resampler every minute in a loop
3. `scripts/gap_detector.py` — Detects and fills data gaps
4. `scripts/backup_runner.py` — Daily pg_dump with 7-day retention
5. `scripts/cron_scheduler.py` — Schedules data_fetcher, fetch_history, gap_detector, backup
6. `migrate_sqlite_to_pg.py` — One-time data migration
7. `Dockerfile` — Python 3.11-slim + supervisor + psycopg2-binary
8. `docker-compose.yml` — PostgreSQL + data-collector services
9. `supervisord.conf` — Process management
10. `postgres/postgresql.conf` — Tuned for 4GB RAM
11. `.dockerignore` — Docker build context exclusions
12. `.env.docker.example` — Docker env template
13. `secrets/pg_password.txt.example` — PG password template

### Files to Modify (7)
1. `live_candle_fetcher.py` — `sqlite3` → `db.py`
2. `resampler.py` — `sqlite3` → `db.py`
3. `data_fetcher.py` — `sqlite3` → `db.py`
4. `fetch_history.py` — `sqlite3` → `db.py`
5. `import_csv.py` — `sqlite3` → `db.py`
6. `indicators.py` — `sqlite3` → `psycopg2`
7. `base_strategy.py` — `sqlite3` → `psycopg2`

## TODO List

### Phase 1: DB Abstraction Layer
- [x] Create `db.py` with PostgreSQL connection, table sanitization, upsert logic
- [x] Add `psycopg2-binary` to `requirements.txt`

### Phase 2: Modify Data Collection Components
- [ ] Modify `live_candle_fetcher.py` — replace `sqlite3.connect()` with `db.get_connection()`, `INSERT OR REPLACE` with `db.upsert_candles()`, sanitize table names
- [ ] Modify `resampler.py` — replace `sqlite3` with `db.py`, `INSERT OR REPLACE` with `db.upsert_candles()`, `?` → `%s`
- [ ] Modify `data_fetcher.py` — replace `sqlite3` with `db.py`, `to_sql()` → `db.upsert_candles()`
- [ ] Modify `fetch_history.py` — replace `sqlite3` with `db.py`
- [ ] Modify `import_csv.py` — replace `sqlite3` with `db.py`, `to_sql()` → `db.upsert_candles()`

### Phase 3: New Components
- [ ] Create `scripts/resampler_loop.py` — wraps resampler in a while loop with 60s sleep
- [ ] Create `scripts/gap_detector.py` — detects gaps in 1m data, backfills via HTTP API
- [ ] Create `scripts/backup_runner.py` — daily pg_dump with 7-day retention
- [ ] Create `scripts/cron_scheduler.py` — schedules data_fetcher, fetch_history, gap_detector, backup

### Phase 4: Docker Setup
- [ ] Create `Dockerfile` (python:3.11-slim + supervisor + psycopg2-binary)
- [ ] Create `docker-compose.yml` (postgres + data-collector services)
- [ ] Create `supervisord.conf` (live_candle_fetcher, resampler_loop, indicators_fetcher, cron_scheduler)
- [ ] Create `postgres/postgresql.conf` (tuned for 4GB RAM)
- [ ] Create `.dockerignore`
- [ ] Create `.env.docker.example`
- [ ] Create `secrets/pg_password.txt.example`
- [ ] Update `.gitignore`

### Phase 5: Host-Side Updates
- [ ] Modify `indicators.py` on host — connect to `localhost:5432`
- [ ] Modify `base_strategy.py` on host — connect to `localhost:5432`

### Phase 6: Migration Tool
- [ ] Create `migrate_sqlite_to_pg.py` — reads from SQLite, writes to PostgreSQL

### Phase 7: Testing & Deployment
- [ ] Commit and push to remote
- [ ] User clones on NAS, copies `.env` and `_data/`
- [ ] User runs migration script
- [ ] User starts Docker containers