alembic upgrade head fails: migration b6f2c1e94a30 indexes a nonexistent column
- Dominant language
- Python
- Stars
- 670
- Forks
- 183
- Avg merge
- 15h 13m
- Merged PRs (30d)
- 368
Description
## Symptom
`./py -m alembic upgrade head` fails on migration `b6f2c1e94a30_rename_entity_invitations_to_shares.py` (merged in PR #14275, commit `1dbe69ba52`):
```
asyncpg.exceptions.UndefinedColumnError: column "recipient_virtual_entity_id" does not exist
[SQL: CREATE UNIQUE INDEX uq_entity_shares_live_recipient ON entity_shares (recipient_virtual_entity_id, target_entity_type, target_entity_id) WHERE status = 'pending' AND recipient_virtual_entity_id IS NOT NULL]
```
## Cause
The migration creates the `uq_entity_shares_live_recipient` index on `recipient_virtual_entity_id`, but the columns it actually adds are `recipient_entity_type` / `recipient_entity_id`. The Row definition (`models/entity_share/row.py`) declares the index on `(recipient_entity_type, recipient_entity_id, target_entity_type, target_entity_id)`. The index column name appears to be a leftover from an earlier draft.
## Additional mismatches vs the Row definition (same migration)
1. Partial unique index WHERE clauses: migration uses `status = 'pending'` for both live indexes; Row uses `status IN ('pending', 'accepted')`.
1. `ix_entity_shares_recipient` is declared on the Row and dropped in `downgrade()`, but never created in `upgrade()`.
1. The Row's `accepted_does_not_expire` check constraint exists in no migration (the follow-up `e4c8b1d70a35` only adds `accepted_resolved`).
## Notes
- PostgreSQL DDL is transactional, so the failed migration rolls back; the DB stays safely at the previous revision. No data corruption.
- The revision is unreleased (`# Part of: NEXT_RELEASE_VERSION`), so the migration file can be fixed in place.
JIRA Issue: BA-7720
Contributor guide
Research direction
Start with migrations/versions/b6f2c1e94a30_rename_entity_invitations_to_shares.py and compare its upgrade and downgrade operations with models/entity_share/row.py. Run `./py -m alembic upgrade head` against PostgreSQL, then verify the migration completes and its indexes and constraints match the Row definition, including rollback behavior.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, python
- Domain
- backend, database
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Active
- Clarity
- Clearly specified
- Newbie friendliness
- 76/100