Cockpit Migration Runbook

An audit of the Spring and Flask migration scripts, the fixes applied to them, and the exact order to run a production schema update. Neither script pair was safe to run as written.

Contents

—Verdict
—What was broken
—What changed
00Preflight
01Spring (SQLite)
01bSpring (MySQL)
02Flask migration
—Rollback

Who this is for. Whoever is running the next production database migration. It contains no credentials and no connection strings — everything sensitive lives in the .env file on cockpit and never in this document. If a step below needs a password, it means read it from .env, not from here.

Answer first

No — neither set would have migrated everything

Both pairs reported success while losing data, and the Spring pair would have left production in an unrecoverable state.

Spring

mysqlbackup.py silently omitted every table whose id is a MySQL AUTO_INCREMENT column. That keyword is a syntax error in SQLite, so the CREATE TABLE failed and the loop moved on to the next table. The codebase has 34 entities using GenerationType.IDENTITY. The script then printed "Backup Complete — Total tables backed up: N" using the full source count.

Spring, worse

mysqlrestore.py drops every table in production first, then rebuilds from the SQLite backup. The rebuild emitted DEFAULT 'NULL' — a literal four-character string — on columns it had also downgraded to LONGTEXT. MySQL rejects any default on a TEXT column (error 1101), so CREATE TABLE would have failed for essentially every table with a nullable varchar — after production had already been dropped, with no dump taken.

Flask

Three tables — leaderboard, elementary_leaderboard_events, skill_snapshots — had no export or import endpoint at all. Since step 4 of the documented workflow runs db_init.py on production, which calls drop_all(), those tables are destroyed there and never restored. db_init.py also printed "Backup not supported for production database" and dropped everything anyway.

Spring is not on MySQL today. DB_URL turned out to be commented out in Spring's .env, so Spring has been running on the local SQLite file, not RDS, for some time. The defects above are all real and all still worth fixing, but they were latent rather than live — the broken MySQL path had not been exercised. Flask is on production MySQL, so its findings apply directly. Spring's live migration is the SQLite phase below.

Evidence. These are not readings of the code. The schema translators were run as pure functions against real SHOW CREATE TABLE output and against all 130 tables in spring/schema_full.txt. The AUTO_INCREMENT failure and the DEFAULT 'NULL' output were both reproduced directly. After the fixes, every parseable table produces legal MySQL.

Audit

What was broken

Ordered by consequence. Data loss means rows that existed in production would not exist after the migration, with no error raised. Breaks prod means the migration itself fails partway, after something destructive has already run. No recovery means nothing would have caught it and nothing would have undone it.

Spring

Impact	Defect	Where
Data loss	Column-level `AUTO_INCREMENT` was never stripped, so SQLite rejected the `CREATE TABLE` and the table was skipped entirely. Affects all 34 `GenerationType.IDENTITY` entities. Reported as a successful backup.	mysqlbackup.py
Data loss	Restore excluded all `HT_` / `HTE_` Envers audit tables but dropped them from MySQL first. The code comment claimed Hibernate would recreate them; the app runs `ddl-auto=none`, so it will not — and every write to an audited entity then fails.	mysqlrestore.py
Breaks prod	`DEFAULT NULL` round-tripped into `DEFAULT 'NULL'`, applied to columns already downgraded to `LONGTEXT`. MySQL error 1101 — after `drop_all_tables()` had run.	mysqlrestore.py
Breaks prod	Schema degraded on every round trip: `varchar(255)`, `datetime(6)`, `bit(1)` and `json` all became `LONGTEXT`; every index, `UNIQUE` key and foreign key was dropped.	mysqlrestore.py
Breaks prod	The production schema update ran through that same translator — `db_init.py` built a local SQLite schema and converted it to MySQL rather than letting Hibernate emit native DDL.	db_init.py
Data loss	A duplicate-key error skipped the entire table's data, printed one line, and still counted the table as restored.	mysqlrestore.py
No recovery	No dump taken before dropping production, and no row-count verification in either direction.	both

Flask

Impact	Defect	Where
Data loss	`leaderboard`, `elementary_leaderboard_events` and `skill_snapshots` had no export or import endpoint. `db_init.py` on production drops them; nothing puts them back.	data_export_import_api.py
No recovery	`db_init.py` printed "Backup not supported for production database", then ran `drop_all()` — no rollback point for a MySQL target.	db_init.py
Data loss	The `_game_profile` column was dropped in both directions — never passed to the `User` constructor on import.	both scripts + API
Data loss	The seed-user filter listed a non-existent `DEFAULT_UID` key and missed `USER_UID` and `MY_UID`. Those users' real production rows were pulled, then discarded locally as "already exists".	db_utils.py
Silent	Failures inside a batched upload were never added to `failed_endpoints`, so a batched type could lose rows and the run still reported success.	db_restore-sqlite2prod.py
Silent	Only `users` and `sections` were treated as critical on export; any other endpoint could fail and the migration continued.	db_migrate-prod2sqlite.py

