# GB5 Platform — Architecture & Integration Reference
**Scope:** central database & connection routing, client/user provisioning, physical database creation, SqlWorkbench schema governance, metadata/data replication, deployment models (on-prem / BYOC / SaaS), and billing/payment integration.
**Audience:** two levels on purpose — Part 1 is a fast mental model for architects/leads; Part 2 is exhaustive, file-cited detail for the engineering/ops team who will actually operate these systems.
**Method:** every claim below was re-verified directly against the current `dev` branch checkout (code reads, migration reads, and in one case a live `dotnet test` run) on 2026-07-25 — not carried over from memory of earlier sessions. Where something is a schema column with no behavior behind it, or a workflow with a real gap, this document says so explicitly rather than rounding up.
**Companion documents:** `docs/Entitlement-Technical-Reference.md` (Entitlement's own subscription/plan/feature-flag model in depth), `docs/Live-DB-Testing-Via-SSH-Tunnel.md` (dev-sandbox connectivity mechanics), and the master engagement tracker (day-to-day status — this document doesn't duplicate it).
---
# Part 1 — Overview
## 1.1 The one-paragraph mental model
GB5 is a **single shared "control plane" database (`gb5system`)** plus **N tenant business databases** — one client's `MCLIENT`/`MUSER`/`TMM*`/etc. tables live in their own physical database (SQL Server or PostgreSQL), while `gb5system` holds only routing/config/session data (`MSERVER`, `MSERVERCONFIG`, `MCLIENTDETAILS`, `MSESSIONSTORE`, `MAUTONUMBER`) plus a handful of genuinely platform-wide modules that intentionally live centrally (Entitlement, Partner, PAY — all scoped by `TenantId`/`ClientId`, not by having their own database). Every request carries a `LoginDTO.DatabaseName`, which `IQueryExecutor` resolves — via `ApplicationConnection` querying `gb5system`'s `MSERVERCONFIG`⋈`MSERVER` — into a real ADO.NET connection string. This one mechanism is what makes the whole platform multi-tenant: one shared codebase, N physical databases, selected purely by which `LoginDTO` a request carries.
Three largely-independent subsystems build on top of that routing layer:
- **SqlWorkbench** governs *schema* — how a brand-new client database gets created, and how its DDL gets updated afterward, all under a Draft→Review→Approve audit trail.
- **DataSync** (`DataSyncRunBLL`/`SyncEngine`) governs *data* — how reference/master data replicates from a central source to N tenant databases, both once at provisioning and repeatedly afterward.
- **Entitlement** governs *commercial state* — what plan a client is on, what features are on/off, and (separately, today) whether they've paid.
## 1.2 What's real vs. what's a placeholder — the single most important thing this document establishes
A recurring pattern surfaced across every research thread: a schema column or table exists, is well-named, and *looks* like a finished feature — but has zero code reading or branching on it. Treating a placeholder as a working feature is the most expensive mistake an ops team can make here, so this table is the load-bearing summary of the whole document:
| Capability | Reality |
|---|---|
| Multi-tenant connection routing | **Real, live, foundational.** Every request depends on it. |
| Client + first-admin-user creation | **Real, code-complete, unit-tested.** Not wired into any onboarding wizard yet. |
| Physical DB creation (snapshot or scripts) | **Real, code-complete, unit-tested.** Never run against a live server end-to-end. SQL Server only. |
| Per-database contained-user security | **Real, code-complete.** Same caveat — unverified live. |
| SqlWorkbench DDL governance (Draft→Review→Approve) | **Real, live-tested (26/26 passing), RBAC-hardened.** SQL Server only. |
| MetadataSync | **Real, but only a hand-entered-changes bridge — not a schema-diff engine.** Don't expect drift detection. |
| DataSync replication engine | **Real, functionally complete for SQL Server↔Postgres.** Scheduler exists but nothing is actually scheduled — every real job today is manually triggered. |
| `MCLIENT.DeploymentType` (On-Prem/Cloud/Hybrid) | **Placeholder.** Written once at creation, never read anywhere. |
| "Hosting: On-premises" shown in the client portal | **Real, but a magic-date sentinel**, not connected to `DeploymentType` at all. |
| BYOC (bring-your-own-cloud) | **Does not exist.** One enum value on an unused column. No provisioning path, no connection-handling branch, no licensing logic. |
| Demo/Trial database | **Does not exist.** No code, only forward-looking schema comments. |
| Entitlement ↔ Payment (PAY) integration | **Does not exist.** Zero FK, zero shared table, zero cross-module call in either direction. |
## 1.3 Deployment models, honestly
"On-prem / BYOC / SaaS" is best understood today as **one real mechanism (multi-tenant SaaS, described in §2.1-2.4) plus two labels with no mechanism behind them yet**:
- **SaaS (multi-tenant)** — real. This is what the entire connection-routing/provisioning/DataSync stack described in this document actually implements.
- **On-premises** — partially real, but only as a *display convention*, not a deployment mechanism: a subscription's `HostingValidTill` date can be set to a sentinel value that the client portal renders as "On-premises" instead of a date. Nothing about how the database is actually hosted, connected to, or updated differs for such a client — it still lives in the same `MSERVERCONFIG`/`MSERVER` routing model as every other tenant.
- **BYOC** — a single enum value (`MPROVISIONINGJOB.DeploymentType = 2`) that is never written or read by any code. There is no design, let alone implementation, for a client whose database lives on infrastructure GB5 doesn't manage.
If the business needs real BYOC or a differentiated on-prem operating model, that is **net-new design work**, not a flag to flip. See §6 for exactly what precedent exists to build from (TCMS's dual-dialect DB creation, the existing multi-server ReportDb/ArchiveDb routing) and what doesn't.
## 1.4 Billing, honestly
Entitlement (subscription/plan/feature lifecycle) and PAY (payment gateway/checkout/webhooks/loyalty/cashback) are both real, working, shared-platform modules — and **have never been connected**. Today, "is this client's subscription active" is decided entirely by a human (or an API caller) explicitly setting a status field in Entitlement; PAY's order pipeline has no concept of "this order is paying for a subscription" at all. This is flagged elsewhere in the program tracker as an unresolved P0 architectural decision, and this document's research confirms precisely how large that gap is: not "mostly wired, needs a config flag" — genuinely zero integration code in either direction. See §7.4 for the exact shape of the decision that needs making.
## 1.5 How the pieces fit together
```mermaid
flowchart TB
subgraph Central["gb5system (central control-plane DB)"]
MSERVER["MSERVER / MSERVERCONFIG (routing registry)"]
MCLIENTDET["MCLIENTDETAILS"]
MAUTONUM["MAUTONUMBER"]
ENT["Entitlement tables (plans, subscriptions, flags)"]
PAY["PAY tables (orders, gateways, loyalty)"]
PARTNER["Partner tables (TPARTNER, TCLIENTDOMAIN)"]
end
subgraph Tenant1["Tenant DB — Client A"]
MCLIENT1["MCLIENT / MUSER / MROLE"]
BIZ1["Business tables (TMM*, TFIN*, ...)"]
end
subgraph Tenant2["Tenant DB — Client B"]
MCLIENT2["MCLIENT / MUSER / MROLE"]
BIZ2["Business tables"]
end
REQ["Incoming request (LoginDTO.DatabaseName)"] --> QE["IQueryExecutor"]
QE --> AC["ApplicationConnection .DatabaseConnectionObjectConnectionName()"]
AC -- "query MSERVERCONFIG ⋈ MSERVER" --> MSERVER
AC -- "resolved connection string" --> Tenant1
AC -- "resolved connection string" --> Tenant2
SWB["SqlWorkbench (schema governance)"] -- "creates / updates" --> Tenant1
SWB -- "creates / updates" --> Tenant2
SWB -- "registers new DB" --> MSERVER
DS["DataSync engine (DataSyncRunBLL)"] -- "replicates reference data" --> Tenant1
DS -- "replicates reference data" --> Tenant2
Central -- "source dataset" --> DS
```
---
# Part 2 — Operational Detail
## 2. Central Database & Connection-Resolution Architecture
### 2.1 The `gb5system` database
One central control-plane database (name confirmed live: lowercase `gb5system`). It is **not** a tenant business database — every tenant's actual business data lives in its own separate physical database. `gb5system` holds:
| Table | Purpose | Key columns |
|---|---|---|
| `MSERVER` | Physical DB server registry | `SERVERID` (PK), `SERVERNAME`, `SERVERIP`, `SERVERMACHINENAME`, `UNIQUEDETAILS` |
| `MSERVERCONFIG` | Logical connection registry — the mapping every `LoginDTO.DatabaseName` resolves through | `SERVERCONFIGID` (PK), `CONNECTIONNAME`, `SERVERID` (FK), `DATABASETYPE`, `DATABASENAME`, `DATABASEUSERNAME`, `DATABASEPASSWORD` (AES-256-GCM encrypted), `DATABASEPORT`, `SYSTEMSERVERCONFIGID`, `REFERNCESERVERCONFIGID` (sic, legacy typo preserved), `ARCHIVESERVERCONFIGID`, `REPORTSERVERCONFIGID` (default `-1`), `CLIENTID`, `CLIENTSITEID`, `SOURCETYPE`, audit columns |
| `MCLIENTDETAILS` | Central client registry | `PARTNERPRODUCTID` (FK→`TPARTNERPRODUCT`, NULL = GoodBooks-direct/unbranded) |
| `MSESSIONSTORE` | One active login session per server-config | `SESSIONID` (PK), `SERVERCONFIGID`, `LOGINEVENTLOGID`, `USERID`, `ISACTIVE` |
| `MAUTONUMBER` | Atomic, cross-module ID-reservation table | `ENTITYID` (PK), `ENTITYCODE`, `AUTOID` — every module's ID minting reads/increments a row here by `ENTITYCODE` |
Also central: `TCLIENTDOMAIN` (custom-domain routing) and `TPARTNER`/`TPARTNERPRODUCT`/`TPARTNERBRAND`/`TPARTNERAPIKEY` (Partner platform) — both live in `gb5system` alongside the routing tables, not in any tenant DB.
`MSERVER`/`MSERVERCONFIG` have no `CREATE TABLE` in this repo's migration history — they're legacy tables carried over from GB4, reverse-engineered here from live `INSERT`/`ALTER` statements and DTOs, not authored fresh.
### 2.2 `ApplicationConnection` — the two resolution paths
File: `GB5Shared/Connection/ApplicationConnection.cs`. (A second, fully commented-out copy exists at `GB5Framework/FrameworkDAL/CustomCode/Connection/ApplicationConnection.cs` — dead code, don't confuse the two in history/blame.)
**Path A — `Gb5SystemConnectionInfo()` / `Gb5SystemConnectionString()`** — purely config-driven, no DB round-trip:
1. Reads `Gb5SystemDTO.DataBaseType` and picks the matching raw connection string (`Gb5System` for SQL Server, `Gb5SystemPG` for Postgres) from `appsettings.json`, with fallback if the configured type's string is blank.
2. Caches by raw string in a static `ConcurrentDictionary`.
3. Regex-extracts `Password=([^;]+)`, decrypts via AES-256-GCM (`Decrypt(encryptedPass, "GB5")` — key = SHA-256("GB5"), 12-byte IV prefix, 16-byte tag suffix, wire format `Base64(IV‖ciphertext‖tag)`), splices the plaintext back in.
4. This is the **seed/bootstrap path into `gb5system` itself** — everything else depends on first getting into `gb5system` via this path.
**Path B — `DatabaseConnectionObjectConnectionName(ConnectionName)`** — resolves an arbitrary logical `CONNECTIONNAME` (any tenant's `LoginDTO.DatabaseName`) to a real connection string, via a live query against `gb5system`:
1. Uses Path A to connect to `gb5system`.
2. Runs `ConnectionQueryBuilder.LOAD_DIFFERENT_DATABASE_NAME_FOR_PRODUCTION_CONNECTIONNNAME`: `FROM MSERVERCONFIG A JOIN MSERVER S ON A.SERVERID = S.SERVERID WHERE A.CONNECTIONNAME = @ConnectionName` (self-joined further for system/referral/archive/parent server configs).
3. Decrypts `DATABASEPASSWORD` (same AES-GCM scheme) and builds the real provider connection string — `Data Source=...;Initial Catalog=...` (SQL Server) or `Host=...;Port=5432;Database=...` (PostgreSQL) — with pool settings from `appsettings.json:ConnectionPool` (`MaxPoolSize` default 100, `ConnectTimeoutSeconds` default 120).
4. Result is cached in `HybridCache`, keyed per `ConnectionName` or per `LoginDTO` (`CacheKeyLevel.DB_SERVER`). A "not found" sentinel is also cached, so a bad `ConnectionName` doesn't hammer `gb5system` every request — but still throws `DataNotFoundException` on every cache hit of that sentinel.
| | Path A | Path B |
|---|---|---|
| Source of truth | `appsettings.json` literal | Live query against `gb5system` |
| Round-trips a DB? | No | Yes |
| Used by | Bootstrap, `ServerConfigCache`, `DomainCacheService`, `ApiKeyAuthMiddleware` | Every `IQueryExecutor` call — i.e. essentially all business-module DAL calls |
**Previously-known inconsistency, now fixed (tracker §25)**: `GB5Shared.Validation.Validation` used to read the *same* `Gb5SystemDTO:Gb5System` config key as a literal, unencrypted string, while `ApplicationConnection.Gb5SystemConnectionInfo()` expected it AES-GCM-encrypted. `Validation` now injects `IApplicationConnection` and calls `Gb5SystemConnectionString()` — the same decrypt path — instead of building its own connection string. Both consumers now agree on the encrypted format. (An earlier draft of `docs/Live-DB-Testing-Via-SSH-Tunnel.md` described this as still-open; that doc has been corrected — don't trust that specific claim if you see it quoted from an older copy.)
### 2.3 `MSERVERCONFIG`/`MSERVER` mapping, concretely
```
LoginDTO.DatabaseName ("EntitlementDb", "GB5DEMO", ...)
│
▼
MSERVERCONFIG.CONNECTIONNAME = @ConnectionName ← WHERE clause
│ (MSERVERCONFIG.DATABASENAME = the real physical DB name — can differ from CONNECTIONNAME)
▼
JOIN MSERVER ON MSERVERCONFIG.SERVERID = MSERVER.SERVERID
│
▼
MSERVER.SERVERIP → "Data Source=" / "Host="
MSERVERCONFIG.DATABASENAME → "Initial Catalog=" / "Database="
MSERVERCONFIG.DATABASEUSERNAME / decrypted DATABASEPASSWORD → credentials
```
`CONNECTIONNAME` and `DATABASENAME` are deliberately separate columns specifically so the physical database name can be renamed/reused per install while the logical name referenced in code stays fixed.
**This mapping is a runtime-only dependency** — invisible to `dotnet build` or even FastEndpoints' DI-graph validation. A real, live-verified example: `EntitlementSL` built and started cleanly with no `MSERVERCONFIG` row for `CONNECTIONNAME='EntitlementDb'`; the failure only surfaced when a real query actually ran (a Quartz job), with the error `"No server configuration found for connection name: EntitlementDb"`. **Operational rule: a clean build/start proves nothing about whether a given `DatabaseName` is actually routable — only a real query does.**
### 2.4 `IQueryExecutor` — fully generic, module-agnostic
File: `GB5Shared/QueryExecutor/QueryExecutor.cs`. Every method (`QuerySingleAsync`, `QueryAsync`, `ExecuteAsync`, `StreamAsync`, `QueryPagedAsync`, `BulkInsertAsync`, `QueryMultiMapAsync`, etc.) takes a `LoginDTO` and funnels through one private helper that calls Path B (`DBConnectionStringCached(loginDTO)`), then opens `NpgsqlConnection` or `SqlConnection` depending on `DataBaseType`. **Zero module-specific branching anywhere** — every module's DAL calls the same shared `IQueryExecutor`, and the physical database actually touched is determined entirely by which `LoginDTO` was passed in. This one fact is what makes GB5 multi-tenant: one codebase, N databases, selected per-request.
A `QueryAsync(string ConnectionName, ...)` overload exists for pre-authentication callers with no full `LoginDTO` yet (e.g. version-check, public brand lookup) — same Path B resolution, keyed directly on the raw connection name.
`SessionQueryAsync`/`SessionExecuteAsync`/`SameSessionCommitAsync` implement same-HTTP-request connection/transaction reuse via `HttpContext.Items`, for multi-step BLL flows needing one shared open transaction without threading a `DbTransaction` through every layer.
### 2.5 Existing "central hub" precedents
Three real, already-running examples of "one central store + N tenant-scoped spokes" — worth knowing before designing anything new, since the pattern already exists three times over:
**DXP** (`GB5Solution/DXP`) — `IDXPSystemContext` codifies it explicitly: DXP's global tables (`MDXPPARTY`, `TDXPPARTYLINK`, `MDXPUSER`, etc.) live in one dedicated DXP system database, never inside any tenant DB. `TDXPPARTYLINK` is the join row: one global `MDXPPARTY` identity → N `(TenantId, DatabaseName, LocalPartyId)` tuples. `GetSystemLogin(userId)` builds a `LoginDTO` for the global tables; `GetTenantLogin(tenantId, databaseName, ...)` builds one for a specific tenant's own DB (reads only — writes always go through that module's own HTTP endpoint, never direct cross-tenant DAL access).
**Partner** (`GB5Solution/Partner`) — central tables (`TPARTNER`, `TPARTNERPRODUCT`, `TPARTNERBRAND`, `TPARTNERAPIKEY`) live in `gb5system`. `MCLIENTDETAILS.PARTNERPRODUCTID` is the FK from the tenant registry into the partner registry. `PartnerSyncBLL.ProvisionPartnerSync` provisions `TDSYNCJOB` rows that drive the DataSync engine (§4) to replicate Partner data down to tenant databases — the "hub pushes to spokes" shape implemented as a scheduled (in practice: manually-triggered, see §4.4) replication job rather than a live query.
`GetPublicBrandByConnection.cs` (Partner) is the cleanest documented two-hop precedent: **Hop 1** (central `gb5system`, via Path A): `MSERVERCONFIG.CONNECTIONNAME → CLIENTID → MCLIENTDETAILS.PARTNERPRODUCTID`. **Hop 2** (the tenant's own DB, via the pre-auth `QueryAsync(ConnectionName, ...)` overload): the actual `TPARTNERPRODUCT`/`TPARTNERBRAND` row data — because brand data is deployed per-tenant today, not centralized.
**Domain/API-key routing** — `TCLIENTDOMAIN` (domain→`ClientId`/`DatabaseName` mapping, lives only in `gb5system`) + `DomainCacheService` (polls `gb5system` every 5 minutes via Path A, caches in `IMemoryCache` with 15-min sliding TTL) + `ApiKeyAuthMiddleware` (SHA-256-hashes an `X-Api-Key` header, looks up `TPARTNERAPIKEY`⋈`TPARTNERPRODUCT` against `gb5system`, resolves `ClientId`/`DatabaseName` from the request `Host` header, synthesizes a `LoginDTO` and injects it as the `Login` header so downstream endpoints work unmodified). Fails **closed** on any DB lookup exception (a real bug — previously fail-open — already fixed).
### 2.6 `ReportConnectionResolver` — the existing multi-database-per-client mechanism
File: `GB5Framework/FrameworkBLL/ReportOrchestration/ReportConnectionResolver.cs`. `BuildConfigAsync`:
1. Always loads the OLTP `MSERVERCONFIG` row first (via `loginDTO.ServerConfigId`) — this row carries `ReportServerConfigId`/`ArchiveServerConfigId`.
2. For `DataSourceType.ReportDb`/`ArchiveDb`, resolves the target config id from those FK columns.
3. If the FK is `-1` (unset — the explicit sentinel for "no dedicated DB configured"), falls back to the OLTP config and logs a warning.
4. If set, loads a **second, different** `MSERVERCONFIG` row and builds the connection off that.
`IsDedicatedAnalyticsDb` on the result records whether the fallback happened, so downstream code can decide e.g. whether `NOLOCK` hints are safe. `ServerConfigCache` is a per-request dictionary cache (not `HybridCache`) that loads a config row by `SERVERCONFIGID` directly against `gb5system`.
**This is the existing mechanism for "one client, more than one physical database"** — used today for Report/Archive DBs, and reused as-is by SqlWorkbench's client-provisioning flow (§3.4) with zero changes to the resolver itself.
### 2.7 The `TUNNEL` dev-testing precedent (not production)
`MSERVER` row `SERVERID = -777800, SERVERNAME = 'TUNNEL', SERVERIP = '127.0.0.1,15433'`, with a sibling `MSERVERCONFIG` row already pointing at it. Purpose: Path B bakes the *real* `MSERVER.SERVERIP` into every generated connection string, so a sandboxed session with no direct route to a shared dev box would otherwise hang for the full 120s timeout trying to dial the real IP. Re-pointing a row's `SERVERID` at `-777800` and opening a local SSH tunnel on port 15433 routes it through instead. **Purely a dev/sandbox convenience — no application logic special-cases this row.** Full recipe in `docs/Live-DB-Testing-Via-SSH-Tunnel.md`.
### 2.8 SqlWorkbench's own, parallel registry
`SW.MSWDBSERVER`/`SW.MSWCLIENTDATABASE`/`SW.MSWDBMODEL` are a **separate ID space** from `MSERVER`/`MSERVERCONFIG`, confirmed explicitly in code comments, not merely inferred. SqlWorkbench uses these to track *provisioning/DDL-lifecycle* state (which schema-model version, which physical server slot, Vault credential paths) — **not** consulted by `ApplicationConnection`/`IQueryExecutor` at request time. The one intentional bridge: `MSWCLIENTDATABASE.SERVERCONFIGID` (added by migration `032_ALTER_ClientProvisioning.sql`) links a SqlWorkbench-provisioned database back into the real `MSERVERCONFIG` registry, so it becomes reachable via ordinary `LoginDTO` routing and via `ReportConnectionResolver`. The two registries are synchronized by that single nullable FK, not merged.
---
## 3. Client & User Creation, and Physical Database Creation
### 3.1 Client + first-admin-user creation
Files: `EntitlementBLL/Implementations/ClientProvisioningBLL.cs`, `EntitlementDAL/Implementations/ClientProvisioningDAL.cs`, `EntitlementDAL/QueryBuilders/ClientProvisioningQB.cs`, `EntitlementSL/Endpoints/ClientProvisioning/CreateClient.cs`.
**Endpoint:** `POST /lic/ClientProvisioning.svc/CreateClient` — internal-staff-only, gated via `[MenuRights("entclientprovisioning", RightOperation.Insert)]` (Login-header + `MROLEVSMENU` check, not JWT).
**Flow** (`ClientProvisioningBLL.CreateClientAsync`):
1. Validates required fields; throws if `ClientProvisioningOptions.DefaultAdminRoleId` isn't configured (no live default set yet).
2. Uniqueness check on `ClientCode` → `ClientCodeAlreadyExistsException` on duplicate.
3. `AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.CLIENT, ...)` allocates the new `ClientId`.
4. Inserts `MCLIENT` (including Entitlement's additive `DEPLOYMENTTYPE`/`SUBSCRIPTIONSTATUS`/`TRIALMODE` columns — `DeploymentType` hardcoded to `0` unconditionally, see §6.1).
5. Builds a second `LoginDTO` scoped to the new `ClientId`, reuses `IUserBLL.SaveUser` (`GB5Framework/FrameworkBLL/User/UserBLL.cs`) for the first admin `MUSER` row.
6. Publishes `EventTypeConstant.CLIENTPROVISIONEDEVENTTYPEID`.
7. Returns `{ ClientId, UserId, TemporaryPassword }` — the one and only place the first admin's plaintext temp password is ever visible.
**Password generation**: `UserBLL.SaveUser`, when `UserPasswordType == 0` and `UserId == 0`, generates a 12-char random alphanumeric password (`RandomGenerateAlphaNumeric(12)`), held transiently on `UserDTO.UserGeneratedPassword` (never persisted); `MUSER.PASSWORD` stores `Encrypt(password, UserCode)` instead.
**A real fix made along the way, worth calling out**: `SaveUser`'s SQL never wrote `MUSER.TENANTID` — harmless for every existing one-DB-per-tenant caller, but unsafe for this reuse against Entitlement's shared platform DB (multiple clients' `MUSER` rows would otherwise coexist with no way to tell them apart). Fixed: `UserDTO.TenantId` now set from `LoginDTO.ClientId`, both `SAVE_USER` (SQL Server) and `SAVE_USER_PG` (Postgres) updated. `SAVE_USER_ORACLE`/`SAVE_USER_MYSQL` remain unimplemented stubs, correctly untouched.
**Where this is NOT wired up**: `ClientProvisioningBLL` is called from nowhere except its own endpoint and tests. It is **not** invoked by `ProvisioningService.ProvisionSubscriptionAsync` (the real Quickstart/IDMS entry point, `POST /lic/Provisioning.svc/Provision`), and no IDMS/Quickstart wizard calls it either. **These are two entirely separate pipelines today**: `CreateClient` makes the identity (`MCLIENT`+`MUSER`); `Provision` makes a subscription + entitlement grants for an *already-existing* client. Nothing chains one into the other automatically.
### 3.2 Physical database creation — dual mode
Files: `SwBLL/Provisioning/ClientDatabaseProvisioner.cs`, `IClientDatabaseProvisioner.cs`, `SqlServerProvisioningOptions.cs`.
**`CreateFromSnapshotAsync`**:
1. `EnsureSqlServer` guard + identifier validation on `DatabaseName`/`ClientDbCode` + `templateBackupRef` must match `^[A-Za-z0-9_\-\.]+\.bak$` (plain filename only, blocks path traversal).
2. Collision guard (§3.2.1).
3. `RESTORE FILELISTONLY FROM DISK` discovers the .bak's logical data/log file names dynamically — callers never supply these.
4. `RESTORE DATABASE ... WITH MOVE ... MOVE ... REPLACE, STATS = 10`, files renamed to `{databaseName}.mdf`/`{databaseName}_log.ldf` under configured destination paths.
5. Asserts DB defaults (§3.3), creates 3 contained users (§3.4), registers in `MSERVERCONFIG` (§3.5).
**`CreateFromScriptsAsync`**: same guard/collision steps → plain `CREATE DATABASE [name];` → same defaults/users/registration → hands off to the **existing, unmodified `ProvisioningBLL.ProvisionClientDatabase`** against a baseline `UpgradePackage` (reuses SqlWorkbench's normal DDL-deployment pipeline rather than reinventing script execution — see §4.2).
**Collision guard**: `EnsureDatabaseDoesNotExistAsync`, checked before both RESTORE-with-REPLACE and CREATE DATABASE — throws `InvalidOperationException` rather than silently overwriting or racing.
**SQL-Server-only, confirmed**: `EnsureSqlServer` throws `NotSupportedException` for any non-SQL-Server `DbType`, called as the first line of both public methods. Doc comment: Postgres is "deliberately out of scope for this release... matches this module's own 'harden before extending' sequencing."
### 3.3 DB-level creation defaults
Asserted identically after both creation modes:
```sql
ALTER DATABASE [{db}] SET RECOVERY {FULL|SIMPLE}; -- FULL for Main, SIMPLE for Report/Archive
ALTER DATABASE [{db}] SET PAGE_VERIFY CHECKSUM;
ALTER DATABASE [{db}] SET AUTO_SHRINK OFF;
ALTER DATABASE [{db}] SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE [{db}] SET CONTAINMENT = PARTIAL;
ALTER DATABASE [{db}] MODIFY FILE (NAME = ..., FILEGROWTH = {N}MB); -- fixed-MB, not percentage
```
`READ_COMMITTED_SNAPSHOT` is **deliberately not set** — changes transaction semantics platform-wide, left as an explicit operator decision rather than a silent default. Autogrowth defaults: data file +256MB, log file +128MB (config-driven, `SqlServerProvisioningOptions`).
### 3.4 Per-database security/login model
Three contained users (`CONTAINMENT = PARTIAL` — authentication happens entirely inside the database, no server-level login exists, so a leaked credential is architecturally confined to that one client's database, not just conventionally denied elsewhere):
| Login | Grant |
|---|---|
| `{clientdbcode}_dba` | `db_owner` |
| `{clientdbcode}_app` | `db_datareader` + `db_datawriter` |
| `{clientdbcode}_readonly` | `db_datareader` only |
Random 24-byte passwords (`RandomNumberGenerator`). **Credentials are Vault-path-only, never plaintext in any app database**: `sqlworkbench/clientdb/{ClientDbId}/{dba|app|readonly}-password`. Only the Vault path strings are persisted to `MSWCLIENTDATABASE`; the plaintext `app` credential exists in-memory only long enough to register it into `MSERVERCONFIG` for runtime routing.
### 3.5 `DatabaseRole` — multi-database per client
`DatabaseRole` enum: `Main=0, Report=1, Archive=2`. One Main row, at most one Report row, zero-or-more Archive rows (one per retention period) per client/model. `RegisterAndLinkServerConfigAsync`:
1. Maps `DatabaseRole` → `dbInstanceType` (Main→0, Report→2, Archive→3).
2. Registers the database via `IServerConfigBLL.RegisterDatabaseAsync` (the `{clientdb}_app` credential becomes the routable connection).
3. Links the new `ServerConfigId` back onto `MSWCLIENTDATABASE`.
4. For Report/Archive, looks up the sibling Main row's `ServerConfigId` and calls `LinkReportOrArchiveAsync` to wire `MSERVERCONFIG.ReportServerConfigId`/`ArchiveServerConfigId` **on the Main row** — this is the one integration point that makes `ReportConnectionResolver` (§2.6) pick up the new database, with zero changes to the resolver itself. If no Main sibling has a `ServerConfigId` yet, this logs a warning and continues rather than failing.
### 3.6 Governance wiring — `ChangeRequestCategory.ClientProvisioning`
Physical DB creation is not a bare API call — it's gated through SqlWorkbench's normal Change Request workflow (full detail in §4.3). `ChangeRequestBLL.Execute()` checks `Category == ClientProvisioning` **before** attempting to build a connection string to the target database (because it may not exist yet), dispatches to `CreateFromSnapshotAsync`/`CreateFromScriptsAsync` per `ProvisioningMode`, sets `MSWCLIENTDATABASE.Status = Active` on success, then falls through to the normal DDL-execution path (a no-op for scripts-mode, useful for snapshot-mode if delta scripts are linked on top of the restored baseline). **Constraint**: since the underlying provisioner is SQL-Server-only, `ClientProvisioning`-category CRs can only target SQL Server servers today — a Postgres target throws before any work begins.
### 3.7 Test coverage
`SwTests`: `ClientDatabaseProvisionerTests.cs` covers guard clauses only (invalid identifiers, non-SQL-Server `DbType`, invalid backup ref, the collision guard) — all against mocks, no real SQL Server needed. `ChangeRequestBLLProvisioningTests.cs` covers `Execute()`'s dispatch logic against a fully mocked `IClientDatabaseProvisioner`. **Not tested anywhere**: actual contained-user creation SQL, real Vault writes, real `MSERVERCONFIG` registration, real DB-default assertions — all of that requires a live SQL Server instance and has never been exercised end-to-end. Live run: `SwTests` 26/26 passing (mocked-boundary tests only).
### 3.8 `MPROVISIONINGJOB` — what it really tracks
Schema: `ProvisioningJobId, ClientId, IdmsEngagementRef` (unique — the real idempotency guard), `ProvisioningMode`, `TrialMode`, `PlanId`, `DeploymentType`, `JobStatus` (Queued/Running/Completed/Failed/PartialFail), `CurrentStep`, `CompletedSteps` (JSON), `LastError`, `RetryCount`, `DockerPackageUrl`, `RequestPayload`.
There **is** a real producer — `POST /lic/Provisioning.svc/Provision` → `ProvisioningService.ProvisionSubscriptionAsync` — but its scope is narrower than "provision a database." Its own doc comment states plainly: *"the job's own automated scope today is exactly 2 steps (subscription creation, entitlement seeding) — NOT the full future 8-step pipeline (DB provisioning, reference/sample data seeding, Docker packaging, welcome email are all separate, not-yet-built work)."* `ProgressPct` is computed against this narrow 2-step scope specifically so it reaches a genuine 100%.
**The critical gap**: the SqlWorkbench `ClientDatabaseProvisioner`/`ChangeRequestBLL` path that actually creates a physical database (§3.2/§3.6) is **completely disconnected** from `MPROVISIONINGJOB` — invoked only manually through SqlWorkbench's own Draft→Submit→Approve→Execute workflow, never triggered by `ProvisionSubscriptionAsync` or by any provisioning-job step. There is no code anywhere that automatically chains "subscription provisioned" → "physical database created."
### 3.9 Demo/DemoDB — does not exist
Exhaustive grep found zero implementation. `ProvisioningJobDTOs.cs`'s own comment: *"TrialMode/DeploymentType/DockerPackageUrl are schema-ready for later Demo (Thread 3) / Docker (Thread 2) work but are not populated by any code yet."* No Demo module, no DemoDB provisioning path, no sample-data seeder anywhere. This is explicitly forward-looking schema, not partial implementation.
---
## 4. SqlWorkbench — Schema Governance (Initial Creation + Ongoing Updates)
### 4.1 `DdlScript` lifecycle
State machine: `Draft(0) → Reviewed(1) → Approved(2) → Deployed(3)` — `Deployed` is defined but nothing ever sets it (no writer exists for status 3).
- **Create**: always forced to `Draft` on insert, regardless of caller input.
- **Review/Approve**: atomic compare-and-swap SQL (`WHERE DDLSCRIPTID=@Id AND SCRIPTSTATUS=@RequiredCurrentStatus`) — if another request already moved it, the operation fails cleanly rather than racing.
- **Editing an existing script, at any status, unconditionally resets it to `Draft`** and clears `ApprovedById` — there is no way to silently modify SQL text on an already-approved script.
- **Checksum**: SHA-256 over the trimmed script text, computed on every save — an audit/tamper-detection field. No code re-verifies it at execution time.
- **Delete**: blocked if the script has ever been logged as executed, and the DAL additionally requires `Draft` status — double-gated.
- Every script is scoped to exactly one `DbModel` + one `ScriptBranch`, plus an optional `ChangeRequestId` FK linking it back to the CR that spawned it.
**What actually prevents an unapproved script from running**: not a single gate on `DdlScriptBLL` itself, but an explicit filter at both real call sites — `ProvisioningBLL.ProvisionClientDatabase` skips any script where `ScriptStatus != Approved`; `ChangeRequestBLL.ExecuteDdlCr`'s SQL only ever selects `SCRIPTSTATUS = 2`. Nothing else in the codebase reads `SQLSCRIPT` and sends it to a live database.
### 4.2 `UpgradePackage` — bundling and applying scripts
A package (`MSWUPGRADEPACKAGE`, lifecycle Draft/Testing/Released/Deprecated) is a named, ordered bundle of DDL-script references.
**Real gap found**: `UpgradePackageDAL.ReplaceDdlLines`/`ReplaceMetaLines` fully implement attaching scripts to a package, but **no BLL method or endpoint exposes them** — only the read side (`GetDdlLines`/`GetMetaLines`) is reachable. **There is currently no wired UI/API path to actually build a package** — this needs closing before ops can rely on package-building through the tool rather than direct SQL.
**`ProvisioningBLL.ProvisionClientDatabase`** — applies a package to an existing (or freshly-created, see §3.2) client database:
1. Filters to `Approved` scripts only (silently drops anything else, not an error).
2. Orders by `Sequence` ascending (0 sorts last), tie-broken by `DdlScriptId`.
3. Executes each script via `TargetDbExecutor.ExecuteScriptAsync`, splitting on standalone-line `GO`.
4. **Log-and-continue**: each script wrapped in its own try/catch; failures are logged to `LSWDDLEXECUTION`/`LSWPROVISIONINGLOG` but **do not stop the loop** — every remaining script still runs.
5. `ClientDatabase.Status` becomes `Active` only if every script succeeded, else `Failed` — never throws for a partial failure.
### 4.3 `ChangeRequestBLL` — the general governance workflow
State machine: `Draft(0)→Submitted(1)→UnderReview(2)→Approved(3)→Rejected(4)/Executed(5)`, same atomic CAS pattern as DdlScript. Every transition appends a row to `LSWCHANGEREQUESTTIMELINE` (Created/Submitted/ReviewStarted/Approved/Rejected/Returned/Executed/Comment) — a real, append-only audit trail, not aspirational.
`Execute()` (only runs when `Approved`):
1. If `Category == ClientProvisioning`, runs the DB-creation pre-step first (§3.6).
2. Dispatches on `QueryType`: `DDL` → `ExecuteDdlCr`; everything else → `ExecuteDmlCr`.
3. On full success, transitions to `Executed`.
**Confirmed strictly stricter than `ProvisioningBLL`**: `ExecuteDdlCr` logs each failure to `LSWDDLEXECUTION` in a `finally` block, then **rethrows** — the first script failure aborts the whole run and leaves the CR stuck at `Approved` for manual investigation. This is the clearest behavioral asymmetry between the two execution paths and matters operationally: a package-driven provisioning run degrades gracefully (partial success, `Failed` status, but every script attempted); a CR-driven DDL change stops dead on the first failure.
**All `ChangeRequestCategory` values**: `SchemaChange(0), DataMigration(1), Performance(2), Security(3), Feature(4), BugFix(5), ClientProvisioning(6)`.
### 4.4 `MetadataSync` — confirmed: a bridge, not a diff engine
**This is the single most important clarification in this section.** `MetadataSync` does **not** introspect a live schema, compare snapshots, or compute drift automatically. `MetadataChangeDTO` has exactly five free-text fields (`TableName, ColumnName, ChangeType, OldValue, NewValue`) populated only by a human typing them in via `SaveChange`. A `SnapshotJson` blob field exists on the parent DTO but nothing reads/writes it from an actual schema catalog.
`GenerateCrFromVersion` is the entire mechanism: load the hand-entered changes → `MetadataSyncSqlBuilder.BuildDmlFromChanges` turns them into literal DML text → wraps it in a new `ChangeRequestDTO` (`Category = DataMigration`, `Draft`) → normal CR workflow from there. **`MetadataSyncSqlBuilder` (the injection-safe DML builder) is confirmed present on the current `dev` branch**, not just a feature branch — it validates `TableName`/`ColumnName` against an identifier regex before interpolating them, and quote-escapes values. Real, current, but scoped exactly as described: a safe way to turn hand-entered row-level changes into a governed CR, not schema-drift detection.
### 4.5 RBAC status
Sampled across DdlScript, ChangeRequest, Provisioning, UpgradePackage, and ad hoc Query endpoints — **every endpoint uses the hardened pattern**: `[MenuRights("MENUCODE", RightOperation.X)]` class attribute + `AllowAnonymous()` in `Configure()` (auth enforced by the `MenuRights` gate, not ASP.NET `Roles()`). No bare/unauthenticated endpoints found anywhere sampled. This is current, live state on `dev`.
### 4.6 `TargetDbExecutor` — dialect support
**Confirmed SQL-Server-only.** Hardcoded `Microsoft.Data.SqlClient` throughout every method — no Postgres/MySQL/Oracle client anywhere in this file, despite an enum suggesting multi-dialect was planned. `EnsureSqlServer` throws `NotSupportedException` for anything else, as a hard, explicit fail-fast.
Notable behavior: `ExecuteQueryAsync` (ad hoc reads, see §4.8) enforces SELECT-only via regex and caps results at 5,000 rows; `ExecuteDmlAsync` (parameterized single-table DML, see §4.8) has no such cap — by design, since it's a different, deliberately separate lane.
### 4.7 Real numbers (live-counted, not estimated)
- **97** endpoints across `SwSL/EndPoints/` (97 unique routes) — largest areas: ChangeRequest (12), Project (11), ObjectGroup (10), MetadataSync (9), SyncGroup (8), DdlScript (7).
- **16** BLL feature-area folders, **14** matching DAL `CustomCode` folders.
- **`SwTests`: 26/26 passing** (live `dotnet test` run) — full solution builds clean.
### 4.8 Adjacent, deliberately ungoverned lanes
Two endpoints reach a client's database directly through `ITargetDbExecutor` **without** the Draft→Review→Approve gate — worth knowing so ops doesn't assume every DB touch goes through governance:
- **`ExecuteAdHocQuery`** (`SWQUERY` menu) — SELECT-only (regex-enforced), capped at 5,000 rows, gated purely by `MenuRights`, no approval workflow.
- **`ExecuteDml`** (`SWDMLSC` menu) — fully parameterized single-table INSERT/UPDATE/DELETE (bracket-escaped columns, Dapper-bound values, empty `WHERE` rejected on UPDATE/DELETE), also `MenuRights`-only, no CR workflow.
Both are safe from SQL injection by construction, but they are a deliberately separate, RBAC-only-gated lane for ad hoc row-level operations, distinct from the governed DDL pipeline described above.
---
## 5. Metadata/Data Replication (the DataSync Engine)
### 5.1 How it actually works
Files: `FrameworkBLL/DataSync/DataSyncRunBLL.cs`, `FrameworkBLL/Engine/{SyncEngine,SyncQueryBuilder,ConflictResolver,TypeNormalizer}.cs`, `FrameworkBLL/Connection/ExternalDbConnectionFactory.cs`.
`ExecuteSyncJobAsync` acquires an atomic run-lock on `TDSYNCJOB` (0 rows affected = already running → throws), creates a `TDSYNCRUNLOG` row, then fires the real work on a **fresh DI scope** via `Task.Run` (deliberately not the request scope, which will be gone by the time background work runs) — returning the `RunId` immediately.
The actual run: opens **two live DB connections concurrently** (source + target, SQL Server via `SqlClient` or Postgres via `Npgsql` — MySQL/Oracle throw `NotSupportedException`, "Phase 4," see §5.6). For each configured table (ordered by `SlNo`), streams paged rows (Dapper `IAsyncEnumerable`, default chunk size 1000) filtered by watermark, and applies each chunk to the target with **Polly retry** (3 attempts, 500ms/1s/2s backoff, transient `DbException` only).
**Three conflict policies**:
- **Overwrite** — delete-then-reinsert, scoped to exactly the current chunk's PK set (not a full-table wipe).
- **Skip** — insert-only-if-absent; existing target rows are never touched.
- **LastWriteWins** — requires a configured modified-date column; inserts if absent, updates only if the source row is strictly newer, otherwise skips. Falls back to Overwrite if no modified-date column is configured.
Per-table exceptions are caught individually — **one bad table does not abort the whole job**; other tables still process, and the run finishes as `PartialFailure(4)` rather than `Failed`.
**Watermarks (`LastInsertedTill`/`LastUpdatedTill`/`LastDeleteTill`) only advance if the entire run succeeds fully (`finalStatus == 2`)** — a `PartialFailure` or `Failed` run leaves all three untouched, so the next run re-scans the identical window. `TDSYNCJOB.LastRunId`/`LastRunStatus`/`LastRunAt`, by contrast, update regardless of outcome.
### 5.2 `TDSYNCJOB`/`TDSYNCRUNLOG` schema
`TDSYNCJOB`: `SourceDbInstanceId`/`TargetDbInstanceId`, `DatasetId` (FK→`MDATASET`), `SchedulerId` (FK→`TSCHEDULER`, **nullable = manual-only**), `ConflictPolicy`, `Direction` (0=SourceToTarget/1=TargetToSource/2=Bidirectional — note: `DataSyncRunBLL` never actually reads this field; the runner always goes source-connection→target-connection regardless of its value, and a discrepancy was found between a code comment and the schema comment on what `1` means — worth resolving before treating `Direction` as authoritative), the three watermarks, `LastRunStatus`/`LastRunAt`/`LastRunId`.
`TDSYNCRUNLOG`: `TriggeredBy` (0=Scheduler/1=Manual), `TriggeredByUserId`, `Status`, row counts (Total/Inserted/Updated/Deleted/Skipped), `ErrorDetail`.
### 5.3 `MDATASET`/`MDATASETDETAIL`/`DBOBJECT` catalog
Three-level model: `DBOBJECT` registers a physical table by name in the platform's generic object catalog; `MDATASET` is a named bundle (e.g. `PARTNER_DIRECT`); `MDATASETDETAIL` is one row per table-in-a-bundle, carrying `SlNo` (processing order), `DbObjectId` (FK), plus DataSync-specific config (`ChunkSize`, `PrimaryKeyColumn`, `CreatedDateColumn`, `ModifiedDateColumn`).
**Confirmed: exactly one file in the whole repo (`Partner/Migration/20260621_PartnerSyncDataset.sql`) has ever registered a real `MDATASET` row** — `PARTNER_DIRECT` (TPARTNER+TPARTNERPRODUCT+TPARTNERBRAND) and `PARTNER_PRODUCT` (TPARTNERPRODUCTBRAND+TPARTNERTERM). **No other module has registered a dataset.** Registering a new one means following this exact pattern: `DBOBJECT` inserts (idempotency-guarded), `MDATASET` insert, then one `MDATASETDETAIL` row per table.
### 5.4 Scheduler — real infrastructure, dormant in practice
`DataSyncSchedulerJob` (`BackgroundService`, `PeriodicTimer`, polls every 60s by default) queries `TDSYNCJOB` **inner-joined to `TSCHEDULER`** — jobs with `SchedulerId IS NULL` are structurally excluded from this query, not merely skipped. For each due job (Cronos cron evaluation against `LastRunAt`), it calls `ExecuteSyncJobAsync(triggeredBy: 0)`.
**Confirmed: Partner's own provisioned jobs set `SchedulerId = null` explicitly and unconditionally** (`PartnerSyncBLL.UpsertJobAsync`). **Net effect: the scheduler infrastructure is real and would work for any job with a `SchedulerId` set, but as of today, nothing is actually scheduled.** The only real dataset registrations (Partner's two) are manual-trigger-only — everything runs via `POST /DataSync/TriggerSyncJob` by hand.
### 5.5 Hard-delete propagation
SQL-Server-source deletes are real, via CDC (`cdc.[dbo_{Table}_CT]`, `__$operation = 1` rows) — if CDC isn't enabled on a table, the query fails, is caught, logged as a warning, and the run continues without hard-delete propagation for that table (graceful degradation, not a hard failure). **Postgres-source hard-deletes are an explicit, documented no-op** — the method returns 0 immediately for any non-SqlServer source, with a code comment deferring this to "Phase 4" and suggesting the interim workaround (soft-delete via an `ISDELETED` flag column, propagated through the normal UPDATE flow) — a convention that is not enforced or verified anywhere in the engine; it's stated intent only.
### 5.6 `SOURCETYPE` — the convention that makes incremental sync safe at all
Present on ~200+ tables platform-wide (legacy-derived and GB5-native alike): `1=Framework, 2=Devadmin, 3=Impadmin, 4=Admin, 5=User`. This is what lets any future filtering logic distinguish framework/central-origin rows from client-customized ones — without it, an `Overwrite`-policy sync could silently clobber a tenant's own edit to a row that also happens to be a "standard" reference row. Partner's own `TDSYNCJOB` rows are stamped `SourceType=5` (User/tenant-created config); the `MDATASET`/`DBOBJECT` catalog rows themselves are `SourceType=1` (Framework-seeded). GB5-native modules (Entitlement, Partner, SqlWorkbench) follow the identical convention — there is no divergent scheme for newer modules.
### 5.7 MySQL/Oracle — confirmed unimplemented
Three independent confirmations: `ExternalDbConnectionFactory` throws `NotSupportedException` for `DBTypeId` 3/4 ("scheduled for Phase 4"); `SyncQueryBuilder` has dialect-string branches for MySQL/Oracle that are unreachable in practice since no connection can ever be opened; `TypeNormalizer`'s own comment defers MySQL/Oracle type-mapping to the same future phase. **Accurate framing: DataSync today is SQL Server ↔ PostgreSQL only, in either direction.**
### 5.8 Test coverage — none
No `FrameworkTests`/`DataSyncTests` project exists anywhere. `GB5Framework` (where the entire engine lives) has **zero automated test coverage**. Every guarantee described in this section — retry behavior, watermark-advance-only-on-full-success, conflict-policy correctness, CDC delete detection — is currently verified only by manual/live-server testing, not CI.
### 5.9 Summary for ops
- **Day-1 provisioning**: a module (today, only Partner) calls its own provisioning endpoint, which upserts `TDSYNCJOB` rows against pre-seeded `MDATASET` definitions with `SchedulerId = null` and `ConflictPolicy = Overwrite` ("central always wins").
- **Ongoing replication**: requires a manual (or externally-orchestrated) call to `POST /DataSync/TriggerSyncJob` — the scheduler exists and works, but no job today has a `SchedulerId` set, so nothing actually runs on a cron in practice.
---
## 6. Deployment Models — On-Premises / BYOC / SaaS
### 6.1 `MCLIENT.DeploymentType` — descriptive column, zero behavioral effect
Added by `20260713_Entitlement_Phase1_MCLIENT_Extension_*.sql`, nullable, default `0`. **Enum values are inconsistent across tables** — a real drift risk to flag before anyone starts branching on either:
| Table | Enum meaning |
|---|---|
| `MCLIENT.DeploymentType` | `0=OnPrem 1=Cloud 2=Hybrid` |
| `MPROVISIONINGJOB.DeploymentType` | `0=SaaS 1=OnPrem 2=BYOCloud` |
`ClientProvisioningBLL` hardcodes `MCLIENT.DeploymentType = 0` unconditionally at creation. `MPROVISIONINGJOB.DeploymentType` is never populated by any code at all — its own DTO comment says explicitly it's "schema-ready... not populated by any code yet." **No SELECT anywhere reads either column for any branching purpose** — connection resolution, provisioning logic, and billing logic are all completely blind to this value today.
### 6.2 The "Hosting: On-premises" field clients actually see — a sentinel, not a deployment-type branch
`SubscriptionResultDto.HostingValidTill` is a plain date column. "On-premises" is represented entirely by a **magic-date sentinel** defined in the frontend shared lib: `HOSTING_NA_SENTINEL = '1900-01-01T00:00:00'`. The client portal's Subscription screen computes `isOnPrem = HostingValidTill === HOSTING_NA_SENTINEL` and swaps the date display for "On-premises" text. Confirmed the backend's own default provisioning path (`ProvisioningService.ProvisionSubscriptionAsync`) already writes this exact sentinel by default. **This signal is completely disconnected from `MCLIENT.DeploymentType`** — two separate, non-cross-referenced ways of saying "on-prem" exist in the codebase today, and only one of them (the sentinel) actually reaches the UI.
### 6.3 Real, working precedent to build from
- **TCMS `TestEnv`** (`GB5Solution/TCMS`) — genuinely working dual-dialect DB creation: Postgres `CREATE DATABASE ... TEMPLATE ...`, SQL Server `BACKUP DATABASE`/`RESTORE ... WITH MOVE` (dynamically discovering logical file names via `sys.master_files`, same technique as §3.2's snapshot mode). Scoped to **test environments only** (`DbInstanceType=1`), not production tenant provisioning — but proves both dialects' DB-creation mechanics already exist in GB5-native/Dapper form.
- **`ReportConnectionResolver`** (§2.6) — proves multi-server-per-client routing already exists; a BYOC client's own cloud-hosted DB server could in principle be registered the same way (one more `MSERVERCONFIG` row), though nothing automates or specially recognizes that scenario today.
- **`MCLIENT.SubscriptionStatus`** — the one column in this family that's actually kept live: `SubscriptionService.SetStatusAsync` writes it in sync with the real subscription state machine (both from admin action and automatically from the go-live Dapr subscriber). However, **nothing reads it either** — the denormalization exists for a stated "fast tenant-status filtering" purpose that hasn't been built yet. (Don't confuse `MCLIENT.TrialMode`, which is dead, with `MENTITLEMENTSUBSCRIPTION.TrialFlag`, which is the one actually used for real trial logic.)
### 6.4 What does NOT exist
- **No BYOC provisioning path** — no option, DTO field, or code branch anywhere that provisions against or registers a customer-owned/external database server. `ClientDatabaseProvisioner` runs entirely against the platform's own managed server pool via Vault-sourced credentials, with no "bring your own connection string" flow.
- **Partner's white-labeling is not a BYOC/reseller-hosting precedent.** `TPARTNER`/`TPARTNERPRODUCT`/`TPARTNERBRAND`/`TCLIENTDOMAIN` give real custom-domain routing and UI branding/terminology overrides — but every one of those still resolves within GB5's own standard managed server pool via the same `gb5system` routing. There is zero reseller-hosts-their-own-instance capability.
- **No self-hosted "GoodBooks runs GB5 for itself" precedent exists either.** The `-1900000000` Admin `MMODULE` / `GBSUPERUSER` `MROLE` IDs used as anchors for Entitlement's own menu-seed migrations are **generic legacy IDs present identically across every one of 11 checked tenant databases** — shared seeding convention, not a distinct module/role created for a self-hosted instance. The one authoritative doc discussing this (`Entitlement-Technical-Reference.md`) explicitly flags admin-auth for a standalone deployment as an **unsolved** gap, not a working example.
### 6.5 Honest bottom line
Real, working infrastructure exists for: multi-dialect DB creation (TCMS), multi-server-per-client routing (ReportDb/ArchiveDb), and white-label branding/custom domains (Partner). Everything specifically **named** for deployment-model differentiation (`MCLIENT.DeploymentType`, `MPROVISIONINGJOB.DeploymentType`/`TrialMode`/`DockerPackageUrl`) is schema-ready placeholder with no branching logic anywhere, by the code's own admission. If the business needs real BYOC or a differentiated on-prem operating model, treat it as net-new design work that can reuse the multi-server-routing and dual-dialect-creation precedents above — not as a flag to flip on existing code.
---
## 7. Billing & Payment Integration
### 7.1 PAY module structure
Location: `GB5Solution/PAY/{PAYDAL,PAYBLL,PAYSL}` — a mature, fully-populated three-tier module, not a skeleton. Key entities, all carrying `TenantId`:
| Concern | Table | Notes |
|---|---|---|
| Gateway catalogue | `MPAYGATEWAY` | Platform-level, **no tenant filter at all** — a shared DevAdmin catalogue ("Razorpay exists"), not per-tenant data |
| Gateway config | `MPAYGATEWAYCONFIG` | Per-tenant merchant credentials, Vault-path references only |
| Order | `TPAYORDER` | `SourceDocType`/`SourceDocId` generic document linkage, `OrderStatus` (Created→Pending→Paid/Failed) |
| Transaction | `TPAYTRANSACTION` | Gateway txn id, status, raw response |
| Webhook log | `TPAYWEBHOOKEVENT` | Append-only, idempotency key, signature-validity flag |
| Cashback rule/ledger | `MCASHBACKRULE`/`TCASHBACKLEDGER` | Order-scoped |
| Loyalty program/ledger/enrollment | `MLOYALTYPROGRAM`/`TLOYALTYLEDGER`/`TCUSTOMERLOYALTYENROLLMENT` | Order-scoped, B2B/B2C/Custom program types |
### 7.2 The real payment flow, as coded
`PayOrderBLL.InitiatePayOrder` is a genuinely implemented, multi-step orchestration (validate → load gateway config → fetch Vault credentials → duplicate-active-order guard → loyalty-redemption validation → compute net payable → insert `TPAYORDER` in one transaction → call the real gateway → insert `TPAYTRANSACTION` + update order status in a second transaction → fire event log). `PayWebhookBLL.ProcessWebhookAsync` is equally real: insert raw event first → load gateway config → verify signature via the gateway's own implementation → idempotency check → distributed advisory lock → map gateway event type to order status → single transaction updating order + transaction + (on Paid) placeholder loyalty/cashback ledger rows + Finance outbox notification → mark processed → fire-and-forget Dapr publish.
**Confirmed genuinely stubbed, not assumption**:
- **Coupon apply is dead code from checkout's perspective** — `ISalesCouponService` (a real, working cross-module HTTP call to Sales) exists, but `InitiatePayOrder` never calls it and hardcodes `CouponDiscAmt = 0`.
- Loyalty-earn/cashback amounts on webhook success are **placeholders** (`0`) — real calculation is deferred to background jobs (`CashbackPayoutJob`/`LoyaltyPointsExpiryJob`), which do exist and run on a timer.
- Cashback payout modes GatewayPayout and Wallet throw `NotImplementedException` — only CreditNote (via Finance) and LoyaltyPoints are implemented.
- **CashfreeGateway is a full stub** — every method throws `NotImplementedException`. Only Razorpay and Stripe are real, working gateway integrations (both with genuine HMAC signature verification; Stripe additionally enforces a 300-second replay window).
- Vendor payout is unimplemented on both real gateways ("Phase 2").
The three previously-flagged missing read endpoints (`GetPayTransactionsByOrder`, `GetWebhookEventList`, `GetCashbackLedgerByOrder`) **now exist** and follow the standard endpoint pattern correctly.
### 7.3 Multi-tenancy model
**PAY is a shared platform-level module, not a per-tenant-database module** — architecturally identical to Entitlement in this respect. Every business/document table filters by `TENANTID`, explicitly **not** `DATABASENAME` (a code comment states this directly: `TPAYORDER` "does NOT have a DATABASENAME column — filter by TENANTID only"). The webhook flow resolves `DatabaseName` from `TenantId` by querying the central `MCLIENT` table when it needs to build a system `LoginDTO` — confirming PAY lives in one central/shared database and reaches across to identify which tenant a webhook belongs to.
### 7.4 The gap — Entitlement ↔ Payment, exhaustively confirmed at zero
This is the single most consequential finding in this document. Checked exhaustively, in both directions:
- No reference to `MENTITLEMENTSUBSCRIPTION`/`MENTITLEMENTPLAN` anywhere in PAY.
- No reference to `TPAYORDER`/`PayGatewayConfig`/`PayTransaction` anywhere in Entitlement.
- `SubscriptionResultDto` has no field referencing any PAY order/transaction/invoice — its only external-system reference is `IdmsEngagementRef` (a sales-engagement tie-back), with no analogous `PayOrderId`.
- `PAYSL/Program.cs` registers `HttpClient`s only for the two payment gateways plus `SalesModule`/`FinanceModule` — **no client pointed at Entitlement**, and Entitlement registers none pointed at PAY either.
- The webhook flow's only downstream consumers are a Finance outbox event (for receipt/voucher posting) and a Dapr `payorder.statuschanged` event explicitly documented as "consumed by Finance module or notification service — no handler in PAY module itself." Entitlement is not among the consumers.
- The three `// TODO: wire Claims("PAY_ACCESS") when entitlement middleware is ready` comments found in PAY are a generic RBAC/claims-authorization TODO — a different, unrelated sense of "entitlement," not a reference to the Entitlement module.
**What this means concretely**: "is this subscription paid/active" is decided **entirely inside Entitlement**, by a human or API caller explicitly setting `SubscriptionStatusEnum`/`LicenseValidTill` — with zero automated dependency on whether any `TPAYORDER` ever reached `Paid`. Conversely, PAY's `SourceDocType`/`SourceDocId` generic linkage has no value or code path targeting an Entitlement subscription — its wiring today targets Sales orders and Finance vouchers. The client portal's Billing screen invoice-history stub correctly reflects this: it makes **zero calls to any PAY endpoint**, because there is nothing to call.
**This should be treated as an open architectural decision, not an incomplete integration** — there is no partial wiring to finish, only a decision to make about what the integration should look like (most naturally: a new Entitlement-specific `SourceDocType` value in `TPAYORDER`, plus either a new Entitlement-side outbox/Dapr consumer or a direct BLL/HTTP call from Entitlement into PAY at subscription-creation/renewal time). None of this exists today; see the master tracker's own flagging of this as a P0 decision.
### 7.5 Loyalty/Cashback — a separate concern from subscription billing
Confirmed genuinely separate: `LoyaltyProgramDTO.ProgramType` (B2B/B2C/Custom) is a general customer-rewards mechanism; `CashbackRuleDTO`/`CashbackLedgerDTO`/`LoyaltyLedgerDTO.ReferenceOrderId` all operate strictly against an individual `PayOrderId`, never a subscription or plan. There is no code path anywhere tying loyalty points, cashback, or coupons to a subscription tier or entitlement — this is a B2C/order-level mechanism layered on top of PAY's checkout flow, unrelated to the SaaS subscription-billing story.
---
## 8. Consolidated Known Gaps
| Gap | Where | Severity |
|---|---|---|
| `Validation.cs` vs `ApplicationConnection.cs` disagree on whether `Gb5SystemDTO:Gb5System`'s password is plaintext or AES-GCM-encrypted | §2.2 | Medium — silent failure mode, not yet reconciled platform-wide |
| Client identity creation (`CreateClient`) and subscription provisioning (`Provision`) are two disconnected pipelines | §3.1, §3.8 | High — blocks any real self-service onboarding |
| Physical DB creation is never triggered automatically by subscription provisioning | §3.8 | High — same blocker, different angle |
| `UpgradePackage` has no wired path to attach scripts to a package | §4.2 | Medium — package-building must currently happen via direct SQL |
| `MetadataSync` is not a schema-diff engine — don't expect drift detection | §4.4 | Low if understood; High if assumed otherwise |
| SqlWorkbench's DB creation/DDL execution (`TargetDbExecutor`) is SQL-Server-only — **DataSync itself already supports Postgres as both source and target**, don't conflate the two | §3.2, §4.6 | Medium — real for any Postgres-hosted tenant's DB *creation/schema updates*; not a DataSync limitation |
| DataSync scheduler is real but nothing is actually scheduled — every real job is manual-trigger-only | §5.4 | Medium — "regular" replication requires a human or external cron today |
| Postgres-source hard-delete propagation is an unimplemented no-op | §5.5 | Medium — reference-data deletes silently don't propagate from a Postgres source |
| Zero automated test coverage for the entire DataSync engine (`GB5Framework`) | §5.8 | High from a change-safety standpoint |
| `MCLIENT.DeploymentType` enum values disagree with `MPROVISIONINGJOB.DeploymentType`'s enum on the same concept | §6.1 | Low today (nothing reads either), High if code starts branching on both without reconciling |
| No BYOC provisioning path exists at all | §6.4 | High if BYOC is a near-term commercial requirement |
| No differentiated on-prem operating model — only a display sentinel | §6.2, §6.4 | Medium — fine if on-prem clients are operationally identical to SaaS ones today |
| Entitlement ↔ Payment integration does not exist | §7.4 | **Highest** — flagged P0 in the program tracker; blocks any real automated subscription billing |
| PAY's Cashfree gateway, gateway/wallet payouts, and coupon-apply are stubbed | §7.2 | Medium — Razorpay/Stripe + CreditNote/LoyaltyPoints payout are the real, working paths today |
---
## 9. Proposed Directions for Known Gaps
Everything below is a **proposed, agreed direction** — reasoned through against the real code this document already cites, so it isn't a guess — but **none of it is built yet**. Each item names its priority/readiness the same way the master engagement tracker does, so this section can be lifted directly into that tracker's backlog. Nothing here should be started without a fresh confirm at the time, since priorities may have shifted.
### 9.1 Entitlement ↔ Payment — loose coupling via reference + async update (§7.4)
**Proposed shape**, reusing mechanisms both modules already have — no new coupling primitive needs inventing:
1. **Entitlement (or Sales, on Entitlement's behalf) creates the PayOrder with a reference.** PAY's `TPAYORDER.SourceDocType`/`SourceDocId` already generically supports "any document type owns this order." Register a new `SourceDocType` value (e.g. `EntitlementSubscription`), pass `SourceDocId = SubscriptionId`. No PAY schema change needed.
2. **PAY handles checkout/gateway/webhook entirely independently** — exactly as it already does for Sales orders. Entitlement never touches gateway/webhook logic, never learns which gateway was used.
3. **Entitlement updates based on the outcome, asynchronously, not synchronously.** PAY already publishes a Dapr `payorder.statuschanged` event and writes a Finance outbox row on every status change. Entitlement adds itself as a second subscriber to that same event — mirroring the exact pattern its own `GoLiveDeclaredSubscriber` already uses for IDMS go-live events (§3.8).
4. **Backstop: a periodic reconciliation job**, not the event subscription alone. Entitlement calls PAY's `GetPayTransactionsByOrder`-style read endpoint by `SourceDocId` on a schedule and self-corrects if its own subscription status has drifted from PAY's real order state. This is required, not optional — Dapr delivery isn't guaranteed, and subscription billing state is too consequential to leave to at-least-once-effort event delivery alone.
**This pattern generalizes beyond Entitlement.** Any future module that needs "create a payment, don't block on it, update state when it resolves" (a hypothetical training-module seat purchase, a marketplace add-on, etc.) is the same three-step shape: register a `SourceDocType`, subscribe to `payorder.statuschanged`, add a reconciliation job. Worth formalizing as a documented pattern (a short "How to accept payment for your module" doc) once a second real caller exists, rather than re-deriving it per module. *Priority: P0 for Entitlement specifically (per the tracker's existing flag) · P2 to formalize as a general pattern · Readiness: Needs scoping — the `SourceDocType` value, the subscriber's exact idempotency handling, and the reconciliation job's schedule all need a short design pass before implementation.*
### 9.2 Chain client-identity creation → DB provisioning → subscription provisioning into one real onboarding flow (§3.1, §3.8)
Three real, working pieces exist today with no orchestration between them: `ClientProvisioningBLL.CreateClientAsync` (identity), `ChangeRequestBLL`'s `ClientProvisioning`-category execution (physical DB), and `ProvisioningService.ProvisionSubscriptionAsync` (subscription + entitlements). **Proposed shape**: a new orchestrator (`ClientOnboardingOrchestratorBLL`, living in Entitlement since it's the natural owner of "a client now exists commercially") that calls all three in sequence, each still going through its own existing validation/governance:
1. `CreateClientAsync` → real `ClientId`.
2. Draft + submit a `ClientProvisioning` Change Request (still requires the normal Approve step — this orchestrator doesn't bypass SqlWorkbench's governance, it just automates *drafting* the request instead of a human hand-typing one).
3. On CR execution success (observed via the CR's own status, or a callback/poll), call `ProvisionSubscriptionAsync`.
4. Register a DataSync job (§9.4) to seed standard reference data into the new database.
*Priority: P0 · Criticality: High — this is the actual blocker on any real self-service onboarding · Readiness: Needs scoping — specifically, whether step 2's Approve gate should require a human in the loop for every new client (safe default) or be auto-approved for a pre-vetted "standard" provisioning template (faster, riskier) is a real product decision, not an engineering one.*
### 9.3 Close the `UpgradePackage` script-attachment gap (§4.2) — DONE
**Implemented**: `IUpgradePackageBLL.AttachDdlScript(AttachDdlScriptRequestDTO, ...)`/`DetachDdlScript(DetachDdlScriptRequestDTO, ...)` — thin wrappers that load the package's current DDL-line set via the already-implemented `GetDdlLines`, apply the one change, and call the already-implemented `ReplaceDdlLines` inside a transaction (mirroring `Save`'s existing transaction/cache-invalidation pattern). Guards: package must exist, must not be `Released` (mirrors `Delete`'s existing guard), attach rejects a duplicate `DdlScriptId`, detach rejects a `DdlScriptId` that isn't attached. Two new `[MenuRights("SWUPGPKG", RightOperation.Update)]` endpoints: `POST /UpgradePackage/AttachDdlScript`, `POST /UpgradePackage/DetachDdlScript`. No schema change — this really was "wire up code that already exists." Verified: `SwSL`/`SwBLL` build 0 errors; new `UpgradePackageBLLTests.cs` (7 guard-clause tests, same convention as `ClientDatabaseProvisionerTests`) + full `SwTests` suite — **33/33 passing** (26 existing + 7 new).
### 9.4 Adopt real scheduling for DataSync jobs created by onboarding (§5.4, §5.9)
Once §9.2's orchestrator exists, it should register the new client's standard-data seed job with a real `SchedulerId` (a `TSCHEDULER` row with an appropriate cron expression) rather than leaving it `null`/manual-only the way Partner's jobs are today — otherwise every new client's reference data silently never re-syncs after the initial seed. Whether recurring sync is even wanted per-table (some reference data is genuinely "seed once," other data — tax tables, currency rates — plausibly wants a real cadence) is a per-`MDATASETDETAIL` decision, not a blanket one. *Priority: P1 · Criticality: Medium · Readiness: Needs scoping — requires deciding, per candidate table, whether "seed once" or "sync on a cadence" is correct, before wiring a `SchedulerId`.*
### 9.5 Postgres-source hard-delete: adopt the soft-delete convention deliberately, don't wait for Phase 4 (§5.5) — DONE
**Documented**: `docs/DataSync-Tagging-Conventions.md` now formally adopts the `ISDELETED` soft-delete convention (the engine's own code comment named it as the workaround but never enforced or verified it) alongside `SOURCETYPE` as a required tagging convention for any table that (a) needs delete-propagation and (b) might ever be synced from a Postgres source, plus a registration checklist. *Enforcement (e.g. a `DdlScript` review checklist item actually gating a new table's registration) remains a small follow-up — not yet wired into any review workflow.*
### 9.6 Add real test coverage for the DataSync engine (§5.8) — DONE, and it found two real bugs
**Implemented**: new `GB5Framework/FrameworkTests` project (35 tests) covering `TypeNormalizer` (pure, public — every documented SQL Server ↔ PostgreSQL value mapping), `SyncQueryBuilder` (internal, reached via `[InternalsVisibleTo("FrameworkTests")]` on `FrameworkBLL` — identifier quoting per dialect, watermark-clause assembly, the QUERYCONDITION injection guard), and `ConflictResolver` (internal, same visibility mechanism) — the last against a **real in-memory SQLite database**, not mocks, since ConflictResolver's own SQL (identifier-quoted DELETE/INSERT/SELECT-by-PK/UPDATE) needs no OFFSET/FETCH or other dialect-specific syntax SQLite can't handle.
**Two real, pre-existing bugs found and fixed as a direct result of writing these tests against a real database instead of mocks:**
1. **`ApplySkipAsync`'s existing-row check was silently broken.** It used `QueryAsync