MemberJunction / MemberJunction/MJ

CodeGen: cascade-delete regeneration is blind to cross-entity drift when a migration pre-cleans EntityField metadata

Open
#3,343 0 comments 0 reactions 1 assignee Claimed by @jordanfanapour View on GitHub
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

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.