> For the complete documentation index, see [llms.txt](https://spice.docs.medtroniclabs.org/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://spice.docs.medtroniclabs.org/deploy/deployment-guide/data-import-and-migration.md).

# Data Import & Migration

A new SPICE deployment needs two kinds of data before field teams can use it:

1. **Reference data**: the administrative hierarchy, facilities, users and roles, and the medication and lab test catalogues. You load this in every deployment.
2. **Historical data**: households, members and clinical records from the system you are replacing. You load this only if you are migrating from a previous system.

This page covers both. For day-to-day administration of the same data, see the [Admin Guide](https://spice.docs.medtroniclabs.org/admin-guide/admin-guide.md).

## Part 1: Reference data

Load reference data in this order, because each step depends on the one before.

| Order | Data                                | How                                       | Notes                                                                                                                                                                                    |
| ----- | ----------------------------------- | ----------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| 1     | Region and administrative hierarchy | Bulk upload (`.xlsx`) in the Admin Portal | See [Administrative Hierarchy](https://spice.docs.medtroniclabs.org/admin-guide/country-level-administrative-divisions.md). Only a super administrator can upload.                       |
| 2     | Facilities                          | Admin Portal, one facility at a time      | See [Facilities](https://spice.docs.medtroniclabs.org/admin-guide/facilities.md). Facilities are linked to villages from step 1.                                                         |
| 3     | Medication and lab test catalogues  | Admin Portal                              | See [Medications & Lab Tests](https://spice.docs.medtroniclabs.org/admin-guide/medications-and-lab-tests.md). You can add several medications in one visit, but there is no file import. |
| 4     | Users and roles                     | Admin Portal, one user at a time          | See [Users, Roles & Access](https://spice.docs.medtroniclabs.org/admin-guide/users-roles-and-access.md). Users are assigned to facilities or areas from steps 1 and 2.                   |
| 5     | Workflows and forms                 | Admin Portal                              | See [Workflow & Form Configuration](https://spice.docs.medtroniclabs.org/admin-guide/clinical-workflow-customization.md).                                                                |

### Administrative hierarchy

The hierarchy upload is the one bulk import the Admin Portal provides:

1. Create the region (a super administrator does this from the region list).
2. On the region page, select **Download Template**, fill in one row per lowest-level unit, and select **Upload**.
3. Check the result. **Rows that fail validation are skipped without an on-screen message**, so download the hierarchy afterwards and compare it with your file.

Uploading the same file again creates nothing new, so it is safe to correct the file and repeat. The upload matches units by code under their parent and never renames existing units.

Decide your area codes before you upload. If you use [administrative-area partitioning](https://spice.docs.medtroniclabs.org/deploy/deployment-guide/fhir-server-and-mapper.md#administrative-area-partitioning) in the FHIR server, district codes become partition identifiers, and changing them later is not practical.

### Facilities, users and catalogues

The Admin Portal creates facilities, users and catalogue entries through forms. The services expose REST APIs behind those forms.

For a large number of users or facilities, the migration tooling described below includes scripts that create them through the same APIs from a CSV. That is a migration aid, not a general import feature. See [Foundation data](#1-foundation-data).

## Part 2: Migrating historical data

### Approach

Migration does not write to the databases directly. The tooling reads **extract files** (CSV) produced from the legacy system and sends them to a running SPICE deployment through its APIs. For households, members and clinical records, it uses the same offline-sync API that the mobile app uploads to, signing in as the field worker who owns each record. Everything therefore passes through the normal validation, the offline sync queue, the FHIR mapper and the FHIR server, and records show the correct creator.

The tooling is a set of Python scripts, one module per kind of data, plus shell orchestrators that run modules in a fixed order. You will adapt three things for your own source system:

| Adapt             | What it involves                                                                                                                                                                                                       |
| ----------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **Extraction**    | Write queries that export your legacy data into the CSV layouts the modules expect (one extract per module). The repository's example queries are specific to its original source systems.                             |
| **Mapping**       | Map your codes and values to SPICE's. Examples: household types, gender values, marital status, diagnosis codes, medication and lab test identifiers. The mappings live in each module's code, so you edit them there. |
| **Scoping rules** | Decide how records belong to areas and users. The modules resolve each row's village, facility and owning field worker against what already exists in SPICE.                                                           |

Plan this work as a small software project. Expect to iterate on extracts and mappings against a test deployment before you migrate to production.

### The staged pipeline

The pipeline works in stages. Each stage depends on the previous one, and running out of order does not usually fail loudly: it produces skipped rows.

```
1. Foundation     hierarchy -> facilities -> organisation units -> users -> passwords
2. Registration   households and members (and members registered outside a household)
3. Services       maternal, newborn and child health records
4. Chronic care   screening -> diagnosis/enrolment -> assessments -> follow-up -> dispensing
5. Verification   summaries, count reconciliation, spot checks
```

Stage 3 and stage 4 are independent of each other once stage 1 is done. Use only the stages your programme needs.

#### 1. Foundation data

| Module                   | What it does                                                                                                                                                                                                                                           | Prerequisite            |
| ------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ----------------------- |
| `location`               | Converts a legacy location table into the hierarchy layout (one row per village with the full chain of parents). It only writes CSV files; there is a matching validation script that compares row counts. Upload the result through the Admin Portal. | None                    |
| `health_facility`        | Creates or updates facilities from a CSV, filtered by area and facility type                                                                                                                                                                           | Hierarchy               |
| `branch`                 | Creates organisation units (branches) from a CSV, and writes a code-to-id mapping that later user payloads use                                                                                                                                         | Hierarchy               |
| `users`                  | Merges source user exports into input CSVs, creates users, and writes a triage report of rows that could not be created                                                                                                                                | Facilities and branches |
| `users/update_passwords` | Sets a known **migration password** on every account the migration will later sign in as                                                                                                                                                               | Users                   |

Two points deserve care:

* Every migrated field worker account must already have the migration password before any record migration runs. A failed sign-in silently skips every row that belongs to that user. In the combined pipeline it shows as a `LOGIN_FAILED` entry in the run summary. Rotate the passwords again after migration.
* A user row with a missing facility fails with a `400` and the user is never created. Fix blanks in the source before migrating users.

#### 2. Registration data

The household and member modules create households and their members under the owning field worker. Members registered outside a household are handled by a separate module. Each row needs a stable **reference id** from the source (it becomes the SPICE reference id), a link from member to household, a village that exists in your hierarchy, and an owner that exists as a user.

Rows are skipped, not failed, when the village cannot be resolved or the owner is not found. Skips are reported with a cause.

#### 3. Maternal, newborn and child health

Records such as pregnancy profile, antenatal and postnatal visits, pregnancy outcome, family planning and child health visits attach to existing members. There are two ways to run them:

| Path                                | How it works                                                                                                                          | Use when                                                                                   |
| ----------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------ |
| **Combined** (`rmnch_sync`)         | One payload per field worker containing households, members and all service records, with each pregnancy grouped under one episode id | You are loading a fresh target from scratch. Pick this path and stay on it.                |
| **Step chain** (`run_migration.sh`) | Separate modules run in sequence: household and member, then each service type                                                        | You need to re-run one record type in isolation, or entities were created by another route |

Do not mix the two paths on the same target. If households and members already exist from the step chain, the combined path withholds all clinical records for them and migrates no clinical data.

#### 4. Chronic care (non-communicable disease) records

This pipeline has its own orchestrator and runs in a fixed order:

```
screening (before) -> diagnosis confirmation and enrolment -> screening (after)
  -> assessments -> follow-up visits -> dispensing
```

The master data it needs (users, medication catalogue, lab test catalogue) must exist first, and later steps need the patients created by earlier ones. Keep the patient extract from the diagnosis step: a later incremental run needs it to recognise patients whose national ID was corrected in between, and otherwise registers them twice.

### Input files, at a conceptual level

Each module reads one or more CSV files. File names do not depend on the area: the same names are reused for every area and only the contents change. Override paths with the module's CSV flags when your names differ.

| Extract              | What each row represents                                             | Key columns to provide                                                                                |
| -------------------- | -------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------- |
| Hierarchy            | A village with its parents                                           | Names and codes at every level                                                                        |
| Facilities           | A facility                                                           | Name, type, area, a code that identifies it                                                           |
| Users                | A user                                                               | Username, role, facility, supervisor (for community workers), phone and email                         |
| Households           | A household                                                          | Stable reference id, head details, village, location, owner                                           |
| Members              | A person                                                             | Stable reference id, household reference id, name, date of birth, gender, national ID, village, owner |
| Service records      | A visit or event for a member                                        | Member reference id, visit date, form-specific values, owner                                          |
| Chronic-care records | A screening, confirmation, assessment, follow-up or dispensing event | Patient identifier, dates, measurements, diagnoses, medications, performing user                      |

The module README files list exact column names, the rules for each, and the categories used to report skipped rows.

### Configure a run

Each module resolves its settings from two layered `.env` files: a common one and an optional per-module one. Keep secrets out of version control.

| Setting                             | Purpose                                                                                                                                     |
| ----------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------- |
| Environment selector                | Chooses which deployment to talk to. Define your own environments in the tooling's environment registry (`scripts/common/environments.py`). |
| Administrator username and password | Used by modules that need an administrator session                                                                                          |
| Password salt                       | The salt the platform uses to hash passwords at sign-in                                                                                     |
| Migration password                  | The password every migrated user is signed in with                                                                                          |
| Device id and app version           | Sent with each request, as the mobile app would                                                                                             |
| Monitor settings                    | Polling interval and maximum wait for the offline sync queue to drain                                                                       |

Create the Python environment once:

```bash
cd data_migration
python3 -m venv env
env/bin/pip install -r scripts/requirements.txt
```

Writing to a production environment asks for an explicit confirmation first. Dry runs skip the prompt.

### Dry runs, validation and checking results

Always run a module with `--dry-run` before the live run. A dry run builds every payload and writes the reports, but sends no records:

```bash
env/bin/python scripts/health_facility/migrate.py --district-name <AREA-NAME> --types <TYPE-LIST> --dry-run
```

Note the limits of a dry run:

* It still signs in and often still reads from the target (reference-id and geography lookups), so it needs network access.
* Most modules overwrite their summary file on every run, including dry runs. Check `dryRun` and the environment in the summary header before you trust any count.

Every run writes machine-readable output under `output/<module>/`:

| Output                                                                                        | Use                                                        |
| --------------------------------------------------------------------------------------------- | ---------------------------------------------------------- |
| Summary JSON with a common header (environment, dry-run flag, filters, input files, duration) | Counts of created, updated, skipped and failed rows        |
| Skipped and failed rows as CSV, with a reason for each                                        | Fix the source data or the mapping, then re-run those rows |
| Per-user summaries (where applicable)                                                         | Find which field worker's data was not migrated, and why   |

After a run, verify the result in three ways:

1. **Reconcile counts.** Compare the number of households, members and records in the source extract with the counts in SPICE (for example in the reporting database or the Admin Portal), per area. The repository includes example count queries for this. Account for rows reported as skipped.
2. **Check the skip reports.** A large `village not found` or `owner not found` category usually means a spelling difference between the source and your hierarchy or user list. Compare by code, not by name.
3. **Spot-check on a device.** Sign in as a migrated field worker, sync, and open several migrated households and patients.

### Idempotent re-runs and recovery

Migration is designed so that a failed or partial run can be repeated, with limits you must understand:

* **Entities are protected.** Before creating households and members, the tooling looks up which reference ids already exist and skips them (reported as already existing). Running the same extract again does not duplicate them.
* **Clinical records are not deduplicated.** There is no record-level duplicate check for assessments. In the combined path, assessments for members that already exist are deliberately withheld on a re-run. Delta loads onto an already-migrated area need dedicated options (deferred replay); read the module documentation before attempting one.
* **The queue must drain first.** The offline-sync API accepts a payload and returns immediately. The platform processes it asynchronously, over minutes to many hours for a large area. **Wait for the queue to drain before you retry or start the next stage.** Retrying early is the main way to create duplicate clinical records, because the lookup cannot yet see the records the first attempt is still creating. A monitor script waits for the queue (`offline_sync_monitor.py`), and its report action exits non-zero while rows are still in progress, so it can gate the next command.
* **Resume points.** Some chronic-care steps support checkpoint files and resume or retry options for failed and skipped rows. Re-run into the same output folder so that checkpoints are honoured.
* **Failed providers.** When a field worker's data fails, fix the cause, wait for the queue to drain, and re-run for only that user (the user filter flag).

### Common problems

| Symptom                                                     | Likely cause and fix                                                                                                                                                                                                                                                                                |
| ----------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| All of one user's rows are skipped                          | Sign-in failed. The user does not exist, is inactive or is locked, or the migration password was not set. Check the user and run the password step.                                                                                                                                                 |
| Many rows skipped as village not found                      | The village in the extract does not match the hierarchy. Fix the spelling in the source or the hierarchy, and match by code where you can.                                                                                                                                                          |
| Households appear without clinical history                  | The path was mixed (see stage 3), or clinical rows were withheld because the members already existed.                                                                                                                                                                                               |
| Duplicate clinical records                                  | A retry ran before the queue drained. Delete the duplicates and always wait for the drain.                                                                                                                                                                                                          |
| Run seems to finish but data is missing hours later         | Processing is asynchronous. Check queue status with the monitor before concluding the run failed.                                                                                                                                                                                                   |
| Writes fail against the wrong area                          | Stale tenant or area settings in the `.env` files. Set the area explicitly for each run and clear unused settings.                                                                                                                                                                                  |
| A tool fails immediately with a missing-environment message | The environment selector is not set. Set it in the common `.env`.                                                                                                                                                                                                                                   |
| Unusually slow migration                                    | The tooling is limited by the backend (database CPU and FHIR server throughput), not by the scripts. See [Sizing & Operations](https://spice.docs.medtroniclabs.org/deploy/deployment-guide/sizing-and-operations.md). Raising worker counts beyond what the backend can absorb makes things worse. |

Use a disposable test deployment for the first full rehearsal, because there is no routine way to undo a migration run.
