Migration safety

Lesson 11
50 min
Module 4: Release AI and database changes

Lesson 11 of 15 · CI/CD, Testing and Safe ReleasesMigration safety

Course overview

1. Understand the delivery path

2. Create a repeatable CI pipeline

3. Manage environments and secrets

4. Release AI and database changes

5. Recover and maintain the release process

What you will be able to do

During every deployment, the old version and the new version run at the same time, on the same database. This lesson changes the database so that both keep working. First add, later remove, and run each change once, in order, before the new version starts.

Before you startWhat to know and to have readyAbout 50 minutes

What you need to know

The history table in PostgreSQL and Compose with api and db, from Persistent data: Add the history in Docker and Cloud Essentials for AI Developers.
Which API changes break clients, from Resource and endpoint design: Rule 3: a version in the path in Build and Deploy Your First API.
The local environments (deploy/local/compose.yaml, scripts/promote-local.sh, port 8082 for production) and the platform lines, from Promote to staging: The local route and The image that did not start on a Mac.
The flag CLASSIFIER_VERSION and the gate, from AI evaluation gate: Worked example: keywords-1.1 behind a flag and The gate in CI.

What you need on your computer

Your repository with keywords-1.1 and the gate merged into main, from AI evaluation gate: Guided practice.
The local production environment, with its .env (POSTGRES_PASSWORD) and secrets/production_api_key.txt, from Configuration separation: Guided practice: the local environment files and Promote to staging: Guided practice: promote your own digest. Docker Desktop must run.
If your project is missing or broken, copy the snapshot end-of-m03 and do the guided practice of the last lesson again.

Run with GitHub Actions (ubuntu-24.04, PostgreSQL 18.6 as a service container) in Nextia-Intelligence/ticket-api-ci. Also run with Docker Desktop 4.81.0 on macOS (Apple silicon) and postgres:18.6-trixie. The Windows and Linux steps are not tested.

The change: `score` next to `confidence`

Version 1.2.0 renames a field. Every classification has confidence, a number from 0 to 1. The name makes clients think that it is a probability. It is not: it is how strongly the keywords match. The team wants the neutral name score.

The API is the easy part. The database is harder. The table classifications has a column confidence, and two versions of the app use it at the same time:

During the rollout, Container Apps starts the new revision next to the old one. Both answer requests for a moment (Operational configuration in the Docker course).
In a canary (Progressive release), both run for hours on purpose.
After a rollback, the old version runs again on the database that the new version changed.

So the database must work for both versions at every moment. Predict: Amira has two ways to change the table. Which one breaks version 1.1.0?

ALTER TABLE classifications RENAME COLUMN confidence TO score;

ALTER TABLE classifications ADD COLUMN IF NOT EXISTS score real;

Option A breaks 1.1.0 at once. Its code writes and reads confidence, and that column no longer exists. The common mistake at the end of this lesson shows the real errors. Option B breaks nothing. 1.1.0 does not know score, so it ignores the new column. 1.2.0 writes both.

Expand now, contract later

Option B is the first half of a pattern called expand and contract:

Expand: add the new thing next to the old thing. Old and new versions both work.
Move: the new version writes both. Clients change from confidence to score in their own time. The API marks confidence as deprecated: it still works, and it will go away in a known version.
Backfill: fill the new column for old rows. A backfill is a change of data, not of structure: UPDATE … SET score = confidence WHERE score IS NULL.
Contract: remove the old thing, in a later release, when nothing uses it any more. Here that is version 2.0, a major version, because removing a field breaks clients.

Each step is a separate release. Between two steps, you can stop, wait for days, or roll back.

The release sequence of score, with the versions that work at each step. Release 1.2.0 is steps 2 and 3. Steps 4 and 5 come later, each in its own release.

Text description of the diagram

Read the table by column. At each step, at least the version before and the version after work, so a deployment can overlap them. Version 1.2.x fails before step 2, so the migration must run before 1.2.0 starts. Version 1.1.0 fails only after step 5, so the contract waits until no 1.1.0 runs and no client reads confidence. Note the cell of 1.2.x at step 3. Version 1.1.0 still writes rows without score, and 1.2.x must work with those rows too. A rollback to 1.1.0 is possible until step 5, because each step before it only adds.

Migrations: numbered files, applied once

Until now, the app made its own table at start-up (CREATE TABLE IF NOT EXISTS, from the Docker course). That works for one table that never changes. It cannot add a column to a table that already exists, and it cannot say which changes a database already has.

