18 - Migration Roadmap: Phases, Decisions & Blockers

The sequenced plan for moving CARR from Supabase to Barry-Wehmiller's Azure environment. Shows what's been completed, what comes next (in order), which decisions BW/CARR must make, and what blocks what. This is the "what happens and when" companion to the architecture overview in 17.
Triggers: "migration plan", "roadmap", "phases", "what's next", "decisions needed", "blockers", "sequencing", "timeline".
Updated: 2026-08-07.

Where we are now (status summary)

• ✅ Azure SQL database provisioned — cbsuse-sql01.database.windows.net / siplatform is stood up
• ✅ Identity resolved — all users are BW-internal (CARR is a BW subsidiary); straight Entra workforce auth, no External ID complexity
• ✅ Migration doc pack complete — 18 documents covering every layer, shared on the co-branded site
• ✅ Agent-building stack shared — 16_agent_building_stack.md sent to Colby, Saeed, Blake (2026-07-21)
• ✅ GitHub Enterprise migration underway — repo transfer to BW Enterprise GitHub in progress (Workstream B, Madhavan + Binoy)
• ✅ BW email credentials for Brian Elbert — issued 2026-07-27; Entra auth + MFA confirmed working
• ✅ Network path — RESOLVED. AVD provisioned 2026-08-06. The former blocker (cbsuse-sql01 has Deny Public Network Access = Yes, Private-Endpoint-only, rejecting all connections from outside the VNet) is cleared by the BW-hosted Azure Virtual Desktop inside the VNet (US East). Access: Windows App → brian.elbert@carrbiosystems.com via OKTA → "Diacto" hostpool. Also makes SSMS directly usable. Details: 15_migration_status.md
• ⏳ DB principal + Azure Portal RBAC — never granted. Now the critical path — grant in parallel; nothing else blocks it
• ⏳ Two written confirmations outstanding — that siplatform is the correct database dedicated to CARR (blocks any DDL), and whether the target is Azure SQL Database or Managed Instance (decides whether the 21 jobs land on Elastic Jobs)
• ⏳ Foundry access — no BW user with Foundry access identified yet; does not block the agent fleet (built container-first on Container Apps), only the optional Foundry-managed edges

Research refresh (2026-08-07)

Targeted Brave + Firecrawl pass to re-verify the time-sensitive facts in this pack. Result: no claim was invalidated; three items firmed up in our favour.

• Elastic Jobs is now GA (general availability confirmed). Our D1 recommendation — Azure SQL Database + Elastic Jobs for the 21 pg_cron jobs — is on a GA feature, not preview. Note: at GA, the Elastic Job Agent is billed by its capacity tier (was free in preview) — a small, expected line item to flag to BW.
• No first-party "SSMA for PostgreSQL." Microsoft's SQL Server Migration Assistant officially covers Access, Db2, MySQL, Oracle, SAP ASE — not PostgreSQL. This validates our approach: the Postgres→T-SQL conversion is LLM-assisted + sqlglot-supported and hand-reviewed, because there is no Microsoft push-button converter for our source engine.
• Azure AI Foundry Agent Service reached GA (core service GA March 2026; hosted agents GA ~July 2026). Relevant nuance for D5: Foundry Hosted Agents run your own container image (Agent Framework, LangGraph, OpenAI/Anthropic SDKs), so a container-first build on Container Apps lifts into Foundry later with little rework. This makes Foundry an optional add-on (Model Router, Foundry IQ RAG, managed eval/identity), not a dependency — the open item is purely access, and it no longer gates Phase 4.
• Confirmed unchanged: Azure Data Studio retired 2026-02-28 (use VS Code + MSSQL extension); SSMS remains Windows-only (so the AVD is what makes it usable); bcp/mssql-tools18 remains the bulk-load path.

What's been completed

