erp-migration

ERP Migration Tool

AFTEC-to-Business Central data migration platform. Maps source fields to BC targets, transforms data, and imports via OData $batch. Go-live target: January 2027.

---

Overview

The ERP Migration Tool manages the full data migration lifecycle from PSI’s legacy UniData/AFTEC ERP to Microsoft Dynamics 365 Business Central. It provides field mapping, data transformation, validation, and automated pipeline execution.

AFTEC (UniData)  →  Migration Tool  →  Business Central
     ↑                    ↑                    ↑
  PSI.UniData.API    Express + MSSQL     OData $batch API
                    (dmt.progressivesurface.com)

Feature	Description
Production URL	https://dmt.progressivesurface.com
Stack	React 18 (Vite) + Express.js + MSSQL
Database	ERPMigrationTool on sql2.ad.ptihome.com
Deployment	Azure App Service (Linux) via GitHub Actions
Current Version	1.27.114

Repository: ProgressiveSurface/erp-migration-tool

---

Architecture

System Components

The tool connects three systems:

1. AFTEC/UniData (source) — accessed via PSI.UniData.API REST endpoints
2. ERPMigrationTool database (MSSQL) — stores mappings, metadata, run history, audit logs
3. Business Central (target) — accessed via OData $batch through a custom AL extension

Application Structure

Single Express process serves both API and client:

• Client: React + Vite, builds to server/public/
• Server: Express.js REST API (260+ endpoints)
• Database auth: Windows Auth (local via msnodesqlv8) or SQL Auth (Azure via tedious), selected by environment variables

Key Server Services

Service	Purpose
metadataSyncService.js	Syncs BC + AFTEC table/field metadata (incl. dictionary fields, relation_table_no)
exportPipelineService.js	AFTEC data export with transforms (autoConvert, splitMultivalue, phone_parse, etc.)
bcImportService.js	BC OData $batch import with field filtering, retry, upsert logic
pipelineOrchestrator.js	Shared pipeline execution for endpoints + scheduler
recordValidationService.js	Truncation, option matching, type validation
syncSnapshotService.js	MD5 hash-based differential sync
migrationTestService.js	Analytical readiness scoring (8 categories, 0-100)
schedulerService.js	node-schedule cron jobs
loggingService.js	Batched SQL Server logging with 30-day retention
auditService.js	User action audit trail
transParserService.js	Parses UniData I-type TRANS expressions to extract table relationships
relationshipSyncService.js	Syncs parsed TRANS relationships + BC relation_table_no into DB
clusteringService.js	BFS clustering of related AFTEC tables into entity candidates
entityAutoSeedService.js	Auto-creates draft data_entities from relationship clusters
conversionCodeMapper.js	Maps AFTEC conversion codes to transform templates and inferred data types
entityMatcherService.js	Cross-system entity matching: proposes BC compositions from AFTEC clusters
sikichTemplateImporter.js	Parses Sikich DMT Excel templates, creates entities, auto-suggests field mappings
dmtExportService.js	Extracts AFTEC data, applies transforms, exports to Sikich DMT Excel format
dmtBcLinker.js	Links DMT template columns to BC fields via 5-strategy matching (aliases, exact, normalized, prefix-strip)
entityMergeService.js	Merges duplicate entity pairs — copies mappings/compositions, deletes duplicate
recordFilterService.js	Extraction filters (exclude/include/deduplicate) per entity, e.g., exclude vendor employees
entityExtractionService.js	Extracts AFTEC data with concurrency control (MAX_CONCURRENT=2), hybrid I-type enrichment, compound key handling

---

Client Pages

Page	Purpose
Dashboard	Stats overview, recent runs, audit log, quick navigation
Entity Manager	Data entity CRUD, table compositions, Sikich template import, auto-seed from dictionary
Entity Workspace	Unified per-entity view with Build/Review mode toggle. Build mode = visual field mapping editor with transforms. Review mode = data preview grid + mapping approval with keyboard nav (j/k/a/r/?) and bulk confirm. URL: /entities/:entityId?mode=review
DMT Export	Export entity data to Sikich DMT Excel format for BC import
Conflict Resolution	Merge conflict management for entity mappings
Table Scoping	Define which BC tables are in scope for migration
Data Pipeline	Execute exports/imports, view run history, manage schedules
Migration Plan	Wave planning and table dependency management
Activity Log	User audit trail with filtering
AFTEC Browser	Browse live AFTEC schema and sample data
BC Browser	Browse BC table schema and field details
Template Editor	Create/manage reusable mapping templates
Dictionary	Abbreviation and acronym lookup
Dictionary Explorer	Force-directed relationship graph, cluster manager, BC relationships, auto-seed
AFTEC Field Report	Coverage analysis of AFTEC fields across entities

