MemberJunction / MemberJunction/MJ
CodeGen: cascade-delete regeneration is blind to cross-entity drift when a migration pre-cleans EntityField metadata
- Dominant language
- TSQL
- Stars
- 29
- Forks
- 6
- Avg merge
- 2d 1h
- Merged PRs (30d)
- 323
Description
## Symptom
`spDeleteAIAgentRun` referenced `AIPromptRun.AgentRunID` after #3274 dropped that column, so **every AI Agent Run delete failed** on a v5.50 database:
```
EXEC [__mj].[spDeleteAIAgentRun] @ID='5A4277BF-...'
Error: Invalid column name 'AgentRunID'.
```
Running `mj codegen` afterwards does **not** fix it. CodeGen generates the corrected procedure body to disk and never executes it.
## Cause
`SQLCodeGenBase.markEntitiesForCascadeDeleteRegeneration` (`packages/CodeGenLib/src/Database/sql_codegen.ts:2359`) drives cascade regeneration from `getModifiedEntitiesWithUpdateAPI`, which reads `ManageMetadataBase.modifiedEntityList` — entities whose metadata changed **during that CodeGen run** (`sql_codegen.ts:2304`).
#3274 deletes the `AgentRunID` `EntityField` row inside the migration itself, deliberately, to break the `R__RefreshMetadata` view-recompile cycle:
> Normally `spDeleteUnneededEntityFields` handles EntityField cleanup, but it runs in `R__RefreshMetadata.sql` which recompiles views first — views that still reference these dropped columns.
That workaround is correct. But it means that by the time CodeGen runs there is **no drift left to detect**: `AIPromptRun` never enters `modifiedEntityList`, `getEntitiesRequiringCascadeDeleteRegeneration` is never asked about it, and `AIAgentRun` — whose delete procedure cascades into `AIPromptRun` — is never marked.
Flags were all correct on both entities (`CascadeDeletes=1`, `spDeleteGenerated=1`, `AllowUpdateAPI=1`, `spUpdateGenerated=1`). The trigger simply never fires.
Also note `Post-CodeGen CRUD validation passed (382 entities checked)` reports success — it validates that routines *exist*, not that they compile against the current schema.
## Impact
Any migration using the documented cycle-break workaround silently leaves dependent delete procedures stale, permanently. Two correct behaviours combining into a defect.
## Suggested fix
Mark cascade dependents from the *schema* delta rather than only from same-run metadata drift, or have the post-CodeGen validator compile-check routine bodies against current columns instead of only checking existence.
Fixed for this release by shipping CodeGen's own corrected output as `V202607281712__v5.50.x__Fix_spDeleteAIAgentRun_Stale_AgentRunID_Cascade.sql` (PR #3342).
Contributor guide
Assessment
This issue has not been assessed yet.