Answer first

Which procedure do I need?

Decide from what the incoming commits change, not from how big they are. The two apps differ because their databases differ:

• Spring runs on the SQLite file ~/open/spring/volumes/sqlite.db (DB_URL is commented out in its .env). Hibernate runs with ddl-auto=none, so it never changes the schema on its own — new code against an old schema fails at runtime.
• Flask runs on MySQL in AWS RDS (database user_management). SQLAlchemy's create_all() can add missing tables but never columns.

Do one app at a time. Finish Spring — up, smoke-tested — before starting Flask.

Before the next upgrade

Merging the fixes this depends on

Four fixes came out of the 2026-10-21 upgrade. Until they are merged, parts of this runbook fail on production. They are independent of each other — none conflicts with another, and all three Flask PRs were test-merged together — so the order below is about risk, not about breaking the build.

Recommended order: 1 (unblocks every Spring deploy), then 2 + 3 + 4 together on Flask, deployed in one git pull && docker compose up -d --build. Each PR description has its own "How to merge" section with the check to run afterwards.

Phase 00

Preflight (both apps)

1. 01

   Get a shell on cockpitdev machine

   ssh cockpit                 # Host cockpit → cockpit.opencodingsociety.com, user ubuntu

   If SSH hangs with Connection timed out during banner exchange while the sites still load, sshd is wedged, not the box. Use the Cockpit web console instead: cockpit.opencodingsociety.com → Terminal. Every command below works there.

2. 02

   Confirm the checkout is clean and see what is comingcockpit · open/spring and open/flask

   cd ~/open/spring            # or ~/open/flask; branch is master / main
   git status -sb              # modified tracked files? stop and find out why before pulling
   git fetch
   git log --oneline HEAD..@{u}

   Untracked files (??) are fine. Modified tracked files ( M) are local edits someone made on the server; check that the incoming commits do not touch them (git diff --name-only HEAD @{u}), or git pull --ff-only will refuse.

3. 03

   Decide which procedure — read the schema deltacockpit

   Spring — list incoming files that are (or were) JPA entities:

   cd ~/open/spring
   for f in $(git diff --name-only HEAD @{u} -- '*.java'); do
     (git show @{u}:$f 2>/dev/null; git show HEAD:$f 2>/dev/null) | grep -qE '@Entity|@Embeddable' && echo "$f"
   done
   git diff HEAD @{u} -- '*.java' | grep -E '^\+\+\+|^[+-]\s*@(Entity|Table|Column|JoinColumn|JoinTable|ManyToOne|OneToMany|ManyToMany|ElementCollection)|^[+-]\s*private [A-Za-z<>, ]+ [a-zA-Z]+;'

   Flask — list column and table definitions that change:

   cd ~/open/flask
   git diff --stat HEAD @{u} -- model/
   git diff HEAD @{u} -- model/ | grep -E '^[+-].*(db\.Column|__tablename__|db\.Table\()'

   Nothing printed → code-only. Then pick from the table in Which procedure.

4. 04

   Pick a quiet time and tell people—

   Spring is fully down for the whole of procedure 01 (a few minutes; init boots the app). Flask stays up in 02 / 02b and is down for most of 02c.

Phase 01 · any Spring entity change

Spring: backup → init → restore

init rebuilds an empty schema from the entities of the code you just pulled; restore loads the backup into it, copying every column both schemas share. New columns take their defaults, new tables start empty, and every existing row comes back — including the *_seq id tables and the HT_*/HTE_* tables, which Hibernate will not recreate.

Rehearse first (dev machine, no production data)

Production data stays on cockpit. Rehearse the exact commit on a copy of your local dev database instead — it has the same 160-odd tables, so it proves init builds and restore reconciles on the new code:

cd spring && git fetch upstream
git worktree add --detach ../spring-rehearsal upstream/master
cp .env ../spring-rehearsal/.env           # DB_URL must stay commented out → SQLite
mkdir -p ../spring-rehearsal/volumes/backups
python3 -c "import sqlite3; s=sqlite3.connect('volumes/sqlite.db'); d=sqlite3.connect('../spring-rehearsal/volumes/sqlite.db'); s.backup(d)"
cd ../spring-rehearsal
python3 scripts/db_migrate.py backup
B=$(ls -t volumes/backups/sqlite_backup_*.db | head -1)
FORCE_YES=true python3 scripts/db_migrate.py init && python3 scripts/db_migrate.py restore --backup-file "$B" --force

It must end with Rows restored equal to Rows in backup and All tables verified. Port 8585 must be free locally (./dev.sh stop spring). Remove the worktree afterwards with git worktree remove ../spring-rehearsal.

On production

1. 01

   Confirm the target and note the before-picturecockpit · open/spring

   cd ~/open/spring
   python3 scripts/db_migrate.py status      # must say Mode: SQLITE; note Tables and Rows