---

Data Model

Field Status Model

Every BC field has a disposition status that controls its visibility in the Mapping Editor:

Status	Color	Meaning
template	Indigo	In partner template (Sikich)
in_scope	Green	Has mapping / actively worked
optional	Blue	Nice-to-have
out_of_scope	Gray	Not migrating
unknown	Orange	Not yet triaged (default)

The Mapping Editor shows two sections: “In Scope” (template/in_scope/required) and “Other Fields” (collapsed by default). Saving a mapping auto-promotes unknown to in_scope.

Key Database Tables

Table	Purpose
bc_tables, bc_fields	BC metadata (field_disposition, field_class, is_required)
aftec_tables, aftec_fields	AFTEC metadata
mappings	Field mappings (bc_field_id → aftec_field_id, transformation_rule, default_value)
pipeline_runs, pipeline_run_records	Execution history with per-record outcomes
sync_snapshots	MD5 hash-based change detection
code_lookups	Source-to-target code translation tables
migration_test_reports	Readiness scores and issues
migration_tables	Table-level planning (wave, priority, status)
server_logs	Application logs
pipeline_schedules	Cron-based scheduled runs
data_entities	Logical entities grouping related tables for migration
entity_table_compositions	Tables assigned to each entity (AFTEC + BC sides)
entity_dependencies	Inter-entity dependency graph (hard, soft, reference)
aftec_table_relationships	TRANS-expression-derived FK relationships between AFTEC tables
aftec_relationship_clusters	Auto-discovered groups of related AFTEC tables
entity_field_mappings	DMT target fields with source mapping, transforms, disposition, confidence
entity_extraction_filters	Per-entity extraction filters (exclude employees, test records, etc.)
aftec_field_population	Cached field population counts from 1000-record samples (empty field detection)
ai_decisions	Auditable AI decision trail (mapping choices, data quality observations)
bc_environments	Selectable BC import targets for the runtime environment switcher (one active row)

---

Transformation System

Field mappings can include stacked transforms applied in sequence. Transforms are stored as JSON arrays in mappings.transformation_rule.

Transform	Description
uppercase / lowercase	Case conversion
trim	Whitespace removal
truncate	Limit string length
date_internal_to_iso	UniData date (days since 12/31/1967) to ISO
date_mdy_to_iso	M/D/Y string to ISO
yn_to_bool / one_zero_to_bool	Boolean conversions
code_lookup	Source code to target code translation (via code_lookups table)
concat_fields	Concatenate two AFTEC fields with separator
phone_parse	Extract phone number by type code from multivalue PHONES field
default_if_empty	Fallback value for nulls
conditional_value	If/then mapping
strip_non_numeric	Remove non-digit characters
remove_decimals / round_2dp / absolute	Numeric transforms

The Mapping Editor shows a live preview of transformed values (green badges) alongside raw source values.

---

Pipeline Execution

Export-Import Flow

1. Export — Fetch records from AFTEC via UniData API, apply autoConvert (conversion codes), split multivalue fields, apply transforms
2. Validate — Check field lengths, option set matching, type compatibility
3. Import — Send to BC via OData $batch (POST for new, PATCH for existing)
4. Record — Log per-record outcomes (success/error/skipped) to pipeline_run_records

Import Modes

The BC metadata extension supports two modes controlled via ImportModeAPI:

• Validated (default) — BC runs all standard validation triggers
• Bypass — Skips OnInsertRecord/OnModifyRecord validation for bulk loading

Dependency Management

Tables can have blocking dependencies (e.g., Customer must import before Contact). The execution plan computes tiers and isReady flags based on dependency completion.

---

BC Metadata Extension

A custom AL extension installed in Business Central that provides API endpoints for the migration tool.

API Group	Purpose
progressivesurface/metadata/v1.0	Read-only table/field/page metadata
progressivesurface/import/v1.0	Upsert records with optional validation bypass

