# SQLite → MySQL cutover

**Status: executed for real, 2026-08-31.** This installation's database of record is
now MySQL (`ca_automator` on `localhost`, credentials in `data/.env`). The app already
supported MySQL as a first-class engine (`db.py`, `schema_mysql.sql`,
`migrate_sqlite_to_mysql.py` — all pre-existing); this note records the rehearsal that
preceded the real cutover, then the real cutover itself.

## Real cutover (2026-08-31)

1. `python3 run.py backup` — fresh SQLite backup (`ca_automator-20260831-220421.db`)
   on top of the existing `data/backups/` snapshots.
2. `CREATE DATABASE ca_automator CHARACTER SET utf8mb4;` then
   `CA_DB_ENGINE=mysql python3 run.py init` — schema created from `schema_mysql.sql`.
3. `migrate_sqlite_to_mysql.py --sqlite-path ../data/ca_automator.db ...` against the
   **real** `data/ca_automator.db` (read-only throughout, never modified) —
   **1,724 rows across 152 tables**, row counts verified matching for every table.
4. Spot-checked post-migration: client count (4, matches source), `firm.country_code`,
   `rate_master` country distribution — all correct.
5. `data/.env` now sets `CA_DB_ENGINE=mysql` + `CA_MYSQL_*` — `start.sh` sources this
   file, so both the web server and worker read MySQL from the next launch onward with
   no other change needed.
6. Verified live: launched the same way `start.sh` does (sourcing `data/.env`) and
   confirmed `db.DB_ENGINE == 'mysql'`, real client data readable, engines functional.

The SQLite file (`data/ca_automator.db` and its `data/backups/` snapshots) is kept as
a pre-cutover historical backup — nothing in the migration path ever wrote to it, and
nothing deletes it now. `server/db.py` still supports `CA_DB_ENGINE=sqlite` (the test
suite runs against it for speed — see `PROJECT_STATE.md`), but this installation's
real data no longer lives there.

## Rehearsal performed (2026-08-31)

Against a **copy** of `data/ca_automator.db` (the live file was never opened for
writes):

1. `CA_DB_ENGINE=mysql python3 run.py init` against a throwaway database —
   created all 152 tables from `schema_mysql.sql`, then applied the
   `country_code` migration (see below) via the existing `_migrate_columns()`
   path. Confirmed both `rate_master.country_code` and `firm.country_code`
   land as `varchar(100) NOT NULL DEFAULT 'NP'`.
2. `migrate_sqlite_to_mysql.py` against the copy — **1,724 rows across 152
   tables**, row counts verified matching source and destination for every
   table (the script's own built-in check).
3. Spot-checked `firm.country_code` and `rate_master.country_code` — both
   correctly `'NP'` on every row post-migration.
4. Dropped the rehearsal database; the real `data/ca_automator.db` was
   confirmed untouched throughout (mtime/size unchanged, client rows intact).

No issues found. The script and schema are ready for a real cutover.

## Cutover steps (when ready to run for real)

1. **Stop the worker and app** (or schedule a maintenance window) so no
   writes land in SQLite mid-migration.
2. `python3 run.py backup` — timestamped SQLite backup, belt-and-suspenders
   on top of the existing `data/backups/` snapshots.
3. Create the MySQL database: `CREATE DATABASE ca_automator CHARACTER SET utf8mb4;`
4. `CA_DB_ENGINE=mysql CA_MYSQL_HOST=... CA_MYSQL_USER=... CA_MYSQL_PASSWORD=... \
   CA_MYSQL_DATABASE=ca_automator python3 run.py init` — creates the schema.
5. `python3 migrate_sqlite_to_mysql.py --sqlite-path ../data/ca_automator.db \
   --mysql-host ... --mysql-user ... --mysql-password ... --mysql-database ca_automator`
   — copies every row; exits non-zero if any table's row count doesn't match.
6. Set `CA_DB_ENGINE=mysql` (plus the `CA_MYSQL_*` vars) in the real
   environment/systemd unit and restart the app and worker.
7. Smoke-test: log in, open a client, open a workfile, confirm the shift
   dial syncs. Compare a few `rate_master`/`client` rows directly against
   the SQLite backup if anything looks off.
8. Once confirmed stable, the SQLite file stays as a historical backup —
   `migrate_sqlite_to_mysql.py` never writes to it, so it's always there to
   fall back on.

## Related: `country_code` migration

`rate_master` and `firm` both gained a `country_code` column (`server/db.py`'s
`_NEW_COLUMNS`, applied idempotently by `init_db()` on both engines). Every
existing row defaults to `'NP'`, so this is a no-op for current behaviour —
`db.rates()`/`db.rate_dict()` still return exactly what they did before
unless a caller explicitly passes a different `country_code`. See
`server/country_service.py` and `PROJECT_STATE.md` for how this feeds the
Country Engine work.
