Schema versions, compatibility and migrations

Schemas change while old readers are still running. An additive field with a default keeps both directions working, while a rename without a transition breaks one side silently. Version policy plus a tested downgrade makes evolution boring.

explain
implement

You can now

Derive the valid states of a versioned record across validation, quarantine, normalization and persistence.
Design a backward-compatible schema change with explicit mixed-version behavior and a tested rollback.
Classify DDL changes as compatible or breaking, and run a paired upgrade and downgrade on a scratch database.

Covers

Schema versions, compatibility and explicit migrations
Backward-compatible migration, mixed versions and rollback

Study, in this order

4 steps

Pydantic Extra data as a version policy
Official docs typed-service build path

Extra data (ignore, forbid, allow): old readers drop, keep or reject fields added by newer writers, set through model_config

Done
Pydantic Default values
Official docs typed-service build path

Default values and Validate default values: a new field with a default lets old payloads validate against the new model

Done
Tutorial: migration scripts, running migrations, downgrading
Alembic tutorial - migration scripts and downgrading

Create a Migration Script (revision, down_revision, upgrade, downgrade); Running our First Migration and Running our Second Migration (upgrade head); Getting Information (history --verbose); Downgrading (downgrade base)

Done
Creating and Modifying Tables
AlgoMaster SQL - selected route

Constraints in DDL (primary and foreign keys, UNIQUE, CHECK, DEFAULT); ALTER TABLE (adding and dropping columns, changing types, adding constraints); DROP TABLE, TRUNCATE and DELETE

Done

Focus on

Additive changes (a new optional field, a new table) keep old readers working. Renames, type narrows and new required fields break one direction until every reader upgrades.
Every migration ships upgrade and downgrade together, and downgrade is executed, not just written. An empty downgrade makes rollback untested.
Alembic orders by down_revision chains, not file names. history --verbose shows the chain and current shows where the database stands.
Old payloads must validate against new models with defaults filling new fields, and new payloads must not crash old readers; the extra policy decides whether unknowns drop, stay or raise.

Reproduce closed-book

Closed-book, evolve a v1 record model to v2 with one optional field: validate a v1 payload against v2 and a v2 payload against v1, stating what fills and what drops. Then write one Alembic revision with upgrade and downgrade and run upgrade head, downgrade base, upgrade head on a scratch database.

Traps

extra forbid on a versioned reader. It rejects payloads from newer writers on unknown fields; versioned boundaries usually ignore or allow and stage the unknowns.
A new required field with existing rows. Old writers cannot supply it, and bulk backfill needs a default or a two-step migration.
Renaming a column while mixed versions read it. Old readers query the old name, so expand (add new, dual-write, migrate readers) before contracting (drop old).

Practice

Alembic tutorial: first migration, second migration, `downgrade` round trip

Running our First Migration; Running our Second Migration; Downgrading

Run on a scratch PostgreSQL 18 database (a throwaway postgres:18 container works). Assert alembic current and history --verbose show a clean chain, and that reads succeed after each step.

Self-test

A v2 model adds nickname with a default. Does a v1 payload validate, and does a v1 model accept a v2 payload under default settings?
What orders two Alembic revisions, and how do you inspect the chain?
Name one DDL change that is safe under mixed versions and one that must wait.
Why run downgrade base and then upgrade head when testing?

Completion criterion

Stop when

Both validation directions pass with stated fill and drop behavior, three DDL changes are classified with reasons, and head to base to head succeeds on a scratch database.

Mark passed, then open 4.4 Only mark this when you can do it without notes.

Leads to

4.4 Relational modeling, joins, aggregation and window functions Apply constraints and normalization to the versioned schema.
4.7 Layer boundaries and command-line contracts Keep version checks at the service edge, not in handlers.

Comes after

4.2 JSON, JSONL, CSV, columnar formats and bounded ingestionVersion the normalized record schema before it spreads.

Resources for this unit

Official docs typed-service build path
Python typing, Pydantic, FastAPI and PostgreSQLprimarycourse
Build one record service in this order: structural protocols and generics; Pydantic models, validators, strict mode, serialization and settings; FastAPI request bodies, dependencies, errors and tests; then PostgreSQL transactions and EXPLAIN. Replay valid, malformed, duplicate and previous-schema requests through CLI and HTTP.
AlgoMaster SQL - selected route
AlgoMasterrequiredcourse
Study SQL Foundations, Querying Data, Joins, Subqueries and CTEs, Window Functions, DDL, DML and Views, Performance Optimization, Transactions and Concurrency, and Data Modeling. Stop when the service queries and transaction tests pass.
Alembic tutorial - migration scripts and downgrading
SQLAlchemysupportreference
Use Create a Migration Script, Running our First and Second Migration, Getting Information and Downgrading to run a paired upgrade and downgrade round trip on a scratch database.

Also in this section

PostgreSQL transactions and EXPLAIN
PostgreSQLrequiredreference
Use the transaction tutorial and EXPLAIN guide to demonstrate commit, rollback, one isolation anomaly or prevented anomaly, and a measured plan improvement on the database actually used.
FastAPI tutorial
FastAPIsupportimplementation
Use Request Body, Dependencies, Handling Errors and Testing to expose the same service layer through HTTP. Keep framework models outside the domain core and preserve the same error semantics as the CLI.
AlgoMaster Low-Level Design - one rehearsal
AlgoMastersupportpractice
Use one fitting design prompt to rehearse interface contracts, dependency direction and failure ownership. Treat the review as feedback, then verify behavior with your own tests.
Stas' Python Cookbook - data and interfaces
Stas Bekmansupportreference
Use Serialization and Deserialization plus Environment and Arguments for focused implementation help. Compare JSON, JSONL, CSV and compressed data, and never use pickle for untrusted interchange.
AlgoMaster Python - interfaces, generics and data files
AlgoMastersupportcourse
Use Interfaces and Protocols plus Generic Types for structural typing, and Working with JSON plus Working with CSV for JSONL, error handling, newline and DictReader behavior. Lesson quizzes check recall; implementation is verified with your own scripts.
SQLAlchemy 2.1 documentation - select, Session and eager loading
SQLAlchemyreferencereference
Use the select() tutorial for 2.x query style, Session basics for the transaction boundary and identity map, and relationship loading for lazy versus selectinload versus joinedload plus raiseload. Use select() style and Session.execute or Session.scalars only, never the legacy Query API.
PyArrow Parquet read and write docs
Apache Arrowsupportreference
Use Reading and Writing Single Files plus Table from_pydict and from_pylist to convert normalized records to Parquet and test types, nulls, column projection and schema.
Stripe idempotent requests
Stripesupportreference
Use the Idempotency-Key header contract, stored-response replay, parameter-mismatch errors and POST-only scope to implement keyed replay for one endpoint.