Import APIs: Item (50206), Customer (50207), Vendor (50208), Contact (50212)

The import APIs support upsert: OnInsertRecord checks if the record already exists and routes to Modify instead of Insert when in bypass mode.

It is additive only — no table/page extensions on standard objects, no event subscribers, no install/upgrade codeunits; objects live in the free per-tenant range 50200–50249.

Deployment (reproducible CI)

The BC environments were recreated after a prior bad-extension incident, and this extension was not re-published — so the direct-OData path was non-functional until rebuilt (June 2026).

The repo now has reproducible CI (no AL-Go): build.yml compiles the .app with the standalone alc.exe + CDN-fetched v28 symbols; deploy.yml publishes it to a BC environment via the Automation API (3-step extensionUpload flow), sandbox-first and Production-guarded. Deploy:

gh workflow run deploy.yml --ref main -f environment=sandbox      # then DEV
gh workflow run deploy.yml --ref main -f environment=Production -f confirm=Production

Per-environment BC setup (required, one-time each): app-only identities must be registered in BC → Microsoft Entra Applications and Enabled in every environment — Automation.ReadWrite.All/Graph consent alone does not grant BC access in this tenant.

• Deploy SP bc-extension-deploy (dc2fdf7c-…): needs D365 AUTOMATION and EXTEN. MGT. - ADMIN.
• Client apps (e.g. the migration tool’s import app 06761071): need the extension’s PSI API Access permission set (caption “PSI Metadata & Import API Access”).

Status (2026-06-17): sandbox + DEV deployed and verified — import APIs (Item/Customer/Vendor/Contact) return HTTP 200 via the live tool. Production intentionally NOT deployed (recreated post-incident prod; held by direction). Reusable pattern + full troubleshooting: Deploy BC Extensions and the repo’s DEPLOYMENT.md.

See bc-metadata-extension repository for details.

---

BC Environment Switching

The direct-OData import path can target any of several Business Central environments at runtime. All PSI BC environments live in the same Entra tenant and are reached by the same client app; the OAuth token (scope …/.default) is tenant-wide. So switching environments only changes the environment name and the company GUID in the API URL — the token, tenant, and client secret never change.

Selectable environments

Seeded into the bc_environments table on startup (verified live 2026-06-16). The bc-metadata-extension is installed and reachable in all three.

Environment	Type	App ver	Company (Progressive Surface)
Production	Production	28.1	5c223602-b34f-f111-a820-000d3aec53ac
DEV	Sandbox	28.1	5c223602-b34f-f111-a820-000d3aec53ac
sandbox	Sandbox	28.1	5c223602-b34f-f111-a820-000d3aec53ac

DEV/sandbox/Production share the same company GUID (DEV and sandbox are copies of Production). The throwaway DEV-260522191617 (older app v27.5, different PS company GUID) and soft-deleted Sandbox-SAVE-… environments are intentionally not selectable.

How it works

• bc_environments table is the registry of selectable targets; the single is_active = 1 row is the current target. The selection is team-wide (persisted in SQL, not per-user) so two people can’t unknowingly import to different environments.
• bcImportService holds the active environment/company as runtime state (setActiveEnvironment / getActiveEnvironment). On switch, company-scoped caches (resolved company name, per-entity field lists) are dropped; the tenant-wide token cache is kept.
• UI: the BC target appears as a dropdown switcher in the sidebar. Production is amber-flagged and requires confirmation before switching, since imports run after a switch write to the selected company.

Endpoints

Endpoint	Purpose
GET /api/pipeline/bc-environments	List selectable environments + the active one
POST /api/pipeline/bc-environment	Switch active environment ({ name }); persists + reconfigures in-process
GET /api/pipeline/bc-config	Current resolved target (environment + company display name)

Per-environment authorization caveat: the client-credentials app must be authorized inside each BC environment (BC admin → Microsoft Entra Applications, with a permission set, state Enabled). If a switch yields 401/403 on bc-health, the app hasn’t been granted access in that environment yet.

---

Dictionary Intelligence

Automatically discovers table relationships by parsing UniData dictionary metadata and BC field relations, then uses that knowledge to auto-propose cross-system entity compositions and field transforms.

Phase 1 — Relationship Discovery (v1.27.0)

