Devlog: 2026-03-17 — SQL MI Schema Extraction

What Happened

Connected to the Azure SQL Managed Instance (PS608 / Watermark TPO database) and extracted the complete production schema, replacing the approximation that was reverse-engineered from PowerBuilder DataWindow source files.

Setup

• Enabled the SQL MI public endpoint in the Azure portal
• Added an NSG inbound rule restricting port 3342 to the CTO's home static IP
• Created a read-only SQL auth user (kevin_pssaas_dev) with db_datareader role on PS608
• Connection string stored in .env (gitignored)
• Used mcr.microsoft.com/mssql-tools Docker image for queries (sqlcmd not installed locally)

What Was Extracted

Category	Count
Base tables	585
Views	258
Primary key constraints	550
Foreign key constraints	283 (313 column mappings)
SQL triggers	45
Unique constraints	13
Default constraints	7
Table-valued functions	7
Stored procedures	30 (mostly legacy dt_* source control)

Raw extracts saved to schema-extract/ (gitignored) in pipe-delimited format:

• loan-tables.txt — 952 lines (loan, loan_extended, loan_history, etc.)
• pscat-tables.txt — 1,036 lines (core business tables)
• rm-tables.txt — 4,118 lines (risk management tables)
• system-tables.txt — 2,961 lines (system, B2B, bid, archive, ULDD, etc.)
• primary-keys.txt, table-col-counts.txt, views-inventory.txt, triggers.txt

Key Findings

• 585 actual tables vs ~170 extracted from PB DataWindows — the DataWindow extraction only captured tables that had UI-facing queries. The real schema is 3.4x larger.
• The loan table has 157 columns — the core entity of the entire system.
• Hub entities: pscat_instruments (~20 inbound FKs), pscat_trade_counterparties (~15), pscat_guarantors (~12), pscat_pools (~10), rmcat_profile_names (~10).
• Many tables have 0 rows at Watermark TPO — modules like DocTrack, Bid Packaging, Archive, and Reservations aren't in active use at this customer.
• Top tables by volume: rmcat_possible_loans (732K rows), rmcat_eligible_loans (561K), loan_notional (434K).

Document Updated

Replaced docs-site/docs/legacy/entity-relationship.md entirely with SQL MI ground truth (1,042 lines → 1,033 lines after Mermaid fixes). Includes 5 ER diagrams, full loan table column inventory, FK relationship map, view/trigger/procedure inventories, data volume analysis, and SaaS data model implications.

Issues Found

• Mermaid ER diagrams don't support PK_FK compound annotations — had to use FK only. Fixed in a follow-up commit.
• Entity definitions placed after relationship lines in a diagram caused transient render errors. Removed the duplicates.

Technical Debt

• None introduced. The schema-extract raw files are gitignored reference material, not code.

What's Next

1. Write the BestEx specification
2. Scaffold the .NET solution structure