The JSON Schema bug that only fires on a customer's first real query

There is a category of bug that passes every test you write, ships to production, and then fails on a customer's very first real query. We hit a clean example of it building against ClinicalTrials.gov, and the root cause was four characters of JSON.

Quick answer

If your output schema declares "type": "string" for a field that is sometimes absent, your pipeline will die on the first sparse record — and no realistic test fixture will catch it. Registry data is sparse by nature: a Phase 1 trial has no results, an observational study has no intervention arm, a terminated trial has no completion date. Declare every optional field as "type": ["string", "null"]. A QA sample chosen because it looks good is the worst fixture you can pick, because it hides exactly this class of failure.

The failure mode

Apify Actors can publish a dataset schema so downstream consumers get a typed, validated table instead of loose JSON. Ours declared fields the obvious way:

{
  "fields": {
    "primary_completion_date": { "type": "string" },
    "enrollment_count":        { "type": "integer" }
  }
}

That reads as correct. It is not, and here is why it is dangerous rather than merely wrong: validation happens at write time, per record. So the run starts, streams a few hundred perfectly good rows, and then hits a trial with no primary completion date. null is not a string. The write is rejected, the exception propagates, and the whole run dies — after the customer has been charged for the rows that already landed.

The correct declaration:

{
  "fields": {
    "primary_completion_date": { "type": ["string", "null"] },
    "enrollment_count":        { "type": ["integer", "null"] }
  }
}

Four characters. The interesting part is not the fix, it is why it survived testing.

Why a good test fixture hid a real bug

When you build a fixture, you naturally reach for a representative record — one that exercises the parser, shows off the fields, and reads well in a code review. For registry data, that means a large, well-funded, fully-reported interventional trial. Every field populated. Every assertion meaningful.

That fixture cannot fail this bug. Not "is unlikely to" — cannot. The failure requires a null, and you deliberately picked a record that has none.

The same applies to smoke tests. A three-row smoke test against the default query hits the most complete, most-cited, best-maintained records in the registry. It passes. Then a customer filters for terminated trials in a rare disease, gets a result set that is 60% sparse, and the run dies on row 4.

We changed two things:

1. Fixtures must include the ugliest record we can find, not the prettiest. Withdrawn studies, missing sponsors, empty location arrays.
2. The scaffolder now emits nullable unions by default, so no new Actor can be born with the bug. The fix belongs at the template, not in a checklist item someone has to remember.

Why is the ClinicalTrials.gov API hard to use?

Not because it blocks you — it is a public, keyless, well-run government API and it does not fight back at all. It is hard because of shape.

The v2 API returns deeply nested protocol sections that mirror the regulatory document structure, not anything you would want in a spreadsheet:

protocolSection
├── identificationModule      → nctId, briefTitle, officialTitle
├── statusModule              → overallStatus, startDateStruct, completionDateStruct
├── sponsorCollaboratorsModule→ leadSponsor.name, collaborators[]
├── designModule              → phases[], enrollmentInfo.count, studyType
├── conditionsModule          → conditions[], keywords[]
├── armsInterventionsModule   → interventions[].type, .name
└── contactsLocationsModule   → locations[].facility, .city, .country

Almost every leaf is optional, several are arrays that are sometimes absent and sometimes empty, and the date fields are structs with their own optional precision qualifier rather than plain strings. Flattening that into one predictable row per trial is the actual work, and it is the part that produces nulls everywhere — which loops straight back to the schema bug above.

The API also uses cursor paging (nextPageToken), not offset. That is the right choice on their side and a small adjustment on yours: page until the token comes back absent, and do not try to compute a page count up front.

Retry the registry's 5xx

One more that costs a run if you skip it: ClinicalTrials.gov returns transient 5xx under load, and a single one will kill an unguarded long query. Retry with exponential backoff and treat 5xx as retryable rather than fatal. The same rule we apply to every government API we touch — these are public services under real load, not enterprise SLAs, and intermittent failure is normal operating behaviour rather than an exception.

What the scraper actually does

ClinicalTrials.gov Scraper searches the registry and returns one flat row per trial — NCT ID, title, phase, status, sponsor, conditions, interventions, enrollment, locations, and start and completion dates — as JSON, CSV or Excel. It pages the cursor API, stops exactly on your result cap, and retries transient registry 5xx instead of failing the run. Pay-per-result at $2.05 per 1,000 rows, so a query that returns nothing costs nothing beyond the start fee.

We are not going to claim registry data is tidy. It is a regulatory filing system with twenty years of schema evolution in it, and absorbing the nesting, the nulls, the cursor paging and the retries is our job, not yours.

If you work adjacent regulatory sources, the neighbours are FDA recalls, NPI healthcare providers and PubMed papers.

The rule worth stealing

Your test fixture should be the worst record in the dataset, not the best one. A fixture chosen for how well it demonstrates the happy path is a fixture guaranteed to miss the sparse-record bug.