A migration is one numbered change of the database structure or data, in a file. Version 1.2.0 has two:

-- The table that C18 made at start-up. IF NOT EXISTS: a database from C18
-- already has it, and this migration then only records that it is there.
CREATE TABLE IF NOT EXISTS classifications (
    id            bigserial PRIMARY KEY,
    request_id    text        NOT NULL,
    category      text        NOT NULL,
    priority      smallint    NOT NULL,
    confidence    real        NOT NULL,
    model_version text        NOT NULL,
    created_at    timestamptz NOT NULL DEFAULT now()
);

-- Expand: add the new column next to the old one. It may be empty, because
-- version 1.1.0 of the app still writes rows without it during the rollout.
ALTER TABLE classifications ADD COLUMN IF NOT EXISTS score real;

001 is the table that exists today, written down. A database from the Docker course already has it, and IF NOT EXISTS makes the migration safe there: it records the table and changes nothing. 002 has no NOT NULL. That is on purpose: 1.1.0 inserts rows without score, and a NOT NULL column would reject them.

The migration runner, ticket_api/migrate.py, applies them. It keeps a list of the applied migrations in a table of its own, schema_migrations. The core is 10 lines:

def pending(conn: psycopg.Connection) -> tuple[list[Path], set[str]]:
    conn.execute(CREATE_LOG)
    applied = {row[0] for row in conn.execute("SELECT version FROM schema_migrations")}
    files = sorted(MIGRATIONS.glob("*.sql"))
    return [f for f in files if f.stem not in applied], applied

# in main():
        for f in todo:
            with conn.transaction():
                conn.execute(f.read_text(encoding="utf-8"))
                conn.execute("INSERT INTO schema_migrations (version) VALUES (%s)", (f.stem,))
            print(f"applied  {f.stem}")

Three rules are in these lines. The files run in the order of their names, so the number at the start sets the order. A file in schema_migrations never runs again. Each file runs in its own transaction, together with the line that records it: either all of it happens, or none of it. The full file is ticket_api/migrate.py at c3c7135. Many teams use a tool such as Alembic or Flyway for this. The idea is the same: numbered files, a table of applied versions, one transaction each.

Worked example: migrate a database from the Docker course

Amira tests the runner on a copy of a database from the Docker course. It has the table that the app made, and one row from version 1.1.0. First she asks what is pending:

python -m ticket_api.migrate --status

The output (simulated):

pending  001_create_classifications
pending  002_add_score

Then she applies them, and runs the same command again:

python -m ticket_api.migrate
python -m ticket_api.migrate

The output (simulated):

applied  001_create_classifications
applied  002_add_score
Database is up to date (2 migrations).
Database is up to date (2 migrations).

The second run applied nothing. A pipeline needs a runner that can run twice with no harm. Every deployment runs it, and most deployments have nothing to apply. The table now has the new column, and the old row has no score:

 id |  request_id  | model_version | confidence | score
----+--------------+---------------+------------+-------
  1 | c18row000001 | keywords-1.0  |        0.9 |

SELECT version FROM schema_migrations lists 001_create_classifications and 002_add_score, each with the time it was applied.

Migrate first, then start the new version

The new version needs score, so the migration must finish before the new version starts. Amira makes the order part of the deployment, not a step that a person must remember.

The image carries its migrations. One line in the Dockerfile copies them, so the image of 1.2.0 can migrate its own database: COPY migrations ./migrations.

Compose runs them first. A migrate service uses the same image, runs the runner, and ends. The api service starts only after it ended without an error:

services:
  api:
    # ...
    depends_on:
      migrate:
        condition: service_completed_successfully

  migrate:
    image: ${IMAGE}
    platform: linux/amd64
    command: ["python", "-m", "ticket_api.migrate"]
    environment:
      DATABASE_URL: postgresql://tickets:${POSTGRES_PASSWORD}@db:5432/tickets
    depends_on:
      db:
        condition: service_healthy

If a migration fails, migrate ends with exit code 1, and api does not start. The old version keeps running. Add the same migrate service to compose.yaml for your own computer. The platform line is the one from Promote to staging: The image that did not start on a Mac: the reference repository added it to migrate in pull request #6, after 1.2.0.

The app stops making the table. In main.py, the call to history.setup() goes away. A table that the app makes at start-up and a table that the migrations make cannot both be the truth.

