Make pre-constraint RBAC backfill upgrades skip existing rows
- Dominant language
- Python
- Stars
- 670
- Forks
- 183
- Avg merge
- 15h 13m
- Merged PRs (30d)
- 368
Description
## Objective
Make the six RBAC backfill migrations that run before the permissions unique constraint exists skip rows that are already present, so their upgrade is idempotent on its own rather than relying on a downgrade to clear the table first.
## Background
These migrations insert with a bare ON CONFLICT DO NOTHING. At their point in the chain the permissions table (created in 9adcd6f48ba1) carries only PrimaryKeyConstraint(id), and id defaults to uuid_generate_v4(). The only conflict target is a freshly generated UUID, so ON CONFLICT never fires and re-running the upgrade duplicates every row. Verified on a scratch DB by replaying the table definition: the same INSERT run twice yields 2 rows, while the same INSERT against a table carrying the unique constraint yields 1.
This was masked because the downgrades used to delete the rows. Once the downgrades become no-ops (BA-6896), a downgrade/upgrade cycle accumulates duplicates until the chain reaches 3f5c20f7bb07 / 7369d1eb7d4a, which dedupe before creating the constraints.
Affected: artifact, artifact_registry, app_config, model_deployment, notification, vfolder. Only the permissions table needs a guard (plus permission_groups and object_permissions for vfolder); association_scopes_entities already carries uq_scope_id_entity_id at these revisions, so its bare ON CONFLICT works.
## Acceptance Criteria
- The six upgrades use a WHERE NOT EXISTS guard so re-running them inserts no duplicate rows.
- Idempotency is verified against a local DB, not only by static reading.
- The nine migrations that already carry an effective constraint are left unchanged.
- pants fmt / lint / check pass.
JIRA Issue: BA-6897
Contributor guide
Research direction
Locate the six pre-constraint RBAC backfill migrations for artifact, artifact_registry, app_config, model_deployment, notification, and vfolder, and review the later migrations 3f5c20f7bb07 and 7369d1eb7d4a for the constraint and deduplication context. Replay each upgrade against a local database to verify no duplicate rows are inserted, leave the nine already-constrained migrations unchanged, then run pants fmt, lint, and check.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 58/100