MemberJunction / MemberJunction/MJ
mj sync push --formatAsMigration produces an unusable migration for any Open App
- Dominant language
- TSQL
- Stars
- 29
- Forks
- 6
- Avg merge
- 2d 1h
- Merged PRs (30d)
- 323
Description
## Summary
`sqlLogging.formatAsMigration` rewrites the **core** schema to `${flyway:defaultSchema}`. In MJ's own repo that is correct, because `${flyway:defaultSchema}` *is* `__mj`. In an **Open App** repo it is not: Skyway binds `${flyway:defaultSchema}` to the *app* schema and `${mjSchema}` to core, so the emitted migration sends every core CRUD call to the app schema and fails on a host.
## Where
`GenericDatabaseProvider.CreateSqlLogger` passes the core schema as the only placeholder source:
```ts
const mjCoreSchema = this.ConfigData.MJCoreSchemaName;
const session = new SqlLoggingSessionImpl(sessionId, filePath, {
defaultSchemaName: mjCoreSchema, ...options
}, ...);
```
`SqlLogger` then rewrites that one schema to `${flyway:defaultSchema}`.
Meanwhile `packages/OpenApp/Engine/src/install/migration-runner.ts` builds the placeholder map as:
```ts
Placeholders: {
'flyway:defaultSchema': canonicalSchema, // the APP schema
mjSchema: mjCoreSchema ?? '__mj',
...
}
```
So a captured `EXEC [__mj].spCreateAIPrompt` becomes `EXEC [${flyway:defaultSchema}].spCreateAIPrompt`, which resolves on a host to `EXEC [__mj_BizAppsSomething].spCreateAIPrompt` — a procedure that does not exist.
## How it shows up today
`bizapps-common` shipped `V202605141122__v5.29.x__Metadata_Sync.sql` with `formatAsMigration` **off** and literal `[__mj]` / `[__mj_BizAppsCommon]` schema names, and its header says so. That works only while every host uses the default core schema name, and it is exactly what an app repo's own migration rules tend to forbid.
## Suggested fix
Let the caller supply the rules instead of inferring one. CodeGen already has this shape in `SQLOutput.schemaPlaceholders`:
```js
schemaPlaceholders: [
{ schema: '__mj_BizAppsCaliber', placeholder: '${flyway:defaultSchema}' },
{ schema: '__mj', placeholder: '${mjSchema}' },
]
```
Accepting the same array in `CreateSqlLogger` options (falling back to today's single-schema behaviour when absent) would make `formatAsMigration` usable by every Open App with no change to MJ's own usage. Note the ordering hazard CodeGen's config already documents: the more specific app schema must precede the generic `__mj` rule, or the greedy rule matches the `__mj` prefix of `__mj_BizAppsCaliber`.
## Related, and worth fixing alongside
With `formatAsMigration` off, `_escapeFlywaySyntaxInStrings` is also skipped — so any `${...}` inside captured template/prompt content reaches Skyway as an undeclared placeholder. Apps working around the schema bug therefore silently lose that protection too.
Found while making `bizapps-caliber`'s metadata ship to hosts (bizapps-caliber#151, R43). `bizapps-ats` will hit this identically.
Contributor guide
Research direction
Start at GenericDatabaseProvider.CreateSqlLogger and follow the schema rewrite in SqlLogger, then compare it with packages/OpenApp/Engine/src/install/migration-runner.ts and CodeGen's SQLOutput.schemaPlaceholders rules. Verify that formatAsMigration can preserve distinct app and core schema placeholders, and that captured template or prompt content still receives Flyway escaping when migration formatting is enabled.
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
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 55/100