1. TRANS Parsing — Scans I-type fields in aftec_fields for TRANS('TargetTable', KeyExpr, 'ReturnField', 'Code') expressions. Classifies key expressions as same_id (@ID), field_ref (simple field), or expression (complex).
2. BC Relations — Reads relation_table_no from BC field metadata to build the BC-side relationship graph.
3. BFS Clustering — Groups related AFTEC tables into entity candidates using 2-hop breadth-first search from root tables (most inbound references). Assigns roles: master, extension, child, reference, related.
4. Auto-Seed — Converts pending clusters into draft data_entities with pre-built entity_table_compositions. Supports dry-run preview before committing.

Phase 2 — Cross-System Matching & Transform Intelligence (v1.28.0)

Cross-System Entity Matching

When an entity is seeded from an AFTEC cluster, the system now auto-proposes BC table compositions using three strategies (in priority order):

1. Table cross-reference — Checks table_xref for manually confirmed AFTEC-to-BC mappings (highest confidence)
2. Name similarity — Normalizes table names and fuzzy-matches (e.g., AFTEC CUSTOMER to BC Customer)
3. BC relationship graph — For unmatched members, finds BC child tables via bc_relationships edges and scores by structural similarity

BC clusters are computed in-memory via BFS on bc_table_relationships (18,703 edges). Results include confidence scores and match source for each proposal.

Conversion Code Intelligence

AFTEC dictionary conversion codes (stored in aftec_fields.conversion_code) now drive three improvements:

Code Pattern	Transform	Inferred Type
D2/, D4-	aftec_date	date
MD2, MR4	aftec_decimal(N)	decimal
MCU / MCL	uppercase / lowercase	text
MT	trim	text

• Enhanced field matching — Suggestions compare BC field names against both field_name and display_name (e.g., AFTEC “SLSMN.NO” with display “Salesperson Number” matches BC “Salesperson Code”)
• Type-aware scoring — Conversion codes reveal the true data type; an integer field with D2/ is recognized as a date
• Auto-populated transforms — Creating a field mapping auto-sets source_transform_rule from the conversion code
• Mapping Editor hints — Amber badges suggest applicable transforms when a conversion code is detected

Dictionary Explorer Page

Interactive visualization with three tabs:

• AFTEC Relationship Graph — Force-directed graph of table relationships with click-to-inspect
• Cluster Manager — View/expand clusters, seed individual or all, preview before committing
• BC Relationships — Separate BC-side relationship graph

Entity Manager Integration

• Auto-Seed from Dictionary button in entity list header (preview + confirm workflow)
• Suggested Related Tables callout in AFTEC composition form (fetches relationships for selected table)
• Suggest BC Tables banner on entities with AFTEC but no BC compositions — previews proposed BC tables with confidence scores and applies in one click

---

Sikich DMT Integration (v1.29.0)

Phase 1 migration strategy: produce Excel files matching Sikich Data Migration Tool (DMT) template format, rather than importing directly to BC via OData. Sikich DMT handles the actual BC import.

AFTEC (UniData) → Migration Tool → Sikich DMT Excel → BC Import

Sikich Template Import

Parses ~40 Sikich DMT Excel templates (located on shared drive) and auto-creates:

1. Data entities — one per template (e.g., “Customer Master”, “Item Master”)
2. Target field schema — DMT column names, types, and lengths stored in entity_field_mappings.target_load_rule
3. AFTEC table composition — auto-matched via known mappings + name similarity
4. Field-level suggestions — auto-maps AFTEC fields to DMT columns using stringSimilarity + conversion code intelligence

System columns are excluded: Entry No., Delete Record, Validation Error, Error Text, Selected, Processed, Modified, BC No., Assign New No.

DMT Export

Extracts AFTEC data and produces downloadable Excel files:

1. Load entity compositions (AFTEC tables) and field mappings
2. Extract composite records via entityExtractionService
3. Apply autoConvert (conversion codes) + explicit transform rules
4. Coerce values for DMT types (Boolean→TRUE/FALSE, Date→YYYY-MM-DD, truncate Text/Code)
5. Write .xlsx with columns in DMT template order

Entity Workflow (Sidebar)

