Freshness & provenance
Every response carries last_synced_at and a per-source schema_state under _meta. When an upstream registry changes shape, we keep the last-good data and flag it stale — so you never silently ship a broken refill. 554,523 tracked changes, queryable via /v1/changes.
Every source is schema-validated at staging — column checks, XSD, and JSON-schema assertions per feed. Failed rows land in quarantine; promotion is blocked above a 1% fail-rate. Nothing reaches the read path unvalidated, so the answer you get has already passed its checks.
When an upstream registry silently changes shape, the run is flagged `schema_drift`: we keep the last-good snapshot serving, alert on call, and surface `schema_state: "stale"` in every API response — so you know before you ship a broken refill.
Every promoted row writes to `changes` (before, after, source, occurred_at) — 554,523 tracked changes and counting. Ask "what changed this week" via `GET /v1/changes?since=…&source=…`, or subscribe by webhook and react the moment a registry moves.
Every response carries `last_synced_at` and `schema_state` per source under `_meta`. The previous good snapshot is still served, but flagged — never silently. Knowing exactly how fresh each answer is means you decide what to trust.
_meta{
"drug": { ... },
"_meta": {
"ndc": { "last_synced_at": "2026-05-30T04:00:00Z", "schema_state": "ok" },
"dmd": { "last_synced_at": "2026-05-29T02:00:00Z", "schema_state": "ok" },
"cima": { "last_synced_at": "2026-05-30T03:00:00Z", "schema_state": "ok" },
"bag_sl": { "last_synced_at": "2026-05-15T05:01:00Z", "schema_state": "ok" },
"aips": { "last_synced_at": "2026-05-26T05:00:00Z", "schema_state": "ok" },
"epha": { "last_synced_at": "2026-05-25T03:00:00Z",
"schema_state": "stale", "reason": "schema_drift_2026-05-29" }
}
}Operational runbook
28 sources across 19 jurisdictions, each on its own cadence. Detection is the easy part. The interesting question is what we do in the four hours after the alert fires — and what your code sees while we're working.
Per-source validator trips on column rename, type change, XML element drift, JSON-schema fail. The run gets `schema_drift` status, promotion is blocked, last-good stays live.
PagerDuty + Slack fire within the cron tick. The runbook URL goes in the body. `_meta.schema_state` flips to `stale` in every response touching that source.
On-call inspects `clinical_drug_data_sync_runs.payload` for the diff. Additive changes go through `bag_sl_add_observed_column` with no redeploy.
Destructive changes get a parser patch + a forced re-run. SLA: best-effort 24h. Customers see the state transition in the change feed and the API response.
Every source has an edge function that pulls the upstream artefact, parses it, and writes to a staging table before any production row moves. drugs, interactions, pharmacode_aliases see only promoted rows.
sources/raw/{source}/{run_id}.{format}
↓
{source}_staging
↓ validation
{source}_quarantine
↓ promotion
drugs · interactions · atc_codesEvery promotion writes one row per affected natural-key. Customers consume via webhook (Growth+) orGET /v1/changes?since=…. Most-requested feature from EHR vendors who currently re-import the catalogue monthly because they cannot tell what moved.
CREATE TABLE changes (
id uuid PRIMARY KEY,
table_name text NOT NULL,
natural_key jsonb NOT NULL,
drug_id uuid REFERENCES drugs(id),
change_type text CHECK (
change_type IN ('insert','update','delete')),
before jsonb,
after jsonb,
source text NOT NULL,
occurred_at timestamptz NOT NULL DEFAULT now()
);