Data import

The import API accepts two file types: CSV and XLSX. Anything else is refused with Unsupported format. Supported: csv, xlsx. The declared content type must also be on an allow-list and consistent with the file extension — a .csv announced as a spreadsheet is rejected with “File extension does not match the declared content type”.

• Maximum size: 50 MB at the API. The wizard applies a stricter 25 MB guard in the browser before uploading, rejecting larger files locally with “File is larger than 25 MB. Split it or contact an administrator.”
• Empty files and files with a header row but no data rows are rejected.
• Encoding: CSV is read as UTF-8 and a byte-order mark is tolerated, so a file exported from Excel as “CSV UTF-8” works.
• Headers are normalised before anything else: lower-cased, with spaces and dashes turned into underscores. Cell values are trimmed, and a row that is blank in every column is dropped.

The template is an XLSX workbook generated on demand for the current study, with one column per eCRF field key, drop-downs seeded with the controlled vocabulary for each coded field, and the field help text carried across. Because its column headers are the field keys, a file built from the template is mapped by identity — no guessing, and no AI step.

Two ways to get it:

• In the import wizard, choose Download the template on the first step and press Download blank template.
• On the Data Exchange page, use the template download in the import panel.

Both call the same endpoint. Any signed-in role may download it; you do not need import rights to look at the field list.

GET /api/v1/data/import/template
X-Study-ID: tiger
Authorization: Bearer <access_token>

→ 200 application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
  Content-Disposition: attachment; filename=ecrf_import_template_tiger.xlsx

An optional dataset_version query parameter selects a specific dataset version of the template. The list of available templates, with their full column list, is also available as JSON — see Bulk operations.

The wizard has six steps and keeps its position in the URL, so refreshing or coming back later resumes the same job. No patient data is kept in browser storage — only the opaque job id travels in the query string, and every row is re-fetched from the server.

1. 1

   Choose path

   Pick between an AI-assisted upload — a spreadsheet in any layout, whose columns and coded values the harmonizer proposes a mapping for — and the template, where you fill in a blank workbook whose columns are already the eCRF field keys. Both paths end in the same review-and-sign flow; the template path skips the guessing. Nothing has been uploaded yet.

2. 2

   Upload

   Drop or pick the file. Before anything leaves the browser the wizard checks the extension and the 25 MB limit, parses the file locally and shows you the first eight rows with their headers, so you can catch an obviously wrong file — a shifted header row, an export of the wrong sheet — without a round trip.

   You can tick test batch here. A test batch is promoted as normal but its records are flagged, and flagged records are excluded from the dashboard aggregations and analytics. Use it for a dry run of a migration.

   On upload the server files the job under your effective institution — your own, or the masquerade target if you are masquerading. No field in the request can name a different institution, and an account with no institution cannot import at all.

3. 3

   Map fields

   The server has now parsed the file and produced a mapping plan: for each source column, the eCRF section and field it should feed, a confidence score and a one-line rationale; and for coded columns, a value-level map from each distinct source value to the controlled vocabulary. Columns it could not place are listed as unmapped.

   Review it and correct anything wrong. Saving a changed plan re-runs the deterministic transform over every staged row from its stored source values — you never have to re-upload to fix a mapping. Setting a column's section to empty drops it back to the unmapped list, which is how you tell the system to ignore a column.

4. 4

   Review

   Every row is shown with its source values beside the proposed eCRF values, flagged rows first. You can correct a value inline, approve a row, or reject it. Corrections are stored separately from the proposal, so the original import is never overwritten and the reviewer's change is auditable.

   Approve all clean rows approves everything without a flag in one action; rows carrying a flag always need an explicit decision from you. You cannot leave this step with rows still pending.

5. 5

   Agreement

   Read the attestation, tick the acknowledgement and type your full name as an electronic signature. This is the gate: the batch cannot be promoted until it is signed. See below for what is recorded.

6. 6

   Import (promote)

   Promotion writes the approved rows into your institution's patient dataset and shows a summary of how many were inserted, skipped and failed. If your institution is still in the parallel run against the legacy system, promotion is refused with a 412 and an explanation — the staged, reviewed and signed batch is kept and can be promoted after cutover.

Mapping happens in two layers, and only the second one is deterministic transformation:

