Health Sync: Linking Health Facility Data

How Rwanda connects facility EMR databases to the central HIC store — the system, the site onboarding methodology, and the lessons learned across nine live hospital deployments between March and July 2026. This reading pairs with the Data Architecture sessions on the source layer and ELT: Health Sync is the pipeline that delivers continuous, facility-level raw data into the warehouse the labs build on.

Deep-dive companions — the full internal documentation is published in this library:

• Health Sync overview — architecture, the three runner loops, and every design decision with its rationale.
• Deployment runbook — the exact commands for all six deployment parts, reproducible in your own environment.

The problem in one paragraph

Facility EMRs (OpenMRS-based MySQL) are isolated inside each facility, while national health intelligence — outbreak surveillance above all — needs that data centrally within minutes. Facility connectivity is unreliable, nobody can visit a site to deploy a fix, and facility servers run fragile production clinical systems. Health Sync answers all three: a durable local queue that loses zero data across outages, self-updating runners that never need a site visit, and a footprint that is minimal, containerized, read-only at the source, and provably harmless.

MySQL (facility) → runner → local SQLite queue → HTTPS → central API → PostgreSQL
                     │                                        │
                     └── polls GitHub, self-updates            └── per-site schema,
                                                                  heartbeats, logs,
                                                                  metrics, dashboard

The single most important design idea for unreliable links: re-sending is free; a gap loses data. Watermarks overlap on purpose, delivery is at-least-once, and the center upserts — correctness comes from idempotency, not from fighting duplicates.

How sites are onboarded — the methodology

This is the most directly transferable artifact for adapting countries. It evolved from a cautious ~5-hour first deployment into a sub-1-hour repeatable playbook by the ninth site. Every step below has its exact commands in the deployment runbook.

Step 0: Prove safety before touching production

Before any hospital deployment, a disposable proof-of-concept site on a spare server demonstrated end-to-end that MySQL, Docker, and the runner could be installed without endangering a fragile facility server: everything containerized, packages version-pinned against automatic upgrades, minimal installs, nothing exposed publicly. This POC — witnessed by facility staff — is what converted institutional hesitation into written deployment authorization.

Step 1: Preload history offline; sync only the delta

First-sync backfill of years of history would hammer both the facility server and the central API. Instead:

1. Obtain a database dump from the facility.
2. Stream-extract only the target tables (dumps shrink 60–80%).
3. Restore into a throwaway, memory-capped, performance-tuned MySQL container on the central server (redo log disabled, binlog off — 2–4× faster, safely cancellable).
4. Load into the facility’s PostgreSQL schema; add primary keys, _synced_at, and analytics indexes.
5. Set the runner’s starting watermark just before the dump cutoff (overlap is free).

The whole pipeline is one resumable script: 14 tracked phases, per-phase state files (deliberately not in /tmp — a reboot once wiped hours of work), memory pre-flight checks with auto-abort, and an incremental mode that can add new tables to a live site without touching existing data or rotating the live site’s credentials.

Step 2: Prepare the source database

Every OpenMRS facility database we met was missing indexes on its timestamp columns. At the first site, this made every sync cycle a 23-second full scan of a 16.7-million-row table. The fix costs under a minute:

CREATE INDEX idx_obs_date_created ON obs (date_created) ALGORITHM=INPLACE LOCK=NONE;

MySQL 8’s online DDL builds the index without locking the table — safe on a live clinical system, and safe to cancel. (Note the syntax: the options are separated by spaces, not commas — we learned this on a production server.)

Measure before touching a live hospital system. Row counts, load averages, and timed test queries turned “will this hurt the EMR?” from a fear into a quantified, defensible answer for facility staff.

Step 3: Install and verify

A one-line installer clones the repository, builds the runner image, walks an interactive wizard for site configuration and table selection, and starts the container. Post-install verification checks heartbeats, first sync cycles, and central row-count deltas.

Step 4: Do no harm — respect what you inherit

• Online DDL only; session-scoped settings; read-only database users.
• Issues that belong to the facility (database network exposure, package update policy) are flagged to the facility’s administrators, not changed unilaterally.
• OS release upgrades disabled; unattended security updates audited rather than removed.

Lessons learned

Each lesson comes from a specific, dated production event.

Infrastructure — design for what is actually there

1. The bottleneck is almost never the sync software — it is the source database. One missing index turned every sync cycle into a 23-second full scan; a 30-second online index build eliminated it. Audit indexes at every site before first sync.
2. Shared national servers are chronically oversubscribed — cap everything. An uncapped import job’s memory growth once starved a 15 GB central server hosting ~25 containers for multiple health programs: the kernel killed processes, remote access dropped, and the import was lost. With a hard memory cap on the same job, the import completed in 20 minutes instead of 6 hours of thrashing followed by death — and any failure now kills one container, not the host.
3. Facility infrastructure is hostile to assumptions. Encountered in practice: repeated power-loss-style outages at the central server (escalated as a UPS/infrastructure finding), broken IPv6 that silently failed package installs and Docker builds (fix: force IPv4 in both apt and Docker), a hospital network firewall that blocked package downloads inside Docker builds (six hypotheses before the correct diagnosis; the fix is now automated in the installer), and dropped SSH sessions mid-operation. Run every long job in a persistent terminal session (tmux); design every operation to be resumable.
4. Evidence before blame. When the central server “crashed” during a preload, systematic kernel-log analysis split the incident into two different events: one genuinely caused by our uncapped import (fixed with caps), and one a building-power problem that predated us (escalated to the infrastructure owner). Different problems, different owners, different fixes — and the confidence to say “this part was not us” came from logs, not assertions.
5. Test on the platform you deploy to. A verification script validated on a macOS laptop failed on the Linux server over a one-character difference in tool behavior. Developer-laptop success does not validate server behavior.

