Health Sync — Facility-to-Center Data Synchronization

Health Sync is HIC’s source-layer ingestion pipeline for facility EMR data: it moves records from facility electronic medical record databases (OpenMRS-based MySQL) into the central PostgreSQL store in near-real-time, from which they feed HIC’s analytics environment (lakehouse layers, dashboards, surveillance systems).

This page documents what the system is, how it works, and the design decisions behind it. The step-by-step site onboarding procedure lives in the deployment runbook.

Why it exists

Rwanda’s health facilities run local EMR systems that are isolated inside each facility. The national health intelligence function needs that data consolidated centrally, in near-real-time, for analysis, dashboards, and outbreak surveillance — the driving use case was Ebola outbreak response, where early case detection requires seeing facility encounter data within minutes, not at end-of-month reporting.

Three realities shaped the design:

1. Connectivity at facilities is unreliable. Links drop, power fails, and a sync system must lose zero data across outages.
2. Nobody can visit a site to deploy a fix. Facilities are hours away; the software must update itself.
3. Facility servers are fragile and not ours. They run production clinical systems on modest hardware. Anything installed must be minimal, isolated, reversible, and provably harmless.

In medallion terms, Health Sync fills the front of the pipeline — reliable, continuous, facility-level raw data arriving centrally:

Facility EMR (MySQL) ──▶ Health Sync ──▶ Central PostgreSQL ──▶ HIC analytics
                                                               (lakehouse, Superset,
                                                                surveillance dashboards)

System overview

Health Sync is two small, independent Node.js services:

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

The runner: three isolated loops

1. Sync loop — every 60s (configurable): for each configured table, read rows newer than a per-table watermark (WHERE timestamp_col > watermark ORDER BY timestamp_col ASC LIMIT 500), enqueue them into a durable local SQLite database, then flush the queue to the central API with exponential backoff. If the network is down, rows simply accumulate locally and drain when connectivity returns.
2. Updater loop — polls the GitHub commits API; when a new commit lands on the main branch, it pulls the code and exits cleanly. Docker’s restart policy relaunches it on the new version. No one visits a facility to deploy a fix.
3. Heartbeat loop — every 30s, reports queue depth and last-sync time to the center, powering the online/stale/offline status dashboard.

All runner state — queue, watermarks, deployed code version — lives in one SQLite file that survives crashes, restarts, and updates.

The central API

• POST /sync/batch — authenticated batch upsert (INSERT … ON CONFLICT DO UPDATE). Auto-creates the facility’s schema and tables on first sync from config-declared column types; stamps every row with a _synced_at timestamp.
• POST /heartbeat, POST /log, GET /status — fleet monitoring.
• GET /metrics + a self-contained HTML dashboard — hourly ingestion bar charts per site and table, so operators see a stalled site as a visual gap rather than reading raw timestamps.

PostgreSQL layout: shared operational tables (site tokens, heartbeats, logs, metrics) in one schema; each facility’s clinical data in its own schema (e.g. gisenyi.obs), giving natural isolation and per-site access control.

Design decisions and their why

These decisions were made deliberately, and several were validated by production incidents.

Durability: embrace duplicates, forbid gaps

This philosophy was tested for real: a queue bug once stalled ~4,000 rows at a site for days. When the fix shipped (via the self-updater itself), the stuck queue drained completely — no data was lost and no manual cleanup was needed.

Security: identity from cryptography, not configuration

Resilience: no silent failures

• Every periodic task is wrapped so that any error is always logged — never swallowed.
• Shell commands run with explicit timeouts; a hung command cannot stall the loops.
• The three runner loops are fully isolated: an updater failure can never stop syncing, and vice versa.
• Graceful shutdown: on stop, the runner finishes in-flight work, closes its state cleanly, and exits within Docker’s grace window.
• Monitoring writes are fire-and-forget: a metrics failure can never affect a sync response. The data pipeline is sacred; everything else is best-effort.

Simplicity as an operational strategy

• Self-update = git pull + clean exit + container restart policy. No orchestration platform needed at facilities.
• The status dashboard is a single self-contained HTML page — no build step, nothing extra to operate.
• Column types are declared in configuration, not auto-inferred — deterministic beats clever.
• Explicit non-goals kept the MVP shippable: no delete propagation, no schema-migration engine, no token-management UI.

Current state at a glance

Live facility sites: Gisenyi DH, Mugonero DH, Byumba L2TH, Kibuye RH, Bushenge PH, Murunda DH, Mibilizi DH, Kibogora DH, and Nemba DH.

Known limitations and roadmap

• Deletes are not propagated (by design); hard deletes at source stay at source.
• Schema migrations are manual — the API creates tables but does not alter them.
• Tables with day-granularity timestamps need periodic watermark backfills (tracked and scripted).
• Planned: HTTP compression for low-bandwidth links; an intermediary machine between facility and central networks; connectors beyond MySQL 8 (the connector interface is pluggable — one file per new database type).

Related pages

• Deployment runbook — the exact per-site onboarding procedure, command by command.
• Data sources — the Prefect-based national system integrations that Health Sync complements.
• Health Sync in the Learning Exchange — the methodology, lessons learned, and country-adaptation questions as presented to exchange delegates.