Consolidate Alembic Files Implementation Plan¶
For agentic workers: REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (
- [ ]) syntax for tracking.
Goal: Replace the 15 migration files under backend/alembic/versions/ with a single autogenerated migration matching current app/models/, since #2211 confirms the DB will be dropped in prod/stage/dev — no upgrade path needs to survive the collapse.
Architecture: Same mechanism as the last collapse (commit 72d56d58, 2026-06-15): delete every file in alembic/versions/, run make db-revision against a fresh empty DB to autogenerate one create_all-shaped migration. Two partial unique indexes found during planning (uq_active_datasource_per_module, uq_carbon_projects_unit_type_nonplan/_unit_plan_name) existed only in migration history, not in model code — rather than hand-writing them into the new migration, Task 0 moves them into __table_args__ (mirroring the existing pattern in factor.py/data_ingestion.py/data_entry.py) so alembic revision --autogenerate emits them itself. The only thing left to hand-restore in the migration is the pg_trgm extension, which is a CREATE EXTENSION statement with nothing in SQLAlchemy to hang it off. backend/alembic/CUSTOM_DB_OBJECTS.md is the existing source of truth for these items — this plan updates it rather than re-deriving it.
Tech Stack: Alembic, SQLModel/SQLAlchemy, Postgres 16, uv run (backend), pytest.
Spec: GitHub issue #2211 — "Merge all alembic files, to drop data migrations. We'll drop database."
Global Constraints¶
- Never hand-author Alembic migrations — generate via
make db-revision, then prune false-positivedrop_indexcalls (guardrails.md). - The pruning target this time is the opposite direction: autogenerate normally adds false-positive drops for existing partial indexes; check for the same class of noise (expression/partial indexes,
postgresql_where) in the fresh diff. - No silent data loss disguised as cleanup: every item in
CUSTOM_DB_OBJECTS.md's "Active" and "Captured in models" sections must be verified present in the new migration before merging. make ci(ruff + ty + vue-tsc) must pass; this PR only touchesbackend/.
What's already been audited (context for whoever executes this)¶
Read backend/alembic/CUSTOM_DB_OBJECTS.md in full before starting — it was written for exactly this operation during the last collapse. It is not fully up to date: two partial unique indexes shipped since then live only in migration history, not in model code (found below). Verified during planning (2026-08-20):
pg_trgmextension — not autogenerated, must be hand-added. Still required:location.py:110haspostgresql_ops={"keywords": "gin_trgm_ops"}.- Partial/expression unique indexes confirmed present in models (autogenerate will pick these up correctly):
uq_factor_identity/uq_factor_identity_no_year(factor.py),uq_aggregation_active/uq_emission_recalc_active/ix_data_ingestion_jobs_is_current_unique/ix_data_ingestion_jobs_pending(data_ingestion.py),audit_document_one_current_idx(audit.py),uq_member_role_per_module(data_entry.py:172table args, added by53ce95a713cc). - Partial unique indexes that were NOT in model code — this is the gap #2211 asked us to find, now closed by Task 0. Autogenerate diffs against
SQLModel.metadata; these lived only in Alembic history and would have been silently dropped by a naive collapse: uq_active_datasource_per_moduleonconnector_datasources(from89c3079e388d): unique on(module_type_id, COALESCE(data_entry_type_id, 0))whereis_active = true.app/models/connector.py'sConnectorDatasourcehad no__table_args__at all — not even a comment noting the index existed in the DB only.uq_carbon_projects_unit_type_nonplan+uq_carbon_projects_unit_plan_nameoncarbon_projects(fromf9d210343448): first is unique on(unit_id, carbon_report_type)wherecarbon_report_type != 'Simulator_Plan'; second is unique on(unit_id, name)wherecarbon_report_type = 'Simulator_Plan'.app/models/carbon_project.pyhad a comment documenting both by name and intent but no actualIndex(...)— so this one was a deliberate choice to keep it migration-only, not an oversight, but it's the same blind spot for autogenerate either way.
Task 0 adds both as Index(...).ddl_if(dialect="postgresql") in __table_args__, mirroring the exact pattern already used in factor.py/data_ingestion.py/data_entry.py — so alembic revision --autogenerate in Task 1 emits them itself. No hand-authored index DDL in the migration; only pg_trgm needs that treatment (see Task 1, Step 3).
- Enum values added via
ALTER TYPE— confirmed present in the Python enums (sosa.Enum(...)emits them directly):SyncStatusEnum.SKIPPED/RETRY_QUEUED(audit.py),IngestionMethod.computed(data_ingestion.py),TargetTypeEnum.REFERENCE_DATA(data_ingestion.py). - Retired, do not recreate — confirmed zero references left in
app/: custom collations (ch_it_ci_ai/ch_de_ci_ai/ch_fr_ci_ai) and their indexes; theupdate_updated_at_column()trigger function. postgresql_concurrently=Trueonuq_member_role_per_module(in53ce95a713cc) is a lock-avoidance technique for building an index on a live ~1M-row table. It does not need to survive into the squashed migration — the collapsed migration runs once against an empty DB, where a plainCREATE UNIQUE INDEXis instant. If autogenerate emits it as a plain index, leave it plain; don't hand-add concurrency back.- Data-only migrations are dropped, not merged —
CUSTOM_DB_OBJECTS.md's own "Data migrations / backfills" section already says these are ignored on collapse. This coversstrip_legacy_primary_factor_id,strip_legacy_status_from_entry_json,backfill_data_entries_year_and_unit_id,migrate_mice_research_facility_type,migrate_legacy_traveler_sentinels, andrename_process_emissions_quantity_to_quantity_kg(09ec5dcb3688, #2025) — no old rows exist once the DB is dropped, so there's nothing to rewrite. Checkedscripts/generate_perf_test_csvs.py, the only seed script touching this key: it already writesquantity_kgfor the process-emissions category (line 499) andquantityonly for the unrelated purchase/energy-combustion categories that keep that key — no seed depends on the dropped migration. - Test coupling found:
backend/tests/integration/services/data_ingestion/test_quantity_kg_migration_pg.pyimports09ec5dcb3688by file path and executes itsUPGRADE_SQL/DOWNGRADE_SQLdirectly. That migration is being deleted (it's a data-only migration, in scope for removal per the point above) — the test has nothing left to test and must be deleted with it, not patched. - Test that stays as-is:
backend/tests/integration/test_alembic_migrations.pyjust runsuv run alembic upgrade headviamake test-db-migrateagainst a throwaway container. It doesn't reference specific revisions, so it keeps working unchanged — but note in Step 6 below it can't by itself prove the collapse is complete (an empty migration also "succeeds"). pyproject.tomlalready carries theE501per-file ignore foralembic/versions/*.pyfrom the last collapse (autogenerated SQL string literals can't be line-wrapped) — confirm it's still there, no new edit expected.- Confirmed with maintainer: the DB drop is a one-time reset scoped to landing this squashed migration.
guardrails.md's "The DB persists across deploys" invariant is unchanged and still applies to every migration after this one — do not edit that file as part of this PR.
Task 0: Move the two orphaned partial indexes into model code¶
Do this before Task 1 — the generated migration in Task 1 depends on these being in SQLModel.metadata already, so autogenerate emits them instead of a human.
Files:
- Modify:
backend/app/models/connector.py -
Modify:
backend/app/models/carbon_project.py -
Step 1: Add the import and index to
ConnectorDatasource
app/models/connector.py — add Index, text to the sqlalchemy import, then on ConnectorDatasource:
__table_args__ = (
# One active datasource per module target. Postgres treats each NULL
# as distinct, so the raw column can't be indexed directly — coalesce
# it into the expression instead. ddl_if gates to Postgres: SQLite
# drops the partial WHERE, turning this into an unconditional unique
# and breaking tests that legitimately reuse (module_type_id, None).
Index(
"uq_active_datasource_per_module",
"module_type_id",
text("COALESCE(data_entry_type_id, 0)"),
unique=True,
postgresql_where=text("is_active = true"),
).ddl_if(dialect="postgresql"),
)
- Step 2: Add the import and indexes to
CarbonProject
app/models/carbon_project.py — add Index, text to the sqlalchemy import, then replace the comment-only block on CarbonProject with:
__table_args__ = (
# Two partial unique indexes. ddl_if gates both to Postgres: the
# SQLite unit-test schema is intentionally unconstrained here.
# One project per (unit_id, carbon_report_type) for Calculator /
# Simulator_Explore; a unit may hold multiple Simulator_Plan
# projects instead, unique per (unit_id, name) since the name is a
# URL identifier.
Index(
"uq_carbon_projects_unit_type_nonplan",
"unit_id",
"carbon_report_type",
unique=True,
postgresql_where=text("carbon_report_type != 'Simulator_Plan'"),
).ddl_if(dialect="postgresql"),
Index(
"uq_carbon_projects_unit_plan_name",
"unit_id",
"name",
unique=True,
postgresql_where=text("carbon_report_type = 'Simulator_Plan'"),
).ddl_if(dialect="postgresql"),
)
- Step 3: Lint and type-check the two files
cd backend
uv run ruff check app/models/connector.py app/models/carbon_project.py
uv run ty check app/models/connector.py app/models/carbon_project.py
Expected: PASS on both. (Confirmed during planning — both clean.)
- Step 4: Commit separately from the migration collapse
This is a model-code fix, independent of Task 1's file-shuffling — keep it its own commit so a revert of one doesn't require reverting the other.
git add app/models/connector.py app/models/carbon_project.py
git commit -m "fix(backend): declare orphaned partial indexes in model code (#2211)"
Task 1: Collapse the migrations¶
Files:
- Delete: all 15 files in
backend/alembic/versions/(listed above,dd2ce8461139through53ce95a713cc) - Delete:
backend/tests/integration/services/data_ingestion/test_quantity_kg_migration_pg.py - Create:
backend/alembic/versions/2026_08_20_1628-ad1593afc72f_initial_migration.py -
Modify:
backend/alembic/CUSTOM_DB_OBJECTS.md -
Step 1: Delete the old migration files
cd backend
git rm alembic/versions/*.py
git rm tests/integration/services/data_ingestion/test_quantity_kg_migration_pg.py
- Step 2: Drop, recreate, and generate against your real dev DB
Settings.settings_customise_sources (app/core/config.py:379-390) ranks .env above real env vars on purpose, so a DB_URL=... make db-revision override is silently ignored — don't try that. Instead use the DB backend/.env already points at; #2211 authorizes dropping it:
cd backend
make db-drop
make db-create
make db-revision message="initial migration"
This generates one migration containing every create_table/create_index for the current models, diffed against a genuinely empty DB.
Executor's note: this worktree's backend/.env had DB_URL commented out, which defaults to a SQLite file (.env's own comment says PostgreSQL is required — the SQLite fallback "only suits isolated unit tests"). Uncommented the localhost:5432 line to point at the already-running co2-calculator-postgres docker-compose container before running the above. .env is gitignored, nothing to commit for this.
- Step 3: Hand-restore what autogenerate still cannot see
With Task 0 done, the only thing left with nothing in SQLAlchemy to hang it off is the pg_trgm extension. In the generated migration's upgrade(), add at the very top (before the first op.create_table):
# Required by the gin_trgm_ops index on locations.keywords.
# Autogenerate cannot emit this; see alembic/CUSTOM_DB_OBJECTS.md.
op.execute("CREATE EXTENSION IF NOT EXISTS pg_trgm")
In downgrade(), add at the very end (after the last op.drop_table):
op.execute("DROP EXTENSION IF EXISTS pg_trgm")
- Step 4: Diff-check the generated migration against
CUSTOM_DB_OBJECTS.md
For each entry under "Captured in models — verify, don't hand-write", grep the new migration file and confirm it's there: uq_factor_identity, uq_factor_identity_no_year, uq_emission_recalc_active, uq_aggregation_active, ix_data_ingestion_jobs_is_current_unique, ix_data_ingestion_jobs_pending, audit_document_one_current_idx, uq_member_role_per_module, and — now that Task 0 landed — uq_active_datasource_per_module, uq_carbon_projects_unit_type_nonplan, uq_carbon_projects_unit_plan_name. If any is missing, the model's __table_args__ didn't round-trip through autogenerate as expected — stop and investigate the model, don't hand-write the index into the migration.
Confirm none of the retired items reappear: no ch_it_ci_ai/ch_de_ci_ai/ch_fr_ci_ai collations, no update_updated_at_column trigger.
Prune any drop_index/create_index pairs that are pure false positives (autogenerate re-describing an index it already matches correctly) — same class of noise 53ce95a713cc pruned.
- Step 5: Update
CUSTOM_DB_OBJECTS.md
Change the "Origin" references from the old file paths to the new single migration's filename. Move uq_active_datasource_per_module and uq_carbon_projects_unit_type_nonplan/uq_carbon_projects_unit_plan_name into the "Captured in models — verify, don't hand-write" section (Task 0 put them in __table_args__, so they belong alongside uq_factor_identity etc., not in "Active" — nothing here is hand-authored anymore except pg_trgm). Keep the doc's overall structure (Active / Captured in models / Retired / Data migrations) — it's the checklist the next collapse will run.
- Step 6: Prove the collapse is complete, not just that it applies
alembic upgrade head succeeding isn't proof the migration matches the models — an empty migration would pass that too. Regenerate against the now-migrated DB and confirm the diff is empty:
cd backend
make db-migrate
uv run alembic revision --autogenerate -m "should_be_empty_verification"
Expected: the new revision file's upgrade()/downgrade() are empty (just pass / # ### commands auto generated by Alembic - please adjust! ### / ### end Alembic commands ### with nothing between). If it contains any op.create_index/op.create_table, Step 3's hand-restore missed something — fix it, then delete this verification file (it's not going in the PR).
Confirmed: second autogenerate produced only pass/pass — deleted the verification file.
- Step 7: Run the migration smoke test
make db-drop && make db-create
uv run pytest tests/integration/test_alembic_migrations.py -v
Expected: PASS — alembic upgrade head succeeds on a fresh container with just the one migration. Confirmed: 1 passed in 8.97s.
- Step 8: Run the backend suite
make ci
uv run pytest tests/unit
Expected: PASS, and no test still references a deleted migration file (the only one that did, test_quantity_kg_migration_pg.py, was removed in Step 1). Confirmed: make ci green (lint + ty + vue-tsc + helm), 2200 passed.
- Step 9: Commit
git add backend/alembic backend/tests
git commit -m "chore(backend): consolidate alembic migrations (#2211)"
Explicitly out of scope¶
- No downgrade-path testing beyond what the squashed migration's own
downgrade()does — pre-v1.x, rollback is "restore from the last drop", notalembic downgrade. - No further changes to
app/models/beyond Task 0's two indexes — if Task 1 Step 4 finds another model/migration mismatch, that's a separate bug to raise, not fix inline here. - No re-verification of the
E501ruff ignore inpyproject.tomlbeyond confirming it's still there — not re-adding it speculatively.