13 Schema Evolution

Schema Evolution

Status: IMPLEMENTED FOUNDATION / LIVE REHEARSAL OPEN. Alembic owns a linear nine-revision ledger and PostgreSQL-backed version table. prepare-schema installs a truly blank database without source rows, upgrades an already-versioned database, and refuses an unversioned non-empty database. Disposable install/upgrade/downgrade tests and a full production-Compose blank install pass. Live state: no production predecessor upgrade, lock-duration, backup/restore, or forward-fix rehearsal has been performed. Denoising state: sharp (noise -> rough -> sharp -> converged) Updated: 2026-07-25

Purpose

Separate two jobs that Dagster asset code previously blurred:

1. Build or refresh data: Dagster assets capture, normalize, rebuild, check, and publish data.
2. Change deployed database shape: Alembic revisions add/alter/drop durable relations, constraints, indexes, and compatibility objects in a reviewed order.

Dagster definitions are not a schema-version ledger. Alembic has no ORM authority here: target_metadata=None, revisions are explicit reviewed SQL/operations, and autogeneration is not used. This follows Alembic’s normal revision chain while preserving the repo’s raw-SQL ownership.

Two Database Paths

New database at the current release

1. Run the dedicated schema-preparation command against a truly blank PostgreSQL database.
2. It acquires a database advisory lock, creates only empty source/migration structures, and materializes the non-source Dagster graph with the explicit schema-install run tag.
3. It verifies that source tables still contain zero rows and that every checked-in query contract has a relation.
4. Only after those checks pass does it stamp ops.schema_migrations at head.
5. Start the Dagster code location and daemon only after preparation succeeds.

make prepare-schema
uv run alembic current --check-heads

make verify-bootstrap remains the separate fixture-backed behavioral acceptance gate. It proves the complete data graph and P01-P12 against an isolated disposable database; it is not the production schema installer.

ops.schema_install_state records install attempts, target revision, completion/failure state, and failed Dagster steps. A failed first install may be retried under the same advisory lock. A current database is a no-op; an older versioned database follows the normal immutable upgrade chain.

Existing deployed database

1. Back up and record the current revision/schema fingerprint.
2. Stop writers affected by the revision.
3. For the one-time adoption only, verify the final pre-Alembic shape and stamp 20260724_0001.
4. Run make prepare-schema (which invokes alembic upgrade head for a versioned database).
5. Run schema/contracts and the affected slice smoke before re-enabling writers.

Blank databases cannot run through the adoption marker: revision 20260724_0001 fails clearly unless the pre-Alembic sentinel relation exists. stamp is never a repair command.

Ownership Rules

• src/bioloupe_data/migrations/versions/ is append-only. Never edit a revision that may have reached any shared environment.
• Keep exactly one base and one head. make migration-check enforces the linear graph.
• Every deployed shape change needs an upgrade, a reviewed rollback/forward-fix decision, and a prior-version test.
• Update current bootstrap DDL in the same change so a new database at head has the same shape.
• Prefer expand -> backfill -> verify -> switch readers -> contract. Do not combine an irreversible drop/rename with the expansion release.
• Large data backfills are Dagster assets with partitions, receipts, and resumability. A migration may add the new shape but must not hold a long DDL transaction while rewriting a large corpus.
• Add foreign-key/check constraints as NOT VALID and validate separately when live table scans or lock duration matter. Create large indexes concurrently in an explicitly non-transactional revision only after measuring and documenting recovery.
• query.q_* names remain consumer contracts. Shape-breaking changes require compatibility views or versioned columns until all consumers are proven cut over.
• Production startup uses upgrade, never stamp, after the one-time baseline adoption.

Current Revision Chain

Revision	Meaning	Upgrade proof
20260724_0001	marker for the final pre-Alembic schema	refuses a blank database
20260724_0002	database-owned curation.decision_events.event_sequence cursor	prior schema -> upgrade -> inserts sequence monotonically -> downgrade -> upgrade
20260724_0003	query-generation manifests, serving heads, promotion/rollback events, and frozen-table mutation guard	prior schema -> upgrade -> registry exists -> empty downgrade works -> downgrade refuses retained generations
20260725_0004	explicit CTGov bootstrap/incremental capture kinds and update windows	prior schema -> upgrade -> new columns/constraints -> downgrade
20260725_0005	current governed SOC and trial-eligibility inference projections	upgrade rewrites effective views to active revisions; downgrade restores prior revisions
20260725_0006	publication claims restricted to each record’s current immutable source artifact	upgrade rewrites current-claim view; downgrade restores the prior definition
20260725_0007	source-bound trial-eligibility projection v5	upgrade activates quote/value-bound claims; downgrade returns to v4
20260725_0008	source-current effective regulatory claims	upgrade adds effective approval/indication/safety views and rewires canonical rebuilds
20260725_0009	resumable blank-schema installation state	upgrade creates ops.schema_install_state; blank installer stamps only after graph verification
20260725_0010	section-bound trial eligibility inference	active view admits only v6 projections whose polarity is tied to the exact source section
20260725_0011	effective FDA label selection	adds an explicit active label selector and prevents ungoverned source sections from entering SOC evidence
20260725_0012	separate regulatory approved use from SOC	removes automatic label-derived SOC publication and requires explicit reviewed promotion
20260725_0013	persisted SOC review candidates	strengthens candidate identity and exposes non-SOC approved-use review subjects
20260725_0014	opaque curation decision ids	removes the false assumption that external curation decision identifiers are UUIDs
20260726_0015	regulatory FDA-label ownership and source-record approval identity	preserves source/evidence history while moving label tables to regulatory ownership; invalidates only derived approval/SOC projections whose old application-grained keys are unsafe
20260726_0016	explicit SOC claim origin	separates current regulatory approved-use candidates from optional legacy reconciliation evidence and rebuilds only derived SOC projections
20260726_0017	complete-live clinical vocabulary activation	removes unsafe incomplete live pointers, preserves immutable artifacts, and enforces fail-closed active selection

The version row lives at ops.schema_migrations; DATABASE_URL is required at runtime and is never stored in alembic.ini.

Commands

make migration-check                 # one base, one head; no database
make prepare-schema                  # blank current install or versioned upgrade
uv run alembic current --check-heads # deployed database is at head
uv run alembic upgrade head          # move an existing versioned database forward
uv run alembic downgrade -1          # rehearsal only when the revision declares safe rollback
uv run alembic upgrade head --sql    # render reviewable SQL where offline mode is supported

tests/test_migrations.py and tests/test_schema_install.py run each database case in its own disposable local PostgreSQL database. They prove revision-graph integrity, baseline refusal, prior-version upgrade/downgrade behavior, blank current installation, zero source rows, required query relations, retry state, unversioned non-empty refusal, and current no-op behavior.

Completion Boundary

This foundation closes “Alembic is installed but unused” and “a blank production database cannot start.” Release-grade live operations still require:

• an immutable fingerprint/preflight for the full stamped baseline;
• conversion of remaining ad hoc deployed ALTER TABLE evolution into revisions;
• upgrade tests from every supported release, not only the adoption baseline;
• measured lock/statement timeouts, backup/restore, and forward-fix rehearsal;
• live proof that the one-shot migration service succeeds against the supported production predecessor before webserver/daemon rollout.

References: Alembic tutorial, Alembic cookbook, and PostgreSQL ALTER TABLE.