Phase 1 Migration Dry-Run — Execution & Report#

Phase 1 is additive-only per ADR 0004: no writes to legacy tables, and every new Cube-2.0-owned artifact ships as an idempotent CREATE IF NOT EXISTS. The dry-run therefore boils down to three things:

1. Read-only audit against staging to confirm the legacy schema still matches what the entity models assume
2. Idempotent apply of the two new tables against staging
3. Diff of the result to prove nothing legacy was touched

This doc is the reviewer’s execution guide and the report template. Fill in § 4 with actual results before signing off.

1. What ships#

Three SQL files under deploy/sql/:

No ALTER statements. No inserts into legacy tables. Nothing to roll back except dropping the two new tables.

2. Prerequisites#

• Read + db_ddladmin access to a copy of BcomMX_01_04 (not the live cluster; staging DB restored from prod backup)
• sqlcmd or Azure Data Studio with the connection string handed off separately
• Backup taken immediately before § 3.2 (in case § 3.2 is somehow non-idempotent — belt and braces)

3. Execution order#

3.1 Audit (read-only)#

sqlcmd -S <staging-host> -d BcomMX_01_04 \
       -U <reviewer-account> -P '<pw>' \
       -i deploy/sql/phase-1-schema-audit.sql \
       -o dry-run-audit.txt

Capture dry-run-audit.txt. Redact any UserName / Email / password column values — the query already returns only lengths, but scan the output before pasting into § 4.

3.2 Apply new tables (idempotent)#

sqlcmd -S <staging-host> -d BcomMX_01_04 \
       -U <reviewer-account> -P '<pw>' \
       -i deploy/sql/phase-1-add-mnxuserauth.sql

sqlcmd -S <staging-host> -d BcomMX_01_04 \
       -U <reviewer-account> -P '<pw>' \
       -i deploy/sql/phase-1-add-mnxloginactivity.sql

Run the same command a second time. Second run should be a no-op (silent — the IF NOT EXISTS guard skips the CREATE). If the second run raises “table already exists”, the guard is broken — file as a blocker on this ticket.

3.3 Verify no legacy tables were altered#

SELECT name, modify_date
FROM sys.tables
WHERE schema_id = SCHEMA_ID('dbo')
  AND modify_date > '<start-timestamp-of-3.2>'
ORDER BY modify_date DESC;

Pass if this returns exactly two rows: MnxUserAuth and MnxLoginActivity. Any additional legacy table with a fresh modify_date is a critical bug — halt and file a blocker.

4. Report — reviewer fills in#

4.1 Environment#

4.2 Audit results (from § 3.1)#

Query 1 — Password format distribution:

Query 2 — Sample column lengths: paste the 3-row output verbatim.

Query 5 — Row counts: paste the union output verbatim.

Query 6 — Max roles per user: highest role_count = <n>. If > 100, revisit the “flat role array in JWT” decision (would push us to a reference token + role-fetch endpoint).

Query 7 — Admin flag distribution: paste the output. Note company_admin count — those are the users whose JWT will include every module in mod_admin.

Query 9 — MnxModule.IsPermission distribution: paste the output. This confirms the split between navigation and permission modules that the Menu API (BFC-25) filters on.

4.3 Apply results (from § 3.2)#

4.4 No-legacy-touched proof (from § 3.3)#

Paste the sys.tables diff output. Expected: exactly MnxUserAuth + MnxLoginActivity.

4.5 Rollback rehearsal (optional but recommended)#

DROP TABLE dbo.MnxLoginActivity;
DROP TABLE dbo.MnxUserAuth;

Run this against staging after the report is signed off, then re-run § 3.2 to confirm the CREATE scripts still succeed after a drop. Documents that Phase 1 is fully reversible.

5. Sign-off#

• All § 4 queries executed and pasted
• Row counts within expected order-of-magnitude of production
• No PasswordFormat=0 rows
• Max roles per user ≤ 100
• Only two new tables appeared in sys.tables diff
• Reviewer name + date: _______

Once the checklist is complete, transition this ticket to DEPLOYED and merge Phase 1 branch to master.

6. Known limitations to document during sign-off#

• Audit does not validate MnxModuleRelationship shape — the entity is used only by the Navigation API (BFC-25). Add a SELECT TOP 10 * FROM MnxModuleRelationship if the reviewer wants coverage.
• No performance measurement — Phase 1 endpoints are all lightweight reads over already-indexed columns. Load test is deferred to Phase 2 when Sales/RMA hit larger row counts.