07 — Postgres → Azure SQL (T-SQL) Conversion: Difficulty + Data-Transfer Runbook

The "how hard is it / what would we do" doc. Scopes converting CARR's Supabase Postgres code + schema to Azure SQL (T-SQL) and moving the data, now that BW IT has locked Azure SQL as the target (6/17 IT call). Grounded in a live census of the CARR DB (drzxmokguptvwptuqjcv, PG17, 2026-06-27) + official Microsoft docs + the tooling landscape.
Triggers: "postgres to azure sql", "t-sql conversion", "how hard is the azure sql migration", "convert plpgsql", "azure sql rls", "data transfer to azure sql".
Supersedes the "Azure SQL = don't" framing in 01_data_layer.md / 05_carr_migration_map.md by client decision — see banner.

⚠ Client decision banner (2026-06-17 IT call)

BW IT (Venkata, BW DBA lead, via Chad; confirmed by Nancy + Heather) made Azure SQL the stake-in-the-sand database target — NOT Postgres. Rationale: BW has no other Postgres anywhere except a small DI team; align to the BW standard. Our own 6/17 deep research (01_data_layer.md) argued the opposite (PG Flexible Server = near lift-and-shift; Azure SQL = "catastrophic" rewrite). That counter-argument is preserved for the record but is no longer the working plan. This doc scopes the Azure SQL rewrite as the accepted path. Flavor (Managed Instance vs SQL Database vs SQL-on-VM) was not pinned on the call — see §6.

✅ Target instance provisioned (2026-07-21)

BW has since stood up the destination Azure SQL database and set up BioCreative's access:

• Server: cbsuse-sql01.database.windows.net · Database: siplatform
• Access: SSMS + MFA + BW credentials; BioCreative login Brian.Elbert@carrbiosystems.com (provisioned by Landon, BW).
• Status: pending credentials. The BW/CARR email credentials for that login are not yet issued (Colby -> Chad Evans). The conversion + data transfer in this doc begin the moment those credentials land — this is the current gating item.

The .database.windows.net endpoint indicates an Azure SQL Database logical server (not Managed Instance / SQL-on-VM); confirm the exact tier with BW cloud ops, and note the MI-vs-SQL-Database feature differences in §6 still apply. Live status + open questions: 15_migration_status.md.

Verdict (the answer, up front)

This is a major rewrite, not a port — and there is no first-party Microsoft tool that does it. The relational shape (86 tables, FKs, indexes) moves with modest effort, but everything that makes CARR's app work lives in Postgres-specific code that has no automated PG→T-SQL converter from Microsoft (SSMA does not support PostgreSQL as a source). The heavy mass:

• 131 functions (103 PL/pgSQL + 28 SQL) → hand/agent-rewrite to T-SQL stored procs/functions.
• 252 RLS policies across 78 tables (120 reference auth.uid()) → re-engineer as Azure SQL SECURITY POLICY + inline predicate functions reading SESSION_CONTEXT.
• 101 views, 46 triggers, 80 JSONB columns, 105 array columns → each needs T-SQL-dialect rewrites.
• In-DB networking (pg_net, http) + supabase_vault + pg_cron (21 jobs) → have no in-database Azure SQL equivalent; they move out of the database (Azure Functions / Key Vault / SQL Agent or Elastic Jobs).

Rough order of magnitude: a multi-week to multi-month effort for the data tier alone, dominated by the 131 functions + 252 policies. This is exactly why automating the repetitive tail (LLM-assisted, with human review) is worth it — and why this isn't a copy-clone.

Live CARR DB census (denominator for every estimate)

Source: drzxmokguptvwptuqjcv, public schema, 2026-06-27.

Object	Count	Conversion weight
Tables	86	Low — DDL maps cleanly
Views	101	Medium — dialect rewrites (casts, ->>, ::, functions)
Functions (PL/pgSQL)	103	High — procedural rewrite to T-SQL
Functions (SQL)	28	Medium
SECURITY DEFINER functions	80	High — execution-context semantics differ
Triggers	46	Medium–High — T-SQL triggers are set-based, not FOR EACH ROW
RLS policies	252 (on 78 tables)	High — full re-engineering
— of which reference auth.uid()	120	High — identity source changes
JSONB columns	80	Medium — JSONB → NVARCHAR(MAX) + JSON_VALUE/OPENJSON
Array columns	105	High — T-SQL has no array type; needs redesign (junction tables / JSON)
gen_random_uuid() defaults	74	Low — → NEWID() / NEWSEQUENTIALID()
Sequences	1	Low
Enum types	0	— (none; good)
pg_cron jobs	21	Medium — → SQL Agent (MI) or Elastic Jobs (SQL DB)
Extensions	http, pg_cron, pg_net, pg_stat_statements, pgcrypto, supabase_vault, uuid-ossp	see §3

1. What carries / adapts / rewrites (per object type)

Construct	Carries?	Azure SQL target	Effort	Notes
Tables, columns, PK/FK, indexes	✅ adapts	CREATE TABLE (T-SQL types)	S	Type remap: uuid→UNIQUEIDENTIFIER, text→NVARCHAR(MAX), timestamptz→DATETIMEOFFSET, boolean→BIT, numeric→DECIMAL, bytea→VARBINARY(MAX).
JSONB columns (80)	⚠ rewrite	NVARCHAR(MAX) + ISJSON check constraint; query via JSON_VALUE/JSON_QUERY/OPENJSON	M	Lose binary/GIN indexing; add computed columns + indexes for hot paths. All ->/->>/@>/jsonb_* rewritten.
Array columns (105)	❌ redesign	Junction table or JSON array in NVARCHAR(MAX)	L	T-SQL has no native array. The single biggest schema-shape change.
PL/pgSQL functions (103)	❌ rewrite	T-SQL stored procs / scalar/TVF functions	L	Different procedural language (RETURN QUERY, FOR..LOOP, RAISE, PERFORM, %ROWTYPE all change).
SQL functions (28)	⚠ rewrite	T-SQL inline TVFs	M	Mostly dialect + set semantics.
Triggers (46)	⚠ rewrite	T-SQL AFTER/INSTEAD OF triggers (set-based on inserted/deleted)	M–L	Postgres FOR EACH ROW + NEW/OLD → T-SQL operates on pseudo-tables; row-loop logic must be re-expressed set-based.
RLS policies (252)	❌ re-engineer	CREATE SECURITY POLICY + inline TVF predicate	L	See §2. Postgres has per-command USING/WITH CHECK; Azure SQL uses FILTER + BLOCK predicates referencing SESSION_CONTEXT.
Views (101)	⚠ rewrite	T-SQL views	M	Dialect: :: casts, coalesce/nullif ok, ->>, limit→TOP/OFFSET FETCH, boolean handling.
Sequences (1) + gen_random_uuid() (74)	✅ adapts	IDENTITY/SEQUENCE; NEWID()/DEFAULT	S
now(), interval, casts	⚠ rewrite	SYSDATETIMEOFFSET(), DATEADD, CAST/CONVERT	S	Pervasive but mechanical.
ON CONFLICT ... DO UPDATE	⚠ rewrite	MERGE (or upsert pattern)	M	Appears across upsert-heavy EFs/functions.

