Capstone: migrate, inspect and restore an owned database

Recovery needs evidence about a complete earlier database state, not only a successful command or a remembered revision name. A migration downgrade runs reviewed Python operations; restoring a backup reinstates the state captured by that backup. These actions can have different schema and data outcomes.

The capstone owns a temporary SQLite file and two Alembic revisions. It commits base rows, closes its own pooled connections and uses SQLite’s backup API to create an independently reopenable earlier image. No application outside this private fixture writes to the database, which is a crucial boundary for the experiment.

After the backup, the program upgrades to owned_label and changes the stored scores by a bound increment. It records that migrated state, disposes its own Engine pool again, restores the backup into the owned file and opens a fresh connection.

For scores [3, 7] and increment 5, changedScores is [8, 12], but restoredScores returns to [3, 7]. The restored revision is owned_base and the restored schema no longer has label. Inspecting all three observations makes the recovery concrete: an earlier version string alone would not establish that rows and columns were restored.

The controlled mistake skips the actual restoration call. The program still reopens the file, but it sees the migrated head, changed scores and added label column. A message saying restore succeeded would therefore be contradicted by the actual database state.

Engine.dispose does not stop other processes, cancel every checked-out transaction or quiesce a real service. This fixture has no such writers by construction. Likewise, restoring the earlier image discards the fixture’s later score updates; a production recovery plan must account for accepted writes after the backup rather than pretending that any older image preserves them automatically.

First type the disposal, backup restoration and fresh revision check. For independent practice read COALESCE(SUM(score), 0) from the restored database and include restoredTotal in the report. Compute it through the reopened connection rather than summing the original Python argument beside an unverified restore.

Keep the earlier migrated-state observations as well as the restored rows, revision and column checks. Use positive values, a zero with a negative value, and an empty table. The empty table is important because SQL SUM alone returns NULL; the requested report contract instead requires an explicit zero.

Step 1: Read a COALESCE(SUM(score), 0) total from the restored database and include restoredTotal in the report. Step 2: Read the total through the fresh restored connection and retain every existing recovery observation.

The solution queries the restored database for its total and returns ten for [3, 7], minus two for [0, -2], and zero for no rows. The rest of the recovery report remains unchanged. An omitted restore cannot satisfy the complete observed state merely by copying a total from the input.

All database handles in the copy helper are explicitly closed; sqlite3 connection context managers alone are not a general connection-closing guarantee. The lab also verifies disposal of its SQLAlchemy engines and keeps every file under an owned temporary workspace. Those checks establish this bounded exercise, not an operational restoration promise for another service or database dialect.

Present the complete evidence chain: the base revision and committed rows, an actual backup, the later migrated state, the restoration call, and independent reopened checks of schema, version and data. Explain precisely which later writes the chosen image does not retain and why downgrade is a different operation.

Review the whole course through that chain: parameterized SQL, mapped constraints, transaction ownership, result shape, bounded queries, loading, set-based writes, savepoints, stale versions, async IO and reviewed migrations each protect a different contract. Finish by changing one controlled defect and naming which independent observation would expose it before copying this design into another environment.

Optional stretch — after the core task works: Design a recovery check for writes accepted after a backup and state what additional journal or reconciliation evidence it requires.

Computer practice: https://applydjinn.com/typorithm/sqlalchemy-lab-20261107.zip. Install the pinned requirements in an isolated Python 3.13 environment and run verify.py. Edit the working exercise baseline, then run --practice sa-capstone. The lab uses disposable SQLite databases and actual SQLAlchemy/Alembic calls; it does not connect to application accounts, production databases or paid services. The app includes prepared results and explained questions without an editor or Python runtime.