Case Study
Rescuing 18 Million Data Points from a Legacy EHR
How Xpio reverse-engineered a legacy ECHO database, built a complete virtual health record, and safely extracted every patient record before system decommission — for HopeSparks Family Services.
- Patient records recovered
- ~6,000
- Data fields per patient
- 280+
- Source tables mapped
- 50+
- Total data points extracted
- 18M+
No API. No docs. No vendor support. 6,000 patients.
Legacy ECHO/Revman EHR database (SQL Server Express) with no modern API, no documentation, and no vendor support for data export
Client transitioning to a new EHR — needed complete patient data extracted before decommissioning the old system
50+ database tables with inconsistent schemas, undocumented relationships, and fragmented data across clinical, billing, and administrative domains
Scanned documents and digital signatures stored in proprietary binary formats with no standard export path
Clinical assessments (PHQ-9, GAD-7, CPSS) embedded in wide tables with 150+ columns — no field mapping documentation
A complete extraction system — from raw SQL to AI-powered patient summaries.
Complete Schema Reverse-Engineering
Xpio mapped the entire ECHO database schema — 50+ tables, thousands of columns, undocumented foreign keys. We built a complete data dictionary from scratch, verified against real patient data across every table.
25-Tab Virtual Health Record
Built a complete virtual health record replicating every ECHO tab: clinical summary, demographics, diagnoses, medications, assessments, progress notes, care plans, billing, signatures, and scanned documents. 100% tab coverage verified.
AI-Powered Clinical Summaries
Claude API generates patient summaries from the extracted structured data — treatment history, assessment trends, medication timelines, and care plan status. Intelligent caching reduced AI response times from 24 seconds to under 50ms.
Intelligent Caching & Performance
Hash-based cache versioning tied to patient data modifications. Cache-first architecture reduced query times from 30+ seconds to under 50ms. 95%+ cache hit rate with automatic invalidation when source data changes.
Data Quality & PHI Protection
Automated data quality scoring per patient — field completeness tracking, validation, and issue identification. SSN masking, date standardization, and empty field filtering. Every record cleaned and verified before export.
Document & Signature Recovery
Recovered scanned clinical documents and digital signatures from proprietary ECHO storage (cd.images linked through episode chains). Binary formats decoded and exported as standard files with full audit trail.
Every clinical domain. Every record.
Clinical Assessments
PHQ-9, GAD-7, CPSS, intake forms
Progress Notes
Session notes, cancellation records
Diagnoses
ICD-10 codes with date ranges
Medications
Prescriptions, dosages, providers
Billing & Financial
Claims, transactions, receipts
Episodes & Enrollments
Admission, discharge, program data
Care Plans
Goals, objectives, interventions
Demographics
Identity, address, communications
Signatures
Digital authorizations and consents
Documents
Scanned images, clinical attachments
Zero data loss. Complete migration.
~6,000
Patient records fully extracted with complete data integrity verification
280+
Data fields per patient mapped across 50+ legacy tables
100%
ECHO tab coverage — all 25 tabs replicated in the virtual health record
50ms
Query response time with intelligent caching (down from 30+ seconds)
85/100
Average data quality score with automated field completeness tracking
Zero
Data loss — every record verified against source before decommission
How the extraction pipeline works.
Data Processing Pipeline
AI Cache Architecture
Every patient summary is generated once, then cached with hash-based versioning tied to the underlying data. When patient data changes, the cache auto-invalidates and regenerates on next access. Cost tracking shows the exact savings per request — from ~$0.06 per AI call to $0.00 for cache hits. 95%+ hit rate in production, with response times dropping from 24 seconds to under 50 milliseconds.
Stuck on a legacy EHR?
ECHO, Revman, Credible, or any legacy system — Pryzma has the tools to get your data out safely.
Talk to Our Team