MemberJunction / MemberJunction/MJ
A captured metadata-sync migration bakes __mj.Entity ids, so Entity-referencing rows cannot port to a host that registered its schema first
- Dominant language
- TSQL
- Stars
- 29
- Forks
- 6
- Avg merge
- 2d 1h
- Merged PRs (30d)
- 323
Description
## Summary
Metadata that references an MJ **Entity by ID** — `EntityPermission` is the common case — cannot be shipped as a captured `mj sync push` migration, because the capture bakes the entity ids of the machine it was generated on. On a host whose `__mj.Entity` ids differ, the insert fails with an FK violation.
## Measured
Applying `bizapps-caliber`'s generated metadata-sync migration to a database that had registered Caliber's schema **before** the CodeGen baseline shipped:
```
Msg 547, Level 16, Procedure __mj.spCreateEntityPermission, Line 26
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_EntityPermission_Entity".
```
The two databases disagree about every Caliber entity id:
| entity | fresh install | pre-baseline database |
|---|---|---|
| `: Applicant Sources` | `09ebe431-…` | `4c1cf072-…` |
| `: Applicants` | `f886956c-…` | `0d4b11e9-…` |
Both are legitimate states. The fresh host gets its ids from the app's shipped CodeGen baseline; the older database minted its own when CodeGen first ran there, and the baseline's `IF NOT EXISTS (ID OR Name)` guard correctly declined to re-insert them. This is the same root as bizapps-caliber#142.
## Why the usual answer doesn't apply
In `metadata/` the reference is authored portably, as a lookup:
```json
"EntityID": "@lookup:MJ: Entities.Name=Caliber: Assessments"
```
That resolves at **push** time, so the portability is lost precisely when the push is captured for shipping. The migration ends up with a literal id and no way to re-resolve.
## Suggested direction
Let the SQL logger emit a name-resolving form for references it knows are entity lookups — a scalar subquery is enough and stays deterministic:
```sql
DECLARE @EntityID UNIQUEIDENTIFIER =
(SELECT ID FROM [${mjSchema}].[Entity] WHERE Name = N'Caliber: Assessments');
```
Anything that preserves the lookup rather than collapsing it would do. Without it, `EntityPermission`, `EntityRelationship`, `ApplicationEntity` and friends are effectively un-shippable for any Open App whose customers include an existing MJ install.
## Workaround today
Ship them and accept that they fail on pre-baseline databases (the rest of the migration still applies), or exclude those entities from the capture and document that permissions must be pushed by an operator.
Found while making `bizapps-caliber`'s metadata reach hosts (bizapps-caliber#151, R43). Related: #3618, and the `${commonSchema}` gap in #3443.
Contributor guide
Research direction
Start in metadata/ and the SQL logger that turns portable entity lookups into captured migrations. Reproduce the push against databases with different __mj.Entity IDs, then preserve the name-based lookup in the generated SQL. Done means EntityPermission and related references can apply without foreign-key failures on pre-baseline hosts.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- database
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 48/100