MemberJunction / MemberJunction/MJ

CodeGen: conflicting soft FK declarations resolve to last-write-wins with no validation

Open
#4,532 0 comments 0 reactions 0 assignees View on GitHub
bug
Dominant language
TSQL
Stars
29
Forks
6
Avg merge
2d 1h
Merged PRs (30d)
323

Description

## Summary

When `additionalSchemaInfo.json` declares more than one target for the same field, CodeGen applies each as a separate `UPDATE` in file order and the **last one silently wins**. There is no conflict detection, no check that the target column exists, and no check that it is unique.

## Cause

`packages/CodeGenLib/src/Database/manage-metadata.ts:4553-4602` (`applySoftPKFKConfig`) issues per entry:

```sql
UPDATE EntityField
SET RelatedEntityID = ..., RelatedEntityFieldName = '', IsSoftForeignKey = 1
WHERE EntityID = ... AND Name = ''
```

Missing validation:

1. **Conflicting entries for one field** are accepted; the last write wins.
2. **Target column existence** is never checked. A declaration pointing at `"ID"` where the column is `id` is accepted silently, and on PostgreSQL those are different identifiers.
3. **Target uniqueness** is never checked, so a field can be pointed at a non-unique column, which multiplies rows through the generated base view.

## Impact

On a production deployment, a generator bug produced 9 declarations for 3 columns (companion issue). CodeGen resolved all 3 to the same wrong column, which emptied two base views completely. A single `RAISE`/error on conflicting targets would have turned a silent data-loss bug into a build failure.

Separately, 10 FK declarations naming a non-existent `"ID"` column were accepted and written into metadata, where they are shown to consumers as valid join targets.

## Suggested fix

In `applySoftPKFKConfig`:

- Group entries by `(EntityID, FieldName)` and **fail** when a field has more than one distinct target, naming the file entries involved.
- Verify the target column exists on the target entity (case-sensitively on PostgreSQL) before writing; log and skip otherwise.
- Verify the target is a primary key or single-column unique key; warn when it is not, since a label join on a non-unique column multiplies rows.

Engine-independent: `manage-metadata.ts` is shared.

Contributor guide

Open the contributing guide

Research direction

Start in packages/CodeGenLib/src/Database/manage-metadata.ts, especially applySoftPKFKConfig at lines 4553-4602, and trace how additionalSchemaInfo.json entries are converted into EntityField updates. Done means conflicting targets fail, missing target columns are logged and skipped, and non-unique targets produce warnings before metadata is written; verify the behavior with the CodeGen metadata workflow.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, sql, typescript
Domain
databases, tooling
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
58/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.