2. 02

   Stop Spring and pullcockpit · open/spring

   docker compose down
   git pull --ff-only
   git log --oneline -1                       # the commit you rehearsed

   Pull before init, or init rebuilds the old schema. The app must be down: init needs port 8585, and the backup must not miss writes made after it.

3. 03

   Back up the live databasecockpit · open/spring

   python3 scripts/db_migrate.py backup

   Uses SQLite's online backup API — never cp the file, it runs in WAL mode. If it fails with unable to open database file, volumes/backups/ is root-owned: sudo chown -R $(id -u):$(id -g) volumes/ and run it again.

4. 04

   Gate — the backup is completegate

   It must end with All tables and all rows accounted for. Pin the file in a variable so the path cannot be mistyped later:

   B=volumes/backups/sqlite_backup_YYYYMMDD_HHMMSS.db     # the file it printed
   ls -l "$B" || echo "MISSING - STOP"

5. 05

   Rebuild the schema and reload the datacockpit · open/spring

   nohup bash -c "FORCE_YES=true python3 scripts/db_migrate.py init && python3 scripts/db_migrate.py restore --backup-file '$B' --force" \
     > ~/upgrade_$(date +%Y%m%d).log 2>&1 &
   tail -f ~/upgrade_$(date +%Y%m%d).log

   && means a failed init never reaches restore; nohup means a dropped SSH or web-console session cannot leave it half done. init boots the app with Maven, so expect a few minutes of Waiting for Spring Boot....

6. 06

   Gate — every row came backgate

   DATABASE RESET COMPLETE
   Rows in backup:  N
   Rows restored:   N          # must equal the line above
   All tables verified: target row counts match the backup.

   All four lines, or do not start the app. If init succeeded but restore failed, re-run only the restore: python3 scripts/db_migrate.py restore --backup-file "$B" --force. Never re-run init at this point.

