MemberJunction / MemberJunction/MJ

CodeGen SQL output log missing EntityField INSERTs from manageEntityFields

Open
#3,158 0 comments 0 reactions 0 assignees View on GitHub
bug
Dominant language
TSQL
Stars
29
Forks
6
Avg merge
1d 8h
Merged PRs (30d)
308

Description

## Summary

When CodeGen regenerates views for geo-enabled entities, the SQL output log file (`CodeGen_Run_*.sql`) is missing the `INSERT`/`UPDATE` statements for virtual `EntityField` records created by `manageEntityFields`. This makes the output log insufficient as a self-contained migration — applying it to another database results in runtime errors because the metadata is incomplete.

## Reproduction

1. Enable geocoding on an entity (`SupportsGeoCoding = 1`, tag address fields with `Geo*` ExtendedTypes)
2. Run CodeGen — it regenerates the base view with the RecordGeoCode LEFT JOIN and creates virtual `__mj_Latitude`/`__mj_Longitude` EntityField records
3. Take the generated `CodeGen_Run_*.sql` output and apply it as a migration to a fresh database
4. The base view is created correctly, but `__mj_Latitude`/`__mj_Longitude` EntityField records are missing
5. At runtime, loading the entity fails with: `Field __mj_Latitude does not exist on [Entity]`

## Root Cause

In `sql_codegen.ts`, the late-phase view regeneration flow (line ~387) calls:

```typescript
await manageMD.manageEntityFields(pool, configInfo.excludeSchemas, true, true, currentUser, true, false, lateRegenFilter);
```

`manageEntityFields` operates via MJ entity objects (Save/Update), which write directly to the database but do **not** emit SQL to `SQLLogging`. So the EntityField INSERT/UPDATE operations are never captured in the output log.

In contrast, the `applyLatePhaseFixups` method (line ~460) correctly logs its ExtendedType UPDATE statements via `SQLLogging.appendToSQLLogFile()`.

## What's Captured vs Missing

| Operation | In SQL Output Log? |
|---|---|
| View DDL (CREATE VIEW with geo LEFT JOIN) | Yes |
| Stored procedure DDL (spCreate/spUpdate/spDelete) | Yes |
| Permissions (GRANT) | Yes |
| ExtendedType fixup (GeoLatitude/GeoLongitude) | Yes |
| **EntityField INSERT for virtual columns** | **No** |

## Expected Behavior

The SQL output log should include INSERT/UPDATE statements for any EntityField records created or modified by `manageEntityFields` during the late-phase regeneration, so the log is a complete, self-contained migration.

## Affected Files

- `packages/CodeGenLib/src/Database/sql_codegen.ts` (line ~387-391)
- `packages/CodeGenLib/src/Database/manage-metadata.ts` (`manageEntityFields`)
- `packages/CodeGenLib/src/Misc/sql_logging.ts`

## Workaround

Manually add EntityField INSERT statements to the migration for virtual geo fields (`__mj_Latitude`, `__mj_Longitude`), matching the schema CodeGen would have created.

Contributor guide

Open the contributing guide

Research direction

Start in packages/CodeGenLib/src/Database/sql_codegen.ts around the late-phase manageEntityFields call, then read manage-metadata.ts and Misc/sql_logging.ts to trace how database writes reach the log. Reproduce the geo-enabled CodeGen flow and apply its CodeGen_Run_*.sql output to a fresh database. Done means the virtual EntityField INSERT/UPDATE statements are present and the migration loads the entity without missing-field errors.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql, typescript
Domain
backend, database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
68/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.