PostgreSQL Schema Casing for Entity Class Names

PostgreSQL Schema Casing for Entity Class Names — Options & Decision

---

## 1. The problem (one paragraph)

MJ derives entity class names (and GraphQL type names) from the schema name. On PostgreSQL,
unquoted DDL identifiers are folded to lowercase (SQL standard behavior; SQL Server, non-standard,
preserves case). So a schema authored `__mj_BizAppsCommon` becomes physically `__mj_bizappscommon`
on PG, and CodeGen emits `mjbizappscommonAddressEntity` instead of the published
`mjBizAppsCommonAddressEntity` → consuming builds fail with **TS2724**. Affects every mixed-case-schema
deployment on PostgreSQL. SQL Server is unaffected.

### 1a. Why this is a CROSS-PACKAGE contract break (the actual failure mechanism)

The entity class name is not a local, cosmetic string — it is the **linking symbol across a package
boundary**, and it is computed **independently in two places that must agree**:

- **The PRODUCER package** — e.g. `@mj-biz-apps/common-entities` (published to npm at 5.31.2). Its
generated `entity_subclasses.ts` **exports** the class names, baked in at the time *it* was generated
(on SQL Server → PascalCase):
```ts
export class mjBizAppsCommonPersonEntity extends BaseEntity<...> { ... }
```
Once published, **these exported names are a frozen contract.**

- **The CONSUMER** — e.g. an app like Izzy. Two kinds of consumer code reference those names:
1. **Hand-written code** that imports them directly:
```ts
import { mjBizAppsCommonPersonEntity } from "@mj-biz-apps/common-entities";
const p = await md.GetEntityObject('MJ_BizApps_Common: People', user);
```
2. **The consumer's OWN `mj codegen` output**, which generates references to the producer's entity
types **by name** — and computes that name from the **consumer's local `vwEntities.ClassName`**,
i.e. from the consumer's *own database's* schema name.

**The break:** the producer computed the name once (PascalCase, when published); the consumer recomputes
it at build time from its local DB.
- On **SQL Server**, both sides compute `mjBizAppsCommonPersonEntity` (schema mixed-case in both DBs) →
**names match** → imports resolve → build passes.
- On **PostgreSQL**, the consumer's DB has the folded schema `__mj_bizappscommon`, so the consumer's
CodeGen computes **`mjbizappscommonPersonEntity`** (lowercase) and emits
`import { mjbizappscommonPersonEntity } from "@mj-biz-apps/common-entities"` — but the package only
**exports** the PascalCase `mjBizAppsCommonPersonEntity`. The imported symbol **does not exist in the
package's exports** → **TS2724**.

This is why the bug is invisible in a self-contained SS build and only appears across the package
boundary on PG: the producer's published export casing and the consumer's PG-recomputed import casing
disagree, and the linker cannot connect them.

**Why this framing decides the options:** the fix must make the two sides *agree on a casing*. Both
options below do this by making the consumer's PG CodeGen recompute **PascalCase** (via the recovered
canonical name), so it matches the **already-published producer exports** → the consumer converges to the
existing contract, with **no republish and no consumer edits**. (The opposite approach — forcing both
sides to lowercase — would require republishing every producer package with lowercase exports as a
breaking change *and* rewriting every consumer's hand-written imports, e.g. Izzy's ~400
`mjBizAppsCommon…Entity` references. That breaks the published contract ecosystem-wide and is out of
scope for this plan.)

## 2. Proven constraints (verified, not assumed — do not relitigate these)

These were each confirmed empirically (live PG, code trace, or both). Every option must live within them.

1. **PG destroys the casing at `CREATE SCHEMA` time.** `CREATE SCHEMA __mj_BizAppsCommon` (unquoted)
is stored by PG as `__mj_bizappscommon`. Verified on a live PG 17 container.