Fixes applied

What changed

Every defect above is fixed in the current scripts. Three structural changes are worth understanding before you run anything.

Spring no longer translates schemas

mysqlbackup.py now records each table's exact SHOW CREATE TABLE output and source row count into a __migration_meta__ table inside the backup file. The restore replays that DDL verbatim, so types, indexes, UNIQUE keys and foreign keys survive intact instead of being re-derived from SQLite type affinities.

For a production update the schema does not come from the backup at all. db_init.py now runs Spring Boot against the target directly with ddl-auto=create, so Hibernate emits native DDL for the current entities. restore --keep-target-schema then loads data into that schema, inserting only the columns both sides share and reporting which columns were added or dropped.

Both sides now verify themselves

Flask gained GET /api/export/counts, which returns row counts for every model. The pull and push scripts call it and print a table-by-table reconciliation, exiting non-zero on any shortfall. Spring re-counts every table against the backup after restoring. Nothing reports success on a partial transfer any more.

Rollback points are mandatory

Both db_init.py scripts and mysqlrestore.py take a mysqldump before anything destructive and refuse to continue if it fails. Overrides exist (ALLOW_NO_BACKUP=true, --skip-safety-dump) but you should not need them.

One entry point instead of five

Spring had accumulated three overlapping migration pairs — the HTTP-based db_prod2local / db_local2prod from the SQLite era, the db_mysql2local / db_local2mysql merge variants, and mysqlbackup / mysqlrestore — plus a dead one-off, db_prod_to_mysql.py, that shelled out to a script which no longer existed. Five helpers were copy-pasted across three files, and db_local2mysql.py carried an entirely separate MySQL writer that received none of the fixes above.

Everything shared now lives in mysql_common.py, and db_migrate.py is the single entry point: status, check, backup, init, restore. It calls straight into the existing implementations rather than reimplementing them, so there is still exactly one backup path and one restore path. The five superseded scripts have been removed.

The anti-drift check. The MySQL-to-SQLite and SQLite-to-MySQL type maps are inverse functions in two different files, and nothing structural keeps them inverse — that drift is exactly what turned every VARCHAR and DATETIME into LONGTEXT. db_migrate.py check pushes every table through both directions and fails on any degradation. It needs no database and no driver, so it runs in CI. It earned its place immediately: on its first run it caught a regression in the previous day's fix — the type substitutions were matching column names, not just types, and this schema has columns literally named timestamp and text. Substitutions now skip backtick-quoted identifiers.

Files touched

spring/ and spring-tracking/

scripts/mysql_common.py (new), scripts/db_migrate.py (new), scripts/sqlite_migrate.py (new), scripts/mysqlbackup.py, scripts/mysqlrestore.py, scripts/db_init.py, README.md. The five superseded scripts were deleted.

flask/

api/data_export_import_api.py, scripts/db_utils.py, scripts/db_migrate-prod2sqlite.py, scripts/db_restore-sqlite2prod.py, scripts/db_init.py, README.md.

Phase 00

Preflight

Do all of this before touching either service. Run the two migrations one at a time, fully finishing Spring before starting Flask.

Commit and push the script changes, and merge them — production pulls this code.
Confirm mysqldump and mysql are on PATH on cockpit: which mysqldump mysql.
Confirm the Python venvs have mysql-connector-python (Spring) and requests (Flask).
Spring .env on cockpit: DB_URL, DB_USERNAME, DB_PASSWORD pointing at the RDS instance — only if you intend to run the MySQL phase.
Flask .env on your dev machine: ADMIN_UID and ADMIN_PASSWORD set to the production admin credentials.
Take an RDS snapshot from the AWS console. This is your last line of defence and it is independent of anything the scripts do.
Pick a low-traffic window. There is a period where each service is down, or serving new code against an old schema.

Then confirm what you are pointed at, and that the schema translation is sound, from the spring repo:

python3 scripts/db_migrate.py status
python3 scripts/db_migrate.py check

check must exit 0. It needs no database and no driver, so it is safe to run anywhere. It reads schema_full.txt, which is a point-in-time fixture and goes stale as entities are added — use check --live to read the schema from the configured MySQL server instead.

Phase 01 · do this one

Spring migration — SQLite

Spring's .env had DB_URL commented out, so application.properties fell back to jdbc:sqlite:volumes/sqlite.db. Spring has been writing to that local file, not RDS. That file is the live production database; the RDS springdatabase is a stale snapshot from the April cutover.

Do not migrate the RDS copy. It has 116 tables and no Envers audit tables at all, versus 130 in the SQLite schema. Restoring it over your live file would roll Spring back to April.

