MemberJunction / MemberJunction/MJ
CodeGen: conflicting soft FK declarations resolve to last-write-wins with no validation
- 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
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