1. Dashboard
2. Entity Manager (import templates, manage compositions)
3. Field Mapping (Entity Mapping Editor — map AFTEC fields to DMT columns)
4. Mapping Review (review all mappings, set dispositions, preview data)
5. DMT Export (extract, transform, download Excel)
6. Conflict Resolution

Legacy tools (Table Scoping, Table Mapping Editor, Data Pipeline) remain in collapsible “Table Tools” section.

Field Disposition

Each field mapping has a disposition column that controls its scope in the pipeline:

Disposition	Meaning
(empty)	Not yet reviewed
migrate	Confirmed for migration
future	Deferred to a future phase
needs_cleanup	Source data needs cleaning first
needs_discussion	Requires team discussion
skip	Out of scope — excluded from coverage % and DMT export

Set disposition in the Entity Mapping Editor (right-click context menu) or Mapping Review page (dropdown per row). Fields with disposition='skip' are excluded from coverage percentages and are not included in DMT Excel exports.

---

Data Cleanup & Entity Dedup (Feb 2026)

After importing ~40 Sikich DMT templates, the system had duplicate entities (e.g., “Customer Master” from DMT import alongside “Customer” from manual planning). A cleanup effort resolved this and introduced automated DMT-to-BC field linking.

Entity Merge

The entityMergeService.js merges duplicate entity pairs:

1. Copies unique entity_field_mappings from the delete entity to the keep entity
2. Merges better data (source_field_id, confidence, notes) from overlapping canonical paths
3. Copies missing entity_table_compositions
4. Deletes the duplicate entity (cascades through all related tables)
5. Re-runs the DMT-BC linker on the keep entity

10 pairs merged, 6 orphans deleted — entity count dropped from 76 to 62.

• REST: POST /api/entities/merge { keepEntityId, deleteEntityId, dryRun }
• MCP: merge_entities tool

DMT-BC Field Linker

The dmtBcLinker.js service automatically links DMT template columns (stored in entity_field_mappings.target_load_rule JSON) to their corresponding bc_fields records by populating target_field_id and target_table_id.

Five matching strategies (applied in order):

#	Strategy	Example
0	Known aliases (19 hardcoded corrections)	“Customer No.” → “No.”, “Descrtiption” → “Description”
1	Exact field_name match (case-insensitive)	“Description” = “Description”
2	Exact field_caption match	Caption-based matching
3	Normalized (strip punctuation/spaces)	“Ship-to Code” → “shiptocode”
4	Entity-prefix stripping + retry 1-3	”Customer No.” on Customer entity → strip prefix → “No.”

Result: 561/677 active DMT fields linked (82.9%). 615 placeholder columns (User Defined, SIK-Toyota, Shortcut Dimension) bulk-dispositioned as skip/future.

• REST: POST /api/dmt-bc-linking/backfill, GET /api/dmt-bc-linking/report, GET /api/dmt-bc-linking/unmatched/:entityId
• MCP: link_dmt_to_bc_fields, get_bc_linking_report, get_unmatched_dmt_columns
• SQL: database/021_dedup_bc_tables.sql (bc_tables dedup), database/022_disposition_placeholder_columns.sql (bulk dispositions)

UI: BC Field Metadata

After linking, BC metadata is surfaced in the UI:

• Entity Mapping Editor: Right-panel sublabel shows Code(20) · BC: No. when the DMT column maps to a differently-named BC field
• AI Review Dashboard: Data types row shows | BC: Code20; expanded detail includes a “BC Field” section with caption and data type

---

Data Quality & Validation

Empty Field Detection

The tool detects AFTEC fields with no data to prevent mapping empty sources to BC targets. A 1000-record sample is queried per table and results are cached in SQL.

• Endpoint: GET /api/aftec/live/field-population/:tableName — returns per-field population counts
• Cache: aftec_field_population table (auto-created on startup) — MERGE upsert, instant on cache hit
• Refresh: ?refresh=true forces re-query from UniData
• UI: Empty unmapped fields show 40% opacity + gray “empty” badge; empty mapped fields show red “no data!” warning

Code Lookup Tables

AFTEC uses numeric codes for payment terms, shipping agents, salesperson, etc. These must be translated to BC-compatible codes via the code_lookups table.

Lookup	AFTEC Source	AFTEC Table	Entries	Status
payment_terms	CREDIT.TERMS / TERMS.CODE	TMF (Terms Master File)	31	Rebuilt from TMF + BC (2026-02-26). 4 new BC codes created.
shipping_agent	FREIGHT table	FREIGHT	12	Working (carrier names, not Incoterms)
customer_status	-	-	2	Working
industry_class	-	-	4	Working

