# sync: full local repricer database for ML and analytics

## Why
Prod keeps only the recent months, older history lives in a separate archive database, and there are gaps
between them. Training the ML repricing model (`../ml`) and running heavy analytical queries need **one local
database with the full history** that can be queried freely without loading prod. This folder provides:

1. **Merge** (`run.sh` -> `merge.py`), once: prod + archive -> local MySQL with an exact seam, plus a rebuild of
   the `buy_box_price_history` gap (2026-07-17 .. 2026-10-14) from `item_price_data`.
2. **Sync** (`sync.sh` -> `sync.py`), regularly: prod only -> into the already merged database (new and changed rows).

Prod and archive are **read-only** (SELECT / mysqldump `--single-transaction`).

## Layout
| what | where |
|---|---|
| code (this folder, in git) | `new-repricer/sync/` |
| MySQL data, credentials, work files (NOT in git) | `~/projects/new-repricer-db/` (override with `REPRICER_DB_DIR`) |
| - MySQL data | `.../data/` |
| - credentials | `.../.env` (root, repricer), `.../secrets/{prod,arch,local}.cnf` |
| - merge / sync state | `.../state.sqlite`, `.../sync_state.sqlite`, previous syncs in `.../sync_runs/` |
| - reports | `.../report.md` (merge), `.../sync_report.md` (sync history) |
| background job logs | `~/logs/bg-merge*.log`, `~/logs/bg-sync*.log` |

MySQL 8.0 in docker, container `repricer-db`, `127.0.0.1:3306`, database `repricer`
(user `repricer`, same password as the backend `DB_PASSWORD`). Tuning for a big database is in `mysql/local.cnf`
(24 GB buffer pool, 8 GB redo, etc.). Manual start:
`docker compose -f docker-compose.yml --env-file ~/projects/new-repricer-db/.env up -d`.

## Prod sync (regular use)
```
~/projects/new-repricer/sync/sync.sh          # checks -> plan (what and how much) -> yes -> background (bg-sync) -> monitor
~/projects/new-repricer/sync/sync.sh --test   # small test: all phases, one small chunk of each kind
~/projects/new-repricer/sync/watch.sh 15 sync # reopen the monitor (Ctrl-C only closes the monitor)
```
- Every run pulls the difference since the previous sync (`_merge_meta.last_sync_utc`). Usually ~5 GB, 10-15 minutes.
- What is pulled: small tables (< 256 MB) and `items` as a whole; big tables get new ids plus a 7-day "tail"
  (45 days for `orders`) to catch changed rows; `item_activities` also gets every row with `updated_at` after the previous sync.
- Rows deleted on prod **stay** locally (history).
- If prod had a migration (structure drifted), the sync stops before loading anything and prints the difference.
- It refuses to start if the merge is not finished or another merge/sync is running.
- Limitation: changes to old rows (older than the tail) in big tables other than `item_activities` are not picked up.

## Merge from scratch (rare; DROPS the local database)
```
~/projects/new-repricer/sync/run.sh            # checks -> yes -> merge.py --fresh in the background (bg-merge-full) -> monitor
~/projects/new-repricer/sync/watch.sh          # merge monitor
```
- Test first: `bgrun -n merge-test "cd ~/projects/new-repricer/sync && python3 merge.py --fresh --test"`.
  It runs ALL phases on a sample. After a test, a production run requires `--fresh` (the script refuses otherwise).
- Resume an interrupted merge: `bgrun -n merge-full "cd ~/projects/new-repricer/sync && python3 merge.py"`.
- Rebuild only the `buy_box_price_history` gap and the report: `python3 merge.py --redo-gap`.
- Phases: schema -> plan -> preflight -> load -> bbph_gap -> indexes -> verify. Takes ~5-6 hours.
- Prod is loaded with REPLACE (prod wins), archive with INSERT IGNORE (only what is missing).

## Safeguards (learned from failures of the first run)
- **preflight** right after the schema and on every resume: rebuilds all dropped indexes and runs the gap SQL on
  empty table copies. An error stops the run within a minute instead of hours later.
- Temp tables copy column types and collations from the real tables (was: a collation mix error).
- Functional indexes (`(cast(date as date))`) are dropped and restored by expression (was: `None` column).
- Gap rows get explicit sequential ids (`row_number`) and are inserted without IGNORE (was: auto_increment gaps
  overran the free id range and INSERT IGNORE silently lost 758k rows).
- Every dump must disable `FOREIGN_KEY_CHECKS`: REPLACE = DELETE+INSERT, and with FK checks on,
  `ON DELETE CASCADE` (items -> buy_box_price_history) would wipe history.
- Database size is read from disk (`information_schema` lies because of cached statistics).

## Network
The link is 300-400 Mbit/s and half of it must stay free: 4 prod streams (+4 archive streams during the merge)
give ~90 Mbit/s; the limiter freezes mysqldump (SIGSTOP) when all inbound traffic on `enp118s0` exceeds 150 Mbit/s
(`MERGE_NET_CAP=120 ./sync.sh` for a lower cap). Measurements: `tools/bw.sh "prod:4 arch:8"`,
limiter check: `tools/limtest.py`.

## Known data facts
- 2026-07-17 and 2026-04-24..26 are service outages (ids are contiguous), not merge errors.
- The archive `item_price_data` is missing 141k ids in October 2025 (same in the source).
- 5 views were not copied: the prod user has no SHOW VIEW privilege.
- 1 of 50.5M rows in the `buy_box_price_history` gap did not fit into the free id range (listed in `report.md`).
