# Full Base Schema — SQL Server (from-scratch client DB creation)

## What this is

This closes the single largest open gap tracked across this whole engagement (tracker
§31.4 flag #1, §31.8, §31.9, §31.10, §31.13): **there was no portable base
Framework/legacy schema anywhere in this repo** — every prior "from-scratch DB
creation" test (`EntTestDb1`, `ONBTESTEDb`) only worked because the target database
already had `MCLIENT`/`MUSER`/`MMENU`/etc. present from an earlier, unrelated
provisioning step. A database created purely via `CREATE DATABASE` + Entitlement's own
baseline package had 6 of 8 scripts fail outright for exactly this reason.

The user provided the real, long-lived legacy DDL source tree
(`/Users/venkatv/Downloads/gbmssql`) — the actual scripts that have created every real
GB4/GB5 client database for years, driven by `createtable.bat` (1,535 `sqlcmd -i`
calls, one per file, in a carefully FK-dependency-ordered sequence spanning ~30
modules: FRAMEWORK, ACCOUNTS, MM, PAYROLL, CRM, TMS, COSTING, FM, LOGISTICS,
DATAWAREHOUSE, SM, GST, HR, ADMIN, PRODUCTION, ANALYTICS, API, MARKETING, WMS, KPI,
PARTNER, MAINTENANCE, ENABLEMENT, VEHICLE, QUALITY, FAM, FOUNDRY, DMS, devsys).

## What's captured here

`chunk_001_of_016.sql` through `chunk_016_of_016.sql` are a **byte-faithful
concatenation** of every file `createtable.bat` references, in its exact original
order — no reordering, no simplification, no "modernization." Each source file is
preceded by a `-- ===== SOURCE: <original Windows path> (createtable.bat line N) =====`
comment for traceability back to the original tree. Chunking (100 source files per
chunk) is purely for file-size manageability in this repo/for review — it carries no
semantic meaning; the true unit of sequencing is the global 1-1690 line order from
`createtable.bat`, preserved exactly across chunk boundaries.

**Verified, not assumed**: `chunk_001` opens with the real `MCLIENT` table (matching
the live column list already confirmed against `USCIMPSYS` earlier in this engagement
— `CLIENTID`/`CLIENTCODE NVARCHAR(10)`/`CLIENTNAME NVARCHAR(100)`/`SOURCETYPE` `CHECK
(0-5)`/etc.) — the same real legacy table this whole engagement has been reasoning
about, not a reconstruction. `chunk_016` ends with `Cosntraint.txt`, the final
cross-module FK-constraint pass, matching `createtable.bat`'s own last two lines.

**Confirmed by direct inspection, not assumption**: many `CREATE TABLE` statements
carry genuine inline `FOREIGN KEY` constraints referencing tables created earlier in
the sequence (e.g. `MUSER` inline-references `MCLIENT`/`MSECURITYQUESTION`/`MIMAGE`,
all created immediately before it) — this is exactly why the global order must never
be reshuffled by module, and why these 16 files must always be applied in numeric
order, start to finish, against an empty database.

The full pipeline captured, in order: CREATE TABLE (the large majority) → CREATE VIEW
→ CREATE INDEX → reference-data `INSERT`s (`-1insertQuerybatch.txt`) → `CREATE
TRIGGER` → additive `ALTER TABLE ... ADD` addon columns (`ForNewDBCreation.txt`) →
deferred/cross-module `FOREIGN KEY` constraints (`Cosntraint.txt`).

## Known gap — one file, not blocking

`MM\PROCEDURE\ExpiryToReturn.txt` is referenced by `createtable.bat` (line 1174) but
does not exist in the provided source tree — the only unresolved reference out of
1,535 (99.93% resolved). By its path (`PROCEDURE`) this is a stored procedure, not a
table/view/index any other script's `CREATE TABLE`/FK could depend on, so its absence
does not block schema creation — flagged here rather than silently dropped. Locate the
real file and add it as a follow-up chunk if/when found.

## Explicitly NOT done here — tracked, not forgotten

- **No idempotency guards** (`IF OBJECT_ID(...) IS NULL ...`) were added around these
  1,534 individual statements, unlike this engagement's smaller incremental
  Entitlement migrations. This is deliberate, not an oversight: this baseline is only
  ever meant to run once, against a genuinely empty, freshly `CREATE DATABASE`'d
  target (SqlWorkbench's `CreateFromScriptsAsync` "from scripts" mode) — never against
  an existing populated database — so the append-only-migration guard convention
  doesn't apply the same way here.
- **Not yet registered as SqlWorkbench `SW.MSWDDLSCRIPT`/`SW.MSWUPGRADEPACKAGE` rows.**
  Per the established Thread 6 pattern (`ENTITLEMENT_BASELINE_V1`, tracker §31.8), each
  of the 1,534 individual source files should become its own `MSWDDLSCRIPT` row (real
  per-script pass/fail audit trail, matching how `createtable.bat` itself treats each
  file as one `sqlcmd` unit) bundled into a new baseline `MSWUPGRADEPACKAGE` (e.g.
  `FULL_BASE_SCHEMA_V1`) that Entitlement's own baseline package would need to run
  *after*. This requires live DB write access to load — not done in this pass; a
  loader tool mirroring the §31.8 "throwaway parameterized console loader" technique
  is the natural next step once there's a go-ahead to touch the live target again.
- **PostgreSQL port — real, sizeable follow-up work, explicitly tracked here per the
  user's own instruction not to let it get silently dropped.** This capture is
  SQL-Server-only, matching Thread 6's own already-established SQL-Server-first scope
  decision (`TargetDbExecutor` itself is SQL-Server-only today) — doing both dialects
  in one pass would have doubled this task's size for a dialect nothing currently runs
  against live. Porting ~1,534 T-SQL scripts to Postgres is NOT a mechanical
  find-replace: `IDENTITY` → `SERIAL`/`GENERATED ALWAYS AS IDENTITY`, bracketed
  `[dbo].[Table]` → unquoted/lowercase identifiers, `GETDATE()` → `now()`, `TINYINT` →
  `SMALLINT`, `GO` batch separators → statement-per-`DO $$`-block or plain semicolons,
  and real per-script review for T-SQL-specific functions/hints. Flagged as its own
  dedicated future thread, not started here.