Fresh installs are missing three indexes that migrated installs have
- Dominant language
- Python
- Stars
- 670
- Forks
- 183
- Avg merge
- 15h 13m
- Merged PRs (30d)
- 368
Description
## Objective
Make the schema produced by a fresh install match the schema produced by replaying migrations, for three indexes that only the migrations create.
## Background
The schema is defined in two places. A fresh install runs mgr schema oneshot, which builds tables from the SQLAlchemy model metadata and stamps head without executing a single migration. An existing install runs alembic upgrade head, which executes the migration files. Both must produce the same schema, but nothing checks that they do.
Three migrations create an index that the corresponding model never declares, so the index exists only on migrated databases:
- ix_role_invitations_invitee_user_id (ad7acfe8aa1c) — role_invitation/row.py declares only uq_role_invitations_active
- ix_vfolders_creator_id (b4e7f1a2c3d5) — vfolder/row.py declares creator_id with no index
- ix_prometheus_query_presets_category_id (a3b4c5d6e7f8) — prometheus_query_preset/row.py declares category_id with no index
Verified on a database built at head by oneshot: none of the three indexes is present, and no later migration drops them, so a migrated database has all three. role_invitations.invitee_user_id is the worst case — it carries a FK to users with ON DELETE CASCADE, so on a fresh install every user deletion scans role_invitations. The partial index uq_role_invitations_active cannot substitute: it is restricted to state != accepted, so it does not cover the rows a cascade must find.
The divergence also breaks downgrades: each of the three downgrades calls drop_index on an index that a fresh install never had, aborting with UndefinedObjectError. Whether a downgrade succeeds therefore depends on how the database was created. This is how the drift was found.
Out of scope: fk_kernels_image_id (cd067180a8b1) and fk_login_sessions_login_client_type_id (c7f2a8e31b04) hardcode constraint names that the metadata naming convention renders differently on a fresh install (fk_kernels_image_id_images and fk_login_sessions_login_client_type_id_login_client_types). Those constraints do exist and are enforced on both paths — only the names differ — so they are tracked separately.
## Acceptance Criteria
- The models declare all three indexes, so a fresh install creates them.
- A migration heals databases already created by oneshot, using an existence check so it is a no-op on migrated databases that already have the indexes.
- The three downgrades no longer fail on a fresh install.
- Verified against a local DB by comparing a oneshot-built schema before and after.
- pants fmt / lint / check pass.
JIRA Issue: BA-6913
Contributor guide
Research direction
Start with role_invitation/row.py, vfolder/row.py, and prometheus_query_preset/row.py, then read migrations ad7acfe8aa1c, b4e7f1a2c3d5, and a3b4c5d6e7f8. Add the three model indexes and make each migration safely create a missing index, then verify fresh and migrated schemas, downgrade behavior, and run pants fmt, lint, and check.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python, sqlalchemy
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Clearly specified
- Newbie friendliness
- 68/100