Data Migration AI
AI-assisted data migration from legacy HIS into the medOS data model.
11 min read diagramsUpdated 2026-03-26docs/architecture/architecture/02-data-migration-ai.md
AI-assisted healthcare data migration — Claude-powered schema analysis, field mapping, and transform generation
Overview
The MedOS Data Migration system is an enterprise-grade pipeline that migrates data from legacy HIS (Hospital Information Systems) into MedOS. It uses Anthropic Claude (Sonnet 4.5) as an AI co-pilot across a three-phase architecture: Collect → Build → Load.
Key Principle: Claude generates code and mappings that humans review — it never directly touches production data at runtime.
Location: his-vajira/apps/ever/microservices/ever-api-migration/
Architecture Diagram
┌─────────────────────────────────────────────────────────────────────────────┐
│ SOURCE SYSTEMS │
│ │
│ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌──────────┐ ┌─────────────────┐ │
│ │ MySQL │ │ MSSQL │ │ Oracle │ │ Postgres │ │ Files │ │
│ │ MariaDB │ │ │ │ │ │ │ │ CSV/TSV/Excel │ │
│ └────┬─────┘ └────┬────┘ └────┬────┘ └─────┬────┘ │ JSON/XML │ │
│ │ │ │ │ └────────┬────────┘ │
│ └──────────────┴────────────┴──────────────┴───────────────┘ │
│ │ │
│ ┌─────────────────────────────────┴─────────────────────────────────────┐ │
│ │ EHR VENDOR ADAPTERS │ │
│ │ ┌──────────┐ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │ │
│ │ │ Epic │ │ TakeCare │ │ TrakCare │ │ Custom │ │ │
│ │ │ SMART/ │ │ TCWS/SITHS │ │ OAuth2/mTLS │ │ API/HL7 │ │ │
│ │ │ FHIR R4 │ │ Swedish │ │ InterSystems│ │ │ │ │
│ │ └──────────┘ └──────────────┘ └──────────────┘ └──────────────┘ │ │
│ └───────────────────────────────────────────────────────────────────────┘ │
└──────────────────────────────────────────────┬──────────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────────────────────────┐
│ │
│ ╔═══════════════════════════════════════════════════════════════════════╗ │
│ ║ PHASE 1: COLLECT (Extract) ║ │
│ ║ ║ │
│ ║ Status: pending → extracting ║ │
│ ║ AI: None (deterministic) ║ │
│ ║ ║ │
│ ║ ┌──────────────────┐ ┌───────────────────┐ ┌───────────────┐ ║ │
│ ║ │ SQL Adapter │ │ File Adapter │ │ EHR Adapter │ ║ │
│ ║ │ ──────────── │ │ ──────────── │ │ ─────────── │ ║ │
│ ║ │ Knex multi-DB │ │ CSV/TSV/Excel │ │ Epic FHIR │ ║ │
│ ║ │ Batch extraction │ │ JSON/NDJSON/XML │ │ TakeCare │ ║ │
│ ║ │ Checkpoints │ │ Type inference │ │ TrakCare │ ║ │
│ ║ │ Resume-on-fail │ │ │ │ │ ║ │
│ ║ └────────┬──────────┘ └─────────┬──────────┘ └──────┬───────┘ ║ │
│ ║ │ │ │ ║ │
│ ║ └─────────────────────────┴──────────────────────┘ ║ │
│ ║ │ ║ │
│ ║ ▼ ║ │
│ ║ ┌─────────────────────────────────────────────┐ ║ │
│ ║ │ DATA LAKE (NDJSON) │ ║ │
│ ║ │ {DATA_LAKE_PATH}/{jobId}/{table}/ │ ║ │
│ ║ │ batch_000001.ndjson │ ║ │
│ ║ │ batch_000002.ndjson │ ║ │
│ ║ │ ... │ ║ │
│ ║ └──────────────────────┬──────────────────────┘ ║ │
│ ╚══════════════════════════════════╪════════════════════════════════════╝ │
│ │ │
│ ╔══════════════════════════════════╪════════════════════════════════════╗ │
│ ║ PHASE 2: BUILD (Analyze + Map) ║ │
│ ║ ║ │
│ ║ Status: analyzing → mapping → generating → reviewing ║ │
│ ║ AI: HEAVY Claude usage (all 3 agents) ║ │
│ ║ ║ │
│ ║ ┌───────────────────────────────────────────────────────────────┐ ║ │
│ ║ │ PHI MASKING ENGINE │ ║ │
│ ║ │ masking.ts — strips PII before any Claude call │ ║ │
│ ║ │ Preserves: column names, types, cardinality, patterns │ ║ │
│ ║ │ Removes: actual patient data, sample values │ ║ │
│ ║ │ Supports: Thai, Swedish, Chinese, English │ ║ │
│ ║ └──────────────────────────────┬────────────────────────────────┘ ║ │
│ ║ │ ║ │
│ ║ ▼ ║ │
│ ║ ┌──────────────────────────────────────────────────────────────┐ ║ │
│ ║ │ AGENT 1: Schema Analyzer (claude-sonnet-4-5-20250514) │ ║ │
│ ║ │ ────────────────────────────────────────────────────── │ ║ │
│ ║ │ Input: Masked schema (tables, columns, types, cardinality) │ ║ │
│ ║ │ Output: TableAnalysis[] with: │ ║ │
│ ║ │ • inferredEntityType (41 MedOS entity types) │ ║ │
│ ║ │ • confidence (0.0-1.0) │ ║ │
│ ║ │ • suggestedMigrationOrder │ ║ │
│ ║ │ Knows: hn=hospital#, vn=visit#, icd10, drugcode, etc. │ ║ │
│ ║ └──────────────────────────────┬───────────────────────────────┘ ║ │
│ ║ │ ║ │
│ ║ ▼ ║ │
│ ║ ┌──────────────────────────────────────────────────────────────┐ ║ │
│ ║ │ AGENT 2: Field Mapper (claude-sonnet-4-5-20250514) │ ║ │
│ ║ │ ────────────────────────────────────────────────────── │ ║ │
│ ║ │ Input: Schema analysis + discovered columns + samples │ ║ │
│ ║ │ Output: FieldMapping[] with: │ ║ │
│ ║ │ • sourceColumn → targetField (dot notation) │ ║ │
│ ║ │ • transformType: direct|lookup|computed|concat|split|def │ ║ │
│ ║ │ • trafficLight: GREEN(>0.9) | YELLOW(0.5-0.9) | RED(<0.5)│ ║ │
│ ║ │ • confidence + reasoning │ ║ │
│ ║ │ Also: unmappedSourceColumns, unmappedTargetFields │ ║ │
│ ║ └──────────────────────────────┬───────────────────────────────┘ ║ │
│ ║ │ ║ │
│ ║ ┌────────────┴────────────┐ ║ │
│ ║ ▼ ▼ ║ │
│ ║ ┌──────────────────────┐ ┌──────────────────────────────────┐ ║ │
│ ║ │ HUMAN REVIEW GATE │ │ AGENT 3: Transform Generator │ ║ │
│ ║ │ ────────────────── │ │ (claude-sonnet-4-5-20250514) │ ║ │
│ ║ │ • GREEN → auto-pass │ │ ──────────────────────────── │ ║ │
│ ║ │ • YELLOW → review │ │ Input: Approved mappings + │ ║ │
│ ║ │ • RED → escalate │ │ code mappings + samples │ ║ │
│ ║ │ │ │ Output: GeneratedTransform: │ ║ │
│ ║ │ Review threads with │ │ • TypeScript function code │ ║ │
│ ║ │ multi-approver flow │ │ • Lookup tables │ ║ │
│ ║ │ │ │ • Test cases │ ║ │
│ ║ └──────────┬───────────┘ │ Guarantees: PURE, DETERMINISTIC │ ║ │
│ ║ │ │ NULL-safe, ISO 8601 dates │ ║ │
│ ║ │ │ Thai/Swedish/Chinese encoding │ ║ │
│ ║ └──────────────┤ │ ║ │
│ ║ └──────────────────┬───────────────┘ ║ │
│ ╚═══════════════════════════════════════════════╪═══════════════════════╝ │
│ │ │
│ ╔═══════════════════════════════════════════════╪═══════════════════════╗ │
│ ║ PHASE 3: LOAD (Transform + Load) ║ │
│ ║ ║ │
│ ║ Status: transforming → loading → reconciling → completed ║ │
│ ║ AI: None (deterministic execution of generated code) ║ │
│ ║ ║ │
│ ║ ┌──────────────────────────────────────────────────────────────┐ ║ │
│ ║ │ TRANSFORM ENGINE │ ║ │
│ ║ │ ──────────────── │ ║ │
│ ║ │ • Reads NDJSON batches from Data Lake │ ║ │
│ ║ │ • Executes Claude-generated TypeScript functions │ ║ │
│ ║ │ • Adds _id (UUID), _migratedFrom, _migratedAt metadata │ ║ │
│ ║ │ • Dry-run mode for testing │ ║ │
│ ║ │ • Per-row error capture │ ║ │
│ ║ │ • Output: .transformed/{tableName}/ │ ║ │
│ ║ └──────────────────────────────┬───────────────────────────────┘ ║ │
│ ║ │ ║ │
│ ║ ▼ ║ │
│ ║ ┌──────────────────────────────────────────────────────────────┐ ║ │
│ ║ │ LOAD ENGINE (via Domain Service APIs) │ ║ │
│ ║ │ ──────────────────────────────────────── │ ║ │
│ ║ │ Entity → Service Action Routing: │ ║ │
│ ║ │ patient → administration.patient.create │ ║ │
│ ║ │ encounter → clinical.encounter.create │ ║ │
│ ║ │ medication_order→ medication.medicationRequest.create │ ║ │
│ ║ │ lab_result → diagnostic.labResult.create │ ║ │
│ ║ │ invoice → financial.invoice.create │ ║ │
│ ║ │ staff → administration.staff.create │ ║ │
│ ║ │ department → administration.department.create │ ║ │
│ ║ │ │ ║ │
│ ║ │ • Never writes directly to DB — always via service APIs │ ║ │
│ ║ │ • Emits hospital_event per loaded record │ ║ │
│ ║ │ • Failures route to DLQ │ ║ │
│ ║ └──────────────────────────────┬───────────────────────────────┘ ║ │
│ ║ │ ║ │
│ ║ ▼ ║ │
│ ║ ┌──────────────────────────────────────────────────────────────┐ ║ │
│ ║ │ RECONCILIATION │ ║ │
│ ║ │ ─────────────── │ ║ │
│ ║ │ Checks: │ ║ │
│ ║ │ ✓ Record count matching (source vs target) │ ║ │
│ ║ │ ✓ Orphaned records detection (referential integrity) │ ║ │
│ ║ │ ✓ Clinical safety validation │ ║ │
│ ║ │ ✓ Financial balance reconciliation │ ║ │
│ ║ │ Traffic light summary: { green, yellow, red } │ ║ │
│ ║ │ Status: passed | failed | pending_review │ ║ │
│ ║ └──────────────────────────────────────────────────────────────┘ ║ │
│ ╚══════════════════════════════════════════════════════════════════════╝ │
│ │
│ ┌──────────────────────────────────────────────────────────────────────┐ │
│ │ CROSS-CUTTING: Deduplication Engine │ │
│ │ • Exact match on national ID (>99% → auto-merge) │ │
│ │ • Fuzzy: Thai nicknames, Chinese transliteration, Swedish surnames │ │
│ │ • 80-99% → human review queue │ │
│ │ • <80% → block merge │ │
│ └──────────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌──────────────────────────────────────────────────────────────────────┐ │
│ │ CROSS-CUTTING: Dead Letter Queue (DLQ) │ │
│ │ • Failed records quarantined with error context │ │
│ │ • Retry, fix-and-retry, skip, or manual resolution │ │
│ └──────────────────────────────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────────────────────────┐
│ TARGET: MedOS │
│ │
│ MongoDB (via Moleculer microservice actions) │
│ ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐ │
│ │ Patients │ │Encounters│ │ Labs │ │ Meds │ │ Invoices │ ... │
│ └──────────┘ └──────────┘ └──────────┘ └──────────┘ └──────────┘ │
└──────────────────────────────────────────────────────────────────────────────┘
Traffic Light System
The traffic light is the core confidence mechanism across the entire pipeline:
| Light | Confidence | Action | Use Case |
|---|---|---|---|
| GREEN | > 0.9 | Auto-approve | Exact match, known pattern, obvious mapping |
| YELLOW | 0.5 - 0.9 | Human review required | Multiple plausible options, needs code mapping |
| RED | < 0.5 | Block / escalate | Cannot map, safety-critical field, conflicting data |
Job Lifecycle
pending → extracting → analyzing → mapping → generating → reviewing
│
┌───────────────┤
▼ ▼
transforming failed
│ │
▼ ▼
loading rolled_back
│
▼
reconciling
│
▼
completed
Database Schema (9 core tables)
| Table | Purpose |
|---|---|
migration_jobs |
Job tracking with phase, status, entity counts, traffic lights |
migration_records |
Per-row audit trail with source/transformed data + claude_reasoning |
migration_code_mappings |
Value-level mappings (source_code → target_code/UUID) |
migration_mapping_profiles |
Generated transform code + lookup tables + test results |
migration_review_threads |
Collaborative review with multi-approver flow |
migration_dedup_candidates |
Patient matching candidates with scores and decisions |
migration_checkpoints |
Extraction resume points (last_id, row_count, checksum) |
migration_batches |
Load tracking with clinical lock flags |
migration_discovered_schemas |
Cached schema introspection + analysis results |
Schema file: his-vajira/medbase/migrations/017_migration_tables.sql
REST API Endpoints
Job Management
| Method | Endpoint | Purpose |
|---|---|---|
| POST | /jobs |
Create migration job |
| GET | /jobs |
List jobs (filter by hospital, status) |
| GET | /jobs/:id |
Get single job |
| PATCH | /jobs/:id/status |
Update job status |
| POST | /jobs/:id/collect |
Start Phase 1 |
| POST | /jobs/:id/build |
Start Phase 2 |
| POST | /jobs/:id/load |
Start Phase 3 |
| GET | /jobs/:id/stats |
Progress + traffic light stats |
Schema Discovery
| Method | Endpoint | Purpose |
|---|---|---|
| POST | /schema/test-connection |
Validate source connectivity |
| POST | /schema/discover |
Full schema introspection |
| POST | /schema/profile |
Single table profiling |
EHR Sync
| Method | Endpoint | Purpose |
|---|---|---|
| POST | /ehr/test-connection |
Test EHR connectivity |
| POST | /ehr/sync |
Full EHR sync |
| POST | /ehr/sync/patients |
Sync demographics |
| POST | /ehr/sync/encounters |
Sync encounters |
| POST | /ehr/sync/lab-results |
Sync lab results |
| GET | /ehr/vendors |
List supported vendors |
Entity Types (41 MedOS entities)
Dimensions: gender, marital_status, nationality, race, religion
Codified Items: dosage_form, administration_route, medication_category, substance, item_type, unit, vendor, manufacturer
Organizational: department, location, ward, room, bed, staff
Clinical: payor, payor_plan, medication_item, stock_item, lab_item, procedure_item, imaging_item, service_item
Data: code_table, patient, allergy, encounter, diagnosis, medication_order, lab_result, vital, clinical_note, immunization, stock_balance, stock_transaction, invoice, claim, payment
Clinical Safety & Compliance
- PHI Masking — No real patient data ever sent to Claude. Multilingual support (Thai, Swedish, Chinese, English)
- Traffic Light Gates — RED fields block auto-approval; YELLOW requires human review
- Clinical Locks — Loaded records with clinical decisions cannot be rolled back
- Human Review Threads — Multi-approver collaborative review with comment history
- Reconciliation — Post-load verification with count matching, orphan detection, financial balance checks
- Audit Trail — Per-record source data, transformed data, and
claude_reasoningstored
Migration Box (Portable Deployment)
A stripped-down Docker Compose deployment for on-site data migration:
File: docker-compose.migration-box.yml
┌─────────────────────────────────┐
│ MIGRATION BOX │
│ │
│ migration-api (Claude + Knex) │
│ medbase-db (Postgres) │
│ mongo (State store) │
│ nats (Message bus) │
│ gateway (Minimal API) │
│ │
│ Env: CLAUDE_API_KEY │
│ DATA_LAKE_PATH │
└─────────────────────────────────┘
Dependencies
| Package | Version | Purpose |
|---|---|---|
@anthropic-ai/sdk |
^0.39.0 | Claude API client |
moleculer |
^0.14.32 | Microservice framework |
knex |
^3.1.0 | SQL query builder (multi-DB) |
mysql2 |
— | MySQL/MariaDB driver |
tedious |
— | MSSQL driver |
pg |
— | PostgreSQL driver |
oracledb |
— | Oracle driver |
xlsx |
— | Excel parser |
csv-parse |
— | CSV parser |
hl7-standard |
^1.0.0 | HL7v2 message parser |
uuid |
^9.0.0 | UUID generation |
zod |
^3.24.0 | Schema validation |