Never cp the database. It runs in WAL mode, so a plain copy of sqlite.db taken while Spring is up can miss everything still in the -wal file, or capture a torn page. db_migrate.py backup uses SQLite's online backup API instead, which is consistent against a live database.

01
Confirm which database you are ongate
```
python3 scripts/db_migrate.py status
```
Must print Mode: SQLITE and a file path. If it prints Mode: MYSQL, then DB_URL is set and you want the MySQL phase below instead. Note the table and row counts — they are your before-picture.
02
Fix the backups directory if neededcockpit · open/spring
```
sudo chown -R $(id -u):$(id -g) volumes/
```
An unable to open database file error here means volumes/backups/ is root-owned, created that way by Docker. The backup checks this up front and tells you the fix, but you can clear it now.
03
Back up the live databasecockpit · open/spring
```
python3 scripts/db_migrate.py backup
```
Writes volumes/backups/sqlite_backup_<ts>.db and verifies every table's row count against the live file. Note the filename — you need it in step 7.
04
Gate — the backup must exit cleangate
```
echo $?   # must print 0
```
The run must end with "All tables and all rows accounted for." Copy the file off the box as well — it is a complete database, so a copy is a complete rollback.
05
Take Spring down and update the codecockpit · open/spring
```
docker compose down
git pull
```
Port 8585 must be free for the next step, which is why the container comes down first.
06
Rebuild the schema from the entitiescockpit · open/spring
```
python3 scripts/db_migrate.py init
```
Takes its own verified backup, deletes the old file, then boots Spring Boot with ddl-auto=create to build a fresh schema and seed data. Your real data is in the backup from step 3, not in this file.
07
Load your data into the new schemacockpit · open/spring
```
python3 scripts/db_migrate.py restore \
    --backup-file volumes/backups/sqlite_backup_<ts>.db
```
Clears the seed rows and loads the backup into the columns both schemas share. Columns your change added take their defaults; *_seq tables come across intact, so Hibernate keeps allocating ids where it left off rather than restarting at 1 and colliding.
08
Gate — every table must reconcilegate

Must end with "All tables verified: target row counts match the backup." Read the two lists above it first:
- Schema differences absorbed — confirm each dropped column is intentional.
- Tables the new schema no longer has — those rows were not restored.
09
Bring Spring up and smoke testcockpit · open/spring
```
docker compose up -d --build
curl -s https://spring.opencodingsociety.com/api/jokes/ | head -c 300
```
Then log in and confirm real user data is present. Compare db_migrate.py status against your step 1 numbers.

Rollback is a file copy. The backup is a complete database, so recovery does not need a script. Removing the stale -wal and -shm files matters — left behind, they can be replayed over the database you just restored.

docker compose down
cp volumes/backups/sqlite_backup_<ts>.db volumes/sqlite.db
rm -f volumes/sqlite.db-wal volumes/sqlite.db-shm
docker compose up -d

If you later move Spring to MySQL

Set DB_URL in .env, then run the MySQL phase below. Migrating the data across is the SQLite backup feeding restore --keep-target-schema after init has built the MySQL schema — the backup format is the same SQLite file either way. Treat that as its own change, on its own day, not bundled with a schema update.

Phase 01b · only once DB_URL is set

Spring migration — MySQL

This is the procedure for when Spring actually runs on RDS. It does not apply while DB_URL is unset — the phase above is the live one. Keep this for when you make the move.

01
Pull production into a verified backupcockpit · open/spring
```
source venv/bin/activate
python3 scripts/db_migrate.py backup
```
Writes volumes/backups/mysql_backup_<ts>.db. Note the exact filename — you need it in step 6.
02
Gate — the backup must exit cleangate
```
echo $?   # must print 0
```
The summary must read "All tables and all rows accounted for." A non-zero exit lists every table that failed or came up short. Do not continue past this point on a failed backup — the rest of the phase destroys the only other copy.
03
Copy the backup off cockpitdev machine
```
scp cockpit:~/open/spring/volumes/backups/mysql_backup_<ts>.db .
```
Optional but cheap. Point your local Spring at the copy and exercise the new code against real data before you commit to the production window.
04
Take Spring down and update the codecockpit · open/spring
```
docker compose down
git pull
```
Port 8585 must be free for the next step, which is why the container comes down first.
05
Rebuild the MySQL schema with Hibernatecockpit · open/spring
```
python3 scripts/db_migrate.py init
```
This drops and recreates every Hibernate-managed table on RDS — including the Envers HT_* / HTE_* audit tables and the *_seq id-allocation tables — and loads seed data. It boots Spring Boot temporarily with ddl-auto=create, so allow a few minutes. The DDL is native MySQL; no translation is involved.
06
Load your data into the new schemacockpit · open/spring
```
python3 scripts/db_migrate.py restore --keep-target-schema \
    --backup-file volumes/backups/mysql_backup_<ts>.db
```
Takes a mysqldump rollback point first, clears the seed rows, then loads the backup into the columns the old and new schemas share. Columns your schema change added are left at their defaults; columns it removed are listed explicitly.
07
Gate — every table must reconcilegate

