MemberJunction / MemberJunction/MJ
Add safe stale EntityRelationship cleanup to CodeGen
- Dominant language
- TSQL
- Stars
- 29
- Forks
- 6
- Avg merge
- 2d 1h
- Merged PRs (30d)
- 323
Description
## Context
Commit `e90e956533` (March 2, 2026) added a "stale EntityRelationship cleanup" step (3.5) to CodeGen that automatically deletes EntityRelationship records it considers stale. This was motivated by stale relationships in AIDP causing invalid Entity Views in generated Entity Forms.
The implementation was too aggressive and caused a regression where valid EntityRelationships (including `__mj` core schema relationships and soft-FK-based relationships from `additionalSchemaInfo`) were incorrectly deleted. The stale cleanup was reverted in `497b151802`.
## Original Problem
CodeGen was only adding relationships and never removing them. Over time, schema changes (dropped columns, renamed FKs) accumulated stale EntityRelationship records that caused issues with generated Entity Views/Forms using invalid filters.
## Bugs in the Reverted Implementation
The `removeStaleOneToManyRelationships()` method had three bugs:
1. **Excluded-schema relationships not protected**: The cleanup queried entity fields with a `NOT IN (excluded schemas)` filter, but queried ALL EntityRelationships without the same filter. Relationships belonging to excluded-schema entities (e.g., `__mj`) had no matching "valid" entries and were incorrectly deleted.
2. **UUID case mismatch**: The valid relationship keys Set used raw UUID string concatenation. SQL Server returns uppercase UUIDs, and any case inconsistency between entity field records and relationship records caused `Set.has()` to fail silently, marking valid relationships as stale. `NormalizeUUID()` was not used.
3. **Missing whitespace trimming**: The commit message itself mentions `nchar(20)` trailing spaces and added `.trim()` to the update helper, but the stale detection did NOT trim `Name`/`RelatedEntityJoinField` when building or checking the Set keys.
## Requirements for a Future Implementation
If this feature is re-implemented, it should:
- [ ] Filter relationships by excluded schemas on BOTH the entity fields query AND the relationships query
- [ ] Use `NormalizeUUID()` (or `UUIDsEqual()`) for all UUID comparisons in the stale detection logic
- [ ] Trim field names to handle `nchar` padding from SQL Server
- [ ] Respect soft foreign keys from `additionalSchemaInfo` (soft-keys.json) — these create EntityRelationships that must not be pruned
- [ ] Consider whether this belongs in CodeGen at all vs. being a manual migration/cleanup tool
- [ ] Consider a "dry run" mode that logs what would be deleted without actually deleting
- [ ] Add unit tests covering all edge cases (excluded schemas, soft FKs, UUID casing, nchar padding)
## Files Previously Modified
- `packages/CodeGenLib/src/Database/manage-metadata.ts` — Added `cleanupStaleEntityRelationships()`, `removeStaleOneToManyRelationships()`, `buildInsertRelationshipSQL()`, `buildUpdateRelationshipJoinFieldSQL()`
- `packages/CodeGenLib/src/Database/sql_codegen.ts` — Added step 3.5 call
- `packages/CodeGenLib/src/runCodeGen.ts` — Added extra `provider.Refresh()` for post-cleanup metadata sync
Contributor guide
Assessment
This issue has not been assessed yet.