Anchor the polymorphic entity pairs to the graph with composite foreign keys
- Dominant language
- Python
- Stars
- 670
- Forks
- 183
- Avg merge
- 15h 13m
- Merged PRs (30d)
- 368
Description
virtual_entities holds (entity_type, entity_id) unique, so a table naming an entity by that pair can reference it with a composite foreign key. Postgres supports these with ON DELETE CASCADE, and MATCH FULL holds a nullable pair to both-or-neither. BA-7568 applied this to entity_shares for both its target and its recipient; this issue applies it to the rest.
Target: entity_labels applies directly, its columns already match. The graph teardown deletes those rows by hand today (repositories/ops/v2/graph_write.py) and that deletion is replaced by the cascade. idle_checker_bindings needs scope_type narrowed from String(64) to String(32) first. permissions needs scope_id changed from String(64) to a UUID column; the permission resolution query casts entity_id to string to compare (repositories/ops/v2/permission/read.py) and that cast goes away with it. app_config_fragments carries its own scope enum, so decide first whether its scope vocabulary is the graph's.
Out of scope, deliberately: audit_logs and audit_log_scopes keep no foreign key. A cascade would erase the record when an entity is purged, and a foreign key without one would block the purge. Records outlive what they describe. object_permissions is deprecated and scheduled for removal, and association_scopes_entities is the legacy mapping BEP-1077 removes.
Done when: each table above either carries the composite foreign key or has a recorded reason not to, orphan rows can no longer outlive the entity they name, and the manual entity_labels cleanup is gone.
Open decision: whether app_config_fragments scope types are graph scopes at all.
JIRA Issue: BA-7696
Contributor guide
Research direction
Start with the entity_labels cleanup in repositories/ops/v2/graph_write.py and the permission resolution query in repositories/ops/v2/permission/read.py, then inspect the schema definitions for entity_labels, idle_checker_bindings, permissions, and app_config_fragments. Resolve whether app_config_fragments uses graph scopes, and verify that each applicable table has the composite foreign key, orphan rows cannot remain, and manual entity_labels deletion is removed.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, python
- Domain
- backend, databases
- Issue type
- Refactor
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100