Migrations hardcode FK constraint names that fresh installs render differently
- Dominant language
- Python
- Stars
- 670
- Forks
- 183
- Avg merge
- 17h 7m
- Merged PRs (30d)
- 358
Description
## Objective
Reconcile foreign key constraint names between the two ways the schema gets built, so a constraint has the same name whether the database was created by a fresh install or by replaying migrations.
## Background
A fresh install builds tables from the model metadata (mgr schema oneshot) and stamps head without running migrations; an existing install replays the migration files. Three migrations create a foreign key under a hardcoded name, while the models declare the same FK without a name and let the metadata naming convention generate one. The constraint is created and enforced on both paths – only the name differs.
Observed on a database built at head by oneshot:
- cd067180a8b1 expects fk_kernels_image_id; fresh install has fk_kernels_image_id_images
- c7f2a8e31b04 expects fk_login_sessions_login_client_type_id; fresh install has fk_login_sessions_login_client_type_id_login_client_types
- a3b4c5d6e7f8 expects fk_prometheus_query_presets_category_id; fresh install has fk_prometheus_query_presets_category_id_prometheus_quer_8a51 – the convention name exceeds PostgreSQL's 63-character identifier limit, so it is truncated and hash-suffixed
This is not cosmetic. Each of those downgrades calls drop_constraint with the hardcoded name and aborts with UndefinedObjectError on a fresh install, so whether a downgrade succeeds depends on how the database was created. Verified for a3b4c5d6e7f8: after BA-6913 removes the index error that masked it, the downgrade still fails on this constraint name. Any future migration or tooling that references these names by string has the same split.
Deciding the target name matters: adopting the convention name means renaming on already-migrated databases, while adopting the hardcoded name means renaming on already-deployed fresh installs. Either way the reconciliation needs an existence-checked migration, and the truncated a3b4c5d6e7f8 name shows the convention does not always produce a usable identifier.
Related: BA-6913 covers the sibling drift where three indexes exist only on migrated databases.
## Acceptance Criteria
- Each of the three FKs carries the same name on a fresh install and on a migrated database.
- The three downgrades no longer fail on a fresh install.
- The reconciliation migration is existence-checked so it is a no-op on databases already carrying the target name.
- Verified against a local DB by comparing a oneshot-built schema with a migrated one.
- pants fmt / lint / check pass.
JIRA Issue: BA-6914
Contributor guide
Research direction
Locate revisions cd067180a8b1, c7f2a8e31b04, and a3b4c5d6e7f8, then inspect their foreign-key creation and downgrade drop_constraint calls. Compare a mgr schema oneshot database with one built by replaying migrations, including PostgreSQL's identifier truncation behavior. Reconcile the names with an existence-checked migration, verify all three downgrades locally, and run pants fmt, lint, and check.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, python
- Domain
- backend, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 48/100