CI tests the migrations. The checks job gets a PostgreSQL 18.6 service container: a database that GitHub Actions starts next to the job and removes after it. Two new tests run the migrations twice and save one classification:

    services:
      postgres:
        image: postgres:18.6-trixie
        env:
          POSTGRES_USER: tickets
          POSTGRES_PASSWORD: ci-only-not-secret
          POSTGRES_DB: tickets
        ports: ["5432:5432"]
        options: >-
          --health-cmd "pg_isready -U tickets -d tickets"
          --health-interval 2s --health-timeout 3s --health-retries 15
    # ...
      - name: Unit, contract and database tests
        env:
          TEST_DATABASE_URL: postgresql://tickets:ci-only-not-secret@localhost:5432/tickets
        run: python -m pytest --junitxml=reports/junit.xml

The password is not a secret: the database exists only for the few seconds of the job. The tests skip themselves when TEST_DATABASE_URL is not set, so pytest still works on a computer without a database. In pull request #5, feature/score-field into main, the run 37667028262 collected 30 tests and passed them all in 0.51 seconds.

The API keeps both fields. models.py gets score, and marks confidence as deprecated in the contract. routes.py sets both to the same value:

    confidence: float = Field(
        ge=0,
        le=1,
        description="Deprecated: use score. The same value. Removed in version 2.0.",
        json_schema_extra={"deprecated": True},
    )
    score: float = Field(ge=0, le=1, description="How sure the classifier is, from 0 to 1.")

The version becomes 1.2.0: the middle number, because clients get a new field and nothing breaks. All files of the release are in commit aa8b83e.

002 only adds an empty column. If 1.2.0 fails, Amira deploys 1.1.0 and leaves the column: 1.1.0 ignores it. A restore from a backup would be worse. It puts back the database of the backup time, and deletes every classification since then. So for an ADD COLUMN, the rollback is “leave it”.

A migration that changes or deletes data is different: for example, a backfill or the contract of 2.0. Before it, take a backup, and check that you can read it. On the local route:

docker exec ticket-api-production-db-1 pg_dump -U tickets -d tickets --format=custom > before-1.2.0.dump
docker exec -i ticket-api-production-db-1 pg_restore --list < before-1.2.0.dump | grep TABLE

The output (simulated, on a copy):

220; 1259 16386 TABLE public classifications tickets
3441; 0 16386 TABLE DATA public classifications tickets

The dump file holds the table and its data, so keep it out of Git. A managed database in the cloud, such as Azure Database for PostgreSQL, takes backups by itself. Your task is to know the restore point before the change, and how long a restore takes.

Guided practice: two versions on one database

This is the module’s completion check, done once with Amira’s values. Use the local route, also if you have Azure: the cloud apps of this course have no database, so /v1/history answers 503 there by design. The migration runs in CI’s tests and in the local environments.

Make the branch: git switch -c feature/score-field (from main).
Add migrations/001_create_classifications.sql, migrations/002_add_score.sql and ticket_api/migrate.py. Make the other changes of commit aa8b83e: Dockerfile, both Compose files, main.py, history.py, models.py, routes.py, contract/openapi.json, the version in pyproject.toml, ci.yml, and the tests.
Run python -m pytest -q. Expect 28 passed and 2 skipped: the database tests skip without TEST_DATABASE_URL.
Push the branch and open a pull request: feature/score-field into main. Expect 30 tests in the CI log, and the gate passed. Merge it.
Find the digest of the new image. It is in the build-info artifact of the CI run on main, as in Publish evidence: build-info.json: digest, commit and run in one file. Then tag the merge commit v1.2.0 and push the tag, as for v1.1.0 in Promote to staging: A version without a new build. Module 5 makes the hotfix from this tag.
Promote it to the local production. The migrate service runs before the API.
Start version 1.1.0 next to it, on the same network and database. Use your 1.1.0 digest.
Classify one ticket with each version. Then look at the table.
Stop the old version.

The commands for steps 6 to 9, with the reference repository’s digests (use yours):

NEW=ghcr.io/usmanahmed99/ticket-api-ci@sha256:bd2316b56067c738199d6203a23845c97fb7b85b550379411eddd7cc6a2ddab8
OLD=ghcr.io/usmanahmed99/ticket-api-ci@sha256:4eb3db8d4ea249fb9b44b27d96deb68aba6da3e3fbc9cf32311a9cbadf4d857a
scripts/promote-local.sh production "$NEW"