#	Item	Date	Notes
1	Full system architecture documented (3-layer map)	2026-07-21	CARR_FULL_SYSTEM_MIGRATION_MAP.md
2	Azure SQL conversion scope assessed	2026-06-17	131 functions, 252 RLS policies, 80 JSONB cols → T-SQL rewrite (07)
3	All 26 Edge Functions censused	2026-06-28	Per-function Azure landing identified (08)
4	Upstream data agents inventoried	2026-07-21	Full SaaS + custom code + Hub dependency map (09)
5	7-agent CARR fleet designed	2026-07-21	Target-state replacement for upstream layer (11)
6	Identity question resolved	2026-07-08	All users BW-internal; Entra workforce auth (04, 15)
7	Lovable role clarified	2026-07-22	Hosting only; no capability loss when removed (03)
8	Azure SQL server provisioned by BW	2026-07-14	cbsuse-sql01.database.windows.net / siplatform
9	Postgres vs Azure SQL reanalysis	2026-06-28	Both paths documented; BW selected Azure SQL (13)
10	Co-branded migration site live	2026-07-15	carr-migration.b.biocreativestrategies.com

Migration phases (sequenced)

Phase 0 — Access & Foundation (in progress)

Goal: establish the access and permissions needed for everything downstream.

Step	Owner	Status	Blocks
Issue BW email credentials for brian.elbert@carrbiosystems.com	Chad Evans (BW) + Colby	✅ Done 2026-07-27	—
Open a network path to cbsuse-sql01 — ✅ route chosen 2026-07-29: BW-hosted Azure Virtual Desktop inside the VNet, US East (Nancy Risch). Chosen from four options; BC-preferred	Nancy Risch (BW)	✅ Decided 7/29	Phases 1–5 (everything)
Supply AVD hardware specs + software list — 8 vCPU / 32 GB / 256 GB Premium SSD recommended (4 vCPU / 16 GB sufficient); full tool list; five provisioning flags. Delivered as 20_avd_provisioning_and_access_response.md + co-branded DOCX	BioCreative	✅ Done 7/31	Server Request
Submit the BW Server Request — noted in the form as a Virtual Desktop, not a Server	Colby	✅ Done 8/04	AVD provisioning
Provision the AVD + set up access — "Diacto" hostpool, Windows App, brian.elbert@carrbiosystems.com via OKTA	BW cloud ops (Nancy)	✅ Done 8/06	— (was: everything)
Settle AVD provisioning specifics — (a) outbound egress to the Supabase source (or a file-transfer alternative), (b) where the Phase 2 container image is built (Docker Desktop / ACR Tasks / CI agent), (c) whether the multi-session profile persists tooling + files, and install-rights cadence	BW cloud ops + Brian	🔄 Raised 7/31	Phase 1 data move · Phase 2 image
Grant DB principal in siplatform: CREATE USER [brian.elbert@carrbiosystems.com] FROM EXTERNAL PROVIDER + db_owner (DDL required — ~86 tables / 101 views / 131 procs). Can be granted in parallel with AVD provisioning	BW Entra/SQL admin	⏳ Not granted	Phase 1
Confirm Azure SQL Database vs Managed Instance — endpoint indicates SQL Database (no SQL Server Agent), which moves the 21 jobs to Elastic Jobs / Functions timers. Real Phase 1 scope change	BW DBA (Venkata)	⏳ Open	Phase 1 job re-homing (1.5)
Grant Azure Portal Reader on the resource group + SQL server	BW cloud ops	⏳ Never requested (new ask)	Self-service diagnostics
Confirm siplatform is dedicated to CARR, not a shared BW app DB	BW + Colby	⏳ Open	Phase 1 (blocks any DDL)
Verify the connection target resolves to cbsuse-sql01/siplatform and is the correct dedicated CARR database before any DDL	BioCreative	🔄 Ready (AVD live)	Phase 1 DDL
Connect + verify access from the AVD (SSMS now viable — the AVD is a Windows desktop; sqlcmd / VS Code + MSSQL extension also available)	BioCreative	🔄 Ready (AVD live); needs Grant B	Phase 1
Confirm resource group / permissions for Container Apps + Container Registry	BW cloud ops + Colby	⏳ Open	Phase 2
Locate a BW user with Foundry access	BW + Colby	⏳ Open	Phase 4
Complete GitHub Enterprise repo transfer	Madhavan + Binoy (BW)	🔄 In progress	Phase 2 (CI/CD)

Phase 1 — Database Migration (Supabase Postgres → Azure SQL)

Goal: move the data tier — schema, data, business logic, security — to Azure SQL.

Prerequisites: Phase 0 (DB credentials).