7. 07

   Start Springcockpit · open/spring

   docker compose up -d --build

   If the build stops at RUN javac Runner.java with error while writing Runner, PR 1 (spring#218) is not merged yet. The database is fine; start with the previous runner image instead: docker compose build web && docker compose up -d --no-build.

8. 08

   Smoke testgate

   docker compose ps                                       # web and code_runner Up
   curl -s -o /dev/null -w "%{http_code}\n" http://localhost:8585/   # 302 (login redirect) = up
   python3 -c "
   import sqlite3; c = sqlite3.connect('file:volumes/sqlite.db?mode=ro', uri=True)
   print('tables', c.execute(\"select count(*) from sqlite_master where type='table'\").fetchone()[0])
   print('person', c.execute('select count(*) from person').fetchone()[0])
   print('integrity', c.execute('pragma quick_check').fetchone()[0])"
   docker compose logs web --since 10m | grep -iE "Schema-validation|APPLICATION FAILED|Exception" | tail

   Table count and person must match the before-picture (plus any new tables); integrity must be ok. Then log in through the site and open a page that uses the changed entity.

Phase 02 · new Flask models only

Flask: create the new tables in place

Nothing is dropped, nothing is reloaded, user ids do not change, and Flask stays up. This is how the nine presence tables went to production on 2026-10-21.

1. 01

   Back up RDS from the hostcockpit · open/flask

   cd ~/open/flask
   OUT=instance/backups/user_management_$(date +%Y%m%d_%H%M%S)_pre_upgrade.sql
   ( eval "$(grep -E '^(DB_ENDPOINT|DB_USERNAME|DB_PASSWORD)=' .env)"
     MYSQL_PWD="$DB_PASSWORD" mysqldump --host="$DB_ENDPOINT" --port=3306 --user="$DB_USERNAME" \
       --single-transaction --set-gtid-purged=OFF --routines --triggers user_management > "$OUT" )
   ls -l "$OUT"; tail -1 "$OUT"

   The web container has no mysqldump, so this runs on the host. It loads only the three DB_* lines from .env — source .env breaks on other lines in that file — and the subshell keeps them out of your session.

2. 02

   Gate — the dump is completegate

   The last line must be -- Dump completed on …. If mysqldump instead says FLUSH TABLES WITH READ LOCK … Access denied, the --set-gtid-purged=OFF flag is missing. Delete any partial file before retrying.

3. 03

   Deploy the codecockpit · open/flask

   git pull --ff-only && git log --oneline -1
   docker compose up -d --build

   Safe before the schema: Flask only creates tables when told to. Pages for the new models error until step 05; everything else keeps working.

4. 04

   Gate — only tables to addgate

   docker compose exec web python scripts/db_upgrade.py --check

   Exit 2 with a list under Tables to create → continue. MISSING COLUMNS → stop and do 02b first. Unused tables / columns (left in place) are harmless leftovers (for example users.kasm_server_needed).

5. 05

   Create the tablescockpit · open/flask

   docker compose exec web python scripts/db_upgrade.py --backup-file "$OUT"

   instance/ is mounted into the container at the same relative path, so $OUT resolves inside it. The script refuses a missing or truncated dump, creates the tables, and re-counts every existing table.

   Before PR 4 (flask#107) is merged, the equivalent by hand is:

   docker compose exec web python -c "from main import app, db; app.app_context().push(); db.create_all()"

6. 06

   Gate and smoke testgate

   The script must end with All N existing tables kept their row counts and Upgrade complete; a second --check must say Up to date. Then:

   curl -s -o /dev/null -w "%{http_code}\n" http://localhost:8587/     # 200
   docker compose logs web --since 10m | grep -iE "error|traceback" | tail

   Log in as a real (non-seed) user and open a page that uses the new model.

Phase 02b · a new column on an existing Flask table

Flask: add the column, then 02

create_all() never alters an existing table, so a new column is one ALTER TABLE, applied before the code that reads it is deployed. The precedent is in model/user.py, next to active and last_verified: the PR that adds a column should carry its ALTER (and any backfill UPDATE) in a comment or its description.

1. 01

   Back up — exactly 02 step 01, and pass its gatecockpit · open/flask

2. 02

   Apply the ALTER from the PRcockpit · open/flask

   ( eval "$(grep -E '^(DB_ENDPOINT|DB_USERNAME|DB_PASSWORD)=' .env)"
     MYSQL_PWD="$DB_PASSWORD" mysql --host="$DB_ENDPOINT" --user="$DB_USERNAME" user_management \
       -e "ALTER TABLE users ADD COLUMN example_flag BOOLEAN NOT NULL DEFAULT 0;" )

   A NOT NULL column needs a DEFAULT or MySQL rejects it on a table that has rows. Think about what existing rows should hold — when active was added, every existing account was set to active with an explicit UPDATE, otherwise everyone would have been locked out.

3. 03

   Continue with 02 from step 03cockpit · open/flask

   Deploy, then db_upgrade.py --check must no longer list MISSING COLUMNS.

Phase 02c · last resort

Flask: full rebuild (db_init + restore)

This drops every table on RDS and round-trips all data through the admin export/import API, so production's code and your local checkout must be the same commit. The full step list, with its gates, is in the flask README, section "Database Management Workflow with Scripts". In short:

1. On cockpit: git pull && docker compose up -d --build (code first — the pull needs production's new export endpoints).
2. On your machine, with the production admin in .env (ADMIN_UID / ADMIN_PASSWORD): python scripts/db_migrate-prod2sqlite.py — must exit 0, every table ok.
3. Test locally against that data.
4. On cockpit, in a Python environment with the app's requirements: python scripts/db_init.py — takes the mysqldump, then drop_all(), create_all(), seed data. Note the dump path.
5. On your machine: python scripts/db_restore-sqlite2prod.py — must report a clean reconciliation; +N extra are seed rows, MISSING is a failure.
6. Check that real accounts are still active: log in as a student and as a teacher.

The pulled SQLite file is the only complete copy of production between steps 4 and 5. Do not delete it until step 5 reconciles.

Phase 03 · no entity / model changes

Code-only upgrade (both apps)

Still take a backup — it costs a minute and a bad release can write bad data.

# Spring
cd ~/open/spring
python3 scripts/db_migrate.py backup          # must end "All tables and all rows accounted for."
git pull --ff-only && docker compose up -d --build

# Flask
cd ~/open/flask
# 02 step 01 (mysqldump), pass its gate, then:
git pull --ff-only && docker compose up -d --build

Smoke test as in 01 step 08 / 02 step 06. Rolling back is git checkout <previous commit> && docker compose up -d --build, because the database did not change.

If it goes wrong

Rollback

Spring — the backup file is a complete database

cd ~/open/spring
docker compose down
git checkout <previous commit>                  # the old code matches the old schema
cp "$B" volumes/sqlite.db
rm -f volumes/sqlite.db-wal volumes/sqlite.db-shm   # or the old WAL replays on top
docker compose up -d --build

Do not start the new code on the old database: with ddl-auto=none the missing columns break every query that touches them.

Flask — load the dump back into RDS

cd ~/open/flask
( eval "$(grep -E '^(DB_ENDPOINT|DB_USERNAME|DB_PASSWORD)=' .env)"
  MYSQL_PWD="$DB_PASSWORD" mysql --host="$DB_ENDPOINT" --user="$DB_USERNAME" user_management < "$OUT" )
git checkout <previous commit> && docker compose up -d --build

After 02 alone, rolling back the code is usually enough — the new tables are unused by the old code. Load the dump when data was changed or lost.

Last line of defence

An RDS snapshot from the AWS console, taken before a 02c rebuild, restores the whole instance. Slow, and it discards everything written since.

Learned the hard way

Pitfalls we have hit

Record

Upgrade log

Add a row each time — date, commit range, procedure, and the reconciliation numbers.