docker run -d --name ticket-api-old --platform linux/amd64 \
  --network ticket-api-production_default -p 127.0.0.1:8083:8000 -e API_KEY=old-key \
  -e DATABASE_URL="postgresql://tickets:$(grep '^POSTGRES_PASSWORD=' .env | cut -d= -f2)@db:5432/tickets" \
  "$OLD"

The output of the promotion (simulated, shortened):

 Container ticket-api-production-migrate-1 Exited
 Container ticket-api-production-db-1 Healthy
 Container ticket-api-production-api-1 Healthy
ok    health (200)
ok    ready (200)
ok    classify with the key (200)
ok    classify without a key (401)
production now runs ghcr.io/usmanahmed99/ticket-api-ci@sha256:bd2316b5...

docker logs ticket-api-production-migrate-1 shows the three lines of the runner, as in the worked example. On your database from Module 3, 001 finds the table that 1.1.0 made, and only records it. Wait a few seconds for the old version to start (it runs with emulation on Apple silicon). Then send the same ticket to both:

TICKET='{"subject": "Parcel never came", "body": "The courier lost it."}'
curl -s -X POST http://127.0.0.1:8082/v1/classify -H "X-API-Key: $(cat secrets/production_api_key.txt)" \
  -H "Content-Type: application/json" -d "$TICKET"; echo
curl -s -X POST http://127.0.0.1:8083/v1/classify -H "X-API-Key: old-key" \
  -H "Content-Type: application/json" -d "$TICKET"; echo
docker exec ticket-api-production-db-1 psql -U tickets -d tickets \
  -c "SELECT id, request_id, model_version, confidence, score FROM classifications ORDER BY id"

The output (simulated):

{"category":"shipping","priority":1,"confidence":0.9,"score":0.9,"model_version":"keywords-1.0"}
{"category":"shipping","priority":1,"confidence":0.9,"model_version":"keywords-1.0"}
 id |  request_id  | model_version | confidence | score
----+--------------+---------------+------------+-------
  1 | e3357fb137f9 | keywords-1.0  |        0.7 |   0.7
  2 | 169da47b5cdb | keywords-1.0  |        0.9 |   0.9
  3 | a533224672fb | keywords-1.0  |        0.9 |
(3 rows)

Both versions classify, and both save their result in the same table. Version 1.2.0 answers with score; version 1.1.0 answers without it, as before. Row 1 is the smoke check’s request. Row 2 is from 1.2.0. Row 3 is from 1.1.0, so its score is empty. In your table, older rows from 1.1.0 also have an empty score.

Then remove the old version with docker rm -f ticket-api-old. The local production keeps running 1.2.0.

Show the expected result

pytest -q on your computer: 28 passed, 2 skipped. In CI: collected 30 items and 30 passed, then Gate passed. The promotion prints the four ok lines. The two classify answers differ only in score. The table has one row per classification. Every row from 1.2.0 has score equal to confidence, and every row from 1.1.0 has an empty score. If the old container answers nothing, wait 10 seconds and send the request again. If it still does not answer after a minute, read docker logs ticket-api-old.

Amira checks classification with both versions, and looks at the table. Both versions work. Rows with an empty score are what 1.1.0 writes during a rollout: the diagram said that 1.2.x must work with them. Module 5 comes back to this table.

Your turn: the new version before its migration

Change one condition: start version 1.2.0 on an empty database, without running the migrations. Use a throwaway database, not your local production. Predict first: does /ready say ready? What does POST /v1/classify answer?

Show the commands and the answer

docker network create try-order
docker run -d --name try-db --network try-order -e POSTGRES_USER=tickets -e POSTGRES_PASSWORD=pw -e POSTGRES_DB=tickets postgres:18.6-trixie
docker run -d --name try-new --platform linux/amd64 --network try-order -p 127.0.0.1:8085:8000 \
  -e API_KEY=k -e DATABASE_URL=postgresql://tickets:pw@try-db:5432/tickets "$NEW"
curl -s http://127.0.0.1:8085/ready; echo
curl -s -X POST http://127.0.0.1:8085/v1/classify -H "X-API-Key: k" -H "Content-Type: application/json" -d '{"subject": "Refund"}'; echo
docker logs try-new 2>&1 | grep UndefinedTable

The output (simulated):

{"status":"ready"}
{"error":{"code":"internal_error","message":"Something went wrong on the server.","request_id":"dbd7955e7ed6","fields":[]}}
psycopg.errors.UndefinedTable: relation "classifications" does not exist