Step	What	Effort	Notes
1.1	Schema conversion: tables, columns, indexes, constraints	M	JSONB (80 cols) → NVARCHAR(MAX) + computed JSON columns; arrays (105 cols) → junction tables or JSON; generated columns → computed columns. Tool-assisted + manual review
1.2	Function conversion: 131 PL/pgSQL → T-SQL stored procedures	L	LLM-assisted translation + semantic verification loop. The biggest single effort
1.3	RLS rewrite: 252 policies → Azure SQL equivalent	L	auth.uid() → SESSION_CONTEXT('user_id') pattern; every policy hand-reviewed
1.4	Trigger conversion: 46 triggers → T-SQL triggers	M	Mostly mechanical; watch for PG-specific syntax (NEW/OLD → INSERTED/DELETED)
1.5	Scheduled jobs: 21 pg_cron → SQL Agent / Elastic Jobs / Functions timers	M	High-frequency jobs (15-min, 2-hr) need reliable sub-hourly scheduling
1.6	Data migration: ~4,200 accounts + ~104,000 contacts + history	S	bcp / BULK INSERT; validate row counts + referential integrity
1.7	Vault → Key Vault: ~15 API keys	S	Straightforward rewire
1.8	Validation: compare Supabase vs Azure SQL outputs on sample queries	M	Semantic equivalence testing on critical business logic

⚡ Decision needed: Azure SQL flavor — SQL Database vs Managed Instance vs SQL-on-VM. Affects available features (SQL Agent is MI-only; SQL Database uses Elastic Jobs). BC recommendation: SQL Database + Elastic Jobs (lowest ops overhead). Update 2026-07-29: the provisioned endpoint (cbsuse-sql01.database.windows.net, gateway cr14.eastus1-a.control, no MI DNS zone segment) indicates Azure SQL Database — so this is effectively already decided, and step 1.5 lands on Elastic Jobs. Awaiting written confirmation from BW.

Phase 2 — App Hosting & Auth (React SPA + Entra)

Goal: host the frontend on Azure and replace the Supabase Auth login system with Entra workforce auth.

Prerequisites: Phase 0 (RG/permissions, GitHub Enterprise), Phase 1 partially (the API middleware needs the Azure SQL connection).

Step	What	Effort	Notes
2.1	Host React/Vite build on Azure Static Web Apps (or Container Apps + nginx)	S	Build SPA → deploy static files → re-point domain. Trivial (proven SpanCorr pattern)
2.2	Build custom API middleware on Container Apps	L	Validates Entra JWT, injects SET LOCAL app.user_id = '<oid>' per request, proxies to Azure SQL. Replaces PostgREST
2.3	Swap frontend auth: supabase-js → MSAL	M	Replace signInWithPassword with MSAL login flow; session handling changes
2.4	User provisioning workflow	S	Define who creates/deactivates users in Entra (replaces BC manually adding users in Supabase dashboard)
2.5	Author Dockerfile + push backend image to Container Registry	S	BioCreative authors; BW hosts

⚡ Decision needed: hosting target — Static Web Apps vs Container Apps vs App Service. BC recommendation: SWA for the frontend (cheapest, built for SPAs), Container Apps for the API middleware.