BC standard API: GET /api/bc/standard/:entitySet queries BC v2.0 API for reference data (paymentTerms, currencies, shipmentMethods, etc.). POST creates new records. Used to verify and create setup data before DMT import.

BC Payment Terms (15 codes as of 2026-02-26): 10 DAYS, 14 DAYS, 15 DAYS, 1M(8D), 2 DAYS, 21 DAYS, 30 DAYS, 40 DAYS, 45 DAYS, 55 DAYS, 60 DAYS, 7 DAYS, 90 DAYS, CM, COD. Four codes (40/45/55/90 DAYS) were created programmatically via the standard API.

Key AFTEC lookup tables:

AFTEC Table	Key Format	Contents	Example
TMF	CO!CODE (e.g., 1!6)	Payment terms: description, discount %, net days	1!1 = NET 30 DAYS, 1!6 = NET 60 DAYS
SALESMAN	CO!CODE (e.g., 1!205)	Salesperson: name, address, phone, sales totals	1!PBS = Progressive Blasting Systems
FREIGHT	-	Shipping carriers/methods	Maps to BC Shipping Agent Code

Extraction Architecture

AFTEC data extraction uses a hybrid approach to handle two UniData API quirks:

1. Compound Key Handling

AFTEC record IDs use !-delimited compound keys. The first segment is always the company prefix (e.g., 1). The extraction strips only the company prefix and preserves remaining segments as the business key.

Segments	Example	Table	Business Key
2	1!100298	CUSTOMER, VENDOR, SALESMAN, TMF	100298
3	1!100647!1	SHIPTO, BMF	100647!1
1	SF	FREIGHT	SF

Tables with 3-segment keys (SHIPTO, BMF) require the full compound business key to be unique. Using only the last segment (e.g., ship-to code 1) causes massive deduplication — SHIPTO collapsed from 2054 to 61 records before this was fixed.

Affected tables: Any table with 3+ segment !-delimited keys. Known: SHIPTO, SHIPTO.1287, SHIPTO.OE, BMF, BMF.1287.

2. Hybrid I-Type Field Extraction

When an entity has I-type (computed/virtual) dictionary fields, the extraction uses a two-phase approach:

1. Regular export (/api/data/dev/{table}/export) — gets ALL records reliably with D-type (physical) field data
2. Export-fields enrichment (/api/data/dev/{table}/export-fields) — evaluates I-type fields server-side via LIST…TOXML, then merges values into the base records

This hybrid approach exists because the TOXML endpoint silently drops records that have I-type evaluation errors. For SHIPTO, TOXML returns ~1954 of 2054 records (~5% loss). The regular export always returns the full record set.

Records that TOXML drops will have empty I-type field values but won’t be missing from the export entirely. The fallback enrichITypeFieldsFromKey can compute simple FIELD(@ID,'!',N) patterns client-side when TOXML fails completely.

Endpoint	Returns	I-type Fields	Record Completeness
Regular export	D-type attributes + rawAttributes	No	100%
Export-fields (TOXML)	Named fields including I-type	Yes	~95% (drops records with eval errors)
Hybrid (current)	All records + I-type where available	Yes	100%

Known Data Quality Issues

Entity	Field	Issue	Resolution
Customer (14)	TAX.1, TAX.2, TERR	0/1000 records populated — were incorrectly mapped	Cleared; set as empty defaults
Customer (14)	CREDIT.TERMS → Payment Terms Code	code_lookup had wrong BC codes	Fixed: rebuilt from TMF + BC data, 4 new BC terms created
Customer (14)	SLS.1 → Salesperson Code	Raw code “205” exported	Correct format for BC — code is the identifier
Vendor (15)	VENDCLASS=E records	640/6193 records are employees with PII	Excluded via extraction filter (entity_extraction_filters ID 3)
Ship-to (85)	All fields	Was exporting 61 of 2054 records	Fixed (v1.29.3): compound key dedup + hybrid extraction
Production BOM (98)	All fields	Same compound key issue as SHIPTO (BMF has 3-segment keys)	Fixed (v1.29.3): same compound key fix

---

Deployment