2. **PG folds identifier *names* but preserves column *data*.** The same string `__mj_BizAppsCommon`
stored as a schema name comes back lowercase; stored as a column value comes back intact. Verified
live. → *Any* solution that "keeps the canonical name in the DB" must keep it as **column data**, not
as a schema identifier.

3. **You cannot recompute canonical from physical.** `__mj_bizappscommon` → `__mj_BizAppsCommon`
requires inventing where the capital letters and word breaks were. No function can do this reliably;
a wrong guess = a broken build. (The one exception: the core `__mj` → `MJ` mapping is a hardcoded
special case in `getSchemaPrefix`, viable only because it's a single known constant.)

4. **MJ deliberately folds to lowercase at every persistence point.** This is by design, not oversight:
`migration-runner.ts` runs the schema through `GetDialect(platform).CanonicalSchemaName(name)` (→
lowercase on PG) before using it as the flyway `defaultSchema`, so the history table, the
`${flyway:defaultSchema}` placeholder substitution, and the physical DDL all land in the *same*
lowercase physical schema. This avoids the "quote-everything-forever" trap (a quoted mixed-case
schema requires every reference everywhere to be quoted, or it folds and mismatches — an unbounded,
silent failure mode). The lowercase-canonical convention is a sound, intentional robustness choice.

5. **Consequently, the mixed-case name survives in exactly two places, both OpenApp-specific:**
- the app **manifest** (`mj-app.json` `schema.name`) — a file, off-DB; **CodeGen does not read manifests**.
- the **`OpenApp` table** (`OpenApp.SchemaName`), which the install copies verbatim from the manifest
as column data → PG preserves it. CodeGen *can* read this (it's in the DB).

6. **For NON-OpenApp mixed-case PG schemas, the canonical name survives nowhere.** A hand-built customer
schema, an MJ-instance schema, or an app installed via raw migration (not `mj app install`) has no
manifest and no `OpenApp` row. The original casing existed only in the author's `CREATE SCHEMA`
statement, which PG folded. **There is no artifact to recover it from — it must be *declared*.**

### The irreducible conclusion
The casing the class names need is destroyed by PG and cannot be recomputed. It survives only for
OpenApps (manifest/`OpenApp` row). Therefore *any* fix must (a) **store** the canonical name in column
data, (b) **auto-populate** it for OpenApps from the one place it survives, and (c) provide a path to
**declare** it for non-OpenApp schemas (or accept lowercase identifiers there). The options below differ
only in *where the canonical name is stored* and *how much else has to change as a result*.

---

## 3. The options

Both options recover the canonical name from the same source (`OpenApp`/manifest — the only place it
survives, per §2.5) and produce the same end result (PascalCase class names matching the published
contract). They differ in **which column holds the canonical value** and, consequently, **what else
changes**. Each is described below in the same structure; §6 has a side-by-side table.

### Option A — Store the canonical name in a new `SchemaInfo.CanonicalSchemaName` column

`SchemaName` keeps its current meaning (the physical name as stored in the catalog — lowercase on PG).
A new nullable `CanonicalSchemaName` column holds the canonical value. The class-name / GraphQL-prefix
logic uses `CanonicalSchemaName` when set and `SchemaName` otherwise. OpenApp install and the CodeGen
metadata-sync proc populate the new column from `OpenApp.SchemaName`.

- **Where canonical is stored:** a new column (`SchemaInfo.CanonicalSchemaName`, nullable).
- **`SchemaName` meaning:** unchanged — remains the physical name all existing SQL uses.
- **Code changed:** migration (column + `vwEntities` COALESCE on the two prefix expressions + proc
backfill clause), `EntityInfo` field, one line in the TS prefix helper, an install hook (~5 files).
- **Identifier-derivation sites changed:** 2 (`vwEntities` and `getGraphQLTypeNameBase`). Other code that
reads `SchemaName` is unaffected because its meaning is unchanged.
- **SQL Server:** column is NULL there, so the prefix falls back to `SchemaName` — output unchanged
(verified byte-identical across 420 entities).
- **Non-OpenApp schemas:** column NULL → falls back to the lowercase `SchemaName` → lowercase
identifiers (functional, not PascalCase) unless the value is declared (see §4).
- **Trade-offs:** adds a column to a core metadata table. The backfill clause is added to
`spUpdateSchemaInfoFromDatabase`, which is already maintained in two dialect copies (see §5). Introduces
a second schema-name field, so readers must know which is physical vs. canonical.
- **Status:** implemented as PR #2992; verified end-to-end on PG (see §7).

### Option B — Make `SchemaName` itself the canonical name; the provider folds it to physical

`SchemaInfo.SchemaName` holds the canonical mixed-case value (sourced from `OpenApp`/manifest rather than
the catalog). The provider folds it to the physical lowercase form (`CanonicalSchemaName()`) wherever it
addresses the real database. No new column; class-name logic reads `SchemaName` directly.

- **Where canonical is stored:** the existing `SchemaName` column (re-sourced from `OpenApp` instead of
the catalog).
- **`SchemaName` meaning:** changes from "physical name" to "canonical name."
- **Code changed:** the sourcing of `SchemaName` (catalog → `OpenApp`), plus a fold-to-physical at every
site that uses `SchemaName` to address the DB: ~16 runtime-provider sites (already routed through
`QuoteSchema`) and ~80–340 CodeGen emission sites (including raw `` `${e.SchemaName}.${e.BaseView}` ``
interpolations that bypass `QuoteSchema`), some of which write the schema name into persisted generated
views/sprocs.
- **Identifier-derivation sites changed:** the same 2, plus the fold-down work above.
- **SQL Server:** `SchemaName` is already canonical on SS, so the value is unchanged there; the fold-down
is a no-op on SS but the code paths still route through it.
- **Non-OpenApp schemas:** same as A — without an `OpenApp` row, `SchemaName` would be sourced from the
catalog (lowercase) and identifiers would be lowercase unless declared (see §4).
- **Trade-offs:** no new column, and the model is uniform ("one canonical schema name; the provider
translates to each dialect's rules" — consistent with how the runtime query path already works via
`QuoteSchema`). Cost is concentrated in CodeGen's emission path; because `SchemaName` no longer matches
the physical schema, any site that uses it without folding produces a wrong-cased reference, and some of
those references are baked into persisted objects. Changing `SchemaName`'s meaning also requires
reconciling existing deployments' metadata + generated objects.
- **Status:** not implemented.

---

## 4. Open decision — non-OpenApp mixed-case PG schemas (applies to A and B)

For schemas with no manifest/`OpenApp` row, the canonical name must be **declared** (constraint §2.6).
Two sub-options:

- **4a. Declaration path:** canonical name is a metadata-sync-managed value on `SchemaInfo`
(version-controlled in a `.schema-info.json`), with an optional `mj.config.cjs` fallback for instances
not using metadata-sync. Provides PascalCase identifiers for non-OpenApp/custom schemas; requires a
declaration step and the supporting metadata-sync/config wiring.
- **4b. NULL fallback only:** ship the storage slot; OpenApps auto-fill it; non-OpenApp schemas are left
NULL → lowercase identifiers (functional, not PascalCase) unless an admin sets the value manually.
No declaration UX; non-OpenApp/custom schemas get lowercase identifiers by default.

This decision is **independent** of A-vs-B and can be made later — both A and B can start with 4b and add
4a if/when custom mixed-case PG schemas require PascalCase identifiers.

## 5. Pre-existing context: metadata-support proc duplication

The metadata-support routines (incl. `spUpdateSchemaInfoFromDatabase`) are **already** maintained twice —
SS baseline (T-SQL) + PG `metadataSupportObjects.ts` (regenerated each codegen) — for ~9 procs. This
duplication **pre-dates this work**. Option A's backfill adds one clause to one of those procs (kept in
sync); Option B would touch the same procs as part of its larger emission-path changes. Two independent
cleanups exist regardless of which option is chosen, and **neither blocks this fix**:
- move Option A's backfill into CodeGen's dialect-agnostic TS metadata-sync (`manage-metadata.ts`) so the
clause lives in exactly one place;
- single-source all ~9 procs from one definition (a CodeGenLib cleanup benefiting the whole codebase).

## 6. Options at a glance (for discussion — no recommendation)

Both options store the same canonical value (recovered from `OpenApp`/manifest) and produce the same end
result (PascalCase class names matching the published contract). They differ in **where** that value lives
and **how much else must change** as a result.

| | Option A — new `CanonicalSchemaName` column | Option B — `SchemaName` becomes canonical |
|---|---|---|
| Where canonical is stored | new nullable column | existing `SchemaName` column |
| `SchemaName` meaning | unchanged (physical) | changes to canonical |
| New column on a core table | yes | no |
| Sites changed | 2 identifier-derivation sites | the same 2, plus fold-to-physical at ~16 runtime + ~80–340 CodeGen emission sites |
| Persisted generated SQL affected | no | yes (schema name baked into some generated views/sprocs) |
| Behavior if a `SchemaName` consumer is missed | n/a (`SchemaName` meaning unchanged) | produces a wrong-cased reference |
| SQL Server output | unchanged (column NULL → fallback; verified, 420 entities) | unchanged (`SchemaName` already canonical on SS) |
| Existing-deployment reconciliation | none beyond the migration | metadata + generated objects must be reconciled to the new `SchemaName` meaning |
| Alignment with "provider owns dialect translation" | partial (provider folds for queries; identifiers read a stored column) | full (single canonical name; provider folds everywhere) |
| Implementation status | implemented (PR #2992), verified end-to-end (§7) | not implemented |

Whatever is chosen, the §4 (non-OpenApp authoring) and §5 (proc single-sourcing) items are separable
follow-ups.

## 7. Verification already performed (for Option A / PR #2992)

- **SS net-zero:** full combined migration applied to a real SS DB → `vwEntities`
ClassName/CodeName/BaseTableCodeName byte-identical across all 420 entities.
- **PG end-to-end:** virgin DB via real `mj migrate` → real `mj app install bizapps-common` → real
`mj codegen` → emitted `entity_subclasses.ts` contained `export class mjBizAppsCommonAddressEntity`
(PascalCase), zero lowercase classes. Backfill populated `SchemaInfo.CanonicalSchemaName` from the
`OpenApp` row; `vwEntities.ClassName` came out PascalCase.
- **Builds + tests:** MJCoreEntities/MJCore/MJServer compile; regression tests for
`getGraphQLTypeNameBase` pass.

## 8. Key references
- PR: #2992 (Option A, implemented)
- Guide: [`guides/POSTGRES_SCHEMA_CASING_GUIDE.md`](../guides/POSTGRES_SCHEMA_CASING_GUIDE.md)
- Canonicalization: `packages/SQLDialect/src/postgresqlDialect.ts` (`CanonicalSchemaName`),
`packages/OpenApp/Engine/src/install/migration-runner.ts:235`
- Prefix logic: `packages/MJCore/src/generic/graphqlTypeNames.ts`,
`vwEntities` in the v5.38 baseline
- Canonical name survives: `OpenApp.SchemaName` (set in
`packages/OpenApp/Engine/src/install/history-recorder.ts:96`)

Start by reviewing PR #2992 and the described entry points: migration-runner.ts, SchemaInfo, EntityInfo, vwEntities, spUpdateSchemaInfoFromDatabase, and getGraphQLTypeNameBase. Compare Option A with Option B and resolve the open non-OpenApp declaration choice. Done means PostgreSQL consumers converge on the published entity names without breaking existing SQL Server behavior.

Written by the indexing model from the issue text.