MemberJunction / MemberJunction/MJ

Add safe stale EntityRelationship cleanup to CodeGen

Open
#2,071 0 comments 0 reactions 0 assignees View on GitHub
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.