CI/CD Pipeline

Trigger: Push to main branch Runner: Self-hosted Linux runner psi-internal

1. Install dependencies (cached)
2. Run server tests (Vitest, ~184 tests) and client tests
3. Bump patch version automatically
4. Build client with Vite (stamps git SHA + build time)
5. Create deployment zip (server + built client, no dev deps or tests)
6. Deploy via az webapp deployment source config-zip to Azure Web App erp-migration-api
7. Commit version bump back to repo
8. Run E2E smoke test (best-effort)

Key setting: SCM_DO_BUILD_DURING_DEPLOYMENT=false prevents Azure Oryx from rebuilding — the zip is pre-built in CI.

April 2026 deployment/auth incident and fixes

On 2026-04-02, deeper production validation uncovered two workflow stability issues and one auth regression:

1. Health gate false failure

   • Deploy workflow originally failed if /api/health was not 200.
   • Because protected endpoints may validly return 401/403, the gate was updated to accept 200/401/403 as reachable.

2. Version bump push race

   • Workflow occasionally failed on non-fast-forward when pushing the post-deploy version bump.
   • Fixed by rebasing before push and making the push step non-blocking when race conditions occur.

3. DMT HTTP 401 incident

   • https://dmt.progressivesurface.com briefly returned 401 after auth standardization.
   • Root cause: authsettingsV2 still had requireAuthentication=true despite platform-auth disable attempts.
   • Remediated by setting app-layer/anonymous platform mode (platform.enabled=false, requireAuthentication=false, AllowAnonymous, identity providers disabled).
   • Post-fix validation: dmt.progressivesurface.com/ and /api/health both returned 200.

Validated production deploy run after fixes:

• Workflow: deploy.yml
• Run ID: 23678116
• Result: success

Environment Variables (Azure)

Variable	Purpose
DB_SERVER	MSSQL server (sql2.ad.ptihome.com)
DB_DATABASE	Database name (ERPMigrationTool)
DB_USER / DB_PASSWORD	SQL Auth credentials
BC_TENANT_ID	Azure AD tenant for BC API
BC_CLIENT_ID / BC_CLIENT_SECRET	App registration for BC API
BC_ENVIRONMENT	BC environment name
BC_COMPANY_ID	Target company GUID

---

MCP Server

The tool exposes its full functionality via the Model Context Protocol (MCP), enabling AI assistants (Claude Code, etc.) to browse schemas, create mappings, run transforms, and export DMT files programmatically.

Transports

Transport	Endpoint	Use Case
stdio	node server/mcp-server.js	Claude Code local development (configured in .mcp.json)
Streamable HTTP	POST https://dmt.progressivesurface.com/mcp	Remote clients, web-based AI tools

Both transports share the same tool registrations via server/mcp-tools.js.

Architecture

mcp-tools.js          ← 40+ tool definitions (registerTools function)
  ├── mcp-server.js   ← stdio wrapper (Claude Code)
  └── index.js /mcp   ← HTTP wrapper (Streamable HTTP, stateless)

• Shared module (mcp-tools.js): All tool registrations in a registerTools(server, getPool) function. Uses a Proxy pattern so tool handlers access the SQL pool without modification.
• stdio (mcp-server.js): Thin wrapper — creates McpServer, connects StdioServerTransport, manages its own SQL pool. Used by Claude Code via .mcp.json.
• HTTP (index.js): Stateless per-request mode — each POST to /mcp creates a fresh McpServer + StreamableHTTPServerTransport, reuses the Express server’s SQL pool. No session tracking (Azure App Service compatible).

Tool Categories (42 tools)

