SNOMED CT (Systematized Nomenclature of Medicine -- Clinical Terms) was installed and configured to provide structured clinical coding across the GP Booking App. This document records what was implemented, how it works, and what remains.
Three core SNOMED tables exist in the sector_gp database (aliased to dsp_clinic / dsp_clinic_dev depending on environment):
| Table | Rows | Source |
|---|---|---|
snomed_concepts |
638,855 | RF2 International 20260801 + UK Clinical Extension 20260923 |
snomed_descriptions |
1,956,244 | RF2 International 20260801 + UK Clinical Extension 20260923 |
snomed_relationships |
3,881,039 | RF2 International 20260801 (683 superseded) + UK Clinical Extension 20260923 |
Counts refreshed 2026-10-16 — the figures above are the corpus as now loaded (International RF2 Snapshot
20260801). The previous load's counts were 529,392 / 1,567,909 / 3,548,588, and the Full-file attribution that
stood here was wrong: measured against the tables, noidrepeats, which only a Snapshot load gives.
2026-10-17 (session 88) — the UK Clinical Extension is imported and live; the corpus is now International +
UK Extension, and the corpus is no longer "International content only". Matthew's decision ("let's get these last
two remaining things done"), executed on the same TRUD package (uk_sct2cl_43.0.0_20260923000001Z.zip, SHA-256
recorded above): concepts 535,233 → 638,855, descriptions 1,716,441 → 1,956,244, relationships
3,580,370 → 3,881,039,max(effective_time)20260801 → 20260923; the extension module
999000011000000103carries 103,622 concepts as expected. Release 34 ispublished/is_livein the
reference register (3 files, 6,476,138 rows), with the staged swap, the 13 canonical indexes and a full closure
rebuild (281,363 rows — the counts in the roots table below are the post-import ones).The load had to be merged first, and that is a loader gap worth knowing. Handing the loader both packages
(--edition international --edition uk_clinical_extension) fails on this release: 683 relationship ids appear in
both files — UK revisions of international relationships, 642 of them flippingactive1→0 and 41 already
0, all in module999000011000000103with a latereffective_time. A plain union duplicates the primary key (the
first attempt died onsnomed_relationships_new_pkey, id16614656028) and, worse, would leave 642 UK-retired
relationships active. RF2 semantics are that the latereffective_timewins, and the extension row is the later
one for every shared id. So the load set was prepared as one file per role: concepts and descriptions
concatenated (measured overlap 0), relationships = international minus the 683 then the extension. Per-file
count(*) == lines − 1still holds and the merged files carry 0 duplicate ids. The durable fix is a dedupe step
insnomed_loader.load_release(greatesteffective_timeper id); raised with OpenCode. Provenance, hashes and the
failed attempt are in/root/work/snomed-uk-ext-20261017/PROVENANCE.md; release 33 in the register is that failed
attempt, kept as the audit trail. Also needed first:seed_reference_sourcesagainstdsp_clinic— the
snomed_uk_clinicalsource row (id 6,trud_item_id101) had never been seeded on this database, so the import had
nothing to attribute its release to.What it changed for users: nothing measurable, and one thing not at all. The acceptance set was re-run after the
swap — identical orderings, no regressions, worst search 51.6 ms — andwaterworksstill fails: the corpus
holds only the occupation concept60635006for that string, which is not in the clinical hierarchy, so the
diagnosis picker returns nothing for a urinary term a patient would use. The UK extension adds UK clinical content,
not that lay synonym.Rollback:
snomed_concepts_old/snomed_descriptions_old/snomed_relationships_old/
snomed_descendants_oldare in place (≈1.07 GB) and are the only copy of the 20260801 International corpus —
drop them only on Matthew's instruction, as with the last pair.
Key relationship type: 116680003 = Is a (attribute) — 684,365 active is_a relationships form the SNOMED hierarchy backbone.
| Table | Purpose | Migration |
|---|---|---|
snomed_descendants |
Pre-computed ancestor→descendant closure for fast hierarchy lookups (261,899 rows across 6 roots, rebuilt from the active IS-A graph 2026-10-16) | scripts/rebuild_snomed_descendants.py |
snomed_icd10_map |
SNOMED-to-ICD-10 billing cross-map (20 common mappings seeded) | 0015 |
drug_safety_rules |
Drug-disease contraindication rules (9 rules seeded) | 0016 |
drug_class_members |
Maps individual drug SNOMED IDs to drug class names for safety checks | 0017 |
The table holds the closure for six roots — the roots it is actually built from — walked over the active
is_a graph (snomed_relationships, type_id = 116680003). Measured 2026-10-17:
| Root ID | Label | Descendants |
|---|---|---|
404684003 |
Clinical finding (diagnoses) | 136,944 |
71388002 |
Procedure | 72,325 |
123037004 |
Body structure | 45,450 |
373873005 |
Pharmaceutical / biologic product | 26,401 |
195967001 |
Asthma (disorder) | 129 |
77386006 |
Pregnancy (finding) | 114 |
| Total | 281,363 |
63449007 (Peptic ulcer disease) is not a root in this table, and neither are the other safety-rule conditions —
an earlier version of this page listed it as one. The drug-safety check still works because clinical_data/views.py
falls back to a recursive walk up the is-a chain from the patient's condition; what is missing is the closure fast
path, not the check.
2026-10-16 measurement WITHDRAWN on 2026-10-17 — the "36% short" claim was wrong, and so were the user-cost
figures that rested on it. The walk behind them did not filteractive, so it counted retired concepts as
descendants the closure should hold: 351,514 rows and "93,909 missing (36%)". With theactivefilter the walk
returns 261,899 — the exact row count the table held — plus 574 stored pairs reachable only through retired
edges. Measured consequence:amoxicillinresolves to the same 55 products from the table and from a fresh
active walk, anddiabetesto the same 635; the larger numbers in the withdrawn table were largely substances and
inactive concepts, not descendants the pickers were failing to offer. The old section's claim that no closure could
reach22298006for heart attack was a corpus limit, and the 20260801 release removed it (below).
2026-10-16 (session 87) — the rebuild ran, and was a confirmed no-op.
scripts/rebuild_snomed_descendants.py
walks the active graph per root, stagessnomed_descendants_new, verifies, and swaps in one transaction.
The production run ondsp_clinicwith--allow-removalsmeasured stored 261,899 / rebuilt 261,899 / added 0 /
lost 0: the corpus import's loader had already rebuilt the closure, so there was nothing to repair.
snomed_descendants_oldis identical to it, and the canonical indexes (snomed_descendants_pkey,
idx_desc_descendant) are in place.
2026-10-17 — end-to-end acceptance re-measure (Hermes, session 88). Run inside the deployed
gp_booking_app
container on the app engine, through the app's ownclinical_data.views.snomed_search()— the function both
pickers call — against the live corpus (383,853 active concepts, 1,408,706 active descriptions,
max(effective_time)20260801). Five runs per query; the eight lay terms are measured unrestricted and
restricted to the clinical finding hierarchy, the drug term restricted to the product root.
| Query | Path | Results | min / median / max (ms) | Leads with |
|---|---|---|---|---|
| heart attack | unrestricted | 7 | 9.1 / 9.6 / 13.7 | Myocardial infarction (disorder) |
| heart attack | clinical finding | 3 | 9.8 / 10.8 / 11.3 | Myocardial infarction (disorder) |
| tummy ache | unrestricted | 1 | 7.5 / 7.8 / 11.2 | Stomach ache (finding) |
| tummy ache | clinical finding | 1 | 7.5 / 8.0 / 8.2 | Stomach ache (finding) |
| chest pain | unrestricted | ≥20 | 12.4 / 13.0 / 18.8 | Chest pain (finding) |
| chest pain | clinical finding | ≥20 | 12.0 / 12.5 / 14.0 | Chest pain (finding) |
| nausea | unrestricted | ≥20 | 9.6 / 9.6 / 14.7 | Nausea (finding) |
| nausea | clinical finding | 19 | 9.7 / 9.9 / 11.0 | Nausea (finding) |
| heartburn | unrestricted | 5 | 8.6 / 8.7 / 11.8 | Heartburn (finding) |
| heartburn | clinical finding | 2 | 8.3 / 8.5 / 9.1 | Heartburn (finding) |
| kidney stone | unrestricted | 14 | 9.4 / 9.7 / 17.2 | Kidney stone (disorder) |
| kidney stone | clinical finding | 5 | 9.8 / 10.0 / 10.2 | Kidney stone (disorder) |
| waterworks | unrestricted | 1 | 8.7 / 9.4 / 51.1 | Water treatment plant operator (waterworks) (occupation) |
| waterworks | clinical finding | 0 | 8.6 / 9.1 / 9.7 | — none — |
| rash | unrestricted | ≥20 | 13.4 / 14.3 / 24.7 | Eruption of skin (disorder) |
| rash | clinical finding | ≥20 | 12.5 / 13.5 / 15.2 | Eruption of skin (disorder) |
| amoxicillin | product hierarchy | ≥20 | 14.1 / 15.0 / 20.7 | Product containing amoxicillin (medicinal product) |
| amoxicillin | unrestricted | ≥20 | 16.8 / 21.9 / 24.1 | Amoxicillin (substance) |
Latency: worst single search 51.1 ms, medians 8–22 ms — inside the 300 ms budget by a wide margin, so the
materialised closure remains the right form and its size against the existing indexes costs nothing measurable.
Re-measured after the UK Clinical Extension import (same script, same set, deployed container, corpus 419,044 active concepts / 1,633,370 active descriptions / max(effective_time) 20260923): identical result orderings, no regressions, worst single search 51.6 ms. The one content failure is unchanged — see waterworks below.
Two findings worth keeping. (i) heart attack under the clinical hierarchy now leads with Myocardial infarction
(disorder) [22298006] and returns three results — the release gave MI an active "Heart attack" synonym and the
is_a path, so the gap the withdrawn section described is closed by the corpus, not by the closure.
(ii) waterworks is the one acceptance term that fails, and it fails on content, not on speed: unrestricted it
returns only Water treatment plant operator (waterworks) (occupation), and under the clinical finding hierarchy it
returns nothing. British lay slang for urinary symptoms is not a synonym in this corpus, so the diagnosis picker is
silent for a term a patient would use, and the unrestricted search offers an occupation. That is a corpus/synonym
question — the UK Clinical Extension, or a local lay-term map — recorded here rather than papered over by a query
change.
Decision (2026-10-16) — Matthew's instruction, and my call on the split. The closure rebuild goes ahead
now. It is arithmetic on data we already hold, it is lossless (0 stored pairs lost), and it closes a gap that is
live in front of users today (the prescription picker offers 55 of the 121amoxicillinproducts). It does not
wait for a new release — and the script it leaves behind is the same step a later import has to re-run, so nothing
is done twice. The corpus release is parked, behind a named gate: SNOMED CT UK Edition is added to the
central reference routine as a sixth source. The routine is already live — it pulls dm+d weekly from TRUD
on a configured key and holds a release register, a per-release licence record and staleness alerts — so the
parked item is a subscription and an importer, not new machinery, and it is decision 5 of the approved
central reference data plan ("other data will
follow the same route"). For the record: the corpus we hold is the 2026-02 release (max RF2effectiveTime
20260201) — roughly eight monthly releases behind the October data the same routine already pulls — it was
loaded by hand from a zip, and no release identity for it is recorded anywhere, which the register would fix.
The heart attack mapping (22298006has no lay synonym here; the three concepts that do carry one have no IS-A
at all) is not a reason to hold the rebuild: a current release is expected to re-point that synonym, and that
is exactly what the parked import would prove.
2026-10-16 (session 87) — the parking gate is CLEARED: the account already holds the SNOMED CT UK Clinical
Edition, so no email route and no new subscription are needed. Measured against the live TRUD API with the
platform's own configured key (read-only; the key itself was never printed): item 101, "SNOMED CT UK Clinical
Edition, RF2: Full, Snapshot & Delta", returns HTTP 200 — latest releaseSNOMEDCT2_43.0.0_20260923000001,
releaseDate2026-10-06, archiveuk_sct2cl_43.0.0_20260923000001Z.zip, 990,865,460 bytes, published
SHA-2564D6FF578AB8C2C631CCA366D8B053C0DB67A0AA946FD864DEBF452D1EDC251DF— downloaded and hash-verified
2026-10-16. The items the account does not hold return HTTP 404 (105SNOMED CT UK Drug Extension,1799
UK Monolith Edition), so the key is scoped to what the account is subscribed to: dm+d (24) and this one.
Licence note: the SNOMED CT UK Edition is not open data — dm+d is OGL, SNOMED CT is distributed under the
SNOMED CT licence, so the sixth source needs its own licence-register entry and must not inherit dm+d's OGL row.
Two corrections to the record above, both measured. (1) The corpus was loaded from Snapshot RF2 files, not
the Full files this page names: noidrepeats in any of the three tables (529,392 / 1,567,909 / 3,548,588 rows, all
distinct — a Full load carries every historical version). (2) The corpus holds one copy, in thedsp_clinic
database: thedefaultandsector_gpaliases both resolve to it (measured in the deployed app —snomed_concepts
529,392 rows,max(effective_time)20260201), whileveripath_dentalhas no SNOMED tables at all and
dsp_clinic_devcarries them empty. An update is therefore a single platform-side operation, not one per client.
Hazard found and raised — the TRUD key echoes back, and our fetch record stores message text verbatim. TRUD's own
error bodies carry the request's API key (NOT FOUND: No releases found for API key …, item …), the key sits in the
request path (/trud/api/v1/keys/<key>/items/<id>/releases) and in everyarchiveFileUrl, and
reference_data.pull.fetch_latest_releasepersistsstr(exc)(theNOT_SUBSCRIBEDpath) and TRUD's ownmessage
(theNO_RELEASEpath) intoReferenceFetchAttempt.message— the field the health surface renders. Nothing has
leaked to date: only item 24 is polled and it is subscribed, so that path has never fired (engine logs, app +
worker, 30 days: 0 occurrences) and noreference_datamodel stores a URL. Raised as note (K) for the code:
redact key-shaped tokens before anything is stored or logged.
2026-10-16 (session 87) — the corpus update itself: 2026-02 → International RF2 Snapshot 20260801, and a load
defect repaid. Built into staging tables while the live tables served traffic, then swapped in a single
transaction (the corpus was never empty and no query saw a half-load). Row counts were asserted against the
source files' own line counts and matched exactly: concepts 529,392 → 535,233, descriptions
1,567,909 → 1,716,441, relationships 3,548,588 → 3,580,370;max(effective_time)20260201 → 20260801;
active concepts 383,853, active descriptions 1,408,706, activeis_arelationships 638,927 (the
628,834 above was the old corpus). All 13 index names were preserved through the swap, including
idx_desc_term_trgm, which the search depends on.The heart attack mapping is fixed by the release — measured through the app's own search function. Concept
22298006Myocardial infarction (disorder) now carries an active synonym "Heart attack" (37443015); with
the clinical-note hierarchy (404684003) the search returns Myocardial infarction (disorder) first, ahead of the
two fear-of concepts, andheart attackunscoped returns 7 rows led by MI. The four concepts that carried that
synonym in the old corpus (155304006,194796000,266288001,66514008) are still retired in this release —
the fix came from MI gaining the lay term, not from the retired concepts coming back.A load defect, found and repaid — the reason ~127,000 descriptions were invisible. The replaced corpus's 15
snomed_descriptionsrows with embedded CR/LF were not cosmetic: one alone held a 3.7 MB blob, and between them
they held 127,627 embedded newlines — i.e. ~127,627 description records (8% of the table) had been swallowed
and were never searchable. Cause, reproduced on a 200,000-row sample of the new release: PostgreSQL's default CSV
quoting — a stray"inside a term (335 lines of the file carry one) opens a quoted field that swallows every
following line until the next quote, so 34,698 of 199,999 sample rows vanished and one row grew to 3.7 MB. Loading
withQUOTE E'\b', a character RF2 terms cannot contain, loads all 199,999. The new corpus has zero rows
with embedded CR/LF (count(*) == lines − 1asserted per file), so this class of loss is gone and the loader now says
so out loud.Provenance, recorded because the replaced corpus had none: release
SNOMEDCT2_43.0.0_20260923000001, TRUD item
101 (SNOMED CT UK Clinical Edition, RF2: Full, Snapshot & Delta), published 2026-10-06, archive
uk_sct2cl_43.0.0_20260923000001Z.zip, 990,865,460 bytes, SHA-256
4D6FF578AB8C2C631CCA366D8B053C0DB67A0AA946FD864DEBF452D1EDC251DF— verified on download and again after
transfer to the database host. Component loaded:SnomedCT_InternationalRF2_PRODUCTION_20260801T120000Z, Snapshot
terminology (sct2_Concept_Snapshot_INT,sct2_Description_Snapshot-en_INT,sct2_Relationship_Snapshot_INT).
Working files:/root/work/snomed-20261016/(archive, extracted files,stage_build.log,swap.log). The download
ran at 15:23 UTC — outside TRUD's 00:00–06:00 window for automation — because it was a one-off manual fetch.Rollback, and what is deliberately left standing. The replaced tables are kept in place as
snomed_concepts_old/snomed_descriptions_old/snomed_relationships_old(49 / 428 / 571 MB, their indexes
renamed*_old); the swap is reversed by the same rename list in one transaction. Drop them only when Matthew is
satisfied, since they are the only copy of the 2026-02 corpus — the February files were never kept.Dropped later the same day, on Matthew's instruction (space). The three
_oldtables (1.05 GB) and the
extracted staging files (942 MB) are gone; the corpus itself is 1,070 MB of tables. This page plus
PROVENANCE.mdare now the rollback record — re-download the archive (SHA-256 recorded above) and re-run the
documented staged load if the corpus ever has to be rebuilt.Both items this block left open are now closed (2026-10-17). (1) The closure was rebuilt — see the corrected
section above: it was already the active hierarchy, and the production rebuild measuredadded 0 / lost 0. (2) The
UK Clinical Extension is imported and live — Matthew's decision, session 88, below. Both statements that stood
here ("not rebuilt, 257,605 rows"; "International content only") are superseded.
What: Context-sensitive SNOMED autocomplete. When a user types in a diagnosis field, results are restricted to Clinical finding descendants. Drug search restricts to Pharmaceutical product descendants.
Files:
clinical_data/views.py → SnomedSearchView, refactored SnomedDrugSearchViewclinical_data/urls.py → snomed/search/, snomed/drugs/search/appointment_form.html, clinicalnote_form.html, prescription_form.html (both apps)API: GET /clinical/snomed/search/?q=<term>&hierarchy=<concept_id>
Status: ✅ Working and deployed. Tested via test-client domain.
What: After selecting a SNOMED concept, the app reads its attributes (Finding site, Laterality, Severity) from snomed_relationships and shows appropriate input fields. Selections are stored as JSON in snomed_attributes field. The clinical note textarea auto-populates with a formatted template.
Files:
clinical_data/views.py → SnomedConceptAttributesViewclinical_data/urls.py → snomed/<int:concept_id>/attributes/clinical_data/models.py → ClinicalNote.snomed_attributes (JSONField)clinicalnote_form.htmlAPI: GET /clinical/snomed/<concept_id>/attributes/
Status: ✅ Attributes panel displays, selections store to DB, textarea populates.
Status: ❌ Not implemented (not requested during this session).
What: Automatic mapping from SNOMED diagnosis codes to ICD-10 billing codes. Uses the snomed_icd10_map table. Displayed in the clinical note form auto-template.
Files:
clinical_data/views.py → SnomedICD10MapViewclinical_data/urls.py → snomed/<int:concept_id>/icd10/clinical_data/models.py → SnomedICD10Mapseed_icd10_mapAPI: GET /clinical/snomed/<concept_id>/icd10/
Status: ✅ API returns mapped codes. Displayed in clinical note template. 20 common mappings pre-seeded.
What: Drug-disease contraindication checking. When a clinician selects a drug on the prescription form, the app checks the patient's SNOMED-coded conditions against safety rules. Uses the drug_safety_rules table and drug_class_members mapping to determine which rules apply.
Files:
clinical_data/views.py → DrugSafetyCheckViewclinical_data/urls.py → drug-safety/check/<patient_id>/clinical_data/models.py → DrugSafetyRule, DrugClassMemberseed_safety_rulesprescription_form.htmlAPI: GET /clinical/drug-safety/check/<patient_id>/?drug_concept_id=<snomed_id>
Status: ⚠️ API and drug class mapping work. Alert displays correctly when patient has a matching disorder concept (not monitoring/finding subtypes — see known issues).
| Migration | Description |
|---|---|
0014_clinicalnote_snomed_attributes |
Adds JSON field to ClinicalNote |
0015_snomedicd10map |
Creates ICD-10 map table |
0016_drugsafetyrule |
Creates drug safety rules table |
0017_drugclassmember |
Creates drug-to-class mapping table |
Updated core/middleware.py to allow RECEPTIONIST role access to /clinical/snomed/ endpoints (needed for autocomplete on the appointment form):
'/clinical/snomed/': ['CLINICIAN', 'RECEPTIONIST', 'PRACTICE_MANAGER', 'ADMIN'],
test-client.gp.veripath.co.ukis_a descendant of Asthma (disorder). Clinical notes should use "Asthma (disorder)" (195967001) as the diagnosis.| Endpoint | Method | Purpose |
|---|---|---|
/clinical/snomed/search/ |
GET | Hierarchy-aware concept search |
/clinical/snomed/drugs/search/ |
GET | Drug product search (hierarchy-scoped) |
/clinical/snomed/<id>/attributes/ |
GET | Concept attribute lookup (Smart Templates) |
/clinical/snomed/<id>/icd10/ |
GET | ICD-10 billing code lookup |
/clinical/drug-safety/check/<patient_id>/ |
GET | Drug-disease safety check |
# Seed ICD-10 mappings
python manage.py seed_icd10_map
# Seed drug safety rules
python manage.py seed_safety_rules
| File | Purpose |
|---|---|
clinical_data/views.py |
All SNOMED API views |
clinical_data/urls.py |
API routes |
clinical_data/models.py |
SNOMED extension models |
clinical_data/forms.py |
ClinicalNoteForm with snomed_attributes |
core/middleware.py |
RBAC rules for /clinical/ paths |
templates/.../clinicalnote_form.html |
Smart Templates UI |
templates/.../appointment_form.html |
SNOMED autocomplete UI |
templates/.../prescription_form.html |
Safety alert UI |
dashboards/views/dashboards.py |
Clinician dashboard (practice context fix) |