2. RLS — the hard structural change (252 policies)

Postgres couples Auth↔RLS via auth.uid(). Azure SQL has RLS too, but the model differs:

• Postgres today: CREATE POLICY ... USING (rep_id = auth.uid()) — per-table, per-command, identity read from the Supabase JWT.
• Azure SQL target: a predicate inline table-valued function + a SECURITY POLICY binding FILTER (read) and BLOCK (write) predicates to each table. Identity is read from SESSION_CONTEXT (Microsoft's recommended pattern for SaaS/multi-tenant isolation), which the API middleware sets per connection: EXEC sp_set_session_context @key=N'user_id', @value=....

So all 252 policies become: (a) one (or a few shared) predicate function(s), (b) a SECURITY POLICY per protected table, and (c) middleware that injects identity into SESSION_CONTEXT on every request. The 120 policies that read auth.uid() all repoint to SESSION_CONTEXT('user_id'). This is the same identity-injection story as 01/05, just expressed in T-SQL instead of SET LOCAL session GUCs. This work depends on the auth swap (Supabase Auth → Entra ID) and the custom API middleware — both out of scope for this doc but prerequisites for RLS to function.

3. The extensions reality — these leave the database entirely

CARR's DB uses 7 non-plpgsql extensions; several have no in-database Azure SQL equivalent:

Extension	Use in CARR	Azure SQL story
pg_cron (21 jobs)	scheduled jobs in-DB	No in-DB scheduler. → SQL Agent jobs (Managed Instance) or Elastic Jobs (SQL Database). 21 jobs to recreate + re-point.
pg_net / http	async/sync HTTP from DB (1+ function calls webhooks)	No in-DB HTTP. Logic moves to Azure Functions / Logic Apps; DB no longer calls out.
supabase_vault	API-key storage (HeyReach, EmailBison, Brave)	→ Azure Key Vault, read by the app/Functions via Managed Identity (not from SQL).
pgcrypto	hashing/crypto	→ T-SQL HASHBYTES/ENCRYPTBYKEY or app-layer.
uuid-ossp	UUID gen	→ NEWID() / NEWSEQUENTIALID().
pg_stat_statements	perf telemetry	→ Query Store (built in).

Implication: the "database does work on its own" model (cron + HTTP + vault inside Postgres) does not survive. Those behaviors get re-homed in Azure Functions + SQL Agent/Elastic Jobs + Key Vault. That's an architecture change, not a translation — and it's why the EFs (next) and scheduling are a parallel workstream, not part of the SQL conversion.

4. Edge Functions are not in SQL-conversion scope

The 21 Deno/TS edge functions are application code, not database code. They port to Azure Functions (which also runs the TypeScript) — a separate workstream, not part of the SQL conversion. The SQL conversion only touches SQL embedded inside the DB objects (functions, views, policies, triggers), plus any inline SQL strings the EFs send.

5. Tooling landscape (verified 2026-06-27)

Tool	Does it convert PG → Azure SQL?	Reality
Microsoft SSMA	❌ No	MS Learn SSMA page (updated 2026-04-24) lists sources as Access, Db2, MySQL, Oracle, SAP ASE — PostgreSQL is not a supported source. (A Feb-2026 trade-press piece claiming PG support is in error vs the official docs.)
Azure DMS	❌ No	DMS does homogeneous PG→PG (and other matched pairs); it does not convert PG→Azure SQL.
Ispirer Toolkit	⚠ Partial (commercial)	Markets a "PostgreSQL → Azure SQL Database" path; claims ~50% time reduction via automation — i.e., automates schema/data + a chunk of code, leaves the hard procedural/RLS tail manual.
DBConvert	⚠ Partial (commercial)	PG↔SQL Server schema + data + optional two-way sync; strong on tables/data, weak on procedural logic.
LLM-assisted conversion	✅ for the tail	Best fit for the 103 PL/pgSQL functions + 252 RLS policies + Supabase-isms that commercial tools leave behind, and re-runnable as migrations evolve.

Net: no turnkey path exists. The pragmatic combo = a commercial tool (or pg_dump-driven script) for bulk DDL + data, plus LLM-assisted conversion for the procedural/RLS/Supabase-ism tail, plus manual review.

6. Which Azure SQL flavor? (open question for BW)

The 6/17 call said "Azure SQL Server / Azure SQL / Azure SQL Managed Instance" loosely — never pinned. It matters a lot:

	Azure SQL Managed Instance	Azure SQL Database	SQL Server on VM
Scheduler for our 21 cron jobs	SQL Agent (native) ✅	Elastic Jobs (external, more setup) ⚠	SQL Agent ✅
Cross-DB queries / linked servers	✅	❌	✅
Closeness to full SQL Server	Highest (PaaS)	Single-DB PaaS	Full (IaaS, you patch it)
Fit for CARR	Best landing	Cheaper, more constraints	Defeats BW managed-cloud mandate

Recommendation: Azure SQL Managed Instance — SQL Agent absorbs the 21 cron jobs with least redesign, and the cross-DB/T-SQL surface is closest to what CARR's functions assume. Take back to Venkata/Chad: "Which Azure SQL — Managed Instance, SQL Database, or SQL-on-VM?" — resourcing depends on it.

7. Data-transfer runbook (schema → data → validate → cutover)

Data is tiny (~4,184 companies / ~104K contacts, <500 MB) — the data move is easy; the schema/code is the work. Because DMS/SSMA don't do PG→Azure SQL, the path is:

1. Convert schema to T-SQL (LLM-assisted conversion + tool, reviewed) → run on an empty Azure SQL target.
2. Convert code objects (functions, views, triggers, RLS) → deploy to target; compile-check.
3. Bulk-load data — Ispirer/DBConvert, Azure Data Factory (PG source → Azure SQL sink), or scripted COPY-to-CSV → bcp/BULK INSERT. Map types (UUID, JSONB→NVARCHAR, arrays→junction/JSON) on the way in.
4. Re-home out-of-DB behaviors — recreate 21 jobs as SQL Agent/Elastic Jobs; move pg_net/http calls to Azure Functions; secrets to Key Vault.
5. Validate — row counts + checksums per table; spot-check JSON/array reshaping; confirm RLS via SESSION_CONTEXT returns the right rows per rep; confirm jobs fire.
6. Cutover — freeze writes (short maintenance window is fine at this size), final delta load, point the new API middleware at Azure SQL, smoke-test, open.

Downtime tolerance is high (small data) → no need for near-zero-downtime logical replication. The risk is correctness of the converted code, not data volume.

8. Difficulty verdict (per area + overall)

Area	Difficulty	Why
Schema/DDL (tables, keys, indexes)	S–M	Mechanical type remap; arrays (105 cols) are the exception.
Data move (<500 MB)	S	Tool or scripted; trivial volume.
Views (101)	M	Dialect rewrites, volume.
Functions (131)	L	Procedural rewrite; the bulk of the effort.
Triggers (46)	M–L	Row→set semantics.
RLS (252 policies)	L	Re-engineer + depends on Entra/middleware.
Out-of-DB (cron 21, pg_net/http, vault)	M (but architecture change)	Re-home, not translate.
Overall data tier	Multi-week → multi-month	Dominated by functions + RLS; gated by the auth/middleware prerequisite.

This is materially harder than the original PG-Flexible-Server plan would have been — which is the cost of the BW standardization decision, and the case for automating the repetitive tail with LLM-assisted conversion.

Open questions

• Azure SQL flavor? MI vs SQL Database vs VM (§6). Gates scheduler + cross-DB approach.
• Who owns the auth/middleware prerequisite? RLS can't function until Entra + SESSION_CONTEXT injection exists.
• Buy vs build for the bulk? License Ispirer/DBConvert for DDL+data and reserve LLM-assisted conversion for the tail, or LLM-only?
• Array redesign policy — junction tables (relational-pure) vs JSON columns (least churn) for the 105 array columns?

Research anchors

• SSMA supported sources (no PostgreSQL): learn.microsoft.com/en-us/sql/ssma/sql-server-migration-assistant (updated 2026-04-24).
• Azure SQL RLS + SESSION_CONTEXT: learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security; MS Q&A recommends SESSION_CONTEXT() for tenant isolation.
• MI vs SQL Database feature comparison: learn.microsoft.com/en-us/azure/azure-sql/database/features-comparison.
• Ispirer PG→Azure SQL: ispirer.com/products/migrate-postgresql-to-microsoft-azure-sql (~50% automation claim).
• Live census: CARR DB drzxmokguptvwptuqjcv, 2026-06-27 (queries in this session).

01 — Data Layer (Azure SQL vs Postgres, migration, RLS + identity)

Deep-research synthesis on CARR's data-tier migration. Azure feature GA/preview status is point-in-time (2026-06-17) — re-verify before relying on it.
Triggers: "azure sql vs postgres", "azure database for postgresql", "rls entra", "pg_cron azure", "pgjwt bypassrls".

Verdict

Azure Database for PostgreSQL (Flexible Server) [GA] is the mandatory target. Azure SQL is the wrong engine. Azure SQL = T-SQL → CARR's 88 migrations, PL/pgSQL triggers, pg_cron, JSONB, and future pgvector would all need rewriting ("catastrophic"). Flexible Server runs community Postgres → schema, drivers, pg_dump all carry over.

What carries over vs breaks

• Carries over (GA): PL/pgSQL triggers (incl. 300-char LinkedIn guard), stored procs, JSONB, generated columns, native RLS, pg_stat_statements.
• Extensions via azure.extensions allowlist: pg_cron (GA — also needs shared_preload_libraries + server restart), pgvector (GA, listed as vector, DiskANN at scale), pg_trgm (GA).
• BLOCKED (critical): pgjwt is not supported, and BYPASSRLS is restricted on Flexible Server. These two are exactly what self-hosted Supabase's GoTrue/PostgREST middleware require → see "Self-hosted Supabase" below.

RLS + identity — the hard part

Supabase couples Auth↔RLS via auth.uid(). On Azure that function won't exist. The pattern that replaces it (session-variable injection):
1. Custom API middleware validates the Entra ID JWT, extracts the user's Object ID (OID).
2. Per transaction: SET LOCAL app.current_user_id = '<entra-oid>';
3. Rewrite every RLS policy: USING (rep_id = auth.uid()) → USING (rep_id = NULLIF(current_setting('app.current_user_id', true), '')::uuid).

This is the single biggest data-model touchpoint (the actor_id/skipped_by stamping pattern). External (non-BW) reps: report 01 leans Entra External ID (CIAM/B2C); report 04 leans B2B guest — unresolved, see 04 + open questions.

Self-hosted Supabase on Azure — NOT viable (correction)

Tempting "lowest-disruption" path (keep supabase-js contract) is blocked on managed Azure PG: Supabase init needs BYPASSRLS (restricted) and pgjwt (unsupported). Only workarounds are running raw Postgres on an IaaS VM (defeats BW's managed-cloud mandate) or forking Supabase (maintenance nightmare). → Embrace an Azure-native data tier with a custom API. (This supersedes the earlier "self-hosted Supabase is the lean" framing.)

Migration mechanics

Data is tiny (~4K companies / 104K contacts, <500 MB). Two paths: pg_dump/pg_restore (offline, simplest) or Azure Database Migration Service (DMS) for near-zero-downtime logical replication (BW will likely want DMS since reps use it daily). DMS migrates data only → dump/restore schema (pg_dump -s) first, then validate pg_cron, RLS, and sequences post-cutover. Full runbook in the report §5.

Ecosystem mapping (corrections to BW's proposal)

• Realtime: BW said Event Grid — wrong for client WebSockets. Use Azure Web PubSub / SignalR. Event Grid is backend event routing only.
• Storage: Azure Blob Storage (not SharePoint — throttling/Graph overhead).
• Vault: Azure Key Vault + Managed Identity.
• Cron: keep pg_cron (least change) over Logic Apps.

Sizing/cost (projection)

General Purpose D2ds_v4 (2 vCore/8GiB) is ample; Burstable B2ms (~$99/mo) could handle it but BW SLA norms favor GP. Premium SSD v2 32–64 GiB ≈ $10–20/mo. Backups 35 days (GA).

Open questions

• External-rep identity model in RLS: CIAM vs B2B guest (gate this early — it blocks the RLS rewrite).
• Which CI tool runs the idempotent SQL migrations post-Supabase-CLI (→ 04, GitHub Actions).

13 — Postgres vs Azure SQL: Full Reanalysis

The "now that we understand the Azure SQL path, is the engine choice still right?" doc. On 6/17 BW locked Azure SQL over the managed-Postgres recommendation (01). We've since fully scoped the Azure SQL path — the conversion runbook (07) and the pre-trained-knowledge probe (10). This doc revisits the decision with that new information: balanced pros/cons of each engine, what actually changed, and an updated recommendation.
Triggers: "postgres vs azure sql reanalysis", "should we keep postgres", "revisit the database decision", "azure sql vs postgres pros cons", "updated db recommendation".
Reads with: 01_data_layer.md (the original PG-wins verdict), 07_azure_sql_conversion.md (the Azure SQL cost), 05_carr_migration_map.md.

---

TL;DR — the recommendation, up front

1. On pure technical merits, managed Postgres (Azure Database for PostgreSQL, Flexible Server) is still the better engine for CARR — by a wide margin on the data tier. The full conversion scoping (07) didn't overturn that; it quantified it.
2. But the decision was never purely technical — BW's standardization mandate (one DB platform their DBAs support) is a legitimate enterprise driver we don't control, and it's already a stated decision.
3. What genuinely changed: we now know the Azure SQL path is expensive but tractable, not "catastrophic" (01's word). Pre-trained model knowledge (10) + sqlglot + LLM-assisted conversion de-risk the rewrite enough that it's a bounded, verification-dominated problem on tiny data — not a blind multi-quarter slog.
4. Net recommendation: present the evidence-based case for managed Postgres once, with the concrete cost delta below. If BW holds firm on Azure SQL — the likely outcome — proceed on that path with eyes open, executing via LLM-assisted conversion. The engine question should be settled once, not relitigated repeatedly.

The one-liner for Jake/BW: "Postgres is still the cleaner engine and here's exactly what the Azure SQL standardization costs in time and risk — but if BW's standard is Azure SQL, we've now de-risked that path and can execute it. Your call; we're ready either way."

---

The critical reframe — what is actually being compared

A huge share of the migration cost is identical under both engines and therefore not a differentiator:

• Leaving Supabase entirely — auth swap (Supabase Auth → Entra ID), a custom API middleware tier, Edge Functions → Azure Functions, Realtime → Web PubSub, Vault → Key Vault, storage → Blob. All required either way, because BW is moving off Supabase regardless of DB engine.
• Azure provisioning, networking, governance, Foundry — same under both.

So the entire decision reduces to the data tier: how much does CARR's in-database code cost to move? That's where the two engines diverge sharply — and it's the only axis this doc weighs.

---

Option A — Keep Postgres (Azure Database for PostgreSQL, Flexible Server) [GA]

The community-Postgres managed service. CARR's schema, drivers, and pg_dump carry over.

Pros
- Near lift-and-shift on the data tier. PL/pgSQL functions (131), triggers (46), views (101), JSONB (80 cols), array columns (105), generated columns, native RLS — all carry over essentially as-is. No dialect rewrite.
- pg_cron (21 jobs), pgvector (future RAG), pg_trgm, pgcrypto all supported via the azure.extensions allowlist. The scheduled-job layer stays in-DB.
- RLS change is mechanical, not structural. Only the identity source changes: auth.uid() → NULLIF(current_setting('app.current_user_id',true),'')::uuid via SET LOCAL. Same 252 policies, same logic, find-and-replace shape — not a re-engineering.
- Lowest risk + fastest to production. Tiny semantic-equivalence surface; the DB behaves the same because it is the same engine.
- Preserves Lovable / supabase-js-style velocity with the least disruption to how CARR is built today.

Cons
- pgjwt unsupported + BYPASSRLS restricted on managed Azure PG — but these only matter to self-hosted Supabase middleware, which we're replacing with custom middleware anyway, so the practical bite is small (see 01 §self-hosted correction).
- Off BW's standard. BW has essentially no other Postgres (only a small DI team). CARR would be a one-off platform their DBAs don't otherwise support — the core institutional objection.
- Tooling/skills mismatch with BW's Microsoft-centric estate (SSMS, SQL Agent, the Azure SQL ecosystem their team already runs).
- Perceived as "the vendor pushing their preference" rather than aligning to enterprise standards — a relationship/optics cost even when technically right.

Difficulty (data tier): ~2–3/10. The hard parts (auth/middleware) are shared with Option B, not Postgres-specific.

---

Option B — Move to Azure SQL (Managed Instance or SQL Database) [GA] — BW's locked choice

A different engine (T-SQL). CARR's relational shape moves; everything procedural is rewritten.

Pros
- BW standard. One DB platform their DBAs support, one backup/HA/monitoring/security posture, one toolchain (SSMS, SQL Agent/Elastic Jobs, Defender for SQL). The decisive enterprise argument.
- Native fit with BW's Microsoft direction — Entra, Key Vault, Azure Functions, and Foundry all sit naturally beside Azure SQL; future BW-built AI tooling lands on the same stack.
- First-class SECURITY POLICY + SESSION_CONTEXT RLS — a Microsoft-recommended multi-tenant pattern; once converted it's idiomatic and well-supported.
- De-risked since 6/17 (the real update): the PG→T-SQL conversion knowledge is pre-trained in the models (10 — Claude/Gemini 6/6, GPT ~5.5/6, no hints), sqlglot handles the mechanical ~50–60% deterministically, and LLM-assisted conversion handles the procedural/RLS tail. Tiny data (<500 MB) makes the data move a non-event.

Cons
- Major rewrite of the in-DB code, not a port: 131 functions (103 PL/pgSQL), 252 RLS policies (120 read auth.uid()), 46 triggers (row-level → set-based over inserted/deleted), 80 JSONB cols → NVARCHAR(MAX)+OPENJSON (lose GIN indexing), 105 array columns → junction tables/JSON (the single biggest schema-shape change). ON CONFLICT→MERGE, now()/intervals/casts pervasive-but-mechanical.
- No first-party converter. SSMA doesn't support Postgres as a source; DMS doesn't convert dialects. The procedural/RLS tail is a manual + LLM-assisted build.
- Semantic-equivalence verification is the real cost (6.5/10). Proving 131 fns + 252 policies + 46 triggers behave identically — not just compile — is a genuine QA program. This is where a migration "looks done" but quietly returns wrong rows to a rep.
- In-DB extensions leave the database — pg_net/http/supabase_vault/pg_cron have no in-DB Azure SQL equivalent; they move to Azure Functions / Key Vault / SQL Agent or Elastic Jobs (more moving parts).
- Team ramp on T-SQL + Azure — building and operating on a new engine/platform is a learning curve on a client's production migration.

Difficulty (data tier): ~8/10 end-to-end (dominated by semantic-equivalence verification plus the Azure-side and BW-gated work).

---

Head-to-head (data tier only — the shared Supabase-exit cost is equal)

Dimension	Keep Postgres (Azure DB for PG)	Move to Azure SQL
In-DB code rewrite (131 fns / 46 triggers / 101 views)	✅ carries over	❌ full T-SQL rewrite
RLS (252 policies)	⚠ mechanical repoint	❌ re-engineer (SECURITY POLICY + SESSION_CONTEXT)
JSONB (80) / arrays (105)	✅ native	⚠ JSON / ❌ redesign
Scheduled jobs (21 pg_cron)	✅ stays in-DB	⚠ moves to SQL Agent/Elastic Jobs
Data move (<500 MB)	✅ trivial	✅ trivial
Semantic-equivalence risk	very low	the main cost (6.5/10)
Time-to-production (data tier)	weeks	weeks-to-months
BW standardization / DBA support	❌ one-off	✅ aligned
Microsoft-stack / Foundry adjacency	neutral	✅ native
De-risked & executable?	✅ easily	✅ now yes (pre-trained models + sqlglot + LLM-assisted tail)
Data-tier difficulty	~2–3/10	~8/10

---

What changed vs the 6/17 verdict (and what didn't)

• Didn't change: the engineering answer. Managed Postgres is still the lower-cost, lower-risk data tier. 01's technical reasoning stands.
• Changed: the characterization of the Azure SQL path. 01 called it "catastrophic." After the full scoping (07), the honest word is "expensive but tractable" — a bounded, verification-dominated effort on tiny data, not an open-ended rewrite. That materially narrows the practical gap and makes accepting BW's choice defensible.
• Sharpened: we can now put concrete numbers on the cost of the standardization decision (131/252/46/80/105, the 8 vs 2–3) that we lacked on the 6/17 call. That's the new ammunition for one informed conversation.
• Forward-looking (see 14): external trend research confirms Postgres leads developer adoption (SO 2025: 55.6% vs SQL Server 27%) and owns the open AI/MCP/pgvector ecosystem — but SQL Server 2025 added native vector/DiskANN/AI-model-mgmt/Copilot, so "Azure SQL can't do AI" is closed. The honest long-term cost of Azure SQL is the ongoing T-SQL velocity tradeoff + loss of in-DB pg_net/pg_cron/vault, not a capability ceiling.

---

The recommendation in practice

1. One informed case to BW (not a fight). Present the head-to-head above to BW IT / Jake: "Standardizing on Azure SQL is a legitimate call. Here's precisely what it costs vs a supported managed-Postgres option — this much rewrite, this much verification, this much longer to production. Azure Database for PostgreSQL is a fully-managed, first-party Azure service [GA] — it may satisfy 'on Azure, supported' without the rewrite. Worth a look before we commit."
2. If BW holds firm on Azure SQL (expected): accept cleanly and execute. The path: LLM-assisted conversion for the procedural/RLS tail, supervised conversion plus an evaluation loop to retire the semantic-equivalence risk, and the joint, BW-resourced Azure-side work. Don't reopen the engine question after this.
3. Either way, the shared Supabase-exit work (auth/middleware/Functions/networking) proceeds in parallel — it's required regardless and shouldn't wait on the engine decision.

Bottom line: the reanalysis confirms Postgres is technically better and confirms Azure SQL is now executable. Lead with the evidence once; then let BW's standardization mandate decide, and be genuinely ready to deliver on the path they choose.

---

Cross-links

• 01_data_layer.md — original PG-wins verdict + the RLS/identity + self-hosted-Supabase corrections.
• 07_azure_sql_conversion.md — the conversion census + runbook (the Option-B cost detail).
• 10_llm_knowledge_and_oss_landscape.md — why conversion is de-risked (pre-trained model knowledge + sqlglot).
• 14_strategic_trend_analysis.md — the forward-looking industry-trend view.
• 05_carr_migration_map.md — component-by-component Azure mapping.

14 — Strategic & Trend Analysis: Postgres vs Azure SQL for an AI-Native Roadmap

The "which way is the industry actually going, and which is easier to build AI on long-term?" doc. 13 weighed the migration cost; this doc answers the forward-looking question Brian raised: is Postgres the more modern direction, is Azure SQL chosen for security/enterprise reasons, and — critically — will building AI tools, APIs, MCP servers, and edge-function-style integrations the way CARR's app is built become harder on one engine than the other over the long run? Backed by live external research (Firecrawl + Brave, 2026-06-27), with sources cited.
Triggers: "is postgres more modern", "database trends 2025 2026", "azure sql vs postgres ai tooling", "mcp server postgres sql server", "long term ai roadmap database", "which database for ai agents".
Reads with: 13_postgres_vs_azure_sql_reanalysis.md (the engine reanalysis this informs), 02_ai_foundry_agents.md (Foundry).

---

TL;DR — the strategic read

1. Postgres is, by the numbers, the more modern / trend-leading engine — #1 in developer adoption two years running and the de-facto default for new cloud-native and AI builds. The momentum is real and toward Postgres.
2. But "Azure SQL can't do AI" is no longer true. SQL Server 2025 (GA 2025-11-18) shipped a native vector type, DiskANN vector indexing, in-engine AI-model management, and GitHub Copilot in SSMS — Microsoft explicitly repositioned it as "the AI-ready enterprise database." The capability gap is narrowing, not widening.
3. BW's Azure SQL choice is a defensible enterprise-standardization + security/compliance call, not a technical mistake — single-vendor support, Entra-native security, Defender for SQL, Fabric/Foundry adjacency.
4. The real long-term cost is an engineering-velocity tradeoff, not an industry trend. CARR's app — its edge functions, RAG layer, and Supabase/Postgres patterns — is Postgres-shaped. On Azure SQL, ongoing development works against a different dialect and toolchain, even though the platform itself is capable. That ongoing build friction, not a feature ceiling, is the honest long-term concern.

One-liner: Postgres is where the open AI ecosystem and developer mindshare are heading; Azure SQL has closed most of the AI feature gap and wins on enterprise standardization. The forward-looking risk for CARR-on-Azure-SQL is the ongoing build friction of a second dialect, not the database's ability to do AI.

---

Q1 — Is Postgres the more modern way? Are trends going that way?

Yes, clearly, on adoption and ecosystem momentum.

• Developer adoption: Stack Overflow 2025 Developer Survey — PostgreSQL 55.6%, the most-used database, #1 for the second year running; MySQL 40.5%, Oracle 10.6%. In the 2024 survey PostgreSQL led at 49% vs SQL Server 27%. Postgres dominates raw developer mindshare and new-project adoption. [SO 2025; tech-insider.org 2026]
• Popularity ranking: DB-Engines (mid-2026) has SQL Server #3 (~698) and PostgreSQL #4 (~688) — a tightening race; PostgreSQL was flagged the "second biggest climber" in early 2025 and its score keeps rising while SQL Server's drifts. [DB-Engines; rapydo.io]
• Innovation cadence: Postgres ships a major version every year (15→16→17→18, 19 beta mid-2026) with a 5-year support window — predictable, transparent. The extension model (pgvector, PostGIS, TimescaleDB, JSONB) "turns one database into many." [postgresql.org; rapydo.io]
• Cloud + AI gravity: every major cloud courts managed Postgres; the AI/RAG ecosystem treats pgvector as the default vector store ("if Postgres is your data platform, pgvector is the default vector store — same backups, same tools, same access controls"). [firecrawl.dev best-vector-databases; digitalapplied.com; zenml.io]

Verdict: for greenfield, cloud-native, and AI-native work, the industry direction is Postgres. This is the trend Brian sensed, and the data confirms it.

---

Q2 — Is Azure SQL chosen for security/enterprise reasons? (and has it caught up on AI?)

Yes on enterprise/security — and it has substantially caught up on AI.

Why an enterprise like BW standardizes on Azure SQL is legitimate:
- Single-vendor support + one operational standard — one backup/HA/DR posture (Always On), one toolchain (SSMS, SQL Agent), one security/compliance model their DBAs already run. BW has essentially no other Postgres; CARR-on-Postgres would be an unsupported one-off.
- Security/compliance depth — Entra ID integration, Defender for SQL, Always Encrypted, fine-grained fixed server roles (new in 2025), and tight Microsoft-stack governance. SQL Server 2025 is "better equipped for modern security and compliance requirements." [cegal.com; learn.microsoft.com]
- Microsoft-stack adjacency — sits natively beside Entra, Key Vault, Azure Functions, Microsoft Fabric, and Foundry, where BW's company-wide AI ambitions live.

The AI-parity update (the important nuance): SQL Server 2025 (GA 2025-11-18, "biggest release in years") added:
- a native vector data type (binary-optimized, JSON-exposed),
- vector functions + approximate-nearest-neighbor vector indexing built on Microsoft's DiskANN,
- in-engine external AI-model management (CREATE EXTERNAL MODEL to call embedding/inference endpoints),
- GitHub Copilot inside SSMS, plus a Standard-edition bump to 256 GB RAM / 32 cores. [learn.microsoft.com "What's new in SQL Server 2025"]

So the 01-era framing ("Azure SQL is the wrong engine for AI") is out of date: Microsoft has explicitly made Azure SQL / SQL Server an "AI-ready enterprise database." For doing RAG and vector search, Azure SQL is now a first-class option, not a dead end.

---

Q3 — Long-term: will building AI tools, APIs, MCPs, and edge-functions be harder on Azure SQL?

This is the decisive question, and the honest answer splits into "industry-general" (gap closing) vs "team-specific" (real, persistent friction).

The MCP / AI-agent ecosystem — both supported, Postgres more open

• Postgres: mature, open MCP servers (e.g. crystaldba/postgres-mcp "Postgres MCP Pro" — read/write, index tuning, health checks for AI agents) + EDB pushing a "Postgres-native" MCP approach to eliminate SQL hallucinations. The open-source agent ecosystem (LangChain, Supabase, pgvector) is Postgres-first. [github.com/crystaldba/postgres-mcp; enterprisedb.com]
• Azure SQL: Microsoft and third parties (Skyvia, et al.) now ship SQL Server MCP servers; Microsoft is actively building MCP access for SQL databases for AI agents. [skyvia.com; cloudwars.com]
• Read: both engines can be driven by AI agents via MCP today. Postgres has more, more-open options; Azure SQL is well-supported and Microsoft-backed. Not a long-term blocker either way.

Where Azure SQL is structurally more work long-term

• The in-database integration model goes away. CARR's pattern leans on in-DB pg_net/http (outbound calls), pg_cron (21 scheduled jobs), and supabase_vault — none of which have an in-database Azure SQL equivalent. They move out to Azure Functions / SQL Agent or Elastic Jobs / Key Vault. That's more moving parts and more glue code for every future automation, forever — not just at migration. (See 07 §3.)
• Edge functions → Azure Functions is a permanent platform shift: the lightweight Deno/supabase-js EF pattern becomes a heavier Azure Functions deployment story.

Where the real long-term cost actually lives — the second-dialect velocity tradeoff

• CARR's stack is Postgres-shaped. Its 20 edge functions, 131 database functions, 252 RLS policies, and RAG layer (pgvector) are all authored in and reason about Postgres.
• On Azure SQL, every future CARR AI feature, API, or agent integration is built against a second dialect — T-SQL procedural quirks, SECURITY POLICY/SESSION_CONTEXT RLS, OPENJSON instead of native JSONB, no array type. Automated conversion handles the one-time migration; it does not remove the ongoing friction of building new things in a second dialect.
• This is the genuine long-term concern — and it's about ongoing development friction, not the database's capability. The industry can build AI on Azure SQL just fine; teams whose muscle memory is Postgres will simply be slower and more error-prone doing so.

---

How this updates the 13 recommendation

• Reinforces the technical case for managed Postgres (Azure Database for PostgreSQL) — the trend data + Postgres-fit make it the lower-friction long-term home, especially for an AI roadmap built on Postgres patterns.
• Tempers the anti-Azure-SQL argument — SQL Server 2025's native vector/AI/MCP closes the "can't do AI" fear; the honest objection is the second-dialect velocity tradeoff and the in-DB-integration loss, not a capability ceiling.
• Net: 13's "make one informed case for Postgres, then accept Azure SQL and execute" stands — now with sharper ammunition for the Postgres case (developer-trend + AI-ecosystem + Postgres-fit) and a fair acknowledgment that Azure SQL is a capable, secure, enterprise-aligned target if BW holds firm. If they do, budget for an ongoing T-SQL learning/velocity cost, not just the one-time conversion.

---

Sources (external, 2026-06-27 sweep)

• Stack Overflow 2025 Developer Survey — database usage (survey.stackoverflow.co/2025).
• "PostgreSQL vs SQL Server 2026" — DB-Engines ranks, SO %, licensing, SQL Server 2025 AI features (tech-insider.org, 2026-06-04).
• "PostgreSQL's Surging Popularity and Innovation" — SO 55.6%, DB-Engines climb, release cadence (rapydo.io, 2025-09-30).
• "What's new in SQL Server 2025" — native vector type, DiskANN, external AI models, Copilot in SSMS, security roles (learn.microsoft.com).
• "A deep dive into Microsoft SQL Server 2025" — Entra + security/compliance posture (cegal.com).
• Vector-DB landscape — pgvector as default vector store (firecrawl.dev, digitalapplied.com, zenml.io).
• MCP servers — github.com/crystaldba/postgres-mcp; enterprisedb.com (Postgres+MCP); skyvia.com, cloudwars.com (SQL Server MCP).

Honesty tags: SQL Server 2025 vector index is GA-with-preview-flag (PREVIEW_FEATURES scoped config) as of the 2025 GA notes; recheck before relying on it. DB-Engines/SO figures are point-in-time (mid-2026) and shift; re-pull before a client conversation.

Cross-links

• 13_postgres_vs_azure_sql_reanalysis.md — the engine reanalysis (cost side); this doc is the trend/forward-looking side.
• 02_ai_foundry_agents.md — Foundry, where BW's AI-agent ambitions sit.
• 07_azure_sql_conversion.md §3 — the in-DB extensions that leave the database.

10 — LLM Knowledge Probe + OSS Landscape (does the context already exist?)

The de-risking doc. Answers a core question: is the Postgres→Azure SQL conversion knowledge already baked into frontier LLMs, or must it be built from scratch? Plus: what open-source tooling to stand on, and which public Microsoft docs feed an LLM-assisted conversion. Live-tested against frontier models (Claude, GPT-4o, Gemini), not assumed.
Triggers: "do the models already know this", "llm knowledge azure sql", "open source converter", "sqlglot", "what to ask BW for".
Reads with: 07_azure_sql_conversion.md (the conversion work), 13_postgres_vs_azure_sql_reanalysis.md (the engine decision).

Headline (the answer)

The conversion knowledge is strongly pre-trained into all three frontier models we tested. Given raw PostgreSQL constructs with no hints, Claude, GPT-4o, and Gemini each independently produced the correct Azure SQL idioms — CREATE SECURITY POLICY + SESSION_CONTEXT for RLS, INSTEAD OF triggers over the inserted pseudo-table, JSON_VALUE/OPENJSON for JSONB, "no native array type → JSON/junction", and MERGE for upserts. This is not teaching a model T-SQL from zero. This materially de-risks the conversion: the job is context-engineering + correctness-validation, not building dialect knowledge.

But the probe also confirmed exactly where the models are plausible-but-needs-verification. That's the gap a deterministic ruleset + validation loop must close, not the model's baseline fluency.

The probe — method

• Where: frontier models via API. Date: 2026-06-27.
• Models: claude-sonnet, gpt-4o, gemini-flash.
• Battery: 6 representative CARR constructs, identical prompt, temp 0.1, "answer only from built-in knowledge, no hedging": (1) PL/pgSQL RETURN QUERY function, (2) auth.uid() RLS policy, (3) BEFORE INSERT FOR EACH ROW trigger, (4) JSONB ->> + @> query, (5) text[] array ANY(), (6) ON CONFLICT DO UPDATE upsert.
• Rubric: correct idiom / semantically faithful / idiomatic / gotcha-flagged.

The probe — scorecard

#	Construct	Claude	GPT-4o	Gemini-flash	Where they agreed (= high confidence)
1	PL/pgSQL RETURN QUERY fn	✅ inline TVF	⚠ stored proc	✅ inline TVF	All convert; GPT chose a proc (loses composable SETOF semantics) — Claude/Gemini's RETURNS TABLE TVF is the faithful answer.
2	RLS auth.uid()	✅	✅	✅ (most complete)	All three reached SECURITY POLICY + predicate TVF + SESSION_CONTEXT unprompted. Gemini added SCHEMABINDING.
3	BEFORE row trigger	✅ THROW	✅ RAISERROR+rollback	✅ THROW	All map BEFORE→INSTEAD OF, NEW→inserted. Correct.
4	JSONB ->> + @>	✅	✅	✅	All use JSON_VALUE. All simplified @> to a single key — multi-key containment is the verify-spot.
5	text[] array	✅ JSON/junction	⚠ STRING_SPLIT	✅ OPENJSON	All flag "no native array." Storage choice (JSON vs delimited vs junction) is a human policy call, not auto.
6	ON CONFLICT upsert	✅ MERGE	✅ MERGE	✅ MERGE	Unanimous, correct.

Net per model: Claude 6/6 (most consistently faithful) · Gemini-flash 6/6 (tied-best; strongest RLS answer) · GPT-4o ~5.5/6 (the proc-vs-TVF downgrade is the only real miss). Consensus across three independent models on the hard idioms (RLS, triggers, JSON, MERGE) is the strongest possible signal the knowledge is real and stable, not a single-model fluke.

What this means for the build

• Skip: teaching the model T-SQL syntax / Azure SQL idioms. It's pre-trained. No giant "here's how T-SQL works" context needed.
• Supply (the actual context gap): (a) CARR's specifics — its schema, its 252 policies' intent, its array-redesign policy, which JSONB keys are hot; (b) the deterministic ruleset to force the faithful choice where models drift (TVF-not-proc, multi-key @>, JSON-vs-junction); (c) the validation loop to catch plausible-but-wrong output.
• Confidence recalibration: the feasibility of an LLM-assisted approach holds or improves — the model does the heavy lifting and already knows the target. The trust-at-scale concern is unchanged — model drift on edge cases is real and is why we validate, not vibe-check.

OSS landscape (what we can stand on)

GitHub + web sweep, 2026-06-27. Two structural truths:

1. The OSS world is overwhelmingly MSSQL→Postgres, not our direction. Nearly every mature tool goes the opposite way (dalibo/sqlserver2pgsql, openimis/database_postgresql, bemade/mssql-to-postgres, ms2pg, Ispirer's reverse path). Our direction (PG→Azure SQL) is thinly served — confirms 07's "no turnkey path." The few PG→MSSQL repos are small/custom (LynxGeekNYC/sql-migration-tool).

2. The reusable asset is a transpiler, not a migrator: sqlglot.

Tool	What it is	Reuse for us	License note
sqlglot (Python)	SQL parser/transpiler, 30+ dialects incl. postgres + tsql	High — for queries/views/DDL/casts. transpile(sql, read="postgres", write="tsql") mechanically handles LIMIT→TOP, :: casts, ->>, function-name remaps. Does NOT parse PL/pgSQL bodies, RLS, or triggers (out of grammar scope).	MIT (verify before vendoring)
Baronco/SQL-Transpiler-MCP-Tool	sqlglot wrapped as an MCP tool for agents	Direct pattern match — exactly the "agent calls a deterministic transpiler tool" shape to reuse. Study/fork.	check
tobilg/polyglot	Rust/Wasm SQL transpiler, 30+ dialects	Alt to sqlglot if we want browser/edge	check
sqlglot-go, jesse-smith/sqltranspiler	Go port + thin wrappers	Reference only	check
Ispirer / DBConvert	Commercial PG→Azure SQL (schema+data+some code)	Buy for bulk DDL+data; they leave the procedural/RLS tail (see 07 §5)	commercial
Babelfish (AWS)	Makes Aurora Postgres speak the T-SQL wire protocol	Not applicable — opposite direction (run SQL Server apps on Postgres), not convert PG→Azure SQL	—

The build-vs-buy answer, sharpened: sqlglot (deterministic) handles the mechanical query/DDL layer for free; the LLM handles the procedural + RLS layer it already knows (per the probe); an LLM-assisted step orchestrates both + validates. Buy a commercial tool only for its bulk DDL+data loader. Not starting from zero on any layer — transpiler for the easy part, pre-trained LLM for the hard part.

Microsoft documentation — public corpus vs ask-BW

Publicly available (free to feed an LLM-assisted conversion — these are the reference set):
- T-SQL language reference (learn.microsoft.com/en-us/sql/t-sql/)
- Row-Level Security: CREATE SECURITY POLICY + predicate functions + SESSION_CONTEXT/sp_set_session_context
- JSON in SQL Server: OPENJSON, JSON_VALUE, JSON_QUERY, ISJSON
- MERGE, STRING_SPLIT, data-type mapping, IDENTITY/SEQUENCE/NEWID()
- MI vs SQL Database feature comparison; SQL Agent vs Elastic Jobs
- SSMA docs (to document why it can't help — no PG source)

Must request from BW (no Azure access yet):
1. Which Azure SQL flavor + version (MI vs SQL Database vs VM; engine version). Gates everything (07 §6).
2. BW's internal schema/naming standards + any house T-SQL conventions.
3. Networking/private-endpoint constraints (Nancy: can't retrofit).
4. Any existing BW PG→Azure SQL playbook or prior migration they've done.
5. Foundry tenant state + what AI infra they expect on it.
6. A throwaway Azure SQL target for the conversion's validation loop (the one hard dependency).

Bottom line

• The knowledge exists — pre-trained in the models (proven, 3-model consensus) + free public MS docs + sqlglot for the mechanical layer. Dialect understanding does not have to be built from scratch. This is the single biggest de-risk and supports the case that this is a strong fit for LLM-assisted conversion.
• The work that remains is genuinely context-engineering + validation + the Azure-side unknowns and BW dependencies, not a research black hole.
• Snapshot caveat: model knowledge drifts; this scorecard is dated 2026-06-27 and should be re-run before the build starts.

Cross-links

• 07_azure_sql_conversion.md — the conversion work + data-transfer runbook.
• 13_postgres_vs_azure_sql_reanalysis.md — the engine decision this de-risking informs.