Category	Tools	Purpose
Browse & Discover	list_aftec_tables, list_entities, get_entity_detail, get_aftec_fields, get_bc_fields, compare_entity_fields, get_aftec_sample_data, search_fields, get_dmt_target_fields, get_entity_mapping_context	Read-only schema browsing
Mapping Operations	get_entity_mappings, suggest_mappings, create_mapping, update_mapping, bulk_update_mappings, add_entity_composition	Field mapping CRUD
Transform & Test	test_transform, auto_convert_sample, suggest_transform	Preview data transformations
Export	preview_dmt_export, preview_export_rows, validate_entity_export, export_entity_to_dmt, batch_export_dmt	DMT Excel generation
Validation	get_mapping_coverage, auto_suggest_entity_mappings, repair_entity_mappings	Coverage reports, auto-suggest
AI Support	save_mapping_question, get_mapping_questions	Human-in-the-loop questions
Decision Intelligence	log_ai_decision, query_ai_decisions	Auditable AI decision trail
BC Comparison	get_bc_existing_records, run_reconciliation, profile_entity_data, get_latest_profiling_report	Data quality profiling
Record Filtering	manage_extraction_filter, preview_filter_impact	Extraction filter CRUD
DMT-BC Linking	link_dmt_to_bc_fields, get_bc_linking_report, get_unmatched_dmt_columns	Target field linking
Entity Merge	merge_entities	Duplicate entity resolution
Knowledge Graph	update_knowledge_graph	Read/append confirmed mapping patterns to .claude/knowledge/mapping-patterns.md — shared across AI sessions

Connecting to the MCP Server

Option 1: Remote HTTP (recommended for most users)

Connect to the live Azure-hosted server — no repo clone needed, no local database required. Works from any machine on the PSI network.

Claude Code — add to your user or project .mcp.json:

{
  "mcpServers": {
    "erp-migration": {
      "type": "url",
      "url": "https://dmt.progressivesurface.com/mcp"
    }
  }
}

Cursor / Windsurf / other MCP clients — use Streamable HTTP transport:

• URL: https://dmt.progressivesurface.com/mcp
• Method: POST
• Content-Type: application/json
• Protocol: MCP Streamable HTTP (stateless, no session ID required)

Verify it works:

curl https://dmt.progressivesurface.com/mcp/health
# → {"status":"ok","transport":"streamable-http","mode":"stateless"}

Option 2: Local stdio (for developers with the repo cloned)

Runs the MCP server as a subprocess — connects directly to the local SQL Server database. Requires the repo and npm install in server/.

The .mcp.json in the repo root already configures this:

{
  "mcpServers": {
    "erp-migration": {
      "command": "node",
      "args": ["server/mcp-server.js"]
    }
  }
}

Claude Code automatically picks this up when opened in the repo directory.

Option 3: Test with curl

Send a raw MCP JSON-RPC request to list entities:

curl -X POST https://dmt.progressivesurface.com/mcp \
  -H "Content-Type: application/json" \
  -d '{"jsonrpc":"2.0","id":1,"method":"tools/call","params":{"name":"list_entities","arguments":{}}}'

What can the MCP server do?

Once connected, an AI assistant (Claude, etc.) can:

• Browse AFTEC and BC schemas, sample live data, search fields
• Map AFTEC source fields to Sikich DMT target columns
• Transform data (dates, decimals, code lookups, phone parsing, etc.)
• Export entities to DMT Excel files for Sikich import
• Validate mapping coverage, data quality, and reconciliation
• Filter which records are included in exports
• Audit all AI decisions with structured reasoning trails

Health Check

GET /mcp/health — returns { status: 'ok', transport: 'streamable-http', mode: 'stateless' }

---

API Endpoint Groups

Route Prefix	Purpose
/api/bc/*, /api/aftec/*	Table/field metadata
/api/metadata/sync	Schema synchronization
/api/mappings/*	Field mapping CRUD
/api/pipeline/*	Export, import, run, schedules, history
/api/validate/*	Data validation
/api/plan/*	Migration planning (waves, dependencies)
/api/scoping/*	Table scoping
/api/templates/*	Mapping templates
/api/transformations/*	Transform preview
/api/suggest/*	AI mapping suggestions
/api/lookups	Code translation tables
/api/audit/*	Audit log
/api/logs	Application logs
/api/entities/*	Entity CRUD, compositions, dependencies, field mappings, BC matching
/api/aftec/relationships/*	AFTEC TRANS relationship graph, sync, stats
/api/aftec/clusters/*	Relationship cluster management, auto-seed
/api/bc/relationships/*	BC FK relationship graph and sync
/mcp	MCP Streamable HTTP endpoint (POST)
/mcp/health	MCP health check
/api/health	Health check

---

Related Pages

• PSI.UniData.API — Source data access layer
• Deploy to Azure — Azure deployment patterns
• Azure Remediation (April 2026) — Hardening rollout and incident record
• Data Brain — ERP and data systems overview

---

Last updated: June 2026