Read the Restore Summary. It must end with "All tables verified: MySQL row counts match the backup." Review two lists before moving on:
- Schema differences absorbed — confirm each dropped column really is intentional.
- Tables the new schema no longer has — these rows were not restored. Confirm each removal is intentional.

Bring Spring back upcockpit · open/spring

docker compose up -d --build
docker compose logs -f --tail=100

09
Smoke testgate
```
curl -s https://spring.opencodingsociety.com/api/jokes/ | head -c 300
```
Then log in through the frontend and open a Groups page to confirm JWT cookies work. Finally, save an audited entity — edit a person, say. That exercises the Envers HTE_* tables, which is exactly what the old restore destroyed.

Phase 02

Flask migration

The order differs from Spring, and from the older README, for one reason: the pull cannot verify itself until production is serving the new export endpoints. So the code deploy comes first.

Why code first. The pull script calls /api/export/counts and the three new export endpoints. None of them exist on production until you deploy. Deploying the Flask code alone is non-destructive — the app only creates or drops tables when db_init.py is run explicitly — so this is safe.

01
Deploy the code, but not the schemacockpit · open/flask
```
git pull
docker compose up -d --build
```
Do not run db_init.py yet. Production keeps its current data and simply gains the new endpoints.
02

Gate — confirm the new endpoints are livegate

Authenticate as the production admin, then hit /api/export/counts. It should return a count for all thirteen tables plus the two association tables. Keep this output — it is your before-picture.
03
Pull production data to localdev machine · flask
```
python scripts/db_migrate-prod2sqlite.py
```
Fetches all thirteen tables, rebuilds the local schema, loads the data, then prints a production-vs-local reconciliation.
04
Gate — the pull must exit cleangate
```
echo $?   # must print 0
```
Every table must read ok. Anything marked MISSING means those rows are not on your machine — and the next phase deletes them from production. The seed-user difference is accounted for automatically.
05

Test locallydev machine · flask

Run the app against the pulled data and exercise the areas your schema change touches. This is the last point where production is still intact.
06
Apply the new schema to productioncockpit · open/flask
```
python scripts/db_init.py
```
Takes a mysqldump to instance/backups/ first and aborts if that fails. Then drop_all(), create_all(), and seed data. Note the dump path it prints.
07
Push your data back to productiondev machine · flask
```
python scripts/db_restore-sqlite2prod.py
```
Uploads all thirteen types in dependency order, batching the large ones, then re-reads production's counts and prints a local-vs-production reconciliation.
08
Gate — production must reconcilegate
```
echo $?   # must print 0
```
Rows marked +N extra on production are fine — that is seed data regenerated by db_init.py. Anything marked MISSING is not. Keep your local database until this passes; until then it is the only complete copy.
09

Smoke testgate

Log in as a real (non-seed) user and confirm their profile, sections and grade data are intact. Load a leaderboard page and a page that reads skill snapshots — those are the three tables that previously would have come back empty.

Adding a new model? It must be registered in three places or its data is lost on the next migration: MIGRATED_MODELS plus export/import endpoints in api/data_export_import_api.py, EXPORT_ENDPOINTS and a loader in scripts/db_migrate-prod2sqlite.py, and IMPORT_ENDPOINTS and a reader in scripts/db_restore-sqlite2prod.py. /api/export/counts is what catches the omission.

If it goes wrong

Rollback

Three recovery points exist, in increasing order of blast radius. Use the narrowest one that covers the failure.

1 · The pre-drop dump (fastest)

Every destructive script writes one before it touches anything and prints the exact restore command. Spring's lands in spring/volumes/backups/predrop_<db>_<ts>.sql; Flask's in flask/instance/backups/<db>_<ts>.sql.

mysql -h <host> -P 3306 -u <user> -p <database> < <dump>.sql

2 · Rebuild Spring from the backup

Without --keep-target-schema, the restore rebuilds each table from the MySQL DDL recorded inside the backup — an exact reproduction of the source schema, not an approximation. On a SQLite target, rollback is the file copy shown in Phase 01 instead.

python3 scripts/db_migrate.py restore \
    --backup-file volumes/backups/mysql_backup_<ts>.db

3 · The RDS snapshot

Restores the whole instance, both databases, to the moment before you started. Slow, and it discards anything written since — but it always works.

One habit worth keeping. The scripts now exit non-zero when a migration is incomplete, and all three READMEs describe where a new model must be registered. The reconciliation table is what turns "it printed Complete" into "every row is accounted for" — read it every time, not just this once.