1. Column → field. In template mode this is identity: a header that matches a known field key is used as-is, and anything else is listed as unmapped and ignored. In AI-assisted mode the harmonizer proposes the mapping from the column headers plus a capped sample of distinct values per column (at most 50), never from whole rows.
2. Value → controlled vocabulary. For coded fields the plan can also carry a per-value map, so a source that writes “M” or “male” lands on the vocabulary term the eCRF expects.

Each transformed value is then coerced against the target schema descriptor for that field, deterministically and with no model:

• Booleans accept the obvious tokens in either direction (true/yes/1/y/t/present/positive and false/no/0/n/f/absent/negative). Anything else is flagged rather than guessed.
• Numbers are parsed with thousands separators stripped, truncated to a whole number where the field is an integer field, and checked against the field's minimum and maximum. A value outside the range is still written but flagged, so you can see and correct it. A boolean is never accepted as a number.
• Coded fields are matched case-insensitively against the allowed options. A value that is not in the vocabulary after the value-map has been applied is not written at all — it is flagged.
• Empty cells are simply not written. They are not zeros and not empty strings.

Staging assigns each row a review status and a list of flags. A row with no flags is pending (clean, awaiting approval); a row with any flag is needs_review and must be dealt with explicitly. Four flag codes exist:

The job also carries a harmonization score: the percentage of staged rows that came through with no flags.

If you want to check a file before staging it, there is a dry-run validation endpoint that parses the file, counts the rows and reports every column that is not a recognised eCRF field, without creating a job or calling the harmonizer. It is the one call that returns a flat errors list — each entry with a row index, a field, a message and a severity. See Bulk operations.

Signing the submitter agreement is what turns a reviewed batch into a promotable one. You attest, on behalf of your institution, that the data is accurate and comes from your institution's own records; that you have reviewed the automated harmonization and accept the value mappings and corrections applied; and that you are authorised to import it into your institution's dataset.

Three things are written in a single transaction:

• a sign-off record carrying your user id, the name you typed as your signature, the acknowledgement flag, the verbatim attestation text you accepted, your IP address, your browser, the timestamp and the batch it covers;
• an append-only consent record against the versioned agreement document, carrying the number of staged rows as context;
• the job's status advancing to awaiting promotion, with a pointer to the sign-off.

If any part fails, the whole transaction is rolled back — a batch can never reach the promotable state without a durable, matching sign-off and consent pair.

Promotion is the only step that writes to the live patient dataset. Everything before it lives in a separate staging table, encrypted at rest, visible only to reviewers scoped to that institution.

Before any write, three preconditions are checked and fail closed:

1. the batch has a signed agreement;
2. the batch has an institution;
3. every row being promoted is approved or corrected — the query filters to those two statuses and each row is asserted again before it is written.

For each row the reviewer's corrections are merged over the proposed values, the result is grouped into eCRF sections, and a content hash is computed. The row is then matched against existing patients by a stable key derived from the row itself, and one of three things happens:

• insert — no existing patient for that key;
• update — a patient exists and the content hash has changed;
• skip — a patient exists and the hash is identical, so there is nothing to write.

That is what makes re-running an import safe: the key is the mapped study number when the file has one, otherwise a hash of the whole source row, so importing the same spreadsheet twice updates or skips rather than duplicating. Imported patients are tagged with their own source system, in a separate identity space from the legacy migration.

Promoted records are created with status draft, and their completion percentage is recalculated from the sections the import actually filled. A row that fails is recorded with its error and the run continues — one bad row does not abort the batch.

How the job status flips to completed. Promotion itself only stamps the counts. The job is marked completed when no open rows remain — every non-deleted row either promoted or explicitly rejected. Anything still pending, under review, or approved but not successfully promoted keeps the job in awaiting promotion, so you can run promotion again; re-running is safe by the same hash rule.

Scope is enforced on every one of these calls, not just the first: a job belonging to another institution answers 404/403 even if you know its id, and the check fails closed for a job with no institution. Masquerading narrows you to the target institution for the whole flow.

Each import leaves several independent trails:

• an audit log entry when the batch is staged, with the mode, the file name, the row count and the resulting status; and another when it is promoted, with the result counts — both carrying your identity, role, IP address, browser and the study;
• a per-row run log written under one run id for the promotion, recording for every row whether it was an insert, an update, a skip or an error, together with the content hash before and after;
• the sign-off and consent records from the agreement step;
• the staged rows themselves, which keep the original source row, the proposed values, the reviewer's corrections and a per-field confidence score — encrypted, and retained with the job.

The file's SHA-256 checksum and byte size are stored on the job, so a batch can be tied back to the exact file that produced it.