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

Open
#3,619 2 comments 0 reactions 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.