⚡ Decision needed: does BW mandate Power Platform for the frontend, or accept the existing React SPA? BC recommendation: keep the React SPA — no capability is lost by removing Lovable (it's just hosting), and a Power Apps rewrite is a separate multi-month project with no migration benefit.

Phase 3 — Edge Functions (26 Deno/TS → Azure)

Goal: move the 26 serverless functions from Supabase Edge to Azure.

Prerequisites: Phase 1 (functions need the Azure SQL connection), Phase 2 partially (functions need Key Vault access).

Step	What	Effort	Notes
3.1	Containerize Deno functions on Container Apps (preferred)	M	Preserves Deno/TS code; Docker + deno serve. Lowest rewrite
3.2	OR rewrite Deno → Node for Azure Functions	L	Deno.env → process.env, URL imports → npm, top-level await changes. Only if BW mandates pure FaaS
3.3	Rewire secrets: Supabase Vault → Key Vault	S	Per-function; mechanical
3.4	Rewire DB calls: supabase-js → direct Azure SQL connection	M	Replace Supabase client with mssql or tedious driver
3.5	AI model calls: keys in Key Vault, call Anthropic/OpenAI directly	S	Foundry Model Router optional later as a drop-in endpoint; not required

⚡ Decision needed: containerize Deno (lower effort, preserves code) vs rewrite to Node (Azure Functions native). BC recommendation: containerize on Container Apps.

Phase 4 — Upstream Internalization (the supply chain → CARR-owned)

Goal: move the external data pipeline (Layer 4) from BC infrastructure to CARR-owned Azure. This is two distinct tracks — the APIs (subscriptions + keys + wiring) and the Agent Virtual Environment (the compute that runs the agents). They can proceed in parallel: the APIs are procurement, the VE is a build.

Prerequisites: Phase 1 (agents write to Azure SQL). Foundry is NOT a prerequisite — the fleet is built container-first on Azure Container Apps; Foundry is an optional later deployment target (see D5).

Track 4a — API access (CARR's own subscriptions + keys)

CARR takes over the 4a data-source APIs by signing their own third-party plans and wiring the integration code to their keys.

Step	What	Effort	Notes
4a.1	Provision CARR's own accounts for the sourcing/enrichment SaaS: Sales Navigator, PhantomBuster, Clay, FullEnrich	S	Procurement — CARR-held contracts + billing, not BC's
4a.2	Store all third-party API keys in Azure Key Vault	S	Replaces BC's key storage; one secret per service
4a.3	Confirm the public life-science/news APIs need no paid plan (ClinicalTrials.gov, NIH RePORTER, SEC EDGAR, PatentsView, PubMed, RSS)	S	Free/public — just register app keys where required
4a.4	Build the integration code chains (pull/push) against CARR's keys	M	The code that calls each API; runs inside the 4b environment

⚡ Decision needed: which APIs does CARR subscribe to directly now vs keep BC-fed during transition? BC recommendation: stand up the enrichment SaaS (Clay/FullEnrich) first; keep public life-sci feeds BC-fed until the Ingestion agent is live.

Track 4b — Agent Virtual Environment (CARR's own compute)

CARR stands up its own virtual environment to run the agents that consume the 4a APIs and do the capture → summarize → match → enrich → internalize work.

Step	What	Effort	Notes
4b.1	Stand up the virtual environment: Azure Container Apps + Functions timers (the "VPS equivalent")	M	The scheduled-compute home for the fleet; replaces bc-ops crons
4b.2	Re-host bc-ops crons as Functions timer triggers	M	carr_burst_classify_seed.sh, sync_carr_emails.py, Clay push
4b.3	Port Colab notebooks into the environment (Container Apps jobs / Azure ML; Foundry optional)	M	Import, classify, enrich, match — manual-run becomes scheduled
4b.4	Build the 7-agent CARR fleet in the environment	L	Ingestion, Enrichment, Account-Matching (HITL), Classification, SSO, Content, Orchestrator. See 11

⚡ Decision needed: agent host — Container Apps vs Foundry Hosted Agents for the VE. BC recommendation (updated 2026-08-07): build container-first on Container Apps — it matches how BC already builds, is portable/provider-agnostic, and needs no Foundry access. Because Foundry Hosted Agents accept a container image, the same image lifts into Foundry later with little rework, so Foundry stays an optional add-on (Model Router, Foundry IQ RAG, managed eval/identity) rather than a dependency.

⚡ Decision needed: review-queue ownership during transition — who works the Account-Matching + message-QA queues? BC, CARR reps, or BW ops?

Phase 5 — Hub Retirement (standalone CARR on Azure)

Goal: remove CARR's dependency on the BC Hub database, making CARR fully standalone on Azure.

Prerequisites: Phase 4 (Ingestion agent built), per-source parity validation.

Step	What	Effort	Notes
5.1	Ingestion agent Phase 2: direct ClinicalTrials.gov pulls	M	Replace sync_clinical_trials_from_hub with direct API
5.2	Direct NIH RePORTER, SEC EDGAR, PatentsView pulls	M	Replace sync_grants_from_hub etc.
5.3	Direct news/RSS ingestion	S	Replace sync_news_from_life_science
5.4	Academic intelligence direct pulls	M	Replace sync_academic_*_from_hub
5.5	Validate parity per source, then retire Hub feed	S per source	Cut over one source at a time; keep Hub as fallback until parity proven

⚡ Decision needed: Hub retirement pace — which sources does BW want pulled directly first? Does any source stay on the BC Hub long-term?

⚡ Decision needed: vendor retention — keep Clay/FullEnrich behind the Enrichment agent, or move to an Azure-native enrichment provider?

Decisions summary

#	Decision	Who decides	Blocks	BC recommendation
D1	Azure SQL flavor (SQL Database vs Managed Instance)	BW DBA team	Phase 1 (job scheduling)	SQL Database + Elastic Jobs (now GA — billed per job-agent tier)
D2	Frontend hosting (SWA vs ACA vs App Service)	BW cloud ops	Phase 2	SWA for frontend, ACA for API
D3	React SPA vs Power Platform	BW + CARR	Phase 2	Keep React SPA (no rewrite needed)
D4	Edge Functions: containerize Deno vs rewrite to Node	BW + BC	Phase 3	Containerize on ACA
D5	Agent host (Container Apps vs Foundry) for the virtual environment	BW + BC	Phase 4b	Container-first on Container Apps (portable, no Foundry-access dependency); same image lifts into Foundry later if BW wants the managed edges (routing/RAG/eval)
D6	Review-queue ownership during transition	BW + CARR	Phase 4b	BC initially, transition to CARR reps
D7	Hub retirement pace	BW + CARR	Phase 5	Phased, one source at a time
D8	Vendor retention (Clay/FullEnrich)	BW + CARR	Phase 4–5	Keep vendors initially behind the agent
D9	Which APIs CARR subscribes to directly now vs keep BC-fed during transition	BW + CARR	Phase 4a	Enrichment SaaS first; public life-sci feeds BC-fed until Ingestion agent live

Blockers

Blocker	Owner	Status	Blocks
~~BW email credentials for Brian Elbert~~	Chad Evans (BW) + Colby	✅ Resolved 2026-07-27	—
~~Network path to cbsuse-sql01~~ — resolved by the in-VNet AVD	BW cloud ops (Nancy)	✅ Resolved 2026-08-06	—
DB principal + db_owner on siplatform	BW Entra/SQL admin	⏳ Not granted — now the critical path	Phase 1
Azure Portal Reader on RG + SQL server	BW cloud ops	⏳ Never requested	Self-service diagnostics
Written confirmation siplatform is dedicated, not shared	BW + Colby	⏳ Open	Phase 1 DDL
Container Apps + Registry RG/permissions	BW cloud ops	⏳ Open	Phase 2 (Dockerfile deploy)
Foundry access (no BW user found)	BW + Colby	⏳ Open — not blocking (Container Apps is the default host; Foundry is optional)	Phase 4 only if Foundry is chosen for the managed edges
GitHub Enterprise repo transfer	Madhavan + Binoy	🔄 In progress	Phase 2 (CI/CD pipeline)

Parallel tracks

Not everything is sequential. Once Phase 0 access lands:

• Track A (database): Phase 1 can begin immediately
• Track B (app/auth): Phase 2 can begin in parallel (the middleware design doesn't need the DB conversion to be complete)
• Track C (functions): Phase 3 starts after Phase 1 is partially done (functions need the Azure SQL connection string)
• Track D1 (APIs — Phase 4a): CARR can procure their own API subscriptions + keys anytime; it's independent of the DB/auth work
• Track D2 (Agent VE — Phase 4b): design can proceed in parallel; implementation needs Phase 1 done (agents write to Azure SQL) and consumes the Track D1 keys. No Foundry access required — built container-first on Container Apps; Foundry optional later

Cross-references

• Architecture overview (the four layers): 17_architecture_overview.md
• Database conversion scope: 07_azure_sql_conversion.md
• Edge Function census: 08_edge_function_census.md
• Upstream agents inventory: 09_upstream_data_agents.md
• Planned 7-agent fleet: 11_carr_agent_fleet.md
• Compute + frontend: 03_compute_frontend.md
• Identity + DevOps: 04_github_devops_governance.md
• Status + call notes: 15_migration_status.md
• Client tooling + the three access grants (SSMS vs macOS clients, Private Endpoint): 19_client_tooling_and_access.md