Skip to content

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-positive drop_index calls (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 touches backend/.

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_trgm extension — not autogenerated, must be hand-added. Still required: location.py:110 has postgresql_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:172 table args, added by 53ce95a713cc).
  • 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_module on connector_datasources (from 89c3079e388d): unique on (module_type_id, COALESCE(data_entry_type_id, 0)) where is_active = true. app/models/connector.py's ConnectorDatasource had 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_name on carbon_projects (from f9d210343448): first is unique on (unit_id, carbon_report_type) where carbon_report_type != 'Simulator_Plan'; second is unique on (unit_id, name) where carbon_report_type = 'Simulator_Plan'. app/models/carbon_project.py had a comment documenting both by name and intent but no actual Index(...) — 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 (so sa.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; the update_updated_at_column() trigger function.
  • postgresql_concurrently=True on uq_member_role_per_module (in 53ce95a713cc) 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 plain CREATE UNIQUE INDEX is 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 covers strip_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, and rename_process_emissions_quantity_to_quantity_kg (09ec5dcb3688, #2025) — no old rows exist once the DB is dropped, so there's nothing to rewrite. Checked scripts/generate_perf_test_csvs.py, the only seed script touching this key: it already writes quantity_kg for the process-emissions category (line 499) and quantity only 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.py imports 09ec5dcb3688 by file path and executes its UPGRADE_SQL/DOWNGRADE_SQL directly. 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.py just runs uv run alembic upgrade head via make test-db-migrate against 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.toml already carries the E501 per-file ignore for alembic/versions/*.py from 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, dd2ce8461139 through 53ce95a713cc)
  • 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", not alembic 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 E501 ruff ignore in pyproject.toml beyond confirming it's still there — not re-adding it speculatively.