/ready says ready, because it checks only that the database answers. Every classification fails with 500. A platform would send traffic to this version. Now run the migration with the same image, and try again:

docker run --rm --platform linux/amd64 --network try-order \
  -e DATABASE_URL=postgresql://tickets:pw@try-db:5432/tickets "$NEW" python -m ticket_api.migrate

The three lines of the runner appear, and the classify request then answers 200 with "score":0.7. This is why Compose starts api only after migrate ended without an error. Remove the objects: docker rm -f try-new try-db, then docker network rm try-order.

Rename in one step

Common mistake

The contract step runs while the old version still runs

Symptom (simulated): a database is changed in one step, confidence renamed to score, while version 1.1.0 still runs on it. Every request to 1.1.0 then fails:

POST /v1/classify   500 {"error":{"code":"internal_error",...}}
GET  /v1/history    500 {"error":{"code":"internal_error",...}}
psycopg.errors.UndefinedColumn: column "confidence" of relation "classifications" does not exist
psycopg.errors.UndefinedColumn: column "confidence" does not exist

The health and readiness checks still pass, because they do not use the column.

Cause: the expand and the contract happened at the same time. Version 1.1.0 inserts into confidence and selects it. After the rename, no version before 1.2.0 can work with this database. A rollback to 1.1.0 would not help either: the old code meets the new table.

Fix:

If it happened, rename the column back (ALTER TABLE classifications RENAME COLUMN score TO confidence), so the running version works again.
Do the change in steps: add score (migration 002), deploy the version that writes both, and backfill.
Remove confidence only in a later major version, after no running version and no client uses it.
Test each migration with the previous version too. The coexistence check of the guided practice does that.

For a real AI service: model results often live in tables like this one: scores, labels, the model version. A new model often wants a new column or a new meaning. Every rollout and every rollback mixes versions for a while, so the table must serve both models. A rename that looks clean in a pull request can stop production for every client.

Check your understanding

Migration 002 adds score without NOT NULL. A reviewer asks for NOT NULL with a default of 0, so the column is never empty. What breaks, or what goes wrong?

Show the answer

With NOT NULL and no default, version 1.1.0 could not insert any row: it does not send score. With a default of 0, the inserts work. But every row from 1.1.0 then claims a score of 0, which is false: those tickets had a real value in confidence. An empty score says “not known yet”, and the backfill (003) later fills it from confidence. During a rollout, the column must accept what the old version writes.

Check your understanding

Version 1.2.0 fails in production after migration 002 ran. Should Amira restore the database backup from before the release?

Show the answer

No. 002 only added an empty column, and 1.1.0 ignores it. She deploys the 1.1.0 digest and leaves the column. A restore would put back the database of the backup time. It would delete every classification since then: real data lost, to undo a change that did no harm. Keep backups for migrations that change or delete data, and restore only when data is lost.

Check your understanding

Predict: Amira runs python -m ticket_api.migrate on a database where 001 is applied and 002 has an error in its second statement. Is the first statement of 002 kept?

Show the answer

No. Each migration runs in one transaction, together with its line in schema_migrations. When the second statement fails, the whole of 002 is rolled back: the column from the first statement is gone, and --status still says pending 002_add_score. In the author’s test, the runner printed applied 001_create_classifications, then a SyntaxError, and ended with exit code 1; 001 stayed applied. After a fix, the next run applies 002 from the start.

Recap

Old and new versions share the database during every rollout, canary and rollback. Every migration must keep the previous version working.
Expand and contract: add score next to confidence now (002), write both, backfill later (003), remove confidence in 2.0.
Migrations are numbered files, applied once each, in order, in one transaction each, and recorded in schema_migrations. The runner can run on every deployment.
Migrate first: the image carries its migrations, and Compose starts api only after migrate ended without an error. CI runs the migrations against PostgreSQL 18.6.
An added column needs no restore to roll back. Take a backup before a migration that changes or deletes data.
Versions 1.1.0 and 1.2.0 classified on the same database. Rows from 1.1.0 have an empty score.

Next: Progressive release

Migration safety

The change: score next to confidence

Expand now, contract later

Migrations: numbered files, applied once

Worked example: migrate a database from the Docker course

Migrate first, then start the new version

Guided practice: two versions on one database

Your turn: the new version before its migration

Rename in one step

The change: `score` next to `confidence`