Data — real health data is messy

6. “Standard EMR” is only mostly true. Across facilities running the same EMR product we found: different dump formats (three variants broke our extractor in sequence), tables whose timestamp column breaks the product convention (created_date instead of date_created — with day-level granularity requiring a periodic watermark backfill), unconventional primary-key names, decimal columns that looked integer, malformed zero-dates beyond the classic form, and non-UTF-8 bytes in dumps. Verify per table, per site; institutionalize each finding in code and configuration, not in someone’s memory.
7. Estimate data growth before building anything on constrained servers. The ingestion-metrics feature was designed as hourly aggregates (~22 MB/year) after calculating that raw per-batch logging would cost ~1.3 GB/year. The back-of-envelope math took ten minutes and settled the design.
8. Exact counts don’t scale; estimates do. The dashboard originally counted every row of every table on every refresh — multi-second full scans that grew with the data. Switching to database catalog estimates cut it to ~25–75 ms with no operational loss.

Process — how the work actually got reliable

9. Every manual run must become a playbook, then a script. Site #1 took ~5 hours with many unknowns. Each failure was folded back into a written playbook, then automated into the resumable preload script and installer. Sites #8 and #9 were routine. Automate after two manual runs; convert every failure into a permanent pre-flight check.
10. Diagnose before retrying. During the migration-tool saga (four distinct failure modes, including a tool that reported success while migrating zero tables), every failure was root-caused with read-only evidence — container logs, both databases’ process lists, host memory — before any action. Each failure turned out to have a different cause; blind retries would have wasted days.
11. Know when to abandon a tool. After repeated silent hangs, the third-party migration tool was replaced with a small purpose-built streaming loader in an afternoon. Sunk cost is not a strategy.
12. Reviews tuned to production reality catch what test suites cannot. Two examples: a timezone handling bug that would have shifted every metrics bucket by two hours on Kigali-time servers (all tests passed on UTC dev machines), and the supply-chain payload below. Both were caught by review, not tests.

Security — including a real incident

13. Supply-chain attack — a case study. The project’s repository was hit by a real supply-chain compromise: an obfuscated malicious loader injected into a process-manager configuration file, hidden past column 90 behind hundreds of spaces of off-screen whitespace, with concealment entries added to .gitignore. Because the runners auto-execute code pulled from this repository, a successful compromise could have reached every facility. What saved us, in order:

    • Routine code review flagged the injected file on first contact.
    • Defense-in-depth: all runners execute in Docker and never evaluate the infected file, so the payload never ran at any health facility.
    • Skepticism about “already cleaned” code: the payload survived two dedicated cleanup commits by hiding off-screen; the final removal was verified with byte-level comparison against a known-clean reference and full-repository fingerprint sweeps — not visual inspection.

    The lesson for any country adopting auto-updating fleet software: the code repository is a patient-data security boundary. Protect it accordingly (access control, review gates, integrity verification), and isolate execution so one compromised file cannot own the fleet.

14. Secrets leak through boring files. A review caught a live repository access token sitting in a stray text file not covered by .gitignore — the exact re-compromise vector for the incident above. It was revoked immediately and history was verified clean. Also learned: rotating the central signing secret invalidates every site token at once — know the blast radius of an auth change before making it.

15. Metadata is sensitive in health systems. An unauthenticated metrics endpoint exposing per-site, per-hour ingestion volumes was flagged in review: even “just aggregate counts” reveal clinic activity patterns. Every endpoint deserves an explicit authentication decision.

16. Least privilege paid for itself repeatedly. Read-only facility users meant the sync system could never corrupt clinical data; per-site schemas and tokens meant one site’s compromise cannot touch another’s data; layered firewalling mattered because container platforms can bypass host firewalls for published ports.

Organizational — the blockers are often not technical

17. National-scale deployment is a coordination exercise. Real blockers cleared: ISP firewall policy for remote-maintenance access (resolved through ministry-level channels, not code), a server migration by the infrastructure owner that silently broke all inbound connectivity, and a mystery TLS certificate that turned out to be another team’s container occupying the port on a shared server. Verify local causes before blaming the network — and budget relationship time with IT teams, facility data managers, and infrastructure owners.
18. Governance messaging is part of the engineering. Deployment authorization from facility leadership was won with concrete safety guarantees: containerized isolation, version-pinned installs, read-only access, the Ministry as data controller, and an anonymization layer before analytics. Data governance framing was integral to buy-in, not an afterthought.

Questions for your country adaptation

For the Week 2 working sessions and your Country HIC Adaptation Roadmap (Sections 3–4), each delegation should consider:

1. Source systems: What EMR/HIS databases run at your facilities? Are their tables timestamped and indexed? Who owns the database credentials?
2. Connectivity profile: What is realistic uptime at your facilities? (The at-least-once + local-queue design assumes the answer is “poor” — if yours is worse, the design still holds; only the queue drains more slowly.)
3. Central infrastructure: Do you have a shared server today? Who else runs on it? What memory/CPU caps would you enforce from day one?
4. Fleet management: Who can physically visit your facilities, and how often? What does that imply about self-updating software and remote diagnostics?
5. Trust boundaries: Who controls the code repository your fleet updates from? Who reviews changes before they reach production?
6. Governance: Who is your data controller? What anonymization happens before analysts see the data? What authorization does a facility director need to sign?